NPOI导出Excel表功能实现(多个工作簿)(备用）

Excel生成操作类：

  1 代码 
  2 using System;
  3 using System.Collections.Generic;
  4 using System.Text;
  5 using System.IO;
  6 using NPOI;
  7 using NPOI.HPSF;
  8 using NPOI.HSSF;
  9 using NPOI.HSSF.UserModel;
 10 using System.Data;
 11 
 12 namespace StarTech.NPOI
 13 {
 14     /// <summary>
 15     /// Excel生成操作类
 16     /// </summary>
 17     public class NPOIHelper
 18     {
 19         /// <summary>
 20         /// 导出列名
 21         /// </summary>
 22         public static System.Collections.SortedList ListColumnsName;
 23         /// <summary>
 24         /// 导出Excel
 25         /// </summary>
 26         /// <param name="dgv"></param>
 27         /// <param name="filePath"></param>
 28         public static void ExportExcel(DataTable dtSource, string filePath)
 29         {
 30             if (ListColumnsName == null || ListColumnsName.Count == 0)
 31                 throw (new Exception("请对ListColumnsName设置要导出的列明！"));
 32 
 33             HSSFWorkbook excelWorkbook = CreateExcelFile();
 34             InsertRow(dtSource, excelWorkbook);
 35             SaveExcelFile(excelWorkbook, filePath);
 36         }
 37         /// <summary>
 38         /// 导出Excel
 39         /// </summary>
 40         /// <param name="dgv"></param>
 41         /// <param name="filePath"></param>
 42         public static void ExportExcel(DataTable dtSource, Stream excelStream)
 43         {
 44             if (ListColumnsName == null || ListColumnsName.Count == 0)
 45                 throw (new Exception("请对ListColumnsName设置要导出的列明！"));
 46 
 47             HSSFWorkbook excelWorkbook = CreateExcelFile();
 48             InsertRow(dtSource, excelWorkbook);
 49             SaveExcelFile(excelWorkbook, excelStream);
 50         }
 51         /// <summary>
 52         /// 保存Excel文件
 53         /// </summary>
 54         /// <param name="excelWorkBook"></param>
 55         /// <param name="filePath"></param>
 56         protected static void SaveExcelFile(HSSFWorkbook excelWorkBook, string filePath)
 57         {
 58             FileStream file = null;
 59             try
 60             {
 61                 file = new FileStream(filePath, FileMode.Create);
 62                 excelWorkBook.Write(file);
 63             }
 64             finally
 65             {
 66                 if (file != null)
 67                 {
 68                     file.Close();
 69                 }
 70             }
 71         }
 72         /// <summary>
 73         /// 保存Excel文件
 74         /// </summary>
 75         /// <param name="excelWorkBook"></param>
 76         /// <param name="filePath"></param>
 77         protected static void SaveExcelFile(HSSFWorkbook excelWorkBook, Stream excelStream)
 78         {
 79             try
 80             {
 81                 excelWorkBook.Write(excelStream);
 82             }
 83             finally
 84             {
 85 
 86             }
 87         }
 88         /// <summary>
 89         /// 创建Excel文件
 90         /// </summary>
 91         /// <param name="filePath"></param>
 92         protected static HSSFWorkbook CreateExcelFile()
 93         {
 94             HSSFWorkbook hssfworkbook = new HSSFWorkbook();
 95             return hssfworkbook;
 96         }
 97         /// <summary>
 98         /// 创建excel表头
 99         /// </summary>
100         /// <param name="dgv"></param>
101         /// <param name="excelSheet"></param>
102         protected static void CreateHeader(HSSFSheet excelSheet)
103         {
104             int cellIndex = 0;
105             //循环导出列
106             foreach (System.Collections.DictionaryEntry de in ListColumnsName)
107             {
108                 HSSFRow newRow = excelSheet.CreateRow(0);
109                 HSSFCell newCell = newRow.CreateCell(cellIndex);
110                 newCell.SetCellValue(de.Value.ToString());
111                 cellIndex++;
112             }
113         }
114         /// <summary>
115         /// 插入数据行
116         /// </summary>
117         protected static void InsertRow(DataTable dtSource, HSSFWorkbook excelWorkbook)
118         {
119             int rowCount = 0;
120             int sheetCount = 1;
121             HSSFSheet newsheet = null;
122 
123             //循环数据源导出数据集
124             newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount);
125             CreateHeader(newsheet);
126             foreach (DataRow dr in dtSource.Rows)
127             {
128                 rowCount++;
129                 //超出10000条数据 创建新的工作簿
130                 if (rowCount == 10000)
131                 {
132                     rowCount = 1;
133                     sheetCount++;
134                     newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount);
135                     CreateHeader(newsheet);
136                 }
137 
138                 HSSFRow newRow = newsheet.CreateRow(rowCount);
139                 InsertCell(dtSource, dr, newRow, newsheet, excelWorkbook);
140             }
141         }
142         /// <summary>
143         /// 导出数据行
144         /// </summary>
145         /// <param name="dtSource"></param>
146         /// <param name="drSource"></param>
147         /// <param name="currentExcelRow"></param>
148         /// <param name="excelSheet"></param>
149         /// <param name="excelWorkBook"></param>
150         protected static void InsertCell(DataTable dtSource, DataRow drSource, HSSFRow currentExcelRow, HSSFSheet excelSheet, HSSFWorkbook excelWorkBook)
151         {
152             for (int cellIndex = 0; cellIndex < ListColumnsName.Count; cellIndex++)
153             {
154                 //列名称
155                 string columnsName = ListColumnsName.GetKey(cellIndex).ToString();
156                 HSSFCell newCell = null;
157                 System.Type rowType = drSource[columnsName].GetType();
158                 string drValue = drSource[columnsName].ToString().Trim();
159                 switch (rowType.ToString())
160                 {
161                     case "System.String"://字符串类型
162                         drValue = drValue.Replace("&", "&");
163                         drValue = drValue.Replace(">", ">");
164                         drValue = drValue.Replace("<", "<");
165                         newCell = currentExcelRow.CreateCell(cellIndex);
166                         newCell.SetCellValue(drValue);
167                         break;
168                     case "System.DateTime"://日期类型
169                         DateTime dateV;
170                         DateTime.TryParse(drValue, out dateV);
171                         newCell = currentExcelRow.CreateCell(cellIndex);
172                         newCell.SetCellValue(dateV);
173 
174                         //格式化显示
175                         HSSFCellStyle cellStyle = excelWorkBook.CreateCellStyle();
176                         HSSFDataFormat format = excelWorkBook.CreateDataFormat();
177                         cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
178                         newCell.CellStyle = cellStyle;
179 
180                         break;
181                     case "System.Boolean"://布尔型
182                         bool boolV = false;
183                         bool.TryParse(drValue, out boolV);
184                         newCell = currentExcelRow.CreateCell(cellIndex);
185                         newCell.SetCellValue(boolV);
186                         break;
187                     case "System.Int16"://整型
188                     case "System.Int32":
189                     case "System.Int64":
190                     case "System.Byte":
191                         int intV = 0;
192                         int.TryParse(drValue, out intV);
193                         newCell = currentExcelRow.CreateCell(cellIndex);
194                         newCell.SetCellValue(intV.ToString());
195                         break;
196                     case "System.Decimal"://浮点型
197                     case "System.Double":
198                         double doubV = 0;
199                         double.TryParse(drValue, out doubV);
200                         newCell = currentExcelRow.CreateCell(cellIndex);
201                         newCell.SetCellValue(doubV);
202                         break;
203                     case "System.DBNull"://空值处理
204                         newCell = currentExcelRow.CreateCell(cellIndex);
205                         newCell.SetCellValue("");
206                         break;
207                     default:
208                         throw (new Exception(rowType.ToString() + "：类型数据无法处理!"));
209                 }
210             }
211         }
212     }
213     //排序实现接口 不进行排序 根据添加顺序导出
214     public class NoSort : System.Collections.IComparer
215     {
216         public int Compare(object x, object y)
217         {
218             return -1;
219         }
220     }
221 }
Excel生成类

