excel

package com.enjoyit.pos.common.util;

import com.enjoyit.pos.common.CommonLog;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileOutputStream;
import java.util.List;

/**
 *  <dependency>
 *             <groupId>org.apache.poi</groupId>
 *             <artifactId>poi-ooxml-schemas</artifactId>
 *             <version>4.1.1</version>
 *         </dependency>
 *
 *         <dependency>
 *             <groupId>org.apache.commons</groupId>
 *             <artifactId>commons-collections4</artifactId>
 *             <version>4.1</version>
 *         </dependency>
 *         <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
 *         <dependency>
 *             <groupId>org.apache.poi</groupId>
 *             <artifactId>poi-ooxml</artifactId>
 *             <version>4.1.1</version>
 *         </dependency>
 *         <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
 *         <dependency>
 *             <groupId>org.apache.poi</groupId>
 *             <artifactId>poi</artifactId>
 *             <version>4.1.1</version>
 *         </dependency>
 */
public class PosExcelUtilXlsx {

    /**
     * 数据写入
     *
     * @param title    表格标题
     * @param colHead  列头（表头）
     * @param dataList 数据列表
     * @return 返回表格
     */
    public <T> XSSFWorkbook createExcel(String title, String[] colHead, List<Object[]> dataList) {
        // 创建一个表格
        XSSFWorkbook wb = new XSSFWorkbook();
        // 创建工作页并赋名
        XSSFSheet sheet = wb.createSheet("Sheet1");
        // 创建标题
        XSSFRow titleRow = sheet.createRow(0);
        XSSFCell titleCell = titleRow.createCell(0);
        // 设置标题高度
        titleRow.setHeight((short) (20 * 25));
        // 给标题设置样式
        XSSFCellStyle titleStyle = this.getTitleStyle(wb);
        // 给列头（表头）设置样式
        XSSFCellStyle colHeadStyle = this.getColHeadStyle(wb);
        // 给数据列表设置样式
        XSSFCellStyle dataListStyle = this.getDataListStyle(wb);
        // 合并单元格 起始行 截至列 起始列 截至行
//        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, dataList.get(0).length - 1));
        CellRangeAddress titleMerge = new CellRangeAddress(0, 0, 0, dataList.get(0).length - 1);
        sheet.addMergedRegion(titleMerge);
        RegionUtil.setBorderTop(BorderStyle.THIN, titleMerge, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, titleMerge, sheet);
        // 给标题样式附上
        titleCell.setCellStyle(titleStyle);
        // 给标题赋值
        titleCell.setCellValue(title);
        // 创建表头行
        XSSFRow colHeadRow = sheet.createRow(1);
        // 设置表头行高度
        colHeadRow.setHeight((short) (15 * 20));
        // 给表头设置样式并赋值
        for (int i = 0; i < colHead.length; i++) {
            XSSFCell colHeadCell = colHeadRow.createCell(i);
            colHeadCell.setCellStyle(colHeadStyle);
            colHeadCell.setCellValue(colHead[i]);
        }
        // 将查询到的数据赋到列表
        for (int i = 0; i < dataList.size(); i++) {
            // 获取每一行的元素
            Object[] data = dataList.get(i);
            // 创建行
            XSSFRow dataRow = sheet.createRow(i + 2);
            // 循环当前行的列元素设置样式并赋值
            for (int j = 0; j < data.length; j++) {
                XSSFCell dataRowCell = dataRow.createCell(j);
                dataRowCell.setCellStyle(dataListStyle);
                dataRowCell.setCellValue(data[j].toString());
                // 自适应列宽
                sheet.autoSizeColumn(j);
                sheet.setColumnWidth(j, sheet.getColumnWidth(j));
            }
        }
        return wb;
    }

    /**
     * 接口调用
     *
     * @param title    表格标题
     * @param colHead  列头（表头）
     * @param dataList 数据列表
     */
    public void exportExcel(String title, String[] colHead, List<Object[]> dataList, String fileName) {
        try {
            XSSFWorkbook result = createExcel(title, colHead, dataList);
            FileOutputStream fileOutputStream = new FileOutputStream(fileName);
            result.write(fileOutputStream);
            fileOutputStream.close();
        } catch (Exception e) {
            CommonLog.getInstance().error(e);
        }
    }

    /**
     * 设置标题样式
     *
     * @param workbook 表格
     * @return 样式
     */
    public XSSFCellStyle getTitleStyle(XSSFWorkbook workbook) {
        // 设置字体
        XSSFFont font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 15);
        // 设置字体加粗
        font.setBold(true);
        // 设置字体样式
        font.setFontName("Courier New");
        // 设置样式;
        XSSFCellStyle style = workbook.createCellStyle();
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        // 设置垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置单元格背景颜色
        style.setFillForegroundColor(IndexedColors.BLACK.getIndex());
        return style;
    }

    /**
     * 给列头设置样式
     *
     * @param workbook 表格
     * @return 样式
     */
    public XSSFCellStyle getColHeadStyle(XSSFWorkbook workbook) {
        // 设置字体
        XSSFFont font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 12);
        // 设置字体样式
        font.setFontName("Courier New");
        // 设置字体加粗
        font.setBold(true);
        // 设置样式;
        XSSFCellStyle style = workbook.createCellStyle();
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        // 设置垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 上边框
        style.setBorderTop(BorderStyle.THIN);
        // 下边框
        style.setBorderBottom(BorderStyle.THIN);
        // 左边框
        style.setBorderLeft(BorderStyle.THIN);
        // 右边框
        style.setBorderRight(BorderStyle.THIN);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置单元格背景颜色
        style.setFillForegroundColor(IndexedColors.BLACK.getIndex());
        return style;
    }

    /**
     * 给数据列表设置样式
     *
     * @param workbook 表格
     * @return 样式
     */
    public XSSFCellStyle getDataListStyle(XSSFWorkbook workbook) {
        // 设置字体
        XSSFFont font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 13);
        // 设置字体样式
        font.setFontName("Courier New");
        // 设置样式;
        XSSFCellStyle style = workbook.createCellStyle();
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        // 设置垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 上边框
        style.setBorderTop(BorderStyle.THIN);
        // 下边框
        style.setBorderBottom(BorderStyle.THIN);
        // 左边框
        style.setBorderLeft(BorderStyle.THIN);
        // 右边框
        style.setBorderRight(BorderStyle.THIN);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置单元格背景颜色
        style.setFillForegroundColor(IndexedColors.BLACK.getIndex());
        return style;
    }
}

　　

