基于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
本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！