none
How to keep the style when I rewrite table from html to excel RRS feed

  • Question

  • Now I am face the problem.

    First I created a DataSet from a xlsx file. And I had to keep the style so I can display it on web.

    Here is my code

            string GetText(string xml)
            {
                XDocument xdoc = XDocument.Parse(xml);
                XNamespace space = @"http://schemas.openxmlformats.org/spreadsheetml/2006/main";
                XElement element = xdoc.Element(space + "si");
                if (element == null) return "";
                StringBuilder sbText = new StringBuilder();
                string str = "";
                if (element.Element(space + "r") != null)
                {
                    foreach (XElement xr in element.Elements(space + "r"))
                    {
                        str = "";
                        if ((xr.Element(space + "t") != null))
                        {
                            str = xr.Element(space + "t").Value;
                            if (string.IsNullOrEmpty(str)) continue;
                            if (xr.Element(space + "rPr") != null)
                            {
                                XElement rpr = xr.Element(space + "rPr");
                                if (rpr.Element(space + "b") != null) str = "<b>" + str + "</b>";
                                if (rpr.Element(space + "u") != null) str = "<u>" + str + "</u>";
                                if (rpr.Element(space + "i") != null) str = "<i>" + str + "</i>";
                            }else if (xr.Element(space + "rpr") != null)
                            {
                                XElement rpr = xr.Element(space + "rpr");
                                if (rpr.Element(space + "b") != null) str = "<b>" + str + "</b>";
                                if (rpr.Element(space + "u") != null) str = "<u>" + str + "</u>";
                                if (rpr.Element(space + "i") != null) str = "<i>" + str + "</i>";
                            }
                        }
                        sbText.Append(str);
                    }
                }
                else if (element.Element(space + "t") != null) return element.Element(space + "t").Value;
                return sbText.ToString();
            }

    It work well. But This is not over. All the text I get is store as xml file.

    This is the main work, Get message from xml file, and then rewrite it into a new xlsx file.

    The problem is, How can I Keep the <b>,<u>,<i> style in the xlsx file.

    Because the tag <b> or <i> or <u> in xlsx table is text, not style.

    Friday, July 18, 2014 8:51 AM

Answers

  • Hi Lythen,

    As far as I know, we can use Open XML SDK to manipulate the Office document. And you can download it from link below:
    Open XML SDK 2.5 for Microsoft Office

    And you can set the text of a range to the style you wanted, then you can use Open XML SDK 2.5 Productivity tool to compare the difference between it and the original one.

    Here are some links for you studying Open XML:
    Welcome to the Open XML SDK 2.5 for Office Spreadsheets (Open XML SDK)

    And link below is a similar thread about formatting in Excel using Open XML:

    Add Styles to excel spread sheet using openxml    

    Best regards

    Fei                             


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 21, 2014 2:19 AM
    Moderator
  • Hi Lythen

    I think what Fei Xue is trying to do is tell you how to research this yourself.

    Create an Excel workbook, as an end user, and type values in cells similar to what you expect to get from the HTML. Save and close the workbook.

    Now open it in the Open XML SDK Productivity tool and

    1) View the underlying XML to see how Excel handles in-cell formatting

    2) View the code the Tool suggests for generating the workbook to see how the Open XML SDK code looks to create the in-cell formatting.

    In essence, your code is going to need to do a CONVERSION of the HTML to Excel Open XML. There's nothing built into the SDK that can do this for you. You could also create an XML TRANSFORM to run on the HTML that will generate Excel Open XML that you can "stream" into a Part.


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, July 21, 2014 2:34 PM
    Moderator

All replies

  • Hi Lythen,

    As far as I know, we can use Open XML SDK to manipulate the Office document. And you can download it from link below:
    Open XML SDK 2.5 for Microsoft Office

    And you can set the text of a range to the style you wanted, then you can use Open XML SDK 2.5 Productivity tool to compare the difference between it and the original one.

    Here are some links for you studying Open XML:
    Welcome to the Open XML SDK 2.5 for Office Spreadsheets (Open XML SDK)

    And link below is a similar thread about formatting in Excel using Open XML:

    Add Styles to excel spread sheet using openxml    

    Best regards

    Fei                             


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 21, 2014 2:19 AM
    Moderator
  • Thank you first.

    But I am using openxml now. There are different styles in a paragraph. I wrote the paragraph into a cell and the style become text. En, say this, "<b>abc</b>",this display on a web is abc, right? Now, I have to wirte it into Excell cell, It all become text, display as  "<b>abc</b>", not abc.

    Yeah, if there is only single style in a paragraph, I can use property directory. But the text I want write into the cell may be "<b>I</b> am is a <u>boy</u>, I fall in love with a <i>girl</i>". I don't know how to keep the style now!

    Monday, July 21, 2014 8:39 AM
  • Hi Lythen

    I think what Fei Xue is trying to do is tell you how to research this yourself.

    Create an Excel workbook, as an end user, and type values in cells similar to what you expect to get from the HTML. Save and close the workbook.

    Now open it in the Open XML SDK Productivity tool and

    1) View the underlying XML to see how Excel handles in-cell formatting

    2) View the code the Tool suggests for generating the workbook to see how the Open XML SDK code looks to create the in-cell formatting.

    In essence, your code is going to need to do a CONVERSION of the HTML to Excel Open XML. There's nothing built into the SDK that can do this for you. You could also create an XML TRANSFORM to run on the HTML that will generate Excel Open XML that you can "stream" into a Part.


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, July 21, 2014 2:34 PM
    Moderator
  • Hi Cindy.

    Thank you very much, I will try.

    and Thank Fei very much.

    Tuesday, July 22, 2014 8:13 AM