How to format numbers and paste formulas?

Mar 8, 2010 at 3:33 PM
Edited Mar 8, 2010 at 3:34 PM

Hi,

thanks for this library, this is really useful.

I have two questions:

1) When I use writer.PasteNumber, I always get an error message from Excel that the file is corrupted and must be restored (if I trust the source). Anyway, after clicking yes, everything seems to be OK, but the numbers are text formatted, so I have to convert them to numbers before I can proceed with the sheet. Could you give a short example how to paste an integer, a decimal with a given number of decimals (like "{0:n3}") and a currency (with the currency symbol)?

2) How can I paste a formula? writer.PasteText("C1", "=A1+B1") displays the formula in the cell, but does not calculate...

Best wishes

Michael

Mar 9, 2010 at 10:30 AM
Edited Mar 9, 2010 at 10:34 AM

In the meantime I found out the following:

1)

PasteNumber("A1", String.Format("{0:c}", decimalValue));

leads to an error when opening the xlsx file - and after letting Excel repair the issue, the number is formatted as a string.

To paste a currency, the following is necessary:

SpreadsheetStyle currencyStyle = SpreadsheetReader.GetDefaultStyle(doc);
string cSymbol = CultureInfo.CurrentCulture.NumberFormat.CurrencySymbol;
currencyStyle.AddFormat(new NumberingFormat()
   { NumberFormatId = (UInt32Value)164U, FormatCode = "\"" + cSymbol + "\"\\ #,##0.00;[Red]\"" + cSymbol + "\"\\ #,##0.00" });
StringBuilder _ammount = new StringBuilder();
_ammount.Append(Convert.ToString(decimalValue).Trim());
_ammount.Replace(CultureInfo.CurrentCulture.NumberFormat.NumberGroupSeparator, "");
_ammount.Replace(CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator, ".");
writer.PasteNumber("A1", _ammount.ToString(), currencyStyle);

Especially the need of replacing the local group separators and decimal separator is annoying.

2)

To add a function:

Cell sumCell = writer.FindCell("H3");
sumCell.CellFormula = new CellFormula("=SUM(A3:G3)");

That's pretty simple, and straightforward.

Hope it helps anyone else, and maybe someone finds a better solution, esp. for 1)

Best wishes
Michael

Mar 25, 2010 at 4:46 PM
do you know how to apply the cell formatting to the cell that you're applying a formula? I'm calculating the sum for some currency values (using your example above) and i'm not sure how to apply the same currency formatting to this formula cell.
Mar 25, 2010 at 4:54 PM

chart004,

should work like this:

writer.SetStyle("H3", currencyStyle);

Best wishes
Michael

Oct 19, 2010 at 5:25 PM

That didn't really work for me; in Excel it showed Custom Format instead of Currency. Since I only care about US Currency this worked just fine for me:

var n = new NumberingFormat();
n.NumberFormatId = 164;
n.FormatCode = "\"$\"#,##0.00";

var currencyStyle = SpreadsheetReader.GetDefaultStyle(doc);
currencyStyle.AddFormat(n);

writer.PasteNumber(cellName, decimalValue.ToString(), currencyStyle);

Feb 2, 2015 at 11:45 PM
Does anyone know the NumberFormatId for ""Number". Where can I get a list of Valid format id's?
Feb 3, 2015 at 3:55 AM
There's a list here: http://closedxml.codeplex.com/wikipage?title=NumberFormatId%20Lookup%20Table

NumberFormatID 1 is probably what you want.
Feb 3, 2015 at 1:42 PM
Thanks That's exactly what I needed. This might be a stupid question but, what are the fundamental differences differences between ooxml and closedxml?
Feb 3, 2015 at 1:59 PM
Well, the "fundamental" difference seems to be the license. As we have a GNU Library General Public License (LGPL) here, ClosedXML is published under the MIT license (which is less strict when re-using the package in any way).

Best wishes
Michael
Coordinator
Feb 3, 2015 at 2:03 PM
Fixed.
Feb 16, 2015 at 3:58 PM
James,

thank you so much for changing the license - I think this is a really good step!

Best wishes
Michael