319 lines
14 KiB
C#
319 lines
14 KiB
C#
|
using System;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using NPOI.XSSF.UserModel;
|
|||
|
|
using NPOI.SS.UserModel;
|
|||
|
|
using NPOI.HSSF.UserModel;
|
|||
|
|
using System.IO;
|
|||
|
|
using System.Data;
|
|||
|
|
using System.Web;
|
|||
|
|
|
|||
|
|
namespace Eson.Utils.ExcelHelper
|
|||
|
|
{
|
|||
|
|
/// <summary>
|
|||
|
|
/// 导出Excel帮助类(文件模板形式，特殊样式时，可自己设置模板)
|
|||
|
|
/// </summary>
|
|||
|
|
public class ExcelFileExport
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
private static ExcelFileExport _instance;
|
|||
|
|
/// <summary>
|
|||
|
|
/// 实例化导出Excel帮助类
|
|||
|
|
/// </summary>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public static ExcelFileExport GetInstance()
|
|||
|
|
{
|
|||
|
|
if (_instance == null)
|
|||
|
|
_instance = new ExcelFileExport();
|
|||
|
|
return _instance;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public IWorkbook workBook;
|
|||
|
|
public ISheet workSheet;
|
|||
|
|
|
|||
|
|
#region Excel文件基本操作
|
|||
|
|
/// <summary>
|
|||
|
|
/// 初始化工作簿(Book)
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="strSaveName">文件名称，也作为工作簿名称</param>
|
|||
|
|
/// <param name="is07Excel">是否导出2007以上版本的Excel</param>
|
|||
|
|
public void InitializeWorkbook(string strSaveName, bool is07Excel)
|
|||
|
|
{
|
|||
|
|
FileStream file = new FileStream(strSaveName, FileMode.Open, FileAccess.Read);
|
|||
|
|
workBook = is07Excel ? (new XSSFWorkbook(file) as IWorkbook) : (new HSSFWorkbook(file) as IWorkbook);
|
|||
|
|
GetSheetByIndex(null);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 按序号打开工作表(Sheet)
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="intSheetIndex">工作表序号，为空时默认第一个工作表</param>
|
|||
|
|
public void GetSheetByIndex(int? intSheetIndex)
|
|||
|
|
{
|
|||
|
|
if (intSheetIndex == null)
|
|||
|
|
workSheet = workBook.GetSheetAt(0);
|
|||
|
|
else
|
|||
|
|
workSheet = workBook.GetSheetAt(Convert.ToInt32(intSheetIndex));
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 按名称打开工作表(Sheet)
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="strSheetName">工作表名称</param>
|
|||
|
|
public void GetSheetByName(string strSheetName)
|
|||
|
|
{
|
|||
|
|
workSheet = workBook.GetSheet(strSheetName);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 初始化工作簿，自动打开第intSheetIndex个Sheet（intSheetIndex默认为0）
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="strExcelFilePath">原Excel文件路径</param>
|
|||
|
|
/// <param name="intSheetIndex">第几个Sheet，默认为0</param>
|
|||
|
|
/// <param name="is07Excel">是否导出2007以上版本的Excel</param>
|
|||
|
|
public void GetSheetFromExcel(string strExcelFilePath, int? intSheetIndex, bool is07Excel)
|
|||
|
|
{
|
|||
|
|
InitializeWorkbook(strExcelFilePath, is07Excel);
|
|||
|
|
GetSheetByIndex(intSheetIndex);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 将工作簿写入文件
|
|||
|
|
/// 如果目标文件已经存在，先删除
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="strSaveFilePath">要保存的文件地址</param>
|
|||
|
|
public void WriteToFile(string strSaveFilePath)
|
|||
|
|
{
|
|||
|
|
FileInfo fi = new FileInfo(strSaveFilePath);
|
|||
|
|
if (fi.Exists)
|
|||
|
|
fi.Delete();
|
|||
|
|
FileStream fs = new FileStream(strSaveFilePath, FileMode.Create);
|
|||
|
|
workBook.Write(fs);
|
|||
|
|
fs.Close();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
///// <summary>
|
|||
|
|
///// 导出Excel文件
|
|||
|
|
///// </summary>
|
|||
|
|
///// <param name="strSaveFilePath">文件路径</param>
|
|||
|
|
///// <param name="strSaveName">保存名称</param>
|
|||
|
|
///// <param name="is07Excel">是否导出2007以上版本的Excel</param>
|
|||
|
|
//public void SaveExcel(string strSaveFilePath, string strSaveName, bool is07Excel)
|
|||
|
|
//{
|
|||
|
|
// WriteToFile(strSaveFilePath);
|
|||
|
|
// Assistant.ToExcel(strSaveFilePath, strSaveName, is07Excel);
|
|||
|
|
//}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
|
|||
|
|
#region 写数据到excel文件
|
|||
|
|
/// <summary>
|
|||
|
|
/// 导出Excel，传入泛型数据
|
|||
|
|
/// </summary>
|
|||
|
|
/// <typeparam name="T">T类型</typeparam>
|
|||
|
|
/// <param name="lst">泛型</param>
|
|||
|
|
/// <param name="dicProperties">想要转换的属性名称列表[key值对应Model属性,value值对应Excel显示的列名]</param>
|
|||
|
|
/// <param name="strSaveName">保存名称</param>
|
|||
|
|
/// <param name="is07Excel">是否导出2007以上版本的Excel，默认是</param>
|
|||
|
|
/// <param name="deamoPath">模板路径（默认不传入时使用默认模板，需要特殊样式时使用自己设置好样式的模板，模板统一放在EXCELFILE下，可在其下另建文件夹，注意模板名称去掉后缀再传入）</param>
|
|||
|
|
//public void WriteListDate<T>(List<T> lst, Dictionary<string, string> dicProperties, string strSaveName, bool is07Excel = true, string deamoPath = null)
|
|||
|
|
// where T : class
|
|||
|
|
//{
|
|||
|
|
// #region 初始化工作簿
|
|||
|
|
// string strSaveFilePath = System.Web.HttpContext.Current.Server.MapPath("~/EXCELFILE/" + Guid.NewGuid().ToString() + (is07Excel ? ".xlsx" : ".xls"));
|
|||
|
|
// File.Copy(System.Web.HttpContext.Current.Server.MapPath(string.Format("~/EXCELFILE/{0}{1}", string.IsNullOrEmpty(deamoPath) ? "demo" : deamoPath, is07Excel ? ".xlsx" : ".xls")), strSaveFilePath);
|
|||
|
|
// InitializeWorkbook(strSaveFilePath, is07Excel);
|
|||
|
|
|
|||
|
|
// //转换数据
|
|||
|
|
// DataTable dtbl = ExtensiveHelper.ConvertListToTable<T>(lst, dicProperties);
|
|||
|
|
|
|||
|
|
// //初始化行
|
|||
|
|
// int rowIndex = 0;
|
|||
|
|
// int intColumn = 0;
|
|||
|
|
// IRow rowExcel = workSheet.CreateRow(rowIndex);
|
|||
|
|
// ICell cellExcel;
|
|||
|
|
|
|||
|
|
// //初始化列
|
|||
|
|
// foreach (KeyValuePair<string, string> property in dicProperties)
|
|||
|
|
// {
|
|||
|
|
// cellExcel = rowExcel.CreateCell(intColumn);
|
|||
|
|
// cellExcel.SetCellValue(property.Value);
|
|||
|
|
// ++intColumn;
|
|||
|
|
// }
|
|||
|
|
// #endregion
|
|||
|
|
|
|||
|
|
// #region 写入内容
|
|||
|
|
// foreach (DataRow rowInfo in dtbl.Rows)
|
|||
|
|
// {
|
|||
|
|
// ++rowIndex;
|
|||
|
|
// rowExcel = workSheet.CreateRow(rowIndex);
|
|||
|
|
// int intColumnContent = 0;
|
|||
|
|
// foreach (KeyValuePair<string, string> property in dicProperties)
|
|||
|
|
// {
|
|||
|
|
// cellExcel = rowExcel.CreateCell(intColumnContent);
|
|||
|
|
// cellExcel.SetCellValue(rowInfo[property.Value].ToString());
|
|||
|
|
// ++intColumnContent;
|
|||
|
|
// }
|
|||
|
|
// }
|
|||
|
|
// #endregion
|
|||
|
|
|
|||
|
|
// //模板流导出
|
|||
|
|
// SaveExcel(strSaveFilePath, strSaveName, is07Excel);
|
|||
|
|
//}
|
|||
|
|
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 输出Excel文件
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="ms">MemoryStream内存流</param>
|
|||
|
|
/// <param name="strSaveName">文件保存名称</param>
|
|||
|
|
/// <param name="is07Excel">是否导出2007以上版本的Excel</param>
|
|||
|
|
//private void ExportExcel(MemoryStream ms, string strSaveName, bool is07Excel)
|
|||
|
|
//{
|
|||
|
|
// ms.Flush();
|
|||
|
|
// ms.Position = 0;
|
|||
|
|
// workBook.Write(ms);
|
|||
|
|
// workBook = null;
|
|||
|
|
// HttpContext current = HttpContext.Current;
|
|||
|
|
// current.Response.ContentType = "application/ms-excel";
|
|||
|
|
// current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + UrlOper.GetToExcelName(strSaveName) + (is07Excel ? ".xlsx" : ".xls"));
|
|||
|
|
// current.Response.BinaryWrite(ms.ToArray());
|
|||
|
|
// current.Response.End();
|
|||
|
|
// ms.Close();
|
|||
|
|
// ms = null;
|
|||
|
|
//}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 导出Excel，传入DataTable内存表
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="dtbl">DataTabke内存表</param>
|
|||
|
|
/// <param name="dicProperties">想要转换的属性名称列表[key值对应Model属性,value值对应Excel显示的列名]</param>
|
|||
|
|
/// <param name="strSaveName">保存名称</param>
|
|||
|
|
/// <param name="is07Excel">是否导出2007以上版本的Excel，默认是</param>
|
|||
|
|
/// <param name="deamoPath">模板路径（默认不传入时使用默认模板，需要特殊样式时使用自己设置好样式的模板，模板统一放在EXCELFILE下，可在其下另建文件夹，注意模板名称去掉后缀再传入）</param>
|
|||
|
|
//public void WriteListDate(DataTable dtbl, Dictionary<string, string> dicProperties, string strSaveName, bool is07Excel = true, string deamoPath = null)
|
|||
|
|
//{
|
|||
|
|
// #region 初始化工作簿
|
|||
|
|
// string strSaveFilePath = System.Web.HttpContext.Current.Server.MapPath("~/EXCELFILE/" + Guid.NewGuid().ToString() + (is07Excel ? ".xlsx" : ".xls"));
|
|||
|
|
// File.Copy(System.Web.HttpContext.Current.Server.MapPath(string.Format("~/EXCELFILE/{0}{1}", string.IsNullOrEmpty(deamoPath) ? "demo" : deamoPath, is07Excel ? ".xlsx" : ".xls")), strSaveFilePath);
|
|||
|
|
// InitializeWorkbook(strSaveFilePath, is07Excel);
|
|||
|
|
|
|||
|
|
// //初始化行
|
|||
|
|
// int rowIndex = 0;
|
|||
|
|
// int intColumn = 0;
|
|||
|
|
// IRow rowExcel = workSheet.CreateRow(rowIndex);
|
|||
|
|
// ICell cellExcel;
|
|||
|
|
|
|||
|
|
// //初始化列
|
|||
|
|
// foreach (KeyValuePair<string, string> property in dicProperties)
|
|||
|
|
// {
|
|||
|
|
// cellExcel = rowExcel.CreateCell(intColumn);
|
|||
|
|
// cellExcel.SetCellValue(property.Value);
|
|||
|
|
// ++intColumn;
|
|||
|
|
// }
|
|||
|
|
// #endregion
|
|||
|
|
|
|||
|
|
// #region 写入内容
|
|||
|
|
// foreach (DataRow rowInfo in dtbl.Rows)
|
|||
|
|
// {
|
|||
|
|
// ++rowIndex;
|
|||
|
|
// rowExcel = workSheet.CreateRow(rowIndex);
|
|||
|
|
// int intColumnContent = 0;
|
|||
|
|
// foreach (KeyValuePair<string, string> property in dicProperties)
|
|||
|
|
// {
|
|||
|
|
// cellExcel = rowExcel.CreateCell(intColumnContent);
|
|||
|
|
// cellExcel.SetCellValue(GetColumValue(dtbl, rowInfo, property.Key));
|
|||
|
|
// ++intColumnContent;
|
|||
|
|
// }
|
|||
|
|
// }
|
|||
|
|
// #endregion
|
|||
|
|
|
|||
|
|
// //模板流导出
|
|||
|
|
// SaveExcel(strSaveFilePath, strSaveName, is07Excel);
|
|||
|
|
//}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 通过提供的列名和具体行，返回DataTable中具体的某一列的值
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="dtbl">内存表</param>
|
|||
|
|
/// <param name="rowInfo">具体行</param>
|
|||
|
|
/// <param name="columName">列名</param>
|
|||
|
|
/// <returns>具体某列的值</returns>
|
|||
|
|
public string GetColumValue(DataTable dtbl, DataRow rowInfo, string columName)
|
|||
|
|
{
|
|||
|
|
string clumValue = "";
|
|||
|
|
foreach (DataColumn clum in dtbl.Columns)
|
|||
|
|
{
|
|||
|
|
if (clum.ColumnName.Equals(columName))
|
|||
|
|
{
|
|||
|
|
clumValue = rowInfo[clum].ToString();
|
|||
|
|
break;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
return clumValue;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 将DataTable导出成Excel文件
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="lstDtbl">DataTabke内存表泛型</param>
|
|||
|
|
/// <param name="dicProperties">字典泛型，想要转换的属性名称列表[key值对应Model属性,value值对应Excel显示的列名]</param>
|
|||
|
|
/// <param name="strSaveName">保存名称</param>
|
|||
|
|
/// <param name="intLineCount">换行数，表之间的间距,不换行可以默认输入null,也可以输入0</param>
|
|||
|
|
/// <param name="is07Excel">是否导出2007以上版本的Excel，默认是</param>
|
|||
|
|
/// <param name="deamoPath">模板路径（默认不传入时使用默认模板，需要特殊样式时使用自己设置好样式的模板，模板统一放在EXCELFILE下，可在其下另建文件夹，注意模板名称去掉后缀再传入）</param>
|
|||
|
|
//public void WriteListDate(List<DataTable> lstDtbl, List<Dictionary<string, string>> lstDicProperties, string strSaveName, int? intLineCount, bool is07Excel = true, string deamoPath = null)
|
|||
|
|
//{
|
|||
|
|
// #region 初始化工作簿
|
|||
|
|
// string strSaveFilePath = System.Web.HttpContext.Current.Server.MapPath("~/EXCELFILE/" + Guid.NewGuid().ToString() + (is07Excel ? ".xlsx" : ".xls"));
|
|||
|
|
// File.Copy(System.Web.HttpContext.Current.Server.MapPath(string.Format("~/EXCELFILE/{0}.{1}", string.IsNullOrEmpty(deamoPath) ? "demo" : deamoPath, is07Excel ? ".xlsx" : ".xls")), strSaveFilePath);
|
|||
|
|
// InitializeWorkbook(strSaveFilePath, is07Excel);
|
|||
|
|
|
|||
|
|
// //初始化行
|
|||
|
|
// int rowIndex = 0;
|
|||
|
|
// IRow rowExcel;
|
|||
|
|
// ICell cellExcel;
|
|||
|
|
|
|||
|
|
// #endregion
|
|||
|
|
|
|||
|
|
// #region 写内容
|
|||
|
|
// for (int i = 0; i < lstDicProperties.Count; i++)
|
|||
|
|
// {
|
|||
|
|
// //写表头
|
|||
|
|
// rowExcel = workSheet.CreateRow(rowIndex);
|
|||
|
|
// int intColumn = 0;
|
|||
|
|
// foreach (KeyValuePair<string, string> property in lstDicProperties[i])
|
|||
|
|
// {
|
|||
|
|
// cellExcel = rowExcel.CreateCell(intColumn);
|
|||
|
|
// cellExcel.SetCellValue(property.Value);
|
|||
|
|
// ++intColumn;
|
|||
|
|
// }
|
|||
|
|
|
|||
|
|
// //写入内容
|
|||
|
|
// foreach (DataRow rowInfo in lstDtbl[i].Rows)
|
|||
|
|
// {
|
|||
|
|
// ++rowIndex;
|
|||
|
|
// rowExcel = workSheet.CreateRow(rowIndex);
|
|||
|
|
// int intColumnContent = 0;
|
|||
|
|
// foreach (KeyValuePair<string, string> property in lstDicProperties[i])
|
|||
|
|
// {
|
|||
|
|
// cellExcel = rowExcel.CreateCell(intColumnContent);
|
|||
|
|
// cellExcel.SetCellValue(GetColumValue(lstDtbl[i], rowInfo, property.Key));
|
|||
|
|
// ++intColumnContent;
|
|||
|
|
// }
|
|||
|
|
// }
|
|||
|
|
|
|||
|
|
// ++rowIndex;
|
|||
|
|
|
|||
|
|
// //换行
|
|||
|
|
// rowIndex += intLineCount == null ? 0 : Convert.ToInt32(intLineCount);
|
|||
|
|
// }
|
|||
|
|
// #endregion
|
|||
|
|
|
|||
|
|
// //模板流导出
|
|||
|
|
// SaveExcel(strSaveFilePath, strSaveName, is07Excel);
|
|||
|
|
//}
|
|||
|
|
#endregion
|
|||
|
|
}
|
|||
|
|
}
|