//private void importExcel(string dir, string file)
//{
// IFormatProvider culture = new System.Globalization.CultureInfo("vi-VN", true);
// string Excelstrcon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dir + file + @";Extended Properties=""Excel 8.0;HDR=YES;""";
// string SQLstr = "SELECT * FROM [Sheet1$]";
// OleDbConnection ExcelCon = new OleDbConnection(Excelstrcon);
// ExcelCon.Open();
// OleDbCommand dbCommand = new OleDbCommand(SQLstr, ExcelCon);
// OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);
// DataTable dTable = new DataTable();
// try
// {
// dataAdapter.Fill(dTable);
// int idx = 1;
// DateTime NgayChet = DateTime.MinValue;
// DateTime currNgay = DateTime.MinValue;
// string StrIDCaSau = "";
// string StrDaBung = "";
// string StrDaPhanLoai = "";
// string StrDau = "";
// string LyDo = "";
// foreach (DataRow r in dTable.Rows)
// {
// NgayChet = DateTime.Parse(r["Ngay"].ToString() + "/" + r["Thang"].ToString() + "/" + r["Nam"].ToString() + " 22:00:00", culture);
// if (NgayChet != currNgay)
// {
// if (StrIDCaSau != "")
// {
// string fail = csCont.CaSauChet(StrIDCaSau, currNgay, UserId, LyDo, StrDaBung, StrDaPhanLoai, StrDau);
// if (fail != "")
// {
// int sidx = idx - 1;
// lblMessage.Text += "<br/>Dòng " + sidx.ToString() + ":" + fail;
// }
// StrIDCaSau = "";
// StrDaBung = "";
// StrDaPhanLoai = "";
// StrDau = "";
// }
// currNgay = NgayChet;
// }
// string s = csCont.CaSau_Chet_GetCaByLoaiCaByChuongAtDate(Convert.ToInt32(r["LoaiCa"]), r["Chuong"].ToString(), Convert.ToInt32(r["SL"]), NgayChet);
// string sTemp = s.Replace("@", "");
// if ((s.Length - sTemp.Length) / 2 != Convert.ToInt32(r["SL"]))
// {
// lblMessage.Text += "<br/>Dòng " + idx.ToString() + " không import do số lượng cá không đủ.";
// idx++;
// continue;
// }
// StrIDCaSau += s;
// if (r["SanPham"] != DBNull.Value && r["SanPham"].ToString() != "")
// {
// if(r["SanPham"].ToString() == "-1")
// {
// for(int i = 0; i<Convert.ToInt32(r["SL"]); i++)
// {
// StrDaBung += "@0@";
// StrDaPhanLoai += "@4@";
// StrDau += "@1@";
// }
// }
// else
// {
// for (int i = 0; i < Convert.ToInt32(r["SL"]); i++)
// {
// StrDaBung += "@" + r["SanPham"].ToString() + "@";
// StrDaPhanLoai += "@4@";
// StrDau += "@0@";
// }
// }
// }
// else
// {
// for (int i = 0; i < Convert.ToInt32(r["SL"]); i++)
// {
// StrDaBung += "@0@";
// StrDaPhanLoai += "@4@";
// StrDau += "@0@";
// }
// }
// idx++;
// }
// if (StrIDCaSau != "")
// {
// string fail = csCont.CaSauChet(StrIDCaSau, currNgay, UserId, LyDo, StrDaBung, StrDaPhanLoai, StrDau);
// if (fail != "")
// {
// int sidx = idx - 1;
// lblMessage.Text += "<br/>Dòng " + sidx.ToString() + ":" + fail;
// }
// }
// // dispose used objects
// dTable.Dispose();
// dataAdapter.Dispose();
// dbCommand.Dispose();
// ExcelCon.Close();
// ExcelCon.Dispose();
// lblMessage.Text += "<br/>Đã import xong!";
// }
// catch (Exception ex)
// {
// // dispose used objects
// dTable.Dispose();
// dataAdapter.Dispose();
// dbCommand.Dispose();
// ExcelCon.Close();
// ExcelCon.Dispose();
// Response.Write(ex.ToString());
// }
//}
private void importExcel(string dir, string file)
{
IFormatProvider culture = new System.Globalization.CultureInfo("vi-VN", true);
string Excelstrcon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dir + file + @";Extended Properties=""Excel 8.0;HDR=YES;""";
string SQLstr = "SELECT * FROM [Sheet1$]";
OleDbConnection ExcelCon = new OleDbConnection(Excelstrcon);
ExcelCon.Open();
OleDbCommand dbCommand = new OleDbCommand(SQLstr, ExcelCon);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);
DataTable dTable = new DataTable();
try
{
dataAdapter.Fill(dTable);
int idx = 1;
DateTime NgayChet = DateTime.MinValue;
string StrIDCaSau = "";
string StrDaBung = "";
string StrDaPhanLoai = "";
string StrDau = "";
string StrPPM = "";
string StrLDC = "";
string StrKL = "";
string LyDo = "";
int hh = 11;
int mm = 10;
int ss = 10;
string TenChuong = "";
int So = 0;
string BienBan = "";
foreach (DataRow r in dTable.Rows)
{
//NgayChet = DateTime.Parse(r["Ngay"].ToString() + "/" + r["Thang"].ToString() + "/" + r["Nam"].ToString() + " 22:00:00", culture);
NgayChet = DateTime.Parse(r["Ngay"].ToString() + "/" + r["Thang"].ToString() + "/" + r["Nam"].ToString() + " " + hh.ToString() + ":" + mm.ToString() + ":" + ss.ToString(), culture);
ss++; if (ss > 59)
{
ss = 10; mm++; if (mm > 59)
{
mm = 10; hh++;
}
}
csCont.ParseChuong(r["Chuong"].ToString().Trim(), out TenChuong, out So);
string s = csCont.CaSau_Chet_GetCaByLoaiCaByChuongAtDate(Convert.ToInt32(r["LoaiCa"]), TenChuong, So, Convert.ToInt32(r["SL"]), NgayChet, Convert.ToBoolean(r["Giong"]));
string sTemp = s.Replace("@", "");
if ((s.Length - sTemp.Length) / 2 != Convert.ToInt32(r["SL"]))
{
lblMessage.Text += "<br/>Dòng " + idx.ToString() + " không import do số lượng cá không đủ.";
idx++;
continue;
}
if (r["BienBan"] != DBNull.Value)
{
BienBan = r["BienBan"].ToString();
}
else
{
BienBan = "";
}
StrIDCaSau = s;
if (r["SanPham"] != DBNull.Value && r["SanPham"].ToString() != "")
{
if (r["SanPham"].ToString() == "-1")
{
for (int i = 0; i < Convert.ToInt32(r["SL"]); i++)
{
StrDaBung += "@0@";
StrDaPhanLoai += "@4@";
StrDau += "@1@";
StrPPM += "@CL@";
StrLDC += "@" + r["LyDoChet"].ToString() + "@";
StrKL += "@" + r["KhoiLuong"].ToString().Replace(",", ".") + "@";
}
}
else
{
for (int i = 0; i < Convert.ToInt32(r["SL"]); i++)
{
StrDaBung += "@" + r["SanPham"].ToString() + "@";
StrDaPhanLoai += "@" + r["PhanLoai"].ToString() + "@";
StrDau += "@0@";
StrPPM += "@" + r["PPM"].ToString() + "@";
StrLDC += "@" + r["LyDoChet"].ToString() + "@";
StrKL += "@" + r["KhoiLuong"].ToString().Replace(",", ".") + "@";
}
}
}
else
{
for (int i = 0; i < Convert.ToInt32(r["SL"]); i++)
{
StrDaBung += "@0@";
StrDaPhanLoai += "@4@";
StrDau += "@0@";
StrPPM += "@CL@";
StrLDC += "@" + r["LyDoChet"].ToString() + "@";
StrKL += "@" + r["KhoiLuong"].ToString().Replace(",", ".") + "@";
}
}
if (StrIDCaSau != "")
{
string fail = csCont.CaSauChet(StrIDCaSau, NgayChet, UserId, StrDaBung, StrDaPhanLoai, StrDau, StrPPM, StrLDC, StrKL, BienBan, "-1", "");
if (fail != "")
{
lblMessage.Text += "<br/>Dòng " + idx.ToString() + ":" + fail;
}
StrIDCaSau = "";
StrDaBung = "";
StrDaPhanLoai = "";
StrDau = "";
StrPPM = "";
StrLDC = "";
StrKL = "";
}
else
{
StrDaBung = "";
StrDaPhanLoai = "";
StrDau = "";
StrPPM = "";
StrLDC = "";
StrKL = "";
}
idx++;
}
// dispose used objects
dTable.Dispose();
dataAdapter.Dispose();
dbCommand.Dispose();
ExcelCon.Close();
ExcelCon.Dispose();
lblMessage.Text += "<br/>Đã import xong!";
}
catch (Exception ex)
{
// dispose used objects
dTable.Dispose();
dataAdapter.Dispose();
dbCommand.Dispose();
ExcelCon.Close();
ExcelCon.Dispose();
Response.Write(ex.ToString());
}
}
