public static int CalculateNewID(System.Data.DataTable table, System.Data.DataColumn dc)
{
object res = table.Compute("Max(" + dc.ColumnName + ")", "");
if (res != null)
{
int maxValue = (short) res + 0;
return maxValue + 1;
}
else
return 1;
/*
if (_row.Table.Rows.Count == 0)
{
return 1;
}
else
{
System.Data.DataView dv = _row.Table.DefaultView;
dv.Sort = dc.ColumnName + ", DESC";
return ((int)dv[0][dc.ColumnName]) + 1;
}
*/
}
/*quita de un DataTable aquellas columnas que no tienen ningun valor*/
public void Quitar_vacios(System.Data.DataTable dt)
{
int k = 1;
while (k < dt.Columns.Count)
{
int contador;
string nombrecol = dt.Columns[k].ColumnName;
contador = (int)dt.Compute("count(" + nombrecol + ")", "");
if (contador == 0)
{
dt.Columns.Remove(dt.Columns[k]);
}
else
{
if (variable_vacia(dt,nombrecol))
{
dt.Columns.Remove(dt.Columns[k]);
}
else
{
k++;
}
}
}
}
public bool WriteDataTableToExcel_SOCTCN11_04_V_Subtotaltheochungtu(System.Data.DataTable dataTable, IQueryable<KT_CTuGoc> list, string taikhoan, string madtpn, DateTime from, DateTime to, DateTime print)
{
var listCT = (from a in list
select new { a.MA_CTU, a.SO_CTU, a.NGAY_CTU }).Distinct().ToList();
string saveAsLocation = Application.StartupPath + @"\Excel\SOCTCN11_04_V.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", "J" + (listCT.Count + dataTable.Rows.Count + 20)].Font.Name = "Times New Roman";
excelSheet.Range["A4", "J10"].Font.Name = "Tahoma";
//Font chữ
excelSheet.Range["A1", "A3"].Font.Size = 10;
excelSheet.Range["A4", "A7"].Font.Size = 12;
excelSheet.Range["A9", "J10"].Font.Size = 10;
excelSheet.Range["A11", "J" + (listCT.Count + dataTable.Rows.Count + 20)].Font.Size = 9;
excelSheet.Range["A4", "A6"].Font.FontStyle = "Bold";
excelSheet.Range["A9", "J10"].Font.FontStyle = "Bold";
excelSheet.Range["G" + (listCT.Count + dataTable.Rows.Count + 11), "J" + (listCT.Count + dataTable.Rows.Count + 13)].Font.FontStyle = "Bold";
//Canh giữa chữ
excelSheet.Range["A4", "J7"].HorizontalAlignment = -4108;
excelSheet.Range["A9", "J10"].HorizontalAlignment = -4108;
excelSheet.Range["A11", "E" + (listCT.Count + dataTable.Rows.Count + 11)].HorizontalAlignment = -4108;
excelSheet.Range["G" + (listCT.Count + dataTable.Rows.Count + 11), "G" + (listCT.Count + dataTable.Rows.Count + 13)].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
//Merge
excelSheet.Range["A4", "J4"].MergeCells = true;
excelSheet.Range["A5", "J5"].MergeCells = true;
excelSheet.Range["A6", "J6"].MergeCells = true;
excelSheet.Range["A7", "J7"].MergeCells = true;
excelSheet.Range["A9", "C9"].MergeCells = true;
excelSheet.Range["D9", "E9"].MergeCells = true;
excelSheet.Range["I9", "J9"].MergeCells = true;
//Kích thước Cột và Dòng
excelSheet.Range["A1", "A" + (listCT.Count + dataTable.Rows.Count + 20)].RowHeight = "16.5";
excelSheet.Range["A1"].ColumnWidth = 6;
excelSheet.Range["B1"].ColumnWidth = 6;
excelSheet.Range["C1"].ColumnWidth = 10;
excelSheet.Range["D1"].ColumnWidth = 6;
excelSheet.Range["E1"].ColumnWidth = 10;
excelSheet.Range["F1"].ColumnWidth = 30;
excelSheet.Range["G1"].ColumnWidth = 40;
excelSheet.Range["H1"].ColumnWidth = 6;
excelSheet.Range["I1"].ColumnWidth = 8;
excelSheet.Range["J1"].ColumnWidth = 8;
//Border
excelSheet.Range["A9", "J10"].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excelSheet.Range["A9", "J10"].Borders.Weight = 2d;
excelSheet.Range["A11", "J" + (listCT.Count + dataTable.Rows.Count + 10)].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlDot;
excelSheet.Range["A11", "J" + (listCT.Count + dataTable.Rows.Count + 10)].Borders.Weight = 2d;
excelSheet.Range["G" + (listCT.Count + dataTable.Rows.Count + 11), "J" + (listCT.Count + dataTable.Rows.Count + 13)].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excelSheet.Range["G" + (listCT.Count + dataTable.Rows.Count + 11), "J" + (listCT.Count + dataTable.Rows.Count + 13)].Borders.Weight = 2d;
//Công thức
object SumNo = dataTable.Compute("SUM(CO_VND)", string.Empty);
object SumCo = dataTable.Compute("SUM(CO_VND)", string.Empty);
excelSheet.Range["I" + (listCT.Count + dataTable.Rows.Count + 11), "I" + (listCT.Count + dataTable.Rows.Count + 11)].Formula = SumNo.ToString();
excelSheet.Range["J" + (listCT.Count + dataTable.Rows.Count + 11), "J" + (listCT.Count + dataTable.Rows.Count + 11)].Formula = SumCo.ToString();
//Format
excelSheet.Range["C11", "C" + (listCT.Count + dataTable.Rows.Count + 10)].EntireColumn.NumberFormat = "dd/MM/yyyy";
excelSheet.Range["E11", "E" + (listCT.Count + dataTable.Rows.Count + 10)].EntireColumn.NumberFormat = "dd/MM/yyyy";
excelSheet.Range["I12", "I" + (listCT.Count + dataTable.Rows.Count + 10)].EntireColumn.NumberFormat = "#,###";
excelSheet.Range["J12", "J" + (listCT.Count + dataTable.Rows.Count + 10)].EntireColumn.NumberFormat = "#,###";
#endregion
//gắn header & footer
excelSheet.Range["A1"].Value2 = "CÔNG TY ABCDab";
excelSheet.Range["A2"].Value2 = "M17 LÊ HOÀNG PHÁI,GÒ VẤP,TP.HCM";
excelSheet.Range["A3"].Value2 = "Mã số thuế : 0300688235";
excelSheet.Range["A4"].Value2 = "SỔ CHI TIẾT TÀI KHOẢN THEO ĐỐI TƯỢNG";
excelSheet.Range["A5"].Value2 = "Tài khoản : " + taikhoan;
excelSheet.Range["A6"].Value2 = "Mã ĐTPN : " + madtpn;
excelSheet.Range["A7"].Value2 = "Từ ngày " + from.ToString("dd/MM/yyyy") + " đến ngày " + to.ToString("dd/MM/yyyy");
excelSheet.Range["A9"].Value2 = "Chứng từ";
excelSheet.Range["D9"].Value2 = "Hóa đơn";
excelSheet.Range["F9"].Value2 = "Người giao dịch";
excelSheet.Range["G9"].Value2 = "Diễn giải";
excelSheet.Range["H9"].Value2 = "TK";
excelSheet.Range["I9"].Value2 = "VND - Phát sinh";
excelSheet.Range["A10"].Value2 = "Loại";
excelSheet.Range["B10"].Value2 = "Số";
excelSheet.Range["C10"].Value2 = "Ngày";
excelSheet.Range["D10"].Value2 = "Số";
excelSheet.Range["E10"].Value2 = "Ngày";
excelSheet.Range["H10"].Value2 = "ĐỨ";
excelSheet.Range["I10"].Value2 = "Nợ";
excelSheet.Range["J10"].Value2 = "Có";
excelSheet.Range["G" + (listCT.Count + dataTable.Rows.Count + 11)].Value2 = "CỘNG PHÁT SINH";
excelSheet.Range["G" + (listCT.Count + dataTable.Rows.Count + 12)].Value2 = "SỐ DƯ ĐẦU KỲ";
excelSheet.Range["G" + (listCT.Count + dataTable.Rows.Count + 13)].Value2 = "SỐ DƯ CUỐI KỲ";
excelSheet.Range["I" + (listCT.Count + dataTable.Rows.Count + 14)].Value2 = "Ngày " + print.Day + " tháng " + print.Month + " năm " + print.Year;
excelSheet.Range["C" + (listCT.Count + dataTable.Rows.Count + 15)].Value2 = "Người lập";
excelSheet.Range["G" + (listCT.Count + dataTable.Rows.Count + 15)].Value2 = "Kế toán trưởng";
excelSheet.Range["I" + (listCT.Count + dataTable.Rows.Count + 15)].Value2 = "Giám đốc";
// loop through each row and add values to our sheet
int rowcount = 10;
foreach (var item in listCT)
{
string expression;
expression = "LOAI_CT = '" + item.MA_CTU + "'";
expression += "and SO_CT = '" + item.SO_CTU + "'";
expression += "and NGAY_CT = '" + item.NGAY_CTU + "'";
var dataRow = dataTable.Select(expression);
foreach (DataRow row in dataRow)
{
rowcount += 1;
excelSheet.Range["A" + rowcount].Value2 = row["LOAI_CT"].ToString();
excelSheet.Range["B" + rowcount].Value2 = row["SO_CT"].ToString();
excelSheet.Range["C" + rowcount].Value2 = row["NGAY_CT"].ToString();
excelSheet.Range["D" + rowcount].Value2 = row["SO_HOADON"].ToString();
excelSheet.Range["E" + rowcount].Value2 = row["NGAY_HOADON"].ToString();
excelSheet.Range["F" + rowcount].Value2 = row["NGUOI_GD"].ToString();
excelSheet.Range["G" + rowcount].Value2 = row["DIENGIAI"].ToString();
excelSheet.Range["H" + rowcount].Value2 = row["TKDU"].ToString();
excelSheet.Range["I" + rowcount].Value2 = row["NO_VND"].ToString();
excelSheet.Range["J" + rowcount].Value2 = row["CO_VND"].ToString();
}
rowcount += 1;
excelSheet.Range["A" + rowcount].Value2 = item.MA_CTU.ToString();
excelSheet.Range["B" + rowcount].Value2 = item.SO_CTU.ToString();
excelSheet.Range["C" + rowcount].Value2 = item.NGAY_CTU.ToString();
excelSheet.Range["G" + rowcount].Value2 = "Cộng chứng từ:";
excelSheet.Range["I" + rowcount].Value2 = String.Format("=sum(I{0}:I{1})", rowcount - dataRow.Length, rowcount -1);
excelSheet.Range["J" + rowcount].Value2 = String.Format("=sum(J{0}:J{1})", rowcount - dataRow.Length, rowcount - 1);
excelSheet.Range["A" + (rowcount), "J" + (rowcount)].Font.Italic = true;
excelSheet.Range["A" + (rowcount), "J" + (rowcount)].Font.Underline = true;
excelSheet.Range["A" + (rowcount), "J" + (rowcount)].Font.Bold = true;
}
//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;
}
}
/// <summary>
/// Constrói dados do grupo.
/// Percorre tabela de dados do relatório, filtrando e distinguindo os dados pela coluna do grupo.
/// Também calcula os valores de cada grupo, totalizando-os.
/// </summary>
/// <param name="p_table">Tabela de dados do relatório.</param>
/// <param name="p_parentgroupcolumn">Coluna do grupo pai.</param>
public void BuildCalculate(System.Data.DataTable p_table, string p_parentgroupcolumn)
{
System.Collections.ArrayList v_allcolumns_temp;
string[] v_allcolumns;
int i, j, k;
// PASSO 1: PEGANDO COLUNAS E DADOS DO GRUPO
// alocando lista de colunas
v_allcolumns_temp = new System.Collections.ArrayList();
// adicionando coluna do grupo pai
if (p_parentgroupcolumn != null && p_parentgroupcolumn != "")
v_allcolumns_temp.Add(p_parentgroupcolumn);
// adicionando coluna do grupo
v_allcolumns_temp.Add(this.v_column);
// adicionando todas as colunas do cabeçalho do grupo (exceto as colunas de valor)
for (k = 0; k < this.v_headerfields.Count; k++)
{
if (((Spartacus.Reporting.Field)this.v_headerfields [k]).v_column != "" &&
!((Spartacus.Reporting.Field)this.v_headerfields [k]).v_groupedvalue &&
!v_allcolumns_temp.Contains(((Spartacus.Reporting.Field)this.v_headerfields [k]).v_column))
v_allcolumns_temp.Add(((Spartacus.Reporting.Field)this.v_headerfields [k]).v_column);
}
// adicionando todas as colunas do rodapé do grupo (exceto as colunas de valor)
for (k = 0; k < this.v_footerfields.Count; k++)
{
if (((Spartacus.Reporting.Field)this.v_footerfields [k]).v_column != "" &&
!((Spartacus.Reporting.Field)this.v_footerfields [k]).v_groupedvalue &&
!v_allcolumns_temp.Contains(((Spartacus.Reporting.Field)this.v_footerfields [k]).v_column))
v_allcolumns_temp.Add(((Spartacus.Reporting.Field)this.v_footerfields [k]).v_column);
}
// alocando vetor de string
v_allcolumns = new string[v_allcolumns_temp.Count];
// copiando nomes de colunas para o vetor de string
for (k = 0; k < v_allcolumns_temp.Count; k++)
v_allcolumns [k] = (string)v_allcolumns_temp [k];
// filtrando dados distintos pela lista de colunas, e armazenando em tabela
if (p_parentgroupcolumn != null && p_parentgroupcolumn != "")
p_table.DefaultView.Sort = p_parentgroupcolumn + ", " + this.v_column;
else
p_table.DefaultView.Sort = this.v_column;
this.v_table = p_table.DefaultView.ToTable(true, v_allcolumns);
// PASSO 2: PREENCHENDO VALORES
// limpando array temporario
v_allcolumns_temp.Clear();
// adicionando todas as colunas do cabeçalho do grupo (somente as colunas de valor)
for (k = 0; k < this.v_headerfields.Count; k++)
{
if (((Spartacus.Reporting.Field)this.v_headerfields [k]).v_column != "" &&
((Spartacus.Reporting.Field)this.v_headerfields [k]).v_groupedvalue &&
! v_allcolumns_temp.Contains(((Spartacus.Reporting.Field)this.v_headerfields [k]).v_column))
v_allcolumns_temp.Add(((Spartacus.Reporting.Field)this.v_headerfields [k]).v_column);
}
// adicionando todas as colunas do rodapé do grupo (somente as colunas de valor)
for (k = 0; k < this.v_footerfields.Count; k++)
{
if (((Spartacus.Reporting.Field)this.v_footerfields [k]).v_column != "" &&
((Spartacus.Reporting.Field)this.v_footerfields [k]).v_groupedvalue &&
! v_allcolumns_temp.Contains(((Spartacus.Reporting.Field)this.v_footerfields [k]).v_column))
v_allcolumns_temp.Add(((Spartacus.Reporting.Field)this.v_footerfields [k]).v_column);
}
// criando colunas de valor na tabela do grupo
for (k = 0; k < v_allcolumns_temp.Count; k++)
this.v_table.Columns.Add((string)v_allcolumns_temp [k]);
// preenchendo valores sumarizados
for (i = 0; i < this.v_table.Rows.Count; i++)
{
for (j = 0; j < v_allcolumns_temp.Count; j++)
this.v_table.Rows [i] [(string)v_allcolumns_temp [j]] = p_table.Compute("Sum(" + ((string)v_allcolumns_temp [j]) + ")", this.v_column + " = '" + this.v_table.Rows [i] [this.v_column].ToString() + "'").ToString();
}
}