使用NPOI把DataSet中的数据导成到Excel 

 1         /// <summary>
 2         /// DataSet数据导出到Excel
 3         /// </summary>
 4         /// <param name="_ds"></param>
 5         /// <param name="_Path">存储路径</param>
 6         /// <returns></returns>
 7         public static bool DataSetToExcelXSSF_New(DataSet _ds, string _Path)
 8         {
 9             try
10             {
11                 #region NPOI 导出方式
12                 XSSFWorkbook hw = new XSSFWorkbook();
13 
14                 #region output every sheet
15 
16                 for (int t = 0; t < _ds.Tables.Count; t++)
17                 {
18                     ISheet sheet2 = (ISheet)hw.CreateSheet(_ds.Tables[t].TableName);
19 
20                     IRow rowCol2 = (IRow)sheet2.CreateRow(0);
21                     for (int j = 0; j < _ds.Tables[t].Columns.Count; j++)
22                     {
23 
24                         ICell cell = (ICell)rowCol2.CreateCell(j);
25 
26                         cell.SetCellValue(_ds.Tables[t].Columns[j].ColumnName);
27                     }
28 
29                     for (int i = 0; i < _ds.Tables[t].Rows.Count; i++)
30                     {
31                         IRow row = (IRow)sheet2.CreateRow(i + 1);
32                         for (int j = 0; j < _ds.Tables[t].Columns.Count; j++)
33                         {
34                             ICell cell = row.CreateCell(j);
35                             //如果某一列是整型数据，在导出的Excel结果文件中那一列的值会转换成数值类型
36                             if (j == 0 || j == 7 || j == 11 || j == 12 || j == 13 || j == 14 || j == 15 || j == 16 || j == 17)
37                             {
38 
39                                 int num = -1;
40                                 bool flag = int.TryParse(_ds.Tables[t].Rows[i][j].ToString(), out num);
41 
42                                 if (flag)
43                                 {
44                                     cell.SetCellValue(num);
45                                 }
46                                 else
47                                 {
48                                     cell.SetCellValue(_ds.Tables[t].Rows[i][j].ToString());
49                                 }
50 
51                             }
52                             else
53                             {
54 
55                                 if (_ds.Tables[t].Rows[i][j].ToString().Contains("<br>"))
56                                 {
57                                     ICellStyle cs = hw.CreateCellStyle();
58 
59                                     cs.WrapText = true;
60                                     cell.CellStyle = cs;
61 
62 
63                                 }
64 
65                                 cell.SetCellValue(_ds.Tables[t].Rows[i][j].ToString().Replace("<br>", "\r\n"));
66                             }
67                         }
68                     }
69                 }
70 
71 
72                 #endregion
73 
74                 FileStream file = new FileStream(_Path, FileMode.Create);
75                 hw.Write(file);
76                 file.Close();
77 
78                 #endregion
79 
80                 //sw.WriteLine("生产的excel 建立完成，再次检查文件是否成功建立");
81                 #region check if the excel file is created
82                 if (!File.Exists(_Path))
83                 {
84                     //sw.WriteLine("生产的excel 未建立成功");
85                     return false;
86                 }
87                 #endregion
88 
89 
90                 return true;
91             }
92             catch (Exception ex)
93             {
94                 throw new Exception(ex.Message, ex);
95             }
96         }
View Code

 

posted on 2015-01-14 10:11  larry_fu  阅读(372)  评论(0)    收藏  举报

导航