Overview

The ProExcel module empowers developers to effortlessly interact with Excel files by providing a seamless way to read, write, and edit existing Excel documents. Whether you're working with data, lists, or tables, the module allows you to access and manipulate Excel content with minimal effort. By simply specifying key parameters such as column, row, and sheet names, developers can quickly perform complex operations on Excel files, significantly streamlining workflows and reducing development time. With ProExcel, handling intricate Excel-related tasks becomes easier, making it an invaluable tool for any developer working with Excel in Mendix.

With ProExcel, developers can easily:

• Read specific cell data, rows, or columns from Excel sheets.
• Read horizontal and vertical lists of data from Excel sheets.
• Write data to specific cells in an Excel sheet or in single sheet multiple tables at the same time.
• Handle Excel data in a way that integrates seamlessly with Mendix entities, enabling easy data manipulation.

Documentation

ProExcel

For full documentation 

ProExcel implementation steps 

Objective:

The ProExcel module empowers developers to effortlessly interact with Excel files by providing a seamless way to read, write, and edit existing Excel documents. Whether you're working with data, lists, or tables, the module allows you to access and manipulate Excel content with minimal effort. By simply specifying key parameters such as column, row, and sheet names, developers can quickly perform complex operations on Excel files, significantly streamlining workflows and reducing development time. With ProExcel, handling intricate Excel-related tasks becomes easier, making it an invaluable tool for any developer working with Excel in Mendix.

Dependencies

To use the ProExcel Module, the following modules and components are required:

• Studio Pro version 9.24.32 or higher
• Excel Importer (available in the Mendix marketplace)
• Community Commons (available in the Mendix marketplace)
• Mx-Model Reflection (available in the Mendix marketplace)

Configuration

1. Module Installation:

   • Import the required modules: Excel Importer, Community Commons, and Mx-Model Reflection from the Mendix marketplace.
   • Import the ProExcel Module from the Mendix marketplace.

2. Passing Excel Files to the Module:

   • The module can handle your own Excel files. To use your file, follow these steps:
     1. Retrieve the Excel file from your desired data source.
     2. Use the DuplicateFileDocument Java action to copy the file content into an ExcelFile entity object.

Note : Excel importer , community commons and Mx-Model reflection are mandatory to import before importing the module

Key Features

1. Read Data from Specific Cells or Ranges:

   • Retrieve data from specific rows, columns, and sheets within an Excel file.
   • Supports both horizontal and vertical data extraction.
   • Easily extract single-cell values or entire columns/rows of data.

2. Write Data to Excel Cells:

   • Effortlessly write data to specific rows, columns, and sheets within an Excel file.
   • Support for bulk writing or modifying specific values in Excel cells.

3. Read Data Horizontally:

   • Extract data in a horizontal manner, where each row corresponds to a separate object containing multiple attributes.
   • Ideal for reading data tables row by row, such as reading student data, product information, etc.

4. Read Data Vertically:

   • Extract data vertically, where each column represents a different entity attribute.
   • Suitable for scenarios where data is stored in columns and needs to be processed into separate entities for each row.

5. Customizable Excel Interaction:

   • Flexibility to specify sheet names, row and column numbers, and the specific data range you want to read or write.
   • Ideal for both small datasets and large, structured tables.

Use Cases

1. Read Data from Specific Cells:

• The ProExcel module allows you to read data from specific rows and columns by specifying the sheet, row, and column.
• Example:
  • Sheet Name: Sheet1
  • Row Number: 2
  • Column Number: 3 (Column 'C')
• This operation returns the data at the specified cell location, which can be used or displayed in your application.

2. Read Data Horizontally:

• You can read horizontal data by specifying the range of rows and columns.
• For example, you might want to read the first 5 rows of a table with 4 columns (Name, Roll No, ID, Location). The data will be parsed into objects with attributes for each column.
• The output will be a list of DynamicEntity objects, where each object holds the values of each column for a specific row.

3. Read Data Vertically:

• Similar to horizontal reading, but instead of rows, you specify the column to read.
• This allows you to read data vertically, which is useful when each column in Excel represents a separate data field, and each row represents a new entry.

4. Write Data to Excel:

• You can write data back to specific cells in an Excel sheet by specifying the sheet name, row, column, and value to be written.
• This is useful for updating Excel files with new data or modifying existing entries.

5. Read Data from Specific Cells (Advanced):

• If you need to extract data from specific areas in your Excel sheet, you can specify both the starting row and column to extract a particular block of data.

Java Actions

The ProExcel Module includes several key Java actions to facilitate the reading and writing of Excel data:

1. Java_action_ExcelReadByRowColumn:

   • Reads data from the specified rows and columns of an Excel sheet.
   • Outputs data as a string for each cell.

2. Java_action_ExcelRead_Horizontally:

   • Reads data horizontally from the Excel sheet.
   • Outputs the data into a list of DynamicEntity objects.

3. Java_action_ExcelRead_Vertically:

   • Reads data vertically from the Excel sheet.
   • Outputs data into a list of DynamicEntity objects.

4. Java_action_WriteByRowAndColumn:

   • Writes specified data to the Excel sheet at the specified row and column.

We can use these java action combinations to read / write the multiple tables or data in single sheet or multiple sheet .