private CExcelTemplateDefinition GetTemplateDefinition(ExcelWorksheet worksheet)
{
CExcelTemplateDefinition def = new CExcelTemplateDefinition();
//Kiem tra o A1 co gia tri [Column] ko
//duyet qua cac cell tren row 1 de kiem cel co gia tri [Column]
for (int i = 1; i <= worksheet.Dimension.End.Column; i++)
{
ExcelRange a1Cell = worksheet.Cells[1, i];
if (a1Cell.Value != null)
{
if (a1Cell.Value.ToString().Equals("[Column]", StringComparison.OrdinalIgnoreCase))
{
def.isTemplate = true;
def.definedColumnIndex = i;
}
}
}
//Neu khong phai template
if (!def.isTemplate)
{
return(def);
}
//Tim kiem row Index
for (int i = 1; i < 100; i++)
{
ExcelRange aiCell = worksheet.Cells[i, def.definedColumnIndex];
if (aiCell.Value != null && aiCell.Value.ToString().Equals("[Row]", StringComparison.OrdinalIgnoreCase))
{
def.definedRowIndex = i;
break;
}
}
if (def.definedRowIndex == 0)
{
//Tim kiem row Index
for (int i = 2; i < 100; i++)
{
ExcelRange aiCell = worksheet.Cells[i, def.definedColumnIndex];
if (aiCell.Value != null && aiCell.Value.ToString().Equals("[Column]", StringComparison.OrdinalIgnoreCase))
{
def.definedRowIndex = i;
def.isHorizontal = true;
break;
}
}
}
//Neu khong tim thay [Row] thi khong phai template
if (def.definedRowIndex <= 0)
{
def.isTemplate = false;
return(def);
}
def.definedColumnField = new List <CExcelCellValue>();
//duyet qua cac o tren dong [Row] de tim column
//for tu A-Z
for (int i = 65; i <= 90; i++)
{
ExcelRange cell = worksheet.Cells["" + (char)i + def.definedRowIndex];
if (cell.Value != null && cell.Value.ToString().Length > 0)
{
CExcelCellValue c = new CExcelCellValue("" + (char)i, cell.Value.ToString());
def.definedColumnField.Add(c);
}
}
def.definedDataRowIndex = new List <CExcelCellValue>();
//Tim i
for (int i = 2; i < 100; i++)
{
if (i == def.definedRowIndex)
{
continue;
}
ExcelRange aiCell = worksheet.Cells[i, def.definedColumnIndex];
if (aiCell.Value != null && aiCell.Value.ToString().Equals("i", StringComparison.OrdinalIgnoreCase))
{
def.loopDataRowIndex = i;
break;
}
//int dataIndex = 0;
if (aiCell.Value != null && aiCell.Value.ToString().Length > 0)
{
def.definedDataRowIndex.Add(new CExcelCellValue("" + i, aiCell.Value.ToString()));
}
}
return(def);
}
private void ApplyDataIndexColumn(ExcelWorksheet ws, CExcelTemplateDefinition def, CExcelCellValue col, DataRow row)
{
for (int i = 0; i < def.definedDataRowIndex.Count; i++)
{
if (def.definedDataRowIndex[i].value.StartsWith("Image:", StringComparison.OrdinalIgnoreCase))
{
string fieldName = def.definedDataRowIndex[i].value.Split(':')[1];
ApplyImageCell(ws, col.address + def.definedDataRowIndex[i].address, def.definedDataRowIndex[i].value, row[fieldName].ToString());
}
else if (row.Table.Columns.Contains(def.definedDataRowIndex[i].value))
{
ws.Cells[col.address + def.definedDataRowIndex[i].address].Value = row[def.definedDataRowIndex[i].value];
}
}
}
