Sometimes you may need to Export Grid View data into Excel Sheet. Using following code u can do that.
using System.IO;
//------------------------- SAVE FILE OPTION -----------------------------
string fileName = "";
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.CheckPathExists = true;
saveFileDialog.AddExtension = true;
saveFileDialog.ValidateNames = true;
saveFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
saveFileDialog.DefaultExt = ".xls";
saveFileDialog.Filter = "Microsoft Excel Workbook (*.xls)|*.xls";
//------------------------------------------------------------------------
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
fileName = saveFileDialog.FileName;
Excel.ApplicationClass ExcelApp = new Excel.ApplicationClass();
ExcelApp.Application.Workbooks.Add(Type.Missing);
int rowCount = dataGridView1.Rows.Count;
int colCount = dataGridView1.Columns.Count;
// Storing header part in Excel
for (int i = 1; i < colCount + 1; i++)
{
ExcelApp.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
// Storing Each row and column value to excel sheet
for (int i = 0; i < rowCount; i++)
{
this.Text = "Processing: " + dataGridView1.Rows[i].Cells[0].Value.ToString();
for (int j = 0; j < colCount; j++)
{
ExcelApp.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
string fileLocation = fileName;
if (File.Exists(fileLocation))
{
try
{
File.Delete(fileLocation);
ExcelApp.ActiveWorkbook.SaveCopyAs(fileLocation);
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
MessageBox.Show("Excel file created-- " + fileLocation);
}
catch (IOException ioe)
{
MessageBox.Show("Close the Excel file and Export again...");
}
}
else
{
ExcelApp.ActiveWorkbook.SaveCopyAs(fileLocation);
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
MessageBox.Show("Excel file created-- " + fileLocation);
}
}
You may need to 'ADD' Office reference into your project. By following steps you can add MS office reference.
First click Project -> Add Reference
Then Go to COM Tab and select Microsoft Excel 11.0 Object Library[or what you have in your PC]
Click OK.
Again Go to Add Reference -> COM Tab and select Microsoft Office 11.0 Object Library and click OK.
