//调用
dgvRecord.DataSource = dt;
DAL.DataToExcel exp =new DAL.DataToExcel();
int flag = exp.ExistsRegedit();
if (flag == 0)
{
MessageBox.Show("请先安装office或者wps,再进行导出 ！");
}
else
{
if (dt != null && dt.Rows.Count > 0)
{
if (flag == 1 || flag == 2 || flag == 3)
{
ExportExcel(dt, flag);
}
else if (flag == 4 || flag == 5 || flag == 6 || flag == 7)
{
ExportExcel(dt, flag);
}
}
else
{
MessageBox.Show("没有要导出的数据！");
}
}
//方法
#region 查询注册表,判断本机是否安装office2003,2007和wps
public int ExistsRegedit()
{
int ifused = 0;
RegistryKey rk = Registry.LocalMachine;
RegistryKey akey = rk.OpenSubKey(@"SOFTWARE\Microsoft\Office\11.0\Excel\InstallRoot\");//查询2003
RegistryKey akey07 = rk.OpenSubKey(@"SOFTWARE\Microsoft\Office\12.0\Excel\InstallRoot\");//查询2007
RegistryKey akeytwo = rk.OpenSubKey(@"SOFTWARE\Kingsoft\Office\6.0\common\");//查询wps
//检查本机是否安装Office2003
if (akey != null)
{
string file03 = akey.GetValue("Path").ToString();
if (File.Exists(file03 + "Excel.exe"))
{
ifused += 1;
}
}
//检查本机是否安装Office2007
if (akey07 != null)
{
string file07 = akey07.GetValue("Path").ToString();
if (File.Exists(file07 + "Excel.exe"))
{
ifused += 2;
}
}
//检查本机是否安装wps
if (akeytwo != null)
{
string filewps = akeytwo.GetValue("InstallRoot").ToString();
if (File.Exists(filewps + @"\office6\et.exe"))
{
ifused += 4;
}
}
return ifused;
}
#endregion
#region 导出excel
public void ExportExcel(System.Data.DataTable dt, int flag)
{
//-***************获取excel对象***************
string saveFileName = "";
bool fileSaved = false;
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
if (flag == 2)
{
saveDialog.Filter = "Excel文件.xlsx|*.xlsx";
}
else if (flag == 4 || flag == 5 || flag == 6 || flag == 7)
{
saveDialog.Filter = "wps文件.et|*.et";
}
else
{
saveDialog.Filter = "Excel文件.xls|*.xls";
}
saveDialog.FileName = "导出普查信息表 " + DateTime.Today.ToString("yyyy-MM-dd");
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0) return; //被点了取消
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = xlApp.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range;
// 列索引，行索引，总列数，总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = dgvRecord.ColumnCount;
int RowCount = dt.Rows.Count;
// *****************获取数据*********************
// 创建缓存数据
object[,] objData = new object[RowCount + 1, colCount];
// 获取具体数据
for (RowIndex = 0; RowIndex < RowCount; RowIndex++)
{
for (colIndex = 0; colIndex < colCount; colIndex++)
{
objData[RowIndex, colIndex] = dt.Rows[RowIndex][colIndex];
}
}
//********************* 把数值型转成字符型l*******************
range = worksheet.get_Range(xlApp.Cells[3, 2], xlApp.Cells[RowCount + 2, 2]);
range.NumberFormatLocal = "@";
range = worksheet.get_Range(xlApp.Cells[3, 3], xlApp.Cells[RowCount + 2, 3]);
range.NumberFormatLocal = "@";
range = worksheet.get_Range(xlApp.Cells[3, 7], xlApp.Cells[RowCount + 2, 7]);
range.NumberFormatLocal = "@";
range = worksheet.get_Range(xlApp.Cells[3, 8], xlApp.Cells[RowCount + 2, 8]);
range.NumberFormatLocal = "@";
//********************* 写入Excel*******************
range = worksheet.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 2, colCount - 2]);
range.Value2 = objData;
System.Windows.Forms.Application.DoEvents();
//特殊数字格式
range = worksheet.get_Range(xlApp.Cells[3, colCount - 5], xlApp.Cells[RowCount + 2, colCount - 5]);
range.NumberFormat = "yyyy-MM-dd hh:mm:ss";
//********************* 设置报表表格样式*******************
//设置为横向打印
//xlApp.ActiveSheet.PageSetup.Orientation=2;
//设置报表表格为最适应宽度
worksheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]).Select();
worksheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]).Columns.AutoFit();
//绘制边框
worksheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[RowCount + 2, colCount]).Borders.LineStyle = 1;
//设置左边线加粗
worksheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[RowCount, 1]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
//设置上边线加粗
worksheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
//设置右边线加粗
worksheet.get_Range(xlApp.Cells[1, colCount], xlApp.Cells[RowCount, colCount]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
//设置下边线加粗
worksheet.get_Range(xlApp.Cells[RowCount + 2, 1], xlApp.Cells[RowCount + 2, colCount]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
xlApp.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
range = worksheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]);
//range.Interior.ColorIndex = 1;//背景色
range.Font.Bold = true;
range.RowHeight = 20;
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 1]).ColumnWidth = 25;
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 2]).ColumnWidth = 13;
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 3]).ColumnWidth = 15;
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 4]).ColumnWidth = 30;
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 5]).ColumnWidth = 22;
//合并单元格
Microsoft.Office.Interop.Excel.Range excelRange = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 29]);
excelRange.Merge(excelRange.MergeCells);
//获取列标题
for (int i = 0; i < colCount; i++)
{
worksheet.Cells[2, i + 1] = dgvRecord.Columns[i].HeaderText;
}
//文档标题
range = worksheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, 1]);
range.Font.Bold = true;
range.Font.Size = 20;
worksheet.Cells[1, 1] = "普查信息表";
//***************************保存**********************
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
else
{
fileSaved = false;
}
xlApp.Quit();
GC.Collect();//强行销毁
if (fileSaved && File.Exists(saveFileName))
{
System.Diagnostics.Process.Start(saveFileName);
}
}
#endregion