From cd7f83d5b2c9a36ad571e016d743ab61824dbfe5 Mon Sep 17 00:00:00 2001
From: whycxzp <glperry@163.com>
Date: 星期二, 10 六月 2025 16:44:50 +0800
Subject: [PATCH] 测试调整格式
---
src/main/java/com/whyc/service/ExcelService.java | 153 +++++++++++++++++++++++++++++++++++++-------------
1 files changed, 113 insertions(+), 40 deletions(-)
diff --git a/src/main/java/com/whyc/service/ExcelService.java b/src/main/java/com/whyc/service/ExcelService.java
index b78d4e6..dac33f0 100644
--- a/src/main/java/com/whyc/service/ExcelService.java
+++ b/src/main/java/com/whyc/service/ExcelService.java
@@ -572,22 +572,6 @@
CellRangeAddress region = new CellRangeAddress(0, 0, 0, columnCount-1);
sheet.addMergedRegion(region);
- //TODO 绗簩琛岀殑鍚堝苟鍒楁槸鏍规嵁鍙樺姩鐨�,鍚庣画璋冩暣
- CellRangeAddress region21 = new CellRangeAddress(1, 1, 0, 1);
- sheet.addMergedRegion(region21);
-
- CellRangeAddress region22 = new CellRangeAddress(1, 1, 3, 9);
- sheet.addMergedRegion(region22);
-
- CellRangeAddress region23 = new CellRangeAddress(1, 1, 10, 13);
- sheet.addMergedRegion(region23);
-
- CellRangeAddress region24 = new CellRangeAddress(1, 1, 14, 17);
- sheet.addMergedRegion(region24);
-
- CellRangeAddress region25 = new CellRangeAddress(1, 1, 18, 24);
- sheet.addMergedRegion(region25);
-
//绗笁琛�,绗簲琛�,绗叚琛岀殑鍚堝苟鍒�,鍓嶄袱涓崟鍏冩ā鍧楁槸鍥哄畾鐨�,鍏朵腑1涓悎骞跺崟鍏�
CellRangeAddress region31 = new CellRangeAddress(2, 2, 1, 2);
CellRangeAddress region32 = new CellRangeAddress(2, 2, 3, 4);
@@ -611,45 +595,56 @@
cellRow1.setCellStyle(style);
//绗簩琛屾槸鏀剧數鍩烘湰淇℃伅
+ //鍥犱负鏄姩鎬佺殑,娌″姙娉曞儚妯℃澘涓�鏍�.
+ //钃勭數姹犵粍鍙�:绗竴鍒�
+ //宸ヤ綔绁ㄤ俊鎭�:浜屼笁鍒�
+ //鏀剧數鏃ユ湡:绗洓鍒楄捣姝�-鏀剧數缁撴潫闈欑疆0.5h
+ //鏀剧數鐢垫祦,鏃堕暱,瀹归噺:鏀剧數缁撴潫鍚庨潤缃�0.5h璧峰埌鍏呮弧鐢�
+ //鐜娓╂箍搴�:澶囨敞
XSSFRow row1 = sheet.createRow(1);
XSSFCell cell1Row2 = row1.createCell(0);
cell1Row2.setCellValue("钃勭數姹犵粍鍙�:" + battInfo.getBattGroupName());
- XSSFCell cell2Row2 = row1.createCell(2);
- cell2Row2.setCellValue("");
- XSSFCell cell3Row2 = row1.createCell(3);
- cell3Row2.setCellValue("宸ヤ綔绁ㄥ彿锛�2273925\n" +
+ XSSFCell cell2Row2 = row1.createCell(1);
+ cell2Row2.setCellValue("宸ヤ綔绁ㄥ彿锛�2273925\n" +
"宸ヤ綔璐熻矗浜猴細椹簯鐕昞n" +
"宸ヤ綔鎴愬憳锛氬懆绱犳枃銆佹潕蹇楁爣銆佺帇鏈堢伩绛�");
- XSSFCell cell4Row2 = row1.createCell(10);
- cell4Row2.setCellValue("鏀剧數鏃ユ湡锛�2021.10.30\n" +
+ XSSFCell cell3Row2 = row1.createCell(3);
+ cell3Row2.setCellValue("鏀剧數鏃ユ湡锛�2021.10.30\n" +
"鏀剧數寮�濮嬫椂闂达細10:00\n" +
"鏀剧數缁撴潫鏃堕棿锛�19:47");
- XSSFCell cell5Row2 = row1.createCell(14);
- cell5Row2.setCellValue("鏀剧數鐢垫祦锛圓锛夛細35\n" +
+ XSSFCell cell4Row2 = row1.createCell(5);
+ cell4Row2.setCellValue("鏀剧數鐢垫祦锛圓锛夛細35\n" +
"鏀剧數鏃堕暱锛堟椂.鍒嗭級锛�600min\n" +
"鏀剧數瀹归噺锛圓h锛夛細300");
- XSSFCell cell6Row2 = row1.createCell(18);
- cell6Row2.setCellValue("鐜娓╁害锛堚剝锛夛細24.5\n" +
+ XSSFCell cell5Row2 = row1.createCell(columnCount-1);
+ cell5Row2.setCellValue("鐜娓╁害锛堚剝锛夛細24.5\n" +
"鐜婀垮害锛�%锛夛細46.7");
- //绗簩琛岃缃姞绮�
- //setRowStyle(sheet.getRow(1),cellStyleFontBold);
+
+ //浜岃鐨勫悎骞跺垪鏄牴鎹彉鍔ㄧ殑
+ row1.setHeightInPoints(57);
+ sheet.getRow(0).setHeightInPoints(42);
+ CellRangeAddress region22 = new CellRangeAddress(1, 1, 1, 2);
+ sheet.addMergedRegion(region22);
+
+ CellRangeAddress region23 = new CellRangeAddress(1, 1, 3, 4);
+ sheet.addMergedRegion(region23);
+
+ CellRangeAddress region24 = new CellRangeAddress(1, 1, 5, columnCount-2);
+ sheet.addMergedRegion(region24);
//绗笁琛屾槸鐢垫睜鍙峰拰鐢靛帇绛夌浉鍏虫姮澶�
//绗洓琛屾槸鐢靛帇鍒楀拰娓╁害鍒楁爣璇�
XSSFRow row2 = sheet.createRow(2);
XSSFRow row3 = sheet.createRow(3);
- XSSFRow row4 = sheet.createRow(4);
- XSSFRow row5 = sheet.createRow(5);
row2.createCell(0).setCellValue("钃勭數姹犲彿锛�#锛�");
row2.createCell(1).setCellValue("钃勭數姹犳诞鍏呯數鍘嬪�硷紙V锛�");
row2.createCell(3).setCellValue("鏀剧數鍓嶈搫鐢垫睜寮�璺數鍘嬪�硷紙V锛�");
- row2.createCell(4);
row3.createCell(0).setCellValue("椤圭洰");
row3.createCell(1).setCellValue("-1锛堢數鍘嬶級");
@@ -845,14 +840,10 @@
//rowTemp.createCell(4).setCellValue("鏀剧數鍓嶈搫鐢垫睜寮�璺俯搴﹀��:"+(j+1)+"#");
if(j == 0){
rowTemp.createCell(1).setCellValue(fcVolList.get(j).getGroupVol());
- rowTemp.createCell(2);
rowTemp.createCell(3).setCellValue(preVolList.get(j).getGroupVol());
- rowTemp.createCell(4);
}else if (j == 1){
rowTemp.createCell(1).setCellValue(fcVolList.get(j).getGroupCurr());
- rowTemp.createCell(2);
rowTemp.createCell(3).setCellValue(preVolList.get(j).getGroupCurr());
- rowTemp.createCell(4);
}else {
rowTemp.createCell(1).setCellValue(fcVolList.get(j).getMonVol());
rowTemp.createCell(2).setCellValue(fcVolList.get(j).getMonVol());
@@ -1066,6 +1057,16 @@
rowTemp.createCell(columnIndexTemp);
}
+ //璁剧疆鍓�6琛屾墍鏈夌殑琛ㄦ牸鍒涘缓,閬垮厤鍑虹幇鏃犺竟妗�
+ for (int i = 0; i < 6; i++) {
+ XSSFRow rowTmp = sheet.getRow(i);
+ for (int j = 0; j < columnCount; j++) {
+ if(rowTmp.getCell(j) == null){
+ rowTmp.createCell(j);
+ }
+ }
+ }
+
//=====================鏍煎紡璁剧疆=====================//
//璁剧疆鍒楀
for (int i = 0; i < columnCount; i++) {
@@ -1075,12 +1076,31 @@
addGlobalStylesToAllCells(sheet, workbook);
//鍒涘缓绗竴琛屾槸鏍囬琛�
- setRowStyle(workbook,sheet.getRow(0),true,15);
+ setRowStyle(workbook,sheet.getRow(0),true,20);
//绗簩琛岃缃姞绮�
- setRowStyle(workbook,sheet.getRow(1),true,15);
-
+ setRowStyle(workbook,sheet.getRow(1),true,11,HorizontalAlignment.LEFT);
+ //绗洓琛岃缃瓧浣撻鑹�
+ setRowStyle(workbook,sheet.getRow(3),false,9,IndexedColors.GREY_40_PERCENT.getIndex());
+ //绗�5琛屽埌鏈�鍚庝竴琛�,璁剧疆鏁板�肩殑灏忔暟鐐逛负4浣�
+ setDateFormat(workbook,sheet,4,113,1,columnCount-1,"0.0000");
//鐢熸垚鍥捐〃
createChart(sheet, columnCount, 6,"");
+ }
+
+ private void setDateFormat(Workbook workbook,XSSFSheet sheet, int rowIndexStart, int rowIndexEnd,int columnIndexStart,int columnIndexEnd, String formatStr) {
+ CellStyle decimalStyle = workbook.createCellStyle();
+ DataFormat format = workbook.createDataFormat();
+ decimalStyle.setDataFormat(format.getFormat(formatStr));
+ for (int i = rowIndexStart; i <= rowIndexEnd; i++) {
+ XSSFRow row = sheet.getRow(i);
+ for (int j = columnIndexStart; j <= columnIndexEnd; j++) {
+ XSSFCell cell = row.getCell(j);
+ if(cell.getCellType() == CellType.NUMERIC) {
+ cell.getCellStyle().setDataFormat(format.getFormat(formatStr));
+ }
+ }
+
+ }
}
private XSSFCellStyle getCellStyleFont(XSSFCellStyle cellStyleOriginal, XSSFWorkbook workbook, boolean isFontBold, int fontSize) {
@@ -1212,6 +1232,7 @@
//璁剧疆瀛椾綋鍜屽姞绮�
Font font = workbook.createFont();
font.setFontHeightInPoints((short) (fontSize));
+ font.setFontName("瀹嬩綋");
font.setBold(isFontBold);
cellStyleNew.setFont(font);
@@ -1219,8 +1240,51 @@
}
}
+ public void setRowStyle(Workbook workbook,Row row,boolean isFontBold,int fontSize,HorizontalAlignment horizontalAlignment) {
+ for (Cell cell : row) {
+ //鍏堝厠闅嗗師鏉ョ殑灞炴��
+ CellStyle cellStyleNew = workbook.createCellStyle();
+ cellStyleNew.cloneStyleFrom(cell.getCellStyle());
+
+ //璁剧疆瀛椾綋鍜屽姞绮�
+ Font font = workbook.createFont();
+ font.setFontHeightInPoints((short) (fontSize));
+ font.setBold(isFontBold);
+ font.setFontName("瀹嬩綋");
+
+ //璁剧疆姘村钩瀵归綈鏂瑰紡
+ cellStyleNew.setAlignment(horizontalAlignment);
+
+ cellStyleNew.setFont(font);
+ cell.setCellStyle(cellStyleNew);
+ }
+ }
+
+ public void setRowStyle(Workbook workbook,Row row,boolean isFontBold,int fontSize,short color) {
+ for (Cell cell : row) {
+ //璺宠繃绗竴鍒�
+ if(cell.getColumnIndex() == 0) {
+ continue;
+ }
+ //鍏堝厠闅嗗師鏉ョ殑灞炴��
+ CellStyle cellStyleNew = workbook.createCellStyle();
+ cellStyleNew.cloneStyleFrom(cell.getCellStyle());
+
+ //璁剧疆瀛椾綋鍜屽姞绮�
+ Font font = workbook.createFont();
+ font.setFontHeightInPoints((short) (fontSize));
+ font.setBold(isFontBold);
+ font.setFontName("瀹嬩綋");
+ font.setColor(color);
+
+ cellStyleNew.setFont(font);
+ cell.setCellStyle(cellStyleNew);
+ }
+ }
+
/**
- * 杩欎釜鏂规硶鏈�鍚庢墽琛�,鐢ㄤ簬瀵瑰凡琚垱寤虹殑琛屽唴鐨勬墍鏈夊崟鍏冩牸娣诲姞杈规.鑳借嚜鍔ㄨ瘑鍒鍒涘缓鐨勬墍鏈夊垪鍜屾墍鏈夊崟鍏冩牸
+ * 璁剧疆鍏ㄥ眬鐨勫睘鎬�.杩欎釜鏂规硶鍦ㄦ墍鏈夊崟鍏冩牸鍒涘缓鍚庤皟鐢�
+ * 鐢ㄤ簬瀵瑰凡琚垱寤虹殑琛屽唴鐨勬墍鏈夊崟鍏冩牸娣诲姞杈规.鑳借嚜鍔ㄨ瘑鍒鍒涘缓鐨勬墍鏈夊垪鍜屾墍鏈夊崟鍏冩牸
* @param sheet
* @param workbook
*/
@@ -1232,7 +1296,16 @@
borderedStyle.setBorderBottom(BorderStyle.THIN);
borderedStyle.setBorderLeft(BorderStyle.THIN);
borderedStyle.setBorderRight(BorderStyle.THIN);
-
+ Font font = workbook.createFont();
+ //榛樿瀛椾綋澶у皬涓�9,瀹嬩綋
+ font.setFontHeightInPoints((short) 9);
+ font.setFontName("瀹嬩綋");
+ borderedStyle.setFont(font);
+ //榛樿妯珫灞呬腑
+ borderedStyle.setAlignment(HorizontalAlignment.CENTER);
+ borderedStyle.setVerticalAlignment(VerticalAlignment.CENTER);
+ //榛樿鑷姩鎹㈣
+ borderedStyle.setWrapText(true);
for (Row row : sheet) {
for (Cell cell : row) {
cell.setCellStyle(borderedStyle);
--
Gitblit v1.9.1