NAV 2018 Excel buffet of dotnet, how to set a row height?

chiosan

Hi, I am currently upgrading my client's 2009 classic to 2018, and there's some excel export report.
The report needs to set fixed column width ,row height and zoom, in 2009 using automation is simple like:

SetHeight(pHeight : Decimal)
IF pHeight > 0 THEN
  XlWrkSht.Range('A' + FORMAT(1) + ':' + xlColID + xlRowID).RowHeight := pHeight;

SetWidth(pColmunID : Code[10];pWidth : Decimal)
IF (pColmunID <> '') AND (pWidth > 0) THEN
  XlWrkSht.Range(pColmunID + FORMAT(1)).ColumnWidth := pWidth;

SetZoom(pZoom : Integer)
XlWrkSht.PageSetup.RightHeader :='';
IF pZoom > 0 THEN
XlWrkSht.PageSetup.Zoom := pZoom;

But it's no longer working in 2018 using dotnet. I could found a function "XlWrkShtWriter.SetColumnWidth" to do the column width and it works.
However there isn't one for row height and zoom. Could anyone give me some advice? Many thanks.

AlexDen

Hi,

You can find an example here:
https://forum.mibuso.com/discussion/73152/excel-buffer-with-extended-formatting

You will need to use 2 functions:
- Call SetRowsProperty() from your report
- Add WriteRowsProperties() to WriteSheet function.

AlexDen

Hi,

You can find an example here:
https://forum.mibuso.com/discussion/73152/excel-buffer-with-extended-formatting

You will need to use 2 functions:
- Call SetRowsProperty() from your report
- Add WriteRowsProperties() to WriteSheet function.

chiosan

Thank AlexDen, it works perfectly. You are amazing.
Is there any luck that I can also set the default zoom rate?

AlexDen

I think it's possible.

Try to change code in WriteSheetViews() function.
Now it adds freeze pane to the sheet.
You should get something like that:

IF (FreezePaneColNo < 2) AND (FreezePaneRowNo < 2) AND (Zoom = 0) THEN
  EXIT;

OpenXmlWorksheet := XlWrkShtWriter.Worksheet;

IF ISNULL(OpenXmlWorksheet.SheetViews) THEN
  OpenXmlWorksheet.SheetViews := OpenXmlSheetViews.SheetViews();

OpenXmlSheetView := OpenXmlSheetView.SheetView();
//OpenXmlSheetView.TabSelected := OpenXmlBoolValue.BooleanValue(TRUE);
// Properties to set sheet zoom >
OpenXmlSheetView.ZoomScale := OpenXmlUInt32Value.FromUInt32(Zoom);   
OpenXmlSheetView.ZoomScaleNormal  := OpenXmlUInt32Value.FromUInt32(Zoom);
// Properties to set sheet zoom <
OpenXmlSheetView.WorkbookViewId := OpenXmlUInt32Value.FromUInt32(0);


IF (FreezePaneColNo < 2) AND (FreezePaneRowNo < 2) THEN BEGIN
  IF FreezePaneRowNo < 1 THEN
    FreezePaneRowNo := 1;
  IF FreezePaneColNo < 1 THEN
    FreezePaneColNo := 1;

  DummyExcelBuf.VALIDATE("Column No.", FreezePaneColNo);
  ColumnCode := DummyExcelBuf.xlColID;
  OpenXmlPane := OpenXmlPane.Pane();
  ...
END;
WrkShtHelper.AppendElementToOpenXmlElement(OpenXmlWorksheet.SheetViews, OpenXmlSheetView);
...

Zoom is your new global variable