private void exp_excel(bool print)
{
try
{
int be = 3, dong = 5, sodong = ds.Tables[0].Rows.Count + 5, socot = ds.Tables[0].Columns.Count - 2, dongke = sodong - 1;
tenfile = ttb.Export_Excel(ds, "bcngay");
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(ttb.getIndex(0) + "1", ttb.getIndex(0) + "1").EntireColumn.Delete(Missing.Value);
for (int i = 0; i < be; i++)
{
osheet.get_Range(ttb.getIndex(i) + "1", ttb.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
osheet.get_Range(ttb.getIndex(be) + dong.ToString(), ttb.getIndex(socot + 1) + sodong.ToString()).NumberFormat = "#,##0.00";
osheet.get_Range(ttb.getIndex(0) + "4", ttb.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
for (int i = 1; i < dong; i++)
{
osheet.Cells[dong - 1, i] = get_ten(i - 1);
}
orange = osheet.get_Range(ttb.getIndex(0) + "1", ttb.getIndex(socot) + sodong.ToString());
osheet.Cells[dong - 1, dsngay.Tables[0].Rows.Count + 5] = "Cộng";
for (int i = 0; i < dsngay.Tables[0].Rows.Count; i++)
{
osheet.Cells[dong - 1, i + 5] = " " + dsngay.Tables[0].Rows[i]["ten"].ToString();
}
osheet.get_Range(ttb.getIndex(4) + "4", ttb.getIndex(dsngay.Tables[0].Rows.Count + 5) + "4").Orientation = 90;
osheet.get_Range(ttb.getIndex(0) + "4", ttb.getIndex(dsngay.Tables[0].Rows.Count + 5) + "4").RowHeight = 30;
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, 2] = ttb.Syte; osheet.Cells[2, 2] = ttb.Tenbv;
osheet.Cells[1, 4] = "BÁO CÁO SỬ DỤNG";
osheet.Cells[2, 4] = (tu.Text == den.Text)?"Ngày : " + tu.Text:"Từ ngày : " + tu.Text + " đến : " + den.Text;
orange = osheet.get_Range(ttb.getIndex(3) + "1", ttb.getIndex(socot - 1) + "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)
{
try
{
ttb.check_process_Excel();
ds = dsxml.Copy();
int be = 2, dong = 4, sodong = ds.Tables[0].Rows.Count + dong, socot = ds.Tables[0].Columns.Count - 1, dongke = sodong - 1;
tenfile = ttb.Export_Excel(ds, "dutru");
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(ttb.getIndex(i) + "1", ttb.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
osheet.get_Range(ttb.getIndex(be - 1) + dong.ToString(), ttb.getIndex(socot + 1) + sodong.ToString()).NumberFormat = "#,##0.00";
osheet.get_Range(ttb.getIndex(0) + "3", ttb.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
int pos = 7;
foreach (DataRow r in dtmakp.Select("makp<>''", "stt,makp"))
{
osheet.Cells[dong - 1, pos] = r["tenkp"].ToString();
pos += 1;
}
//osheet.Cells[dong-1,pos]="Tồn "+makho.Text;
for (int i = 0; i < 6; i++)
{
osheet.Cells[dong - 1, i + 1] = get_ten(i);
}
orange = osheet.get_Range(ttb.getIndex(0) + "1", ttb.getIndex(socot) + sodong.ToString());
orange.Font.Name = "Arial";
orange.Font.Size = 8;
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
osheet.Cells[1, 2] = ttb.Syte; osheet.Cells[2, 2] = ttb.Tenbv;
s_mmyy = tu.Value.ToString().PadLeft(2, '0') + yyyy.Value.ToString().PadLeft(4, '0').Substring(2, 2);
osheet.Cells[1, 4] = "TỔNG HỢP DÙ TRÙ";
string s_title = ttb.title(tu.Value.ToString(), tu.Value.ToString()) + " năm " + yyyy.Value.ToString();
osheet.Cells[2, 4] = s_title;
orange = osheet.get_Range(ttb.getIndex(3) + "1", ttb.getIndex(socot - 1) + "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 { }
}
private void exp_excel()
{
try
{
string s;
int be = 3, dong = 6, sodong = ds.Tables[0].Rows.Count + 6, socot = ds.Tables[0].Columns.Count - 2, dongke = sodong - 2;
tenfile = ttb.Export_Excel(ds, "biendong");
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(ttb.getIndex(i) + "1", ttb.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
osheet.Cells[dong - 2, 3] = "Tên hoạt chất";
osheet.Cells[dong - 2, 4] = "Tên thuốc - hàm lượng";
osheet.Cells[dong - 2, 5] = "ĐVT";
osheet.Cells[dong - 2, 6] = "Diễn giải";
for (int i = 0; i < i_rec; i++)
{
s = ds.Tables[0].Columns[i + 6].ColumnName.ToString();
osheet.Cells[dong - 2, i + 7] = "'" + s.Substring(4, 2) + "/" + s.Substring(2, 2) + "/" + s.Substring(0, 2);
}
osheet.Cells[dong - 2, i_rec + 7] = "Tỷ lệ";
osheet.Cells[dong - 2, i_rec + 8] = "Ghi chú";
osheet.get_Range(ttb.getIndex(0) + "1", ttb.getIndex(0) + "1").EntireColumn.Delete(Missing.Value);
osheet.get_Range(ttb.getIndex(0) + "1", ttb.getIndex(0) + "1").EntireColumn.Delete(Missing.Value);
osheet.get_Range(ttb.getIndex(be) + dong.ToString(), ttb.getIndex(socot) + sodong.ToString()).NumberFormat = "#,##0.00";
osheet.get_Range(ttb.getIndex(0) + "4", ttb.getIndex(socot - 1) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
orange = osheet.get_Range(ttb.getIndex(0) + "1", ttb.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] = ttb.Syte; osheet.Cells[2, 1] = ttb.Tenbv;
osheet.Cells[1, 3] = "BIẾN ĐỘNG GIÁ THUỐC";
osheet.Cells[2, 3] = "Từ ngày " + tu.Text + " đến " + den.Text;
orange = osheet.get_Range(ttb.getIndex(2) + "1", ttb.getIndex(socot - 1) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
oxl.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void butExcel_Click(object sender, System.EventArgs e)
{
ttb.check_process_Excel();
string tenfile = "";
if (dt == null)
{
if (ds != null)
{
tenfile = ttb.Export_Excel(ds, ReportFile.Substring(0, ReportFile.Length - 4));
}
else
{
return;
}
}
else
{
tenfile = ttb.Export_Excel(dt, ReportFile.Substring(0, ReportFile.Length - 4));
}
if (tenfile == "")
{
return;
}
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;
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";
oxl.Visible = true;
}
private void exp_excel(bool print, bool biendong)
{
try
{
int be = 3, dong = 5, sodong = ds.Tables[0].Rows.Count + 5, socot = ds.Tables[0].Columns.Count - 2, dongke = sodong - 1;
tenfile = ttb.Export_Excel(ds, "theodoigia");
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(ttb.getIndex(0) + "1", ttb.getIndex(0) + "1").EntireColumn.Delete(Missing.Value);
for (int i = 0; i < be; i++)
{
osheet.get_Range(ttb.getIndex(i) + "1", ttb.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
osheet.get_Range(ttb.getIndex(be) + dong.ToString(), ttb.getIndex(socot + 1) + sodong.ToString()).NumberFormat = "#,##0.000";
osheet.get_Range(ttb.getIndex(0) + "4", ttb.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
for (int i = 1; i < dong; i++)
{
osheet.Cells[dong - 1, i] = get_ten(i - 1);
}
orange = osheet.get_Range(ttb.getIndex(0) + "1", ttb.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, 2] = ttb.Syte; osheet.Cells[2, 2] = ttb.Tenbv;
s_mmyy = tu.Value.ToString().PadLeft(2, '0') + yyyy.Value.ToString().PadLeft(4, '0').Substring(2, 2);
s_tu = tu.Value.ToString().PadLeft(2, '0');
s_den = den.Value.ToString().PadLeft(2, '0');
s_yy = yyyy.Value.ToString().PadLeft(4, '0').Substring(2, 2);
osheet.Cells[1, 4] = "THEO DÕI BIẾN ĐỘNG GIÁ";
osheet.Cells[2, 4] = (tu.Value == den.Value && yy.Value == yyyy.Value)?"Tháng : " + s_tu + "/" + yyyy.Value.ToString():"Từ tháng :" + s_tu + "/" + yy.Value.ToString() + " đến tháng :" + s_den + "/" + yyyy.Value.ToString();
orange = osheet.get_Range(ttb.getIndex(3) + "1", ttb.getIndex(socot - 1) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
osheet.get_Range(ttb.getIndex(4) + "4", ttb.getIndex(ds.Tables[0].Columns.Count) + "4").NumberFormat = "@";
osheet.get_Range(ttb.getIndex(4) + "4", ttb.getIndex(ds.Tables[0].Columns.Count) + "4").RowHeight = 24;
if (!biendong)
{
for (int i = 0; i < dtngay.Rows.Count; i++)
{
osheet.Cells[dong - 1, i + 5] = dtngay.Rows[i]["ngaydoi"].ToString();
}
}
else
{
osheet.Cells[dong - 1, 5] = s_tu + "/" + yy.Value.ToString();
if (s_tu != s_den)
{
osheet.Cells[dong - 1, 6] = s_den + "/" + yyyy.Value.ToString();
osheet.Cells[dong - 1, 7] = "Chênh lệch";
}
}
orange = osheet.get_Range(ttb.getIndex(4) + "5", ttb.getIndex(socot + 1) + sodong.ToString());
orange.EntireColumn.AutoFit();
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()
{
try
{
int be = 21, dong = 23, sodong = ds.Tables[0].Rows.Count + 24, socot = ds.Tables[0].Columns.Count - 2, dongke = sodong - 1;
tenfile = ttb.Export_Excel(ds, "bbthanhly");
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(ttb.getIndex(0) + "1", ttb.getIndex(1) + "1").EntireColumn.Delete(Missing.Value);
for (int i = 0; i < be; i++)
{
osheet.get_Range(ttb.getIndex(i) + "1", ttb.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
osheet.get_Range(ttb.getIndex(5) + dong.ToString(), ttb.getIndex(socot) + sodong.ToString()).NumberFormat = "#,##0.00";
osheet.get_Range(ttb.getIndex(0) + "21", ttb.getIndex(socot - 1) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
for (int i = 1; i < 6; i++)
{
osheet.Cells[dong - 1, i] = get_title(i - 1);
orange = osheet.get_Range(ttb.getIndex(i - 1) + "21", ttb.getIndex(i - 1) + "22");
orange.MergeCells = true;
}
for (int i = 0; i < dtkho.Rows.Count; i++)
{
osheet.Cells[dong - 1, i + 6] = dtkho.Rows[i]["ten"].ToString();
}
i_rec = dtkho.Rows.Count;
for (int i = 5; i < 9; i++)
{
osheet.Cells[dong - 1, i + i_rec + 1] = get_title(i);
}
for (int i = 6 + i_rec; i < 6 + i_rec + 3; i++)
{
orange = osheet.get_Range(ttb.getIndex(i) + "21", ttb.getIndex(i) + "22");
orange.MergeCells = true;
}
osheet.Cells[dong - 2, 6] = "Số lượng";
orange = osheet.get_Range(ttb.getIndex(5) + "21", ttb.getIndex(i_rec + 5) + "21");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Bold = true;
orange = osheet.get_Range(ttb.getIndex(0) + dong.ToString(), ttb.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] = ttb.Syte; osheet.Cells[2, 1] = ttb.Tenbv;
osheet.Cells[1, 3] = "BIÊN BẢN THANH LÝ,HỦY";
osheet.Cells[2, 3] = ngay;
for (int i = 0; i < 16; i++)
{
osheet.Cells[i + 4, 2] = get_ten(i);
}
for (int i = 0; i < 12; i++)
{
osheet.Cells[i + 6, 7] = get_chucdanh(i);
}
orange = osheet.get_Range(ttb.getIndex(2) + "1", ttb.getIndex(socot - 1) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
osheet.Cells[sodong - 1, 2] = "Tổng số tiền :";
osheet.Cells[sodong - 1, 5 + i_rec + 3] = tongcong;
string s = doiso.Doiso_Unicode(ds.Tables[0].Rows.Count.ToString()).Trim();
osheet.Cells[sodong + 1, 2] = "Tổng số :" + s.Substring(0, s.Length - 4) + " khoản";
osheet.Cells[sodong + 2, 2] = "Số tiền :" + doiso.Doiso_Unicode(Convert.ToInt64(tongcong).ToString());
int so1 = sodong + 4, so2 = sodong + 5;
osheet.Cells[sodong + 4, 3] = "HỘI ĐỒNG THANH LÝ";
osheet.Cells[sodong + 5, 3] = "(Ký)";
orange = osheet.get_Range(ttb.getIndex(2) + so1.ToString(), ttb.getIndex(7) + so2.ToString());
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
so1 = sodong - 1;
orange = osheet.get_Range(ttb.getIndex(1) + so1.ToString(), ttb.getIndex(socot) + so2.ToString());
orange.Font.Bold = true;
for (int i = sodong + 6; i < sodong + 6 + 12; i++)
{
osheet.Cells[i, 2] = get_ten(i - sodong - 4);
}
for (int i = sodong + 6; i < sodong + 6 + 12; i++)
{
osheet.Cells[i, 7] = get_chucdanh(i - sodong - 6);
}
oxl.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
