No gridlines in Excel 2013

8 posts, 1 answers
  1. Heiko
    Heiko avatar
    124 posts
    Member since:
    Oct 2012

    Posted 09 Dec 2014 Link to this post

    Hi!

    I adapted the example "CreateModifyExport" in xaml-sdk to create a workbook then export it via XlsxFormatProvider. This works fine except that when I open the exported file in Excel 2013 (64 bit) there are no gridlines visible. Maybe I missed something but there is no code where gridlines are enabled/disabled.
    Second issue is that AutoFitWidth is not working correctly, the column width does not fit the text.

    Regards
    Neils 
  2. Answer
    Deyan
    Admin
    Deyan avatar
    136 posts

    Posted 10 Dec 2014 Link to this post

    Hello Neils,

    Thank you for contacting us.

    As you may see on this link to the SDK example the normal style of the workbook is modified as a solid fill is applied to it. This way the default style of the cells will have this fill applied. As the fills layer is above the gridlines layer this results in hiding the gridlines.

    If you want to have both cell background and border lines you can set some borders to the normal style. As the borders layer is above the fills layer the borders will be visible above the fills. The following code snippet shows how you can achieve that:
    CellBorder border = new CellBorder(CellBorderStyle.Thin, new ThemableColor(ThemeColorType.Text2));
    normalStyle.RightBorder = border;
    normalStyle.TopBorder = border;
    normalStyle.LeftBorder = border;
    normalStyle.BottomBorder = border;

    As for the AutoFitWidth issue - this issue is fixed in the current version of RadSpreadProcessing. If you upgrade to the latest SP version the AutoFitWidth method should be working as expected.

    I hope this is helpful. If you have any other questions or concerns please do not hesitate to contact us again.

    Regards,
    Deyan
    the Telerik team
     

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

     
  3. Heiko
    Heiko avatar
    124 posts
    Member since:
    Oct 2012

    Posted 10 Dec 2014 in reply to Deyan Link to this post

    Thanks, Deyan... exactly what I was missing. Maybe I should learn a little bit more of Excel.  ;-)

    The AutoFitWidth issue I will check later.

    Regards
    Neils
  4. SRoll
    SRoll avatar
    3 posts
    Member since:
    Jul 2016

    Posted 01 Sep Link to this post

    As for the AutoFitWidth issue - this issue is fixed in the current version of RadSpreadProcessing. If you upgrade to the latest SP version the AutoFitWidth method should be working as expected.

    Is it really fixed? I use version 2016.2.606.45 and I have to manually increase the width after the call to AutoFitWidht. Otherwise, the column is to small to fit the content. Tested with Excel 2016

  5. Nikolay Demirev
    Admin
    Nikolay Demirev avatar
    103 posts

    Posted 02 Sep Link to this post

    Hi Sylvain,

    We are not aware of any issues related to the AutoFitWidth method.

    Could you share with us what is the text in the cells where you observe the issue related to AutoFitWidth? Also, could you share all properties which you set to the cell? This will help us reproduce the issue and fix it.

    Regards,
    Nikolay Demirev
    Telerik by Progress
    Do you need help with upgrading your AJAX, WPF or WinForms project? Check the Telerik API Analyzer and share your thoughts.
  6. SRoll
    SRoll avatar
    3 posts
    Member since:
    Jul 2016

    Posted 02 Sep in reply to Nikolay Demirev Link to this post

    Hi Nikolay,

    The issue is easy to reproduce, I made you a simplified example.

    The problem is worst when the content is larger.

    01.using System;
    02.using System.Collections.Generic;
    03.using System.Diagnostics;
    04.using System.IO;
    05.using Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx;
    06.using Telerik.Windows.Documents.Spreadsheet.Model;
    07.using Telerik.Windows.Documents.Spreadsheet.PropertySystem;
    08. 
    09.namespace AutoFitWidthIssue
    10.{
    11.    public class Program
    12.    {
    13.        private static readonly Workbook Workbook = new Workbook();
    14.        private static readonly Worksheet Worksheet = Workbook.Worksheets.Add();
    15. 
    16.        public static void Main(string[] args)
    17.        {
    18.            var titleStyle = Workbook.Styles.Add("TitleStyle", CellStyleCategory.TitlesAndHeadings);
    19.            titleStyle.BeginUpdate();
    20.            titleStyle.FontSize = 30;
    21.            titleStyle.IsBold = true;
    22.            titleStyle.EndUpdate();
    23. 
    24.            var weekNumberStyle = Workbook.Styles.Add("WeekNumberStyle", CellStyleCategory.TitlesAndHeadings);
    25.            weekNumberStyle.BeginUpdate();
    26.            weekNumberStyle.FontSize = 20;
    27.            weekNumberStyle.IsBold = true;
    28.            weekNumberStyle.EndUpdate();
    29. 
    30.            var promotionHeadingStyle = Workbook.Styles.Add("HeadingStyle", CellStyleCategory.TitlesAndHeadings);
    31.            promotionHeadingStyle.BeginUpdate();
    32.            promotionHeadingStyle.IsBold = true;
    33.            promotionHeadingStyle.EndUpdate();
    34. 
    35.            var currentRow = 0;
    36.            MergeCells(currentRow, 0, 10);
    37.            var titleCell = Worksheet.Cells[currentRow, 0];
    38.            titleCell.SetStyleName("TitleStyle");
    39.            titleCell.SetValue("Document Title");
    40. 
    41.            currentRow += 1;
    42.            MergeCells(currentRow, 0, 2);
    43.            var weekNumberCell = Worksheet.Cells[currentRow, 0];
    44.            weekNumberCell.SetValue("Week number 1");
    45.            weekNumberCell.SetStyleName("WeekNumberStyle");
    46. 
    47.            var content = new List<KeyValuePair<string, string>>
    48.            {
    49.                new KeyValuePair<string, string>("Heading", "Value"),
    50.                new KeyValuePair<string, string>("Larger heading", "Larger value"),
    51.                new KeyValuePair<string, string>("Even Larger heading", "This value is really really large!"),
    52.                new KeyValuePair<string, string>("Oops", "The previous column is not large enough!"),
    53.            };
    54. 
    55.            currentRow += 1;
    56. 
    57.            //Write the values
    58.            for (var i = 0; i < content.Count; i++)
    59.            {
    60.                var pair = content[i];
    61.                var heading = Worksheet.Cells[currentRow, i];
    62.                heading.SetValue(pair.Key);
    63.                heading.SetStyleName("HeadingStyle");
    64. 
    65.                var value = Worksheet.Cells[currentRow + 1, i];
    66.                value.SetValue(pair.Value);
    67.            }
    68. 
    69.            Worksheet.Columns[Worksheet.UsedCellRange].AutoFitWidth();
    70. 
    71.            var filePath = "AutoWidthIssue_" + DateTime.Now.ToFileTime() + ".xlsx";
    72.            var formatProvider = new XlsxFormatProvider();
    73.            using (var output = new FileStream(filePath, FileMode.Create))
    74.            {
    75.                formatProvider.Export(Workbook, output);
    76.            }
    77. 
    78.            Process.Start(filePath);
    79.        }
    80. 
    81.        private static void MergeCells(int row, int column, int colspan)
    82.        {
    83.            Worksheet.Cells[new CellIndex(row, column), new CellIndex(row, column + colspan - 1)].Merge();
    84.        }
    85.    }
    86.}

  7. SRoll
    SRoll avatar
    3 posts
    Member since:
    Jul 2016

    Posted 02 Sep in reply to SRoll Link to this post

    Code without the line numbers... I wanted to edit the other one but it seems it is not possible :(

    using System;
    using System.Collections.Generic;
    using System.Diagnostics;
    using System.IO;
    using Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx;
    using Telerik.Windows.Documents.Spreadsheet.Model;
    using Telerik.Windows.Documents.Spreadsheet.PropertySystem;
     
    namespace AutoFitWidthIssue
    {
        public class Program
        {
            private static readonly Workbook Workbook = new Workbook();
            private static readonly Worksheet Worksheet = Workbook.Worksheets.Add();
     
            public static void Main(string[] args)
            {
                var titleStyle = Workbook.Styles.Add("TitleStyle", CellStyleCategory.TitlesAndHeadings);
                titleStyle.BeginUpdate();
                titleStyle.FontSize = 30;
                titleStyle.IsBold = true;
                titleStyle.EndUpdate();
     
                var weekNumberStyle = Workbook.Styles.Add("WeekNumberStyle", CellStyleCategory.TitlesAndHeadings);
                weekNumberStyle.BeginUpdate();
                weekNumberStyle.FontSize = 20;
                weekNumberStyle.IsBold = true;
                weekNumberStyle.EndUpdate();
     
                var promotionHeadingStyle = Workbook.Styles.Add("HeadingStyle", CellStyleCategory.TitlesAndHeadings);
                promotionHeadingStyle.BeginUpdate();
                promotionHeadingStyle.IsBold = true;
                promotionHeadingStyle.EndUpdate();
     
                var currentRow = 0;
                MergeCells(currentRow, 0, 10);
                var titleCell = Worksheet.Cells[currentRow, 0];
                titleCell.SetStyleName("TitleStyle");
                titleCell.SetValue("Document Title");
     
                currentRow += 1;
                MergeCells(currentRow, 0, 2);
                var weekNumberCell = Worksheet.Cells[currentRow, 0];
                weekNumberCell.SetValue("Week number 1");
                weekNumberCell.SetStyleName("WeekNumberStyle");
     
                var content = new List<KeyValuePair<string, string>>
                {
                    new KeyValuePair<string, string>("Heading", "Value"),
                    new KeyValuePair<string, string>("Larger heading", "Larger value"),
                    new KeyValuePair<string, string>("Even Larger heading", "This value is really really large!"),
                    new KeyValuePair<string, string>("Oops", "The previous column is not large enough!"),
                };
     
                currentRow += 1;
     
                //Write the values
                for (var i = 0; i < content.Count; i++)
                {
                    var pair = content[i];
                    var heading = Worksheet.Cells[currentRow, i];
                    heading.SetValue(pair.Key);
                    heading.SetStyleName("HeadingStyle");
     
                    var value = Worksheet.Cells[currentRow + 1, i];
                    value.SetValue(pair.Value);
                }
     
                Worksheet.Columns[Worksheet.UsedCellRange].AutoFitWidth();
     
                var filePath = "AutoWidthIssue_" + DateTime.Now.ToFileTime() + ".xlsx";
                var formatProvider = new XlsxFormatProvider();
                using (var output = new FileStream(filePath, FileMode.Create))
                {
                    formatProvider.Export(Workbook, output);
                }
     
                Process.Start(filePath);
            }
     
            private static void MergeCells(int row, int column, int colspan)
            {
                Worksheet.Cells[new CellIndex(row, column), new CellIndex(row, column + colspan - 1)].Merge();
            }
        }
    }

  8. Nikolay Demirev
    Admin
    Nikolay Demirev avatar
    103 posts

    Posted 06 Sep Link to this post

    Hello Sylvain,

    I believe this issue is related to the way Excel renders the text inside the cells. In the following paragraphs, I will try to explain in more details how the values for the column width should be calculated.

    The width of the columns in XLSX file is stored as a value whose measurement unit is the number of characters that can fit the column. In the document format specification, there is a formula used for calculating the width of a character. The width of a single character is the maximal width of the digits from 0 to 9 measured with the settings of the normal style (like font family, font size, etc.).

    For example, one character has 8 pixels width in the default Normal style in Excel 2016. If we have text which is 80 pixels wide, its width in Excel units will be around 10.

    In other words, the column width is strongly coupled with the text length. If you do not change the font properties of the Normal style, this width will have the same visual representation no matter the scale factor for instance. If you scale the text which is 80 pixels to 200%, it will become 160 pixels wide, but also the width of one character will be 20 pixels and the width in Excel units will be still 10.

    Knowing how the column widths are measured we can make an experiment using Excel. We set "This value is really really large!" value to cell A1 and "test" value to cell B1. Double clicking the column A1 border auto fits the column width. After that, we change the scale factor. As a result of the changed scale factor, the text in the first cell changes its size relatively to the column size and at 80% scale factor it gets clipped by cell B1. This means that in some cases Excel does not render the text with the right size.

    RadSpreadsheet uses the RadSpreadProcessing as its model and you can see the document generated by your code. I have compared the text width in Excel 2016 and in RadSpreadsheet at different scale factors and they are different most of the time. But at 80% scale factor, the difference is about 4 pixels. 

    I have attached the video from my tests with Excel where you can see that its auto fit causes the cell content to be clipped.

    Regards,
    Nikolay Demirev
    Telerik by Progress
    Do you need help with upgrading your AJAX, WPF or WinForms project? Check the Telerik API Analyzer and share your thoughts.
Back to Top