C# How To Read And Write Microsoft Excel Documents

Do you like this?

Summary:
This short post shows you how to modify MS Excel documents using C#


Content:

1) Add References for Microsoft Excel 12.0 Object Library
2) In the Properties window of Microsoft Excel 12.0 Object Library, change the property Embed Interop Types from True to False
3) The following snippet of code shows you an example how to read and change cells' values in Excel files.


using Excel = Microsoft.Office.Interop.Excel;

Excel.ApplicationClass oExcelApp = new Excel.ApplicationClass();
object readOnly = false;
object isVisible = true;
object missing = System.Reflection.Missing.Value;

Excel.Workbook oExcelWorkBook = oExcelApp.Workbooks.Open(filePath,
								missing, readOnly,
								missing, missing, missing,
								missing, missing, missing,
								missing, missing, missing,
								missing, missing, missing);
//
// Get sheet Count and store the number of sheets.
//
int numSheets = oExcelWorkBook.Sheets.Count;

//
// Iterate through the sheets. They are indexed starting at 1.
//
for (int sheetNum = 1; sheetNum < numSheets + 1; sheetNum++)
{
	Excel.Worksheet sheet = (Excel.Worksheet)oExcelWorkBook.Sheets[sheetNum];
	
	//
	// Take the used range of the sheet. 
	//
	Excel.Range excelRange = sheet.UsedRange;
	int RowCount = excelRange.Rows.Count;
	int ColumnCount = excelRange.Columns.Count;
	for (int r=1; r <= RowCount; r++){
		for (int c = 1; c <= ColumnCount; c++)
		{
			dynamic cell = excelRange.Cells[r,c];
			try
			{
				if (cell.Locked == false)
				{
					string content = cell.Value2;
					if (content!=null && !content.Trim().Equals("") )
					{
						content = content.Trim();
						cell.Value2 = cell.Value2 + " - This is a test";
					}
				}
			}
			catch (Exception) { 
				// we are using dynamic type for cell variable so
				// the variable might not have all the properties we used in our code
			}
			
		}
	}

}

oExcelWorkBook.Save();	
oExcelApp.Application.Quit();

 
comments powered by Disqus