关于java:Java后端实现Excel导入导出功能

实现后端 Excel 文件的导入导出代码，自定义列表实现导出

1、创立 excelutil 工具类

public class ExcelUtil {
private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
public static void createSheetHead(Sheet sheet, List<String> headList, CellStyle style) {

  Row row = sheet.createRow(0);
  for (int i = 0; i < headList.size(); i++) {String headStr = headList.get(i);
     Cell cell = row.getCell(i);
     cell = row.createCell(i);
     cell.setCellValue(headStr);
     cell.setCellStyle(style);
  }

}
public static void createRemark(Sheet sheet, String text, CellStyle style) {

  // 导出形容

int lastRowNum = sheet.getLastRowNum() + 1;

  Row row = sheet.createRow(lastRowNum);
  CellRangeAddress region = new CellRangeAddress(lastRowNum, lastRowNum, 0, 5);
  sheet.addMergedRegion(region);
  ExcelUtil.rowCreateCellData(row, 0, text, style);

}
public static void createRemark(Sheet sheet, String text) {

  // 导出形容

int lastRowNum = sheet.getLastRowNum() + 1;

  Row row = sheet.createRow(lastRowNum);
  CellRangeAddress region = new CellRangeAddress(lastRowNum, lastRowNum, 0, 5);
  sheet.addMergedRegion(region);
  ExcelUtil.rowCreateCellData(row, 0, text);

}
/**

• @param row 行队形

  • @param cellIndex 单元格索引
  • @param val 值
  • @param style 单元格格局

  */
  public static void rowCreateCellData(Row row, int cellIndex, String val, CellStyle style) {

   Cell cell = row.createCell(cellIndex);
   cell.setCellStyle(style);
   rowSetCellVal(row, cellIndex, val);

  }
  /**

  • 给行新增单元格并插入数据

  *

  • @param row 行队形
  • @param cellIndex 单元格索引
  • @param val 值

  */
  public static void rowCreateCellData(Row row, int cellIndex, String val) {

   Cell cell = row.createCell(cellIndex);
   rowSetCellVal(row, cellIndex, val);

  }
  /**

  • 给行新增单元格并插入数据

• @param row 行队形

  • @param cellIndex 单元格索引
  • @param val 值
  • @param style 单元格格局

  */
  public static void rowCreateCellData(Row row, int cellIndex, BigDecimal val, CellStyle style) {

   Cell cell = row.createCell(cellIndex);
   cell.setCellStyle(style);
   rowSetCellVal(row, cellIndex, val);

  }
  /**

  • 给行新增单元格并插入数据

  *

  • @param row 行队形
  • @param cellIndex 单元格索引
  • @param val 值

  */
  public static void rowCreateCellData(Row row, int cellIndex, BigDecimal val) {

   Cell cell = row.createCell(cellIndex);
   rowSetCellVal(row, cellIndex, val);

  }
  /**

  • 给行新增单元格并插入数据

  *

  • @param row 行队形
  • @param cellIndex 单元格索引
  • @param val 值
  • @param style 单元格格局

  */
  public static void rowCreateCellData(Row row, int cellIndex, Double val, CellStyle style) {

   Cell cell = row.createCell(cellIndex);
   cell.setCellStyle(style);
   rowSetCellVal(row, cellIndex, val);

  }
  /**

  • 给行新增单元格并插入数据

  *

  • @param row 行队形
  • @param cellIndex 单元格索引
  • @param val 值

  */
  public static void rowCreateCellData(Row row, int cellIndex, Double val) {

   Cell cell = row.createCell(cellIndex);
   rowSetCellVal(row, cellIndex, val);

  }
  /**

  • 给行新增单元格并插入数据

• @param row 行队形

  • @param cellIndex 单元格索引
  • @param val 值
  • @param style 单元格格局

  */
  public static void rowCreateCellData(Row row, int cellIndex, Integer val, CellStyle style) {

   Cell cell = row.createCell(cellIndex);
   cell.setCellStyle(style);
   rowSetCellVal(row, cellIndex, val);

  }
  /**

  • 给行新增单元格并插入数据

  *

  • @param row 行队形
  • @param cellIndex 单元格索引
  • @param val 值

  */
  public static void rowCreateCellData(Row row, int cellIndex, Integer val) {

   Cell cell = row.createCell(cellIndex);
   rowSetCellVal(row, cellIndex, val);

  }
  /**

  • 给行指定单元格 set 数据

• @param cellIndex 单元格索引

  • @param val 值

  */
  public static void rowSetCellVal(Row row, int cellIndex, String val) {

   Cell cell = row.getCell(cellIndex);
   cell.setCellType(Cell.CELL_TYPE_STRING);
   cell.setCellValue(val == null ? "" : StringUtils.substring(val, 0, 32766));

  }
  /**

  • 给行指定单元格 set 数据

• @param cellIndex 单元格索引

  • @param val 值

  */
  public static void rowSetCellVal(Row row, int cellIndex, Double val) {

   Cell cell = row.getCell(cellIndex);
   cell.setCellType(Cell.CELL_TYPE_NUMERIC);
   cell.setCellValue(val);

  }
  /**

  • 给行指定单元格 set 数据

  *

  • @param cellIndex 单元格索引
  • @param val 值

  */
  public static void rowSetCellVal(Row row, int cellIndex, BigDecimal val) {

   Cell cell = row.getCell(cellIndex);
   cell.setCellType(Cell.CELL_TYPE_NUMERIC);
   if (val != null) {cell.setCellValue(val.doubleValue());
   } else {cell.setCellValue(0.00D);
   }

  }
  /**

  • 给行指定单元格 set 数据

• @param cellIndex 单元格索引

  • @param val 值

  */
  public static void rowSetCellVal(Row row, int cellIndex, Integer val) {

   Cell cell = row.getCell(cellIndex);
   cell.setCellType(Cell.CELL_TYPE_NUMERIC);
   cell.setCellValue(val == null ? 0 : val);

  }
  public static <T> T getCellValue(Row row, int i, Class<T> classType) {

   Object value = null;
   Cell cell = row.getCell(i);
   if (cell == null) {return null;}
   if (cell.getCellType() == Cell.CELL_TYPE_STRING || cell.getCellType() == Cell.CELL_TYPE_BLANK) {value = cell.getStringCellValue();
   } else {if (String.class.getName().equals(classType.getName())) {cell.setCellType(CellType.STRING);
         value = cell.getStringCellValue();} else {value = cell.getNumericCellValue();
      }
   }
   if (value != null) {if (String.class.getName().equals(classType.getName())) {value = value.toString();
      } else if (Integer.class.getName().equals(classType.getName())) {if (Pattern.compile("^+{0,1}[1-9]d*").matcher(String.valueOf(value)).matches()) {value = Integer.valueOf(String.valueOf(value));
         } else {return null;}
      } else if (Double.class.getName().equals(classType.getName())) {if (Pattern.compile("[-+]{0,1}d+.d*|[-+]{0,1}d*.d+").matcher(String.valueOf(value)).matches()) {value = Double.valueOf(String.valueOf(value));
         }
         return null;
      } else if (BigDecimal.class.getName().equals(classType.getName())) {if (Pattern.compile("[-+]{0,1}d+.d*|[-+]{0,1}d*.d+").matcher(String.valueOf(value)).matches()) {value = new BigDecimal(String.valueOf(value));
         }
         return null;
      }
   }
   return (T) value;

  }

}

