使用NPOI实现excel的导入导出 - itprobie-菜鸟程序员

公告

NPOI2可以对excle2003和excle2007进行导入导出的操作

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Web;
  5 
  6 using System.IO;
  7 using System.Reflection;
  8 using System.Collections;
  9 using System.Data;
 10 using NPOI.SS.UserModel;
 11 
 12 /// <summary>
 13 /// Summary description for OfficeHelper
 14 /// </summary>
 15 public class OfficeHelper
 16 {
 17     DownHelper downHelper = null;
 18     public OfficeHelper()
 19     {
 20         downHelper = new DownHelper();
 21     }
 22 
 23 
 24     /// <summary>
 25     /// 从excle导入到数据集，excle中的工作表对应dataset中的table，工作表名和列名分别对应table中的表名和列名
 26     /// </summary>
 27     /// <param name="path"></param>
 28     /// <returns></returns>
 29     public DataSet ExcelToDataSet(string path)
 30     {
 31         DataSet ds = new DataSet();
 32         IWorkbook wb = WorkbookFactory.Create(path);
 33         for (int sheetIndex = 0; sheetIndex < wb.Count; sheetIndex++)
 34         {
 35             ISheet sheet = wb.GetSheetAt(sheetIndex);
 36             DataTable dt = new DataTable(sheet.SheetName);
 37 
 38             //添加列
 39             int columnCount = sheet.GetRow(0).PhysicalNumberOfCells;
 40             for (int i = 0; i < columnCount; i++)
 41                 dt.Columns.Add(sheet.GetRow(0).GetCell(i).StringCellValue);
 42 
 43             //添加行,从索引为1的行开始
 44             int rowsCount = sheet.PhysicalNumberOfRows;
 45             for (int i = 1; i < rowsCount; i++)
 46             {
 47                 DataRow dr = dt.NewRow();
 48                 for (int j = 0; j < columnCount; j++)
 49                     dr.SetField(j, sheet.GetRow(i).GetCell(j).StringCellValue);
 50                 dt.Rows.Add(dr);
 51             }
 52             ds.Tables.Add(dt);
 53         }
 54         return ds;
 55     }
 56 
 57     /// <summary>
 58     /// 将集合中的数据导入到excle中，不同的集合对应excel中的不同的工作表
 59     /// </summary>
 60     /// <param name="lists">不同对象的集合,集合中的对象可以通过设置特性来关联列名</param>
 61     /// <param name="fileName">保存的文件名，后缀名为.xls或.xlsx</param>
 62     public void ListToExcel(IList[] lists, string fileName)
 63     {
 64         DataSetToExcel(ConvertToDataSet(lists), fileName);
 65     }
 66 
 67 
 68     /// <summary>
 69     /// 将数据集中的数据导入到excel中，多个table对应的导入到excel对应多个工作表
 70     /// </summary>
 71     /// <param name="ds">要导出到excle中的数据集，数据集中表名和字段名在excel中对应工作表名和标题名称</param>
 72     /// <param name="fileName">保存的文件名，后缀名为.xls或.xlsx</param>
 73     public void DataSetToExcel(DataSet ds, string fileName)
 74     {
 75         if (ds != null)
 76         {
 77             IWorkbook wb = CreateSheet(fileName);
 78             foreach (DataTable dt in ds.Tables)
 79             {
 80                 ImportToWorkbook(dt, ref wb);
 81             }
 82 
 83             downHelper.DownloadByOutputStreamBlock(
 84                 new MemoryStream(ToByte(wb)), fileName);
 85         }
 86     }
 87 
 88     /// <summary>
 89     /// 将数据导入到excel中
 90     /// </summary>
 91     /// <param name="dt">要导出到excle中的数据表，表名和字段名在excel中对应工作表名和标题名称</param>
 92     /// <param name="fileName">保存的文件名，后缀名为.xls或.xlsx</param>
 93     public void DataTableToExcel(DataTable dt, string fileName)
 94     {
 95         IWorkbook wb = CreateSheet(fileName);
 96         ImportToWorkbook(dt, ref wb);
 97         downHelper.DownloadByOutputStreamBlock(
 98             new MemoryStream(ToByte(wb)), fileName);
 99     }
100 
101 
102     private DataSet ConvertToDataSet(IList[] lists)
103     {
104         DataSet ds = new DataSet();
105 
106         foreach (IList list in lists)
107         {
108             if (list != null && list.Count > 0)
109             {
110                 string tableName = list[0].GetType().Name;
111                 object[] classInfos = list[0].GetType().
112                     GetCustomAttributes(typeof(EntityMappingAttribute), true);
113 
114                 if (classInfos.Length > 0)
115                     tableName = ((EntityMappingAttribute)classInfos[0]).Name;
116 
117                 DataTable dt = new DataTable(tableName);
118                 object obj = list[0];
119                 PropertyInfo[] propertyInfos = obj.GetType().
120                     GetProperties(BindingFlags.Public | BindingFlags.Instance);
121 
122                 foreach (PropertyInfo propertyInfo in propertyInfos)
123                 {
124                     object[] infos = propertyInfo.
125                         GetCustomAttributes(typeof(EntityMappingAttribute), true);
126                     if (infos.Length > 0)
127                         dt.Columns.Add(((EntityMappingAttribute)infos[0]).Name);
128                     else
129                         dt.Columns.Add(propertyInfo.Name);
130                 }
131 
132                 //添加数据
133                 for (int i = 0; i < list.Count; i++)
134                 {
135                     DataRow dr = dt.NewRow();
136                     object objTemp = list[i];
137                     PropertyInfo[] propertyInfosTemp = objTemp.GetType().
138                         GetProperties(BindingFlags.Public | BindingFlags.Instance);
139                     for (int j = 0; j < propertyInfosTemp.Count(); j++)
140                     {
141                         dr.SetField(j, propertyInfosTemp[j].GetValue(obj, null));
142                     }
143                     dt.Rows.Add(dr);
144                 }
145 
146                 ds.Tables.Add(dt);
147             }
148             else
149             {
150                 ds.Tables.Add(new DataTable(list.GetType().Name));
151             }
152         }
153 
154         return ds;
155     }
156 
157 
158     private void ImportToWorkbook(DataTable dt, ref IWorkbook wb)
159     {
160         string sheetName = dt.TableName ?? "Sheet1";
161         //创建工作表
162         ISheet sheet = wb.CreateSheet(sheetName);
163         //添加标题
164         IRow titleRow = sheet.CreateRow(0);
165         SetRow(titleRow,
166             GetCloumnNames(dt),
167             GetCellStyle(sheet.Workbook, FontBoldWeight.Bold));
168 
169         //添加数据行
170         for (int i = 0; i < dt.Rows.Count; i++)
171         {
172             IRow dataRow = sheet.CreateRow(i + 1);
173             SetRow(
174                 dataRow,
175                 GetRowValues(dt.Rows[i]),
176                 GetCellStyle(sheet.Workbook));
177         }
178 
179         //设置表格自适应宽度
180         AutoSizeColumn(sheet);
181     }
182 
183     private byte[] ToByte(IWorkbook wb)
184     {
185         using (MemoryStream ms = new MemoryStream())
186         {
187             //XSSFWorkbook即读取.xlsx文件返回的MemoryStream是关闭
188             //但是可以ToArray(),这是NPOI的bug
189             wb.Write(ms);
190             return ms.ToArray();
191         }
192     }
193 
194     private IWorkbook CreateSheet(string path)
195     {
196         IWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook(); ;
197         string extension = System.IO.Path.GetExtension(path).ToLower();
198         if (extension == ".xls")
199             wb = new NPOI.HSSF.UserModel.HSSFWorkbook();
200         else if (extension == ".xlsx")
201             wb = new NPOI.XSSF.UserModel.XSSFWorkbook();
202 
203         return wb;
204     }
205 
206     private int GetWidth(DataTable dt, int columnIndex)
207     {
208         IList<int> lengths = new List<int>();
209         foreach (DataRow dr in dt.Rows)
210             lengths.Add(Convert.ToString(dr[columnIndex]).Length * 256);
211         return lengths.Max();
212     }
213 
214     private IList<string> GetRowValues(DataRow dr)
215     {
216         List<string> rowValues = new List<string>();
217 
218         for (int i = 0; i < dr.Table.Columns.Count; i++)
219             rowValues.Add(Convert.ToString(dr[i]));
220 
221         return rowValues;
222     }
223 
224     private IList<string> GetCloumnNames(DataTable dt)
225     {
226         List<string> columnNames = new List<string>();
227 
228         foreach (DataColumn dc in dt.Columns)
229             columnNames.Add(dc.ColumnName);
230 
231         return columnNames;
232     }
233 
234     private void SetRow(IRow row, IList<string> values)
235     {
236         SetRow(row, values, null);
237     }
238 
239     private void SetRow(IRow row, IList<string> values, ICellStyle cellStyle)
240     {
241         for (int i = 0; i < values.Count; i++)
242         {
243             ICell cell = row.CreateCell(i);
244             cell.SetCellValue(values[i]);
245             if (cellStyle != null)
246                 cell.CellStyle = cellStyle;
247         }
248     }
249 
250     private ICellStyle GetCellStyle(IWorkbook wb)
251     {
252         return GetCellStyle(wb, FontBoldWeight.None);
253     }
254 
255     private ICellStyle GetCellStyle(IWorkbook wb, FontBoldWeight boldweight)
256     {
257         ICellStyle cellStyle = wb.CreateCellStyle();
258 
259         //字体样式
260         IFont font = wb.CreateFont();
261         font.FontHeightInPoints = 10;
262         font.FontName = "微软雅黑";
263         font.Color = (short)FontColor.Normal;
264         font.Boldweight = (short)boldweight;
265 
266         cellStyle.SetFont(font);
267 
268         //对齐方式
269         cellStyle.Alignment = HorizontalAlignment.Center;
270         cellStyle.VerticalAlignment = VerticalAlignment.Center;
271 
272         //边框样式
273         cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
274         cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
275         cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
276         cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
277 
278         //设置背景色
279         cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.White.Index;
280         cellStyle.FillPattern = FillPattern.SolidForeground;
281 
282 
283         //是否自动换行
284         cellStyle.WrapText = false;
285 
286         //缩进
287         cellStyle.Indention = 0;
288 
289         return cellStyle;
290     }
291 
292     private void AutoSizeColumn(ISheet sheet)
293     {
294         //获取当前列的宽度，然后对比本列的长度，取最大值
295         for (int columnNum = 0; columnNum <= sheet.PhysicalNumberOfRows; columnNum++)
296             AutoSizeColumn(sheet, columnNum);
297     }
298 
299     private void AutoSizeColumn(ISheet sheet, int columnNum)
300     {
301         int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
302         for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
303         {
304             IRow currentRow = sheet.GetRow(rowNum) == null ?
305                 sheet.CreateRow(rowNum) : sheet.GetRow(rowNum);
306             if (currentRow.GetCell(columnNum) != null)
307             {
308                 ICell currentCell = currentRow.GetCell(columnNum);
309                 int length = System.Text.Encoding.Default.GetBytes(currentCell.ToString()).Length;
310                 if (columnWidth < length)
311                     columnWidth = length;
312             }
313         }
314         sheet.SetColumnWidth(columnNum, columnWidth * 256);
315     }
316 }

