分享我基于NPOI+ExcelReport实现的导入与导出EXCEL类库：ExcelUtility
1. ExcelUtility功能：
1.将数据导出到EXCEL(支持XLS,XLSX，支持多种类型模板，支持列宽自适应)
类名：ExcelUtility. Export
2.将EXCEL数据导入到数据对象中（DataTable、Dataset，支持XLS,XLSX）
类名：ExcelUtility. Import
类库项目文件结构如下图示：
2. ExcelUtility依赖组件：
1.NPOI 操作EXCEL核心类库
2.NPOI.Extend NPOI扩展功能
3. ExcelReport 基于NPOI的二次扩展，实现模板化导出功能
4. System.Windows.Forms 导出或导入时，弹出文件选择对话框（如果用在WEB中可以不需要，但我这里以CS端为主）
3.使用环境准备：
1.通过NUGet引用NPOI包、ExcelReport 包；（ExcelReport 存在BUG,可能需要用我项目中修复过后的DLL）
2.引用ExcelUtility类库；
4.具体使用方法介绍（示例代码，全部为测试方法）：
导出方法测试：
/// <summary>
/// 测试方法：测试将DataTable导出到EXCEL，无模板
/// </summary>
[TestMethod]
public void TestExportToExcelByDataTable()
{
DataTable dt = GetDataTable();
string excelPath = ExcelUtility.Export.ToExcel(dt, "导出结果");
Assert.IsTrue(File.Exists(excelPath));
}
结果如下图示：
/// <summary>
/// 测试方法：测试将DataTable导出到EXCEL，无模板，且指定导出的列名
/// </summary>
[TestMethod]
public void TestExportToExcelByDataTable2()
{
DataTable dt = GetDataTable();
string[] expColNames = { "Col1", "Col2", "Col3", "Col4", "Col5" };
string excelPath = ExcelUtility.Export.ToExcel(dt, "导出结果", null, expColNames);
Assert.IsTrue(File.Exists(excelPath));
}
结果如下图示：
/// <summary>
/// 测试方法：测试将DataTable导出到EXCEL，无模板，且指定导出的列名，以及导出列名的重命名
/// </summary>
[TestMethod]
public void TestExportToExcelByDataTable3()
{
DataTable dt = GetDataTable();
string[] expColNames = { "Col1", "Col2", "Col3", "Col4", "Col5" };
Dictionary<string, string> expColAsNames = new Dictionary<string, string>() {
{"Col1","列一"},
{"Col2","列二"},
{"Col3","列三"},
{"Col4","列四"},
{"Col5","列五"}
};
string excelPath = ExcelUtility.Export.ToExcel(dt, "导出结果", null, expColNames,expColAsNames);
Assert.IsTrue(File.Exists(excelPath));
}
结果如下图示：
/// <summary>
/// 测试方法：测试将DataTable导出到EXCEL，无模板，且指定导出列名的重命名
/// </summary>
[TestMethod]
public void TestExportToExcelByDataTable4()
{
DataTable dt = GetDataTable();
Dictionary<string, string> expColAsNames = new Dictionary<string, string>() {
{"Col1","列一"},
{"Col5","列五"}
};
string excelPath = ExcelUtility.Export.ToExcel(dt, "导出结果", null, null, expColAsNames);
Assert.IsTrue(File.Exists(excelPath));
}
结果如下图示：
/// <summary>
/// 测试方法：测试依据模板+DataTable来生成EXCEL
/// </summary>
[TestMethod]
public void TestExportToExcelWithTemplateByDataTable()
{
DataTable dt = GetDataTable();//获取数据
string templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "/excel.xlsx"; //获得EXCEL模板路径
SheetFormatterContainer<DataRow> formatterContainers = new SheetFormatterContainer<DataRow>(); //实例化一个模板数据格式化容器
PartFormatterBuilder partFormatterBuilder = new PartFormatterBuilder();//实例化一个局部元素格式化器
partFormatterBuilder.AddFormatter("Title", "IT学员");//将模板表格中Title的值设置为跨越IT学员
formatterContainers.AppendFormatterBuilder(partFormatterBuilder);//添加到工作薄格式容器中，注意只有添加进去了才会生效
CellFormatterBuilder cellFormatterBuilder = new CellFormatterBuilder();//实例化一个单元格格式化器
cellFormatterBuilder.AddFormatter("rptdate", DateTime.Today.ToString("yyyy-MM-dd HH:mm"));//将模板表格中rptdate的值设置为当前日期
formatterContainers.AppendFormatterBuilder(cellFormatterBuilder);//添加到工作薄格式容器中，注意只有添加进去了才会生效
//实例化一个表格格式化器，dt.Select()是将DataTable转换成DataRow[]，name表示的模板表格中第一行第一个单元格要填充的数据参数名
TableFormatterBuilder<DataRow> tableFormatterBuilder = new TableFormatterBuilder<DataRow>(dt.Select(), "name");
tableFormatterBuilder.AddFormatters(new Dictionary<string, Func<DataRow, object>>{
{"name",r=>r["Col1"]},//将模板表格中name对应DataTable中的列Col1
{"sex",r=>r["Col2"]},//将模板表格中sex对应DataTable中的列Col2
{"km",r=>r["Col3"]},//将模板表格中km对应DataTable中的列Col3
{"score",r=>r["Col4"]},//将模板表格中score对应DataTable中的列Col
{"result",r=>r["Col5"]}//将模板表格中result对应DataTable中的列Co5
});
formatterContainers.AppendFormatterBuilder(tableFormatterBuilder);//添加到工作薄格式容器中，注意只有添加进去了才会生效
string excelPath = ExcelUtility.Export.ToExcelWithTemplate<DataRow>(templateFilePath, "table", formatterContainers);
Assert.IsTrue(File.Exists(excelPath));
}
模板如下图示：
结果如下图示：
/// <summary>
/// 测试方法：测试依据模板+List来生成EXCEL
/// </summary>
[TestMethod]
public void TestExportToExcelWithTemplateByList()
{
List<Student> studentList = GetStudentList();//获取数据
string templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "/excel.xlsx"; //获得EXCEL模板路径
SheetFormatterContainer<Student> formatterContainers = new SheetFormatterContainer<Student>(); //实例化一个模板数据格式化容器
PartFormatterBuilder partFormatterBuilder = new PartFormatterBuilder();//实例化一个局部元素格式化器
partFormatterBuilder.AddFormatter("Title", "IT学员");//将模板表格中Title的值设置为跨越IT学员
formatterContainers.AppendFormatterBuilder(partFormatterBuilder);//添加到工作薄格式容器中，注意只有添加进去了才会生效
CellFormatterBuilder cellFormatterBuilder = new CellFormatterBuilder();//实例化一个单元格格式化器
cellFormatterBuilder.AddFormatter("rptdate", DateTime.Today.ToString("yyyy-MM-dd HH:mm"));//将模板表格中rptdate的值设置为当前日期
formatterContainers.AppendFormatterBuilder(cellFormatterBuilder);//添加到工作薄格式容器中，注意只有添加进去了才会生效
//实例化一个表格格式化器，studentList本身就是可枚举的无需转换，name表示的模板表格中第一行第一个单元格要填充的数据参数名
TableFormatterBuilder<Student> tableFormatterBuilder = new TableFormatterBuilder<Student>(studentList, "name");
tableFormatterBuilder.AddFormatters(new Dictionary<string, Func<Student, object>>{
{"name",r=>r.Name},//将模板表格中name对应Student对象中的属性Name
{"sex",r=>r.Sex},//将模板表格中sex对应Student对象中的属性Sex
{"km",r=>r.KM},//将模板表格中km对应Student对象中的属性KM
{"score",r=>r.Score},//将模板表格中score对应Student对象中的属性Score
{"result",r=>r.Result}//将模板表格中result对应Student对象中的属性Result
});
formatterContainers.AppendFormatterBuilder(tableFormatterBuilder);
string excelPath = ExcelUtility.Export.ToExcelWithTemplate<Student>(templateFilePath, "table", formatterContainers);
Assert.IsTrue(File.Exists(excelPath));
}
结果如下图示：（模板与上面相同）
/// <summary>
/// 测试方法：测试依据模板+DataTable来生成多表格EXCEL（注意：由于NPOI框架限制，目前仅支持模板文件格式为：xls）
/// </summary>
[TestMethod]
public void TestExportToRepeaterExcelWithTemplateByDataTable()
{
DataTable dt = GetDataTable();//获取数据
string templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "/excel2.xls"; //获得EXCEL模板路径
SheetFormatterContainer<DataRow> formatterContainers = new SheetFormatterContainer<DataRow>(); //实例化一个模板数据格式化容器
//实例化一个可重复表格格式化器，dt.Select()是将DataTable转换成DataRow[]，rpt_begin表示的模板表格开始位置参数名，rpt_end表示的模板表格结束位置参数名
RepeaterFormatterBuilder<DataRow> tableFormatterBuilder = new RepeaterFormatterBuilder<DataRow>(dt.Select(), "rpt_begin", "rpt_end");
tableFormatterBuilder.AddFormatters(new Dictionary<string, Func<DataRow, object>>{
{"sex",r=>r["Col2"]},//将模板表格中sex对应DataTable中的列Col2
{"km",r=>r["Col3"]},//将模板表格中km对应DataTable中的列Col3
{"score",r=>r["Col4"]},//将模板表格中score对应DataTable中的列Col
{"result",r=>r["Col5"]}//将模板表格中result对应DataTable中的列Co5
});
PartFormatterBuilder<DataRow> partFormatterBuilder2 = new PartFormatterBuilder<DataRow>();//实例化一个可嵌套的局部元素格式化器
partFormatterBuilder2.AddFormatter("name", r => r["Col1"]);//将模板表格中name对应DataTable中的列Col1
tableFormatterBuilder.AppendFormatterBuilder(partFormatterBuilder2);//添加到可重复表格格式化器中，作为其子格式化器
CellFormatterBuilder<DataRow> cellFormatterBuilder = new CellFormatterBuilder<DataRow>();//实例化一个可嵌套的单元格格式化器
cellFormatterBuilder.AddFormatter("rptdate", r => DateTime.Today.ToString("yyyy-MM-dd HH:mm"));//将模板表格中rptdate的值设置为当前日期
tableFormatterBuilder.AppendFormatterBuilder(cellFormatterBuilder);//添加到可重复表格格式化器中，作为其子格式化器
formatterContainers.AppendFormatterBuilder(tableFormatterBuilder);//添加到工作薄格式容器中，注意只有添加进去了才会生效
string excelPath = ExcelUtility.Export.ToExcelWithTemplate<DataRow>(templateFilePath, "multtable", formatterContainers);
Assert.IsTrue(File.Exists(excelPath));
}
模板如下图示：（注意：该模板仅支持XLS格式文件，XLSX下存在问题）
结果如下图示：
以下是模拟数据来源所定义的方法（配合测试）：
private DataTable GetDataTable()
{
DataTable dt = new DataTable();
for (int i = 1; i <= 6; i++)
{
if (i == 4)
{
dt.Columns.Add("Col" + i.ToString(), typeof(double));
}
else
{
dt.Columns.Add("Col" + i.ToString(), typeof(string));
}
}
for (int i = 1; i <= 10; i++)
{
dt.Rows.Add("Name" + i.ToString(), (i % 2) > 0 ? "男" : "女", "科目" + i.ToString(), i * new Random().Next(1, 5), "待定", Guid.NewGuid().ToString("N"));
}
return dt;
}
private List<Student> GetStudentList()
{
List<Student> studentList = new List<Student>();
for (int i = 1; i <= 10; i++)
{
studentList.Add(new Student
{
Name = "Name" + i.ToString(),
Sex = (i % 2) > 0 ? "男" : "女",
KM = "科目" + i.ToString(),
Score = i * new Random().Next(1, 5),
Result = "待定"
});
}
return studentList;
}
class Student
{
public string Name { get; set; }
public string Sex { get; set; }
public string KM { get; set; }
public double Score { get; set; }
public string Result { get; set; }
}
导入方法测试：
/// <summary>
/// 测试方法：测试将指定的EXCEL数据导入到DataTable
/// </summary>
[TestMethod]
public void TestImportToDataTableFromExcel()
{
//null表示由用户选择EXCEL文件路径，data表示要导入的sheet名,0表示数据标题行
DataTable dt= ExcelUtility.Import.ToDataTable(null, "data", 0);
Assert.AreNotEqual(0, dt.Rows.Count);
}
数据源文件内容如下图示：
下面贴出该类库主要源代码：
ExcelUtility.Export类：
using ExcelReport;
using ExcelUtility.Base;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace ExcelUtility
{
public sealed class Export
{
/// <summary>
/// 由DataSet导出Excel
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <param name="filePath">导出路径，可选</param>
/// <returns></returns>
public static string ToExcel(DataSet sourceDs, string filePath = null)
{
if (string.IsNullOrEmpty(filePath))
{
filePath = Common.GetSaveFilePath();
}
if (string.IsNullOrEmpty(filePath)) return null;
bool isCompatible = Common.GetIsCompatible(filePath);
IWorkbook workbook = Common.CreateWorkbook(isCompatible);
ICellStyle headerCellStyle = Common.GetCellStyle(workbook,true);
ICellStyle cellStyle = Common.GetCellStyle(workbook);
for (int i = 0; i < sourceDs.Tables.Count; i++)
{
DataTable table = sourceDs.Tables[i];
string sheetName = string.IsNullOrEmpty(table.TableName) ? "result" + i.ToString() : table.TableName;
ISheet sheet = workbook.CreateSheet(sheetName);
IRow headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in table.Columns)
{
ICell headerCell = headerRow.CreateCell(column.Ordinal);
headerCell.SetCellValue(column.ColumnName);
headerCell.CellStyle = headerCellStyle;
sheet.AutoSizeColumn(headerCell.ColumnIndex);
}
// handling value.
int rowIndex = 1;
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
ICell cell = dataRow.CreateCell(column.Ordinal);
cell.SetCellValue((row[column] ?? "").ToString());
cell.CellStyle = cellStyle;
Common.ReSizeColumnWidth(sheet, cell);
}
rowIndex++;
}
}
FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.Write(fs);
fs.Dispose();
workbook = null;
return filePath;
}
/// <summary>
/// 由DataTable导出Excel
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <param name="colAliasNames">导出的列名重命名数组</param>
/// <param name="sheetName">工作薄名称，可选</param>
/// <param name="filePath">导出路径，可选</param>
/// <returns></returns>
public static string ToExcel(DataTable sourceTable, string[] colAliasNames, string sheetName = "result", string filePath = null)
{
if (sourceTable.Rows.Count <= 0) return null;
if (string.IsNullOrEmpty(filePath))
{
filePath = Common.GetSaveFilePath();
}
if (string.IsNullOrEmpty(filePath)) return null;
if (colAliasNames == null || sourceTable.Columns.Count != colAliasNames.Length)
{
throw new ArgumentException("列名重命名数组与DataTable列集合不匹配。", "colAliasNames");
}
bool isCompatible = Common.GetIsCompatible(filePath);
IWorkbook workbook = Common.CreateWorkbook(isCompatible);
ICellStyle headerCellStyle = Common.GetCellStyle(workbook, true);
ICellStyle cellStyle = Common.GetCellStyle(workbook);
ISheet sheet = workbook.CreateSheet(sheetName);
IRow headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in sourceTable.Columns)
{
ICell headerCell = headerRow.CreateCell(column.Ordinal);
headerCell.SetCellValue(colAliasNames[column.Ordinal]);
headerCell.CellStyle = headerCellStyle;
sheet.AutoSizeColumn(headerCell.ColumnIndex);
}
// handling value.
int rowIndex = 1;
foreach (DataRow row in sourceTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
ICell cell = dataRow.CreateCell(column.Ordinal);
cell.SetCellValue((row[column] ?? "").ToString());
cell.CellStyle = cellStyle;
Common.ReSizeColumnWidth(sheet, cell);
}
rowIndex++;
}
FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.Write(fs);
fs.Dispose();
sheet = null;
headerRow = null;
workbook = null;
return filePath;
}
/// <summary>
/// 由DataGridView导出
/// </summary>
/// <param name="grid">要导出的DataGridView对象</param>
/// <param name="sheetName">工作薄名称，可选</param>
/// <param name="filePath">导出路径，可选</param>
/// <returns></returns>
public static string ToExcel(DataGridView grid, string sheetName = "result", string filePath = null)
{
if (grid.Rows.Count <= 0) return null;
if (string.IsNullOrEmpty(filePath))
{
filePath = Common.GetSaveFilePath();
}
if (string.IsNullOrEmpty(filePath)) return null;
bool isCompatible = Common.GetIsCompatible(filePath);
IWorkbook workbook = Common.CreateWorkbook(isCompatible);
ICellStyle headerCellStyle = Common.GetCellStyle(workbook, true);
ICellStyle cellStyle = Common.GetCellStyle(workbook);
ISheet sheet = workbook.CreateSheet(sheetName);
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < grid.Columns.Count; i++)
{
ICell headerCell = headerRow.CreateCell(i);
headerCell.SetCellValue(grid.Columns[i].HeaderText);
headerCell.CellStyle = headerCellStyle;
sheet.AutoSizeColumn(headerCell.ColumnIndex);
}
int rowIndex = 1;
foreach (DataGridViewRow row in grid.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
for (int n = 0; n < grid.Columns.Count; n++)
{
ICell cell = dataRow.CreateCell(n);
cell.SetCellValue((row.Cells[n].Value ?? "").ToString());
cell.CellStyle = cellStyle;
Common.ReSizeColumnWidth(sheet, cell);
}
rowIndex++;
}
FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.Write(fs);
fs.Dispose();
sheet = null;
headerRow = null;
workbook = null;
return filePath;
}
/// <summary>
/// 由DataTable导出Excel
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <param name="sheetName">工作薄名称，可选</param>
/// <param name="filePath">导出路径，可选</param>
/// <param name="colNames">需要导出的列名,可选</param>
/// <param name="colAliasNames">导出的列名重命名，可选</param>
/// <returns></returns>
public static string ToExcel(DataTable sourceTable, string sheetName = "result", string filePath = null, string[] colNames = null, IDictionary<string, string> colAliasNames = null)
{
if (sourceTable.Rows.Count <= 0) return null;
if (string.IsNullOrEmpty(filePath))
{
filePath = Common.GetSaveFilePath();
}
if (string.IsNullOrEmpty(filePath)) return null;
bool isCompatible = Common.GetIsCompatible(filePath);
IWorkbook workbook = Common.CreateWorkbook(isCompatible);
ICellStyle headerCellStyle = Common.GetCellStyle(workbook, true);
ICellStyle cellStyle = Common.GetCellStyle(workbook);
ISheet sheet = workbook.CreateSheet(sheetName);
IRow headerRow = sheet.CreateRow(0);
if (colNames == null || colNames.Length <= 0)
{
colNames = sourceTable.Columns.Cast<DataColumn>().OrderBy(c => c.Ordinal).Select(c => c.ColumnName).ToArray();
}
// handling header.
for (int i = 0; i < colNames.Length; i++)
{
ICell headerCell = headerRow.CreateCell(i);
if (colAliasNames != null && colAliasNames.ContainsKey(colNames[i]))
{
headerCell.SetCellValue(colAliasNames[colNames[i]]);
}
else
{
headerCell.SetCellValue(colNames[i]);
}
headerCell.CellStyle = headerCellStyle;
sheet.AutoSizeColumn(headerCell.ColumnIndex);
}
// handling value.
int rowIndex = 1;
foreach (DataRow row in sourceTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
for (int i = 0; i < colNames.Length; i++)
{
ICell cell = dataRow.CreateCell(i);
cell.SetCellValue((row[colNames[i]] ?? "").ToString());
cell.CellStyle = cellStyle;
Common.ReSizeColumnWidth(sheet, cell);
}
rowIndex++;
}
FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.Write(fs);
fs.Dispose();
sheet = null;
headerRow = null;
workbook = null;
return filePath;
}
/// <summary>
///由SheetFormatterContainer导出基于EXCEL模板的文件
/// </summary>
/// <param name="templatePath">模板路径</param>
/// <param name="sheetName">模板中使用的工作薄名称</param>
/// <param name="formatterContainer">模板数据格式化容器</param>
/// <param name="filePath">导出路径，可选</param>
/// <returns></returns>
public static string ToExcelWithTemplate<T>(string templatePath, string sheetName, SheetFormatterContainer<T> formatterContainer, string filePath = null)
{
if (!File.Exists(templatePath))
{
throw new FileNotFoundException(templatePath + "文件不存在！");
}
if (string.IsNullOrEmpty(filePath))
{
filePath = Common.GetSaveFilePath();
}
if (string.IsNullOrEmpty(filePath)) return null;
string templateConfigFilePath = Common.GetTemplateConfigFilePath(templatePath, false);
var workbookParameterContainer = new WorkbookParameterContainer();
workbookParameterContainer.Load(templateConfigFilePath);
SheetParameterContainer sheetParameterContainer = workbookParameterContainer[sheetName];
ExportHelper.ExportToLocal(templatePath, filePath, new SheetFormatter(sheetName, formatterContainer.GetFormatters(sheetParameterContainer)));
return filePath;
}
}
}
ExcelUtility.Import类：
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using ExcelUtility.Base;
namespace ExcelUtility
{
public sealed class Import
{
/// <summary>
/// 由Excel导入DataTable
/// </summary>
/// <param name="excelFileStream">Excel文件流</param>
/// <param name="sheetName">Excel工作表名称</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <param name="isCompatible">是否为兼容模式</param>
/// <returns>DataTable</returns>
public static DataTable ToDataTable(Stream excelFileStream, string sheetName, int headerRowIndex, bool isCompatible)
{
IWorkbook workbook = Common.CreateWorkbook(isCompatible, excelFileStream);
ISheet sheet = null;
int sheetIndex = -1;
if (int.TryParse(sheetName, out sheetIndex))
{
sheet = workbook.GetSheetAt(sheetIndex);
}
else
{
sheet = workbook.GetSheet(sheetName);
}
DataTable table = Common.GetDataTableFromSheet(sheet, headerRowIndex);
excelFileStream.Close();
workbook = null;
sheet = null;
return table;
}
/// <summary>
/// 由Excel导入DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径，为物理路径,可传空值</param>
/// <param name="sheetName">Excel工作表名称</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataTable</returns>
public static DataTable ToDataTable(string excelFilePath, string sheetName, int headerRowIndex)
{
if (string.IsNullOrEmpty(excelFilePath))
{
excelFilePath = Common.GetOpenFilePath();
}
if (string.IsNullOrEmpty(excelFilePath))
{
return null;
}
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
bool isCompatible = Common.GetIsCompatible(excelFilePath);
return ToDataTable(stream, sheetName, headerRowIndex, isCompatible);
}
}
/// <summary>
/// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
/// </summary>
/// <param name="excelFileStream">Excel文件流</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <param name="isCompatible">是否为兼容模式</param>
/// <returns>DataSet</returns>
public static DataSet ToDataSet(Stream excelFileStream, int headerRowIndex, bool isCompatible)
{
DataSet ds = new DataSet();
IWorkbook workbook = Common.CreateWorkbook(isCompatible, excelFileStream);
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
DataTable table = Common.GetDataTableFromSheet(sheet, headerRowIndex);
ds.Tables.Add(table);
}
excelFileStream.Close();
workbook = null;
return ds;
}
/// <summary>
/// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径，为物理路径。可传空值</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataSet</returns>
public static DataSet ToDataSet(string excelFilePath, int headerRowIndex)
{
if (string.IsNullOrEmpty(excelFilePath))
{
excelFilePath = Common.GetOpenFilePath();
}
if (string.IsNullOrEmpty(excelFilePath))
{
return null;
}
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
bool isCompatible = Common.GetIsCompatible(excelFilePath);
return ToDataSet(stream, headerRowIndex, isCompatible);
}
}
}
}
Common类根据单元格内容重新设置列宽ReSizeColumnWidth
/// <summary>
/// 根据单元格内容重新设置列宽
/// </summary>
/// <param name="sheet"></param>
/// <param name="cell"></param>
public static void ReSizeColumnWidth(ISheet sheet, ICell cell)
{
int cellLength = (Encoding.Default.GetBytes(cell.ToString()).Length + 5) * 256;
const int maxLength = 255 * 256;
if (cellLength > maxLength)
{
cellLength = maxLength;
}
int colWidth = sheet.GetColumnWidth(cell.ColumnIndex);
if (colWidth < cellLength)
{
sheet.SetColumnWidth(cell.ColumnIndex, cellLength);
}
}
注意这个方法中，列宽自动设置最大宽度为255个字符宽度。
/// <summary>
/// 创建表格样式
/// </summary>
/// <param name="sheet"></param>
/// <returns></returns>
public static ICellStyle GetCellStyle(IWorkbook workbook, bool isHeaderRow = false)
{
ICellStyle style = workbook.CreateCellStyle();
if (isHeaderRow)
{
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
IFont f = workbook.CreateFont();
f.Boldweight = (short)FontBoldWeight.Bold;
style.SetFont(f);
}
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
return style;
}
发文时，部份方法代码已经更新了，所以实际效果以GIT项目中的为准。
该类库源码已分享到该路径中：http://git.oschina.net/zuowj/ExcelUtility GIT Repository路径：git@git.oschina.net:zuowj/ExcelUtility.git
