user1632718 user1632718 - 1 year ago 98
C# Question

Simple way to Export DataGridView to Excel

I am trying to copy DataGridView data to Excel and I am using this code:

public static void ExportToExcel(DataGridView dgView)
{
Microsoft.Office.Interop.Excel.Application excelApp = null;

try
{
// instantiating the excel application class
object misValue = System.Reflection.Missing.Value;
excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook currentWorkbook = excelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet currentWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)currentWorkbook.ActiveSheet;

currentWorksheet.Columns.ColumnWidth = 18;

if (dgView.Rows.Count > 0)
{
currentWorksheet.Cells[1, 1] = DateTime.Now.ToString("s");
int i = 1;

foreach (DataGridViewColumn dgviewColumn in dgView.Columns)
{
// Excel work sheet indexing starts with 1
currentWorksheet.Cells[2, i] = dgviewColumn.Name;
++i;
}

Microsoft.Office.Interop.Excel.Range headerColumnRange = currentWorksheet.get_Range("A2", "G2");
headerColumnRange.Font.Bold = true;
headerColumnRange.Font.Color = 0xFF0000;

//headerColumnRange.EntireColumn.AutoFit();
int rowIndex = 0;

for (rowIndex = 0; rowIndex < dgView.Rows.Count; rowIndex++)
{
DataGridViewRow dgRow = dgView.Rows[rowIndex];

for (int cellIndex = 0; cellIndex < dgRow.Cells.Count; cellIndex++)
{
currentWorksheet.Cells[rowIndex + 3, cellIndex + 1] = dgRow.Cells[cellIndex].Value;
}
}

Microsoft.Office.Interop.Excel.Range fullTextRange = currentWorksheet.get_Range("A1", "G" + (rowIndex + 1).ToString());
fullTextRange.WrapText = true;
fullTextRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
}
else
{
string timeStamp = DateTime.Now.ToString("s");
timeStamp = timeStamp.Replace(':', '-');
timeStamp = timeStamp.Replace("T", "__");
currentWorksheet.Cells[1, 1] = timeStamp;
currentWorksheet.Cells[1, 2] = "No error occured";
}

using (SaveFileDialog exportSaveFileDialog = new SaveFileDialog())
{
exportSaveFileDialog.Title = "Select Excel File";
exportSaveFileDialog.Filter = "Microsoft Office Excel Workbook(*.xlsx)|*.xlsx";

if (DialogResult.OK == exportSaveFileDialog.ShowDialog())
{
string fullFileName = exportSaveFileDialog.FileName;
// currentWorkbook.SaveCopyAs(fullFileName);
// indicating that we already saved the workbook, otherwise call to Quit() will pop up
// the save file dialogue box

currentWorkbook.SaveAs(fullFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, System.Reflection.Missing.Value, misValue, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, misValue, misValue, misValue);
currentWorkbook.Saved = true;
MessageBox.Show("Exported successfully", "Exported to Excel", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (excelApp != null)
{
excelApp.Quit();
}
}
}


But as there is over 200 000 records, its taking a long time to export. Is there a faster way to do this?

Answer Source

Try this code. It's faster than the normal interop methods, also it converts into CSV which can be read easily by excel.

int cols;
//open file 
StreamWriter wr = new StreamWriter("GB STOCK.csv");

//determine the number of columns and write columns to file 
cols = dgvStock.Columns.Count;
for (int i = 0; i < cols - 1; i++)
{ 
    wr.Write(dgvStock.Columns[i].Name.ToString().ToUpper() + ",");
} 
wr.WriteLine();

//write rows to excel file
for (int i = 0; i < (dgvStock.Rows.Count - 1); 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();