smartor.git - Gitblit

			@@ -1,14 +1,11 @@
			package com.ruoyi.common.utils.poi;

			import java.io.File;
			import java.io.FileOutputStream;
			import java.io.IOException;
			import java.io.InputStream;
			import java.io.OutputStream;
			import java.io.*;
			import java.lang.reflect.Field;
			import java.lang.reflect.Method;
			import java.lang.reflect.ParameterizedType;
			import java.math.BigDecimal;
			import java.net.URLEncoder;
			import java.text.DecimalFormat;
			import java.time.LocalDate;
			import java.time.LocalDateTime;
			@@ -23,8 +20,10 @@
			import java.util.Set;
			import java.util.UUID;
			import java.util.stream.Collectors;
			import javax.servlet.http.HttpServletRequest;
			import javax.servlet.http.HttpServletResponse;

			import com.ruoyi.common.utils.ServletUtils;
			import org.apache.commons.lang3.ArrayUtils;
			import org.apache.commons.lang3.RegExUtils;
			import org.apache.commons.lang3.reflect.FieldUtils;
			@@ -39,14 +38,9 @@
			import org.apache.poi.ss.util.CellRangeAddress;
			import org.apache.poi.ss.util.CellRangeAddressList;
			import org.apache.poi.util.IOUtils;
			import org.apache.poi.xddf.usermodel.chart.*;
			import org.apache.poi.xssf.streaming.SXSSFWorkbook;
			import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
			import org.apache.poi.xssf.usermodel.XSSFDataValidation;
			import org.apache.poi.xssf.usermodel.XSSFDrawing;
			import org.apache.poi.xssf.usermodel.XSSFPicture;
			import org.apache.poi.xssf.usermodel.XSSFShape;
			import org.apache.poi.xssf.usermodel.XSSFSheet;
			import org.apache.poi.xssf.usermodel.XSSFWorkbook;
			import org.apache.poi.xssf.usermodel.*;
			import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
			import org.slf4j.Logger;
			import org.slf4j.LoggerFactory;
			@@ -445,6 +439,118 @@
			response.setCharacterEncoding("utf-8");
			this.init(list, sheetName, title, Type.EXPORT);
			exportExcel(response);
			}

			/**
			* 导出包含两个Sheet的Excel
			* Sheet1: 基于当前泛型类型T的注解导出
			* Sheet2: 基于传入的clazz2类型的注解导出
			*
			* @param response 返回数据
			* @param list1 第一个Sheet的数据集合
			* @param sheetName1 第一个Sheet的名称
			* @param list2 第二个Sheet的数据集合
			* @param sheetName2 第二个Sheet的名称
			* @param clazz2 第二个Sheet数据对象的Class
			*/
			public void exportExcelTwoSheet(HttpServletResponse response,
			List<T> list1, String sheetName1,
			List<?> list2, String sheetName2, Class<?> clazz2) {
			response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
			response.setCharacterEncoding("utf-8");
			// 初始化并写入Sheet1
			this.init(list1, sheetName1, StringUtils.EMPTY, Type.EXPORT);
			writeSheet();
			// 追加写入Sheet2
			writeAdditionalSheet(list2, sheetName2, clazz2);
			// 输出到响应流
			try {
			wb.write(response.getOutputStream());
			} catch (Exception e) {
			log.error("导出Excel异常{}", e.getMessage());
			} finally {
			IOUtils.closeQuietly(wb);
			}
			}

			/**
			* 向当前工作簿追加写入一个Sheet
			*
			* @param dataList 数据集合
			* @param sheetName Sheet名称
			* @param dataClazz 数据对象的Class
			*/
			@SuppressWarnings("unchecked")
			public void writeAdditionalSheet(List<?> dataList, String sheetName, Class<?> dataClazz) {
			if (dataList == null) {
			dataList = new ArrayList<>();
			}
			// 保存原始状态
			Class<T> originClazz = this.clazz;
			List<Object[]> originFields = this.fields;
			List<T> originList = this.list;
			String originSheetName = this.sheetName;
			int originRownum = this.rownum;
			List<Field> originSubFields = this.subFields;
			Method originSubMethod = this.subMethod;
			int originSubMergedFirstRowNum = this.subMergedFirstRowNum;
			int originSubMergedLastRowNum = this.subMergedLastRowNum;
			Map<Integer, Double> originStatistics = this.statistics;
			short originMaxHeight = this.maxHeight;

			// 设置新类型
			this.clazz = (Class<T>) dataClazz;
			this.list = (List<T>) dataList;
			this.sheetName = sheetName;
			this.rownum = 0;
			this.subFields = null;
			this.subMethod = null;
			this.subMergedFirstRowNum = 1;
			this.subMergedLastRowNum = 0;
			this.statistics = new HashMap<Integer, Double>();

			// 创建新Sheet
			this.sheet = wb.createSheet();
			wb.setSheetName(wb.getNumberOfSheets() - 1, sheetName);

			// 重新创建字段
			createExcelField();

			// 补充新字段的样式
			this.styles.putAll(annotationHeaderStyles(wb, this.styles));
			this.styles.putAll(annotationDataStyles(wb));

			// 写入数据
			writeSheet();

			// 恢复原始状态
			this.clazz = originClazz;
			this.fields = originFields;
			this.list = originList;
			this.sheetName = originSheetName;
			this.rownum = originRownum;
			this.subFields = originSubFields;
			this.subMethod = originSubMethod;
			this.subMergedFirstRowNum = originSubMergedFirstRowNum;
			this.subMergedLastRowNum = originSubMergedLastRowNum;
			this.statistics = originStatistics;
			this.maxHeight = originMaxHeight;
			}

			/**
			* 对list数据源将其里面的数据导入到excel表单（带文件名）
			*
			* @param response 返回数据
			* @param list 导出数据集合
			* @param sheetName 工作表的名称
			* @param fileName 导出文件的名称
			* @return 结果
			*/
			public void exportExcelWithFileName(HttpServletResponse response, List<T> list, String sheetName, String fileName) {
			//设置文件名
			HttpServletRequest request = ServletUtils.getRequest();
			setExcelFileName(response, request, fileName);
			exportExcel(response, list, sheetName, StringUtils.EMPTY);
			}

			/**
			@@ -1432,4 +1538,169 @@
			}
			return method;
			}

			/**
			* 设置导出Excel文件名称
			* @param response 返回数据
			* @param request 浏览器请求头
			* @param fileName 文件名称
			*/
			public void setExcelFileName(HttpServletResponse response, HttpServletRequest request, String fileName) {
			try {
			//会清空响应缓冲区的内容，确保后续输出不会受到之前数据的影响
			response.reset();
			// 定义浏览器响应表头，顺带定义下载名
			fileName = fileName + ".xlsx";
			// 对文件名进行URL编码，支持中文文件名
			String encodedFileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
			// 使用 filename* 参数支持 UTF-8 编码（RFC 5987），同时保留 filename 参数兼容旧浏览器
			response.setHeader("Content-Disposition", "attachment; filename=\"" + encodedFileName +
			"\"; filename*=utf-8''" + encodedFileName);
			// 定义下载的类型，标明是excel文件
			response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
			response.setCharacterEncoding("utf-8");
			}catch (Exception e){
			log.error("{}设置导出Excel文件名称异常,{}", fileName, e.getMessage());
			}
			}

			// public static void main(String[] args) {
			// // 1. 创建工作簿
			// try (XSSFWorkbook workbook = new XSSFWorkbook()) {
			// // 2. 生成柱状图 Sheet
			// createBarChartSheet(workbook);
			// // 3. 生成折线图 Sheet
			// createLineChartSheet(workbook);
			// // 4. 生成饼状图 Sheet
			// createPieChartSheet(workbook);
			//
			// // 5. 导出到本地文件
			// try (FileOutputStream fos = new FileOutputStream("D:/图表导出.xlsx")) {
			// workbook.write(fos);
			// System.out.println("Excel 图表导出成功！路径：D:/图表导出.xlsx");
			// }
			// } catch (IOException e) {
			// e.printStackTrace();
			// }
			// }

			// ==================== 1. 创建柱状图 Sheet ====================
			private static void createBarChartSheet(XSSFWorkbook workbook) {
			XSSFSheet sheet = workbook.createSheet("柱状图");
			writeChartData(sheet);

			XSSFDrawing drawing = sheet.createDrawingPatriarch();
			XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 3, 1, 12, 15);
			XSSFChart chart = drawing.createChart(anchor);
			chart.setTitleText("产品销量-柱状图");
			chart.setTitleOverlay(false);

			// 坐标轴
			XDDFCategoryAxis xAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
			XDDFValueAxis yAxis = chart.createValueAxis(AxisPosition.LEFT);
			yAxis.setCrosses(AxisCrosses.AUTO_ZERO);

			// 数据范围：分类A2:A6，数值B2:B6
			XDDFDataSource<String> cat = XDDFDataSourcesFactory.fromStringCellRange(sheet,
			new CellRangeAddress(1, 5, 0, 0));
			XDDFNumericalDataSource<Double> val = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
			new CellRangeAddress(1, 5, 1, 1));

			// 柱状图数据
			XDDFBarChartData barChart = (XDDFBarChartData) chart.createData(ChartTypes.BAR, xAxis, yAxis);
			XDDFBarChartData.Series series = (XDDFBarChartData.Series) barChart.addSeries(cat, val);
			series.setTitle("销量", null);
			barChart.setBarDirection(BarDirection.COL);

			// 绘制（关键：缺失就不显示图表）
			chart.plot(barChart);
			}

			// ==================== 2. 创建折线图 Sheet ====================
			private static void createLineChartSheet(XSSFWorkbook workbook) {
			XSSFSheet sheet = workbook.createSheet("折线图");
			// 写入表头 + 数据
			writeChartData(sheet);

			// 绘图区域
			XSSFDrawing drawing = sheet.createDrawingPatriarch();
			XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 20);
			XSSFChart chart = drawing.createChart(anchor);

			// 图表标题
			chart.setTitleText("月度销售额折线图");
			chart.setTitleOverlay(false);

			// 图例
			XDDFChartLegend legend = chart.getOrAddLegend();
			legend.setPosition(LegendPosition.TOP_RIGHT);

			// X轴、Y轴
			XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
			bottomAxis.setTitle("月份");
			XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
			leftAxis.setTitle("销售额（万元）");

			// 数据范围
			XDDFDataSource<String> months = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(0, 5, 0, 0));
			XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(0, 5, 1, 1));

			// 创建折线图
			XDDFLineChartData lineData = (XDDFLineChartData) chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);
			XDDFLineChartData.Series series = (XDDFLineChartData.Series) lineData.addSeries(months, values);
			series.setTitle("销售额", null);
			series.setMarkerStyle(MarkerStyle.CIRCLE); // 显示圆点

			chart.plot(lineData);
			}

			// ========== 新增：饼状图 ==========
			private static void createPieChartSheet(XSSFWorkbook workbook) {
			XSSFSheet sheet = workbook.createSheet("饼状图");
			writeChartData(sheet);

			XSSFDrawing drawing = sheet.createDrawingPatriarch();
			XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 3, 1, 12, 15);
			XSSFChart chart = drawing.createChart(anchor);
			chart.setTitleText("产品占比-饼状图");
			chart.setTitleOverlay(false);

			// 饼图不需要XY轴
			XDDFDataSource<String> cat = XDDFDataSourcesFactory.fromStringCellRange(sheet,
			new CellRangeAddress(1, 5, 0, 0));
			XDDFNumericalDataSource<Double> val = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
			new CellRangeAddress(1, 5, 1, 1));

			// 饼图数据
			XDDFPieChartData pieChart = (XDDFPieChartData) chart.createData(ChartTypes.PIE, null, null);
			pieChart.addSeries(cat, val);
			chart.plot(pieChart);
			}

			// ==================== 公共方法：写入图表数据 ====================
			private static void writeChartData(XSSFSheet sheet) {
			Object[][] data = {
			{"分类", "数值"},
			{"产品A", 35},
			{"产品B", 28},
			{"产品C", 42},
			{"产品D", 19},
			{"产品E", 26}
			};
			int rowIdx = 0;
			for (Object[] rows : data) {
			Row row = sheet.createRow(rowIdx++);
			int cellIdx = 0;
			for (Object val : rows) {
			if (val instanceof String) {
			row.createCell(cellIdx++).setCellValue((String) val);
			} else if (val instanceof Number) {
			row.createCell(cellIdx++).setCellValue(((Number) val).doubleValue());
			}
			}
			}
			sheet.autoSizeColumn(0);
			sheet.autoSizeColumn(1);
			}

			}