/// <summary> /// Doc file excel chi tiet dua vao database /// </summary> /// <param name="clientKey"></param> /// <param name="excelfile"></param> /// <returns></returns> public string ImportExcel(string clientKey, string excelfile) { try { CLogManager.WriteSL("IMPORT", "File:" + excelfile); int startDataRow = 11; CMixExcel mixExcel = new CMixExcel(excelfile, false); ExcelPackage pck = (ExcelPackage)mixExcel.ExcelMixCore; var worksheet = pck.Workbook.Worksheets[1]; bool isAllOk = true; CLogManager.WriteSL("IMPORT", "Begin For to :" + worksheet.Dimension.End.Row.ToString()); for (int row = startDataRow; row <= worksheet.Dimension.End.Row; row++) { string inputValue = GetRowInXml(worksheet, row); CLogManager.WriteSL("IMPORT", "Input :" + inputValue); if (string.IsNullOrEmpty(inputValue)) { continue; } inputValue = "<RequestParams Sys_ViewID=\"28\" Action=\"INSERT\" " + inputValue + "/>"; if (ExecuteImport(clientKey, inputValue)) { //Delete imported row worksheet.DeleteRow(row); row--; } else { isAllOk = false; } } string downloadDir = System.Configuration.ConfigurationManager.AppSettings["DownloadDirectory"]; string tempFile = downloadDir + "\\Product\\Error";// + Guid.NewGuid().ToString() + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"; if (!Directory.Exists(tempFile)) { Directory.CreateDirectory(tempFile); } string fileName = Guid.NewGuid().ToString() + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"; tempFile = tempFile + "/" + fileName; //Khi xong thi luu file con lai vao temp pck.SaveAs(new System.IO.FileInfo(tempFile)); mixExcel.CloseStream(); if (isAllOk) { return("Code\n00-OK"); } else { return("Code\n01-//Product//Error//" + fileName); } } catch (Exception ex) { CLogManager.WriteSL("IMPORT", ex.ToString()); return("Code\n02-" + ex.Message); } }
/// <summary> /// /// </summary> /// <param name="dt"></param> /// <returns></returns> public string Export4SSTemplate(System.Data.DataSet ds) { string templateFile = AppDomain.CurrentDomain.BaseDirectory + "\\_Template\\NIPPON_4SSTemplate.xlsx"; string newFile = AppDomain.CurrentDomain.BaseDirectory + "\\_Template\\NIPPON_4SS" + Guid.NewGuid().ToString() + ".xlsx"; //Copy template to file if (File.Exists(templateFile)) { File.Copy(templateFile, newFile); } else { return "01-Template not found"; } //chia datatable lam 2 table summary va detail DataTable dtSummary = ds.Tables[0]; DataTable dtDetail = ds.Tables[1]; CMixExcel mixExcel = new CMixExcel(newFile, true); int colIndex = 3; ExcelPackage pck = (ExcelPackage)mixExcel.ExcelMixCore; ExcelWorksheet worksheet = pck.Workbook.Worksheets[1]; ApplyDataToShopSignWorksheet(dtSummary, worksheet); ExcelWorksheet worksheetDetail = pck.Workbook.Worksheets[2]; ApplyDataToShopSignWorksheet(dtDetail, worksheetDetail); //Xoa cot template //if (!string.IsNullOrEmpty(mixExcel.PathFile)) newFile = AppDomain.CurrentDomain.BaseDirectory + "\\_Template\\NIPPON_4SS" + Guid.NewGuid().ToString() + ".xlsx"; //pck.Save(); pck.SaveAs(new FileInfo(newFile)); mixExcel.CloseStream(); //pck.Stream.Flush(); //pck.Stream.Close(); FileStream fs = new FileStream(newFile, FileMode.OpenOrCreate); if (fs != null) { byte[] binaryData = new byte[fs.Length]; long bytesRead = fs.Read(binaryData, 0, (int)fs.Length); fs.Close(); string base64Data = Convert.ToBase64String(binaryData); string result = String.Format("00-{0}", base64Data); return result; } return "01-Unknow Error"; }
/// <summary> /// /// </summary> /// <param name="dt"></param> /// <returns></returns> public string Export4SSTemplate(System.Data.DataTable dt) { string templateFile = AppDomain.CurrentDomain.BaseDirectory + "\\_Template\\NIPPON_4SSTemplate.xlsx"; string newFile = AppDomain.CurrentDomain.BaseDirectory + "\\_Template\\NIPPON_4SS" + Guid.NewGuid().ToString() + ".xlsx"; //Copy template to file if (File.Exists(templateFile)) { File.Copy(templateFile, newFile); } else { return "01-Template not found"; } CMixExcel mixExcel = new CMixExcel(newFile, true); int colIndex = 3; ExcelPackage pck = (ExcelPackage)mixExcel.ExcelMixCore; var worksheet = pck.Workbook.Worksheets[1]; ExcelRange columnTemplate = worksheet.Cells["C1:C7"]; ExcelRange pastedColumn = worksheet.Cells["D1"];//1, colIndex, 7, colIndex]; //columnTemplate.Copy(pastedColumn); for (int i = 3; i < dt.Columns.Count; i++) { if (colIndex > 3) { pastedColumn = worksheet.Cells[1, colIndex];//1, colIndex, 7, colIndex]; columnTemplate.Copy(pastedColumn); } pastedColumn[1, colIndex].Value = dt.Columns[i].ColumnName; pastedColumn[2, colIndex].Value = dt.Rows[0][i]; pastedColumn[3, colIndex].Value = dt.Rows[1][i]; pastedColumn[4, colIndex].Value = dt.Rows[2][i]; pastedColumn[5, colIndex].Value = dt.Rows[3][i]; pastedColumn[6, colIndex].Value = dt.Rows[4][i]; pastedColumn[7, colIndex].Value = dt.Rows[5][i]; colIndex++; } //Xoa cot template //if (!string.IsNullOrEmpty(mixExcel.PathFile)) newFile = AppDomain.CurrentDomain.BaseDirectory + "\\_Template\\NIPPON_4SS" + Guid.NewGuid().ToString() + ".xlsx"; //pck.Save(); pck.SaveAs(new FileInfo(newFile)); mixExcel.CloseStream(); //pck.Stream.Flush(); //pck.Stream.Close(); FileStream fs = new FileStream(newFile, FileMode.OpenOrCreate); if (fs != null) { byte[] binaryData = new byte[fs.Length]; long bytesRead = fs.Read(binaryData, 0, (int)fs.Length); fs.Close(); string base64Data = Convert.ToBase64String(binaryData); string result = String.Format("00-{0}", base64Data); return result; } return "01-Unknow Error"; }
/// <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(""); }
/// <summary> /// /// </summary> /// <param name="dt"></param> /// <returns></returns> public string Export4SSTemplate(System.Data.DataTable dt) { string templateFile = AppDomain.CurrentDomain.BaseDirectory + "\\_Template\\NIPPON_4SSTemplate.xlsx"; string newFile = AppDomain.CurrentDomain.BaseDirectory + "\\_Template\\NIPPON_4SS" + Guid.NewGuid().ToString() + ".xlsx"; //Copy template to file if (File.Exists(templateFile)) { File.Copy(templateFile, newFile); } else { return("01-Template not found"); } CMixExcel mixExcel = new CMixExcel(newFile, true); int colIndex = 3; ExcelPackage pck = (ExcelPackage)mixExcel.ExcelMixCore; var worksheet = pck.Workbook.Worksheets[1]; ExcelRange columnTemplate = worksheet.Cells["C1:C7"]; ExcelRange pastedColumn = worksheet.Cells["D1"];//1, colIndex, 7, colIndex]; //columnTemplate.Copy(pastedColumn); for (int i = 3; i < dt.Columns.Count; i++) { if (colIndex > 3) { pastedColumn = worksheet.Cells[1, colIndex];//1, colIndex, 7, colIndex]; columnTemplate.Copy(pastedColumn); } pastedColumn[1, colIndex].Value = dt.Columns[i].ColumnName; pastedColumn[2, colIndex].Value = dt.Rows[0][i]; pastedColumn[3, colIndex].Value = dt.Rows[1][i]; pastedColumn[4, colIndex].Value = dt.Rows[2][i]; pastedColumn[5, colIndex].Value = dt.Rows[3][i]; pastedColumn[6, colIndex].Value = dt.Rows[4][i]; pastedColumn[7, colIndex].Value = dt.Rows[5][i]; colIndex++; } //Xoa cot template //if (!string.IsNullOrEmpty(mixExcel.PathFile)) newFile = AppDomain.CurrentDomain.BaseDirectory + "\\_Template\\NIPPON_4SS" + Guid.NewGuid().ToString() + ".xlsx"; //pck.Save(); pck.SaveAs(new FileInfo(newFile)); mixExcel.CloseStream(); //pck.Stream.Flush(); //pck.Stream.Close(); FileStream fs = new FileStream(newFile, FileMode.OpenOrCreate); if (fs != null) { byte[] binaryData = new byte[fs.Length]; long bytesRead = fs.Read(binaryData, 0, (int)fs.Length); fs.Close(); string base64Data = Convert.ToBase64String(binaryData); string result = String.Format("00-{0}", base64Data); return(result); } return("01-Unknow Error"); }