Last active
September 9, 2016 11:28
-
-
Save Munawwar/924404 to your computer and use it in GitHub Desktop.
C# .NET - Convert Excel 2007 (XLSX) to CSV using OLE DB
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
* 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 too. 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 | |
*/ | |
/* | |
* How to tweak the code | |
* | |
* You will have to change the path to the xlsx file and specify the worksheet name. Where? Read the comments as you go through the code. | |
*/ | |
using System; | |
using System.Collections.Generic; | |
using System.Linq; | |
using System.Text; | |
using System.Data; | |
using System.Data.OleDb; | |
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 containing the schema guid. | |
sheets = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); | |
// Add the sheet name to the string array. | |
int k = 0; | |
String temp; | |
String[] worksheets = new String[sheets.Rows.Count]; | |
foreach (DataRow row in sheets.Rows) | |
{ | |
temp=row["TABLE_NAME"].ToString(); | |
worksheets[k] = temp.Substring(1,temp.Length-3); | |
} | |
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, String worksheetName) | |
{ | |
try | |
{ | |
//Fill the dataset with information from the Sheet 1 worksheet. | |
var adapter1 = new OleDbDataAdapter("SELECT * FROM ["+worksheetName+"$]", 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 worksheet list | |
String[] worksheetList = getWorksheetList(connectionString); | |
if (worksheetList != null) | |
foreach (String worksheetName in worksheetList) | |
{ | |
Console.WriteLine("Worksheet Name:"+worksheetName); | |
Console.WriteLine("------------------------------"); | |
echoAsCSV(connectionString, worksheetName); | |
Console.WriteLine("------------------------------"); | |
} | |
Console.Read(); | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment