NPOI实现 Excel导入导出

最新推荐文章于 2025-04-28 09:04:35 发布

原创最新推荐文章于 2025-04-28 09:04:35 发布

· 290 阅读

1 ·

版权

C# 同时被 2 个专栏收录

4 篇文章

订阅专栏

.NET

3 篇文章

订阅专栏

本文介绍了一种使用自定义属性实现对象集合与Excel数据互换的方法。通过定义Column和DateTimeFormate属性，可以轻松地将对象集合写入Excel，并从Excel读取数据到对象集合。文章详细展示了如何创建Excel文件，设置单元格样式，以及如何处理不同类型的数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

实现对象集合写入到Excel和Excel数据读出到对象集合的功能

/// <summary>
/// 自定义列属性
/// </summary>
public class ColumnAttribute:Attribute
{
public string ColumnName { get; set; }

public ColumnAttribute(string ColumnName)
{
this.ColumnName = ColumnName;
}
}

/// <summary>
/// 时间格式
/// </summary>
public class DateTimeFormateAttribute : Attribute
{
public string Formate { get; set; }

public DateTimeFormateAttribute(string formate)
{
this.Formate = formate;
}
}

/// <summary>
/// 从Excel中读取数据
/// </summary>
/// <typeparam name="T"></typeparam>
public class ExcelHelper<T> where T : class
{
/// <summary>
/// 从Excle中获取数据
/// </summary>
/// <param name="ctx"></param>
/// <param name="filePath">文件路径</param>
/// <param name="columnAccount">Excle列数量</param>
/// <returns></returns>
public static List<T> GetDataFromExcel(string filePath, int columnAccount)
{
PropertyInfo rowIndexPro = null;
List<T> results = new List<T>();
IWorkbook workbook = GetData(filePath);
if (workbook == null || workbook.NumberOfSheets == 0)
{
return null;
}
Type t = typeof(T);
Assembly ass = Assembly.Load(t.Assembly.FullName);
var propertyes = t.GetProperties();//workbook.NumberOfSheets
Dictionary<string, PropertyInfo> propertyColumnNames = new Dictionary<string, PropertyInfo>(); //列明属性名
foreach (var pro in propertyes)
{
var columnAttributes = pro.GetCustomAttributes(typeof(ColumnAttribute), false);
if (columnAttributes != null && columnAttributes.Length > 0)
{
string columnName = columnAttributes.Select(p => ((ColumnAttribute)p).ColumnName).FirstOrDefault();
if (propertyColumnNames.ContainsKey(columnName))
{
throw new Exception(string.Format("类名{0}具有重复的Column属性", t.FullName));
}
propertyColumnNames.Add(columnName, pro);
}
if(pro.GetCustomAttributes(typeof(RowIndexAttribute),false).Length>0)
{
rowIndexPro = pro;
}
}
for (int i = 0; i < 1; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
List<string> columnNames = new List<string>(); //Excle列名
IRow headerRow = sheet.GetRow(0);
for (int j = 0; j < columnAccount; j++) //查询Excel列名
{
ICell cell = headerRow.GetCell(j);
string name = cell != null ? cell.ToString() : "";
if (string.IsNullOrEmpty(name))
{
continue;
}
if (columnNames.Contains(name))
{
throw new Exception(string.Format("Excel中具有重复的列名：{0}", name));
}
columnNames.Add(name);

}
for (int index = 1; index <= sheet.LastRowNum; index++) //遍历Excel每一行数据
{
T obj = ass.CreateInstance(t.FullName) as T;
if (obj != null)
{
if(rowIndexPro!=null)
{
rowIndexPro.SetValue(obj, index,null);
}
IRow excelrow = sheet.GetRow(index);
for (int j = 0; j < columnAccount; j++) //遍历每一行的每一列
{
ICell cell = excelrow.GetCell(j);
if (cell == null)
{
continue;
}
string columnName = columnNames[j];
if (propertyColumnNames.ContainsKey(columnName))
{
PropertyInfo pro = propertyColumnNames[columnName];
string proFullName = pro.PropertyType.FullName;
if (proFullName.Contains("Int16"))
{
Int16 v1 = 0;
if (Int16.TryParse(cell.ToString(), out v1))
{
pro.SetValue(obj, v1, null);
}
continue;
}
if (proFullName.Contains("Int32"))
{
Int32 v2 = 0;
if (Int32.TryParse(cell.ToString(), out v2))
{
pro.SetValue(obj, v2, null);
}
continue;
}
if (proFullName.Contains("String"))
{
pro.SetValue(obj, cell.ToString(), null);
continue;
}
if (proFullName.Contains("Int64"))
{
Int64 v3 = 0;
if (Int64.TryParse(cell.ToString(), out v3))
{
pro.SetValue(obj, v3, null);
}
continue;
}
if (proFullName.Contains("Decimal"))
{
decimal v4 = 0;
if (decimal.TryParse(cell.ToString(), out v4))
{
pro.SetValue(obj, v4, null);
}
continue;
}
if (proFullName.Contains("DateTime"))
{
DateTime v5 = DateTime.Now;
if (cell.CellType == CellType.Numeric)
{
pro.SetValue(obj, cell.DateCellValue, null);
}
else if (DateTime.TryParse(cell.ToString(), out v5))
{
pro.SetValue(obj, v5, null);
}
continue;
}
if (proFullName.Contains("Boolean"))
{
bool v6 = true;
if (bool.TryParse(cell.ToString(), out v6))
{
pro.SetValue(obj, v6, null);
}
continue;
}
if (proFullName.Contains("Single"))
{
float v7 = 0;
if (float.TryParse(cell.ToString(), out v7))
{
pro.SetValue(obj, v7, null);
}
continue;
}
if (proFullName.Contains("Double"))
{
double v8 = 0;
if (double.TryParse(cell.ToString(), out v8))
{
pro.SetValue(obj, v8, null);
}
}

}
}
}
results.Add(obj);
}

}
return results;
}

public static DataTable GetDataTableFromExcel( string filePath)
{
IWorkbook workbook = GetData(filePath);
if (workbook == null || workbook.NumberOfSheets == 0)
{
return null;
}
DataTable dt = new DataTable();
ISheet sheet = workbook.GetSheetAt(0);
IRow firstRow = sheet.GetRow(0);
//获取Excel列表列的总数
int columns = 0;
while (true)
{
ICell cell = firstRow.GetCell(columns);
if (cell == null)
{
break;
}
columns++;
dt.Columns.Add(cell.ToString());
}
for (int i = 0; i < sheet.LastRowNum; i++)
{
DataRow row = dt.NewRow();
IRow excelrow = sheet.GetRow(i + 1);
for (int j = 0; j < columns; j++)
{
ICell cell = excelrow.GetCell(j);
if (cell != null)
{
if (cell.CellType == 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 || format == 20)
row[j] = cell.DateCellValue;
else
row[j] = cell.NumericCellValue;
}
else
{
row[j] = excelrow.GetCell(j).ToString();
}
}
}
dt.Rows.Add(row);
}
return dt;
}

