/// <summary> /// Get company name /// </summary> /// <param>Get company name, default idv = ID01 </param> public static string Get_CompanyName() { CFGRepo _CFGRepo = new CFGRepo(); return _CFGRepo.GetNameByIDV("ID01"); }
//5 public bool WriteDataTableToExcel_InCanDoiPhatSinhTaiKhoan_Mau1(System.Data.DataTable dataTable, DateTime from, DateTime to, DateTime print) { string saveAsLocation = Application.StartupPath + @"\Excel\InCanDoiPhatSinhTaiKhoan_Mau1.xls"; string worksheetName = "IN"; Microsoft.Office.Interop.Excel.Application excel; Microsoft.Office.Interop.Excel.Workbook excelworkBook; Microsoft.Office.Interop.Excel.Worksheet excelSheet; Microsoft.Office.Interop.Excel.Range excelCellrange; try { // Start Excel and get Application object. excel = new Microsoft.Office.Interop.Excel.Application(); // for making Excel visible excel.Visible = false; excel.DisplayAlerts = false; // Creation a new Workbook excelworkBook = excel.Workbooks.Add(Type.Missing); // Workk sheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet; excelSheet.Name = worksheetName; excelSheet.PageSetup.Application.ActiveWindow.DisplayGridlines = false; #region General Cells //Kiểu chữ excelSheet.Range["A1", "H" + (dataTable.Rows.Count + 55)].Font.Name = "Tahoma"; //Font chữ excelSheet.Range["A1", "A3"].Font.Size = 10; excelSheet.Range["G1", "G3"].Font.Size = 8; excelSheet.Range["A4", "A7"].Font.Size = 12; excelSheet.Range["A6", "H7"].Font.Size = 10; excelSheet.Range["A6", "H7"].Font.FontStyle = "Bold"; excelSheet.Range["A8", "H" + (dataTable.Rows.Count + 8)].Font.Size = 9; excelSheet.Range["A4", "A4"].Font.FontStyle = "Bold"; excelSheet.Range["A" + (dataTable.Rows.Count+ 30), "H" + (dataTable.Rows.Count + 30)].Font.FontStyle = "Bold"; excelSheet.Range["A" + (dataTable.Rows.Count + 8), "H" + (dataTable.Rows.Count + 8)].Font.FontStyle = "Bold"; //Canh giữa chữ excelSheet.Range["A4", "H7"].HorizontalAlignment = -4108; excelSheet.Range["A" + (dataTable.Rows.Count + 10), "H" + (dataTable.Rows.Count + 14)].HorizontalAlignment = -4108; excelSheet.Range["G1", "G3"].HorizontalAlignment = -4108; //Merge excelSheet.Range["A4", "H4"].MergeCells = true; excelSheet.Range["A5", "H5"].MergeCells = true; excelSheet.Range["C6", "D6"].MergeCells = true; excelSheet.Range["E6", "F6"].MergeCells = true; excelSheet.Range["G6", "H6"].MergeCells = true; //Kích thước Cột và Dòng excelSheet.Range["A1", "A" + (dataTable.Rows.Count + 55)].RowHeight = "16.5"; excelSheet.Range["A1"].ColumnWidth = 9; excelSheet.Range["B1"].ColumnWidth = 40; excelSheet.Range["C1"].ColumnWidth = 15; excelSheet.Range["D1"].ColumnWidth = 15; excelSheet.Range["E1"].ColumnWidth = 15; excelSheet.Range["F1"].ColumnWidth = 15; excelSheet.Range["G1"].ColumnWidth = 15; excelSheet.Range["H1"].ColumnWidth = 15; //Border excelSheet.Range["A6", "H7"].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; excelSheet.Range["A6", "H7"].Borders.Weight = 2d; excelSheet.Range["A8", "H" + (dataTable.Rows.Count + 7)].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlDot; excelSheet.Range["A8", "H" + (dataTable.Rows.Count + 7)].Borders.Weight = 2d; excelSheet.Range["A" + (dataTable.Rows.Count + 8), "H" + (dataTable.Rows.Count + 8)].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; excelSheet.Range["A" + (dataTable.Rows.Count + 8), "H" + (dataTable.Rows.Count + 8)].Borders.Weight = 2d; //Công thức excelSheet.Range["C" + (dataTable.Rows.Count + 8)].Formula = String.Format("=sum(C8:C{0})", dataTable.Rows.Count + 7); excelSheet.Range["D" + (dataTable.Rows.Count + 8)].Formula = String.Format("=sum(D8:D{0})", dataTable.Rows.Count + 7); excelSheet.Range["E" + (dataTable.Rows.Count + 8)].Formula = String.Format("=sum(E8:E{0})", dataTable.Rows.Count + 7); excelSheet.Range["F" + (dataTable.Rows.Count + 8)].Formula = String.Format("=sum(F8:F{0})", dataTable.Rows.Count + 7); excelSheet.Range["G" + (dataTable.Rows.Count + 8)].Formula = String.Format("=sum(G8:G{0})", dataTable.Rows.Count + 7); excelSheet.Range["H" + (dataTable.Rows.Count + 8)].Formula = String.Format("=sum(H8:H{0})", dataTable.Rows.Count + 7); //format #endregion CFGRepo _CFGRepo = new CFGRepo(); //gắn header excelSheet.Range["A1"].Value2 = _CFGRepo.GetNameByIDV("ID01"); excelSheet.Range["A2"].Value2 = _CFGRepo.GetNameByIDV("ID02"); excelSheet.Range["A3"].Value2 = "Mã số thuế: " + _CFGRepo.GetNameByIDV("ID03"); excelSheet.Range["A4"].Value2 = "BẢNG CÂN ĐỐI PHÁT SINH TÀI KHOẢN"; excelSheet.Range["A5"].Value2 = "Từ ngày " + from.ToString("dd/MM/yyyy") + " đến ngày " + to.ToString("dd/MM/yyyy"); excelSheet.Range["A6"].Value2 = "Mã"; excelSheet.Range["A7"].Value2 = "hiệu"; excelSheet.Range["B6"].Value2 = "Tên tài khoản"; excelSheet.Range["C6"].Value2 = "Số dư đầu kỳ (Vnd)"; excelSheet.Range["E6"].Value2 = "Phát sinh trong kỳ (Vnd)"; excelSheet.Range["G1"].Value2 = "Mẫu số B01 – DN"; excelSheet.Range["G2"].Value2 = "(Ban hành theo TT số 200/2014/TT-BTC"; excelSheet.Range["G3"].Value2 = "Ngày 22/12/2014 của Bộ trưởng BTC)"; excelSheet.Range["G6"].Value2 = "Số dư cuối kỳ (Vnd)"; excelSheet.Range["C7"].Value2 = "Nợ"; excelSheet.Range["D7"].Value2 = "Có"; excelSheet.Range["E7"].Value2 = "Nợ"; excelSheet.Range["F7"].Value2 = "Có"; excelSheet.Range["G7"].Value2 = "Nợ"; excelSheet.Range["H7"].Value2 = "Có"; excelSheet.Range["B" + (dataTable.Rows.Count + 8)].Value2 = "Tổng cộng"; excelSheet.Range["G" + (dataTable.Rows.Count + 10)].Value2 = "Ngày " + print.Day + " tháng " + print.Month + " năm " + print.Year; excelSheet.Range["B" + (dataTable.Rows.Count + 11)].Value2 = "Người lập"; excelSheet.Range["D" + (dataTable.Rows.Count + 11)].Value2 = "Kế toán trưởng"; excelSheet.Range["G" + (dataTable.Rows.Count + 11)].Value2 = "Giám đốc"; excelSheet.Range["B" + (dataTable.Rows.Count + 15)].Value2 = ""; excelSheet.Range["G" + (dataTable.Rows.Count + 15)].Value2 = _CFGRepo.GetNameByIDV("ID04"); // loop through each row and add values to our sheet int rowcount = 7; foreach (DataRow datarow in dataTable.Rows) { double _vnd_du_no = Utils.CDblDef(datarow["VND_DU_NO"], 0); double _vnd_du_co = Utils.CDblDef(datarow["VND_DU_CO"], 0); double _vnd_ps_no = Utils.CDblDef(datarow["VND_PS_NO"], 0); double _vnd_ps_co = Utils.CDblDef(datarow["VND_PS_CO"], 0); double _vnd_ck_no = Utils.CDblDef(datarow["VND_CK_NO"], 0); double _vnd_ck_co = Utils.CDblDef(datarow["VND_CK_CO"], 0); rowcount += 1; excelSheet.Range["A" + rowcount].Value2 = Utils.CStrDef(datarow["MA_TK"], ""); excelSheet.Range["B" + rowcount].Value2 = Utils.CStrDef(datarow["TEN_TK"], ""); excelSheet.Range["C" + rowcount].Value2 = _vnd_du_no > 0 ? String.Format("{0:###,##0}",datarow["VND_DU_NO"]) : ""; excelSheet.Range["D" + rowcount].Value2 = _vnd_du_co > 0 ? String.Format("{0:###,##0}", datarow["VND_DU_CO"]) : ""; excelSheet.Range["E" + rowcount].Value2 = _vnd_ps_no > 0 ? String.Format("{0:###,##0}", datarow["VND_PS_NO"]) : ""; excelSheet.Range["F" + rowcount].Value2 = _vnd_ps_co > 0 ? String.Format("{0:###,##0}", datarow["VND_PS_CO"]) : ""; excelSheet.Range["G" + rowcount].Value2 = _vnd_ck_no > 0 ? String.Format("{0:###,##0}", datarow["VND_CK_NO"]) : ""; excelSheet.Range["H" + rowcount].Value2 = _vnd_ck_co > 0 ? String.Format("{0:###,##0}", datarow["VND_CK_CO"]) : ""; } //now save the workbook and exit Excel excelworkBook.SaveAs(saveAsLocation); excelworkBook.Close(); excel.Quit(); Process.Start(saveAsLocation); return true; } catch (Exception ex) { MessageBox.Show(ex.Message); return false; } finally { excelSheet = null; excelCellrange = null; excelworkBook = null; } }