package com.qianwen.smartman.modules.workinghour.service;
|
|
import java.io.FileOutputStream;
|
import java.io.File;
|
import java.io.IOException;
|
import java.text.SimpleDateFormat;
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
import org.apache.poi.ss.usermodel.BorderStyle;
|
import org.apache.poi.ss.usermodel.CellStyle;
|
import org.apache.poi.ss.usermodel.Chart;
|
import org.apache.poi.ss.usermodel.ClientAnchor;
|
import org.apache.poi.ss.usermodel.Drawing;
|
import org.apache.poi.ss.usermodel.FillPatternType;
|
import org.apache.poi.ss.usermodel.HorizontalAlignment;
|
import org.apache.poi.ss.usermodel.IndexedColors;
|
import org.apache.poi.ss.util.CellRangeAddress;
|
import org.apache.poi.xssf.streaming.SXSSFRow;
|
import org.apache.poi.xssf.streaming.SXSSFSheet;
|
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
|
import org.apache.poi.xssf.usermodel.XSSFChart;
|
import org.junit.jupiter.api.Test;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarChart;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarSer;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBoolean;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.CTCatAx;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.CTChart;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.CTDLbls;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.CTLegend;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.CTScaling;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.CTSerTx;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrRef;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.CTValAx;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.STAxPos;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.STBarDir;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.STBarGrouping;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.STDispBlanksAs;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.STLegendPos;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.STOrientation;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.STTickLblPos;
|
|
public class PartWorkingHourServiceTest {
|
static SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
|
|
private static String systemTime = System.currentTimeMillis()+"";//df.format(System.currentTimeMillis());;
|
private static SXSSFWorkbook wb = new SXSSFWorkbook();
|
|
private SXSSFSheet sheet = null;
|
|
@Test
|
public void testMain() {
|
// 字段名
|
List<String> fldNameArr = new ArrayList();
|
|
// 标题
|
List<String> titleArr = new ArrayList();
|
|
// 模拟数据
|
List<Map> dataList = new ArrayList();
|
|
Map dataMap1 = new HashMap();
|
|
dataMap1.put("value1", "货币基金");
|
|
dataMap1.put("value2", Math.floor(Math.random() * 100) + "");
|
|
dataMap1.put("value3", Math.floor(Math.random() * 100) + "");
|
|
dataMap1.put("value4", Math.floor(Math.random() * 100) + "");
|
|
Map dataMap2 = new HashMap();
|
|
dataMap2.put("value1", "指数基金");
|
|
dataMap2.put("value2", Math.floor(Math.random() * 100) + "");
|
|
dataMap2.put("value3", Math.floor(Math.random() * 100) + "");
|
|
dataMap2.put("value4", Math.floor(Math.random() * 100) + "");
|
|
Map dataMap3 = new HashMap();
|
|
dataMap3.put("value1", "偏股基金");
|
|
dataMap3.put("value2", Math.floor(Math.random() * 100) + "");
|
|
dataMap3.put("value3", Math.floor(Math.random() * 100) + "");
|
|
dataMap3.put("value4", Math.floor(Math.random() * 100) + "");
|
|
Map dataMap4 = new HashMap();
|
|
dataMap4.put("value1", "债券基金");
|
|
dataMap4.put("value2", Math.floor(Math.random() * 100) + "");
|
|
dataMap4.put("value3", Math.floor(Math.random() * 100) + "");
|
|
dataMap4.put("value4", Math.floor(Math.random() * 100) + "");
|
|
|
|
fldNameArr.add("value1");
|
|
fldNameArr.add("value2");
|
|
fldNameArr.add("value3");
|
|
fldNameArr.add("value4");
|
|
titleArr.add("类型");
|
|
titleArr.add("买入");
|
|
titleArr.add("卖出");
|
|
titleArr.add("分红");
|
|
dataList.add(dataMap1);
|
|
dataList.add(dataMap2);
|
|
dataList.add(dataMap3);
|
|
dataList.add(dataMap4);
|
|
|
|
//PoiExcelUtil ecu = new PoiExcelUtil();
|
|
try {
|
|
// 创建柱状图
|
//ecu.createBarChart(titleArr, fldNameArr, dataList);
|
createBarChart(titleArr, fldNameArr, dataList);
|
// 创建饼状图
|
// ecu.createPieChart(titleArr, fldNameArr, dataList);
|
|
// 创建折线图
|
//ecu.createTimeXYChar(titleArr, fldNameArr, dataList);
|
//
|
// 创建面积图
|
//ecu.createAreaChart(titleArr, fldNameArr, dataList);
|
|
String title="测试数据";
|
//导出到文件
|
FileOutputStream out = new FileOutputStream(new File("D:/PoiExcel/"+systemTime+"_"+title+".xls"));
|
|
wb.write(out);
|
|
out.close();
|
|
} catch (IOException e) {
|
|
e.printStackTrace();
|
|
}
|
|
}
|
/**
|
* 创建柱状图(堆积图，多组)
|
*
|
* @throws IOException
|
*/
|
|
public void createBarChart(List<String> titleArr, List<String> fldNameArr, List<Map> dataList) {
|
|
// 创建一个sheet页
|
sheet = wb.createSheet("sheet0");
|
// drawSheet0Table(sheet,titleArr,fldNameArr,dataList);
|
// 堆积=STBarGrouping.STACKED 多组=STBarGrouping.CLUSTERED
|
//boolean result = drawSheet0Map(sheet, STBarGrouping.CLUSTERED, fldNameArr, dataList, titleArr);
|
|
boolean result = drawSheet0Map(sheet, STBarGrouping.STACKED, fldNameArr, dataList, titleArr);//yys改
|
System.out.println("生成柱状图(堆积or多组)-->" + result);
|
|
}
|
|
/**
|
* 生成柱状图
|
*
|
* @param sheet 页签
|
* @param group 柱状图类型(堆积,多组)
|
* @param fldNameArr 坐标名称
|
* @param dataList 统计数据
|
* @return
|
*/
|
|
// private boolean drawSheet0Map(SXSSFSheet sheet, Enum group, List fldNameArr, List<Map> dataList, List titleArr) {
|
private boolean drawSheet0Map(SXSSFSheet sheet, STBarGrouping.Enum group, List<String> fldNameArr, List<Map> dataList, List<String> titleArr) {
|
|
boolean result = false;
|
|
// 获取sheet名称
|
String sheetName = sheet.getSheetName();
|
|
result = drawSheet0Table(sheet, titleArr, fldNameArr, dataList);
|
|
// 创建一个画布
|
Drawing drawing = sheet.createDrawingPatriarch();
|
|
// 画一个图区域
|
// 前四个默认0，从第8行到第25行,从第0列到第6列的区域
|
|
ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 8, 6, 25);
|
|
// 创建一个chart对象
|
Chart chart = drawing.createChart(anchor);
|
|
CTChart ctChart = ((XSSFChart) chart).getCTChart();
|
|
CTPlotArea ctPlotArea = ctChart.getPlotArea();
|
|
// 创建柱状图模型
|
CTBarChart ctBarChart = ctPlotArea.addNewBarChart();
|
|
CTBoolean ctBoolean = ctBarChart.addNewVaryColors();
|
|
ctBarChart.getVaryColors().setVal(true);
|
|
// 设置图类型
|
// STBarGrouping.Enum group = null;
|
ctBarChart.addNewGrouping().setVal(group);
|
|
ctBoolean.setVal(true);
|
|
|
//ctBarChart.addNewBarDir().setVal(STBarDir.COL);
|
ctBarChart.addNewBarDir().setVal(STBarDir.BAR);//yangysmod ,改成了横向
|
// 是否添加左侧坐标轴
|
ctChart.addNewDispBlanksAs().setVal(STDispBlanksAs.ZERO);
|
|
ctChart.addNewShowDLblsOverMax().setVal(true);
|
|
// 设置这两个参数是为了在STACKED模式下生成堆积模式；(standard)标准模式时需要将这两行去掉
|
if ("stacked".equals(group.toString()) || "percentStacked".equals(group.toString())) {
|
|
ctBarChart.addNewGapWidth().setVal(150);
|
|
ctBarChart.addNewOverlap().setVal((byte) 100);
|
|
}
|
|
// 创建序列,并且设置选中区域
|
//for (int i = 0; i < fldNameArr.size() - 1; i++) {
|
|
CTBarSer ctBarSer = ctBarChart.addNewSer();
|
|
CTSerTx ctSerTx = ctBarSer.addNewTx();
|
|
// 图例区
|
CTStrRef ctStrRef = ctSerTx.addNewStrRef();
|
|
// 选定区域第0行,第1,2,3列标题作为图例 //1 2 3
|
//String legendDataRange = new CellRangeAddress(0, 0, i + 1, i + 1).formatAsString(sheetName, true);
|
|
//ctStrRef.setF(legendDataRange);
|
|
ctBarSer.addNewIdx().setVal(3);//
|
//ctBarSer.
|
// 横坐标区
|
CTAxDataSource cttAxDataSource = ctBarSer.addNewCat();
|
|
ctStrRef = cttAxDataSource.addNewStrRef();
|
|
// 选第0列,第1-6行作为横坐标区域
|
String axisDataRange = new CellRangeAddress(1, dataList.size(), 0, 0).formatAsString(sheetName, true);
|
|
ctStrRef.setF(axisDataRange);
|
|
// 数据区域
|
CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();
|
|
CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();
|
|
// 选第1-6行,第1-3列作为数据区域 //1 2 3
|
//String numDataRange = new CellRangeAddress(1, dataList.size(), i + 1, i + 1).formatAsString(sheetName, true);
|
String numDataRange = new CellRangeAddress(1, dataList.size(), 1, 1).formatAsString(sheetName, true);//yangys mod
|
|
System.out.println("数据区域"+numDataRange);
|
|
ctNumRef.setF(numDataRange);
|
|
|
// 添加柱状边框线
|
ctBarSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[]{0, 0, 0});
|
|
// 设置负轴颜色不是白色
|
ctBarSer.addNewInvertIfNegative().setVal(false);
|
|
// 设置标签格式
|
ctBoolean.setVal(false);
|
|
CTDLbls newDLbls = ctBarSer.addNewDLbls();
|
|
newDLbls.setShowLegendKey(ctBoolean);
|
|
ctBoolean.setVal(true);
|
newDLbls.setShowVal(ctBoolean);
|
|
ctBoolean.setVal(false);
|
|
newDLbls.setShowCatName(ctBoolean);
|
newDLbls.setShowSerName(ctBoolean);
|
newDLbls.setShowPercent(ctBoolean);
|
newDLbls.setShowBubbleSize(ctBoolean);
|
newDLbls.setShowLeaderLines(ctBoolean);
|
|
//}
|
|
// 告诉BarChart它有坐标轴，并给它们id
|
ctBarChart.addNewAxId().setVal(123456);
|
|
ctBarChart.addNewAxId().setVal(123457);
|
|
// 横坐标
|
CTCatAx ctCatAx = ctPlotArea.addNewCatAx();
|
|
ctCatAx.addNewAxId().setVal(123456); // id of the cat axis
|
|
CTScaling ctScaling = ctCatAx.addNewScaling();
|
|
ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
|
|
ctCatAx.addNewAxPos().setVal(STAxPos.B);
|
|
ctCatAx.addNewCrossAx().setVal(123457); // id of the val axis
|
|
ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
|
|
// 纵坐标
|
CTValAx ctValAx = ctPlotArea.addNewValAx();
|
|
ctValAx.addNewAxId().setVal(123457); // id of the val axis
|
|
ctScaling = ctValAx.addNewScaling();
|
|
ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
|
|
// 设置位置
|
ctValAx.addNewAxPos().setVal(STAxPos.L);
|
|
ctValAx.addNewCrossAx().setVal(123456); // id of the cat axis
|
|
ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
|
|
// 是否删除主左边轴
|
ctValAx.addNewDelete().setVal(false);
|
|
// 是否删除横坐标
|
ctCatAx.addNewDelete().setVal(false);
|
|
// legend图注
|
|
|
// if (true) {
|
CTLegend ctLegend = ctChart.addNewLegend();
|
|
ctLegend.addNewLegendPos().setVal(STLegendPos.B);
|
|
ctLegend.addNewOverlay().setVal(false);
|
// }
|
|
return result;
|
|
}
|
|
|
/**
|
* 生成数据表
|
*
|
* @param sheet sheet页对象
|
* @param titleArr 表头字段
|
* @param fldNameArr 左边标题字段
|
* @param dataList 数据
|
* @return 是否生成成功
|
*/
|
|
private boolean drawSheet0Table(SXSSFSheet sheet, List<String> titleArr, List<String> fldNameArr, List<Map> dataList) {
|
|
// 测试时返回值
|
boolean result = true;
|
|
// 初始化表格样式
|
List styleList = tableStyle();
|
|
// 根据数据创建excel第一行标题行
|
SXSSFRow row0 = sheet.createRow(0);
|
|
for (int i = 0; i < titleArr.size(); i++) {
|
|
// 设置标题
|
row0.createCell(i).setCellValue(titleArr.get(i));
|
|
// 设置标题行样式
|
row0.getCell(i).setCellStyle((CellStyle) styleList.get(0));
|
|
}
|
|
// 填充数据
|
for (int i = 0; i < dataList.size(); i++) {
|
|
// 获取每一项的数据
|
Map data = (Map) dataList.get(i);
|
|
// 设置每一行的字段标题和数据
|
SXSSFRow rowi = sheet.createRow(i + 1);
|
|
for (int j = 0; j < data.size(); j++) {
|
|
// 判断是否是标题字段列
|
if (j == 0) {
|
|
rowi.createCell(j).setCellValue((String) data.get("value" + (j + 1)));
|
|
// 设置左边字段样式
|
sheet.getRow(i + 1).getCell(j).setCellStyle((CellStyle) styleList.get(0));
|
|
} else {
|
|
rowi.createCell(j).setCellValue(Double.valueOf((String) data.get("value" + (j + 1))));
|
|
// 设置数据样式
|
sheet.getRow(i + 1).getCell(j).setCellStyle((CellStyle) styleList.get(2));
|
|
}
|
|
}
|
|
}
|
|
return result;
|
|
}
|
|
|
/**
|
* 生成表格样式
|
*
|
* @return
|
*/
|
|
private static List tableStyle() {
|
|
List cellStyleList = new ArrayList();
|
|
// 样式准备
|
// 标题样式
|
CellStyle style = wb.createCellStyle();
|
|
style.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
|
|
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
|
|
style.setBorderBottom(BorderStyle.THIN); // 下边框
|
|
style.setBorderLeft(BorderStyle.THIN);// 左边框
|
|
style.setBorderTop(BorderStyle.THIN);// 上边框
|
|
style.setBorderRight(BorderStyle.THIN);// 右边框
|
|
style.setAlignment(HorizontalAlignment.CENTER);
|
|
cellStyleList.add(style);
|
|
CellStyle style1 = wb.createCellStyle();
|
|
style1.setBorderBottom(BorderStyle.THIN); // 下边框
|
|
style1.setBorderLeft(BorderStyle.THIN);// 左边框
|
|
style1.setBorderTop(BorderStyle.THIN);// 上边框
|
|
style1.setBorderRight(BorderStyle.THIN);// 右边框
|
|
style1.setAlignment(HorizontalAlignment.CENTER);
|
|
cellStyleList.add(style1);
|
|
CellStyle cellStyle = wb.createCellStyle();
|
|
cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
|
|
cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
|
|
cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
|
|
cellStyle.setBorderRight(BorderStyle.THIN);// 右边框
|
|
cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平对齐方式
|
|
// cellStyle.setVerticalAlignment(VerticalAlignment.TOP);//垂直对齐方式
|
cellStyleList.add(cellStyle);
|
|
return cellStyleList;
|
|
}
|
|
|
//https://blog.csdn.net/Lyq2017901206/article/details/126547542
|
|
}
|
