Java实用工具类：Excel的导入和导出

一：将Excel表格中的数据导入到数据库中

1、实体类创建

@Data
public class ImportProduct {

    //商家id
    private String id;
    //商家名称
    private String businessName;
    //概述
    private String profile;
    //价格
    private BigDecimal productPrice;
    //标签
    private String keyWordId;
    //创建人
    private String createAt;
    //创建时间
    private Date createTime;
}

2、controller层调用

对要导入的excel进行格式和非空的判断

public HttpResult importProduct(@RequestParam("file") MultipartFile file){
    String fileName = file.getOriginalFilename();
    try {
        List<String> content = ExcelHelper.exportListFromExcel(file.getInputStream(),file.getOriginalFilename(),0);
        if (fileName.endsWith("xls") == false && fileName.endsWith("xlsx") == false){
            return HttpResult.error(HttpStatus.SC_INTERNAL_SERVER_ERROR,"不是Excel格式的文件");
        }else if(null == content || content.isEmpty()){
            return HttpResult.error(HttpStatus.SC_INTERNAL_SERVER_ERROR,"Excel为空");
        }else{
            return HttpResult.ok(importProductService.importProduct(file));
        }
    } catch (IOException e) {
        return HttpResult.error(HttpStatus.SC_INTERNAL_SERVER_ERROR,"接口异常");
    }
}

3、实现类

@Override
public int importProduct(MultipartFile file) {
    String fileName = file.getOriginalFilename();
    SnowFlake snowFlake = new SnowFlake(1, 1);
    Workbook workbook = null;
    int rows = 0;
    //判断excel的版本
    try {
        if (fileName.endsWith("xls")) {
            workbook = new HSSFWorkbook(file.getInputStream());
        } else {
            workbook = new XSSFWorkbook(file.getInputStream());
        }
        Sheet sheet = workbook.getSheet("sheet1");

        rows = sheet.getLastRowNum();
        for (int i = 1; i < rows + 1; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                ImportProduct product = new ImportProduct();
                //商家id
                //ImportExcelUtils是一个工具类，会在最后贴出来
                String id = ImportExcelUtils.getCellValue(row.getCell(0));
                if (StringUtils.isBlank(id)){
                    product.setId(Long.toString(snowFlake.nextId()));
                }else{
                    product.setId(id);
                }

                //商家名称
                String businessName = ImportExcelUtils.getCellValue(row.getCell(1));

                product.setBusinessName(businessName);

                //概述
                String profile = ImportExcelUtils.getCellValue(row.getCell(2));

                product.setProfile(profile);

                //价格
                String productPrice = ImportExcelUtils.getCellValue(row.getCell(3));

                if (!StringUtils.isBlank(productPrice)){

                    BigDecimal price = new BigDecimal(productPrice);

                    product.setProductPrice(price);

                }
                //标签
                String keyWordId = ImportExcelUtils.getCellValue(row.getCell(4));

                product.setKeyWordId(keyWordId);

                product.setCreateAt(UserThreadLocal.get());

                product.setCreateTime(new Date());
		//导入到库中
                importProductMapper.insert(product);
            }
        }
    } catch (IOException e) {
        rows = 0;
        e.printStackTrace();
    }
    return rows;
}

4、工具类

public class ImportExcelUtils {
    public static  String getCellValue(Cell cell){
        String value = "";
        if (null != cell){
            switch (cell.getCellType()){
                case HSSFCell.CELL_TYPE_BLANK:
                    value = "";
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    value = cell.getNumericCellValue()+"";
                    if (HSSFDateUtil.isCellDateFormatted(cell)){
                        Date date = cell.getDateCellValue();
                        if (null != date){
                            value = new SimpleDateFormat("yyyy-mm-dd").format(date);
                        }else{
                            value = "";
                        }
                    }else {
                        value = new DecimalFormat("0").format(cell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    value = cell.getBooleanCellValue()+"";
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    value = cell.getCellFormula()+"";
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    value = "非法字符";
                    break;
                default:
                    value = "未知类型";
                    break;
            }
        }
        return value.trim();
    }
}

二、将数据导出到Excel表格中

1、实体类和导入的实体类一样

2、controller层调用

public HttpResult exportProduct(HttpServletResponse response, ImportProduct product){
    return HttpResult.ok(exportProductService.exportExcel(response,product));
}

3、实现类

@Override
public int exportExcel(HttpServletResponse response, ImportProduct product) {
    //创建webBook，对应一个Excel文件
    HSSFWorkbook workbook = new HSSFWorkbook();
    //添加一个sheet
    HSSFSheet sheet = workbook.createSheet("sheet1");
    //添加表头第0行
    HSSFRow row = sheet.createRow(0);
    //创建单元格格式，设置表头，格式为居中
    HSSFCellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    //创建单元格、表中字段设置
    //第一列
    HSSFCell cell = row.createCell(0);
    cell.setCellValue("创建时间");
    cell.setCellStyle(cellStyle);
    //第二列
    cell = row.createCell(1);
    cell.setCellValue("商家名称");
    cell.setCellStyle(cellStyle);
    //第三列
    cell = row.createCell(2);
    cell.setCellValue("概述");
    cell.setCellStyle(cellStyle);

    //第四列
    cell = row.createCell(3);
    cell.setCellValue("价格");
    cell.setCellStyle(cellStyle);


    //第五列
    cell = row.createCell(4);
    cell.setCellValue("标签");
    cell.setCellStyle(cellStyle);

    //第六列
    cell = row.createCell(5);
    cell.setCellValue("创建人");
    cell.setCellStyle(cellStyle);


    //第七列
    cell = row.createCell(6);
    cell.setCellValue("id");
    cell.setCellStyle(cellStyle);

    //取出数据
    List<ExportProductEntity> list = exportProductMapper.selectData(product);
    //循环取出值
    for (int i = 0;i <list.size();i++){
        row = sheet.createRow(i);
        ImportProduct importProduct = list.get(i);
        //创建单元格并设置值
        cell = row.createCell(0);
        cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(customerSearchEntity.getCreateTime()));
        row.createCell(1).setCellValue(importProduct.getBusinessName());
        row.createCell(2).setCellValue(importProduct.getProfile());
        row.createCell(3).setCellValue(importProduct.getProductPrice());
        row.createCell(4).setCellValue(importProduct.getKeyWordId());
        row.createCell(5).setCellValue(importProduct.getCreateAt());
        row.createCell(6).setCellValue(importProduct.getId());

    }
    //输出Excel文件

    try {
       /* OutputStream outputStream = response.getOutputStream();
        response.reset();
        Long filename = System.currentTimeMillis();*/
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
        String fileName = dateFormat.format(new Date());
        FileOutputStream fileOutputStream = new FileOutputStream("C:\\WXWork\\product"+fileName+".xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return list.size();
}