/// <summary>
/// Insert new row, copy format row i to new row, set value
/// </summary>
/// <param name="ws"></param>
/// <param name="def"></param>
/// <param name="row"></param>
private void ApplyLoopDataRow(ExcelWorksheet ws, CExcelTemplateDefinition def, DataRow row)
{
string templateRange = "";
templateRange = "A{0}:" + def.definedColumnField[def.definedColumnField.Count - 1].address + "{0}";
templateRange = string.Format(templateRange, def.loopDataRowIndex);
ExcelRange rowTemplate = ws.Cells[templateRange];
double rowHeight = ws.Row(def.loopDataRowIndex).Height;
int pasteRow = row.Table.Rows.IndexOf(row) + def.loopDataRowIndex + 1;
string pasteAddress = "A" + pasteRow;
ws.InsertRow(pasteRow, 1);
ws.Row(pasteRow).Height = rowHeight;
rowTemplate.Copy(ws.Cells[pasteAddress]);
for (int i = 0; i < def.definedColumnField.Count; i++)
{
if (row.Table.Columns.Contains(def.definedColumnField[i].value))
{
ws.Cells[def.definedColumnField[i].address + pasteRow].Value = row[def.definedColumnField[i].value];
}
}
}
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];
}
}
}
/// <summary>
/// Xoa các dòng dinh nghia template
/// </summary>
/// <param name="ws"></param>
/// <param name="def"></param>
private void DeleteDefineRow(ExcelWorksheet ws, CExcelTemplateDefinition def)
{
//xoa row i
if (def.loopDataRowIndex > 0)
{
ws.DeleteRow(def.loopDataRowIndex, 1);
}
//xóa [Row]
if (def.definedRowIndex > 0)
{
ws.DeleteRow(def.definedRowIndex, 1);
}
//xoa column
ws.DeleteColumn(def.definedColumnIndex);
}
/// <summary>
/// Cot dau tien la cot define
/// o A1 la chuoi [Column] dung de xac dinh la sheet chua template
/// Trên cột đầu sẽ có ô chứa chuỗi [Row] để định nghĩa các cột dữ liệu mapping
/// trên dòng có chứa ô [Row], cot nao can dien du lieu thi chua ten field du lieu
/// duoi o [Row] la ô có chứa ký tự "i" hoặc một số không âm
/// "i": là dòng lặp duyệt từ 0 đến hết. duoi o "i" thi khong duoc phep co gia tri so
/// số: là index của dòng dữ liệu
/// Sau khi đổ dữ liệu vào thì xóa cột define và row define
/// </summary>
/// <param name="templatePath"></param>
/// <param name="dt"></param>
/// <returns></returns>
public string ExportTemplate(string templatePath, System.Data.DataTable title, System.Data.DataTable dt, int sheetNumber = 1, bool isExportPdf = false)
{
FileInfo info = new FileInfo(templatePath);
string newFile = info.DirectoryName + "\\" + Guid.NewGuid().ToString() + info.Name;
if (info.Exists)
{
File.Copy(templatePath, newFile);
}
else
{
return("01-Template not found");
}
CMixExcel mixExcel = new CMixExcel(newFile, true);
ExcelPackage pck = (ExcelPackage)mixExcel.ExcelMixCore;
var worksheet = pck.Workbook.Worksheets[sheetNumber];
ApplyTitleData(worksheet, title);
CExcelTemplateDefinition def = GetTemplateDefinition(worksheet);
if (def == null || !def.isTemplate)
{
return("");
}
if (def.loopDataRowIndex > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
ApplyLoopDataRow(worksheet, def, dt.Rows[i]);
}
}
else if (!def.isHorizontal && def.definedDataRowIndex.Count > 0)
{
for (int i = 0; i < def.definedDataRowIndex.Count; i++)
{
int rowindex = 0;
if (int.TryParse(def.definedDataRowIndex[i].value, out rowindex))
{
if (dt.Rows.Count > rowindex)
{
DataRow row = dt.Rows[rowindex];
ApplyDataIndexRow(worksheet, def, def.definedDataRowIndex[i], row);
}
}
}
}
else if (def.isHorizontal && def.definedColumnField.Count > 0)
{
for (int i = 0; i < def.definedColumnField.Count; i++)
{
int rowindex = 0;
if (int.TryParse(def.definedColumnField[i].value, out rowindex))
{
if (dt.Rows.Count > rowindex)
{
DataRow row = dt.Rows[rowindex];
ApplyDataIndexColumn(worksheet, def, def.definedColumnField[i], row);
}
}
}
}
DeleteDefineRow(worksheet, def);
string newFile2 = AppDomain.CurrentDomain.BaseDirectory + "\\_Template\\Excel\\SaveAs" + Guid.NewGuid().ToString() + ".xlsx";
pck.SaveAs(new FileInfo(newFile2));
pck.Stream.Flush();
pck.Stream.Close();
mixExcel.CloseStream();
//delete tempfile
File.Delete(newFile);
//pck.Stream.Flush();
//pck.Stream.Close();
//isExportPdf = true;
if (isExportPdf)
{
string pdfFile = AppDomain.CurrentDomain.BaseDirectory + "\\_Template\\Excel\\SaveAs" + Guid.NewGuid().ToString() + ".pdf";
FileInfo f = new FileInfo(newFile2);
newFile2 = f.FullName;
// CExcelToPDF.ExportWorkbookToPdf(newFile2, pdfFile);
File.Delete(newFile2);
newFile2 = pdfFile;
}
FileStream fs = new FileStream(newFile2, FileMode.OpenOrCreate);
if (fs != null)
{
byte[] binaryData = new byte[fs.Length];
long bytesRead = fs.Read(binaryData, 0, (int)fs.Length);
fs.Close();
File.Delete(newFile2);
string base64Data = Convert.ToBase64String(binaryData);
string result = String.Format("00-{0}", base64Data);
return(result);
}
return("");
}
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);
}
