Example #1
0
 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;
         }
    */
 }
Example #2
0
        /*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++;
                    }
                }
            }
        }
Example #3
0
        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();
            }
        }