POI设置 Excle格式

2019独角兽企业重金招聘Python工程师标准>>>

1.设置背景色(根据RGB设置自定义颜色)

XLS自定义颜色格式设置
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
HSSFPalette palette = workbook.getCustomPalette();
palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 252, (byte) 228, (byte) 214);
palette.getColor(HSSFColor.LIME.index);
cellStyle.setFillForegroundColor(palette.getColor(HSSFColor.LIME.index).getIndex());

XLSX自定义颜色格式设置

XSSFCellStyle cellStyle = workbook.createCellStyle();
 cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

cellStyle.setFillForegroundColor(new XSSFColor( new Color(252, 228, 214)));

cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

2.设置边框
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

3.设置列宽
HSSFSheet sheet = workbook.createSheet("sheetName"); //创建sheet
sheet.setColumnWidth((short) 0, (short) 2600);// 设置列宽
sheet.setColumnWidth((short) 1, (short) 2400);

4.例子
HSSFCellStyle cellstyle = (HSSFCellStyle) workbook.createCellStyle();// 设置表头样式
cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置居中

HSSFCellStyle headerStyle = (HSSFCellStyle) workbook .createCellStyle();// 创建标题样式
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);    //设置垂直居中
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);    //设置水平居中
HSSFFont headerFont = (HSSFFont) workbook.createFont();    //创建字体样式
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);    // 字体加粗
headerFont.setFontName("Times New Roman");    //设置字体类型
headerFont.setFontHeightInPoints((short) 8);    //设置字体大小
headerStyle.setFont(headerFont);    //为标题样式设置字体样式

HSSFCellStyle headerStyle1 = (HSSFCellStyle) workbook .createCellStyle();// 创建标题样式1
headerStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
headerStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont headerFont1 = (HSSFFont) workbook.createFont();
headerFont1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 字体加粗
headerFont1.setFontName("Times New Roman");
headerFont1.setFontHeightInPoints((short) 8);
headerStyle1.setFont(headerFont1);

HSSFCellStyle headerStyle2 = (HSSFCellStyle) workbook .createCellStyle();// 创建标题样式2
headerStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
headerStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont headerFont2 = (HSSFFont) workbook.createFont();
headerFont2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 字体加粗
headerFont2.setFontName("Times New Roman");
headerFont2.setFontHeightInPoints((short) 8);
headerStyle2.setFont(headerFont2);
headerStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
headerStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
headerStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
headerStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框

HSSFCellStyle cell_Style = (HSSFCellStyle) workbook .createCellStyle();// 设置字体样式
cell_Style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell_Style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直对齐居中
cell_Style.setWrapText(true); // 设置为自动换行
HSSFFont cell_Font = (HSSFFont) workbook.createFont();
cell_Font.setFontName("宋体");
cell_Font.setFontHeightInPoints((short) 8);
cell_Style.setFont(cell_Font);
cell_Style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
cell_Style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
cell_Style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
cell_Style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框

HSSFRow row = sheet.createRow((short)1);    //创建行
HSSFCell cell = row.createCell((short)1);    //创建列
cell.setCellStyle(headerStyle2);    //单元格引用样式

转载于:https://my.oschina.net/sky2008/blog/2877390