Microsoft.Ace.OLEDB.12.0 Provider Not Registered

Recently while trying to import some data from Excel onto SQL on a new machine I got the following error at SQL Server Import and Export Wizard Step 2. After doing some searching found the solution is to install Office 2007 Data Connectivity Components System Driver.

If you need to  get it fixed use the following link to get it from Microsoft Download Center.

http://www.microsoft.com/en-us/download/details.aspx?id=23734

Automatically Sizing Excel Columns

Today while trying to do some formatting on Excel using .Net I came up with an error.

One of the things I tried is to make Excel columns automatically size according to the content having on them. As all of you might know we can get this done in Excel by simply double clicking on the column’s right margin. While doing this in code I got the following exception.

ExcelSheet.get_Range("A1", "E10")' threw an exception of type 'System.Runtime.InteropServices.COMException'
base {System.Runtime.InteropServices.ExternalException}: {"Exception from HRESULT: 0x800401A8"}

The code involved in generating this error is as below.

1. (ExcelSheet.get_Range("A1", "E10")).EntireColumn.AutoFit();

 

Later I found the reason for this error. Error will occur when we use AutoFit () on empty cells. Because initially I did not have anything in my excel sheet I kept on getting this. So to overcome this error use the same code to auto fit the cell contents simply after the cells are populated with values.

If you cannot get AutoFit () to work the reason might be the same thing. make sure the cells you apply auto fit have some values on them.

The best thing is to use AutoFit () after all data are entered into Excel sheet.

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. 
    

Creating an Excel Sheet using .Net

In this entry I will show how you can create a Microsoft Excel file using .Net.

// Create the Excel Application object.
ApplicationClass ExcelApp = new ApplicationClass();
// Set the visibility of the application.
ExcelApp.Visible = true;
// Create a new Excel Workbook.
Workbook ExcelWorkbook = ExcelApp.Workbooks.Add(Type.Missing);
// Create a new Excel Sheet.
Worksheet ExcelSheet = (Worksheet)ExcelWorkbook.Sheets.Add(ExcelWorkbook.Sheets.get_Item(1), Type.Missing, 1, XlSheetType.xlWorksheet);
try
{
// Loop for 10 rows.
for (int rwCount = 1; rwCount <= 10; rwCount++)

{

// Loop for 3 columns.

for (int clmCount = 1; clmCount <= 3; clmCount++)

{

ExcelSheet.Cells[rwCount, clmCount] = "This is Row - " + rwCount + " Column - " + clmCount;

}

}

// Save the Excel sheet.

// The @ symbol makes the string to contain any special characters inside the string without breaking the string.

ExcelApp.Save(@"C:\Projects\Ex.xls");

}

Accessing Data in an Excel File using .Net

In this article I will show how you can open Microsoft Excel files using .Net.
You can use OLE objects to access data in Excel files as of you are accessing SQL Server data using SQL objects.

// Create an OLEDBConnection to connect to the Excel file.
// I'm getting the required file by using a file dialog.
// The @ symbol makes the string to contain any special characters inside the string without breaking the string.
OleDbConnection dbConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + openFileDialog1.FileName.ToString()+ @";Extended Properties=""Excel 8.0;HDR=Yes;""");
// Open the connection.
dbConnection.Open();
// Create a command object to work on the data.
// Note that I have given the sheet name as [Sheet1$] to retrieve data from that named sheet in the particular Excel file.
OleDbCommand dbCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", dbConnection);
// Creating a data reader to read data.
OleDbDataReader dbReader = dbCommand.ExecuteReader();
// Get the position of the column Desc 1.
int SearchingItem = dbReader.GetOrdinal("Desc 1");
// Read through the data.
while (dbReader.Read())
{
// Traverse through all the data columns.
for (int i = 0; i <>
{
ExcelSheet.Cells[CurrentRowCount, i + 1] = dbReader.GetValue(i).ToString();
}
}