// private static string _fileName; //文件名
private static bool _disposed;
private static FileStream _fs;
private static IWorkbook _workbook;
public void Dispose()
Dispose(true);
GC.SuppressFinalize(this);
///
/// 判断工作部是否存在
///
///
execl文件名
///
excel工作薄sheet的名称
///
返回的DataTable
public static bool IsSheetExist(string filename, string sheetname)
var flag = true;
_fs = new FileStream(filename, FileMode.Open, FileAccess.Read);
if (filename.IndexOf(".xlsx", StringComparison.Ordinal) > 0) // 2007版本
_workbook = new XSSFWorkbook(_fs);
else if (filename.IndexOf(".xls", StringComparison.Ordinal) > 0) // 2003版本
_workbook = new HSSFWorkbook(_fs);
ISheet sheet = _workbook.GetSheet(sheetname);
if (sheet == null)
return false;
catch (Exception ex)
//ErrHandler.WriteContent("----执行文件异常----" + ex.Message);
return false;
return flag;
///
/// 判断execl是否有数据
///
///
execl文件名
///
excel工作薄sheet的名称
///
返回的DataTable
public static bool IsEmpty(string filename, string sheetname)
var flag = true;
_fs = new FileStream(filename, FileMode.Open, FileAccess.Read);
if (filename.IndexOf(".xlsx", StringComparison.Ordinal) > 0) // 2007版本
_workbook = new XSSFWorkbook(_fs);
else if (filename.IndexOf(".xls", StringComparison.Ordinal) > 0) // 2003版本
_workbook = new HSSFWorkbook(_fs);
ISheet sheet = _workbook.GetSheet(sheetname);
if (sheet != null)
var firstRow = sheet.GetRow(0);
if (firstRow == null)
return false;
return false;
catch (Exception ex)
//ErrHandler.WriteContent("----执行文件异常----" + ex.Message);
return false;
return flag;
///
/// 将DataTable数据导入到excel中
///
///
要导入的数据
///
文件名字
///
要导入的excel的sheet的名称
///
DataTable的列名是否要导入
///
导入数据行数(包含列名那一行)
public static int DataTableToExcel(DataTable data, string filename, string sheetName, bool isColumnWritten)
_fs = new FileStream(filename, FileMode.OpenOrCreate, FileAccess.ReadWrite);
if (filename.IndexOf(".xlsx", StringComparison.Ordinal) > 0) // 2007版本
_workbook = new XSSFWorkbook();
else if (filename.IndexOf(".xls", StringComparison.Ordinal) > 0) // 2003版本
_workbook = new HSSFWorkbook();
ISheet sheet;
if (_workbook != null)
sheet = _workbook.CreateSheet(sheetName);
return -1;
int j;
int count;
if (isColumnWritten) //写入DataTable的列名
var row = sheet.CreateRow(0);
for (j = 0; j < data.Columns.Count; ++j)
row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
count = 1;
count = 0;
int i;
for (i = 0; i < data.Rows.Count; ++i)
var row = sheet.CreateRow(count);
for (j = 0; j < data.Columns.Count; ++j)
row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
++count;
_workbook.Write(_fs); //写入到excel
return count;
catch (Exception ex)
//Console.WriteLine("Exception: " + ex.Message);
return -1;
public static MemoryStream GetMemoryStream(DataSet ds, string file)
IWorkbook workbook;
var fileExt = Path.GetExtension(file).ToLower();
switch (fileExt)
case ".xlsx":
workbook = new XSSFWorkbook(); //解析2007以后的版本
break;
case ".xls":
workbook = new HSSFWorkbook();//解析2003之前的版本
break;
default:
workbook = null;
break;
if (workbook == null)
return null;
#region 设置表头、填充数据
var sheetnum = 1;
foreach (DataTable dt in ds.Tables)
//创建工作簿
var sheet = string.IsNullOrEmpty(dt.TableName)
? workbook.CreateSheet("Sheet" + sheetnum)
: workbook.CreateSheet(dt.TableName);
#region 设置表头
var row = sheet.CreateRow(0);
for (var i = 0; i < dt.Columns.Count; i++)
var cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
#endregion
#region 填充数据
for (var i = 0; i < dt.Rows.Count; i++)
var row1 = sheet.CreateRow(i + 1);
for (var j = 0; j < dt.Columns.Count; j++)
var cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
#endregion
sheetnum++;
#endregion
//转为字节数组
var stream = new MemoryStream();
workbook.Write(stream);
return stream;
catch (Exception e)
return null;
///
/// DataSet数据保存到excel
///
///
excel数据源
///
///
public static void DataSetToWebExcel(DataSet ds, string filename)
IWorkbook workbook;
var fileExt = Path.GetExtension(filename).ToLower();
switch (fileExt)
case ".xlsx":
workbook = new XSSFWorkbook(); //解析2007以后的版本
break;
case ".xls":
workbook = new HSSFWorkbook();//解析2003之前的版本
break;
default:
workbook = null;
break;
#region 设置表头、填充数据
var sheetnum = 1;
foreach (DataTable dt in ds.Tables)
//创建工作簿
var sheet = string.IsNullOrEmpty(dt.TableName)
? workbook.CreateSheet("Sheet" + sheetnum)
: workbook.CreateSheet(dt.TableName);
#region 设置表头
var row = sheet.CreateRow(0);
for (var i = 0; i < dt.Columns.Count; i++)
var cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
#endregion
#region 填充数据
for (var i = 0; i < dt.Rows.Count; i++)
var row1 = sheet.CreateRow(i + 1);
for (var j = 0; j < dt.Columns.Count; j++)
var cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
#endregion
sheetnum++;
#endregion
#region web保存数据到excel
//转为字节数组
var stream = new MemoryStream();
if (workbook != null) workbook.Write(stream);
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}",filename));
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.BinaryWrite(stream.GetBuffer());
HttpContext.Current.Response.End();
#endregion
catch (Exception e)
MyLog4NetInfo.logError(String.Format("/DataSetToWebExcel-导出Excel异常,异常信息：{0}", e));
///
///
public static void DataTableToWebExcel(DataTable dt, string filename)
IWorkbook workbook;
var fileExt = Path.GetExtension(filename).ToLower();
switch (fileExt)
case ".xlsx":
workbook = new XSSFWorkbook(); //解析2007以后的版本
break;
case ".xls":
workbook = new HSSFWorkbook();//解析2003之前的版本
break;
default:
workbook = null;
break;
#region 设置表头、填充数据
var sheetnum = 1;
//创建工作簿
var sheet = string.IsNullOrEmpty(dt.TableName)
? workbook.CreateSheet("Sheet" + sheetnum)
: workbook.CreateSheet(dt.TableName);
#region 设置表头
var row = sheet.CreateRow(0);
for (var i = 0; i < dt.Columns.Count; i++)
var cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
#endregion
#region 填充数据
for (var i = 0; i < dt.Rows.Count; i++)
var row1 = sheet.CreateRow(i + 1);
for (var j = 0; j < dt.Columns.Count; j++)
var cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
#endregion
workbook.Write(stream);
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}",filename));
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.BinaryWrite(stream.GetBuffer());
HttpContext.Current.Response.End();
#endregion
catch (Exception e)
///
/// 将excel中的数据导入到DataTable中
///
///
///
excel工作薄sheet的index值
///
第一行是否是DataTable的列名
///
///
返回的DataTable
public static DataTable ExcelToDataTable(string filename, int sheetIndex, bool isFirstRowColumn, out string message)
var returns = string.Empty;
var data = new DataTable();
_fs = new FileStream(filename, FileMode.Open, FileAccess.Read);
if (filename.IndexOf(".xlsx", StringComparison.Ordinal) > 0) // 2007版本
_workbook = new XSSFWorkbook(_fs);
else if (filename.IndexOf(".xls", StringComparison.Ordinal) > 0) // 2003版本
_workbook = new HSSFWorkbook(_fs);
ISheet sheet;
sheet = _workbook.GetSheetAt(sheetIndex);
// Source名称 fileName+sheetName
var pureFileName = filename.Substring(filename.LastIndexOf("\\") + 1);
var sourceName = pureFileName + "@" + sheet.SheetName;
if (sheet != null)
var firstRow = sheet.GetRow(0);
if (firstRow == null)
returns = "Excel表的列为空！！！";
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
int startRow;
if (isFirstRowColumn)
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
var cell = firstRow.GetCell(i);
var cellValue = cell.StringCellValue;
if (cellValue != null)
var column = new DataColumn(cellValue);
data.Columns.Add(column);
// 循环结束后，在最后添加来源sheet名称，命名为source_sheet
data.Columns.Add("source_sheet");
startRow = sheet.FirstRowNum + 1;
startRow = sheet.FirstRowNum;
//最后一列的标号
var rowCount = sheet.LastRowNum;
for (var i = startRow; i <= rowCount; ++i)
var row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
var dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
dataRow[j] = row.GetCell(j).ToString();
// 循环结束后添加来源Sheet
dataRow[cellCount] = sourceName;
data.Rows.Add(dataRow);
returns = "";
message = returns;
return data;
catch (Exception ex)
//ErrHandler.WriteContent("----执行文件异常----" + ex.Message);
message = ex.Message;
return null;
///
/// 将excel中的数据导入到DataTable中
///
///
文件路径；c://test.xlsx
///
excel工作薄sheet的名称；模板
///
第一行是否是DataTable的列名
///
///
返回的DataTable
public static DataTable ExcelToDataTable(string filename, string sheetname, bool isFirstRowColumn, out string message)
var returns = string.Empty;
var data = new DataTable();
_fs = new FileStream(filename, FileMode.Open, FileAccess.Read);
if (filename.IndexOf(".xlsx", StringComparison.Ordinal) > 0) // 2007版本
_workbook = new XSSFWorkbook(_fs);
else if (filename.IndexOf(".xls", StringComparison.Ordinal) > 0) // 2003版本
_workbook = new HSSFWorkbook(_fs);
ISheet sheet;
sheet = _workbook.GetSheet(sheetname);
// Source名称 fileName+sheetName
var pureFileName = filename.Substring(filename.LastIndexOf("\\") + 1);
var sourceName = pureFileName + "@" + sheet.SheetName;
if (sheet != null)
var firstRow = sheet.GetRow(0);
if (firstRow == null)
returns = "Excel表的列为空！！！";
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
int startRow;
if (isFirstRowColumn)
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
var cell = firstRow.GetCell(i);
var cellValue = cell.StringCellValue;
if (cellValue != null)
var column = new DataColumn(cellValue);
data.Columns.Add(column);
// 循环结束后，在最后添加来源sheet名称，命名为source_sheet
data.Columns.Add("source_sheet");
startRow = sheet.FirstRowNum + 1;
startRow = sheet.FirstRowNum;
//最后一列的标号
var rowCount = sheet.LastRowNum;
for (var i = startRow; i <= rowCount; ++i)
var row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
var dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
dataRow[j] = row.GetCell(j).ToString();
// 循环结束后添加来源Sheet
dataRow[cellCount] = sourceName;
data.Rows.Add(dataRow);
returns = "";
message = returns;
return data;
catch (Exception ex)
//ErrHandler.WriteContent("----执行文件异常----" + ex.Message);
message = ex.Message;
return null;
///
/// 将excel中的数据导入到DataTable中
///
///
execl文件名
///
excel工作薄sheet的名称
///
第一行是否是DataTable的列名
///
返回的DataTable
public static DataTable ExcelToDataTable(string filename, string sheetname, bool isFirstRowColumn)
var returns = string.Empty;
var data = new DataTable();
_fs = new FileStream(filename, FileMode.Open, FileAccess.Read);
if (filename.IndexOf(".xlsx", StringComparison.Ordinal) > 0) // 2007版本
_workbook = new XSSFWorkbook(_fs);
else if (filename.IndexOf(".xls", StringComparison.Ordinal) > 0) // 2003版本
_workbook = new HSSFWorkbook(_fs);
ISheet sheet;
sheet = _workbook.GetSheet(sheetname);
// Source名称 fileName+sheetName
var pureFileName = filename.Substring(filename.LastIndexOf("\\") + 1);
var sourceName = pureFileName + "@" + sheet.SheetName;
if (sheet != null)
var firstRow = sheet.GetRow(0);
if (firstRow == null)
returns = "Excel表的列为空！！！";
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
int startRow;
if (isFirstRowColumn)
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
var cell = firstRow.GetCell(i);
var cellValue = cell.StringCellValue;
if (cellValue != null)
var column = new DataColumn(cellValue);
data.Columns.Add(column);
// 循环结束后，在最后添加来源sheet名称，命名为source_sheet
data.Columns.Add("source_sheet");
startRow = sheet.FirstRowNum + 1;
startRow = sheet.FirstRowNum;
//最后一列的标号
var rowCount = sheet.LastRowNum;
for (var i = startRow; i <= rowCount; ++i)
// var row = sheet.GetRow(i);
// if (row == null) continue; //没有数据的行默认是null
IRow row = sheet.GetRow(i);
if (row != null)
var dataRow = data.NewRow();
for (int j = 0; j < cellCount; ++j)
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
dataRow[j] = row.GetCell(j).ToString();
//for (int j = row.FirstCellNum; j < cellCount; ++j)
// if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
// dataRow[j] = row.GetCell(j).ToString();
// 循环结束后添加来源Sheet
dataRow[cellCount] = sourceName;
data.Rows.Add(dataRow);
return data;
catch (Exception ex)
//ErrHandler.WriteContent("----执行文件异常----" + ex.Message);
return null;
///
/// 获取Excel文件的列数
///
///
public static int GetCellNums(string filename, string sheetname, bool isFirstRowColumn)
int cellnums = 0;
_fs = new FileStream(filename, FileMode.Open, FileAccess.Read);
if (filename.IndexOf(".xlsx", StringComparison.Ordinal) > 0) // 2007版本
_workbook = new XSSFWorkbook(_fs);
else if (filename.IndexOf(".xls", StringComparison.Ordinal) > 0) // 2003版本
_workbook = new HSSFWorkbook(_fs);
ISheet sheet = _workbook.GetSheet(sheetname);
var firstRow = sheet.GetRow(0);
if (firstRow == null)
cellnums = 0;
cellnums = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
return cellnums;
catch (Exception ex)
//ErrHandler.WriteContent("----执行文件异常----" + ex.Message);
return 0;
///
/// 判断指定列名是否存在
///
///
public static string IsExistAppointCellName(string filename, string sheetname, List
listcellnames)
string resul = string.Empty;
_fs = new FileStream(filename, FileMode.Open, FileAccess.Read);
if (filename.IndexOf(".xlsx", StringComparison.Ordinal) > 0) // 2007版本
_workbook = new XSSFWorkbook(_fs);
else if (filename.IndexOf(".xls", StringComparison.Ordinal) > 0) // 2003版本
_workbook = new HSSFWorkbook(_fs);
ISheet sheet = _workbook.GetSheet(sheetname);
var firstRow = sheet.GetRow(0);
if (firstRow == null)
resul = "列名获取失败";
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
List celllist = new List();
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
var cell = firstRow.GetCell(i);
var cellValue = cell.StringCellValue;
if (cellValue != null)
var column = new DataColumn(cellValue);
celllist.Add(column.ColumnName);
string[] arrtemp = listcellnames.Except(celllist).ToArray();
resul = arrtemp.Length > 0 ? string.Join(",", arrtemp) : "";
return resul;
catch (Exception ex)
//ErrHandler.WriteContent("----执行文件异常----" + ex.Message);
return "列名获取失败";
///
/// 获取Excel文件的Sheet个数
///
///
public int GetSheets(string filename)
_fs = new FileStream(filename, FileMode.Open, FileAccess.Read);
if (filename.IndexOf(".xlsx", StringComparison.Ordinal) > 0) // 2007版本
_workbook = new XSSFWorkbook(_fs);
else if (filename.IndexOf(".xls", StringComparison.Ordinal) > 0) // 2003版本
_workbook = new HSSFWorkbook(_fs);
return _workbook.NumberOfSheets;
catch (Exception ex)
//ErrHandler.WriteContent("----执行文件异常----" + ex.Message);
return 0;
///
/// 将DataTable转化为csv文件
///
///
///
public void ConvertDataTableToCsv(DataTable dataTable, String csvPath, ref HashSet titleSet)
if (File.Exists(csvPath))
File.Delete(csvPath);
// Create the file.
using (FileStream fs = File.Create(csvPath))
foreach (DataColumn col in dataTable.Columns)
titleSet.Add(col.Caption);
foreach (DataRow row in dataTable.Rows)
string line = "";
foreach (var item in row.ItemArray)
if (item.GetType().ToString() == "System.String")
line += item + ",";
else if (item.GetType().ToString() == "System.DBNull")
line += ",";
line = line.Remove(line.Length - 1);
line += "\n";
Byte[] info = new UTF8Encoding(true).GetBytes(line);
fs.Write(info, 0, info.Length);
catch (Exception ex)
Console.WriteLine(ex.ToString());
protected virtual void Dispose(bool disposing)
if (!_disposed)
if (disposing)
_fs.Close();
_fs = null;
_disposed = true;
