C# NPOI Excel应用
参考链接
https://www.cnblogs.com/tangpeng97/p/7839189.html
https://www.cnblogs.com/chenyanbin/archive/2019/05/10/10832614.html
https://www.cnblogs.com/dedeyi/p/3240592.html 解决单元格null的问题
准备工作
- 安装好插件后，需要重写方法 //年代有点久，忘记为什么要重写了
/// <summary>
/// 重写Npoi方法
/// </summary>
public class NpoiMemoryStream : MemoryStream
{
public NpoiMemoryStream()
{
AllowClose = true;
}
public bool AllowClose { get; set; }
public override void Close()
{
if (AllowClose)
base.Close();
}
}
Excel文件转DataTable
注意点
- xlsx和xls 需要分别对应两个类型，xlsx对应XSSFWorkbook，xls对应HSSFWorkbook
根据文件路径得到Excel转Datatable
XSSFWorkbook workbook = new XSSFWorkbook("D:\\test.xlsx");//这段没测试，应该可以用
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
DataTable dt = new DataTable(sheet.SheetName);
// write header row
IRow headerRow = sheet.GetRow(0);
foreach (ICell headerCell in headerRow)
{
dt.Columns.Add(headerCell.ToString());
}
// write the rest
int rowIndex = 0;
foreach (IRow row in sheet)
{
// skip header row
if (rowIndex++ == 0) continue;
DataRow dataRow = dt.NewRow();
dataRow.ItemArray = row.Cells.Select(c => c.ToString()).ToArray();
dt.Rows.Add(dataRow);
}
return dt;
根据文件转换成字节流生成DataTable
/// <summary>
/// 根据Excel文件流和类型 得到对应IWorkbook
/// </summary>
/// <param name="stream"></param>
/// <param name="type"></param>
/// <returns></returns>
private static IWorkbook GetIWorkbookByStream(Stream stream, string type) //将Excel格式判定抽象出来
{
IWorkbook workbook;
if (type.Equals(".xlsx"))
{
workbook = new XSSFWorkbook(stream);
}
else
{
workbook = new HSSFWorkbook(stream);
}
return workbook;
}
public static DataTable GetDataTableByISheet(ISheet sheet)//根据抽象出来的工作簿返回表格
{
DataTable dt = new DataTable();
IRow headerRow = sheet.GetRow(0);
foreach (ICell headerCell in headerRow)
{
dt.Columns.Add(headerCell.ToString());
}
// write the rest
int rowIndex = 0;
foreach (IRow row in sheet)
{
// skip header row 去除首行标题
if (rowIndex++ == 0) continue;
DataRow dataRow = dt.NewRow();
//dataRow.ItemArray = row.Cells.Select(c => c.ToString()).ToArray();这个会自动去除为null的单元格，导致错位问题
//遍历每一个单元格，防止单元格为null被自动去除
for (int i = 0, len = row.LastCellNum; i < len; i++)
{
ICell cell = row.GetCell(i);
if (cell != null)
{
switch (cell.CellType)
{
case CellType.String:
dataRow[i] = cell.StringCellValue;
break;
case CellType.Numeric:
dataRow[i] = cell.NumericCellValue;
break;
case CellType.Boolean:
dataRow[i] = cell.BooleanCellValue;
break;
default:
dataRow[i] = "ERROR";
break;
}
}
}
dt.Rows.Add(dataRow);
}
return dt;
}
public static DataSet GetDataSetByIWorkbook(IWorkbook workbook)//根据抽象出来的Excel文件返回DataSet
{
DataSet ds = new DataSet();
// IWorkbook workbook = GetIWorkbookByStream(stream, type);
var flag = workbook.ActiveSheetIndex;
for(int i = 0; i <= flag; i++)
{
ds.Tables.Add(GetDataTableByISheet(workbook.GetSheetAt(i)));
}
return ds;
}
/// <summary>
/// 根据上传文件得到DataTable，只取第一张表
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public static DataTable GetDataTableByExcelFile(HttpPostedFileBase file)
{
DataTable result = new DataTable();
IWorkbook workbook;
if (file.FileName.EndsWith(".xlsx"))
{
workbook = GetIWorkbookByStream(file.InputStream, ".xlsx");
}
else if (file.FileName.EndsWith(".xls"))
{
workbook = GetIWorkbookByStream(file.InputStream, ".xls");
}
else
throw new Exception("文件格式不正确，只允许.xlsx或.xls");
result = GetDataTableByISheet(workbook.GetSheetAt(0));
return result;
}
public static DataSet GetDataSetByExcelFile(HttpPostedFileBase file)
{
DataSet ds = new DataSet();
IWorkbook workbook;
//GetDataSetByISheet(file.InputStream, ".xlsx");
if (file.FileName.EndsWith(".xlsx"))
{
workbook = GetIWorkbookByStream(file.InputStream, ".xlsx");
}
else if (file.FileName.EndsWith(".xls"))
{
workbook = GetIWorkbookByStream(file.InputStream, ".xls");
}
else
throw new Exception("文件格式不正确，只允许.xlsx或.xls");
ds = GetDataSetByIWorkbook(workbook);
return ds;
}
调用方式
DataTable dt =GetDataTableByExcelFile(file);//Excel第一张表
DataSet ds =GetDataSetByExcelFile(file);//Excel表集合
根据DataTable生成Excel文件
- 可传入单个DataTable或者List
/// <summary>
/// 根据DataTable 生成Excel
/// </summary>
/// <param name="source"></param>
/// <returns></returns>
public static NpoiMemoryStream GetExcelFile( DataTable source)
{
//创建Excel文件的对象,XLSX
XSSFWorkbook book = new XSSFWorkbook();
//添加一个sheet
ISheet sheet = book.CreateSheet($"OA导出");
//行下标记录
int rowIndex = 0;
//创建首行
IRow row0 = sheet.CreateRow(rowIndex++);
////创建单元格
//ICell cell0 = row0.CreateCell(0);
////设置单元格内容
//cell0.CellStyle.Alignment = HorizontalAlignment.CenterSelection;
//cell0.SetCellValue("料品情况查询");
//sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, source.Columns.Count));
// IRow row1 = sheet.CreateRow(rowIndex++);
for (var i = 0; i < source.Columns.Count; i++)
{
row0.CreateCell(i).SetCellValue(source.Columns[i].ToString());
}
for (var i = 0; i < source.Rows.Count; i++)
{
IRow rowTemp = sheet.CreateRow(rowIndex++);
for (var j = 0; j < source.Columns.Count; j++)
{
if (source.Rows[i][j].GetType().Name == "Decimal")
{
rowTemp.CreateCell(j).SetCellValue(Convert.ToDouble(source.Rows[i][j]));
}
else
{
rowTemp.CreateCell(j).SetCellValue(source.Rows[i][j].ToString());
}
}
}
for (int columnNum = 0; columnNum < source.Columns.Count; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;//获取当前列宽度
for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)//在这一列上循环行
{
IRow currentRow = sheet.GetRow(rowNum);
ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;//获取当前单元格的内容宽度
if (columnWidth < length + 1)
{
columnWidth = length + 1;
}//若当前单元格内容宽度大于列宽，则调整列宽为当前单元格宽度，后面的+1是我人为的将宽度增加一个字符
}
//sheet.SetColumnWidth(columnNum, columnWidth * 256);
if (columnWidth > 255)
{
columnWidth = 254;
}
else
{
sheet.SetColumnWidth(columnNum, columnWidth * 256);
}
}
var ms = new NpoiMemoryStream();
ms.AllowClose = false;
book.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return ms;
}
/// <summary>
/// 根据多个DataTable 生成一个Excel多个表
/// </summary>
/// <param name="sources"></param>
/// <returns></returns>
public static NpoiMemoryStream GetExcelFile(List<DataTable> sources)
{
//创建Excel文件的对象,XLSX
XSSFWorkbook book = new XSSFWorkbook();
int flag = 1;
foreach(DataTable source in sources)
{
//添加一个sheet
ISheet sheet = book.CreateSheet($"OA导出"+flag);
//行下标记录
int rowIndex = 0;
//创建首行
IRow row0 = sheet.CreateRow(rowIndex++);
////创建单元格
//ICell cell0 = row0.CreateCell(0);
////设置单元格内容
//cell0.CellStyle.Alignment = HorizontalAlignment.CenterSelection;
//cell0.SetCellValue("料品情况查询");
//sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, source.Columns.Count));
// IRow row1 = sheet.CreateRow(rowIndex++);
for (var i = 0; i < source.Columns.Count; i++)
{
row0.CreateCell(i).SetCellValue(source.Columns[i].ToString());
}
for (var i = 0; i < source.Rows.Count; i++)
{
IRow rowTemp = sheet.CreateRow(rowIndex++);
for (var j = 0; j < source.Columns.Count; j++)
{
if (source.Rows[i][j].GetType().Name == "Decimal")
{
rowTemp.CreateCell(j).SetCellValue(Convert.ToDouble(source.Rows[i][j]));
}
else
{
rowTemp.CreateCell(j).SetCellValue(source.Rows[i][j].ToString());
}
}
}
for (int columnNum = 0; columnNum < source.Columns.Count; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;//获取当前列宽度
for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)//在这一列上循环行
{
IRow currentRow = sheet.GetRow(rowNum);
ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;//获取当前单元格的内容宽度
if (columnWidth < length + 1)
{
columnWidth = length + 1;
}//若当前单元格内容宽度大于列宽，则调整列宽为当前单元格宽度，后面的+1是我人为的将宽度增加一个字符
}
//sheet.SetColumnWidth(columnNum, columnWidth * 256);
if (columnWidth > 255)
{
columnWidth = 254;
}
else
{
sheet.SetColumnWidth(columnNum, columnWidth * 256);
}
}
flag++;
}
var ms = new NpoiMemoryStream();
ms.AllowClose = false;
book.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return ms;
}
