CODEWITHSUNDEEP
c#excelepplus
Missy
Missy

Reputation: 1368

Formating Specific Columns Using Epplus Not Saving

I'm using EPPlus and iterating through all the columns of each worksheet in a workbook. I'm trying to format every column with a header that contains the word "NUMBER" as a number format. It runs and it hits the breakpoint where I set the number format but, when I open the spreadsheet, the columns are still formatted as text. Any help would very much be appreciated.

    private void cleanSpreadSheet(string fileName)
    {
        // set all columns with a header of number to numeric type
        FileInfo existingFile = new FileInfo(fileName);
        var package = new ExcelPackage(existingFile);

        ExcelWorkbook wb = package.Workbook;

        foreach (ExcelWorksheet workSheet in wb.Worksheets)
        {
            var start = workSheet.Dimension.Start;
            var end = workSheet.Dimension.End;
            for (int col = start.Column; col <= end.Column; col++)
            { // col by col
                if (workSheet.Cells[1, col].Text.ToUpper().Contains("NUMBER"))
                {
                    workSheet.Column(col).Style.Numberformat.Format = "0";                         
                }

            }
        }
        package.Save();
        package.Dispose();
        wb.Dispose();
    }

Upvotes: 0

Views: 886

Answers (1)

sfm
sfm

Reputation: 613

The issue may be that your existing Excel workbook has data formatted as Excel "Text". If this is the case, you won't likely be able to simply convert each cells' format to a number as Excel doesn't know how to convert "Text" to "Numbers".

Instead, you may need to iterate over each column and row in EPPlus and replace each value after casting them. The code below could use some error checking on the casts, but gives you a sense of the concept.

//foreach row ... foreach column... ExcelRange cell = worksheet.Cells[row, col]; cell.Value = double.Parse((string)cell.Value);

Upvotes: 2

Related Questions