private void butExcel_Click(object sender, System.EventArgs e)
{
if (butExcel.Enabled == false)
{
return;
}
d.check_process_Excel();
string tenfile = d.Export_Excel(dt, ReportFile.Substring(0, ReportFile.Length - 4));
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
oxl.ActiveWindow.DisplayZeros = false;
try
{
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.LeftMargin = 20;
osheet.PageSetup.RightMargin = 20;
osheet.PageSetup.TopMargin = 30;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
}
catch { }
oxl.Visible = true;
}
private void exp_excel(bool print)
{
d.check_process_Excel();
ds = dsxml.Copy();
int k = 9, dong = 1, sodong = ds.Tables[0].Rows.Count + dong, socot = ds.Tables[0].Columns.Count - 1, dongke = sodong;
tenfile = d.Export_Excel(ds, "BCXUATKHO");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
osheet.get_Range(d.getIndex(1) + dong.ToString(), d.getIndex(socot + 1) + sodong.ToString()).NumberFormat = format_soluong;
osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
for (int i = 0; i < 8; i++)
{
osheet.Cells[dong, i + 1] = get_ten(i);
}
foreach (DataRow r in dskp.Tables[0].Select("true", "mabd"))
{
osheet.Cells[dong, k++] = r["ma"].ToString();
}
osheet.Cells[dong, k] = "Tổng cộng";
osheet.get_Range(d.getIndex(8) + "2", d.getIndex(socot - 1) + dongke.ToString()).Font.ColorIndex = 5;
orange = osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot) + sodong.ToString());
orange.Font.Name = "Arial";
orange.Font.Size = 10;
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
string s_title = "Từ ngày " + tu.Text + " đến " + den.Text;
if (tu.Text == den.Text)
{
s_title = "Ngày " + tu.Text;
}
osheet.PageSetup.LeftHeader = d.Syte + "\n" + d.Tenbv;
osheet.PageSetup.CenterHeader = "&\"Arial,Bold\"&14BÁO CÁO XUẤT KHO\n" + s_title;
if (print)
{
osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
else
{
oxl.Visible = true;
}
}
private void butPreview_Click(object sender, System.EventArgs e)
{
bIntheoSLDuyet = chkslduyet.Checked;
s_field_soluong = (bIntheoSLDuyet) ? "slthuc" : "slyeucau";
d.check_process_Excel();
//Get_buoi();
taotable();
if (ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show(
lan.Change_language_MessageText("Không có số liệu !"), d.Msg);
tu.Focus();
return;
}
exp_excel(false);
}
private void exp_excel(bool print)
{
d.check_process_Excel();
try
{
int be = 3, dong = 5, sodong = dsxml.Tables[0].Rows.Count + 5, socot = dsxml.Tables[0].Columns.Count - 1, dongke = sodong;
tenfile = d.Export_Excel(dsxml, "bccpkcb");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));//,Missing.Value,Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
for (int i = 0; i < be; i++)
{
osheet.get_Range(d.getIndex(i) + "1", d.getIndex(i) + "1").EntireRow.Insert(Missing.Value); //,Missing.Value);
}
osheet.get_Range(d.getIndex(i_cot) + dong.ToString(), d.getIndex(socot) + sodong.ToString()).NumberFormat = "#,##0.00";
osheet.get_Range(d.getIndex(0) + "4", d.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
for (int i = 0; i < i_cot; i++)
{
osheet.Cells[dong - 1, i + 1] = get_ten(i).ToString();
}
osheet.Cells[dong - 1, socot + 1] = "TỔNG CỘNG";
osheet.Cells[sodong, i_cot - 1] = "TỔNG CỘNG";
orange = osheet.get_Range(d.getIndex(0) + "4", d.getIndex(1) + "4");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Bold = true;
int so = sodong, j = 1;
decimal tc = 0;
foreach (DataRow r in dtnhomvp.Select("true", "stt"))
{
osheet.Cells[dong - 1, j + i_cot] = r["ten"].ToString();
orange = osheet.get_Range(d.getIndex(j + i_cot) + "3", d.getIndex(j + i_cot) + "3");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Bold = true;
osheet.Cells[sodong, j + i_cot] = r["tc"].ToString();
osheet.get_Range(d.getIndex(j + i_cot - 1) + so.ToString(), d.getIndex(j + i_cot - 1) + so.ToString()).Font.Bold = true;
tc += decimal.Parse(r["tc"].ToString());
j++;
}
osheet.Cells[sodong, socot + 1] = tc;
osheet.get_Range(d.getIndex(socot) + so.ToString(), d.getIndex(socot) + so.ToString()).Font.Bold = true;
orange = osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot) + sodong.ToString());
orange.Font.Name = "Arial";
orange.Font.Size = 8;
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.LeftMargin = 20;
osheet.PageSetup.RightMargin = 20;
osheet.PageSetup.TopMargin = 30;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
osheet.Cells[1, 1] = d.Syte; osheet.Cells[2, 1] = d.Tenbv;
orange = osheet.get_Range(d.getIndex(1) + "1", d.getIndex(3) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
osheet.Cells[1, 3] = "BÁO CÁO CHI PHÍ KHÁM CHỮA BỆNH " + madoituong.Text.Trim().ToUpper() + " NGỌAI TRÚ";
osheet.Cells[2, 3] = (tu.Text == den.Text)?"Ngày : " + tu.Text:"Từ ngày :" + tu.Text + " đến ngày :" + den.Text;
orange = osheet.get_Range(d.getIndex(2) + "1", d.getIndex(socot) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
if (print)
{
osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
else
{
oxl.Visible = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void exp_excel(bool print)
{
d.check_process_Excel();
try
{
int be = 3, dong = 5, sodong = dsxml.Tables[0].Rows.Count + 5, socot = dsxml.Tables[0].Columns.Count - 1, dongke = sodong;
tenfile = d.Export_Excel(dsxml, "solankham");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));//,Missing.Value,Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
for (int i = 0; i < be; i++)
{
osheet.get_Range(d.getIndex(i) + "1", d.getIndex(i) + "1").EntireRow.Insert(Missing.Value); //,Missing.Value);
}
osheet.get_Range(d.getIndex(be + 2) + dong.ToString(), d.getIndex(socot) + sodong.ToString()).NumberFormat = "#,##0";
osheet.get_Range(d.getIndex(0) + "4", d.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
for (int i = 0; i <= socot; i++)
{
osheet.Cells[dong - 1, i + 1] = get_ten(i).ToString();
}
osheet.Cells[sodong, 2] = "TỔNG CỘNG";
orange = osheet.get_Range(d.getIndex(0) + "4", d.getIndex(1) + "4");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Bold = true;
int so = sodong;
osheet.Cells[sodong, 4] = soluot;
osheet.Cells[sodong, 5] = dcongkham;
osheet.Cells[sodong, 6] = dthuoc;
osheet.Cells[sodong, 7] = dcls;
osheet.Cells[sodong, 8] = dcongkham + dthuoc + dcls;
orange = osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot) + sodong.ToString());
orange.Font.Name = "Arial";
orange.Font.Size = 8;
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
osheet.PageSetup.Orientation = XlPageOrientation.xlPortrait;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.LeftMargin = 20;
osheet.PageSetup.RightMargin = 20;
osheet.PageSetup.TopMargin = 30;
osheet.Cells[sodong + 1, 6] = "Ngày ... tháng ... năm ....";
osheet.Cells[sodong + 2, 2] = "THỦ TRƯỞNG ĐƠN VỊ";
osheet.Cells[sodong + 2, 6] = " NGƯỜI LẬP BIỂU ";
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
osheet.Cells[1, 1] = d.Syte; osheet.Cells[2, 1] = d.Tenbv;
orange = osheet.get_Range(d.getIndex(1) + "1", d.getIndex(3) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
osheet.Cells[1, 3] = "TỔNG HỢP SỐ LƯỢT KHÁM BỆNH " + madoituong.Text.Trim().ToUpper() + " NGỌAI TRÚ";
osheet.Cells[2, 3] = (tu.Text == den.Text)?"Ngày : " + tu.Text:"Từ ngày :" + tu.Text + " đến ngày :" + den.Text;
orange = osheet.get_Range(d.getIndex(2) + "1", d.getIndex(socot) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
if (print)
{
osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
else
{
oxl.Visible = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void exp_excelCanLoc(bool print)
{
try
{
d.check_process_Excel();
int be = 3, cot = ds.Tables[0].Columns.Count, dong = ds.Tables[0].Rows.Count + be + 2;
DataColumn dc = new DataColumn();
dc.ColumnName = "ghichu";
dc.DataType = Type.GetType("System.String");
ds.Tables[0].Columns.Add(dc);
DataRow drstt = ds.Tables[0].NewRow();
for (int i = 0; i < cot + 1; i++)
{
drstt[i] = i.ToString();
}
ds.Tables[0].Rows.InsertAt(drstt, 0);
tenfile = d.Export_Excel(ds, "ylenh");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
for (int i = 0; i < be; i++)
{
osheet.get_Range(d.getIndex(i) + "1", d.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
for (int i = 0; i < 7; i++)
{
osheet.Cells[be + 1, i + 1] = get_ten(i);
}
for (int i = 0; i < dsmabd.Tables[0].Rows.Count; i++)
{
osheet.Cells[be + 1, i + 8] = dsmabd.Tables[0].Rows[i]["tenbd"].ToString();
osheet.Cells[dong + 1, i + 8] = dsmabd.Tables[0].Rows[i]["soluong"].ToString();
}
osheet.Cells[4, cot + 1] = "Ghi chú";
orange = osheet.get_Range(d.getIndex(be) + "4", d.getIndex(cot) + "4");
orange.VerticalAlignment = XlVAlign.xlVAlignBottom;
orange.Orientation = 90;
orange.RowHeight = 200;
orange.EntireRow.AutoFit();
orange = osheet.get_Range(d.getIndex(0) + "1", d.getIndex(cot + 10) + dong + 10);
orange.Font.Name = "Arial";
orange.Font.Size = 10;
orange.Font.Bold = false;
osheet.get_Range(d.getIndex(0) + "4", d.getIndex(cot) + (dong + 1)).Borders.LineStyle = XlBorderWeight.xlHairline;
//orange.EntireRow.AutoFit();
orange.EntireColumn.AutoFit();
orange = osheet.get_Range(d.getIndex(cot) + "1", d.getIndex(cot + 100) + dong + 10);
orange.Cells.ColumnWidth = 3;
//to mau tu cot thu 6
for (int i = 7; i < cot; i++)
{
if (i % 2 == 0)
{
orange = osheet.get_Range(d.getIndex(i) + "4", d.getIndex(i) + dong.ToString());
orange.Interior.Color = System.Drawing.Color.Beige.ToArgb();
}
}
//
osheet.get_Range(d.getIndex(4) + (dong + 1), d.getIndex(cot - 1) + (dong + 1)).Font.Bold = true;
oxl.ActiveWindow.DisplayZeros = false;
osheet.Cells[1, 2] = d.Tenbv;
osheet.Cells[2, 2] = (tu.Text == den.Text) ? "Ngày " + tu.Text : "Ngày " + tu.Text + " - " + den.Text;
osheet.Cells[1, 4] = "TỔNG HỢP Y LỆNH";
orange = osheet.get_Range(d.getIndex(2) + "1", d.getIndex(cot - 1) + "1");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
osheet.Cells[dong + 4, 26] = "In ngày " + System.DateTime.Today.Day.ToString() + " tháng " + System.DateTime.Today.Month.ToString() + " năm " + System.DateTime.Today.Year.ToString();
osheet.Cells[dong + 5, 2] = "Khoa Dược";
osheet.Cells[dong + 5, 7] = "Bác Sĩ";
osheet.Cells[dong + 5, 15] = "Điều dưỡng thực hiện";
osheet.Cells[dong + 5, 26] = "Người sao y lệnh";
osheet.Cells[dong + 8, 2] = "Họ tên...................";
osheet.Cells[dong + 8, 7] = "Họ tên...................";
osheet.Cells[dong + 8, 15] = "Họ tên...................";
osheet.Cells[dong + 8, 26] = "Họ tên...................";
//orange = osheet.get_Range(d.getIndex(0) + dong+4, d.getIndex(cot) + dong+4);
//orange.Font.Name = "Arial";
//orange.Font.Size = 8;
//orange.Font.Bold = false;
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.LeftMargin = 20;
osheet.PageSetup.RightMargin = 20;
osheet.PageSetup.TopMargin = 30;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
if (print)
{
osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
//osheet.PrintPreview(Missing.Value);
else
{
oxl.Visible = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
