How to Group by data rows from Data table and print different excel sheet

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#.
Ganesh STech Lead cum developerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MishaProgrammerCommented:
I assume, that you use EntityFramework and you already have collection of your data.
I recommend you touse NPOI library for working with Excel.
Try to use this code. There is creating a groups of data and writting data to Excel file in this code

using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using NPOI.HSSF.Model; 
using NPOI.HSSF.UserModel; 
using System.IO;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            List<Person> personList = new List<Person> {
                new Person(){ FirstName = "John", LastName = "Smith",Department = "E-101"},
                new Person(){ FirstName = "John1", LastName = "Smith1",Department = "E-101"},
                new Person(){ FirstName = "Jack", LastName = "Smith",Department = "E-102"},
                new Person(){ FirstName = "Jack1", LastName = "Smith1",Department = "E-102"},
                new Person(){ FirstName = "Sara", LastName = "Smith",Department = "E-103"},
                new Person(){ FirstName = "Sara1", LastName = "Smith1",Department = "E-103"}
            };

            var AllProups = personList.GroupBy(x => x.Department);
            foreach (var OneGroup in AllProups)
            {
                HSSFWorkbook excelFile = new HSSFWorkbook();
                HSSFSheet sheet1 = (HSSFSheet)excelFile.CreateSheet("sheet1");
                using (var file = new FileStream($"{OneGroup.Key}.xls", FileMode.Create, FileAccess.ReadWrite))
                {
                    int i = 0;
                    foreach (var personOne in OneGroup)
                    {
                        NPOI.SS.UserModel.IRow row = sheet1.CreateRow(i);
                        row.CreateCell(0).SetCellValue(personOne.FirstName);
                        row.CreateCell(1).SetCellValue(personOne.LastName);
                        i++;
                    }
                    excelFile.Write(file);
                    file.Close();
                }                
            }
            Console.ReadKey();
        }
    }

    public class Person
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Department { get; set; }
    }
}

Open in new window

0
Ganesh STech Lead cum developerAuthor Commented:
Installed dll NPOI.

But, unable to use the reference.

Error : type or namespace "NPOI" could not be found.

But, NPOI.dll is installed in bin folder...

????
0
MishaProgrammerCommented:
Use NuGet to install NPOI. It add NPOI and other dll`s  to your project.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Ganesh STech Lead cum developerAuthor Commented:
Syntax error is comming. pls. check the screenshot...
0
MishaProgrammerCommented:
If you post errors, it will be better, then you post only screenshot.

What .Net Framework do you use? I write this example in .Net Framework 4.7.1. It is C# 6.0
If you use early versions, use this code, to make file name:
using (var file = new FileStream( OneGroup.Key + ".xls", FileMode.Create, FileAccess.ReadWrite))

Open in new window


Did you correct install NPOI with NuGet packges? Did you put reference to NPOI if your code
using NPOI.HSSF.UserModel; 

Open in new window

0
Ganesh STech Lead cum developerAuthor Commented:
i am using .Net Framework 4.5.

Now i have only one error in "IRow"  ...?
0
Ganesh STech Lead cum developerAuthor Commented:
Also can you pls. give in  win form mode...so that i can find and confirm the excel where it will create and update...?
0
MishaProgrammerCommented:
Error in  "IRow"  appear, because you don`t correct install NPOI.
When I install NPOI in my project, I can see reference on
NPOI
NPOI.OOXML
NPOI.OpenXML4NET
NPOI.OpenXMLFormats
But you need only NPOI.

Windows form mode - you put code in button click event, for example, somethig like this.
I write come comments....
private void button1_Click(object sender, EventArgs e)
        {
            List<Person> personList = new List<Person> {
                new Person(){ FirstName = "John", LastName = "Smith",Department = "E-101"},
                new Person(){ FirstName = "John1", LastName = "Smith1",Department = "E-101"},
                new Person(){ FirstName = "Jack", LastName = "Smith",Department = "E-102"},
                new Person(){ FirstName = "Jack1", LastName = "Smith1",Department = "E-102"},
                new Person(){ FirstName = "Sara", LastName = "Smith",Department = "E-103"},
                new Person(){ FirstName = "Sara1", LastName = "Smith1",Department = "E-103"}
            };

            var AllProups = personList.GroupBy(x => x.Department); // create groups by depertment names
            foreach (var OneGroup in AllProups) // loop by all groups
            {
                HSSFWorkbook excelFile = new HSSFWorkbook(); // create NPOI class object, which work with excel
                HSSFSheet sheet1 = (HSSFSheet)excelFile.CreateSheet("sheet1"); // create new sheet in your excel file
                using (var file = new FileStream(OneGroup.Key + ".xls", FileMode.Create, FileAccess.ReadWrite)) 
                    // create new excel file and open it to read and write
                {
                    int i = 0;
                    foreach (var personOne in OneGroup) // loop in each group
                    {
                        NPOI.SS.UserModel.IRow row = sheet1.CreateRow(i);// create new row for each person
                        row.CreateCell(0).SetCellValue(personOne.FirstName); // write FirstName in the first cell of current row
                        row.CreateCell(1).SetCellValue(personOne.LastName);  // write LastName in the second cell of current row
                        i++;
                    }
                    excelFile.Write(file); // write data to file
                    file.Close(); // close file
                }
            }
        }

