none
C# - How to Group by data rows from Data table and print different excel sheet RRS feed

  • Question

  • Hi,

    I want to group by data rows from data table and print those rows in different Excel with the name of group by values.

    below steps need to perform..

    1. Main table is data table and it contains some rows.

    2. i want to group by rows using department column. In that case i can get 3 different groups such that E-101,E102,E103 will be formed.

    3. After grouping those values, i want to print and store in different Excel files in the name of E-101.xls, E-102.xls, E-103.xls,etc..

    4. The sample output has highlighted in the image (==> E-101.xls, E-102.xls, E-103.xls )

    4.  How to do this in C#.

    Wednesday, May 16, 2018 6:16 AM

Answers

  • Hello Gani,

    More detailed info like below.

     static void Main(string[] args)
            {
                DataTable dt = LoadData();
    
                var departments = from r in dt.Rows.OfType<DataRow>()
                                  group r by r["Department"] into g
                                  select new { Department = g.Key, Data = g };
    
                foreach (var department in departments)
                {
                   // CreateExcel(department.Department);
    
                    string relativePath = string.Format(@"{0}.xls", department.Department);
                    string FullPath = AppDomain.CurrentDomain.BaseDirectory + relativePath;
                    string connectionString ="Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source=" +FullPath + ";Extended Properties='Excel 8.0;HDR=YES;'";
                    using (OleDbConnection cn = new OleDbConnection(connectionString))
                    {
                        cn.Open();
                        CreateDataTable(cn);
                        foreach (var row in department.Data)
                        {
                            insertExcel(cn, row);
                        }
                    }
                }
            }
    
            private static void CreateDataTable(OleDbConnection cn)
            {
                OleDbCommand cmd1 = new OleDbCommand("CREATE TABLE MySheet1 (DeptName char(255), Department char(255),Category1 char(255),Category2 char(255))", cn);
                cmd1.ExecuteNonQuery();
            }
    
            private static void insertExcel(OleDbConnection cn, DataRow row)
            {
    
                OleDbCommand cmd1 = new OleDbCommand("INSERT INTO [MySheet1] ([DeptName],[Department],[Category1],[Category2]) VALUES(@value1, @value2, @value3, @value4)", cn);
                cmd1.Parameters.AddWithValue("@value1", row[0]);
                cmd1.Parameters.AddWithValue("@value2", row[1]);
                cmd1.Parameters.AddWithValue("@value3", row[2]);
                cmd1.Parameters.AddWithValue("@value4", row[3]);
                cmd1.ExecuteNonQuery();
            }
    

    Best Regards,

    Neil Hu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Gani tpt Friday, May 18, 2018 4:18 AM
    Thursday, May 17, 2018 8:55 AM
    Moderator

All replies

  • Use the group by clause in LINQ to do this if you're loading the data into memory.  You mentioned having a data table so I assume you mean DataTable.

    //Load your data into your data table
    var datatable = LoadData();
    
    var departments = from r in datatable.Rows.OfType<DataRow>()
                      group r by r["Department"] into g
                      select new { Department = g.Key, Data = g };
    
    foreach (var department in departments)
    {
       //Have access to the department and the rows that match it
    };

    What you do with the groups once have them is up to you. Questions related to Excel should be posted in the Excel forum where they can direct you on how to use the Excel API.


    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, May 16, 2018 2:34 PM
    Moderator
  • Hello Gani,

    More detailed info like below.

     static void Main(string[] args)
            {
                DataTable dt = LoadData();
    
                var departments = from r in dt.Rows.OfType<DataRow>()
                                  group r by r["Department"] into g
                                  select new { Department = g.Key, Data = g };
    
                foreach (var department in departments)
                {
                   // CreateExcel(department.Department);
    
                    string relativePath = string.Format(@"{0}.xls", department.Department);
                    string FullPath = AppDomain.CurrentDomain.BaseDirectory + relativePath;
                    string connectionString ="Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source=" +FullPath + ";Extended Properties='Excel 8.0;HDR=YES;'";
                    using (OleDbConnection cn = new OleDbConnection(connectionString))
                    {
                        cn.Open();
                        CreateDataTable(cn);
                        foreach (var row in department.Data)
                        {
                            insertExcel(cn, row);
                        }
                    }
                }
            }
    
            private static void CreateDataTable(OleDbConnection cn)
            {
                OleDbCommand cmd1 = new OleDbCommand("CREATE TABLE MySheet1 (DeptName char(255), Department char(255),Category1 char(255),Category2 char(255))", cn);
                cmd1.ExecuteNonQuery();
            }
    
            private static void insertExcel(OleDbConnection cn, DataRow row)
            {
    
                OleDbCommand cmd1 = new OleDbCommand("INSERT INTO [MySheet1] ([DeptName],[Department],[Category1],[Category2]) VALUES(@value1, @value2, @value3, @value4)", cn);
                cmd1.Parameters.AddWithValue("@value1", row[0]);
                cmd1.Parameters.AddWithValue("@value2", row[1]);
                cmd1.Parameters.AddWithValue("@value3", row[2]);
                cmd1.Parameters.AddWithValue("@value4", row[3]);
                cmd1.ExecuteNonQuery();
            }
    

    Best Regards,

    Neil Hu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Gani tpt Friday, May 18, 2018 4:18 AM
    Thursday, May 17, 2018 8:55 AM
    Moderator