void exportToExcel(string fullpath)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
List <string> ListKhoa = bll.GetListKhoa_BLL();
ListKhoa.RemoveAt(ListKhoa.Count - 1);
foreach (string tenkhoa in ListKhoa)
{
tbStatusBar.Text = "Saving... Please Wait";
Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.Add(Type.Missing, xlWorkBook.Worksheets[xlWorkBook.Worksheets.Count], 1, XlSheetType.xlWorksheet);
xlWorkSheet.Name = tenkhoa;
Range r = xlWorkSheet.Range["A8:A10"];
r.Merge();
r.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
r.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
r.Font.Bold = true;
r.Cells[1, 1] = "Số TT";
Range r1 = xlWorkSheet.Range["B8:N8"];
r1.Merge();
r1.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
r1.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
r1.Font.Bold = true;
xlWorkSheet.Cells[8, 2] = "Ghi tăng tài sản, dụng cụ, đồ gỗ";
Range r2 = xlWorkSheet.Range["O8:S8"];
r2.Merge();
r2.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
r2.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
r2.Font.Bold = true;
r2.Cells[1, 1] = "Ghi giảm tài sản";
Range r3 = xlWorkSheet.Range["B9:D9"];
r3.Merge();
r3.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
r3.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
r3.Font.Bold = true;
r3.Cells[1, 1] = "Chứng từ";
xlWorkSheet.Cells[10, 2] = "Mã TS";
xlWorkSheet.Cells[10, 3] = "SLTS Nhập về";
xlWorkSheet.Cells[10, 4] = "Mã NSD";
Range r4 = xlWorkSheet.Range["E9:E10"];
r4.Merge();
r4.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
r4.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
r4.Font.Bold = true;
r4.Cells[1, 1] = "Tên tài sản cố định, CC, DC và đồ gỗ ...";
Range r5 = xlWorkSheet.Range["F9:F10"];
r5.Merge();
r5.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
r5.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
r5.Font.Bold = true;
r5.Cells[1, 1] = "Số hiệu, thông số kỹ thuật";
Range r6 = xlWorkSheet.Range["G9:G10"];
r6.Merge();
r6.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
r6.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
r6.Font.Bold = true;
r6.Cells[1, 1] = "SL Tại Phòng";
Range r7 = xlWorkSheet.Range["H9:H10"];
r7.Merge();
r7.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
r7.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
r7.Font.Bold = true;
r7.Cells[1, 1] = "Thành tiền";
Range r8 = xlWorkSheet.Range["I9:I10"];
r8.Merge();
r8.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
r8.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
r8.Font.Bold = true;
r8.Cells[1, 1] = "Tỷ lệ %CL";
Range r9 = xlWorkSheet.Range["J9:J10"];
r9.Merge();
r9.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
r9.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
r9.Font.Bold = true;
r9.Cells[1, 1] = "Tỷ lệ %CL";
Range r10 = xlWorkSheet.Range["K9:K10"];
r10.Merge();
r10.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
r10.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
r10.Font.Bold = true;
r10.Cells[1, 1] = "Tỷ lệ %CL";
Range r11 = xlWorkSheet.Range["L9:L10"];
r11.Merge();
r11.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
r11.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
r11.Font.Bold = true;
r11.Cells[1, 1] = "Tỷ lệ %CL";
Range r12 = xlWorkSheet.Range["M9:M10"];
r12.Merge();
r12.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
r12.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
r12.Font.Bold = true;
r12.Cells[1, 1] = "Tỷ lệ %CL";
Range r13 = xlWorkSheet.Range["N9:N10"];
r13.Merge();
r13.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
r13.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
r13.Font.Bold = true;
r13.Cells[1, 1] = "Tỷ lệ HM";
Range r14 = xlWorkSheet.Range["O9:P9"];
r14.Merge();
r14.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
r14.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
r14.Font.Bold = true;
r14.Cells[1, 1] = "Chứng từ";
Range r15 = xlWorkSheet.Range["O10:O10"];
r15.Merge();
r15.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
r15.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
r15.Font.Bold = true;
r15.Cells[1, 1] = "Ngày";
Range r16 = xlWorkSheet.Range["P10:P10"];
r16.Merge();
r16.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
r16.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
r16.Font.Bold = true;
r16.Cells[1, 1] = "Lý do";
Range r17 = xlWorkSheet.Range["Q9:Q10"];
r17.Merge();
r17.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
r17.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
r17.Font.Bold = true;
r17.Cells[1, 1] = "Số lượng Thanh lý";
Range r18 = xlWorkSheet.Range["R9:R10"];
r18.Merge();
r18.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
r18.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
r18.Font.Bold = true;
r18.Cells[1, 1] = "Thành tiền";
Range r19 = xlWorkSheet.Range["S9:S10"];
r19.Merge();
r19.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
r19.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
r19.Font.Bold = true;
r19.Cells[1, 1] = "Ghi chú";
QLTSDataContext db = new QLTSDataContext();
var s = db.TaiSans.Where(p => p.MaTaiSan.Substring(4, 3) == bll.GetMaKhoa_BLL(tenkhoa) && p.MaTaiSan.Substring(8, 3) == "000").Select(p => p);
List <TaiSan> li = s.ToList <TaiSan>();
int begini = 12;
int beginj = 2;
int STT = 0;
foreach (TaiSan ts in li)
{
xlWorkSheet.Cells[begini, beginj - 1] = ++STT;
xlWorkSheet.Cells[begini, beginj] = ts.MaTaiSan;
xlWorkSheet.Cells[begini, beginj + 1] = ts.SoLuongNhap;
xlWorkSheet.Cells[begini, beginj + 2] = ts.NgayGhiTang.Value.Year.ToString();
xlWorkSheet.Cells[begini, beginj + 3] = ts.TenTaiSan;
xlWorkSheet.Cells[begini, beginj + 4] = ts.ThongSoKyThuat;
xlWorkSheet.Cells[begini, beginj + 5] = bll.GetSLTSOPhong_BLL(ts.MaTaiSan.Substring(0, 7));
xlWorkSheet.Cells[begini, beginj + 6] = ts.SoLuongNhap * 10;
xlWorkSheet.Cells[begini, beginj + 7] = ts.TyLeCL;
xlWorkSheet.Cells[begini, beginj + 8].Formula = "=I" + begini + "-N" + begini;
xlWorkSheet.Cells[begini, beginj + 9].Formula = "=J" + begini + "-N" + begini;
xlWorkSheet.Cells[begini, beginj + 10].Formula = "=K" + begini + "-N" + begini;
xlWorkSheet.Cells[begini, beginj + 11].Formula = "=L" + begini + "-N" + begini;
xlWorkSheet.Cells[begini, beginj + 12] = ts.TyLeHM;
List <DTO.ChungTuGiam> k = db.ChungTuGiams.Where(p => p.MaTaiSan.Substring(0, 7) == ts.MaTaiSan.Substring(0, 7)).Select(p => p).ToList <DTO.ChungTuGiam>();
{ } DTO.ChungTuGiam ctg = null;
if (k.Count > 0)
{
ctg = k[0];
}
if (ctg != null)
{
xlWorkSheet.Cells[begini, beginj + 13] = ctg.NgayGhiGiam.Value.Year.ToString();
xlWorkSheet.Cells[begini, beginj + 14] = ctg.NoiDung;
xlWorkSheet.Cells[begini, beginj + 15] = bll.GetSLThanhLy_DAL(ts.MaTaiSan.Substring(0, 7));
xlWorkSheet.Cells[begini, beginj + 16] = ctg.ThanhTien.ToString();
xlWorkSheet.Cells[begini, beginj + 17] = ctg.GhiChu;
}
begini++;
}
Range b = xlWorkSheet.Range[xlWorkSheet.Cells[11, 1], xlWorkSheet.Cells[begini, beginj + 17]];
b.Borders.LineStyle = XlLineStyle.xlDot;
b.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
b.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
b.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous;
b.Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlContinuous;
xlWorkSheet.Columns.AutoFit();
xlWorkSheet.Rows.AutoFit();
Range x = xlWorkSheet.Range["E4:E4"];
x.Merge();
xlWorkSheet.Cells[4, 5].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
x.Font.Bold = true;
x.Font.Size = 22;
x.Cells[1, 1] = "BẢNG KIỂM KÊ, ĐÁNH GIÁ TÀI SẢN NĂM 2018";
x = xlWorkSheet.Range["C5:C5"];
x.Merge();
xlWorkSheet.Cells[5, 3].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
x.Font.Bold = true;
x.Font.Size = 16;
x.Cells[1, 1] = "SỔ THEO DÕI TÀI SẢN CỐ ĐỊNH VÀ CÔNG CỤ, DỤNG CỤ, VRTMH TẠI NƠI SỬ DỤNG";
xlWorkSheet.Cells[begini + 3, 2] = " - Gồm : */ Thiết bị : ................mục ; */ Đồ gỗ, VRTNH :.............mục ";
xlWorkSheet.Cells[begini + 3, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
xlWorkSheet.Cells[begini + 4, 2] = " - Sổ này có ..................trang, đánh số từ trang 01 đến ...........";
xlWorkSheet.Cells[begini + 4, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
xlWorkSheet.Cells[begini + 5, 2] = " - Ngày mở sổ:.......................";
xlWorkSheet.Cells[begini + 5, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
xlWorkSheet.Cells[begini + 6, 3] = "Hiệu trưởng";
xlWorkSheet.Cells[begini + 6, 3].Font.Bold = true;
xlWorkSheet.Cells[begini + 6, 5] = "Kế toán tài sản";
xlWorkSheet.Cells[begini + 6, 5].Font.Bold = true;
xlWorkSheet.Cells[begini + 5, beginj + 14] = "Đà Nẵng, ngày tháng năm";
xlWorkSheet.Cells[begini + 6, beginj + 14] = "Người ghi sổ";
xlWorkSheet.Cells[begini + 6, beginj + 14].Font.Bold = true;
Marshal.ReleaseComObject(xlWorkSheet);
}
xlWorkBook.SaveAs(fullpath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(0);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
tbStatusBar.Text = "Working normally";
}