This project has moved and is read-only. For the latest updates, please go here.

Extra backslashes added to Numberformat AND HorizontalAlignment not working?

May 12, 2010 at 12:04 AM
Edited May 12, 2010 at 12:12 AM

I have two problems while trying to use the library.

1) I declared a cell style that contains a dollar sign ( $ ). When the file is saved, either EPPlus or Excel, I'm not sure which, adds an extra backslash in front of my format.

Example:

ExcelRange.Style.Numberformat.Format = @"$#,##0.00_);($#,##0.00)";

That's a literal/verbatim string. But if you open up the Excel spreadsheet generated, and look at the format used on the cell, it's listed as:

\$#,##0.00_);($#,##0.00)

2) This style is applied to the same cell mentioned above:

range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;

But when you open the file, it cell is not left aligned. Since it's a number, Excel automatically tries to right align it. Help?

May 12, 2010 at 7:59 AM

Hi

 

I had a look at your problems and I could not replicate your first problem (at least not with the latest source).

 

2) This is a bug. I have missed to add the General alignment to the Horizontal alignment enum and set the Left enum as default. That means left will be formated as general.

I have fixed it, but I cant check it in right now since I have a few unfinished things im working on. I'll try to check it in next week.

 

If you are in a hurry you can fix it your self. Add General to the ExcelHorizontalAlignment enum and change the default values from ExcelHorizontalAlignment.Left to ExcelHorizontalAlignment.General (a few places in the ExcelXfsXml class)

May 12, 2010 at 4:54 PM

Jan, thanks for the quick response. I'll put in your quick fix just for development purposes, and update the lib accordingly when you release a new revision.

I know the first problem is a little obscure. The Numberformat that I specified above is the same thing as the predefined format for Currency in Excel 2007. But when viewing the output cell, the format drop down lists it as "Custom." It should show up as Currency.

I found the extra backslash by right clicking on the cell > Format Cells. It will show the the format string in the "Type" input box. "\$#,##0.00_);(\$#,##0.00)"

If you remove the backslash and hit OK, the drop down changes to Currency, It looks like something is escaping the dollar sign symbols prior to output into the XML file.

May 17, 2010 at 8:51 PM

I'm not sure what's the wrong here, but try extract the EPPlus generated package and then create one in Excel with the format you want and do the same (just rename them xxx.xlsx.zip). 

Compare the formats in the xl\Styles.xml files.

My file started like this (with the lates source). So i dont know where the backslashes comes from.

<styleSheet xmlns:d="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <d:numFmts count="4">
    <numFmt numFmtId="164" formatCode="yyyy-mm-dd" />
    <numFmt numFmtId="165" formatCode="$#,##0.00_);($#,##0.00)" />

if you cant get it to work, please send me some sample code and what regional settings you are using

May 17, 2010 at 11:53 PM

Jan, thanks. I did what you described, and it turns out it was a conflict with Excel's use of the dollar symbol. So if you want to display the dollar sign in a cell, it needs to be escaped. Either by a backslash, or as the "Currency" format uses, by encasing the dollar symbol in double quotes. So basically, it should be like this:

ExcelRange.Style.Numberformat.Format = "\"$\"#,##0.00_);(\"$\"#,##0.00)";

Thanks for all of the help and patience.

Jul 22, 2010 at 12:12 PM

Hi juxtaposed,

 

i have a doubt i am using as

<font size="2">

worksheet.Cells[row, col].Style.Numberformat.Format =

</font>"\"$\"#,##0.00_);(\"$\"#,##0.00)";

it is converting in to currency. if we click on a cell it is giving that it as an error if i click on it , it is showing

as Number stored as text.

For Example if we open an new excel and write an integer value than it will automatically aligns right side. and in excel 2007

there is a currency format which shows in red color if any value as negative.

and one more doubt is "\"$\"#,##0.00_);(\"$\"#,##0.00)" whether we have to write in any EPPlus class(Such as excelnumberformat,format classes or)

 file or directly in the code page we can use it.

 

i hope some suggestions.