Java操作Excel之Poi

    excel文档一般使用Apache的POI和JExcelAPI这两个工具来操作。这里我们使用POI实现读写excel文档。

<!-- 读取Excel表格包 -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.14</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.14</version>
    </dependency>

com.communication.vo：用于接收Excle中的数据

package com.communication.vo;

public class ExcelInfoVo {
    private int num;
    private String name;
    private String sex;

    public int getNum() {
        return num;
    }

    public void setNum(int num) {
        this.num = num;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    @Override
    public String toString() {
        return "ExcelInfoVo{" +
                "num=" + num +
                ", name='" + name + '\''+
                ",sex='" + sex + '\'' +
                "}";
    }

}

读取Excle功能实现

@RequestMapping("/readexcel")
    public void readExcel() throws IOException {
        List<ExcelInfoVo> list = importExcel("D:/t.xlsx",0);
        System.out.println(list);
    }
public List<ExcelInfoVo> importExcel(String file, int index) throws IOException {
        FileInputStream in = null;
        List<ExcelInfoVo> result = null;
        try {
            in = new FileInputStream(file);
            result = new ArrayList<ExcelInfoVo>();
            //创建07及之后版的工作簿
            Workbook wb = new XSSFWorkbook(in);
            /*创建07之前版本的工作簿
            Workbook wb = new HSSFWorkbook(in);*/
            //创建表单
            Sheet sheet = wb.getSheetAt(index);
            //遍历sheet里面的每一行
            for(Row row : sheet) {
                //第一行不是有效数据排除掉
                if(row.getRowNum() < 1) {
                    continue;
                }
                ExcelInfoVo eInfo  = new ExcelInfoVo();
                //这里要注意数据格式转换，Cell是单元格的意思，0,1,2代表的是第几列
                eInfo.setNum(new Double(row.getCell(0).getNumericCellValue()).intValue());
                /*
                   Java中将double转型为int有2个方法。
                   1.
                   double   d   =   12.0;  
                   int   i   =   (new Double(d)).intValue();
                   2.
                   double   d   =   12.0;  
                   int   i   =   (int)d;
                   不要使用(int)来强制转换 （原因待查）
                */
                eInfo.setName(row.getCell(1).getStringCellValue());
                eInfo.setSex(row.getCell(2).getStringCellValue());
                result.add(eInfo);
            } 
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            in.close();
        }
        return result;
    }

二、写入Excle

@RequestMapping("/writeExcel")
    public void writeExcel() {
        OutputStream fos = null;
        //1.创建工作簿
        Workbook wb = new XSSFWorkbook();
        try {
            //2.创建sheet，并设置列宽
            Sheet sheet = wb.createSheet("名单");
            sheet.setColumnWidth(0, 18*256);
            sheet.setColumnWidth(1, 18*256);
            sheet.setColumnWidth(2, 18*256);
            //创建Row为单元格赋值
            Row row = sheet.createRow(0);
            row.createCell(0).setCellValue(0);;
            row.createCell(1).setCellValue("陈茵");
            row.createCell(2).setCellValue("女");
            //将工作簿里的内容写入Excle文件中
            fos = new FileOutputStream("D:/1.xlsx");
            wb.write(fos);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                fos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }           
        }
    }
}