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