NAV2013 R2 -> As data are inserted into imported Excel file, the style (layout) is changed

Damjan

Hi All,

System: NAV2013 R2
Errors:
Issue #1 -> Excel file cannot be edited by the NAV, if any style is applied on the sheet.
Issue #2 -> Styles are changed as Excel file is edited with some data from Job record.

Functional steps:
1. on page "Job Card" user clicks on button "Populate Excel"
2. function to copy the template file (*.xlsx) is called that finds the Excel file on File Server and copies it into the File Server folder (each Job has its own folder), if folder does not exist, it creates one folder
3. function that creates interaction log entry is called -> interaction log entry is inserted with some data as expected
4. report is called that populates data from Job record into the cells of Excel file -> Error pops if styles are applied on sheet of template file and data are being inserted (see issue #1).
5. Excel file is opened on the users' client computer -> issue styles on sheets are changed, but not fully, just some (see issue #2).

Issues described:
Issues #1 (resolved)
Microsoft Dynamics NAV

---

Break On Error Message:

A call to Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.WorkbookWriter.ValidateDocument failed with this message: The spreadsheet is corrupted. The following error occurred: The element has unexpected child element 'http://schemas.openxmlformats.org/spreadsheetml/2006/main:headerFooter'..

---

OK

---

Issue #1 solution:
- inserted new sheet "NAV_DATA" into the template file, the sheet is set to hidden and some Excel formulas are inserted to copy data to desired cells on other sheets of same Excel file.
- data from record "Job" are inserted on step 4 as expected.
- Excel file is opened as expected with populated data on desired cells.

Issue #2 (unresolved)
As Excel file is opened on step 5, data are inserted as expected, but styles are changed on all sheets where any style is applied. Style is not really totally different from the original (template) file, just all images are placed on different place (moved) and some width of cells are reseted to some x values, but still, I do not want that any style on any sheet is changed as Excel file is opened on the clients computer.


Thanks

Damjan

with code TempExcelBufferRec.DownloadAndOpenExcel the Excel files is saved on the disk and styles are not changed, I will try to use this function to download the file silently and then to copy file to the correct folder on File Server.