Exemple #1
0
        /// <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");
        }