NPOI 是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目。使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。需要在NuGet下载NPOI包。
优点:读取Excel速度较快,读取方式操作灵活性。
读取Excel
/// <summary>
/// NPOI读Excel至DataTable
/// </summary>
/// <param name="fileName">Excel路径</param>
/// <param name="sheetName">工作表名默认读取第一个工作表</param>
/// <returns></returns>
public static System.Data.DataTable NPExcelToDataTable(string fileName,string sheetName="")
{
FileStream fs = null;
NPOI.SS.UserModel.IWorkbook workbook = null;
NPOI.SS.UserModel.ISheet sheet = null;
System.Data.DataTable dt = new System.Data.DataTable();
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(fs);
else if (fileName.IndexOf(".xls") > 0) // 97-2003版本
workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
//workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs);
if (sheetName != "") //是否有传入表名
{
sheet = workbook.GetSheet(sheetName);
}
else
{
sheet = workbook.GetSheetAt(0); //读取第一个sheet
}
int startRow = 0; //开始读取行数
if (sheet == null)
throw new Exception("未找到工作表");
dt.TableName = sheet.SheetName;
NPOI.SS.UserModel.IRow firstRow = sheet.GetRow(startRow); //第一行
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
NPOI.SS.UserModel.ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
if (dt.Columns.IndexOf(cellValue) > 0) //是否有重复列名
{
System.Data.DataColumn column = new System.Data.DataColumn(Convert.ToString("重复列名" + cellValue + i));
dt.Columns.Add(column);
}
else
{
System.Data.DataColumn column = new System.Data.DataColumn(cellValue);
dt.Columns.Add(column);
}
}
}
}
startRow = startRow + 1;
int rowCount = sheet.LastRowNum; //总行数
//填充行
for (int i = startRow; i <= rowCount; ++i)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
System.Data.DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(j);
if (cell != null) //同理,没有数据的单元格都默认是null
{
if (row.GetCell(j).CellType == NPOI.SS.UserModel.CellType.FORMULA) //是否为公式
{
try
{
dataRow[j] = cell.StringCellValue;
}
catch
{
if (NPOI.SS.UserModel.DateUtil.IsCellDateFormatted(cell)) //是否是日期
{
dataRow[j] = cell.DateCellValue;
}
else { dataRow[j] = cell.NumericCellValue; }
}
}
else { dataRow[j] = row.GetCell(j).ToString(); }
}
}
dt.Rows.Add(dataRow);
}
fs.Close();
return dt;
}
catch (Exception ex)
{
if (fs != null)
{
fs.Close();
}
throw new Exception(ex.Message);
}
}
测试读取1000*23Excel数据耗时2.5秒左右
写入Excel
在Office2007版使用AutoSizeColumn(int列号)时会报错。就用了NPOI自身自带为列宽指定宽度函数SetColumnWidth(int 列号,int宽度)设置了固定宽度。也可以遍历一下每一列的文字,求出文字的宽度,算出这一列的最大值,重新设置本列的宽度即可,跟自适应列宽的效果是一样的。
/// <summary>
/// NPOI将datatable写入Excel
/// </summary>
/// <param name="data">DataTable</param>
/// <param name="fileName">路径</param>
/// <param name="isColumnWritten">是否写入列名</param>
public static void NPDataTableToExcel(System.Data.DataTable data, string fileName, bool isColumnWritten)
{
NPOI.SS.UserModel.IWorkbook workbook = null;
NPOI.SS.UserModel.ISheet sheet = null;
FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
int rowIndex = 0; //行号
try
{
sheet = workbook.CreateSheet(data.TableName);
if (isColumnWritten == true) //写入DataTable的列名
{
NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex);
#region 设置样式
row.HeightInPoints = 25; //行高
NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle(); //创建样式对象
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; //水平居中
headStyle.FillForegroundColor = new NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT().GetIndex(); //具体数字代表的颜色看NPOI颜色对照表,全部在HSSFColor下面
headStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND; //前景色
headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; //单元格边框类型
headStyle.BottomBorderColor = new NPOI.HSSF.Util.HSSFColor.BLUE().GetIndex(); //单元格边框颜色
NPOI.SS.UserModel.IFont font = workbook.CreateFont(); //创建一个字体样式对象
font.FontHeightInPoints = 12; //字体大小
font.IsItalic = false; //斜体
//font.Boldweight = short.MaxValue; //字体加粗
font.Color = new NPOI.HSSF.Util.HSSFColor.BLACK().GetIndex();//颜色参考NPOI的颜色对照表(替换掉PINK())
headStyle.SetFont(font); //将字体样式赋给样式对象
#endregion
for (int j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
row.GetCell(j).CellStyle = headStyle;
}
rowIndex++;
}
// handling value.
for (int i = 0; i < data.Rows.Count; ++i)
{
NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex);
for (int j = 0; j < data.Columns.Count; ++j)
{
string a = data.Rows[i][j].ToString();
row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
}
rowIndex++;
}
for (int cl = 0; cl < data.Columns.Count; cl++)
{
//sheet.AutoSizeColumn(cl); //自适应列宽,会报错
sheet.SetColumnWidth(cl, 13 * 256); //列宽为13 (注意的是列的宽度为 列宽*256)
}
//CellRangeAddress四个参数为:起始行,结束行,起始列,结束列,单元格合并后,样式以左上角的单元格为准
//sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 10)); //合并单元格
workbook.Write(fs); //向打开的这个文件中写入数据
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (fs != null)
fs.Close();
}
}
调整列宽的代码:
//获取当前列的宽度,然后对比本列的长度,取最大值
for (int columnNum = 0; columnNum <= data.Columns.Count; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
for (int rowNum = 0; rowNum <= sheet.LastRowNum; rowNum++)
{
NPOI.SS.UserModel.IRow currentRow;
//当前行未被使用过
if (sheet.GetRow(rowNum) == null)
{
currentRow = sheet.CreateRow(rowNum);
}
else
{
currentRow = sheet.GetRow(rowNum);
}
if (currentRow.GetCell(columnNum) != null)
{
NPOI.SS.UserModel.ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length;
}
}
}
sheet.SetColumnWidth(columnNum, columnWidth * 256);
}
测试写入24000*23的数据Ofice97-2003版耗时2秒左右,Office2007版耗时1分多钟,未加上调整列宽。