Click here to Skip to main content
15,907,492 members
Search within:


Error: To export datagridview to excel file in C#
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
C#
Visual-Studio
Hi all,

Using VS 2008, c# windows forms.

I've been trying to export my datagridview to an excel file. But the excel file has only the first data on the gridview. I've already add the reference which is Microsoft.Office.Interop.Excel.

Here's my code on my Export button:

C#
using Microsoft.Office.Interop; 

Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            ExcelApp.Application.Workbooks.Add(Type.Missing);
            ExcelApp.Columns.ColumnWidth = 10;
            
          for (int i = 0; i < dataGridView2.Rows.Count; i++)
            {
                DataGridViewRow row = dataGridView2.Rows[i];
                for (int j = 0; j < row.Cells.Count; j++)
                {
                    ExcelApp.Cells[i + 1, j + 1] = row.Cells[j].ToString();
                }
            }
            ExcelApp.ActiveWorkbook.SaveCopyAs("C:\\reports.xls");
            ExcelApp.ActiveWorkbook.Saved = true;
            ExcelApp.Quit();
            MessageBox.Show("Excel file created,you can find the file C:\\reports.xls");


Thanks for the reply.
Posted
Updated 19-Jan-12 21:26pm
v2
Add a Solution

4 solutions

Hi use below code
add namespace

using Microsoft.Office.Interop.Excel;
 
//First fetch all records from grid to dataset
DataSet dset = new DataSet();
dset.Tables.Add();

for (int i = 0; i < grdBothPrintXLFull.Columns.Count; i++)
                {
                    dset.Tables[0].Columns.Add(grdBothPrintXLFull.Columns[i].HeaderText);
                }

//add rows to the table
System.Data.DataRow dr1;
                for (int i = 0; i < grdBothPrintXLFull.Rows.Count; i++)
                {
                    dr1 = dset.Tables[0].NewRow();

                    //for (int j = 0; j < grdMainPrintXLFull.Columns.Count; j++)
                    //{
                    System.Web.UI.WebControls.Label lblCCName = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblCCName");
                    System.Web.UI.WebControls.Label lblItemName = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblItemName");
                    System.Web.UI.WebControls.Label lblItemCode = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblItemCode");
                    System.Web.UI.WebControls.Label lblItemDesc = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblItemDesc");
                    System.Web.UI.WebControls.Label lblBINNo = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblBINNo");
                    System.Web.UI.WebControls.Label lblItemNature = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblItemNature");
                    System.Web.UI.WebControls.Label lblUOM = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblUOM");
                    System.Web.UI.WebControls.Label lblOPBAL = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblOPBAL");
                    System.Web.UI.WebControls.Label lblInQty = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblInQty");
                    System.Web.UI.WebControls.Label lblOutQty = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblOutQty");
                    System.Web.UI.WebControls.Label lblBal = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblBal");
                    System.Web.UI.WebControls.Label lblMinStkLvl = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblMinStkLvl");
                    System.Web.UI.WebControls.Label lblSecUOM = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblSecUOM");
                    System.Web.UI.WebControls.Label lblSecBal = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblSecBal");
                    System.Web.UI.WebControls.Label lblRate = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblRate");
                    System.Web.UI.WebControls.Label lblStockValue = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblStockValue");

                    dr1[0] = lblCCName.Text.ToString();
                    dr1[1] = lblItemName.Text.ToString();
                    dr1[2] = lblItemCode.Text.ToString();
                    dr1[3] = lblItemDesc.Text.ToString();
                    dr1[4] = lblBINNo.Text.ToString();
                    dr1[5] = lblItemNature.Text.ToString();
                    dr1[6] = lblUOM.Text.ToString();
                    dr1[7] = lblOPBAL.Text.ToString();
                    dr1[8] = lblInQty.Text.ToString();
                    dr1[9] = lblOutQty.Text.ToString();
                    dr1[10] = lblBal.Text.ToString();
                    dr1[11] = lblMinStkLvl.Text.ToString();
                    dr1[12] = lblSecUOM.Text.ToString();
                    dr1[13] = lblSecBal.Text.ToString();
                    dr1[14] = lblRate.Text.ToString();
                    dr1[15] = lblStockValue.Text.ToString();
                    dset.Tables[0].Rows.Add(dr1);
                }
                dr1 = dset.Tables[0].NewRow();
                dr1[0] = "";
                dr1[1] = "";
                dr1[2] = "";
                dr1[3] = "";
                dr1[4] = "";
                dr1[5] = "";
                dr1[6] = "";
                dr1[7] = "";
                dr1[8] = "";
                dr1[9] = "";
                dr1[10] = "";
                dr1[11] = "";
                dr1[12] = "";
                dr1[13] = "";
                dr1[14] = "Tot Val (INR):";
                dr1[15] = ((System.Web.UI.WebControls.Label)grdBothPrintXLFull.FooterRow.FindControl("lblGrandTotal")).Text;
                dset.Tables[0].Rows.Add(dr1);


