none
How to Calculate column widths in Excel Open XML RRS feed

  • Question

  •      

    I am currently using the below example to calculate the width of the columns. I have used the formula, which is updated in Open XML specification. I couldn't get the exact width value of the columns as per in MS Excel. I want to know how to calculate the width of the columns.

    Code Snippet:

           System.Drawing.Graphics graphics = this.CreateGraphics();

                StringFormat format = new StringFormat();
                format.FormatFlags = StringFormatFlags.MeasureTrailingSpaces;

                graphics.PageUnit = GraphicsUnit.Pixel;
                int systemDpi = 0;
                string measureString = "0";
                string fontName = "Calibri";
                int fontSize = 11;
                Font stringFont = new Font(fontName,fontSize);
                systemDpi = (int)graphics.DpiX;
                float maxsize = 0;
                float currentSize = 0;
                int numberOfCharacters = measureString.Length;

                for (int i = 0; i < numberOfCharacters; i++)
                {
                   // currentSize = TextRenderer.MeasureText(measureString[i].ToString(), stringFont).Width;
                    currentSize = graphics.MeasureString(measureString[i].ToString(), stringFont).Width;
                    if (currentSize > maxsize)
                        maxsize = currentSize;

                }


                float pixels = maxsize;
                float value = ((numberOfCharacters*pixels+5)/pixels*256)/256;
                double width = Math.Truncate(value);

       Regards,
         Prakash

    Wednesday, August 13, 2014 10:23 AM

Answers

  • Hi Prakash,

    As far as I know, there is no such API in Open XML SDK we can calculate the width based on the text in the cells. Based on my understanding the width of Range is a calculated by Excel application. But we can set or get the with of a column by the with attribute of col element like figure below:

     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.

    Wednesday, August 20, 2014 8:57 AM
    Moderator

All replies

  • Hello Prakash,

    The Excel object model provides the Width property for the Range class.

    If you need to calculate it using the Open XML SDK I'd suggest asking such questions in the Open XML Format SDK forum instead. There you will get the most qualified feedback I believe.

    Wednesday, August 13, 2014 11:51 AM
  • Hi Prakash,

    As far as I know, there is no such API in Open XML SDK we can calculate the width based on the text in the cells. Based on my understanding the width of Range is a calculated by Excel application. But we can set or get the with of a column by the with attribute of col element like figure below:

     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.

    Wednesday, August 20, 2014 8:57 AM
    Moderator