Saturday, January 19, 2008

Export Data to Excel (Not HTML Tables)

Recently I working with export data to an excel file, but had faced a lot of problems. The main problem of it: The exported file is saved as HTML tables with XLS extension, not the actual XLS format. In this way, I can’t re-import the data using the OleDbConnection.

I have been searching around the net, but no exact solution that suit my situation. Finally, I had found another way to export data into the actual XLS format, by using Excel.Application.

First thing that you need to do is to add Excel dll (Microsoft Excel 11.0 Object Library) as a reference.

Here are the codes.

Private Function ExportToExcel(ByVal dt As System.Data.DataTable)
    Dim xlsApp As New Excel.Application
    Dim xlsWorkbook As Excel.Workbook
    Dim xlsWorksheets As Excel.Worksheets
    Dim xlsWorksheet As Excel.Worksheet
    Dim strhdr As String
    Dim row As Integer
    Dim drow As DataRow
    Dim strFile As String = "test.xls"
    Dim filename As String = Server.MapPath("Doc") & "\" & strFile

    If dt.Rows.Count > 0 Then
        'Create new workbook
        xlsWorkbook =
        xlsApp.Workbooks.Add

        'Get the first worksheet
        xlsWorksheet = CType(xlsWorkbook.Worksheets(1), Excel.Worksheet)

        'Activate current worksheet
        xlsWorksheet.Activate()

        'Set header row to row 1
        row = 1

        'Add table headers to worksheet
        xlsWorksheet.Cells(row,1).Value = "NAME"
        xlsWorksheet.Cells(row, 2).Value = "JOB POSITION"
        xlsWorksheet.Cells(row, 3).Value = "ORIGIN"

        'Format header row (bold, extra row height, autofit
width)
        With xlsWorksheet.Range("A" & row, "C" & row)
            .Font.Bold = True
            .Rows(row).RowHeight = 1.5 * xlsWorksheet.StandardHeight
            .EntireRow.AutoFit()
        End With

        'Freeze the column headers
        With xlsWorksheet.Range("A" & row + 1, "C" & row + 1).Select
            xlsApp.ActiveWindow.FreezePanes = True
        End With

        'Write data to Excel worksheet
        For Each drow In dt.Rows
            row += 1
            If Not IsDBNull(dr.Item("NAME")) Then xlsWorksheet.Cells(row, 1).Value = dr.Item("NAME")
            If Not IsDBNull(dr.Item("JOB POSITION")) Then xlsWorksheet.Cells(row, 2).Value = dr.Item("JOB POSITION")
            If Not IsDBNull(dr.Item("ORIGIN")) Then xlsWorksheet.Cells(row, 3).Value =
dr.Item("ORIGIN")
        Next

        'Format data rows (align to top, autofit width and height)
        With xlsWorksheet.Range("A2", "C" & row)
            .VerticalAlignment = CType(XlVAlign.xlVAlignCenter, Excel.XlVAlign)
            .HorizontalAlignment = CType(XlHAlign.xlHAlignLeft, Excel.XlHAlign)
            .EntireColumn.AutoFit()
            .EntireRow.AutoFit()
        End With

        'Make excel workbook visible to user after all data has been added to worksheet
        xlsApp.DisplayAlerts = False
        xlsWorkbook.Close(True, filename)

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

If you got any question, feel free to ask me.


Update on 07 Aug 2008:
For C# .NET example, please refer to Export Data to Excel (Not HTML Table) in C# .NET

19 comments:

Jerry said...

Thanks for your nice work.
Is anything missing in the line "xlsWorkbook.Close(True, filename)"

ejuly on August 7, 2008 at 6:45 PM said...

Hi Jerry,
There is nothing missing in that line. Are you encounter any problem with the codes? Please feel free to ask if so.

Jerry said...

Hi ejujy,
I'm using C#. And Close() requires 3 arguments. Third one is "RouteWorkBook". Thanks a lot once again. This was what exaclty I was in search of. I feel I'm very near to my requirements

ejuly on August 7, 2008 at 8:31 PM said...

Jerry, for C# .NET, you can just put the RouteWorkBook as null.

xlsWorkbook.Close(true, filename, null);

Hope this will help u.

Jerry said...

Hi ejuly,
Could you please let me know
what should be the argument passed along with "xlsApp.Workbooks.Add". And since Value is not a property of Cells, How can we rewrite in C#

