一. 导出excel,具体代码如下:
public ResponseEntity<byte[]> listExcel(@RequestBody JSONObject param) throws Exception { List<Bean> list = userService.queryList(params); String[] titles = {"系统序号", "客户名称", "金额", "明细", "审核状态", "创建时间"}; ByteArrayOutputStream out = new ByteArrayOutputStream(); // 操作具体的数据 Workbook wb = userService.exportSalesScore(titles, out, list); HttpHeaders headers = new HttpHeaders(); headers.setContentDispositionFormData("attachment", "充值明细导出.xls"); headers.setContentType(MediaType.APPLICATION_OCTET_STREAM); wb.write(out); return new ResponseEntity<byte[]>(out.toByteArray(), headers, HttpStatus.OK); } public Workbook exportSalesScore(String[] titles, ByteArrayOutputStream out, List<Bean> list) throws Exception { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet hssfSheet = workbook.createSheet("sheet1"); HSSFRow row = hssfSheet.createRow(0); HSSFCellStyle hssfCellStyle = workbook.createCellStyle(); HSSFCell hssfCell; for (int i = 0; i < titles.length; i++) { hssfCell = row.createCell(i);//列索引从0开始 hssfCell.setCellValue(titles[i]);//列名1 hssfCell.setCellStyle(hssfCellStyle);//列居中显示 } if(CollectionUtils.isNotEmpty(list)) { SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); for (int i = 0; i < list.size(); i++) { row = hssfSheet.createRow(i + 1); Bean s = list.get(i); row.createCell(0).setCellValue(s.getId()); row.createCell(1).setCellValue(s.getUsername()); row.createCell(2).setCellValue(s.getMoney()); row.createCell(3).setCellValue(s.getDetail()); row.createCell(4).setCellValue(s.getAuditString()); row.createCell(5).setCellValue(df.format(s.getCreateTime())); } } try { workbook.write(out); out.flush(); out.close(); } catch (Exception e) { log.info("导出异常:{}", e); } return workbook; }
二. 读取excel,具体代码如下:
ReadExcelUtils excelReader = new ReadExcelUtils("文件在服务器上的路径"); List<Map<Integer, Object>> mapList = excelReader.readExcelContent(); /** * 读取Excel数据内容 * * @param * @return Map 包含单元格数据内容的Map对象 * @author zengwendong */ public List<Map<Integer,Object>> readExcelContent() throws Exception{ if(wb==null){ throw new Exception("Workbook对象为空!"); } List<Map<Integer,Object>> content = new ArrayList<>(); sheet = wb.getSheetAt(0); // 得到总行数 int rowNum = sheet.getLastRowNum(); row = sheet.getRow(0); int colNum = row.getPhysicalNumberOfCells(); // 第一行为表头的标题 // 正文内容从第四行开始(当前业务需要,企小保灵工小保人员清单表) for (int i = 3; i <= rowNum; i++) { row = sheet.getRow(i); int j = 1; Map<Integer,Object> cellValue = new HashMap<>(); while (j < colNum) { Object obj = getCellFormatValue(row.getCell(j)); cellValue.put(j, obj); j++; } content.add(cellValue); } return content; } public class ReadExcelUtils { private Logger logger = LoggerFactory.getLogger(ReadExcelUtils.class); private Workbook wb; private Sheet sheet; private Row row; public ReadExcelUtils(String filepath) { if(filepath==null){ return; } String ext = filepath.substring(filepath.lastIndexOf(".")); try { InputStream is = new FileInputStream(filepath); if(".xls".equals(ext)){ wb = new HSSFWorkbook(is); }else if(".xlsx".equals(ext)){ wb = new XSSFWorkbook(is); }else{ wb=null; } } catch (FileNotFoundException e) { logger.error("FileNotFoundException", e); } catch (IOException e) { logger.error("IOException", e); } } /** * 读取Excel表格表头的内容 * * @param * @return String 表头内容的数组 * @author zengwendong */ public String[] readExcelTitle() throws Exception{ if(wb==null){ throw new Exception("Workbook对象为空!"); } sheet = wb.getSheetAt(0); row = sheet.getRow(0); // 标题总列数 int colNum = row.getPhysicalNumberOfCells(); System.out.println("colNum:" + colNum); String[] title = new String[colNum]; for (int i = 0; i < colNum; i++) { title[i] = row.getCell(i).getCellFormula(); } return title; } /** * 读取Excel数据内容 * * @param * @return Map 包含单元格数据内容的Map对象 * @author zengwendong */ public List<Map<Integer,Object>> readExcelContent() throws Exception{ if(wb==null){ throw new Exception("Workbook对象为空!"); } List<Map<Integer,Object>> content = new ArrayList<>(); sheet = wb.getSheetAt(0); // 得到总行数 int rowNum = sheet.getLastRowNum(); row = sheet.getRow(0); int colNum = row.getPhysicalNumberOfCells(); // 第一行为表头的标题 // 正文内容从第四行开始(当前业务需要,企小保灵工小保人员清单表) for (int i = 3; i <= rowNum; i++) { row = sheet.getRow(i); int j = 1; Map<Integer,Object> cellValue = new HashMap<>(); while (j < colNum) { Object obj = getCellFormatValue(row.getCell(j)); cellValue.put(j, obj); j++; } content.add(cellValue); } return content; } /** * * 根据Cell类型设置数据 * * @param cell * @return * @author zengwendong */ private Object getCellFormatValue(Cell cell) { Object cellvalue = ""; if (cell != null) { // 判断当前Cell的Type switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC:// 如果当前Cell的Type为NUMERIC case Cell.CELL_TYPE_FORMULA: { // 判断当前的cell是否为Date if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); cellvalue = date; } else {// 如果是纯数字 // 取得当前Cell的数值 cellvalue = String.valueOf(cell.getNumericCellValue()); } break; } case Cell.CELL_TYPE_STRING:// 如果当前Cell的Type为STRING // 取得当前的Cell字符串 cellvalue = cell.getRichStringCellValue().getString(); break; default:// 默认的Cell值 } } return cellvalue; } public static void main(String[] args) { try { String filepath = "/Users/yinpz/Downloads/企小保灵工小保人员清单表.xls"; ReadExcelUtils excelReader = new ReadExcelUtils(filepath); List<Map<Integer, Object>> mapList = excelReader.readExcelContent(); JSONArray jsonArray = JSONArray.parseArray(JSON.toJSONString(mapList)); for (Object object : jsonArray) { JSONObject json = JSON.parseObject(object.toString()); System.out.println(json); } // 对读取Excel表格内容测试 System.out.println(JSON.toJSONString(mapList)); } catch (FileNotFoundException e) { System.out.println("未找到指定路径的文件!"); e.printStackTrace(); }catch (Exception e) { e.printStackTrace(); } } }