数据库内容导出到Excel

此案例需要引入NPOI.dll文件和Ionic.Zip.dll文件
  1 using System;
  2 
  3 using System.Collections.Generic;
  4 
  5 using System.Linq;
  6 
  7 using System.Text;
  8 
  9 using System.Data.SqlClient;
 10 
 11 using NPOI.SS.UserModel;
 12 
 13 using NPOI.HSSF.UserModel;
 14 
 15 using System.IO;
 16 
 17  
 18 
 19 namespace 导出数据到Excel
 20 
 21 {
 22 
 23     class Program
 24 
 25     {
 26 
 27         static void Main(string[] args)
 28 
 29         {
 30 
 31    
 32 
 33             bool b = false;
 34 
 35             using (SqlDataReader reader = SqlHelper.ExecuteReader("select * from T_Customers"))
 36 
 37             {
 38 
 39  
 40 
 41                 if (reader.HasRows)
 42 
 43                 {
 44 
 45                     b = true;
 46 
 47 //创建一个工作簿
 48 
 49                     using (Workbook book = new HSSFWorkbook())
 50 
 51                     {
 52 
 53                         using (Sheet sheet = book.CreateSheet("CustomerInfo.xls"))
 54 
 55                         {
 56 
 57     //创建一行，该行信息存在字段名称
 58 
 59                             Row rowHeader = sheet.CreateRow(0);
 60 
 61 //创建单元格，创建的单元格的个数与数据库中字段的个数相同，数据库中的字段的个数可以
 62 
 63   使用reader.FieldCount得到
 64 
 65                             for (int c = 0; c < reader.FieldCount; c++)
 66 
 67                             {
 68 
 69                                 rowHeader.CreateCell(c).SetCellValue(reader.GetName(c));
 70 
 71                             }
 72 
 73  
 74 
 75                             int indexof = 1;
 76 
 77                             while (reader.Read())
 78 
 79                             {
 80 
 81                                 Row row = sheet.CreateRow(indexof);
 82 
 83                                 for (int i = 0; i < reader.FieldCount; i++)
 84 
 85                                 {
 86 
 87                                     Cell cell = row.CreateCell(i);
 88 
 89                                     switch (reader.GetDataTypeName(i))
 90 
 91                                     {
 92 
 93                                         case "int":
 94 
 95                                             if (reader.IsDBNull(i))
 96 
 97                                             {
 98 
 99                                                 cell.SetCellType(CellType.BLANK);
100 
101                                             }
102 
103                                             else
104 
105                                             {
106 
107                                                 cell.SetCellValue(reader.GetInt32(i));
108 
109                                             }
110 
111                                             break;
112 
113                                         case "varchar":
114 
115                                         case "char":
116 
117                                         case "nchar":
118 
119                                         case "nvarchar":
120 
121                                             if (reader.IsDBNull(i))
122 
123                                             {
124 
125                                                 cell.SetCellType(CellType.BLANK);
126 
127                                             }
128 
129                                             else
130 
131                                             {
132 
133                                                 cell.SetCellValue(reader.GetString(i));
134 
135                                             }
136 
137                                             break;
138 
139                                         case "datetime":
140 
141                                             if (reader.IsDBNull(i))
142 
143                                             {
144 
145                                                 cell.SetCellType(CellType.BLANK);
146 
147                                             }
148 
149                                             else
150 
151                                             {
152 
153                                                 cell.SetCellValue(reader.GetDateTime(i).ToString());
154 
155                                             }
156 
157                                             break;
158 
159                                         default: break;
160 
161  
162 
163                                     }
164 
165  
166 
167                                 }
168 
169                                 indexof++;
170 
171                             }
172 
173                             using (FileStream fsWrite = File.OpenWrite("CustomerInfo.xls"))
174 
175                             {
176 
177                                 book.Write(fsWrite);
178 
179                                 Console.WriteLine("数据导入成功");
180 
181                             }
182 
183                         }
184 
185                         if (!b)
186 
187                         {
188 
189                             Console.WriteLine("没数据");
190 
191                         }
192 
193                     }
194 
195                 }
196 
197             }
198 
199         }
200 
201     }
202 
203 }