目的

用于Chatgpt模型训练

实现效果

Excel

Jsonl

如何实现

引入maven依赖

	<dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>5.2.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.2</version></dependency>

实现方法

@Service
public class ExcelToJsonlService {@SneakyThrowspublic void convertExcelToJsonl(String excel, String jsonl) throws Exception {ObjectMapper objectMapper = new ObjectMapper();File inputFile = new File("D:" + File.separator + "1.xlsx");File outputFile = new File("D:" + File.separator + "1.jsonl");@CleanupFileInputStream fileInputStream = new FileInputStream(inputFile);@CleanupFileOutputStream fileOutputStream = new FileOutputStream(outputFile);Workbook workbook = WorkbookFactory.create(fileInputStream);Sheet sheet = workbook.getSheetAt(0);List<Map<String, Object>> dataList = new ArrayList<>();// 遍历每一行for (int i = 1; i <= sheet.getLastRowNum(); i++) {Row row = sheet.getRow(i);Map<String, Object> dataMap = new HashMap<>();// 遍历每一列for (int j = 0; j < row.getLastCellNum(); j++) {Cell cell = row.getCell(j);String columnName = sheet.getRow(0).getCell(j).getStringCellValue();// 根据列名处理不同类型的数据switch (cell.getCellType()) {case STRING:dataMap.put(columnName, cell.getStringCellValue());break;case NUMERIC:dataMap.put(columnName, cell.getNumericCellValue());break;case BOOLEAN:dataMap.put(columnName, cell.getBooleanCellValue());break;default:dataMap.put(columnName, null);}}dataList.add(dataMap);}// 将数据写入jsonl文件中for (Map<String, Object> data : dataList) {String json = objectMapper.writeValueAsString(data);fileOutputStream.write(json.getBytes());fileOutputStream.write("\n".getBytes()); // 每条数据结束后加上换行符}}
}

接口测试

    @PostMapping("/excelToJsonl")@SneakyThrowspublic R excelToJsonl(@RequestParam(value = "excel",required = false) String excel,@RequestParam(value = "jsonl",required = false) String jsonl) {excelToJsonlService.convertExcelToJsonl(excel, jsonl);return R.ok();}

以上就是使用Spring实现Excel转换成jsonl文档格式的代码示例。