结果图:
1、插入单表
2、插入多表
二、代码
```csharp
```csharp
sharp``
public static partial class ExcelHelper
{
public class PropertyAndColumn
{
public PropertyAndColumn(PropertyInfo p, ExcelColumnAttribute e)
{
P = p;
E = e;
}
public PropertyInfo P { get; set; }
public ExcelColumnAttribute E { get; set; }
}
/// <summary>
/// 将实体类集合写入excel文件
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data"></param>
/// <param name="filePath"></param>
/// <param name="sheetName"></param>
/// <param name="dateFormat">时间格式</param>
public static void WriteToExcel(IEnumerable<SheetInfo> sheetInfos, string filePath)
{
string dateFormat = "yyyy-MM-dd HH:mm:ss";
var workbook = GetWorkbook(sheetInfos, dateFormat);
WriteToFile(filePath, workbook);
}
/// <summary>
/// 将实体类集合写入多个表的excel文件3
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data"></param>
/// <param name="filePath"></param>
/// <param name="sheetName"></param>
/// <param name="dateFormat">时间格式</param>
public static void WriteManySheetsToExcel(IEnumerable<SheetInfo> sheetInfos, string filePath, string dateFormat = "yyyy-MM-dd HH:mm:ss")
{
//判断是否有重名表
var temp = from a in sheetInfos
group a by a.SheetName into g
where g.Count() > 1
select g;
if (temp.Any())
{
throw new Exception($"以下页签名称重复: {string.Join(",", temp.Select(g => g.Key))}");
}
var workbook = GetWorkbook(sheetInfos, dateFormat);
WriteToFile(filePath, workbook);
}
/// <summary>
///
/// </summary>
/// <param name="filePath"></param>
/// <param name="workbook"></param>
public static void WriteToFile(string filePath, IWorkbook workbook)
{
using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
//写入数据流
workbook.Write(fs);
if (fs.CanRead)
{
fs.Flush();
}
}
}
/// <summary>
/// 重载GetWook方法,实现创建多页表
/// </summary>
/// <param name="sheetInfos"></param>
/// <param name="dateFormat"></param>
/// <returns></returns>
public static IWorkbook GetWorkbook(IEnumerable<SheetInfo> sheetInfos, string dateFormat = "yyyy-MM-dd HH:mm:ss")
{
//对excel版本进行判断,用哪个插件
//xls文件对应的类是:HSSFWorkBook
//xlsx文件对应的类是:XSSFWorkBook 这两个类都继承接口:IWorkBook。
IWorkbook workbook = new XSSFWorkbook();
foreach (var sheetInfo in sheetInfos)
{
ISheet sheet = workbook.CreateSheet(sheetInfo.SheetName);
IRow row = sheet.CreateRow(0);
ICellStyle dateCellStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateCellStyle.DataFormat = format.GetFormat(dateFormat);
///将每列的值与实例对象的属性建立连接
List<PropertyAndColumn> propertyAndColumns = new List<PropertyAndColumn>();
///通过反射获取对象的值
foreach (var property in sheetInfo.RowType.GetProperties())
{
if (property.GetCustomAttributes(typeof(ExcelColumnAttribute), true).SingleOrDefault() is ExcelColumnAttribute e)
{
if (e.WriteToExcel)
{
propertyAndColumns.Add(new PropertyAndColumn(property, e));
}
}
}
propertyAndColumns = propertyAndColumns.OrderBy(a => a.E.WriteOrder).ToList();
for (int i = 0; i < propertyAndColumns.Count; i++)
{
var a = propertyAndColumns[i];
row.CreateCell(i).SetCellValue(a.E.HeaderText);
row.Height = 350;
sheet.AutoSizeColumn(i);
}
var rows = sheetInfo.Rows.ToList();
//处理数据内容
for (int i = 0; i < rows.Count; i++)
{
var currentRowData = rows[i];
row = sheet.CreateRow(1 + i);
row.Height = 250;
for (int j = 0; j < propertyAndColumns.Count; j++)
{
var a = propertyAndColumns[j];
object value = a.P.GetValue(currentRowData);
if (value != null)
{
var c = row.CreateCell(j);
if (value is DateTime d)
{
c.SetCellValue(d);
c.CellStyle = dateCellStyle;
}
else
{
c.SetCellValue(value.ToString());
}
}
sheet.SetColumnWidth(j, 256 * 15);
}
}
}
return workbook;
}
}
}