Open in new window

0
Ganesh STech Lead cum developerAuthor Commented:
Thanks. i cleared the error. every thing is perfect except header.

I have column header in my datatable. When i grouped and moved to different excel, then it should come along with the header.

How do we call the datatable header in three different excel...

Note : Also, i have "n" number of columns (more than 100 columns) in my datatable. how do we call or read all the columns dynamically instead of calling one by one like below (This is important).
 row.CreateCell(0).SetCellValue(personOne.FirstName); // write FirstName in the first cell of current row
 row.CreateCell(1).SetCellValue(personOne.LastName);


If this is Ok, then mine is perfect...
0
MishaProgrammerCommented:
There is no simple code to export data from DataTable to Excel file with NPOI by one command or method. Only loop by rows and columns.
As I wrote early, I assumed that you use EntityFramework.
I add code to export headers (column names to Execel)
            DataTable dt = new DataTable(); // It will be your data table with your data!
            HSSFWorkbook excelFile = new HSSFWorkbook();
            HSSFSheet sheet1 = (HSSFSheet)excelFile.CreateSheet("sheet1");
            using (var file = new FileStream(OneGroup.Key + ".xls", FileMode.Create, FileAccess.ReadWrite))
            {
                //make a header row 
                        NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        NPOI.SS.UserModel.ICell cell = row1.CreateCell(j);
                        string columnName = dt.Columns[j].ToString();
                        cell.SetCellValue(columnName);
                    }
                    //loops through data 
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        NPOI.SS.UserModel.IRow row = sheet1.CreateRow(i + 1);
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            NPOI.SS.UserModel.ICell cell = row.CreateCell(j);
                            string columnName = dt.Columns[j].ToString();
                            cell.SetCellValue(dt.Rows[i][columnName].ToString());
                        }
                    }
                    excelFile.Write(file);
                    file.Close();
            }

Open in new window



If you especially want to use one method to export, you can use GemBox library (not forget to install in NuGet) to simple export.

There is some example for this library, Also you can adapted it for your purpose:
using System.Data;
using System.Text;
using GemBox.Spreadsheet;

class Sample
{
    [STAThread]
    static void Main(string[] args)
    {
        // If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        ExcelFile ef = new ExcelFile();
        ExcelWorksheet ws = ef.Worksheets.Add("DataTable to Sheet");

        DataTable dt = new DataTable();

        dt.Columns.Add("ID", typeof(int));
        dt.Columns.Add("FirstName", typeof(string));
        dt.Columns.Add("LastName", typeof(string));

        dt.Rows.Add(new object[] { 100, "John", "Doe" });
        dt.Rows.Add(new object[] { 101, "Fred", "Nurk" });
        dt.Rows.Add(new object[] { 103, "Hans", "Meier" });
        dt.Rows.Add(new object[] { 104, "Ivan", "Horvat" });
        dt.Rows.Add(new object[] { 105, "Jean", "Dupont" });
        dt.Rows.Add(new object[] { 106, "Mario", "Rossi" });

        ws.Cells[0, 0].Value = "DataTable insert example:";

        // Insert DataTable into an Excel worksheet.
        ws.InsertDataTable(dt,
            new InsertDataTableOptions()
            {
                ColumnHeaders = true,
                StartRow = 2
            });

        ef.Save("DataTable to Sheet.xlsx");
    }
}

Open in new window

0
Ganesh STech Lead cum developerAuthor Commented:
Column name has come.

But, Excel shows all the records. it suppose to be displayed only group by records @ every excel..

??????
0
MishaProgrammerCommented:
Do you ask about my example with GemBox? It is only example use
ws.InsertDataTable(dt,
            new InsertDataTableOptions()
            {
                ColumnHeaders = true,
                StartRow = 2
            });

Open in new window


