报表技术1-POI导入导出

Excel说明

在企业级应用开发中，Excel报表是一种最常见的报表需求。Excel报表开发一般分为两种形式：

• 为了方便操作，基于Excel的报表批量上传数据，也就是把Excel中的数据导入到系统中。
• 通过java代码生成Excel报表。也就是把系统中的数据导出到Excel中，方便查阅。

Excel的两种版本

• Excel2003 是一个特有的二进制格式，其核心结构是复合文档类型的结构，存储数据量较小；
• Excel2007 的核心结构是 XML 类型的结构，采用的是基于 XML 的压缩方式，使其占用的空间更小，操作效率更高。

常见的Excel操作工具

JXL(很少使用)

JXL只能对Excel进行操作,属于比较老的框架，它只支持到Excel 95-2000的版本。现在已经停止更新和维护,所以本课程中只时简单地演示一下jxl的代码，不会把它作为重点。
主要语法：
1、 创建可写入的Excel工作薄

WritableWorkbook workbook= Workbook.createWorkbook(输出流);

2、创建工作表

WritableSheet sheet= workbook.createSheet(工作表的名称, 工作表的索引值);

3、创建单元格

添加文本类单元格

Label labelC = new Label(列索引值, 行索引值, “单元格中的内容”);
sheet.addCell(labelC);

4、写入到文件

workbook.write();// 写入数据

5、释放资源：

workbook.close();// 关闭文件

导出一些个简单的excel(很少使用)

