Importing and Exporting a DataTable from/to an Excel file

Posted on May 2, 2013 by tuncalik

As explained in the related article, a MatrixTable of finaquant can easily be converted to or from a DataTable of the .net framework (ADO.net).

Data integration of excel with .net framework

That is, importing or exporting a MatrixTable from/to an excel sheet can be accomplished in two steps:

Import: (1) From Excel to DataTable (2) From DataTable to MatrixTable

Export: (1) From MatrixTable to DataTable (2) From DataTable to Excel

Data integration of .net with excel

Tables and values can be exchanged between .net and excel by using the methods in C# listed below.

The requirements for debugging and running these methods:

1) Microsoft Office and Excel must be installed
2) Following excel-related COM assemblies must be referenced:
Microsoft Office XX.0 Object Library
Microsoft Excel XX.0 Object Library
3) Following namespaces must be included in reference list in the code:
using System.Data;
using Excel = Microsoft.Office.Interop.Excel;

Feel free to use the code wherever you want provided that you leave any copyright information for Finaquant Analytics intact. We would appreciate any comment for possible corrections and improvements in the code.

Import DataTable from Excel Sheet using ODCB driver

This method reads a table in an excel sheet into a DataTable object using the ODBC adapter. It requires Microsoft Access Database Engine as driver software. The first line of the table in excel sheet must contain field names.

C# code for ReadTableFromExcelSheet_ODBC()
/// <summary>
/// Reads a table in an excel sheet into DataTable object using ODBC adapter.
/// First line of table must contain field names.
/// </summary>
/// <param name="ExcelFilePath">A valid file path like  @"C:\Users\John\Documents\Inventory.xlsx</param>
/// <param name="SheetName">Name of excel sheet</param>
/// <returns>Table</returns>
public static DataTable ReadTableFromExcelSheet_ODBC(string ExcelFilePath, string SheetName)
{
	// PARAMETER CHECKS
	if (ExcelFilePath == null || ExcelFilePath == "")
		throw new Exception("ReadTableFromExcelSheet_ODBC: Null or empty string FilePath");
	if (SheetName == null || SheetName == "")
		throw new Exception("ReadTableFromExcelSheet_ODBC: Null or empty string SheetName");
	if (! File.Exists(ExcelFilePath))
		throw new Exception("ReadTableFromExcelSheet_ODBC: Excel file is not found!");
 
	// parameter checks OK, continue...
	string ConnStr = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + ExcelFilePath + ";";
	string Sql = "SELECT * FROM [" + SheetName + "$]";
 
	try
	{
		var adp = new OdbcDataAdapter(Sql, ConnStr);
 
		// fill datatable
		DataTable dt = new DataTable();
		adp.Fill(dt);
		return dt;
	}
	catch (Exception ex)
	{
		throw new Exception("ReadTableFromExcelSheet_ODBC: " + ex.Message);
	}
}

Import DataTable from Excel Sheet using OLEDB driver

This method reads a table from an excel sheet into a DataTable using the OLEDB driver.

C# code for ReadTableFromExcelSheet_OLEDB
/// <summary>
/// Read DataTable from a sheet of excel file.
/// Header line of the table in in excel sheet must contain field names.
/// Copyrights: Finaquant Analytics - www.finaquant.com
/// </summary>
/// <param name="Tbl">DataTable input</param>
/// <param name="ExcelFilePath">A valid file path like  @"C:\Users\John\Documents\Inventory.xlsx</param>
/// <param name="SheetName">Name of excel sheet</param>
public static DataTable ReadTableFromExcelSheet_OLEDB(string ExcelFilePath, string SheetName)
{
	// var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", Filepath);
 
	// PARAMETER CHECKS
	if (ExcelFilePath == null || ExcelFilePath == "")
		throw new Exception("ReadTableFromExcelSheet_OLEDB: Null or empty string FilePath");
	if (SheetName == null || SheetName == "")
		throw new Exception("ReadTableFromExcelSheet_OLEDB: Null or empty string SheetName");
	if (!File.Exists(ExcelFilePath))
		throw new Exception("ReadTableFromExcelSheet_OLEDB: Excel file is not found!");
 
	// parameter checks OK, continue...
	// set extended property for connection string
	string ExtendedProperty;
	if (ExcelFilePath.Substring(ExcelFilePath.LastIndexOf('.')).ToLower() == ".xlsx")
		ExtendedProperty = "Excel 12.0 Xml";
	else if (ExcelFilePath.Substring(ExcelFilePath.LastIndexOf('.')).ToLower() == ".xls")
		ExtendedProperty = "Excel 8.0";
	else if (ExcelFilePath.Substring(ExcelFilePath.LastIndexOf('.')).ToLower() == ".xlsm")
		ExtendedProperty = "Excel 12.0 Macro";
	else if (ExcelFilePath.Substring(ExcelFilePath.LastIndexOf('.')).ToLower() == ".xlsb")
		ExtendedProperty = "Excel 12.0";
	else
		throw new Exception("ReadTableFromExcelSheet_OLEDB: Invalid file type!");
 
	// var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"", FilePath);
	var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=\"{1};HDR=Yes;IMEX=1\"", ExcelFilePath, ExtendedProperty);
 
	try
	{
		var adapter = new OleDbDataAdapter("SELECT * FROM [" + SheetName + "$]", connectionString);
 
		var TblOut = new DataTable();
		adapter.FillSchema(TblOut, SchemaType.Source);
		// fill table
		adapter.Fill(TblOut);
		return TblOut;
	}
	catch (Exception ex)
	{
		throw new Exception("ReadTableFromExcelSheet_OLEDB: Could not read table from excel file!\n" + ex.Message + "\n");
	}
}

