Excel sheet processing
It looks what is simpler than open file, modify something there and save it, but Excel file does not. Microsoft Excel is often used application and I think a lot people created different code to modify xls files using c# and surmounted the same problems. This short article with attached source code can be used as start point of your excel automation application to help you build it faster.
My task is modification following xls file:
to:
Need populate "Next Test" column with values equal sum of "Next Test" subtitle and "Last Test".
"Next Test" sub title is 34 and 31.
Lets start.
First of all need add Microsoft.Office.Interop.Excel and Microsoft.Office.Tools.Excel references to application.
First tricky: if your application use English Excel version but the regional settings for the
computer are configured for a non-English language then you need modify current culture before open xls file
and restore it back after close xls file. Otherwise you will get "Old Format or Invalid Type Library" error.
/// <summary>
/// Set "en-US" culture
/// </summary>
private void SetNewCurrentCulture()
{
this.oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
}
/// <summary>
/// Set back old culture
/// </summary>
private void ResetCurrentCulture()
{
System.Threading.Thread.CurrentThread.CurrentCulture = this.oldCI;
}
Now I can open xls file and go through rows inside try-catch because if I get some Exception after xls file was opened
then started Excel automation will be still up and lock xls file. It is second tricky.
If you do not use try-catch then after crash you need kill EXCEL process using task manager.
this.SetNewCurrentCulture();
Excel.Application xlsApp = new Excel.Application();
Excel.Workbooks xlsBooks = (Excel.Workbooks)xlsApp.Workbooks;
try
{
Excel._Workbook xlsBook = xlsBooks.Open(this.xlsFile.FullName, 0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "", true, false, 0, false, false, false);
// we use first sheet only
Excel.Worksheet xlsSheet = (Excel.Worksheet)xlsApp.Worksheets[1];
// first row is titles
for (int rowIndex = 2; rowIndex < xlsSheet.Rows.Count; rowIndex++)
{
............................
............................
............................
}
}
catch (Exception ex)
{
............................
}
finally
{
xlsBooks.Close();
xlsApp.Quit();
this.ResetCurrentCulture();
}
If you can not open xls file then need play with Open parameters.
Third tricky: to get or set cell value you need use Range propety Value2. It can be made as:
object dstObject = ((Excel.Range)xlsSheet.Cells[rowIndex, this.cellDestIndex]).Value2;
string dstValue = dstObject.ToString().Trim();
Why property name is Value2?, good question for Excel developers.
Fourth tricky: to get sheet row need use EntireRow property of Range.
For example to remove row I get cell then get row using EntireRow property and then run Delete with shift definition.
((Excel.Range)xlsSheet.Cells[rowIndex, this.cellDestIndex]).EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
Last trycky: by default excel sheet has 65K rows, usually no all rows are populated in the xls sheet and you need check when populated rows are finished and stop processing.
Now you can fast start developing your own excel automation application.
That's it.
Contact universal@vitana-group.com if you have any questions or found a bug.
Full source code you can get here.