Skip to content
GitHub

Import and export

Export happens on the server: click Export in a list page’s toolbar and an xlsx / csv is built from the current filters or the selected rows, limited by the same data scope as the list. Up to 5,000 rows download straight away; larger exports become background jobs that send an in-app message when done, and the file stays under Profile → Exports for 72 hours. Import is three steps: download a template with dropdowns → upload and preview (nothing is written; every row’s problems are listed) → commit. Users, departments and dictionary items can be imported, and a whole department tree can come from one file, even with children listed before their parents.

Department import: the preview before committing
List pageExport (permission code)Import (permission code)
System → Userssys:user:exportYes, superusers only (no permission code), create only
System → Departmentssys:dept:exportsys:dept:import, all three modes
System → Dictionaries (once a dictionary type is selected)dict:data:exportdict:data:import, all three modes
System → Noticessys:notice:export—
Logs → Sign-in log / Operation log / Data changeslog:login:export · log:opera:export · log:change:export—
Profile → ExportsAny signed-in user, own records only—

The seed data grants none of these import / export buttons to any non-superuser role; tick them under Roles for the roles that need them. Business-data lists support row selection and exporting the selection; audit logs are read-only and export by filter only; tree lists have no multi-select.

What can be exported, and what does the file look like?

Section titled “What can be exported, and what does the file look like?”

The Export button opens a menu with two items:

  • Export current results (N): sends the list’s filters to the backend as they are. A filter the backend does not recognise is rejected rather than ignored, so you never get more rows than you were looking at.
  • Export N selected: appears once rows are selected; at most 1,000 at a time.

The format is xlsx (default) or csv. The file is named resource_YYYYmmdd-HHMMSS.xlsx.

  • Columns: every column defined for the resource. The backend can export chosen columns in a chosen order, but the UI does not send that yet.
  • Times: converted to the exporter’s own time zone (set in Profile, falling back to the system default), formatted yyyy-mm-dd hh:mm:ss.
  • xlsx gets a second “export info” sheet: who exported it, when, with which filters, how many rows, in which time zone. csv has no such sheet; it is UTF-8 with a BOM and CRLF line endings so Excel opens it correctly on a double-click.
  • Rows are ordered by ID, newest first; departments in tree order.
  • Integers above 2^53−1 (snowflake IDs) are written as text, otherwise Excel turns the last digits into zeros.
RowsBehaviour
0Refused: nothing to export
≤ 5,000Built within the request, downloaded directly
5,001 – 200,000Queued as a Celery job; the button points you to Exports, and an in-app message links to the download when it is done
> 200,000 (20,000 for departments)Refused, not truncated: a truncated file looks exactly like a complete one

Each person can have at most 3 queued or running exports. A background job checks permissions again before it runs: if the export permission was revoked while it was queued, the job does not run.

Profile → Exports lists only your own: queued / running (with a progress bar, refreshed every 3 seconds) / done / failed (with the reason) / expired. Files live in the private area of the default storage and are deleted after 72 hours; records are kept for 90 days. The cleanup job maintenance.purge_expired_exports runs at minute 20 of every hour and also marks exports stuck for over half an hour as failed.

What is formula injection, and how is it prevented?

Section titled “What is formula injection, and how is it prevented?”

A cell starting with =, +, - or @ is run as a formula when Excel opens the file: someone who sets their username to =HYPERLINK(…) can get the admin who exports the table to open a link. Leading spaces, full-width spaces and non-breaking spaces are skipped before checking, and full-width = + - @, tab, carriage return, newline and \0 also count.

  • xlsx: every text cell is written with the text type and the “show as text” flag, without changing the value. This has been tested by reading back with openpyxl, not yet in real Excel.
  • csv: cells that look like formulas get a leading '.

Control characters that XML does not allow become �, and text over 32,767 characters (Excel’s cell limit) is truncated.

What do the import template and preview look like?

Section titled “What do the import template and preview look like?”

The template is an xlsx: the first sheet has only the header row, and a second “notes” sheet lists, per column, whether it is required, a description and the allowed values. Columns with fixed options have dropdowns: enums like status, department codes the importer can see, role codes, dictionary type codes, up to 500 entries each. Short lists sit in the dropdown itself; long ones or ones with commas go on a hidden sheet. The dropdowns are warning-level: Excel warns about a value outside the list but does not block it, and the backend validation is what counts.

Uploads must be .xlsx: at most 5 MB, at most 100 MB uncompressed, at most 1,000 data rows. Only the first worksheet is read, whichever one was active when the file was saved. Headers are matched by text: the field key, the Chinese label and the English label all work; a missing required column or two columns with the same name rejects the whole file, and unrecognised columns are listed in the preview and not imported. A formula cell is an error (not read as empty), and fully blank rows are skipped.

The preview writes nothing and returns the total, the valid rows, how many will be created and updated, and every row’s problems. Enum columns accept either language and common synonyms. Duplicates within the file are flagged as “same as row N”. You can exclude rows in the preview before committing; changing the import mode runs the preview again.

Commit uses a one-time token from the preview: valid for 30 minutes, usable once, bound to the importer and the resource. Every row is validated again at commit, because the database may have changed since the preview.

ModeRow already existsRow does not exist
Create onlyErrorCreated
Create and updateUpdatedCreated
Update onlyUpdatedError

“Already exists” is decided by department code for departments, dictionary type code + label for dictionary items, and username for users (user import is create-only, and a username, email or phone that clashes with an existing user is an error). When updating, a blank cell keeps the current value; it does not clear the field.

