c#高效率导出多维表头excel的实例代码

摘要：复制代码代码如下:[DllImport("User32.dll",CharSet=CharSet.Auto)]publicstaticext...

复制代码代码如下:

[DllImport("User32.dll", CharSet = CharSet.Auto)]

public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);

private void ExportToExcel(string fielName)

{

//实例化一个Excel.Application对象

Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

try

{

if (dgv_Result.DataSource == null)

return;

if (dgv_Result.Rows.Count == 0)

return;

//新增加一个工作簿，Workbook是直接保存，不会弹出保存对话框，加上Application会弹出保存对话框，值为false会报错

Microsoft.Office.Interop.Excel.Workbook xlBook = excel.Workbooks.Add(true);

//1.添加表头

excel.Cells[1, 1] = tyclass;

for (int i = 0; i < dgv_Result.Columns.Count; i++)

{

excel.Cells[2, i + 1] = dgv_Result.Columns[i].Name;

}

#region 2.实现Excel多维表头采用合并单元格的方式

Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.ActiveSheet;

Microsoft.Office.Interop.Excel.Range excelRange = sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[1, 2]);

Microsoft.Office.Interop.Excel.Range excelRange1 = sheet.get_Range(sheet.Cells[1, 3], sheet.Cells[1, 4]);

Microsoft.Office.Interop.Excel.Range excelRange2 = sheet.get_Range(sheet.Cells[1,5], sheet.Cells[1, 6]);

Microsoft.Office.Interop.Excel.Range excelRange3 = sheet.get_Range(sheet.Cells[1,7], sheet.Cells[1, 8]);

Microsoft.Office.Interop.Excel.Range excelRange4 = sheet.get_Range(sheet.Cells[1, 2], sheet.Cells[1, 3]);

Microsoft.Office.Interop.Excel.Range excelRange5 = sheet.get_Range(sheet.Cells[1, 6], sheet.Cells[1, 7]);

Microsoft.Office.Interop.Excel.Range excelRange6 = sheet.get_Range(sheet.Cells[1, 4], sheet.Cells[1,5]);

excelRange.Merge(excelRange.MergeCells);

excelRange1.Merge(excelRange1.MergeCells);

excelRange4.Merge(excelRange4.MergeCells);

excelRange2.Merge(excelRange2.MergeCells);

excelRange3.Merge(excelRange3.MergeCells);

excelRange5.Merge(excelRange5.MergeCells);

excelRange6.Merge(excelRange6.MergeCells);

Microsoft.Office.Interop.Excel.Range columnRange = sheet.get_Range("A1", "H2"); //得到 Range 范围 A-H 表示1-8列，1-2表示跨几行

columnRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

columnRange.Font.Size = 10;

columnRange.Font.Bold = true;

#endregion

#region 3.添加行数据，直接给Range赋值可提高效率

Microsoft.Office.Interop.Excel.Range range = sheet.get_Range("A3", "H" + (dgv_Result.Rows.Count + 2).ToString()); //得到 Range 范围

string[,] AryData = new string[dgv_Result.Rows.Count-1, dgv_Result.Columns.Count];

for (int i = 0; i < dgv_Result.Rows.Count - 1; i++)

{

for (int j = 0; j < dgv_Result.Columns.Count; j++)

{

AryData[i,j] = dgv_Result.Rows[i].Cells[j].Value.ToString();

}

range.Value2 = AryData;

range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

#endregion

sheet.Cells.Columns.AutoFit();//设置Excel表格的列宽

excel.SheetsInNewWorkbook = 1;//设置Excel单元格对齐方式

excel.DisplayAlerts = false; //设置禁止弹出保存和覆盖的询问提示框

excel.AlertBeforeOverwriting = false;

//保存excel文件

xlBook.SaveAs(fielName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

MessageBox.Show("导出成功！", "提示");

}

catch (Exception ex)

{

MessageBox.Show(ex.Message, "错误提示");

}

finally

{

IntPtr pt = new IntPtr(excel.Hwnd);

int k = 0;

GetWindowThreadProcessId(pt, out k);

System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);