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

No gridlines in Excel 2013

8 Answers 251 Views

SpreadProcessing

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

8 Answers, 1 is accepted

Accepted

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.

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

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

}

answered on 06 Sep 2016, 02:29 PM

ExcelTest.zip

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.