In my web application, we used to import data from MS Excel file. Few days before one of the client representative posted an issue that he can’t import data from XLSX (MS Excel 2007 File Format) files. Previously we were using OLE DB provider to import data from Excel file (Checkout my previous post regarding How to Import / Export from C# : IMPORT / EXPORT DATA IN MS EXCEL USING C# ), but Microsoft worked on the Office 2007 file formats, and we are not able to connect to Excel using Microsoft.Jet.OLEDB. Yesterday I got a chance to work on this module, and thought of implementing XLSX support. After few searches I found a new provider from Microsoft to connect to Excel 2007 files, called Microsoft.ACE.OLEDB.12.0.
/// <summary>
/// Imports Data from Microsoft Excel File.
/// </summary>
/// <param name="FileName">Filename from which data need to import</param>
/// <returns>List of DataTables, based on the number of sheets</returns>
private List<DataTable> ImportExcel(string FileName)
{
List<DataTable> _dataTables = new List<DataTable>();
string _ConnectionString = string.Empty;
string _Extension = Path.GetExtension(FileName);
//Checking for the extentions, if XLS connect using Jet OleDB
if (_Extension.Equals(".xls", StringComparison.CurrentCultureIgnoreCase))
{
_ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};Extended Properties=Excel 8.0";
}
//Use ACE OleDb
else if (_Extension.Equals(".xlsx", StringComparison.CurrentCultureIgnoreCase))
{
_ConnectionString =
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0";
}
DataTable dataTable = null;
using (OleDbConnection oleDbConnection =
new OleDbConnection(string.Format(_ConnectionString, FileName)))
{
oleDbConnection.Open();
//Getting the meta data information.
//This DataTable will return the details of Sheets in the Excel File.
DataTable dbSchema = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables_Info, null);
foreach (DataRow item in dbSchema.Rows)
{
//reading data from excel to Data Table
using (OleDbCommand oleDbCommand = new OleDbCommand())
{
oleDbCommand.Connection = oleDbConnection;
oleDbCommand.CommandText = string.Format("SELECT * FROM [{0}]",
item["TABLE_NAME"].ToString());
using (OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter())
{
oleDbDataAdapter.SelectCommand = oleDbCommand;
dataTable = new DataTable(item["TABLE_NAME"].ToString());
oleDbDataAdapter.Fill(dataTable);
_dataTables.Add(dataTable);
}
}
}
}
return _dataTables;
}
The connection string also supports HDR attribute, which decides, whether the Excel file first row is header or not. It can be either Yes or No. It can be used with both Excel 2003 and Excel 2007 files.
Update: You can download the Microsoft.ACE.OLEDB.12.0 provider from Microsoft : 2007 Office System Driver: Data Connectivity Components
I dont understand the “TABLE_NAME” what is it referencing is it the worksheet name?
oleDbCommand.CommandText = string.Format(“SELECT * FROM [{0}]“, item["TABLE_NAME"].ToString());
and
dataTable = new DataTable(item["TABLE_NAME"].ToString());
Yes rob, it worksheet name only. I am getting the TABLE_NAME from this statement.
DataTable dbSchema = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables_Info, null);
Actually here is what i am trying to do , it doesnt throw and error. When this runs the dataGridview shows a horizontal scroll bar so something should be there. I have tried it with multiple xls and xlsx files…any ideas
private void button2_Click(object sender, EventArgs e)
{
this.openFileDialog1.FileName = “*.xls”;
if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
{
dataGridView1.DataSource = ImportExcel(openFileDialog1.FileName);
}
}
Hi Rob, this function returns collection of Data Tables, if you are using DataGridView you need to bind the first item of the list. Try this code
using (OpenFileDialog openFileDialog = new OpenFileDialog())
{
openFileDialog.Filter = “Excel Files|*.xls; *.xlsx”;
if (openFileDialog.ShowDialog(this) == DialogResult.OK)
{
ExcelImporter importer = new ExcelImporter();
this.dataGridView1.DataSource = importer.ImportExcel(openFileDialog.FileName).First();
}
}
It displays the contents of the first sheet in DataGridView, and ExcelImporter class contains the function of ImportExcel.
Hi Anuraj
I was trying to connect the returned List to a dataGridView
but when I do the bottom scroll bar appears but no data , tried it with many xls and xlsx files all with a Sheet1 sheet with data any ideas on whats going wrong?
private void button2_Click(object sender, EventArgs e)
{
this.openFileDialog1.FileName = “*.xls”;
if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
{
dataGridView1.DataSource = null;
dataGridView1.DataSource = ImportExcel(openFileDialog1.FileName);
dataGridView1.AutoGenerateColumns = false;
}
}
Please check my last comment reply. Let me know if its not working.
Hi Anuraj,
thanks for the code, but I’m struggling with the first row not being imported at all (probably
it’s being omiited as a column header). Is there a way to import all the rows (including first one) ?
Try using “HDR=Yes;” which indicates that the first row contains column names, not data. “HDR=No;” indicates the opposite.
Hi Again Anuraj
Its all working now returning all the info on the spread sheet, however I would like it to only return the spread sheet name “TableName”
I tired changing
oleDbCommand.CommandText = string.Format(“SELECT * FROM [{0}]“, item["TABLE_NAME"].ToString());
to
oleDbCommand.CommandText = string.Format(“SELECT TableName FROM [{0}]“, item["TABLE_NAME"].ToString());
or
oleDbCommand.CommandText = string.Format(“SELECT TABLE_NAM FROM [{0}]“, item["TABLE_NAME"].ToString());
none worked any ideas?
In this codings, what is ImportExcel?. while using this i an getting error. anyone can tell me?
In this codings, what is ImportExcel?. while using this i am getting error. anyone can tell me?
@sumitha Can you tell me what error you are getting?
Error 1 ‘Windows_import_data.Form2.ExcelImport(string)’: not all code paths return a value
Error 2 The type or namespace name ‘ExcelImporter’ could not be found (are you missing a using directive or an assembly reference?)
@Sumitha : I am not sure it will resolve your issue.
Error 1 ‘Windows_import_data.Form2.ExcelImport(string)’: not all code paths return a value – This error says the ExcelImport method should returns something, and its not returning anything. Please check the method.
Error 2 The type or namespace name ‘ExcelImporter’ could not be found (are you missing a using directive or an assembly reference?) – This error says Visual Studio could not able to resolve the ExcelImporter class. Please provide the namespace if any.
It will be great if you can provide the source code.
I’ve tried this code and adapted it to use an INSERT into an .xlsx sheet, and it works perfect on my local XP development machine but once deployed on the server, which is a 32 bits Windows 2003 machine, the .xlsx file i try to INSERT into, stays empty. The code runs fine, no errors or exceptions. I’m using AccessDatabaseEngine2010. What could be wrong?
@johan: Sorry I didn’t tried the code on Windows server machine. I think the code I provided works with XL 2007. For XL 2010, you need to modify the connection string like this Microsoft.ACE.OLEDB.14.0, instead of Microsoft.ACE.OLEDB.12.0.
hi,
what if I want to display the records of an excel file in a listview then save it on a database?can you please help me with the codes that I need to run.I am using MS Visual C# 2010 and MS Office 2007.
Thank you so much.