java POI 下载Excel自定义模板 导入Excel 导出Excel
一、应用场景
1.点击导入按钮，弹出导入框
2.点击下载模板，用户在模板中填入数据
3.导入模板数据
4.经过一系列处理后，根据模板导出数据
二、下载Excel模板
public void downloadTemplate(HttpServletResponse response) {
String newFileName = null;
try {
// URLEncoder.encode可以防止中文乱码
newFileName = URLEncoder.encode("模板名称", "UTF-8").replaceAll("\\+", "%20");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
// 当客户端请求的资源是一个可下载的资源（这里的“可下载”是指浏览器会弹出下载框或者下载界面）时，对这个可下载资源的描述（例如下载框中的文件名称）就是来源于该头域。
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + newFileName + ".xlsx");
// 服务器告诉浏览器它发送的数据属于什么文件类型，也就是响应数据的MIME类型
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 关闭缓存（HTTP/1.1）
response.setHeader("Cache-Control", "no-store");
// 关闭缓存（HTTP/1.0）
response.setHeader("Pragma", "no-cache");
// 缓存有效时间
response.setDateHeader("Expires", 0);
InputStream inputStream = null;
OutputStream outputStream = null;
try {
// 自定义模板路径 当时位置为 resources/download.xlsx
inputStream = new ClassPathResource("/download.xlsx").getInputStream();
XSSFWorkbook wb = new XSSFWorkbook(inputStream);
outputStream = response.getOutputStream();
wb.write(outputStream);
outputStream.flush();
} catch (IOException e) {
e.printStackTrace();
} finally {
IoUtil.close(inputStream);
IoUtil.close(outputStream);
}
}三、导入Excel
public String importData(MultipartFile file) throws Exception {
// 新增的对象(示例)
InsertData insertData = new InsertData();
int successNum = 0;
int failureNum = 0;
StringBuilder successMsg = new StringBuilder();
StringBuilder failureMsg = new StringBuilder();
// 获取文件输入流
InputStream inputStream = file.getInputStream();
// 读取整个excel
XSSFWorkbook sheets = new XSSFWorkbook(inputStream);
// 获取第一个sheet页
XSSFSheet sheetAt = sheets.getSheetAt(0);
try {
// 从第四行开始循环获取每一行数据
for (int i = 3; i < sheetAt.getPhysicalNumberOfRows() - 1; i++) {
Row row = sheetAt.getRow(i);
// 从第四行开始循环获取每一列数据 只获取第一列到第九列的数据，后面的数据导出时添加
for (int j = 0; j < 9; j++) {
/**
* 业务处理代码......
* 新增数据(示例) 获取第一列和第二列的数据
* */
if("".equals(getCellValue(row.getCell(0))) {
failureMsg.insert(0, "很抱歉，导入失败，姓名不能为空！");
throw new ServiceException(failureMsg.toString());
}else {
insertData.setName(getCellValue(row.getCell(0)));
}
insertData.setAge(getCellValue(row.getCell(1)));
this.insert(insertData);
successNum++;
successMsg.append("<br/>" + " 导入成功");
}
}
} catch (Exception e) {
failureNum++;
}
if (failureNum > 0) {
throw new ServiceException(failureMsg.toString());
} else {
successMsg.insert(0, "恭喜您，数据已全部导入成功！共 " + successNum + " 条，数据如下：");
}
return successMsg.toString();
}private String getCellValue(Cell cell){
String cellValue = "";
switch (cell.getCellType()){
case _NONE: // 未知类型，用于表示初始化前的状态或缺少具体类型。仅供内部使用。
break;
case NUMERIC: // 数字类型，用于Excel日期格式化
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date d = cell.getDateCellValue();
DateFormat df = new SimpleDateFormat("EEE MMM dd HH:mm:ss 'CST' yyyy", Locale.US);
DateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
try {
cellValue = sdf.format(df.parse(d.toString()));
} catch (ParseException e) {
e.printStackTrace();
}
} else {
cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
}
break;
case STRING: // 字符串类型
cellValue = cell.getStringCellValue();
break;
case FORMULA: // 公式类型
cellValue = String.valueOf(cell.getStringCellValue());
break;
case BLANK: // 空白的单元格
break;
case BOOLEAN: // 布尔类型
break;
case ERROR: // 错误类型
break;
default:
}
return cellValue;
}四、导出Excel
public void exportCodeExcel(HttpServletResponse response) {
try {
// 拿到需要导出的数据
List<TestData> testDataList = testDataMapper.getList();
InputStream inputStream = new ClassPathResource("/download.xlsx").getInputStream();
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
// 动态列 修改表头名 、修改模板数据等操作
// 填充excel数据
exportExcelData(workbook, testDataList);
// 输出流
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
// excel工作空间写入流
workbook.write(byteArrayOutputStream);
// 写到本地
String excelName = "导出";
OutputStream output = response.getOutputStream();
response.reset();
response.setHeader("Content-disposition", "attachment; filename=" +
new String(excelName.getBytes(), StandardCharsets.ISO_8859_1) + ".xlsx");
response.setContentType("application/msexcel");
workbook.write(output);
output.flush();
output.close();
workbook.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}private void exportExcelData(XSSFWorkbook workbook, List<TestData> testDataList) {
// 填充第一个sheet页内容
XSSFSheet sheetAt = workbook.getSheetAt(0);
/**
* XSSFSheet.shiftRows() 是 Apache POI 库中 XSSF（基于XML的Excel文件格式）工作表的 方法之一。它用于将工作表中的行进行移动。
* shiftRows() 方法具有以下参数：
* startRow：开始移动的行索引。
* endRow：结束移动的行索引。
* n：要移动的行数。正数表示向下移动，负数表示向上移动。
* copyRowHeight：是否复制移动的行的行高。
* resetOriginalRowHeight：是否将原始行的行高设置为默认值
* */
sheetAt.shiftRows(3 + 1, sheetAt.getLastRowNum(), testDataList.size(), true, false);
int rowNum = 3;
for (TestData dt : testDataList) {
// 从第四行开始添加每一列的数据
Row row = sheetAt.createRow(rowNum);
row.createCell(0).setCellValue(dt.getName());
row.createCell(1).setCellValue(dt.getAge());
row.createCell(2).setCellValue(dt.getIdCard());
row.createCell(3).setCellValue(dt.getSex());
row.createCell(4).setCellValue(dt.getPhone());
rowNum++;
}
}