none
Create an excel file using c# RRS feed

  • Question

  • Hi, I am working on c# application and the requirement is when I get data from the database, I have to do few calculations and based on those business logics  I have to create a new excel file and save some data on the excel file.

     

    Solution I am looking is ,

    1) How to create an excel file within the business logic ( within the code)

    2) How to add  new data to the file which I have created in the first step.

    Please advise.

     

    Note: If this is not correct forum, please advise me which is the correct forum before you move this to Off-Topic Posts.

     

    Thanks and Regards,

    nw

    Monday, October 15, 2007 7:24 PM

Answers

  • There are several ways to export data into Excel:

    1. Use Jet OLEDB provider. You would need to execute INSERT SQL statements and insert data row-by -row. Jet provides pretty performance if you do not need to export large volumes of data in one show, but Jet does not allow to format data.  Here are some samples how to work with Excel using Jet

     

    http://support.microsoft.com/kb/316934/en-us

    http://support.microsoft.com/kb/306572/en-us

    http://support.microsoft.com/kb/306022/en-us

     

    2. Use Microsoft Visual Studio Tools for Office (VSTO). This library of classes is COM-based and accessible through COM Interop, but provides access to full functionality of Excel. Performance is not the best, but you could format data as you wish. Here are samples using VSTO

     

    http://support.microsoft.com/kb/302096/en-us

     

    3. You could export into XML format supported by Excel starting version 2002. Here is sample how to do this

     

    http://support.microsoft.com/kb/307029/en-us

     

    4. You could try third-party components. I have developed my own and you could try it from my Web site. It provides really good performance, especially in big chunks of data, but formatting capabilities are limited.

     

    Tuesday, October 16, 2007 2:19 AM
    Moderator
  • In order to create a new XLS file you will need to create a table (Worksheet). Otherwise, you will need to use automation with the Excel application to create the file. Below is an example which implements the data access method:

     

    Code Block

    Dim ConnectionString As String

    Dim ConnectionStringText As String

    ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

    "Data Source=C:\Test Files\ExcelWB.xls;Extended Properties=Excel 8.0"

     

    Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)

    ExcelConnection.Open()

    Dim SQLDDLCommand As String = "CREATE TABLE tblCustomers " & _

    "(CustomerID INTEGER, " & _

    "[Last Name] TEXT(50), " & _

    "[First Name] TEXT(50), " & _

    "Phone TEXT(10), " & _

    "Email TEXT(50))"

     

    Dim ExcelCommand As New System.Data.OleDb.OleDbCommand(SQLDDLCommand, ExcelConnection)

    ExcelCommand.ExecuteNonQuery()

    ExcelConnection.Close()

     

     

     

    Tuesday, October 16, 2007 1:08 PM

All replies

  • There are several ways to export data into Excel:

    1. Use Jet OLEDB provider. You would need to execute INSERT SQL statements and insert data row-by -row. Jet provides pretty performance if you do not need to export large volumes of data in one show, but Jet does not allow to format data.  Here are some samples how to work with Excel using Jet

     

    http://support.microsoft.com/kb/316934/en-us

    http://support.microsoft.com/kb/306572/en-us

    http://support.microsoft.com/kb/306022/en-us

     

    2. Use Microsoft Visual Studio Tools for Office (VSTO). This library of classes is COM-based and accessible through COM Interop, but provides access to full functionality of Excel. Performance is not the best, but you could format data as you wish. Here are samples using VSTO

     

    http://support.microsoft.com/kb/302096/en-us

     

    3. You could export into XML format supported by Excel starting version 2002. Here is sample how to do this

     

    http://support.microsoft.com/kb/307029/en-us

     

    4. You could try third-party components. I have developed my own and you could try it from my Web site. It provides really good performance, especially in big chunks of data, but formatting capabilities are limited.

     

    Tuesday, October 16, 2007 2:19 AM
    Moderator
  • In order to create a new XLS file you will need to create a table (Worksheet). Otherwise, you will need to use automation with the Excel application to create the file. Below is an example which implements the data access method:

     

    Code Block

    Dim ConnectionString As String

    Dim ConnectionStringText As String

    ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

    "Data Source=C:\Test Files\ExcelWB.xls;Extended Properties=Excel 8.0"

     

    Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)

    ExcelConnection.Open()

    Dim SQLDDLCommand As String = "CREATE TABLE tblCustomers " & _

    "(CustomerID INTEGER, " & _

    "[Last Name] TEXT(50), " & _

    "[First Name] TEXT(50), " & _

    "Phone TEXT(10), " & _

    "Email TEXT(50))"

     

    Dim ExcelCommand As New System.Data.OleDb.OleDbCommand(SQLDDLCommand, ExcelConnection)

    ExcelCommand.ExecuteNonQuery()

    ExcelConnection.Close()

     

     

     

    Tuesday, October 16, 2007 1:08 PM
  • An end-user is using a spreadsheet that automatically fetches exchange rate from Oanda.com site. In the spreadsheet, there's a macro that uses the following URL to connect to Oanda.com:

     

    http://www.oanda.com/convert/fxhistory?lang=en&date1=01%2F12%2F04&date=01%2F12%2F04&date_fmt=normal&exch=USD&exch2=&expr=EUR&expr2=&margin_fixed=0&&SUBMIT=Get+Table&format=HTML&redirected=1

     

    But I can't find any component on ASP.NET that connects to database using URL address string. The objective is to move the function of this spreadsheet onto an Intranet platform. And user is able to choose exchange rate for each currency conversion at a chosen date on a web interface.

    For your information, the EXCEL spreadsheet uses following code to connect to the website:

     With Sheets("Exchange Rate").Range("A1").QueryTable
            .Connection = _
             "URL;http://www.oanda.com/convert/fxhistory?lang=en&date1=" _
                & dayNum & "%2F" & monthNum & "%2F" & yearNum & "&date=" _
                & dayNum & "%2F" & monthNum & "%2F" & yearNum & "&date_fmt=normal" _
                & "&exch=" & currencyFrom & "&exch2=&expr=" & currencyTo & "" _
                & "&expr2=&margin_fixed=0&&SUBMIT=Get+Table&format=HTML&redirected=1"
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = "7"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With

     
    Will it work if I replaced your ConnectionString variable with "URL;http://www.oanda.com/convert/fxhistory?lang=en&date1=" _
                & dayNum & "%2F" & monthNum & "%2F" & yearNum & "&date=" _
                & dayNum & "%2F" & monthNum & "%2F" & yearNum & "&date_fmt=normal" _
                & "&exch=" & currencyFrom & "&exch2=&expr=" & currencyTo & "" _
                & "&expr2=&margin_fixed=0&&SUBMIT=Get+Table&format=HTML&redirected=1"?
     
    And then I'll just have to pick up the rate from the EXCEL spreadsheet and pass it to ASP.NET web application.
    Monday, November 19, 2007 3:20 AM
  • Hi dude,

    Look at below link.

    may it help you.

    http://dotnet-magic.blogspot.com/2011/10/createformat-excel-file-from-cnet.html

     

     

     

     

    Thursday, October 13, 2011 8:30 AM