C# .NET - export dataset to excel in windows application(winForms)

Hi,

hi,

Web

In order for this to work, there is an important modification in web.config file. We have to add <identity impersonate="true"> else you will get an 'Access is denied' error.

In the application, we have to add a reference for a COM component called "Microsoft Excel 9.0 object library".

Now we have to just loop through the dataset records and populate to each cell in the excel.

Code:

private void createDataInExcel(DataSet ds)

{

      Application oXL;

      _Workbook oWB;

      _Worksheet oSheet;

      Range oRng;

      string strCurrentDir = Server.MapPath(".") + "\\reports\\";

      try

      {

           oXL = new Application();

           oXL.Visible = false;

           //Get a new workbook.

           oWB = (_Workbook)(oXL.Workbooks.Add( Missing.Value ));

           oSheet = (_Worksheet)oWB.ActiveSheet;

           //System.Data.DataTable dtGridData=ds.Tables[0];

           int iRow =2;

           if(ds.Tables[0].Rows.Count>0)

           {

                 //   for(int j=0;j<ds.Tables[0].Columns.Count;j++)

                 //   {

                 //    oSheet.Cells[1,j+1]=ds.Tables[0].Columns[j].ColumnName;

                 //

                 for(int j=0;j<ds.Tables[0].Columns.Count;j++)

                 {

                       oSheet.Cells[1,j+1]=ds.Tables[0].Columns[j].ColumnName;

                 }

                 // For each row, print the values of each column.

                 for(int rowNo=0;rowNo<ds.Tables[0].Rows.Count;rowNo++)

                 {

                     for(int colNo=0;colNo<ds.Tables[0].Columns.Count;colNo++)

                     {

                         oSheet.Cells[iRow,colNo+1]=ds.Tables[0].Rows[rowNo][colNo].ToString();

                     }

                 }

                 iRow++;

            }

              oRng = oSheet.get_Range("A1", "IV1");

            oRng.EntireColumn.AutoFit();

            oXL.Visible = false;

            oXL.UserControl = false;

            string strFile ="report"+ DateTime.Now.Ticks.ToString() +".xls";//+

            oWB.SaveAs( strCurrentDir + 
         strFile,XlFileFormat.xlWorkbookNormal,null,null,false,false,XlSaveAsAccessMode.xlShared,false,false,null,null);

             // Need all following code to clean up and remove all references!!!

           oWB.Close(null,null,null);

           oXL.Workbooks.Close();

           oXL.Quit();

           Marshal.ReleaseComObject (oRng);

           Marshal.ReleaseComObject (oXL);

           Marshal.ReleaseComObject (oSheet);

           Marshal.ReleaseComObject (oWB);

           string  strMachineName = Request.ServerVariables["SERVER_NAME"];

           Response.Redirect("http://" + strMachineName +"/"+"ViewNorthWindSample/reports/"+strFile);

      }

      catch( Exception theException )

      {

            Response.Write(theException.Message);

        }

}

Win Forms

http://www.eggheadcafe.com/articles/20050404.asp

http://stackoverflow.com/questions/373925/c-winforms-app-export-dataset-to-excel

hi,
here is code for you

There will not be any problem in your code, you only need to format  excel column  so that instead of  1E+10 it will display 010000147509

Hey guys thanks for your reply,

I tried formatting excell cells as told by Anoop but also in vain I am getting same output,
even I tried Reena's code there also am getting same output really am not understanding where am goin wrong?
please anyone help me to solve this issue,Anoop can you please tell me how to format it properly.....

thanks in advance

AMH

Select Columns-> click format cells-> select number format and select 1234 and put decimal place 0

Thanks Anoop,
why I have to do it everytime when I save an excell sheet :)

Actually while creating excel file, all the cell will be in default format, ie in general format and in general format if its large number it will display only like x.xxxE+xx where x can be any integer

Hey Anoop,
 thanks a lot for ur reply,
I got the solution this is what I did to format cells programmatically, see the below code...

Worksheet xWS = (Worksheet)excel.ActiveSheet;

Range rng = (Range)xWS.Cells[1, 1];

Range rng1 = (Range)xWS.Cells[1, 2];

rng.EntireColumn.NumberFormat = "###0";

rng1.EntireColumn.NumberFormat = "###0";

It works.....
 happy coding :)

AMH

Hi,

there is also another easy option to export http://www.gemboxsoftware.com/support/articles/import-export-dataset-xls-xlsx-ods-csv-html-net other than http://www.gemboxsoftware.com/support/articles/excel-automation-and-excel-interop - with this http://www.gemboxsoftware.com/spreadsheet/overview component.

Here is a sample code:

var ef = new ExcelFile();

 

foreach (DataTable dataTable in dataSet.Tables)

    ef.Worksheets.Add(dataTable.TableName).InsertDataTable(dataTable, 0, 0, true);

 

ef.SaveXls(dataSet.DataSetName + ".xls");