< rowEnd; row++) {xssfRow = xssfSheet.getRow(row);xssfCell = xssfRow.getCell(column);//都转成String，避免小数精度xssfCell.setCellType(CellType.STRING);result.add(getCellValueByCell(xssfCell));}} catch (Exception e) {log.error("failed to read data from excel file!");throw new RuntimeException();}return result;}/*** 获取单元格各类型值，返回字符串类型** @param cell* @return*/private static String getCellValueByCell(XSSFCell cell) {//判断是否为null或空串if (cell == null || ("").equals(cell.toString().trim())) {return "";}String cellValuehttp://www.kingceram.com/post/= "";CellType cellType = cell.getCellTypeEnum();// 以下是判断数据的类型switch (cellType) {// 数字case NUMERIC:cellValue = String.valueOf(cell.getNumericCellValue());break;// 字符串case STRING:cellValue = cell.getStringCellValue();break;// Booleancase BOOLEAN:cellValue = String.valueOf(cell.getBooleanCellValue());break;// 公式case FORMULA:cellValue = String.valueOf(cell.getCellFormula());break;// 空值case BLANK:cellValuehttp://www.kingceram.com/post/= "";break;// 故障case ERROR:cellValuehttp://www.kingceram.com/post/= "非法字符";break;default:cellValuehttp://www.kingceram.com/post/= "未知类型";break;}return cellValue;}/*** 解析.xlsx文件，转成XSSFWorkbook** @param excelFile* @return*/public static XSSFWorkbook getXSSFWorkbookFrExcel(MultipartFile excelFile) {log.warn("receive excel file, file size:[{} MB]", excelFile.getSize() / (1000 * 1024));XSSFWorkbook wb;try {InputStream is = excelFile.getInputStream();wb = new XSSFWorkbook(is);is.close();} catch (Exception e) {log.error("get XSSFWorkbook from excelFile error! e:{} message:{}", e, e.getMessage());return null;}return wb;}/*** 获取XSSFSheet的行数** @param xssfSheet* @return*/public static int getRowCount(XSSFSheet xssfSheet) {return xssfSheet.getPhysicalNumberOfRows();}/*** 获取xssfSheet的列数(默认第一行)** @param xssfSheet* @return*/public static int getColumnCount(XSSFSheet xssfSheet) {return xssfSheet.getRow(0).getPhysicalNumberOfCells();}}
导出查询结果的代码示例：
/*** 导出查询记录*/@Overridepublic boolean exportQryRecords(HttpServletResponse response, AwardSendRecordQryCondDTO qryCondDTO) {// 导出类型可配置String exportType = ConfigManager.getString(ApplicationConstants.FILE_EXPORT_TYPE_KEY, ApplicationConstants.FILE_EXPORT_TYPE);AbstractFileExporter exporter = Optional.ofNullable(FileExporterFactory.createExporter(exportType, AwardSendRecordDTO.class)).orElseThrow(() -> new BusinessException(ResultCodeEnum.SERVER_BUSYNESS));// 获取数据集RowBounds rowBounds = PageUtils.getRowBound();List dataTemp;int total = 0;int maxExportItemNum = ConfigManager.getInteger(ApplicationConstants.FILE_EXPORT_ITEMNUM_MAX_KEY, ApplicationConstants.FILE_EXPORT_ITEMNUM_MAX);try {while (true) {Page pageData = http://www.kingceram.com/post/awardSendRecordDAO.pageQueryConditionallyAsc(rowBounds, qryCondDTO);dataTemp = pageData.getResult();if (CollectionUtils.isEmpty(dataTemp)) {log.warn("query result empty.");break;}// 做多导出(默认)20W数据if (total + pageData.size() > maxExportItemNum) {log.warn("reach max export size.[total={} dataTempSize={}]", total, pageData.size());break;}List awardSendRecords = dataTemp.stream().map(awardSendRecordDO -> beanConvertMapper.convertToAwardSendRecordDTO(awardSendRecordDO)).collect(Collectors.toList());// 增量填充数据exporter.appendData(awardSendRecords);total += awardSendRecords.size();// 翻页rowBounds = PageUtils.next(rowBounds);}log.warn("query export data end.[currentPage={} total={}]", rowBounds.getOffset(), total);// 导出文件String fileName = exporter.getFileName("奖品发放记录");response.setContentType("application/octet-stream");response.setHeader("Content-disposition", "attachment;filename=" + fileName);OutputStream outputStream = response.getOutputStream();response.flushBuffer();exporter.writeStream(outputStream);// 写完数据关闭流outputStream.close();return true;} catch (Exception e) {log.error("exportQryRecords error! e:{}", e);return false;}}