The company that I am working for requires their inventory to be exported to an excel sheet on a regular basis. Thankfully it is less than 50k items in total, but with how I am currently exporting, it is taking an absurd amount of time. The items are split into three different tables, but I think my issue comes from having to open multiple connections per item to get information such as converting brandID to brandName, or locationID to locationName.
我工作的公司要求他们的库存定期出口到excel表。值得庆幸的是，它总共不到5万件，但是我目前的出口方式却是荒谬的。这些项目分为三个不同的表格，但我认为我的问题来自于必须为每个项目打开多个连接以获取诸如将brandID转换为brandName或locationID转换为locationName等信息。
Note that all code is writen in c#. Here is the current method that I am using to start the exporting:
请注意，所有代码都是用c＃编写的。以下是我用于开始导出的当前方法：
public void exportAllItems()
{
exportTable = new System.Data.DataTable();
exportTable.Columns.Add("Vendor", typeof(string));
exportTable.Columns.Add("Store_ID", typeof(string));
exportTable.Columns.Add("ItemNumber", typeof(string));
exportTable.Columns.Add("Shipment_Date", typeof(string));
exportTable.Columns.Add("Brand", typeof(string));
exportTable.Columns.Add("Model", typeof(string));
exportTable.Columns.Add("Club_Type", typeof(string));
exportTable.Columns.Add("Shaft", typeof(string));
exportTable.Columns.Add("Number_of_Clubs", typeof(string));
exportTable.Columns.Add("Tradein_Price", typeof(double));
exportTable.Columns.Add("Premium", typeof(double));
exportTable.Columns.Add("WE PAY", typeof(double));
exportTable.Columns.Add("QUANTITY", typeof(int));
exportTable.Columns.Add("Ext'd Price", typeof(double));
exportTable.Columns.Add("RetailPrice", typeof(double));
exportTable.Columns.Add("Comments", typeof(string));
exportTable.Columns.Add("Image", typeof(string));
exportTable.Columns.Add("Club_Spec", typeof(string));
exportTable.Columns.Add("Shaft_Spec", typeof(string));
exportTable.Columns.Add("Shaft_Flex", typeof(string));
exportTable.Columns.Add("Dexterity", typeof(string));
exportTable.Columns.Add("Destination", typeof(string));
exportTable.Columns.Add("Received", typeof(string));
exportTable.Columns.Add("Paid", typeof(string));
exportAllAdd_Clubs();
exportAllAdd_Accessories();
exportAllAdd_Clothing();
DataColumnCollection dcCollection = exportTable.Columns;
// Export Data into EXCEL Sheet
Application ExcelApp = new Application();
ExcelApp.Application.Workbooks.Add(Type.Missing);
for (int i = 1; i < exportTable.Rows.Count + 2; i++)
{
for (int j = 1; j < exportTable.Columns.Count + 1; j++)
{
if (i == 1)
{
ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString();
}
else
ExcelApp.Cells[i, j] = exportTable.Rows[i - 2][j -
1].ToString();
}
}
//Get users profile, downloads folder path, and save to workstation
string pathUser =
Environment.GetFolderPath(Environment.SpecialFolder.UserProfile);
string pathDownload = Path.Combine(pathUser, "Downloads");
ExcelApp.ActiveWorkbook.SaveCopyAs(pathDownload + "\\TotalInventory-
" + DateTime.Now.ToString("d MMM yyyy") + ".xlsx");
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
}
Due to the requirements of the company, the excel sheet needs to have headers, and the same information as the datatable that is created above. After the creation of the exportTable, I call three other methods that go through each item table. I'll show just one of them because they are mostly the same:
由于公司的要求，excel表需要具有标题，以及与上面创建的数据表相同的信息。在创建了exportTable之后，我调用了其他三个遍历每个item表的方法。我只展示其中一个因为它们大致相同：
//Puts the clubs in the export table
public void exportAllAdd_Clubs()
{
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "Select * from tbl_clubs";
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
exportTable.Rows.Add("",
(lm.locationName(Convert.ToInt32(reader["locationID"]))).ToString(),
(Convert.ToInt32(reader["sku"])).ToString(),
"", idu.brandType(Convert.ToInt32(reader["brandID"])),
idu.modelType(Convert.ToInt32(reader["brandID"])),
reader["clubType"].ToString(),
reader["shaft"].ToString(),
reader["numberOfClubs"].ToString(), 0, Convert.ToDouble(reader["premium"]),
Convert.ToDouble(reader["cost"]),
Convert.ToInt32(reader["quantity"]), 0,
Convert.ToDouble(reader["price"]), reader["comments"].ToString(), "",
reader["clubSpec"].ToString(),
reader["shaftSpec"].ToString(),
reader["shaftFlex"].ToString(), reader["dexterity"].ToString(), "", "", "");
}
conn.Close();
}
Inside of the above method, I have to make three calls to different methods to convert the data to a meaningful string. One for the brand, model, and location.
在上面的方法中，我必须对不同的方法进行三次调用，以将数据转换为有意义的字符串。一个用于品牌，型号和位置。
I am open to all sorts of ideas on how to speed this up or properly go about bulk exporting.
我对如何提高速度或适当地进行批量出口的各种想法持开放态度。
Thank you for your time!
感谢您的时间！
Update
更新
After messing around with EPPlus, my export times were still taking way too long. I think it has to do with how I am going through each row in the database and exporting them one at a time. Still trying to find a way to do it differently.
在搞乱EPPlus后，我的出口时间仍然太长。我认为这与我如何浏览数据库中的每一行并一次导出一行有关。仍在努力寻找一种不同的方式。
1 个解决方案
#1
0
I figured out what the problem was:
我弄清楚问题是什么：
Instead of having one giant query with sub queries, I was doing multiple queries per item which was taking way too much time. After remembering that I could do sub queries, and implementing the changes, the export time dropped from over 30 minutes to just under 5 seconds.
我没有对子查询进行一次巨型查询，而是每个项目进行多次查询，这花费了太多时间。在记住我可以进行子查询并实现更改之后，导出时间从超过30分钟下降到不到5秒。
#1
0
I figured out what the problem was:
我弄清楚问题是什么：
Instead of having one giant query with sub queries, I was doing multiple queries per item which was taking way too much time. After remembering that I could do sub queries, and implementing the changes, the export time dropped from over 30 minutes to just under 5 seconds.
我没有对子查询进行一次巨型查询，而是每个项目进行多次查询，这花费了太多时间。在记住我可以进行子查询并实现更改之后，导出时间从超过30分钟下降到不到5秒。