EPPlus, Find and Set a Value for a Named Range

Question

I am pulling my hair out trying to set the value of the named range (in this case, one named cell) using the ExcelPackage library (3.0.1), it should be simple:

ExcelNamedRange er = xlPackage.Workbook.Names["Customer"]; er.Value = "Foo Bar"; 

I obviously am doing it wrong - does anyone have an example that I can execute

thanks

Answer 1

I searched the ExcelPackage documentation to see how the collection of Type Names [] types returned, and found that the documentation was coming up soon, or at least that's what they talked about in 2007.

I suggest you use EPPlus, which is an excel library (xlsx only), which worked great for me.

official link

Basic usage instructions

Now, to set the value for each cell in the named range:

 ExcelWorksheet sheet = _openXmlPackage.Workbook.Worksheets["SheetName"]; using (ExcelNamedRange namedRange = sheet.Names["RangeName"]) { for (int rowIndex = Start.Row; rowIndex <= namedRange.End.Row; rowIndex++) { for (int columnIndex = namedRange.Start.Column; columnIndex <= namedRange.End.Column; columnIndex++) { sheet.Cells[rowIndex, columnIndex].Value = "no more hair pulling"; } } } 

Answer 2

I had to work using the cell value.

 using (ExcelPackage xlPackage = new ExcelPackage(newFile)) { foreach (ExcelWorksheet worksheet in xlPackage.Workbook.Worksheets) { var dimension = worksheet.Dimension; if (dimension == null) { continue; } var cells = from row in Enumerable.Range(dimension.Start.Row, dimension.End.Row) from column in Enumerable.Range(dimension.Start.Column, dimension.End.Column) //where worksheet.Cells[row, column].Value.ToString() != String.Empty select worksheet.Cells[row, column]; try { foreach (var excelCell in cells) { try { if (excelCell.Value.ToString().Equals("[Customer]")) { excelCell.Value = "Customer Name"; } } catch (Exception) { } } } catch (Exception a) { Console.WriteLine(a.Message); } }