具体代码参考：
[HttpPost("ExportReportList")] [ProducesResponseType(typeof(ReportGetListResponse), 200)] public async Task<IActionResult> ExportReportList(CreateExportReportBillRequest req) { try { var request = new CreateReportBillRequest { task_id = req.task_id, startTime = req.startTime, endTime = req.endTime }; if (request.requestHashCode == 0) { request.requestHashCode = req.GetHashCode(); } _logger.LogInformation($"【{request.requestHashCode}】导出记录{JsonConvert.SerializeObject(request)}"); var response = await _reportService.CreateReportBill(request, true); if (response.ActionResult == "1") { var _reportModel = response.Data as CreateReportBillData; if (_reportModel == null || !_reportModel.signPersons.Any()) return Json( new WebApiResult(ApiResultCode.Fail, "无法导出清单")); List<CreateReportBillResponse> lstRep = _reportModel.signPersons; var upStream = GetDeriveStream(lstRep); var file = File(upStream.ToArray(), "application/x-zip-compressed", "AAA.xlsx"); upStream.Dispose(); return file; } return null; } catch (Exception ex) { throw ex; } }
private MemoryStream GetDeriveStream(List<CreateReportBillResponse> reportList) { Dictionary<string, string> header = new Dictionary<string, string>(); header.Add("user_name", "姓名"); header.Add("phone_num", "手机号"); header.Add("pay_cost_str", "本次支付金额"); header.Add("hidStr", "隐藏列"); var dt = NpoiHelper.List2DataTable(reportList, header); var stream = NpoiHelper.RenderReportDataTableToXLSXStream(dt, MerchantId); return stream; }
/// <summary> /// List转DataTable /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="list">列表</param> /// <param name="header">列头</param> /// <returns></returns> public static DataTable List2DataTable<T>(List<T> list, IDictionary<string, string> header = null) where T : class { //如果header无效 if (header == null || header.Count == 0) return GetDataTable(list, typeof(T)); DataTable dt = new DataTable(); PropertyInfo[] p = typeof(T).GetProperties(); foreach (PropertyInfo pi in p) { //源数据实体是否包含header列 if (header.ContainsKey(pi.Name)) { // The the type of the property Type columnType = pi.PropertyType; // We need to check whether the property is NULLABLE if (pi.PropertyType.IsGenericType && pi.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>)) { // If it is NULLABLE, then get the underlying type. eg if "Nullable<int>" then this will return just "int" columnType = pi.PropertyType.GetGenericArguments()[0]; } dt.Columns.Add(header[pi.Name], columnType); } } if (list != null) { for (int i = 0; i < list.Count; i++) { IList tempList = new ArrayList(); foreach (PropertyInfo pi in p) { object o = pi.GetValue(list[i], null); if (header == null || header.Count == 0 || //如果header无效 header.ContainsKey(pi.Name)) // 或源数据实体包含header列 { tempList.Add(o); } } object[] itm = new object[header.Count]; for (int j = 0; j < tempList.Count; j++) { itm.SetValue(tempList[j], j); } dt.LoadDataRow(itm, true); } } List<string> dtColumnList = new List<string>(); try { for (int i = 0; i < header.Count; i++) { foreach (KeyValuePair<string, string> item in header) { if (dtColumnList.Where(c => c.Equals(item.Value)).FirstOrDefault() == null) { dt.Columns[item.Value].SetOrdinal(i); dtColumnList.Add(item.Value); break; } } } } catch (Exception ex) { throw; } return dt; }
public static MemoryStream RenderReportDataTableToXLSXStream(DataTable sourceTable, Guid MerchantId, string sheetName = "Sheet1", string dateTimeFormat = "yyyy-MM-dd HH:mm:ss") { XSSFWorkbook workbook = new XSSFWorkbook(); WriteReportToXLSXSheet(workbook, sourceTable, sheetName, dateTimeFormat, MerchantId); ISheet sheet = workbook.GetSheetAt(0); //设置自适应宽度,9为Excel列数，根据需要自已修改 for (int columnNum = 0; columnNum <= 2; columnNum++) { int columnWidth = sheet.GetColumnWidth(columnNum) / 256; //5为开始修改的行数，默认为0行开始 for (int rowNum = 5; rowNum <= sheet.LastRowNum; rowNum++) { IRow currentRow = sheet.GetRow(rowNum); if (currentRow.GetCell(columnNum) != null) { ICell currentCell = currentRow.GetCell(columnNum); int length = Encoding.Default.GetBytes(currentCell.ToString()).Length+1; if (columnWidth < length) { columnWidth = length; } } } sheet.SetColumnWidth(columnNum, columnWidth * 256); } MemoryStream ms = new MemoryStream(); workbook.Write(ms, true); ms.Seek(0, SeekOrigin.Begin); return ms; }
/// <summary> /// 将数据内容写入Office2007版本以后的Excel对应的Sheet中 /// </summary> /// <param name="workBook"></param> /// <param name="sourceTable">将要写入的数据源</param> /// <param name="sheetName">Sheet名称</param> /// <param name="dateTimeFormat">时间格式化方式</param> private static void WriteReportToXLSXSheet(IWorkbook workBook, DataTable sourceTable, string sheetName, string dateTimeFormat,Guid MerchantId) { ISheet sheet = workBook.CreateSheet(sheetName); workBook.GetSheetAt(0).ProtectSheet(MerchantId.ToString()); //解除锁定样式 ICellStyle cellstyleUnLock = workBook.CreateCellStyle(); cellstyleUnLock.IsLocked = false;//解锁 //锁定样式 ICellStyle style1 = workBook.CreateCellStyle(); style1.IsLocked = true;//上锁 //设置字体 IFont font1 = workBook.CreateFont(); //给样式设置字体 style1.SetFont(font1); style1.IsLocked = true; font1.FontHeightInPoints = 12; font1.FontName = "等线"; font1.Color = HSSFColor.Red.Index; IRow des1 = sheet.CreateRow(0); des1.CreateCell(0).SetCellValue("必读！说明："); des1.GetCell(0).CellStyle = style1; IRow des2 = sheet.CreateRow(1); des2.CreateCell(0).SetCellValue("1、A"); des2.GetCell(0).CellStyle = style1; IRow des3 = sheet.CreateRow(2); des3.CreateCell(0).SetCellValue("2、B"); des3.GetCell(0).CellStyle = style1; IRow des4 = sheet.CreateRow(3); des4.CreateCell(0).SetCellValue("3、C"); des4.GetCell(0).CellStyle = style1; IRow des5 = sheet.CreateRow(4); des5.CreateCell(0).SetCellValue("4、D"); des5.GetCell(0).CellStyle = style1; IRow headerRow = sheet.CreateRow(5);//从第6行开始写入标题 sheet.SetColumnHidden(10,true);//隐藏传值 foreach (DataColumn column in sourceTable.Columns) headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); int rowIndex = 6;//从第7行开始写入值 foreach (DataRow row in sourceTable.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); IDataFormat dataformat = workBook.CreateDataFormat(); ICellStyle style = workBook.CreateCellStyle(); style.IsLocked = true; foreach (DataColumn column in sourceTable.Columns) { if (row[column] is DBNull) { dataRow.CreateCell(column.Ordinal).SetCellValue(string.Empty); continue; } if (column.DataType == typeof(int)) { dataRow.CreateCell(column.Ordinal).SetCellValue((int)row[column]); } else if (column.DataType == typeof(float)) { dataRow.CreateCell(column.Ordinal).SetCellValue((float)row[column]); } else if (column.DataType == typeof(double)) { dataRow.CreateCell(column.Ordinal).SetCellValue((double)row[column]); } else if (column.DataType == typeof(Byte)) { dataRow.CreateCell(column.Ordinal).SetCellValue((byte)row[column]); } else if (column.DataType == typeof(UInt16)) { dataRow.CreateCell(column.Ordinal).SetCellValue((UInt16)row[column]); } else if (column.DataType == typeof(UInt32)) { dataRow.CreateCell(column.Ordinal).SetCellValue((UInt32)row[column]); } else if (column.DataType == typeof(UInt64)) { dataRow.CreateCell(column.Ordinal).SetCellValue((UInt64)row[column]); } else if (column.DataType == typeof(DateTime)) { dataRow.CreateCell(column.Ordinal).SetCellValue((DateTime)row[column]); style.DataFormat = dataformat.GetFormat(dateTimeFormat); dataRow.GetCell(column.Ordinal).CellStyle = style; } else { dataRow.CreateCell(column.Ordinal).SetCellValue(Convert.ToString(row[column])); } if (column.ColumnName != "本次支付金额") dataRow.GetCell(column.Ordinal).CellStyle = style; else dataRow.GetCell(column.Ordinal).CellStyle = cellstyleUnLock; } rowIndex++; } }
