项目中经常会用到表格的导入导出
今天来简绍一下我所了解的两种方式
1.拼接成表格的简单方式直接导出，服务器上不用安装其他程序 可以直接导出
public async Task<FileResult> AuditLogExport(QueryAppAuditLogExportInput input) {var content = list.Select(item => new { OperatorUserName = item.OperatorUserName, OperatorIp = item.OperatorIp, ManipulatedUserName = item.ManipulatedUserName, //ManipulatedIp = item.ManipulatedIp, SendCode = item.SendCode.IsNullOrEmpty() ? "-" : item.SendCode, //SendIP = item.SendIP.IsNullOrEmpty() ? "-" : item.SendIP, ReceiveCode = item.ReceiveCode.IsNullOrEmpty() ? "-" : item.ReceiveCode, ReceiveIP = item.ReceiveIP.IsNullOrEmpty() ? "-" : item.ReceiveIP, RequestContent = item.RequestContent.IsNullOrEmpty() ? "-" : item.RequestContent, ReponseContent = item.ReponseContent, IsActionResult = item.ActionResult ? "成功" : "失败", LogTypeStr = item.LogTypeStr, LogLevelStr = item.LogLevelStr, ElapsedMilliseconds = item.ElapsedMilliseconds, ExceptionContent = item.ExceptionContent.IsNullOrEmpty() ? "-" : item.ExceptionContent, CreationTime = item.CreationTimeStr, }).ToList(); //命名导出表格的StringBuilder变量 StringBuilder fileContent = new StringBuilder(string.Empty, 3000); fileContent.Append("<table border=\"1\" width=\"100%\">"); //第一行列名 fileContent.Append("<tr height=\"50\" align=\"center\">"); fileContent.Append("<th style='background-color:#22DDDD'>序号</th>"); fileContent.Append("<th style='background-color:#22DDDD'>操作人用户名</th>"); fileContent.Append("<th style='background-color:#22DDDD'>操作人IP</th>"); //fileContent.Append("<th style='background-color:#22DDDD'>被操作人用户名</th>"); fileContent.Append("<th style='background-color:#22DDDD'>发送方编码</th>"); fileContent.Append("<th style='background-color:#22DDDD'>接收方编码</th>"); fileContent.Append("<th style='background-color:#22DDDD'>接收方IP</th>"); fileContent.Append("<th style='background-color:#22DDDD'>请求参数</th>"); fileContent.Append("<th style='background-color:#22DDDD'>响应内容</th>"); fileContent.Append("<th style='background-color:#22DDDD'>处理结果</th>"); fileContent.Append("<th style='background-color:#22DDDD'>日志类型</th>"); fileContent.Append("<th style='background-color:#22DDDD'>日志级别</th>"); fileContent.Append("<th style='background-color:#22DDDD'>业务耗时(ms)</th>"); fileContent.Append("<th style='background-color:#22DDDD'>异常内容)</th>"); fileContent.Append("<th style='background-color:#22DDDD'>日志时间</th>"); fileContent.Append("</tr>"); int i = 0; //内容循环 foreach (var m in content) { i++; fileContent.Append("<tr height=\"50\" align=\"center\">"); fileContent.Append($"<td>{i}</td>"); fileContent.Append($"<td>{m.OperatorUserName}</td>"); fileContent.Append($"<td>{m.OperatorIp}</td>"); //fileContent.Append($"<td>{m.ManipulatedUserName}</td>"); fileContent.Append($"<td>{m.SendCode}</td>"); fileContent.Append($"<td>{m.ReceiveCode}</td>"); fileContent.Append($"<td>{m.ReceiveIP}</td>"); fileContent.Append($"<td>{m.RequestContent}</td>"); fileContent.Append($"<td>{m.ReponseContent}</td>"); fileContent.Append($"<td>{m.IsActionResult}</td>"); fileContent.Append($"<td>{m.LogTypeStr}</td>"); fileContent.Append($"<td>{m.LogLevelStr}</td>"); fileContent.Append($"<td>{m.ElapsedMilliseconds}</td>"); fileContent.Append($"<td>{m.ExceptionContent}</td>"); fileContent.Append($"<td>{m.CreationTime}</td>"); fileContent.Append("</tr>"); } fileContent.Append("</table>"); byte[] fileContents = Encoding.UTF8.GetBytes(fileContent.ToString()); string name = $"xxx-{DateTime.Now:yyyyMMddHHmmssfff}.xlsx"; return File(fileContents, "application/ms-excel", name); }
2.使用 第三方类库的方式 进行导出，服务器上需要安装 Execl 才能支持
using OfficeOpenXml;
public async Task<IActionResult> GetResourceReport() { var list = await _standingBookAppService.GetAllResourceTypes(); var content = list.Select(item => new { Title = item.Title, Code = item.Code, Remark = item.Remark }).ToList(); //定义表头 var heads = new List<string>() { "资源名称", "资源编码", "备注信息" }; var excelFilePath = ExportExcelExtensions.ExportExcel(content, heads, _hostingEnvironment.ContentRootPath, "资源类型报表"); return File( new FileStream(excelFilePath, FileMode.Open), "application/octet-stream", $"资源类型报表 {DateTime.Now:yyyy-MM-dd HHmmss}.xlsx" ); }
public class ExportExcelExtensions { /// <summary> /// 生成Excel /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dataList">数据源</param> /// <param name="headers">表头</param> /// <param name="rootPath">根目录</param> /// <param name="name">文档窗口名称</param> /// <returns></returns> public static string ExportExcel<T>(List<T> dataList, List<string> headers, string rootPath, string name) { var tmp = Path.Combine(rootPath, "wwwroot", "ImportExport"); var sWebRootFolder = StringExtensions.GetRuntimeDirectory(tmp); if (!Directory.Exists(sWebRootFolder)) { Directory.CreateDirectory(sWebRootFolder); } var sFileName = $@"tempExcel_{DateTime.Now:yyyyMMddHHmmss}"; var path = Path.Combine(sWebRootFolder, sFileName); var file = new FileInfo(path); if (file.Exists) { file.Delete(); file = new FileInfo(path); } using var package = new ExcelPackage(file); //创建sheet var worksheet = package.Workbook.Worksheets.Add(name); worksheet.Cells.LoadFromCollection(dataList, true); //表头字段 for (int i = 0; i < headers.Count; i++) { worksheet.Cells[1, i + 1].Value = headers[i]; // // 对字体的设置 worksheet.Cells[1, i + 1].Style.Font.Bold = true; worksheet.Cells[1, i + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; worksheet.Cells[1, i + 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center; worksheet.Cells[1, i + 1].Style.Font.Size = 14; worksheet.Cells[1, i + 1].Style.Font.Color.SetColor(Color.FromArgb(0, 0, 128)); // 单元格背景色的设置 worksheet.Cells[1, i + 1].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[1, i + 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(146, 208, 80)); // 单独设置单元格底部边框样式和颜色（上下左右均可分开设置） worksheet.Cells[1, i + 1].Style.Border.BorderAround(ExcelBorderStyle.Thin); worksheet.Cells[1, i + 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; worksheet.Cells[1, i + 1].Style.Border.Right.Style = ExcelBorderStyle.Thin; // 自当使用文字大小 worksheet.Cells[1, i + 1].AutoFitColumns(); worksheet.Row(i + 1).Height = 25;//设置行高 worksheet.Row(i + 1).CustomHeight = true;//自动调整行高 worksheet.Column(i + 1).Width = 30;//设置列宽 } for (int i = 0; i < dataList.Count; i++) { worksheet.Row(i + 1).Height = 25; // 设置每行高度 } worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中 worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中 // worksheet.Cells.Style.ShrinkToFit = true;//单元格自动适应大小 worksheet.Cells.Style.WrapText = true;//自动换行 package.Save(); return path; } /// <summary> /// 创建excel模板 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="headers"></param> /// <param name="rootPath"></param> /// <param name="name"></param> /// <returns></returns> public static string CreatExcelTemplate(List<string> headers, string rootPath, string name) { var tmp = Path.Combine(rootPath, "wwwroot", "ImportExport"); var sWebRootFolder = StringExtensions.GetRuntimeDirectory(tmp); if (!Directory.Exists(sWebRootFolder)) { Directory.CreateDirectory(sWebRootFolder); } var sFileName = $@"tempExcel_{DateTime.Now:yyyyMMddHHmmss}"; var path = Path.Combine(sWebRootFolder, sFileName); var file = new FileInfo(path); if (file.Exists) { file.Delete(); file = new FileInfo(path); } using var package = new ExcelPackage(file); //创建sheet var worksheet = package.Workbook.Worksheets.Add(name); //表头字段 for (int i = 0; i < headers.Count; i++) { worksheet.Cells[1, i + 1].Value = headers[i]; // // 对字体的设置 worksheet.Cells[1, i + 1].Style.Font.Bold = true; worksheet.Cells[1, i + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; worksheet.Cells[1, i + 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center; worksheet.Cells[1, i + 1].Style.Font.Size = 14; worksheet.Cells[1, i + 1].Style.Font.Color.SetColor(Color.FromArgb(0, 0, 128)); // 单元格背景色的设置 worksheet.Cells[1, i + 1].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[1, i + 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(146, 208, 80)); // 单独设置单元格底部边框样式和颜色（上下左右均可分开设置） worksheet.Cells[1, i + 1].Style.Border.BorderAround(ExcelBorderStyle.Thin); worksheet.Cells[1, i + 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; worksheet.Cells[1, i + 1].Style.Border.Right.Style = ExcelBorderStyle.Thin; // 自当使用文字大小 worksheet.Cells[1, i + 1].AutoFitColumns(); worksheet.Row(i + 1).Height = 25;//设置行高 worksheet.Row(i + 1).CustomHeight = true;//自动调整行高 worksheet.Column(i + 1).Width = 30;//设置列宽 } worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中 worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中 // worksheet.Cells.Style.ShrinkToFit = true;//单元格自动适应大小 worksheet.Cells.Style.WrapText = true;//自动换行 package.Save(); return path; } public static object Export<T, S>(T source, List<S> dataList, ExportSettingsCellection cellection, string rootPath, string title, bool showTitle = true, bool haveBaseInfo = true) { #region 组装部分 var tmp = Path.Combine(rootPath, "wwwroot"); var sWebRootFolder = StringExtensions.GetRuntimeDirectory(tmp); if (!Directory.Exists(sWebRootFolder)) { Directory.CreateDirectory(sWebRootFolder); } var sFileName = $@"tempExcel_{DateTime.Now:yyyyMMddHHmmss}"; var path = Path.Combine(sWebRootFolder, sFileName); var file = new FileInfo(path); if (file.Exists) { file.Delete(); file = new FileInfo(path); } using var package = new ExcelPackage(file); //创建sheet var workSheet = package.Workbook.Worksheets.Add(title); //workSheet.Cells.LoadFromCollection(dtSource, true); #endregion #region 处理逻辑部分 //1.当前行 var curRowIndex = 0; #region 表头 var maxColumnCount = cellection.ColsShowFildNum * 2 + 5;//预计所有列数 if (showTitle == true) { curRowIndex++; workSheet.Cells[curRowIndex, 1, curRowIndex, maxColumnCount].Merge = true; workSheet.Cells[curRowIndex, 1].Value = title; var headerStyle = workSheet.Workbook.Styles.CreateNamedStyle("headerStyle"); headerStyle.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //headerStyle.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous; headerStyle.Style.VerticalAlignment = ExcelVerticalAlignment.Center; headerStyle.Style.Font.Bold = true; headerStyle.Style.Font.Size = 20; workSheet.Cells[curRowIndex, 1].StyleName = "headerStyle"; } #endregion #region 第一基础部分 if (haveBaseInfo) { //需要分列的行数 var fetchCount = (cellection.headers.Count / cellection.ColsShowFildNum) + (cellection.headers.Count % cellection.ColsShowFildNum); //排序 cellection.headers = cellection.headers.OrderBy(x => x.Sort).ToList(); //2. 拼接表头 第一行 var forcount = 1;//循环的次数 var titlecellindex = 1; var fieldcellindex = 1; curRowIndex++; foreach (var column in cellection.headers) { fieldcellindex = titlecellindex + 1; //1.field 2.值 1.field 2.值...依次一行 workSheet.Cells[curRowIndex, titlecellindex].Value = column.Title; Type type = typeof(T); PropertyInfo[] propertyInfos = type.GetProperties(); foreach (var propertyInfo in propertyInfos) { if (column.Field.Equals(propertyInfo.Name)) { object value = propertyInfo.GetValue(source); var pType = propertyInfo.PropertyType; pType = pType.Name == "Nullable`1" ? Nullable.GetUnderlyingType(pType) : pType; if (column.Title.Equals("图片1")) { try { Stream stream = WebRequest.Create(value.ToString()).GetResponse().GetResponseStream(); MemoryStream ms = new MemoryStream(); stream.CopyTo(ms); //System.Drawing.Image img = System.Drawing.Image.FromStream(stream); //img.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg); byte[] buff = new byte[ms.Length]; ms.Position = 0; ms.Read(buff, 0, (int)ms.Length); ms.Close(); // 图片在研究 ExcelShellImage.InsertImage(workSheet, buff, curRowIndex, j, true); } catch (Exception ex) { continue; } } else { if (pType == typeof(DateTime)) { workSheet.Cells[curRowIndex, fieldcellindex].Style.Numberformat.Format = "yyyy-MM-dd hh:mm"; workSheet.Cells[curRowIndex, fieldcellindex].Value = Convert.ToDateTime(value); } else if (pType == typeof(int)) { workSheet.Cells[curRowIndex, fieldcellindex].Style.Numberformat.Format = "#0"; workSheet.Cells[curRowIndex, fieldcellindex].Value = Convert.ToInt32(value); } else if (pType == typeof(double) || pType == typeof(decimal)) { //if (column.Precision != null) workSheet.Cells[curRowIndex, forcount + 1].Style.Numberformat.Format = "#,##0.00";//保留两位小数 workSheet.Cells[curRowIndex, fieldcellindex].Value = Convert.ToDouble(value); } else { workSheet.Cells[curRowIndex, fieldcellindex].Value = value == null ? "" : value.ToString(); } //跨列 if (column.CellSpanNum > 0) { workSheet.Cells[curRowIndex, fieldcellindex, curRowIndex, column.CellSpanNum + fieldcellindex].Merge = true; forcount = forcount + (column.CellSpanNum / 2 + column.CellSpanNum % 2); } workSheet.Cells[curRowIndex, fieldcellindex].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; workSheet.Cells[curRowIndex, fieldcellindex].Style.VerticalAlignment = ExcelVerticalAlignment.Center; fieldcellindex = fieldcellindex + column.CellSpanNum; } break; } } #region 设置样式 // 对字体的设置 workSheet.Cells[curRowIndex, titlecellindex].Style.Font.Bold = true; workSheet.Cells[curRowIndex, titlecellindex].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; workSheet.Cells[curRowIndex, titlecellindex].Style.VerticalAlignment = ExcelVerticalAlignment.Center; workSheet.Cells[curRowIndex, titlecellindex].Style.Font.Size = column.TitleFontSize; workSheet.Cells[curRowIndex, fieldcellindex].Style.Font.Size = column.FieldFontSize; // 单元格背景色的设置 //workSheet.Cells[curRowIndex, fieldcellindex].Style.Fill.PatternType = ExcelFillStyle.Solid; //workSheet.Cells[curRowIndex, fieldcellindex].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(146, 208, 80)); // 单独设置单元格底部边框样式和颜色（上下左右均可分开设置） workSheet.Cells[curRowIndex, titlecellindex].Style.Border.BorderAround(column.TitleExcelBorderStyle); workSheet.Cells[curRowIndex, titlecellindex].Style.Border.Bottom.Style = column.TitleExcelBorderStyle; workSheet.Cells[curRowIndex, titlecellindex].Style.Border.Right.Style = column.TitleExcelBorderStyle; // 自当使用文字大小 workSheet.Cells[curRowIndex, titlecellindex].AutoFitColumns(); workSheet.Row(curRowIndex).Height = column.TitleHeight;//设置行高 workSheet.Row(curRowIndex).CustomHeight = true;//自动调整行高 workSheet.Column(curRowIndex + 1).Width = column.TitleWidth;//设置列宽 #endregion if (forcount % cellection.ColsShowFildNum == 0) { //换行 curRowIndex++; //从第一列开始 titlecellindex = 1; fieldcellindex = 0; } //if ((forcount / cellection.ColsShowFildNum) >= fetchCount) //{ // break; //} titlecellindex = fieldcellindex + 1; forcount++; } } #endregion #region 第二列表部分 if (haveBaseInfo) { //curRowIndex = curRowIndex + 4; } else { //curRowIndex = curRowIndex + 2; } curRowIndex = curRowIndex + 1; if (dataList.Any() && dataList.Count > 0) { //表头字段 for (int i = 0; i < cellection.bodysSettings.Count; i++) { workSheet.Cells[curRowIndex, i + 1].Value = cellection.bodysSettings[i].Title; // 对字体的设置 workSheet.Cells[curRowIndex, i + 1].Style.Font.Bold = true; workSheet.Cells[curRowIndex, i + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; workSheet.Cells[curRowIndex, i + 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center; workSheet.Cells[curRowIndex, i + 1].Style.Font.Size = cellection.bodysSettings[i].TitleFontSize; // 单独设置单元格底部边框样式和颜色（上下左右均可分开设置） workSheet.Cells[curRowIndex, i + 1].Style.Border.BorderAround(ExcelBorderStyle.Thin); workSheet.Cells[curRowIndex, i + 1].Style.Border.Bottom.Style = cellection.bodysSettings[i].TitleExcelBorderStyle; workSheet.Cells[curRowIndex, i + 1].Style.Border.Right.Style = cellection.bodysSettings[i].TitleExcelBorderStyle; // 自当使用文字大小 workSheet.Cells[curRowIndex, i + 1].AutoFitColumns(); workSheet.Row(curRowIndex).Height = cellection.bodysSettings[i].TitleHeight;//设置行高 workSheet.Row(curRowIndex).CustomHeight = true;//自动调整行高 workSheet.Column(i + 1).Width = cellection.bodysSettings[i].TitleWidth;//设置列宽 } curRowIndex++; //数据 foreach (var item in dataList) { var bodyfieldcellindex = 1; foreach (var column in cellection.bodysSettings) { Type type = typeof(S); PropertyInfo[] propertyInfos = type.GetProperties(); foreach (var propertyInfo in propertyInfos) { if (column.Field.Equals(propertyInfo.Name)) { object value = propertyInfo.GetValue(item); var pType = propertyInfo.PropertyType; pType = pType.Name == "Nullable`1" ? Nullable.GetUnderlyingType(pType) : pType; if (column.Title.Equals("图片")) { try { Stream stream = WebRequest.Create(value.ToString()).GetResponse().GetResponseStream(); MemoryStream ms = new MemoryStream(); stream.CopyTo(ms); //System.Drawing.Image img = System.Drawing.Image.FromStream(stream); //img.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg); byte[] buff = new byte[ms.Length]; ms.Position = 0; ms.Read(buff, 0, (int)ms.Length); ms.Close(); // 图片在研究 ExcelShellImage.InsertImage(workSheet, buff, curRowIndex, bodyfieldcellindex, true); //workSheet.Row(curRowIndex).Height = 200; } catch (Exception ex) { continue; } } else { if (pType == typeof(DateTime)) { workSheet.Cells[curRowIndex, bodyfieldcellindex].Style.Numberformat.Format = "yyyy-MM-dd hh:mm"; workSheet.Cells[curRowIndex, bodyfieldcellindex].Value = Convert.ToDateTime(value); } else if (pType == typeof(int)) { workSheet.Cells[curRowIndex, bodyfieldcellindex].Style.Numberformat.Format = "#0"; workSheet.Cells[curRowIndex, bodyfieldcellindex].Value = Convert.ToInt32(value); } else if (pType == typeof(double) || pType == typeof(decimal)) { workSheet.Cells[curRowIndex, bodyfieldcellindex].Value = Convert.ToDouble(value); } else { workSheet.Cells[curRowIndex, bodyfieldcellindex].Value = value == null ? "" : value.ToString(); } workSheet.Cells[curRowIndex, bodyfieldcellindex].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; workSheet.Cells[curRowIndex, bodyfieldcellindex].Style.VerticalAlignment = ExcelVerticalAlignment.Center; workSheet.Row(curRowIndex).Height = 144;//设置行高 workSheet.Row(curRowIndex).CustomHeight = true;//自动调整行高 workSheet.Column(bodyfieldcellindex + 1).Width = 200;//设置列宽 } bodyfieldcellindex++; } } } curRowIndex++; } } #endregion #endregion package.Save(); return path; } } public class ExportSettings { /// <summary> /// 标题 /// </summary> /// public string Title { get; set; } /// <summary> /// 字段 /// </summary> public string Field { get; set; } public int TitleHeight { get; set; } = 30; public int TitleWidth { get; set; } = 30; /// <summary> /// 标题边框颜色 /// </summary> public ExcelBorderStyle TitleExcelBorderStyle { get; set; } = ExcelBorderStyle.Thin; /// <summary> /// 标题字体 /// </summary> public int TitleFontSize { get; set; } = 15; /// <summary> /// 标题字体 /// </summary> public int FieldFontSize { get; set; } = 15; /// <summary> /// 字段展示排序 /// </summary> public int Sort { get; set; } = 2; /// <summary> /// 跨列数值 /// </summary> public int CellSpanNum { get; set; } = 0; } public class ExportSettingsCellection { public List<ExportSettings> headers { get; set; } public List<ExportSettings> bodysSettings { get; set; } /// <summary> /// 横向需显示字段 /// 注明：headers会根据这个配置的数字去划分一行显示几个字段 /// </summary> public int ColsShowFildNum { get; set; } = 3; }