文件下载

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Web;
  5 
  6 using System.IO;
  7 
  8 /// <summary>
  9 /// 文件下载有以下四种方式, 大文件下载的处理方法：将文件分块下载。
 10 /// Response.OutputStream.Write
 11 /// Response.TransmitFile
 12 /// Response.WriteFile
 13 /// Response.BinaryWrite
 14 /// </summary>
 15 public class DownHelper
 16 {
 17     HttpResponse Response = null;
 18     public DownHelper()
 19     {
 20         Response = HttpContext.Current.Response;
 21     }
 22 
 23     public void DownloadByOutputStreamBlock(System.IO.Stream stream, string fileName)
 24     {
 25         using (stream)
 26         {
 27             //将流的位置设置到开始位置。
 28             stream.Position = 0;
 29             //块大小
 30             long ChunkSize = 102400;
 31             //建立100k的缓冲区
 32             byte[] buffer = new byte[ChunkSize];
 33             //已读字节数
 34             long dataLengthToRead = stream.Length;
 35 
 36             Response.ContentType = "application/octet-stream";
 37             Response.AddHeader("Content-Disposition",
 38                 string.Format("attachment; filename={0}", HttpUtility.UrlPathEncode(fileName)));
 39 
 40             while (dataLengthToRead > 0 && Response.IsClientConnected)
 41             {
 42                 int lengthRead = stream.Read(buffer, 0, Convert.ToInt32(ChunkSize));//读取的大小
 43                 Response.OutputStream.Write(buffer, 0, lengthRead);
 44                 Response.Flush();
 45                 Response.Clear();
 46                 dataLengthToRead -= lengthRead;
 47             }
 48             Response.Close();
 49         }
 50     }
 51 
 52     public void DownloadByTransmitFile(string filePath, string fielName)
 53     {
 54         FileInfo info = new FileInfo(filePath);
 55         long fileSize = info.Length;
 56         Response.Clear();
 57         Response.ContentType = "application/x-zip-compressed";
 58         Response.AddHeader("Content-Disposition",
 59             string.Format("attachment;filename={0}", HttpUtility.UrlPathEncode(fielName)));
 60         //不指明Content-Length用Flush的话不会显示下载进度  
 61         Response.AddHeader("Content-Length", fileSize.ToString());
 62         Response.TransmitFile(filePath, 0, fileSize);
 63         Response.Flush();
 64         Response.Close();
 65     }
 66 
 67     public void DownloadByWriteFile(string filePath, string fileName)
 68     {
 69         FileInfo info = new FileInfo(filePath);
 70         long fileSize = info.Length;
 71         Response.Clear();
 72         Response.ContentType = "application/octet-stream";
 73         Response.AddHeader("Content-Disposition",
 74             string.Format("attachment;filename={0}", HttpUtility.UrlPathEncode(fileName)));
 75 
 76         //指定文件大小  
 77         Response.AddHeader("Content-Length", fileSize.ToString());
 78         Response.WriteFile(filePath, 0, fileSize);
 79         Response.Flush();
 80         Response.Close();
 81     }
 82 
 83     public void DownloadByOutputStreamBlock(string filePath, string fileName)
 84     {
 85         using (FileStream stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read))
 86         {
 87             //指定块大小  
 88             long chunkSize = 102400;
 89             //建立一个100K的缓冲区  
 90             byte[] buffer = new byte[chunkSize];
 91             //已读的字节数  
 92             long dataToRead = stream.Length;
 93 
 94             //添加Http头  
 95             Response.ContentType = "application/octet-stream";
 96             Response.AddHeader("Content-Disposition",
 97                 string.Format("attachment;filename={0}", HttpUtility.UrlPathEncode(fileName)));
 98             Response.AddHeader("Content-Length", dataToRead.ToString());
 99 
100             while (dataToRead > 0 && Response.IsClientConnected)
101             {
102                 int length = stream.Read(buffer, 0, Convert.ToInt32(chunkSize));
103                 Response.OutputStream.Write(buffer, 0, length);
104                 Response.Flush();
105                 Response.Clear();
106                 dataToRead -= length;
107             }
108             Response.Close();
109         }
110     }
111 
112     public void DownloadByBinary(string filePath, string fileName)
113     {
114         using (FileStream stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read))
115         {
116             //指定块大小  
117             long chunkSize = 102400;
118             //建立一个100K的缓冲区  
119             byte[] bytes = new byte[chunkSize];
120             //已读的字节数  
121             long dataToRead = stream.Length;
122 
123             //添加Http头  
124             Response.ContentType = "application/octet-stream";
125             Response.AddHeader("Content-Disposition",
126                 string.Format("attachment;filename={0}", HttpUtility.UrlPathEncode(fileName)));
127 
128             Response.AddHeader("Content-Length", bytes.Length.ToString());
129             Response.BinaryWrite(bytes);
130             Response.Flush();
131             Response.Close();
132         }
133     }
134 
135     public void DownloadByBinaryBlock(string filePath, string fileName)
136     {
137         using (FileStream stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read))
138         {
139             //指定块大小  
140             long chunkSize = 102400;
141             //建立一个100K的缓冲区  
142             byte[] buffer = new byte[chunkSize];
143             //已读的字节数  
144             long dataToRead = stream.Length;
145 
146             //添加Http头  
147             Response.ContentType = "application/octet-stream";
148             Response.AddHeader("Content-Disposition",
149                 string.Format("attachment;filename={0}", HttpUtility.UrlPathEncode(fileName)));
150             Response.AddHeader("Content-Length", dataToRead.ToString());
151 
152             while (dataToRead > 0 && Response.IsClientConnected)
153             {
154                 int length = stream.Read(buffer, 0, Convert.ToInt32(chunkSize));
155                 Response.BinaryWrite(buffer);
156                 Response.Flush();
157                 Response.Clear();
158 
159                 dataToRead -= length;
160             }
161             Response.Close();
162         }
163     }
164 }

自定义特性

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Web;
 5 
 6 [AttributeUsage(AttributeTargets.Property | AttributeTargets.Class)]
 7 public class EntityMappingAttribute : Attribute
 8 {
 9     public string Name { get; set; }
10 }

posted on 2012-09-18 17:49 itprobie-菜鸟程序员阅读(20045) 评论(0) 编辑收藏举报

刷新页面返回顶部