c# 高效率导出多维表头excel
2023-09-14 09:08:37 时间
[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); p.Kill(); } }
相关文章
- C#.NET将数组导入Excel源代码
- C#数据结构与算法揭秘二
- C#.NET常见问题(FAQ)-如何批量增加或取消注释
- C#.NET 无法直接启动带有类库输出类型的项目怎么办
- 编写高质量代码改善C#程序的157个建议[用抛异常替代返回错误、不要在不恰当的场合下引发异常、重新引发异常时使用inner Exception]
- C# 十进制与十六进制互转
- C#.net word excel powerpoint (ppt) 转换成 pdf 文件
- 给 C# 开发者的代码审查清单
- C# 操作Excel之旁门左道 [ C# | Excel ]
- c# 解决死锁问题Monitor
- C# 读取Excel中的数据
- C# 获取Excel工作薄中Sheet页(工作表)名集合
- C#和Excel进行报表开发
- C#对Excel打印时,PageSetup 对象详解
- C#读取Excel技术概览 (2)
- C# 读取Excel中的数据
- C# Excel数据验重及Table数据验重
- Atitit.excel导出 功能解决方案 php java C#.net版总集合.doc
- Atitit.http代理的实现 代码java php c# python
- atitit.跨语言实现备份mysql数据库 为sql文件特性 api 兼容性java c#.net php js
- c# - 编辑excel的依赖
- (六十)c#Winform自定义控件-鼓风机(工业)-HZHControls
- c# 将字符串转换为指定类型的值
- C#里使用ExcelDataReader读取EXCEL文件的简单方法
- (73)C#里怎么样调用WIN API的函数时传送结构或指针对象
- [C#] 解决使用ClosedXML生成Excel报表丢失样式的两种方法