Excel操作

//poi  代码生成Excel模板并下载

private void downCreateExl(HttpServletRequest request, HttpServletResponse response) {
    try {
        SXSSFWorkbook wb = new SXSSFWorkbook();
        XSSFCellStyle style=(XSSFCellStyle)wb.createCellStyle();
        Color c=new Color(147,208,15);
        XSSFColor xssfColor=new XSSFColor(c);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFillForegroundColor(xssfColor);
        createSheet(wb,style);//创建sheet页及内容
        response.setContentType("application/x-xls");
        response.setHeader("Content-Disposition","attachment; filename=文件名.xlsx");
        OutputStream outputStream= null;
        outputStream = response.getOutputStream();
        wb.write(outputStream);
        outputStream.flush();
        outputStream.close();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

//创建sheet

private void createSheet(SXSSFWorkbook wb,XSSFCellStyle style) {

    //每行设多列内容，列数和表头相等
    Sheet sheet_content=wb.createSheet("填写信息内容");
    Row head_row=sheet_content.createRow(0);
　　String modelHead = "自定义表头字段并按逗号拼接";
    if(StringUtils.isNotBlank(modelHead)) {
        String[] heads = modelHead.split(",",-1);
        for (int i=0;i<heads.length;i++) {
            sheet_content.setColumnWidth(i,4000);
            Cell cell=head_row.createCell(i);
            cell.setCellValue(heads[i]);
            cell.setCellStyle(style);
        }
    }

    //每行设一列内容 例：住房形式参照
    Sheet sheet_house = wb.createSheet("住房形式参照");
    Row house_row0 = sheet_house.createRow(0);
    Cell cell0 = house_row0.createCell(0);
    cell0.setCellValue("住房形式对照:");
    Row house_row1 = sheet_house.createRow(1);
    Cell cell1 = house_row1.createCell(0);
    cell1.setCellValue("T:租房");
    Row house_row2 = sheet_house.createRow(2);
    Cell cell2 = house_row2.createCell(0);
    cell2.setCellValue("M:房屋抵押");
    Row house_row3 = sheet_house.createRow(3);
    Cell cell3 = house_row3.createCell(0);
    cell3.setCellValue("O:自有住房");
    Row house_row4 = sheet_house.createRow(4);
    Cell cell4 = house_row4.createCell(0);
    cell4.setCellValue("N:无固定住所");
    Row house_row5 = sheet_house.createRow(5);
    Cell cell5 = house_row5.createCell(0);
    cell5.setCellValue("other:其他");
}

// 读取本地模板文件并下载

public void downExlForloacal(HttpServletResponse response) {
    response.setCharacterEncoding("utf-8");
    response.setContentType("multipart/form-data");
    response.setHeader("Content-Disposition", "attachment;fileName=文件名.xlsx");
    try {
        /*String path = Thread.currentThread().getContextClassLoader()
                .getResource("").getPath()
                + "download";//这个download目录为啥建立在classes下的
        */
        InputStream inputStream = new FileInputStream(new File("D:\文件名.xlsx"));

        OutputStream os = response.getOutputStream();
        byte[] b = new byte[2048];
        int length;
        while ((length = inputStream.read(b)) > 0) {
            os.write(b, 0, length);
        }
        // 这里主要关闭。
        os.close();
        inputStream.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

// POI 上传Excel

　

/**
 * 上传Excel
 * @author chenyafeng  
 * @date 2018/5/18 
　* service : 已经初始化的service实例
 */
@PostMapping("uploadExcel")
@ResponseBody
public void uploadExcel(@RequestBody MultipartFile file) {
    try {
        service.uploadExcel(file.getInputStream());
    } catch (IOException e) {
        e.getMessage();
    }
}

service:

public void uploadExcel(InputStream inputStream) {
    try{
        XSSFWorkbook wb = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = wb.getSheetAt(0);//获取内容页面
        if (sheet == null) {
            return ;
        }
        int totalRow=sheet.getLastRowNum();//行数
        if (totalRow <= 0) return Result.newError().withMsg("内容为空");//只有表头行
        for(int i=1;i<=totalRow;i++) {//跳过第一行表头
            XSSFRow row=sheet.getRow(i);
            if(row == null){
                continue;
            }
            int totalColumn = row.getLastCellNum();//获得该行列数
            for(int j=0;j<totalColumn;j++){
                XSSFCell cell=row.getCell(j);
                if(cell==null){
                    //相关操作
                }else{
                    int cellType=cell.getCellType();
                    switch(cellType){
                        case Cell.CELL_TYPE_STRING:
                            //相关操作 cell.getStringCellValue();获取单元格的值
                            break;
                        case Cell.CELL_TYPE_BLANK:
                            //相关操作
                            break;
                        case Cell.CELL_TYPE_ERROR:
                            //相关操作
                            break;
                        default:
                            //相关操作，这里 只要不是 String 类型的值，即根据cell.getRawValue() 按字符串方式获取单元格的值，若需要其他类型可根据 cell.type获取相应类型的值
　　　　　　　　　　　　　　　　//如： cell.getBooleanCellValue(),cell.getNumericCellValue();
                            break;
                    }
                }
                
            }
            
    } catch (IOException e) {
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

//下载文件名中文为下划线，是由于浏览器的默认编码不同所引起的，解决方法如下:

String fileName = "文件名";
if(SessionManager.getRequest().getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0){
    fileName = new String(fileName.getBytes("GBK"),"ISO8859-1");
}else{
    fileName = new String(fileName.getBytes("UTF8"), "ISO8859-1");
}