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