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]; } } }