1、导出大量数据到excel

  1. package com.sgcc.hpc.dc.util;
  3. import lombok.extern.slf4j.Slf4j;
  4. import org.apache.poi.ss.usermodel.*;
  5. import org.apache.poi.ss.util.CellRangeAddress;
  6. import org.apache.poi.xssf.streaming.SXSSFSheet;
  7. import org.apache.poi.xssf.streaming.SXSSFWorkbook;
  9. import java.io.FileOutputStream;
  10. import java.time.LocalDateTime;
  11. import java.time.format.DateTimeFormatter;
  12. import java.util.HashMap;
  13. import java.util.Map;
  14. import java.util.concurrent.*;
  15. import java.util.concurrent.atomic.AtomicInteger;
  18. /**
  19.  * @Author: 李孟帅
  20.  * @CreateTime: 2021/5/11$ 10:35$
  21.  * @Description: TODO
  22.  */
  23. @Slf4j
  24. public class Poi {
  26.     private final static DateTimeFormatter PATTERN = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS");
  27.     private final SXSSFWorkbook workbook;
  28.     private SXSSFSheet sheet;
  30.     private final String fileName;
  32.     private final AtomicInteger size = new AtomicInteger(0);
  34.     public Poi(String fileName) {
  35.         this.fileName = fileName;
  36.         // 1.创建工作簿
  37.         // 阈值，内存中的对象数量最大值，超过这个值会生成一个临时文件存放到硬盘中
  38.         workbook = new SXSSFWorkbook(1000);
  39.     }
  41.     public AtomicInteger getSize() {
  42.         return size;
  43.     }
  45.     public void createSheet() {
  46.         createSheet("sheet" + workbook.getNumberOfSheets());
  47.     }
  49.     public void createSheet(String sheetName) {
  50.         //2.在Workbook中添加一个sheet,对应Excel文件中的sheet
  51.         sheet = workbook.createSheet(sheetName);
  52.         //3.设置样式以及字体样式
  53.         CellStyle titleCellStyle = createTitleCellStyle();
  54.         CellStyle headCellStyle = createHeadCellStyle();
  56.         //4.创建标题、表头，内容和合并单元格等操作
  57.         int rowNum = 0;// 行号
  58.         // 创建第一行，索引从0开始
  59.         Row row0 = this.sheet.createRow(rowNum++);
  60.         row0.setHeight((short) 800);// 设置行高
  61.         String title = "这里是标题标题标题";
  62.         Cell c00 = row0.createCell(0);
  63.         c00.setCellValue(title);
  64.         c00.setCellStyle(titleCellStyle);
  65.         // 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
  66.         this.sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
  68.         // 第二行
  69.         Row row1 = this.sheet.createRow(rowNum++);
  70.         row1.setHeight((short) 500);
  71.         String[] row_first = {"采集点的标识", "采集点的值", "采集时间"};
  72.         for (int i = 0; i < row_first.length; i++) {
  73.             Cell tempCell = row1.createCell(i);
  74.             tempCell.setCellValue(row_first[i]);
  75.             tempCell.setCellStyle(headCellStyle);
  76.         }
  78.     }
  80.     private CellStyle createTitleCellStyle() {
  81.         CellStyle cellStyle = createCellStyle();
  82.         cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.index);
  83.         cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  84.         return cellStyle;
  85.     }
  87.     private CellStyle createHeadCellStyle() {
  88.         CellStyle cellStyle = createCellStyle();
  89.         cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
  90.         cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  91.         return cellStyle;
  92.     }
  94.     private CellStyle createCellStyle() {
  95.         CellStyle cellStyle = workbook.createCellStyle();
  96.         cellStyle.setAlignment(HorizontalAlignment.CENTER);
  97.         cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
  99.         cellStyle.setBorderBottom(BorderStyle.THIN);
  100.         cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
  101.         cellStyle.setBorderLeft(BorderStyle.THIN);
  102.         cellStyle.setLeftBorderColor(IndexedColors.BLACK.index);
  103.         cellStyle.setBorderRight(BorderStyle.THIN);
  104.         cellStyle.setRightBorderColor(IndexedColors.BLACK.index);
  105.         cellStyle.setBorderTop(BorderStyle.THIN);
  106.         cellStyle.setTopBorderColor(IndexedColors.BLACK.index);
  108.         return cellStyle;
  109.     }
  111.     public void write(BlockingQueue<Map<String, String>> queue) {
  112.         try {
  113.             // int rowNum = this.sheet.getLastRowNum() + 1;
  114.             while (true) {
  115.                 Map<String, String> take = queue.take();
  116.                 String code = take.get("code");
  117.                 SXSSFSheet sheet = workbook.getSheet(code);
  118.                 if (sheet == null) {
  119.                     createSheet(code);
  120.                 }
  121.                 sheet = workbook.getSheet(code);
  122.                 int rowNum = sheet.getLastRowNum() + 1;
  123.                 Row tempRow = sheet.createRow(rowNum);
  124.                 tempRow.setHeight((short) 500);
  125.                 // 循环单元格填入数据
  126.                 for (int j = 0; j < 3; j++) {
  127.                     Cell tempCell = tempRow.createCell(j);
  128.                     tempCell.setCellStyle(createCellStyle());
  129.                     String tempValue;
  130.                     if (j == 0) {
  131.                         tempValue = take.get("code");
  132.                     } else if (j == 1) {
  133.                         tempValue = take.get("value");
  134.                     } else {
  135.                         tempValue = take.get("time");
  136.                     }
  137.                     tempCell.setCellValue(tempValue);
  138.                     //设置列宽，必须在单元格设值以后进行
  139.                     sheet.setColumnWidth(0, 4000);
  140.                     sheet.setColumnWidth(1, 3000);
  141.                     sheet.setColumnWidth(2, 3000);
  142.                 }
  143.                 int num = this.size.incrementAndGet();
  144.                 if (num == 10000) {
  145.                     break;
  146.                 }
  147.             }
  149.         } catch (InterruptedException e) {
  150.             log.warn(e.getMessage());
  151.         }
  153.     }
  155.     public void flush() {
  156.         //导出
  157.         try {
  158.             FileOutputStream outputStream = new FileOutputStream(fileName);
  159.             workbook.write(outputStream);
  160.             workbook.close();
  161.             outputStream.close();
  163.         } catch (Exception e) {
  164.             e.printStackTrace();
  165.         }
  166.     }
  168.     public static void main(String[] args) throws InterruptedException {
  171.         LocalDateTime now = LocalDateTime.now();
  172.         ThreadPoolExecutor executor = new ThreadPoolExecutor(1, 1, 30, TimeUnit.SECONDS, new LinkedBlockingDeque<>());
  173.         // ArrayBlockingQueue<Map<String, String>> queue = new ArrayBlockingQueue<>(1024);
  174.         LinkedBlockingQueue<Map<String, String>> queue = new LinkedBlockingQueue<>(10240);
  176.         ScheduledThreadPoolExecutor scheduledThreadPoolExecutor = new ScheduledThreadPoolExecutor(1);
  177.         AtomicInteger index = new AtomicInteger(0);
  178.         String basePath = "C:\\Users\\lms\\Desktop\\";
  179.         scheduledThreadPoolExecutor.scheduleAtFixedRate(() -> {
  180.             // 当文件个数超过10个时，会覆盖旧的文件
  181.             if (index.get()==10){
  182.                 index.set(0);
  183.             }
  184.             String fileName = basePath + "报表文件名称-" + index.incrementAndGet() + ".xlsx";
  185.             Poi poi = new Poi(fileName);
  186.             long start = System.currentTimeMillis();
  187.             poi.write(queue);
  188.             log.warn("数据10000条了，开始写入磁盘！");
  189.             poi.flush();
  190.             log.warn("写入excel时间：{}ms,队列中还有元素个数：{}", System.currentTimeMillis() - start, queue.size());
  194.         }, 10, 1000, TimeUnit.MILLISECONDS);
  197.         while (true) {
  198.             int count = 1;
  199.             for (int i = 0; i < 10; i++) {
  200.                 HashMap<String, String> map = new HashMap<>();
  201.                 String format = now.plusSeconds(count++).format(PATTERN);
  202.                 map.put("code", "inputRegister-1");
  203.                 map.put("value", String.valueOf(i * 10 + 0.23));
  204.                 map.put("time", format);
  205.                 queue.put(map);
  206.             }
  207.             for (int i = 8; i >= 0; i--) {
  208.                 HashMap<String, String> map = new HashMap<>();
  209.                 String format = now.plusSeconds(count++).format(PATTERN);
  210.                 map.put("code", "inputRegister-2");
  211.                 map.put("value", String.valueOf(i * 10 + 0.23));
  212.                 map.put("time", format);
  213.                 queue.put(map);
  214.             }
  215.             Thread.sleep(10);
  217.             // break;
  219.         }
  222.     }
  223. }

