public Form1()
{
InitializeComponent();
QLTSContext db = new QLTSContext();
db.TAISANs.Select(p => p);
bll = new QLTS_BLL();
button4.Enabled = false;
LoadCbb();
}
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> ListMaPhong = bll.GetListMaPhong();
ListMaPhong.RemoveAt(ListMaPhong.Count - 1);
foreach (string maphong in ListMaPhong)
{
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 = bll.getTenPhong(maphong);
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] = "Mã số TB";
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";
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";
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ú";
QLTSContext db = new QLTSContext();
var s = db.TAISANs.Where(p => p.MaPhong.Contains(maphong)).Select(p => p);
//new { p.MaTS, p.MaPhong, p.NgayGhiTang, p.TenTS, p.ThongSoKyThuat, p.SoLuongCTT, p.TyLeCL, p.TyLeHM }
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.MaTS;
xlWorkSheet.Cells[begini, beginj + 1] = ts.MaLoaiTS;
xlWorkSheet.Cells[begini, beginj + 2] = ts.NgayGhiTang.Year.ToString();
xlWorkSheet.Cells[begini, beginj + 3] = ts.TenTS;
xlWorkSheet.Cells[begini, beginj + 4] = ts.ThongSoKyThuat;
xlWorkSheet.Cells[begini, beginj + 5] = ts.SoLuong;
xlWorkSheet.Cells[begini, beginj + 6] = ts.ThanhTien;
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 <CHUNGTUGIAM> k = db.CHUNGTUGIAMs.Where(p => p.MaTS.Equals(ts.MaTS)).Select(p => p).ToList <CHUNGTUGIAM>();
CHUNGTUGIAM ctg = null;
if (k.Count > 0)
{
ctg = k[0];
}
if (ctg != null)
{
xlWorkSheet.Cells[begini, beginj + 13] = ctg.NgayGhiGiam.Year.ToString();
xlWorkSheet.Cells[begini, beginj + 14] = ctg.NoiDung;
xlWorkSheet.Cells[begini, beginj + 15] = ctg.SoLuong.ToString();
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 2015";
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";
}
public QLTS_DAL()
{
db = new QLTSContext();
}
