POI处理Excel,条形图,散点图,折线图
项目中需要使用到Java来处理Excel图表的情况,记录一次Java使用POI来处理条形图,散点图,折线图。直接上代码:
一、引入Maven依赖
// An highlighted block
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<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>ooxml-schemas</artifactId>
<version>1.4</version>
</dependency>
二、条形图实现
------------------------------------------条形图开始展示-------------------------------------------
2.1实现效果展示
2.2 代码实现如下:
// 处理条形图
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
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.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.XDDFCategoryDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend;
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.CTPlotArea;
import com.ceshi.FileUtil;
/**
* @Author Lee
* @Date 2021/3/20
* POI实现Excel条形图
**/
public class XSSFUtils {
public static void main(String[] args) throws Exception{
XSSFWorkbook wb = new XSSFWorkbook();
String sheetName = "Sheet1";
FileOutputStream fileOut = null;
try {
XSSFSheet sheet = wb.createSheet(sheetName);
List<String> testList = new ArrayList<String>();
testList.add("测试数据1");
testList.add("测试数据2");
testList.add("测试数据3");
testList.add("测试数据4");
testList.add("测试数据5");
testList.add("测试数据6");
testList.add("测试数据7");
List<Integer> areaList = new ArrayList<Integer>();
// 第二行,测试值
areaList.add(-10);
areaList.add(-90);
areaList.add(98);
areaList.add(95);
areaList.add(85);
areaList.add(77);
areaList.add(32);
//创建一个画布
XSSFDrawing drawing = sheet.createDrawingPatriarch();
//前四个默认0,[0,10]:从0列10行开始;[10,30]:宽度10个单元格,30向下扩展到30行
XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 10, 10, 30);
//创建一个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.TOP);
bottomAxis.setTitle("X轴标题");
//值(Y轴)轴,标题位置
XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
leftAxis.setTitle("Y轴标题");
//CellRangeAddress(起始行号,终止行号, 起始列号,终止列号)
//分类轴标(X轴)数据,单元格范围位置[0, 0]到[0, 6]
// XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(0, 0, 0, 6));
//直接传入数据处理
XDDFCategoryDataSource countries = XDDFDataSourcesFactory.fromArray(testList.toArray(new String[testList.size()]));
//数据1,单元格范围位置[1, 0]到[1, 6]
// XDDFNumericalDataSource<Double> area = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, 6));
//直接传入数据处理
XDDFNumericalDataSource<Integer> area = XDDFDataSourcesFactory.fromArray(areaList.toArray(new Integer[areaList.size()]));
//bar:条形图,XDDFChartData可以看看可以实现的视图种类
XDDFBarChartData bar = (XDDFBarChartData) chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
//设置为可变颜色
bar.setVaryColors(false);
//条形图方向,纵向/横向:纵向
bar.setBarDirection(BarDirection.COL);
//图表加载数据,条形图1
XDDFBarChartData.Series series1 = (XDDFBarChartData.Series) bar.addSeries(countries, area);
//条形图例标题
series1.setTitle("测试", null);
XDDFSolidFillProperties fill = new XDDFSolidFillProperties();
//条形图,填充颜色
series1.setFillProperties(fill);
CTPlotArea plotArea = chart.getCTChart().getPlotArea();
//绘制
chart.plot(bar);
//柱状图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);
//***** 非常重要,可以设置excel中的对应(以互补色代表负值),对于条形图有负数的情况,一定要设置,否则颜色无法填充
plotArea.getBarChartArray(0).getSerArray(0).addNewInvertIfNegative().setVal(false);
// 将输出写入excel文件
String tmpFileName = "/data/tmp/" + System.currentTimeMillis() + ".xlsx";
FileUtil.createNewFile(tmpFileName);
fileOut = new FileOutputStream(tmpFileName);
wb.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
} finally {
wb.close();
if (fileOut != null) {
fileOut.close();
}
}
}
}
------------------*明显的分割线-------------------------
重要提示::如果条形图中有负值的情况,请务必设置这个
plotArea.getBarChartArray(0).getSerArray(0).addNewInvertIfNegative().setVal(false);
坑我已经踩过了,记录一下
------------------------------------------条形图End-------------------------------------------
三、散点图实现-----对应Excel中的XY散点图
------------------------------------------散点图Start-------------------------------------------
3.1初始数据准备
自己组装数据同样可以作为原始数据去画图,具体代码注释
3.2实现效果展示
3.3代码实现如下:
// 处理散点图
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xddf.usermodel.PresetLineDash;
import org.apache.poi.xddf.usermodel.XDDFLineProperties;
import org.apache.poi.xddf.usermodel.XDDFPresetLineDash;
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.ChartTypes;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
import org.apache.poi.xddf.usermodel.chart.XDDFCategoryDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSourcesFactory;
import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFScatterChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFValueAxis;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* @Author Lee
* @Date 2021/3/20
* POI实现Excel条形图
**/
public class XSSFUtils {
public static void main(String[] args) throws Exception{
String tmpFileName = "/data/tmp/testzhexiantu.xlsx";
File file = new File(tmpFileName);
System.out.println(file.exists());
InputStream fis = new FileInputStream(file);
XSSFWorkbook wb = new XSSFWorkbook(fis);
XSSFSheet sheet = wb.getSheetAt(0);
//创建一个画布
XSSFDrawing drawing = sheet.createDrawingPatriarch();
//前四个默认0,[0,25]:从0列25行开始;[15,45]:宽度15个单元格,45向下扩展到45行
ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 25, 15, 45);
//创建一个chart对象
XSSFChart chart = drawing.createChart(anchor);
//标题
chart.setTitleText("测试标题1");
//标题覆盖
chart.setTitleOverlay(false);
//图例位置
XDDFChartLegend legend = chart.getOrAddLegend();
legend.setPosition(LegendPosition.TOP);
//分类轴标(X轴),标题位置
//****** 如果是多条线,且X轴时数值类型的,切记::::要用XDDFValueAxis
XDDFValueAxis bottomAxis = chart.createValueAxis(AxisPosition.TOP);
// //****** 如果是多条线,用XDDFCategoryAxis,会出现乱码情况
//XDDFCategoryAxis bottomAxis = chart.createXCategoryAxis(AxisPosition.TOP);
bottomAxis.setTitle("测试标题2");
//值(Y轴)轴,标题位置
XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.BOTTOM);
leftAxis.setTitle("测试标题3");
//CellRangeAddress(起始行号,终止行号, 起始列号,终止列号)
//分类轴标(X轴)数据,单元格范围位置[0, 0]到[0, 6]
XDDFNumericalDataSource<Double> x = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(2, 7, 0, 0));
// //已有数值处理
//XDDFNumericalDataSource<Integer> x = XDDFDataSourcesFactory.fromArray(xList.toArray(new Integer[xList.size()]));
//数据,单元格范围位置[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});
XDDFNumericalDataSource<Double> y = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(2, 7, 1, 1));
//已有数值处理
//XDDFNumericalDataSource<Integer> y = XDDFDataSourcesFactory.fromArray(yList.toArray(new Integer[yList.size()]));
// XDDFNumericalDataSource<Integer> x1 = XDDFDataSourcesFactory.fromArray(x1List.toArray(new Integer[x1List.size()]));
//scatter:XY散点图
XDDFScatterChartData scatter = (XDDFScatterChartData) chart.createData(ChartTypes.SCATTER, bottomAxis, leftAxis);
leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
//设置为可变颜色
// //图表加载数据,条形图1
XDDFSolidFillProperties fill = new XDDFSolidFillProperties();
XDDFScatterChartData.Series series1 = (XDDFScatterChartData.Series) scatter.addSeries(x, y);
// //条形图例标题
series1.setTitle("测试1", null);
XDDFCategoryDataSource x1 = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(12, 14, 0, 0));
XDDFNumericalDataSource<Double> y1 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(12, 14, 1, 1));
//图表加载数据,条形图1
XDDFScatterChartData.Series series2 = (XDDFScatterChartData.Series) scatter.addSeries(x1, y1);
// //条形图例标题
series2.setTitle("测试2", null);
// scatter.addSeries(x1, y1);
//条形图,填充颜色
series2.setFillProperties(fill);
XDDFNumericalDataSource<Double> x2 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(19, 21, 0, 0));
XDDFNumericalDataSource<Double> y2 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(19, 21, 1, 1));
// //图表加载数据,条形图1
XDDFScatterChartData.Series series3 = (XDDFScatterChartData.Series) scatter.addSeries(x2, y2);
// //条形图例标题
series3.setTitle("测试3", null);
//
XDDFLineProperties line4 = new XDDFLineProperties();
line4.setPresetDash(new XDDFPresetLineDash(PresetLineDash.DASH_DOT));//虚线
series3.setLineProperties(line4);
//条形图,填充颜色
series3.setFillProperties(fill);
//绘制
chart.plot(scatter);
FileOutputStream fileOut = new FileOutputStream(tmpFileName);
wb.write(fileOut);
wb.close();
}
}
重要提示::如果是多条线,且X轴时数值类型的,切记:::要用XDDFValueAxis
如果使用XDDFCategoryAxis,X轴会把每个值作为一个点来处理,X轴会出现韩文,乱码
坑我已经踩过了,记录一下
------------------------------------------散点图End-------------------------------------------