Updating Excel without losing formatting

radek.bb
radek.bb Member Posts: 49
in NAV Three Tier
Excel Buffer provides a functionality to update worksheet - but the trick is: it is forcing new cell Decorator during update - function WriteCellValue looks like this:
[External] WriteCellValue(ExcelBuffer : Record "Excel Buffer Advanced")
WITH ExcelBuffer DO BEGIN
  GetCellDecorator(ExcelBuffer, Decorator);

  CellTextValue := "Cell Value as Text";
  OnWriteCellValueOnBeforeSetCellValue(Rec,CellTextValue);
  
  CASE "Cell Type" OF
    "Cell Type"::Number:
      XlWrkShtWriter.SetCellValueNumber("Row No.",xlColID,CellTextValue,NumberFormat,Decorator);
    "Cell Type"::Text:
      XlWrkShtWriter.SetCellValueText("Row No.",xlColID,CellTextValue,Decorator);
    "Cell Type"::Date:
      XlWrkShtWriter.SetCellValueDate("Row No.",xlColID,CellTextValue,NumberFormat,Decorator);
    "Cell Type"::Time:
      XlWrkShtWriter.SetCellValueTime("Row No.",xlColID,CellTextValue,NumberFormat,Decorator);
    ELSE
      ERROR(Text039)
  END;

I could change it to something like this:
[External] WriteCellValue(ExcelBuffer : Record "Excel Buffer Advanced")
WITH ExcelBuffer DO BEGIN
  GetCellDecorator(ExcelBuffer, Decorator);

  CellTextValue := "Cell Value as Text";
  OnWriteCellValueOnBeforeSetCellValue(Rec,CellTextValue);
  
// extra part to update value without changing decorator
  IF UpdateValueOnly THEN                          
    XlWrkShtWriter.UpdateCellValueText("Row No.",xlColID,CellTextValue)
  ELSE
// end of extra part
  CASE "Cell Type" OF
    "Cell Type"::Number:
      XlWrkShtWriter.SetCellValueNumber("Row No.",xlColID,CellTextValue,NumberFormat,Decorator);
    "Cell Type"::Text:
      XlWrkShtWriter.SetCellValueText("Row No.",xlColID,CellTextValue,Decorator);
    "Cell Type"::Date:
      XlWrkShtWriter.SetCellValueDate("Row No.",xlColID,CellTextValue,NumberFormat,Decorator);
    "Cell Type"::Time:
      XlWrkShtWriter.SetCellValueTime("Row No.",xlColID,CellTextValue,NumberFormat,Decorator);
    ELSE
      ERROR(Text039)
  END;

And it almost works - the destination formatting is preserved - but the value is indeed put as text - not as the type expected in the cell.
The only way I found to do this is to use ancient-old approach - from Nav2009 when Excel Buffer was implemented by Excel automation.
But obviously the solution is extremely slow and requires Excel installed on client machine (cannot be used on server side).

So the question:

Is it a way to preserve formatting while updating values on selected cells?
I would imagine that we should either get the current cell Decorator and use it in existing code - or use some other function to update cell value only....

Categories