This is a migrated thread and some comments may be shown as answers.

No gridlines in Excel 2013

7 Answers 45 Views
SpreadProcessing
This is a migrated thread and some comments may be shown as answers.
Heiko
Top achievements
Rank 1
Veteran
Heiko asked on 09 Dec 2014, 04:19 PM
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 

7 Answers, 1 is accepted

Sort by
0
Accepted
Deyan
Telerik team
answered on 10 Dec 2014, 10:18 AM
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 layer this results in hiding the .

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 . 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 , hybrid and native mobile apps.

 
0
Heiko
Top achievements
Rank 1
Veteran
answered on 10 Dec 2014, 11:47 AM
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
0
SRoll
Top achievements
Rank 1
answered on 01 Sep 2016, 07:36 AM

[quote]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.[/quote]

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

0
Nikolay Demirev
Telerik team
answered on 02 Sep 2016, 08:26 AM
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.
0
SRoll
Top achievements
Rank 1
answered on 02 Sep 2016, 10:16 AM

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.}

0
SRoll
Top achievements
Rank 1
answered on 02 Sep 2016, 10:19 AM

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();
        }
    }
}

0
Nikolay Demirev
Telerik team
answered on 06 Sep 2016, 02:29 PM
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.
Tags
SpreadProcessing
Asked by
Heiko
Top achievements
Rank 1
Veteran
Answers by
Deyan
Telerik team
Heiko
Top achievements
Rank 1
Veteran
SRoll
Top achievements
Rank 1
Nikolay Demirev
Telerik team
Share this question
or