public static IWorkbook GetData(string filePath)
{
if (!File.Exists(filePath))
{
String msg = "找不到文件路径";
throw new Exception(msg);
}
IWorkbook workbook = null;

using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
if (filePath.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (filePath.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook(fs);
}
return workbook;
}

/// <summary>
/// 将数据写入到Excel中去
/// </summary>
/// <param name="ctx"></param>
/// <param name="filePath"></param>]
/// <param name="datas"></param>
/// <returns>返回文件路径</returns>
public static string WriteDataToExcel( List<T> datas)
{
string fileName = Guid.NewGuid().ToString().Replace("-", "");
string baseDirectory = System.AppDomain.CurrentDomain.BaseDirectory;
string directory = string.Format("{0}/export", baseDirectory);
if (!Directory.Exists(directory))
{
Directory.CreateDirectory(directory);
}
string filePath = string.Format("{0}/export/{1}.xls", baseDirectory, fileName);
using (FileStream fs = File.Open(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
IWorkbook workbook = null;
if (filePath.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook();
else if (filePath.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook();
#region 写头部信息
Type t = typeof(T);
var propertyes = t.GetProperties();//workbook.NumberOfSheets
Dictionary<string, PropertyInfo> propertyColumnNames = new Dictionary<string, PropertyInfo>(); //列明属性名
foreach (var pro in propertyes)
{
var columnAttributes = pro.GetCustomAttributes(typeof(ColumnAttribute), false);
if (columnAttributes != null && columnAttributes.Length > 0)
{
string columnName = columnAttributes.Select(p => ((ColumnAttribute)p).ColumnName).FirstOrDefault();
if (propertyColumnNames.ContainsKey(columnName))
{
throw new Exception(string.Format("类名{0}具有重复的Column属性", t.FullName));
}
propertyColumnNames.Add(columnName, pro);
}
}
ISheet sheet = workbook.CreateSheet();
ICellStyle cellstyle = workbook.CreateCellStyle();
//cellstyle.FillForegroundColor = 54;
cellstyle.FillForegroundColor = 9;
cellstyle.FillPattern = FillPattern.SolidForeground;
cellstyle.BorderBottom = BorderStyle.Thin;
cellstyle.BorderLeft = BorderStyle.Thin;
cellstyle.BorderRight = BorderStyle.Thin;
cellstyle.BorderTop = BorderStyle.Thin;
cellstyle.Alignment = HorizontalAlignment.Center;
IRow header = sheet.CreateRow(0);
int i = 0;
foreach (var kv in propertyColumnNames)
{
ICell cell = header.CreateCell(i);
cell.SetCellValue(kv.Key);
cell.CellStyle = cellstyle;
i++;
}
#endregion
#region 写Excel数据
int rowindex = 1;
foreach (T obj in datas)
{
IRow row = sheet.CreateRow(rowindex);
i = 0;
foreach (var kv in propertyColumnNames)
{
sheet.AutoSizeColumn(i);
ICell cell = row.CreateCell(i);
i++;
object v = kv.Value.GetValue(obj, null);
if (kv.Value.PropertyType.Name == typeof(DateTime).Name)
{
var attr = kv.Value.GetCustomAttributes(typeof(DateTimeFormateAttribute), false);
if (attr != null && attr.Length > 0 && v != null)
{
DateTimeFormateAttribute formateAttr = (DateTimeFormateAttribute)attr[0];
cell.SetCellValue(((DateTime)v).ToString(formateAttr.Formate));
continue;
}
if (v != null)
{
cell.SetCellValue(((DateTime)v).ToString("yyyy-MM-dd"));
}
else
{
cell.SetCellValue("");
}
}
else
{
cell.SetCellValue(v != null ? v.ToString() : "");
}
}
rowindex++;
}
#endregion

workbook.Write(fs);
//workbook.Dispose();
fs.Flush();
}
return filePath;
}

}

public class Person
{
[Column("姓名")]
public string Name { get; set; }

[Column("生日")]
[DateTimeFormate("yyyy-MM-dd")]
public DateTime BirthDay { get; set; }
}

public void main(object[] args)
{
List<Person> persons = new List<Person>();
Person p = new Person();
p.Name = "张三";
p.BirthDay = DateTime.Parse("2020-1-1");
persons.Add(p);
ExcelHelper<Person>.WriteDataToExcel(persons);
}

产生的结果