Exemplo n.º 1
0
        private void LoadMasterFile()
        {
            var provider = new ExcelProvider();
            var sheet    = this.shellViewModel.Sheets.SelectedItem;

            if (sheet != null)
            {
                var schools = provider.Read(this.shellViewModel.MasterFilePath, sheet.Name);

                var distinct = (from s in schools
                                orderby s.SchoolFullName
                                select s).DistinctBy(s => s.SchoolFullName);

                this.shellViewModel.Schools.Clear();
                this.shellViewModel.FilteredSchools.Clear();

                foreach (var school in distinct)
                {
                    var viewModel = new SchoolViewModel(school);
                    this.shellViewModel.Schools.Add(viewModel);
                    this.shellViewModel.FilteredSchools.Add(viewModel);
                }
            }

            this.shellViewModel.ApplyFilter();
        }
Exemplo n.º 2
0
        private void LoadFile(string fileName, string workSheetName = null)
        {
            var excel = new ExcelProvider(fileName, workSheetName);

            Assert.AreEqual(5, excel.Columns.Count());

            Assert.AreEqual(1, excel.Columns.First().Index);
            Assert.AreEqual("A", excel.Columns.First().Header);
            Assert.AreEqual("ID", excel.Columns.First().Name);

            Assert.AreEqual(5, excel.Columns.Last().Index);
            Assert.AreEqual("E", excel.Columns.Last().Header);
            Assert.AreEqual("BirthDate", excel.Columns.Last().Name);

            Assert.AreEqual(5, excel.Rows.Count());

            Assert.AreEqual(5, excel.Rows.Last().Index);
            Assert.AreEqual(5, excel.Rows.Last().Get <int>(1));
            Assert.AreEqual(5, excel.Rows.Last().Get <int>("A"));
            Assert.AreEqual(5, excel.Rows.Last().GetByName <int>("ID"));

            Assert.AreEqual("Peter", excel.Rows.Last().Get <string>(2));
            Assert.AreEqual("Peter", excel.Rows.Last().Get <string>("B"));
            Assert.AreEqual("Peter", excel.Rows.Last().GetByName <string>("FirstName"));

            Assert.AreEqual(DateTime.Parse("3/05/1979 0:00:00"), excel.Rows.Last().GetByName <DateTime>("BirthDate"));
        }
Exemplo n.º 3
0
 public ExcelDataAccess(string path)
 {
     _Path = path;
     _exProvider = ExcelProvider.Create(@path);
     if (_exProvider == null)
         MessageShow.FileDoesNotExist(path);
 }
Exemplo n.º 4
0
        public override void Execute(object parameter)
        {
            var provider = new ExcelProvider();
            var sheets   = provider.ReadSchema(this.shellViewModel.MasterFilePath);

            this.shellViewModel.Sheets.Clear();

            foreach (var sheet in sheets)
            {
                this.shellViewModel.Sheets.Add(new SheetViewModel(sheet));
            }

            if (!string.IsNullOrEmpty(this.shellViewModel.SelectedSheetName))
            {
                foreach (var sheet in this.shellViewModel.Sheets)
                {
                    if (sheet.Name == this.shellViewModel.SelectedSheetName)
                    {
                        this.shellViewModel.Sheets.SelectedItem = sheet;
                    }
                }
            }

            this.shellViewModel.Sheets.EnsureSelected();
        }
