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

Question

how to export data from ms sql 2008 to excel files without installed ms office to my pc? and i am using C#

Answer 1

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)

Answer 2

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

Answer 3

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