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实现效果展示

Excel中的效果图

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初始数据准备

自己组装数据同样可以作为原始数据去画图,具体代码注释
Excel中的数据准备

3.2实现效果展示

Excel散点图实现效果展示

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-------------------------------------------

  • 4
    点赞
  • 9
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值