private static void SetExcelWorksheetInfo(ref ExcelWorksheet excelSheet, ExcelSheetInfo sheetInfo)
{
if (excelSheet == null || sheetInfo == null || sheetInfo.Info == null)
{
return;
}
StandardFormatExcelWorksheet(ref excelSheet);
var info = sheetInfo.Info;
for (int i = 0; i < info.Count; i++)
{
if (info[i] != null)
{
for (int j = 0; j < info[i].Count; j++)
{
var element = info[i][j];
var cell = excelSheet.Cells[i + 1, j + 1];
SetExcelRange(ref cell, element);
}
}
}
// 自适应;
excelSheet.Cells.AutoFitColumns();
}
/// 创建Excel;
private static bool CreateExcelSheet(ExcelSheetInfo sheetInfo)
{
if (sheetInfo == null || string.IsNullOrEmpty(sheetInfo.ExcelPath) ||
string.IsNullOrEmpty(sheetInfo.SheetName) || sheetInfo.Info == null)
{
return(false);
}
var name = sheetInfo.SheetName;
var path = sheetInfo.ExcelPath.Replace("\\", "/");
if (File.Exists(path))
{
File.Delete(path);
}
try
{
using (FileStream fs = File.Open(path, FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite))
{
ExcelPackage excel = new ExcelPackage(fs);
ExcelWorksheet excelSheet = excel.Workbook.Worksheets.Add(name);
SetExcelWorksheetInfo(ref excelSheet, sheetInfo);
excel.Save();
}
return(true);
}
catch (Exception e)
{
LogHelper.PrintError(e.ToString());
if (File.Exists(path))
{
File.Delete(path);
}
return(false);
}
}
/// 写Excel;
public static bool WriteExcelSheet(ExcelSheetInfo sheetInfo)
{
if (sheetInfo == null || string.IsNullOrEmpty(sheetInfo.ExcelPath) ||
string.IsNullOrEmpty(sheetInfo.SheetName) || sheetInfo.Info == null)
{
return(false);
}
var name = sheetInfo.SheetName;
var path = sheetInfo.ExcelPath.Replace("\\", "/");
if (!File.Exists(path))
{
return(CreateExcelSheet(sheetInfo));
}
var file = new FileInfo(Path.GetFileName(path));
using (FileStream fs = File.Open(path, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
{
ExcelPackage oldExcel = new ExcelPackage(fs);
ExcelWorksheet excelSheet = null;
ExcelPackage excel = new ExcelPackage(file);
{
foreach (var tempSheet in oldExcel.Workbook.Worksheets)
{
if (tempSheet.Name == name)
{
excelSheet = excel.Workbook.Worksheets.Add(tempSheet.Name, tempSheet);
}
else
{
excel.Workbook.Worksheets.Add(tempSheet.Name, tempSheet);
}
}
if (excelSheet == null)
{
excelSheet = excel.Workbook.Worksheets.Add(name);
}
SetExcelWorksheetInfo(ref excelSheet, sheetInfo);
excel.Save();
}
}
var tempPath = file.FullName.Replace("\\", "/");
if (File.Exists(tempPath))
{
try
{
File.Copy(tempPath, path, true);
File.Delete(tempPath);
return(true);
}
catch (Exception e)
{
LogHelper.PrintError(e.ToString());
File.Delete(tempPath);
return(false);
}
}
else
{
return(false);
}
}
