代码改变世界

【原创】WinForm导出数据到EXCEL（根据微软的Excel插件）

2012-06-04 16:49  杨新华  阅读(2037)  评论(12)  编辑  收藏  举报

导出数据到Excel算是一个比较成熟的技术点了，现在的方法也比较，比如：可以用微软的EXCEL组件，第三方开源组件(NPOI,MyXls等等。)
今天我就首先说一下微软的EXCEL组件

在开发过程中可能出现一系列的问题

（1）Excel行数的限制，office2003的行数为65536行。如果超过这个行数应该再来一个sheet

（2）导出数据慢的问题，如果数据量比较小可能一般的程序逻辑看不出来它的性能问题，当我们导出的数据很多时，应该选择什么方案解决

（3)  导出的格式问题，一些导出到Excel中的格式可能需要计算，那么它就是数字型的

本例先简单说一下实现逻辑，不多说，直接上代码：

（1）添加引用

（2）可以写代码了

第一步：创建一个Excel工作薄

           Interop.Excel.Application xlApp = new Interop.Excel.Application(); ////建立一个Excel.Application的新进程

            Excel.Workbooks workbooks = xlApp.Workbooks; //工作薄的创建

            Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); //工作表sheet的创建

【当然也可以用下面的代码创建工作表sheet】

  　　　　object missing = System.Reflection.Missing.Value;

              Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//添加一个sheet

第二步：插入数据

                        object[,] dataArray = new object[rowCount, colCount];

                        for (int j = 0; j < rowCount; j++)

                        {

                            for (int k = 0; k < colCount; k++)

                            {

                                dataArray[j, k] = drs[j][k].ToString();

                            }

                        }

                        worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowCount, colCount]).Value2 = dataArray;

注：这种方式是把一个二维数组的数据插入到Excel的固定区域，不用一个单元格一个单元格的插入，速度快很多。解决了导出数据慢的问题

可能你保存的时候定义Excel的格式，可以用NumberFormat来定义，例如：

worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowCount + 1, colCount]).NumberFormat = "@";

