none
Spreadsheet format number RRS feed

  • Question

  • This seems like it should be quite simple, but apparently now since I turn up nothing in Google.

    I can add a number to a cell, but how do I make a NumberFormat and apply it to the cell?

    Regards,

    Rick 
    Friday, November 7, 2008 12:55 AM

Answers

  • Hi Rick,

    I don't know how to write the exact code as you wanted, nor am I familiar with the related open xml elements, but if I were going to finish the task, I would follow these steps:

    1. Use Excel to create a spreadsheet 1.xlsx which has a number filled in A1
    2. Copy 1.xlsx to 2.xlsx, and change the A1 format to some format that I want
    3. Use OpenXmlDiff tool in the Open XML SDK V2 to find different elements in these two files
    4. Use ClassExplorer tool in the Open XML SDK V2 to find the corresponding class name in the SDK
    5. Write code using the classes

    This is my general way of programming against open xml file format now. Hope you find it helpful.

    -Xinxing
    xinxing
    • Marked as answer by Rick Roen Friday, November 7, 2008 10:15 AM
    • Unmarked as answer by Rick Roen Friday, November 7, 2008 10:15 AM
    • Marked as answer by Rick Roen Friday, November 7, 2008 10:15 AM
    Friday, November 7, 2008 2:39 AM

All replies

  • Hi Rick,

    I don't know how to write the exact code as you wanted, nor am I familiar with the related open xml elements, but if I were going to finish the task, I would follow these steps:

    1. Use Excel to create a spreadsheet 1.xlsx which has a number filled in A1
    2. Copy 1.xlsx to 2.xlsx, and change the A1 format to some format that I want
    3. Use OpenXmlDiff tool in the Open XML SDK V2 to find different elements in these two files
    4. Use ClassExplorer tool in the Open XML SDK V2 to find the corresponding class name in the SDK
    5. Write code using the classes

    This is my general way of programming against open xml file format now. Hope you find it helpful.

    -Xinxing
    xinxing
    • Marked as answer by Rick Roen Friday, November 7, 2008 10:15 AM
    • Unmarked as answer by Rick Roen Friday, November 7, 2008 10:15 AM
    • Marked as answer by Rick Roen Friday, November 7, 2008 10:15 AM
    Friday, November 7, 2008 2:39 AM
  • Thank you XinXing, I will give this a try.

    In any case, since formatting a number is really a basic task for a spreadsheet it seems amazingly hard to do this from the SDK.

    In the few spreadsheet examples that I have found this "basic formatting" is never mentioned which makes them a rather incomplete.  On to my pet peve that it would be nice when MS or anyone shows us example code that they do a complete job or at least tell us that some common tasks like formatting a number is not possible, inconvient to do, or maybe bizarrely complicated. I'm very grateful that someone has taken the time to show me how to do something with example code, but since they certainly know a lot more than I, please finish the job and give us a real-world example, not just one that highlights the easy parts and makes no mention of the difficulties.

    Rick
    Friday, November 7, 2008 10:14 AM
  • Just in case this is of help to someone else, here is the general method to add a number format and add it to a cell...and oh, by the way, XinXing's recommendations about the tools that come with the OpenXml SDK V2 are VERY helpful to do this kind of thing.

        Get the Stylesheet

        Add a Stylesheet.NumberFormats.NumberFormat    
        Set the NumberFormatId and a FormatCode
        Increase the Stylesheet.NumberFormats.Count by 1

        Add a Stylesheet.CellFormats.CellFormat
        Set the NumberFormatId to the NumberFormat.NumberFormatId from above (this references the format)
        Increase the Stylesheet.CellFormats.Count by 1

        SAVE the Spreadsheet - Spreadsheet.Save - very important or you will have invalid cell style references

        Add a  Worksheet.SheetData.Row.Cell
        Set the Cell.StyleIndex to the 1-based position from the Stylesheet.CellFormats.CellFormat above

        Save the Worksheet

        Done

    This blog is very helpful to quickly get up to speed using the OpenXml tools: http://blogs.msdn.com/ericwhite/archive/2008/09/06/announcing-the-first-ctp-of-open-xml-sdk-v2.aspx
        

    Rick

    Friday, November 7, 2008 1:22 PM
  • Hi Rick,

    Thanks for the sharing!

    The Open Xml Sdk Team is doing an survey on user scenario to better understand the requirement and to provide more sample code to your needs. If you have interest, you could visit our Connect site: https://connect.microsoft.com/site/sitehome.aspx?SiteID=589.

    There are more sample code there too.

    Best,
    Xinxing

    xinxing
    Saturday, November 8, 2008 6:36 AM
  • Rick Roen said:

    Just in case this is of help to someone else, here is the general method to add a number format and add it to a cell...and oh, by the way, XinXing's recommendations about the tools that come with the OpenXml SDK V2 are VERY helpful to do this kind of thing.

        Get the Stylesheet

        Add a Stylesheet.NumberFormats.NumberFormat    
        Set the NumberFormatId and a FormatCode
        Increase the Stylesheet.NumberFormats.Count by 1

        Add a Stylesheet.CellFormats.CellFormat
        Set the NumberFormatId to the NumberFormat.NumberFormatId from above (this references the format)
        Increase the Stylesheet.CellFormats.Count by 1

        SAVE the Spreadsheet - Spreadsheet.Save - very important or you will have invalid cell style references

        Add a  Worksheet.SheetData.Row.Cell
        Set the Cell.StyleIndex to the 1-based position from the Stylesheet.CellFormats.CellFormat above

        Save the Worksheet

        Done

    This blog is very helpful to quickly get up to speed using the OpenXml tools: http://blogs.msdn.com/ericwhite/archive/2008/09/06/announcing-the-first-ctp-of-open-xml-sdk-v2.aspx
        

    Rick


    This doesn't seem to work for me. Can you please post an example of some working code by any chance? I also can't find the spreadsheet.Save method
    Tuesday, December 30, 2008 4:40 PM
  • static UInt32Value ID= 700U; //there are some reserved IDs; for example 1U is for integer or 14U for date

    using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
    {
     NumberFormat nf = new NumberFormat();
             nf.NumberFormatId = ID;
             nf.FormatCode = "#.#";
            
             spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberFormats.Append(nf);

             CellFormats styleFormats = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats;
             CellFormat cf = new CellFormat() { NumberFormatId = ID, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true };
             styleFormats.Append(cf);

     spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
    }

    Tuesday, August 11, 2009 9:04 AM
  • I tried with the above code. But, its not working.... Can u pls give a complete example for Number formatting(Currency formatting) a cell in Excel through OpenXML SDK.

    Thursday, February 11, 2010 9:38 AM