Using foreach loop get data and save into excel file

Question

1.00/5 (1 vote)

See more:

In table record as follows

SQL

Select * from  Employee

Cityid AssName  ProjectName   Mobile    Source       Destination
  1     Rakesh    Java        987445     TK            BLR
  1     Suresh    Dotnet      884554     RM            BTP
  1     Vignesh   Testing     451211     RP            KOL
  1     Suresh    Mainframe   457845     RF            KOL

  2     Ramesh    Animation   454542     JS            KOC
  2     Magesh    Warehouse   211455     WH            KOC
  2     Santhosh  Database    445545     RO            CHN
  2     Vignesh   ETLTool     154555     VJ            CHN

console application code as follows

i am displaying the above data from table Employee(Database) in to excel file using console application.

My console application code as follows

C#

      string connectionstring = "Server=(local);initial           catalog=OneC;Trusted_Connection=True";
      SqlConnection con = new SqlConnection(connectionstring);
      SqlCommand command= new SqlCommand();
      SqlDataReader dr;
      DataSet ds= new DataSet();
      command.CommandText = "Select * from  Employee";
      command.CommandType = CommandType.Text;
      command.Connection = con;
      con.Open();
      dr= cmd.ExecuteReader();
       if (dr.HasRows)
       {

using (System.IO.StreamWriter sw = new System.IO.StreamWriter(@"C:\Details\Excel.xls"))
        {
           while (dr.Read())
               {
                  for (int i = 0; i < dr.FieldCount; i++)
                   {
                        sw.Autoflush = true;
                        sw.write(dr[i].Tostring() + "\t");
                   }
                         sw,writeline("\n");
               }
        }
                          con.Close();
       }

When i exeucte above query in my system in c folder under the folder details excel file is created as follows

in my system C Folder created as details in that details folder excel file is created a

1     Rakesh    Java        987445     TK            BLR
1     Suresh    Dotnet      884554     RM            BTP
1     Vignesh   Testing     451211     RP            KOL
1     Suresh    Mainframe   457845     RF            KOL

2     Ramesh    Animation   454542     JS            KOC
2     Magesh    Warehouse   211455     WH            KOC
2     Santhosh  Database    445545     RO            CHN
2     Vignesh   ETLTool     154555     VJ            CHN

But i want excel file to be saved based on city id. The city id 1 details to be saved in one excel

And another city id 2 details to be saved in another excel

for that how can i do in asp.net using my above code in console application

I want City id 1 details to be saved in one excel as follows

1     Rakesh    Java        987445     TK            BLR
1     Suresh    Dotnet      884554     RM            BTP
1     Vignesh   Testing     451211     RP            KOL
1     Suresh    Mainframe   457845     RF            KOL

I want City id 2 details to be saved in another excel as follows

2     Ramesh    Animation   454542     JS            KOC
2     Magesh    Warehouse   211455     WH            KOC
2     Santhosh  Database    445545     RO            CHN
2     Vignesh   ETLTool     154555     VJ            CHN

What I have tried:

i want to display data into based on Cityid in different excel

I want City id 1 details to be saved in one excel as follows

1 Rakesh Java 987445 TK BLR
1 Suresh Dotnet 884554 RM BTP
1 Vignesh Testing 451211 RP KOL
1 Suresh Mainframe 457845 RF KOL

I want City id 2 details to be saved in another excel as follows

2 Ramesh Animation 454542 JS KOC
2 Magesh Warehouse 211455 WH KOC
2 Santhosh Database 445545 RO CHN
2 Vignesh ETLTool 154555 VJ CHN

Posted 13-Aug-16 3:43am

sudhakarthikeyan

Updated 15-Aug-16 0:17am

Patrice T

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2016-08-13T05:17:00

Firstly you are not writing an Excel file, you are writing text. If you want the output to be in a format that Excel will load directly then you should write it as CSV (fields separated by commas), or use OLEDB to create a proper Excel format file: see Working with MS Excel(xls / xlsx) Using MDAC and Oledb[^]. Using the second option you can easily create different files or different worksheets in one file.

Maciej Los · Answer 2 · 2016-08-15T00:17:00

Tons of examples is available here: Search[^]
An author of first referenced article is using very fast CopyFromRecordset method to write data into Excel file.

The base idea is to load data into DataTable object, then to filter data using Linq and export datasets into different Excel sheets.

A step-by-step guide:

Connect to the database using SqlConnection[^]
Create SqlCommand[^]
Use SqlDataReader[^] to read the data
Load data into DataTable[^] object
Filter DataTable object on CityId field via using Linq To DataSet[^]
LINQ to DataSet Examples[^]
Loop through the resultset and write data into new Excel workbook
Workbooks.Add method (Microsoft.Office.Interop.Excel)[^]
How to: Use COM Interop to Create an Excel Spreadsheet (C#)[^]
How to: Access Office Interop Objects by Using Visual C# Features (C# Programming Guide)[^]

C#

//get unique CityId from DataTable 
//dt is variable type of DataTable
var uniquecities = dt.AsEnumerable().Select(x=> x.Field<int>("CityId")).Distinct().ToList();
//loop through the res
foreach(var c in uniquecities)
{
    //get the data for single CityId 
    var mydata = dt.AsEnumerable().Where(x => x.Field<int>("CityId")==c).ToList();
    //create new workbook
    Excel.Workbook wbk = ExcelInstance.Workbooks.Add();
    Excel.Worksheet wsh = wbk.Worksheets[1];
    //loop through the resultset  
    foreach(d in mydata)
    {
        //your method to write data into Excel sheet!
    }
}
</int></int>

Using foreach loop get data and save into excel file

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0