【個人使用.Net類庫】（3）Excel文件操作類（基於NPOI），.netnpoi
Web開發工作中經常要根據業務的需要生成對應的報表。經常采用的方法如下：
- 將DataTable導出至Excel文件;
- 讀取模板Excel文件;
- 修改模板Excel文件對應的內容。
因此，便想到封裝一個基於NPOI的Excel操作類（至於為什麼不用Excel組件，那是因為Excel組件效率低且必須安裝Office），所完成的功能大致如上所示，這樣平時的報表開發效率就比原來高效很多。
首先是DataTable導出至Excel文件，代碼關鍵部分有注釋說明，具體代碼如下所示：
/// <summary>
/// 從DataTable中將數據導出到Excel文件
/// </summary>
/// <param name="dtSource">提供導出數據的DataTable</param>
/// <param name="headerText">表頭文本</param>
/// <returns></returns>
public static MemoryStream ExportDataTable(DataTable dtSource, string headerText)
{
//創建工作表
var workbook = new HSSFWorkbook();
//創建sheet頁
var sheet = workbook.CreateSheet();
#region 添加Excel文件屬性信息
var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "ZWKJ";
workbook.DocumentSummaryInformation = dsi;
var si = PropertySetFactory.CreateSummaryInformation();
si.Author = "鞠小軍";
si.ApplicationName = "使用NPOI創建的Excel文件";
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
#endregion
//設置日期格式
var dateStyle = workbook.CreateCellStyle();
var format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列寬
var columnWidth = new int[dtSource.Columns.Count];
//遍歷DataTable的列
foreach (DataColumn column in dtSource.Columns)
{
columnWidth[column.Ordinal] = Encoding.GetEncoding(936).GetBytes(column.ColumnName).Length;
}
//遍歷所有的Row,若當前Row內容長度超出列名長度，則將此列的長度設為該Row內容長度
for (var i = 0; i < dtSource.Rows.Count; i++)
{
for (var j = 0; j < dtSource.Columns.Count; j++)
{
var currentRowLength = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (currentRowLength > columnWidth[j])
columnWidth[j] = currentRowLength;
}
}
var rowIndex = 0;
#region 表頭及樣式
var headRow = sheet.CreateRow(0);
headRow.HeightInPoints = 25;
headRow.CreateCell(0).SetCellValue(headerText);
var headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
var font = workbook.CreateFont();
font.Boldweight = 700;
font.FontHeightInPoints = 20;
headStyle.SetFont(font);
headRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
#endregion
#region 列頭及樣式
var columnRow = sheet.CreateRow(1);
var columnStyle = workbook.CreateCellStyle();
columnStyle.Alignment = HorizontalAlignment.CENTER;
var columnFont = workbook.CreateFont();
columnFont.Boldweight = 700;
columnFont.FontHeightInPoints = 10;
columnStyle.SetFont(columnFont);
foreach (DataColumn column in dtSource.Columns)
{
//設置列頭內容
columnRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
//設置列頭樣式
columnRow.GetCell(column.Ordinal).CellStyle = columnStyle;
//設置列寬
sheet.SetColumnWidth(column.Ordinal, (columnWidth[column.Ordinal] + 1) * 256);
}
#endregion
rowIndex = 2;
foreach (DataRow row in dtSource.Rows)
{
if(rowIndex == 65535)
sheet = workbook.CreateSheet();
#region 填充數據
var dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
var newCell = dataRow.CreateCell(column.Ordinal);
var cellValue = row[column].ToString();
switch (column.DataType.ToString())
{
//字符串類型
case "System.String":
double result;
if (double.TryParse(cellValue, out result))
{
newCell.SetCellValue(result);
break;
}
newCell.SetCellValue(cellValue);
break;
//DateTime類型
case "System.DateTime":
DateTime tmpdt;
if (DateTime.TryParse(cellValue, out tmpdt))
{
newCell.SetCellValue(tmpdt);
newCell.CellStyle = dateStyle;
break;
}
newCell.SetCellValue(cellValue);
break;
//布爾類型
case "System.Boolean":
bool boolV;
if (bool.TryParse(cellValue, out boolV))
{
newCell.SetCellValue(boolV);
break;
}
newCell.SetCellValue(cellValue);
break;
//整型
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV;
if (int.TryParse(cellValue, out intV))
{
newCell.SetCellValue(intV);
break;
}
newCell.SetCellValue(cellValue);
break;
//浮點型
case "System.Decimal":
case "System.Double":
double doubV;
if (double.TryParse(cellValue, out doubV))
{
newCell.SetCellValue(doubV);
break;
}
newCell.SetCellValue(cellValue);
break;
//空值處理
case "System.DBNull":
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
rowIndex++;
#endregion
}
using (var ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}
}
/// <summary>
/// DataTable導出到Excel文件
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="headerText">表頭</param>
/// <param name="fileName">Excel文件保存位置(包含文件名)</param>
public static void ExportDataTableToExcel(DataTable dtSource, string headerText,string fileName)
{
using (var ms = ExportDataTable(dtSource, headerText))
{
using (var fileStream = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
var data = ms.ToArray();
fileStream.Write(data,0,data.Length);
fileStream.Flush();
}
}
}
測試從DataTable數據導出至Excel的表如下圖所示（限於篇幅，圖中只有一部分數據）：
![]()
/// <summary>
/// 將sheet頁導出至DataTable中
/// </summary>
/// <param name="sheet">需要導出的sheet</param>
/// <param name="headerRowIndex">表頭所在行號,-1表示沒有列頭</param>
/// <returns>返回的DataTable</returns>
public static DataTable ImportSheetToDataTable(HSSFSheet sheet, int headerRowIndex)
{
var dataTable = new DataTable();
try
{
HSSFRow headerRow;
int cellCount;
if (headerRowIndex < 0)
{
headerRow = (HSSFRow)sheet.GetRow(0);
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
var column = new DataColumn(i.ToString(CultureInfo.InvariantCulture));
dataTable.Columns.Add(column);
}
}
else
{
headerRow = (HSSFRow)sheet.GetRow(headerRowIndex+1);
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
if (headerRow.GetCell(i) == null)
{
if (dataTable.Columns.IndexOf(i.ToString(CultureInfo.InvariantCulture)) > 0)
{
var column = new DataColumn("重復列名" + i);
dataTable.Columns.Add(column);
}
else
{
var column = new DataColumn(Convert.ToString(i));
dataTable.Columns.Add(column);
}
}
else if (dataTable.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
{
var column = new DataColumn("重復列名" + i);
dataTable.Columns.Add(column);
}
else
{
var column = new DataColumn(headerRow.GetCell(i).ToString());
dataTable.Columns.Add(column);
}
}
}
for (var i = (headerRowIndex + 2); i <= sheet.LastRowNum; i++)
{
try
{
HSSFRow row;
if (sheet.GetRow(i) == null)
{
row = sheet.CreateRow(i) as HSSFRow;
}
else
{
row = sheet.GetRow(i) as HSSFRow;
}
var dataRow = dataTable.NewRow();
if (row != null)
for (int j = row.FirstCellNum; j <= cellCount; j++)
{
try
{
if (row.GetCell(j) == null) continue;
switch (row.GetCell(j).CellType)
{
case CellType.STRING:
var str = row.GetCell(j).StringCellValue;
if (!string.IsNullOrEmpty(str))
{
dataRow[j] = str;
}
else
{
dataRow[j] = null;
}
break;
case CellType.NUMERIC:
if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
{
dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
}
else
{
dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
}
break;
case CellType.BOOLEAN:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.ERROR:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
case CellType.FORMULA:
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.STRING:
var strFormula = row.GetCell(j).StringCellValue;
if (!string.IsNullOrEmpty(strFormula))
{
dataRow[j] = strFormula;
}
else
{
dataRow[j] = null;
}
break;
case CellType.NUMERIC:
dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.BOOLEAN:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.ERROR:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
dataRow[j] = "";
break;
}
break;
default:
dataRow[j] = "";
break;
}
}
catch (Exception)
{
//這裡添加異常異常日志記錄
return null;
}
}
dataTable.Rows.Add(dataRow);
}
catch (Exception)
{
//這裡添加異常異常日志記錄
return null;
}
}
}
catch (Exception)
{
//這裡添加異常異常日志記錄
return null;
}
return dataTable;
}
/// <summary>
/// 將Excel指定sheet數據導出至DataTable中
/// </summary>
/// <param name="fileName"></param>
/// <param name="sheetIndex"></param>
/// <param name="headerRowIndex"></param>
/// <returns>返回的DataTable</returns>
public static DataTable ImportExcelToDataTable(string fileName, int sheetIndex, int headerRowIndex)
{
HSSFWorkbook workbook;
using (var fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
workbook = new HSSFWorkbook(fileStream);
}
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(sheetIndex);
var dataTable = ImportSheetToDataTable(sheet, headerRowIndex);
workbook = null;
sheet = null;
return dataTable;
}
測試讀取Excel模板文件使用上面生成的test.xls文件，測試代碼如下所示：
![]()
/// <summary>
/// 更新Excel表格
/// </summary>
/// <param name="outputFile">需更新的excel表格路徑</param>
/// <param name="sheetname">sheet名</param>
/// <param name="updateData">需更新的數據</param>
/// <param name="coluid">需更新的列號</param>
/// <param name="rowid">需更新的開始行號</param>
public static void UpdateExcel(string outputFile, string sheetname, double[] updateData, int coluid, int rowid)
{
var readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
var hssfworkbook = new HSSFWorkbook(readfile);
var sheet1 = hssfworkbook.GetSheet(sheetname);
for (var i = 0; i < updateData.Length; i++)
{
try
{
if (sheet1.GetRow(i + rowid) == null)
{
sheet1.CreateRow(i + rowid);
}
if (sheet1.GetRow(i + rowid).GetCell(coluid) == null)
{
sheet1.GetRow(i + rowid).CreateCell(coluid);
}
sheet1.GetRow(i + rowid).GetCell(coluid).SetCellValue(updateData[i]);
}
catch (Exception ex)
{
//這裡添加異常異常日志記錄
throw;
}
}
try
{
readfile.Close();
var writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write);
hssfworkbook.Write(writefile);
writefile.Close();
}
catch (Exception ex)
{
//這裡添加異常異常日志記錄
}
}
測試代碼如下（使用上面生成的test.xls文件）：
![]()
using System;
using System.Data;
using System.Globalization;
using System.IO;
using System.Text;
using NPOI.HPSF;
using NPOI.SS.Formula.Eval;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.Collections;
namespace DotNetCommon.Excel
{
/// <summary>
/// 類說明：Excel操作靜態類（基於NPOI組件）
/// 編碼人：鞠小軍
/// 聯系方式：[email protected]
/// </summary>
public static class ExcelHelper
{
/// <summary>
/// 獲取Excel文件sheet頁總數
/// </summary>
/// <param name="outputFile">要讀取Excel文件</param>
/// <returns></returns>
public static int GetSheetNumber(string outputFile)
{
var number = 0;
try
{
var readFile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
var workbook = new HSSFWorkbook(readFile);
number = workbook.NumberOfSheets;
}
catch (Exception)
{
//這裡添加異常異常日志記錄
return 0;
}
return number;
}
/// <summary>
/// 獲取Excel文件sheet名稱集合
/// </summary>
/// <param name="outputFile">要讀取Excel文件</param>
/// <returns></returns>
public static ArrayList GetSheetName(string outputFile)
{
var arrayList = new ArrayList();
try
{
var readFile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
var workbook = new HSSFWorkbook(readFile);
for (var i = 0; i < workbook.NumberOfSheets; i++)
{
arrayList.Add(workbook.GetSheetName(i));
}
}
catch (Exception)
{
//這裡添加異常異常日志記錄
return null;
}
return arrayList;
}
/// <summary>
/// 從DataTable中將數據導出到Excel文件
/// </summary>
/// <param name="dtSource">提供導出數據的DataTable</param>
/// <param name="headerText">表頭文本</param>
/// <returns></returns>
public static MemoryStream ExportDataTable(DataTable dtSource, string headerText)
{
//創建工作表
var workbook = new HSSFWorkbook();
//創建sheet頁
var sheet = workbook.CreateSheet();
#region 添加Excel文件屬性信息
var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "ZWKJ";
workbook.DocumentSummaryInformation = dsi;
var si = PropertySetFactory.CreateSummaryInformation();
si.Author = "鞠小軍";
si.ApplicationName = "使用NPOI創建的Excel文件";
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
#endregion
//設置日期格式
var dateStyle = workbook.CreateCellStyle();
var format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列寬
var columnWidth = new int[dtSource.Columns.Count];
//遍歷DataTable的列
foreach (DataColumn column in dtSource.Columns)
{
columnWidth[column.Ordinal] = Encoding.GetEncoding(936).GetBytes(column.ColumnName).Length;
}
//遍歷所有的Row,若當前Row內容長度超出列名長度，則將此列的長度設為該Row內容長度
for (var i = 0; i < dtSource.Rows.Count; i++)
{
for (var j = 0; j < dtSource.Columns.Count; j++)
{
var currentRowLength = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (currentRowLength > columnWidth[j])
columnWidth[j] = currentRowLength;
}
}
var rowIndex = 0;
#region 表頭及樣式
var headRow = sheet.CreateRow(0);
headRow.HeightInPoints = 25;
headRow.CreateCell(0).SetCellValue(headerText);
var headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
var font = workbook.CreateFont();
font.Boldweight = 700;
font.FontHeightInPoints = 20;
headStyle.SetFont(font);
headRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
#endregion
#region 列頭及樣式
var columnRow = sheet.CreateRow(1);
var columnStyle = workbook.CreateCellStyle();
columnStyle.Alignment = HorizontalAlignment.CENTER;
var columnFont = workbook.CreateFont();
columnFont.Boldweight = 700;
columnFont.FontHeightInPoints = 10;
columnStyle.SetFont(columnFont);
foreach (DataColumn column in dtSource.Columns)
{
//設置列頭內容
columnRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
//設置列頭樣式
columnRow.GetCell(column.Ordinal).CellStyle = columnStyle;
//設置列寬
sheet.SetColumnWidth(column.Ordinal, (columnWidth[column.Ordinal] + 1) * 256);
}
#endregion
rowIndex = 2;
foreach (DataRow row in dtSource.Rows)
{
if(rowIndex == 65535)
sheet = workbook.CreateSheet();
#region 填充數據
var dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
var newCell = dataRow.CreateCell(column.Ordinal);
var cellValue = row[column].ToString();
switch (column.DataType.ToString())
{
//字符串類型
case "System.String":
double result;
if (double.TryParse(cellValue, out result))
{
newCell.SetCellValue(result);
break;
}
newCell.SetCellValue(cellValue);
break;
//DateTime類型
case "System.DateTime":
DateTime tmpdt;
if (DateTime.TryParse(cellValue, out tmpdt))
{
newCell.SetCellValue(tmpdt);
newCell.CellStyle = dateStyle;
break;
}
newCell.SetCellValue(cellValue);
break;
//布爾類型
case "System.Boolean":
bool boolV;
if (bool.TryParse(cellValue, out boolV))
{
newCell.SetCellValue(boolV);
break;
}
newCell.SetCellValue(cellValue);
break;
//整型
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV;
if (int.TryParse(cellValue, out intV))
{
newCell.SetCellValue(intV);
break;
}
newCell.SetCellValue(cellValue);
break;
//浮點型
case "System.Decimal":
case "System.Double":
double doubV;
if (double.TryParse(cellValue, out doubV))
{
newCell.SetCellValue(doubV);
break;
}
newCell.SetCellValue(cellValue);
break;
//空值處理
case "System.DBNull":
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
rowIndex++;
#endregion
}
using (var ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}
}
/// <summary>
/// DataTable導出到Excel文件
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="headerText">表頭</param>
/// <param name="fileName">Excel文件保存位置(包含文件名)</param>
public static void ExportDataTableToExcel(DataTable dtSource, string headerText,string fileName)
{
using (var ms = ExportDataTable(dtSource, headerText))
{
using (var fileStream = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
var data = ms.ToArray();
fileStream.Write(data,0,data.Length);
fileStream.Flush();
}
}
}
/// <summary>
/// 將sheet頁導出至DataTable中
/// </summary>
/// <param name="sheet">需要導出的sheet</param>
/// <param name="headerRowIndex">表頭所在行號,-1表示沒有列頭</param>
/// <returns>返回的DataTable</returns>
public static DataTable ImportSheetToDataTable(HSSFSheet sheet, int headerRowIndex)
{
var dataTable = new DataTable();
try
{
HSSFRow headerRow;
int cellCount;
if (headerRowIndex < 0)
{
headerRow = (HSSFRow)sheet.GetRow(0);
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
var column = new DataColumn(i.ToString(CultureInfo.InvariantCulture));
dataTable.Columns.Add(column);
}
}
else
{
headerRow = (HSSFRow)sheet.GetRow(headerRowIndex+1);
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
if (headerRow.GetCell(i) == null)
{
if (dataTable.Columns.IndexOf(i.ToString(CultureInfo.InvariantCulture)) > 0)
{
var column = new DataColumn("重復列名" + i);
dataTable.Columns.Add(column);
}
else
{
var column = new DataColumn(Convert.ToString(i));
dataTable.Columns.Add(column);
}
}
else if (dataTable.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
{
var column = new DataColumn("重復列名" + i);
dataTable.Columns.Add(column);
}
else
{
var column = new DataColumn(headerRow.GetCell(i).ToString());
dataTable.Columns.Add(column);
}
}
}
for (var i = (headerRowIndex + 2); i <= sheet.LastRowNum; i++)
{
try
{
HSSFRow row;
if (sheet.GetRow(i) == null)
{
row = sheet.CreateRow(i) as HSSFRow;
}
else
{
row = sheet.GetRow(i) as HSSFRow;
}
var dataRow = dataTable.NewRow();
if (row != null)
for (int j = row.FirstCellNum; j <= cellCount; j++)
{
try
{
if (row.GetCell(j) == null) continue;
switch (row.GetCell(j).CellType)
{
case CellType.STRING:
var str = row.GetCell(j).StringCellValue;
if (!string.IsNullOrEmpty(str))
{
dataRow[j] = str;
}
else
{
dataRow[j] = null;
}
break;
case CellType.NUMERIC:
if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
{
dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
}
else
{
dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
}
break;
case CellType.BOOLEAN:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.ERROR:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
case CellType.FORMULA:
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.STRING:
var strFormula = row.GetCell(j).StringCellValue;
if (!string.IsNullOrEmpty(strFormula))
{
dataRow[j] = strFormula;
}
else
{
dataRow[j] = null;
}
break;
case CellType.NUMERIC:
dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.BOOLEAN:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.ERROR:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
dataRow[j] = "";
break;
}
break;
default:
dataRow[j] = "";
break;
}
}
catch (Exception)
{
//這裡添加異常異常日志記錄
return null;
}
}
dataTable.Rows.Add(dataRow);
}
catch (Exception)
{
//這裡添加異常異常日志記錄
return null;
}
}
}
catch (Exception)
{
//這裡添加異常異常日志記錄
return null;
}
return dataTable;
}
/// <summary>
/// 將Excel指定sheet數據導出至DataTable中
/// </summary>
/// <param name="fileName"></param>
/// <param name="sheetIndex"></param>
/// <param name="headerRowIndex"></param>
/// <returns>返回的DataTable</returns>
public static DataTable ImportExcelToDataTable(string fileName, int sheetIndex, int headerRowIndex)
{
HSSFWorkbook workbook;
using (var fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
workbook = new HSSFWorkbook(fileStream);
}
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(sheetIndex);
var dataTable = ImportSheetToDataTable(sheet, headerRowIndex);
workbook = null;
sheet = null;
return dataTable;
}
#region 更新excel中的數據
/// <summary>
/// 更新Excel表格
/// </summary>
/// <param name="outputFile">需更新的excel表格路徑</param>
/// <param name="sheetname">sheet名</param>
/// <param name="updateData">需更新的數據</param>
/// <param name="coluid">需更新的列號</param>
/// <param name="rowid">需更新的開始行號</param>
public static void UpdateExcel(string outputFile, string sheetname, string[] updateData, int coluid, int rowid)
{
FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
for (int i = 0; i < updateData.Length; i++)
{
try
{
if (sheet1.GetRow(i + rowid) == null)
{
sheet1.CreateRow(i + rowid);
}
if (sheet1.GetRow(i + rowid).GetCell(coluid) == null)
{
sheet1.GetRow(i + rowid).CreateCell(coluid);
}
sheet1.GetRow(i + rowid).GetCell(coluid).SetCellValue(updateData[i]);
}
catch (Exception ex)
{
// 這裡添加異常異常日志記錄
throw;
}
}
try
{
readfile.Close();
FileStream writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write);
hssfworkbook.Write(writefile);
writefile.Close();
}
catch (Exception ex)
{
// 這裡添加異常異常日志記錄
}
}
/// <summary>
/// 更新Excel表格
/// </summary>
/// <param name="outputFile">需更新的excel表格路徑</param>
/// <param name="sheetname">sheet名</param>
/// <param name="updateData">需更新的數據</param>
/// <param name="coluids">需更新的列號</param>
/// <param name="rowid">需更新的開始行號</param>
public static void UpdateExcel(string outputFile, string sheetname, string[][] updateData, int[] coluids, int rowid)
{
var readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
var hssfworkbook = new HSSFWorkbook(readfile);
readfile.Close();
var sheet1 = hssfworkbook.GetSheet(sheetname);
for (var j = 0; j < coluids.Length; j++)
{
for (var i = 0; i < updateData[j].Length; i++)
{
try
{
if (sheet1.GetRow(i + rowid) == null)
{
sheet1.CreateRow(i + rowid);
}
if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null)
{
sheet1.GetRow(i + rowid).CreateCell(coluids[j]);
}
sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]);
}
catch (Exception ex)
{
// 這裡添加異常異常日志記錄
}
}
}
try
{
var writefile = new FileStream(outputFile, FileMode.Create);
hssfworkbook.Write(writefile);
writefile.Close();
}
catch (Exception ex)
{
//這裡添加異常異常日志記錄
}
}
/// <summary>
/// 更新Excel表格
/// </summary>
/// <param name="outputFile">需更新的excel表格路徑</param>
/// <param name="sheetname">sheet名</param>
/// <param name="updateData">需更新的數據</param>
/// <param name="coluid">需更新的列號</param>
/// <param name="rowid">需更新的開始行號</param>
public static void UpdateExcel(string outputFile, string sheetname, double[] updateData, int coluid, int rowid)
{
var readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
var hssfworkbook = new HSSFWorkbook(readfile);
var sheet1 = hssfworkbook.GetSheet(sheetname);
for (var i = 0; i < updateData.Length; i++)
{
try
{
if (sheet1.GetRow(i + rowid) == null)
{
sheet1.CreateRow(i + rowid);
}
if (sheet1.GetRow(i + rowid).GetCell(coluid) == null)
{
sheet1.GetRow(i + rowid).CreateCell(coluid);
}
sheet1.GetRow(i + rowid).GetCell(coluid).SetCellValue(updateData[i]);
}
catch (Exception ex)
{
//這裡添加異常異常日志記錄
throw;
}
}
try
{
readfile.Close();
var writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write);
hssfworkbook.Write(writefile);
writefile.Close();
}
catch (Exception ex)
{
//這裡添加異常異常日志記錄
}
}
/// <summary>
/// 更新Excel表格
/// </summary>
/// <param name="outputFile">需更新的excel表格路徑</param>
/// <param name="sheetname">sheet名</param>
/// <param name="updateData">需更新的數據</param>
/// <param name="coluids">需更新的列號</param>
/// <param name="rowid">需更新的開始行號</param>
public static void UpdateExcel(string outputFile, string sheetname, double[][] updateData, int[] coluids, int rowid)
{
var readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
var hssfworkbook = new HSSFWorkbook(readfile);
readfile.Close();
var sheet1 = hssfworkbook.GetSheet(sheetname);
for (var j = 0; j < coluids.Length; j++)
{
for (var i = 0; i < updateData[j].Length; i++)
{
try
{
if (sheet1.GetRow(i + rowid) == null)
{
sheet1.CreateRow(i + rowid);
}
if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null)
{
sheet1.GetRow(i + rowid).CreateCell(coluids[j]);
}
sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]);
}
catch (Exception ex)
{
//這裡添加異常異常日志記錄
}
}
}
try
{
var writefile = new FileStream(outputFile, FileMode.Create);
hssfworkbook.Write(writefile);
writefile.Close();
}
catch (Exception)
{
//這裡添加異常異常日志記錄
}
}
#endregion
}
}
測試使用的文件打包，如下所示：
測試用打包文件下載
net npoi 導出excel表
建議你檢查一下dt裡的列名是否與SONumber 一致
PS：會不會是你的NPOI的問題
寫一段NPOI20讀寫excel2007的例子？net的
///寫...
public static void ExportXls(HttpContext context, DataTable dt, string columns)
{
HSSFWorkbook book = new HSSFWorkbook();
//超過5W條分Sheet
int sheetCount = dt.Rows.Count % 50000 == 0 ? dt.Rows.Count / 50000 : dt.Rows.Count / 50000 + 1;
ISheet sheet = null;
IRow row = null;
ICell cell = null;
for (int i = 0; i != sheetCount; i++)
{
sheet = book.CreateSheet(string.Format("RCList{0}", i + 1));
row = sheet.GetRow(0) ?? sheet.CreateRow(0);
for (int k = 0; k != dt.Columns.Count; k++)
{
cell = row.GetCell(k) ?? row.CreateCell(k);
cell.SetCellValue(dt.Columns[k].ColumnName);
}
}
for (int i = 0; i != dt.Rows.Count; i++)
{
sheet = book.GetSheetAt(i / 50000);
row = sheet.GetRow(i - (i / 50000) * 50000 + 1) ?? sheet.CreateRow(i - (i / 50000) * 50000 + 1);
for (int k = 0; k != dt.Columns.Count; k++)
{
cell = row.GetCell(k) ?? row.CreateCell(k);
if (dt.Rows[i][k] != DBNull.Value)
{
cell.SetCellValue(dt.Rows[i][k].ToString());
}
}
}
}...余下全文>>
