将DataSet导入到Excel的多个Sheet中
参考MSDN文章:
如何:使用 COM Interop 创建 Excel 电子表格(C# 编程指南)
如何: 使用 Visual C#.net Excel 的句柄的事件
代码:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 | private bool WriteToExcel( string filePath, DataSet ds) { Excel.Application oXL; Excel._Workbook oWB; Excel._Worksheet oSheet; Excel.Range oRng; object oMissing = System.Reflection.Missing.Value; //Start Excel and get Application object. oXL = new Excel.Application(); oXL.Visible = false ; try { //Get a new workbook. oWB = (Excel._Workbook)(oXL.Workbooks.Open(filePath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing)); oXL.DisplayAlerts = false ; for ( int i = 0; i < ds.Tables.Count; i++) { oSheet = (Excel.Worksheet)oWB.Worksheets.Add(oMissing, oMissing, 1, oMissing); oSheet.Name = ds[i].TableName.Trim(); int rowIndex = 1; int colIndex = 0; for ( int j = 0; j < ds.Tables[i].Columns.Count; j++) { //Add table headers going cell by cell. oSheet.Cells[1, j + 1] = ds.Tables[i].Columns[j].ColumnName.Trim(); colIndex++; } string maxHeaderCell = NumberToChar(colIndex) + "1" ; //Format A1:D1 as bold, vertical alignment = center. oSheet.get_Range( "A1" , maxHeaderCell).Font.Bold = true ; oSheet.get_Range( "A1" , maxHeaderCell).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; // Create an array to multiple values at once. string [,] saNames = new string [ds.Tables[i].Rows.Count, ds.Tables[i].Columns.Count]; for ( int n = 0; n < ds.Tables[i].Rows.Count; n++) { for ( int m = 0; m < ds.Tables[i].Columns.Count; m++) { saNames[n, m] = ds.Tables[i].Rows[n][ds.Tables[i].Columns[m].ColumnName.Trim()].ToString(); } rowIndex++; } oSheet.get_Range( "A2" , NumberToChar(colIndex) + rowIndex.ToString()).Value2 = saNames; oRng = oSheet.get_Range( "A1" , maxHeaderCell); oRng.EntireColumn.AutoFit(); } // 保存并关闭这个工作簿 oWB.Close(Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing); System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB); //Make sure Excel is visible and give the user control //of Microsoft Excel's lifetime. //oXL.Visible = true; //oXL.UserControl = true; return true ; } catch (Exception ex) { MessageBox.Show(ex.Message); return false ; } finally { oXL.Quit(); //釋放Excel資源 System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL); oWB = null ; oSheet = null ; oRng = null ; oXL = null ; GC.Collect(); } } private string NumberToChar( int number) { if (1 <= number && 26 >= number) { int num = number + 64; System.Text.ASCIIEncoding asciiEncoding = new System.Text.ASCIIEncoding(); byte [] btNumber = new byte [] { ( byte )num }; return asciiEncoding.GetString(btNumber); } else if (number > 26) { int NewNum = number % 26; int count = number / 26; string ss = NumberToChar(count); int num = NewNum + 64; System.Text.ASCIIEncoding asciiEncoding = new System.Text.ASCIIEncoding(); byte [] btNumber = new byte [] { ( byte )num }; return ss + asciiEncoding.GetString(btNumber); } return "数字不在转换范围内" ; } |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 深入理解 Mybatis 分库分表执行原理
· 如何打造一个高并发系统?
· .NET Core GC压缩(compact_phase)底层原理浅谈
· 现代计算机视觉入门之:什么是图片特征编码
· .NET 9 new features-C#13新的锁类型和语义
· Sdcb Chats 技术博客:数据库 ID 选型的曲折之路 - 从 Guid 到自增 ID,再到
· 语音处理 开源项目 EchoSharp
· 《HelloGitHub》第 106 期
· Spring AI + Ollama 实现 deepseek-r1 的API服务和调用
· 使用 Dify + LLM 构建精确任务处理应用