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