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);
}
}