第三步：保存Excel

 workbook.SaveAs(strFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                workbook.Close(true, Type.Missing, Type.Missing);
                workbook = null;

 

以上是最简洁的方式，不过第一个问题还没有给解决，直接上代码吧。主要是逻辑问题了。没有什么技术难点。

  1 /// <summary>
  2         /// 导出Excel文件
  3         /// </summary>
  4         /// <param name="ds">要导入到Excel的数据</param>
  5         public static string DataSetToExcel2(System.Data.DataSet ds)
  6         {
  7             string strFilePath = System.AppDomain.CurrentDomain.BaseDirectory + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
  8 
  9             Interop.Excel.Application xlApp = new Interop.Excel.Application();
 10             Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
 11             Interop.Excel.Workbook workbook = workbooks.Add(Interop.Excel.XlWBATemplate.xlWBATWorksheet);
 12 
 13             try
 14             {
 15                 if (ds.Tables.Count == 0)
 16                 {
 17                     return "没有数据导出";
 18                 }
 19 
 20                 int MaxRowCount = 60000; //一个sheet的最大行数限制，主要考虑到office2003的行数有限
 21 
 22                 DataTable dt = ds.Tables[0];//数据源
 23 
 24                 int rowCount = dt.Rows.Count;
 25                 int colCount = dt.Columns.Count;
 26 
 27                 if (rowCount > 0 && rowCount <= MaxRowCount)
 28                 {
 29                     object missing = System.Reflection.Missing.Value;
 30                     Interop.Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//添加一个sheet
 31                     //Worksheet worksheet = (Worksheet)workbook.Worksheets[i];
 32                     worksheet.Name = "sheet";
 33 
 34 
 35                     object[,] dataArray = new object[rowCount + 1, colCount];
 36                     //表头导出
 37                     for (int j = 0; j < colCount; j++)
 38                     {
 39                         dataArray[0, j] = dt.Columns[j].ColumnName.ToString();
 40                     }
 41                     //内容导出
 42                     for (int j = 0; j < rowCount; j++)
 43                     {
 44                         for (int k = 0; k < colCount; k++)
 45                         {
 46                             dataArray[j + 1, k] = dt.Rows[j][k].ToString();
 47                         }
 48                     }
 53                     worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowCount + 1, colCount]).NumberFormat = "@";
 54                     worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowCount + 1, colCount]).Value2 = dataArray;
 55                 }
 56                 else //超过sheet表单的就再创适sheet表单
 57                 {
 58                     int sheetCount = 1; //sheet表单个数
 59                     if (rowCount % MaxRowCount == 0)
 60                     {
 61                         sheetCount = rowCount / MaxRowCount;
 62                     }
 63                     else
 64                     {
 65                         sheetCount = rowCount / MaxRowCount + 1;
 66                     }
 67 
 68                     int Flag = 1;
 69                     for (var m = 0; m < sheetCount; m++)
 70                     {
 71                         //添加一个sheet表单
 72                         object missing1 = System.Reflection.Missing.Value;
 73                         Excel.Worksheet worksheet1 = (Excel.Worksheet)workbook.Worksheets.Add(missing1, missing1, missing1, missing1);//添加一个sheet
 74 
 75                         worksheet1.Name = "第" + (m + 1) + "页sheet";
 76                         //如果是最后一个sheet的话 并且最后一个sheet表单数据不等于60000
 77                         if (Flag == sheetCount && (rowCount % MaxRowCount != 0))
 78                         {
 79                             int newrowCount = rowCount - ((Flag - 1) * MaxRowCount); //最后一个sheet的数据
 80                             object[,] dataArray = new object[newrowCount + 1, colCount];
 81 
 82                             int RowIndex = 0;
 83 
 84 
 85                             for (int j = 0; j < colCount; j++)
 86                             {
 87                                 dataArray[0, j] = dt.Columns[j].ColumnName.ToString();
 88                             }
 89 
 90                             int startIndex = (Flag - 1) * MaxRowCount;
 91                             for (int n = startIndex; n < startIndex + newrowCount; n++)
 92                             {
 93                                 for (int t = 0; t < colCount; t++)
 94                                 {
 95                                     dataArray[RowIndex + 1, t] = dt.Rows[n][t].ToString();
 96                                 }
 97 
 98                                    RowIndex++;
103                             }
104                             worksheet1.get_Range(worksheet1.Cells[1, 1], worksheet1.Cells[newrowCount + 1, colCount]).NumberFormat = "@";
105                             worksheet1.get_Range(worksheet1.Cells[1, 1], worksheet1.Cells[newrowCount + 1, colCount]).Value2 = dataArray;
106 
107                         }
108                         else
109                         {
110                             object[,] dataArray = new object[MaxRowCount + 1, colCount];
111 
112                             int RowIndex = 0;
113                             for (int j = 0; j < colCount; j++)
114                             {
115                                 dataArray[0, j] = dt.Columns[j].ColumnName.ToString();
116                             }
117 
118                             int startIndex = (Flag - 1) * MaxRowCount;
119                             for (int n = startIndex; n < startIndex + MaxRowCount; n++)
120                             {
121                                 for (int t = 0; t < colCount; t++)
122                                 {
123                                     dataArray[RowIndex + 1, t] = dt.Rows[n][t].ToString();
124                                 }
125                                 if (n % 100 == 0)
126                                 {
127 
128                                     System.Windows.Forms.Application.DoEvents();
129                                 }
130 
131                                 RowIndex++;
132                             }
133                             worksheet1.get_Range(worksheet1.Cells[1, 1], worksheet1.Cells[MaxRowCount + 1, colCount]).NumberFormat = "@";
134                             worksheet1.get_Range(worksheet1.Cells[1, 1], worksheet1.Cells[MaxRowCount + 1, colCount]).Value2 = dataArray;
135                         }
136                         Flag++;
137                     }
138 
139                 }
140                 workbook.SaveAs(strFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
141                 workbook.Close(true, Type.Missing, Type.Missing);
142                 workbook = null;
143             }
144 
145 
146             catch
147             {
148                 xlApp.Quit();
149                 xlApp = null;
150                 System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
151                 System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
152 
153                 GC.Collect();
154                 return "出现异常";
155             }
156             finally
157             {
158                 System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
159                 System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
160 
161                 GC.Collect();
162             }
163 
164             return "导出成功，导出文件路径：" + strFilePath;
165         }

 

头一次写文章，主要记录自己开发过程的点点滴滴，如果能帮助大家一点最好，哈哈。

 转载的请注原创地址，谢谢。