using HMI.Core;
|
using HMI.Core.Util;
|
using NPOI.HSSF.UserModel;
|
using NPOI.SS.Formula.Functions;
|
using NPOI.SS.UserModel;
|
using NPOI.XSSF.UserModel;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.IO;
|
using System.Linq;
|
using System.Reflection;
|
|
namespace HMI.Util
|
{
|
public class ExcelHelper
|
{
|
public static void ExportExcel<T>(string targetPath, IList<T> datas)
|
{
|
Type type = typeof(T);
|
var properties = PropertyHelper.GetAtterProperty<T, ExcelColumnAttribute>(out var attrs);
|
if (properties == null || properties.Count <= 0)
|
{
|
throw new Exception("No Attribute");
|
}
|
IWorkbook workbook = null;
|
if (targetPath.EndsWith(".xlsx"))
|
{
|
workbook = new XSSFWorkbook();
|
}
|
else if (targetPath.EndsWith(".xls"))
|
{
|
workbook = new HSSFWorkbook();
|
}
|
workbook.CreateSheet("sheet1");////新建一个工作表
|
ISheet SheetOne = workbook.GetSheet("Sheet1");
|
IRow row1 = SheetOne.CreateRow(0);
|
for (int i = 0; i < properties.Count; i++)
|
{
|
row1.CreateCell(i).SetCellValue(LangUtil.Get((attrs[i]).Name));
|
}
|
|
for (int i = 0; i < datas.Count(); i++)
|
{
|
IRow rowTemp = SheetOne.CreateRow(i + 1);
|
for (int j = 0; j < properties.Count; j++)
|
{
|
var attr = attrs[j];
|
var value = attr.IgnoreExport ? "" : properties[j].GetValue(datas[i]);
|
rowTemp.CreateCell(j).SetCellValue(value?.ToString());
|
}
|
}
|
|
using (FileStream fs = new FileStream(targetPath, FileMode.OpenOrCreate))
|
{
|
workbook.Write(fs);
|
}
|
}
|
|
public static void ExportExcel<T>(string targetPath, DataTable table)
|
{
|
Type type = typeof(T);
|
var properties = PropertyHelper.GetAtterProperty<T, ExcelColumnAttribute>(out var attrs);
|
if (properties == null || properties.Count <= 0)
|
{
|
throw new Exception("No Attribute");
|
}
|
IWorkbook workbook = null;
|
if (targetPath.EndsWith(".xlsx"))
|
{
|
workbook = new XSSFWorkbook();
|
}
|
else if (targetPath.EndsWith(".xls"))
|
{
|
workbook = new HSSFWorkbook();
|
}
|
workbook.CreateSheet("sheet1");////新建一个工作表
|
ISheet SheetOne = workbook.GetSheet("Sheet1");
|
IRow row1 = SheetOne.CreateRow(0);
|
for (int i = 0; i < properties.Count; i++)
|
{
|
row1.CreateCell(i).SetCellValue(LangUtil.Get((attrs[i]).Name));
|
}
|
|
for (int i = 0; i < table.Rows.Count; i++)
|
{
|
IRow rowTemp = SheetOne.CreateRow(i + 1);
|
for (int j = 0; j < properties.Count; j++)
|
{
|
var attr = attrs[j];
|
object obj = attr.IgnoreExport ? "" : table.Rows[i][properties[j].Name];
|
string value = obj?.ToString();
|
if (obj is DateTime time)
|
{
|
value = time.ToString("yyyy-MM-dd HH:mm:ss");
|
}
|
rowTemp.CreateCell(j).SetCellValue(value);
|
}
|
}
|
|
using (FileStream fs = new FileStream(targetPath, FileMode.OpenOrCreate))
|
{
|
workbook.Write(fs);
|
}
|
}
|
|
public static void ExportExcel(string targetPath, List<string[]> rows)
|
{
|
IWorkbook workbook = null;
|
if (targetPath.EndsWith(".xlsx"))
|
{
|
workbook = new XSSFWorkbook();
|
}
|
else if (targetPath.EndsWith(".xls"))
|
{
|
workbook = new HSSFWorkbook();
|
}
|
workbook.CreateSheet("sheet1");////新建一个工作表
|
ISheet SheetOne = workbook.GetSheet("sheet1");
|
|
|
for (int i = 0; i < rows.Count; i++)
|
{
|
IRow rowTemp = SheetOne.CreateRow(i);
|
for (int j = 0; j < rows[i].Length; j++)
|
{
|
rowTemp.CreateCell(j).SetCellValue(rows[i][j]);
|
}
|
}
|
|
using (FileStream fs = new FileStream(targetPath, FileMode.OpenOrCreate))
|
{
|
workbook.Write(fs);
|
}
|
}
|
|
|
public static List<T> ReadFromExcel<T>(string sourcePath)
|
{
|
Type type = typeof(T);
|
var properties = PropertyHelper.GetAtterProperty<T, ExcelColumnAttribute>(out var attrs);
|
if (properties == null || properties.Count <= 0)
|
{
|
throw new Exception("No Attribute");
|
}
|
Dictionary<string, PropertyInfo> dict = new Dictionary<string, PropertyInfo>();
|
for (int i = 0; i < attrs.Count; i++)
|
{
|
dict.Add(LangUtil.Get(attrs[i].Name), properties[i]);
|
}
|
using (FileStream fileStream1 = new FileStream(sourcePath, FileMode.Open, FileAccess.Read))
|
{
|
IWorkbook workbook = null;
|
if (sourcePath.EndsWith(".xls"))
|
{
|
workbook = new HSSFWorkbook(fileStream1);
|
}
|
else
|
{
|
workbook = new XSSFWorkbook(fileStream1);
|
}
|
int currentRow = 1;
|
try
|
{
|
ISheet sheet = workbook.GetSheetAt(0);
|
IRow titleRow = sheet.GetRow(0);
|
|
List<T> result = new List<T>();
|
IRow row;
|
for (currentRow = 1; currentRow < (sheet.LastRowNum + 1); currentRow++)
|
{
|
row = sheet.GetRow(currentRow);
|
if (row != null)
|
{
|
T obj = (T)Activator.CreateInstance(type);
|
bool isEmpty = true;
|
for (int j = 0; j < row.Count(); j++)
|
{
|
string title = titleRow.Cells[j].StringCellValue;
|
if (dict.ContainsKey(title))
|
{
|
var cell = row.GetCell(j);
|
if (cell == null || cell.CellType == CellType.Blank)
|
continue;
|
CellType ct = cell.CellType;
|
object value = null;
|
if (ct == CellType.String)
|
value = row.GetCell(j).StringCellValue;
|
else if (ct == CellType.Numeric)
|
value = row.GetCell(j).NumericCellValue;
|
else if (ct == CellType.Boolean)
|
value = row.GetCell(j).BooleanCellValue;
|
else
|
value = row.GetCell(j).StringCellValue;
|
|
var prop = dict[title];
|
if (prop.PropertyType == typeof(string))
|
{
|
value = Convert.ToString(value);
|
}
|
else if (prop.PropertyType == typeof(double) || prop.PropertyType == typeof(double?))
|
{
|
value = Convert.ToDouble(value);
|
}
|
else if (prop.PropertyType == typeof(int) || prop.PropertyType == typeof(int?))
|
{
|
value = Convert.ToInt16(value);
|
}
|
else if (prop.PropertyType == typeof(bool) || prop.PropertyType == typeof(bool?))
|
{
|
value = Convert.ToBoolean(value);
|
}
|
else if (prop.PropertyType.IsEnum)
|
{
|
value = Enum.Parse(prop.PropertyType, value.ToString());
|
}
|
prop.SetValue(obj, value);
|
isEmpty = false;
|
}
|
}
|
if (!isEmpty) result.Add(obj);
|
}
|
}
|
return result;
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"第{currentRow}行解析错误：{ex.Message}", ex);
|
}
|
finally
|
{
|
workbook.Close();
|
}
|
}
|
}
|
}
|
}
|
