Spreadsheet: type in bulk, check first and save at once.

A grid for entering many rows at once

The rows are numbered. At the top you have +10 rows, Undo, a counter of filled rows and the Validate and Save buttons.

Each column with its own type, even database lists

Each column has its own type: text, number, currency, date or database list, with search inside the cell itself. That's the case for the State column:

<mad-sheet model="Cidade" database="maderp" rows-min="10" max-rows="2000"><mad-sheet-col field="estado_id" label="State" type="combo"model="Estado" key="id" display="{nome}" order-by="nome" width="220px" /><mad-sheet-col field="nome" label="Name" required /><mad-sheet-col field="situacao" label="Status" /><mad-sheet-col field="codigo_ibge" label="IBGE code" /></mad-sheet>

Validate first, save afterwards

Validate checks the whole batch: required fields, types and references to other tables. Cells with problems get marked. Save writes everything in a single transaction, and if anything fails, nothing is saved.

When to use the data-entry spreadsheet

Use it when someone enters many similar records, one after another. The person types everything into the grid and saves in one go, instead of opening a form per row.

• Inventory count. Product and quantity typed in during the stocktake, with the sum in the totals row.
• Financial entries. The month's expenses, with an amount and a date on each row.
• Quotes. Quantity and unit price per row, and a calculated column shows each row's total.
• Team hours (HR). Employee, date and hours, with the employee picked from a database list.

To filter, sum or export what already exists, use the list view. For a record with many fields or with line items, like a sale, use the form. The spreadsheet is built in the visual editor, like the other screens.

What you configure

Besides the table and the columns, these settings change the day-to-day of whoever is typing.

Required required

An empty cell in this column fails the batch.

Initial value default

Fills new rows, such as today's date, and saves typing the same thing on every row.

Calculated column compute

Does the math on each row, like quantity times price. The column is read-only and isn't saved.

List that depends on another depends-on

On the same row, one column filters the options of the other, like category and product. Changing the first clears the second.

Mask mask

CPF, CNPJ (Brazilian tax IDs), postal code, phone, license plate and more. CPF and CNPJ check digits are verified on the server.

What goes into the Laravel project

The spreadsheet is a class in app/Control with the <mad-sheet> view. Saving goes through points where you can plug in your own code:

• Checking and saving are separate methods. onValidateBatch checks the batch without saving, and onSaveBatch saves. Errors come back per cell, with the row and the field.
• A hook before each row. In a class of yours that extends the spreadsheet's, beforeSaveRow() receives each row before it is saved. That's where you write a calculated column or fill in user, company and unit. An exception at this point rolls back the whole batch.
• A hook after the batch. afterSaveBatch() receives the saved records, for example to update a balance.

Like every MadBuilder screen, these files open in your editor, go into your Git and accept hand-written code in protected blocks, which stay there when the screen is regenerated. See how the code is generated.

Questions about the spreadsheet

And more

See the form in detail or explore the page types.