public static class NPOIHelper
{
/// <summary>
/// Excel导入,返回Datable
/// </summary>
/// <param name="file">要导入的Excel文件(包含路径,文件名与扩展名)</param>
/// <returns>返回Datable</returns>
public static DataTable ExcelToTable(string file, string sheetName)
{
try
{
//定义DT
DataTable dt = new DataTable();
//工作表
IWorkbook workbook = null;
//获取文件
string fileExt = Path.GetExtension(file).ToLower();
//读取文件
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
fs.Position = 0;
//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); }
else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); }
else { return null; }
//if (workbook == null) { return null; }
ISheet sheet = workbook.GetSheet(sheetName); //获取第1页
//表头 第1行位表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
//讲表头填充到DataTable
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
public static DataTable ExcelToDataSet(string file)
{
try
{
//定义DT
DataTable dt = new DataTable();
//工作表
IWorkbook workbook = null;
//获取文件
string fileExt = Path.GetExtension(file).ToLower();
//读取文件
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
fs.Position = 0;
//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); }
else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); }
else { return null; }
//if (workbook == null) { return null; }
ISheet sheet = workbook.GetSheet(""); //获取第1页
//表头 第1行位表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
//讲表头填充到DataTable
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
public static int DataTableToExcel(DataTable data, string sheetName, string fileName, bool isColumnWritten)
{
int i = 0;
int j = 0;
int count = 0;
ISheet sheet = null;
IWorkbook workbook = null;
var fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook();
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook();
try
{
if (workbook != null)
{
sheet = workbook.CreateSheet(sheetName);
}
else
{
return -1;
}
if (isColumnWritten == true) //写入DataTable的列名
{
IRow 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;
}
for (i = 0; i < data.Rows.Count; ++i)
{
IRow 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 bool DataSetToExcel(DataSet dataSet, string fileName, bool isColumnWritten)
{
IWorkbook workbook = null;
var fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook();
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook();
for (int t = 0; t < dataSet.Tables.Count; t++)
{
var data = dataSet.Tables[t];
var sheetName = data.TableName;
int i = 0;
int j = 0;
int count = 0;
ISheet sheet = null;
try
{
if (workbook != null)
{
sheet = workbook.CreateSheet(sheetName);
}
else
{
return false;
}
if (isColumnWritten == true) //写入DataTable的列名
{
IRow 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;
}
for (i = 0; i < data.Rows.Count; ++i)
{
IRow row = sheet.CreateRow(count);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
}
++count;
}
}
catch (Exception ex)
{
return false;
}
}
workbook.Write(fs); //写入到excel
fs.Close();
fs.Dispose();
return true;
}
public static void StreamToFile(Stream stream, string fileName)
{
// 把 Stream 转换成 byte[]
byte[] bytes = new byte[stream.Length];
stream.Read(bytes, 0, bytes.Length);
// 设置当前流的位置为流的开始
stream.Seek(0, SeekOrigin.Begin);
// 把 byte[] 写入文件
FileStream fs = new FileStream(fileName, FileMode.Create);
BinaryWriter bw = new BinaryWriter(fs);
bw.Write(bytes);
bw.Close();
fs.Close();
}
public static IList<T> DtExchangeEntList<T>(DataTable dt) where T : new()
{
IList<T> list = new List<T>();
if (dt != null)
{
//循环DataTable的行
for (int j = 1; j < dt.Rows.Count; j++)
{
T model = new T();
var modelType = model.GetType();
for (int i = 0; i < dt.Columns.Count; i++)
{
foreach (var item in modelType.GetProperties())
{
string ColumnName = dt.Columns[i].ColumnName;
if (ColumnName == item.Name)
{
if (item.PropertyType == typeof(string))
{
try
{
item.SetValue(model, dt.Rows[j][i].ToString().Trim(), null);
}
catch
{
item.SetValue(model, "", null);
}
}
else if (item.PropertyType == typeof(int))
{
try
{
double temp = double.Parse(dt.Rows[j][i].ToString().Trim());
item.SetValue(model, Convert.ToInt32(temp.ToString()), null);
}
catch
{
item.SetValue(model, 0, null);
}
}
else if (item.PropertyType == typeof(float))
{
try
{
item.SetValue(model, float.Parse(dt.Rows[j][i].ToString().Trim()), null);
}
catch
{
item.SetValue(model, 0.0, null);
}
}
else if (item.PropertyType == typeof(double))
{
try
{
item.SetValue(model, double.Parse(dt.Rows[j][i].ToString().Trim()), null);
}
catch
{
item.SetValue(model, 0.0, null);
}
}
else if (item.PropertyType == typeof(DateTime))
{
try
{
item.SetValue(model, DateTime.Parse(dt.Rows[j][i].ToString().Trim()), null);
}
catch
{
item.SetValue(model, null, null);
}
}
else if (item.PropertyType == typeof(bool))
{
try
{
string MS = dt.Rows[j][i].ToString().Trim();
if (MS == "否" || MS == "0")
{
item.SetValue(model, false, null);
}
else if (MS == "是" || MS == "1")
{
item.SetValue(model, true, null);
}
}
catch
{
item.SetValue(model, false, null);
}
}
}
}
}
list.Add(model);
}
}
return list;
}
/// <summary>
/// Datable导出,保存为单页签Excel
/// </summary>
/// <param name="dt">要导出的Datable</param>
/// <param name="file">Excel文件(包括路径,文件名与扩展名)</param>
public static void TableToExcel(DataTable dt, string file)
{
try
{
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
if (workbook == null) { return; }
ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Datable导出,保存为单页签Excel,MVC专用
/// </summary>
/// <param name="dt">要导出的Datable</param>
/// <param name="file">Excel文件(包括路径,文件名与扩展名) application/vnd.ms-excel</param>
public static MemoryStream TableToExcelForMVC(DataTable dt, string file)
{
try
{
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
if (workbook == null) { return null; }
ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//转为字节数组
NpoiMemoryStream stream = new NpoiMemoryStream();
stream.AllowClose = false;
workbook.Write(stream);
stream.Seek(0, SeekOrigin.Begin);
stream.AllowClose = true;
return stream;
//var buf = stream.ToArray();
保存为Excel文件
//using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
//{
// fs.Write(buf, 0, buf.Length);
// fs.Flush();
//}
}
catch (Exception ex)
{
throw ex;
}
}
//新建类 重写Npoi流方法
public class NpoiMemoryStream : MemoryStream
{
public NpoiMemoryStream()
{
AllowClose = true;
}
public bool AllowClose { get; set; }
public override void Close()
{
if (AllowClose)
base.Close();
}
}
/// <summary>
/// Datable导出,保存为1个或多个页签的Excel
/// </summary>
/// <param name="dt">要导出的Datable</param>
/// <param name="file">Excel文件(包括路径,文件名与扩展名)</param>
/// <param name="sheetCount">页签数</param>
public static void TableToExcelForSheets(DataTable dt, string file, int sheetCount = 1)
{
try
{
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
if (workbook == null) { return; }
//页签数判断
if (sheetCount == 1)
{
ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
}
else
{
for (int k = 1; k <= sheetCount; k++)
{
ISheet sheet = workbook.CreateSheet("Sheet" + k);
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
var rows = (dt.Rows.Count / sheetCount);
var n = 0;
for (int i = rows * (k - 1); i < rows * k; i++)
{
IRow row1 = sheet.CreateRow(n + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
n++;
}
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Datable导出,保存为1个或多个页签的Excel
/// </summary>
/// <param name="dt">要导出的Datable</param>
/// <param name="file">Excel文件(包括路径,文件名与扩展名)</param>
/// <param name="rows">每页的记录数</param>
public static void TableToExcelForRows(DataTable dt, string file, int rows)
{
try
{
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
if (workbook == null) { return; }
var sheetCount = Math.Ceiling(((double)dt.Rows.Count / (double)rows));
//页签数判断
if (sheetCount == 1)
{
ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
}
else if (sheetCount > 1)
{
int rowcount;
for (int k = 1; k <= sheetCount; k++)
{
ISheet sheet = workbook.CreateSheet("Sheet" + k);
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
//var rows = (dt.Rows.Count / sheetCount);
var n = 0;
rowcount = rows * k;
//如果越界,取最大值
if (rowcount > dt.Rows.Count)
{
rowcount = dt.Rows.Count;
}
for (int i = rows * (k - 1); i < rowcount; i++)
{
IRow row1 = sheet.CreateRow(n + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
n++;
}
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
catch (Exception ex)
{
throw ex;
}
}
//获取单元格值
private static object GetValueType(ICell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
return cell.NumericCellValue;
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
public static DataTable ListToDataTable<T>(List<T> entitys, Dictionary<string, string> dic = null)
{
DataTable dt = new DataTable();
//检查实体集合不能为空
if (entitys == null || entitys.Count < 1)
return dt;
//取出第一个实体的所有Propertie
Type entityType = entitys[0].GetType();
PropertyInfo[] entityProperties = entityType.GetProperties();
//生成DataTable的structure
//生产代码中，应将生成的DataTable结构Cache起来，此处略
for (int i = 0; i < entityProperties.Length; i++)
{
//dt.Columns.Add(entityProperties[i].Name, entityProperties[i].PropertyType);
dt.Columns.Add(entityProperties[i].Name);
}
if (dic != null)
{
var dr = dt.NewRow();
for (int i = 0; i < dt.Columns.Count; i++)
{
var columnName = dt.Columns[i].ColumnName;
if (dic.Keys.Contains(columnName))
dr[i] = dic[columnName];
else
dr[i] = "";
}
dt.Rows.Add(dr);
}
//将所有entity添加到DataTable中
foreach (object entity in entitys)
{
//检查所有的的实体都为同一类型
if (entity.GetType() != entityType)
{
throw new Exception("要转换的集合元素类型不一致");
}
object[] entityValues = new object[entityProperties.Length];
for (int i = 0; i < entityProperties.Length; i++)
{
entityValues[i] = entityProperties[i].GetValue(entity, null);
}
dt.Rows.Add(entityValues);
}
return dt;
}
public static bool CopySheet(string sourceFile, string sourceSheetName, string destionFile, string destionSheetName)
{
var flag = true;
ISheet sheet = null;
DataTable data = new DataTable();
IWorkbook workbook = null;
FileStream fs = null;
try
{
fs = new FileStream(sourceFile, FileMode.Open, FileAccess.Read);
if (sourceFile.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (sourceFile.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook(fs);
if (sourceFile != null)
{
sheet = workbook.GetSheet(sourceSheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet == null)
return false;
HSSFWorkbook workbookMerged = new HSSFWorkbook();
IWorkbook workbookDestionation = null;
var fsDestionation = new FileStream(destionFile, FileMode.Open, FileAccess.ReadWrite);
if (destionFile.IndexOf(".xlsx") > 0) // 2007版本
workbookDestionation = new XSSFWorkbook(fsDestionation);
else if (destionFile.IndexOf(".xls") > 0) // 2003版本
workbookDestionation = new HSSFWorkbook(fsDestionation);
//sheet.CrossCloneSheet(workbookDestionation, "1");
sheet.CopyTo(workbookMerged, sourceSheetName, true, true);
using (FileStream fsw = new FileStream(destionFile, FileMode.Append, FileAccess.Write))
{
workbookMerged.Write(fsw);
}
}
catch (Exception ex)
{
///记录日志
flag = false;
}
return flag;
}
}生成Excel多个sheet页的demo
var dtBasic = NPOIHelper.ListToDataTable(list);
dtBasic.TableName = "Sheet1名称";
var dtBasicTwo = NPOIHelper.ListToDataTable(listTwo);
dtBasicTwo.TableName = "Sheet2名称";
var dataSet = new DataSet();
dataSet.Tables.Add(dtBasic);
dataSet.Tables.Add(dtBasicTwo);
NPOIHelper.DataSetToExcel(dataSet, downLoadPath, true);
201
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
