public void ExportReportExcell(string MaTacGia) { System.Globalization.CultureInfo vi = new System.Globalization.CultureInfo("vi-VN"); string str_Thongbao = null; string strfilename = "/Bienlaithanhtoan" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; object template = HttpContext.Current.Server.MapPath("~/Template") + "/BIENLAITHANHTOAN.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 { DataTable _dtmatacgia = new DataTable(); DataSet _ds = new DataSet(); int lb = int.Parse(cboAnPham.SelectedValue.ToString()); if (txt_cmtnd.Text.Trim() != "") { _ds = Daltinbai.Sp_InbienlaithanhtoannhuanbutALL(lb, txt_tungay.Text.Trim(), txt_denngay.Text.Trim(), 0, Global.MaXuatBan, txt_cmtnd.Text.Trim()); } else { _ds = Daltinbai.Sp_InbienlaithanhtoannhuanbutALL(lb, txt_tungay.Text.Trim(), txt_denngay.Text.Trim(), int.Parse(HiddenFieldTacgiatin.Value), Global.MaXuatBan, ""); } if (_ds.Tables[0].Rows.Count > 0) { oSheet.get_Range("A4", "A4").Value2 = "Họ tên: " + UltilFunc.GetTenTacGiaTinBai(MaTacGia).ToUpper(); string diachi = UltilFunc.GetDiaChiTacGia(MaTacGia, 1); if (diachi != "") { oSheet.get_Range("A5", "A5").Value2 = "Địa chỉ: " + diachi.ToUpper(); oSheet.get_Range("A5", "A5").WrapText = true; } int _row = 8; 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(); if (_ds.Tables[0].Rows[i]["Publish_Date"].ToString() != "") { oSheet.get_Range("B" + _row.ToString().Trim(), "B" + _row.ToString().Trim()).Value2 = Convert.ToDateTime(_ds.Tables[0].Rows[i]["Publish_Date"]).ToString("dd/MM/yyyy"); } else { oSheet.get_Range("B" + _row.ToString().Trim(), "B" + _row.ToString().Trim()).Value2 = ""; } oSheet.get_Range("C" + _row.ToString().Trim(), "C" + _row.ToString().Trim()).Value2 = _ds.Tables[0].Rows[i]["Tieude"].ToString(); oSheet.get_Range("D" + _row.ToString().Trim(), "D" + _row.ToString().Trim()).Value2 = _ds.Tables[0].Rows[i]["DiemTin"].ToString(); _row++; } oSheet.get_Range("C8", "C" + _row.ToString().Trim()).WrapText = true; oSheet.get_Range("A8", "A" + _row.ToString().Trim()).Cells.RowHeight = 25; oSheet.get_Range("A7", "D" + _row.ToString().Trim()).Borders.LineStyle = Excel1.XlLineStyle.xlContinuous; oSheet.get_Range("D" + _row.ToString().Trim(), "D" + _row.ToString().Trim()).Value2 = "=SUM(D8:E" + (_row - 1) + ")"; oSheet.get_Range("D" + _row.ToString().Trim(), "D" + _row.ToString().Trim()).Font.Bold = true; oSheet.get_Range("D" + _row.ToString().Trim(), "D" + _row.ToString().Trim()).HorizontalAlignment = Excel1.Constants.xlRight; oSheet.get_Range("A" + _row.ToString().Trim(), "C" + _row.ToString().Trim()).MergeCells = true; oSheet.get_Range("A" + _row.ToString().Trim(), "C" + _row.ToString().Trim()).Value2 = "TỔNG TIỀN:"; oSheet.get_Range("A" + _row.ToString().Trim(), "A" + _row.ToString().Trim()).HorizontalAlignment = Excel1.Constants.xlRight; oSheet.get_Range("C" + (_row + 1).ToString().Trim(), "C" + (_row + 1).ToString().Trim()).Value2 = "Số tiền bằng chữ:"; oSheet.get_Range("C" + (_row + 1).ToString().Trim(), "C" + (_row + 1).ToString().Trim()).Font.Name = "Times New Roman"; oSheet.get_Range("C" + (_row + 1).ToString().Trim(), "C" + (_row + 1).ToString().Trim()).Font.Size = 13; oSheet.get_Range("B" + (_row + 1).ToString().Trim(), "D" + (_row + 1).ToString().Trim()).MergeCells = true; oSheet.get_Range("A" + (_row + 2).ToString().Trim(), "D" + (_row + 2).ToString().Trim()).MergeCells = true; oSheet.get_Range("A" + (_row + 2).ToString().Trim(), "D" + (_row + 2).ToString().Trim()).Value2 = "Ngày " + DateTime.Now.Day + " tháng " + DateTime.Now.Month + " năm " + DateTime.Now.Year; oSheet.get_Range("A" + (_row + 2).ToString().Trim(), "D" + (_row + 2).ToString().Trim()).HorizontalAlignment = Excel1.Constants.xlRight; oSheet.get_Range("A" + (_row + 2).ToString().Trim(), "D" + (_row + 2).ToString().Trim()).Font.Size = 11; oSheet.get_Range("A" + (_row + 3).ToString().Trim(), "D" + (_row + 3).ToString().Trim()).MergeCells = true; oSheet.get_Range("A" + (_row + 3).ToString().Trim(), "D" + (_row + 3).ToString().Trim()).Value2 = "NGƯỜI NHẬN TIỀN NGƯỜI TRẢ TIỀN"; oSheet.get_Range("A" + (_row + 3).ToString().Trim(), "D" + (_row + 3).ToString().Trim()).Font.Bold = true; oSheet.get_Range("A" + (_row + 3).ToString().Trim(), "D" + (_row + 3).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); } }