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
|
||
}
|
||
}
|