//below code is export ds to excel
ApplicationClass excel = new ApplicationClass();
                Workbook wBook;
                Worksheet wSheet;
                wBook = excel.Workbooks.Add(System.Reflection.Missing.Value);
                wSheet = (Worksheet)wBook.ActiveSheet;
                System.Data.DataTable dt = dset.Tables[0];
                System.Data.DataColumn dc = new DataColumn();
                int colIndex = 0;
                int rowIndex = 4;
                foreach (DataColumn dcol in dt.Columns)
                {
                    colIndex = colIndex + 1;
                    excel.Cells[5, colIndex] = dcol.ColumnName;
                }
                foreach (DataRow drow in dt.Rows)
                {
                    rowIndex = rowIndex + 1;
                    colIndex = 0;
                    foreach (DataColumn dcol in dt.Columns)
                    {
                        colIndex = colIndex + 1;
                        excel.Cells[rowIndex + 1, colIndex] = drow[dcol.ColumnName];
                    }
                }
                wSheet.Columns.AutoFit();
                String strFileName = Server.MapPath("~\\Images\\StockStatement.xls");
 

Boolean blnFileOpen = false;
                try
                {
                    System.IO.FileStream fileTemp = File.OpenWrite(strFileName);
                    fileTemp.Close();
                }
                catch
                {
                    blnFileOpen = false;
                }
                if (System.IO.File.Exists(strFileName))
                {
                    System.IO.File.Delete(strFileName);
                }
                Range oRng;
                wSheet.Cells[1, 2] = lblOffice1.Text;
                wSheet.Cells[3, 2] = lblCostCenter1.Text;
                wSheet.Cells[4, 1] = lblOfficeName1.Text;
                wSheet.get_Range("B1", "B1").Font.Bold = true;
                wSheet.get_Range("B1", "B1").Font.ColorIndex = 55;
                wSheet.get_Range("B3", "B3").Font.ColorIndex = 55;
                wSheet.get_Range("A4", "A4").Font.ColorIndex = 55;
                wSheet.get_Range("B1", "E1").Merge(Type.Missing);
                wSheet.get_Range("B3", "E3").Merge(Type.Missing);
                wSheet.get_Range("B1", "B1").HorizontalAlignment = Constants.xlCenter;
                wSheet.get_Range("B3", "B3").HorizontalAlignment = Constants.xlCenter;
                wSheet.get_Range("B3", "B3").Font.Bold = true;
                wSheet.get_Range("A4", "A4").Font.Bold = true;
                wSheet.get_Range("A4", "A4").HorizontalAlignment = Constants.xlLeft;
                wSheet.get_Range("A5", "P5").Font.Bold = true;
                wSheet.get_Range("A5", "P5").Interior.ColorIndex = 43;
                wSheet.Name = "Stock Statement";
                //AutoFit columns ABig Grin | :-D . 
                oRng = wSheet.get_Range("A1", "P1");
                oRng.EntireColumn.AutoFit();
                wBook.SaveAs(strFileName, XlFileFormat.xlExcel12, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, XlSaveAsAccessMode.xlShared, XlSaveConflictResolution.xlLocalSessionChanges, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false);
 
Share this answer
 
Posted
Hi use below code

Add reference below in your project
Microsoft office 12.0 controls library


add namespace

using Microsoft.Office.Interop.Excel;
 
//First fetch all records from grid to dataset
DataSet dset = new DataSet();
dset.Tables.Add();

