locked
how to export data from ms sql 2008 to excel files without installed ms office to my pc? RRS feed

  • Question

  • how to export data from ms sql 2008 to excel files without installed ms office to my pc? and i am using C#
    Saturday, July 28, 2012 1:21 PM

Answers

  • You should be able to accomplish this task with a SQL statement. Below is an example:

    System.Data.OleDb.OleDbConnection ExcelConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + "c:\\Test Files\\Book20.xls" + ";" + "Extended Properties=\"Excel 8.0;HDR=YES\"");
    
    System.Data.OleDb.OleDbCommand ExcelCommand = new System.Data.OleDb.OleDbCommand("SELECT * INTO [Orders] FROM [Orders] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes];", ExcelConnection);
    
    ExcelCommand.ExecuteNonQuery();
    ExcelConnection.Close();
    


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Allen_MSDN Thursday, August 9, 2012 10:01 AM
    Tuesday, July 31, 2012 3:26 PM
  • Paul's method is one of your best options unless you need to do some formatting. If formatting is needed consider the method from Beth Massi.

    The following example was the out come of following Beth's article but in this case data is retrieved from an MS-Access table into a DataTable. So for you if this is of interest you simply point to your database, do a select statement and load the data into a DataTable and follow along with Beth's instructions (she does a DataContext for a data source) and my example.

    Please note that the last line opens the created xml file in Excel as when Excel is installed it becomes the default app for xml files.

    Imports (as per Beth's article)

    Imports <xmlns="urn:schemas-microsoft-com:office:spreadsheet">
    Imports <xmlns:o="urn:schemas-microsoft-com:office:office">
    Imports <xmlns:x="urn:schemas-microsoft-com:office:excel">
    Imports <xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
    Imports <xmlns:html="http://www.w3.org/TR/REC-html40">

    Code

    Dim dt As New DataTable
    Using cn As New OleDb.OleDbConnection With
        {
            .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=People.accdb"
        }
        Using cmd As New OleDb.OleDbCommand With
            {
                .Connection = cn,
                .CommandText = "SELECT TOP 10 * FROM Person"
            }
            cn.Open()
            dt.Load(cmd.ExecuteReader)
        End Using
    End Using
    Dim Persons = From P In dt.AsEnumerable Select _
                  <Row>
                      <Cell><Data ss:Type="String"><%= P.Field(Of String)("FirstName") %></Data></Cell>
                      <Cell><Data ss:Type="String"><%= P.Field(Of String)("LastName") %></Data></Cell>
                  </Row>
    Dim sheet = _
    <?xml version="1.0"?>
    <?mso-application progid="Excel.Sheet"?>
    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
        xmlns:o="urn:schemas-microsoft-com:office:office"
        xmlns:x="urn:schemas-microsoft-com:office:excel"
        xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
        xmlns:html="http://www.w3.org/TR/REC-html40">
        <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
            <Author>kevininstructor</Author>
            <LastAuthor>kevininstructor</LastAuthor>
            <Created>2011-06-22T02:26:45Z</Created>
            <Version>12.00</Version>
        </DocumentProperties>
        <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
            <WindowHeight>12300</WindowHeight>
            <WindowWidth>21075</WindowWidth>
            <WindowTopX>240</WindowTopX>
            <WindowTopY>75</WindowTopY>
            <ProtectStructure>False</ProtectStructure>
            <ProtectWindows>False</ProtectWindows>
        </ExcelWorkbook>
        <Styles>
            <Style ss:ID="Default" ss:Name="Normal">
                <Alignment ss:Vertical="Bottom"/>
                <Borders/>
                <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
                <Interior/>
                <NumberFormat/>
                <Protection/>
            </Style>
            <Style ss:ID="s62">
                <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"
                    ss:Bold="1"/>
            </Style>
        </Styles>
        <Worksheet ss:Name="People">
            <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount=<%= Persons.Count + 1 %> x:FullColumns="1"
                x:FullRows="1" ss:DefaultRowHeight="15">
                <Row>
                    <Cell ss:StyleID="s62"><Data ss:Type="String">First</Data></Cell>
                    <Cell ss:StyleID="s62"><Data ss:Type="String">Last</Data></Cell>
                </Row>
                <%= Persons %>
            </Table>
            <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                <PageSetup>
                    <Header x:Margin="0.3"/>
                    <Footer x:Margin="0.3"/>
                    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                </PageSetup>
                <Print>
                    <ValidPrinterInfo/>
                    <HorizontalResolution>600</HorizontalResolution>
                    <VerticalResolution>600</VerticalResolution>
                </Print>
                <Selected/>
                <Panes>
                    <Pane>
                        <Number>3</Number>
                        <ActiveRow>4</ActiveRow>
                        <ActiveCol>1</ActiveCol>
                    </Pane>
                </Panes>
                <ProtectObjects>False</ProtectObjects>
                <ProtectScenarios>False</ProtectScenarios>
            </WorksheetOptions>
        </Worksheet>
        <Worksheet ss:Name="Sheet2">
            <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
                x:FullRows="1" ss:DefaultRowHeight="15">
            </Table>
            <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                <PageSetup>
                    <Header x:Margin="0.3"/>
                    <Footer x:Margin="0.3"/>
                    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                </PageSetup>
                <ProtectObjects>False</ProtectObjects>
                <ProtectScenarios>False</ProtectScenarios>
            </WorksheetOptions>
        </Worksheet>
        <Worksheet ss:Name="Sheet3">
            <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
                x:FullRows="1" ss:DefaultRowHeight="15">
            </Table>
            <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                <PageSetup>
                    <Header x:Margin="0.3"/>
                    <Footer x:Margin="0.3"/>
                    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                </PageSetup>
                <ProtectObjects>False</ProtectObjects>
                <ProtectScenarios>False</ProtectScenarios>
            </WorksheetOptions>
        </Worksheet>
    </Workbook>
    sheet.Save("Persons_dt.xml")
    Process.Start("Persons_dt.xml")


    KSG

    • Proposed as answer by Alexander Sun Monday, August 6, 2012 7:08 AM
    • Marked as answer by Allen_MSDN Thursday, August 9, 2012 10:01 AM
    Friday, August 3, 2012 3:42 PM

All replies

  • You should be able to accomplish this task with a SQL statement. Below is an example:

    System.Data.OleDb.OleDbConnection ExcelConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + "c:\\Test Files\\Book20.xls" + ";" + "Extended Properties=\"Excel 8.0;HDR=YES\"");
    
    System.Data.OleDb.OleDbCommand ExcelCommand = new System.Data.OleDb.OleDbCommand("SELECT * INTO [Orders] FROM [Orders] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes];", ExcelConnection);
    
    ExcelCommand.ExecuteNonQuery();
    ExcelConnection.Close();
    


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Allen_MSDN Thursday, August 9, 2012 10:01 AM
    Tuesday, July 31, 2012 3:26 PM
  • Hi Dyeni,

    Welcome to the MSDN forum.

    I found a nice sample which I think is helpful. Please check this: http://www.codeproject.com/Articles/151789/Export-Data-to-Excel-Word-PDF-without-Automation-f

    Have a nice day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, August 1, 2012 6:20 AM
  • Paul's method is one of your best options unless you need to do some formatting. If formatting is needed consider the method from Beth Massi.

    The following example was the out come of following Beth's article but in this case data is retrieved from an MS-Access table into a DataTable. So for you if this is of interest you simply point to your database, do a select statement and load the data into a DataTable and follow along with Beth's instructions (she does a DataContext for a data source) and my example.

    Please note that the last line opens the created xml file in Excel as when Excel is installed it becomes the default app for xml files.

    Imports (as per Beth's article)

    Imports <xmlns="urn:schemas-microsoft-com:office:spreadsheet">
    Imports <xmlns:o="urn:schemas-microsoft-com:office:office">
    Imports <xmlns:x="urn:schemas-microsoft-com:office:excel">
    Imports <xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
    Imports <xmlns:html="http://www.w3.org/TR/REC-html40">

    Code

    Dim dt As New DataTable
    Using cn As New OleDb.OleDbConnection With
        {
            .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=People.accdb"
        }
        Using cmd As New OleDb.OleDbCommand With
            {
                .Connection = cn,
                .CommandText = "SELECT TOP 10 * FROM Person"
            }
            cn.Open()
            dt.Load(cmd.ExecuteReader)
        End Using
    End Using
    Dim Persons = From P In dt.AsEnumerable Select _
                  <Row>
                      <Cell><Data ss:Type="String"><%= P.Field(Of String)("FirstName") %></Data></Cell>
                      <Cell><Data ss:Type="String"><%= P.Field(Of String)("LastName") %></Data></Cell>
                  </Row>
    Dim sheet = _
    <?xml version="1.0"?>
    <?mso-application progid="Excel.Sheet"?>
    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
        xmlns:o="urn:schemas-microsoft-com:office:office"
        xmlns:x="urn:schemas-microsoft-com:office:excel"
        xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
        xmlns:html="http://www.w3.org/TR/REC-html40">
        <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
            <Author>kevininstructor</Author>
            <LastAuthor>kevininstructor</LastAuthor>
            <Created>2011-06-22T02:26:45Z</Created>
            <Version>12.00</Version>
        </DocumentProperties>
        <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
            <WindowHeight>12300</WindowHeight>
            <WindowWidth>21075</WindowWidth>
            <WindowTopX>240</WindowTopX>
            <WindowTopY>75</WindowTopY>
            <ProtectStructure>False</ProtectStructure>
            <ProtectWindows>False</ProtectWindows>
        </ExcelWorkbook>
        <Styles>
            <Style ss:ID="Default" ss:Name="Normal">
                <Alignment ss:Vertical="Bottom"/>
                <Borders/>
                <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
                <Interior/>
                <NumberFormat/>
                <Protection/>
            </Style>
            <Style ss:ID="s62">
                <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"
                    ss:Bold="1"/>
            </Style>
        </Styles>
        <Worksheet ss:Name="People">
            <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount=<%= Persons.Count + 1 %> x:FullColumns="1"
                x:FullRows="1" ss:DefaultRowHeight="15">
                <Row>
                    <Cell ss:StyleID="s62"><Data ss:Type="String">First</Data></Cell>
                    <Cell ss:StyleID="s62"><Data ss:Type="String">Last</Data></Cell>
                </Row>
                <%= Persons %>
            </Table>
            <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                <PageSetup>
                    <Header x:Margin="0.3"/>
                    <Footer x:Margin="0.3"/>
                    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                </PageSetup>
                <Print>
                    <ValidPrinterInfo/>
                    <HorizontalResolution>600</HorizontalResolution>
                    <VerticalResolution>600</VerticalResolution>
                </Print>
                <Selected/>
                <Panes>
                    <Pane>
                        <Number>3</Number>
                        <ActiveRow>4</ActiveRow>
                        <ActiveCol>1</ActiveCol>
                    </Pane>
                </Panes>
                <ProtectObjects>False</ProtectObjects>
                <ProtectScenarios>False</ProtectScenarios>
            </WorksheetOptions>
        </Worksheet>
        <Worksheet ss:Name="Sheet2">
            <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
                x:FullRows="1" ss:DefaultRowHeight="15">
            </Table>
            <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                <PageSetup>
                    <Header x:Margin="0.3"/>
                    <Footer x:Margin="0.3"/>
                    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                </PageSetup>
                <ProtectObjects>False</ProtectObjects>
                <ProtectScenarios>False</ProtectScenarios>
            </WorksheetOptions>
        </Worksheet>
        <Worksheet ss:Name="Sheet3">
            <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
                x:FullRows="1" ss:DefaultRowHeight="15">
            </Table>
            <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                <PageSetup>
                    <Header x:Margin="0.3"/>
                    <Footer x:Margin="0.3"/>
                    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                </PageSetup>
                <ProtectObjects>False</ProtectObjects>
                <ProtectScenarios>False</ProtectScenarios>
            </WorksheetOptions>
        </Worksheet>
    </Workbook>
    sheet.Save("Persons_dt.xml")
    Process.Start("Persons_dt.xml")


    KSG

    • Proposed as answer by Alexander Sun Monday, August 6, 2012 7:08 AM
    • Marked as answer by Allen_MSDN Thursday, August 9, 2012 10:01 AM
    Friday, August 3, 2012 3:42 PM