package com.artfess.yhxt.util; import com.artfess.base.util.FileUtil; import io.jsonwebtoken.lang.Assert; import net.lingala.zip4j.core.ZipFile; import net.lingala.zip4j.model.ZipParameters; import net.lingala.zip4j.util.Zip4jConstants; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.VerticalAlignment; import java.io.File; import java.util.ArrayList; /** * @author zhx * @create 2022/3/28 */ public class PoiStyleUtil { public static void setExcelStyleSmall(HSSFCellStyle cellStyle, HSSFWorkbook workbook) { // cellStyle.setAlignment(HorizontalAlignment.CENTER); // cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setWrapText(true); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 8); cellStyle.setFont(font); } public static void setExcelStylePhotos(HSSFCellStyle cellStyle, HSSFWorkbook workbook) { cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setWrapText(true); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 12); cellStyle.setFont(font); } public static void setExcelStylePhotosOnLine(HSSFCellStyle cellStyle, HSSFWorkbook workbook) { cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setWrapText(true); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 12); cellStyle.setFont(font); } public static void setExcelStyleOne(HSSFCellStyle cellStyle, HSSFWorkbook workbook) { cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setWrapText(true); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 10); cellStyle.setFont(font); } public static void setExcelStyleTwo(HSSFCellStyle cellStyle, HSSFWorkbook workbook) { cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setWrapText(true); cellStyle.setBorderBottom(BorderStyle.THIN); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 10); cellStyle.setFont(font); } public static void setExcelStyleRight(HSSFCellStyle cellStyle, HSSFWorkbook workbook) { cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setWrapText(true); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 10); cellStyle.setFont(font); } public static void setExcelStyleLeft(HSSFCellStyle cellStyle, HSSFWorkbook workbook) { // cellStyle.setAlignment(HorizontalAlignment.CENTER); // cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setWrapText(true); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 10); cellStyle.setFont(font); } public static void setExcelStyleLeftAlign(HSSFCellStyle cellStyle, HSSFWorkbook workbook) { cellStyle.setAlignment(HorizontalAlignment.LEFT); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setWrapText(true); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 10); cellStyle.setFont(font); } //标题 public static void setExcelStyleTitle(HSSFCellStyle cellStyle, HSSFWorkbook workbook) { // cellStyle.setAlignment(HorizontalAlignment.CENTER); // cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 18); cellStyle.setFont(font); } public static void setExcelStyleTitleOne(HSSFCellStyle cellStyle, HSSFWorkbook workbook) { cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 18); cellStyle.setFont(font); } public static void setExcelStyleTitleNoLine(HSSFCellStyle cellStyle, HSSFWorkbook workbook) { cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 18); cellStyle.setFont(font); } public static void cellStyleTitleNoLineSmall(HSSFCellStyle cellStyle, HSSFWorkbook workbook) { cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 12); cellStyle.setFont(font); } //设置边框 public static void setExcelStyle(HSSFCellStyle cellStyle, HSSFWorkbook workbook) { cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setWrapText(true); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 10); cellStyle.setFont(font); } public static void setExcelStyleNoLine(HSSFCellStyle cellStyle, HSSFWorkbook workbook) { cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setWrapText(true); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 10); cellStyle.setFont(font); } public static void setWorkOrderStyleTitle(HSSFCellStyle cellStyle, HSSFWorkbook workbook){ // cellStyle.setBorderBottom(BorderStyle.THIN); // cellStyle.setBorderLeft(BorderStyle.THIN); // cellStyle.setBorderRight(BorderStyle.THIN); // cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setWrapText(true); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 18); cellStyle.setFont(font); } public static void setWorkOrderCellStyle1(HSSFCellStyle cellStyle, HSSFWorkbook workbook){ // cellStyle.setBorderBottom(BorderStyle.THIN); // cellStyle.setBorderLeft(BorderStyle.THIN); // cellStyle.setBorderRight(BorderStyle.THIN); // cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setWrapText(true); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 10); cellStyle.setFont(font); } public static void setWorkOrderCellStyle2(HSSFCellStyle cellStyle, HSSFWorkbook workbook){ cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setVerticalAlignment(VerticalAlignment.DISTRIBUTED); cellStyle.setWrapText(true); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 10); cellStyle.setFont(font); } public static void setWorkOrderCellStyle3(HSSFCellStyle cellStyle, HSSFWorkbook workbook){ cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setWrapText(true); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 10); cellStyle.setFont(font); } public static void setWorkOrderCellStyle4(HSSFCellStyle cellStyle, HSSFWorkbook workbook){ /* cellStyle.setAlignment(HorizontalAlignment.CENTER);*/ cellStyle.setVerticalAlignment(VerticalAlignment.DISTRIBUTED); cellStyle.setWrapText(true); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 10); cellStyle.setFont(font); } /** * 压缩文件 * * @param path 压缩文件\文件夹路径 * @param isDelete 压缩后是否删除原文件\文件夹 */ public static void zip(String path, Boolean isDelete) { ZipFile zipFile = null; try { ZipParameters parameters = new ZipParameters(); parameters.setCompressionMethod(Zip4jConstants.COMP_DEFLATE); parameters.setCompressionLevel(Zip4jConstants.DEFLATE_LEVEL_NORMAL); File file = new File(path); Assert.isTrue(file.exists(), "文件不存在"); ArrayList list = new ArrayList<>(); if (file.isDirectory()) { zipFile = new ZipFile(new File(path + ".zip")); zipFile.setFileNameCharset("GBK"); zipFile.addFolder(path, parameters); } else { zipFile = new ZipFile(new File(path.split(".")[0] + ".zip")); zipFile.setFileNameCharset("GBK"); zipFile.addFile(file, parameters); } if (isDelete) { FileUtil.deleteDir(file); } } catch (Exception e) { e.printStackTrace(); } } //设置值和格式 public static synchronized void sheetStyleAndValue(HSSFSheet sheet, HSSFCellStyle cellStyle, Integer rowIndex, Integer cellIndex, String value) { HSSFRow row = sheet.getRow(rowIndex); if (row == null) { row = sheet.createRow(rowIndex); } HSSFCell cell = row.getCell(cellIndex); if (cell == null) { cell = row.createCell(cellIndex); } cell.setCellStyle(cellStyle); cell.setCellValue(value); } public static synchronized void sheetSetHight(HSSFSheet sheet, Integer rowIndex, Integer height) { HSSFRow row = sheet.getRow(rowIndex); row.setHeight((short) (height * 20)); } }