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