Excel Buffer overwrites format NAV 2016

jwilder

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

jwilder

I do not know the former method that can accomplish this. Please tell me how to set the cell decorator so it does not overwrite.

archer89

please follow
http://saurav-nav.blogspot.co.uk/2015/08/nav-2013-r2-export-to-excel-with-font.html
http://saurav-nav.blogspot.co.uk/2016/03/nav-2013-r2-export-to-excel-with-font.html

jwilder

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.

archer89

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.

jwilder

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.

archer89

in that case you'll need a customized excelbuffer for your need, kind of none-decorator or copy-decorator.

jwilder

I do not know the former method that can accomplish this. Please tell me how to set the cell decorator so it does not overwrite.

jwilder

Agree but the dotnet control does not seem to have a method for this. Can you find one?

archer89

i will check that and write a blog post about it.

jwilder

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.

archer89

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