export data from access to excel in .NET

[sakhi kul]

hello everybody,
I need a code to export data from Ms-access database to Ms-Excel sheet using VB.NET
Thank you in advance:)

[Tschuikow]

With this Code you can write a DataTable into a excel-document:

it works very fine for me.

using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Data;

namespace Rainbird.Examples.SpreadsheetML
{
        public static void ExportDataTableToWorksheet(DataTable dataSource, string fileName)
        {

            XmlTextWriter writer = new XmlTextWriter(fileName, Encoding.UTF8);

            writer.Formatting = Formatting.Indented;

            writer.WriteStartDocument();

            // <?mso-application progid="Excel.Sheet"?>
            writer.WriteProcessingInstruction("mso-application", "progid=\"Excel.Sheet\"");

            // <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet >"
            writer.WriteStartElement("Workbook", "urn:schemas-microsoft-com:office:spreadsheet");

  
            writer.WriteAttributeString("xmlns", "o", null, "urn:schemas-microsoft-com:office:office");
            writer.WriteAttributeString("xmlns", "x", null, "urn:schemas-microsoft-com:office:excel");
            writer.WriteAttributeString("xmlns", "ss", null, "urn:schemas-microsoft-com:office:spreadsheet");
            writer.WriteAttributeString("xmlns", "html", null, "http://www.w3.org/TR/REC-html40");

            // <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
            writer.WriteStartElement("DocumentProperties", "urn:schemas-microsoft-com:office:office");

            // write document properties
            writer.WriteElementString("Author", Environment.UserName);
            writer.WriteElementString("LastAuthor", Environment.UserName);
            writer.WriteElementString("Created", DateTime.Now.ToString("u") + "Z");
            writer.WriteElementString("Company", "Unknown");
            writer.WriteElementString("Version", "11.8122");

            // </DocumentProperties>
            writer.WriteEndElement();

            // <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
            writer.WriteStartElement("ExcelWorkbook", "urn:schemas-microsoft-com:office:excel");

            // write sheet properties
            writer.WriteElementString("WindowHeight", "13170");
            writer.WriteElementString("WindowWidth", "17580");
            writer.WriteElementString("WindowTopX", "120");
            writer.WriteElementString("WindowTopY", "60");
            writer.WriteElementString("ProtectStructure", "False");
            writer.WriteElementString("ProtectWindows", "False");

            // </ExcelWorkbook>
            writer.WriteEndElement();

            // <Styles>
            writer.WriteStartElement("Styles");

            // <Style ss:ID="Default" ss:Name="Normal">
            writer.WriteStartElement("Style");
            writer.WriteAttributeString("ss", "ID", null, "Default");
            writer.WriteAttributeString("ss", "Name", null, "Normal");

            // <Alignment ss:Vertical="Bottom"/>
            writer.WriteStartElement("Alignment");
            writer.WriteAttributeString("ss", "Vertical", null, "Bottom");
            writer.WriteEndElement();


            writer.WriteElementString("Borders", null);
            writer.WriteElementString("Font", null);
            writer.WriteElementString("Interior", null);
            writer.WriteElementString("NumberFormat", null);
            writer.WriteElementString("Protection", null);

            // </Style>
            writer.WriteEndElement();

            // </Styles>
            writer.WriteEndElement();

            // <Worksheet ss:Name="xxx">
            writer.WriteStartElement("Worksheet");
            writer.WriteAttributeString("ss", "Name", null, dataSource.TableName);

            // <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="3" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="60">
            writer.WriteStartElement("Table");
            writer.WriteAttributeString("ss", "ExpandedColumnCount", null, dataSource.Columns.Count.ToString());
            writer.WriteAttributeString("ss", "ExpandedRowCount", null, dataSource.Rows.Count.ToString());
            writer.WriteAttributeString("x", "FullColumns", null, "1");
            writer.WriteAttributeString("x", "FullRows", null, "1");
            writer.WriteAttributeString("ss", "DefaultColumnWidth", null, "60");

            // loop through all records
            foreach (DataRow row in dataSource.Rows)
            {
                // <Row>
                writer.WriteStartElement("Row");

                // loop through all cells
                foreach (object cellValue in row.ItemArray)
                {
                    // <Cell>
                    writer.WriteStartElement("Cell");

                    // <Data ss:Type="String">xxx</Data>
                    writer.WriteStartElement("Data");
                    writer.WriteAttributeString("ss", "Type", null, "String");

                    // write cell value
                    writer.WriteValue(cellValue);

                    // </Data>
                    writer.WriteEndElement();

                    // </Cell>
                    writer.WriteEndElement();
                }
                // </Row>
                writer.WriteEndElement();
            }
            // </Table>
            writer.WriteEndElement();

            // <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
            writer.WriteStartElement("WorksheetOptions", "urn:schemas-microsoft-com:office:excel");


            writer.WriteStartElement("PageSetup");
            writer.WriteStartElement("Header");
            writer.WriteAttributeString("x", "Margin", null, "0.4921259845");
            writer.WriteEndElement();
            writer.WriteStartElement("Footer");
            writer.WriteAttributeString("x", "Margin", null, "0.4921259845");
            writer.WriteEndElement();
            writer.WriteStartElement("PageMargins");
            writer.WriteAttributeString("x", "Bottom", null, "0.984251969");
            writer.WriteAttributeString("x", "Left", null, "0.78740157499999996");
            writer.WriteAttributeString("x", "Right", null, "0.78740157499999996");
            writer.WriteAttributeString("x", "Top", null, "0.984251969");
            writer.WriteEndElement();
            writer.WriteEndElement();

            // <Selected/>
            writer.WriteElementString("Selected", null);

            // <Panes>
            writer.WriteStartElement("Panes");

            // <Pane>
            writer.WriteStartElement("Pane");


            writer.WriteElementString("Number", "1");
            writer.WriteElementString("ActiveRow", "1");
            writer.WriteElementString("ActiveCol", "1");

            // </Pane>
            writer.WriteEndElement();

            // </Panes>
            writer.WriteEndElement();

            // <ProtectObjects>False</ProtectObjects>
            writer.WriteElementString("ProtectObjects", "False");

            // <ProtectScenarios>False</ProtectScenarios>
            writer.WriteElementString("ProtectScenarios", "False");

            // </WorksheetOptions>
            writer.WriteEndElement();

            // </Worksheet>
            writer.WriteEndElement();

            // </Workbook>
            writer.WriteEndElement();

            // save file to disk
            writer.Flush();
            writer.Close();
        }
    }
}

[hungk53c]

But I wanted write with file excel has header, you can help me