public void downLoadXlsByJxl(HttpServletResponse response){
        try {
           //创建一个工作薄
            ServletOutputStream outputStream = response.getOutputStream();
            WritableWorkbook workbook = Workbook.createWorkbook(outputStream);
           //创建一个工作表
            WritableSheet sheet = workbook.createSheet("一个JXL入门", 0);
           //设置列宽
            sheet.setColumnView(0,5);
            sheet.setColumnView(1,8);
            sheet.setColumnView(2,15);
            sheet.setColumnView(3,15);
            sheet.setColumnView(4,30);
            //处理标题
            String[] titles = new String[]{"编号","姓名","手机号","入职日期","现住址"};
            Label label = null;
            for (int i = 0; i < titles.length; i++) {
                label = new Label(i,0,titles[i]);
                sheet.addCell(label);
            }
            //处理导出的内容
            List<User> userList = this.findAll();
            int rowIndex = 1;
            for (User user : userList) {
                label = new Label(0,rowIndex,user.getId().toString());
                sheet.addCell(label);
                label = new Label(1,rowIndex,user.getUserName());
                sheet.addCell(label);
                label = new Label(2,rowIndex,user.getPhone());
                sheet.addCell(label);
                label = new Label(3,rowIndex,simpleDateFormat.format(user.getHireDate()));
                sheet.addCell(label);
                label = new Label(4,rowIndex,user.getAddress());
                sheet.addCell(label);
                rowIndex++;
            }
            
            //导出的文件名称
            String filename="一个JXL入门.xls";
            //设置文件的打开方式和mime类型
             response.setHeader( "Content-Disposition", "attachment;filename="  + new String(filename.getBytes(),"ISO8859-1"));
            response.setContentType("application/vnd.ms-excel"); 
           //导出
            workbook.write();
            //关闭资源
            workbook.close();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

结果如图：

POI(重点)

• POI是apache的项目,可对微软的Word,Excel,PPT进行操作,包括office2003和2007,Excle2003和2007。POI现在一直有更新。所以现在主流使用POI。
• Apache POI是Apache软件基金会的开源项目，由Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java语言操作Microsoft Office的功能。

API对象介绍：

工作簿：WorkBook	HSSFWordBook :2003版本，XSSFWorkBook :2007级以上
工作表 ： Sheet	HSSFSheet ：2003版本，XSSFSheet ：2007级以上
行 ：Row	HSSFRow ：2003版本，XSSFRow ：2007级以上
单元格： Cell	HSSFCell ：2003版本，XSSFCell ：2007级以上

POI操作Excel高低版本区别
只是类名不一样，方法都一样

对应excel名称	低版本中的类名	高版本中的类名
工作簿	HSSFWorkbook	XSSFWorkbook
工作表	HSSFSheet	XSSFSheet
行	HSSFRow	XSSFRow
单元格	HSSFCell	XSSFCell
单元格样式	HSSFCellStyle	XSSFCellStyle

1.添加依赖

处理低版本excel
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.1</version>
</dependency>

处理高版本excel
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.1</version>
</dependency>

处理百万数据，导入导出
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.0.1</version>
</dependency>

2.代码测试

操作低版本

public class POIDemo01 {
    public static void main(String[] args) throws Exception {
        //1.创建一个全新工作簿
        Workbook workbook = new HSSFWorkbook();
        //2.在工作簿中创建新的工作表
        Sheet sheet = workbook.createSheet("POI操作");
        //3.在工作表中创建行-行的脚标
        Row row = sheet.createRow(0);
        //4.在行中创建单元格-单元格脚标
        Cell cell = row.createCell(0);
        //5.在单元格写入内容
        cell.setCellValue("第一行第一列的单元格数据");
        workbook.write(new FileOutputStream(new File("D:/test.xls")));
        workbook.close();
    }
}

操作高版本

public class POIDemo02 {
    public static void main(String[] args) throws Exception{
        Workbook workbook = new XSSFWorkbook(); //创建了一个全新（里面什么都没有）的工作薄
        Sheet sheet = workbook.createSheet("demo测试");  //创建了一个全新（里面什么都没有）的工作表
        Row row = sheet.createRow(0);  //创建了第一行（空的）
        Cell cell = row.createCell(0);//创建的是第一行的第一个单元格
        cell.setCellValue("这是我第一次玩POI");
        //把工作薄输出到本地磁盘
        workbook.write(new FileOutputStream("d://test.xlsx"));
    }
}

结果：

数据导入

数据的导入就是读取excel中的内容，转成对象插入到数据库中

实现步骤：
1、根据上传的文件创建Workbook
2、获取到第一个sheet工作表
3、从第二行开始读取数据
4、读取每一个单元格，把内容放入到用户对象的相关的属性中

   public void uploadExcel(MultipartFile file) throws Exception {

        SimpleDateFormat simpleDateFormat = new  SimpleDateFormat("yyyy-MM-dd");

        //有内容的workbook
        Workbook workbook = new XSSFWorkbook(file.getInputStream());
        
        //获取到第一个工作表
        Sheet sheet = workbook.getSheetAt(0);
        
        //读取工作表中的内容
        //获取当前sheet的最后一行的索引值
        int lastRowIndex = sheet.getLastRowNum();
        
        Row row = null;
        
        //遍历读取工作表中的内容，数据类型
        for (int i = 1; i <= lastRowIndex; i++) {
            row = sheet.getRow(i);
            String username = row.getCell(0).getStringCellValue();
            String phone = null;
            try {
                phone = row.getCell(1).getStringCellValue()+"";
            } catch (Exception e) {
                e.printStackTrace();
            }
            String province = row.getCell(2).getStringCellValue();
            String city = row.getCell(3).getStringCellValue();
            
            //double类型转Integer
            Integer salary = ((Double) row.getCell(4).getNumericCellValue()).intValue();
            //日期格式转换
            Date hireDate = simpleDateFormat.parse(row.getCell(5).getStringCellValue());
            Date birthDay = simpleDateFormat.parse(row.getCell(6).getStringCellValue());
            String address = row.getCell(7).getStringCellValue();
            
            //封装用户信息
            User user = new User();
            user.setUserName(username);
            user.setBirthday(birthDay);
            user.setAddress(address);
            user.setCity(city);
            user.setHireDate(hireDate);
            user.setProvince(province);
            user.setSalary(salary);
            user.setPhone(phone);
            //执行插入方法
            //System.out.println(user.toString());
            userMapper.insert(user);
        }

    }

数据导出

1、创建一个全新的工作薄
2、在新的工作薄中创建一个新的工作表
3、在工作表创建第一行作为标题行，标题固定
4、从第二行循环遍历创建，有多少条用户数据就应该创建多少行
5、把每一个user对象的属性放入到相应的单元格中

public void downLoadXlsx(HttpServletResponse response) throws Exception {

    private SimpleDateFormat simpleDateFormat = new  SimpleDateFormat("yyyy-MM-dd");
    
         //1.创建一个全新的工作簿
        Workbook workbook = new XSSFWorkbook();
        
        //2.创建全新的工作表
        Sheet sheet = workbook.createSheet("用户数据");
        
        //调整列宽var1索引值, var2 1代表一个标准字母的1/256
        sheet.setColumnWidth(0,5*256);
        sheet.setColumnWidth(1,8*256);
        sheet.setColumnWidth(2,10*256);
        sheet.setColumnWidth(3,15*256);
        sheet.setColumnWidth(4,30*256);
        
        //3.处理固定的标题---编号，姓名，手机号，入职日期，现住址
        String[] title = new String[]{"编号","姓名","手机号","入职日期","现住址"};
        
        //标题行
        Row titleRow = sheet.createRow(0);
        Cell cell = null;
        for (int i = 0; i < 5; i++) {
            cell = titleRow.createCell(i);
            cell.setCellValue(title[i]);
        }
        
        //4.从第二行循环遍历，向单元格中放入数据
        List<User> list = userMapper.selectAll();
        int rowIndex = 1;
        Row row = null;
        
        for (User user : list) {
            row = sheet.createRow(rowIndex);
            cell = row.createCell(0);
            cell.setCellValue(user.getId());
            cell = row.createCell(1);
            cell.setCellValue(user.getUserName());
            cell = row.createCell(2);
            cell.setCellValue(user.getPhone());
            cell = row.createCell(3);
            cell.setCellValue(simpleDateFormat.format(user.getHireDate()));
            cell = row.createCell(4);
            cell.setCellValue(user.getBirthday());
            rowIndex++;
        }
        
        //一个流两个头
        String fileName = "员工数据.xlsx";
        response.setHeader("content-disposition","attachment;filename="+new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.ms-excel");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        //执行
        workbook.write(response.getOutputStream());
    }

结果：

导出带样式的数据(不建议)

      /**
     * POI导出用户数据---简单样式
     * @param response
     * @throws Exception
     */
    public void downLoadXlsxByPoiWithCellStyle(HttpServletResponse response) throws Exception {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();

        /**
         * 大标题行
         */
        Row bigTitleRow = sheet.createRow(0);

        //1.边框线(全边框)，行高(42)，合并单元格(第一行第一个到第五个单元格)，对齐方式(水平垂直居中)，字体：黑体18号
        CellStyle bigTitleRowCellStyle = workbook.createCellStyle();

        //上下左右边框
        bigTitleRowCellStyle.setBorderTop(BorderStyle.THIN);//BorderStyle.THIN细线
        bigTitleRowCellStyle.setBorderBottom(BorderStyle.THIN);
        bigTitleRowCellStyle.setBorderLeft(BorderStyle.THIN);
        bigTitleRowCellStyle.setBorderRight(BorderStyle.THIN);
        //对齐方式，水平居中对齐，垂直居中对齐
        bigTitleRowCellStyle.setAlignment(HorizontalAlignment.CENTER);
        bigTitleRowCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //设置行高
        bigTitleRow.setHeightInPoints(42);

        //设置列宽
        sheet.setColumnWidth(0,5*256);
        sheet.setColumnWidth(1,10*256);
        sheet.setColumnWidth(2,10*256);
        sheet.setColumnWidth(3,10*256);
        sheet.setColumnWidth(4,30*256);

        //字体
        //1.创建字体
        Font font = workbook.createFont();
        //2.设置字体样式
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 18);
        //3.字体放入样式中
        bigTitleRowCellStyle.setFont(font);

        //--5个单元格合并
        for (int i = 0; i < 5; i++) {
            Cell cell = bigTitleRow.createCell(i);
            cell.setCellStyle(bigTitleRowCellStyle);
        }
        //起始行，结束行，开始列，结束列
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,4));

        //向单元格中添加句子
        bigTitleRow.getCell(0).setCellValue("用户信息数据");
        //大标题行结束

        /**
         * 小标题行
         */
        Row littleTitleRow = sheet.createRow(1);

        //1.边框线(全边框)，行高(42)，合并单元格(第一行第一个到第五个单元格)，对齐方式(水平垂直居中)，字体：黑体18号
        CellStyle littleTitleRowCellStyle = workbook.createCellStyle();

        //克隆大标题的样式
        //上下左右边框
        //对齐方式，水平居中对齐，垂直居中对齐
        littleTitleRowCellStyle.cloneStyleFrom(bigTitleRowCellStyle);

        //设置列宽
        littleTitleRow.setHeightInPoints(32);
        //字体
        //1.创建字体
        Font littleFont = workbook.createFont();
        //2.设置字体样式
        littleFont.setFontName("宋体");
        littleFont.setFontHeightInPoints((short) 12);
        littleFont.setBold(true);
        //3.字体放入样式中
        littleTitleRowCellStyle.setFont(littleFont);

        //添加小标题内容
        String[] titles = new String[]{"编号","姓名","手机号","入职日期","现住址"};
        for (int i = 0; i < 5; i++) {
            Cell cell = littleTitleRow.createCell(i);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(littleTitleRowCellStyle);
        }
        //小标题行结束
        
        /**
         * 内容样式
         */
        CellStyle contentRowCellStyle = workbook.createCellStyle();
        contentRowCellStyle.cloneStyleFrom(bigTitleRowCellStyle);
        contentRowCellStyle.setAlignment(HorizontalAlignment.LEFT);
        Font contentFont = workbook.createFont();
        contentFont.setFontName("宋体");
        contentFont.setFontHeightInPoints((short) 11);
        contentFont.setBold(false);
        contentRowCellStyle.setFont(contentFont);

        Row contentRow = null;
        Cell cell = null;
        int contentRowIndex = 2;
        
        //添加用户数据
        List<User> users = userMapper.selectAll();
        for (User user : users) {
            contentRow = sheet.createRow(contentRowIndex);

            cell = contentRow.createCell(0);
            cell.setCellValue(user.getId());

            cell = contentRow.createCell(1);
            cell.setCellValue(user.getUserName());

            cell = contentRow.createCell(2);
            cell.setCellValue(user.getPhone());

            cell = contentRow.createCell(3);
            cell.setCellValue(simpleDateFormat.format(user.getHireDate()));

            cell = contentRow.createCell(4);
            cell.setCellValue(user.getAddress());

            cell.setCellStyle(contentRowCellStyle);
            contentRowIndex++;
        }
        /**
         * 一个流两个头
         */
        String fileName = "员工样式数据.xlsx";
        response.setHeader("content-disposition","attachment;filename="+new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.ms-excel");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook.write(response.getOutputStream());
    }

结果：

根据模板导出数据

    public void downLoadXlsxByPoiWithTemplate(HttpServletResponse response) throws Exception {
       
        //1.获取到模板
        //获取项目的根目录，创建目录
        File rootFile = new File(ResourceUtils.getURL("classpath:").getPath());
        File templateFile = new File(rootFile, "/excel_template/userList.xlsx");
        //创建有模板的工作簿
        Workbook workbook = new XSSFWorkbook(templateFile);
        Sheet sheet = workbook.getSheetAt(0);
       
        //2.查询所有的用户数据
        List<User> list = userMapper.selectAll();
       
        //3.放入到模板中
        int rowIndex = 2;
        Row row = null;
        Cell cell = null;
   
        //获取准备好的内容单元格样式，第二个sheet的第一个单元格的样式
        CellStyle cellStyle = workbook.getSheetAt(1).getRow(0).getCell(0).getCellStyle();
		//遍历结果
        for (User user : list) {
            row = sheet.createRow(rowIndex);
            row.setHeightInPoints(15);
            cell = row.createCell(0);
            cell.setCellValue(user.getId());
            cell = row.createCell(1);
            cell.setCellValue(user.getUserName());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(2);
            cell.setCellValue(user.getPhone());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(3);
            cell.setCellValue(simpleDateFormat.format(user.getHireDate()));
            cell.setCellStyle(cellStyle);
            cell = row.createCell(4);
            cell.setCellValue(user.getAddress());
            cell.setCellStyle(cellStyle);
            rowIndex++;
        }
        
        //删除第二个sheet
        workbook.removeSheetAt(1);
        
        //4.导出文件
        //一个流两个头
        String fileName = "员工模板数据.xlsx";
        response.setHeader("content-disposition","attachment;filename="+new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.ms-excel");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        //执行
        workbook.write(response.getOutputStream());
    }

模板样式：sheet1

sheet2

代码：https://gitee.com/suisui9857/user_management