Exemplo n.º 5
0
        public DataSet GetDataFromExcel(string strFileName, out ArrayList al_error)
        {
            DataSet ds = null;

            ExcelProvider.GetExcelData(strFileName, out ds, out al_error);
            return(ds);
        }
        private void btnExport_Click(object sender, EventArgs e)
        {
            using (SaveFileDialog sfd = new SaveFileDialog())
            {
                sfd.Filter = "Excel Files | *.xlsx";
                if (sfd.ShowDialog() == DialogResult.OK)
                {
                    try
                    {
                        if (sfd.FileName != "" && sfd.FileName.EndsWith(".xlsx"))
                        {
                            var excelProvider = new ExcelProvider();

                            var fileBytes = excelProvider.ExportQuery("BuildingPMDebtor", FilterData(), new ExcelStyleSheet());

                            File.WriteAllBytes(sfd.FileName, fileBytes);

                            Process.Start(sfd.FileName);
                            // MessageBox.Show("Saved Succesfully", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "Report Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
            }
        }
Exemplo n.º 7
0
        private void btnExport_Click(object sender, EventArgs e)
        {
            using (SaveFileDialog sfd = new SaveFileDialog())
            {
                sfd.Filter = "Excel Files | *.xlsx";
                if (sfd.ShowDialog() == DialogResult.OK)
                {
                    try
                    {
                        if (sfd.FileName != "" && sfd.FileName.EndsWith(".xlsx"))
                        {
                            var excelProvider = new ExcelProvider();

                            var fileBytes = excelProvider.ExportQuery("Trustee Report List", _TrusteeReportResults.AsQueryable(), new ExcelStyleSheet());

                            File.WriteAllBytes(sfd.FileName, fileBytes);

                            Process.Start(sfd.FileName);
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "Report Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
            }
        }
Exemplo n.º 8
0
        public ActionResult <List <Booking> > GetSeed()
        {
            var xls = new ExcelProvider(_context);
            var res = xls.ParseData();

            return(null);
        }
Exemplo n.º 9
0
        private void CreateOffer_btn_Click(object sender, RoutedEventArgs e)
        {
            ExcelProvider ex = new ExcelProvider();

            ex.OpenExcelFile(kpFilePath, true);
            ex.WriteTOcell("F11", DateTime.Today.ToString(), "");
            ex.CloseWithoutSaving();
        }
Exemplo n.º 10
0
    public static ExcelProvider Create(string filePath, string sheet)
    {
        ExcelProvider provider = new ExcelProvider();

        provider.sheet    = sheet;
        provider.filePath = filePath;
        return(provider);
    }
Exemplo n.º 11
0
        public async Task <IActionResult> SearchExcel(string name)
        {
            var products = await SearchProducts(name);

            ExcelProvider <ProductOutputModel> excel = new ExcelProvider <ProductOutputModel>();
            var productsExcelStream = excel.CreateExcel(products);

            return(File(productsExcelStream, "application/ms-excel", "Catalog.xlsx"));
        }
Exemplo n.º 12
0
 static void Main(string[] args)
 {
     log4net.Config.XmlConfigurator.Configure();
     string firstName, lastName, dob, lexisNexisID;
     string filePath = @"yor directory path\your file name";
     
     ExcelProvider provider = ExcelProvider.Create(filePath, "Sheet1");
     foreach (ExcelRow row in (from x in provider select x))
     {
         Console.WriteLine("{0}", row.GetString(1));
Exemplo n.º 13
0
        static void Main(string[] args)
        {
            var excelProvider  = new ExcelProvider();
            var words          = excelProvider.ReadColumn("C:\\Users\\StephanyHenrique\\Documents\\planilha2.xls");
            var googleProvider = new GoogleProvider();

            foreach (var word in words)
            {
                googleProvider.SaveAudio(word);
            }
        }
Exemplo n.º 14
0
 public void ShouldParseExact()
 {
     using (var excelProvider = new ExcelProvider(@"D:\ExcelParser.xlsx"))
     {
         var customers = excelProvider.ParseExact<Customer>(x => x.Cells["Id"].Value.Equals("1"));
         foreach (var customer in customers)
         {
             //Do Something
         }
     }
 }
Exemplo n.º 15
0
 public void ShouldParseExact()
 {
     using (var excelProvider = new ExcelProvider(@"D:\ExcelParser.xlsx"))
     {
         var customers = excelProvider.ParseExact <Customer>(x => x.Cells["Id"].Value.Equals("1"));
         foreach (var customer in customers)
         {
             //Do Something
         }
     }
 }
Exemplo n.º 16
0
        public void TestCreateExcel()
        {
            ExcelProvider <Product> excel = new ExcelProvider <Product>();

            // Empty list test
            var mem = excel.CreateExcel(new List <Product>());

            ValidateExcel(mem, true);

            // Normal list
            var products2 = new List <Product>
            {
                new Product
                {
                    Id         = 1,
                    Name       = "Test",
                    LastUpdate = DateTime.Now,
                    Photo      = "test.photo",
                    Price      = 1
                },
                new Product
                {
                    Id         = 2,
                    Name       = "Test2",
                    LastUpdate = DateTime.Now,
                    Photo      = "test2.photo",
                    Price      = 2
                }
            };
            var mem2 = excel.CreateExcel(products2);

            // Different list
            var sheet = ValidateExcel(mem2);

            Assert.AreEqual(sheet.Cell(2, 1).Value, products2[0].Name);

            var testList = new List <TestClass>
            {
                new TestClass
                {
                    Byte = 2,
                    Int  = 3,
                    Long = 4
                }
            };
            ExcelProvider <TestClass> excelTestClass = new ExcelProvider <TestClass>();
            var mem3   = excelTestClass.CreateExcel(testList);
            var sheet3 = ValidateExcel(mem3);

            Assert.AreEqual(Convert.ToInt32(sheet3.Cell(2, 1).Value), testList[0].Int);
        }
Exemplo n.º 17
0
        private void ToXlsFile(ListView lv_items, string strFileName, string strPageName)
        {
            Cursor.Current = Cursors.WaitCursor;
            DataTable tab = new DataTable(strPageName);

            for (int i = 1; i <= lv_items.Columns.Count; i++)
            {
                System.Type tag = lv_items.Columns[i - 1].Tag as System.Type;
                tab.Columns.Add(lv_items.Columns[i - 1].Text, tag);
            }
            foreach (ListViewItem item in lv_items.Items)
            {
                DataRow row = tab.NewRow();
                for (int j = 1; j <= lv_items.Columns.Count; j++)
                {
                    System.Type type2 = lv_items.Columns[j - 1].Tag as System.Type;
                    if (j == 1)
                    {
                        if (string.IsNullOrEmpty(item.Text))
                        {
                            row[lv_items.Columns[j - 1].Text] = DBNull.Value;
                        }
                        else if (type2 == typeof(decimal))
                        {
                            row[lv_items.Columns[j - 1].Text] = Convert.ToDecimal(item.Text);
                        }
                        else
                        {
                            row[lv_items.Columns[j - 1].Text] = item.Text;
                        }
                    }
                    else if (string.IsNullOrEmpty(item.SubItems[j - 1].Text))
                    {
                        row[lv_items.Columns[j - 1].Text] = DBNull.Value;
                    }
                    else if (type2 == typeof(decimal))
                    {
                        row[lv_items.Columns[j - 1].Text] = Convert.ToDecimal(item.SubItems[j - 1].Text);
                    }
                    else
                    {
                        row[lv_items.Columns[j - 1].Text] = item.SubItems[j - 1].Text;
                    }
                }
                tab.Rows.Add(row);
            }
            ExcelProvider.SetExcelData(strFileName, tab);
            Cursor.Current = Cursors.Default;
        }
Exemplo n.º 18
0
        private void GetExcelImportTemplate()
        {
            string excelFileTemplate = ModelInfoSelected.ModelName + "_Template_" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
            var    dialog            = new SaveFileDialog()
            {
                FileName = excelFileTemplate,
                Filter   = "Excel Files(*.xlsx)|*.xlsx|All(*.*)|*"
            };

            if (dialog.ShowDialog() == true)
            {
                DataTable tempTable    = (new DataTable()).FromHeaders(ModelInfoSelected.ModelProperties);
                bool      exportResult = ExcelProvider.Export(tempTable, dialog.FileName);

                BBCodeBlock codeBlock = new BBCodeBlock();
                if (exportResult)
                {
                    codeBlock.BBCode = string.Format("Succesfully imported excel {0} model data template.\nFile: {1}.\n[color=Green]Open file?[/color]", ModelInfoSelected.ModelName, dialog.FileName);

                    var dlg = new ModernDialog
                    {
                        Title   = "Import Excel Template",
                        Content = codeBlock
                    };
                    dlg.Buttons = new[] { dlg.YesButton, dlg.NoButton };
                    bool?result = dlg.ShowDialog();
                    if (result != null && result.Value)
                    {
                        Process.Start(dialog.FileName);
                    }
                }
                else
                {
                    codeBlock.BBCode = string.Format("Error importing excel {0} model data template.\nFile: {1}.\n[color=Red]Please try again.[/color]", ModelInfoSelected.ModelName, dialog.FileName);
                    var dlg = new ModernDialog
                    {
                        Title   = "Import Excel Template",
                        Content = codeBlock
                    };
                    dlg.Buttons = new[] { dlg.OkButton };
                    dlg.ShowDialog();
                }
            }
        }
Exemplo n.º 19
0
        private void LoadDataBtn_Click_1(object sender, EventArgs e)
        {
            DataProvider provider = new ExcelProvider();

            data = provider.Load(sourceFilePath);

            if (data != null)
            {
                ColumnChartBtn.Enabled     = true;
                LineChartBtn.Enabled       = true;
                CurveChartBtn.Enabled      = true;
                ChangeStyleBtn.Enabled     = true;
                AddChartBtn.Enabled        = true;
                PieChartbtn.Enabled        = true;
                AddRowDataBtn.Enabled      = true;
                AddColumnDataBtn.Enabled   = true;
                TransposeMatrixBtn.Enabled = true;
            }
        }
Exemplo n.º 20
0
        private void ImportDataFromExcelFile()
        {
            var dialog = new OpenFileDialog()
            {
                Filter = "Excel Files(*.xlsx)|*.xlsx|All(*.*)|*"
            };

            if (dialog.ShowDialog() == true)
            {
                DataTable tempTable;
                bool      importResult = ExcelProvider.Import(out tempTable, dialog.FileName);

                BBCodeBlock codeBlock = new BBCodeBlock();
                if (importResult)
                {
                    codeBlock.BBCode = string.Format("Succesfully imported excel {0} model data.\nFile: {1}.\n[color=Green]Load data?[/color]", ModelInfoSelected.ModelName, dialog.FileName);

                    var dlg = new ModernDialog
                    {
                        Title   = "Import Excel Data",
                        Content = codeBlock
                    };
                    dlg.Buttons = new[] { dlg.YesButton, dlg.NoButton };
                    bool?result = dlg.ShowDialog();
                    if (result != null && result.Value)
                    {
                        ModuleItems = tempTable;
                    }
                }
                else
                {
                    codeBlock.BBCode = string.Format("Error importing excel {0} model data.\nFile: {1}.\n[color=Red]Please try again.[/color]", ModelInfoSelected.ModelName, dialog.FileName);
                    var dlg = new ModernDialog
                    {
                        Title   = "Import Excel Data",
                        Content = codeBlock
                    };
                    dlg.Buttons = new[] { dlg.OkButton };
                    dlg.ShowDialog();
                }
            }
        }
        public override Task WriteResponseBodyAsync(OutputFormatterWriteContext context)
        {
            try
            {
                var    response = context.HttpContext.Response;
                string xml      = GetXMLFromObject(context.Object);

                var accept = context.HttpContext.Request.Headers["Accept"].FirstOrDefault();
                response.ContentType = accept;

                var templateFile = context.HttpContext.Request.Headers["Template"].FirstOrDefault();
                ExcelProvider.ConvertToStream(templateFile, xml, response.Body);

                return(Task.CompletedTask);
            }
            catch
            {
                throw;
            }
        }
        public ActionResult GetExport(int count = 0)
        {
            var productFaker = new Faker <Product>()
                               .CustomInstantiator(f => new Product())
                               .RuleFor(p => p.Id, f => f.IndexFaker)
                               .RuleFor(p => p.Ean, f => f.Commerce.Ean13())
                               .RuleFor(p => p.Name, f => f.Commerce.ProductName())
                               .RuleFor(p => p.Description, f => f.Lorem.Sentence(f.Random.Int(5, 20)))
                               .RuleFor(p => p.Brand, f => f.Company.CompanyName())
                               .RuleFor(p => p.Category, f => f.Commerce.Categories(1).First())
                               .RuleFor(p => p.Price, f => f.Commerce.Price(1, 1000, 2, "€"))
                               .RuleFor(p => p.Quantity, f => f.Random.Int(0, 1000))
                               .RuleFor(p => p.Rating, f => f.Random.Float(0, 1))
                               .RuleFor(p => p.ReleaseDate, f => f.Date.Past(2));

            var excelProvider = new ExcelProvider();

            excelProvider.Generate(productFaker.Generate(count), null, "Sheet 1");

            return(File(excelProvider.File, "application/octet-stream"));
        }
Exemplo n.º 23
0
        private void btnExport_Click(object sender, EventArgs e)
        {
            using (SaveFileDialog sfd = new SaveFileDialog())
            {
                sfd.Filter = "Excel Files | *.xlsx";
                if (sfd.ShowDialog() == DialogResult.OK)
                {
                    try
                    {
                        if (sfd.FileName != "" && sfd.FileName.EndsWith(".xlsx"))
                        {
                            var excelProvider = new ExcelProvider();

                            var results = _AllResults.Select(a => new TransactionSearchModel
                            {
                                BuildingPath    = a.BuildingPath,
                                TransactionDate = a.TransactionDate,
                                AccountNumber   = a.AccountNumber,
                                LinkAccount     = (a.LinkAccount == "\0\0\0\0\0\0\0") ? string.Empty : a.LinkAccount, //Pastel handles null values as \0\0\0\0\0\0\0
                                Reference       = a.Refrence,
                                Description     = a.Description,
                                Amount          = a.Amount
                            }).AsQueryable();

                            var fileBytes = excelProvider.ExportQuery("Transaction Search", results, new ExcelStyleSheet());

                            File.WriteAllBytes(sfd.FileName, fileBytes);

                            Process.Start(sfd.FileName);
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "Report Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
            }
        }
Exemplo n.º 24
0
        private static void Main(string[] args)
        {
            var e = new ExcelProvider();

            e.Read(OdsFile, "KiTa");

            var data = File.ReadAllBytes(CsvFile);


            var lines = File.ReadAllLines(CsvFile);

            using (var reader = new StreamReader(CsvFile, true))
            {
                reader.Read();
                Console.WriteLine(reader.CurrentEncoding);
            }



            var c = new CsvProvider();

            c.Read(CsvFile);
        }
Exemplo n.º 25
0
        public void ToExcel(string url, string name, string stateName, string columnsOverride, bool?labelColumns, string xlsName = "", string controllerAssembly = "Concentrator.ui.Management.Controllers")
        {
            if (string.IsNullOrEmpty(controllerAssembly))
            {
                controllerAssembly = "Concentrator.ui.Management.Controllers";
            }
            var grid            = new System.Web.UI.WebControls.GridView();
            var addExtraColumns = labelColumns ?? false;

            if (string.IsNullOrEmpty(xlsName))
            {
                xlsName = name;
            }

            var jsonData = (((JsonResult)GetDataSource(url, controllerAssembly)).Data);
            var data     = jsonData.GetType().GetProperty("results").GetValue(jsonData, null);

            DatabaseStateColumnProvider provider = new DatabaseStateColumnProvider();
            var columns = provider.GetColumnDefinitions(Client.User.UserID, stateName, GetUnitOfWork());

            if (columnsOverride != null)
            {
                string[] values = columnsOverride.Split(',');

                foreach (var item in values)
                {
                    var stateColumns = columns.Where(c => columnsOverride.Contains(c.Header)).ToList();

                    columns = stateColumns;
                }
            }
            if (addExtraColumns)
            {
                DefaultColumnDefinition fromDateColumn = new DefaultColumnDefinition()
                {
                    DataIndex = "fromDate", Header = "FromDate", Hidden = false, Width = 100
                };
                columns.Add(fromDateColumn);

                DefaultColumnDefinition toDateColumn = new DefaultColumnDefinition()
                {
                    DataIndex = "toDate", Header = "toDate", Hidden = false, Width = 100
                };
                columns.Add(toDateColumn);

                DefaultColumnDefinition labelColumn = new DefaultColumnDefinition()
                {
                    DataIndex = "Label", Header = "Label", Hidden = false, Width = 100
                };
                columns.Add(labelColumn);

                DefaultColumnDefinition priceColumn = new DefaultColumnDefinition()
                {
                    DataIndex = "Price", Header = "Price", Hidden = false, Width = 100
                };
                columns.Add(priceColumn);

                DefaultColumnDefinition productIDColumn = new DefaultColumnDefinition()
                {
                    DataIndex = "ProductID", Header = "ProductID", Hidden = false, Width = 100
                };
                columns.Add(productIDColumn);

                DefaultColumnDefinition vendorIDColumn = new DefaultColumnDefinition()
                {
                    DataIndex = "VendorID", Header = "VendorID", Hidden = false, Width = 100
                };
                columns.Add(vendorIDColumn);

                DefaultColumnDefinition connectorIDColumn = new DefaultColumnDefinition()
                {
                    DataIndex = "ConnectorID", Header = "ConnectorID", Hidden = false, Width = 100
                };
                columns.Add(connectorIDColumn);
            }
            var excelProvider = new ExcelProvider(columns, data, xlsName);

            var excel = excelProvider.GetExcelDocument();

            Response.Clear();
            Response.ClearContent();

            Response.AddHeader("content-disposition", string.Format("attachment; filename={0}.xlsx", name));
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.BinaryWrite(excel);
            Response.End();
        }
Exemplo n.º 26
0
        public void inputpromotionpricelistdetail(object obj)
        {
            string connection_string = Utils.getConnectionstr();
            LinqtoSQLDataContext db  = new LinqtoSQLDataContext(connection_string);

            datainportF2 inf = (datainportF2)obj;

            string filename = inf.filename;
            string programe = inf.programe.Trim();



            //   string connection_string = Utils.getConnectionstr();

            LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);


            dc.ExecuteCommand("DELETE FROM tbl_KApromotinprice Where tbl_KApromotinprice.programId = '" + programe + "'");
            //    dc.tblFBL5Nnewthisperiods.DeleteAllOnSubmit(rsthisperiod);
            dc.CommandTimeout = 0;
            dc.SubmitChanges();


            //list vn
            var listvn = new List <string>();

            listvn = (from tbl_karegion in dc.tbl_karegions
                      select tbl_karegion.Region.Trim()).ToList();
            listvn.Add("VN");

            //list vn
            // listvn.Contains("dog");
            // lisKanuber

            var listkeyaccount   = new List <string>();
            var listkeyaccountrs = (from tbl_ka_prCustomer in dc.tbl_ka_prCustomers
                                    group tbl_ka_prCustomer.KeyAccount by tbl_ka_prCustomer.KeyAccount into h
                                    select h.Key);

            foreach (var item in listkeyaccountrs)
            {
                listkeyaccount.Add(((int)item).ToString());
            }
            //   listvn.Add("VN");
            //liskanumber

            // listsaledistric

            var listsaledistric = new List <string>();

            listsaledistric = (from tbl_ka_prCustomer in dc.tbl_ka_prCustomers
                               group tbl_ka_prCustomer.Salesdistrict by tbl_ka_prCustomer.Salesdistrict into g
                               select g.Key.Trim()).ToList();
            //   listvn.Add("VN");
            //listsaledistric

            // listsafuction

            var listfuction = new List <string>();

            listfuction = (from tbl_KAlistpricefunction in dc.tbl_KAlistpricefunctions
                           group tbl_KAlistpricefunction.function by tbl_KAlistpricefunction.function into g
                           select g.Key.Trim()).ToList();

            //   listvn.Add("VN");
            //listsafuction



            ExcelProvider ExcelProvide = new ExcelProvider();

            //#endregion
            System.Data.DataTable sourceData = ExcelProvide.GetDataFromExcel(filename);

            DataTable batable = new DataTable();

            batable.Columns.Add("product_code", typeof(string));
            batable.Columns.Add("product_group", typeof(string));

            batable.Columns.Add("percent_amount", typeof(double));
            batable.Columns.Add("amount", typeof(double));

            batable.Columns.Add("customerid", typeof(double));
            batable.Columns.Add("keyaccount", typeof(double));

            batable.Columns.Add("sales_region", typeof(string));
            batable.Columns.Add("saledistrict", typeof(string));
            batable.Columns.Add("programId", typeof(string));

            batable.Columns.Add("fromdate", typeof(DateTime));
            batable.Columns.Add("todate", typeof(DateTime));
            batable.Columns.Add("row", typeof(int));



            string sales_region = "0";
            double keyaccount   = 0;
            string saledistrict = "0";
            double customerid   = 0;


            //string product_group = "0";
            //double percent_amount = 0;
            //double amount = 0;



            int product_codeid = 0;
            //     int product_nameid = 0;

            int product_groupid = 0;
            //    int product_groupnameid = 0;

            // int percent_amountid = 0;
            int amountid = 0;
            int unitid   = 0;

            int UoMid = 0;

            //int customeridid = 0;
            //int keyaccountid = 0;
            int sales_regionid = 0;
            //int saledistrictid = 0;
            int colid = 0;

            int fromdateid = 0;
            int todateid   = 0;

            //    int rowif = 0;

            int    headindex   = 0;
            bool   grouproduct = false;
            double penctamount = 0;
            double amount      = 0;

            for (int rowid = 0; rowid < sourceData.Rows.Count; rowid++)
            {
                headindex = 1;


                for (int columid = 0; columid < sourceData.Columns.Count; columid++)
                {
                    string value = sourceData.Rows[rowid][columid].ToString();

                    if (value != null)
                    {
                        #region setcolum
                        if (value.Trim() == "CnTy")
                        {
                            sales_regionid = columid;
                            headindex      = 0;
                        }

                        if (value.Trim() == "Material")
                        {
                            //   product_nameid = columid;
                            colid          = product_codeid;
                            product_codeid = columid;
                            grouproduct    = false;
                            headindex      = 0;
                        }


                        if (value.Trim() == "Material")
                        {
                            if (colid < columid && colid != 0)
                            {
                                product_codeid = colid;
                                headindex      = 0;
                                grouproduct    = false;
                            }
                        }


                        if (value.Trim() == "Matl Group")
                        {
                            colid           = product_groupid;
                            product_groupid = columid;
                            //       product_groupnameid = columid;
                            grouproduct = true;
                            headindex   = 0;
                        }

                        if (value.Trim() == "Matl Group")
                        {
                            if (colid < columid && colid != 0)
                            {
                                product_groupid = colid;
                                headindex       = 0;
                                grouproduct     = true;
                            }
                        }



                        if (value.Trim() == "Amount")
                        {
                            amountid  = columid;
                            headindex = 0;
                        }
                        if (value.Trim() == "Unit")
                        {
                            unitid    = columid;
                            headindex = 0;
                        }

                        if (value.Trim() == "UoM")
                        {
                            UoMid     = columid;
                            headindex = 0;
                        }

                        if (value.Trim() == "Valid From")
                        {
                            fromdateid = columid;
                            headindex  = 0;
                        }

                        if (value.Trim() == "Valid to")
                        {
                            todateid  = columid;
                            headindex = 0;
                        }

                        #endregion

                        // view basetable
                    }

                    //------------
                }// forcolum

                #region setvalue of pricelist
                //   string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
                string sales_regionvalue = sourceData.Rows[rowid][sales_regionid].ToString();


                if (headindex != 0 && sales_regionvalue != "" && listvn.Contains(sales_regionvalue.Trim()))   // trong nhoms fuction
                {
                    sales_region = sales_regionvalue;
                    keyaccount   = 0;
                }



                if (headindex != 0 && sales_regionvalue != "" && listkeyaccount.Contains(sales_regionvalue))   // trong nhoms fuction
                {
                    // sales_region = sales_regionvalue;
                    keyaccount = double.Parse(sales_regionvalue);
                }

                if (headindex != 0 && sales_regionvalue != "" && listsaledistric.Contains(sales_regionvalue.Trim()))   // trong nhoms fuction
                {
                    sales_region = "0";
                    keyaccount   = 0;

                    saledistrict = sales_regionvalue;
                }

                if (headindex != 0 && sales_regionvalue != "" && sales_regionvalue.Trim().Length >= 8 && Utils.IsValidnumber(sales_regionvalue))   // trong nhoms fuction
                {
                    sales_region = "0";
                    keyaccount   = 0;
                    saledistrict = "0";
                    customerid   = double.Parse(sales_regionvalue.ToString());
                }


                if (headindex != 0 && sales_regionvalue != "" && listfuction.Contains(sales_regionvalue.Trim()))   // trong nhoms fuction
                {
                    DataRow dr = batable.NewRow();

                    dr["sales_region"] = sales_region.Trim();
                    dr["programId"]    = programe;// sourceData.Rows[rowid][product_codeid].ToString().Trim();// Utils.GetValueOfCellInExcel(worksheet, rowid, columUoM);

                    //   dr["Material"] = Utils.GetValueOfCellInExcel(worksheet, rowid, columpmaterial);
                    dr["keyaccount"]   = keyaccount;
                    dr["saledistrict"] = saledistrict.ToString(); // sourceData.Rows[rowid][columunit].ToString().Trim();//  Utils.GetValueOfCellInExcel(worksheet, rowid, columunit);
                    dr["customerid"]   = customerid;              //sourceData.Rows[rowid][columname].ToString().Trim();//Utils.GetValueOfCellInExcel(worksheet, rowid, columname);
                    if (sourceData.Rows[rowid][product_codeid] != null && sourceData.Rows[rowid][product_codeid].ToString() != "" && grouproduct == false)
                    {
                        dr["product_code"]  = sourceData.Rows[rowid][product_codeid].ToString().Trim();// Utils.GetValueOfCellInExcel(worksheet, rowid, columUoM);
                        dr["product_group"] = "0";
                    }
                    else
                    {
                        dr["product_code"] = "0";
                    }

                    if (sourceData.Rows[rowid][product_groupid] != null && sourceData.Rows[rowid][product_groupid].ToString() != "" && grouproduct == true)
                    {
                        dr["product_group"] = sourceData.Rows[rowid][product_groupid].ToString().Trim();// Utils.GetValueOfCellInExcel(worksheet, rowid, columUoM);
                        dr["product_code"]  = "0";
                    }
                    else
                    {
                        dr["product_group"] = "0";
                    }

                    dr["row"] = rowid;// double.Parse(sourceData.Rows[rowid][columpamount].ToString());// Utils.GetValueOfCellInExcel(worksheet, rowid, columpamount);

                    if (sourceData.Rows[rowid][unitid].ToString().Trim() == "%")
                    {
                        //    dr["percent_amount"] = double.Parse(sourceData.Rows[rowid][amountid].ToString());// Utils.GetValueOfCellInExcel(worksheet, rowid, columpamount);
                        //  dr["amount"] = 0;

                        penctamount          = double.Parse(sourceData.Rows[rowid][amountid].ToString());// Utils.GetValueOfCellInExcel(worksheet, rowid, columpamount);;
                        amount               = 0;
                        dr["percent_amount"] = penctamount;
                        dr["amount"]         = amount;
                    }
                    else
                    {
                        penctamount          = 0;
                        amount               = double.Parse(sourceData.Rows[rowid][amountid].ToString());// Utils.GetValueOfCellInExcel(worksheet, rowid, columpamount);;
                        dr["percent_amount"] = penctamount;
                        dr["amount"]         = amount;

                        //      dr["amount"] = double.Parse(sourceData.Rows[rowid][amountid].ToString());// Utils.GetValueOfCellInExcel(worksheet, rowid, columpamount);
                        //    dr["percent_amount"] = 0;
                    }
                    dr["fromdate"] = Utils.chageExceldatetoData(sourceData.Rows[rowid][fromdateid].ToString()); // Utils.GetValueOfCellInExcel(worksheet, rowid, columValid_From);
                    dr["todate"]   = Utils.chageExceldatetoData(sourceData.Rows[rowid][todateid].ToString());   // Utils.GetValueOfCellInExcel(worksheet, rowid, columValid_to);

                    if (penctamount + amount != 0)
                    {
                        batable.Rows.Add(dr);
                    }
                }



                #endregion
            } //fro row

            //conpy to server
            string destConnString = Utils.getConnectionstr();

            //adapter.FillSchema(sourceData, SchemaType.Source);
            //sourceData.Columns["Posting Date"].DataType = typeof(DateTime);
            //sourceData.Columns["Invoice Doc Nr"].DataType = typeof(float);
            //sourceData.Columns["Billed Qty"].DataType = typeof(float);
            //sourceData.Columns["Cond Value"].DataType = typeof(float);
            //sourceData.Columns["Sales Org"].DataType = typeof(string);
            //sourceData.Columns["Cust Name"].DataType = typeof(string);
            //sourceData.Columns["Outbound Delivery"].DataType = typeof(string);
            //sourceData.Columns["Mat Group"].DataType = typeof(string);
            //sourceData.Columns["Mat Group Text"].DataType = typeof(string);
            //sourceData.Columns["UoM"].DataType = typeof(string);
            //---------------fill data


            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
            {
                bulkCopy.DestinationTableName = "tbl_KApromotinprice";
                // Write from the source to the destination.
                bulkCopy.BulkCopyTimeout = 0;

                bulkCopy.ColumnMappings.Add("product_code", "product_code");

                bulkCopy.ColumnMappings.Add("product_group", "product_group");
                //   bulkCopy.ColumnMappings.Add("MaterialNAme", "MaterialNAme");
                bulkCopy.ColumnMappings.Add("percent_amount", "percent_amount");
                bulkCopy.ColumnMappings.Add("amount", "amount");
                bulkCopy.ColumnMappings.Add("customerid", "customerid");
                bulkCopy.ColumnMappings.Add("keyaccount", "keyaccount");
                bulkCopy.ColumnMappings.Add("sales_region", "sales_region");


                bulkCopy.ColumnMappings.Add("saledistrict", "saledistrict");
                bulkCopy.ColumnMappings.Add("programId", "programId");
                bulkCopy.ColumnMappings.Add("fromdate", "fromdate");
                bulkCopy.ColumnMappings.Add("todate", "todate");
                bulkCopy.ColumnMappings.Add("row", "row");


                try
                {
                    bulkCopy.WriteToServer(batable);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    Thread.CurrentThread.Abort();
                }
            }
            //copy to server
            //   string connection_string = Utils.getConnectionstr();

            //    LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);

            //    var typeffmain = typeof(tbl_KAbaseprice);
            //     var typeffsub = typeof(tbl_KAbaseprice);

            //    VInputchange inputcdata1 = new VInputchange("", "Base price list", dc, "tbl_KAbaseprice", "tbl_KAbaseprice", typeffmain, typeffsub, "id", "id", "");
            //    inputcdata1.ShowDialog();
            //  View.Viewdatatable TB = new View.Viewdatatable(batable, "lIST DATA");
            //  TB.ShowDialog();

            //  }
        }
Exemplo n.º 27
0
        public void inputbasepricelistdetail(object obj)
        {
            string connection_string = Utils.getConnectionstr();
            LinqtoSQLDataContext db  = new LinqtoSQLDataContext(connection_string);

            datainportF inf = (datainportF)obj;

            string filename = inf.filename;

            //   string filename = theDialog.FileName.ToString();

            //   string connection_string = Utils.getConnectionstr();

            LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);


            dc.ExecuteCommand("DELETE FROM tbl_KAbaseprice");
            //    dc.tblFBL5Nnewthisperiods.DeleteAllOnSubmit(rsthisperiod);
            dc.CommandTimeout = 0;
            dc.SubmitChanges();



            ExcelProvider ExcelProvide = new ExcelProvider();

            //#endregion
            System.Data.DataTable sourceData = ExcelProvide.GetDataFromExcel(filename);

            DataTable batable = new DataTable();

            batable.Columns.Add("PriceList", typeof(string));
            batable.Columns.Add("Material", typeof(string));
            batable.Columns.Add("MaterialNAme", typeof(string));
            batable.Columns.Add("Amount", typeof(double));

            batable.Columns.Add("Unit", typeof(string));
            batable.Columns.Add("UoM", typeof(string));

            batable.Columns.Add("Valid_From", typeof(DateTime));
            batable.Columns.Add("Valid_to", typeof(DateTime));



            string Pricelist       = "";
            int    columpricelist  = 0;
            int    columpmaterial  = 0;
            int    columname       = 0;
            int    columpamount    = 0;
            int    columunit       = 0;
            int    columUoM        = 0;
            int    columValid_From = 0;
            int    columValid_to   = 0;
            int    headindex       = 0;

            for (int rowid = 0; rowid < sourceData.Rows.Count; rowid++)
            {
                headindex = 1;
                for (int columid = 0; columid < sourceData.Columns.Count; columid++)
                {
                    string value = sourceData.Rows[rowid][columid].ToString();

                    if (value != null)
                    {
                        #region setcolum
                        if (value.Trim() == "CnTy")
                        {
                            columpricelist = columid;
                            headindex      = 0;
                        }

                        if (value.Trim() == "Material")
                        {
                            if (columname == 0)
                            {
                                columpmaterial = columid;
                                headindex      = 0;
                            }
                        }


                        if (value.Trim() == "Material")
                        {
                            if (columpmaterial != 0)
                            {
                                columname = columid;
                                headindex = 0;
                            }
                        }


                        if (value.Trim() == "Amount")
                        {
                            columpamount = columid;
                            headindex    = 0;
                        }
                        if (value.Trim() == "Unit")
                        {
                            columunit = columid;
                            headindex = 0;
                        }

                        if (value.Trim() == "UoM")
                        {
                            columUoM  = columid;
                            headindex = 0;
                        }

                        if (value.Trim() == "Valid From")
                        {
                            columValid_From = columid;
                            headindex       = 0;
                        }

                        if (value.Trim() == "Valid to")
                        {
                            columValid_to = columid;
                            headindex     = 0;
                        }

                        #endregion


                        // view basetable
                    }

                    //------------
                }    // colum


                #region setvalue of pricelist
                //   string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
                string valuepricelist = sourceData.Rows[rowid][columpricelist].ToString();
                if (headindex != 0 && valuepricelist != "" && valuepricelist != "YPR0")
                {
                    Pricelist = valuepricelist;
                }

                if (headindex != 0 && valuepricelist == "YPR0")
                {
                    DataRow dr = batable.NewRow();
                    dr["PriceList"] = Pricelist.Trim();

                    //   dr["Material"] = Utils.GetValueOfCellInExcel(worksheet, rowid, columpmaterial);
                    dr["Material"] = sourceData.Rows[rowid][columpmaterial].ToString().Trim();

                    dr["MaterialNAme"] = sourceData.Rows[rowid][columname].ToString().Trim();                            //Utils.GetValueOfCellInExcel(worksheet, rowid, columname);
                    dr["Amount"]       = double.Parse(sourceData.Rows[rowid][columpamount].ToString());                  // Utils.GetValueOfCellInExcel(worksheet, rowid, columpamount);
                    dr["Unit"]         = sourceData.Rows[rowid][columunit].ToString().Trim();                            //  Utils.GetValueOfCellInExcel(worksheet, rowid, columunit);
                    dr["UoM"]          = sourceData.Rows[rowid][columUoM].ToString().Trim();                             // Utils.GetValueOfCellInExcel(worksheet, rowid, columUoM);
                    dr["Valid_From"]   = Utils.chageExceldatetoData(sourceData.Rows[rowid][columValid_From].ToString()); // Utils.GetValueOfCellInExcel(worksheet, rowid, columValid_From);
                    dr["Valid_to"]     = Utils.chageExceldatetoData(sourceData.Rows[rowid][columValid_to].ToString());   // Utils.GetValueOfCellInExcel(worksheet, rowid, columValid_to);

                    batable.Rows.Add(dr);
                }

                #endregion
            }// row

            //conpy to server
            string destConnString = Utils.getConnectionstr();

            //adapter.FillSchema(sourceData, SchemaType.Source);
            //sourceData.Columns["Posting Date"].DataType = typeof(DateTime);
            //sourceData.Columns["Invoice Doc Nr"].DataType = typeof(float);
            //sourceData.Columns["Billed Qty"].DataType = typeof(float);
            //sourceData.Columns["Cond Value"].DataType = typeof(float);
            //sourceData.Columns["Sales Org"].DataType = typeof(string);
            //sourceData.Columns["Cust Name"].DataType = typeof(string);
            //sourceData.Columns["Outbound Delivery"].DataType = typeof(string);
            //sourceData.Columns["Mat Group"].DataType = typeof(string);
            //sourceData.Columns["Mat Group Text"].DataType = typeof(string);
            //sourceData.Columns["UoM"].DataType = typeof(string);
            //---------------fill data


            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
            {
                bulkCopy.DestinationTableName = "tbl_KAbaseprice";
                // Write from the source to the destination.
                bulkCopy.BulkCopyTimeout = 0;

                bulkCopy.ColumnMappings.Add("PriceList", "PriceList");

                bulkCopy.ColumnMappings.Add("Material", "Material");
                bulkCopy.ColumnMappings.Add("MaterialNAme", "MaterialNAme");
                bulkCopy.ColumnMappings.Add("Amount", "Amount");
                bulkCopy.ColumnMappings.Add("Unit", "Unit");
                bulkCopy.ColumnMappings.Add("UoM", "UoM");
                bulkCopy.ColumnMappings.Add("Valid_From", "[Valid From]");
                bulkCopy.ColumnMappings.Add("Valid_to", "[Valid to]");


                //bulkCopy.ColumnMappings.Add("Valid_to", "Valid_to");
                //bulkCopy.ColumnMappings.Add("Valid_to", "Valid_to");
                //bulkCopy.ColumnMappings.Add("Valid_to", "Valid_to");
                //bulkCopy.ColumnMappings.Add("Valid_to", "Valid_to");



                try
                {
                    bulkCopy.WriteToServer(batable);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    Thread.CurrentThread.Abort();
                }
            }
            //copy to server
            //   string connection_string = Utils.getConnectionstr();

            //    LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);

            //    var typeffmain = typeof(tbl_KAbaseprice);
            //     var typeffsub = typeof(tbl_KAbaseprice);

            //    VInputchange inputcdata1 = new VInputchange("", "Base price list", dc, "tbl_KAbaseprice", "tbl_KAbaseprice", typeffmain, typeffsub, "id", "id", "");
            //    inputcdata1.ShowDialog();
            //  View.Viewdatatable TB = new View.Viewdatatable(batable, "lIST DATA");
            //  TB.ShowDialog();

            //  }
        }
        private void importsexcel2(object obj)
        {
            //     List<tblFBL5N> fbl5n_ctrllist = new List<tblFBL5N>();
            edlpinput_ctrl md = new edlpinput_ctrl();

            bool kq = md.deleteedlp();

            datainportF inf = (datainportF)obj;

            string filename = inf.filename;



            //      ExcelProvider ExcelProvide = new ExcelProvider();
            //#endregion
            System.Data.DataTable sourceData = ExcelProvider.GetDataFromExcel(filename);

            System.Data.DataTable batable = new System.Data.DataTable();



            batable.Columns.Add("Soldto", typeof(double));
            batable.Columns.Add("SalesOrg", typeof(string));
            batable.Columns.Add("CustName", typeof(string));
            batable.Columns.Add("InvoiceDocNr", typeof(double));
            batable.Columns.Add("OutboundDelivery", typeof(string));
            batable.Columns.Add("MatNumber", typeof(string));
            batable.Columns.Add("MatText", typeof(string));
            batable.Columns.Add("BilledQty", typeof(double));
            batable.Columns.Add("CondValue", typeof(double));
            batable.Columns.Add("MatGroup", typeof(string));
            batable.Columns.Add("MatGroupText", typeof(string));

            batable.Columns.Add("UoM", typeof(string));



            int Soldto           = -1;
            int SalesOrg         = -1;
            int CustName         = -1;
            int InvoiceDocNr     = -1;
            int OutboundDelivery = -1;
            int MatNumber        = -1;
            int MatText          = -1;
            int BilledQty        = -1;
            int CondValue        = -1;
            int MatGroup         = -1;
            int MatGroupText     = -1;
            int UoM = -1;

            int rowseet = sourceData.Rows.Count;

            if (rowseet > 5)
            {
                rowseet = 5;
            }


            for (int rowid = 0; rowid < rowseet; rowid++)
            {
                // headindex = 1;
                for (int columid = 0; columid < sourceData.Columns.Count; columid++)
                {
                    #region
                    string value = sourceData.Rows[rowid][columid].ToString();
                    //OleDbCommand command = new OleDbCommand(
                    //                   @"SELECT [], [],[],[],
                    //                [], [], [],[],
                    //                [], [], [], [] FROM [Sheet1$]
                    //                 WHERE (([Invoice Doc Nr] is not null ) AND ([Sales Org]<> ''))", conn);



                    if (value != null && value != "")
                    {
                        //    #region setcolum
                        if (value.Trim() == ("Sold-to"))
                        {
                            Soldto = columid;
                            //  headindex = rowid;
                        }

                        if (value.Trim() == ("Sales Org"))
                        {
                            SalesOrg = columid;
                            //    headindex = 0;
                        }



                        if (value.Trim() == ("Cust Name"))
                        {
                            CustName = columid;
                            //    headindex = 0;
                        }

                        if (value.Trim() == ("Invoice Doc Nr"))
                        {
                            InvoiceDocNr = columid;
                            //   headindex = 0;
                        }


                        if (value.Trim() == ("Outbound Delivery"))
                        {
                            OutboundDelivery = columid;
                        }
                        if (value.Trim() == ("Mat Number"))
                        {
                            MatNumber = columid;
                        }



                        if (value.Trim() == ("Mat Text"))
                        {
                            MatText = columid;
                        }
                        if (value.Trim() == ("Billed Qty"))
                        {
                            BilledQty = columid;
                        }

                        if (value.Trim() == ("Cond Value"))
                        {
                            CondValue = columid;
                        }
                        if (value.Trim() == ("Mat Group"))
                        {
                            MatGroup = columid;
                        }
                        if (value.Trim() == ("Mat Group Text"))
                        {
                            MatGroupText = columid;
                        }
                        if (value.Trim() == ("UoM"))
                        {
                            UoM = columid;
                        }
                    }
                    #endregion
                } // colum
            }     // roww off heatder

            #region



            if (Soldto == -1)
            {
                MessageBox.Show("Dữ liệu thiếu cột Sold to  ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (SalesOrg == -1)
            {
                MessageBox.Show("Dữ liệu thiếu cột  Sales Org", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (CustName == -1)
            {
                MessageBox.Show("Dữ liệu thiếu cột   Cust Name", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (InvoiceDocNr == -1)
            {
                MessageBox.Show("Dữ liệu thiếu cột Invoice Doc Nr  ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (OutboundDelivery == -1)
            {
                MessageBox.Show("Dữ liệu thiếu cột Outbound Delivery", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (MatNumber == -1)
            {
                MessageBox.Show("Dữ liệu thiếu cột  Mat Number  ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (MatText == -1)
            {
                MessageBox.Show("Dữ liệu thiếu cột  Mat Text", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (BilledQty == -1)
            {
                MessageBox.Show("Dữ liệu thiếu cột Billed Qty ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (CondValue == -1)
            {
                MessageBox.Show("Dữ liệu thiếu cột CondValue ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (MatGroup == -1)
            {
                MessageBox.Show("Dữ liệu thiếu cột  MatGroup", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (MatGroupText == -1)
            {
                MessageBox.Show("Dữ liệu thiếu cột  MatGroupText", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (UoM == -1)
            {
                MessageBox.Show("Dữ liệu thiếu cột  UoM", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            #endregion


            for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++)
            {
                #region


                //   string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
                string InvoiceDocNrv = sourceData.Rows[rowixd][InvoiceDocNr].ToString();
                if (InvoiceDocNrv != "" && Utils.IsValidnumber(InvoiceDocNrv))
                {
                    if (double.Parse(InvoiceDocNrv) > 0)
                    {
                        DataRow dr = batable.NewRow();
                        dr["Soldto"]   = double.Parse(sourceData.Rows[rowixd][Soldto].ToString());
                        dr["SalesOrg"] = sourceData.Rows[rowixd][SalesOrg].ToString().Trim();
                        dr["CustName"] = sourceData.Rows[rowixd][CustName].ToString().Trim();
                        if (Utils.IsValidnumber(sourceData.Rows[rowixd][InvoiceDocNr].ToString()))
                        {
                            dr["InvoiceDocNr"] = double.Parse(sourceData.Rows[rowixd][InvoiceDocNr].ToString());
                        }

                        dr["InvoiceDocNr"]     = double.Parse(sourceData.Rows[rowixd][InvoiceDocNr].ToString());
                        dr["OutboundDelivery"] = sourceData.Rows[rowixd][OutboundDelivery].ToString().Trim();
                        dr["MatNumber"]        = sourceData.Rows[rowixd][MatNumber].ToString().Trim();
                        dr["MatText"]          = sourceData.Rows[rowixd][MatText].ToString().Trim();
                        if (Utils.IsValidnumber(sourceData.Rows[rowixd][BilledQty].ToString()))
                        {
                            dr["BilledQty"] = double.Parse(sourceData.Rows[rowixd][BilledQty].ToString());
                        }

                        if (Utils.IsValidnumber(sourceData.Rows[rowixd][CondValue].ToString()))
                        {
                            dr["CondValue"] = double.Parse(sourceData.Rows[rowixd][CondValue].ToString());
                        }

                        dr["MatGroup"]     = sourceData.Rows[rowixd][MatGroup].ToString().Trim();
                        dr["MatGroupText"] = sourceData.Rows[rowixd][MatGroupText].ToString().Trim();

                        dr["UoM"] = sourceData.Rows[rowixd][UoM].ToString().Trim();



                        batable.Rows.Add(dr);
                    }
                }

                #endregion
            }

            //    Utils util = new Utils();
            string destConnString = Utils.getConnectionstr();

            //---------------fill data


            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
            {
                bulkCopy.BulkCopyTimeout      = 0;
                bulkCopy.DestinationTableName = "tblEDLP";
                // Write from the source to the destination.
                bulkCopy.ColumnMappings.Add("[Soldto]", "[Sold-to]");
                bulkCopy.ColumnMappings.Add("[SalesOrg]", "[Sales Org]");
                bulkCopy.ColumnMappings.Add("[CustName]", "[Cust Name]");
                bulkCopy.ColumnMappings.Add("[InvoiceDocNr]", "[Invoice Doc Nr]");
                bulkCopy.ColumnMappings.Add("[OutboundDelivery]", "[Outbound Delivery]");
                bulkCopy.ColumnMappings.Add("[MatNumber]", "[Mat Number]");
                bulkCopy.ColumnMappings.Add("[MatText]", "[Mat Text]");
                bulkCopy.ColumnMappings.Add("[BilledQty]", "[Billed Qty]");
                bulkCopy.ColumnMappings.Add("[CondValue]", "[Cond Value]");
                bulkCopy.ColumnMappings.Add("[MatGroup]", "[Mat Group]");
                bulkCopy.ColumnMappings.Add("[MatGroupText]", "[Mat Group Text]");
                bulkCopy.ColumnMappings.Add("[UoM]", "[UoM]");



                try
                {
                    bulkCopy.WriteToServer(batable);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    Thread.CurrentThread.Abort();
                }
            }
        }
Exemplo n.º 29
0
        private void importsexcel2(object obj)
        {
            string connection_string = Utils.getConnectionstr();
            LinqtoSQLDataContext db  = new LinqtoSQLDataContext(connection_string);
            Salesinput_ctrl      md  = new Salesinput_ctrl();
            bool kq = md.deleteedlp();
            //if (!kq)
            //{
            //    MessageBox.Show("Không xóa được bảng Edlpinput!", "Thông báo ", MessageBoxButtons.OK, MessageBoxIcon.Information);
            //}

            datainportF inf      = (datainportF)obj;
            string      filename = inf.filename;

            string connectionString = "";

            if (filename.Contains(".xlsx") || filename.Contains(".XLSX"))
            {
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";" + "Extended Properties=Excel 12.0;";
            }
            else
            {
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " + filename + ";" + "Extended Properties=Excel 8.0;";
            }

            //------
            //---------------fill data


            ExcelProvider ExcelProvide = new ExcelProvider();

            //#endregion
            System.Data.DataTable sourceData = ExcelProvide.GetDataFromExcel(filename);
            //        Sales Group Sales Group desc Sales Off Sales Office Desc

            System.Data.DataTable batable = new System.Data.DataTable();
            //  batable.Columns.Add("Soldto", typeof(double));
            batable.Columns.Add("ContractNo", typeof(string));
            batable.Columns.Add("BatchNo", typeof(double));
            batable.Columns.Add("PayType", typeof(string));
            batable.Columns.Add("PaidRequestAmt", typeof(double));
            batable.Columns.Add("PaidNote", typeof(string));
            batable.Columns.Add("PaymentDoc", typeof(string));
            //  PaymentDoc


            string username = Utils.getusername();

            batable.Columns.Add("Username", typeof(string));
            batable.Columns["Username"].DefaultValue = username;
            batable.Columns.Add("status", typeof(Boolean));
            batable.Columns["status"].DefaultValue = false;



            #region setcolum


            int PaymentDocid     = -1;
            int ContractNoid     = -1;
            int BatchNoid        = -1;
            int PayTypeid        = -1;
            int PaidRequestAmtid = -1;
            int PaidNoteid       = -1;

            //     View.Viewdatatable vi1 = new View.Viewdatatable(sourceData, "Test");

            //     vi1.ShowDialog();
            //        int headindex = -2;

            for (int rowid = 0; rowid < 3; rowid++)
            {
                // headindex = 1;
                for (int columid = 0; columid < sourceData.Columns.Count; columid++)
                {
                    string value = sourceData.Rows[rowid][columid].ToString();
                    //            MessageBox.Show(value +":"+ rowid);

                    if (value != null)
                    {
                        #region setcolum
                        if (value.Trim() == "ContractNo")
                        {
                            ContractNoid = columid;
                            //  headindex = rowid;
                        }

                        if (value.Trim() == ("BatchNo"))
                        {
                            BatchNoid = columid;
                            //   headindex = rowid;
                        }


                        if (value.Trim() == ("PayType"))
                        {
                            PayTypeid = columid;
                            //  headindex = rowid;
                        }


                        if (value.Trim() == ("PaidRequestAmt"))
                        {
                            PaidRequestAmtid = columid;
                            //  headindex = rowid;
                        }


                        if (value.Trim() == "PaidNote")
                        {
                            PaidNoteid = columid;
                            // headindex = rowid;
                        }



                        if (value.Trim() == "PaymentDoc")
                        {
                            PaymentDocid = columid;
                            // headindex = rowid;
                        }


                        #endregion
                    }
                } // colum
            }     // roww off heatder

            #endregion
            if (ContractNoid == -1)
            {
                MessageBox.Show("Please check ContractNo colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }


            if (BatchNoid == -1)
            {
                MessageBox.Show("Please check BatchNo colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (PayTypeid == -1)
            {
                MessageBox.Show("Please check PayType colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (PaidRequestAmtid == -1)
            {
                MessageBox.Show("Please check PaidRequestAmt colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (PaidNoteid == -1)
            {
                MessageBox.Show("Please check PaidNote colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (PaymentDocid == -1)
            {
                MessageBox.Show("Please check PaymentDoc colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++)
            {
                #region setvalue of massconfirm
                //   string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
                string PaidRequestAmt = sourceData.Rows[rowixd][PaidRequestAmtid].ToString();
                if (PaidRequestAmt != "" && Utils.IsValidnumber(PaidRequestAmt))
                {
                    DataRow dr = batable.NewRow();
                    dr["ContractNo"]     = sourceData.Rows[rowixd][ContractNoid].ToString().Trim();
                    dr["BatchNo"]        = double.Parse(sourceData.Rows[rowixd][BatchNoid].ToString().Trim());
                    dr["PayType"]        = sourceData.Rows[rowixd][PayTypeid].ToString().Trim();
                    dr["PaidRequestAmt"] = double.Parse(sourceData.Rows[rowixd][PaidRequestAmtid].ToString().Trim());
                    dr["PaidNote"]       = sourceData.Rows[rowixd][PaidNoteid].ToString().Trim();
                    dr["PaymentDoc"]     = sourceData.Rows[rowixd][PaymentDocid].ToString().Trim();



                    batable.Rows.Add(dr);
                }



                #endregion
            }// row



            string destConnString = Utils.getConnectionstr();

            //---------------fill data


            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
            {
                bulkCopy.BulkCopyTimeout      = 0;
                bulkCopy.DestinationTableName = "tbl_MassConfirmTemp";
                // Write from the source to the destination.
                bulkCopy.ColumnMappings.Add("ContractNo", "ContractNo");
                bulkCopy.ColumnMappings.Add("BatchNo", "BatchNo");
                bulkCopy.ColumnMappings.Add("PayType", "PayType");
                bulkCopy.ColumnMappings.Add("PaidRequestAmt", "PaidRequestAmt");
                bulkCopy.ColumnMappings.Add("PaidNote", "PaidNote");
                bulkCopy.ColumnMappings.Add("PaymentDoc", "PaymentDoc");

                bulkCopy.ColumnMappings.Add("Username", "Username");
                bulkCopy.ColumnMappings.Add("status", "status");


                try
                {
                    bulkCopy.WriteToServer(batable);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    Thread.CurrentThread.Abort();
                }
            }
        }
Exemplo n.º 30
0
        private void importsexcelSumaryletter(object obj)
        {
            //     List<tblFBL5N> fbl5n_ctrllist = new List<tblFBL5N>();
            //     fbl5n_ctrl md = new fbl5n_ctrl();

            //     bool kq = md.deleteFbl5n();

            datauploadSumary inf = (datauploadSumary)obj;

            string filename = inf.filename;

            DateTime todate    = inf.todate;
            DateTime returdate = inf.returdate;


            //   ExcelProvider ExcelProvide = new ExcelProvider();
            //#endregion
            System.Data.DataTable sourceData = ExcelProvider.GetDataFromExcel(filename);

            System.Data.DataTable batable = new System.Data.DataTable();

            batable.Columns.Add("GroupLetter", typeof(string));
            batable.Columns.Add("Nameletter", typeof(string));

            batable.Columns.Add("Tongtiennonuoc", typeof(double));
            batable.Columns.Add("Tongtienthanhtoan", typeof(double));
            batable.Columns.Add("Tongno", typeof(double));

            //     batable.Columns.Add("Stt", typeof(string));
            batable.Columns.Add("toDate", typeof(DateTime));
            batable.Columns.Add("returndate", typeof(DateTime));



            int GroupLetterid       = -1;
            int Nameletterid        = -1;
            int Tongtiennonuocid    = -1;
            int Tongtienthanhtoanid = -1;
            int Tongnoid            = -1;



            for (int rowid = 0; rowid < 3; rowid++)
            {
                // headindex = 1;
                for (int columid = 0; columid < sourceData.Columns.Count; columid++)
                {
                    #region
                    //
                    //
                    //


                    string value = sourceData.Rows[rowid][columid].ToString();
                    //            MessageBox.Show(value +":"+ rowid);

                    if (value != null && value != "")
                    {
                        //    #region setcolum
                        if (value.Trim() == ("Group Letter"))
                        {
                            GroupLetterid = columid;
                            //  headindex = rowid;
                        }

                        if (value.Trim() == ("Name letter"))
                        {
                            Nameletterid = columid;
                            //    headindex = 0;
                        }


                        if (value.Trim() == ("1.Tổng Tiền Nợ Nước"))
                        {
                            Tongtiennonuocid = columid;
                            //   headindex = 0;
                        }


                        if (value.Trim() == ("2.Tổng Tiền Thanh Toán"))
                        {
                            Tongtienthanhtoanid = columid;//
                        }
                        if (value.Trim() == ("Tổng Tiền Công Nợ (1)+(2)"))
                        {
                            Tongnoid = columid;//
                        }
                    }
                    #endregion
                } // colum
            }     // roww off heatder


            if (GroupLetterid == -1)
            {
                MessageBox.Show("Dữ liệu thiếu cột Group Letter", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (Nameletterid == -1)
            {
                MessageBox.Show("Dữ liệu thiếu cột Name letter", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (Tongtiennonuocid == -1)
            {
                MessageBox.Show("Dữ liệu thiếu cột 1.Tổng Tiền Nợ Nước  ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (Tongtienthanhtoanid == -1)
            {
                MessageBox.Show("Dữ liệu thiếu cột 2.Tổng Tiền Thanh Toán ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (Tongnoid == -1)
            {
                MessageBox.Show("Dữ liệu thiếu cột Tổng Tiền Công Nợ (1)+(2) ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            //     int stt = 0;

            for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++)
            {
                #region


                //   string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
                string GroupLetter  = sourceData.Rows[rowixd][GroupLetterid].ToString();
                string Nameletter   = sourceData.Rows[rowixd][Nameletterid].ToString();
                string tongtiennuoc = sourceData.Rows[rowixd][Tongtiennonuocid].ToString();


                if (GroupLetter != "" && Nameletter != "" && Utils.IsValidnumber(tongtiennuoc))
                {
                    //     stt = stt + 1;

                    DataRow dr = batable.NewRow();


                    dr["Tongtiennonuoc"]    = double.Parse(sourceData.Rows[rowixd][Tongtiennonuocid].ToString());    //.Trim();
                    dr["Tongtienthanhtoan"] = double.Parse(sourceData.Rows[rowixd][Tongtienthanhtoanid].ToString()); //.Trim();
                    dr["Tongno"]            = double.Parse(sourceData.Rows[rowixd][Tongnoid].ToString());            //.Trim();

                    dr["GroupLetter"] = sourceData.Rows[rowixd][GroupLetterid].ToString().Trim();

                    dr["Nameletter"] = sourceData.Rows[rowixd][Nameletterid].ToString().Trim();



                    //      dr["Stt"] = stt;
                    dr["toDate"]     = todate;
                    dr["returndate"] = returdate;


                    batable.Rows.Add(dr);
                }

                #endregion
            }

            //    Utils util = new Utils();
            string destConnString = Utils.getConnectionstr();

            //---------------fill data


            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
            {
                bulkCopy.DestinationTableName = "tblNKAArletterRpts";
                // Write from the source to the destination.
                bulkCopy.ColumnMappings.Add("GroupLetter", "[GroupLetter]");
                bulkCopy.ColumnMappings.Add("Nameletter", "[GroupName]");
                bulkCopy.ColumnMappings.Add("Tongtiennonuoc", "[tongnonuoc]");
                bulkCopy.ColumnMappings.Add("Tongtienthanhtoan", "[tongtienthanhtoan]");
                bulkCopy.ColumnMappings.Add("Tongno", "[tongno]");
                //     bulkCopy.ColumnMappings.Add("Stt", "[stt]");

                bulkCopy.ColumnMappings.Add("toDate", "[toDate]");
                bulkCopy.ColumnMappings.Add("returndate", "[returndate]");



                try
                {
                    bulkCopy.WriteToServer(batable);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    Thread.CurrentThread.Abort();
                }
            }
        }
Exemplo n.º 31
0
        public static bool Import(string filePath, string szTemplateValue)
        {
            try
            {
                GlobalMethod.log.Info("开始导入");
                EnterRepository.GetRepositoryEnter().UsersRepository.db.Configuration.AutoDetectChangesEnabled = false;
                EnterRepository.GetRepositoryEnter().UsersRepository.db.Configuration.ValidateOnSaveEnabled = false;
                Hashtable htUsers  = new Hashtable();
                var       lstUsers = EnterRepository.GetRepositoryEnter().UsersRepository.LoadEntities().ToList();
                foreach (var item in lstUsers)
                {
                    if (!htUsers.ContainsKey(item.Tel))
                    {
                        htUsers.Add(item.Tel, item.ID);
                    }
                }
                var       lstEmployee = EnterRepository.GetRepositoryEnter().EmployeeRepository.LoadEntities().ToList();
                Hashtable htEmployee  = new Hashtable();
                foreach (var item in lstEmployee)
                {
                    if (htEmployee.Contains(item.Name))
                    {
                        continue;
                    }
                    htEmployee.Add(item.Name, item.ID);
                }
                string[]      values        = szTemplateValue.Split(',');
                ExcelProvider excelProvider = ExcelProvider.Create(filePath, "Sheet1");
                int           passCount     = 0;
                int           importCount   = 0;
                foreach (ExcelRow row in excelProvider)
                {
                    int          ID   = 0;
                    Models.Users user = new Models.Users();
                    for (int index = 0; index < values.Length; index++)
                    {
                        switch (values[index])
                        {
                        case "网报序号":
                            if (!string.IsNullOrEmpty(row.GetString("网报序号")))
                            {
                                user.ID = int.Parse(row.GetString(values[index]));
                            }
                            break;

                        case "多退少补":
                            user.MoneyBack = row.GetString(values[index]);
                            break;

                        case "酒店房价":
                            user.HotelExpense = row.GetString(values[index]);
                            break;

                        case "酒店":
                            user.Hotel = row.GetString(values[index]);
                            break;

                        case "房号":
                            user.Room = row.GetString(values[index]);
                            break;

                        case "已交款额":
                            user.PayMoney = row.GetString(values[index]);
                            break;

                        case "备注":
                            user.Baks = row.GetString(values[index]);
                            break;

                        case "联系方式":
                            user.Tel = row.GetString(values[index]);
                            break;

                        case "网报密码":
                            user.Pwd = row.GetString(values[index]);
                            break;

                        case "所在学校":
                            user.School = row.GetString(values[index]);
                            break;

                        case "性别":
                            user.Gender = row.GetString(values[index]);
                            break;

                        case "报名次序":
                            string sequences = row.GetString(values[index]);
                            if (string.IsNullOrEmpty(sequences))
                            {
                                sequences = "0";
                            }
                            user.Sequences = int.Parse(sequences);
                            break;

                        case "业务员":
                            user.EmployeeName = row.GetString(values[index]);
                            break;

                        case "意向同住人":
                            user.ExceptRoomie = row.GetString(values[index]);
                            break;

                        case "报考类型":
                            user.Template = row.GetString(values[index]);
                            break;

                        case "收缴余款所在地":
                            user.PayPlace = row.GetString(values[index]);
                            break;

                        case "所报学校":
                            user.ExamSchool = row.GetString(values[index]);
                            break;

                        case "姓名":
                            user.Name = row.GetString(values[index]);
                            break;

                        case "提交考点":
                            user.ExamPlace = row.GetString(values[index]);
                            break;

                        default:
                            break;
                        }
                    }
                    if (string.IsNullOrEmpty(user.Tel))
                    {
                        passCount++;
                        GlobalMethod.log.Warn(string.Format("导入excel,考生{0}号码为空,跳过", user.Name));
                        continue;
                    }
                    object szEmployeeID = htEmployee[user.EmployeeName];
                    if (szEmployeeID == null)
                    {
                        passCount++;
                        GlobalMethod.log.Warn(string.Format("导入excel,考生{0}的业务员姓名未能在系统内找到,跳过", user.Name));
                        continue;
                    }
                    user.EmployeeID = szEmployeeID == null ? 0 : int.Parse(szEmployeeID.ToString());
                    if (!htUsers.ContainsKey(user.Tel))
                    {
                        user.CreateTime = DateTime.Now;
                        EnterRepository.GetRepositoryEnter().UsersRepository.AddEntity(user);
                    }
                    else
                    {
                        user.ID = int.Parse(htUsers[user.Tel].ToString());
                        EnterRepository.GetRepositoryEnter().UsersRepository.EditEntity(user, new string[] { "EmployeeID", "Name", "School", "ExamSchool", "Sequences", "Tel", "Baks", "Pwd", "PayMoney", "ExamPlace", "Room", "Hotel", "HotelExpense", "MoneyBack", "Gender", "Template", "PayPlace", "ExceptRoomie", "Status" });
                    }
                }
                importCount = EnterRepository.GetRepositoryEnter().SaveChange();
                GlobalMethod.log.Info(string.Format("本次导入成功,共导入{0}考生,应数据异常跳过{1}个"
                                                    , importCount.ToString()
                                                    , passCount.ToString()));
                return(true);
            }
            catch (Exception ex)
            {
                GlobalMethod.log.Error(ex);
                throw;
            }
            finally
            {
                EnterRepository.GetRepositoryEnter().UsersRepository.db.Configuration.AutoDetectChangesEnabled = true;
                EnterRepository.GetRepositoryEnter().UsersRepository.db.Configuration.ValidateOnSaveEnabled = true;
            }
        }
        private void importFreglassessexcel(object obj)
        {
            string connection_string = Utils.getConnectionstr();

            LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string);

            fREEGALSSES_CTRL Rm = new fREEGALSSES_CTRL();

            string username = Utils.getusername();

            bool kq = Rm.deleteallFreglassesBEgin();

            datainportF inf = (datainportF)obj;

            string filename = inf.filename;

            //OleDbCommand command = new OleDbCommand(
            //              @"SELECT [CUSTOMER], [SALORG],[COLAMT],[PERNO]
            //                         FROM [Sheet1$]
            //                         WHERE ( [CUSTOMER] is not null ) ", conn);


            //    ExcelProvider ExcelProvide = new ExcelProvider();
            //#endregion
            System.Data.DataTable sourceData = ExcelProvider.GetDataFromExcel(filename);

            System.Data.DataTable batable = new System.Data.DataTable();

            batable.Columns.Add("CUSTOMER", typeof(double));
            batable.Columns.Add("SALORG", typeof(string));
            //  batable.Columns.Add("PERNO", typeof(string));
            batable.Columns.Add("Freeglass3years", typeof(int));
            batable.Columns.Add("Freeglass3phantram", typeof(int));
            batable.Columns.Add("AmountFreeglassesValue", typeof(double));
            batable.Columns.Add("TypeDoc", typeof(string));
            batable.Columns.Add("userupdate", typeof(string));
            batable.Columns.Add("Posting_Date", typeof(DateTime));
            //        CUSTOMER SALORG  Freeglass3years Freeglass3phantram  AmountFreeglassesValue



            int CUSTOMERid               = -1;
            int SALORGid                 = -1;
            int Freeglass3yearsid        = -1;
            int Freeglass3phantramid     = -1;
            int AmountFreeglassesValueid = -1;

            int rowseet = sourceData.Rows.Count;

            if (rowseet > 5)
            {
                rowseet = 5;
            }
            for (int rowid = 0; rowid < rowseet; rowid++)
            {
                // headindex = 1;
                for (int columid = 0; columid < sourceData.Columns.Count; columid++)
                {
                    #region
                    string value = sourceData.Rows[rowid][columid].ToString();
                    //            MessageBox.Show(value +":"+ rowid);

                    if (value != null && value != "")
                    {
                        //    #region setcolum
                        if (value.Trim() == ("CUSTOMER"))
                        {
                            CUSTOMERid = columid;
                            //  headindex = rowid;
                        }

                        if (value.Trim() == ("SALORG"))
                        {
                            SALORGid = columid;
                            //    headindex = 0;
                        }


                        if (value.Trim() == ("Freeglass3years"))
                        {
                            Freeglass3yearsid = columid;
                            //   headindex = 0;
                        }


                        if (value.Trim() == ("Freeglass3phantram"))
                        {
                            Freeglass3phantramid = columid;
                        }

                        if (value.Trim() == ("AmountFreeglassesValue"))
                        {
                            AmountFreeglassesValueid = columid;
                        }
                    }
                    #endregion
                } // colum
            }     // roww off heatder


            if (CUSTOMERid == -1)
            {
                MessageBox.Show("Dữ liệu thiếu cột CUSTOMER", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (SALORGid == -1)
            {
                MessageBox.Show("Dữ liệu thiếu cột SALORG", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (Freeglass3yearsid == -1)
            {
                MessageBox.Show("Dữ liệu thiếu cột Freeglass3years", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (Freeglass3phantramid == -1)
            {
                MessageBox.Show("Dữ liệu thiếu cột Freeglass3phantram", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (AmountFreeglassesValueid == -1)
            {
                MessageBox.Show("Dữ liệu thiếu cột AmountFreeglassesValue", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }



            for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++)
            {
                #region


                //   string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
                string CUSTOMERvl = sourceData.Rows[rowixd][CUSTOMERid].ToString();
                if (CUSTOMERvl != "" && Utils.IsValidnumber(CUSTOMERvl))
                {
                    if (double.Parse(CUSTOMERvl) > 0)
                    {
                        DataRow dr = batable.NewRow();
                        dr["CUSTOMER"] = double.Parse(sourceData.Rows[rowixd][CUSTOMERid].ToString());//.Trim();
                        dr["SALORG"]   = sourceData.Rows[rowixd][SALORGid].ToString().Trim();

                        if (sourceData.Rows[rowixd][Freeglass3yearsid].ToString() != "")
                        {
                            dr["Freeglass3years"] = int.Parse(sourceData.Rows[rowixd][Freeglass3yearsid].ToString().Trim());
                        }
                        else
                        {
                            dr["Freeglass3years"] = 0;
                        }

                        //----------------
                        if (sourceData.Rows[rowixd][Freeglass3phantramid].ToString() != "")
                        {
                            dr["Freeglass3phantram"] = int.Parse(sourceData.Rows[rowixd][Freeglass3phantramid].ToString().Trim());
                        }
                        else
                        {
                            dr["Freeglass3phantram"] = 0;
                        }

                        ///----

                        if (sourceData.Rows[rowixd][Freeglass3yearsid].ToString() != "")
                        {
                            dr["AmountFreeglassesValue"] = int.Parse(sourceData.Rows[rowixd][AmountFreeglassesValueid].ToString().Trim());
                        }
                        else
                        {
                            dr["AmountFreeglassesValue"] = 0;
                        }



                        dr["TypeDoc"] = "Begin";//.Trim();//sourceData.Rows[rowixd][COLAMTid].ToString().Trim();

                        dr["userupdate"]   = username;
                        dr["Posting_Date"] = DateTime.Today;



                        batable.Rows.Add(dr);
                    }
                }

                #endregion

                //    Utils util = new Utils();


                //---------------fill data
            }
            string destConnString = Utils.getConnectionstr();
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
            {
                bulkCopy.DestinationTableName = "tblFBL5NNewCol3year";
                // Write from the source to the destination.
                bulkCopy.ColumnMappings.Add("CUSTOMER", "[Customer code]");
                bulkCopy.ColumnMappings.Add("SALORG", "[Region]");
                bulkCopy.ColumnMappings.Add("Freeglass3years", "[Freeglass3years]");
                bulkCopy.ColumnMappings.Add("Freeglass3phantram", "[Freeglass3phantram]");
                bulkCopy.ColumnMappings.Add("AmountFreeglassesValue", "[AmountFreeglassesValue]");
                bulkCopy.ColumnMappings.Add("TypeDoc", "TypeDoc");
                bulkCopy.ColumnMappings.Add("userupdate", "[userupdate]");

                bulkCopy.ColumnMappings.Add("Posting_Date", "[Posting Date]");

                try
                {
                    bulkCopy.WriteToServer(batable);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    Thread.CurrentThread.Abort();
                }
            }
        }
 /// <summary>
 /// Initializes a new instance of the <see cref="ReportToExcelController" /> class.
 /// </summary>
 /// <param name="manager">The manager.</param>
 public ReportToExcelController(IReportManager manager)
 {
     this.reportManager = manager;
     this.excelProvider = new ExcelProvider();
     this.pdfGenerator = new PDFGenerator();
 }