none
How do I uotput data to excel? RRS feed

Answers

  • Sorry wrong post, this is about how to Read from Excel. But I don't delete is because meybe it is helpfull.

    You can do it with OleDB, the positive thing about it is that the client don't need to have Excel installed. Otherwise you can use the Microsoft Excel 10.0 Object Library, here is a article about it.




    public DataTable GetDataTableFromExcel( string filename, string sheetName )
    {
        OleDbConnection dbConn = null;
        DataTable resultTable = new DataTable( sheetName );

        try
        {
            // Build connection string.
            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + filename + ";Extended Properties=Excel 8.0;";

            // Create connection and open it.
            dbConn = new OleDbConnection( connString );
            dbConn.Open();

            if ( !sheetName.EndsWith( "$" ) )
            {
                sheetName += '$';
            }
            string query = string.Format( "SELECT * FROM [{0}]", sheetName );
            using ( OleDbDataAdapter adapter = new OleDbDataAdapter( query, dbConn ) )
            {
                adapter.Fill( resultTable );
            }

            return resultTable;
        }
        finally
        {
            if ( dbConn != null )
            {
                dbConn.Close();
                dbConn.Dispose();
            }
        }
    }
    Monday, October 16, 2006 9:17 AM
    Moderator
  • There is a great article on the code project about exporting a DataSetto an Excel file: Export a DataSet to Microsoft Excel without the use of COM objects. Otherwise you can use the Microsoft Excel 10.0 Object Library, here is a article about it.
    Monday, October 16, 2006 9:20 AM
    Moderator

All replies

  • Well, it depends on what kind of application you want...

    Here is a simple method, just write dat to an Excel file:

    public void WriteToExcel(string s)           
            
    {
          //the file name is the current date, you can define as you like
                   string path = @"C:" + DateTime.Now.ToLongDateString() + ".xls";
        //Check whether the file exits or not, if not, create it
                 
    if (!File.Exists(path))
                 
    {
                             FileStream file 
    = new FileStream(path, FileMode.Append, FileAccess.Write);
                     
    }

                
    using (StreamWriter sw = new StreamWriter(path,true))
                
    {
                           //write the string s to excel file
                             
      sw.WriteLine(s);
                        }
    }

    Hope it useful!

    Monday, October 16, 2006 9:06 AM
  • Sorry wrong post, this is about how to Read from Excel. But I don't delete is because meybe it is helpfull.

    You can do it with OleDB, the positive thing about it is that the client don't need to have Excel installed. Otherwise you can use the Microsoft Excel 10.0 Object Library, here is a article about it.




    public DataTable GetDataTableFromExcel( string filename, string sheetName )
    {
        OleDbConnection dbConn = null;
        DataTable resultTable = new DataTable( sheetName );

        try
        {
            // Build connection string.
            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + filename + ";Extended Properties=Excel 8.0;";

            // Create connection and open it.
            dbConn = new OleDbConnection( connString );
            dbConn.Open();

            if ( !sheetName.EndsWith( "$" ) )
            {
                sheetName += '$';
            }
            string query = string.Format( "SELECT * FROM [{0}]", sheetName );
            using ( OleDbDataAdapter adapter = new OleDbDataAdapter( query, dbConn ) )
            {
                adapter.Fill( resultTable );
            }

            return resultTable;
        }
        finally
        {
            if ( dbConn != null )
            {
                dbConn.Close();
                dbConn.Dispose();
            }
        }
    }
    Monday, October 16, 2006 9:17 AM
    Moderator
  • There is a great article on the code project about exporting a DataSetto an Excel file: Export a DataSet to Microsoft Excel without the use of COM objects. Otherwise you can use the Microsoft Excel 10.0 Object Library, here is a article about it.
    Monday, October 16, 2006 9:20 AM
    Moderator
  • That is very helpful.

    Thank you.

    Tuesday, October 17, 2006 3:56 AM