使用foreach循环获取数据并保存到excel文件中 [英] Using foreach loop get data and save into excel file
问题描述
在表格记录中如下
In table record as follows
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
控制台应用程序代码如下
i使用控制台应用程序将表Employee（Database）中的上述数据显示到excel文件中。 />
我的控制台应用程序代码如下
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();
}
当我在我的系统中执行以上查询时，在c文件夹下的文件夹详细信息excel文件是创建如下
在我的系统中C文件夹创建为详细信息文件夹excel文件中的详细信息创建了一个
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
但是我希望根据城市ID保存excel文件。城市id 1详细信息保存在一个excel
和另一个城市id 2详细信息保存在另一个excel
为什么我在asp.net中使用上面的代码在控制台应用程序中执行
我想要城市ID 1详细信息保存在一个excel中如下
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
我想要城市id 2详细信息将保存在另一个excel中如下
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
我尝试过：
i希望根据Cityid以不同的excel显示数据
我希望City id 1详细信息保存在一个excel中如下
1 Rakesh Java 987445 TK BLR
1 Suresh Dotnet 884554 RM BTP
1 Vignesh测试451211 RP KOL
1 Suresh Mainframe 457845 RF KOL
我希望City id 2的详细信息保存在另一个excel中如下
2 Ramesh动画454542 JS KOC
2 Magesh仓库211455 WH KOC
2 Santhosh数据库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
推荐答案
首先你不是在写一个Excel文件，你正在写文字。如果您希望输出采用Excel将直接加载的格式，则应将其写为CSV（以逗号分隔的字段），或使用OLEDB创建正确的Excel格式文件：请参阅使用MS Excel（xls / xlsx）使用MDAC和Oledb [< a href =http://www.codeproject.com/Articles/37055/Working-with-MS-Excel-xls-xlsx-Using-MDAC-and-Oledtarget =_ blanktitle =New Window> ^ 。使用第二个选项，您可以轻松地在一个文件中创建不同的文件或不同的工作表。
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.
这里有大量示例： Search [ ^ ]
首次引用文章的作者使用非常快的CopyFromRecordset方法将数据写入Excel文件。
基本思路是将数据加载到DataTable对象中，然后使用Linq过滤数据并将数据集导出到不同的Excel工作表中。
分步指南：
Tons of examples is available here: Search[^]
An author of first referenced article is using very fastCopyFromRecordsetmethod to write data into Excel file.
The base idea is to load data intoDataTableobject, then to filter data using Linq and export datasets into different Excel sheets.
A step-by-step guide:
- 使用SqlConnection [ ^ ]
- 创建 SqlCommand [ ^ ]
- 使用 SqlDataReader [ ^ ]读取数据
- 将数据加载到DataTable [ ^ ] object
- 过滤DataTable对象使用
CityId字段Linq To DataSet [ ^ ]
LINQ到DataSet示例 [ ^ ] - 循环结果集并将数据写入新的Excel工作簿
Workbooks.Add方法（Microsoft.Office.Interop.Excel） [ ^ ]
< a href =https://msdn.microsoft.com/en-us/library/ms173186(v=vs.80).aspx>如何：使用COM Interop创建Excel Spr eadsheet（C＃） [ ^ ]
如何：访问Office互操作对象使用Visual C＃功能（C＃编程指南） [ ^ ]
- Connect to the database using SqlConnection[^]
- Create SqlCommand[^]
- Use SqlDataReader[^] to read the data
- Load data into DataTable[^] object
- Filter DataTable object on
CityIdfield 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)[^]
//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>
这篇关于使用foreach循环获取数据并保存到excel文件中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