导出 excel（有三局部，须要定义 response 响应 VO）

思路：先查出选中的批量数据放进列表，再通过二维数组实现 excel 行列单元格.

（1）接口类：定义表头，表格数据列表遍历

@ResponseBody
@RequestMapping(“/exportToExcel.json”)
@SysLog(module = “ 商品模块 ”, operationDesc = “ 导出商品品牌信息 ”)
public void exportToExcel(HttpServletRequest request, HttpServletResponse response) {
try {

  String mallId = mallService.selectMallIdByCode(TenantAppCodeUtil.getInstance().getServerTenantAppCode());
  GoodsBrand brand = new GoodsBrand();
  brand.setMallId(mallId);
  List<GoodsBrand> list = goodsBrandService.selectList(brand);
  // excel 题目

String[] title = { “ 品牌名称 ()”, “ 简要形容 ”, “ 品牌形容 ”, “ 是否举荐 ”, “ 优先级 ()” };

  // excel 文件名

String fileName = “ 商品品牌 ” + System.currentTimeMillis() + “.xls”;

  // sheet 名

String sheetName = “ 商品品牌 ”;

  String[][] values = new String[list.size()][];
  if (list != null && list.size() > 0) {for (int i = 0; i < list.size(); i++) {values[i] = new String[title.length];
        GoodsBrand obj = list.get(i);
        values[i][0] = obj.getBrandName();
        values[i][1] = obj.getBrief();
        values[i][2] = obj.getContent();
        values[i][3] = obj.getIsCommend() == 1 ? "是" : "否";
        values[i][4] = obj.getPriority();}
  }
  outPutExcel(response, title, fileName, sheetName, values);

} catch (Exception e) {

  e.printStackTrace();
  logger.error(e.getMessage(), e);

}
}

（2）输入 Excel 办法: 文件门路、IO 操作（调用 excelutil 工具类办法）