Import DataTable from a Range in Excel Sheet

This method reads a table from a given range in an excel sheet into a DataTable.

C# code for ReadTableFromExcelRange
/// <summary>
/// Read table from a range in an excel sheet.
/// Header line of the table in in excel sheet must contain field names.
/// Copyrights: Finaquant Analytics - www.finaquant.com
/// </summary>
/// <param name="ExcelFilePath">A valid file path like  @"C:\Users\John\Documents\Inventory.xlsx</param>
/// <param name="SheetName">Name of excel sheet</param>
/// <param name="RangeStr">A valid range string like "B3:E10"</param>
/// <returns>Table</returns>
public static DataTable ReadTableFromExcelRange(string ExcelFilePath, string SheetName, string RangeStr)
{
	// PARAMETER CHECKS
	if (ExcelFilePath == null || ExcelFilePath == "")
		throw new Exception("ReadTableFromExcelRange: Null or empty string FilePath");
	if (SheetName == null || SheetName == "")
		throw new Exception("ReadTableFromExcelRange: Null or empty string SheetName");
	if (RangeStr == null || RangeStr == "")
		throw new Exception("ReadTableFromExcelRange: Null or empty string RangeStr");
	if (!File.Exists(ExcelFilePath))
		throw new Exception("ReadTableFromExcelRange: Excel file is not found!");
 
	// parameter checks OK ...
	object misValue = System.Reflection.Missing.Value;
	var xlApp = new Excel.Application();
	bool IfReadOnly = true;
	Excel.Worksheet xlWorkSheet = null;
	Excel.Workbook xlWorkBook = null;
 
	try
	{
		// open workbook
		xlWorkBook = xlApp.Workbooks.Open(ExcelFilePath, 0, IfReadOnly, 5, "", "", true,
				Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
 
		// check if sheet exists
		bool SheetExists = false;
 
		foreach (Excel.Worksheet sheet in xlWorkBook.Sheets)
		{
			if (sheet.Name.Equals(SheetName))
			{
				SheetExists = true;
				break;
			}
		}
 
		if (!SheetExists)
			throw new Exception("ReadTableFromExcelRange: Sheet is not found in excel file!");
 
		// get worksheet
		xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets.get_Item(SheetName);
 
		// get range object
		Excel.Range myRange = (Excel.Range)xlWorkSheet.get_Range(RangeStr);
 
		// initiate data table
		DataTable tbl = new DataTable();
 
		// get column names from header line
		for (int i = 0; i < myRange.Columns.Count; i++)
		{
			object CellValue = (object) myRange.Cells[1, i + 1].Value2;
			tbl.Columns.Add(CellValue.ToString(), typeof(object));
		}
 
		// read values row by row
		for (int i = 0; i < myRange.Rows.Count - 1; i++)
		{
			tbl.Rows.Add();
 
			for (int j = 0; j < myRange.Columns.Count; j++)
			{
				object CellValue = (object) myRange.Cells[2 + i, j + 1].Value2;
				tbl.Rows[i][j] = CellValue;
			}
		}
		// return data table
		return tbl;
	}
	catch (Exception ex)
	{
		throw new Exception("ReadTableFromExcelRange: " + ex.Message);
	}
	finally
	{
		try
		{
			releaseObject(xlWorkSheet);
			releaseObject(xlWorkBook);
			releaseObject(xlApp);
		}
		catch { }
	}
}
//Helper function for releasing unused resources
public static void releaseObject(object obj)
{
	try
	{
		System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
		obj = null;
	}
	catch (Exception ex)
	{
		obj = null;
		System.Diagnostics.Debug.WriteLine("Exception Occured while releasing object " + ex.ToString());
	}
	finally
	{
		GC.Collect();
	}
}

Read a Value from a Cell in Excel Sheet

This method reads the value of a cell of an excel sheet.

C# code for ReadValueFromExcelCell
/// <summary>
/// Read value from a cell of an excel sheet.
/// Copyrights: Finaquant Analytics - www.finaquant.com
/// </summary>
/// <param name="ExcelFilePath">A valid file path like  @"C:\Users\John\Documents\Inventory.xlsx</param>
/// <param name="SheetName">Name of excel sheet</param>
/// <param name="CellStr">A valid cell string like "B3"</param>
/// <returns>Scalar value</returns>
public static object ReadValueFromExcelCell(string ExcelFilePath, string SheetName, string CellStr)
{
	// PARAMETER CHECKS
	if (ExcelFilePath == null || ExcelFilePath == "")
		throw new Exception("ReadValueFromExcelCell: Null or empty string FilePath");
	if (SheetName == null || SheetName == "")
		throw new Exception("ReadValueFromExcelCell: Null or empty string SheetName");
	if (CellStr == null || CellStr == "")
		throw new Exception("ReadValueFromExcelCell: Null or empty string CellStr");
	if (!File.Exists(ExcelFilePath))
		throw new Exception("ReadValueFromExcelCell: Excel file is not found!");
 
	// parameter checks OK ...
	object misValue = System.Reflection.Missing.Value;
	var xlApp = new Excel.Application();
	bool IfReadOnly = true;
	Excel.Worksheet xlWorkSheet = null;
	Excel.Workbook xlWorkBook = null;
 
	try
	{
		// open workbook
		xlWorkBook = xlApp.Workbooks.Open(ExcelFilePath, 0, IfReadOnly, 5, "", "", true,
				Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
 
		// check if sheet exists
		bool SheetExists = false;
 
		foreach (Excel.Worksheet sheet in xlWorkBook.Sheets)
		{
			if (sheet.Name.Equals(SheetName))
			{
				SheetExists = true;
				break;
			}
		}
 
		if (!SheetExists)
			throw new Exception("ReadValueFromExcelCell: Sheet is not found in excel file!");
 
		// get worksheet
		xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets.get_Item(SheetName);
 
		// get range object
		Excel.Range myRange = (Excel.Range)xlWorkSheet.get_Range(CellStr);
 
		// initiate data table
		object CellValue = (object)myRange.Cells[1, 1].Value2;
		return CellValue;
	}
	catch (Exception ex)
	{
		throw new Exception("ReadValueFromExcelCell: " + ex.Message);
	}
	finally
	{
		try
		{
			releaseObject(xlWorkSheet);
			releaseObject(xlWorkBook);
			releaseObject(xlApp);
		}
		catch { }
	}
}

Export DataTable to Excel File

This method writes a DataTable into the first sheet of a new excel file. It makes the excel file open and visible for the user.

C# code for WriteTableToExcelFile
/// <summary>
/// Write table into a new excel file. 
/// Make excel file visible to user after writing.
/// </summary>
/// <param name="tbl">Table</param>
public static void WriteTableToExcelFile(DataTable tbl)
{
	if (tbl == null || tbl.Columns.Count == 0)
		throw new Exception("WriteTableToExcelFile: Null or empty input table!\n");
	try
	{
		// load excel, and create a new workbook
		Excel.Application excelApp = new Excel.Application();
		excelApp.Workbooks.Add();
 
		// single worksheet
		Excel._Worksheet workSheet = excelApp.ActiveSheet;
 
		// column headings
		for (int i = 0; i < tbl.Columns.Count; i++)
		{
			workSheet.Cells[1, (i + 1)] = tbl.Columns[i].ColumnName;
		}
 
		// rows
		for (int i = 0; i < tbl.Rows.Count; i++)
		{
			// to do: format datetime values before printing
			for (int j = 0; j < tbl.Columns.Count; j++)
			{
				workSheet.Cells[(i + 2), (j + 1)] = tbl.Rows[i][j];
			}
		}
 
		// make excel visible for user
		excelApp.Visible = true;
	}
	catch (Exception ex)
	{
		throw new Exception("WriteTableToExcelFile: " + ex.Message);
	}
}

Export DataTable to Excel Sheet

This method writes a DataTable into an excel sheet. A new sheet is created if the sheet with the given name does not exist.

C# code for WriteTableToExcelSheet
/// <summary>
/// Write table to excel sheet.
/// Copyrights: Finaquant Analytics - www.finaquant.com
/// </summary>
/// <param name="tbl">Table</param>
/// <param name="ExcelFilePath">File path for excel file. An error is raised if file is not found.</param>
/// <param name="SheetName">Name of sheet. A new sheet with given name is created if it does not exist.</param>
/// <param name="CellStr">A cell address like "B3". Upper left corner for the table to be inserted.</param>
/// <param name="ClearSheetContent">If true, clear all sheet content before writing table.</param>
/// <param name="SaveWorkbook">If true, save workbook without making it visible (open) to user.</param>
public static void WriteTableToExcelSheet(DataTable tbl, string ExcelFilePath, string SheetName,
	string CellStr = "A1", bool ClearSheetContent = true, bool SaveWorkbook = true)
{
	// PARAMETER CHECKS
	if (ExcelFilePath == null || ExcelFilePath == "")
		throw new Exception("WriteTableToExcelSheet: Null or empty string FilePath");
	if (SheetName == null || SheetName == "")
		throw new Exception("WriteTableToExcelSheet: Null or empty string SheetName");
	if (!File.Exists(ExcelFilePath))
		throw new Exception("WriteTableToExcelSheet: Excel file is not found!");
 
	// parameter checks OK ...
	object misValue = System.Reflection.Missing.Value;
	var xlApp = new Excel.Application();
	bool IfReadOnly = false;
	Excel.Worksheet xlWorkSheet = null;
	Excel.Workbook xlWorkBook = null;
 
	try
	{
		// open workbook
		xlWorkBook = xlApp.Workbooks.Open(ExcelFilePath, 0, IfReadOnly, 5, "", "", true,
				Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
 
		// check if sheet exists
		bool SheetExists = false;
 
		foreach (Excel.Worksheet sheet in xlWorkBook.Sheets)
		{
			if (sheet.Name.Equals(SheetName))
			{
				SheetExists = true;
				break;
			}
		}
 
		// create a new sheet if it doesnot exist
		if (SheetExists)
			xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets.get_Item(SheetName);
		else
		{
			xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets.Add();
			xlWorkSheet.Name = SheetName;
		}
 
		if (ClearSheetContent)
			xlWorkSheet.Cells.ClearContents();  // clear sheet content
 
		Excel.Range myCell = (Excel.Range)xlWorkSheet.get_Range(CellStr);
 
		// write column names as table header (first line)
		for (int i = 0; i < tbl.Columns.Count; i++)
		{
			myCell.Cells[1, i + 1] = tbl.Columns[i].ColumnName;
		}
 
		// write table entries
		DateTime dt;
		for (int i = 0; i < tbl.Rows.Count; i++)
		{
			// to do: format datetime values before printing
			for (int j = 0; j < tbl.Columns.Count; j++)
			{
				if (tbl.Columns[j].DataType == typeof(DateTime))
				{
					dt = (System.DateTime)tbl.Rows[i][j];
					myCell.Cells[i + 2, j + 1] = dt.ToShortDateString();
				}
				else
				{
					myCell.Cells[i + 2, j + 1] = tbl.Rows[i][j].ToString();
				}
			}
		}
 
		if (SaveWorkbook)
		{
			xlWorkBook.Save();
			xlWorkBook.Close(true, misValue, misValue);
			xlApp.Quit();
		}
		else
		{
			xlWorkSheet.Activate();
			xlApp.Visible = true;
		}
	}
	catch (Exception ex)
	{
		throw new Exception("WriteTableToExcelSheet: " + ex.Message);
	}
	finally
	{
		try
		{
			releaseObject(xlWorkSheet);
			releaseObject(xlWorkBook);
			releaseObject(xlApp);
		}
		catch { }
	}
}

Write a Value into a Cell of Excel Sheet

This methods writes a given value into a cell of excel sheet.

C# code for WriteValueToExcelCell
/// <summary>
/// Write a value into a cell of excel file.
/// Copyrights: Finaquant Analytics - www.finaquant.com
/// </summary>
/// <param name="CellValue">Value</param>
/// <param name="ExcelFilePath">File path for excel file. An error is raised if file is not found.</param>
/// <param name="SheetName">Name of sheet. A new sheet with given name is created if it does not exist.</param>
/// <param name="CellStr">A cell address like "B3" for the value to be inserted.</param>
/// <param name="ClearSheetContent">If true, clear all sheet content before writing table.</param>
/// <param name="SaveWorkbook">If true, save workbook without making it visible (open) to user.</param>
public static void WriteValueToExcelCell(object CellValue, string ExcelFilePath, string SheetName,
	string CellStr, bool ClearSheetContent = false, bool SaveWorkbook = true)
{
	// PARAMETER CHECKS
	if (CellValue == null)
		throw new Exception("WriteValueToExcelCell: Null-valued cell value");
	if (ExcelFilePath == null || ExcelFilePath == "")
		throw new Exception("WriteValueToExcelCell: Null or empty string FilePath");
	if (SheetName == null || SheetName == "")
		throw new Exception("WriteValueToExcelCell: Null or empty string SheetName");
	if (!File.Exists(ExcelFilePath))
		throw new Exception("WriteValueToExcelCell: Excel file is not found!");
 
	// parameter checks OK ...
	object misValue = System.Reflection.Missing.Value;
	var xlApp = new Excel.Application();
	bool IfReadOnly = false;
	Excel.Worksheet xlWorkSheet = null;
	Excel.Workbook xlWorkBook = null;
 
	try
	{
		// open workbook
		xlWorkBook = xlApp.Workbooks.Open(ExcelFilePath, 0, IfReadOnly, 5, "", "", true,
				Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
 
		// check if sheet exists
		bool SheetExists = false;
 
		foreach (Excel.Worksheet sheet in xlWorkBook.Sheets)
		{
			if (sheet.Name.Equals(SheetName))
			{
				SheetExists = true;
				break;
			}
		}
 
		// create a new sheet if it doesnot exist
		if (SheetExists)
			xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets.get_Item(SheetName);
		else
		{
			xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets.Add();
			xlWorkSheet.Name = SheetName;
		}
 
		if (ClearSheetContent)
			xlWorkSheet.Cells.ClearContents();  // clear sheet content
 
		Excel.Range myCell = (Excel.Range)xlWorkSheet.get_Range(CellStr);
 
		myCell.Cells[1, 1] = CellValue;
 
		if (SaveWorkbook)
		{
			xlWorkBook.Save();
			xlWorkBook.Close(true, misValue, misValue);
			xlApp.Quit();
		}
		else
		{
			xlWorkSheet.Activate();
			xlApp.Visible = true;
		}
	}
	catch (Exception ex)
	{
		throw new Exception("WriteValueToExcelCell: " + ex.Message);
	}
	finally
	{
		try
		{
			releaseObject(xlWorkSheet);
			releaseObject(xlWorkBook);
			releaseObject(xlApp);
		}
		catch { }
	}
}

References and useful links

  1. Microsoft Access Database Engine 2010 Redistributable
  2. Read Excel Sheet Data into DataTable
  3. Microsoft.Jet.OLEDB.4.0′ provider is not registered on the local machine
Digiprove sealCopyright secured by Digiprove © 2013 Tunc Ali Kütükcüoglu
Share
This entry was posted in Calculation engine and tagged , , . Bookmark the permalink.

5 Responses to Importing and Exporting a DataTable from/to an Excel file

  1. selmar says:
    May 4, 2013 at 12:24 pm

    By chance I was working in a project for excel integration, this article helped a lot. Thanks.

    One question: What is releaseObject() in ReadTableFromExcelRange()? If this is a method it is maybe missing in the code. Selina

  2. Avatar photo tuncalik says:
    May 5, 2013 at 10:37 pm

    Hello Selina, yes you are right. This piece of code was intially forgotton. The required helper method releaseObject() is then appended to the code for ReadTableFromExcelRange().

  3. merinos says:
    May 9, 2013 at 10:00 pm

    Thank you for the source code for excel integration. A question: What is the difference of matrixtable compared to datatable? What do I need the matrixtable for?

  4. Avatar photo admin says:
    May 14, 2013 at 7:22 pm

    A MatrixTable has a simplified data structure compared to a DataTable with primary focus on analytical table functions like table addition, table multiplication, table combination, filtering, sampling and so on.
    For more information you may visit:
    http://software.tuncalik.com/converting-a-matrixtable-to-a-datatable-and-vice-versa/3452
    and
    http://software.tuncalik.com/help-pages-for-finaquant-products/text-and-numeric-attributes-key-figures

Leave a Reply