I'm trying to set the width to 4 columns in a new spreadsheet but it doesn't work the way I expect.
Here's is the code
01.
Workbook workbook =
new
Workbook();
02.
Worksheet worksheet = workbook.Worksheets.Add();
03.
04.
worksheet.Columns[1].SetWidth(
new
ColumnWidth(8.11,
true
));
05.
worksheet.Columns[2].SetWidth(
new
ColumnWidth(33.78,
true
));
06.
worksheet.Columns[3].SetWidth(
new
ColumnWidth(60.67,
true
));
07.
worksheet.Columns[4].SetWidth(
new
ColumnWidth(7.67,
true
));
08.
09.
IWorkbookFormatProvider formatProvider =
new
Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.XlsxFormatProvider();
10.
11.
using
(Stream output =
new
FileStream(fileName, FileMode.Create))
12.
{
13.
formatProvider.Export(workbook, output);
14.
}
When I check the size of each column in the exported file this is what I get:
column 1: 0.63 (10 pixels)
column 2: 4.00 (43 pixels)
column 3: 7.89 (78 pixels)
column 4: 0.63 (10 pixels)
and what I need is:
column 1: 8.11 (80 pixels)
column 2: 33.78 (311 pixels)
column 3: 60.67 (553 pixels)
column 4: 7.67 (76 pixels)
You can see the screenshot I attached.
How can I get the exact size I'm specifying?
Thank you,
PD: I'm using version Telerik 2019.1.114.40
6 Answers, 1 is accepted
Hello,
Richard, the SpreadProcessing column width measurement unit is pixels and are converted to the Excel units on export. The conversion depends on the text measurement. There are some known issues with the conversion caused by differences in the measurement of text width between Excel and SpreadProcessing.
If you want to set the widths in Excel units you could use the following code, but still, there will be some error:
worksheet.Columns[0].SetWidth(new ColumnWidth(UnitHelper.ExcelColumnWidthToPixelWidth(this.radSpreadsheet.Workbook, 8.11), true));
I hope I was able to help.
Regards,
Nikolay Demirev
Progress Telerik

Hello Nikolay,
Thank you for your response.
Now I need to set the height of some rows in the same way I'm setting the width of the columns.
I'm trying to follow the same steps you suggested in your answer but I can't find something like UnitHelper.ExcelRowHeightToPixelHeight helper in UnitHelper.
I tried to use
worksheet.Rows[0].SetHeight(new RowHeight(UnitHelper.ExcelColumnWidthToPixelWidth(workbook, 105), true));
to get a row with 175 pixels height but instead I get an exception
System.ArgumentOutOfRangeException: 'rowHeight should be greater or equal than 0 and less or equal than 600.
Parameter name: rowHeight'
What can I do to set the height of a row?
Thank you.
Hi Richard,
The row height is again measured in pixels in RadSpreadProcessing, but in Excel, it is measured in points. So in order to set 105 in the Excel measurement units you have to use the following code:
worksheet.Rows[0].SetHeight(new RowHeight(UnitHelper.PointToDip(105), true));
The conversion of the measurement units should not add any error in the result.
Regards,
Nikolay Demirev
Progress Telerik

Hello,
Richard, the SpreadProcessing column width measurement unit is pixels and are converted to the Excel units on export. The conversion depends on the text measurement. There are some known issues with the conversion caused by differences in the measurement of text width between Excel and SpreadProcessing.
If you want to set the widths in Excel units you could use the following code, but still, there will be some error:
worksheet.Columns[0].SetWidth(new ColumnWidth(UnitHelper.ExcelColumnWidthToPixelWidth(this.radSpreadsheet.Workbook, 8.11), true));
I hope I was able to help.
Regards,
Nikolay Demirev
Progress Telerik
[/quote]
Hello, how would I go about adding a value to the width of a column after using autoFit, smth like
worksheet.Columns(i).SetWidth(worksheet.Columns(i).GetWidth() + 10)
The reason I ask this is because AutoFit is not perfect, the column width is a bit narrower than it should (no formatting changes), so I need to increase it by smth like 10-20 pixels.
Hi Arben,
Your idea is correct, but the API is a little bit different. Here is a sample code for adding 10 px to the column width:
ColumnSelection columnSelection = worksheet.Columns[i];
ColumnWidth columnWidth = columnSelection.GetWidth().Value;
columnSelection.SetWidth(new ColumnWidth(columnWidth.Value + 10, columnWidth.IsCustom));
Regards,
Nikolay Demirev
Progress Telerik
Our thoughts here at Progress are with those affected by the outbreak.

Hi Arben,
Your idea is correct, but the API is a little bit different. Here is a sample code for adding 10 px to the column width:
ColumnSelection columnSelection = worksheet.Columns[i];
ColumnWidth columnWidth = columnSelection.GetWidth().Value;
columnSelection.SetWidth(new ColumnWidth(columnWidth.Value + 10, columnWidth.IsCustom));
Regards,
Nikolay Demirev
Progress Telerik
Our thoughts here at Progress are with those affected by the outbreak.
[/quote]
That worked perfectly, thank you.