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);
}
}
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);
}
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();
}
}
}
}