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); } }