/// <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); }