package com.gctl.brp.untils;
import lombok.experimental.UtilityClass;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
/**
* Description:用于设置excel的特殊要求的样式
*/
@UtilityClass
public class ExcelStylesUtils {
/**
* firstRow 開始行號 根据此项目,默认为2(下标0开始)
* lastRow 根据此项目,默认为最大65535
* firstCol 区域中第一个单元格的列号 (下标0开始)
* lastCol 区域中最后一个单元格的列号
* strings 下拉内容
* 用于设置excel 的下拉列表
* */
public static void selectList(Workbook workbook, int firstCol, int lastCol, String[] strings ){
//设置需要处理的sheet页
Sheet sheet = workbook.getSheetAt(0);
//生成下拉列表的范围
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 65535, firstCol, lastCol);
DataValidationHelper helper = sheet.getDataValidationHelper();
//设置下拉框数据
DataValidationConstraint constraint = helper.createExplicitListConstraint(strings);
DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
//处理Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation) {
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
}
/**
* 设置批注
*/
public static void setAnnotation(Workbook workbook,int row1,int col1,int row2,int col2,int row3,int col3,String content){
Sheet sheet = workbook.getSheetAt(0);
XSSFDrawing patriarch = (XSSFDrawing) sheet.createDrawingPatriarch();
Cell cell = sheet.createRow(row1).createCell(col1);
cell.setCellValue(new XSSFRichTextString());
// (int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2)
// 前四个参数是坐标点,后四个参数是编辑和显示批注时的大小.
Comment comment = patriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) col2, row2, (short) col3, row3));
comment.setString(new XSSFRichTextString(content));
cell.setCellComment(comment);
}
/**
* 设置单元格格式
*/
public static void setStyle(Workbook workbook,int col){
Sheet sheet = workbook.getSheetAt(0);
/**
* 设置单元格格式
*/
CellStyle cellStyle = workbook.createCellStyle();
DataFormat dataFormat = workbook.createDataFormat();
cellStyle.setDataFormat(dataFormat.getFormat("@"));
sheet.setDefaultColumnStyle(col,cellStyle);
}
}
08-09
“相关推荐”对你有帮助么?
-
非常没帮助
-
没帮助
-
一般
-
有帮助
-
非常有帮助
提交