private void ReadExcelPivot()
{
for (int i = 1; i <= (package.Workbook.Worksheets.Count > SheetCount ? SheetCount : package.Workbook.Worksheets.Count); i++)
{
var worksheet = package.Workbook.Worksheets[i];
ObjSheetPivot[i - 1] = new ObjSheetsPivot();
ObjSheetPivot[i - 1].SheetName = worksheet.Name;
ObjSheetPivot[i - 1].IsPivot = false;
ObjSheetPivot[i - 1].CountCellPivot = 0;
ObjSheetPivot[i - 1].CellPivot = new ObjPivot[PivotCount];
ReadCellDataPivot(ref worksheet, ref ObjSheetPivot[i - 1]);
}
}
// Xử lý Pivot và Biến; Language
private void ReadCellDataPivot(ref ExcelWorksheet worksheet, ref ObjSheetsPivot ObjS)
{
var rowCount = worksheet.Dimension?.Rows;
var colCount = worksheet.Dimension?.Columns;
if (!rowCount.HasValue || !colCount.HasValue)
{
return;
}
for (int row = 1; row <= rowCount.Value; row++)
{
for (int col = 1; col <= colCount.Value; col++)
{
dynamic CellData = worksheet.Cells[row, col].Value;
if (CellData != null)
{
// thay thế nếu là Varable languageư
string cellData = CellData.ToString(); bool IsTag = false; string Formula = "";
try
{
cellData = ReplaceStringValue(cellData, BeginVarableLang, EndTag, ref IsTag, true);
cellData = ReplaceStringValue(cellData, BeginVarable, EndTag, ref IsTag, false);
}
catch (Exception) { }
if (IsTag) // Varable language
{
worksheet.Cells[row, col].Value = cellData;
}
else
{
SearchTagByCell(cellData, BeginStaticValue, EndTag, ref IsTag, ref Formula);
if (IsTag)
{
worksheet.Cells[row, col].Formula = Formula;
}
else
{
// thay thế nếu là Varable Param
//cellData = CellData.ToString(); IsTag = false;
//cellData = ReplaceStringValue(cellData, BeginVarable, EndTag, ref IsTag, false);
//if (IsTag) // Varable
// worksheet.Cells[row, col].Value = cellData;
//else
//{
//BeginDataPivot
cellData = CellData.ToString(); IsTag = false;
string[] a;
SearchTagByCell(cellData, BeginDataPivot, EndTag, ref IsTag, ref Formula);
if (IsTag)
{
//PivotColumn.Name.ID:ListData:2:Formula
string[] c = Formula.Split(new string[] { CharSplitDataPivot }, StringSplitOptions.None);
a = c[0].Split(new string[] { CharSplitDataSource }, StringSplitOptions.None);
ObjS.IsPivot = IsTag;
ObjS.CellPivot[ObjS.CountCellPivot] = new ObjPivot();
ObjS.CellPivot[ObjS.CountCellPivot].CellRow = row;
ObjS.CellPivot[ObjS.CountCellPivot].CellCol = col;
ObjS.CellPivot[ObjS.CountCellPivot].PivotVal = a; // PivotColumn.Name.ID
ObjS.CellPivot[ObjS.CountCellPivot].DataSource = c[1]; //ListData
if (c.Length > 2)
{
ObjS.CellPivot[ObjS.CountCellPivot].DataSourceRow = int.Parse(c[2]); //2
}
else
{
ObjS.CellPivot[ObjS.CountCellPivot].DataSourceRow = 1;
}
if (c.Length > 3)
{
ObjS.CellPivot[ObjS.CountCellPivot].Formula = c[3]; //Formula
}
else
{
ObjS.CellPivot[ObjS.CountCellPivot].Formula = "";
}
ObjS.CountCellPivot++;
}
//}
}
}
}
}
}
}