Java导出Excel并下载

Java使用POI生成Excel、下载文件

Java查询结果生成多 Sheet 的页 Excel,提供页面导出功能

查询结果直接导出 Excel

• 思路: 使用 import javax.servlet.http.HttpServletResponse 获取输出流,将文件写入,实现导出

controller 代码

@RequestMapping(value = "xx" method = RequestMethod.POST)
public void batchCommonalityQuery(HttpServletResponse response) {
  try {
    String fileName = file.getOriginalFilename();
    // 文件名称   
    String name = "xx.xlsx";

    response.setContentType("application/octet-stream");
    response.addHeader("Content-Disposition",	"attachment;filename=\"" + new String(name.getBytes("UTF-8"), "ISO-8859-1"));
    
    OutputStream outputStream = response.getOutputStream();
    
    List<List<UserUrlVO>> list = this.commonalityService.batchCommonsQuery(file, outputStream);
  } catch (Exception e) {
    e.printStackTrace();
  }
}

service 代码

private void exportExcel(List<List<T>> list, List<String> sheetName, String[] header, OutputStream out) {
  HSSFWorkbook workbook = new HSSFWorkbook();

  try {
    for (int i = 0; i < list.size(); i++) {
      // sheet页名
      HSSFSheet sheet = workbook.createSheet(sheetName.get(i));

      // list内容
      List<T> values = list.get(i);
      HSSFRow row;// = sheet.createRow(values.size());

      // 设置行标题(Excel第一行)
      row = sheet.createRow(0);
      for (int h = 0; h < header.length; h++) {
        row.createCell(h).setCellValue(header[h]);
        sheet.autoSizeColumn(h, true);
        sheet.setColumnWidth(h, sheet.getColumnWidth(h) * 17 / 10);
      }
      // 写入行 —— 单元格
      for (int j = 0; j < values.size(); j++) {
        row = sheet.createRow(j + 1);

        Object object = values.get(j);
        // 遍历实体类属性
        Field[] fields = object.getClass().getDeclaredFields();
        // 单元格内容
        for (int k = 0; k < fields.length; k++) {
          Field field = fields[k];
          field.setAccessible(true);
          row.createCell(k).setCellValue(field.get(object).toString());
        }
      }
    }
    // 响应下载
    workbook.write(out);
    out.close();
  } catch (Exception e) {
    e.printStackTrace();
  } finally {
    try {
      out.flush();
      out.close();
    } catch (IOException e) {
      e.printStackTrace();
    }
  }
}

将文件保存到服务器,提供返回下载路径

controller 代码

 @RequestMapping(value = "downloadFile")
public void downloadFile(@RequestParam(value = "filepath", required = true) String filePath,HttpServletResponse response) {
	this.commonalityService.downloadFile(filePath, response);
}

service 代码

1. 将文件写入本地

final static String EXCEL_PATH = System.getProperty("os.name").toLowerCase().startsWith("win") ? "D:\\fileupload\\excel\\" : "/root/commonality";

  // 将文件写入本地
  private void writeExcel(List<List<T>> list, List<String> sheetName, String[] header, String fileName) {
    HSSFWorkbook workbook = new HSSFWorkbook();

    try {
      for (int i = 0; i < list.size(); i++) {
        // sheet页名
        HSSFSheet sheet = workbook.createSheet(sheetName.get(i));

        // list内容
        List<T> values = list.get(i);
        HSSFRow row;// = sheet.createRow(values.size());

        // 设置行标题(Excel第一行)
        row = sheet.createRow(0);
        for (int h = 0; h < header.length; h++) {
          row.createCell(h).setCellValue(header[h]);
          sheet.autoSizeColumn(h, true);
          sheet.setColumnWidth(h, sheet.getColumnWidth(h) * 17 / 10);
        }
        // 写入行 —— 单元格
        for (int j = 0; j < values.size(); j++) {
          row = sheet.createRow(j + 1);

          Object object = values.get(j);
          // 遍历实体类属性
          Field[] fields = object.getClass().getDeclaredFields();
          // 单元格内容
          for (int k = 0; k < fields.length; k++) {
            Field field = fields[k];
            field.setAccessible(true);
            row.createCell(k).setCellValue(field.get(object).toString());
          }
        }
      }
      // 判断文件夹目录是否存在,不存在则创建
      File file = new File(EXCEL_PATH);
      if (!file.exists()) {
        // 创建的是多级目录,file.mkdir(); 只能创建一级目录
        file.mkdirs();
      }
      // 生成文件保存到服务器
      String path = EXCEL_PATH + fileName;
      FileOutputStream stream = new FileOutputStream(path);
      workbook.write(stream);
      stream.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

2. 下载文件

public void downloadFile(String filePath, HttpServletResponse response) {
	OutputStream outputStream = null;
	FileInputStream fis = null;
	try {
		response.setContentType("application/octet-stream");
		response.setCharacterEncoding("UTF-8");
		response.addHeader("Content-Disposition",
				"attachment;filename=\"" + new String(filePath.getBytes("UTF-8"), "ISO-8859-1"));

		outputStream = response.getOutputStream();
		fis = new FileInputStream(EXCEL_PATH + filePath);
		byte[] bytes = new byte[1024];
		int lengtth = 0;
		while ((lengtth = fis.read(bytes)) != -1) {
			outputStream.write(bytes, 0, lengtth);
		}
		outputStream.flush();
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		try {
			if (outputStream != null) {
				outputStream.close();
			}
			if (fis != null) {
				fis.close();
			}
		} catch (IOException e) {
			e.printStackTrace();
		}

	}
}

公共方法

// @描述：是否是2003的excel，返回true是2003
private boolean isExcel2003(String filePath) {
  return filePath.matches("^.+\\.(?i)(xls)$");
}

// @描述：是否是2007的excel，返回true是2007
private boolean isExcel2007(String filePath) {
  return filePath.matches("^.+\\.(?i)(xlsx)$");
}

private Object getCellFormatValue(Cell cell) {
  Object cellValue = null;
  if (cell != null) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_NUMERIC:
      cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
      break;
    case Cell.CELL_TYPE_STRING:
      cellValue = cell.getRichStringCellValue().getString().trim();
      break;
    default:
      cellValue = "";
      break;
    }
  } else {
    cellValue = "";
  }
  return cellValue;
}