5

I'm having an issue when I try to change the format of an entire column.

My issue is that I don't get the XLCellValues operand. I am able to choose XLDataType.Text but that does not work. This is what I have:

eventSheet.Column("A").CellsUsed().SetDataType(XLDataType.Text); I have also tried replacing the A with 1. No luck that way either. Could it be a newer revision of the ClosedXML package? Thank you!

Sebastian Mora
  • 51
  • 1
  • 2

1 Answers1

9

The IXLCell.SetDataType method does not change the display format. It just changes the type of the underlying value. To change the display format, use IXLCell.Style.NumberFormat

Example:

var workbook = new XLWorkbook();
var ws = workbook.Worksheets.Add("Style NumberFormat");

var co = 2;
var ro = 1;

// Using a custom format
ws.Cell(++ro, co).Value = "123456.789";
ws.Cell(ro, co).Style.NumberFormat.Format = "$ #,##0.00";

ws.Cell(++ro, co).Value = "12.345";
ws.Cell(ro, co).Style.NumberFormat.Format = "0000";

// Using a OpenXML's predefined formats
ws.Cell(++ro, co).Value = "12.345";
ws.Cell(ro, co).Style.NumberFormat.NumberFormatId = 3;

ws.Column(co).AdjustToContents();

workbook.SaveAs("StylesNumberFormat.xlsx");

You can find more information on the ClosedXML wiki, e.g. at https://github.com/ClosedXML/ClosedXML/wiki/Styles-NumberFormat

Francois Botha
  • 4,520
  • 1
  • 34
  • 46
  • 1
    To format as US dollar currency use: cells.Style.NumberFormat.Format = @"[$$-en-US]#,##0.00_);[Red]([$$-en-US]#,##0.00)" (no need to set NumberFormatId) – Tolga Jun 07 '21 at 02:15
  • And remember that Excel column numbers start at 1, not zero. – Silver Sagely Dec 15 '22 at 22:49