poi 可以选择行和列导出数据到Excel表格

poi 可以选择行和列导出数据到Excel表格

	@Override
	public Response exportExcel(HttpServletResponse response, Long[] ids, String[] columns) {
		try {
			if (ids == null || ids.length == 0) {
				responseUtils.failed("id不能为空");
			}
			
			if (columns == null || columns.length == 0) {
				responseUtils.failed("columns不能为空");
			} 
			
			List<Map<String, String>> enterprise = enterpriseRepository.findAllByIds(ids);
			XSSFWorkbook workbook = new XSSFWorkbook();
			
			XSSFSheet sheet = workbook.createSheet();
			// 设置格式
			XSSFCellStyle contextstyle =workbook.createCellStyle();
			XSSFCellStyle setBorder = createCommonStyle(workbook);
			XSSFCellStyle setBorder1 = createCommonStyle1(workbook);
			// 设置表头
			XSSFRow headRow = sheet.createRow(0);
			XSSFCell cell0 = headRow.createCell(0);
			cell0.setCellValue("NAME");
			cell0.setCellStyle(setBorder);
			XSSFCell cell1 = headRow.createCell(1);
			cell1.setCellValue("NAME");
			cell1.setCellStyle(setBorder);
			for (int i = 0; i < columns.length; i++) {
				XSSFCell cell = headRow.createCell(i + 2);
				cell.setCellValue(columns[i]);
				cell.setCellStyle(setBorder);
			}

			// 填充数据
			String[] eName = changeName(columns);
			for (int i = 0; i < ids.length; i++) {
				XSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
				XSSFCell cellR0 = dataRow.createCell(0);
				cellR0.setCellValue(enterprise.get(i).get("name"));
				cellR0.setCellStyle(setBorder);
				XSSFCell cellR1 = dataRow.createCell(1);
				cellR1.setCellValue(arrToString(enterpriseRepository.findProjectNameByEid(ids[i])));
				cellR1.setCellStyle(setBorder1);
				//dataRow.createCell(1).setCellValue(arrToString(enterpriseRepository.findProjectNameByEid(ids[i])));
				for (int j = 0; j < columns.length; j++) {

					Object data = enterprise.get(i).get(eName[j]);
					Boolean isNum = false;// data是否为数值型
					Boolean isInteger = false;// data是否为整数
					Boolean isPercent = false;// data是否为百分数          

					if (data != null || "".equals(data) ) {
						// 判断data是否为数值型
						isNum = data.toString().matches("^(-?\\d+)(\\.\\d+)?$");
						// 判断data是否为整数（小数部分是否为0）
						isInteger = data.toString().matches("^[-\\+]?[\\d]*$");
						// 判断data是否为百分数（是否包含“%”）
						isPercent = data.toString().contains("%");
					}

					// 如果单元格内容是数值类型，涉及到金钱（金额、本、利），则设置cell的类型为数值型，设置data的类型为数值类型
					if (isNum && !isPercent) {
						XSSFDataFormat df = workbook.createDataFormat(); 
						// 此处设置数据格式
						if (isNum) {
							contextstyle.setDataFormat(df.getFormat("#,#0"));
						} else {
							contextstyle.setDataFormat(df.getFormat("#,##0.00"));
						}
						XSSFCell cellR = dataRow.createCell(j + 2);
						// 设置单元格格式
						cellR.setCellStyle(contextstyle);
						cellR.setCellStyle(setBorder1);
						// 设置单元格内容为double类型
						cellR.setCellValue(Double.parseDouble(data.toString()));
					} else if (data == null ) {
						XSSFCell cellR = dataRow.createCell(j + 2);
						cellR.setCellStyle(setBorder1);
						cellR.setCellValue("");
					} else {
						XSSFCell cellR = dataRow.createCell(j + 2);
						cellR.setCellStyle(contextstyle);
						cellR.setCellStyle(setBorder1);
						// 设置单元格内容为字符型
						cellR.setCellValue(data.toString());
					}

				}
			}

			// 必须在单元格设值以后进行
            // 设置为根据内容自动调整列宽    
            for (int k = 0; k < columns.length + 2; k++) {
                sheet.autoSizeColumn(k);
            }
            // 处理中文不能自动调整列宽的问题
            this.setSizeColumn(sheet, columns.length + 2);

			// 字节数组输出流
			ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
			workbook.write(outputStream);
			String fileName = DateFormatUtils.format(new Date(), "yyyy-MM-dd HH:mm:ss") + ".xlsx";
			outputStream.writeTo(response.getOutputStream());
			response.setContentType("application/octet-stream");
			fileName = URLEncoder.encode(fileName, "UTF-8");
			response.setHeader("Content-Disposition",
					String.format("attachment; filename=%s; filename*=utf-8 %s", fileName, fileName));
			response.setContentLengthLong(outputStream.size());
			workbook.close();
			return responseUtils.success();
		} catch (Exception e) {
			log.error("导出失败", e);
			return responseUtils.failed("导出失败");
		}
	}

// 自适应宽度(中文支持)
    private void setSizeColumn(XSSFSheet sheet, int size) {
 
        for (int columnNum = 0; columnNum < size; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                XSSFRow currentRow;
                //当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }
 
                if (currentRow.getCell(columnNum) != null) {
                    XSSFCell currentCell = currentRow.getCell(columnNum);
                    if (currentCell.getCellType() == CellType.STRING) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            sheet.setColumnWidth(columnNum, columnWidth * 205);
        }
    }

 public String[] changeName(String[] names) {
		
		 for (int i = 0; i < names.length; i++) {
			 
			 if (names[i].equals("name")) {
					names[i] = "map中对应的key";
				}
			 // ...........
			 
		}
		return names;
 }

	 // 设置表格样式
	 private XSSFCellStyle createCommonStyle(XSSFWorkbook workbook) {
	        XSSFCellStyle setBorder = workbook.createCellStyle();
	        setBorder.setBorderBottom(BorderStyle.THIN); // 下边框
	        setBorder.setBorderLeft(BorderStyle.THIN); // 左边框
	        setBorder.setBorderTop(BorderStyle.THIN); // 上边框
	        setBorder.setBorderRight(BorderStyle.THIN); // 右边框
	        setBorder.setWrapText(true); //设置自动换行
	        setBorder.setAlignment(HorizontalAlignment.CENTER); // 设置垂直居中
	        setBorder.setVerticalAlignment(VerticalAlignment.CENTER); // 设置水平居中
	        
	        return setBorder;
	    }
	 // 设置表格样式
	 private XSSFCellStyle createCommonStyle1(XSSFWorkbook workbook) {
	        XSSFCellStyle setBorder1 = workbook.createCellStyle();
	        setBorder1.setBorderBottom(BorderStyle.THIN); // 下边框
	        setBorder1.setBorderLeft(BorderStyle.THIN); // 左边框
	        setBorder1.setBorderTop(BorderStyle.THIN); // 上边框
	        setBorder1.setBorderRight(BorderStyle.THIN); // 右边框
	        setBorder1.setWrapText(true); //设置自动换行

	        return setBorder1;
	    }