Options

Excel Buffer overwrites format NAV 2016

jwilderjwilder Member Posts: 263
In the past(NAV 2009 R2) I have used the Excel buffer to import a spreadsheet make a few changes and export it back out. This worked nice and preserved the formatting unless I specifically change the formatting such as bolding or underline.
I am finding in NAV 2016 I am losing the formatting. If I simply import a sheet and export it out with no changes it loses all the formatting.

Here is a quick sample of what I am talking about:
//Read File into Excel Buffer
FullFileName := 'C:\Users\myusername\Desktop\Book1.xlsx';
OriginalFileName := FullFileName;
FullFileName := FileMgt.UploadFileSilent(FullFileName);
ExcelSheetName := ExcelBuffer.SelectSheetsName(FullFileName);
ExcelBuffer.OpenBook(FullFileName,ExcelSheetName);
ExcelBuffer.ReadSheet;

//Write File back to spreadsheet
ExcelBuffer.UpdateBook(FullFileName,ExcelSheetName);
ExcelBuffer.WriteSheet('Report Header','SWK',USERID);
ExcelBuffer.CloseBook;
ExcelBuffer.OverwriteAndOpenExistingExcel(OriginalFileName);
ExcelBuffer.GiveUserControl;

How do I preserve the formatting that already existed on the sheet such as cell color, word color etc...

Best Answer

Answers

  • Options
    archer89archer89 Member Posts: 337
    edited 2016-08-07
    best regards
    Franz Kalchmair, MVP
    Alias: Jonathan Archer

    please like / agree / verify my answer, if it was helpful for you. thx.
    Blog: http://moxie4nav.wordpress.com/
  • Options
    jwilderjwilder Member Posts: 263
    Thanks for responding Jonathan but unfortunately I am trying to preseve the font, style, color, format etc... on an already existing spreadsheet. I read the spreadsheet in (which does not read in anything about the format), process the data and then export it back out to the same spreadsheet, same cells.
    When exporting out it forces some time of format and overwrites what is already there. I was looking for a way to set the cell decorator to "preserve original format" or "none" or something like that when exporting it back out.
  • Options
    archer89archer89 Member Posts: 337
    please check if readsheet reads also the formatting. if not, thats a reason why.
    also check writesheet. as i remember writesheet writes the values using default formatting settings. in the end some customization will be needed. using saurav's posting you will be able to do that.
    best regards
    Franz Kalchmair, MVP
    Alias: Jonathan Archer

    please like / agree / verify my answer, if it was helpful for you. thx.
    Blog: http://moxie4nav.wordpress.com/
  • Options
    jwilderjwilder Member Posts: 263
    Read sheet does not pull in the formatting. Yes writesheet use default settings but there is no setting for "none". So every time it writes to a cell it overwrites the formatting in excel base on the decorator logic in NAV. I need a null decorator or something like that.
  • Options
    archer89archer89 Member Posts: 337
    in that case you'll need a customized excelbuffer for your need, kind of none-decorator or copy-decorator.
    best regards
    Franz Kalchmair, MVP
    Alias: Jonathan Archer

    please like / agree / verify my answer, if it was helpful for you. thx.
    Blog: http://moxie4nav.wordpress.com/
  • Options
    jwilderjwilder Member Posts: 263
    Agree but the dotnet control does not seem to have a method for this. Can you find one?
  • Options
    archer89archer89 Member Posts: 337
    i will check that and write a blog post about it.
    best regards
    Franz Kalchmair, MVP
    Alias: Jonathan Archer

    please like / agree / verify my answer, if it was helpful for you. thx.
    Blog: http://moxie4nav.wordpress.com/
  • Options
    jwilderjwilder Member Posts: 263
    Jonathan have you done a blog on this yet? I do not think this is possible with the excel dotnet methods/properties we currently have (NAV 2016). I would love for you to prove me wrong.
  • Options
    archer89archer89 Member Posts: 337
    i checked it. it's not possible to write data back to the excel doc. without formatting. there is no NoneFormatDecorator.
    So i developed a .net assembly which covers the needed functionality: writing data to and reading data from an excel doc without any formatting. when writing cell values are simply overwritten.

    you can download the lib from: https://1drv.ms/u/s!ApMe9MLXVSbOdlGstmPs1ZGLgNc

    sample code:

    LOCAL TestXlsLib()
    // open excel workbook
    XlsWorkbook := XlsWorkbook.Workbook('c:\temp\test.xlsx');
    // select sheet 'Sheet2'
    XlsWorksheet := XlsWorksheet.Worksheet(XlsWorkbook,'Sheet2');

    // read one value
    value := XlsWorksheet.GetValue(1, 1);
    MESSAGE(value);

    // Write some values ... without format
    // only overwrite values, formatting stays
    XlsWorksheet.SetValue(1, 1, 'First Name');
    XlsWorksheet.SetValue(1, 2, 'Last Name');
    XlsWorksheet.SetValue(1, 3, 'Full Name');
    XlsWorksheet.SetValue(1, 4, 'Salary');

    // read one value
    value := XlsWorksheet.GetValue(1, 1);
    MESSAGE(value);

    //XlsWorkbook.Save(); // save the current loaded xls doc.
    // save the doc as new file
    XlsWorkbook.SaveAs('c:\temp\test2.xlsx');
    // close excel doc, close excel
    XlsWorkbook.Close();

    // if an error occurs ...
    if XlsWorkbook.Error <> '' then
    MESSAGE(XlsWorkbook.Error);

    // the variables
    //XlsWorkbook DotNet XlsLib.Workbook.'XlsLib, Version=1.0.0.0, Culture=neutral, PublicKeyToken=38613a2311532c9a'
    //XlsWorksheet DotNet XlsLib.Worksheet.'XlsLib, Version=1.0.0.0, Culture=neutral, PublicKeyToken=38613a2311532c9a'
    //value Text
    best regards
    Franz Kalchmair, MVP
    Alias: Jonathan Archer

    please like / agree / verify my answer, if it was helpful for you. thx.
    Blog: http://moxie4nav.wordpress.com/
Sign In or Register to comment.