This project has moved. For the latest updates, please go here.

Styles from templates are reset

May 11, 2010 at 7:43 PM

I have a custom style defined in a template file that I load in from an existing Excel file. The custom style is called "MyCustomStyle," and it has formatting that includes: Left Justify, middle vertical alignment.

Cell G9 has MyCustomStyle applied to it in the template. After the template is processed through EPPlus, the MyCustomStyle is still in the file, but the formatting for that style has been reset to the defaults. This happens regardless of whether I change the values in any cells or not. Help?

 

var templateFile = new FileInfo(@"C:\template.xlsx");

var pckTemplate = new ExcelPackage(templateFile);

var xlPackage = new ExcelPackage(new MemoryStream(), pckTemplate.Stream);
var worksheet = xlPackage.Workbook.Worksheets["Sheet1"];

worksheet.Name = "Yay New Name!";

worksheet.Cells["G9"].Value = 423.82;

Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;  filename=" + filename);
Response.BinaryWrite(xlPackage.GetAsByteArray());

May 12, 2010 at 12:09 AM
Edited May 12, 2010 at 12:14 AM
I guess I'll answer my own question. It doesn't look like EPPlus retains custom styles defined in a template. I just implemented a helper class that applies the desired styles to a range of cells.
using OfficeOpenXml;
using OfficeOpenXml.Style;

namespace MyNamespace
{
    public static class EpplusHelper
    {
        public static void setCellStyle1 (ExcelRange range)
        {
            range.Style.Font.Color... // blah blah.
        }

    }
}

 The helper class is used as such:

var xlPackage = new ExcelPackage(new MemoryStream(), pckTemplate.Stream);
var worksheet = xlPackage.Workbook.Worksheets["Sheet1"];

// you can send it an individual cell or a range, like A1-E9
EpplusHelper.setCellStyle1(worksheet.Cells["G9"]);
Coordinator
May 12, 2010 at 8:03 AM

I fixed a problem with namedstyles in templates some time ago, so try the new source and see it that helps. I'll have a closer look to this when I have the time.