Excel show cell as fractions instead text?

Mar 14, 2009 at 6:07 PM
 I tried to use ExcelPackage, I used example download and changed 1 line code in sample1 as :
     cell.Value = "00009888876745635";  // tins of Peas sold
As you can see, I set cell.value is a string(all are digits) and after I run project and viewed file sample1.xlsx, that cell showed 9.88888E+12
Can you help me find a solution to show exactly in that cell=00009888876745635
Thanks and bests regards,


 
Mar 16, 2009 at 7:37 AM
If I'm not mistaken, you should be able to solve this with number formatting, but in order to apply number formatting, you need to impliment styles on the speadsheet.

I have been working on styles for the last week or so and have made some progress, but there is alot of work that still needs to happen to get it to work according to the specification.

I just need to add borders then I'll upload a patch so that more people can work on the code to help improve it.

Hopefully the styling would solve your problem. I'll do a bit of testing today and see if it does, if it doesn't, I'll see if i can find a solution.

Regards,

MS
Mar 16, 2009 at 7:09 PM
Thanks for reply,
 I had tried to use file template and used example3, I format cell C5 in template file is Text and set value = 00009888876745635, it showed as Text. After I run project, it will fill some row and column C (C5, C6, C7 ...) still format is Text but value showed in excel as fractions or number (00009888876745635 => 9.88888E+12; 0021 => 21).
 So I think that you should check function cell.Value, there could convert string to fractions/number.
Thanks and regards,
 TT
Jul 23, 2009 at 1:36 PM

I need this functionality ,too, is there any solution?

thanks~

Nov 27, 2009 at 2:50 PM

Hello all,

I too required this functionality for assigning format strings but didn't want to go through the pain of writing the whole styles component. I've come up with a quick and dirty workaround that appears to work perfectly. Some of the code is VB so I apologize to all the C#ers out there.

First thing I did was to add the following line right after line 244 in ExcelWorkbook.cs:

_xmlStyles.LoadXml("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?><styleSheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\"><fonts count=\"1\"><font><sz val=\"11\"/><color theme=\"1\"/><name val=\"Calibri\"/><family val=\"2\"/><scheme val=\"minor\"/></font></fonts><fills count=\"2\"><fill><patternFill patternType=\"none\"/></fill><fill><patternFill patternType=\"gray125\"/></fill></fills><borders count=\"1\"><border><left/><right/><top/><bottom/><diagonal/></border></borders><cellStyleXfs count=\"1\"><xf numFmtId=\"0\" fontId=\"0\" fillId=\"0\" borderId=\"0\"/></cellStyleXfs><cellXfs count=\"1\"><xf numFmtId=\"0\" fontId=\"0\" fillId=\"0\" borderId=\"0\" xfId=\"0\"/></cellXfs><cellStyles count=\"1\"><cellStyle name=\"Normal\" xfId=\"0\" builtinId=\"0\"/></cellStyles><dxfs count=\"0\"/><tableStyles count=\"0\" defaultTableStyle=\"TableStyleMedium9\" defaultPivotStyle=\"PivotStyleLight16\"/></styleSheet>");

I then commented out all the code between it and "// save it to the package" I realize that it's cheating, but I didn't feel like writing all the code to generate the XML node-by-node and the original code seemed to generate a corrupt excel file if I tried to use it. The XML string I'm using came from the styles.xml file of a blank Excel file I created on my machine in Office 2007.

I then went to my VB code (which uses the ExcelPackage assembly) and added the following function:

   Private Function AddNumberFormat(ByVal stylesDoc As XmlDocument, ByVal formatString As String) As Integer
        Dim numFmts As XmlElement = stylesDoc.CreateElement("numFmts", stylesDoc.DocumentElement.NamespaceURI)
        Dim numFmt As XmlElement = stylesDoc.CreateElement("numFmt", stylesDoc.DocumentElement.NamespaceURI)
        Dim numFmtId As Integer = numFmts.ChildNodes.Count + 164 '164 is Some sort of magic number

        numFmt.SetAttribute("numFmtId", numFmtId)
        numFmt.SetAttribute("formatCode", formatString)

        numFmts.AppendChild(numFmt)
        stylesDoc.DocumentElement.InsertBefore(numFmts, stylesDoc.DocumentElement.FirstChild)

        numFmts.SetAttribute("count", numFmts.ChildNodes.Count)

        'Add to the CellStyles:
        Dim cellXfs As XmlElement = stylesDoc.DocumentElement.GetElementsByTagName("cellXfs")(0)
        Dim xf As XmlElement = stylesDoc.CreateElement("xf", stylesDoc.DocumentElement.NamespaceURI)

        xf.SetAttribute("numFmtId", numFmtId)
        xf.SetAttribute("fontId", "0")
        xf.SetAttribute("fillId", "0")
        xf.SetAttribute("borderId", "0")
        xf.SetAttribute("xfId", "0")
        xf.SetAttribute("applyNumberFormat", "1")

        Dim addedStyleId As Integer = cellXfs.ChildNodes.Count

        cellXfs.AppendChild(xf)

        cellXfs.SetAttribute("count", cellXfs.ChildNodes.Count)

        Return addedStyleId
    End Function
Now, when I'm building my Excel document I can do this:
Dim workbookStyles As XmlDocument = xlPackage.Workbook.StylesXml 

Dim newStyleID as Integer = AddNumberFormat(workbookStyles, "0000000")

worksheet.Cell(row, col).StyleID = newStyleID
It works like a charm and there's no corruption of the xslx. It also works if you're using a template. If you're dynamically adding styles you might want to maintain a dictionary to ensure that you aren't creating duplicate styles (I'm not sure what Excel would do in that case...probably nothing).
I hope this helps. If you have any questions, please feel free to contact me.
Clayton
Oct 26, 2011 at 4:12 PM

Hello, I needed show numerics values like text then  I added a string empty (" ") at the end of my value.

i.e: xlPackage.Workbook.Worksheets[1].Cell(contRow, contCol).Value = "08 ";

Also, the cell (contRow, contCol) was formatted like Text.

And this work for me.

I hope this is useful for you.