纳税服务系统【用户模块之使用POI导入excel、导出excel】

2023-07-29 14:09:52

前言

再次回到我们的用户模块上，我们发现还有两个功能没有完成：

对于将网页中的数据导入或导出到excel文件中，我们是完全没有学习过的。但是呢，在Java中操作excel是相对常用的，因此也有组件供我们使用

JAVA中操作Excel的有两种比较主流的工具包

JXL
POI

这次我们主要学习的是POI操作excel。

JXL有一个缺陷，只能操作03或以前版本的excel，而POI可以操作97-07版本的。

POI基础

面向对象看excel

首先，要用Java来操作excel的话，肯定用对象来指定excel相关的内容的。我们来看看excel由什么组成：

POI是这样看的：

整个excel称作为工作薄
工作薄下可以创建很多张表，称作为工作表
工作表有非常多的行
行又可细分单元格【指定行的列就可以定位到工作表任意的位置了】

给我们一顿分析以后，我们发现它们之间是有从属关系的：

工作表从属于工作薄
行从属于工作表
单元格从属于行

操作Excel步骤

导入POI开发包：

poi-ooxml-3.10.1-20140818.jar，
poi-ooxml-schemas-3.10.1-20140818.jar，
以及复制在ooxml-lib目录下的xmlbeans-2.6.0.jar，dom4j-1.6.1.jar【dom4j一般在项目导入的时候已经存在了】。
poi-ooxml-3.11-20141221.jar



/**

 * 使用POI1无非操作Excel无非就4个步骤：

 *

 * 创建/读取工作薄

 * 创建/读取工作表

 * 创建/读取行

 * 创建/读取单元格

 *

 *

 * */

创建Excel并写入数据



    @Test

    public void testWrite() throws IOException {

        //创建工作薄

        HSSFWorkbook workbook = new HSSFWorkbook();

        //创建工作表

        HSSFSheet sheet = workbook.createSheet("我是新的工作表");

        //创建行,坐标从0开始，我创建的是第三行

        HSSFRow row = sheet.createRow(2);

        //创建单元格，坐标也是从0开始，于是就是第三行第三列

        HSSFCell cell = row.createCell(2);

        //往单元格写数据

        cell.setCellValue("helloWorld");

        //把工作薄写到硬盘中

        FileOutputStream outputStream = new FileOutputStream("C:\\工作薄.xls");

        workbook.write(outputStream);

        //关闭流

        workbook.close();

        outputStream.close();

    }

读取Excel的数据



    @Test

    public void testRead() throws IOException {

        //获取输入流，读取Excel数据

        FileInputStream inputStream = new FileInputStream("C:\\工作薄.xls");

        //创建工作薄

        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);

        //得到工作表

        HSSFSheet sheet = workbook.getSheetAt(0);

        //得到行

        HSSFRow row = sheet.getRow(2);

        //得到单元格

        HSSFCell cell = row.getCell(2);

        //得到单元格的数据

        String cellValue = cell.getStringCellValue();

        System.out.println(cellValue);

    }

03与07版本

03版本使用的是HSSFWorkbook这么一个类来操作03的Excel数据
07版本使用的是XSSFWorkbook这么一个类来操作07的Excel数据

其实他们的方法都是一样的，仅仅是类的不同。而使用哪个对象，我们可以根据后缀名来判断创建哪个对象【是03还是07】



    @Test

    public void testRead03And07Excel() throws Exception {

        String fileName = "D:\\itcast\\测试.xlsx";

        if(fileName.matches("^.+\\.(?i)((xls)|(xlsx))$")){//判断是否excel文档

            boolean is03Excel = fileName.matches("^.+\\.(?i)(xls)$");

            FileInputStream inputStream = new FileInputStream(fileName);

            //1、读取工作簿

            Workbook workbook = is03Excel ?new HSSFWorkbook(inputStream):new XSSFWorkbook(inputStream);

            //2、读取第一个工作表

            Sheet sheet = workbook.getSheetAt(0);

            //3、读取行；读取第3行

            Row row = sheet.getRow(2);

            //4、读取单元格；读取第3行第3列

            Cell cell = row.getCell(2);

            System.out.println("第3行第3列单元格的内容为：" + cell.getStringCellValue());

            workbook.close();

            inputStream.close();

        }

    }

Excel样式

回到我们的需求中，当我们使用POI导出数据的时候，Excel应该要有样式才好看的。类似下面的模板：

在POI中可以利用格式化对象来格式化excel文档；也即设置excel内容的样式。

POI中主要的格式化对象常用的有：

合并单元格
设置单元格样式
- 设置单元格字体
- 居中
- 背景颜色等

