Click here to Skip to main content
15,033,215 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How to export data from ms access to ms excel in c#.net
Posted
Comments
Ranjithkumar54 22-Oct-12 8:02am
   
How to export data from generic list to excel file using streamwriter in console application

   
Below code works for me

Table in TST database
SQL
CREATE TABLE planets
(
name    varchar(50),
size    int
)

INSERT INTO planets
SELECT 'EARTH',1200


C# Code (Console Application)

using System;
using System.IO;
using System.Data.SqlClient;

namespace CP_316977
{
    class Program
    {
        static void Main(string[] args)
        {
            string path = @"D:\db.xls";
            if (!File.Exists(path))
            {
                // Create a file to write to.
                using (StreamWriter sw = File.CreateText(path))
                {
                    SqlConnection cn = new SqlConnection( "Data Source=MDT765;Initial Catalog=TST;User Id=sa;Password=sa@123;");

                    SqlCommand cmd = new SqlCommand("SELECT * FROM planets", cn);
                    try
                    {
                        cn.Open();
                        SqlDataReader dr = cmd.ExecuteReader();
                        while (dr.Read())
                        {
                            sw.WriteLine(dr["name"].ToString() + "\t" + dr["size"].ToString());
                        }

                        Console.WriteLine("Database has been exported.");
                    }
                    catch (Exception excpt)
                    {
                        Console.WriteLine(excpt.Message);
                    }
                }
   
            }
        }
    }
}
   
Hi,

Add reference below in your project

Microsoft office 12.0 controls library 


After that use below code
add namespace
using Microsoft.Office.Interop.Excel;

ApplicationClass excel = new ApplicationClass();
                Workbook wBook;
                Worksheet wSheet;
                wBook = excel.Workbooks.Add(System.Reflection.Missing.Value);
                wSheet = (Worksheet)wBook.ActiveSheet;
                System.Data.DataTable dt = dset.Tables[0];
                System.Data.DataColumn dc = new DataColumn();
                int colIndex = 0;
                int rowIndex = 4;
                foreach (DataColumn dcol in dt.Columns)
                {
                    colIndex = colIndex + 1;
                    excel.Cells[5, colIndex] = dcol.ColumnName;
                }
                foreach (DataRow drow in dt.Rows)
                {
                    rowIndex = rowIndex + 1;
                    colIndex = 0;
                    foreach (DataColumn dcol in dt.Columns)
                    {
                        colIndex = colIndex + 1;
                        excel.Cells[rowIndex + 1, colIndex] = drow[dcol.ColumnName];
                    }
                }
                wSheet.Columns.AutoFit();
                String strFileName = Server.MapPath("~\\Images\\StockStatement.xls");


Boolean blnFileOpen = false;
                try
                {
                    System.IO.FileStream fileTemp = File.OpenWrite(strFileName);
                    fileTemp.Close();
                }
                catch
                {
                    blnFileOpen = false;
                }
                if (System.IO.File.Exists(strFileName))
                {
                    System.IO.File.Delete(strFileName);
                }
                Range oRng;
                wSheet.Cells[1, 2] = lblOffice1.Text;
                wSheet.Cells[3, 2] = lblCostCenter1.Text;
                wSheet.Cells[4, 1] = lblOfficeName1.Text;
                wSheet.get_Range("B1", "B1").Font.Bold = true;
                wSheet.get_Range("B1", "B1").Font.ColorIndex = 55;
                wSheet.get_Range("B3", "B3").Font.ColorIndex = 55;
                wSheet.get_Range("A4", "A4").Font.ColorIndex = 55;
                wSheet.get_Range("B1", "E1").Merge(Type.Missing);
                wSheet.get_Range("B3", "E3").Merge(Type.Missing);
                wSheet.get_Range("B1", "B1").HorizontalAlignment = Constants.xlCenter;
                wSheet.get_Range("B3", "B3").HorizontalAlignment = Constants.xlCenter;
                wSheet.get_Range("B3", "B3").Font.Bold = true;
                wSheet.get_Range("A4", "A4").Font.Bold = true;
                wSheet.get_Range("A4", "A4").HorizontalAlignment = Constants.xlLeft;
                wSheet.get_Range("A5", "P5").Font.Bold = true;
                wSheet.get_Range("A5", "P5").Interior.ColorIndex = 43;
                wSheet.Name = "Stock Statement";
                //AutoFit columns A:D. 
                oRng = wSheet.get_Range("A1", "P1");
                oRng.EntireColumn.AutoFit();
                wBook.SaveAs(strFileName, XlFileFormat.xlExcel12, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, XlSaveAsAccessMode.xlShared, XlSaveConflictResolution.xlLocalSessionChanges, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false);
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900