Read and Write Excel in C#
In this article I'm going to explain how to read and write data from Excel file with cell by cell. Recently I'm in a situation to update or modify the data in each cell. If I go with Excel there is no option to update the data cell wise but when we go with Excel we can read / write / update data cell by cell, but coming back to performance it's little bit down as compare with Csv but in a situation to read / write/ update cell information Excel is the best option.
Read and Write Excel:
Index:
1. Description
2. Read Excel File
3. Write Excel File
4. Source Code
5. Output
6. Conclusion1. Description:
In this article I'm going to explain how to read and write data from Excel file with cell by cell. Recently I'm in a situation to update or modify the data in each cell. If I go with Excel there is no option to update the data cell wise but when we go with Excel we can read / write / update data cell by cell, but coming back to performance it's little bit down as compare with Csv but in a situation to read / write/ update cell information Excel is the best option.
In this article I will show you how to handle Excel file using C#.
To achieve this you have to include the dll of Microsoft.Office.Interop.Excel dll as a reference to the project.
To add the dll right click on project reference and click on .NET tab and choose the Microsoft.Office.Interop.Excel and add it to your project reference.2. Read Excel File:
I just pass the path of the file as input and if you want to read data from particular sheet then pass the sheetName as input parameter, and return the data in table format and store the same in DataTable.
public static DataTable ReadExcel(string inputPath, string sheetName)
{
DataTable dtSheet = new DataTable();
try
{
#region Read data from excel
objApp.Visible = false;
//Open the Workbook
objWrkbook = objApp.Workbooks.Open(inputPath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Get all sheets available under the workbook
objSheets = (Excel.Sheets)objWrkbook.Worksheets;
// Get the particular sheet which you want to get the information
objWrkSheet = (Microsoft.Office.Interop.Excel.Worksheet)objSheets.get_Item(sheetName);
//Get the column count of the sheet
int colcnt = objWrkSheet.UsedRange.Columns.Count;
//Get Row count of the sheet
int rowcnt = objWrkSheet.UsedRange.Rows.Count;
DataRow dr;
Excel.Range rang;
for (int row = 1; row <= rowcnt; row++)
{
dr = dtSheet.NewRow();
for (int col = 1; col <= colcnt; col++)
{
rang = (Excel.Range)objWrkSheet.Cells[row, col];
if (row == 1)
{
// Read Headers from Excel Sheet and store it in DataTable
dtSheet.Columns.Add(Convert.ToString(rang.Value2));
}
else
{
// Read rows
dr[col - 1] = rang.Value2;
}
}
if (row != 1)
dtSheet.Rows.Add(dr);
dtSheet.AcceptChanges();
#endregion
}
}
catch (Exception ex)
{
throw;
}
finally
{
if (objWrkSheet != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(objWrkSheet);
if (objWrkbook != null)
objWrkbook.Close(false, inputPath, Type.Missing);
if (objApp != null)
objApp.Quit();
}
return dtSheet;
}3. Write Excel File:
This mehod help us to write data into Excel file with cell by cell, while handling with Excel we have to be very carefull to Release the objects once the work is done. If not it might be big issue while writing the next record and the file is unable to open since it is Read by some one else and it shows the error like File is ReadOnly mode it is being Edited by someone else.
public static void WriteExcel(DataRow drRow, string outputFilePath, string sheetName)
{
try
{
#region Write Headers to Excel file
objApp.Visible = true;
objApp.DisplayAlerts = false;
if (!string.IsNullOrEmpty(outputFilePath) && !File.Exists(outputFilePath))
{
//create workbook with sheet
#region create excel sheet
objWrkbook = objApp.Workbooks.Add(1);
objWrkSheet = (Excel.Worksheet)objWrkbook.ActiveSheet;
objWrkSheet.Name = "Sheet1";
#endregion
}
else
{
//open workbook with sheet
#region open excel sheet
objWrkbook = objApp.Workbooks.Open(outputFilePath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
#endregion
}
objSheets = (Excel.Sheets)objWrkbook.Worksheets;
//open the specific sheet in the workbook
objWrkSheet = (Microsoft.Office.Interop.Excel.Worksheet)objSheets.get_Item(sheetName);
int colcnt = objWrkSheet.UsedRange.Columns.Count;
int rowcnt = objWrkSheet.UsedRange.Rows.Count;
// write columns to the excel sheet
#region create headers to excel
if (colcnt == 1 && rowcnt == 1)
{
for (int i = 0; i < drRow.Table.Columns.Count; i++)
{
objWrkSheet.Cells[1, i + 1] = drRow.Table.Columns[i].ColumnName;
}
colcnt = drRow.Table.Columns.Count;
}
#endregion
//write rows to the excel sheet
#region insert rows to excel
for (int i = 1; i <= colcnt; i++)
{
objWrkSheet.Cells[rowcnt + 1, i] = drRow[i - 1];
}
#endregion
//save the workbook
#region save workbook
objWrkbook.SaveAs(outputFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
#endregion
#endregion
}
catch (Exception ex)
{
throw;
}
finally
{
//clear objects
#region clear workbook objects
if (objWrkSheet != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(objWrkSheet);
if (objWrkbook != null)
objWrkbook.Close(true, outputFilePath, true);
if (objApp != null)
objApp.Quit();
#endregion
}
}4. Source Code:
Source code of the project.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using log4net;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;
using System.IO;
namespace ExcelReadWrite
{
public static class ExcelReadWrite
{
private static Excel.Workbook objWrkbook = null;
private static Excel.Worksheet objWrkSheet = null;
private static Excel.Sheets objSheets = null;
private static Excel.Application objApp = new Excel.Application();
#region public methods
public static DataTable ReadExcel(string inputPath, string sheetName)
{
DataTable dtSheet = new DataTable();
try
{
#region Read data from excel
objApp.Visible = false;
objWrkbook = objApp.Workbooks.Open(inputPath, 0, false, 5, "",
"", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
objSheets = (Excel.Sheets)objWrkbook.Worksheets;
//int sheetcount = objSheets.Count;
objWrkSheet = (Microsoft.Office.Interop.Excel.Worksheet)objSheets.get_Item(sheetName);
int colcnt = objWrkSheet.UsedRange.Columns.Count;
int rowcnt = objWrkSheet.UsedRange.Rows.Count;
DataRow dr;
Excel.Range rang;
for (int row = 1; row <= rowcnt; row++)
{
dr = dtSheet.NewRow();
for (int col = 1; col <= colcnt; col++)
{
rang = (Excel.Range)objWrkSheet.Cells[row, col];
if (row == 1)
{
dtSheet.Columns.Add(Convert.ToString(rang.Value2));
}
else
{
dr[col - 1] = rang.Value2;
}
}
if (row != 1)
dtSheet.Rows.Add(dr);
}
dtSheet.AcceptChanges();
#endregion
}
catch (Exception ex)
{
throw;
}
finally
{
if (objWrkSheet != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(objWrkSheet);
if (objWrkbook != null)
objWrkbook.Close(false, inputPath, Type.Missing);
if (objApp != null)
objApp.Quit();
}
return dtSheet;
}
public static void WriteExcel(DataRow drRow, string outputFilePath, string sheetName)
{
try
{
#region Write Headers to Excel file
objApp.Visible = true;
objApp.DisplayAlerts = false;
if (!string.IsNullOrEmpty(outputFilePath) && !File.Exists(outputFilePath))
{
//create workbook with sheet
#region create excel sheet
objWrkbook = objApp.Workbooks.Add(1);
objWrkSheet = (Excel.Worksheet)objWrkbook.ActiveSheet;
objWrkSheet.Name = "Sheet1";
#endregion
}
else
{
//open workbook with sheet
#region open excel sheet
objWrkbook = objApp.Workbooks.Open(outputFilePath, 0, false, 5, "",
"", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
#endregion
}
objSheets = (Excel.Sheets)objWrkbook.Worksheets;
//open the specific sheet in the workbook
objWrkSheet = (Microsoft.Office.Interop.Excel.Worksheet)objSheets.get_Item(sheetName);
int colcnt = objWrkSheet.UsedRange.Columns.Count;
int rowcnt = objWrkSheet.UsedRange.Rows.Count;
// write columns to the excel sheet
#region create headers to excel
if (colcnt == 1 && rowcnt == 1)
{
for (int i = 0; i < drRow.Table.Columns.Count; i++)
{
objWrkSheet.Cells[1, i + 1] = drRow.Table.Columns[i].ColumnName;
}
colcnt = drRow.Table.Columns.Count;
}
#endregion
//write rows to the excel sheet
#region insert rows to excel
for (int i = 1; i <= colcnt; i++)
{
objWrkSheet.Cells[rowcnt + 1, i] = drRow[i - 1];
}
#endregion
//save the workbook
#region save workbook
objWrkbook.SaveAs(outputFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
#endregion
#endregion
}
catch (Exception ex)
{
throw;
}
finally
{
//clear objects
#region clear workbook objects
if (objWrkSheet != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(objWrkSheet);
if (objWrkbook != null)
objWrkbook.Close(true, outputFilePath, true);
if (objApp != null)
objApp.Quit();
#endregion
}
}
#endregion
}
}
5. Output:
6. Conclusion:
If you want to read the inputs from Excel and write the output result into Excel, I guess this article helps you a lot. Hope you enjoyed reuse this article and save your valuable time.
Follow