private void outPutExcel(HttpServletResponse response, String[] title, String fileName, String sheetName, String[][] values) throws IOException {
// 创立 HSSFWorkbook
HSSFWorkbook wb = ExportExcelUtil.getHSSFWorkbook(sheetName, title, values, null);
// 将文件存到指定地位
this.setResponseHeader(response, fileName);
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
}

（3）发送响应流办法

public void setResponseHeader(HttpServletResponse response, String fileName) {
try {

  try {fileName = new String(fileName.getBytes(), "ISO8859-1");
  } catch (UnsupportedEncodingException e) {e.printStackTrace();
     logger.error(e.getMessage(), e);
  }
  response.setContentType("application/octet-stream;charset=ISO8859-1");
  response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
  response.addHeader("Pargam", "no-cache");
  response.addHeader("Cache-Control", "no-cache");

} catch (Exception ex) {

  ex.printStackTrace();
  logger.error(ex.getMessage(), ex);

}
}

导入 Excel（单步操作就行了）

思路：先读取文件的每一个单元格，读取进来一条一条写进数据库.

@RequestMapping(“/importExcel.json”)
@ResponseBody
@SysLog(module = “ 商品模块 ”, operationDesc = “ 导入商品品牌 ”)
public BaseResponseBody importExcel(@RequestParam(value = “file”) MultipartFile file, HttpServletRequest request) {
BaseResponseBody result = null;
try {

  String mallId = mallService.selectMallIdByCode(TenantAppCodeUtil.getInstance().getServerTenantAppCode());
  String fileName = file.getOriginalFilename();
  Workbook workbook = null;
  // 获取 excel 文件的 io 流

InputStream is = file.getInputStream();

  // 依据文件后缀名不同 (xls 和 xlsx) 取得不同的 Workbook 实现类对象

if (fileName.endsWith(“xls”)) {

     // 2003

workbook = new HSSFWorkbook(is);

  } else if (fileName.endsWith("xlsx")) {// 2007 及 2007 以上

workbook = new XSSFWorkbook(is);

  }
  // 工作表

Sheet sheet = workbook.getSheetAt(0);

  // 所有工作行

int lastRowNum = sheet.getLastRowNum();

  for (int i = 1; i <= lastRowNum; i++) {
     String brandName = null;
     String brief = null;
     String content = null;
     String isCommend = null;
     String priority = null;
     // 每一行

Row row = sheet.getRow(i);

     if (row == null) {break;}
     Cell cell0 = row.getCell(0);
     if (cell0 != null) {row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
        brandName = row.getCell(0).getStringCellValue();
        if ("".equals(brandName.trim())) {break;}
     }
     Cell cell1 = row.getCell(1);
     if (cell1 != null) {row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
        brief = row.getCell(1).getStringCellValue();}
     Cell cell2 = row.getCell(2);
     if (cell2 != null) {row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
        content = row.getCell(2).getStringCellValue();}
     Cell cell3 = row.getCell(3);
     if (cell3 != null) {row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
        isCommend = row.getCell(3).getStringCellValue();}
     Cell cell4 = row.getCell(4);
     if (cell4 != null) {row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
        priority = row.getCell(4).getStringCellValue();}
     if (StringUtils.isNotBlank(brandName) && StringUtils.isNotBlank(priority)) {GoodsBrand brand = new GoodsBrand();
        brand.setMallId(mallId);
        brand.setBrandName(brandName);
        List<GoodsBrand> brandList = goodsBrandService.selectList(brand);
        if (brandList != null && brandList.size() > 0) {return new BaseResponseBody(false, CommonResponseCode.ERROR.getCode(), this.getLocaleMessage(this.getClass(), GoodsResponseMsg.GOODS_BRAND_IMPORT_ERROR_BYSAMENAME.getMsg()), null);
        }
        brand.setBrief(brief);
        brand.setContent(content);
        brand.setStatus(1);
        brand.setIsCommend("否".equals(isCommend) ? 0 : 1);
        brand.setPriority(priority);
        goodsBrandService.insertSelective(brand);
     }
  }
  return new BaseResponseBody(true, CommonResponseCode.SUCCESS.getCode(), this.getLocaleMessage(this.getClass(), GoodsResponseMsg.GOODS_BRAND_IMPORT_SUCCESS.getMsg()), null);

} catch (Exception e) {

  logger.error(e.getMessage(), e);
  result = new BaseResponseBody(false, CommonResponseCode.ERROR.getCode(), this.getLocaleMessage(this.getClass(), GoodsResponseMsg.GOODS_BRAND_IMPORT_ERROR.getMsg()), e.getMessage(), null);

}
return result;
}