项目中经常会用到表格的导入导出
今天来简绍一下我所了解的两种方式
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;
}
标签：5.0,Execl,Style,workSheet,Cells,worksheet,var,Net,curRowIndex From： https://www.cnblogs.com/OneSeting/p/16730195.html