Posted By

Munawwar on 04/18/11

Tagged

excel

Versions (?)

Last Edited at 04/18/11 12:41am

Statistics

Viewed 1076 times

Favorited by 1 user(s)

Related snippets

C# .NET - Read Excel 2007 with OLE DB and convert data to CSV format

/ Published in: C#

Highlighted TextExpand

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

/*
 * Dependency: Office 2007 
 * OR 
 * Install 2007 Office System Driver - Data Connectivity Components from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en
 * No references or anything needed to be added.
 * 
 * Other Notes:
 * 1. There is a 64-bit version also. But I am using the 32-bit one.
 * 2. For Office 2010. there is a 'Microsoft Access Database Engine 2010 Redistributable' at http://www.microsoft.com/downloads/en/details.aspx?FamilyID=C06B8369-60DD-4B64-A44B-84B371EDE16D
 * 3. I am Using OLEDB here. ODBC can also be used.
 * 4. Code to read excel from http://www.dreamincode.net/forums/blog/1267/entry-3238-c%23-and-mvc3-uploading-and-parsing-an-excel-document-is-easier-than-it-seems/
 * 5. Code to read worskeets from http://www.codeproject.com/KB/aspnet/getsheetnames.aspx
 * 
 * I am doing two tasks here:
 * 1. Displaying all worksheet names from the XLSX file
 * 2. Echo the data from the file in CSV format
 */
namespace ExcelOleDbTest
{
    class ExcelOleDb
    {
        public static String[] getWorksheetList(String connectionString)
        {
            OleDbConnection objConn = null;
            DataTable sheets = null;
            try
            {
                objConn = new OleDbConnection(connectionString);
                objConn.Open(); // Open connection with the database.

// Get the data table containg the schema guid.
                sheets = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

// Add the sheet name to the string array.
                int k = 0;
                String[] worksheets = new String[sheets.Rows.Count];
                foreach (DataRow row in sheets.Rows)
                {
                    worksheets[k] = row["TABLE_NAME"].ToString().Replace("'", "").Replace("$", "");
                }
                return worksheets;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return null;
            }
            finally
            {
                // Clean up.

if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
                if (sheets != null)
                    sheets.Dispose();
            }
        }
        public static void echoAsCSV(string connectionString)
        {
            try
            {
                //Fill the dataset with information from the Sheet 1 worksheet.
                var adapter1 = new OleDbDataAdapter("SELECT * FROM [Feedback Results$]", connectionString);
                var ds = new DataSet();
                adapter1.Fill(ds, "results");
                DataTable data = ds.Tables["results"];

//Show all columns
                for (int i = 0; i < data.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < data.Columns.Count; j++)
                        Console.Write("\"" + data.Rows[i].ItemArray[j] + "\";");
                    Console.WriteLine();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
        static void Main(string[] args)
        {
            foreach(String arg in args) {
                Console.WriteLine(arg);
            }

//Create a connection string to access the Excel file using the ACE provider.
            //This is for Excel 2007. 2003 uses an older driver.
            //Change the location of the xlsx file
            var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;", "../../myxlsx/sample.xlsx");

//Echo worskeet list
            String[] worksheetList = getWorksheetList(connectionString);
            if (worksheetList != null)
                foreach (String worksheetName in worksheetList)
                    Console.WriteLine(worksheetName);

echoAsCSV(connectionString);

Console.Read();
        }
    }
}

Report this snippet Tweet