使用poi 操作 Excel的一些方法

使用poi 操作 Excel的一些方法

• 使用到的maven依赖

<!-- poi -->
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>3.15</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>3.15</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-examples</artifactId>
  <version>3.15</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml-schemas</artifactId>
  <version>3.15</version>
</dependency>
<!-- end poi-->

• 将图片插入到Excel表格中

public static void main(String[] args) {
    FileOutputStream fileOut = null;
    BufferedImage bufferImg;//图片
    try {
        //先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        //将图片读到BufferedImage
        bufferImg = ImageIO.read(new File("C:\\Users\\admin\\Desktop\\20180414.jpg"));
        // 将图片写入流中
        ImageIO.write(bufferImg, "jpg", byteArrayOut);
        // 创建一个工作薄
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建一个sheet
        HSSFSheet sheet = wb.createSheet();
        // 利用HSSFPatriarch将图片写入EXCEL
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        /**
         * 该构造函数有8个参数
         * 前四个参数是控制图片在单元格的位置，分别是图片距离单元格left，top，right，bottom的像素距离
         */
        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,
                (short) 10, 1, (short) 20, 8);
        // 插入图片
        patriarch.createPicture(anchor, wb.addPicture(byteArrayOut
                .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        //生成的excel文件地址
        fileOut = new FileOutputStream("D:\\123.xls");
        // 写入excel文件
        wb.write(fileOut);
    } catch (IOException io) {
        io.printStackTrace();
        System.out.println("io erorr : " + io.getMessage());
    } finally {
        if (fileOut != null) {
            try {
                fileOut.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

• 修改Excel表格中某个单元格中的内容

@Test
public void updateChart() {
    try {
        //初始化输入流和输出流
        String filePath = "D:\\统计报表模板\\告警统计图.xlsx";
        FileInputStream inPut = new FileInputStream(filePath);
        //获取对象
        Workbook workBook = new XSSFWorkbook(inPut);
        FileOutputStream outPut = new FileOutputStream(filePath);
        //获取工作簿
        Sheet sheet = workBook.getSheetAt(0);

        Row row = sheet.getRow(3);
        Cell cell = row.getCell(0);
        cell.setCellValue(100);

        inPut.close();
        workBook.write(outPut);
        outPut.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

• 在Excel表格中生成折线图

@Test
public void test() throws IOException {
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    final int NUM_ROWS = 3;
    final int NUM_COLUMNS = 10;

    Row row;
    Cell cell;

    for (int i = 0; i < NUM_ROWS; i++) {
        row = sheet.createRow(i);
        for (int j = 0; j < NUM_COLUMNS; j++) {
            cell = row.createCell(j);
            if (i == 0) {
                cell.setCellValue(2000 + j);
            } else {
                cell.setCellValue((int) (Math.random() * 100 + 1));
            }
        }
    }

    Drawing drawing = sheet.createDrawingPatriarch();
    //设置统计图的位置，后面四个参数是左上角图标和右下角图标
    ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 2, 5, 10, 15);

    Chart chart = drawing.createChart(anchor);
    ChartLegend legend = chart.getOrCreateLegend();
    //统计图里图例的位置
    legend.setPosition(LegendPosition.TOP_RIGHT);

    ScatterChartData data = chart.getChartDataFactory().createScatterChartData();

    ValueAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM);
    ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
    leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
    bottomAxis.setCrosses(AxisCrosses.AUTO_ZERO);
    ChartDataSource<String> xs = DataSources.fromStringCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_COLUMNS - 1));
    ChartDataSource<Number> ys1 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_COLUMNS - 1));

    ChartDataSource<Number> ys2 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_COLUMNS - 1));
    //折线的标题
    data.addSerie(xs, ys1).setTitle("苹果(:个)");
    data.addSerie(xs, ys2).setTitle("梨子(:个)");

    chart.plot(data, bottomAxis, leftAxis);

    String path = "D:\\加密\\CHAT1.xlsx";

    FileOutputStream fos = new FileOutputStream(path);
    workbook.write(fos);
    fos.close();
}