使用NPOI导入.xlsx遇到“EOF in header”报错，网上找好很多方法，没解决，最后换成EPPlus.Core导入。
导出默认是.xls。
NPOI操作类：
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Collections;
using System.Data;
namespace CommonUtils
{
/// <summary>
/// Excel操作相关
/// </summary>
public class ExcelHelper
{
#region 读取Excel到DataTable
/// <summary>
/// 读取Excel文件的内容
/// </summary>
/// <param name="path"></param>
/// <param name="sheetName">工作表名称</param>
/// <returns></returns>
public static DataTable GetDataTable(string path, string sheetName = null)
{
if (path.ToLower().EndsWith(".xlsx"))
return EPPlusHelper.WorksheetToTable(path, sheetName);
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
return GetDataTable(file, sheetName);
}
}
/// <summary>
/// 从Excel文件流读取内容
/// </summary>
/// <param name="file"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public static DataTable GetDataTable(Stream file, string contentType, string sheetName = null)
{
//载入工作簿
IWorkbook workBook = null;
if (contentType == "application/vnd.ms-excel")
{
workBook = new HSSFWorkbook(file);
}
else if (contentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
workBook = new XSSFWorkbook(file);
}
else
{
try
{
workBook = new HSSFWorkbook(file);
}
catch
{
try
{
workBook = new XSSFWorkbook(file);
}
catch
{
throw new Exception("文件格式不被支持!");
}
}
}
//获取工作表(sheetName为空则默认获取第一个工作表)
var sheet = string.IsNullOrEmpty(sheetName) ? workBook.GetSheetAt(0) : workBook.GetSheet(sheetName);
//生成DataTable
if (sheet != null)
return GetDataTable(sheet);
else
throw new Exception(string.Format("工作表{0}不存在!", sheetName ?? ""));
}
/// <summary>
/// 读取工作表数据
/// </summary>
/// <param name="sheet"></param>
/// <returns></returns>
private static DataTable GetDataTable(ISheet sheet)
{
IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable(sheet.SheetName);
//默认第一个非空行为列头
bool isTitle = true;
//行索引
int titleRowIndex = 0;
//默认列头后的第一个数据行，作为DataTable列类型的依据
IRow firstDataRow = null;
while (rows.MoveNext())
{
IRow row = null;
if (rows.Current is XSSFRow)//*.xlsx
{
row = (XSSFRow)rows.Current;
}
else//*.xls
{
row = (HSSFRow)rows.Current;
}
//是否空行
if (IsEmptyRow(row))
{
if (isTitle)
{
titleRowIndex++;
}
continue;
}
else
{
if (isTitle)
{
firstDataRow = sheet.GetRow(titleRowIndex + 1);//默认列头后的第一个数据行，作为DataTable列类型的依据
}
}
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
var cell = row.GetCell(i);
if (isTitle)
{
var firstDataRowCell = firstDataRow.GetCell(i);
if (firstDataRowCell != null || cell != null)
{
dt.Columns.Add(cell.StringCellValue.Trim());
}
else
{
dt.Columns.Add(string.Format("未知列{0}", i + 1));
}
}
else
{
if (i > dt.Columns.Count - 1) break;
dr[i] = GetCellValue(cell, dt.Columns[i].DataType);
}
}
if (!isTitle && !IsEmptyRow(dr, dt.Columns.Count))
{
dt.Rows.Add(dr);
}
isTitle = false;
}
return dt;
}
/// <summary>
/// 获取单元格值
/// </summary>
/// <param name="cell"></param>
/// <param name="colType"></param>
/// <returns></returns>
private static object GetCellValue(ICell cell, Type colType)
{
if (cell == null || cell.ToString().ToUpper().Equals("NULL") || cell.CellType == NPOI.SS.UserModel.CellType.Blank)
return DBNull.Value;
object val = null;
switch (cell.CellType)
{
case NPOI.SS.UserModel.CellType.Boolean:
val = cell.BooleanCellValue;
break;
case NPOI.SS.UserModel.CellType.Numeric:
var cellValueStr = cell.ToString().Trim();
if (cellValueStr.IndexOf('-') >= 0 || cellValueStr.IndexOf('/') >= 0)
{
DateTime d = DateTime.MinValue;
DateTime.TryParse(cellValueStr, out d);
if (!d.Equals(DateTime.MinValue)) val = cellValueStr;
}
if (val == null)
{
decimal vNum = 0;
decimal.TryParse(cellValueStr, out vNum);
val = vNum;
}
break;
case NPOI.SS.UserModel.CellType.String:
val = cell.StringCellValue;
break;
case NPOI.SS.UserModel.CellType.Error:
val = cell.ErrorCellValue;
break;
case NPOI.SS.UserModel.CellType.Formula:
default:
val = "=" + cell.CellFormula;
break;
}
return val;
}
/// <summary>
/// 检查是否空数据行
/// </summary>
/// <param name="dr"></param>
/// <returns></returns>
private static bool IsEmptyRow(DataRow dr, int colCount)
{
bool isEmptyRow = true;
for (int i = 0; i < colCount; i++)
{
if (dr[i] != null && !dr[i].Equals(DBNull.Value))
{
isEmptyRow = false;
break;
}
}
return isEmptyRow;
}
/// <summary>
/// 检查是否空的Excel行
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
private static bool IsEmptyRow(IRow row)
{
bool isEmptyRow = true;
for (int i = 0; i < row.LastCellNum; i++)
{
if (row.GetCell(i) != null)
{
isEmptyRow = false;
break;
}
}
return isEmptyRow;
}
#endregion
#region 生成DataTable到Excel
/// <summary>
/// 生成Excel数据到路径
/// </summary>
/// <param name="data"></param>
/// <param name="path"></param>
public static void GenerateExcel(DataTable data, string path)
{
var workBook = GenerateExcelData(data);
//保存至路径
using (FileStream fs = File.OpenWrite(path)) //打开一个xls文件，如果没有则自行创建，如果存在则在创建时不要打开该文件！
{
workBook.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
}
}
/// <summary>
/// 生成Excel数据到字节流
/// </summary>
/// <param name="data"></param>
/// <param name="path"></param>
public static byte[] GenerateExcel(DataTable data)
{
var workBook = GenerateExcelData(data);
using (MemoryStream ms = new MemoryStream())
{
workBook.Write(ms);
return ms.GetBuffer();
}
}
/// <summary>
/// 生成DataTable到Excel
/// </summary>
/// <param name="data"></param>
/// <param name="path"></param>
private static IWorkbook GenerateExcelData(DataTable data)
{
//创建工作簿
var workBook = new HSSFWorkbook();
//生成文件基本信息
GenerateSummaryInformation(workBook);
//创建工作表
var sheet = workBook.CreateSheet("Sheet1");
//创建行
if (data != null && data.Columns.Count > 0)
{
IRow row = sheet.CreateRow(0);
for (int i = 0; i < data.Columns.Count; i++)
{
var cell = row.CreateCell(i);
cell.SetCellValue(data.Columns[i].ColumnName);
}
}
//创建数据行
if (data != null && data.Rows.Count > 0)
{
for (int rowIndex = 1; rowIndex <= data.Rows.Count; rowIndex++)
{
IRow row = sheet.CreateRow(rowIndex);
for (int colIndex = 0; colIndex < data.Columns.Count; colIndex++)
{
var cell = row.CreateCell(colIndex);
var cellValue = data.Rows[rowIndex - 1][colIndex];
switch (data.Columns[colIndex].DataType.Name)
{
case "Byte":
case "Int16":
case "Int32":
case "Int64":
case "Decimal":
case "Single":
case "Double":
double doubleVal = 0;
if (cellValue != null && !cellValue.Equals(System.DBNull.Value))
{
double.TryParse(cellValue.ToString(), out doubleVal);
cell.SetCellValue(doubleVal);
}
break;
case "DateTime":
DateTime dtVal = DateTime.MinValue;
if (cellValue != null && !cellValue.Equals(System.DBNull.Value))
{
DateTime.TryParse(cellValue.ToString(), out dtVal);
if (dtVal != DateTime.MinValue)
{
cell.SetCellValue(dtVal);
}
}
break;
default:
if (cellValue != null && !cellValue.Equals(System.DBNull.Value))
{
cell.SetCellValue(cellValue.ToString());
}
break;
}
}
}
}
return workBook;
}
/// <summary>
/// 创建文档的基本信息(右击文件属性可看到的)
/// </summary>
/// <param name="workBook"></param>
private static void GenerateSummaryInformation(HSSFWorkbook workBook)
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Company";
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "Subject";//主题
si.Author = "Author";//作者
workBook.DocumentSummaryInformation = dsi;
workBook.SummaryInformation = si;
}
#endregion
}
}EPPlus.Core工具类：
//using EPPlus.Extensions;
using OfficeOpenXml;
using System.Data;
namespace CommonUtils
{
/// <summary>
/// 使用 EPPlus 第三方的组件读取Excel
/// </summary>
public class EPPlusHelper
{
private static string GetString(object obj)
{
if (obj == null)
return "";
return obj.ToString();
}
/// <summary>
///将指定的Excel的文件转换成DataTable （Excel的第一个sheet）
/// </summary>
/// <param name="fullFielPath">文件的绝对路径</param>
/// <returns></returns>
public static DataTable WorksheetToTable(string fullFielPath, string sheetName = null)
{
//如果是“EPPlus”，需要指定LicenseContext。
//EPPlus.Core 不需要指定。
//ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
FileInfo existingFile = new FileInfo(fullFielPath);
ExcelPackage package = new ExcelPackage(existingFile);
ExcelWorksheet worksheet = null;
if (string.IsNullOrEmpty(sheetName))
{
//不传入 sheetName 默认取第1个sheet。
//EPPlus 索引是0
//EPPlus.Core 索引是1
worksheet = package.Workbook.Worksheets[1];
}
else
{
worksheet = package.Workbook.Worksheets[sheetName];
}
if (worksheet == null)
throw new Exception("指定的sheetName不存在");
return WorksheetToTable(worksheet);
}
/// <summary>
/// 将worksheet转成datatable
/// </summary>
/// <param name="worksheet">待处理的worksheet</param>
/// <returns>返回处理后的datatable</returns>
public static DataTable WorksheetToTable(ExcelWorksheet worksheet)
{
//获取worksheet的行数
int rows = worksheet.Dimension.End.Row;
//获取worksheet的列数
int cols = worksheet.Dimension.End.Column;
DataTable dt = new DataTable(worksheet.Name);
DataRow dr = null;
for (int i = 1; i <= rows; i++)
{
if (i > 1)
dr = dt.Rows.Add();
for (int j = 1; j <= cols; j++)
{
//默认将第一行设置为datatable的
if (i == 1)
dt.Columns.Add(GetString(worksheet.Cells[i, j].Value));
//剩下的写入datatable
else
dr[j - 1] = GetString(worksheet.Cells[i, j].Value);
}
}
return dt;
}
}
}使用：
// See https://aka.ms/new-console-template for more information
using CommonUtils;
using System.Data;
Console.WriteLine("Hello, World!");
try
{
string dir = AppContext.BaseDirectory;
//2003
string fullName = Path.Combine(dir, "测试excel.xls");
DataTable dt = ExcelHelper.GetDataTable(fullName);
Console.WriteLine("Hello, World!" + dir);
//2007
string fullName2 = Path.Combine(dir, "测试excel.xlsx");
//dt = ExcelHelper.GetDataTable(fullName);
//DataTable dt2 = ExcelHelper.GetDataTable(fullName2, "sheetf");
DataTable dt2 = ExcelHelper.GetDataTable(fullName2);
string saveFullName = Path.Combine(dir, "save_excel.xls");
//ExcelHelper2.ExportExcelByMemoryStream(saveFullName, dt2);
string saveFullName2 = Path.Combine(dir, "save_excel2.xls");
ExcelHelper.GenerateExcel(dt2, saveFullName2);
Console.WriteLine("Hello, World!" + dir);
}
catch (Exception ex)
{
Console.WriteLine("ex:" + ex.Message);
}
Console.ReadKey();
源码：http://xiazai.jb51.net/202112/yuanma/ConsoleOperExcel_jb51.rar，使用vs2022 。
到此这篇关于.NET6导入和导出EXCEL的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持编程网。
