public void TestNotNullPictures()
{
IWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("SheetWithDrawing.xls");
// TODO - add GetFormat() to interface PictureData and genericise wb.AllPictures
List <HSSFPictureData> lst = (List <HSSFPictureData>)wb.GetAllPictures();
foreach (HSSFPictureData pict in lst)
{
Assert.IsNotNull(pict);
}
}
private void SetWorksheet4OBJHeader()
{
IList lst_Pic;
DataRowView row = (DataRowView)lkedonvicungcapphanmem.GetSelectedDataRow();
switch (row["TEST_SOFTWARE_SUP_NAME"].ToString())
{
case "IDEXX":
row_min = 21; row_title = 27;
row_data = 28;
row_max = 51;
col_min = 2; col_title = 3;
col_max = 28;
//For xlsx
//originalWorkbook = new XSSFWorkbook(file);
//For xls
//originalWorkbook = new HSSFWorkbook(file);
//lst_Pic = originalWorkbook.GetAllPictures();
//for (int i = 0; i < lst_Pic.Count; i++)
//{
// var pic = (HSSFPictureData)lst_Pic[i];
// byte[] data = pic.Data;
// //BinaryWriter writer = new BinaryWriter(File.OpenWrite(@"D:\Temp_Xml\IBD_IDEXX_" + filename + ".jpeg"));
// BinaryWriter writer = new BinaryWriter(File.OpenWrite(@"D:\Temp_Xml\IBD_IDEXX_" + filename + ".jpeg"));
// writer.Write(data);
// writer.Flush();
// writer.Close();
//}
//////////////////////////OBJHeader///////////////////////////////////////////////////////
OBJHeader.FileName = txtTenXetNghiem.Text = (string)worksheet.Cells[8, "D"].Value;;
OBJHeader.FilePath = openFileDialog1.FileName;
OBJHeader.Case = worksheet.Cells[25, "I"].Value;
OBJHeader.Count = int.Parse(worksheet.Cells[10, "AG"].Value);
OBJHeader.GMean = Decimal.Parse(string.Format("{0:##,##0.00}", worksheet.Cells[11, "AG"].Value));
OBJHeader.Mean = Decimal.Parse(string.Format("{0:##,##0.00}", worksheet.Cells[12, "AG"].Value));
OBJHeader.SD = Decimal.Parse(string.Format("{0:##,##0.00}", worksheet.Cells[13, "AG"].Value));
OBJHeader.CV = Decimal.Parse(string.Format("{0:##,##0.00}", worksheet.Cells[15, "AG"].Value));
OBJHeader.Min = Decimal.Parse(string.Format("{0:##,##0.00}", worksheet.Cells[16, "AG"].Value));
OBJHeader.Max = Decimal.Parse(string.Format("{0:##,##0.00}", worksheet.Cells[17, "AG"].Value));
OBJHeader.Tech = (string)worksheet.Cells[18, "AG"].Value;
//XtraMessageBox.Show((string)worksheet.Cells[20, "AG"].Value);
OBJHeader.Date = worksheet.Cells[20, "AG"].Value;
OBJHeader.ID = OBJLines.IBD_RESULT_Header_LAB_ID
= BUSHeader.IBD_RESULT_Header_LABDAO_INSERT(OBJHeader);
txtID.Text = OBJHeader.ID.ToString();
for (int i = 28; i <= 51; i++)
{
if (i <= 31)
{
OBJLines.Line_No = worksheet.Cells[i, "B"].Value;
string well = worksheet.Cells[i, "D"].Value.ToString();
OBJLines.Row = well.Substring(0, 1);
OBJLines.Col = well.Substring(1, well.Length - 1);
OBJLines.OD = Decimal.Parse(string.Format("{0:##,##0.00}", worksheet.Cells[i, "K"].Value));
OBJLines.SP = Decimal.Parse(string.Format("{0:##,##0.00}", "0"));
OBJLines.Titer = int.Parse("0");
OBJLines.GroupTiter = int.Parse("0");
OBJLines.Result = "";
OBJLines.IBD_RESULT_Header_LAB_ID = OBJHeader.ID;
//OBJLines.CTXN_ID = int.Parse(row["CTXN_ID"].ToString());
//OBJLines.KHMau_BanGiao = row["KHMau_BanGiao"].ToString();
//BUSLines.IBD_RESULT_Lines_LABDAO_INSERT(OBJLines);
}
else
{
OBJLines.Line_No = worksheet.Cells[i, "B"].Value;
string well = worksheet.Cells[i, "D"].Value.ToString();
OBJLines.Row = well.Substring(0, 1);
OBJLines.Col = well.Substring(1, well.Length - 1);
OBJLines.OD = Decimal.Parse(string.Format("{0:##,##0.00}", worksheet.Cells[i, "K"].Value));
OBJLines.SP = Decimal.Parse(string.Format("{0:##,##0.00}", worksheet.Cells[i, "Q"].Value));
OBJLines.Titer = int.Parse(worksheet.Cells[i, "W"].Value.ToString());
OBJLines.GroupTiter = int.Parse(worksheet.Cells[i, "Z"].Value.ToString());
OBJLines.Result = worksheet.Cells[i, "AA"].Value.ToString();
OBJLines.IBD_RESULT_Header_LAB_ID = OBJHeader.ID;
//OBJLines.CTXN_ID = int.Parse(row["CTXN_ID"].ToString());
//OBJLines.KHMau_BanGiao = row["KHMau_BanGiao"].ToString();
//BUSLines.IBD_RESULT_Lines_LABDAO_INSERT(OBJLines);
}
BUSLines.IBD_RESULT_Lines_LABDAO_INSERT(OBJLines);
}
break;
case "BIOCHEK":
//originalWorkbook = new HSSFWorkbook(file);
//lst_Pic = originalWorkbook.GetAllPictures();
//for (int i = 0; i < originalWorkbook.GetAllPictures().Count; i++)
//{
// var pic = (HSSFPictureData)lst_Pic[i];
// byte[] data = pic.Data;
// BinaryWriter writer = new BinaryWriter(File.OpenWrite(@"D:\Temp_Xml\IBD_BIOCHEK_" + filename + ".jpeg"));
// writer.Write(data);
// writer.Flush();
// writer.Close();
//}
for (int i = 20; i <= 48; i++)
{
switch ((string)worksheet.Cells[i, "S"].Value)
{
case "GMT:":
OBJHeader.GMean = Convert.ToDecimal(worksheet.Cells[i, "V"].Value);
break;
case "Mean Titer:":
OBJHeader.Mean = Convert.ToDecimal(worksheet.Cells[i, "V"].Value);
break;
case "%CV:":
OBJHeader.CV = Convert.ToDecimal(worksheet.Cells[i, "V"].Value);
break;
case "Min-Max Titer:":
OBJHeader.Min = Convert.ToDecimal(0.000); //float.Parse(worksheet.Cells[i, "V"].Value);
OBJHeader.Max = Convert.ToDecimal(0.000); //float.Parse(worksheet.Cells[i, ""].Value);
break;
default:
OBJHeader.Case = OBJHeader.KHMau_GiaoMau = OBJHeader.FileName = txtTenXetNghiem.Text = (string)worksheet.Cells[11, "J"].Value;
OBJHeader.FilePath = openFileDialog1.FileName;
OBJHeader.CTXN_ID = (int)row["CTXN_ID"];
//OBJHeader.Count = 0;
OBJHeader.SD = 0;
OBJHeader.Tech = "0";
break;
}
switch ((string)worksheet.Cells[i, "AD"].Value)
{
case "Test date:":
string dte_tmp = (string)worksheet.Cells[i, "AI"].Value;
string[] lst_dte_tmp = dte_tmp.Split('/');
OBJHeader.Date = Convert.ToDateTime(lst_dte_tmp[1] + "/" + lst_dte_tmp[0] + "/" + lst_dte_tmp[2]);
break;
case "No. Sample":
OBJHeader.Count = int.Parse((string)worksheet.Cells[i, "AI"].Value);
break;
case "Neg/Sus/Pos:":
neg_sus_pos = (string)worksheet.Cells[i, "AI"].Value;
//XtraMessageBox.Show(neg_sus_pos);
string[] lst_neg_sus_pos = neg_sus_pos.Split('/');
OBJHeader.Neg = int.Parse(lst_neg_sus_pos[0]);
//XtraMessageBox.Show(lst_neg_sus_pos[0]);
OBJHeader.Sus = int.Parse(lst_neg_sus_pos[1]);
//XtraMessageBox.Show(lst_neg_sus_pos[1]);
OBJHeader.Pos = int.Parse(lst_neg_sus_pos[2]);
//XtraMessageBox.Show(lst_neg_sus_pos[2]);
break;
}
}
//////////////////////////OBJHeader///////////////////////////////////////////////////////
OBJHeader.ID = OBJLines.IBD_RESULT_Header_LAB_ID
= BUSHeader.IBD_RESULT_Header_LABDAO_INSERT(OBJHeader);
txtID.Text = OBJHeader.ID.ToString();
for (int i = 50; i <= 60; i++)
{
if (worksheet.Cells[i, "B"].Value == "Sample ID")
{
row_data = i + 1;
//row_max = row_data + 24;
row_max = row_data + kHMau_CTXN_LABBUS.MAX_KHMau_CTXN_LABDAO_SoLuongXN(OBJHeader.KHMau_GiaoMau) + 4;
}
}
//XtraMessageBox.Show(row_data.ToString());
for (int i = row_data; i < row_max; i++)
{
if (worksheet.Cells[i, "B"].Value != null)
{
if (i < row_data + 4)
{
OBJLines.Line_No = worksheet.Cells[i, "B"].Value;
string well = worksheet.Cells[i, "F"].Value.ToString();
OBJLines.Row = well.Substring(0, 1);
OBJLines.Col = well.Substring(1, well.Length - 1);
OBJLines.OD = Decimal.Parse(string.Format("{0:##,##0.000}", worksheet.Cells[i, "M"].Value));
OBJLines.SP = Convert.ToDecimal(worksheet.Cells[i, "O"].Value, new CultureInfo("en-US"));
OBJLines.Titer = 0.000M;
OBJLines.GroupTiter = 0;
OBJLines.Result = "";
OBJLines.IBD_RESULT_Header_LAB_ID = OBJHeader.ID;
OBJLines.CTXN_ID = int.Parse(row["CTXN_ID"].ToString());
OBJLines.KHMau_GiaoMau = (string)worksheet.Cells[11, "J"].Value;
}
else
{
//XtraMessageBox.Show(i.ToString());
OBJLines.Line_No = worksheet.Cells[i, "B"].Value;
string well = worksheet.Cells[i, "F"].Value.ToString();
OBJLines.Row = well.Substring(0, 1);
OBJLines.Col = well.Substring(1, well.Length - 1);
OBJLines.OD = Decimal.Parse(string.Format("{0:##,##0.000}", worksheet.Cells[i, "M"].Value));
OBJLines.SP = Convert.ToDecimal(worksheet.Cells[i, "O"].Value, new CultureInfo("en-US"));
OBJLines.Titer = int.Parse(worksheet.Cells[i, "T"].Value.ToString());
OBJLines.GroupTiter = int.Parse(worksheet.Cells[i, "X"].Value.ToString());
OBJLines.Result = worksheet.Cells[i, "AF"].Value;
OBJLines.IBD_RESULT_Header_LAB_ID = OBJHeader.ID;
OBJLines.CTXN_ID = int.Parse(row["CTXN_ID"].ToString());
OBJLines.KHMau_GiaoMau = (string)worksheet.Cells[11, "J"].Value;
}
BUSLines.IBD_RESULT_Lines_LABDAO_INSERT(OBJLines);
}
}
break;
case "ID.vet":
lst_Pic = originalWorkbook.GetAllPictures();
//for (int i = 0; i < originalWorkbook.GetAllPictures().Count; i++)
//{
// var pic = (HSSFPictureData)lst_Pic[i];
// byte[] data = pic.Data;
// BinaryWriter writer = new BinaryWriter(File.OpenWrite(@"D:\Temp_Xml\IBD_IDvet_" + filename + ".jpeg"));
// writer.Write(data);
// writer.Flush();
// writer.Close();
//}
for (int i = 3; i <= 26; i++)
{
switch ((string)worksheet.Cells[i, "A"].Value)
{
case "GMT:":
OBJHeader.GMean = Convert.ToDecimal(worksheet.Cells[i, "B"].Value);
break;
case "Mean Titer:":
OBJHeader.Mean = Convert.ToDecimal(worksheet.Cells[i, "B"].Value);
break;
case "%CV:":
OBJHeader.CV = Convert.ToDecimal(worksheet.Cells[i, "B"].Value);
break;
case "Test date :":
string dte_tmp = worksheet.Cells[i, "B"].Value.ToString();
//string[] lst_dte_tmp = dte_tmp.Split('/');
//OBJHeader.Date = Convert.ToDateTime(lst_dte_tmp[1] + "/" + lst_dte_tmp[0] + "/" + lst_dte_tmp[2]);
OBJHeader.Date = Convert.ToDateTime(dte_tmp.Substring(0, 10));
break;
case "Name of farm :":
OBJHeader.Case = worksheet.Cells[i, "B"].Value.ToString();
OBJHeader.FileName =
txtTenXetNghiem.Text = worksheet.Cells[i, "B"].Value.ToString();
break;
default:
OBJHeader.FilePath = openFileDialog1.FileName;
OBJHeader.SD = 0;
OBJHeader.Tech = "0";
break;
}
switch ((string)worksheet.Cells[i, "L"].Value)
{
case "G.M.T.":
OBJHeader.GMean = Convert.ToDecimal(worksheet.Cells[i, "M"].Value);
break;
case "Mean":
OBJHeader.Mean = Convert.ToDecimal(worksheet.Cells[i, "M"].Value);
break;
case "CV %":
OBJHeader.CV = Convert.ToDecimal(worksheet.Cells[i, "M"].Value);
break;
case "Min":
OBJHeader.Min = Convert.ToDecimal(worksheet.Cells[i, "M"].Value);
break;
case "Max:":
OBJHeader.Max = Convert.ToDecimal(worksheet.Cells[i, "M"].Value);
break;
case "Total":
OBJHeader.Count = int.Parse(worksheet.Cells[i, "M"].Value.ToString());
break;
}
switch ((string)worksheet.Cells[i, "AD"].Value)
{
case "Neg/Sus/Pos:":
neg_sus_pos = (string)worksheet.Cells[i, "AI"].Value;
string[] lst_neg_sus_pos = neg_sus_pos.Split('/');
OBJHeader.Neg = int.Parse(lst_neg_sus_pos[0]);
OBJHeader.Sus = int.Parse(lst_neg_sus_pos[1]);
OBJHeader.Pos = int.Parse(lst_neg_sus_pos[2]);
break;
}
}
//////////////////////////OBJHeader///////////////////////////////////////////////////////
OBJHeader.ID = OBJLines.IBD_RESULT_Header_LAB_ID
= BUSHeader.IBD_RESULT_Header_LABDAO_INSERT(OBJHeader);
txtID.Text = OBJHeader.ID.ToString();
//for (int i = 0; i <= 10; i++)
//{
// if (worksheet.Cells[i, "D"].Value.ToString() == "Reference")
// {
//
// }
//}
row_data = 5;
//row_max = row_data + 96;
row_max = row_data + kHMau_CTXN_LABBUS.MAX_KHMau_CTXN_LABDAO_SoLuongXN(OBJHeader.KHMau_GiaoMau) + 4;
for (int i = row_data; i < row_max; i++)
{
if (i < row_data + 4)
{
OBJLines.Line_No = worksheet.Cells[i, "D"].Value;
string well = worksheet.Cells[i, "E"].Value.ToString();
OBJLines.Row = well.Substring(0, 1);
OBJLines.Col = well.Substring(1, well.Length - 1);
OBJLines.OD = Decimal.Parse(string.Format("{0:##,##0.00}", worksheet.Cells[i, "F"].Value));
OBJLines.SP = 0.000M;
OBJLines.Titer = 0.000M;
OBJLines.GroupTiter = 0;
OBJLines.Result = "";
OBJLines.IBD_RESULT_Header_LAB_ID = OBJHeader.ID;
}
else
{
if (worksheet.Cells[i, "D"].Value != null)
{
OBJLines.Line_No = worksheet.Cells[i, "D"].Value;
string well = worksheet.Cells[i, "E"].Value.ToString();
OBJLines.Row = well.Substring(0, 1);
OBJLines.Col = well.Substring(1, well.Length - 1);
OBJLines.OD = Decimal.Parse(string.Format("{0:##,##0.00}", worksheet.Cells[i, "F"].Value));
OBJLines.SP = Convert.ToDecimal(worksheet.Cells[i, "G"].Value, new CultureInfo("en-US"));
OBJLines.Titer = int.Parse(worksheet.Cells[i, "I"].Value.ToString());
OBJLines.GroupTiter = int.Parse(worksheet.Cells[i, "J"].Value.ToString());
OBJLines.Result = worksheet.Cells[i, "H"].Value;
OBJLines.IBD_RESULT_Header_LAB_ID = OBJHeader.ID;
}
}
BUSLines.IBD_RESULT_Lines_LABDAO_INSERT(OBJLines);
}
break;
}
try
{
kHMau_CTXN_LABBUS.KHMau_CTXN_LABDAO_UPDATE_CoKetQua(OBJHeader.KHMau_GiaoMau);
}
catch (Exception ex)
{
XtraMessageBox.Show(ex.Message);
}
try
{
//CHEK PC Name
string PCname = System.Environment.MachineName;
//IMAGE
lst_Pic = originalWorkbook.GetAllPictures();
//Step 1 : Map driver Z = \\VPV-ASL-SAMPLE\SYNC_NUTRICIEL_IMG
//Step 2 : As below
//2019-11-05 : var pic = (HSSFPictureData)lst_Pic[1] lỗi nhận icon
var pic = (HSSFPictureData)lst_Pic[1];
byte[] data = pic.Data;
string path = string.Empty;
//BinaryWriter writer = new BinaryWriter(File.OpenWrite(@"D:\Temp_Xml\IBD_IDEXX_" + filename + ".jpeg"));
//if (PCname == "vpv-lab-sample")
// path = @"\\D:\SYNC_NUTRICIEL_IMG\IMG_" + OBJHeader.KHMau_GiaoMau + OBJHeader.CTXN_ID.ToString() + ".jpeg";
//else
path = @"X:\Temp_Xml\IMG_" + OBJHeader.KHMau_GiaoMau + OBJHeader.CTXN_ID.ToString() + ".jpeg";
BinaryWriter writer = new BinaryWriter(File.OpenWrite(path));
writer.Write(data);
writer.Flush();
writer.Close();
}
catch (Exception ex)
{
XtraMessageBox.Show(ex.Message);
}
XtraMessageBoxArgs args = new XtraMessageBoxArgs();
args.AutoCloseOptions.Delay = 1000;
args.AutoCloseOptions.ShowTimerOnDefaultButton = true;
args.DefaultButtonIndex = 0;
args.Caption = "Lưu thành công ";
args.Text = " Đọc dữ liệu tư file excel hoàn tất. Thông báo này sẽ tự đóng sau 1 giây.";
args.Buttons = new DialogResult[] { DialogResult.OK };
XtraMessageBox.Show(args).ToString();
//}
//catch (Exception ex)
//{
// XtraMessageBoxArgs args = new XtraMessageBoxArgs();
// args.AutoCloseOptions.Delay = 20000;
// args.AutoCloseOptions.ShowTimerOnDefaultButton = true;
// args.DefaultButtonIndex = 0;
// args.Caption = "Thông báo ";
// args.Text = " Có lỗi phát sinh. Xin vui lòng chụp màn hình và gởi bộ phận phát triển. Cảm ơn ." + ex.Message + " . Thông báo này sẽ tự đóng sau 20 giây.";
// args.Buttons = new DialogResult[] { DialogResult.OK };
// XtraMessageBox.Show(args).ToString();
//}
Is_close = true;
}