/// <summary>
/// DataTable导出到Excel
/// </summary>
/// <param name="enumExceltype">Excel文件格式</param>
/// <param name="encoding"></param>
public DataTableExporter(EnumExcelType enumExceltype, Encoding encoding)
{
if (encoding == null)
{
throw new ArgumentNullException("encoding");
}
switch (enumExceltype)
{
case EnumExcelType.XLS:
iworkbook = new HSSFWorkbook();
break;
case EnumExcelType.XLSX:
iworkbook = new XSSFWorkbook();
break;
default:
throw new ArgumentException("参数enumExceltype的值“" + enumExceltype.ToString() + "”无效。");
}
this.ExcelType = enumExceltype;
this.Encoding = encoding;
}
/// <summary>
///
/// </summary>
/// <typeparam name="T">Data Type which you want to write to excel file</typeparam>
/// <param name="data">The data source which will be written to excel file</param>
/// <returns></returns>
public static Int32 Generate(Object data, String tempGeneratedFolder, String sheetName, out String generatedFileName, EnumExcelType excelType = EnumExcelType.NONE)
{
Int32 result = 0;
IWorkbook wBook = null;
StringBuilder fullFileName = new StringBuilder();
FileMode fsMode = FileMode.CreateNew;
generatedFileName = String.Empty;
if (ValidateTempGeneratedFolder(tempGeneratedFolder))
{
generatedFileName = GenerateRandomFileName();
//Create new excel file based on Excel 97/2003
try
{
//Validate SheetName or generate default sheet name from filename
if (String.IsNullOrWhiteSpace(sheetName))
sheetName = generatedFileName;
if (excelType == EnumExcelType.XLSX)
generatedFileName += ".xlsx";
else
generatedFileName += ".xls";
fullFileName.Append(tempGeneratedFolder);
fullFileName.Append("\\");
fullFileName.Append(generatedFileName);
if (File.Exists(fullFileName.ToString()))
fsMode = FileMode.Open;
else
fsMode = FileMode.CreateNew;
using (FileStream fs = new FileStream(fullFileName.ToString(), fsMode, FileAccess.ReadWrite))
{
// Check
if (excelType == EnumExcelType.XLSX)
{
if (fsMode == FileMode.Open)
wBook = new XSSFWorkbook(fs);
else
wBook = new XSSFWorkbook();
}
else
{
if (fsMode == FileMode.Open)
wBook = new HSSFWorkbook(fs);
else
wBook = new HSSFWorkbook();
}
//Write data to sheet
Int32 maxRow = 0;
try
{
WriteDataToSheet(data, ref wBook, sheetName, 0, 0, out maxRow);
}
catch
{
}
//Write and close the file.
wBook.Write(fs);
fs.Close();
}
}
catch
{
throw;
}
}
return result;
}
/// <summary>
/// DataTable导出到Excel
/// </summary>
/// <param name="enumExceltype">Excel文件格式</param>
public DataTableExporter(EnumExcelType enumExceltype) : this(enumExceltype, Encoding.GetEncoding(936))
{
}
/// <summary>
///
/// </summary>
/// <typeparam name="T">Data Type which you want to write to excel file</typeparam>
/// <param name="data">The data source which will be written to excel file</param>
/// <returns></returns>
public static Int32 GenerateEx(
IEnumerable <ObjectDataWrapper> datas,
String tempGeneratedFolder,
out String generatedFileName,
string workbookTitle = "",
string workbookAuthor = "",
string workbookSubject = "",
string workbookKeywords = "",
string fromTemplateFileName = "",
string templatePassword = "",
EnumExcelType excelType = EnumExcelType.NONE)
{
Int32 result = 0;
IWorkbook wBook = null;
String fullFileName;
FileMode fsMode = FileMode.CreateNew;
generatedFileName = String.Empty;
if (ValidateTempGeneratedFolder(tempGeneratedFolder))
{
generatedFileName = GenerateRandomFileName();
//Create new excel file based on Excel 97/2003
try
{
if (excelType == EnumExcelType.XLSX)
{
generatedFileName += ".xlsx";
}
else
{
generatedFileName += ".xls";
}
fullFileName = Path.Combine(tempGeneratedFolder, generatedFileName).ToString();
if (File.Exists(fullFileName.ToString()))
{
fsMode = FileMode.Open;
}
else
{
fsMode = FileMode.CreateNew;
}
if (String.IsNullOrWhiteSpace(fromTemplateFileName))
{
using (FileStream fs = new FileStream(fullFileName.ToString(), fsMode, FileAccess.ReadWrite))
{
// Check
if (excelType == EnumExcelType.XLSX)
{
if (fsMode == FileMode.Open)
{
wBook = new XSSFWorkbook(fs);
}
else
{
wBook = new XSSFWorkbook();
}
POIXMLProperties xmlProps = ((XSSFWorkbook)wBook).GetProperties();
xmlProps.CoreProperties.Title = workbookTitle;
xmlProps.CoreProperties.Creator = workbookAuthor;
xmlProps.CoreProperties.Subject = workbookSubject;
xmlProps.CoreProperties.Keywords = workbookKeywords;
}
else
{
if (fsMode == FileMode.Open)
{
wBook = new HSSFWorkbook(fs);
}
else
{
wBook = new HSSFWorkbook();
}
SummaryInformation summaryInformation = ((HSSFWorkbook)wBook).SummaryInformation;
if (summaryInformation == null)
{
((HSSFWorkbook)wBook).CreateInformationProperties();
summaryInformation = ((HSSFWorkbook)wBook).SummaryInformation;
}
summaryInformation.Title = workbookTitle;
summaryInformation.Author = workbookAuthor;
summaryInformation.Subject = workbookSubject;
summaryInformation.Keywords = workbookKeywords;
}
//Write data to sheet
Int32 maxRow = 0;
try
{
foreach (var data in datas)
{
WriteDataToSheet(data.Data, ref wBook, data.SheetName, 0, 0, out maxRow);
}
}
catch
{
}
//Write and close the file.
if (excelType == EnumExcelType.XLSX)
{
XSSFFormulaEvaluator.EvaluateAllFormulaCells(wBook);
}
else
{
HSSFFormulaEvaluator.EvaluateAllFormulaCells(wBook);
}
wBook.Write(fs);
fs.Close();
}
}
else
{
FileInfo fileTemplate = new FileInfo(fromTemplateFileName);
if (!fileTemplate.Exists)
{
throw new Exception("File template doesn't exists.");
}
using (FileStream fs = new FileStream(fromTemplateFileName.ToString(), FileMode.Open, FileAccess.ReadWrite))
{
// Check
if (excelType == EnumExcelType.XLSX)
{
wBook = new XSSFWorkbook(fs);
POIXMLProperties xmlProps = ((XSSFWorkbook)wBook).GetProperties();
xmlProps.CoreProperties.Title = workbookTitle;
xmlProps.CoreProperties.Creator = workbookAuthor;
xmlProps.CoreProperties.Subject = workbookSubject;
xmlProps.CoreProperties.Keywords = workbookKeywords;
}
else
{
wBook = new HSSFWorkbook(fs);
SummaryInformation summaryInformation = ((HSSFWorkbook)wBook).SummaryInformation;
if (summaryInformation == null)
{
((HSSFWorkbook)wBook).CreateInformationProperties();
summaryInformation = ((HSSFWorkbook)wBook).SummaryInformation;
}
summaryInformation.Title = workbookTitle;
summaryInformation.Author = workbookAuthor;
summaryInformation.Subject = workbookSubject;
summaryInformation.Keywords = workbookKeywords;
}
//Write data to sheet
Int32 maxRow = 0;
try
{
foreach (var data in datas)
{
WriteDataToSheet(data.Data, ref wBook, data.SheetName, 0, 0, out maxRow);
}
}
catch
{
}
//Write and close the file.
FileStream fsOut = new FileStream(fullFileName, FileMode.CreateNew, FileAccess.ReadWrite);
if (excelType == EnumExcelType.XLSX)
{
XSSFFormulaEvaluator.EvaluateAllFormulaCells(wBook);
}
else
{
HSSFFormulaEvaluator.EvaluateAllFormulaCells(wBook);
}
wBook.Write(fsOut);
fs.Close();
fsOut.Close();
}
}
}
catch
{
throw;
}
}
return(result);
}
