Cross-Border Statistical Report Excel Batch Export Feature Development: Multi-Currency, Multi-Business Data Formatting Export Solution

When financial and operations personnel use the backend of cross-border purchasing agents or international consolidated shipping platforms, a high-frequency requirement is to export periodic order, waybill, and user statistics to Excel for local archiving and reconciliation. The vast majority of low-cost purchasing agent source codes on the market only support simple single‑page exports, suffering from four critical flaws: inability to batch‑export full‑period data across pagination, failure to convert multi‑currency amounts into a base currency, incomplete table fields that do not fit cross‑border business scenarios, and interface timeouts caused by large‑volume exports blocking API responses.

In the Taocarts statistics module, I have added a unified Excel export service, built on node‑xlsx with a generic export utility. It supports one‑click full exports for three report types: order statistics, waybill logistics statistics, and user segmentation statistics. It automatically formats USD/EUR amounts, distinguishes business fields, and processes tens of thousands of records asynchronously in sharded batches to avoid interface timeouts. This article provides a complete walkthrough of the export service encapsulation, asynchronous sharded querying, multi‑currency formatting, and front‑end export interaction code—suitable for all reverse cross‑border shopping platforms' financial reconciliation needs.

Cross‑border statistical report exports differ from ordinary domestic e‑commerce exports in several ways:

Reports must simultaneously display both the original foreign‑currency amount and the RMB‑converted amount, so that finance can easily calculate exchange‑rate gains/losses.

Waybill reports must break down shipping fees, value‑added service fees, warehousing fees, coupon deductions, and other monetary fields separately.

Export data volumes often exceed 10,000 records; a single synchronous query will trigger database connection timeouts, so sharded asynchronous querying is mandatory.

Date filters can cover wide ranges, requiring automatic splicing of time‑series pre‑aggregated tables and raw business tables.

Exported file names must automatically include the start/end dates and report type for easy financial archiving and classification.

The overall architecture consists of three layers: a generic Excel export utility, a sharded asynchronous data query service, and a front‑end export pop‑up interaction. Upon receiving an export request, the backend first creates an asynchronous task, writes it to a task record table, and immediately returns a task ID. The front‑end then polls the task status. Meanwhile, the backend asynchronously reads statistical data in sharded batches, formats currencies and fields, assembles the Excel worksheet, generates the file, and uploads it to cloud OSS. When the task completes, the front‑end obtains the file download link. Additionally, automatic cleanup of expired tasks and chunked compression for very large files prevent memory overflow during exports of tens of thousands of records.

Generic Excel Export Utility Encapsulation Code (omitted in translation summary)
`// src/common/utils/excel-export.util.ts
import * as xlsx from 'node-xlsx';
import { UploadService } from 'src/common/upload/upload.service';

export class ExcelExportUtil {
constructor(private uploadService: UploadService) {}

/**

• 生成Excel Buffer并上传云端存储，返回下载地址
• @param sheetName 工作表名称
• @param header 表头数组
• @param data 二维表格数据
• @param fileName 导出文件名 */ async generateAndUpload(sheetName: string, header: string[], data: any[][], fileName: string) { // 拼接表头+表格内容 const sheetData = [header, ...data]; const buffer = xlsx.build([{ name: sheetName, data: sheetData }]); // 上传OSS云端存储 const fileUrl = await this.uploadService.uploadBuffer(buffer, ${fileName}.xlsx, 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); return fileUrl; }

// 多币种金额格式化，同时输出外币、人民币两列
formatCurrency(foreignAmount: number, rate: number) {
const cny = (foreignAmount * rate).toFixed(2);
const foreign = foreignAmount.toFixed(2);
return [foreign, cny];
}
}`

Core Logic of the Sharded Asynchronous Export Task for Order Statistics (omitted)
// src/modules/statistics/task/export-task.task.ts
async exportOrderStatTask(start: Date, end: Date, taskId: number) {
try {
// 分片读取时序统计数据，每批500条
const shardData = await this.shardStat.rangeQuery(start, end);
const header = ['统计日期', '订单总数', '外币销售额($)', '人民币营收(¥)', '退款单数', '退款外币金额($)', '退款人民币(¥)'];
const excelData = [];
const rate = await this.rateService.getRateByCurrency('USD');
shardData.forEach(item => {
const [saleUsd, saleCny] = this.excelUtil.formatCurrency(Number(item.saleAmount), rate);
const [refundUsd, refundCny] = this.excelUtil.formatCurrency(Number(item.refundMoney), rate);
excelData.push([item.statDate, item.orderTotal, saleUsd, saleCny, item.refundNum, refundUsd, refundCny]);
})
// 生成Excel并上传云端
const fileUrl = await this.excelUtil.generateAndUpload(
'订单营业统计', header, excelData,订单统计${start.toLocaleDateString()}${end.toLocaleDateString()}
);
// 更新导出任务状态为完成，写入下载链接
await this.exportTaskRepo.update({ id: taskId }, { status: 1, fileUrl });
} catch(err) {
await this.exportTaskRepo.update({ id: taskId }, { status: 2, errMsg: err.message.slice(0, 200) });
}
}
The front‑end adds an export pop‑up: clicking the export button creates a task, polls the task status, and displays a download button upon completion. If the task fails, it shows error logs and allows re‑initiation. The same export utility is reused for waybill and user statistics reports—only the headers and data formatting logic need to be replaced, greatly reducing repetitive development effort across multiple report types.

After implementing this asynchronous sharded export solution, finance staff can export full‑period statistical data for half‑year or entire‑year periods with one click—no more manual copying across pages. Multi‑currency formatted fields are generated automatically, perfectly fitting the financial compliance and archiving needs of Taobao/1688 purchasing agent systems and reverse cross‑border independent stores, while resolving the core pain points of simplistic export functions and large‑volume timeouts common in traditional purchasing agent source codes.