EPPlus: Copy Style for Range

Question

I would like to insert x-new rows / columns into the worksheet and apply the style of the row / column from which it was inserted (backgroundcolor / border, etc.).

This is how I add new lines:

xlsSheet.InsertRow(18, RowCount); 

Then I would like to copy / apply the "base" line style to the newly inserted lines:

 for (int i = 0; i < RowCount; i++) { xlsSheet.Cells[16, 1, 16, xlsSheet.Dimension.End.Column].Copy(xlsSheet.Cells[16 + i + 1, 1]); } 

But this code does not copy / apply the style of the "base" lines. At this point, I have a workaround with interop, but it takes years compared to epplus .: - /

Answer 1

I think they violated this part of the copy functions with version 4. See this:

http://epplus.codeplex.com/workitem/15068

So, you can simply manually set the style identifiers after the copy:

 [TestMethod] public void Copy_Styles_Test() { //http://stackoverflow.com/questions/31853046/epplus-copy-style-to-a-range //Throw in some data var datatable = new DataTable("tblData"); datatable.Columns.AddRange(new[] {new DataColumn("Col1", typeof (int)), new DataColumn("Col2", typeof (int)), new DataColumn("Col3", typeof (int)) }); for (var i = 0; i < 20; i++) { var row = datatable.NewRow(); row[0] = i; row[1] = i * 10; row[2] = i * 100; datatable.Rows.Add(row); } var existingFile = new FileInfo(@"c:\temp\test.xlsx"); if (existingFile.Exists) existingFile.Delete(); using (var pck = new ExcelPackage(existingFile)) { const int rowCount = 5; const int startRow = 18; //Show the data var xlsSheet = pck.Workbook.Worksheets.Add("Sheet1"); xlsSheet.Cells.LoadFromDataTable(datatable, true); //Throw in some styles for testing xlsSheet.Row(startRow).Style.Fill.PatternType = ExcelFillStyle.Solid; xlsSheet.Row(startRow).Style.Fill.BackgroundColor.SetColor(Color.Aqua); xlsSheet.Cells[String.Format("A{0}:C{0}", startRow)].Style.Fill.BackgroundColor.SetColor(Color.Red); //Insert new rows xlsSheet.InsertRow(startRow, rowCount); //Copy the cells and manually set the style IDs var copyrow = startRow + rowCount; for (var i = 0; i < rowCount; i++) { var row = startRow + i; xlsSheet.Cells[String.Format("{0}:{0}", copyrow)].Copy(xlsSheet.Cells[String.Format("{0}:{0}", row)]); xlsSheet.Row(row).StyleID = xlsSheet.Row(copyrow).StyleID; } //May not be needed but cant hurt xlsSheet.Cells.Worksheet.Workbook.Styles.UpdateXml(); //save it pck.Save(); } } 

Answer 2

You must define the worksheet as follows:

  string sheetName="Your Sheet Name"; ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName); 

Then you can use the following code to change the style of the whole sheet:

  Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#B8C9E9"); ws.Cells.Style.Fill.PatternType = ExcelFillStyle.Solid; ws.Cells.Style.Fill.BackgroundColor.SetColor(colFromHex); ws.Cells.Style.Border.Top.Style = ExcelBorderStyle.Medium; // . . . . . 

And use the following code to change the range style:

  Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#B8C9E9"); ws.Cells["A1:H16"].Style.Fill.PatternType = ExcelFillStyle.Solid; ws.Cells["A1:H16"].Style.Fill.BackgroundColor.SetColor(colFromHex); ws.Cells["A1:H16"].Style.Border.Top.Style = ExcelBorderStyle.Medium; 

Answer 3

In code 4.0.4:

 if (copyStylesFromRow > 0) { var cseS = new CellsStoreEnumerator<int>(_styles, copyStylesFromRow, 0, copyStylesFromRow, ExcelPackage.MaxColumns); //Fixes issue 15068 , 15090 while(cseS.Next()) { for (var r = 0; r < rows; r++) { _styles.SetValue(rowFrom + r, cseS.Column, cseS.Value); } } } 

it uses the value of copyStylesFromRow, but because of the sequence of code, it uses new line numbers. Therefore, if you want to insert 4 lines, starting from line 3:

 workbook.Worksheets[1].InsertRow(3,4,6); 

Inserts 4 new lines, starting from line 3, because line 3 is included, you must point to the 6th line. This is a mistake, but you can explain it.