MySQL绘制POI的实体图_poi 画图工具类

package com.kehua.framework.utils;

import java.io.FileOutputStream;

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.XDDFLineProperties;

import org.apache.poi.xddf.usermodel.XDDFShapeProperties;

import org.apache.poi.xddf.usermodel.XDDFSolidFillProperties;

import org.apache.poi.xddf.usermodel.chart.*;

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;

public class DrawXlsxUtil {

/**

* 根据sheet数据画图(单序列)

* caution:文件打开关闭由外部操作

* @param sourceSheet 数据源sheet表(获取数据的表)

* @param distSheet 目标sheet表(画图的表)

* @param anchorPosition 画板位置

* @param chartTitle 图名称

* @param xAxisTitle x轴标识

* @param yAxisTitle y轴标识

* @param xAxisDataRangeAddress x轴数据位置(根据sheet获取)

* @param yAxisDataRangeAddress y轴数据位置

* @param chartType 图样式：暂支持折线、条形

* @param color 颜色

*/

public static void createDataChart(XSSFSheet sourceSheet, XSSFSheet distSheet, AnchorPosition anchorPosition,

String chartTitle, String xAxisTitle, String yAxisTitle,

CellRangeAddress xAxisDataRangeAddress, CellRangeAddress yAxisDataRangeAddress,

ChartTypes chartType, PresetColor color) {

if (!(ChartTypes.BAR.equals(chartType) || ChartTypes.LINE.equals(chartType))) return;

//Create a panel to draw

XSSFDrawing drawing = distSheet.createDrawingPatriarch();

XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0,

anchorPosition.getCol1Index(), anchorPosition.getRow1Index(),

anchorPosition.getCol2Index(), anchorPosition.getRow2Index());

XSSFChart chart = drawing.createChart(anchor);

// Use a category axis for the bottom axis.

XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);

chart.setTitleText(chartTitle);

bottomAxis.setTitle(xAxisTitle);

XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);

leftAxis.setTitle(yAxisTitle);

leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);

leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);

XDDFCategoryDataSource xs = XDDFDataSourcesFactory.fromStringCellRange(sourceSheet, xAxisDataRangeAddress);

XDDFNumericalDataSource ys1 = XDDFDataSourcesFactory.fromNumericCellRange(sourceSheet, yAxisDataRangeAddress);

XDDFChartData data = chart.createData(chartType, bottomAxis, leftAxis);

XDDFChartData.Series series1 = data.addSeries(xs, ys1);

//line

if (data instanceof XDDFLineChartData) {

XDDFLineChartData.Series series = (XDDFLineChartData.Series)series1;

series.setSmooth(false);

series.setMarkerStyle(MarkerStyle.NONE);

}

//bar

if (data instanceof XDDFBarChartData) {

// in order to transform a bar chart into a column chart, you just need to change the bar direction

XDDFBarChartData bar = (XDDFBarChartData) data;

bar.setBarDirection(BarDirection.COL);

}

//don't delete the following line

series1.setTitle("picture", null);

chart.plot(data);

solidFillSeries(data, 0, color);

}

/**

* 设置图属性

* @param data 待画的数据

* @param index 数据序列编号 从0开始

* @param color 颜色

*/

private static void solidFillSeries(XDDFChartData data, int index, PresetColor color){

XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));

XDDFChartData.Series series = data.getSeries().get(index);

XDDFShapeProperties properties = series.getShapeProperties();

if (properties == null) properties = new XDDFShapeProperties();

//line

if (data instanceof XDDFLineChartData) {

XDDFLineProperties line = new XDDFLineProperties();

line.setFillProperties(fill);

properties.setLineProperties(line);

}

//bar

if (data instanceof XDDFBarChartData) properties.setFillProperties(fill);

series.setShapeProperties(properties);

}

/**

* 画折线图调用参考

* @throws Exception

*/

private static void createLineChartDemo() throws Exception{

XSSFWorkbook wb = new XSSFWorkbook();

XSSFSheet sheet = wb.createSheet("linechart");

final int NUM_OF_ROWS = 3;

final int NUM_OF_COLUMNS = 10;

Row row;

Cell cell;

for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) {

row = sheet.createRow((short) rowIndex);

for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) {

cell = row.createCell((short) colIndex);

if (rowIndex == 0) cell.setCellValue(String.valueOf(colIndex * (rowIndex + 1.0)));

else cell.setCellValue(colIndex * (rowIndex + 1.0));

}

}

CellRangeAddress xAxisDataRangeAddress = new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1);

CellRangeAddress yAxisDataRangeAddress = new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1);

AnchorPosition anchorPosition = new AnchorPosition(5, 5, 15, 15);

createDataChart(sheet, sheet, anchorPosition, "Picture","Time(min)", "Power(kW)",

xAxisDataRangeAddress, yAxisDataRangeAddress, ChartTypes.LINE, PresetColor.YELLOW);

FileOutputStream fileOut = new FileOutputStream("D:/ooxml-chart.xlsx");

wb.write(fileOut);

}

/**

* 设置画板在sheet中的位置

*/

public static class AnchorPosition {

private int col1Index;//起始位置

private int row1Index;

private int col2Index;//结束位置

private int row2Index;

public AnchorPosition(int col1Index, int row1Index, int col2Index, int row2Index) {

this.col1Index = col1Index;

this.row1Index = row1Index;

this.col2Index = col2Index;

this.row2Index = row2Index;

}

public int getCol1Index() {

return col1Index;

}

public void setCol1Index(int col1Index) {

this.col1Index = col1Index;

}

public int getRow1Index() {

return row1Index;

}

public void setRow1Index(int row1Index) {

this.row1Index = row1Index;

}

public int getCol2Index() {

return col2Index;

}

public void setCol2Index(int col2Index) {

this.col2Index = col2Index;

}

public int getRow2Index() {

return row2Index;

}

public void setRow2Index(int row2Index) {

this.row2Index = row2Index;

}

}

}