POI的样式对象明显是属性工作薄的。应用于工作表

合并单元格

属于工作薄，应用于工作表

创建合并单元格对象的时候要给出4个参数，它们分别表示：

行的起始位置
行的结束位置
列的起始位置
列的结束位置



    @Test

    public void testCellRange() throws IOException {

        //创建工作薄

        HSSFWorkbook workbook = new HSSFWorkbook();

        //创建合并单元格对象,从第六行开始到第十行，从第六列开始，到第十列

        CellRangeAddress cellRangeAddress = new CellRangeAddress(5, 9, 5, 9);

        //创建工作表

        HSSFSheet sheet = workbook.createSheet("我是新的工作表");

        //应用于工作表

        sheet.addMergedRegion(cellRangeAddress);

        //创建行,坐标从0开始，我创建的是第六行

        HSSFRow row = sheet.createRow(5);

        //创建单元格，坐标也是从0开始，于是就是第六行第六列

        HSSFCell cell = row.createCell(5);

        //往单元格写数据

        cell.setCellValue("helloWorld");

        //把工作薄写到硬盘中

        FileOutputStream outputStream = new FileOutputStream("C:\\工作薄.xls");

        workbook.write(outputStream);

        //关闭流

        workbook.close();

        outputStream.close();

    }

设置单元格样式

上面的图我们可以发现，我们已经实现了合并单元格，但是一般我们都是将字体设置成居中、字体大小等等。POI也提供了相对应的对象给我们实现：

设置居中

样式属于工作薄，应用于单元格：

    @Test

    public void test() throws IOException {

        //创建工作薄

        HSSFWorkbook workbook = new HSSFWorkbook();

        //创建样式对象

        HSSFCellStyle style = workbook.createCellStyle();

        //创建合并单元格对象,从第六行开始到第十行，从第六列开始，到第十列

        CellRangeAddress cellRangeAddress = new CellRangeAddress(5, 9, 5, 9);

        //设置水平居中

        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        //设置垂直居中

        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        //创建工作表

        HSSFSheet sheet = workbook.createSheet("我是新的工作表");

        sheet.addMergedRegion(cellRangeAddress);

        //创建行,坐标从0开始，我创建的是第六行

        HSSFRow row = sheet.createRow(5);

        //创建单元格，坐标也是从0开始，于是就是第六行第六列

        HSSFCell cell = row.createCell(5);

        //往单元格写数据

        cell.setCellValue("helloWorld");

        //设置单元格的样式

        cell.setCellStyle(style);

        //把工作薄写到硬盘中

        FileOutputStream outputStream = new FileOutputStream("C:\\工作薄.xls");

        workbook.write(outputStream);

        //关闭流

        workbook.close();

        outputStream.close();

    }

设置字体

字体属于工作薄，应用于样式【和css是类似的】



    @Test

    public void test() throws IOException {

        //创建工作薄

        HSSFWorkbook workbook = new HSSFWorkbook();

        //创建样式对象

        HSSFCellStyle style = workbook.createCellStyle();

        //创建合并单元格对象,从第六行开始到第十行，从第六列开始，到第十列

        CellRangeAddress cellRangeAddress = new CellRangeAddress(5, 9, 5, 9);

        //设置水平居中

        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        //设置垂直居中

        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        //创建font对象

        HSSFFont font = workbook.createFont();

        //设置字体为粗体

        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        //字体为23字号

        font.setFontHeightInPoints((short) 23);

        //设置字体的颜色

        font.setColor(HSSFFont.COLOR_RED);

        //字体应用于样式

        style.setFont(font);

        //创建工作表

        HSSFSheet sheet = workbook.createSheet("我是新的工作表");

        sheet.addMergedRegion(cellRangeAddress);

        //创建行,坐标从0开始，我创建的是第六行

        HSSFRow row = sheet.createRow(5);

        //创建单元格，坐标也是从0开始，于是就是第六行第六列

        HSSFCell cell = row.createCell(5);

        //往单元格写数据

        cell.setCellValue("helloWorld");

        //设置单元格的样式

        cell.setCellStyle(style);

        //把工作薄写到硬盘中

        FileOutputStream outputStream = new FileOutputStream("C:\\工作薄.xls");

        workbook.write(outputStream);

        //关闭流

        workbook.close();

        outputStream.close();

    }

实现导出功能

绑定按钮事件，请求Action处理导出，打开一个输入框给用户下载

        function doExportExcel() {

            window.open("${basePath}user/user_exportExcel.action");

        }

