黑马

 

 

 

 3.poi入门操作

　　3.1 搭建环境

<dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>4.1.0</version>
  </dependency>
  <!-- poi2007相应依赖坐标 -->
  <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>4.1.0</version>
  </dependency>
  <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml-schemas</artifactId>
      <version>4.1.0</version>
  </dependency>

 3.4基本操作

 

public class App {
    public static void main(String[] args) {
        //1.创建工作簿
        Workbook wb = new XSSFWorkbook();//2007版
        //2.创建sheet
        Sheet sheet = wb.createSheet("test");
        //3文件流
        try (FileOutputStream fos = new FileOutputStream("E:\poi\test.xlsx")) {
            wb.write(fos);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}

Demo2 设置单元格样式

视频链接

/**
 * Demo2设置单元格样式
 */
public class Demo2 {
    public static void main(String[] args) {
        //1.创建工作簿
        Workbook wb = new XSSFWorkbook();//2007版
        //2.创建sheet
        Sheet sheet = wb.createSheet("test");
        //创建行对象，参数：索引从0开始
        Row row = sheet.createRow(2);
        //创建单元格对象，索引从0开始
        Cell cell = row.createCell(2);
        cell.setCellValue("才高八斗");

        //样式处理
        //创建样式对象
        CellStyle style = wb.createCellStyle();
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        style.setBorderBottom(BorderStyle.THIN);//下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框

        //创建字体对象
        Font font = wb.createFont();
        font.setFontName("华文行楷");
        font.setFontHeightInPoints((short) 28);//设置字号
        style.setFont(font);

        //行高和列宽
        row.setHeightInPoints(50);//设置行高
        sheet.setColumnWidth(2, 31 * 256);//第三列，设置列宽需要乘以256

        //设置水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        //向单元格设置样式
        cell.setCellStyle(style);

        //3文件流
        try (FileOutputStream fos = new FileOutputStream("E:\poi\test3.xlsx")) {
            wb.write(fos);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}

 Demo3 在Excel中添加图片

视频链接

/**
 * Demo3在Excel中添加图片
 */
public class Demo1 {
    public static void main(String[] args) throws IOException {
        //1.创建工作簿
        Workbook wb = new XSSFWorkbook();//2007版
        //2.创建sheet
        Sheet sheet = wb.createSheet("test");

        //------------------------------------------------------------
        //1.读取图片流
        FileInputStream fis = new FileInputStream("E:\poi\merry.jpg");

        //2.转化二进制数组
        byte[] bytes = IOUtils.toByteArray(fis);
        fis.read(bytes);
        //3.向poi内存中添加一张图片，返回图片在图片集合中的索引
        int index = ((XSSFWorkbook) wb).addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
        //4.绘制图片工具类
        CreationHelper helper = wb.getCreationHelper();
        //5.创建一个绘图对象
        Drawing<?> drawing = sheet.createDrawingPatriarch();
        //6.创建锚点，设置图片坐标
        ClientAnchor anchor = helper.createClientAnchor();
        //设置图片起始位置
        anchor.setRow1(1);
        anchor.setCol1(1);
        ////设置图片结束位置
        anchor.setRow2(15);
        anchor.setCol2(15);
        //7.绘制图片
        Picture picture = drawing.createPicture(anchor, index);//图片位置，图片的索引
//        picture.resize();//自适应渲染图片
        //------------------------------------------------------------
        //3文件流
        try (FileOutputStream fos = new FileOutputStream("E:\poi\test4.xlsx")) {
            wb.write(fos);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}

 加载Excel

/**
 * Demo4读取Excel并解析
 * sheet.getLastRowNum():获取最后一行的索引值
 * row.getLastCellNum():获取最后一个单元格的号码
 */
public class Demo4 {
    public static void main(String[] args) throws IOException {
        //1.根据Excel文件创建工作簿
        Workbook wb = new XSSFWorkbook("E:\poi\demo2.xlsx");
        //2.获取Sheet
        Sheet sheet = wb.getSheetAt(0);
        //3.获取sheet中的每一行和每一个单元格
        for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);//根据索引获取每一行
            if (row == null){
                continue;
            }
            StringBuilder sb = new StringBuilder();
            for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
                //根据索引获取每一个单元格
                Cell cell = row.getCell(cellNum);
                if(cell == null){
                    continue;
                }
                //获取每一个单元格的内容
                Object value = getCellValue(cell);
                sb.append(value).append("-");
            }
            System.out.println(sb.toString());
        }

    }

    private static Object getCellValue(Cell cell) {

        //1.获取单元格的属性类型
        CellType cellType = cell.getCellType();
        //2.根据单元格的类型获取时间
        Object value = null;
        switch (cellType) {
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    //日期格式
                    value = cell.getDateCellValue();
                } else {
                    value = cell.getNumericCellValue();
                }
                break;
            case STRING:
                value = cell.getStringCellValue();
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case FORMULA://公式类型
                value = cell.getCellFormula();
                break;
            default:
                break;
        }

        return value;
    }
}