AseptSoft Core Documentation
AseptSoft Core Documentation
Breadcrumbs

Excel Tabular Exports

The Excel Tabular Export engine is the core system powering all structured Excel exports and imports in AseptSoft. It provides a consistent, cumulative mechanism for reading, writing, and updating Excel workbooks with tabular data.

🏭 Pharma Example — Maintaining a Living CIP Parameter Spreadsheet

In a pharmaceutical facility, process parameters (temperatures, flow rates, concentrations) are frequently reviewed and updated across CIP and SIP modules. The tabular export engine lets the validation team export parameters to Excel, add review notes and conditional formatting, then re-export after each change cycle. Because the engine matches rows by name, new parameters are added and removed parameters are flagged — all without disturbing the team's annotations.

📦 Consumers

The following export/import operations use this engine:

🗂️ Data Hierarchy

One export operation produces data organized in this hierarchy:

Level

Excel Element

Description

1

Workbooks (files)

One or more .xlsx files

2

Worksheets (pages)

Each workbook contains one or more worksheets

3

Tables

Each worksheet contains a single data table

4

Cells

Each cell is identified by a row-column pair

📐 Table Structure

Every table has:

Component

Description

Header row

Column names — must be unique within the table

Dominant column

A special column whose values uniquely identify each row (e.g., "Name")

Data cells

The intersection of a row and a non-dominant column

📏 Format Identification Rules

For the engine to correctly locate and update a table in an existing worksheet, these rules must be followed:

🚫 Required Empty Regions

Region

Rule

Above the header

All cells above the header row on any column must be empty

Left of the header

All cells to the left of the first header cell on any row must be empty

Border cells

Cells immediately bordering the table (top and left) must be empty

✅ Free Regions

You can place any content (text, charts, notes, images) in areas that are:

  • Below the last data row

  • To the right of the last data column

  • Diagonally below-right of the table

This is what makes cumulative exports work — your custom content in free regions is preserved during re-exports.

🏷️ Header Uniqueness

Each header cell value must be unique. AseptSoft ensures this before exporting.

🔑 Dominant Column Uniqueness

Values in the dominant column must be unique. This column is used to match rows during cumulative updates. AseptSoft always places it as the first column on initial export, but you can reorder columns afterward.

🔄 Format Update Rules (Cumulative Behavior)

When re-exporting to an existing file, the engine:

  1. Identifies existing tables using the format rules above

  2. Matches old data with new data by name

  3. Splits the data into: items to remove, items to update, items to add

  4. Applies changes according to the configured options

💡 Row Matching Example

If the existing dominant column contains [HA-170, HA-169, HA-167] and the new data has [HA-170, HA-169, HA-1]:

  • HA-170 — matched, updated in place

  • HA-169 — matched, updated in place

  • HA-167 — exists only in old data, deleted (if Row Deletion option is enabled)

  • HA-1 — exists only in new data, last row is duplicated and updated with HA-1's values (if Row Creation option is enabled)

The duplicate-then-update strategy preserves row formatting from the existing file.

⚙️ Configuration Options

Each consumer configures which options are active. Here is the complete list:

📁 Workbook Level

Option

Description

Workbook Deletion

If a workbook in the destination folder has no matching data, delete it

📄 Worksheet Level

Option

Description

Worksheet Creation

Create new worksheets for data that has no existing worksheet

Worksheet Reorder

Reorder existing worksheets to match the new data's sequence

Worksheet Deletion

Delete worksheets that have no matching data

📊 Column Level

Option

Description

Column Creation

Add missing columns for new data fields

Column Reorder

Reorder existing columns to match the new data's sequence

Column Deletion

Remove columns that have no matching data

📝 Row Level

Option

Description

Row Creation

Add rows for new data records

Row Reorder

Reorder existing rows to match the new data's sequence

Row Deletion

Remove rows that have no matching data

📌 Behavior Notes

  • Fresh documents (just created): Worksheets, columns, and rows are always created regardless of creation options

  • Existing documents (update scenario): Creation options determine whether new elements are added

  • Reorder options: When enabled, elements are rearranged to match the order defined by the new data. When disabled, the user's custom ordering is preserved.

  • Deletion options: When enabled, orphaned elements are removed. When disabled, they remain in place (possibly with stale data).

🔃 Transposition Support

The engine supports transposed mode where rows and columns are swapped:

Normal Mode

Transposed Mode

Header = first row

Header = first column

Dominant = first column

Dominant = first row

Records = rows

Records = columns

This is useful when the natural data orientation is vertical rather than horizontal.

⚠️ Duplicate Detection

When duplicate detection is enabled, the engine detects and warns about:

  • Duplicate values in the dominant column

  • Duplicate worksheet names in the workbook

This prevents silent data loss during cumulative updates.