导入Excel复杂表头 ,然后生成一个html形式的字符串 ,返回前端
/** * 导入excel */ @RequestMapping(value = "/excelImport") @ResponseBody public Map list(String condition, @RequestParam MultipartFile file, HttpServletRequest request) throws IOException, InvalidFormatException { /*MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request; MultipartFile file = multipartRequest.getFile("fileUpload");*/ String fileName = file.getOriginalFilename(); // String suffixName = fileName.substring(fileName.lastIndexOf(".")); String realPath = request.getRealPath("/"); File dest = new File(realPath + "/" + fileName); String path = realPath+fileName; try { file.transferTo(dest); //保存文件 } catch (IllegalStateException e) { } ExcelShower els = new ExcelShower(); try { Map result = els.read(null, path); System.out.println(result.get("lsb").toString()); System.out.println(result.get("title").toString()); System.out.println(result.get("content").toString()); return result; } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; }
public Map read(Map properties,String fileUrl) throws Exception { HSSFSheet sheet = null; StringBuffer lsb = new StringBuffer(); // String excelFileName = SojaProperties.getSojaRoot() + // "/page/readExcel/sale.xls"; String excelFileName = "C:\\Users\\Administrator.QH-20171111LPZE\\Desktop\\2222测试.xls"; HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream( fileUrl)); // 获整个Excel Map map = new HashMap(); map = excleToHtml(workbook); System.out.println(lsb.toString()); return map; } public Map excleToHtml(HSSFWorkbook workbook) { HSSFSheet sheet; StringBuffer lsb = new StringBuffer(); Map map = new HashMap(); List<String> titleList = new ArrayList<>(); List<String> contentList = new ArrayList<>(); try { for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) { sheet = workbook.getSheetAt(sheetIndex);// 获所有的sheet String sheetName = workbook.getSheetName(sheetIndex); // sheetName if (workbook.getSheetAt(sheetIndex) != null) { sheet = workbook.getSheetAt(sheetIndex);// 获得不为空的这个sheet if (sheet != null) { int firstRowNum = sheet.getFirstRowNum(); // 第一行 int lastRowNum = sheet.getLastRowNum(); // 最后一行 // 构造Table lsb.append("<table style=\"width:100% !important; border:1px solid #000;border-width:1px 0 0 1px;margin:2px 0 2px 0;border-collapse:collapse;\">"); for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) { if (sheet.getRow(rowNum) != null) {// 如果行不为空, HSSFRow row = sheet.getRow(rowNum); short firstCellNum = row.getFirstCellNum(); // 该行的第一个单元格 short lastCellNum = row.getLastCellNum(); // 该行的最后一个单元格 int height = (int) (row.getHeight() / 15.625); // 行的高度 if(rowNum != lastRowNum){ lsb.append("<tr height=\"" + height + "\" style=\"border:1px solid #000;border-width:0 1px 1px 0;margin:2px 0 2px 0;\">"); } for (short cellNum = firstCellNum; cellNum <= lastCellNum; cellNum++) { // 循环该行的每一个单元格 HSSFCell cell = row.getCell(cellNum); if (cell != null) { //获取最后二行的值 if(rowNum == lastRowNum-1){ System.out.println(cell.getStringCellValue()); String a =cell.getStringCellValue(); if (cell.getStringCellValue().equals("") ||cell.getStringCellValue() == null) { for (int begin = rowNum-1; rowNum >=0; begin--) { HSSFRow rowPre = sheet.getRow(begin); cell = rowPre.getCell(cellNum); if (!cell.getStringCellValue().equals("") && cell.getStringCellValue() != null){ break; } } } titleList.add(getCellValue(cell).toString()); cell = row.getCell(cellNum); } if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { continue; } else { StringBuffer tdStyle = new StringBuffer( "<td style=\"border:1px solid #000; border-width:0 1px 1px 0;margin:2px 0 2px 0; "); HSSFCellStyle cellStyle = cell .getCellStyle(); HSSFPalette palette = workbook .getCustomPalette(); // 类HSSFPalette用于求颜色的国际标准形式 HSSFColor hColor = palette .getColor(cellStyle .getFillForegroundColor()); HSSFColor hColor2 = palette .getColor(cellStyle .getFont(workbook) .getColor()); String bgColor = convertToStardColor(hColor);// 背景颜色 short boldWeight = cellStyle .getFont(workbook) .getBoldweight(); // 字体粗细 short fontHeight = (short) (cellStyle .getFont(workbook) .getFontHeight() / 2); // 字体大小 String fontColor = convertToStardColor(hColor2); // 字体颜色 if (bgColor != null && !"".equals(bgColor .trim())) { tdStyle.append(" background-color:" + bgColor + "; "); } if (fontColor != null && !"".equals(fontColor .trim())) { tdStyle.append(" color:" + fontColor + "; "); } tdStyle.append(" font-weight:" + boldWeight + "; "); tdStyle.append(" font-size: " + fontHeight + "%;"); if(rowNum != lastRowNum){ lsb.append(tdStyle + "\""); } int width = (int) (sheet .getColumnWidth(cellNum) / 35.7); // int cellReginCol = getMergerCellRegionCol( sheet, rowNum, cellNum); // 合并的列(solspan) int cellReginRow = getMergerCellRegionRow( sheet, rowNum, cellNum);// 合并的行(rowspan) String align = convertAlignToHtml(cellStyle .getAlignment()); // String vAlign = convertVerticalAlignToHtml(cellStyle .getVerticalAlignment()); if(rowNum != lastRowNum){ lsb.append(" align=\"" + align + "\" valign=\"" + vAlign + "\" width=\"" + width + "\" "); lsb.append(" colspan=\"" + cellReginCol + "\" rowspan=\"" + cellReginRow + "\""); lsb.append(">" + getCellValue(cell) + "</td>"); } //获取最后二行的值 /*if(rowNum == lastRowNum-1){ titleList.add(getCellValue(cell).toString()); }*/ if(rowNum == lastRowNum){ contentList.add(getCellValue(cell).toString()); } } } } lsb.append("</tr>"); } } } } } } catch (FileNotFoundException e) { } catch (IOException e) { } lsb.append("</table>"); map.put("lsb",lsb); map.put("title",titleList); map.put("content",contentList); return map; }