POI 重疊、並列柱狀圖（條形圖），顯示資料，自定義顏色

爱做笔记發表於2024-05-16
1、pom.xml

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.4</version>
</dependency>
<dependency>
<groupId>commons-codec</groupId>
<artifactId>commons-codec</artifactId>
<version>1.13</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-compress</artifactId>
<version>1.19</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-schemas</artifactId>
<version>1.4</version>
</dependency>


2、poi 柱狀圖、條形圖自定義顏色，重疊條形圖，顯示數值。
package com.itcodai.course01.entity;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xddf.usermodel.PresetColor;
import org.apache.poi.xddf.usermodel.XDDFColor;
import org.apache.poi.xddf.usermodel.XDDFSolidFillProperties;
import org.apache.poi.xddf.usermodel.chart.AxisCrossBetween;
import org.apache.poi.xddf.usermodel.chart.AxisCrosses;
import org.apache.poi.xddf.usermodel.chart.AxisPosition;
import org.apache.poi.xddf.usermodel.chart.BarDirection;
import org.apache.poi.xddf.usermodel.chart.BarGrouping;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
import org.apache.poi.xddf.usermodel.chart.XDDFBarChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFCategoryAxis;
import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSourcesFactory;
import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFValueAxis;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarSer;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTDPt;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;
import org.openxmlformats.schemas.drawingml.x2006.chart.STDLblPos;
public class test { public static void main(String[] args) throws IOException { XSSFWorkbook wb = new XSSFWorkbook();
    String sheetName = "Sheet1";
    FileOutputStream fileOut = null;
    try { XSSFSheet sheet = wb.createSheet(sheetName);
//第一行，國家名稱 
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellValue("俄羅斯");
        cell = row.createCell(1);
        cell.setCellValue("加拿大");
        cell = row.createCell(2);
        cell.setCellValue("美國");
        cell = row.createCell(3);
        cell.setCellValue("中國");
        cell = row.createCell(4);
        cell.setCellValue("巴西");
        cell = row.createCell(5);
        cell.setCellValue("澳大利亞");
        cell = row.createCell(6);
        cell.setCellValue("印度");
// 第二行，鄉村地區 
        row = sheet.createRow(1);
        cell = row.createCell(0);
        cell.setCellValue(17098242);
        cell = row.createCell(1);
        cell.setCellValue(9984670);
        cell = row.createCell(2);
        cell.setCellValue(9826675);
        cell = row.createCell(3);
        cell.setCellValue(9596961);
        cell = row.createCell(4);
        cell.setCellValue(8514877);
        cell = row.createCell(5);
        cell.setCellValue(7741220);
        cell = row.createCell(6);
        cell.setCellValue(3287263);
// 第三行，農村人口 
        row = sheet.createRow(2);
        cell = row.createCell(0);
        cell.setCellValue(14590041);
        cell = row.createCell(1);
        cell.setCellValue(35151728);
        cell = row.createCell(2);
        cell.setCellValue(32993302);
        cell = row.createCell(3);
        cell.setCellValue(14362887);
        cell = row.createCell(4);
        cell.setCellValue(21172141);
        cell = row.createCell(5);
        cell.setCellValue(25335727);
        cell = row.createCell(6);
        cell.setCellValue(13724923);
// 第四行，面積平局 
        row = sheet.createRow(3);
        cell = row.createCell(0);
        cell.setCellValue(9435701.143);
        cell = row.createCell(1);
        cell.setCellValue(9435701.143);
        cell = row.createCell(2);
        cell.setCellValue(9435701.143);
        cell = row.createCell(3);
        cell.setCellValue(9435701.143);
        cell = row.createCell(4);
        cell.setCellValue(9435701.143);
        cell = row.createCell(5);
        cell.setCellValue(9435701.143);
        cell = row.createCell(6);
        cell.setCellValue(9435701.143);
// 第四行，人口平局 
        row = sheet.createRow(4);
        cell = row.createCell(0);
        cell.setCellValue(22475821.29);
        cell = row.createCell(1);
        cell.setCellValue(22475821.29);
        cell = row.createCell(2);
        cell.setCellValue(22475821.29);
        cell = row.createCell(3);
        cell.setCellValue(22475821.29);
        cell = row.createCell(4);
        cell.setCellValue(22475821.29);
        cell = row.createCell(5);
        cell.setCellValue(22475821.29);
        cell = row.createCell(6);
        cell.setCellValue(22475821.29);
//建立一個畫布 
        XSSFDrawing drawing = sheet.createDrawingPatriarch();
//前四個預設0，[0,5]：從0列5行開始;[7,26]:到7列26行結束 
//預設寬度(14-8)*12 
        XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 7, 26);
//建立一個chart物件 
        XSSFChart chart = drawing.createChart(anchor);
//標題 
        chart.setTitleText("地區排名前七的國家");
//標題覆蓋 
        chart.setTitleOverlay(false);
//圖例位置 
        XDDFChartLegend legend = chart.getOrAddLegend();
        legend.setPosition(LegendPosition.TOP);
//分類軸標(X軸),標題位置 
        XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
        bottomAxis.setTitle("國家");
//值(Y軸)軸,標題位置 
        XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
        leftAxis.setTitle("面積和人口");
//CellRangeAddress(起始行號，終止行號， 起始列號，終止列號） 
//分類軸標(X軸)資料，單元格範圍位置[0, 0]到[0, 6] 
        XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(0, 0, 0, 6));
// XDDFCategoryDataSource countries = XDDFDataSourcesFactory.fromArray(new String[] {"俄羅斯","加拿大","美國","中國","巴西","澳大利亞","印度"});
//資料1，單元格範圍位置[1, 0]到[1, 6] 
        XDDFNumericalDataSource<Double> area = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, 6));
