Thursday, August 7, 2008

Export Data to Excel (Not HTML Table) in C# .NET

In my previous post - Export Data to Excel (Not HTML Tables), I had shown you how to export to excel in VB .NET.

In this post, I will use a C# .NET example instead.

Of cause first thing that you need to do is to add Excel.dll (Microsoft excel 11.0 Object Library) into your .NET project references.

Here is the codes:

private void ExportToExcelFile(System.Data.DataTable dt)
{
   Excel.Application xlsApp = new Excel.ApplicationClass();
   Excel.Workbook xlsWorkbook;
   Excel.Worksheet xlsWorksheet;
   string strhdr;
   int row;
   string strFile = "file1.xls";
   string filename = Server.MapPath(strFile);

   if (dt.Rows.Count > 0)
   {
      //Create new workbook
      xlsWorkbook = xlsApp.Workbooks.Add(true);

      //Get the first worksheet
      xlsWorksheet = (Excel.Worksheet)(xlsWorkbook.Worksheets[1]);

      //Activate current worksheet
      xlsWorksheet.Activate();

      //Set header row to row 1
      row = 1;

      //Add table headers to worksheet
      xlsWorksheet.Cells[row, 1] = "Name";
      xlsWorksheet.Cells[row, 2] = "Gender";
      xlsWorksheet.Cells[row, 3] = "Age";

      //Format header row (bold, extra row height, autofit width)
      xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).Font.Bold = true;
      xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).Rows.RowHeight = 1.5 * xlsWorksheet.StandardHeight;
      xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).EntireRow.AutoFit();

      //Freeze the columm headers
      xlsWorksheet.get_Range("A" + (row + 1).ToString(), "C" + (row + 1).ToString()).Select();
      xlsApp.ActiveWindow.FreezePanes = true;

      //Write data to Excel worksheet
      foreach (DataRow dr in dt.Rows)
      {
         row += 1;
         if (dr["Name"] != null)
            xlsWorksheet.Cells[row, 1] = dr["Name"];
         if (dr["Gender"] != null)
            xlsWorksheet.Cells[row, 2] = dr["Gender"];
         if (dr["Age"] != null)
            xlsWorksheet.Cells[row, 3] = dr["Age"];
      }

      //Format data rows (align to center and left, autofit width and height)
      xlsWorksheet.get_Range("A2", "C" + row.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
      xlsWorksheet.get_Range("A2", "C" + row.ToString()).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
      xlsWorksheet.get_Range("A2", "c" + row.ToString()).EntireColumn.AutoFit();
      xlsWorksheet.get_Range("A2", "c" + row.ToString()).EntireRow.AutoFit();

      //Make excel workbook visible to user after all data has been added to worksheet.
      xlsApp.DisplayAlerts = false;
      xlsWorkbook.Close(true, filename, null);

      //Export data to client machine
      strhdr = "attachment;filename=" + strFile;
      Response.Clear();
      Response.ContentType = "application/vnd.ms-excel";
      Response.ContentEncoding = System.Text.Encoding.Default;
      Response.AppendHeader("Content-Disposition",strhdr);
      Response.WriteFile(filename);
      Response.Flush();
      Response.Clear();
      Response.Close();
   }
}


Cheers!

12 comments:

Jerry said...

Hats off!!!!!
Marvelous work...

ejuly on August 8, 2008 at 2:34 PM said...

Congratulations! You got your work done.

Anonymous said...

Hi i am using
"Microsoft Excel 12.0 Object Library",
i am Opening excel sheet after downloading that excel sheet and again uploading that downloaded excel sheet getting error
"External Table is not in the Correct Format"
Can u help me in this problem

Thanks
Amarnath Mitta

ejuly on May 28, 2009 at 10:23 PM said...

Hi Amarnath Mitta,

One of the possibilities is the xls file you have was exported in HTML format with XLS extension.

Try to change the Extended Properties= Excel 8.0 to Extended Properties=HTML Import.

Hope this will help.

Amarnath Reddy Mitta said...

Hi sir

This is Amar can u give ur gmail id

amarmitta@gmail.com this is my gmail

Sree Bala Malleswara Swamy on May 29, 2009 at 1:03 PM said...

Hi
I am using your code for downloading excel format but when i am opening excel Sheet Getting Error

The file You are trying to open,"EasyIf.xll",is i different format than specified by the file extension.verift that the file is not corrupted and is from a trusted source before opening the file.Do you want open the file now.Dialog Box will appear with Yes No Cancel

when we Click on yes it will open EasyIf.Xll{ReadOnly] with dialog box when we click on Yes Excel sheet will open.

when downloading that excel sheet Getting error
External Data is not in the Correct Format.please help me in this

Thanks

Amarnath Mitta

AmarnathReddy Mitta said...

Hi sir
if it is possible can give u r code for uploading Excel.

Atually my req is

1)Uploading Excel Sheet and then importing data into Sqlserver2005
2)Updating all records with Status
3)Getting that records to Datatable and then converted to excel
4)That exel Should Download,user can do changes on that excel.
5)Again uploading that Changes excel into database.

Thanks
Amarnath Mitta
SofwareEngineer
Hyderabad

AmarnathReddy Mitta said...

Hi
By using this code, after downloading to excel format automatically it is inserting into present dotnet working folder,
I dont want to insert that excel files into folder,how can we remove that excel files from working folder.

please suggest me

Thanks
Amarnath Mitta

ejuly on May 29, 2009 at 10:12 PM said...

Hi Amarnath Mitta,

Current.Response.ContentType = "application/vnd.ms-excel"
HttpContext.Current.Response.AddHeader("content-disposition",
"attachment;filename=ABC.xls")
HttpContext.Current.Response.Write(strHTML)
HttpContext.Current.Response.End()
This will do, but exported as HTML format.

Any other problems please drop me a mail at ejuly07@gmail.com


july

Chaw Su Su on June 30, 2009 at 11:08 AM said...

Hi,
i am using MS Excel 2007 or MS Excel 2003.
No problem at MS Excel 2003.

I am downloading that excel sheet (.xls) and again uploading to MS SQL Server 2008 that downloaded excel sheet getting error
"External Table is not in the Correct Format"
Can u help me in this problem?

also, I can't open in MS Excel 2007. I got this error
"The file You are trying to open,"export.xls",is i different format than specified by the file extension.verift that the file is not corrupted and is from a trusted source before opening the file.Do you want open the file now.Dialog Box will appear with Yes No Cancel"

Pls help me. Thanks
Chaw

ejuly on June 30, 2009 at 11:33 AM said...

Hi Chaw,

This error generally occurs if the excel file you are trying to connect is not in a valid format, e.g. the excel file might be in HTML format.


july

hanuman on April 9, 2010 at 11:11 PM said...

what is code it is giving me
Excel.ApplicationClass() System.Security.Permissions.SecurityPermission error , i can not use it.
Next it is saying ambigous datatable error . How to call the
ExportToExcelFile(System.Data.DataTable dt) on button click event if i am using a datatable from dataset.
Please help otherwise this code is not of any use for me.I need export to excel multiple sheets badly.

 

Get paid for your opinions! Click on the banner above to join Planet Pulse. Its totally free to sign up, and you can earn UNLIMITED. Find out more by visiting PLANET PULSE.
July Code Blog Copyright © 2010 Blogger Template Designed by Bie Blogger Template