代码改变世界

NPOI 帮助类

2018-09-06 10:37  音乐让我说  阅读(551)  评论(0)    收藏  举报

NPOI 帮助类

代码实现了来自于互联网

  1 using System;
  2 using System.Data;
  3 using System.IO;
  4 using System.Text;
  5 using NPOI.HSSF.UserModel;
  6 using NPOI.SS.UserModel;
  7 using NPOI.SS.Util;
  8 
  9 namespace HD.Helper.Common
 10 {
 11     /// <summary>
 12     /// NPOI操作帮助类
 13     /// </summary>
 14     public class NPOIHelper
 15     {       
 16         /// <summary>  
 17         /// DataTable导出到Excel文件  
 18         /// </summary>  
 19         /// <param name="dtSource">源DataTable</param>  
 20         /// <param name="strHeaderText">表头文本</param>  
 21         /// <param name="strFileName">保存位置</param>  
 22         public static void Export(DataTable dtSource, string strHeaderText, string strFileName)
 23         {
 24             using (MemoryStream ms = Export(dtSource, strHeaderText))
 25             {
 26                 using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
 27                 {
 28                     byte[] data = ms.ToArray();
 29                     fs.Write(data, 0, data.Length);
 30                     fs.Flush();
 31                 }
 32             }
 33         }
 34 
 35         /// <summary>  
 36         /// DataTable导出到Excel的MemoryStream  
 37         /// </summary>  
 38         /// <param name="dtSource">源DataTable</param>  
 39         /// <param name="strHeaderText">表头文本</param>  
 40         public static MemoryStream Export(DataTable dtSource, string strHeaderText)
 41         {
 42             HSSFWorkbook workbook = new HSSFWorkbook();
 43             ISheet sheet = workbook.CreateSheet();
 44 
 45             ICellStyle dateStyle = workbook.CreateCellStyle();
 46             IDataFormat format = workbook.CreateDataFormat();
 47             dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
 48 
 49             #region 取得每列的列宽（最大宽度）
 50             int[] arrColWidth = new int[dtSource.Columns.Count];
 51             foreach (DataColumn item in dtSource.Columns)
 52             {
 53                 //GBK对应的code page是CP936
 54                 arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
 55             }
 56             for (int i = 0; i < dtSource.Rows.Count; i++)
 57             {
 58                 for (int j = 0; j < dtSource.Columns.Count; j++)
 59                 {
 60                     int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
 61                     if (intTemp > arrColWidth[j])
 62                     {
 63                         arrColWidth[j] = intTemp;
 64                     }
 65                 }
 66             }
 67             #endregion
 68 
 69             int rowIndex = 0;
 70 
 71             foreach (DataRow row in dtSource.Rows)
 72             {
 73                 #region 新建表，填充表头，填充列头，样式
 74                 if (rowIndex == 65535 || rowIndex == 0)
 75                 {
 76                     if (rowIndex != 0)
 77                     {
 78                         sheet = workbook.CreateSheet();
 79                     }
 80 
 81                     #region 表头及样式
 82                     {
 83                         IRow headerRow = sheet.CreateRow(0);
 84                         headerRow.HeightInPoints = 25;
 85                         headerRow.CreateCell(0).SetCellValue(strHeaderText);
 86 
 87                         ICellStyle headStyle = workbook.CreateCellStyle();
 88                         headStyle.Alignment = HorizontalAlignment.CENTER;
 89                         IFont font = workbook.CreateFont();
 90                         font.FontHeightInPoints = 20;
 91                         font.Boldweight = 700;
 92                         headStyle.SetFont(font);
 93 
 94                         headerRow.GetCell(0).CellStyle = headStyle;
 95 
 96                         sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
 97                     }
 98                     #endregion
 99 
100 
101                     #region 列头及样式
102                     {
103                         IRow headerRow = sheet.CreateRow(1);
104                         ICellStyle headStyle = workbook.CreateCellStyle();
105                         headStyle.Alignment = HorizontalAlignment.CENTER;
106                         IFont font = workbook.CreateFont();
107                         font.FontHeightInPoints = 10;
108                         font.Boldweight = 700;
109                         headStyle.SetFont(font);
110 
111 
112                         foreach (DataColumn column in dtSource.Columns)
113                         {
114                             headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
115                             headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
116 
117                             //设置列宽  
118                             sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
119 
120                         }
121                     }
122                     #endregion
123 
124                     rowIndex = 2;
125                 }
126                 #endregion
127 
128 
129                 #region 填充内容
130                 ICellStyle contentStyle = workbook.CreateCellStyle();
131                 contentStyle.Alignment = HorizontalAlignment.LEFT;
132                 IRow dataRow = sheet.CreateRow(rowIndex);
133                 foreach (DataColumn column in dtSource.Columns)
134                 {
135                     ICell newCell = dataRow.CreateCell(column.Ordinal);
136                     newCell.CellStyle = contentStyle;
137 
138                     string drValue = row[column].ToString();
139 
140                     switch (column.DataType.ToString())
141                     {
142                         case "System.String"://字符串类型  
143                             newCell.SetCellValue(drValue);
144                             break;
145                         case "System.DateTime"://日期类型  
146                             DateTime dateV;
147                             DateTime.TryParse(drValue, out dateV);
148                             newCell.SetCellValue(dateV);
149 
150                             newCell.CellStyle = dateStyle;//格式化显示  
151                             break;
152                         case "System.Boolean"://布尔型  
153                             bool boolV = false;
154                             bool.TryParse(drValue, out boolV);
155                             newCell.SetCellValue(boolV);
156                             break;
157                         case "System.Int16"://整型  
158                         case "System.Int32":
159                         case "System.Int64":
160                         case "System.Byte":
161                             int intV = 0;
162                             int.TryParse(drValue, out intV);
163                             newCell.SetCellValue(intV);
164                             break;
165                         case "System.Decimal"://浮点型  
166                         case "System.Double":
167                             double doubV = 0;
168                             double.TryParse(drValue, out doubV);
169                             newCell.SetCellValue(doubV);
170                             break;
171                         case "System.DBNull"://空值处理  
172                             newCell.SetCellValue("");
173                             break;
174                         default:
175                             newCell.SetCellValue("");
176                             break;
177                     }
178 
179                 }
180                 #endregion
181 
182                 rowIndex++;
183             }
184 
185 
186             using (MemoryStream ms = new MemoryStream())
187             {
188                 workbook.Write(ms);
189                 ms.Flush();
190                 ms.Position = 0;
191 
192                 //sheet.Dispose();
193                 //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet  
194                 return ms;
195             }
196 
197         }
198 
199         /// <summary>读取excel  
200         /// 默认第一行为标头  
201         /// </summary>  
202         /// <param name="strFileName">excel文档路径</param>  
203         /// <returns></returns>  
204         public static DataTable Import(string strFileName)
205         {
206             DataTable dt = new DataTable();
207 
208             HSSFWorkbook hssfworkbook;
209             using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
210             {
211                 hssfworkbook = new HSSFWorkbook(file);
212             }
213             ISheet sheet = hssfworkbook.GetSheetAt(0);
214             System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
215 
216             IRow headerRow = sheet.GetRow(0);
217             int cellCount = headerRow.LastCellNum;
218 
219             for (int j = 0; j < cellCount; j++)
220             {
221                 ICell cell = headerRow.GetCell(j);
222                 dt.Columns.Add(cell.ToString());
223             }
224 
225             for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
226             {
227                 IRow row = sheet.GetRow(i);
228                 DataRow dataRow = dt.NewRow();
229 
230                 for (int j = row.FirstCellNum; j < cellCount; j++)
231                 {
232                     if (row.GetCell(j) != null)
233                         dataRow[j] = row.GetCell(j).ToString();
234                 }
235 
236                 dt.Rows.Add(dataRow);
237             }
238             return dt;
239         }
240 
241     }
242 }

 

额外的示例：https://files.cnblogs.com/files/Music/NPOI_Demo_2018_09_06.rar

额外的示例2：NPOIDemo.rar 

 

谢谢浏览！