随笔 - 48  文章 - 3  评论 - 57  阅读 - 18万

将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 "数字不在转换范围内";
      }
posted on   Melou  阅读(1026)  评论(0编辑  收藏  举报
编辑推荐:
· 深入理解 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 构建精确任务处理应用
< 2010年11月 >
31 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 1 2 3 4
5 6 7 8 9 10 11

点击右上角即可分享
微信分享提示