NPOI 导出EXCEL 方法拓展
/// /// 导出公用方法/// public static class ExcelExportHelper{#region 实体调用导出/// /// 单实体调用/// /// public static void ExcelExport(this ExcelExportDto exportDto, string excelName, ExcelTableRegionDto excelTableRegionDto = null){List excelExportDtos = new List();excelExportDtos.Add(exportDto);HSSFWorkbook workbook = new HSSFWorkbook();//创建.xls文件List _Dto = null;if (excelTableRegionDto != null){_Dto = new List();_Dto.Add(excelTableRegionDto);}ExcelExportFun(excelExportDtos, excelName, _Dto);}/// /// 多实体集合调用/// /// public static void ExcelExport(this List exportDto, string excelName, List excelTableRegionDto = null){ExcelExportFun(exportDto, excelName, excelTableRegionDto);}#endregion/// /// 单实体调用，直接导出datatable所有数据，******表头中文需要在datatable中写好从DataTable获取Sheet名称******/// 存在有表单名称和表单组织/// /// DataTable数据源/// 导出文件名/// Excel组织名称/// Excel表单名称/// Excel表头合并列public static void ExcelExport(this DataTable exportDto, string excelName, string Company="", string Subject="", ExcelTableRegionDto excelTableRegionDto = null){ DataSet excelExportDtos = new DataSet();excelExportDtos.Tables.Add(exportDto.Copy());List _Dto = null;if (excelTableRegionDto != null){_Dto = new List();_Dto.Add(excelTableRegionDto);}ExcelExportFun(excelExportDtos, excelName, _Dto, Company, Subject);}/// /// 多实体集合调用，直接导出datatable所有数据，******表头中文需要在datatable中写好从DataTable获取Sheet名称******/// 存在有表单名称和表单组织/// /// DataTable数据源/// 导出文件名/// Excel组织名称/// Excel表单名称/// Excel表头合并列public static void ExcelExport(this DataSet exportDto, string excelName, string Company = "", string Subject = "", List excelTableRegionDto = null){ ExcelExportFun(exportDto, excelName, excelTableRegionDto,Company,Subject);}/// /// 单个Sheet页面,只支持合并列不支持合并行******表头中文需要在datatable中写好 从DataTable获取Sheet名称******/// /// 数据源/// 导出EXCEL名称/// 合并列数据集合public static void ExcelExport(this DataTable exportDto, string excelName, ExcelTableRegionDto excelTableRegionDto){DataSet excelExportDtos = new DataSet();excelExportDtos.Tables.Add(exportDto.Copy());List _Dto = null;if (excelTableRegionDto != null) {_Dto = new List();_Dto.Add(excelTableRegionDto);} ExcelExportFun(excelExportDtos, excelName, _Dto);}/// /// 多个Sheet页面,只支持合并列不支持合并行******表头中文需要在datatable中写好 从DataTable获取Sheet名称******/// /// 数据源/// 导出EXCEL名称/// 合并列数据集合public static void ExcelExport(this DataSet exportDto, string excelName, List excelTableRegionDto){ExcelExportFun(exportDto, excelName, excelTableRegionDto);} /// /// 多sheet导出/// /// /// [Obsolete]private static void ExcelExportFun(DataSet excelDtos, string excelName, params string[] sheetName){string[] str = sheetName;HSSFWorkbook workbook = new HSSFWorkbook();//创建.xls文件var a = str.Length;var i = 0;foreach (DataTable item in excelDtos.Tables){if (i == a - 1){i = a - 1;}else {i = 0;}workbook = ExcelExportISheet(workbook, item, str[i], excelDtos.Tables.IndexOf(item));i++;}if (string.IsNullOrWhiteSpace(excelName)){excelName = "新建工作表";}if (excelName.Trim(' ').Contains(".xls"))//存在.xls则删除掉{var c = excelName.Trim(' ').ToString().Split('.');excelName = c[0].ToString();}Response(workbook, excelName);} /// /// 多sheet导出 /// /// 数据源/// Excel文件名称/// 合并行/// Excel增加组织名称/// Excel增加表单名称private static void ExcelExportFun(DataSet excelDtos, string excelName, List excelTableRegionDto = null, string Company = "", string Subject = ""){HSSFWorkbook workbook = new HSSFWorkbook();//创建.xls文件//excel 增加组织名称if (!string.IsNullOrEmpty(Company)) {DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();dsi.Company = Company;workbook.DocumentSummaryInformation = dsi;}//excel 增加表单名称if (!string.IsNullOrEmpty(Subject)){SummaryInformation si = PropertySetFactory.CreateSummaryInformation();si.Subject = Subject;workbook.SummaryInformation = si;}foreach (DataTable item in excelDtos.Tables){workbook = ExcelExportISheet(workbook, item, item.TableName, excelDtos.Tables.IndexOf(item), excelTableRegionDto);}if (string.IsNullOrWhiteSpace(excelName)){excelName = "新建工作表";}if (excelName.Trim(' ').Contains(".xls"))//存在.xls则删除掉{var c = excelName.Trim(' ').ToString().Split('.');excelName = c[0].ToString();}Response(workbook, excelName);} /// /// 多sheet导出/// /// /// private static void ExcelExportFun(List excelDtos, string excelName, List excelTableRegionDto = null){if (string.IsNullOrWhiteSpace(excelName)){excelName = "新建工作表";if (!string.IsNullOrWhiteSpace(excelDtos[0].SheetName)){excelName = excelDtos[0].SheetName;}}HSSFWorkbook workbook = new HSSFWorkbook();//创建.xls文件foreach (var item in excelDtos){workbook = ExcelExportISheet(workbook, item, excelDtos.IndexOf(item), excelTableRegionDto);}if (excelName.Trim(' ').Contains(".xls"))//存在.xls则删除掉{var c = excelName.Trim(' ').ToString().Split('.');excelName = c[0].ToString();}Response(workbook, excelName);}private static void Response(HSSFWorkbook workbook, string excelName){HttpResponse httpResponse = HttpContext.Current.Response;httpResponse.Clear();httpResponse.Buffer = true;httpResponse.Charset = Encoding.UTF8.BodyName;httpResponse.AppendHeader($"Content-Disposition", $"attachment;filename={HttpUtility.UrlEncode(excelName, Encoding.UTF8)}.xls");httpResponse.ContentEncoding = Encoding.UTF8;httpResponse.ContentType = "application/vnd.ms-excel; charset=UTF-8";workbook.Write(httpResponse.OutputStream);httpResponse.End();}/// /// 创建单个sheet/// /// 实例化/// 数据集合/// excel名称/// Sheet页码(多个sheet时调用)/// excelTableRegionDto = null){if (string.IsNullOrWhiteSpace(excelDto.SheetName)) {excelDto.SheetName = $"Sheet{sheetIndex + 1}";}ISheet sheet = workbook.CreateSheet(excelDto.SheetName); //创建sheetIRow row = sheet.CreateRow(0);//创建行对象，填充表头//表头默认居中ICellStyle style = workbook.CreateCellStyle();style.Alignment = HorizontalAlignment.CenterSelection;IFont font = workbook.CreateFont();font.Boldweight = short.MaxValue;style.SetFont(font);ICell cell = null;foreach (var item in excelDto.ExcelFildDtos){cell = row.CreateCell(excelDto.ExcelFildDtos.IndexOf(item));cell.SetCellValue(item.FieldTile);cell.CellStyle = style;}//循环里面不允许创建值类型和引用类型参数会加大内存var i = 0;var a = 0;IRow drow = null;ICell cellFild = null;var cellStyleList = CellStyleList(workbook);//设置内容if (excelDto.Data != null && excelDto.Data.Rows.Count > 0){foreach (DataRow item in excelDto.Data.Rows){i++;drow = sheet.CreateRow(i);//创建行foreach (var items in excelDto.ExcelFildDtos){a = excelDto.ExcelFildDtos.IndexOf(items);cellFild = drow.CreateCell(a);CellStyle(workbook, cellStyleList[CellStyleListIndex(items.FieldType, item[items.FieldName].ToString().Trim())], cellFild, item[items.FieldName].GetType(),items.FieldType, item[items.FieldName].ToString().Trim());}}}//创建合并列if (excelTableRegionDto != null){if (excelTableRegionDto.Any(w => w.TableName == excelDto.SheetName)){var _List = excelTableRegionDto.First(w => w.TableName == excelDto.SheetName).FieldList;sheet.ShiftRows(0, sheet.LastRowNum, 1);IRow _Row = sheet.CreateRow(0);ICell _Cell = null;foreach (ExcelRegionDto item in _List){_Cell = _Row.CreateCell(item.FirstCol);_Cell.SetCellValue(item.FieldName);_Cell.CellStyle = style;sheet.AddMergedRegion(new CellRangeAddress(0, 0, item.FirstCol, item.FastCol));}}}return workbook;}/// /// 创建单个sheet/// /// 实例化/// 数据集合/// excel名称/// Sheet页码(多个sheet时调用)/// excelTableRegionDto = null){if (string.IsNullOrWhiteSpace(SheetName)){SheetName = $"Sheet{sheetIndex + 1}";}ISheet sheet = workbook.CreateSheet(SheetName); //创建sheetIRow row = sheet.CreateRow(0);//创建行对象，填充表头//表头默认居中ICellStyle style = workbook.CreateCellStyle();style.Alignment = HorizontalAlignment.CenterSelection;IFont font = workbook.CreateFont();font.Boldweight = short.MaxValue;style.SetFont(font);ICell cell = null;foreach (DataColumn item in excelDto.Columns){cell = row.CreateCell(excelDto.Columns.IndexOf(item));cell.SetCellValue(item.ColumnName);cell.CellStyle = style;}//循环里面不允许创建值类型和引用类型参数会加大内存var i = 0;var a = 0;IRow drow = null;ICell cellFild = null;var cellStyleList = CellStyleList(workbook);//设置内容if (excelDto != null && excelDto.Rows.Count > 0){foreach (DataRow item in excelDto.Rows){i++;drow = sheet.CreateRow(i);//创建行foreach (DataColumn items in excelDto.Columns){a = excelDto.Columns.IndexOf(items);cellFild = drow.CreateCell(a);//CellStyle(workbook, cellStyleList[CellStyleListIndex(-2, item[items.ColumnName].ToString().Trim())], cellFild, item[items.ColumnName].GetType(),-2, item[items.ColumnName].ToString().Trim());CellStyle(workbook, cellStyleList[CellStyleListIndex_Tow(item[items.ColumnName].GetType())], cellFild, item[items.ColumnName].GetType(), CellStyleListIndex_Tow(item[items.ColumnName].GetType()), item[items.ColumnName].ToString().Trim());}}}//创建合并列if (excelTableRegionDto != null) {if (excelTableRegionDto.Any(w => w.TableName == SheetName)){var _List = excelTableRegionDto.First(w => w.TableName == SheetName).FieldList;sheet.ShiftRows(0, sheet.LastRowNum, 1);IRow _Row = sheet.CreateRow(0);ICell _Cell = null;foreach (ExcelRegionDto item in _List){ _Cell = _Row.CreateCell(item.FirstCol);_Cell.SetCellValue(item.FieldName);_Cell.CellStyle = style;sheet.AddMergedRegion(new CellRangeAddress(0, 0, item.FirstCol, item.FastCol)); }}}return workbook;}private static ICell CellStyle(HSSFWorkbook book, ICellStyle cellStyle, ICell cell, Type type,int filedType, T obj){ var dataFormat = DataFormat(book, filedType, obj);if (filedType != -2){cellStyle.DataFormat = dataFormat;cell.CellStyle=cellStyle;}switch (type.Name.ToLower()){case "string": cellStyle.DataFormat = DataFormat(book, -2, obj);cell.CellStyle=cellStyle; cell.SetCellValue(obj.ToString().Trim());break;case "int16":case "int32":case "int64":case "byte":if (filedType == -2){cellStyle.DataFormat = DataFormat(book, (int)ExcelFildType.Number, obj);cell.CellStyle=cellStyle;} if (int.TryParse(obj.ToString(), out int intV)) {cell.SetCellValue(intV);}else{cell.SetCellValue(obj.ToString());}break;case "boolean":if (bool.TryParse(obj.ToString(), out bool boolV)){cell.SetCellValue(boolV);}else{cell.SetCellValue(obj.ToString());}break;case "datetime"://dataTimeFormatCustom.GetFormat("yyyy-MM-dd HH:mm:ss");if (filedType == -2){cellStyle.DataFormat = DataFormat(book, (int)ExcelFildType.DateTime, obj);cell.CellStyle = cellStyle;} if (DateTime.TryParse(obj.ToString(), out DateTime dateV)){cell.SetCellValue(dateV);}else{cell.SetCellValue(obj.ToString());}break;case "decimal":case "double":if (filedType == -2){cellStyle.DataFormat = DataFormat(book, (int)ExcelFildType.Number, obj);cell.CellStyle = cellStyle;}if (double.TryParse(obj.ToString(), out double doubleV)) {cell.SetCellValue(doubleV);}else{cell.SetCellValue(obj.ToString());} break;case "dbnull":cell.SetCellValue("");break;default:break;}return cell;}private static string IntSplit(object obj){var split = obj.ToString().Split('.');var build = new StringBuilder();if (split.Length > 1 && split[1].Length > 0){build.Append(".");for (int i = 0; i < split[1].Length; i++){build.Append("0");}}return build.ToString();} private static short DataFormat(HSSFWorkbook book, int filedType,object obj){ return FiledTypeExcel(book,filedType, obj);}private static short FiledTypeExcel(HSSFWorkbook book,int type, object obj){ IDataFormat fromat = book.CreateDataFormat();switch (type){case (int)ExcelFildType.Capitalize: return fromat.GetFormat("[DbNum2][$-804]0"); case (int)ExcelFildType.Money: return fromat.GetFormat($"¥#,##0{IntSplit(obj)}");case (int)ExcelFildType.Hundred: return fromat.GetFormat("0.00%");case (int)ExcelFildType.Date: return fromat.GetFormat($"yyyy-MM-dd"); case (int)ExcelFildType.DateTime: return fromat.GetFormat($"yyyy-MM-dd HH:mm:ss");case (int)ExcelFildType.Time: return fromat.GetFormat($"HH:mm:ss");case (int)ExcelFildType.Number: return fromat.GetFormat($"#,##0{IntSplit(obj)};[Red]-#,##0{IntSplit(obj)}"); //千位符，负数默认标红case (int)ExcelFildType.Fraction: return fromat.GetFormat("??/??");case (int)ExcelFildType.method: return fromat.GetFormat("0.00E+00");case (int)ExcelFildType.Dates: return fromat.GetFormat($"yyyy年m月d日");case (int)ExcelFildType.DatesTime: return fromat.GetFormat($"yyyy年m月d日 HH:mm:ss");case (int)ExcelFildType.MoneyKj: return fromat.GetFormat($"$#,##0{IntSplit(obj)}"); //千位符前面$符号}return fromat.GetFormat("General");}private static IList CellStyleList(HSSFWorkbook workbook){ICellStyle General = workbook.CreateCellStyle();//GeneralICellStyle Capitalize = workbook.CreateCellStyle();//CapitalizeICellStyle Money = workbook.CreateCellStyle();//MoneyICellStyle Hundred = workbook.CreateCellStyle();//HundredICellStyle Date = workbook.CreateCellStyle();//DateICellStyle DateTime = workbook.CreateCellStyle();//DateTimeICellStyle Time = workbook.CreateCellStyle();//TimeICellStyle Number = workbook.CreateCellStyle();//NumberICellStyle Fraction = workbook.CreateCellStyle();//FractionICellStyle method = workbook.CreateCellStyle();//methodICellStyle Dates = workbook.CreateCellStyle();//DatesICellStyle DatesTime = workbook.CreateCellStyle();//DatesTimeICellStyle MoneyKj = workbook.CreateCellStyle();//MoneyKj ICellStyle MoneyKjs = workbook.CreateCellStyle();//MoneyKj 金额四位小数ICellStyle Moneys = workbook.CreateCellStyle();//Money 金额四位小数ICellStyle Numbers = workbook.CreateCellStyle();//Number 金额四位小数 IList list = new List();list.Add(General);//0list.Add(Capitalize);//1list.Add(Money);//2list.Add(Hundred);//3list.Add(Date);//4list.Add(DateTime);//5list.Add(Time);//6list.Add(Number);//7list.Add(Fraction);//8list.Add(method);//9list.Add(Dates);//10list.Add(DatesTime);//11list.Add(MoneyKj);//12list.Add(MoneyKjs);//13list.Add(Moneys);//14list.Add(Numbers);//15return list;}private static int CellStyleListIndex_Tow(Type type) {switch (type.Name.ToLower()){case "string":return 0; case "int16":case "int32":case "int64":case "byte":return (int)ExcelFildType.Number; case "datetime":return (int)ExcelFildType.DateTime;case "date":return (int)ExcelFildType.Date;case "time":return (int)ExcelFildType.Time;case "decimal":case "double":return (int)ExcelFildType.Number; default:return 0; }}private static int CellStyleListIndex(int type, object obj){switch (type){case (int)ExcelFildType.Capitalize:return 1;case (int)ExcelFildType.Money:if (SplitIntLengt(obj) > 2) {return 14;}return 2;case (int)ExcelFildType.Hundred:return 3;case (int)ExcelFildType.Date:return 4;case (int)ExcelFildType.DateTime:return 5;case (int)ExcelFildType.Time:return 6;case (int)ExcelFildType.Number:if (SplitIntLengt(obj) > 2){return 15;}return 7;case (int)ExcelFildType.Fraction:return 8;case (int)ExcelFildType.method:return 9;case (int)ExcelFildType.Dates:return 10;case (int)ExcelFildType.DatesTime:return 11;case (int)ExcelFildType.MoneyKj:if (SplitIntLengt(obj) > 2){return 13;}return 12;}return 0;}private static int SplitIntLengt(object obj){var split = obj.ToString().Split('.');var build = new StringBuilder();if (split.Length > 1 && split[1].Length > 0){return split[1].Length;}return 2;}}public class ExcelTableRegionDto{public string TableName { get; set; } = string.Empty;public List FieldList { get; set; } = new List();}public class ExcelRegionDto{/// /// 字段名称/// public string FieldName { get; set; } =string.Empty; /// /// 起始列/// public int FirstCol { get; set; } = 0;/// /// 结束列/// public int FastCol { get; set; } = 0;}public class ExcelExportDto{public List ExcelFildDtos { get; set; } = new List();/// /// Sheet 名称/// public string SheetName { get; set; }/// /// 数据源/// public DataTable Data { get; set; } = null;}public class ExcelFildDto{/// /// EXCEL 标题/表头/// public string FieldTile { get; set; }/// /// Excel 表字段/// public string FieldName { get; set; }/// /// 特殊格式/// public int FieldType { get; set; } = -2;//默认-2 计算本身类型不计算指定类型public enum ExcelFildType{/// /// 中文金额大写/// Capitalize = -1, /// /// 货币类型/// Money =1,/// /// 百分比/// Hundred = 2,/// /// 年月日 短日期/// Date = 3, /// /// 年月日 短日期时分秒/// DateTime = 4,/// /// 时分秒/// Time = 5,/// /// 数值类型/// Number = 6,/// /// 分数/// Fraction = 7,/// /// 科学计算法/// method = 8,/// /// 年月日长日期/// Dates = 9,/// /// 年月日 长日期时分秒/// DatesTime = 10,/// /// 会计专用/// MoneyKj = 11, }} 如有不对，请指出及时更改与学习
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！