large datagridview export to excel
im attempting to export the datagridview to excel(.xls) in a winforms application using visual studio 2010 in C#, the problem being it is taking forever to save, so far i have 4220 rows and 20 columns. Is there a faster way to do this. NOTE: I am populating the datagridview from the saved excel file. I appreciate your help....my save code is as follows:
private void btnSave_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
// Get the Header from dataGridView
for (int h = 1; h < dataGridView1.Columns.Count + 1; h++)
{
xlWorkSheet.Cells[1, h] = dataGridView1.Columns[h - 1].HeaderText;
}
// Get the Cell Values
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
DataGridViewCell cell = dataGridView1[j, i];
xlWorkSheet.Cells[i + 2, j + 1] = cell.Value;
}
}
//xlWorkBook.SaveCopyAs("FORM TEST.xlsx");
xlWorkBook.SaveAs("GB STOCK.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
xlApp = null;
xlWorkBook = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
After having read through various searches and through the answers above i came across this code, it works extremely faster (almost instantaneous) as compared with my orginal where it took just under 2 minutes. I am very grateful for your answers above and i will look into these especially the copy and paste method which was an interesting read. I am a relatively new to this (new hobby) and am only beginning to understand some concepts with regards to exporting datasets etc. I know this is by no means the best way of acheiving what i set out to but it does what i want it to at the moment. Once again thanks to all who have helped, i am learning alot.
int cols;
//open file
StreamWriter wr = new StreamWriter("GB STOCK.csv", false, Encoding.UTF8);
//determine the number of columns and write columns to file
cols = dgvStock.Columns.Count;
for (int i = 0; i < cols; i++)
{
wr.Write(dgvStock.Columns[i].Name.ToString().ToUpper() + ",");
}
wr.WriteLine();
//write rows to excel file
for (int i = 0; i < (dgvStock.Rows.Count); i++)
{
for (int j = 0; j < cols; j++)
{
if (dgvStock.Rows[i].Cells[j].Value != null)
{
wr.Write(dgvStock.Rows[i].Cells[j].Value + ",");
}
else
{
wr.Write(",");
}
}
wr.WriteLine();
}
//close file
wr.Close();
You want to try to minimize the number of calls you make between your .NET code and the Excel process - those are very slow to execute, so populating cell-by-cell takes a long time.
Better to put the contents of your grid into an array: you can then dump that to the Excel sheet in a single operation.
Write Array to Excel Range
Using interop to copy cell by cell is very slow. I would suggest using copy paste instead. See this msdn article for an example of how to copy the data to the clipboard; you can then use interop to paste the values into the spreadsheet.
链接地址: http://www.djcxy.com/p/36772.html