# lesscoding-util **Repository Path**: chargeduck/lesscoding-util ## Basic Information - **Project Name**: lesscoding-util - **Description**: This repository is used to record my commonly used utility classes. Currently, it only contains the Excel import and export tool classes, as well as custom annotations for synchronizing relational databases to Neo4j. - **Primary Language**: Java - **License**: MIT - **Default Branch**: main - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 0 - **Created**: 2026-08-06 - **Last Updated**: 2026-08-13 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README # LessCoding Util [中文文档](README_zh-CN.md) A Java utility library that provides Excel template export/import capabilities based on Apache POI, designed to reduce boilerplate code. ## Features - **Single-row Header Export**: Define column mappings and write data in one line - **Multi-level Merged Header Export**: Recursive tree structure supporting arbitrary header nesting levels - **Formula-based Dropdown Validation**: Hidden sheets + Name Manager for VLOOKUP cascading dropdowns - **Excel Import Parsing**: Specify the header row and auto-parse into `List` structure, with field name replacement - **HTTP Response Header Utility**: One-line file download header setup with optional timestamp suffix - **Zero Extra Dependencies**: Only requires `poi-ooxml` + `slf4j-api` (Servlet is `provided`) - **Java 8+**: Compatible with JDK 8 ## Maven Dependency ```xml io.github.chargeduck lesscoding-util 1.0.0 ``` ## Quick Start ### Export Excel (Single-row Header) ```java // 1. Define headers List> headers = Stream.of( new ExcelHeaderVo<>("Name", Goods::getName), new ExcelHeaderVo<>("Category", Goods::getCategoryStr), new ExcelHeaderVo<>("Unit", Goods::getUnit), new ExcelHeaderVo<>("Price", Goods::getPrice) ).collect(Collectors.toList()); // 2. Create Workbook and write data Workbook workbook = new XSSFWorkbook(); TemplateUtil.writeDataSheet(workbook, "Goods List", headers, goodsList, null); // 3. Set response header and output TemplateUtil.setResponseHeader(response, "Goods List.xlsx", true); workbook.write(response.getOutputStream()); ``` ### Export Template with Dropdowns ```java Workbook workbook = new XSSFWorkbook(); // Write hidden sheet (dropdown data source) TemplateUtil.writeHiddenSheet(workbook, "category", categoryList, GoodsCategory::getName, GoodsCategory::getId); // Configure formula dropdown List formulas = Stream.of( new ExcelFormula() .setCellIndex(1) .setByFormula(true) .setNameManager(TemplateUtil.getSheetNameManagerName("category")) .setFormulaStr("INDEX(categoryName,0,1)") .setSimpleCellIndex(headers.size()) .setSimpleHeader("Category ID") .setSimpleCellDataCell(2) ).collect(Collectors.toList()); TemplateUtil.writeDataSheet(workbook, "Goods Template", headers, dataList, formulas); workbook.write(outputStream); ``` ### Cascading Dropdown (Province → City → District) A complete three-level cascading dropdown template. Hidden sheets store the `[value column, parent column]` mappings, and `ExcelFormula` chains columns together via the name manager. ```java // Data source entity: value = child (city/district), parent = parent (province/city) class Region { private final String value; private final String parent; Region(String value, String parent) { this.value = value; this.parent = parent; } String getValue() { return value; } String getParent() { return parent; } } ``` ```java List provinceCityList = Arrays.asList( new Region("Guangzhou", "Guangdong"), new Region("Shenzhen", "Guangdong"), new Region("Hangzhou", "Zhejiang")); List cityDistrictList = Arrays.asList( new Region("Tianhe", "Guangzhou"), new Region("Nanshan", "Shenzhen"), new Region("Xihu", "Hangzhou")); Workbook workbook = new XSSFWorkbook(); // 1. Write hidden sheets: each has [value column, parent column] TemplateUtil.writeHiddenSheets(workbook, Arrays.asList( new HiddenSheetVo<>("ProvinceCity", provinceCityList, Arrays.asList(Region::getValue, Region::getParent)), new HiddenSheetVo<>("CityDistrict", cityDistrictList, Arrays.asList(Region::getValue, Region::getParent)))); // 2. Data sheet headers List> headers = Arrays.asList( new ExcelHeaderVo<>("Province", RegionRow::getProvince), new ExcelHeaderVo<>("City", RegionRow::getCity), new ExcelHeaderVo<>("District", RegionRow::getDistrict)); TemplateUtil.reorderExcelHeaderList(headers); // 3. Cascading validation: // Province column -> explicit list // City column -> filter ProvinceCity by the Province cell (index 0) // District column -> filter CityDistrict by the City cell (index 1) List formulas = Arrays.asList( new ExcelFormula().setCellIndex(0).setByFormula(false) .setDataList(Arrays.asList("Guangdong", "Zhejiang", "Jiangsu")), new ExcelFormula().setCellIndex(1).setByFormula(true) .setSpecialFormulaFlag(true) .setSpecialFilterNM(TemplateUtil.getSheetNameManagerName("ProvinceCity")) .setSpecialFilterNMCell(2) .setSpecialFilterNMOffset(1) .setSpecialFilterByCell(0), new ExcelFormula().setCellIndex(2).setByFormula(true) .setSpecialFormulaFlag(true) .setSpecialFilterNM(TemplateUtil.getSheetNameManagerName("CityDistrict")) .setSpecialFilterNMCell(2) .setSpecialFilterNMOffset(1) .setSpecialFilterByCell(1)); // 4. Generate the template (null dataList => 1000 empty template rows) TemplateUtil.writeDataSheet(workbook, "Region Template", headers, null, formulas); ``` > **Note**: The hidden sheet column order matters — the first column is the > value to be picked (city / district) and the second column is the filter > key (province / city). `RegionRow` is a POJO exposing `province` / `city` / > `district` getters. ### Export with Merged Headers ```java List> mergeHeaders = Arrays.asList( ExcelMergeHeaderVo.group("Basic Info", ExcelMergeHeaderVo.leaf("Name", new ExcelHeaderVo<>("Name", User::getName)), ExcelMergeHeaderVo.leaf("Age", new ExcelHeaderVo<>("Age", User::getAge)) ), ExcelMergeHeaderVo.group("Contact", ExcelMergeHeaderVo.leaf("Phone", new ExcelHeaderVo<>("Phone", User::getPhone)), ExcelMergeHeaderVo.leaf("Email", new ExcelHeaderVo<>("Email", User::getEmail)) ) ); Sheet sheet = TemplateUtil.writeDataSheetByMerge(workbook, "Users", mergeHeaders, formulas); // Sheet has headers + 1000 template rows generated; caller can continue filling data // Pass an extra templateRow argument to customize the empty-template row count: // TemplateUtil.writeDataSheetByMerge(workbook, "Users", mergeHeaders, formulas, 500); ``` ### Import Parsing ```java Workbook workbook = WorkbookFactory.create(inputStream); List> data = TemplateUtil.parseBaseAndExtra( workbook, "Goods List", 0, null); // Each row is a LinkedHashMap: key=header text, value=cell value ``` ## Core API ### TemplateUtil (Facade Utility Class) All public APIs are accessed via `TemplateUtil` static methods: | Method | Description | |---|---| | `setResponseHeader(response, fileName)` | Set HTTP file download response header | | `setResponseHeader(response, fileName, addTimeStr)` | Set response header, optionally append timestamp | | `writeHiddenSheet(workbook, sheetName, dataList, mapperList...)` | Write hidden sheet (varargs) | | `writeHiddenSheet(workbook, sheetName, dataList, mapperList)` | Write hidden sheet (List version) | | `writeHiddenSheets(workbook, hiddenSheetVoList)` | Batch write hidden sheets | | `writeDataSheet(workbook, sheetName, headers, dataList, formulas)` | Single-row header data sheet | | `writeDataSheet(workbook, sheetName, headers, dataList, formulas, templateRow)` | Single-row header data sheet with custom empty-template rows | | `writeDataSheetByMerge(workbook, sheetName, mergeHeaders, formulas)` | Multi-level merged header sheet | | `writeDataSheetByMerge(workbook, sheetName, mergeHeaders, formulas, templateRow)` | Multi-level merged header sheet with custom empty-template rows | | `parseBaseAndExtra(workbook, sheetName, headerRowIndex, replaceMap)` | Parse Excel into `List` | | `getSheetNameManagerName(sheetName)` | Get Name Manager name for a given sheet | | `getLetterByIndex(index, plusFlag)` | Column index → Excel column letter | | `reorderExcelHeaderList(list)` | Reorder header cellIndex values | | `reorderExcelFormula(list, headerSize)` | Auto-assign helper column indices for formulas | ### Data Models | Class | Description | |---|---| | `ExcelHeaderVo` | Header column definition: index, text, getter function | | `ExcelMergeHeaderVo` | Multi-level merged header tree node with `group()`/`leaf()` factory methods | | `HiddenSheetVo` | Hidden sheet definition: name, data list, column mappers | | `ExcelFormula` | Formula/dropdown validation: supports explicit list and formula reference modes | | `TemplateUtil.InnerDict` | Built-in dictionary key-value pair (code/name) for quick hidden sheet construction | ## Project Structure ``` src/main/java/io/github/chargeduck/excel/ ├── TemplateUtil.java # Facade utility class (all public API entry points) ├── export/ │ ├── SimpleExporter.java # Single-row header exporter │ └── MergeExporter.java # Multi-level merged header exporter (recursive tree) ├── parser/ │ └── ExcelParser.java # Excel import parser ├── model/ │ ├── ExcelHeaderVo.java # Header column definition │ ├── ExcelMergeHeaderVo.java# Merged header tree node │ ├── ExcelFormula.java # Formula/dropdown validation definition │ └── HiddenSheetVo.java # Hidden sheet definition └── internal/ ├── LetterHelper.java # Column letter conversion utility ├── NameManagerHelper.java # Name Manager utility ├── StrFormatter.java # String formatting utility └── GroupRange.java # Merged header span helper ``` ## License MIT License