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

How to set proper width to a column?

6 Answers 1725 Views
SpreadProcessing
This is a migrated thread and some comments may be shown as answers.
Richard
Top achievements
Rank 1
Richard asked on 28 Oct 2019, 05:07 PM
Hi support,

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

Sort by
0
Nikolay Demirev
Telerik team
answered on 31 Oct 2019, 08:23 AM

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

Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
0
Richard
Top achievements
Rank 1
answered on 04 Nov 2019, 01:53 PM

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.

0
Nikolay Demirev
Telerik team
answered on 05 Nov 2019, 07:12 AM

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

Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
0
Arben
Top achievements
Rank 1
answered on 06 Jul 2020, 12:01 PM
[quote]Nikolay Demirev said:

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

Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.

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

0
Nikolay Demirev
Telerik team
answered on 08 Jul 2020, 06:15 AM

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

Progress is here for your business, like always. Read more about the measures we are taking to ensure business continuity and help fight the COVID-19 pandemic.
Our thoughts here at Progress are with those affected by the outbreak.
0
Arben
Top achievements
Rank 1
answered on 08 Jul 2020, 12:42 PM
[quote]Nikolay Demirev said:

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

Progress is here for your business, like always. Read more about the measures we are taking to ensure business continuity and help fight the COVID-19 pandemic.
Our thoughts here at Progress are with those affected by the outbreak.

[/quote]

 

That worked perfectly, thank you.

Tags
SpreadProcessing
Asked by
Richard
Top achievements
Rank 1
Answers by
Nikolay Demirev
Telerik team
Richard
Top achievements
Rank 1
Arben
Top achievements
Rank 1
Share this question
or