private void RowToDataTable(DataTable t, dynamic d, int iData, int row,
ObjSheets ObjS, int CellDataSrcStart = 0, int CellDataSrcEnd = 0, int PivotCol = 0, string PivotFormula = "")
{
DataRow dtRow = t.NewRow();
for (int i = 0; i < t.Columns.Count; i++)
{
//if (ObjS.CellDataSource[i].Formula != "")
//{
// if (!ObjS.CellDataSource[i].IsValue)
// worksheet.Cells[row, ObjS.CellDataSource[i].CellCol].Formula = ExecFormula(ObjS.CellDataSource[i].Formula, row);
// else
// worksheet.Cells[row, ObjS.CellDataSource[i].CellCol].Value = ObjS.CellDataSource[i].Formula;
//}
//else
//{
//if (PivotFormula != "" && ObjS.CellDataSource[i].CellCol >= PivotCol) SetValidation(ref worksheet, worksheet.Cells[row, ObjS.CellDataSource[i].CellCol].Address, PivotFormula);
object ValS1 = Tools.GetDataJson(DLanguge, d, iData, ObjS.CellDataSource[i].DataSource[1], 0, 2);
//worksheet.Cells[row, ObjS.CellDataSource[i].CellCol].Value = ValS1;
dtRow[i] = ValS1;
//}
}
t.Rows.Add(dtRow);
}
// Xử lý datasource và Formula
private void WriteCellData(dynamic d, int iData, int row, ExcelWorksheet worksheet, ObjSheets ObjS, int CellDataSrcStart = 0, int CellDataSrcEnd = 0,
int PivotCol = 0, string PivotFormula = "")
{
for (int i = CellDataSrcStart; i <= CellDataSrcEnd; i++)
{
if (ObjS.CellDataSource[i].Formula != "")
{
if (!ObjS.CellDataSource[i].IsValue)
{
worksheet.Cells[row, ObjS.CellDataSource[i].CellCol].Formula = ExecFormula(ObjS.CellDataSource[i].Formula, row);
}
else
{
worksheet.SetValue(row, ObjS.CellDataSource[i].CellCol, ObjS.CellDataSource[i].Formula);
}
}
else
{
//if (PivotFormula != "" && ObjS.CellDataSource[i].CellCol >= PivotCol) SetValidation(ref worksheet, worksheet.Cells[row, ObjS.CellDataSource[i].CellCol].Address, PivotFormula);
object ValS1 = /*Random.Equals(1, 100000); //*/ Tools.GetDataJson(DLanguge, d, iData, ObjS.CellDataSource[i].DataSource[1], 0, 2);
worksheet.SetValue(row, ObjS.CellDataSource[i].CellCol, ValS1);
}
}
}
private void WriteExcelPivot(ObjSheetsPivot[] objSheet)
{
for (int i = 0; i < objSheet.Length; i++)
{
if (objSheet[i] != null)
{
if (objSheet[i].IsPivot)
{
ExcelWorksheet w = package.Workbook.Worksheets[objSheet[i].SheetName];
int row; int col; int l; int cntColAdded = 0;
for (int j = 0; j < objSheet[i].CountCellPivot; j++)
{
/*
* ObjS.CellPivot[ObjS.CountCellPivot].PivotVal = a; // PivotColumn.Name.ID
* ObjS.CellPivot[ObjS.CountCellPivot].DataSource = c[1]; //ListData
* ObjS.CellPivot[ObjS.CountCellPivot].DataSourceRow = int.Parse(c[2]); //2
*/
var obj = objSheet[i].CellPivot[j];
row = obj.CellRow;
col = obj.CellCol + cntColAdded;
dynamic d1 = dPivot[obj.PivotVal[0]];
//GetDataJson(HRSContext context, dynamic d, int i, string sKey, int j = 0, bool IsFormat = false)
object ValS1 = Tools.GetDataJson(DLanguge, d1, 0, obj.PivotVal[1], 0, 2);
w.Cells[row, col].Value = ValS1; // d1.Items[0][obj.PivotVal[1]].ToString();
ValS1 = Tools.GetDataJson(DLanguge, d1, 0, obj.PivotVal[2], 0, 2);
w.Cells[row + obj.DataSourceRow, col].Value = BeginDataSource + obj.DataSource + "." + ValS1 /*d1.Items[0][obj.PivotVal[2]].ToString()*/ + EndTag;
//=SUM([{C}10:{C}11]).4_=CHOOSE(WEEKDAY([{C}8]), "Sun", "Mon","Tue","Wed","Thu","Fri","Sat").-1_=DAY([{C}8]).-2]
string[] aFormual = obj.Formula.Split(new string[] { "_" }, StringSplitOptions.None);
if (obj.Formula != "")
{
for (int ia = 0; ia < aFormual.Length; ia++)
{
string[] b = aFormual[ia].Split(new string[] { "." }, StringSplitOptions.None);
//formual[ia] = w.Cells[row + int.Parse(aFormual[ia]), col].Formula;
//formual = formual.Replace("{c}", col.ToString());
w.Cells[row + int.Parse(b[1]), col].Formula = CreateFormula(w, col, b[0]);
}
//if (obj.Formula != "") SetValidation(ref w, w.Cells[row + obj.DataSourceRow, col].Address, aFormual[0]);
//w.Cells[row, col].Value = 0;
}
for (l = 1; l < d1.Items.Count; l++)
{
col++;
w.InsertColumn(col, 1, col - 1); cntColAdded++;
ValS1 = Tools.GetDataJson(DLanguge, d1, l, obj.PivotVal[1], 0, 2);
w.Cells[row, col].Value = ValS1; // d1.Items[l][obj.PivotVal[1]].ToString();
ValS1 = Tools.GetDataJson(DLanguge, d1, l, obj.PivotVal[2], 0, 2);
w.Cells[row + obj.DataSourceRow, col].Value = BeginDataSource + obj.DataSource + "." + ValS1 /*d1.Items[l][obj.PivotVal[2]].ToString()*/ + EndTag;
if (obj.Formula != "")
{
for (int ia = 0; ia < aFormual.Length; ia++)
{
string[] b = aFormual[ia].Split(new string[] { "." }, StringSplitOptions.None);
//formual[ia] = w.Cells[row + int.Parse(aFormual[ia]), col].Formula;
//formual = formual.Replace("{c}", col.ToString());
w.Cells[row + int.Parse(b[1]), col].Formula = CreateFormula(w, col, b[0]);
}
//if (obj.Formula != "") SetValidation(ref w, w.Cells[row + obj.DataSourceRow, col].Address, obj.Formula);
}
}
}
}
}
}
}