How to uppercase all the words in excel spreadsheet using NPOI C#

Question

I need to convert all the words conataing in a sheet to uppercase using NPOI in C#; I can't find method to do this.

• Before applying uppercase : Cell[1;1]=stackoverflow
• After applying uppercase : Cell[1;1]=STACKOVERFLOW

Answer 1

I don't think it is possible without looping through cells using NPOI.
Probably it can be done using Interop to Excel since it is possible to select range in file and perform some actions on it (like in Excel), but NPOI doesn't offers such ability.

Howewer, you don't need to loop through all cells in sheet since there exists properties FirstRowNum and LastRowNum and they gives you range of rows actually containing data.

So your loop could look like (converting to uppercase all strings from the first worksheet of file):

var hssfwb;
using (var file = new FileStream(@"your_file.xls", FileMode.Open, FileAccess.Read))
{
    hssfwb = new HSSFWorkbook(file);
    file.Close();
}

var sheet = hssfwb.GetSheetAt(0);
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
    var row = sheet.GetRow(i);
    if (row != null)
    {
        foreach (ICell cell in row.Cells.Where(c => c.CellType == CellType.String))
            cell.SetCellValue(cell.StringCellValue.ToUpper());
    }
}