#nullable enable
|
using Furion.FriendlyException;
|
using Furion;
|
using Microsoft.AspNetCore.Http;
|
using OfficeOpenXml.Style;
|
using OfficeOpenXml;
|
using Microsoft.Extensions.Configuration;
|
|
namespace iWare.Wms.Core.Util
|
{
|
public static class ExcelUtil
|
{
|
/// <summary>
|
/// 在文件中导出标准格式的数据
|
/// </summary>
|
/// <param name="headers"></param>
|
/// <param name="data"></param>
|
/// <returns></returns>
|
public static string ToExcel(List<string> headers, List<List<object>> data, string sheetName, string target, string fileName)
|
{
|
if (!target.EndsWith("\\"))
|
{
|
target += "\\";
|
}
|
target += DateTime.Now.Ticks + "\\";
|
if (!Directory.Exists(target))
|
{
|
Directory.CreateDirectory(target);
|
}
|
fileName = fileName + "[" + DateTime.Now.ToString("yyyy-MM-dd") + "]" + ".xlsx";
|
var path = target + fileName;
|
using (var package = new ExcelPackage(new FileInfo(path)))
|
{
|
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();
|
}
|
return path;
|
}
|
|
/// <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>
|
/// 导入标准格式的数据文件
|
/// </summary>
|
/// <param name="file">上传的文件</param>
|
/// <returns></returns>
|
public static void FromExcel(IFormFile file, out List<string> headers, out List<List<object?>> data, out string sheetName)
|
{
|
if (file == null) throw Oops.Oh(ErrorCode.D8000);
|
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[1, col++].Value?.ToString();
|
|
while (!string.IsNullOrEmpty(header))
|
{
|
headers.Add(header);
|
header = worksheet?.Cells[1, col++]?.Value?.ToString();
|
}
|
|
var headersCount = headers.Count;
|
|
if (headersCount == 0) return;
|
|
var rowsCount = (int)Math.Ceiling(((double)cellsCount) / headersCount) - 1;
|
|
for (var i = 0; i < rowsCount; i++)
|
{
|
List<object?> row = new();
|
for (var j = 0; j < headersCount; j++)
|
{
|
row.Add(worksheet?.Cells[i + 2, j + 1]?.Value);
|
}
|
data.Add(row);
|
}
|
}
|
}
|
}
|
}
|
