java操作excel

1. 生成并下载excel文件

controller

@RequestMapping(value = "/download", method = RequestMethod.GET)
    public xxx downloadFile(HttpServletResponse response) {
        response.setContentType("application/octet-stream");
        response.setHeader("content-type", "application/octet-stream");
        try {
            response.setHeader("Content-Disposition", "attachment;fileName=" + java.net.URLEncoder.encode(fileName, "UTF-8"));// 设置文件名
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        try {
            excelService.download(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

service

public interface ExcelService {
void download(OutputStream outputStream) throws IOException;
}

impl

@Override
    public void download(OutputStream outputStream) throws IOException{
        //1.创建excel(工作簿)
        Workbook wb = new XSSFWorkbook();
        //2.创建Sheet
        Sheet sheet = wb.createSheet();
        //3.创建表头,参数为要创建的行的位置
        Row header = sheet.createRow(0);
        //4.创建单元格对象,向单元格写数据
        Cell cell = null;
        List<String> headerList = Lists.newArrayList("姓名","年龄","性别","爱好","生日");
        for (int i = 0; i < headerList.size(); i++) {
            //行对象.createCell(单元格位置)
            cell = header.createCell(i);
            //赋值
            cell.setCellValue(headerList.get(i));
        }
        //5.工作簿写进输出流
        wb.write(outputStream);
    }

2. 解析前端上传的excel

Controller

@RequestMapping(value = "/excel/import", method = RequestMethod.GET)
    public xxx uploadExcel(@RequestParam("upload") MultipartFile file) {
        excelService.uploadExcel(file);
        return xxx.OK;
    }

service

public interface ExcelService {
    void uploadExcel(MultipartFile multipartFile);
}

impl

public void uploadExcel(MultipartFile multipartFile) {
    List<List> dataList = ExcelUtil.loadExcelData(multipartFile);
}

ExcelUtil

public static List<List> loadExcelData(MultipartFile file) throws Exception {
        Workbook wb;// 创建Excel2003文件对象
        wb = WorkbookFactory.create(file.getInputStream());
        //获取第一个页签对象
        Sheet sheet = wb.getSheetAt(0);
        //获取有内容的总行数
        int rowsNumber = sheet.getLastRowNum();
        if (rowsNumber >= 0) {
            List<List> excelList = new ArrayList<>(rowsNumber);
            //遍历页签的每一行
            Row firstRow = sheet.getRow(0);
            if (rowsNumber == 0 && firstRow == null) {
                return null;
            }
            //总列数
            int cellsNumber = sheet.getRow(0).getLastCellNum();

            for (int i = 0; i <= rowsNumber; i++) {
                Row row = sheet.getRow(i);// 获取行对象
                if (row == null) {// 如果为空，不处理
                    continue;
                }
                int num = 0;//记录该行的空格总数
                List<String> newList = new ArrayList<>(cellsNumber);
                for (int j = 0; j < cellsNumber; j++) {
                    Cell cell = row.getCell(j);// 获取单元格对象
                    if (cell != null) {
                        String value = getValueByType(cell);
                        if (StringUtils.isEmpty(value)) {
                            num += 1;
                        }
                        newList.add(value);
                    } else {
                        newList.add("");
                        num += 1;
                    }
                }
                if (num < cellsNumber) {//不是全部空格，把这行加到返回数据中
                    excelList.add(newList);
                }
            }
            return excelList;
        }
        wb.close();
        return null;
    }



private static String getValueByType(Cell cell) {
        //判断是否为null或空串
        if (cell==null || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue = "";
        int cellType=cell.getCellType();
        if(cellType==Cell.CELL_TYPE_FORMULA){ //表达式类型
            cellType=evaluator.evaluate(cell).getCellType();
        }
         
        switch (cellType) {
        case Cell.CELL_TYPE_STRING: //字符串类型
            cellValue= cell.getStringCellValue().trim();
            cellValue=StringUtils.isEmpty(cellValue) ? "" : cellValue; 
            break;
        case Cell.CELL_TYPE_BOOLEAN:  //布尔类型
            cellValue = String.valueOf(cell.getBooleanCellValue()); 
            break; 
        case Cell.CELL_TYPE_NUMERIC: //数值类型
             if (HSSFDateUtil.isCellDateFormatted(cell)) {  //判断日期类型
                 cellValue =    DateUtil.formatDateByFormat(cell.getDateCellValue(), "yyyy-MM-dd");
             } else {  //否
                 cellValue = new DecimalFormat("#.######").format(cell.getNumericCellValue()); 
             } 
            break;
        default: //其它类型，取空串吧
            cellValue = "";
            break;
        }
        return cellValue;
    }