protected virtual bool CheckParameters(DataTable dtSource,
int iStartRow,
int iStartColumn,
string strFilePath,
string strTemplatePath,
ref ExportResult result)
{
if (null == dtSource)
{
result.Message = "数据源为空引用！";
result.Status = false;
return(false);
}
if (iStartRow < 0 || iStartRow > 65535)
{
result.Message = "起始行不满足条件（0~65535）！";
result.Status = false;
return(false);
}
if (iStartColumn < 0 || iStartColumn > 256)
{
result.Message = "起始列不满足条件（0~256）！";
result.Status = false;
return(false);
}
if (string.IsNullOrWhiteSpace(strFilePath))
{
result.Message = "请输入导出文件名称！";
result.Status = false;
return(false);
}
if (string.IsNullOrWhiteSpace(strTemplatePath))
{
result.Message = "请输入模板文件名称！";
result.Status = false;
return(false);
}
if (!File.Exists(strTemplatePath))
{
result.Message = "模板文件不存在！";
result.Status = false;
return(false);
}
return(true);
}
static void ExportExcel(int recordCount)
{
var template_file = "Template\\进出记录.xlsx";
var now = DateTime.Now;
var filePath = $"TempFiles\\{now.Year}\\{now.Month}\\{now.Day}\\{now.ToString("HHmmss")}";
var result_file = $"{filePath}\\进出记录_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
if (!Directory.Exists(filePath + "\\pics"))
{
Directory.CreateDirectory(filePath + "\\pics");
}
// 模拟文件拷贝
File.Copy(@".\\pics\image.jpg", $"{filePath}\\pics\\image.jpg");
var excel = new ExportExtend();
var result = new ExportResult();
var table = GetTestData(recordCount);
excel.ExportExcelByTemplate(table, 1, 0, result_file, "进出记录", template_file, ref result);
Console.WriteLine("执行结果：");
var pre_str = "\t";
Console.WriteLine(pre_str + result.Status);
if (string.IsNullOrWhiteSpace(result.Message))
{
Console.WriteLine(pre_str + result.Message);
}
if (null != result.Error)
{
Console.WriteLine(pre_str + result.Error.ToString());
}
}
public virtual void ExportExcelByTemplate(DataTable dtSource,
int iStartRow,
int iStartColumn,
string strFilePath,
string sheetName,
string strTemplatePath,
ref ExportResult result)
{
if (!CheckParameters(dtSource, iStartRow, iStartColumn, strFilePath, strTemplatePath, ref result))
{
return;
}
try
{
File.Copy(strTemplatePath, strFilePath, true);
// 获取 Workbook 对象
IWorkbook workbook = GetWorkbook(strFilePath);
// 获取 Sheet 对象
ISheet sheet = GetSheet(workbook, sheetName);
// 数据
CreateRows(iStartRow, iStartColumn, workbook, sheet, dtSource);
// 保存
SaveExcel(workbook, strFilePath);
result.Status = true;
}
catch (Exception ex)
{
result.Status = false;
result.Error = ex;
}
}
