private void ReadExcel()
{
for (int i = 1; i <= (package.Workbook.Worksheets.Count > SheetCount ? SheetCount : package.Workbook.Worksheets.Count); i++)
{
var worksheet = package.Workbook.Worksheets[i];
ObjSheet[i - 1] = new ObjSheets();
ObjSheet[i - 1].SheetName = worksheet.Name;
ObjSheet[i - 1].IsDataList = false;
ObjSheet[i - 1].CountCellData = 0;
ObjSheet[i - 1].CellDataSource = new ObjDataSource[DataSourceCloumnCount];
ObjSheet[i - 1].CountDataSource = 0;
ObjSheet[i - 1].DataSrcList = new ClsDataSrcList[DataSourceCount];
ReadCellData(ref worksheet, ref ObjSheet[i - 1]);
}
}
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);
}
private void ReadCellData(ref ExcelWorksheet worksheet, ref ObjSheets ObjS)
{
var rowCount = worksheet.Dimension?.Rows;
var colCount = worksheet.Dimension?.Columns;
if (!rowCount.HasValue || !colCount.HasValue)
{
return;
}
string DataSrcNamePrev = "";
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)
{
//BeginDataSource
string cellData = CellData.ToString(); bool IsTag = false; string Formula = ""; int aCount; string[] a;
cellData = CellData.ToString(); IsTag = false;
a = "a".Split(new string[] { CharSplitDataSource }, StringSplitOptions.None); aCount = 0;
SearchTagByCell(cellData, BeginDataSource, EndTag, ref IsTag, ref a, ref aCount);
if (IsTag)
{
ObjS.IsDataList = IsTag;
}
if (IsTag)
{
if (DataSrcNamePrev != a[0])
{
ObjS.DataSrcList[ObjS.CountDataSource] = new ClsDataSrcList();
ObjS.DataSrcList[ObjS.CountDataSource].CellRowStart = row;
ObjS.DataSrcList[ObjS.CountDataSource].CellDataSrcStart = ObjS.CountCellData;
if (ObjS.CountDataSource > 0)
{
ObjS.DataSrcList[ObjS.CountDataSource - 1].CellDataSrcEnd = ObjS.CountCellData - 1;
}
ObjS.DataSrcList[ObjS.CountDataSource].DataSrcName = a[0];
DataSrcNamePrev = a[0];
ObjS.CountDataSource++;
}
ObjS.DataSrcList[ObjS.CountDataSource - 1].CellDataSrcEnd = ObjS.CountCellData;
ObjS.CellDataSource[ObjS.CountCellData] = new ObjDataSource();
ObjS.CellDataSource[ObjS.CountCellData].CellRow = row;
ObjS.CellDataSource[ObjS.CountCellData].CellCol = col;
ObjS.CellDataSource[ObjS.CountCellData].DataSource = a;
ObjS.CellDataSource[ObjS.CountCellData].aCount = aCount;
ObjS.CellDataSource[ObjS.CountCellData].Formula = "";
ObjS.CellDataSource[ObjS.CountCellData].IsValue = false;
ObjS.CountCellData++;
}
SearchTagByCell(cellData, BeginRepeatDynamicFormula, EndTag, ref IsTag, ref Formula);
if (IsTag)
{
ObjS.DataSrcList[ObjS.CountDataSource - 1].CellDataSrcEnd = ObjS.CountCellData;
ObjS.CellDataSource[ObjS.CountCellData] = new ObjDataSource();
ObjS.CellDataSource[ObjS.CountCellData].CellRow = row;
ObjS.CellDataSource[ObjS.CountCellData].CellCol = col;
ObjS.CellDataSource[ObjS.CountCellData].Formula = Formula;
ObjS.CellDataSource[ObjS.CountCellData].IsValue = false;
ObjS.CountCellData++;
}
}
}
}
}
// 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);
}
}
}