Npoi导出Excel操作类
来源：互联网 发布：日本转运公司 知乎 编辑：程序博客网 时间：2024/05/22 04:38
using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.IO;using NPOI.SS.UserModel;using NPOI.HSSF.UserModel;using System.Data;using System.ComponentModel;using System.Web.Mvc;namespace Util{ public class ExcelHelper { public static class ExportTool<T> { /// <summary> /// List<T>转化为Excel文件,并返回FileStreamResult /// </summary> /// <param name="list">需要转化的List<T></param> /// <param name="headerList">Excel标题行的List列表</param> /// <param name="fileName">Excel的文件名</param> /// <returns></returns> public static FileStreamResult ExportListToExcel_MVCResult(IList<T> list, IList<String> headerList, String fileName) { FileStreamResult fsr = new FileStreamResult(ExportListToExcel(list, headerList, null), "application/ms-excel"); fsr.FileDownloadName = HttpUtility.UrlEncode(fileName + ".xls"); return fsr; } /// <summary> /// List<T>转化为Excel文件,并返回FileStreamResult /// </summary> /// <param name="list">需要转化的List<T></param> /// <param name="headerList">Excel标题行的List列表</param> /// <param name="fileName">Excel的文件名</param> /// <param name="sortList">指定导出List<T>中哪些属性,并按顺序排序</param> /// <returns></returns> public static FileStreamResult ExportListToExcel_MVCResult(IList<T> list, IList<String> headerList, String fileName, IList<String> sortList) { FileStreamResult fsr = new FileStreamResult(ExportListToExcel(list, headerList, sortList), "application/ms-excel"); fsr.FileDownloadName = HttpUtility.UrlEncode(fileName + ".xls"); return fsr; } public static MemoryStream ExportListToExcel(IList<T> list, IList<String> headerList, IList<String> sortList) { try { //文件流对象 //FileStream file = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite); MemoryStream stream = new MemoryStream(); //打开Excel对象 HSSFWorkbook workbook = new HSSFWorkbook(); //Excel的Sheet对象 NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet("sheet1"); //set date format CellStyle cellStyleDate = workbook.CreateCellStyle(); DataFormat format = workbook.CreateDataFormat(); cellStyleDate.DataFormat = format.GetFormat("yyyy年m月d日"); //使用NPOI操作Excel表 NPOI.SS.UserModel.Row row = sheet.CreateRow(0); int count = 0; PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T)); //if (headerList != null && properties.Count != headerList.Count) // throw new Exception("集合的属性个数和标题行List的个数不一致"); //如果没有自定义的行首,那么采用反射集合的属性名做行首 if (headerList == null) { for (int i = 0; i < properties.Count; i++) //生成sheet第一行列名 { NPOI.SS.UserModel.Cell cell = row.CreateCell(count++); cell.SetCellValue(String.IsNullOrEmpty(properties[i].DisplayName) ? properties[i].Name : properties[i].DisplayName); } } else { for (int i = 0; i < headerList.Count; i++) //生成sheet第一行列名 { NPOI.SS.UserModel.Cell cell = row.CreateCell(count++); cell.SetCellValue(headerList[i]); } } //将数据导入到excel表中 for (int i = 0; i < list.Count; i++) { NPOI.SS.UserModel.Row rows = sheet.CreateRow(i + 1); count = 0; object value = null; //如果自定义导出属性及排序字段为空,那么走反射序号的方式 if (sortList == null) { for (int j = 0; j < properties.Count; j++) { NPOI.SS.UserModel.Cell cell = rows.CreateCell(count++); value = properties[j].GetValue(list[i]); cell.SetCellValue(value == null ? String.Empty : value.ToString()); } } else { for (int j = 0; j < sortList.Count; j++) { NPOI.SS.UserModel.Cell cell = rows.CreateCell(count++); value = properties[sortList[j]].GetValue(list[i]); cell.SetCellValue(value == null ? String.Empty : value.ToString()); } } } //保存excel文档 sheet.ForceFormulaRecalculation = true; workbook.Write(stream); workbook.Dispose(); stream.Seek(0, SeekOrigin.Begin); return stream; } catch { return new MemoryStream(); } } /// <summary> /// 将DataSet数据集转换HSSFworkbook对象，并保存为Stream流 /// </summary> /// <param name="ds"></param> /// <returns>返回数据流Stream对象</returns> public static MemoryStream ExportDatasetToExcel(DataSet ds) { try { //文件流对象 //FileStream file = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite); MemoryStream stream = new MemoryStream(); //打开Excel对象 HSSFWorkbook workbook = new HSSFWorkbook(); //Excel的Sheet对象 NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet("sheet1"); var cellFont = workbook.CreateFont(); var cellStyle = workbook.CreateCellStyle(); //- 加粗，白色前景色 cellFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; //- 这个是填充的模式，可以是网格、花式等。如果需要填充单色，请使用：SOLID_FOREGROUND //cellStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND; //- 设置这个样式的字体，如果没有设置，将与所有单元格拥有共同字体！ cellStyle.SetFont(cellFont); cellStyle.Alignment = HorizontalAlignment.CENTER; //set date format CellStyle cellStyleDate = workbook.CreateCellStyle(); DataFormat format = workbook.CreateDataFormat(); cellStyleDate.DataFormat = format.GetFormat("yyyy年m月d日"); //使用NPOI操作Excel表 NPOI.SS.UserModel.Row row = sheet.CreateRow(0); int count = 0; for (int i = 0; i < ds.Tables[0].Columns.Count; i++) //生成sheet第一行列名 { NPOI.SS.UserModel.Cell cell = row.CreateCell(count++); cell.SetCellValue(ds.Tables[0].Columns[i].Caption); cell.CellStyle = cellStyle; } //将数据导入到excel表中 for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { NPOI.SS.UserModel.Row rows = sheet.CreateRow(i + 1); count = 0; for (int j = 0; j < ds.Tables[0].Columns.Count; j++) { NPOI.SS.UserModel.Cell cell = rows.CreateCell(count++); Type type = ds.Tables[0].Rows[i][j].GetType(); if (type == typeof(int) || type == typeof(Int16) || type == typeof(Int32) || type == typeof(Int64)) { cell.SetCellValue((int)ds.Tables[0].Rows[i][j]); } else { if (type == typeof(float) || type == typeof(double) || type == typeof(Double)) { cell.SetCellValue((Double)ds.Tables[0].Rows[i][j]); } else { if (type == typeof(DateTime)) { cell.SetCellValue(((DateTime)ds.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd HH:mm")); } else { if (type == typeof(bool) || type == typeof(Boolean)) { cell.SetCellValue((bool)ds.Tables[0].Rows[i][j]); } else { cell.SetCellValue(ds.Tables[0].Rows[i][j].ToString()); } } } } } } //保存excel文档 sheet.ForceFormulaRecalculation = true; workbook.Write(stream); workbook.Dispose(); return stream; } catch { return new MemoryStream(); } } } }}
0 0
- Npoi导出Excel操作类
- NPOI操作EXCEL导出
- 利用NPOI操作excel导出
- npoi 操作excel类
- .NET调用NPOI组件导入导出Excel的操作类
- 通过NPOI 导出Excel 操作1
- 使用NPOI操作Excel导入导出数据
- Excel组件NPOI操作类
- NPOI操作EXCEL 将Table中数据导出到Excel
- 使用NPOI 导入导出Excel的类
- NPOI导出Excel
- 利用npoi导出excel
- NPOI导出Excel
- NPOI Excel导入导出
- 使用NPOI导出EXCEL
- NPOI导出excel
- 利用npoi导出excel
- NPOI导出Excel功能
- 经纬财富:六盘水基本面喜忧参半 白银先涨后跌
- R语言绘图篇（二）
- 钩子方法
- Toad 使用快速入门（转贴）
- 电动葫芦运行异常响声如何解决？
- Npoi导出Excel操作类
- desk
- 面向对象部分_206
- web.xml中 Listener的使用
- uva10487
- Apple更新Macbook Air产品线，将于4.30开始销售新Air
- MFC容器类CArray,CList
- 选购电动葫芦应该注以下问题
- java学习笔记
