java+dataset+foreach,使用foreach循环获取数据并保存到excel文件中

In table record as followsSelect * 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

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

解决方案

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.

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:

//get unique CityId from DataTable

//dt is variable type of DataTable

var uniquecities = dt.AsEnumerable().Select(x=> x.Field("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("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!

}

}