/// <summary>
/// excel操作通用类
/// </summary>
public class ExcelHelper : IDisposable
{
// private static string _fileName; //文件名
private static bool _disposed;
private static FileStream _fs;
private static IWorkbook _workbook;
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
/// <summary>
/// 判断工作部是否存在
/// </summary>
/// <param name="filename">execl文件名</param>
/// <param name="sheetname">excel工作薄sheet的名称</param>
/// <returns>返回的DataTable</returns>
public static bool IsSheetExist(string filename, string sheetname)
{
var flag = true;
try
{
_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;
}
/// <summary>
/// 判断execl是否有数据
/// </summary>
/// <param name="filename">execl文件名</param>
/// <param name="sheetname">excel工作薄sheet的名称</param>
/// <returns>返回的DataTable</returns>
public static bool IsEmpty(string filename, string sheetname)
{
var flag = true;
try
{
_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;
}
}
else
{
return false;
}
}
catch (Exception ex)
{
//ErrHandler.WriteContent("----执行文件异常----" + ex.Message);
return false;
}
return flag;
}
/// <summary>
/// 将DataTable数据导入到excel中
/// </summary>
/// <param name="data">要导入的数据</param>
/// <param name="filename">文件名字</param>
/// <param name="sheetName">要导入的excel的sheet的名称</param>
/// <param name="isColumnWritten">DataTable的列名是否要导入</param>
/// <returns>导入数据行数(包含列名那一行)</returns>
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();
try
{
ISheet sheet;
if (_workbook != null)
{
sheet = _workbook.CreateSheet(sheetName);
}
else
{
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;
}
else
{
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)
{
try
{
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;
}
}
/// <summary>
/// DataSet数据保存到excel
/// </summary>
/// <param name="ds">excel数据源</param>
/// <param name="filename"></param>
/// <returns></returns>
public static void DataSetToWebExcel(DataSet ds, string filename)
{
try
{
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));
}
}
/// <summary>
///
/// </summary>
/// <param name="dt"></param>
/// <param name="filename"></param>
public static void DataTableToWebExcel(DataTable dt, string filename)
{
try
{
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
#endregion
#region web保存数据到excel
//转为字节数组
var stream = new MemoryStream();
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)
{
}
}
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="filename"></param>
/// <param name="sheetIndex">excel工作薄sheet的index值</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <param name="message"></param>
/// <returns>返回的DataTable</returns>
public static DataTable ExcelToDataTable(string filename, int sheetIndex, bool isFirstRowColumn, out string message)
{
var returns = string.Empty;
var data = new DataTable();
try
{
_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表的列为空！！！";
}
else
{
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;
}
else
{
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;
}
}
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="filename">文件路径；c://test.xlsx</param>
/// <param name="sheetname">excel工作薄sheet的名称；模板</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <param name="message"></param>
/// <returns>返回的DataTable</returns>
public static DataTable ExcelToDataTable(string filename, string sheetname, bool isFirstRowColumn, out string message)
{
var returns = string.Empty;
var data = new DataTable();
try
{
_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表的列为空！！！";
}
else
{
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;
}
else
{
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;
}
}
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="filename">execl文件名</param>
/// <param name="sheetname">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns>返回的DataTable</returns>
public static DataTable ExcelToDataTable(string filename, string sheetname, bool isFirstRowColumn)
{
var returns = string.Empty;
var data = new DataTable();
try
{
_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表的列为空！！！";
}
else
{
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;
}
else
{
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;
}
}
/// <summary>
/// 获取Excel文件的列数
/// </summary>
/// <returns></returns>
public static int GetCellNums(string filename, string sheetname, bool isFirstRowColumn)
{
int cellnums = 0;
try
{
_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;
}
else
{
cellnums = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
}
return cellnums;
}
catch (Exception ex)
{
//ErrHandler.WriteContent("----执行文件异常----" + ex.Message);
return 0;
}
}
/// <summary>
/// 判断指定列名是否存在
/// </summary>
/// <returns></returns>
public static string IsExistAppointCellName(string filename, string sheetname, List<string> listcellnames)
{
string resul = string.Empty;
try
{
_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 = "列名获取失败";
}
else
{
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
List<string> celllist = new List<string>();
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 "列名获取失败";
}
}
/// <summary>
/// 获取Excel文件的Sheet个数
/// </summary>
/// <returns></returns>
public int GetSheets(string filename)
{
try
{
_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;
}
}
/// <summary>
/// 将DataTable转化为csv文件
/// </summary>
/// <param name="dataTable"></param>
/// <param name="csvPath"></param>
public void ConvertDataTableToCsv(DataTable dataTable, String csvPath, ref HashSet<string> titleSet)
{
try
{
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;
}
}
}
注：需要引用NOPI.dll
下载地址：https://www.nuget.org/packages/NPOI/