调用方法： 

 1 代码 
 2  //导出数据列 实现根据添加顺序导出列
 3         StarTech.NPOI.NPOIHelper.ListColumnsName = new SortedList(new StarTech.NPOI.NoSort());
 4         StarTech.NPOI.NPOIHelper.ListColumnsName.Add("MemberName", "姓名");
 5         StarTech.NPOI.NPOIHelper.ListColumnsName.Add("username", "账号");
 6         StarTech.NPOI.NPOIHelper.ListColumnsName.Add("starttime", "登陆时间");
 7         StarTech.NPOI.NPOIHelper.ListColumnsName.Add("lasttime", "在线到期时间");
 8         StarTech.NPOI.NPOIHelper.ListColumnsName.Add("state", "状态");
 9         Response.Clear();
10         Response.BufferOutput = false;
11         Response.ContentEncoding = System.Text.Encoding.UTF8;
12         string filename = HttpUtility.UrlEncode(DateTime.Now.ToString("在线用户yyyyMMdd"));
13         Response.AddHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
14         Response.ContentType = "application/ms-excel";
15         StarTech.NPOI.NPOIHelper.ExportExcel(dtSource, Response.OutputStream);
16         Response.Close();
调用Helper

 

posted @ 2013-11-08 13:27  吻上明天  阅读(2372)  评论(0)  编辑  收藏  举报