By default the valid rows go in and the bad ones are skipped. With Import only if every row succeeds ticked, a single failure rolls back the whole batch and nothing is written.

When rows fail you can download an error report: the original cells plus “row” and “reason” columns, ready to fix and import again. Only the importer can download it, and it expires with the preview token.

How does one file build a whole department tree?

Section titled “How does one file build a whole department tree?”

Each row has its own department code and a parent department code (parent_code). The parent can be an existing department the importer can see, or another row in the same file, in any order: rows are written top-down by depth at commit. Cycles are checked against the tree as it will be after the import, so moving A under B and B out of A in the same file is not mistaken for a cycle, while a real cycle is reported. If a parent row has an error, the rows under it are flagged too.

Import and export are not on the Config page; everything is in .env, and the backend (and the worker) need a restart after a change:

KeyDefaultNotes
EXPORT_SYNC_MAX_ROWS5000Up to this many rows are built within the request; more go to the background
EXPORT_MAX_ROWS200000Cap for one export; above it the export is refused
EXPORT_MAX_CONCURRENT_PER_USER3Queued or running exports per person
EXPORT_FILE_RETENTION_HOURS · EXPORT_RECORD_RETENTION_DAYS72 · 90Hours to keep the file, days to keep the record
EXPORT_BATCH_SIZE1000Rows read per batch in a background export
TABLE_IMPORT_SIZE_MAX · TABLE_IMPORT_UNCOMPRESSED_MAX5 MB · 100 MBUpload size and uncompressed size (against zip bombs)
TABLE_IMPORT_MAX_ROWS · TABLE_IMPORT_MAX_ENTRIES1000 · 1000Data rows, entries inside the archive
IMPORT_EXPIRE_SECONDS1800Lifetime of the preview token and the error report
USER_IMPORT_DEFAULT_PASSWORDemptyInitial password for imported users. Empty = user import is off; it must pass the password rules, and production refuses weak defaults. Deliberately kept off the Config page

They are defined in the import and export sections of core/conf.py.

Adding import / export to your own list page

Section titled “Adding import / export to your own list page”

Export:

  1. Call register_export(ExportSpec(...)) in app/admin/dataio.py (or a plugin’s own dataio.py): resource name, permission code, columns, allowed filters, the query. Reuse the list endpoint’s DAO get_select for the query, so data scope and filter semantics match the list and nothing is exportable that the list would hide.
  2. Add a POST /xxx/export route with RequestPermission('xxx:export') that calls export_service.start (copy dept.py).
  3. Put the menu button’s permission code into the seed SQL for all three databases, write a data migration, and update sql/seed_manifest.json; test_permission_codes.py checks that these agree.
  4. Add export.* keys in both languages.
  5. On the page: <Can perm><ExportButton path filters selectedIds total /></Can>.

The Celery worker loads these definitions through load_dataio_modules(), so resources registered in plugins can export in the background too.

Import: register_import(ImportSpec(...)) declares the columns, the match key (key_of), validation, the write and the supported modes; add the /import/template, /import/preview and /import/commit routes; put an <ImportSheet> on the page. Step-by-step notes and pitfalls are in common/dataio/AGENTS.md.

  • No .xls or csv import; formulas in cells are not accepted.
  • A blank cell cannot clear a field on import, and import cannot move an existing department to the top level.
  • Someone limited by data scope who imports new departments may create departments outside their own scope that they then cannot see.
  • Swapping the names of two sibling departments in one file collides.
  • User import is create-only and superuser-only, so “export the user table, edit it, import it back” does not work; the round trip is tested for departments only.
  • The completion message for background exports needs the notifications plugin (notification); without it nothing is sent and you check Exports yourself.
  • The UI cannot choose which columns to export yet.

A few UI texts don’t match the behaviour; noted here so they don’t mislead:

  • The department template says a blank parent_code means a top-level department, but in “Create and update” / “Update only” mode a blank keeps the current parent.
  • The import dialog describes all three modes as matching by “code”; dictionary items actually match by dictionary type code + label (the dictionary page’s own description gets this right).

Backend tests run against a real database:

  • test_export.py (24 tests): over the cap is refused, not truncated; formulas stay text in xlsx and get quoted in csv; background exports are visible only to their owner; the worker re-checks permissions before running; an expired file is refused, then purged.
  • test_import.py (29 tests): a tree in one file is created parents first; reorganising within one file is not a false cycle; the modes decide what an existing code means; an exported file imports back unchanged; what happens when a write fails after other rows were written; create-or-update follows the database at commit time.
  • test_user_import.py (19 tests): non-superusers are refused by all three endpoints; import is off with no default password; one token cannot be committed twice.
  • tests/test_excel_ops.py (37 tests): only the first sheet is read; a formula becomes an error instead of an empty value; a zip bomb is rejected before openpyxl opens it; long option lists move to a hidden sheet.

In the browser (Playwright): data-io.spec.ts (the exported CSV is exactly the filtered set; the export can be downloaded again from Exports; one file builds a department tree, children before parents included) and user-import.spec.ts (the preview points out three kinds of problems and commit creates only the clean rows; one token cannot be committed twice; a non-xlsx file is rejected with a reason).

On the live demo (admin / 123456), open System → Departments, click Import, download the template, fill in two or three rows with one row’s parent being another row in the same file, upload it and see how the preview marks new rows and problems. For export, try Logs → Sign-in log and export a csv by filter. The demo is shared, so other people see the departments you import; please delete them afterwards.