Action处理

    /************导出Excel*************************/

    public void exportExcel() throws IOException {

        //查找出列表的全部数据

        List<User> list = userServiceImpl.findObjects();

        //导出其实就是让用户下载该Excel文件

        HttpServletResponse response = ServletActionContext.getResponse();

        //设置头和指定名称

        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("列表展示.xls", "UTF-8"));

        //指定返回的类容数据

        response.setContentType("application/x-execl");

        ServletOutputStream outputStream = response.getOutputStream();

        //给Service层做导出Excel操作

        userServiceImpl.exportExcel(list, outputStream);

    }

Service实现



    /**

     * 第一行写死，字体大小11，居中，粗体,合并单元格

     * 第二行写死，粗体

     * 第三行开始，是数据库列表的数据

     */

    @Override

    public void exportExcel(List<User> list, ServletOutputStream outputStream) {

        /***********创建工作薄---样式---字体--单元格*************/

        HSSFWorkbook workbook = new HSSFWorkbook();

        //第一行的合并单元格

        CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 4);

        //创建第一行样式【居中】

        HSSFCellStyle cellStyle = workbook.createCellStyle();

        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        //创建第二行样式【居中】

        HSSFCellStyle cellStyle2 = workbook.createCellStyle();

        cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        //创建第一行字体

        HSSFFont font = workbook.createFont();

        font.setFontHeightInPoints((short) 23);

        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        //创建第二行字体

        HSSFFont font2 = workbook.createFont();

        font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        //字体应用于样式

        cellStyle.setFont(font);

        cellStyle2.setFont(font2);

        /***********创建工作表*************/

        HSSFSheet sheet = workbook.createSheet("用户列表");

        //第一行单元格应用于工作表

        sheet.addMergedRegion(cellRangeAddress);

        //设置默认列宽

        sheet.setDefaultColumnWidth(25);

        /***********创建行*************/

        //第一行

        HSSFRow row = sheet.createRow(0);

        HSSFCell cell = row.createCell(0);

        cell.setCellStyle(cellStyle);

        cell.setCellValue("用户列表");

        //第二行数据也是写死的，我们用数组遍历即可

        String[] data = {"用户名","帐号", "所属部门", "性别", "电子邮箱"};

        HSSFRow row1 = sheet.createRow(1);

        for (int i = 0; i < data.length; i++) {

            HSSFCell cell1 = row1.createCell(i);

            cell1.setCellValue(data[i]);

            //加载第二行样式

            cell1.setCellStyle(cellStyle2);

        }

        /***************行和列在循环的时候，不要重复了。不然会报错的！！！！*****************/

        //第三行数据就是我们数据库保存的数据

        if (list != null) {

            int i=2;

            for (User user : list) {

                //从第三行开始

                HSSFRow row2 = sheet.createRow(i);

                HSSFCell row2Cel0 = row2.createCell(0);

                row2Cel0.setCellValue(user.getName());

                HSSFCell row2Cell = row2.createCell(1);

                row2Cell.setCellValue(user.getAccount());

                HSSFCell row2Cel2 = row2.createCell(2);

                row2Cel2.setCellValue(user.getDept());

                HSSFCell row2Cel3 = row2.createCell(3);

                row2Cel3.setCellValue(user.isGender() ? "男" : "女");

                HSSFCell row2Cel4 = row2.createCell(4);

                row2Cel4.setCellValue(user.getEmail());

                i++;

            }

        }

        try {

            //写到outputSteam上

            workbook.write(outputStream);

            workbook.close();

            outputStream.close();

        } catch (IOException e) {

            e.printStackTrace();

        }

    }

效果

优化一

我们来看下面这段代码，他们都要设置居中，字体就除了大小不同。其他都相同。却占用了这么多代码！！！



        //创建第一行样式【居中】

        HSSFCellStyle cellStyle = workbook.createCellStyle();

        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        //创建第二行样式【居中】

        HSSFCellStyle cellStyle2 = workbook.createCellStyle();

        cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        //创建第一行字体

        HSSFFont font = workbook.createFont();

        font.setFontHeightInPoints((short) 23);

        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        //创建第二行字体

        HSSFFont font2 = workbook.createFont();

        font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        //字体应用于样式

        cellStyle.setFont(font);

        cellStyle2.setFont(font2);

于是我就抽取成一个方法来得到样式

需要工作薄来创建样式
只有字体的大小是变化的



    /**

     * @param workbook 当前使用工作薄

     * @param fontSize 字体大小

     *

     * */

    public HSSFCellStyle createStyle(HSSFWorkbook workbook, short fontSize) {

        HSSFCellStyle cellStyle = workbook.createCellStyle();

        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        HSSFFont font = workbook.createFont();

        font.setFontHeightInPoints(fontSize);

        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        cellStyle.setFont(font);

        return cellStyle;

    }

