2.excel导出-poi使用-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_44739500/article/details/109473528

POI

1 POI概述

1 什么是poi

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

2 包结构说明

HSSF提供读写Microsoft Excel XLS格式档案的功能。
XSSF提供读写Microsoft Excel OOXML XLSX格式档案的功能。
HWPF提供读写Microsoft Word DOC格式档案的功能。
HSLF提供读写Microsoft PowerPoint格式档案的功能。
HDGF提供读Microsoft Visio格式档案的功能。
HPBF提供读Microsoft Publisher格式档案的功能。
HSMF提供读Microsoft Outlook格式档案的功能。

2 POI的使用

2.1 Excel的操作

2.2.1 jar包依赖

导入jar包–本次使用maven，所以导入pom依赖。

<dependencies>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.8</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.8</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>3.8</version>
    </dependency>
    <dependency>
        <groupId>org.apache.xmlbeans</groupId>
        <artifactId>xmlbeans</artifactId>
        <version>3.1.0</version>
    </dependency>
    <dependency>
        <groupId>dom4j</groupId>
        <artifactId>dom4j</artifactId>
        <version>1.6.1</version>
    </dependency>
    <dependency>
        <groupId>poi</groupId>
        <artifactId>poi-scratchpad-2.5.1-final</artifactId>
        <version>20040804</version>
    </dependency>
</dependencies>

2.2.2 Excel文件的导出

public static void writerExcel() throws IOException {
    ArrayList<Student> students = new ArrayList<>();
    for (int i = 0; i < 5; i++) {
        students.add(new Student(i+1000,"张三"+i,"男",60+Float.parseFloat(""+Math.random()*40)));
    }
    //1.创建excel对象
    HSSFWorkbook sheets = new HSSFWorkbook();
    //2.创建一个sheet
    HSSFSheet sheet1 = sheets.createSheet("sheet1");
    //4.创建行列并赋值：下标默认从0开始
    HSSFRow row = sheet1.createRow(0);
    //3.设置样式
    HSSFCellStyle style = sheets.createCellStyle();
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);//设置图案颜色
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//设置图案样式
    String[] rows=new String[]{"序号","学号","名字","性别","成绩"};
    for (int i = 0; i < rows.length; i++) {
        //HSSFCell cell = row.createCell(i);
        Cell cell = row.createCell(i);
        cell.setCellValue(rows[i]);
        cell.setCellStyle(style);
        System.out.println(cell.getCellStyle().getFillBackgroundColor());
    }
    for (int i = 0; i < students.size(); i++) {
        HSSFRow tempRow = sheet1.createRow(i+1);
        Student tempStudent = students.get(i);
        HSSFCell cell = tempRow.createCell(0);
        cell.setCellValue(i+1);
        tempRow.createCell(1).setCellValue(tempStudent.getId());
        tempRow.createCell(2).setCellValue(tempStudent.getName());
        tempRow.createCell(3).setCellValue(tempStudent.getSex());
        tempRow.createCell(4).setCellValue(tempStudent.getScore());
    }
    //5.创建关联输出流和文件
    FileOutputStream fileOutputStream = new FileOutputStream("14_poi/studentScoreBook.xls");
    sheets.write(fileOutputStream);
    fileOutputStream.close();
}

2.2.3 EXCEL文件的读

public static void readExcel() throws IOException {
    //创建文件输入流关联表
    FileInputStream fileInputStream = new FileInputStream("14_poi/studentScoreBook.xls");
    //1.创建工作簿
    HSSFWorkbook sheets = new HSSFWorkbook(fileInputStream);
    //2.获取工作表
    int numberOfSheets = sheets.getNumberOfSheets();
    //3.获取每一个单元格
    for (int i = 0; i < numberOfSheets; i++) {
        HSSFSheet sheetAt = sheets.getSheetAt(i);
        Iterator<Row> rowIterator = sheetAt.iterator();
        while (rowIterator.hasNext()){
            //获取一行
            Row rowNext = rowIterator.next();
            Iterator<Cell> cellIterator = rowNext.cellIterator();
            while (cellIterator.hasNext()){
                //获取每一个单元格
                Cell cellNext = cellIterator.next();
                int cellType = cellNext.getCellType();
                if (cellType== HSSFCell.CELL_TYPE_NUMERIC){
                    //日期和数字类型的
                    if (HSSFDateUtil.isCellDateFormatted(cellNext)){
                        Date dateCellValue = cellNext.getDateCellValue();
                        System.out.println(dateCellValue);
                    }else {
                        double numericCellValue = cellNext.getNumericCellValue();
                        System.out.println("numeric：："+numericCellValue);
                    }
                }else if (cellType==HSSFCell.CELL_TYPE_BOOLEAN){
                    boolean booleanCellValue = cellNext.getBooleanCellValue();
                    System.out.println("boolean：："+booleanCellValue);
                }else{
                    String stringCellValue = cellNext.getStringCellValue();
                    System.out.println("其他类型使用字符串进行接收：："+stringCellValue);
                }
            }
        }
        fileInputStream.close();
    }
}

2.2.4 文件的上传与下载

需求：上传一个文件并保存到本地磁盘中去。

