Exemple #1
0
        private void button1_Click(object sender, EventArgs e)
        {
            dataGridView1.Columns.Clear();
            using (OpenFileDialog ofd = new OpenFileDialog()
            {
                Filter = "Exel WorkBook|*", ValidateNames = true
            })
            {
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    FileStream             fs     = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read);
                    Excel.IExcelDataReader reader = Excel.ExcelReaderFactory.CreateOpenXmlReader(fs);
                    reader.IsFirstRowAsColumnNames = true;
                    DataSet result = reader.AsDataSet();
                    //result.Fill(test);

                    dataGridView1.DataSource = result.Tables[0];
                    reader.Close();
                }
            }

            lblName.Visible     = true;
            txtName.Visible     = true;
            btnImportDB.Visible = true;
        }
        public ContentResult uploadFile(String type, String data)
        {
            try
            {
                DataTable          dt   = new DataTable();
                HttpPostedFileBase file = Request.Files[0];
                if (file.ContentLength > 0)
                {
                    Excel.IExcelDataReader excelReader = Excel.ExcelReaderFactory.CreateOpenXmlReader(file.InputStream);
                    DataSet result = excelReader.AsDataSet();
                    excelReader.IsFirstRowAsColumnNames = true;
                    dt = result.Tables[0];
                }
                var list = JsonConvert.SerializeObject(dt,
                                                       Formatting.None,
                                                       new JsonSerializerSettings()
                {
                    ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore
                });

                return(Content(list, "application/json"));
            }
            catch (Exception e)
            {
                e.ToString();
                return(null);
            }
        }
Exemple #3
0
        private static DataTable ExcelToDataTable(string fileName)
        {
            //open file and returns as Stream
            FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read);

            //Createopenxmlreader via ExcelReaderFactory
            Excel.IExcelDataReader excelReader = Excel.ExcelReaderFactory.CreateOpenXmlReader(stream); //.xlsx
            //Set the First Row as Column Name
            excelReader.IsFirstRowAsColumnNames = true;
            //Return as DataSet
            DataSet result = excelReader.AsDataSet();
            //Get all the Tables
            DataTableCollection table = result.Tables;
            //Store it in DataTable
            DataTable resultTable = table["Sheet1"];

            //return
            return(resultTable);
        }
Exemple #4
0
        private void btnExcel_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog ofd = new OpenFileDialog()
            {
                Filter = "Exel WorkBook|*", ValidateNames = true
            })
            {
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    dataGridView1.Rows.Clear();
                    gridView.Clear();
                    if (con.State != ConnectionState.Open)
                    {
                        con.Open();
                    }
                    Sum = 0;
                    FileStream             fs     = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read);
                    Excel.IExcelDataReader reader = Excel.ExcelReaderFactory.CreateOpenXmlReader(fs);
                    reader.IsFirstRowAsColumnNames = true;
                    DataSet result = reader.AsDataSet();
                    //result.Fill(test);

                    foreach (DataRow myDataRow in result.Tables[0].Rows)
                    {
                        string mHang          = myDataRow[1].ToString();
                        double giaDoLoad      = 0;
                        int    khoiLuongLoad  = 0;
                        int    soLuongLoad    = 0;
                        double ck1Load        = 0;
                        double ck2Load        = 0;
                        double ck3Load        = 0;
                        double giaNetNhapLoad = 0;
                        double tienHangLoad   = 0;
                        double tienKMLoad     = 0;
                        int    TangBaoLoad    = 0;
                        if (myDataRow[2].ToString() == "")
                        {
                            break;
                        }
                        else
                        {
                            soLuongLoad = Int32.Parse(myDataRow[2].ToString());
                        }
                        if (myDataRow[3].ToString() != "")
                        {
                            ck1Load = double.Parse(myDataRow[3].ToString());
                        }
                        if (myDataRow[4].ToString() != "")
                        {
                            ck2Load = double.Parse(myDataRow[4].ToString());
                        }
                        if (myDataRow[5].ToString() != "")
                        {
                            ck3Load = double.Parse(myDataRow[5].ToString());
                        }
                        if (myDataRow[6].ToString() != "")
                        {
                            TangBaoLoad = Int32.Parse(myDataRow[6].ToString());
                        }
                        string donviLoad   = "";
                        string firstColum  = mHang;
                        string secondColum = soLuongLoad.ToString();
                        string threeColum  = "";
                        string fourColum   = "";
                        string fiveColum   = "";
                        string sixColum    = "";

                        SqlCommand    cmd = new SqlCommand("SELECT * FROM sanpham WHERE id_banggia = '" + id_bangGia + "' AND mahang = '" + mHang + "' ", con);
                        SqlDataReader dr;
                        dr = cmd.ExecuteReader();
                        while (dr.Read())
                        {
                            giaDoLoad     = double.Parse(dr["giado"].ToString());
                            khoiLuongLoad = Int32.Parse(dr["khoiluong"].ToString());
                            donviLoad     = dr["donvi"].ToString();
                        }
                        dr.Close();
                        giaNetNhapLoad = giaDoLoad * (100 - ck1Load) / 100 - (ck2Load + ck3Load) * khoiLuongLoad;
                        tienKMLoad     = (giaDoLoad - giaNetNhapLoad) * soLuongLoad;
                        tienHangLoad   = giaNetNhapLoad * soLuongLoad;
                        Sum           += tienHangLoad;
                        NoConLai       = Sum;
                        threeColum     = donviLoad.ToString();
                        fourColum      = string.Format("{0:n0}", tienKMLoad);
                        fiveColum      = string.Format("{0:n0}", giaNetNhapLoad);
                        sixColum       = string.Format("{0:n0}", tienHangLoad);
                        string[] rowAdd = { firstColum, secondColum, threeColum, fourColum, fiveColum, sixColum };
                        gridView.Add(new ListItem()
                        {
                            idHang   = firstColum,
                            soLuong  = secondColum,
                            kMai     = fourColum,
                            giaNet   = fiveColum,
                            tienHang = sixColum
                        });
                        dataGridView1.Rows.Add(rowAdd);
                        if (TangBaoLoad != 0)
                        {
                            int tangbao = (soLuongLoad / TangBaoLoad);
                            if (tangbao >= 1)
                            {
                                firstColum  = mHang;
                                secondColum = tangbao.ToString();
                                threeColum  = donviLoad.ToString();
                                fourColum   = "Tặng Bao";
                                fiveColum   = string.Format("{0:n0}", giaNetNhapLoad);
                                sixColum    = "0";

                                //string[] rowAdd = { firstColum, secondColum, threeColum, fourColum, fiveColum, sixColum, sevenColum };
                                string[] rowAdd2 = { firstColum, secondColum, threeColum, fourColum, fiveColum, sixColum };
                                gridView.Add(new ListItem()
                                {
                                    idHang   = firstColum,
                                    soLuong  = secondColum,
                                    kMai     = fourColum,
                                    giaNet   = fiveColum,
                                    tienHang = sixColum
                                });
                                dataGridView1.Rows.Add(rowAdd2);
                            }
                        }
                    }
                    txtSum.Text = string.Format("{0:n0}", Sum);
                    reader.Close();
                    if (con.State == ConnectionState.Open)
                    {
                        con.Close();
                    }
                }
            }
        }