ejuly on August 7, 2008 at 9:10 PM said...

Hi Jerry,

To create a new workbook in C#:
xlsWorkbook = xlsApp.Workbooks.Add(true);

To insert the data into a cell:
xlsWorksheet.Cells[row, 1] = "your_value";

ejuly on August 7, 2008 at 9:16 PM said...

You can also refer to Export Data to Excel File not in HTML Table Format using C#.NET for C# example.

nyi on January 8, 2009 at 2:10 PM said...

Hi, I got the following error. How to resolve it? Pls let me now. Thanks.

"Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154."

and highlighting this line of code---

Dim App As New Excel.Application
I imported the required namespace for Excel
Imports Microsoft.Office.Interop


Thanks
Chaw

ejuly on January 8, 2009 at 5:59 PM said...

I guess that your aspnet user may not have the appropriate permissions to be allowed to start an excel instance.

Please refer to this link.

Hope this will help.

nyi on January 9, 2009 at 10:44 PM said...

Hi ejujy,
thanks a lot once again. I am using windows vista and microsoft excel 2007. sometime, i can't close the file.

i got this error
"Microsoft Office Excel cannot access the file 'C:\37D38000'. There are several possible reasons:
• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook."

and highlighting this line of code---
xlsWorkBook.Close(true, @"C:\exportExcel.xls", null);

If i can create the excel file, can't open in excel 2007. what should i do?

thanks

ejuly on January 10, 2009 at 12:05 AM said...

It looks like you have specified a file name ('C:\37D38000') without a XLS extension - this might be the reason. :)

Nyi on March 9, 2009 at 2:39 PM said...

Hi Jerry,
I would like to export data to excel with multiple sheets. How should I do?
I have more than 85,000 records in database. How should I spread the sheets. My application is using Client/Server(Windows) application.

Thanks
Chaw

ejuly on March 9, 2009 at 9:37 PM said...

Hi Chaw,

Here is a sample of codes to create a new sheet in Excel.

object oMissing = System.Reflection.Missing.Value;

Excel.Worksheet xlsWorksheet;

//Add a worksheet to the workbook.
xlsWorksheet = (Excel.Worksheet)xlsApp.Worksheets.Add(oMissing, oMissing, oMissing, oMissing );

//Name the sheet
xlsWorksheet.Name ="Sheet_1";

How do you want to split the 85000 records into multiple sheets? 10000 records per sheet? Or 20000 records per sheet? There are many ways to achieve this, e.g. for/foreach loop.

Chaw Su Su on March 11, 2009 at 4:08 PM said...

Hi Jerry,
Thanks a lot once again.

eg.
RowCount =150,555 or more than that
long rowLimit = 65000;
I want to auto split after the rowLimit in one sheet and then go to other sheet and so on. I am using SqlReader to retrieve the data from sql server 2008.
I really don't know how to do. That's why, Please write the same code for me.

How about the performance?

Thanks
Chaw

Chaw Su Su on March 11, 2009 at 4:51 PM said...

Hi Jerry,
after the row records 21700, I got that error message.

Error : Exception from HRESULT: 0x800AC472

Thanks
Chaw

Chaw Su Su on March 11, 2009 at 8:43 PM said...

Hi Jerry,
eg.
long rowsCound = 150,000;
long rowLimit = 65,000;
I would lie to split the records into multiple sheets. 65,000 records pe sheet.
Could you give the some sample code for me?
How about the performance?
Thanks.
Chaw

ejuly on March 11, 2009 at 10:09 PM said...

Hi Chaw,

regarding the error message, can you show me where is the exception happens?

If you have a long processing, Data Set may be is a better choice than Data Reader, because Data Reader keeps the database connection open till you finish entire processing of your data.

Sample codes:
DataSet ds = new DataSet();
ds = GetRowFromDb();

int index = 0;

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

  if (index <= 65000){
    //do your insert
  }
  else{
    // create a new worksheet and do your insert here

    index = 0;
   }
}


July

Chaw Su Su on March 12, 2009 at 10:09 AM said...

Hi Jerry,
I got it. Thanks a lot.

Thanks
Chaw

Mbongeni on January 10, 2012 at 10:22 PM said...

Hi Guys

My question is how do you export two separate tab pages into two separate spreadsheets in excel.

thanks in advance.

 

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