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


/ Published in: C#
Save to your folder(s)



Copy this code and paste it in your HTML
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using System.Data;
  6. using System.Data.OleDb;
  7.  
  8. /*
  9.  * Dependency: Office 2007
  10.  * OR
  11.  * 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
  12.  * No references or anything needed to be added.
  13.  *
  14.  * Other Notes:
  15.  * 1. There is a 64-bit version also. But I am using the 32-bit one.
  16.  * 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
  17.  * 3. I am Using OLEDB here. ODBC can also be used.
  18.  * 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/
  19.  * 5. Code to read worskeets from http://www.codeproject.com/KB/aspnet/getsheetnames.aspx
  20.  *
  21.  * I am doing two tasks here:
  22.  * 1. Displaying all worksheet names from the XLSX file
  23.  * 2. Echo the data from the file in CSV format
  24.  */
  25. namespace ExcelOleDbTest
  26. {
  27. class ExcelOleDb
  28. {
  29. public static String[] getWorksheetList(String connectionString)
  30. {
  31. OleDbConnection objConn = null;
  32. DataTable sheets = null;
  33. try
  34. {
  35. objConn = new OleDbConnection(connectionString);
  36. objConn.Open(); // Open connection with the database.
  37.  
  38. // Get the data table containg the schema guid.
  39. sheets = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
  40.  
  41. // Add the sheet name to the string array.
  42. int k = 0;
  43. String[] worksheets = new String[sheets.Rows.Count];
  44. foreach (DataRow row in sheets.Rows)
  45. {
  46. worksheets[k] = row["TABLE_NAME"].ToString().Replace("'", "").Replace("$", "");
  47. }
  48. return worksheets;
  49. }
  50. catch (Exception ex)
  51. {
  52. Console.WriteLine(ex.Message);
  53. return null;
  54. }
  55. finally
  56. {
  57. // Clean up.
  58.  
  59. if (objConn != null)
  60. {
  61. objConn.Close();
  62. objConn.Dispose();
  63. }
  64. if (sheets != null)
  65. sheets.Dispose();
  66. }
  67. }
  68. public static void echoAsCSV(string connectionString)
  69. {
  70. try
  71. {
  72. //Fill the dataset with information from the Sheet 1 worksheet.
  73. var adapter1 = new OleDbDataAdapter("SELECT * FROM [Feedback Results$]", connectionString);
  74. var ds = new DataSet();
  75. adapter1.Fill(ds, "results");
  76. DataTable data = ds.Tables["results"];
  77.  
  78. //Show all columns
  79. for (int i = 0; i < data.Rows.Count - 1; i++)
  80. {
  81. for (int j = 0; j < data.Columns.Count; j++)
  82. Console.Write("\"" + data.Rows[i].ItemArray[j] + "\";");
  83. Console.WriteLine();
  84. }
  85. }
  86. catch (Exception ex)
  87. {
  88. Console.WriteLine(ex.Message);
  89. }
  90. }
  91. static void Main(string[] args)
  92. {
  93. foreach(String arg in args) {
  94. Console.WriteLine(arg);
  95. }
  96.  
  97. //Create a connection string to access the Excel file using the ACE provider.
  98. //This is for Excel 2007. 2003 uses an older driver.
  99. //Change the location of the xlsx file
  100. var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;", "../../myxlsx/sample.xlsx");
  101.  
  102. //Echo worskeet list
  103. String[] worksheetList = getWorksheetList(connectionString);
  104. if (worksheetList != null)
  105. foreach (String worksheetName in worksheetList)
  106. Console.WriteLine(worksheetName);
  107.  
  108. echoAsCSV(connectionString);
  109.  
  110. Console.Read();
  111. }
  112. }
  113. }

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.