for (int i = 0; i < grdBothPrintXLFull.Columns.Count; i++)
                {
                    dset.Tables[0].Columns.Add(grdBothPrintXLFull.Columns[i].HeaderText);
                }

//add rows to the table
System.Data.DataRow dr1;
                for (int i = 0; i < grdBothPrintXLFull.Rows.Count; i++)
                {
                    dr1 = dset.Tables[0].NewRow();

                    //for (int j = 0; j < grdMainPrintXLFull.Columns.Count; j++)
                    //{
                    System.Web.UI.WebControls.Label lblCCName = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblCCName");
                    System.Web.UI.WebControls.Label lblItemName = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblItemName");
                    System.Web.UI.WebControls.Label lblItemCode = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblItemCode");
                    System.Web.UI.WebControls.Label lblItemDesc = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblItemDesc");
                    System.Web.UI.WebControls.Label lblBINNo = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblBINNo");
                    System.Web.UI.WebControls.Label lblItemNature = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblItemNature");
                    System.Web.UI.WebControls.Label lblUOM = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblUOM");
                    System.Web.UI.WebControls.Label lblOPBAL = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblOPBAL");
                    System.Web.UI.WebControls.Label lblInQty = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblInQty");
                    System.Web.UI.WebControls.Label lblOutQty = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblOutQty");
                    System.Web.UI.WebControls.Label lblBal = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblBal");
                    System.Web.UI.WebControls.Label lblMinStkLvl = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblMinStkLvl");
                    System.Web.UI.WebControls.Label lblSecUOM = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblSecUOM");
                    System.Web.UI.WebControls.Label lblSecBal = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblSecBal");
                    System.Web.UI.WebControls.Label lblRate = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblRate");
                    System.Web.UI.WebControls.Label lblStockValue = (System.Web.UI.WebControls.Label)grdBothPrintXLFull.Rows[i].Cells[0].FindControl("lblStockValue");

                    dr1[0] = lblCCName.Text.ToString();
                    dr1[1] = lblItemName.Text.ToString();
                    dr1[2] = lblItemCode.Text.ToString();
                    dr1[3] = lblItemDesc.Text.ToString();
                    dr1[4] = lblBINNo.Text.ToString();
                    dr1[5] = lblItemNature.Text.ToString();
                    dr1[6] = lblUOM.Text.ToString();
                    dr1[7] = lblOPBAL.Text.ToString();
                    dr1[8] = lblInQty.Text.ToString();
                    dr1[9] = lblOutQty.Text.ToString();
                    dr1[10] = lblBal.Text.ToString();
                    dr1[11] = lblMinStkLvl.Text.ToString();
                    dr1[12] = lblSecUOM.Text.ToString();
                    dr1[13] = lblSecBal.Text.ToString();
                    dr1[14] = lblRate.Text.ToString();
                    dr1[15] = lblStockValue.Text.ToString();
                    dset.Tables[0].Rows.Add(dr1);
                }
                dr1 = dset.Tables[0].NewRow();
                dr1[0] = "";
                dr1[1] = "";
                dr1[2] = "";
                dr1[3] = "";
                dr1[4] = "";
                dr1[5] = "";
                dr1[6] = "";
                dr1[7] = "";
                dr1[8] = "";
                dr1[9] = "";
                dr1[10] = "";
                dr1[11] = "";
                dr1[12] = "";
                dr1[13] = "";
                dr1[14] = "Tot Val (INR):";
                dr1[15] = ((System.Web.UI.WebControls.Label)grdBothPrintXLFull.FooterRow.FindControl("lblGrandTotal")).Text;
                dset.Tables[0].Rows.Add(dr1);


//below code is export ds to excel
ApplicationClass excel = new ApplicationClass();
                Workbook wBook;
                Worksheet wSheet;
                wBook = excel.Workbooks.Add(System.Reflection.Missing.Value);
                wSheet = (Worksheet)wBook.ActiveSheet;
                System.Data.DataTable dt = dset.Tables[0];
                System.Data.DataColumn dc = new DataColumn();
                int colIndex = 0;
                int rowIndex = 4;
                foreach (DataColumn dcol in dt.Columns)
                {
                    colIndex = colIndex + 1;
                    excel.Cells[5, colIndex] = dcol.ColumnName;
                }
                foreach (DataRow drow in dt.Rows)
                {
                    rowIndex = rowIndex + 1;
                    colIndex = 0;
                    foreach (DataColumn dcol in dt.Columns)
                    {
                        colIndex = colIndex + 1;
                        excel.Cells[rowIndex + 1, colIndex] = drow[dcol.ColumnName];
                    }
                }
                wSheet.Columns.AutoFit();
                String strFileName = Server.MapPath("~\\Images\\StockStatement.xls");
 

