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);
for (int j = 0; j < columns.length; j++) {
Object data = enterprise.get(i).get(eName[j]);
Boolean isNum = false;
Boolean isInteger = false;
Boolean isPercent = false;
if (data != null || "".equals(data) ) {
isNum = data.toString().matches("^(-?\\d+)(\\.\\d+)?$");
isInteger = data.toString().matches("^[-\\+]?[\\d]*$");
isPercent = data.toString().contains("%");
}
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);
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;
}