156-open-excel-doc-in-c

To get data from Excel in C# or to insert data into Excel in C#, we need to open Excel doc in C# first. There are two ways to access Excel data in C#, either

  • To use Microsoft.Office.Interop.Excel.dll namespace

or

  • To use OLEDB

For using Microsoft.Office.Interop.Excel.dll we need to download this dll file first. Next step is to add reference, like for any other dll included in your current project. We also need to use pre processor command that is:

using Excel = Microsoft.Office.Interop.Excel;

Now, we need to declare four objects in order to read Excel data from C# using Interop namespace:

  • Excel.Application xlsApp;
  • Excel.Workbook xlsWorkBook;
  • Excel.Worksheet xlsWorkSheet;
  • object misValue = System.Reflection.Missing.Value;

We just declared one instance of MS Excel application (xlsApp), one object of MS Excel workbook type (xlsWorkBook), one object of MS Excel worksheet type (xlsWorkSheet) and one object of missing value (misValue) that is necessary as parameter for opening MS Excel document. Then we need to create application object:

xlsApp = new Excel.Application();

To open workbook:
xlsWorkBook = xlsApp.Workbooks.Open(str_file_to_open, 0, true, 5, “”, “”, true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, “\t”, false, false, 0, true, 1, 0);

And to open worksheet:

xlsWorkSheet = (Excel.Worksheet)xlsWorkBook.Worksheets.get_Item(1);

Now, data are ready for reading. We can read worksheet name:

string str_name_of_worksheet = xlsWorkSheet.Name.ToString();

And we can read from the cells:

string str_content = xlsWorkSheet.get_Range(“B1”, “B1”).Value2.ToString();

This is particularly useful when we want to insert data insert data into Excel in C# by using OLEDB, so that we will know exactly worksheet name as well as column name.

We can also insert value into the cell:

xlsWorkSheet.Cells[1, 1].Value = “Write something”;

At the end we need to close application and the document:

xlsWorkBook.Close(true, misValue, misValue);
xlsApp.Quit();

Whole code is:

if(openFileDialog1.ShowDialog() == DialogResult.OK )
{

string str_file_to_open;
str_file_to_open = openFileDialog1.FileName;

Excel.Application xlsApp;
Excel.Workbook xlsWorkBook;
Excel.Worksheet xlsWorkSheet;
object misValue = System.Reflection.Missing.Value;

xlsApp = new Excel.Application();
xlsWorkBook = xlsApp.Workbooks.Open(str_file_to_open, 0, true, 5, “”, “”, true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, “\t”, false, false, 0, true, 1, 0);
xlsWorkSheet = (Excel.Worksheet)xlsWorkBook.Worksheets.get_Item(1);
string str_name_of_worksheet = xlsWorkSheet.Name.ToString();
MessageBox.Show(str_name_of_worksheet);
string str_content = xlsWorkSheet.get_Range(“B1”, “B1”).Value2.ToString();
MessageBox.Show(str_content);
xlsWorkSheet.Cells[1, 1].Value = “Write something”;
xlsWorkBook.Close(true, misValue, misValue);
xlsApp.Quit();

}

Another way to insert data into Excel in C# is to use OLEDB. For that purpose, we need to use following namespace:

using System.Data.OleDb;

We need now one OleDbConnection and one OleDbCommand object. Since access to the data is very sensitive to connection string accuracy, it is best to write code with try-catch structure.

System.Data.OleDb.OleDbConnection xlsConnection;
System.Data.OleDb.OleDbCommand xlsCommand = new System.Data.OleDb.OleDbCommand();
string sql = null;

Connection string is different for XLSX and XLS documents. So, filter in OpenDialog object should be following:

Excel files|*.xls|Excel 7.0 and above|*.xlsx

When excel file is chosen code must make difference for connection string depending of whether excel file is XLSX or XLS. For that purpose, string .EndsWith() is most convinient.

if (str_file_to_open.EndsWith(“XLSX”))
{

connectionString = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source='” + str_file_to_open + “‘;Extended Properties=Excel 12.0;”;

}
if (str_file_to_open.EndsWith(“XLS”))
{

connectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source='” + str_file_to_open + “‘;Extended Properties=Excel 8.0;”;

}

Connection to Excel file should be made:

xlsConnection = new System.Data.OleDb.OleDbConnection(connectionString);
xlsConnection.Open();
xlsCommand.Connection = xlsConnection;

We need now one data table object:

DataTable dtExcelSheetName = xlsConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

Name of the worksheet can be read as following:

string strExcelSheetName = dtExcelSheetName.Rows[0][“Table_Name”].ToString();

Sql command can be defined directly:

sql = “Insert into [Sheet1$] (Name,Surname) values(‘John’,’Smith’)”;

or we can read worksheet name first

sql = “Insert into [” + strExcelSheetName + “] (Name,Surname) values(‘John’,’Smith’)”;

Names of the columns can also be read:

sql = “Insert into [” + strExcelSheetName + “] ( + ” + xlsWorkSheet.get_Range(“B1”, “B1”).Value2.ToString() + “,” + xlsWorkSheet.get_Range(“c1”, “c1”).Value2.ToString() + “) values(‘John’,’Smith’)”;

xlsCommand.CommandText = sql;
xlsCommand.ExecuteNonQuery();
xlsConnection.Close();

Whole code is here:

if( openFileDialog2.ShowDialog() == DialogResult.OK )
{

string str_file_to_open;
str_file_to_open = openFileDialog2.FileName;
str_file_to_open = str_file_to_open.ToUpper();
this.Text = str_file_to_open;

System.Data.OleDb.OleDbConnection xlsConnection;
System.Data.OleDb.OleDbCommand xlsCommand = new System.Data.OleDb.OleDbCommand();
string sql = null;
string connectionString = “”;

if (str_file_to_open.EndsWith(“XLSX”))
{

connectionString = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source='” + str_file_to_open + “‘;Extended Properties=Excel 12.0;”;

}
if (str_file_to_open.EndsWith(“XLS”))
{

connectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source='” + str_file_to_open + “‘;Extended Properties=Excel 8.0;”;

}
xlsConnection = new System.Data.OleDb.OleDbConnection(connectionString);
xlsConnection.Open();
xlsCommand.Connection = xlsConnection;

DataTable dtExcelSheetName = xlsConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string strExcelSheetName = dtExcelSheetName.Rows[0][“Table_Name”].ToString();

sql = “Insert into [” + strExcelSheetName + “] (Name,Surname) values(‘John’,’Smith’)”;

xlsCommand.CommandText = sql;
xlsCommand.ExecuteNonQuery();
xlsConnection.Close();

}

External links:

Open Excel doc in C# with OLEDB in csharp.net-informations
Open Excel doc in C# in csharp.net-informations
Open Excel doc in C# in codeproject
Open Excel doc in C# in c-sharpcorner
Open Excel doc in C# in mindstick

Posted in c-sharp-code-examples

Leave a Reply

Your email address will not be published. Required fields are marked *

*