public string GetSumColumByName(string colName)
{
string ret = null;
if (SumCellPosList != null)
{
foreach (string cellPos in SumCellPosList)
{
var aName = ExcelUtils.GetColName(cellPos);
if (colName.Equals(aName))
{
return(cellPos);
}
}
}
return(ret);
}
public void SaveToExcel(DataTable[] dataSrc, FileInfo newFile, FileInfo templateFile, bool printHeader = false)
{
int len = dataSrc.Length;
if (len != sections.Count)
{
throw new Exception("numbers of sections and data sources are difference!");
}
using (ExcelPackage pck = new ExcelPackage(newFile, templateFile))
{
ExcelWorksheet ws = pck.Workbook.Worksheets[1];
if (SheetName != null)
{
ws.Name = SheetName;
}
foreach (var p in cellsData)
{
ws.Cells[p.Key].Value = p.Value;
}
int totalCount = 0;
int first_row = -1;
for (int i = 0; i < len; i++)
{
var src = dataSrc[i];
int cnt = src.Rows.Count;
var curentSection = sections[i];
if (cnt > 0)
{
totalCount += cnt;
int currentRow = ExcelUtils.GetRow(curentSection.FillATCellPos);
if (first_row == -1)
{
first_row = currentRow;
}
ExcelUtils.Excel_String_Filter(src);
int newRows = cnt - 1;
if (newRows > 0)
{
ws.InsertRow(currentRow, newRows, currentRow);
curentSection.ShitfTo(newRows, false);
for (int j = i + 1; j < len; j++)
{
sections[j].ShitfTo(newRows, true);
}
if (SummarySection != null)
{
SummarySection.ShitfTo(newRows, false);
}
}
ws.Cells[curentSection.FillATCellPos].LoadFromDataTable(src, printHeader);
if (curentSection.CounterCellPos != null)
{
ws.Cells[curentSection.CounterCellPos].Value = cnt;
}
if (curentSection.SumCellPosList != null)
{
foreach (string sumPos in curentSection.SumCellPosList)
{
var colName = ExcelUtils.GetColName(sumPos);
ws.Cells[sumPos].Formula = string.Format("Sum({0}{1}:{0}{2})", colName, currentRow, currentRow + newRows);
}
}
if (curentSection.SumColumList != null)
{
foreach (string sumCol in curentSection.SumColumList)
{
string[] parts = sumCol.Split('-');
string sumPos = parts[0];
var colName = parts[1];
ws.Cells[sumPos].Formula = string.Format("Sum({0}{1}:{0}{2})", colName, currentRow, currentRow + newRows);
}
}
}
}
if (SummarySection != null)
{
if (SummarySection.CounterCellPos != null)
{
if (totalCount > 0)
{
ws.Cells[SummarySection.CounterCellPos].Value = totalCount;
}
else
{
ws.Cells[SummarySection.CounterCellPos].Value = "";
}
}
if (SummarySection.SumCellPosList != null)
{
foreach (string sumPos in SummarySection.SumCellPosList)
{
var colName = ExcelUtils.GetColName(sumPos);
List <string> cols = new List <string>();
for (int i = 0; i < len; i++)
{
string ret = sections[i].GetSumColumByName(colName);
if (ret != null)
{
cols.Add(ret);
}
}
if (cols.Count == 0)
{
int lastRow = ExcelUtils.GetRow(sumPos) - 1;
ws.Cells[sumPos].Formula = string.Format("Sum({0}{1}:{0}{2})", colName, first_row, lastRow);
}
else
{
ws.Cells[sumPos].Formula = string.Format("Sum({0})", string.Join(",", cols));
}
}
}
if (SummarySection.SumColumList != null)
{
foreach (string sumCol in SummarySection.SumColumList)
{
string[] parts = sumCol.Split('-');
string sumPos = parts[0];
var colName = parts[1];
var colName2 = ExcelUtils.GetColName(sumPos);
List <string> cols = new List <string>();
for (int i = 0; i < len; i++)
{
string ret = sections[i].GetSumColumByName(colName2);
if (ret != null)
{
cols.Add(ret);
}
}
if (cols.Count == 0)
{
int lastRow = ExcelUtils.GetRow(sumPos) - 1;
ws.Cells[sumPos].Formula = string.Format("Sum({0}{1}:{0}{2})", colName, first_row, lastRow);
}
else
{
ws.Cells[sumPos].Formula = string.Format("Sum({0})", string.Join(",", cols));
}
//ws.Cells[sumPos].Formula = string.Format("Sum({0}{1}:{0}{2})", colName, first_row, lastRow);
}
}
}
pck.Save();
}
}