|
文章目录
1、Excel Cell单元格背景色+颜色名称对照关系2、Excel Cell单元格背景填充样式+颜色填充对照关系3、Excel Cell字体样式设置+对照图4、Excel 行高、列宽设置5、Excel单元格边框设置+边框类型图片对比附一：一些问题1、关于列宽使用磅*20的计算方式2、关于行高使用磅*256+185的计算方式3、关于sheet.getLastRowNum()最终行数不正确问题4、IDEA中按住快捷键（Shift）+鼠标悬浮到对应单词可以查看颜色
附二：参考链接
需要的Maven环境配置
org.apache.poi
poi
5.2.2
org.apache.poi
poi-ooxml
5.2.2
部分需要注意的问题，通过注释的方式写在代码里面了，看代码的时候需要注意下注释
1、Excel Cell单元格背景色+颜色名称对照关系
/**
* 设置单元格背景颜色
*
* 像素、磅、点、缇等各种单位换算参考链接
* https://blog.csdn.net/tanghuan/article/details/113539369
* Excel Cell设置背景颜色参考链接
* https://blog.csdn.net/weixin_43845227/article/details/123580523
* Excel POI Cell背景颜色对照关系表参考链接
* https://blog.csdn.net/lenovo96166/article/details/102765781
* https://www.cnblogs.com/quchunhui/p/14378115.html
* Excel Cell POI Width宽度设置公式参考链接(拟合方程)
* https://blog.csdn.net/duqian42707/article/details/51491312
* https://blog.csdn.net/aosica321/article/details/72320050
*
*/
public static void setBackgroundColorCellStyle() throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("excel样式设置");
int rowIndex = 0;
for (IndexedColors color : IndexedColors.values()) {
short colorIndex = color.getIndex();
HSSFCellStyle cell1Style = workbook.createCellStyle();
// 设置的背景颜色
cell1Style.setFillForegroundColor(colorIndex);
// 填充效果（全景填充）
cell1Style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell1Style.setAlignment(HorizontalAlignment.CENTER);
cell1Style.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFRow row = sheet.createRow(rowIndex++);
row.setHeight((short) (25 * 20));
// 第一列
HSSFCell cell1 = row.createCell(0);
cell1.setCellStyle(cell1Style);
cell1.setCellValue("X:" + colorIndex);
// 第二列
HSSFCellStyle cell2Style = workbook.createCellStyle();
cell2Style.setAlignment(HorizontalAlignment.CENTER);
cell2Style.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFCell cell2 = row.createCell(1);
cell2.setCellStyle(cell2Style);
cell2.setCellValue(color.name());
// 设置列宽
sheet.setColumnWidth(0, 10 * 256 + 185);
sheet.setColumnWidth(1, 35 * 256 + 185);
}
FileOutputStream outputStream = new FileOutputStream("D:/temp/Excel背景颜色列表.xlsx");
workbook.write(outputStream);
outputStream.close();
workbook.close();
}
  
2、Excel Cell单元格背景填充样式+颜色填充对照关系
/**
* 设置背景颜色填充效果
*
* 背景颜色填充效果参考链接
* https://blog.csdn.net/qq_39541254/article/details/107940224
*
*/
public static void setFillBackgroundColor() throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("excel填充设置");
int rowIndex = 0;
short colorIndex = IndexedColors.RED.getIndex(); // 选择红色（可参照上图背景色色号） --> 10
// 填充样式
for (FillPatternType patternType : FillPatternType.values()) {
HSSFCellStyle cell1Style = workbook.createCellStyle();
// 设置的背景颜色
cell1Style.setFillForegroundColor(colorIndex);
// 填充效果（全景填充）
cell1Style.setFillPattern(patternType);
// 设置垂直居中
cell1Style.setAlignment(HorizontalAlignment.CENTER);
cell1Style.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置字体
HSSFFont font = workbook.createFont();
// 加粗
font.setBold(true);
cell1Style.setFont(font);
HSSFRow row = sheet.createRow(rowIndex++);
// 设置行高：height = 磅 * 20 （1磅=0.353毫米=20缇）-> POI中行高是"缇(twips)"
row.setHeight((short) (25 * 20));
// 第一列
HSSFCell cell1 = row.createCell(0);
cell1.setCellStyle(cell1Style);
cell1.setCellValue("code:" + patternType.getCode());
HSSFCellStyle cell2Style = workbook.createCellStyle();
// 设置垂直居中
cell2Style.setAlignment(HorizontalAlignment.CENTER);
cell2Style.setVerticalAlignment(VerticalAlignment.CENTER);
// 第二列
HSSFCell cell2 = row.createCell(1);
cell2.setCellStyle(cell2Style);
cell2.setCellValue(patternType.name());
// 设置列宽: width = 256*磅 + 185
sheet.setColumnWidth(0, 10 * 256 + 185);
sheet.setColumnWidth(1, 24 * 256 + 185);
}
FileOutputStream outputStream = new FileOutputStream("D:/temp/Excel背景填充效果.xlsx");
workbook.write(outputStream);
outputStream.close();
workbook.close();
}
 
3、Excel Cell字体样式设置+对照图
/**
* 设置单元格字体样式
*/
public static void setCellFontStyle() throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("excel字体样式");
HSSFCellStyle cellStyle = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
// 字体加粗
font.setBold(true);
// 字体倾斜
font.setItalic(true);
// 字体删除线
font.setStrikeout(true);
// 字体颜色
font.setColor(IndexedColors.YELLOW.getIndex());
// 字体大小:字号
font.setFontHeightInPoints((short) 14);
// 设置行高
// font.setFontHeight((short) 14);
// 字体
font.setFontName("宋体");
cellStyle.setFont(font);
// 设置文字垂直居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置单元格内容自动换行（文字超出列宽自动换行）
cellStyle.setWrapText(true);
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellStyle(cellStyle);
cell.setCellValue("字体");
row.setHeight((short) (30 * 20));
sheet.setColumnWidth(0, 30 * 256 + 185);
FileOutputStream outputStream = new FileOutputStream("D:/temp/Excel字体样式.xlsx");
workbook.write(outputStream);
outputStream.close();
workbook.close();
}
 
4、Excel 行高、列宽设置
/**
* 行高列宽设置
*/
public static void setRowHeightAndCellWidth() throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("excel行高、列宽");
// 定义一个5行、5列的数据
int[][] data = {{1, 2, 3, 4, 5}, {6, 7, 8, 9, 10}, {11, 12, 13, 14, 15}, {16, 17, 18, 19, 20}, {21, 22, 23, 24, 25}};
for (int rowIndex = 0; rowIndex 换算 1磅=20缇
row.setHeight((short) (25 * 20));
for (int cellIndex = 0; cellIndex |