How to export an Excel with custom layout

When use Pimics Excel export

Business Central contains an Excel integraiton by default. This integration can be use for internal purposes. Imidielty when you want to export a price list or another type of feed with the custom template the default integration can't be used. In that scenarios our Excel export will be helpful.

Before the export

There are several things which have to be set it before the Excel Export.

We recommend to download a RapidStart package PackagePIMICS.ENU.EXCELEXP.rapidstart (or another) from our configuration packages. it contains the base configuration which can be change for your purposes. For more details see documentation of packages.

Mapping

A mapping is typically created once per export type. You can define the format and layout of the Excel in the mapping. It means which column will be placed into the Excel. In the mapping is no selection of items, categories or prices, this is defined in publication.

When you use the configuration package some values can be already created.

  1. Choose the Search icon, enter Export Mapping and then choose the related link.
  2. Select New to create a new mapping
  3. Specify field Description
  4. Set Excel in field Type, after that a new group Excel - Details is available
  5. If you have a file which should be used as a template select it in the field Template File.
    • This point is not required in the case of missing template data will be exported into an empty Excel worksheet.
  6. Go to Excel - Details and create a new line, specify these fields:
    1. Target List Name set a name of Excel list
    2. First Data Line set a line number where the data will be placed from
  7. Select List Settings from the group actions
  8. In a page Excel Export Mapping Line you can define a mapping between data in Pimics and the export.
    1. Create a new line
    2. Set Source Type to Item
    3. Set Source Field No. to 1. Use a lookup and select field Code
    4. Set Destination Type to Column
    5. Set Destination Value to A
    6. Repeat steps 1 to 6 to create other fields or use an action Copy to create a new line.
  9. Close all pages

Publication Setup

  1. Choose the Search icon, enter Publication Channel and then choose the related link.
  2. Select New to create a new channel
  3. Specify fields Code and Description
  4. Set Type to Excel
  5. Set Publication Type to No Details
  6. In Excel group select Mapping Code which you created before in Mapping
  7. Choose the Search icon, enter Publication Group and then choose the related link.
  8. Create a new publication group for all types what you want to have in publication with predefined filters and rules. How to create publication group.
  9. Close all pages

Export data

We recommend to have a publication for each different content, it means if you want to export different items (or prices) for different customers, each should have their own publication with the same mapping.

In this scenario we assume that you download and apply the configuration packages. So all values is included in this package, but you an change it for your purposes.

  1. Go to Publication
  2. Create a new publication, select Excel (Publication) from the template list
  3. Select action Edit to open the Publication Card
  4. Define settings for pricing in group Pricing & Period
  5. Specify channel from point Publication Setup in field Export Channel
  6. Select action Publication Structure
  7. Define data for the export
    • Select data you want to include in the export, by functions in group Insert New Peer.
    • The data is inserted into publication and you can browse it in the publication tree.
    • You can read more details about how to define publication structure
  8. Close the page Edit Publication Tree
  9. Select action Publish from Process
  10. The Excel file is generated and downloaded
  11. Next time when you want to export it again select action Update Publication or Reorganize Publication to update data. More details
  12. Select action Publish to get a file again
  13. Close all pages
Title: How to export an Excel with custom layout, Author: Pimics Team