当使用的时候，代码就变成了这样调用：

        HSSFCellStyle cellStyle = createStyle(workbook, (short) 24);

        HSSFCellStyle cellStyle2 = createStyle(workbook, (short) 13);

优化二

我们的Service业务层的代码看起来太多了。这样我们维护起来就不方便了。

我的做法是：把代码抽取成Utils的方法，Service层调用就好了。

实现导入功能

现在我有这么一个Excel文件，要把信息存储到数据库中，并且在浏览器显示出来

其实导入Excel就是文件上传，只不过不用把文件保存在服务器的硬盘数据中而是保存在数据库中，输出到浏览器就行了。

        function doImportExcel() {

            document.forms[0].action = "${basePath}user/user_importExcel.action";

            document.forms[0].submit();

        }

Action封装文件上传

/*************上传Excel************************/

    private File userExcel;

    private String userExcelFileName;

    private String userExcelContentType;

    public void setUserExcel(File userExcel) {

        this.userExcel = userExcel;

    }

    public void setUserExcelFileName(String userExcelFileName) {

        this.userExcelFileName = userExcelFileName;

    }

    public void setUserExcelContentType(String userExcelContentType) {

        this.userExcelContentType = userExcelContentType;

    }

Action处理

主要判断有没有上传文件。给Service层处理



    /************导入Excel*************************/

    public String importExcel() throws IOException {

        //1、获取excel文件

        if(userExcel != null){

            //是否是excel

            if(userExcelFileName.matches("^.+\\.(?i)((xls)|(xlsx))$")){

                //2、导入

                userServiceImpl.importExcel(userExcel, userExcelFileName);

            }

        }

        return "list";

    }

Utils封装成集合返回



 public static List<User> importExcel(File userExcel, String userExcelFileName) {

        try {

            FileInputStream fileInputStream = new FileInputStream(userExcel);

            boolean is03Excel = userExcelFileName.matches("^.+\\.(?i)(xls)$");

            //1、读取工作簿

            Workbook workbook = is03Excel ? new HSSFWorkbook(fileInputStream) : new XSSFWorkbook(fileInputStream);

            //2、读取工作表

            Sheet sheet = workbook.getSheetAt(0);

            //3、读取行

            List<User> users = new ArrayList<>();

            if (sheet.getPhysicalNumberOfRows() > 2) {

                User user = null;

                for (int k = 2; k < sheet.getPhysicalNumberOfRows(); k++) {

                    //4、读取单元格

                    Row row = sheet.getRow(k);

                    user = new User();

                    //用户名

                    Cell cell0 = row.getCell(0);

                    user.setName(cell0.getStringCellValue());

                    //帐号

                    Cell cell1 = row.getCell(1);

                    user.setAccount(cell1.getStringCellValue());

                    //所属部门

                    Cell cell2 = row.getCell(2);

                    user.setDept(cell2.getStringCellValue());

                    //性别

                    Cell cell3 = row.getCell(3);

                    user.setGender(cell3.getStringCellValue().equals("男"));

                    //手机号

                    String mobile = "";

                    Cell cell4 = row.getCell(4);

                    try {

                        mobile = cell4.getStringCellValue();

                    } catch (Exception e) {

                        double dMobile = cell4.getNumericCellValue();

                        mobile = BigDecimal.valueOf(dMobile).toString();

                    }

                    user.setMobile(mobile);

                    //电子邮箱

                    Cell cell5 = row.getCell(5);

                    user.setEmail(cell5.getStringCellValue());

                    //生日

                    Cell cell6 = row.getCell(6);

                    if (cell6.getDateCellValue() != null) {

                        user.setBirthday(cell6.getDateCellValue());

                    }

                    //默认用户密码为 123456

                    user.setPassword("123456");

                    //默认用户状态为 有效

                    user.setState(User.USER_STATE_VALID);

                    users.add(user);

                }

            }

            workbook.close();

            fileInputStream.close();

            return users;

        } catch (Exception e) {

            e.printStackTrace();

        }

        return null;

    }

Service调用



    public void importExcel(File userExcel, String userExcelFileName) {

        List<User> users = ExcelUtils.importExcel(userExcel, userExcelFileName);

        for (User user : users) {

            save(user);

        }

    }

码农公寓

前言

POI基础

面向对象看excel

操作Excel步骤

创建Excel并写入数据

读取Excel的数据

03与07版本

Excel样式

合并单元格

设置单元格样式

设置居中

设置字体

实现导出功能

Action处理

Service实现

效果

优化一

优化二

实现导入功能

Action封装文件上传

Action处理

Utils封装成集合返回

Service调用

相关文章