@RequestMapping("/extractAllStudentToExcel")
public ResponseEntity<byte[]> extractAllStudentToExcel(HttpServletRequest request) {
    //1.获取全部的学生对象
    List<Student> allStudent = studentDao.getAllStudent();
    allStudent.forEach(System.out::println);
    //2.创建工作表
    HSSFWorkbook sheets = new HSSFWorkbook();
    HSSFSheet sheet = sheets.createSheet("学生信息表");
    //4.创建行列并赋值：下标默认从0开始
    HSSFRow row = sheet.createRow(0);
    //3.设置样式
    String[] rows=new String[]{"序号","学号","名字","性别","成绩","入学时间"};
    System.out.println(HSSFWorkbook.class.getProtectionDomain().getCodeSource().getLocation());
    for (int i = 0; i < rows.length; i++) {
        Cell cell = row.createCell(i);
        cell.setCellValue(rows[i]);
    }
    for (int i = 0; i < allStudent.size(); i++) {
        HSSFRow tempRow = sheet.createRow(i+1);
        Student tempStudent = allStudent.get(i);
        HSSFCell cell = tempRow.createCell(0);
        cell.setCellValue(i+1);
        tempRow.createCell(1).setCellValue(tempStudent.getId());
        tempRow.createCell(2).setCellValue(tempStudent.getName());
        tempRow.createCell(3).setCellValue(tempStudent.getSex());
        tempRow.createCell(4).setCellValue(tempStudent.getScore());
        Cell dateTemp=tempRow.createCell(5);
        HSSFCellStyle style4 = sheets.createCellStyle();
        HSSFDataFormat df = sheets.createDataFormat(); // 此处设置数据格式
        style4.setDataFormat(df.getFormat("yyyy-MM-dd hh:mm:ss"));
        dateTemp.setCellValue(tempStudent.getBirth());
        dateTemp.setCellStyle(style4);
    }
    //5.创建关联输出流和文件
    String fileName="学生信息.xls";
    String mimeType = request.getServletContext().getMimeType(fileName);
    MultiValueMap<String,String> headers=new HttpHeaders();
    headers.add("Content-Disposition","attchement;filename="+new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
    System.out.println("mmeType:"+mimeType);
    headers.add("Content-Type", mimeType);
    byte[] bytes = sheets.getBytes();
    System.out.println(bytes);
    return new ResponseEntity<>(bytes, headers, HttpStatus.OK);
}
@RequestMapping("/saveStudentByExcel")
public String saveStudentByExcel(@RequestParam(value = "file",required = false)MultipartFile file) throws IOException {
    if (!file.isEmpty()){
        InputStream inputStream = file.getInputStream();
        //1.创建工作簿
        HSSFWorkbook sheets = new HSSFWorkbook(inputStream);
        //2.获取工作表
        Sheet sheet = sheets.getSheetAt(0);//获取一个工作表
        for (int k = 1; k < sheet.getPhysicalNumberOfRows(); k++) {
            Row row = sheet.getRow(k);//获取一行数据
            //获取行下的所有单元格
            Student s = new Student();
            for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                Cell cell = row.getCell(j);//获取下标为j的单元格
                //判断单元格数据的类型
                if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                    String value = cell.getStringCellValue();
                    if (j==2){
                        s.setName(value);
                    }else if(j==3){
                        s.setSex(value);
                    }
                } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {//数字和日期类型
                    //判断单元格数据的格式
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                        Date value = cell.getDateCellValue();
                        s.setBirth(value);
                    } else {
                        double value = cell.getNumericCellValue();
                        if (j == 1) {
                            s.setId((int) value);
                        }
                        if (j == 4) {
                            s.setScore((float) value);
                        }
                    }
                }
            }
            studentDao.addOne(s);
        }
    }
    return "success";
}

2.2 单元格的样式设置

HSSFCellStyle cellStyle = wb.createCellStyle();

2.3.1 设置背景色

cellStyle.setFillForegroundColor((short) 13);// 设置背景色  

cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

2.3.2 设置边框

cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框  

cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框  

cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框  

cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

2.3.3 设置居中:

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中

2.3.4 设置字体:

HSSFFont font = wb.createFont();  
font.setFontName("黑体");  
font.setFontHeightInPoints((short) 16);//设置字体大小  


HSSFFont font2 = wb.createFont();  
font2.setFontName("仿宋_GB2312");  
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示  
font2.setFontHeightInPoints((short) 12);  

cellStyle.setFont(font);//选择需要用到的字体格式

2.3.5 设置列宽:

sheet.setColumnWidth(0, 3766); 

//第一个参数代表列id(从0开始),第2个参数代表宽度值  参考 ："2012-08-10"的宽度为2500

2.3.6 设置自动换行:

cellStyle.setWrapText(true);//设置自动换行

2.3.7 合并单元格:

Region region1 = new Region(0, (short) 0, 0, (short) 6);//参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号 
//此方法在POI3.8中已经被废弃，建议使用下面一个  
或者用
CellRangeAddress region1 = new CellRangeAddress(rowNumber, rowNumber, (short) 0, (short) 11);  
//参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列   
但应注意两个构造方法的参数不是一样的，具体使用哪个取决于POI的不同版本。
sheet.addMergedRegion(region1);