644 lines
24 KiB
C#
644 lines
24 KiB
C#
using System;
|
||
using System.Collections.Generic;
|
||
using System.IO;
|
||
using System.Linq;
|
||
using System.Reflection;
|
||
using System.Text;
|
||
using System.Text.RegularExpressions;
|
||
using System.Threading.Tasks;
|
||
using System.Web;
|
||
using AngleSharp;
|
||
using AngleSharp.Dom;
|
||
using Hncore.Infrastructure.Data;
|
||
using Hncore.Infrastructure.Extension;
|
||
using Microsoft.AspNetCore.Mvc;
|
||
using NPOI.HSSF.UserModel;
|
||
|
||
namespace Hncore.Infrastructure.Common
|
||
{
|
||
public static class ExcelHelper
|
||
{
|
||
public static async Task<Stream> GetStreamFromHtml(string html)
|
||
{
|
||
MemoryStream ms = new MemoryStream();
|
||
|
||
HSSFWorkbook book = new HSSFWorkbook();
|
||
|
||
var context = BrowsingContext.New(Configuration.Default);
|
||
|
||
var document = await context.OpenAsync(req => req.Content(html));
|
||
|
||
try
|
||
{
|
||
var tables = document.GetElementsByTagName("table");
|
||
|
||
foreach (var table in tables)
|
||
{
|
||
var sheetName = table.GetAttribute("sheetname");
|
||
|
||
var sheet = book.CreateSheet(sheetName);
|
||
|
||
var trs = table.GetElementsByTagName("tr");
|
||
|
||
var rowIndex = 0;
|
||
|
||
foreach (var tr in trs)
|
||
{
|
||
var row = sheet.CreateRow(rowIndex);
|
||
|
||
var tds = tr.GetElementsByTagName("td");
|
||
|
||
var columnIndex = 0;
|
||
|
||
foreach (var td in tds)
|
||
{
|
||
row.CreateCell(columnIndex).SetCellValue(td.InnerHtml.Trim());
|
||
|
||
columnIndex++;
|
||
}
|
||
|
||
rowIndex++;
|
||
|
||
}
|
||
}
|
||
|
||
book.Write(ms);
|
||
ms.Position = 0;
|
||
}
|
||
finally
|
||
{
|
||
document.Close();
|
||
document.Dispose();
|
||
|
||
context.Active.Close();
|
||
context.Active.Dispose();
|
||
|
||
document = null;
|
||
context = null;
|
||
|
||
book.Clear();
|
||
book.Close();
|
||
book = null;
|
||
}
|
||
|
||
|
||
return ms;
|
||
}
|
||
|
||
public static async Task ResponseExcelFromHtml(this ControllerBase controllerBase, string fileName, string html)
|
||
{
|
||
using (var ms = await GetStreamFromHtml(html))
|
||
{
|
||
var bytes = ms.StreamToBytes();
|
||
|
||
var response = controllerBase.HttpContext.Response;
|
||
|
||
response.ContentType = "application/octet-stream";
|
||
response.Headers.Add("Content-Disposition", $"attachment; filename={fileName.UrlEncode()}");
|
||
response.Headers.Add("X-Suggested-Filename", fileName.UrlEncode());
|
||
response.Headers.Add("Content-Length", bytes.Length.ToString());
|
||
|
||
await response.Body.WriteAsync(bytes, 0, bytes.Length);
|
||
await response.Body.FlushAsync();
|
||
}
|
||
}
|
||
/// <summary>
|
||
/// excel二进制文件转二维string结合
|
||
/// </summary>
|
||
/// <param name="stream"></param>
|
||
/// <param name="st"></param>
|
||
/// <returns></returns>
|
||
public static List<List<string>> ReadFromStream(Stream stream, int st = 0)
|
||
{
|
||
//根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档
|
||
HSSFWorkbook workbook;
|
||
try
|
||
{
|
||
workbook = new HSSFWorkbook(stream);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
LogHelper.Error("ReadFromStream",ex);
|
||
throw new BusinessException("文件读取错误！");
|
||
}
|
||
List<List<string>> lis1 = new List<List<string>>();
|
||
{
|
||
//获取excel的第一个sheet
|
||
var sheet = workbook.GetSheetAt(st);
|
||
if (sheet == null)
|
||
throw new BusinessException("该文件内没有包含任何工作簿");
|
||
//获取sheet的首行
|
||
var headerRow = sheet.GetRow(0);
|
||
//一行最后一个方格的编号 即总的列数
|
||
int cellCount = headerRow.LastCellNum;
|
||
|
||
try
|
||
{
|
||
for (int i = 0; i <= sheet.LastRowNum; i++)
|
||
{
|
||
List<string> lis = new List<string>();
|
||
var row = sheet.GetRow(i);
|
||
if (row == null)
|
||
continue;
|
||
for (int j = 0; j < cellCount; j++)
|
||
{
|
||
var cell = row.GetCell(j);
|
||
if (cell == null)
|
||
{
|
||
lis.Add("");
|
||
continue;
|
||
}
|
||
try
|
||
{
|
||
switch (cell.CellType)
|
||
{
|
||
case NPOI.SS.UserModel.CellType.Unknown:
|
||
lis.Add("Unknown");
|
||
break;
|
||
case NPOI.SS.UserModel.CellType.Numeric:
|
||
if (HSSFDateUtil.IsCellDateFormatted(cell))//对日期格式进行特殊对待
|
||
lis.Add(HSSFDateUtil.GetJavaDate(cell.NumericCellValue).ToString());
|
||
else
|
||
lis.Add(cell.NumericCellValue.ToString());
|
||
break;
|
||
case NPOI.SS.UserModel.CellType.String:
|
||
lis.Add(cell.StringCellValue.ToString());
|
||
break;
|
||
case NPOI.SS.UserModel.CellType.Formula:
|
||
lis.Add(cell.CellFormula.ToString());
|
||
break;
|
||
case NPOI.SS.UserModel.CellType.Blank:
|
||
lis.Add("");
|
||
break;
|
||
case NPOI.SS.UserModel.CellType.Boolean:
|
||
lis.Add(cell.BooleanCellValue.ToString());
|
||
break;
|
||
case NPOI.SS.UserModel.CellType.Error:
|
||
lis.Add(cell.ErrorCellValue.ToString());
|
||
break;
|
||
default:
|
||
break;
|
||
}
|
||
}
|
||
catch
|
||
{
|
||
lis.Add("");
|
||
}
|
||
}
|
||
|
||
//如果本行所有单元格都是空，就跳过本行
|
||
if (lis.All(item => string.IsNullOrEmpty(item)))
|
||
continue;
|
||
|
||
lis1.Add(lis);
|
||
}
|
||
}
|
||
catch(Exception ex)
|
||
{
|
||
LogHelper.Error("ReadFromStream", ex);
|
||
throw new BusinessException("文件格式错误！");
|
||
}
|
||
}
|
||
#region 细节化处理
|
||
lis1 = lis1.Where(s => !s.TrueForAll(f => string.IsNullOrWhiteSpace(f))).ToList();//去除全是空格的空行。
|
||
lis1 = lis1.Select(s => s = s.Select(y => y = y.Trim()).ToList()).ToList();//去除空格
|
||
if (lis1.Count == 1) throw new Exception("Excel中无有效数据！");
|
||
#endregion
|
||
return lis1;
|
||
}
|
||
/// <summary>
|
||
/// 导出列表到excel
|
||
/// 导出到sheet的数据一致
|
||
/// </summary>
|
||
/// <typeparam name="T"></typeparam>
|
||
/// <param name="excelData">每一个sheet的数据</param>
|
||
/// <param name="excelTitle">列表的属性和名称值</param>
|
||
/// <returns></returns>
|
||
public static byte[] ExportListToExcel<T>(List<ExcelData<T>> excelData, List<ExcelTitle> excelTitle)
|
||
{
|
||
var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
|
||
|
||
var entityType = typeof(T);
|
||
PropertyInfo[] entityProperties = entityType.GetProperties();
|
||
|
||
if (excelData == null || excelData.Count == 0)
|
||
{
|
||
return null;
|
||
}
|
||
try
|
||
{
|
||
foreach (var item in excelData)
|
||
{
|
||
#region MyRegion
|
||
//var sheet = workbook.CreateSheet(item.SheetName.Replace('/','-'));
|
||
//var titleRow = sheet.CreateRow(0);
|
||
//for (int i = 0; i < excelTitle.Count; i++)
|
||
//{
|
||
// titleRow.CreateCell(i).SetCellValue(excelTitle[i].Title);
|
||
//}
|
||
//var sheetData = item.Data;
|
||
//for (int j = 0; j < sheetData.Count; j++)
|
||
//{
|
||
// var dataRow = sheet.CreateRow(j + 1);
|
||
// for (int i = 0; i < excelTitle.Count; i++)
|
||
// {
|
||
// if (excelTitle[i].Property.ToUpper().Equals("ID"))
|
||
// {
|
||
// var num = j + 1;
|
||
// dataRow.CreateCell(i).SetCellValue(num);
|
||
// continue;
|
||
// }
|
||
// var entityProperty = entityProperties.FirstOrDefault(m => m.Name == excelTitle[i].Property);
|
||
|
||
// var cellVal = entityProperty?.GetValue(sheetData[j]);
|
||
// if (cellVal?.GetType().Name == "DateTime")
|
||
// {
|
||
// dataRow.CreateCell(i).SetCellValue(((DateTime?)cellVal)?.ToString("yyyy/MM/dd HH:mm:ss"));
|
||
// }
|
||
// else
|
||
// {
|
||
// dataRow.CreateCell(i).SetCellValue(cellVal?.ToString());
|
||
// }
|
||
// //dataRow.CreateCell(i).SetCellValue(cellVal?.ToString());
|
||
// }
|
||
//}
|
||
#endregion
|
||
CreateSheetData(workbook, item, excelTitle, entityProperties);
|
||
}
|
||
using (var ms = new MemoryStream())
|
||
{
|
||
workbook.Write(ms);
|
||
var bytes = ms.ToArray();
|
||
return bytes;
|
||
}
|
||
}
|
||
catch(Exception ex)
|
||
{
|
||
LogHelper.Error("ExportListToExcel=>" + ex);
|
||
|
||
throw ex;
|
||
}
|
||
finally
|
||
{
|
||
workbook.Clear();
|
||
workbook.Close();
|
||
workbook = null;
|
||
}
|
||
}
|
||
public static byte[] ExportListToExcel<T>(ExcelData<T> excelData, List<ExcelTitle> excelTitle)
|
||
{
|
||
var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
|
||
|
||
var entityType = typeof(T);
|
||
PropertyInfo[] entityProperties = entityType.GetProperties();
|
||
|
||
try
|
||
{
|
||
#region MyRegion
|
||
//var sheet = workbook.CreateSheet(excelData.SheetName.Replace('/', '-'));
|
||
//var titleRow = sheet.CreateRow(0);
|
||
//for (int i = 0; i < excelTitle.Count; i++)
|
||
//{
|
||
// titleRow.CreateCell(i).SetCellValue(excelTitle[i].Title);
|
||
//}
|
||
//var sheetData = excelData.Data;
|
||
//for (int j = 0; j < sheetData.Count; j++)
|
||
//{
|
||
// var dataRow = sheet.CreateRow(j + 1);
|
||
// for (int i = 0; i < excelTitle.Count; i++)
|
||
// {
|
||
// var cellVal = entityProperties.FirstOrDefault(m => m.Name == excelTitle[i].Property)?.GetValue(sheetData[j]);
|
||
|
||
// if (cellVal?.GetType().Name == "DateTime")
|
||
// {
|
||
// dataRow.CreateCell(i).SetCellValue(((DateTime?)cellVal)?.ToString("yyyy/MM/dd HH:mm:ss"));
|
||
// }
|
||
// else
|
||
// {
|
||
// dataRow.CreateCell(i).SetCellValue(cellVal?.ToString());
|
||
// }
|
||
// }
|
||
//}
|
||
#endregion
|
||
CreateSheetData(workbook, excelData, excelTitle, entityProperties);
|
||
using (var ms = new MemoryStream())
|
||
{
|
||
workbook.Write(ms);
|
||
var bytes = ms.ToArray();
|
||
return bytes;
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
LogHelper.Error("ExportListToExcel=>" + ex);
|
||
|
||
throw ex;
|
||
}
|
||
finally
|
||
{
|
||
workbook.Clear();
|
||
workbook.Close();
|
||
workbook = null;
|
||
}
|
||
}
|
||
/// <summary>
|
||
/// 创建excel表单数据
|
||
/// </summary>
|
||
/// <typeparam name="T"></typeparam>
|
||
/// <param name="workbook"></param>
|
||
/// <param name="excelData"></param>
|
||
/// <param name="excelTitle"></param>
|
||
/// <param name="entityProperties"></param>
|
||
private static void CreateSheetData<T>(NPOI.XSSF.UserModel.XSSFWorkbook workbook,
|
||
ExcelData<T> excelData, List<ExcelTitle> excelTitle,PropertyInfo[] entityProperties)
|
||
{
|
||
var sheet = workbook.CreateSheet(excelData.SheetName.Replace('/', '-'));
|
||
var titleRow = sheet.CreateRow(0);
|
||
for (int i = 0; i < excelTitle.Count; i++)
|
||
{
|
||
titleRow.CreateCell(i).SetCellValue(excelTitle[i].Title);
|
||
}
|
||
var sheetData = excelData.Data;
|
||
for (int j = 0; j < sheetData.Count; j++)
|
||
{
|
||
var dataRow = sheet.CreateRow(j + 1);
|
||
for (int i = 0; i < excelTitle.Count; i++)
|
||
{
|
||
var currentTitle = excelTitle[i];
|
||
if (currentTitle.Property.Equals("序号"))
|
||
{
|
||
var num = j + 1;
|
||
dataRow.CreateCell(i).SetCellValue(num);
|
||
continue;
|
||
}
|
||
var cellVal = entityProperties.FirstOrDefault(m => m.Name == currentTitle.Property)?.GetValue(sheetData[j]);
|
||
if (currentTitle.Format != null)
|
||
{
|
||
cellVal = currentTitle.Format(cellVal);
|
||
}
|
||
if (currentTitle.Expr != null)
|
||
{
|
||
cellVal = currentTitle.Expr(sheetData[j]);
|
||
}
|
||
else if (cellVal?.GetType().Name == "DateTime")
|
||
{
|
||
dataRow.CreateCell(i).SetCellValue(((DateTime?)cellVal)?.ToString("yyyy/MM/dd HH:mm:ss"));
|
||
}
|
||
dataRow.CreateCell(i).SetCellValue(cellVal?.ToString());
|
||
}
|
||
}
|
||
}
|
||
#region DownloadAsync(下载)
|
||
|
||
|
||
/// <summary>
|
||
/// 下载
|
||
/// </summary>
|
||
/// <param name="stream">流</param>
|
||
/// <param name="fileName">文件名,包含扩展名</param>
|
||
public static async Task DownloadAsync(this ControllerBase controllerBase, Stream stream, string fileName)
|
||
{
|
||
await DownloadAsync(controllerBase,stream, fileName, Encoding.UTF8);
|
||
}
|
||
/// <summary>
|
||
/// 下载
|
||
/// </summary>
|
||
/// <param name="stream">流</param>
|
||
/// <param name="fileName">文件名,包含扩展名</param>
|
||
/// <param name="encoding">字符编码</param>
|
||
public static async Task DownloadAsync(this ControllerBase controllerBase, Stream stream, string fileName, Encoding encoding)
|
||
{
|
||
stream.Seek(0, SeekOrigin.Begin);
|
||
var buffer = new byte[stream.Length];
|
||
stream.Read(buffer, 0, buffer.Length);
|
||
|
||
await DownloadAsync(controllerBase, buffer, fileName, encoding);
|
||
}
|
||
/// <summary>
|
||
/// 下载
|
||
/// </summary>
|
||
/// <param name="bytes">字节流</param>
|
||
/// <param name="fileName">文件名,包含扩展名</param>
|
||
public static async Task DownloadAsync(this ControllerBase controllerBase, byte[] bytes, string fileName)
|
||
{
|
||
await DownloadAsync(controllerBase,bytes, fileName, Encoding.UTF8);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 下载
|
||
/// </summary>
|
||
/// <param name="bytes">字节流</param>
|
||
/// <param name="fileName">文件名,包含扩展名</param>
|
||
/// <param name="encoding">字符编码</param>
|
||
public static async Task DownloadAsync(this ControllerBase controllerBase,byte[] bytes, string fileName, Encoding encoding)
|
||
{
|
||
var response = controllerBase.HttpContext.Response;
|
||
if (bytes == null || bytes.Length == 0)
|
||
return;
|
||
fileName = fileName.Replace(" ", "");
|
||
fileName = HttpUtility.UrlEncode(fileName, encoding);
|
||
response.ContentType = "application/octet-stream";
|
||
response.Headers.Add("Content-Disposition", $"attachment; filename={fileName}");
|
||
response.Headers.Add("Content-Length", bytes.Length.ToString());
|
||
response.Headers.Add("X-Suggested-Filename", fileName);
|
||
await response.Body.WriteAsync(bytes, 0, bytes.Length);
|
||
await response.Body.FlushAsync();
|
||
}
|
||
|
||
#endregion
|
||
}
|
||
|
||
public class ExcelTitle
|
||
{
|
||
/// <summary>
|
||
/// 导出数据对应的 属性字段名
|
||
/// </summary>
|
||
public string Property { get; set; }
|
||
/// <summary>
|
||
/// excel 的title
|
||
/// </summary>
|
||
public string Title { get; set; }
|
||
|
||
public Func<object, object> Format { get; set; }
|
||
public Func<object, object> Expr { get; set; }
|
||
}
|
||
public class ExcelData<T>
|
||
{
|
||
/// <summary>
|
||
/// 导出数据对应的 属性字段名
|
||
/// </summary>
|
||
public string SheetName { get; set; }
|
||
/// <summary>
|
||
/// excel 的title
|
||
/// </summary>
|
||
public List<T> Data { get; set; }
|
||
}
|
||
#region Excel导入验证
|
||
/// <summary>
|
||
/// Excel导入验证
|
||
/// </summary>
|
||
public static class ValidForExcel
|
||
{
|
||
|
||
/// <summary>
|
||
/// 解析字符串到int32、double、datetime...
|
||
/// </summary>
|
||
/// <typeparam name="T"></typeparam>
|
||
/// <param name="str"></param>
|
||
/// <param name="rowNo"></param>
|
||
/// <param name="message"></param>
|
||
/// <param name="isCanNullOrEmpty"></param>
|
||
/// <param name="isPositiveOrZero">自然数0,1,2,3...</param>
|
||
/// <returns></returns>
|
||
public static T ValidParseStr<T>(string str,string message = "数据", bool isCanNullOrEmpty = true, bool isPositiveOrZero = true)
|
||
{
|
||
var val = default(T);
|
||
if (string.IsNullOrEmpty(str))
|
||
{
|
||
if (isCanNullOrEmpty)
|
||
{
|
||
return default(T);
|
||
}
|
||
else
|
||
{
|
||
throw new Exception($"{message}不能为空");
|
||
}
|
||
}
|
||
else
|
||
{
|
||
if (typeof(T).Name == typeof(int).Name || typeof(T).FullName == typeof(int?).FullName)
|
||
{
|
||
if (int.TryParse(str, out var valueInt))
|
||
{
|
||
if (isPositiveOrZero && valueInt < 0)
|
||
{
|
||
goto error;
|
||
}
|
||
return (T)Convert.ChangeType(valueInt, TypeCode.Int32);
|
||
}
|
||
else
|
||
{
|
||
goto error;
|
||
}
|
||
}
|
||
if (typeof(T).Name == typeof(Decimal).Name || typeof(T).FullName == typeof(Decimal?).FullName)
|
||
{
|
||
if (Decimal.TryParse(str, out var valueInt))
|
||
{
|
||
if (isPositiveOrZero && valueInt < 0)
|
||
{
|
||
goto error;
|
||
}
|
||
return (T)Convert.ChangeType(valueInt, TypeCode.Decimal);
|
||
}
|
||
else
|
||
{
|
||
goto error;
|
||
}
|
||
}
|
||
if (typeof(T).Name == typeof(Double).Name || typeof(T).FullName == typeof(Double?).FullName)
|
||
{
|
||
if (Double.TryParse(str, out var valueInt))
|
||
{
|
||
if (isPositiveOrZero && valueInt < 0)
|
||
{
|
||
goto error;
|
||
}
|
||
return (T)Convert.ChangeType(valueInt, TypeCode.Double);
|
||
}
|
||
else
|
||
{
|
||
goto error;
|
||
}
|
||
}
|
||
if (typeof(T).Name == typeof(Single).Name || typeof(T).FullName == typeof(Single?).FullName)
|
||
{
|
||
if (Single.TryParse(str, out var valueInt))
|
||
{
|
||
if (isPositiveOrZero && valueInt < 0)
|
||
{
|
||
goto error;
|
||
}
|
||
return (T)Convert.ChangeType(valueInt, TypeCode.Single);
|
||
}
|
||
else
|
||
{
|
||
goto error;
|
||
}
|
||
}
|
||
if (typeof(T).Name == typeof(DateTime).Name || typeof(T).FullName == typeof(DateTime?).FullName)
|
||
{
|
||
if (DateTime.TryParse(str, out var valueInt))
|
||
{
|
||
return (T)Convert.ChangeType(valueInt, TypeCode.DateTime);
|
||
}
|
||
else
|
||
{
|
||
goto error;
|
||
}
|
||
}
|
||
}
|
||
return val;
|
||
error: throw new Exception($"{message}的值有误");
|
||
}
|
||
|
||
/// <summary>
|
||
/// 提供正则、提示信息，返回验证结果
|
||
/// </summary>
|
||
/// <param name="str"></param>
|
||
/// <param name="rowNo"></param>
|
||
/// <param name="pattern"></param>
|
||
/// <param name="message"></param>
|
||
/// <param name="isCanNullOrEmpty"></param>
|
||
/// <returns></returns>
|
||
public static string ValidByPattern(string str,string pattern, string message = "数据", bool isCanNullOrEmpty = true)
|
||
{
|
||
if (string.IsNullOrEmpty(str))
|
||
{
|
||
if (isCanNullOrEmpty)
|
||
{
|
||
return str;
|
||
}
|
||
else
|
||
{
|
||
throw new BusinessException($"{message}不能为空");
|
||
}
|
||
}
|
||
else
|
||
{
|
||
if (!Regex.IsMatch(str, pattern)) throw new BusinessException($"{message}输入有误");
|
||
return str;
|
||
}
|
||
}
|
||
/// <summary>
|
||
/// 根据枚举判断
|
||
/// </summary>
|
||
/// <typeparam name="T"></typeparam>
|
||
/// <param name="str"></param>
|
||
/// <param name="message"></param>
|
||
/// <param name="isCanNullOrEmpty"></param>
|
||
/// <returns></returns>
|
||
public static int? ValidByEnum<T>(string str, string message, bool isCanNullOrEmpty = true) //where T:Enum
|
||
{
|
||
|
||
if (string.IsNullOrEmpty(str))
|
||
{
|
||
if (isCanNullOrEmpty)
|
||
{
|
||
return null;
|
||
}
|
||
else
|
||
{
|
||
throw new BusinessException($"{message}不能为空");
|
||
}
|
||
}
|
||
else
|
||
{
|
||
var value = EnumExtension.EnumToList<T>().Find(s => s.Name == str)?.Value;
|
||
if (value == null) throw new BusinessException($"未知的数据：{str}");
|
||
return Convert.ToInt32(value);
|
||
}
|
||
}
|
||
}
|
||
#endregion
|
||
} |