public void ExportReportExcell() { System.Globalization.CultureInfo vi = new System.Globalization.CultureInfo("vi-VN"); string str_Thongbao = null; string strfilename = "/Thongkenhuanbut_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; object template = HttpContext.Current.Server.MapPath("~/Template") + "/NBCTV_QDND.xlt"; string dir_filename = HttpContext.Current.Server.MapPath("~/DataExport") + strfilename; object Missing = System.Reflection.Missing.Value; oExcel = new Excel1.Application(); oWorkBooks = oExcel.Workbooks; oWorkBook = oWorkBooks.Add(template); oSheet = (Excel1.Worksheet)oWorkBook.ActiveSheet; oExcel.Visible = false; oExcel.UserControl = true; try { int lb = int.Parse(cbo_Anpham.SelectedValue); if (cbo_Anpham.SelectedIndex == 0) { UltilFunc.AlertJS("Bạn chưa chọn ấn phẩm"); return; } int Vungmien = 0; if (cboVungmien.SelectedValue != "") { Vungmien = int.Parse(cboVungmien.SelectedValue); } DataSet _ds = Daltinbai.Sp_Thongkethanhtoannhuanbut(lb, int.Parse(cboThanhtoan.SelectedValue), txt_tungay.Text.Trim(), txt_denngay.Text.Trim(), int.Parse(cbo_PVCTV.SelectedValue), Vungmien, Global.MaXuatBan); if (_ds.Tables[0].Rows.Count > 0) { if (cbo_Anpham.SelectedValue != "") { if (cbo_PVCTV.SelectedIndex > 0) { oSheet.get_Range("A3", "A3").Value2 = cbo_Anpham.SelectedItem.Text.ToUpper() + " " + cbo_PVCTV.SelectedItem.Text.ToUpper(); } else { oSheet.get_Range("A3", "A3").Value2 = cbo_Anpham.SelectedItem.Text.ToUpper(); } } oSheet.get_Range("A4", "A4").Value2 = "Ngày " + DateTime.Now.Day + " tháng " + DateTime.Now.Month + " năm " + DateTime.Now.Year; int _row = 7; for (int i = 0; i < _ds.Tables[0].Rows.Count; i++) { oSheet.get_Range("A" + _row.ToString().Trim(), "A" + _row.ToString().Trim()).Value2 = (i + 1).ToString(); oSheet.get_Range("B" + _row.ToString().Trim(), "B" + _row.ToString().Trim()).Value2 = UltilFunc.GetTenTacGiaTinBai(_ds.Tables[0].Rows[i]["TacgiaTin"].ToString()); oSheet.get_Range("B" + _row.ToString().Trim(), "B" + _row.ToString().Trim()).WrapText = true; oSheet.get_Range("C" + _row.ToString().Trim(), "C" + _row.ToString().Trim()).Value2 = UltilFunc.GetDiaChiTacGia(_ds.Tables[0].Rows[i]["TacgiaTin"].ToString(), 1); oSheet.get_Range("C" + _row.ToString().Trim(), "C" + _row.ToString().Trim()).WrapText = true; oSheet.get_Range("D" + _row.ToString().Trim(), "D" + _row.ToString().Trim()).Value2 = _ds.Tables[0].Rows[i]["Tieude"].ToString(); oSheet.get_Range("D" + _row.ToString().Trim(), "D" + _row.ToString().Trim()).WrapText = true; oSheet.get_Range("E" + _row.ToString().Trim(), "E" + _row.ToString().Trim()).Value2 = _ds.Tables[0].Rows[i]["DiemTin"].ToString(); _row++; } oSheet.get_Range("E7", "E" + _row.ToString().Trim()).WrapText = true; oSheet.get_Range("B7", "B" + _row.ToString().Trim()).WrapText = true; oSheet.get_Range("A7", "A" + _row.ToString().Trim()).Cells.RowHeight = 25; oSheet.get_Range("A6", "E" + _row.ToString().Trim()).Borders.LineStyle = Excel1.XlLineStyle.xlContinuous; oSheet.get_Range("E" + _row.ToString().Trim(), "E" + _row.ToString().Trim()).Value2 = "=SUM(E7:E" + (_row - 1) + ")"; oSheet.get_Range("E" + _row.ToString().Trim(), "E" + _row.ToString().Trim()).Font.Bold = true; oSheet.get_Range("D" + _row.ToString().Trim(), "D" + _row.ToString().Trim()).HorizontalAlignment = Excel1.Constants.xlRight; oSheet.get_Range("D" + _row.ToString().Trim(), "D" + _row.ToString().Trim()).Value2 = "TỔNG TIỀN:"; oSheet.get_Range("B" + (_row + 1).ToString().Trim(), "E" + (_row + 1).ToString().Trim()).MergeCells = true; oSheet.get_Range("B" + (_row + 1).ToString().Trim(), "B" + (_row + 1).ToString().Trim()).Value2 = "Số tiền bằng chữ:"; oSheet.get_Range("B" + (_row + 1).ToString().Trim(), "B" + (_row + 1).ToString().Trim()).Font.Bold = true; oSheet.get_Range("B" + (_row + 1).ToString().Trim(), "B" + (_row + 1).ToString().Trim()).Font.Name = "Times New Roman"; oSheet.get_Range("B" + (_row + 1).ToString().Trim(), "B" + (_row + 1).ToString().Trim()).Font.Size = 13; oSheet.get_Range("C" + (_row + 1).ToString().Trim(), "E" + (_row + 1).ToString().Trim()).MergeCells = true; oSheet.get_Range("A" + (_row + 1).ToString().Trim(), "A" + (_row + 1).ToString().Trim()).Cells.RowHeight = 30; oSheet.get_Range("A" + (_row + 2).ToString().Trim(), "E" + (_row + 2).ToString().Trim()).MergeCells = true; oSheet.get_Range("A" + (_row + 2).ToString().Trim(), "E" + (_row + 2).ToString().Trim()).Value2 = "THỦ TRƯỞNG CQ TÀI CHÍNH TRƯỞNG PHÒNG BĐCTV NGƯỜI LẬP"; oSheet.get_Range("A" + (_row + 2).ToString().Trim(), "A" + (_row + 2).ToString().Trim()).Columns.AutoFit(); oSheet.get_Range("A" + (_row + 2).ToString().Trim(), "A" + (_row + 2).ToString().Trim()).HorizontalAlignment = Excel1.Constants.xlCenter; oSheet.get_Range("A" + (_row + 2).ToString().Trim(), "A" + (_row + 2).ToString().Trim()).Font.Size = 13; oSheet.get_Range("A" + (_row + 2).ToString().Trim(), "E" + (_row + 2).ToString().Trim()).Font.Name = "Times New Roman"; oSheet.get_Range("A" + (_row + 2).ToString().Trim(), "A" + (_row + 2).ToString().Trim()).Cells.RowHeight = 40; oSheet.get_Range("A" + (_row + 3).ToString().Trim(), "E" + (_row + 3).ToString().Trim()).MergeCells = true; oSheet.get_Range("A" + (_row + 3).ToString().Trim(), "A" + (_row + 3).ToString().Trim()).Cells.RowHeight = 40; oSheet.get_Range("C" + (_row + 4).ToString().Trim(), "E" + (_row + 4).ToString().Trim()).MergeCells = true; oSheet.get_Range("C" + (_row + 4).ToString().Trim(), "E" + (_row + 4).ToString().Trim()).Value2 = " Đại tá: Lê Quý Hoàng Nguyễn Thị Liên"; oSheet.get_Range("C" + (_row + 4).ToString().Trim(), "C" + (_row + 4).ToString().Trim()).Font.Size = 13; } } catch (Exception ex) { str_Thongbao = ex.ToString(); } finally { object filename = @dir_filename; oWorkBook.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Missing, Missing, Missing, Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing, Missing, Missing, Missing, Missing); if (oSheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet); oSheet = null; } if (oWorkBook != null) { oWorkBook.Close(Missing, @dir_filename, Missing); System.Runtime.InteropServices.Marshal.ReleaseComObject(oWorkBook); oWorkBook = null; } if (oExcel != null) { oExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel); oExcel = null; } GC.Collect(); GC.WaitForPendingFinalizers(); foreach (System.Diagnostics.Process proc in System.Diagnostics.Process.GetProcessesByName("EXCEL.exe")) { proc.Kill(); } Page.Response.Redirect("~/DataExport/" + strfilename); } }