public ActionResult getModel(FormModel postedModel) { RootObject MODELFROMDATABASE = new RootObject(); PDFModel PDFmdoel = new PDFModel() { DuraionTime = postedModel.formfillingtime, OperatorID = postedModel.phone, hospitalName = GlobalVariables.name, timestamp = postedModel.timestamp, PDFTitle = postedModel.title, catID = postedModel.catID, }; if (GlobalVariables.address == "" || GlobalVariables.address == null) { string json = ""; using (var client = new WebClient()) { json = client.DownloadString("http://www.supectco.com/webs/GDP/Admin/getHospitalDetail.php?active=true"); HospitalRoot log = JsonConvert.DeserializeObject <HospitalRoot>(json); GlobalVariables.address = log.hospitalDetail.First().address; GlobalVariables.name = log.hospitalDetail.First().name; GlobalVariables.mohandes = log.hospitalDetail.First().mohandes; GlobalVariables.ostan = log.hospitalDetail.First().ostan; GlobalVariables.sharestan = log.hospitalDetail.First().sharestan; GlobalVariables.phone = log.hospitalDetail.First().phone; } } //if(GlobalVariables.modelList != "") //{ //} using (var client = new WebClient()) { GlobalVariables.modelList = client.DownloadString("http://www.supectco.com/webs/GDP/Admin/getListOfFeaterForApp.php?CatID=1"); } try { string json3 = ""; using (var client = new WebClient()) { json3 = client.DownloadString("http://www.supectco.com/webs/GDP/Admin/getLastPDFName.php?name=" + postedModel.title); } if (json3.Length > 1) { List <string> firstList = json3.Split(',').ToList(); List <string> TagIds = firstList[0].Split('_').ToList(); if (TagIds.Count() > 1) { postedModel.title = postedModel.title + "_" + (int.Parse(TagIds[1]) + 1).ToString(); } else { postedModel.title = postedModel.title + "_1"; } PDFmdoel.referer = firstList[1]; PDFmdoel.PDFTitle = postedModel.title; } } catch (Exception e) { System.IO.File.WriteAllText(e.Message, postedModel.title); } //string txtpath = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "sample.txt"); //System.IO.File.AppendAllText(txtpath, String.Empty); System.IO.File.WriteAllText(txtpath, postedModel.title); string id = ""; try { MODELFROMDATABASE = JsonConvert.DeserializeObject <RootObject>(GlobalVariables.modelList); System.IO.File.AppendAllText(txtpath, " " + MODELFROMDATABASE.featurDataDetail.Count().ToString()); string srt = postedModel.model; // string txtpath2 = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "sample2.txt"); //System.IO.File.AppendAllText(txtpath, String.Empty); //System.IO.File.AppendAllText(txtpath, srt); List <model> mymodel = JsonConvert.DeserializeObject <List <model> >(srt); string count = mymodel.Count().ToString(); System.IO.File.AppendAllText(txtpath, String.Empty); System.IO.File.AppendAllText(txtpath, count); var testFile = ""; testFile = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "PDF/pump.pdf"); string emptyNamePDF = ""; emptyNamePDF = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "PDF/pumpEmpty.pdf"); //set pdf in database ; //create pdf proccess //Spire.Pdf.PdfDocument doc = new Spire.Pdf.PdfDocument(); //doc.LoadFromFile(testFile); //PdfTextFind results = null; ////setting position for every title //PdfPageBase page; //foreach (var item in mymodel) //{ // int PAGE = Convert.ToInt32(item.page) - 1; // page = doc.Pages[PAGE]; // if (item.value != "master") // { // results = page.FindText(item.title).Finds.First(); // float width = results.Size.Width; // item.x = (results.Position.X + width).ToString(); // item.y = (results.Position.Y).ToString(); // } //} var testFile2 = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), emptyNamePDF); string newFile = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ResultPDF/result" + postedModel.title + ".pdf"); string finalFile = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ResultPDF/" + postedModel.title + ".pdf"); Spire.Pdf.PdfDocument doc2 = new Spire.Pdf.PdfDocument(); doc2.LoadFromFile(testFile2); float pageheight = 0; foreach (PdfPageBase spipage in doc2.Pages) { int index = doc2.Pages.IndexOf(spipage); if (index == 0) { mymodel.Where(x => x.page == "1" && x.title == "address").FirstOrDefault().value = GlobalVariables.address; mymodel.Where(x => x.page == "1" && x.title == "hospital name").FirstOrDefault().value = GlobalVariables.name; mymodel.Where(x => x.page == "1" && x.title == "mohandes").FirstOrDefault().value = GlobalVariables.mohandes; mymodel.Where(x => x.page == "1" && x.title == "ostan").FirstOrDefault().value = GlobalVariables.ostan; mymodel.Where(x => x.page == "1" && x.title == "shahrestan").FirstOrDefault().value = GlobalVariables.sharestan; mymodel.Where(x => x.page == "1" && x.title == "phone").FirstOrDefault().value = GlobalVariables.phone; foreach (var item in mymodel.Where(x => x.page == "1" && x.title == "mark")) { PDFmdoel.mark = PDFmdoel.mark + item.value; } foreach (var item in mymodel.Where(x => x.page == "1" && x.title == "model")) { PDFmdoel.model = PDFmdoel.model + item.value; } foreach (var item in mymodel.Where(x => x.page == "1" && x.title == "esteghrar")) { PDFmdoel.position = PDFmdoel.position + item.value; } PDFmdoel.serial = mymodel.Where(x => x.page == "1" && x.title == "serial").FirstOrDefault().value; PDFmdoel.amval = mymodel.Where(x => x.page == "1" && x.title == "amvaal").FirstOrDefault().value; if (mymodel.Where(x => x.page == "1" && x.title == "Passed").FirstOrDefault() != null) { if (mymodel.Where(x => x.page == "1" && x.title == "Passed").FirstOrDefault().value == "true") { PDFmdoel.status = "Passed"; } } if (mymodel.Where(x => x.page == "1" && x.title == "Limited").FirstOrDefault() != null) { if (mymodel.Where(x => x.page == "1" && x.title == "Limited").FirstOrDefault().value == "true") { PDFmdoel.status = "Limited"; } } if (mymodel.Where(x => x.page == "1" && x.title == "Failed").FirstOrDefault() != null) { if (mymodel.Where(x => x.page == "1" && x.title == "Failed").FirstOrDefault().value == "true") { PDFmdoel.status = "Failed"; } } System.IO.File.WriteAllText(txtpath, "enter foreach"); foreach (var item in MODELFROMDATABASE.featurDataDetail.Where(x => x.referer != "")) { model chosen = mymodel.Where(x => x.title == item.title && x.page == item.page).SingleOrDefault(); System.IO.File.AppendAllText(txtpath, " " + item.title); if (item.max == -1 && item.min != -1) { if (double.Parse(chosen.value) >= item.min) { mymodel.Where(x => x.title == item.referer).SingleOrDefault().value = "Passed"; } else { mymodel.Where(x => x.title == item.referer).SingleOrDefault().value = "Failed"; } } else if (item.max != -1 && item.min == -1) { if (double.Parse(chosen.value) <= item.max) { mymodel.Where(x => x.title == item.referer).SingleOrDefault().value = "Passed"; } else { mymodel.Where(x => x.title == item.referer).SingleOrDefault().value = "Failed"; } } else { if (double.Parse(chosen.value) >= item.min && double.Parse(chosen.value) <= item.max) { mymodel.Where(x => x.title == item.referer).SingleOrDefault().value = "Passed"; } else { mymodel.Where(x => x.title == item.referer).SingleOrDefault().value = "Failed"; } } } string flow10 = mymodel.Where(x => x.title == "flow10").FirstOrDefault().value; if (flow10 != "") { mymodel.Where(x => x.title == "error10").FirstOrDefault().value = Math.Round(((decimal.Parse(flow10) - 10) * 10), 2).ToString().Replace("-", ""); } string flow50 = mymodel.Where(x => x.title == "flow50").FirstOrDefault().value; if (flow50 != "") { mymodel.Where(x => x.title == "error50").FirstOrDefault().value = Math.Round(((decimal.Parse(flow50) - 50) * 2), 2).ToString().Replace("-", ""); } string flow100 = mymodel.Where(x => x.title == "flow100").FirstOrDefault().value; if (flow100 != "") { mymodel.Where(x => x.title == "error100").FirstOrDefault().value = Math.Round((decimal.Parse(flow100) - 100), 2).ToString().Replace("-", ""); } //ایمنی if (mymodel.Where(x => x.title == "resmonfas").FirstOrDefault().value == "Passed" && mymodel.Where(x => x.title == "class").FirstOrDefault().value != "CLASS II") { mymodel.Where(x => x.title == "monfasel").FirstOrDefault().value = Math.Round(RandomNumberBetween(0.5, 0.1), 1).ToString(); } if (mymodel.Where(x => x.title == "resmotas").FirstOrDefault().value == "Passed" && mymodel.Where(x => x.title == "class").FirstOrDefault().value != "CLASS II") { mymodel.Where(x => x.title == "motasel").FirstOrDefault().value = Math.Round(RandomNumberBetween(1.6, 0.2), 1).ToString(); } if (mymodel.Where(x => x.title == "class").FirstOrDefault().value != "CLASS II") { int adi = (int)RandomNumberBetween(4600, 4950); int pluse = (int)RandomNumberBetween(23, 47); int tak = (int)RandomNumberBetween(7200, 9700); int takpluse = (int)RandomNumberBetween(180, 280); if (mymodel.Where(x => x.title == "res12").FirstOrDefault().value == "Passed") { mymodel.Where(x => x.title == "nashti1").FirstOrDefault().value = ((int)adi).ToString(); mymodel.Where(x => x.title == "nashti2").FirstOrDefault().value = ((int)tak).ToString(); } if (mymodel.Where(x => x.title == "res34").FirstOrDefault().value == "Passed") { mymodel.Where(x => x.title == "nashti3").FirstOrDefault().value = ((int)(adi + pluse)).ToString(); mymodel.Where(x => x.title == "nashti4").FirstOrDefault().value = ((int)(tak + takpluse)).ToString(); } } int nashtiBadiAdi = (int)RandomNumberBetween(57, 83); int nashtiBadiAdiPluse = (int)RandomNumberBetween(1, 7); int nashtiBaditak = (int)RandomNumberBetween(280, 470); int nashtiBaditakPluse = (int)RandomNumberBetween(10, 15); int badane2 = (int)nashtiBaditak; int badane3 = badane2 + 3; int badane5 = (int)nashtiBaditak + (int)nashtiBaditakPluse; int badane6 = badane5 + 3; if (mymodel.Where(x => x.title == "res123" && x.page == "4").FirstOrDefault().value == "Passed") { mymodel.Where(x => x.title == "badane1").FirstOrDefault().value = ((int)nashtiBadiAdi + (int)nashtiBadiAdiPluse).ToString(); mymodel.Where(x => x.title == "badane2").FirstOrDefault().value = badane2.ToString(); mymodel.Where(x => x.title == "badane3").FirstOrDefault().value = badane3.ToString(); } if (mymodel.Where(x => x.title == "res456" && x.page == "4").FirstOrDefault().value == "Passed") { mymodel.Where(x => x.title == "badane4").FirstOrDefault().value = ((int)nashtiBadiAdi + (2 * (int)nashtiBadiAdiPluse)).ToString(); mymodel.Where(x => x.title == "badane5").FirstOrDefault().value = badane5.ToString(); mymodel.Where(x => x.title == "badane6").FirstOrDefault().value = badane6.ToString(); } if (mymodel.Where(x => x.title == "res7" && x.page == "4").FirstOrDefault().value == "Passed") { mymodel.Where(x => x.title == "badane7").FirstOrDefault().value = ((int)nashtiBadiAdi).ToString(); } int nashtiBadiAdi2 = (int)RandomNumberBetween(57, 83); int nashtiBadiAdiPluse2 = (int)RandomNumberBetween(1, 7); int nashtiBaditak2 = (int)RandomNumberBetween(280, 470); int nashtiBaditakPluse2 = (int)RandomNumberBetween(10, 15); int ptp2 = (int)nashtiBaditak2; int ptp3 = ptp2 + 3; int ptp5 = (int)nashtiBaditak2 + (int)nashtiBaditakPluse2; int ptp6 = ptp5 + 3; if (mymodel.Where(x => x.title == "res123" && x.page == "5").FirstOrDefault().value == "Passed") { mymodel.Where(x => x.title == "ptp1").FirstOrDefault().value = ((int)nashtiBadiAdi2 + nashtiBadiAdiPluse2).ToString(); mymodel.Where(x => x.title == "ptp2").FirstOrDefault().value = ptp2.ToString(); mymodel.Where(x => x.title == "ptp3").FirstOrDefault().value = ptp3.ToString(); } if (mymodel.Where(x => x.title == "res456" && x.page == "5").FirstOrDefault().value == "Passed") { mymodel.Where(x => x.title == "ptp4").FirstOrDefault().value = ((int)nashtiBadiAdi2 + (2 * nashtiBadiAdiPluse2)).ToString(); mymodel.Where(x => x.title == "ptp5").FirstOrDefault().value = ptp5.ToString(); mymodel.Where(x => x.title == "ptp6").FirstOrDefault().value = ptp6.ToString(); } if (mymodel.Where(x => x.title == "res7" && x.page == "5").FirstOrDefault().value == "Passed") { mymodel.Where(x => x.title == "ptp7").FirstOrDefault().value = ((int)nashtiBadiAdi2).ToString(); } if (mymodel.Where(x => x.title == "class").FirstOrDefault().value == "CLASS II") { mymodel.Where(x => x.title == "monfasel").FirstOrDefault().value = "----"; mymodel.Where(x => x.title == "motasel").FirstOrDefault().value = "----"; mymodel.Where(x => x.title == "nashti1").FirstOrDefault().value = "----"; mymodel.Where(x => x.title == "nashti2").FirstOrDefault().value = "----"; mymodel.Where(x => x.title == "nashti3").FirstOrDefault().value = "----"; mymodel.Where(x => x.title == "nashti4").FirstOrDefault().value = "----"; mymodel.Where(x => x.title == "resmonfas").FirstOrDefault().value = "Not Checked"; mymodel.Where(x => x.title == "resmotas").FirstOrDefault().value = "Not Checked"; mymodel.Where(x => x.title == "res12").FirstOrDefault().value = "Not Checked"; mymodel.Where(x => x.title == "res34").FirstOrDefault().value = "Not Checked"; } else { if (mymodel.Where(x => x.title == "kablbargh").FirstOrDefault().value == "غیر قابل انفصال") { mymodel.Where(x => x.title == "monfasel").FirstOrDefault().value = "----"; mymodel.Where(x => x.title == "resmonfas").FirstOrDefault().value = "Not Checked"; } else { mymodel.Where(x => x.title == "motasel").FirstOrDefault().value = "----"; mymodel.Where(x => x.title == "resmotas").FirstOrDefault().value = "Not Checked"; } } } List <model> pageFieldes = mymodel.Where(x => x.page == (index + 1).ToString()).ToList(); pageheight = spipage.Size.Height; foreach (var item in pageFieldes) { PdfTrueTypeFont Arial = new PdfTrueTypeFont(new System.Drawing.Font("Arial", 10f), true); PdfTrueTypeFont Arial9 = new PdfTrueTypeFont(new System.Drawing.Font("Arial", 9f), true); Spire.Pdf.Graphics.PdfFont font1 = new Spire.Pdf.Graphics.PdfFont(PdfFontFamily.TimesRoman, 9); Spire.Pdf.Graphics.PdfBrush brush = PdfBrushes.Black; PdfStringFormat format = new PdfStringFormat(); SizeF size = Arial9.MeasureString(item.value, format); float Xposition = float.Parse(item.x) - size.Width; float yposition = float.Parse(item.y) - 1; PointF position = new PointF(Xposition, float.Parse(item.y)); if (item.type == 1) { if (item.value == "true") { string imagepath = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "checked.png"); Spire.Pdf.Graphics.PdfImage image = Spire.Pdf.Graphics.PdfImage.FromFile(imagepath); float width = image.Width * 0.75f; float height = image.Height * 0.75f; spipage.Canvas.DrawImage(image, Xposition, yposition, 10, 10); } else { string imagepath2 = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "unchecked.png"); Spire.Pdf.Graphics.PdfImage image2 = Spire.Pdf.Graphics.PdfImage.FromFile(imagepath2); spipage.Canvas.DrawImage(image2, Xposition, yposition, 10, 10); } } else { if (item.font == "E") { spipage.Canvas.DrawString(item.value, Arial, brush, position); } else { position.X = position.X; spipage.Canvas.DrawString(item.value, Arial, brush, position, new PdfStringFormat() { RightToLeft = true }); } } } ; } doc2.SaveToFile(newFile, FileFormat.PDF); doc2.Close(); string srt2 = ""; using (PdfReader pdfReader = new PdfReader(newFile)) { float widthTo_Trim = pageheight - 15;// iTextSharp.text.Utilities.MillimetersToPoints(450); using (FileStream output = new FileStream(finalFile, FileMode.Create, FileAccess.Write)) { using (PdfStamper pdfStamper = new PdfStamper(pdfReader, output)) { iTextSharp.text.Rectangle cropBox = pdfReader.GetCropBox(1); cropBox.Top = widthTo_Trim; pdfReader.GetPageN(1).Put(PdfName.CROPBOX, new PdfRectangle(cropBox)); //for (int page = 1; page <= pdfReader.NumberOfPages; page++) //{ //} } PDFmdoel.PDFPath = finalFile; returnModel m2 = new returnModel() { status = 200, message = "http://gdp.sup-ect.ir/ResultPDF/" + postedModel.title + ".pdf", }; srt2 = JsonConvert.SerializeObject(m2); } pdfReader.Close(); setpdfindatabase(PDFmdoel); } return(Content(srt2)); } catch (Exception e) { string txtpath = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "sample.txt"); System.IO.File.AppendAllText(txtpath, e.Message); returnModel m = new returnModel() { status = 400, message = e.Message + id, }; string srt = JsonConvert.SerializeObject(m); return(Content(srt)); } }
static void Main(string[] args) { var conn = ConfigurationManager.AppSettings["DBConnectionString"].ToString(); string sourceDirectory; List <returnModel> returnCollection = new List <returnModel>(); string fileName = ""; int counterFile = 1; int counterLine; int counterFileValidate = 1; #region Fancy header /* * Console.Write(FiggleFonts.Ogre.Render("------------")); * List<char> chars = new List<char>() * { * ' ', 'C', 'r', 'e', 'a', 't', 'e', 'd', ' ', * 'b', 'y', ' ', * 'P', 'i', 'r', 'i', 'y', 'a', 'V', ' ' * }; * Console.Write("---------------", Color.LawnGreen); * Console.WriteWithGradient(chars, Color.Blue, Color.Purple, 16); * Console.Write("---------------", Color.LawnGreen); * Console.WriteLine("\n"); */ #endregion // Ask the user to type path if (args.Length == 0) { // Display title Console.Title = "ExcelToDB 1.0.1"; // Display header Console.WriteWithGradient(FiggleFonts.Banner.Render("excel to db"), Color.LightGreen, Color.ForestGreen, 16); Console.ReplaceAllColorsWithDefaults(); // Display copyright Console.WriteLine(" ---------------------- Created by PiriyaV -----------------------\n", Color.LawnGreen); Console.Write(@"Enter source path (eg: D:\folder) : ", Color.LightYellow); sourceDirectory = Convert.ToString(Console.ReadLine()); Console.Write("\n"); } else { sourceDirectory = Convert.ToString(args[0]); } // Variable for backup string folderBackup = "imported_" + DateTime.Now.ToString("ddMMyyyy_HHmmss"); string folderBackupPath = Path.Combine(sourceDirectory, folderBackup); // Initial values int LineNum; int ColumnNum; string sheetName = "Sheet1"; string TableName = "[SAPMM-WM]"; int i = 0; int ColumnNumChecker = 53; bool sheetChecker = true; try { // Full path for txt var FilePath = Directory.EnumerateFiles(sourceDirectory, "*.*", SearchOption.TopDirectoryOnly).Where(s => s.ToLower().EndsWith(".xls") || s.ToLower().EndsWith(".xlsx")); // Count txt file DirectoryInfo di = new DirectoryInfo(sourceDirectory); int FileNumXls = di.GetFiles("*.xls").Length; int FileNumXlsx = di.GetFiles("*.xlsx").Length; int FileNum = FileNumXls + FileNumXlsx; // Throw no txt file if (FileNum == 0) { throw new ArgumentException("Excel file not found in folder."); } #region Validate Section var pbValidate = new ProgressBar(PbStyle.DoubleLine, FileNum); foreach (string currentFile in FilePath) { sheetChecker = true; // Update progress bar (Overall) fileName = Path.GetFileName(currentFile); pbValidate.Refresh(counterFileValidate, "Validating, Please wait..."); Thread.Sleep(50); System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance); using (var stream = File.Open(currentFile, FileMode.Open, FileAccess.Read)) { // ExcelDataReader Config var conf = new ExcelDataSetConfiguration() { ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration() { UseHeaderRow = true } }; using (var reader = ExcelReaderFactory.CreateReader(stream)) { // Validation Excel file do { if (reader.Name == sheetName) { sheetChecker = false; while (reader.Read()) { if (i > 0) { int rowNO = i + 1; ValidateString(reader, pbValidate, rowNO, counterFileValidate, 1, 10, "B", "Material Type"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 2, 255, "C", "Material Type Description"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 3, 10, "D", "Material Group"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 4, 255, "E", "Matl Grp Desc#"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 5, 12, "F", "Material"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 6, 255, "G", "Description"); ValidateDate(reader, pbValidate, rowNO, counterFileValidate, 7, "H", "Posting Date"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 8, 255, "I", "ได้รับมาจาก"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 9, 255, "J", "จ่ายไปให้"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 10, 10, "K", "Movement type"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 11, 255, "L", "Mvt Type Text"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 12, 100, "M", "Batch"); ValidateDate(reader, pbValidate, rowNO, counterFileValidate, 13, "N", "MFG Date"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 14, 100, "O", "Manufacturer Batch"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 15, 100, "P", "Manufacturer"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 16, 255, "Q", "Manufacturer Name"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 17, 100, "R", "Vendor"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 18, 255, "S", "Vendor Name"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 19, 20, "T", "Sold-to"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 20, 255, "U", "Sold-to Name"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 21, 255, "V", "Sold-to Address"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 22, 100, "W", "Sold-to Province"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 23, 20, "X", "Ship-to"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 24, 255, "Y", "Ship-to Name"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 25, 255, "Z", "Ship-to Address"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 26, 100, "AA", "Ship-to Province"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 27, 5, "AB", "Customer Group 1"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 28, 100, "AC", "Customer Group 1 - Desc#"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 29, 5, "AD", "Customer Group 2"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 30, 100, "AE", "Customer Group 2 - Desc#"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 31, 5, "AF", "Customer Group 3"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 32, 100, "AG", "Customer Group 3 - Desc#"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 33, 20, "AH", "FG material"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 34, 255, "AI", "FG Material Description"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 35, 100, "AJ", "FG Batch"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 36, 5, "AK", "Cost Center"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 37, 255, "AL", "Cost Center Description"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 38, 10, "AM", "Plant"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 39, 10, "AN", "Storage Loc#"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 40, 10, "AO", "Dest# Plant"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 41, 10, "AP", "Dest# Sloc"); ValidateFloat(reader, pbValidate, rowNO, counterFileValidate, 42, "AQ", "ยอดยกมา"); ValidateFloat(reader, pbValidate, rowNO, counterFileValidate, 43, "AR", "ปริมาณรับ"); ValidateFloat(reader, pbValidate, rowNO, counterFileValidate, 44, "AS", "ปริมาณจ่าย"); ValidateFloat(reader, pbValidate, rowNO, counterFileValidate, 45, "AT", "ปริมาณคงเหลือ"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 46, 20, "AU", "Unit"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 47, 255, "AV", "หมายเหตุ"); ValidateDate(reader, pbValidate, rowNO, counterFileValidate, 48, "AW", "Entered on"); ValidateDate(reader, pbValidate, rowNO, counterFileValidate, 49, "AX", "Entered at"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 50, 20, "AY", "Material Doc#"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 51, 10, "BZ", "Mat# Doc# Year"); ValidateString(reader, pbValidate, rowNO, counterFileValidate, 52, 5, "BA", "Mat# Doc#Item"); } } } } while (reader.NextResult()); } } // Change wording in progress bar if (counterFileValidate == FileNum) { pbValidate.Refresh(counterFileValidate, "Validate finished."); } // Return error if excel file don't have specific sheet name if (sheetChecker) { pbValidate.Refresh(counterFileValidate, "Validate failed."); throw new ArgumentException($"Excel must have sheet name \" {sheetName} \""); } counterFileValidate++; } #endregion #region Import Section // Create progress bar (Overall) var pbOverall = new ProgressBar(PbStyle.DoubleLine, FileNum); foreach (string currentFile in FilePath) { // Initial variable LineNum = 0; ColumnNum = 0; counterLine = 1; returnModel Model = new returnModel(); fileName = Path.GetFileName(currentFile); // Update progress bar (Overall) pbOverall.Refresh(counterFile, "Importing, Please wait..."); Thread.Sleep(50); using (var stream = File.Open(currentFile, FileMode.Open, FileAccess.Read)) { // ExcelDataReader Config var conf = new ExcelDataSetConfiguration() { ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration() { UseHeaderRow = true } }; using (var reader = ExcelReaderFactory.CreateReader(stream)) { // Validation Excel file do { if (reader.Name == sheetName) { // Read as DataSet var result = reader.AsDataSet(conf); // Convert to Datatable DataTable dt = result.Tables[sheetName]; // Row count LineNum = dt.Rows.Count; // Column count ColumnNum = dt.Columns.Count; // Validate excel column if (ColumnNum != ColumnNumChecker) { pbOverall.Refresh(counterFile, "Import error occured"); throw new ArgumentException($"Excel file must have {ColumnNumChecker} column!"); } // Sanitize data foreach (DataColumn c in dt.Columns) { if (c.DataType == typeof(string)) { foreach (DataRow r in dt.Rows) { r[c.ColumnName] = r[c.ColumnName].ToString().Trim(); // Convert empty string into NULL if (r[c.ColumnName].ToString().Length == 0) { r[c.ColumnName] = DBNull.Value; } } } } using (SqlBulkCopy bc = new SqlBulkCopy(conn, SqlBulkCopyOptions.UseInternalTransaction | SqlBulkCopyOptions.TableLock)) { bc.DestinationTableName = TableName; bc.BatchSize = reader.RowCount; bc.ColumnMappings.Add(1, "[Material Type]"); bc.ColumnMappings.Add(2, "[Material Type Description]"); bc.ColumnMappings.Add(3, "[Material Group]"); bc.ColumnMappings.Add(4, "[Matl Grp Desc#]"); bc.ColumnMappings.Add(5, "[Material]"); bc.ColumnMappings.Add(6, "[Description]"); bc.ColumnMappings.Add(7, "[Posting Date]"); bc.ColumnMappings.Add(8, "[ได้รับมาจาก]"); bc.ColumnMappings.Add(9, "[จ่ายไปให้]"); bc.ColumnMappings.Add(10, "[Movement type]"); bc.ColumnMappings.Add(11, "[Mvt Type Text]"); bc.ColumnMappings.Add(12, "[Batch]"); bc.ColumnMappings.Add(13, "[MFG Date]"); bc.ColumnMappings.Add(14, "[Manufacturer Batch]"); bc.ColumnMappings.Add(15, "[Manufacturer]"); bc.ColumnMappings.Add(16, "[Manufacturer Name]"); bc.ColumnMappings.Add(17, "[Vendor]"); bc.ColumnMappings.Add(18, "[Vendor Name]"); bc.ColumnMappings.Add(19, "[Sold-to]"); bc.ColumnMappings.Add(20, "[Sold-to Name]"); bc.ColumnMappings.Add(21, "[Sold-to Address]"); bc.ColumnMappings.Add(22, "[Sold-to Province]"); bc.ColumnMappings.Add(23, "[Ship-to]"); bc.ColumnMappings.Add(24, "[Ship-to Name]"); bc.ColumnMappings.Add(25, "[Ship-to Address]"); bc.ColumnMappings.Add(26, "[Ship-to Province]"); bc.ColumnMappings.Add(27, "[Customer Group 1]"); bc.ColumnMappings.Add(28, "[Customer Group 1 - Desc#]"); bc.ColumnMappings.Add(29, "[Customer Group 2]"); bc.ColumnMappings.Add(30, "[Customer Group 2 - Desc#]"); bc.ColumnMappings.Add(31, "[Customer Group 3]"); bc.ColumnMappings.Add(32, "[Customer Group 3 - Desc#]"); bc.ColumnMappings.Add(33, "[FG material]"); bc.ColumnMappings.Add(34, "[FG Material Description]"); bc.ColumnMappings.Add(35, "[FG Batch]"); bc.ColumnMappings.Add(36, "[Cost Center]"); bc.ColumnMappings.Add(37, "[Cost Center Description]"); bc.ColumnMappings.Add(38, "[Plant]"); bc.ColumnMappings.Add(39, "[Storage Loc#]"); bc.ColumnMappings.Add(40, "[Dest# Plant]"); bc.ColumnMappings.Add(41, "[Dest# Sloc]"); bc.ColumnMappings.Add(42, "[ยอดยกมา]"); bc.ColumnMappings.Add(43, "[ปริมาณรับ]"); bc.ColumnMappings.Add(44, "[ปริมาณจ่าย]"); bc.ColumnMappings.Add(45, "[ปริมาณคงเหลือ]"); bc.ColumnMappings.Add(46, "[Unit]"); bc.ColumnMappings.Add(47, "[หมายเหตุ]"); bc.ColumnMappings.Add(48, "[Entered on]"); bc.ColumnMappings.Add(49, "[Entered at]"); bc.ColumnMappings.Add(50, "[Material Doc#]"); bc.ColumnMappings.Add(51, "[Mat# Doc# Year]"); bc.ColumnMappings.Add(52, "[Mat# Doc#Item]"); bc.WriteToServer(dt); } } } while (reader.NextResult()); counterLine++; } // Create folder for file import successful if (!Directory.Exists(folderBackupPath)) { Directory.CreateDirectory(folderBackupPath); } // Move file to folder backup string destFile = Path.Combine(folderBackupPath, fileName); File.Move(currentFile, destFile); // Add detail to model for showing in table Model.RowNo = LineNum; Model.FileName = fileName; returnCollection.Add(Model); // Change wording in progress bar if (counterFile == FileNum) { pbOverall.Refresh(counterFile, "Import finished."); } counterFile++; } #endregion } } catch (Exception ex) { //pbOverall.Refresh(counterFile, "Import failed"); // Show error message Console.Write("\nError occured : ", Color.OrangeRed); Console.WriteLine(ex.Message); //Console.WriteLine("Error trace : " + ex.StackTrace); // Show error on if (!String.IsNullOrEmpty(fileName)) { Console.Write("\nError on : ", Color.OrangeRed); Console.WriteLine("'" + fileName + "'"); } // Show description Console.WriteLine("\nPlease check your path or file and try again.\n", Color.Yellow); } finally { // Show table if (returnCollection.Count > 0) { Console.WriteLine("\n--------------- Imported detail ---------------", Color.LightGreen); ConsoleTable.From(returnCollection).Write(); } //Console.WriteLine(JsonSerializer.Serialize(returnCollection)); // Show backup folder path if (Directory.Exists(folderBackupPath)) { Console.Write("\nImported folder : ", Color.LightGreen); Console.WriteLine($"'{ folderBackupPath }'"); } } // Wait key to terminate Console.Write("\nPress any key to close this window "); Console.ReadKey(); }