java通过poi操作excel表格

java通过poi操作excel表

大学舍友找上我，让我帮忙做一个页面可以操作excel表格的页面，我也是第一次接触poi，还是度娘好用，通过几篇文章的阅读，基本可以对excel进行操作，完成他说的功能。还是老样子，初来驾到，写的不好请指教。接下来，盘他！！！

1、先看下效果

前端我是从网上随便找的模板，后台返回List<List>集合，前端通过ajax调用查询所有后循环将值复制给前端值中，效果就是这个样子，可能有点丑，但是不要在意这些细节。接下来我们就看下这个后台到低是怎么写的！

2、后台实现

所需依赖如下：

<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
            <version>1.5.10.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>


        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.1.1</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl -->
        <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.12</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

（1）查询所有

@RequestMapping("queryAll")
    @ResponseBody
    public List<List<String>> queryAll() throws IOException, BiffException {
        List<List<String>> allData = new ArrayList<List<String>>();
        File file = new File("C:/Users/Administrator/Desktop/TestFile.xls");
        //获取该文件的io输入流
        InputStream is = new FileInputStream(file.getAbsolutePath());
        //获取该文件的文档对象
        Workbook wb = Workbook.getWorkbook(is);
        //得到第一个工作簿对象
        Sheet sheet = wb.getSheet(0);
        // 得到所有的行数
        int max = sheet.getRows();
        for (int j = 1; j < max; j++) {
            List<String> oneData = new ArrayList<String>();
            // 得到每一行的单元格的数据
            Cell[] cells = sheet.getRow(j);
            for (int k = 0; k < cells.length; k++) {
            	//将一行数据存入一个list集合中
                oneData.add(cells[k].getContents().trim());
            }
            // 存储每一条数据
            allData.add(oneData);
        }
        return allData;
    }

（2）增加和编辑

我这边比较懒，编辑时我会传num=行数，增加时会传num=0，后面有对0进行判断。（这个行数包含了excel表格的表头描述每个类都是干什么的那行）

@RequestMapping("/save/{num}")
    @ResponseBody
    public String save(@RequestBody Product product, @PathVariable("num") Integer num) throws IOException, WriteException {

        //创建Excel文件，B库CD表文件
        File file = new File("C:/Users/Administrator/Desktop/TestFile.xls");
        Integer max = 0;
        try {
            List<List<String>> allData = readExcel(file, product, num);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }

private static List<List<String>> readExcel(File file, Product product, Integer num) throws Exception {

        // 创建输入流，读取Excel
        InputStream is = new FileInputStream(file.getAbsolutePath());
        // jxl提供的Workbook类
        Workbook wb = Workbook.getWorkbook(is);
        // 只有一个sheet,直接处理
        //创建一个Sheet对象
        Sheet sheet = wb.getSheet(0);
        // 得到所有的行数
        int max = sheet.getRows();
        if (max == 0) {
            max = 1;//表示如果是新的文件，那么从第一行开始写，0行是要写表头
        }
        WritableWorkbook workbook = null;
        Workbook rwb = Workbook.getWorkbook(file);
        workbook = Workbook.createWorkbook(file, rwb);
        WritableSheet sheetA = workbook.getSheet(0);
        Label labelA = null;
        String[] titleA = {"名称", "规格", "数量", "单价", "总价", "生产厂家", "下单时间", "到货时间", "已到数量", "备注"};
        for (int i = 0; i < titleA.length; i++) {
            labelA = new Label(i, 0, titleA[i]);
            //写表头
            sheetA.addCell(labelA);
        }
        //新文件增加时，传过来的num当然为0了，哪些肯定要在第一行max=1写数据。第0行要留给表头
        if (num == 0) {
            labelA = new Label(0, max, product.getName() + " ");
            sheetA.addCell(labelA);
            labelA = new Label(1, max, product.getGuige());
            sheetA.addCell(labelA);
            labelA = new Label(2, max, product.getNumber() + "");
            sheetA.addCell(labelA);
            labelA = new Label(3, max, product.getPrice());
            sheetA.addCell(labelA);
            labelA = new Label(4, max, product.getAllPrice());
            sheetA.addCell(labelA);
            labelA = new Label(5, max, product.getfProduct());
            sheetA.addCell(labelA);
            labelA = new Label(6, max, product.getStartDate());
            sheetA.addCell(labelA);
            labelA = new Label(7, max, product.getToDate());
            sheetA.addCell(labelA);
            labelA = new Label(8, max, product.getYidaoNum());
            sheetA.addCell(labelA);
            labelA = new Label(9, max, product.getDes());
            sheetA.addCell(labelA);
        } else {
        //num不为0时，说明是编辑，直接修改num那行的数据就好了
            labelA = new Label(0, num, product.getName() + " ");
            sheetA.addCell(labelA);
            labelA = new Label(1, num, product.getGuige());
            sheetA.addCell(labelA);
            labelA = new Label(2, num, product.getNumber() + "");
            sheetA.addCell(labelA);
            labelA = new Label(3, num, product.getPrice());
            sheetA.addCell(labelA);
            labelA = new Label(4, num, product.getAllPrice());
            sheetA.addCell(labelA);
            labelA = new Label(5, num, product.getfProduct());
            sheetA.addCell(labelA);
            labelA = new Label(7, num, product.getToDate());
            sheetA.addCell(labelA);
            labelA = new Label(8, num, product.getYidaoNum());
            sheetA.addCell(labelA);
            labelA = new Label(9, num, product.getDes());
            sheetA.addCell(labelA);
        }

        workbook.write();//写入数据        
        workbook.close(); //关闭连接
 }

（3）删除

@RequestMapping("delete/{romNum}")
@ResponseBody
public void delete(@PathVariable("romNum") Integer romNum) throws IOException, BiffException, WriteException {
        File file = new File("C:/Users/Administrator/Desktop/TestFile.xls");
        InputStream is = new FileInputStream(file.getAbsolutePath());
        WritableWorkbook workbook = null;
        Workbook rwb = Workbook.getWorkbook(file);
        //得到文档写的操作对象
        workbook = Workbook.createWorkbook(file, rwb);
        WritableSheet sheetA = workbook.getSheet(0);
        sheetA.removeRow(romNum);
        workbook.write();//写入数据        
        workbook.close(); //关闭连接
}

（4）编辑

编辑需要先查，后改，所以执行两个方法（和2中的增加方法）

@RequestMapping("queryByid/{romNum}")
    @ResponseBody
    public List<String> queryByid(@PathVariable("romNum") Integer romNum) throws IOException, BiffException {
        File file = new File("C:/Users/Administrator/Desktop/TestFile.xls");
        InputStream is = new FileInputStream(file.getAbsolutePath());
        Workbook wb = Workbook.getWorkbook(is);
        // 只有一个sheet,直接处理
        //创建一个Sheet对象
        Sheet sheet = wb.getSheet(0);
        Cell[] cells = sheet.getRow(romNum);
        List<String> list = new ArrayList<>();
        for (int k = 0; k < cells.length; k++) {
            list.add(cells[k].getContents().trim());
        }
        return list;
    }

欢迎大家留言交流和指导，感谢批评，有批评就有进步。