实现后端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")) {
// 2003workbook = 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;
}