Boolean blnFileOpen = false;
                try
                {
                    System.IO.FileStream fileTemp = File.OpenWrite(strFileName);
                    fileTemp.Close();
                }
                catch
                {
                    blnFileOpen = false;
                }
                if (System.IO.File.Exists(strFileName))
                {
                    System.IO.File.Delete(strFileName);
                }
                Range oRng;
                wSheet.Cells[1, 2] = lblOffice1.Text;
                wSheet.Cells[3, 2] = lblCostCenter1.Text;
                wSheet.Cells[4, 1] = lblOfficeName1.Text;
                wSheet.get_Range("B1", "B1").Font.Bold = true;
                wSheet.get_Range("B1", "B1").Font.ColorIndex = 55;
                wSheet.get_Range("B3", "B3").Font.ColorIndex = 55;
                wSheet.get_Range("A4", "A4").Font.ColorIndex = 55;
                wSheet.get_Range("B1", "E1").Merge(Type.Missing);
                wSheet.get_Range("B3", "E3").Merge(Type.Missing);
                wSheet.get_Range("B1", "B1").HorizontalAlignment = Constants.xlCenter;
                wSheet.get_Range("B3", "B3").HorizontalAlignment = Constants.xlCenter;
                wSheet.get_Range("B3", "B3").Font.Bold = true;
                wSheet.get_Range("A4", "A4").Font.Bold = true;
                wSheet.get_Range("A4", "A4").HorizontalAlignment = Constants.xlLeft;
                wSheet.get_Range("A5", "P5").Font.Bold = true;
                wSheet.get_Range("A5", "P5").Interior.ColorIndex = 43;
                wSheet.Name = "Stock Statement";
                //AutoFit columns ABig Grin | :-D . 
                oRng = wSheet.get_Range("A1", "P1");
                oRng.EntireColumn.AutoFit();
                wBook.SaveAs(strFileName, XlFileFormat.xlExcel12, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, XlSaveAsAccessMode.xlShared, XlSaveConflictResolution.xlLocalSessionChanges, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false);
 
Share this answer
 
Posted
Hi,
Go through this-

Exporting DataGrid to Excel, Word and Text Files[^]

C# class library for exporting data to CSV/Excel file[^]

Exporting in MS Excel by MS Excel way[^]
 
Share this answer
 
Posted
Comments
fahfahfah 22-Jan-12 0:43am    
Hi javed,

ikeep getting this error at this line:

ExcelApp.Cells[i + 2, j + 1] = dataGridView2.Rows[i].Cells[j].Value.ToString();

It says, "object reference not set to an instance of an obj"

Why is that so? My code is similar to this link: http://www.gridview.net/export-datagridview-to-excel-in-c/
Jαved 30-Jan-12 0:53am    
Hi,
sorry for the late reply.
I think the error you received is because you forget to use New keyword while creating object somewhere.
check following link :
http://www.gridview.net/export-datagridview-to-excel-in-c/[^]
 
Share this answer
 
Posted
Comments
fahfahfah 20-Jan-12 4:24am    
Hi Manoj, thanks for your reply. I used the code provided from the link. I get the error message: "object reference not set to an instance of an obj". Please kindly explain. Thanks.
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
Export datagridview to excel while keeping the formatting
Export dynamically added columns(datagridview) to excel
export datagridview to excel
Export datagridview added columns to excel +formatting
how to export datagridview to excel from sdf file
Exporting rows of datagridview to excel
Could not open XLSX file exported using oLedb
export datagridview to excel in windows c#
Error exporting data that from 5 table with inner join to excel sheet
Problem in export datagridview data in excel in C winform

Advertise
Privacy
Cookies
Terms of Use
Last Updated 21 Mar 2013
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web01 2.8:2024-05-28:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900