// XDDFNumericalDataSource<Integer> area = XDDFDataSourcesFactory.fromArray(new Integer[] {17098242,9984670,9826675,9596961,8514877,7741220,3287263});
//資料2，單元格範圍位置[2, 0]到[2, 6] 
        XDDFNumericalDataSource<Double> population = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, 6));
//bar：條形圖， 
        XDDFBarChartData bar = (XDDFBarChartData) chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
        leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
        leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
//設定為可變顏色 
        bar.setVaryColors(true);
//條形圖方向，縱向/橫向：縱向 
        bar.setBarDirection(BarDirection.COL);
//圖表載入資料，條形圖1 
        XDDFBarChartData.Series series1 = (XDDFBarChartData.Series) bar.addSeries(countries, area);
//條形圖例標題 
        series1.setTitle("面積", null);
        XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.RED));
//條形圖，填充顏色 
        series1.setFillProperties(fill);
//圖表載入資料，條形圖2 
        XDDFBarChartData.Series series2 = (XDDFBarChartData.Series) bar.addSeries(countries, population);
//條形圖例標題 
        series2.setTitle("人口", null);
        XDDFSolidFillProperties fill2 = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.BLUE));
//條形圖，填充顏色 
        series2.setFillProperties(fill2);
        CTPlotArea plotArea = chart.getCTChart().getPlotArea();
//繪製 
        chart.plot(bar);
// 堆積條形圖，將2個條形圖重疊起來 
        bar.setBarGrouping(BarGrouping.STACKED);
//修正重疊，使鋼筋真正堆疊而不是並排 
        plotArea.getBarChartArray(0).addNewOverlap().setVal((byte) 100);
//柱狀圖1上顯示數值 
        plotArea.getBarChartArray(0).getSerArray(0).addNewDLbls();
        plotArea.getBarChartArray(0).getSerArray(0).getDLbls().addNewShowVal().setVal(true);
        plotArea.getBarChartArray(0).getSerArray(0).getDLbls().addNewShowLegendKey().setVal(false);
        plotArea.getBarChartArray(0).getSerArray(0).getDLbls().addNewShowCatName().setVal(false);
        plotArea.getBarChartArray(0).getSerArray(0).getDLbls().addNewShowSerName().setVal(false);
//柱狀圖2上顯示數值 
        plotArea.getBarChartArray(0).getSerArray(1).addNewDLbls();
        plotArea.getBarChartArray(0).getSerArray(1).getDLbls().addNewShowVal().setVal(true);
        plotArea.getBarChartArray(0).getSerArray(1).getDLbls().addNewShowLegendKey().setVal(false);
        plotArea.getBarChartArray(0).getSerArray(1).getDLbls().addNewShowCatName().setVal(false);
        plotArea.getBarChartArray(0).getSerArray(1).getDLbls().addNewShowSerName().setVal(false);
//給每個條形圖設定顏色 
        CTBarSer ser = plotArea.getBarChartArray(0).getSerArray(0);
//黑色 
        CTDPt dpt = ser.addNewDPt();
        dpt.addNewIdx().setVal(0);
        dpt.addNewSpPr().addNewSolidFill().addNewSrgbClr().setVal(new byte[] { (byte) 0, (byte) 0, (byte) 0 });
//紅色 
        dpt = ser.addNewDPt();
        dpt.addNewIdx().setVal(1);
        dpt.addNewSpPr().addNewSolidFill().addNewSrgbClr().setVal(new byte[] { (byte) 255, (byte) 0, (byte) 0 });
//藍色 
        dpt = ser.addNewDPt();
        dpt.addNewIdx().setVal(2);
        dpt.addNewSpPr().addNewSolidFill().addNewSrgbClr().setVal(new byte[] { (byte) 0, (byte) 0, (byte) 255 });
//綠色 
        dpt = ser.addNewDPt();
        dpt.addNewIdx().setVal(3);
        dpt.addNewSpPr().addNewSolidFill().addNewSrgbClr().setVal(new byte[] { (byte) 0, (byte) 128, (byte) 0 });
//黃色 
        dpt = ser.addNewDPt();
        dpt.addNewIdx().setVal(4);
        dpt.addNewSpPr().addNewSolidFill().addNewSrgbClr().setVal(new byte[] { (byte) 255, (byte) 255, (byte) 0 });
//紫色 
        dpt = ser.addNewDPt();
        dpt.addNewIdx().setVal(5);
        dpt.addNewSpPr().addNewSolidFill().addNewSrgbClr().setVal(new byte[] { (byte) 80, (byte) 0, (byte) 80 });
//棕色 
        dpt = ser.addNewDPt();
        dpt.addNewIdx().setVal(6);
        dpt.addNewSpPr().addNewSolidFill().addNewSrgbClr().setVal(new byte[] { (byte) 165, (byte) 42, (byte) 42 });
//邊框 
//ser.getSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] { (byte) 0, (byte) 0, (byte) 0 });
// 列印圖表的xml 
//System.out.println(chart.getCTChart());
// 將輸出寫入excel檔案 
        String filename = "排行榜前七的國家.xlsx";
        fileOut = new FileOutputStream(filename);
        wb.write(fileOut);
    } catch (Exception e) { e.printStackTrace();
    } finally { wb.close();
        if (fileOut != null) { fileOut.close();
        } } } }

相關文章