As you ask about NPOI, I thought, you already adapt early code =)
 DataTable dt = new DataTable(); // It will be your data table with your data!
            dt.Clear();
            dt.Columns.Add("FirstName");
            dt.Columns.Add("LastName");
            dt.Columns.Add("Department");

            DataRow row1 = dt.NewRow();
            row1["FirstName"] = "John";
            row1["LastName"] = "Smith";
            row1["Department"] = "E-101";
            dt.Rows.Add(row1);

            DataRow row2 = dt.NewRow();
            row2["FirstName"] = "John1";
            row2["LastName"] = "Smith1";
            row2["Department"] = "E-101";
            dt.Rows.Add(row2);

            DataRow row3 = dt.NewRow();
            row3["FirstName"] = "Jack";
            row3["LastName"] = "Smith";
            row3["Department"] = "E-102";
            dt.Rows.Add(row3);

            DataRow row4 = dt.NewRow();
            row4["FirstName"] = "Jack2";
            row4["LastName"] = "Smith2";
            row4["Department"] = "E-102";
            dt.Rows.Add(row4);

            DataRow row5 = dt.NewRow();
            row5["FirstName"] = "Sara";
            row5["LastName"] = "Smith";
            row5["Department"] = "E-103";
            dt.Rows.Add(row5);

            DataRow row6 = dt.NewRow();
            row6["FirstName"] = "Sara1";
            row6["LastName"] = "Smith1";
            row6["Department"] = "E-103";
            dt.Rows.Add(row6);

            var  AllProups = dt.AsEnumerable().GroupBy(x => x["Department"]).
                Select(g=> g.OrderBy(x=> x["FirstName"]).CopyToDataTable());
            foreach (var OneGroup in AllProups)
            {
                HSSFWorkbook excelFile = new HSSFWorkbook();
                HSSFSheet sheet1 = (HSSFSheet)excelFile.CreateSheet("sheet1");
                using (var file = new FileStream(OneGroup.Rows[0]["Department"] + ".xls", FileMode.Create, FileAccess.ReadWrite))
                {
                    //make a header row 
                    NPOI.SS.UserModel.IRow rowOne = sheet1.CreateRow(0);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        NPOI.SS.UserModel.ICell cell = rowOne.CreateCell(j);
                        string columnName = dt.Columns[j].ToString();
                        cell.SetCellValue(columnName);
                    }
                    int i = 0;
                    foreach (DataRow rr in OneGroup.Rows)
                    {
                        NPOI.SS.UserModel.IRow row = sheet1.CreateRow(i + 1);
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            NPOI.SS.UserModel.ICell cell = row.CreateCell(j);
                            string columnName = dt.Columns[j].ToString();
                            cell.SetCellValue(rr[columnName].ToString());
                        }
                        i++;
                    }
                    excelFile.Write(file);
                    file.Close();
                }
            }

Open in new window


Try this for DataTable
0
Ganesh STech Lead cum developerAuthor Commented:
No words to say..It's excellent and working Fine.

Thanks a lot.

can you give some sample's for, how to make colourful header using NPOI syntax.?

or else

Can you provide the link to learn NPOI cells header, colour option and others...?
0
MishaProgrammerCommented:
There is example with code about cell style.
 
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
,,,,,,,,,,,,,,,,,,,,,
using (var file = new FileStream(OneGroup.Rows[0]["Department"] + ".xls", FileMode.Create, FileAccess.ReadWrite))
                {
                    //make a header row 
                    NPOI.SS.UserModel.IRow rowOne = sheet1.CreateRow(0);
                    HSSFCellStyle styleHeader = (HSSFCellStyle)excelFile.CreateCellStyle(); // create style

                    // cell background
                    styleHeader.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Green.Index; // background color
                    styleHeader.FillPattern = FillPattern.SolidForeground; // background pattern

                    // font color
                    HSSFFont font1 = (HSSFFont)excelFile.CreateFont(); // text font
                    font1.Color = NPOI.HSSF.Util.HSSFColor.Yellow.Index; // color
                    styleHeader.SetFont(font1);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        NPOI.SS.UserModel.ICell cell = rowOne.CreateCell(j);
                        string columnName = dt.Columns[j].ToString();
                        cell.SetCellValue(columnName);
                        cell.CellStyle = styleHeader; // set style to each header cell
                    }
,,,,,,,,,,,,

Open in new window


Site of NPOI
https://archive.codeplex.com/?p=npoi
Also you can see example of set style to cell on this link:
http://www.zachhunter.com/2010/05/getting-started-with-npoi/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ganesh STech Lead cum developerAuthor Commented:
Excellent Solution..
0
MishaProgrammerCommented:
Thank you! Glad to help you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.