.Net Core NPOI读取Excel 并转为数据实体类

创建应用程序

这里直接创建Console程序

引用NPOI的NuGet包

• PM> Install-Package NPOI -Version 2.5.1
• 直接Nuget包管理器添加
  
  

导入Excel

public static string ReadExcel()
{
DataTable dtTable = new DataTable();
List<string> rowList = new List<string>();
ISheet sheet;
try
{
using (var stream = new FileStream(InputPath, FileMode.Open))
{
stream.Position = 0;
XSSFWorkbook xssWorkbook = new XSSFWorkbook(stream);
sheet = xssWorkbook.GetSheetAt(0);
IRow headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
ICell cell = headerRow.GetCell(j);
if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) continue;
{
dtTable.Columns.Add(cell.ToString());
}
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row == null) continue;
if (row.Cells.All(d => d.CellType == CellType.Blank)) continue;
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
if (!string.IsNullOrEmpty(row.GetCell(j).ToString()) && !string.IsNullOrWhiteSpace(row.GetCell(j).ToString()))
{
rowList.Add(row.GetCell(j).ToString());
}
}
}
if (rowList.Count > 0)
dtTable.Rows.Add(rowList.ToArray());
rowList.Clear();
}
}
}
catch (Exception ex)
{
Console.WriteLine($"ReadExcel: {ex.Message}");
} return JsonConvert.SerializeObject(dtTable);
}


转为数据实体

• 创建实体类

public class ConvertDataModel
{
public string EID { get; set; }
public string SECURITY_CODE { get; set; }
public string SECURITY_INNER_CODE { get; set; } public string CHANGE_DATE { get; set; } public string NAME_ABBR_CH { get; set; } }


• 转换

var sorc = JsonConvert.DeserializeObject<List<ConvertDataModel>>(str);


导出Excel

public static void WriteExcel(List<ConvertDataModel> convertDatas)
{
// Lets converts our object data to Datatable for a simplified logic.
// Datatable is most easy way to deal with complex datatypes for easy reading and formatting.
try
{
DataTable table = (DataTable)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(convertDatas), (typeof(DataTable)));
var memoryStream = new MemoryStream(); using (var fs = new FileStream(OutputPath, FileMode.Create, FileAccess.Write))
{
IWorkbook workbook = new XSSFWorkbook();
ISheet excelSheet = workbook.CreateSheet("Sheet1"); List<String> columns = new List<string>();
IRow row = excelSheet.CreateRow(0);
int columnIndex = 0; foreach (DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
row.CreateCell(columnIndex).SetCellValue(column.ColumnName);
columnIndex++;
} int rowIndex = 1;
foreach (DataRow dsrow in table.Rows)
{
row = excelSheet.CreateRow(rowIndex);
int cellIndex = 0;
foreach (String col in columns)
{
row.CreateCell(cellIndex).SetCellValue(dsrow[col].ToString());
cellIndex++;
} rowIndex++;
}
workbook.Write(fs);
}
}
catch (Exception ex)
{
Console.WriteLine($"WriteExcel: {ex.Message}");
}
}


引用： https://www.thecodebuzz.com/read-and-write-excel-file-in-net-core-using-npoi/