2、绘制xy散点图

  1. package com.sgcc.lms.easypoi.util;
  3. import org.apache.poi.openxml4j.util.ZipSecureFile;
  4. import org.apache.poi.ss.usermodel.ClientAnchor;
  5. import org.apache.poi.ss.util.CellRangeAddress;
  6. import org.apache.poi.xddf.usermodel.XDDFSolidFillProperties;
  7. import org.apache.poi.xddf.usermodel.chart.*;
  8. import org.apache.poi.xssf.usermodel.XSSFChart;
  9. import org.apache.poi.xssf.usermodel.XSSFDrawing;
  10. import org.apache.poi.xssf.usermodel.XSSFSheet;
  11. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  13. import java.io.File;
  14. import java.io.FileInputStream;
  15. import java.io.FileOutputStream;
  16. import java.io.InputStream;
  18. public class XSSFUtils {
  20.     public static void main(String[] args) throws Exception {
  22.         String tmpFileName = "C:\\Users\\lms\\Desktop\\RTU采集数据_ankong_5.xlsx";
  23.         File file = new File(tmpFileName);
  24.         System.out.println(file.exists());
  25.         InputStream fis = new FileInputStream(file);
  26.         ZipSecureFile.setMinInflateRatio(-1.0d);
  27.         XSSFWorkbook wb = new XSSFWorkbook(fis);
  28.         // SXSSFWorkbook workbook = new SXSSFWorkbook(wb);
  29.         XSSFSheet sheet = wb.getSheetAt(0);
  31.         //创建一个画布
  32.         XSSFDrawing drawing = sheet.createDrawingPatriarch();
  33.         //前四个默认0，[5,20]：从5列0行开始;[0,15]:宽度15个单元格，15向下扩展到第15行   左上角[0,5] -> 右下角[15,20]
  34.         ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 5, 1, 20, 15);
  35.         //创建一个chart对象
  36.         XSSFChart chart = drawing.createChart(anchor);
  37.         //标题
  38.         chart.setTitleText("code");
  39.         //标题覆盖
  40.         chart.setTitleOverlay(false);
  41.         //图例位置
  42.         XDDFChartLegend legend = chart.getOrAddLegend();
  43.         legend.setPosition(LegendPosition.TOP);
  45.         //分类轴标(X轴),标题位置
  46.         //****** 如果是多条线，且X轴时数值类型的，切记：：：：要用XDDFValueAxis
  47.         XDDFValueAxis bottomAxis = chart.createValueAxis(AxisPosition.TOP);
  48.         bottomAxis.setTitle("time");
  49.         XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.BOTTOM);
  50.         leftAxis.setTitle("value");
  52.         //CellRangeAddress(起始行号，终止行号， 起始列号，终止列号）
  53.         //分类轴标(X轴)数据，单元格范围位置[0, 0]到[0, 6]
  54.         XDDFNumericalDataSource<Double> x = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(2, 1600, 2, 2));
  55.         XDDFNumericalDataSource<Double> y = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(2, 1600, 1, 1));
  57.         //scatter：XY散点图
  58.         XDDFScatterChartData scatter = (XDDFScatterChartData) chart.createData(ChartTypes.SCATTER, bottomAxis, leftAxis);
  60.         // 设置y轴刻度的样式
  61.         leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
  62.         leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
  64.         XDDFScatterChartData.Series series1 = (XDDFScatterChartData.Series) scatter.addSeries(x, y);
  65.         //条形图例标题
  66.         series1.setTitle("code", null);
  67.         XDDFSolidFillProperties fill = new XDDFSolidFillProperties();
  68.         //条形图，填充颜色
  69.         series1.setFillProperties(fill);
  70.         //绘制
  71.         chart.plot(scatter);
  72.         FileOutputStream fileOut = new FileOutputStream(tmpFileName);
  73.         wb.write(fileOut);
  74.         wb.close();
  75.     }
  76. }