Reading and Writing to Excel Files

Even though I posted similar 2 articles in 2008, the code of that article was having few errors. So thought to put more complete post on this.

The code will read an Excel file using an OleDbConnection and will write the same data back to another Excel file. This will explain how to read and write to Excel files. One thing to remember when running this code is to make sure the source Excel file is open. Otherwise you will get an error similar to “External table is not in the expected format.”

Also note that there are few connection string parameters you can use while opening Excel files.

HDR = Yes – Use when first row contains column headers.

HDR = No  - Use when first row contains data.

Excel xx.x – Use the following Table as a guide.

Parameter Value	Excel Version
Excel 12.0	Excel 2007 (Released in 2007)
Excel 11.0	Excel 2003 (Released in 2003)
Excel 10.0	Excel XP (Released in 2001)
Excel 9.0	Excel 2000 (Released in 1999)
Excel 8.0	Excel 97 (Released in 1997)

 

IMEX=1 – Use this when you want to treat all your data in the file as text.

For example - >

Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

To make the code to work properly you need to refer the Microsoft.Office.Interop.Excel assembly by adding a reference to your project.

1. using System;
2. using System.Data;
3. using System.Windows.Forms;
4. using System.Data.OleDb;
5. using Microsoft.Office.Interop.Excel;
7. namespace TestApp
8. {
9.     public partial class Form1 : Form
10.     {
11.         public Form1()
12.         {
13.             InitializeComponent();
14.         }
16.         private void button1_Click(object sender, EventArgs e)
17.         {
18.             openFileDialog1.ShowDialog();
19.             // Create an OLEDBConnection to connect to the Excel file.
20.             // I'm getting the required file by using a file dialog.
21.             // The @ symbol makes the string to contain any special characters inside the string without breaking the string.
22.             OleDbConnection dbConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + openFileDialog1.FileName.ToString() + @";Extended Properties=""Excel 8.0;HDR=Yes;""");
23.             // Open the connection.
24.             dbConnection.Open();
25.             // Create a command object to work on the data.
26.             // Note that I have given the sheet name as [Sheet1$] to retrieve data from that named sheet in the particular Excel file.
27.             OleDbCommand dbCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", dbConnection);
28.             // Creating a data reader to read data.
29.             OleDbDataReader dbReader = dbCommand.ExecuteReader();
30.             // If needed you can get the position of any column (e.g. Age), this will only work if you use HDR=Yes.
31.             int SearchingItem = dbReader.GetOrdinal("Age");
33.             // Create the Excel Application object.
34.             ApplicationClass ExcelApp = new ApplicationClass();
35.             // Set the visibility of the application.
36.             ExcelApp.Visible = true;
37.             // Create a new Excel Workbook.
38.             Workbook ExcelWorkbook = ExcelApp.Workbooks.Add(Type.Missing);
39.             // Create a new Excel Sheet.
40.             Worksheet ExcelSheet = (Worksheet)ExcelWorkbook.Sheets.Add(ExcelWorkbook.Sheets.get_Item(1), Type.Missing, 1, XlSheetType.xlWorksheet);
41.             // Will keep the current row index. This should start from 1 since the first row is 1.
42.             int CurrentRowIndex = 1;
43.             try
44.             {
45.                 // Read through the data.
46.                 while (dbReader.Read())
47.                 {
48.                     // Traverse through all the data columns.
49.                     for (int i = 0; i < dbReader.VisibleFieldCount; i++)
50.                     {
51.                         ExcelSheet.Cells[CurrentRowIndex, i + 1] = dbReader.GetValue(i);
52.                     }
53.                     CurrentRowIndex++;
54.                 }
55.                 // Save the Excel sheet.
56.                 // The @ symbol makes the string to contain any special characters inside the string without breaking the string.
57.                 ExcelApp.Save(@"C:\Projects\Ex.xls");
58.             }
59.             catch (Exception ex)
60.             {
61.                 MessageBox.Show(ex.ToString());
62.             }
63.         }
64.     }
65. }
66.