#nullable enable
|
using OfficeOpenXml;
|
using OfficeOpenXml.Style;
|
using RestSharp;
|
using StackExchange.Profiling.Internal;
|
|
namespace Admin.NET.Core
|
{
|
public static class ExcelUtil
|
{
|
/// <summary>
|
/// 在response中导出标准格式的数据
|
/// </summary>
|
/// <param name="headers"></param>
|
/// <param name="data"></param>
|
/// <returns></returns>
|
public static void ToExcel(List<string> headers, List<List<object>> data, string sheetName, Stream newStream)
|
{
|
using var package = new ExcelPackage(newStream);
|
var worksheet = package.Workbook.Worksheets.Add(sheetName);
|
|
for (int j = 0; j < headers.Count; j++)
|
{
|
worksheet.Cells[1, j + 1].Value = headers[j];
|
worksheet.Cells[1, j + 1].Style.Font.Bold = true;//加粗
|
worksheet.Cells[1, j + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
|
worksheet.Cells[1, j + 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
|
worksheet.Cells[1, j + 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;//边框常规粗细
|
worksheet.Cells[1, j + 1].Style.Border.Left.Style = ExcelBorderStyle.Thin;//边框常规粗细
|
worksheet.Cells[1, j + 1].Style.Border.Right.Style = ExcelBorderStyle.Thin;//边框常规粗细
|
worksheet.Cells[1, j + 1].Style.Border.Top.Style = ExcelBorderStyle.Thin;//边框常规粗细
|
}
|
|
for (int i = 0; i < data.Count; i++)
|
{
|
for (int j = 0; j < data[i].Count; j++)
|
{
|
worksheet.Cells[2 + i, j + 1].Value = data[i][j];
|
}
|
}
|
package.Save();
|
}
|
|
|
|
/// <summary>
|
/// 在response中导出标准格式的多表数据
|
/// </summary>
|
/// <param name="headersList"></param>
|
/// <param name="dataList"></param>
|
/// <param name="sheetNameList"></param>
|
/// <param name="newStream"></param>
|
public static void ToExcel(List<List<string>> headersList, List<List<List<object>>> dataList, List<string> sheetNameList, Stream newStream)
|
{
|
using var package = new ExcelPackage(newStream);
|
for (var k = 0; k < sheetNameList.Count; k++)
|
{
|
var worksheet = package.Workbook.Worksheets.Add(sheetNameList[k]);
|
|
for (int j = 0; j < headersList[k].Count; j++)
|
{
|
worksheet.Cells[1, j + 1].Value = headersList[k][j];
|
worksheet.Cells[1, j + 1].Style.Font.Bold = true;//加粗
|
worksheet.Cells[1, j + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
|
worksheet.Cells[1, j + 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
|
worksheet.Cells[1, j + 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;//边框常规粗细
|
worksheet.Cells[1, j + 1].Style.Border.Left.Style = ExcelBorderStyle.Thin;//边框常规粗细
|
worksheet.Cells[1, j + 1].Style.Border.Right.Style = ExcelBorderStyle.Thin;//边框常规粗细
|
worksheet.Cells[1, j + 1].Style.Border.Top.Style = ExcelBorderStyle.Thin;//边框常规粗细
|
}
|
|
for (int i = 0; i < dataList[k].Count; i++)
|
{
|
for (int j = 0; j < dataList[k][i].Count; j++)
|
{
|
worksheet.Cells[2 + i, j + 1].Value = dataList[k][i][j];
|
}
|
}
|
|
|
}
|
|
package.Save();
|
}
|
|
|
|
|
|
/// <summary>
|
/// 在response中根据模板导出单表数据
|
/// </summary>
|
/// <param name="headers"></param>
|
/// <param name="templatePath"></param>
|
/// <param name="data"></param>
|
/// <param name="sheetName"></param>
|
/// <param name="dataStartLine"></param>
|
/// <param name="newStream"></param>
|
/// <returns></returns>
|
public static void ToExcel(string templatePath, List<string> headers, List<List<object>> data, string sheetName, int headStartLine, int dataStartLine, Stream newStream)
|
{
|
|
var options = new RestClientOptions(App.Configuration["FileUrl"])
|
{
|
ThrowOnAnyError = true,
|
//MaxTimeout = 2000
|
};
|
|
var client = new RestClient(options);
|
var request = new RestRequest($"/file/download", Method.Get);
|
request?.AddParameter(Parameter.CreateParameter("path", templatePath, ParameterType.QueryString))
|
?.AddParameter(Parameter.CreateParameter("fileName", "template.xlsx", ParameterType.QueryString));
|
|
var response = client.DownloadData(request ?? null!);
|
|
MemoryStream ms = new(response ?? null!);
|
|
|
using var package = new ExcelPackage(newStream, ms);
|
var worksheet = package.Workbook.Worksheets[0];
|
|
worksheet.Name = sheetName;
|
|
for (int j = 0; j < headers.Count; j++)
|
{
|
worksheet.Cells[headStartLine, j + 1].Value = headers[j];
|
}
|
|
for (int i = 0; i < data.Count; i++)
|
{
|
for (int j = 0; j < data[i].Count; j++)
|
{
|
worksheet.Cells[dataStartLine + i, j + 1].Value = data[i][j];
|
}
|
}
|
package.Save();
|
|
}
|
|
/// <summary>
|
/// 在response中根据模板导出多表数据
|
/// </summary>
|
/// <param name="templatePath"></param>
|
/// <param name="headersList"></param>
|
/// <param name="dataList"></param>
|
/// <param name="sheetNameList"></param>
|
/// <param name="headStartLineList"></param>
|
/// <param name="dataStartLineList"></param>
|
/// <param name="newStream"></param>
|
public static void ToExcel(string templatePath, List<List<string>> headersList, List<List<List<object>>> dataList, List<string> sheetNameList, List<int> headStartLineList, List<int> dataStartLineList, Stream newStream)
|
{
|
var options = new RestClientOptions(App.Configuration["FileUrl"])
|
{
|
ThrowOnAnyError = true,
|
};
|
|
var client = new RestClient(options);
|
var request = new RestRequest($"/file/download", Method.Get);
|
request?.AddParameter(Parameter.CreateParameter("path", templatePath, ParameterType.QueryString))
|
?.AddParameter(Parameter.CreateParameter("fileName", "template.xlsx", ParameterType.QueryString));
|
|
var response = client.DownloadData(request ?? null!);
|
|
MemoryStream ms = new(response ?? null!);
|
|
using var package = new ExcelPackage(newStream, ms);
|
for (var k = 0; k < sheetNameList.Count; k++)
|
{
|
var worksheet = package.Workbook.Worksheets.FirstOrDefault(x => x.Name == sheetNameList[k]) ?? null!;
|
|
for (int j = 0; j < headersList[k].Count; j++)
|
{
|
worksheet.Cells[headStartLineList[k], j + 1].Value = headersList[k][j];
|
|
}
|
|
for (int i = 0; i < dataList[k].Count; i++)
|
{
|
for (int j = 0; j < dataList[k][i].Count; j++)
|
{
|
worksheet.Cells[dataStartLineList[k] + i, j + 1].Value = dataList[k][i][j];
|
}
|
}
|
}
|
package.Save();
|
}
|
|
|
/// <summary>
|
/// 导入标准格式的数据文件
|
/// </summary>
|
/// <param name="file">上传的文件</param>
|
/// <returns></returns>
|
public static void FromExcel(IFormFile file, int headStartLine, int dataStartLine, out List<string> headers, out List<List<object?>> data, out string sheetName)
|
{
|
if (file == null) throw Oops.Oh(ErrorCodeEnum.D8000);
|
|
//暂时注释，以后再放开 【Editby shaocx,2024-04-15】
|
/*
|
string key = "UploadFile:Excel";
|
var allowContentTypes = App.Configuration.GetSection($"{key}:contentType").Get<IEnumerable<string>>();
|
if (!allowContentTypes.Contains(file.ContentType)) throw Oops.Oh(ErrorCode.D8001);
|
//*/
|
|
sheetName = "";
|
headers = new();
|
data = new();
|
|
using var package = new ExcelPackage(file.OpenReadStream());
|
var worksheet = package.Workbook.Worksheets.Count > 0 ? package.Workbook.Worksheets[0] : null;
|
|
sheetName = package.Workbook.Worksheets.Count > 0 ? package.Workbook.Worksheets[0].Name : "";
|
var cellsCount = worksheet?.Cells?.Count() ?? 0;
|
|
int col = 1;
|
string? header = worksheet?.Cells[headStartLine, col++].Value?.ToString();
|
|
while (!string.IsNullOrWhiteSpace(header))
|
{
|
headers.Add(header);
|
header = worksheet?.Cells[headStartLine, col++]?.Value?.ToString();
|
}
|
|
var headersCount = headers.Count;
|
|
if (headersCount == 0) throw Oops.Oh("表头数量不能为0");
|
|
var rowsCount = (int)Math.Ceiling(((double)cellsCount) / headersCount) - dataStartLine + 1;
|
|
for (var i = 0; i < rowsCount; i++)
|
{
|
List<object?> row = new();
|
for (var j = 0; j < headersCount; j++)
|
{
|
row.Add(worksheet?.Cells[i + dataStartLine, j + 1]?.Value);
|
}
|
data.Add(row);
|
}
|
}
|
|
|
/// <summary>
|
/// 导入多表格式的数据文件
|
/// </summary>
|
/// <param name="file">上传的文件</param>
|
/// <returns></returns>
|
public static void FromExcel(IFormFile file, Dictionary<string, int> nameHeadStartLineDict, Dictionary<string, int> nameDataStartLineDict,
|
out List<List<string>> headersList, out List<List<List<object?>>> dataList, out List<string> sheetNameList)
|
{
|
if (file == null) throw Oops.Oh(ErrorCodeEnum.D8000);
|
string key = "UploadFile:Excel";
|
var allowContentTypes = App.Configuration.GetSection($"{key}:contentType").Get<IEnumerable<string>>();
|
if (!allowContentTypes.Contains(file.ContentType)) throw Oops.Oh(ErrorCodeEnum.D8001);
|
sheetNameList = new();
|
headersList = new();
|
dataList = new();
|
|
using var package = new ExcelPackage(file.OpenReadStream());
|
|
foreach (var worksheet in package.Workbook.Worksheets)
|
{
|
if (!nameHeadStartLineDict.ContainsKey(worksheet?.Name ?? "")) throw Oops.Oh("非法的Excel文件");
|
|
sheetNameList.Add(worksheet?.Name ?? "");
|
var cellsCount = worksheet?.Cells?.Count() ?? 0;
|
var headers = new List<string>();
|
int col = 1;
|
int r = nameHeadStartLineDict[worksheet?.Name ?? ""];
|
string? header = worksheet?.Cells[r, col++].Value?.ToString();
|
while (!string.IsNullOrWhiteSpace(header))
|
{
|
headers.Add(header);
|
header = worksheet?.Cells[r, col++]?.Value?.ToString();
|
}
|
headersList.Add(headers);
|
var headersCount = headers.Count;
|
if (headersCount == 0) throw Oops.Oh("表头数量不能为0");
|
r = nameDataStartLineDict[worksheet?.Name ?? ""];
|
var rowsCount = (int)Math.Ceiling(((double)cellsCount) / headersCount) - r + 1;
|
List<List<object?>> data = new();
|
|
for (var i = 0; i < rowsCount; i++)
|
{
|
List<object?> row = new();
|
for (var j = 0; j < headersCount; j++)
|
{
|
row.Add(worksheet?.Cells[i + r, j + 1]?.Value);
|
}
|
data.Add(row);
|
}
|
dataList.Add(data);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
/// 以文件流IFormFile方式导入到DataTable
|
/// </summary>
|
/// <param name="file"></param>
|
/// <param name="headStartLine"></param>
|
/// <param name="dataStartLine"></param>
|
/// <param name="flag"></param>
|
/// <returns></returns>
|
public static DataTable ImportExcelToDataTable(IFormFile file, int headStartLine, int dataStartLine)
|
{
|
|
if (file == null) throw Oops.Oh(ErrorCodeEnum.D8000);
|
|
//暂时注释，以后再放开 【Editby shaocx,2024-04-15】
|
/*
|
string key = "UploadFile:Excel";
|
var allowContentTypes = App.Configuration.GetSection($"{key}:contentType").Get<IEnumerable<string>>();
|
if (!allowContentTypes.Contains(file.ContentType)) throw Oops.Oh(ErrorCode.D8001);
|
//*/
|
|
List<string> headers = new List<string>();
|
System.Data.DataTable dataTable = null;
|
ExcelPackage package = null;
|
DataColumn column = null;
|
DataRow dataRow = null;
|
try
|
{
|
|
//读取Excel文件
|
using (package = new ExcelPackage(file.OpenReadStream()))
|
{
|
var worksheet = package.Workbook.Worksheets.Count > 0 ? package.Workbook.Worksheets[0] : null;
|
|
dataTable = new System.Data.DataTable();
|
|
var rowsCount = worksheet.Dimension.Rows-2;//总行数
|
//var cellsCount = worksheet?.Cells?.Count() ?? 0;
|
|
|
|
int col = 1;
|
string? header = worksheet?.Cells[headStartLine, col++].Value?.ToString();
|
//构建datatable的列
|
while (!string.IsNullOrWhiteSpace(header))
|
{
|
headers.Add(header);
|
column = new DataColumn(header);
|
dataTable.Columns.Add(column);
|
header = worksheet?.Cells[headStartLine, col++]?.Value?.ToString();
|
}
|
var headersCount = headers.Count;
|
if (headersCount == 0) throw Oops.Oh("表头数量不能为0");
|
//var rowsCount = (int)Math.Ceiling(((double)cellsCount) / headersCount) - dataStartLine + 1;
|
|
//填充行
|
for (var i = 0; i < rowsCount; i++)
|
{
|
dataRow = dataTable.NewRow();
|
|
for (var j = 0; j < headersCount; j++)
|
{
|
var cell = worksheet?.Cells[i + dataStartLine, j + 1];
|
|
if (cell == null)
|
{
|
dataRow[j] = "";
|
}
|
else
|
{
|
#region 类型判断
|
//CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
|
//switch (cell.GetType())
|
//{
|
// case CellType.Blank:
|
// dataRow[j] = "";
|
// break;
|
|
// case CellType.Numeric:
|
// short format = cell.CellStyle.DataFormat;
|
// //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
|
// if (format == 14 || format == 31 || format == 57 || format == 58)
|
// {
|
// if (DateUtil.IsCellDateFormatted(cell))
|
// {
|
// dataRow[j] = cell.DateCellValue.Date.ToString();
|
// }
|
// }
|
// else
|
// {
|
// dataRow[j] = cell.NumericCellValue;
|
// }
|
|
// break;
|
|
// case CellType.String:
|
// dataRow[j] = cell.StringCellValue;
|
// break;
|
//}
|
|
#endregion
|
dataRow[j] = cell?.Value;
|
}
|
}
|
|
//去除空白行数据
|
bool rowdataisnull = true;
|
for (int k = 0; k <= headersCount - 1; ++k)
|
{
|
if (!string.IsNullOrEmpty(dataRow[k] as string))
|
{
|
rowdataisnull = false;
|
}
|
}
|
if (!rowdataisnull)
|
{
|
dataTable.Rows.Add(dataRow);
|
}
|
|
}
|
|
|
};
|
|
|
}
|
catch (Exception ex)
|
{
|
|
throw Oops.Oh("导入异常，请联系管理员");
|
}
|
finally
|
{
|
if (package != null) package.Dispose();
|
|
}
|
return dataTable;
|
|
|
|
}
|
|
|
|
}
|
}
|
