private void importExcelThuoc(string dir, string file)
{
DateTime NgayAn = DateTime.MinValue;
decimal KhoiLuong = 0;
int SoLuongCa = 0;
int SoLuongTT = 0;
string columnName = "";
decimal value = 0;
int idVatTu = 0;
int SoCaAn = 0;
string StrChuong = "";
string StrKL = "";
decimal kl = 0;
int LoaiCa = 0;
string[] aChuongOnly = null;
string[] aChuongExcept = null;
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;""";
int numSheet = int.Parse(txtNumSheet.Text);
for (int num = 1; num <= numSheet; num++)
{
string SQLstr = "SELECT * FROM [Sheet" + num.ToString() + "$]";
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 = 2;
ArrayList colNames = new ArrayList();
for (int i = 4; i < dTable.Columns.Count; i++)
{
columnName = dTable.Columns[i].ColumnName;
colNames.Add(columnName);
if (columnName.StartsWith("Column"))
{
int cot = i + 1;
lblMessage.Text += "<br/>Cột thứ " + cot.ToString() + " sheet " + num.ToString() + " có tên không hợp lệ (" + columnName + ")";
}
}
foreach (DataRow r in dTable.Rows)
{
DateTime NgayChoAn = DateTime.Parse(r["Ngay"].ToString() + "/" + r["Thang"].ToString() + "/" + r["Nam"].ToString() + " 23:00:00", culture);
DateTime NgayChoAnOutput = DateTime.MinValue;
int IDCaSauAn = csCont.CaSauAn_GetCaSauAnByNgay(NgayChoAn, out NgayChoAnOutput);
for (int i = 4; i < dTable.Columns.Count; i++)
{
columnName = colNames[i - 4].ToString();
if (columnName.StartsWith("LoaiCa"))
{
LoaiCa = Convert.ToInt32(r[i]);
aChuongOnly = null;
aChuongExcept = null;
}
else if (columnName.StartsWith("Chuong-Only"))
{
string sChuongOnly = r[i].ToString();
sChuongOnly = sChuongOnly.Substring(1, sChuongOnly.Length - 2);
aChuongOnly = sChuongOnly.Split(new string[] { "!!" }, StringSplitOptions.RemoveEmptyEntries);
}
else if (columnName.StartsWith("Chuong-Except"))
{
string sChuongExcept = r[i].ToString();
sChuongExcept = sChuongExcept.Substring(1, sChuongExcept.Length - 2);
aChuongExcept = sChuongExcept.Split(new string[] { "!!" }, StringSplitOptions.RemoveEmptyEntries);
}
else if (columnName.StartsWith("Column"))
{
}
else
{
value = 0;
if (r[i] != DBNull.Value) value = Convert.ToDecimal(r[i]);
if (value != 0)
{
idVatTu = int.Parse(columnName.Substring(columnName.IndexOf('(') + 1, columnName.IndexOf(')') - columnName.IndexOf('(') - 1));
if (IDCaSauAn == 0) IDCaSauAn = csCont.CaSauAn_ThemMoi(NgayChoAn, UserId, "");
DataTable tblChuong = csCont.CaSauAn_GetChuongByThuocByLoaiCa(IDCaSauAn, idVatTu, LoaiCa, out KhoiLuong, out SoLuongCa, out SoLuongTT, out NgayAn);
if (aChuongOnly != null)
{
for (int j = tblChuong.Rows.Count - 1; j > -1; j--)
{
int k = 0;
for (k = 0; k < aChuongOnly.Length; k++)
{
if (tblChuong.Rows[j]["Chuong"].ToString().Contains(aChuongOnly[k]))
{
break;
}
}
if (k == aChuongOnly.Length) tblChuong.Rows.RemoveAt(j);
}
}
if (aChuongExcept != null)
{
for (int j = tblChuong.Rows.Count - 1; j > -1; j--)
{
for (int k = 0; k < aChuongExcept.Length; k++)
{
if (tblChuong.Rows[j]["Chuong"].ToString().Contains(aChuongExcept[k]))
{
tblChuong.Rows.RemoveAt(j);
break;
}
}
}
}
SoCaAn = 0;
StrChuong = "";
StrKL = "";
foreach (DataRow rC in tblChuong.Rows)
{
SoCaAn += Convert.ToInt32(rC["SoLuong"]);
}
foreach (DataRow rC in tblChuong.Rows)
{
StrChuong += "@" + rC["IDChuong"].ToString() + "@";
kl = value * Convert.ToDecimal(rC["SoLuong"]) / Convert.ToDecimal(SoCaAn);
StrKL += "@" + String.Format("{0:0.#####}", kl).Replace(',', '.') + "@";
}
csCont.CaSauAn_InsertUpdateThuoc_UpdateChuong(IDCaSauAn, idVatTu, LoaiCa, SoCaAn, StrChuong, StrKL);
}
}
}
idx++;
}
// dispose used objects
dTable.Dispose();
dataAdapter.Dispose();
dbCommand.Dispose();
ExcelCon.Close();
ExcelCon.Dispose();
lblMessage.Text += "<br/>Đã import xong Sheet " + num.ToString() + "!";
}
catch (Exception ex)
{
// dispose used objects
dTable.Dispose();
dataAdapter.Dispose();
dbCommand.Dispose();
ExcelCon.Close();
ExcelCon.Dispose();
Response.Write(ex.ToString());
}
}
}