| Docs

How to export to an Excel file with custom layout

Author: Pimics Team, Modified On:

When to use the Pimics Excel export

Business Central contains an Excel integration by default, and this integration can be used for internal ERP purposes. However, if you want to export a price list or another type of feed with a custom layout, the default integration cannot be used. That is the reason why we made Pimics functionality that will allow you a lot of flexibility in defining the data for your Excel exports, and deciding how you want them to look.

Before exporting to Excel

There are several things to be set before being able to export data to an Excel file.

We recommend downloading our RapidStart package PackagePIMICS.ENU.EXCELEXP.rapidstart from our configuration packages page. It contains the base configuration which you can then change for your own purposes. For more details see our packages documentation.

Mapping

A mapping is typically created once per export type. You can use this mapping to define the format and layout of your Excel data. You can look at it as defining the header for your Excel exported file. The actual data will be added to this header at the time the data will be exported. Which items, categories or prices will be included into the final exported file is defined in the particular publication that you will export from.

If you use our predefined configuration package, some values can be already created. Be sure to change them if you need to.

  1. Using Alt+Q, search for export mapping and then click on the relevant link
  2. Select New to create a new mapping
  3. Specify the Description
  4. Set Excel in field Type; after that a new group Excel - Details will become available
  5. If you already have a file which should be used as a template select it in the field Template File
    • In case no template is uploaded, data will be exported into an empty Excel worksheet
  6. Go to Excel - Details and create a new line, specifying these fields:
    1. Target List Name to set a name for the Excel list
    2. First Data Line to set a line number beginning from where data will be inserted
  7. Select List Settings from the group actions
  8. In 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. Lookup and select field Code
    4. Set Destination Type to Column
    5. Set Destination Value to A
    6. Repeat steps 1 to 5 to create other fields, updating the column label. Or you can use action Copy to create a new line
  9. Close all pages

Publication Setup

  1. Using Alt+Q, search for publication channel and then click on the relevant link
  2. Select New to create a new channel
  3. Specify Code and Description
  4. Set Type to Excel
  5. Set Publication Type to No Details
  6. In Excel group select the Mapping Code that you created before in Mapping
  7. Using Alt+Q, search for publication group and then click on the relevant link
  8. Create a new publication group for all types that you want to include in your publication, with predefined filters and rules. How to create publication group.
  9. Close all pages

Exporting product data

We recommend creating a publication for each different purpose. For example if you need to export different items (or prices) for different customers, each should have their own publication set up, using the same mapping.

In this scenario we assume that you downloaded and applied the Allium-provided configuration packages. All values are included in this package, and they can be changed at any time to suit your own purposes.

  1. Go to Publication Management > Publications
  2. Create a new publication and select Excel (Publication) from the template list
  3. Select Edit to open the publication card
  4. Define settings for pricing in group Pricing & Period
  5. In field Export Channel specify the channel you created previously in Publication Setup
  6. Select Publication Structure
  7. Define data for the export
    • Select the data you want to include in your export, by using the function Insert New Peer
    • The data is inserted into the publication and you can browse it in the publication tree
    • Here you can find more details about how to define a publication structure
  8. Close the page Edit Publication Tree
  9. Select Process and then Publish
  10. The Excel file is generated and downloaded
  11. When running the export at a later date, it is best to first run either Update Publication or Reorganize Publication, to update the publication data. More details here
  12. Close all pages
Title: How to export to an Excel file with custom layout, Author: Pimics Team