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

谁践踏了优雅 2021-09-17 06:50 360阅读 0赞

# 前言 #

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

![这里写图片描述][SouthEast]

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

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

*  JXL
 *  **POI**

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

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

# POI基础 #

## 面向对象看excel ##

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

![这里写图片描述][SouthEast 1]

POI是这样看的：

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

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

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

![这里写图片描述][SouthEast 2]

![这里写图片描述][SouthEast 3]

--------------------

## 操作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();
    
        }

![这里写图片描述][SouthEast 4]

![这里写图片描述][SouthEast 5]

--------------------

## 读取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);
    
        }

![这里写图片描述][SouthEast 6]

![这里写图片描述][SouthEast 7]

--------------------

## 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应该要有样式才好看的**。类似下面的模板：

![这里写图片描述][SouthEast 8]

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

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

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

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

![这里写图片描述][SouthEast 9]

--------------------

## 合并单元格 ##

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

创建合并单元格对象的时候要给出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();
    
        }

![这里写图片描述][SouthEast 10]

## 设置单元格样式 ##

上面的图我们可以发现，我们已经实现了合并单元格，但是一般我们都是将**字体设置成居中、字体大小等等**。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();
    
        }

![这里写图片描述][SouthEast 11]

### **设置字体** ###

**字体属于工作薄，应用于样式【和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();
    
        }

![这里写图片描述][SouthEast 12]

--------------------

# 实现导出功能 #

**绑定按钮事件，请求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();
            }
    
    
        }

## 效果 ##

![这里写图片描述][SouthEast 13]

--------------------

## 优化一 ##

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

//创建第一行样式【居中】
            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层调用就好了。

![这里写图片描述][SouthEast 14]

--------------------

# 实现导入功能 #

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

![这里写图片描述][SouthEast 15]

**其实导入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);
            }
        }

--------------------

[SouthEast]: /images/20210827/85d8834acfef42c4b9a72796aedca418.png
[SouthEast 1]: /images/20210827/da19c51086bc4465a06a7291fa2bcd51.png
[SouthEast 2]: /images/20210827/97ae4a45cded4b5cbdd710171c882a37.png
[SouthEast 3]: /images/20210827/4326dc575b5547b79bd0075eeaf7ff8e.png
[SouthEast 4]: /images/20210827/98513b3b347d4aaf8f0aa1f385d459cc.png
[SouthEast 5]: /images/20210827/53e5247533de4010a4e301ec17ecef4d.png
[SouthEast 6]: /images/20210827/6d15e6d5bc824721bb208ff5da943c28.png
[SouthEast 7]: /images/20210827/a7e07dbdc737482980f604ce88c2e05f.png
[SouthEast 8]: /images/20210827/6a1b7b449be148a6b2adf2c0653772f0.png
[SouthEast 9]: /images/20210827/af38e97d6a084ee49da0c2f0eed991b6.png
[SouthEast 10]: /images/20210827/5b581d893f854895988d292478943cbf.png
[SouthEast 11]: /images/20210827/9a8b3e258c124f418e6ba513c24637c0.png
[SouthEast 12]: /images/20210827/b200a2a711f64e7cb94d667a079f55c7.png
[SouthEast 13]: /images/20210827/c84211d62bdb40b38363befb383941c5.png
[SouthEast 14]: /images/20210827/570e750b0f2643dabd64c62eeb88691a.png
[SouthEast 15]: /images/20210827/17022ed69d2d42ceb4d327ed6f27633b.png