public static void main(String[] args) {
        BufferedReader bufferedReader = PosFileUtils.readFile("D:\\1.txt");
        PosExcelUtilXlsx posExcelUtilXlsx = new PosExcelUtilXlsx();
        String[] title = new String[15];
        List<Object[]> contextList = new ArrayList<>();
        title[0] = "序号";
        title[1] = "日期";
        title[2] = "相差时间";
        title[3] = "年轻代总内存(M)";
        title[4] = "gc前年轻代用量(M)";
        title[5] = "gc后年轻代用量(M)";
        title[6] = "gc耗时";
        title[7] = "堆总内存(M)";
        title[8] = "gc前堆内存用量(M)";
        title[9] = "gc后堆内存用量(M)";
        title[10] = "gc耗时";
        title[11] = "总耗时";
        title[12] = "老年代总内存(M)";
        title[13] = "gc前老年代用量(M)";
        title[14] = "gc后老年代用量(M)";
        try {
            String readLine;
            String showDate = null;
            int in = 1;
            while (null != (readLine = bufferedReader.readLine())) {
                if (!readLine.contains("DefNew")) {
                    continue;
                }
                String[] context = new String[15];
                String date = readLine.substring(0, readLine.indexOf("+0800:")).replace("T", " ");
                String defNew = readLine.substring(readLine.indexOf("DefNew: ") + 8, readLine.indexOf("K->"));
                String gcDefNew = readLine.substring(readLine.indexOf("K->") + 3, readLine.indexOf("K("));
                String totalDefNew = readLine.substring(readLine.indexOf("K(") + 2, readLine.indexOf("K),"));
                String gcDefNewTime = readLine.substring(readLine.indexOf("), ") + 3, readLine.indexOf(" secs"));

                String heap = readLine.substring(readLine.indexOf("secs] ") + 6, readLine.indexOf("K->", readLine.indexOf("K->") + 1));
                String gcHeap = readLine.substring(readLine.indexOf("K->", readLine.indexOf("K->") + 1) + 3, readLine.indexOf("K(", readLine.indexOf("K(") + 1));
                String totalHeap = readLine.substring(readLine.indexOf("K(", readLine.indexOf("K(") + 1) + 2, readLine.indexOf("K),", readLine.indexOf("K),") + 1));
                String gcHeapTime = readLine.substring(readLine.indexOf("), ", readLine.indexOf("), ") + 1) + 3, readLine.indexOf(" secs", readLine.indexOf(" secs") + 1));
                String realContext = readLine.substring(readLine.indexOf("real=") + 5);
                String real = realContext.substring(0, realContext.indexOf(" secs"));
                String divDate = "0.00";
                String dateFormatter = DateFormatter.HYPHEN_YYYY_MM_DD_HH_MM_SS.getFormatter();
                SimpleDateFormat sdf = new SimpleDateFormat(dateFormatter);
                Date dateParse = sdf.parse(date);
                if (CommonMethod.isNotEmpty(showDate)) {
                    Date showDateParse = sdf.parse(showDate);
                    divDate = ConvertUtil.toString((dateParse.getTime() - showDateParse.getTime()) / 1000);
                }
                showDate = date;
                context[0] = ConvertUtil.toString(in);
                context[1] = date;
                context[2] = ConvertUtil.toString(divDate);
                context[3] = ConvertUtil.toString(PrecisionUtils.div(ConvertUtil.toDouble(totalDefNew), 1024));
                context[4] = ConvertUtil.toString(PrecisionUtils.div(ConvertUtil.toDouble(defNew), 1024));
                context[5] = ConvertUtil.toString(PrecisionUtils.div(ConvertUtil.toDouble(gcDefNew), 1024));
                context[6] = gcDefNewTime;
                context[7] = ConvertUtil.toString(PrecisionUtils.div(ConvertUtil.toDouble(totalHeap), 1024));
                context[8] = ConvertUtil.toString(PrecisionUtils.div(ConvertUtil.toDouble(heap), 1024));
                context[9] = ConvertUtil.toString(PrecisionUtils.div(ConvertUtil.toDouble(gcHeap), 1024));
                context[10] = gcHeapTime;
                context[11] = real;
                context[12] = ConvertUtil.toString(PrecisionUtils.div(PrecisionUtils.sub(ConvertUtil.toDouble(totalHeap), ConvertUtil.toDouble(totalDefNew)), 1024));
                context[13] = ConvertUtil.toString(PrecisionUtils.div(PrecisionUtils.sub(ConvertUtil.toDouble(heap), ConvertUtil.toDouble(defNew)), 1024));
                context[14] = ConvertUtil.toString(PrecisionUtils.div(PrecisionUtils.sub(ConvertUtil.toDouble(gcHeap), ConvertUtil.toDouble(gcDefNew)), 1024));
                in++;
                contextList.add(context);
            }
            posExcelUtilXlsx.exportExcel("GC数据分析表", title, contextList, "D:\\gc.xlsx");
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }