NPOI帮助类
/// <summary>
/// Excel操作类
/// </summary>
public static class ExcelOperation
{
/// Excel操作类
/// </summary>
public static class ExcelOperation
{
#region//Public-Method
/// <summary>
/// 获取excel列名(列名为空也会返回一个空列名)
/// </summary>
/// <param name="path">路径</param>
/// <param name="sheetIndex">表下标</param>
/// <param name="fieldRowIndex">从第几行开始</param>
/// <returns></returns>
public static List<string> GetCNames(string path, int sheetIndex = 0, int fieldRowIndex = 0)
{
//列名
List<string> cNames = new List<string>();
try
{
//获取文件流
using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook workbook;
/// 获取excel列名(列名为空也会返回一个空列名)
/// </summary>
/// <param name="path">路径</param>
/// <param name="sheetIndex">表下标</param>
/// <param name="fieldRowIndex">从第几行开始</param>
/// <returns></returns>
public static List<string> GetCNames(string path, int sheetIndex = 0, int fieldRowIndex = 0)
{
//列名
List<string> cNames = new List<string>();
try
{
//获取文件流
using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook workbook;
//获取excel的文件类型
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
//
ISheet sheet = null;
//获取工作表,默认获取第一张
if (workbook.NumberOfSheets > sheetIndex && workbook.NumberOfSheets > 0)
{
//获取excel表格
sheet = workbook.GetSheetAt(sheetIndex);
}
else
{
System.Windows.Forms.MessageBox.Show("索引超出界限或该文件表格为空！");
}
//是否已经获取工作表，如果没有则直接返回
if (sheet == null)
{
return null;
}
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
//
ISheet sheet = null;
//获取工作表,默认获取第一张
if (workbook.NumberOfSheets > sheetIndex && workbook.NumberOfSheets > 0)
{
//获取excel表格
sheet = workbook.GetSheetAt(sheetIndex);
}
else
{
System.Windows.Forms.MessageBox.Show("索引超出界限或该文件表格为空！");
}
//是否已经获取工作表，如果没有则直接返回
if (sheet == null)
{
return null;
}
#region//获取表头,文本类型
//获取列名(默认获取第一行)
if (fieldRowIndex > sheet.LastRowNum)//判断行标是否超界
{
return null;
}
IRow headRow = sheet.GetRow(fieldRowIndex);
//列数
int cellcount = headRow.LastCellNum;
for (int i = 0; i < cellcount; i++)
{
//获取行中的i个元素
ICell cell = headRow.GetCell(i);
//将列名添加至数组
cNames.Add(cell.ToString());
}
#endregion
}
return cNames;
}
catch
{
return null;
}
}
//获取列名(默认获取第一行)
if (fieldRowIndex > sheet.LastRowNum)//判断行标是否超界
{
return null;
}
IRow headRow = sheet.GetRow(fieldRowIndex);
//列数
int cellcount = headRow.LastCellNum;
for (int i = 0; i < cellcount; i++)
{
//获取行中的i个元素
ICell cell = headRow.GetCell(i);
//将列名添加至数组
cNames.Add(cell.ToString());
}
#endregion
}
return cNames;
}
catch
{
return null;
}
}
/// <summary>
/// 获取excel列名
/// </summary>
/// <param name="path">路径</param>
/// <param name="sheetName">表名</param>
/// <param name="fieldRowIndex">从第几行开始</param>
/// <returns></returns>
public static List<string> GetCNames(string path, string sheetName, int fieldRowIndex = 0)
{
//列名
List<string> cNames = new List<string>();
try
{
//获取文件流
using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook workbook;
/// 获取excel列名
/// </summary>
/// <param name="path">路径</param>
/// <param name="sheetName">表名</param>
/// <param name="fieldRowIndex">从第几行开始</param>
/// <returns></returns>
public static List<string> GetCNames(string path, string sheetName, int fieldRowIndex = 0)
{
//列名
List<string> cNames = new List<string>();
try
{
//获取文件流
using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook workbook;
//获取excel的文件类型
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
//
ISheet sheet = null;
//获取工作表
if (string.IsNullOrWhiteSpace(sheetName))
{
//sheet = workbook.GetSheetAt(0);
System.Windows.Forms.MessageBox.Show(sheetName + "表不存在!");
}
else
{
//获取excel表格
sheet = workbook.GetSheet(sheetName);
}
//是否已经获取工作表，如果没有则直接返回
if (sheet == null)
{
return null;
}
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
//
ISheet sheet = null;
//获取工作表
if (string.IsNullOrWhiteSpace(sheetName))
{
//sheet = workbook.GetSheetAt(0);
System.Windows.Forms.MessageBox.Show(sheetName + "表不存在!");
}
else
{
//获取excel表格
sheet = workbook.GetSheet(sheetName);
}
//是否已经获取工作表，如果没有则直接返回
if (sheet == null)
{
return null;
}
#region//获取表头,文本类型
//获取列名所在行(默认获取第一行)
if (fieldRowIndex > sheet.LastRowNum)//判断行标是否超界
{
return null;
}
IRow headRow = sheet.GetRow(fieldRowIndex);
//列数
int cellcount = headRow.LastCellNum;
for (int i = 0; i < cellcount; i++)
{
//获取行中的i个元素
ICell cell = headRow.GetCell(i);
cNames.Add(cell.ToString());
}
#endregion
#endregion
}
return cNames;
}
catch
{
return null;
}
return cNames;
}
catch
{
return null;
}
}
/// <summary>
/// 从Excel中获取Datatable
/// </summary>
/// <param name="path">路径</param>
/// <param name="sheetIndex">表下标</param>
/// <param name="fieldRowIndex">从第几行开始</param>
/// <returns></returns>
public static DataTable GetDataTable(string path, int sheetIndex = 0, int fieldRowIndex = 0)
{
//保存获取表格的数据
DataTable dt = new DataTable();
try
{
//获取文件流
using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook workbook;
//获取excel的文件类型
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
//
ISheet sheet = null;
//获取工作表,默认获取第一张
if (workbook.NumberOfSheets > sheetIndex && workbook.NumberOfSheets > 0)
{
//获取工作表
sheet = workbook.GetSheetAt(sheetIndex);
}
else
{
System.Windows.Forms.MessageBox.Show("索引超出界限或该文件表格为空！");
}
//是否已经获取工作表，如果没有则直接返回
if (sheet == null)
{
return null;
}
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
//
ISheet sheet = null;
//获取工作表,默认获取第一张
if (workbook.NumberOfSheets > sheetIndex && workbook.NumberOfSheets > 0)
{
//获取工作表
sheet = workbook.GetSheetAt(sheetIndex);
}
else
{
System.Windows.Forms.MessageBox.Show("索引超出界限或该文件表格为空！");
}
//是否已经获取工作表，如果没有则直接返回
if (sheet == null)
{
return null;
}
#region//获取表头,文本类型
//默认获取第一行
if (fieldRowIndex > sheet.LastRowNum)//判断行标是否超界
{
System.Windows.Forms.MessageBox.Show("行索引超出界限！");
return null;
}
//获取表头行
IRow headRow = sheet.GetRow(fieldRowIndex);
//列数
int cellcount = headRow.LastCellNum;
for (int i = 0; i < cellcount; i++)
{
DataColumn dc = new DataColumn();
//获取行中的i个元素
ICell cell = headRow.GetCell(i);
if (cell != null)
{
dc.ColumnName = cell.ToString();
//获取cell的数据类型
dc.DataType = Type.GetType("System.String");
}
else
{
dt.Columns.Add("");
dc.DataType = Type.GetType("System.String");
}
//添加列
if (!dt.Columns.Contains(dc.ColumnName))//判断列是否已经存在
{
dt.Columns.Add(dc);
}
else
{
}
}
#endregion
#region//获取表格内容
for (int i = (fieldRowIndex + 1); i <= sheet.LastRowNum; i++)
{
//获取工作表的一行
IRow row = sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellcount; j++)
{
if (row.GetCell(j) != null)
{
//判断单元格是否为日期格式(如果Excel有重复行会导致DataRow索引出界)
if (row.GetCell(j).CellType == NPOI.SS.UserModel.CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
{
if (row.GetCell(j).DateCellValue.Year > 1000)
{
dataRow[j] = row.GetCell(j).DateCellValue.ToString();
}
else
{
dataRow[j] = row.GetCell(j).ToString();
{
if (row.GetCell(j) != null)
{
//判断单元格是否为日期格式(如果Excel有重复行会导致DataRow索引出界)
if (row.GetCell(j).CellType == NPOI.SS.UserModel.CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
{
if (row.GetCell(j).DateCellValue.Year > 1000)
{
dataRow[j] = row.GetCell(j).DateCellValue.ToString();
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
}
dt.Rows.Add(dataRow);
}
#endregion
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
}
dt.Rows.Add(dataRow);
}
#endregion
return dt;
}
}
catch
{
return null;
}
}
catch
{
return null;
}
}
/// <summary>
/// 获取Excel中的所有表格
/// </summary>
/// <param name="path"></param>
/// <param name="fieldRowIndex"></param>
/// <returns></returns>
public static List<DataTable> GetDataTables(string path, int fieldRowIndex = 0)
{
List<DataTable> tables = new List<DataTable>();
/// 获取Excel中的所有表格
/// </summary>
/// <param name="path"></param>
/// <param name="fieldRowIndex"></param>
/// <returns></returns>
public static List<DataTable> GetDataTables(string path, int fieldRowIndex = 0)
{
List<DataTable> tables = new List<DataTable>();
try
{
//获取文件流
using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook workbook;
{
//获取文件流
using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook workbook;
//获取excel的文件类型
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
for (int n = 0; n< workbook.NumberOfSheets;n++)
{
//
ISheet sheet = workbook.GetSheetAt(n);//获取表格
{
//
ISheet sheet = workbook.GetSheetAt(n);//获取表格
string sheetName = sheet.SheetName;//表名
DataTable dt = new DataTable();
dt.TableName = sheetName;
#region//获取表头,文本类型
//默认获取第一行
if (fieldRowIndex > sheet.LastRowNum)//判断行标是否超界
{
System.Windows.Forms.MessageBox.Show("行索引超出界限！");
return null;
}
//获取表头行
IRow headRow = sheet.GetRow(fieldRowIndex);
//列数
int cellcount = headRow.LastCellNum;
for (int i = 0; i < cellcount; i++)
{
DataColumn dc = new DataColumn();
//获取行中的i个元素
ICell cell = headRow.GetCell(i);
if (cell != null)
{
dc.ColumnName = cell.ToString();
//获取cell的数据类型
dc.DataType = Type.GetType("System.String");
}
else
{
dt.Columns.Add("");
dc.DataType = Type.GetType("System.String");
}
//添加列
if (!dt.Columns.Contains(dc.ColumnName))//判断列是否已经存在
{
dt.Columns.Add(dc);
}
else
{
//默认获取第一行
if (fieldRowIndex > sheet.LastRowNum)//判断行标是否超界
{
System.Windows.Forms.MessageBox.Show("行索引超出界限！");
return null;
}
//获取表头行
IRow headRow = sheet.GetRow(fieldRowIndex);
//列数
int cellcount = headRow.LastCellNum;
for (int i = 0; i < cellcount; i++)
{
DataColumn dc = new DataColumn();
//获取行中的i个元素
ICell cell = headRow.GetCell(i);
if (cell != null)
{
dc.ColumnName = cell.ToString();
//获取cell的数据类型
dc.DataType = Type.GetType("System.String");
}
else
{
dt.Columns.Add("");
dc.DataType = Type.GetType("System.String");
}
//添加列
if (!dt.Columns.Contains(dc.ColumnName))//判断列是否已经存在
{
dt.Columns.Add(dc);
}
else
{
}
}
#endregion
#endregion
bool isGoOn = false;
for (int i = (fieldRowIndex + 1); i <= sheet.LastRowNum; i++)
{
//获取工作表的一行
IRow row = sheet.GetRow(i);
{
//获取工作表的一行
IRow row = sheet.GetRow(i);
for (int c = row.FirstCellNum; c < row.Cells.Count; c++)
{
if (row.GetCell(c).ToString().Trim() != "")
{
{
if (row.GetCell(c).ToString().Trim() != "")
{
isGoOn = true;
break;
}
}
}
}
//判断是否继续获取
if (isGoOn is false)
{
tables.Add(dt);
if (isGoOn is false)
{
tables.Add(dt);
break;
}
}
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellcount; j++)
{
if (row.GetCell(j) != null)
{
//判断单元格是否为日期格式(如果Excel有重复行会导致DataRow索引出界)
if (row.GetCell(j).CellType == NPOI.SS.UserModel.CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
{
if (row.GetCell(j).DateCellValue.Year > 1000)
{
dataRow[j] = row.GetCell(j).DateCellValue.ToString();
}
else
{
dataRow[j] = row.GetCell(j).ToString();
{
if (row.GetCell(j) != null)
{
//判断单元格是否为日期格式(如果Excel有重复行会导致DataRow索引出界)
if (row.GetCell(j).CellType == NPOI.SS.UserModel.CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
{
if (row.GetCell(j).DateCellValue.Year > 1000)
{
dataRow[j] = row.GetCell(j).DateCellValue.ToString();
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
}
dt.Rows.Add(dataRow);
}
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
}
dt.Rows.Add(dataRow);
}
if (isGoOn is true)
{
tables.Add(dt);
}
}
{
tables.Add(dt);
}
}
return tables;
}
}
catch (Exception)
{
}
catch (Exception)
{
throw;
}
}
}
}
/// <summary>
/// 从Excel中获取Datatable
/// </summary>
/// <param name="path">路径</param>
/// <param name="sheetName">表名</param>
/// <param name="fieldRowIndex">从第几行开始</param>
/// <returns></returns>
public static DataTable GetDataTable(string path, string sheetName, int fieldRowIndex = 0)
{
//新建表格保存excel数据
DataTable dt = new DataTable();
try
{
//获取文件流
using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook workbook;
//获取excel的文件类型
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
//
ISheet sheet = null;
//获取工作表
if (string.IsNullOrWhiteSpace(sheetName))
{
//sheet = workbook.GetSheetAt(0);
System.Windows.Forms.MessageBox.Show(sheetName+"表不存在!");
}
else
{
sheet = workbook.GetSheet(sheetName);
}
//是否已经获取工作表，如果没有则直接返回
if (sheet == null)
{
return null;
}
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
//
ISheet sheet = null;
//获取工作表
if (string.IsNullOrWhiteSpace(sheetName))
{
//sheet = workbook.GetSheetAt(0);
System.Windows.Forms.MessageBox.Show(sheetName+"表不存在!");
}
else
{
sheet = workbook.GetSheet(sheetName);
}
//是否已经获取工作表，如果没有则直接返回
if (sheet == null)
{
return null;
}
#region//获取表头,文本类型
//默认获取第一行
if (fieldRowIndex > sheet.LastRowNum)//判断行标是否超界
{
System.Windows.Forms.MessageBox.Show("行索引超出界限！");
return null;
}
IRow headRow = sheet.GetRow(fieldRowIndex);
//列数
int cellcount = headRow.LastCellNum;
for (int i = 0; i < cellcount; i++)
{
DataColumn dc = new DataColumn();
//获取行中的i个元素
ICell cell = headRow.GetCell(i);
if (cell != null)
{
dc.ColumnName = cell.ToString();
//获取cell的数据类型
//dc.DataType = Type.GetType("System.String");
dc.DataType = GetType(cell.CellType);
}
else
{
dt.Columns.Add("");
dc.DataType = GetType(cell.CellType);
}
dt.Columns.Add(dc);
}
#endregion
#region//获取表格内容
for (int i = (fieldRowIndex + 1); i <= sheet.LastRowNum; i++)
{
//获取工作表的某一行
IRow row = sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellcount; j++)
{
if (row.GetCell(j) != null)
{
//判断单元格是否为日期格式
if (row.GetCell(j).CellType == NPOI.SS.UserModel.CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
{
if (row.GetCell(j).DateCellValue.Year > 1000)
{
dataRow[j] = row.GetCell(j).DateCellValue.ToString();
}
else
{
dataRow[j] = row.GetCell(j).ToString();
{
if (row.GetCell(j) != null)
{
//判断单元格是否为日期格式
if (row.GetCell(j).CellType == NPOI.SS.UserModel.CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
{
if (row.GetCell(j).DateCellValue.Year > 1000)
{
dataRow[j] = row.GetCell(j).DateCellValue.ToString();
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
}
//添加列
dt.Rows.Add(dataRow);
}
#endregion
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
}
//添加列
dt.Rows.Add(dataRow);
}
#endregion
return dt;
}
}
catch
{
return null;
}
}
catch
{
return null;
}
}
/// <summary>
/// 更新数据
/// </summary>
/// <param name="path">路径</param>
/// <param name="sheetIndex">表下标</param>
/// <param name="fieldRowIndex">表头从第几行开始</param>
/// <param name="rowIndex">第几条数据</param>
/// <param name="values">Key(列名)-值</param>
/// <returns></returns>
public static DataTable UpDataExcel(string path, Dictionary<string, object> values, int sheetIndex = 0, int fieldRowIndex = 0, int rowIndex = 0)
{
//保存更新后的表
DataTable dt = new DataTable();
try
{
//获取excel表
dt = GetDataTable(path, sheetIndex, fieldRowIndex);
/// 更新数据
/// </summary>
/// <param name="path">路径</param>
/// <param name="sheetIndex">表下标</param>
/// <param name="fieldRowIndex">表头从第几行开始</param>
/// <param name="rowIndex">第几条数据</param>
/// <param name="values">Key(列名)-值</param>
/// <returns></returns>
public static DataTable UpDataExcel(string path, Dictionary<string, object> values, int sheetIndex = 0, int fieldRowIndex = 0, int rowIndex = 0)
{
//保存更新后的表
DataTable dt = new DataTable();
try
{
//获取excel表
dt = GetDataTable(path, sheetIndex, fieldRowIndex);
//判断表是否为空,且rowIndex不能超出索引
if (dt != null && dt.Rows.Count > 0 && rowIndex < dt.Rows.Count)
{
//遍历字典
foreach (KeyValuePair<string, object> keyValue in values)
{
if (dt.Columns.Contains(keyValue.Key))
{
dt.Rows[rowIndex][keyValue.Key] = keyValue.Value;
}
else
{
//提醒窗口，字段不存在的提醒是否终止该操作(但是已经修改过的数据还在)
System.Windows.Forms.DialogResult dr = System.Windows.Forms.MessageBox.Show("不包含列:" + keyValue.Key, "是否跳过继续", System.Windows.Forms.MessageBoxButtons.YesNo, System.Windows.Forms.MessageBoxIcon.Question);
if (dr == System.Windows.Forms.DialogResult.Yes)
{
//点确定的代码
continue;
}
else
{
//点取消的代码
break;
}
}
}
}
else
{
System.Windows.Forms.MessageBox.Show("表为空表或者目标索引出界！\n数据更新失败！");
}
return dt;
}
catch
{
return null;
}
}
if (dt != null && dt.Rows.Count > 0 && rowIndex < dt.Rows.Count)
{
//遍历字典
foreach (KeyValuePair<string, object> keyValue in values)
{
if (dt.Columns.Contains(keyValue.Key))
{
dt.Rows[rowIndex][keyValue.Key] = keyValue.Value;
}
else
{
//提醒窗口，字段不存在的提醒是否终止该操作(但是已经修改过的数据还在)
System.Windows.Forms.DialogResult dr = System.Windows.Forms.MessageBox.Show("不包含列:" + keyValue.Key, "是否跳过继续", System.Windows.Forms.MessageBoxButtons.YesNo, System.Windows.Forms.MessageBoxIcon.Question);
if (dr == System.Windows.Forms.DialogResult.Yes)
{
//点确定的代码
continue;
}
else
{
//点取消的代码
break;
}
}
}
}
else
{
System.Windows.Forms.MessageBox.Show("表为空表或者目标索引出界！\n数据更新失败！");
}
return dt;
}
catch
{
return null;
}
}
/// <summary>
/// 将表格存储为Excel
/// </summary>
/// <param name="path">路径</param>
/// <param name="dt">表</param>
/// <param name="sheetName">sheet表名</param>
/// <param name="hasFieldRow">是否转表名</param>
/// <returns></returns>
public static bool SaveAsExcel(string path, DataTable dt, string sheetName, bool hasFieldRow = true)
{
try
{
//获取excel的文件类型
ExcelType type = GetExcelFileType(path).Value;
IWorkbook workbook;
//新建工作目录
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook();
break;
default:
workbook = new HSSFWorkbook();
break;
}
ISheet sheet;
//表名
if (!string.IsNullOrWhiteSpace(sheetName))
{
//创建工作表
sheet = workbook.CreateSheet(sheetName);
//新建工作目录
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook();
break;
default:
workbook = new HSSFWorkbook();
break;
}
ISheet sheet;
//表名
if (!string.IsNullOrWhiteSpace(sheetName))
{
//创建工作表
sheet = workbook.CreateSheet(sheetName);
}
else
{
int i = 1;
while (true)
{
//判断表名是否存在
if (workbook.GetSheetIndex("sheet" + i) == -1)
{
//创建表
sheet = workbook.CreateSheet("sheet" + i);
else
{
int i = 1;
while (true)
{
//判断表名是否存在
if (workbook.GetSheetIndex("sheet" + i) == -1)
{
//创建表
sheet = workbook.CreateSheet("sheet" + i);
break;
}
else
{
i++;
}
}
}
//创建表是否成功
if (sheet == null)
{
return false;
}
}
else
{
i++;
}
}
}
//创建表是否成功
if (sheet == null)
{
return false;
}
#region//添加表头
if (hasFieldRow)//判断是否添加表头
{
IRow headRow = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
//获取单元格
ICell cell = headRow.CreateCell(i);
//设置单元格数据类型
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(dt.Columns[i].ColumnName);
}
}
#endregion
if (hasFieldRow)//判断是否添加表头
{
IRow headRow = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
//获取单元格
ICell cell = headRow.CreateCell(i);
//设置单元格数据类型
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(dt.Columns[i].ColumnName);
}
}
#endregion
#region//添加数据
//添加表头为1，不添加为0
int starRow;
if (hasFieldRow)
{
starRow = 1;
}
else
{
starRow = 0;
}
//遍历行
for (int i = 0; i < dt.Rows.Count; i++)
{
//获取第i行
DataRow dr = dt.Rows[i];
//创建行
IRow cells = sheet.CreateRow(i + starRow);
//遍历列
for (int j = 0; j < dr.ItemArray.Length; j++)
{
//创建单元格
ICell cell = cells.CreateCell(j);
//设置单元格数据类型
cell.SetCellType(CellType.String);
//设置单元格值
cell.SetCellValue(dr.ItemArray[j].ToString());
}
}
#endregion
}
#endregion
//保存至Path
bool success= Export(workbook, path);
bool success= Export(workbook, path);
return success;
}
catch
{
return false;
}
}
}
catch
{
return false;
}
}
/// <summary>
/// 将表格存储为Excel
/// </summary>
/// <param name="path">保存路径</param>
/// <param name="dt">DataTable</param>
/// <param name="tagRowIndex">标红的行号</param>
/// <param name="sheetName"></param>
/// <param name="hasFieldRow"></param>
/// <returns></returns>
public static bool SaveAsExcelWithTag(string path, DataTable dt, List<int> tagRowIndex,string sheetName, bool hasFieldRow = true)
{
try
{
//获取excel的文件类型
ExcelType type = GetExcelFileType(path).Value;
/// 将表格存储为Excel
/// </summary>
/// <param name="path">保存路径</param>
/// <param name="dt">DataTable</param>
/// <param name="tagRowIndex">标红的行号</param>
/// <param name="sheetName"></param>
/// <param name="hasFieldRow"></param>
/// <returns></returns>
public static bool SaveAsExcelWithTag(string path, DataTable dt, List<int> tagRowIndex,string sheetName, bool hasFieldRow = true)
{
try
{
//获取excel的文件类型
ExcelType type = GetExcelFileType(path).Value;
IWorkbook workbook;
//新建工作目录
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook();
break;
default:
workbook = new HSSFWorkbook();
break;
}
ISheet sheet;
//表名
if (!string.IsNullOrWhiteSpace(sheetName))
{
//创建工作表
sheet = workbook.CreateSheet(sheetName);
//新建工作目录
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook();
break;
default:
workbook = new HSSFWorkbook();
break;
}
ISheet sheet;
//表名
if (!string.IsNullOrWhiteSpace(sheetName))
{
//创建工作表
sheet = workbook.CreateSheet(sheetName);
}
else
{
int i = 1;
while (true)
{
//判断表名是否存在
if (workbook.GetSheetIndex("sheet" + i) == -1)
{
//创建表
sheet = workbook.CreateSheet("sheet" + i);
else
{
int i = 1;
while (true)
{
//判断表名是否存在
if (workbook.GetSheetIndex("sheet" + i) == -1)
{
//创建表
sheet = workbook.CreateSheet("sheet" + i);
break;
}
else
{
i++;
}
}
}
//创建表是否成功
if (sheet == null)
{
return false;
}
}
else
{
i++;
}
}
}
//创建表是否成功
if (sheet == null)
{
return false;
}
#region//添加表头
if (hasFieldRow)//判断是否添加表头
{
IRow headRow = sheet.CreateRow(0);
if (hasFieldRow)//判断是否添加表头
{
IRow headRow = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
//获取单元格
ICell cell = headRow.CreateCell(i);
//设置单元格数据类型
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(dt.Columns[i].ColumnName);
}
}
#endregion
{
//获取单元格
ICell cell = headRow.CreateCell(i);
//设置单元格数据类型
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(dt.Columns[i].ColumnName);
}
}
#endregion
#region//添加数据
//添加表头为1，不添加为0
int starRow;
if (hasFieldRow)
{
starRow = 1;
}
else
{
starRow = 0;
}
ICellStyle style = workbook.CreateCellStyle();
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
style.FillPattern = FillPattern.SolidForeground;
//遍历行
for (int i = 0; i < dt.Rows.Count; i++)
{
//获取第i行
DataRow dr = dt.Rows[i];
for (int i = 0; i < dt.Rows.Count; i++)
{
//获取第i行
DataRow dr = dt.Rows[i];
//判断是不是标注行
bool isTag = tagRowIndex.Contains(i) ? true : false;
bool isTag = tagRowIndex.Contains(i) ? true : false;
//创建行
IRow cells = sheet.CreateRow(i + starRow);
IRow cells = sheet.CreateRow(i + starRow);
//遍历列
for (int j = 0; j < dr.ItemArray.Length; j++)
{
//创建单元格
ICell cell = cells.CreateCell(j);
//设置单元格数据类型
cell.SetCellType(CellType.String);
//设置单元格值
cell.SetCellValue(dr.ItemArray[j].ToString());
for (int j = 0; j < dr.ItemArray.Length; j++)
{
//创建单元格
ICell cell = cells.CreateCell(j);
//设置单元格数据类型
cell.SetCellType(CellType.String);
//设置单元格值
cell.SetCellValue(dr.ItemArray[j].ToString());
//判断是否进行标注行
if (isTag is true)
{
cell.CellStyle = style;
}
if (isTag is true)
{
cell.CellStyle = style;
}
}
}
#endregion
//保存至Path
bool success = Export(workbook, path);
bool success = Export(workbook, path);
return success;
}
catch
{
return false;
}
}
catch
{
return false;
}
}
/// <summary>
/// 多表存储至指定路径
/// </summary>
/// <param name="path"></param>
/// <param name="dts"></param>
/// <param name="hasFieldRow"></param>
/// <returns></returns>
public static bool SaveAsExcel(string path, List<DataTable> dts, bool hasFieldRow = true)
{
try
{
/// 多表存储至指定路径
/// </summary>
/// <param name="path"></param>
/// <param name="dts"></param>
/// <param name="hasFieldRow"></param>
/// <returns></returns>
public static bool SaveAsExcel(string path, List<DataTable> dts, bool hasFieldRow = true)
{
try
{
//获取excel的文件类型
ExcelType type = GetExcelFileType(path).Value;
ExcelType type = GetExcelFileType(path).Value;
IWorkbook workbook;
//新建工作目录
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook();
break;
default:
workbook = new HSSFWorkbook();
break;
}
//新建工作目录
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook();
break;
default:
workbook = new HSSFWorkbook();
break;
}
foreach (DataTable dt in dts)
{
ISheet sheet;
{
ISheet sheet;
string sheetName = dt.TableName;
//表名
if (!string.IsNullOrWhiteSpace(sheetName))
{
//创建工作表
sheet = workbook.CreateSheet(sheetName);
if (!string.IsNullOrWhiteSpace(sheetName))
{
//创建工作表
sheet = workbook.CreateSheet(sheetName);
}
else
{
int i = 1;
while (true)
{
//判断表名是否存在
if (workbook.GetSheetIndex("sheet" + i) == -1)
{
//创建表
sheet = workbook.CreateSheet("sheet" + i);
else
{
int i = 1;
while (true)
{
//判断表名是否存在
if (workbook.GetSheetIndex("sheet" + i) == -1)
{
//创建表
sheet = workbook.CreateSheet("sheet" + i);
break;
}
else
{
i++;
}
}
}
//创建表是否成功
if (sheet == null)
{
return false;
}
}
else
{
i++;
}
}
}
//创建表是否成功
if (sheet == null)
{
return false;
}
#region//添加表头
if (hasFieldRow)//判断是否添加表头
{
IRow headRow = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
//获取单元格
ICell cell = headRow.CreateCell(i);
//设置单元格数据类型
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(dt.Columns[i].ColumnName);
}
}
#endregion
if (hasFieldRow)//判断是否添加表头
{
IRow headRow = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
//获取单元格
ICell cell = headRow.CreateCell(i);
//设置单元格数据类型
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(dt.Columns[i].ColumnName);
}
}
#endregion
#region//添加数据
//添加表头为1，不添加为0
int starRow;
if (hasFieldRow)
{
starRow = 1;
}
else
{
starRow = 0;
}
//遍历行
for (int i = 0; i < dt.Rows.Count; i++)
{
//获取第i行
DataRow dr = dt.Rows[i];
//创建行
IRow cells = sheet.CreateRow(i + starRow);
//遍历列
for (int j = 0; j < dr.ItemArray.Length; j++)
{
//创建单元格
ICell cell = cells.CreateCell(j);
//设置单元格数据类型
cell.SetCellType(CellType.String);
//设置单元格值
cell.SetCellValue(dr.ItemArray[j].ToString());
}
}
#endregion
}
#endregion
}
//保存至Path
bool success = Export(workbook, path);
bool success = Export(workbook, path);
return success;
}
catch (Exception)
{
}
catch (Exception)
{
throw;
}
}
}
}
/// <summary>
/// 往Excel里面插入DataTable
/// </summary>
/// <param name="path">excel路径</param>
/// <param name="dt">表</param>
/// <param name="aimRowIndex">从第几行开始</param>
/// <param name="insertIndex">下标</param>
/// <param name="hasFieldRow"></param>
/// <returns></returns>
public static bool InsertDataoExcel(string path, DataTable dt, int insertIndex, int aimRowIndex = 0, bool hasFieldRow = true)
{
/// 往Excel里面插入DataTable
/// </summary>
/// <param name="path">excel路径</param>
/// <param name="dt">表</param>
/// <param name="aimRowIndex">从第几行开始</param>
/// <param name="insertIndex">下标</param>
/// <param name="hasFieldRow"></param>
/// <returns></returns>
public static bool InsertDataoExcel(string path, DataTable dt, int insertIndex, int aimRowIndex = 0, bool hasFieldRow = true)
{
try
{
//获取文件流
using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook workbook;
{
//获取文件流
using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook workbook;
//获取excel的文件类型
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
ISheet sheet = null;
int name_i = 1;
while (true)
{
if (workbook.GetSheetIndex("Sheet" + name_i) == -1)
{
//判断插入下标是否符合要求
if (insertIndex > (workbook.NumberOfSheets + 1))
{
System.Windows.Forms.MessageBox.Show("不能将表插入该索引下！");
}
else
{
//创建工作表
sheet = workbook.CreateSheet("Sheet" + name_i);
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
ISheet sheet = null;
int name_i = 1;
while (true)
{
if (workbook.GetSheetIndex("Sheet" + name_i) == -1)
{
//判断插入下标是否符合要求
if (insertIndex > (workbook.NumberOfSheets + 1))
{
System.Windows.Forms.MessageBox.Show("不能将表插入该索引下！");
}
else
{
//创建工作表
sheet = workbook.CreateSheet("Sheet" + name_i);
//设置表的索引下标
workbook.SetSheetOrder("Sheet" + name_i, insertIndex);
}
workbook.SetSheetOrder("Sheet" + name_i, insertIndex);
}
break;
}
else
{
name_i++;
}
}
}
else
{
name_i++;
}
}
//是否已经获取工作表，如果没有则直接返回
if (sheet == null)
{
return false;
}
if (sheet == null)
{
return false;
}
#region//往表中追加Datatable（包括表头信息）
if (insertIndex < workbook.NumberOfSheets)//插入的下标不能在已有数据范围
{
if (hasFieldRow)
{
//添加表头
IRow headRow = sheet.CreateRow(0 + aimRowIndex);
for (int i = 0; i < dt.Columns.Count; i++)
{
//获取单元格
ICell cell = headRow.CreateCell(i);
//设置单元格数据类型
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(dt.Columns[i].ColumnName);
}
}
//添加表头starRow为1，不添加则为0
int starRow;
if (hasFieldRow)
{
starRow = 1;
}
else
{
starRow = 0;
}
//遍历行
for (int i = 0; i < dt.Rows.Count; i++)
{
//创建行
IRow cells = sheet.CreateRow(sheet.LastRowNum + starRow);
//遍历列
for (int j = 0; j < dt.Columns.Count; j++)
{
//创建单元格
ICell cell = cells.CreateCell(j);
//设置单元格属性
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(dt.Rows[i][j].ToString());
if (insertIndex < workbook.NumberOfSheets)//插入的下标不能在已有数据范围
{
if (hasFieldRow)
{
//添加表头
IRow headRow = sheet.CreateRow(0 + aimRowIndex);
for (int i = 0; i < dt.Columns.Count; i++)
{
//获取单元格
ICell cell = headRow.CreateCell(i);
//设置单元格数据类型
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(dt.Columns[i].ColumnName);
}
}
//添加表头starRow为1，不添加则为0
int starRow;
if (hasFieldRow)
{
starRow = 1;
}
else
{
starRow = 0;
}
//遍历行
for (int i = 0; i < dt.Rows.Count; i++)
{
//创建行
IRow cells = sheet.CreateRow(sheet.LastRowNum + starRow);
//遍历列
for (int j = 0; j < dt.Columns.Count; j++)
{
//创建单元格
ICell cell = cells.CreateCell(j);
//设置单元格属性
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
}
}
#endregion
//保存
bool success = Export(workbook, path);
bool success = Export(workbook, path);
return success;
}
}
catch
{
return false;
}
}
}
catch
{
return false;
}
}
/// <summary>
/// 往Excel里面插入DataTable
/// </summary>
/// <param name="path">excel路径</param>
/// <param name="dt">表</param>
/// <param name="sheetName">sheet表名</param>
/// <param name="insertIndex">下标</param>
/// <param name="hasFieldRow"></param>
/// <returns></returns>
public static bool InsertDataoExcel(string path, DataTable dt, string sheetName, int insertIndex, bool hasFieldRow = true)
{
try
{
//获取文件流
using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook workbook;
//获取excel的文件类型
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
ISheet sheet = null;
if (workbook.GetSheetIndex(sheetName) == -1)
{
if (insertIndex > (workbook.NumberOfSheets + 1))
{
System.Windows.Forms.MessageBox.Show("不能将表插入该索引下！");
}
else
{
//创建表
sheet = workbook.CreateSheet(sheetName);
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
ISheet sheet = null;
if (workbook.GetSheetIndex(sheetName) == -1)
{
if (insertIndex > (workbook.NumberOfSheets + 1))
{
System.Windows.Forms.MessageBox.Show("不能将表插入该索引下！");
}
else
{
//创建表
sheet = workbook.CreateSheet(sheetName);
//设置表的索引下标
workbook.SetSheetOrder(sheetName, insertIndex);
}
}
else
{
System.Windows.Forms.MessageBox.Show(sheetName+ "表创建失败," + sheetName+"表已经存在!");
return false;
}
//是否已经获取工作表，如果没有则直接返回
if (sheet == null)
{
return false;
}
workbook.SetSheetOrder(sheetName, insertIndex);
}
}
else
{
System.Windows.Forms.MessageBox.Show(sheetName+ "表创建失败," + sheetName+"表已经存在!");
return false;
}
//是否已经获取工作表，如果没有则直接返回
if (sheet == null)
{
return false;
}
#region//往表中追加Datatable
if (insertIndex < workbook.NumberOfSheets)//插入的下标不能在已有数据范围
{
if (hasFieldRow)
{
//添加表头
IRow headRow = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
//获取单元格
ICell cell = headRow.CreateCell(i);
//设置单元格数据类型
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(dt.Columns[i].ColumnName);
}
}
if (insertIndex < workbook.NumberOfSheets)//插入的下标不能在已有数据范围
{
if (hasFieldRow)
{
//添加表头
IRow headRow = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
//获取单元格
ICell cell = headRow.CreateCell(i);
//设置单元格数据类型
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(dt.Columns[i].ColumnName);
}
}
int starRow;
if (hasFieldRow)
{
starRow = 1;
}
else
{
starRow = 0;
}
//遍历行
for (int i = 0; i < dt.Rows.Count; i++)
{
//创建行
IRow cells = sheet.CreateRow(sheet.LastRowNum + starRow);
//遍历列
for (int j = 0; j < dt.Columns.Count; j++)
{
//创建单元格
ICell cell = cells.CreateCell(j);
//设置单元格属性
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(dt.Rows[i][j].ToString());
}
if (hasFieldRow)
{
starRow = 1;
}
else
{
starRow = 0;
}
//遍历行
for (int i = 0; i < dt.Rows.Count; i++)
{
//创建行
IRow cells = sheet.CreateRow(sheet.LastRowNum + starRow);
//遍历列
for (int j = 0; j < dt.Columns.Count; j++)
{
//创建单元格
ICell cell = cells.CreateCell(j);
//设置单元格属性
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
}
#endregion
}
#endregion
//保存
bool success = Export(workbook,path);
bool success = Export(workbook,path);
return success;
}
}
catch
{
return false;
}
}
}
}
catch
{
return false;
}
}
/// <summary>
/// 往Excel里面插入Sheet
/// </summary>
/// <param name="aimpath">目标excel</param>
/// <param name="aimSheetIndex">目标sheet下标</param>
/// <param name="aimRowIndex">目标从第几行开始</param>
/// <param name="path">源excel路径</param>
/// <param name="sheetIndex">源excelsheet下标</param>
/// <returns></returns>
public static bool InsertDataoExcel(string aimpath, int aimSheetIndex, int aimRowIndex, string path, int sheetIndex)
{
try
{
//获取源目标的datatble
DataTable dt = GetDataTable(path, sheetIndex);
//将datatable插入到目标excel中
bool success = InsertDataoExcel(aimpath,dt, aimSheetIndex, aimRowIndex);
/// 往Excel里面插入Sheet
/// </summary>
/// <param name="aimpath">目标excel</param>
/// <param name="aimSheetIndex">目标sheet下标</param>
/// <param name="aimRowIndex">目标从第几行开始</param>
/// <param name="path">源excel路径</param>
/// <param name="sheetIndex">源excelsheet下标</param>
/// <returns></returns>
public static bool InsertDataoExcel(string aimpath, int aimSheetIndex, int aimRowIndex, string path, int sheetIndex)
{
try
{
//获取源目标的datatble
DataTable dt = GetDataTable(path, sheetIndex);
//将datatable插入到目标excel中
bool success = InsertDataoExcel(aimpath,dt, aimSheetIndex, aimRowIndex);
return success;
}
catch
{
return false;
}
}
catch
{
return false;
}
}
/// <summary>
/// 追加记录DataTable(不含表头，将表内容直接追加进去至末尾)
/// </summary>
/// <param name="path">路径</param>
/// <param name="sheetIndex">目标表下标</param>
/// <param name="dt">表格</param>
/// <returns></returns>
public static bool AppendDataTableToExcel(string path, int sheetIndex, DataTable dt,bool hasFieldRow = true)
{
try
{
//获取文件流
using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook workbook;
//获取excel的文件类型
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
//
ISheet sheet = null;
//获取工作表,默认获取第一张
if (workbook.NumberOfSheets > sheetIndex && workbook.NumberOfSheets > 0)
{
sheet = workbook.GetSheetAt(sheetIndex);
}
else
{
System.Windows.Forms.MessageBox.Show("索引超出界限或该文件表格为空！");
}
//是否已经获取工作表，如果没有则直接返回
if (sheet == null)
{
return false;
}
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
//
ISheet sheet = null;
//获取工作表,默认获取第一张
if (workbook.NumberOfSheets > sheetIndex && workbook.NumberOfSheets > 0)
{
sheet = workbook.GetSheetAt(sheetIndex);
}
else
{
System.Windows.Forms.MessageBox.Show("索引超出界限或该文件表格为空！");
}
//是否已经获取工作表，如果没有则直接返回
if (sheet == null)
{
return false;
}
#region//往表中追加Datatable
if (hasFieldRow)//判断是否添加表头
{
//添加表头(当表为空时从0开始，不为空则加1)
IRow headRow = sheet.CreateRow(sheet.LastRowNum+(sheet.LastRowNum==0?0:1));
for (int i = 0; i < dt.Columns.Count; i++)
{
//获取单元格
ICell cell = headRow.CreateCell(i);
//设置单元格数据类型
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(dt.Columns[i].ColumnName.ToString());
}
}
//添加表头为1，不添加表头为0
int starRow;
if (hasFieldRow)
{
starRow = 1;
}
else
{
starRow = 0;
}
if (hasFieldRow)//判断是否添加表头
{
//添加表头(当表为空时从0开始，不为空则加1)
IRow headRow = sheet.CreateRow(sheet.LastRowNum+(sheet.LastRowNum==0?0:1));
for (int i = 0; i < dt.Columns.Count; i++)
{
//获取单元格
ICell cell = headRow.CreateCell(i);
//设置单元格数据类型
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(dt.Columns[i].ColumnName.ToString());
}
}
//添加表头为1，不添加表头为0
int starRow;
if (hasFieldRow)
{
starRow = 1;
}
else
{
starRow = 0;
}
//遍历行
for (int i = 0; i < dt.Rows.Count; i++)
{
//创建行
IRow cells = sheet.CreateRow(sheet.LastRowNum + starRow);
//遍历列
for (int j = 0; j < dt.Columns.Count; j++)
{
//创建单元格
ICell cell = cells.CreateCell(j);
//设置单元格属性
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(dt.Rows[i][j].ToString());
}
for (int i = 0; i < dt.Rows.Count; i++)
{
//创建行
IRow cells = sheet.CreateRow(sheet.LastRowNum + starRow);
//遍历列
for (int j = 0; j < dt.Columns.Count; j++)
{
//创建单元格
ICell cell = cells.CreateCell(j);
//设置单元格属性
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
#endregion
#endregion
//保存
bool success = Export(workbook, path);
bool success = Export(workbook, path);
return success;
}
}
catch
{
return false;
}
}
}
}
catch
{
return false;
}
}
/// <summary>
/// 追加记录DataRow
/// </summary>
/// <param name="path">路径</param>
/// <param name="sheetIndex">目标表下标</param>
/// <param name="dr">行数据</param>
/// <returns></returns>
public static bool AppendDataRowToExcel(string path, int sheetIndex, DataRow dr)
{
try
{
//获取文件流
using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook workbook;
//获取excel的文件类型
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
//
ISheet sheet = null;
//获取工作表,默认获取第一张
if (workbook.NumberOfSheets > sheetIndex && workbook.NumberOfSheets > 0)
{
//获取工作表
sheet = workbook.GetSheetAt(sheetIndex);
}
else
{
System.Windows.Forms.MessageBox.Show("索引超出界限或该文件表格为空！");
}
//是否已经获取工作表，如果没有则直接返回
if (sheet == null)
{
return false;
}
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
//
ISheet sheet = null;
//获取工作表,默认获取第一张
if (workbook.NumberOfSheets > sheetIndex && workbook.NumberOfSheets > 0)
{
//获取工作表
sheet = workbook.GetSheetAt(sheetIndex);
}
else
{
System.Windows.Forms.MessageBox.Show("索引超出界限或该文件表格为空！");
}
//是否已经获取工作表，如果没有则直接返回
if (sheet == null)
{
return false;
}
#region//往表中追加Datatable(没有追加表头数据)
//创建行单元(当表为空时从0开始，不为空则加1)
IRow cells = sheet.CreateRow(sheet.LastRowNum+(sheet.LastRowNum==0?0:1));
//遍历列
for (int j = 0; j < dr.ItemArray.Length; j++)
{
//创建单元格
ICell cell = cells.CreateCell(j);
//设置单元格属性
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(dr.ItemArray[j].ToString());
}
#endregion
//创建行单元(当表为空时从0开始，不为空则加1)
IRow cells = sheet.CreateRow(sheet.LastRowNum+(sheet.LastRowNum==0?0:1));
//遍历列
for (int j = 0; j < dr.ItemArray.Length; j++)
{
//创建单元格
ICell cell = cells.CreateCell(j);
//设置单元格属性
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(dr.ItemArray[j].ToString());
}
#endregion
//保存
bool success = Export(workbook, path);
bool success = Export(workbook, path);
return success;
}
}
catch
{
return false;
}
}
}
}
catch
{
return false;
}
}
/// <summary>
/// 插入一个工作表
/// </summary>
/// <param name="aimpath">目标表路径</param>
/// <param name="aimSheetIndex">目标表存放表下标</param>
/// <param name="aimRowIndex">目标表第几行开始</param>
/// <param name="path">插入表路径</param>
/// <param name="sheetIndex">插入表的表下标</param>
/// <returns></returns>
public static bool InsetSheetToExcel(string aimpath, int aimSheetIndex, int aimRowIndex, string path, int sheetIndex)
{
try
{
//目标工作表
ISheet aimSheet = CreateSheet(aimpath, aimSheetIndex);
//插入的工作表
ISheet pathISheet = GetExcelSheet(path, sheetIndex);
ISheet pathISheet = GetExcelSheet(path, sheetIndex);
//判断工作表是否为空
if (aimSheet != null && pathISheet != null)
{
for (int i = 0; i <= pathISheet.LastRowNum; i++)
{
//新建行
IRow cells = aimSheet.CreateRow(i);
if (aimSheet != null && pathISheet != null)
{
for (int i = 0; i <= pathISheet.LastRowNum; i++)
{
//新建行
IRow cells = aimSheet.CreateRow(i);
//获取插入表行
IRow row = pathISheet.GetRow(i);
IRow row = pathISheet.GetRow(i);
for (int j = 0; j < row.LastCellNum; j++)
{
//创建单元
ICell cell = cells.CreateCell(j);
{
//创建单元
ICell cell = cells.CreateCell(j);
//设置单元格类型
cell.SetCellType(CellType.String);
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(row.GetCell(j).ToString());
cell.SetCellValue(row.GetCell(j).ToString());
}
}
}
Export(aimSheet.Workbook, aimpath);
return true;
}
}
catch
{
return false;
}
}
}
catch
{
return false;
}
return false;
}
}
#endregion
#region//Private-Method
/// <summary>
/// 获取Excel里面的工作表
/// </summary>
/// <param name="path">Excel路径</param>
/// <param name="sheetIndex">工作表索引下标</param>
/// <returns></returns>
private static ISheet GetExcelSheet(string path,int sheetIndex)
{
try
{
//获取文件流
using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook workbook;
/// 获取Excel里面的工作表
/// </summary>
/// <param name="path">Excel路径</param>
/// <param name="sheetIndex">工作表索引下标</param>
/// <returns></returns>
private static ISheet GetExcelSheet(string path,int sheetIndex)
{
try
{
//获取文件流
using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook workbook;
//获取excel的文件类型
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
//
ISheet sheet = null;
//获取工作表,默认获取第一张
if (workbook.NumberOfSheets > sheetIndex && workbook.NumberOfSheets > 0)
{
//获取工作表
sheet = workbook.GetSheetAt(sheetIndex);
}
else
{
System.Windows.Forms.MessageBox.Show("索引超出界限或该文件表格为空！");
}
//是否已经获取工作表，如果没有则直接返回
if (sheet == null)
{
return null;
}
else
{
return sheet;
}
}
}
catch
{
return null;
}
}
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
//
ISheet sheet = null;
//获取工作表,默认获取第一张
if (workbook.NumberOfSheets > sheetIndex && workbook.NumberOfSheets > 0)
{
//获取工作表
sheet = workbook.GetSheetAt(sheetIndex);
}
else
{
System.Windows.Forms.MessageBox.Show("索引超出界限或该文件表格为空！");
}
//是否已经获取工作表，如果没有则直接返回
if (sheet == null)
{
return null;
}
else
{
return sheet;
}
}
}
catch
{
return null;
}
}
/// <summary>
/// 获取Excel里面的工作表
/// </summary>
/// <param name="path">Excel路径</param>
/// <param name="sheetIndex">工作表名称</param>
/// <returns></returns>
private static ISheet GetExcelSheet(string path, string sheetName)
{
try
{
//获取文件流
using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook workbook;
//获取excel的文件类型
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
//
ISheet sheet = null;
//获取工作表,默认获取第一张
if (string.IsNullOrWhiteSpace(sheetName))
{
//sheet = workbook.GetSheetAt(0);
System.Windows.Forms.MessageBox.Show(sheetName + "表不存在!");
}
else
{
sheet = workbook.GetSheet(sheetName);
}
//是否已经获取工作表，如果没有则直接返回
if (sheet == null)
{
return null;
}
else
{
return sheet;
}
}
}
catch
{
return null;
}
}
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
//
ISheet sheet = null;
//获取工作表,默认获取第一张
if (string.IsNullOrWhiteSpace(sheetName))
{
//sheet = workbook.GetSheetAt(0);
System.Windows.Forms.MessageBox.Show(sheetName + "表不存在!");
}
else
{
sheet = workbook.GetSheet(sheetName);
}
//是否已经获取工作表，如果没有则直接返回
if (sheet == null)
{
return null;
}
else
{
return sheet;
}
}
}
catch
{
return null;
}
}
/// <summary>
/// Excel创建一个工作表
/// </summary>
/// <param name="path"></param>
/// <param name="insertIndex"></param>
/// <returns></returns>
private static ISheet CreateSheet(string path, int insertIndex)
{
try
{
//获取文件流
using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook workbook;
//获取excel的文件类型
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
ISheet sheet = null;
int name_i = 1;
while (true)
{
if (workbook.GetSheetIndex("Sheet" + name_i) == -1)
{
//判断插入下标是否符合要求
if (insertIndex > (workbook.NumberOfSheets + 1))
{
System.Windows.Forms.MessageBox.Show("不能将表插入该索引下！");
}
else
{
//创建工作表
sheet = workbook.CreateSheet("Sheet" + name_i);
//设置表的索引下标
workbook.SetSheetOrder("Sheet" + name_i, insertIndex);
}
workbook.SetSheetOrder("Sheet" + name_i, insertIndex);
}
break;
}
else
{
name_i++;
}
}
}
else
{
name_i++;
}
}
//是否已经获取工作表，如果没有则直接返回
if (sheet == null)
{
return null;
}
else
{
return sheet;
}
}
}
catch
{
return null;
}
}
if (sheet == null)
{
return null;
}
else
{
return sheet;
}
}
}
catch
{
return null;
}
}
/// <summary>
/// Excel创建一个工作表
/// </summary>
/// <param name="path"></param>
/// <param name="insertIndex"></param>
/// <returns></returns>
private static ISheet CreateSheet(string path, string sheetName,int insertIndex=0)
{
try
{
//获取文件流
using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook workbook;
//获取excel的文件类型
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
ISheet sheet = null;
if (workbook.GetSheetIndex(sheetName) == -1)
{
if (insertIndex > (workbook.NumberOfSheets + 1))
{
System.Windows.Forms.MessageBox.Show("不能将表插入该索引下！");
}
else
{
//创建表
sheet = workbook.CreateSheet(sheetName);
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
ISheet sheet = null;
if (workbook.GetSheetIndex(sheetName) == -1)
{
if (insertIndex > (workbook.NumberOfSheets + 1))
{
System.Windows.Forms.MessageBox.Show("不能将表插入该索引下！");
}
else
{
//创建表
sheet = workbook.CreateSheet(sheetName);
//设置表的索引下标
workbook.SetSheetOrder(sheetName, insertIndex);
}
workbook.SetSheetOrder(sheetName, insertIndex);
}
}
else
{
System.Windows.Forms.MessageBox.Show(sheetName + "表创建失败," + sheetName + "表已经存在!");
}
//是否已经获取工作表，如果没有则直接返回
if (sheet == null)
{
return null;
}
else
{
return sheet;
}
}
}
catch
{
return null;
}
}
if (sheet == null)
{
return null;
}
else
{
return sheet;
}
}
}
catch
{
return null;
}
}
/// <summary>
/// excel的类型
/// </summary>
private enum ExcelType
{
xlsx, xls,
}
/// <summary>
/// 获取指定excel文件后缀的格式类型，Nullable为可控类型如Nullable<int32>
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
private static Nullable<ExcelType> GetExcelFileType(string fileName)
{
var ext = Path.GetExtension(fileName);
if (!string.IsNullOrWhiteSpace(ext) && (ext.ToLower() == ".xls" || ext.ToLower() == ".xlsx"))
return ext.ToLower() == ".xls" ? ExcelType.xls : ExcelType.xlsx;
else
return null;
}
/// <summary>
/// 获取表格对应datatable的数据类型
/// </summary>
/// <param name="cellType"></param>
/// <returns></returns>
private static Type GetType(CellType cellType)
{
Type type = null;
switch (cellType)
{
case CellType.Numeric:
type = System.Type.GetType("System.Double");
break;
default:
type = System.Type.GetType("System.String");
break;
}
return type;
}
/// 获取表格对应datatable的数据类型
/// </summary>
/// <param name="cellType"></param>
/// <returns></returns>
private static Type GetType(CellType cellType)
{
Type type = null;
switch (cellType)
{
case CellType.Numeric:
type = System.Type.GetType("System.Double");
break;
default:
type = System.Type.GetType("System.String");
break;
}
return type;
}
/// <summary>
/// 获取表格对应datatable的数据类型
/// </summary>
/// <param name="cellType"></param>
/// <returns></returns>
private static CellType GetType(Type type)
{
CellType celltype = CellType.String;
switch (type.ToString())
{
case "System.Int32":
case "System.Float":
case "System.Double":
celltype = CellType.Numeric;
break;
default:
celltype = CellType.String;
break;
}
return celltype;
}
/// 获取表格对应datatable的数据类型
/// </summary>
/// <param name="cellType"></param>
/// <returns></returns>
private static CellType GetType(Type type)
{
CellType celltype = CellType.String;
switch (type.ToString())
{
case "System.Int32":
case "System.Float":
case "System.Double":
celltype = CellType.Numeric;
break;
default:
celltype = CellType.String;
break;
}
return celltype;
}
/// <summary>
/// 导出至savePath
/// </summary>
/// <param name="workbook"></param>
/// <param name="savePath"></param>
private static bool Export(IWorkbook workbook, string savePath)
{
try
{
// 写入 ,创建其支持存储区为内存的流
System.IO.MemoryStream ms = new System.IO.MemoryStream();
//写入内存
workbook.Write(ms);
workbook = null;
//
FileStream fs = new FileStream(savePath, FileMode.Create, FileAccess.Write);
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
//关闭
ms.Close();
//释放
ms.Dispose();
fs.Close();
return true;
}
catch
{
return false;
}
}
}
catch
{
return false;
}
}
/// <summary>
/// 导出至savePath
/// </summary>
/// <param name="savePath"></param>
private static bool ExcelSave( string savePath)
{
try
{
IWorkbook workbook;
//获取文件流
using (var stream = new FileStream(savePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
//获取excel的文件类型
ExcelType type = GetExcelFileType(savePath).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
}
if (workbook == null)
{
return false;
}
// 写入 ,创建其支持存储区为内存的流
System.IO.MemoryStream ms = new System.IO.MemoryStream();
//写入内存
workbook.Write(ms);
workbook = null;
//
FileStream fs = new FileStream(savePath, FileMode.Create, FileAccess.Write);
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
//关闭
ms.Close();
//释放
ms.Dispose();
/// 导出至savePath
/// </summary>
/// <param name="savePath"></param>
private static bool ExcelSave( string savePath)
{
try
{
IWorkbook workbook;
//获取文件流
using (var stream = new FileStream(savePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
//获取excel的文件类型
ExcelType type = GetExcelFileType(savePath).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
}
if (workbook == null)
{
return false;
}
// 写入 ,创建其支持存储区为内存的流
System.IO.MemoryStream ms = new System.IO.MemoryStream();
//写入内存
workbook.Write(ms);
workbook = null;
//
FileStream fs = new FileStream(savePath, FileMode.Create, FileAccess.Write);
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
//关闭
ms.Close();
//释放
ms.Dispose();
fs.Close();
return true;
}
catch
{
return false;
}
}
}
catch
{
return false;
}
}
private static IWorkbook GetWorkBook(string path)
{
using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook workbook;
//获取excel的文件类型
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
return workbook;
}
}
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
return workbook;
}
}
#endregion
}
}
浙公网安备 33010602011771号