Java POI excel单元格背景色(填充)、字体颜色(对齐)、边框(颜色)、行高、列宽设置

以你之姓@ 2023-10-13 12:39 33阅读 0赞

#### 文章目录 ####

*  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环境配置

<dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi</artifactId>
          <version>5.2.2</version>
        </dependency>
        
        <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi-ooxml</artifactId>
          <version>5.2.2</version>
        </dependency>

***部分需要注意的问题，通过注释的方式写在代码里面了，看代码的时候需要注意下注释***

## 1、Excel Cell单元格背景色+颜色名称对照关系 ##

/**
         * 设置单元格背景颜色
         * <pre>
         *     像素、磅、点、缇等各种单位换算参考链接
         *          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
         * </pre>
         */
        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();
        }

![image.png][]![image.png][image.png 1]![image.png][image.png 2]

## 2、Excel Cell单元格背景填充样式+颜色填充对照关系 ##

/**
         * 设置背景颜色填充效果
         * <pre>
         *      背景颜色填充效果参考链接
         *          https://blog.csdn.net/qq_39541254/article/details/107940224
         * </pre>
         */
        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();
        }

![image.png][image.png 3]![image.png][image.png 4]

## 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();
        }

![image.png][image.png 5]![image.png][image.png 6]

## 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 < data.length; rowIndex++) {
        
                int[] cellData = data[rowIndex];
    
                HSSFRow row = sheet.createRow(rowIndex);
                // 行高计算方式：缇(twips) = 磅 * 20 ==> 换算 1磅=20缇
                row.setHeight((short) (25 * 20));
                for (int cellIndex = 0; cellIndex < cellData.length; cellIndex++) {
        
                    HSSFCell cell = row.createCell(cellIndex);
                    // 列宽计算方式：8磅 * 256 + 185
                    sheet.setColumnWidth(cellIndex, 8 * 256 + 185);
                    cell.setCellValue(cellData[cellIndex]);
                }
            }
    
            FileOutputStream outputStream = new FileOutputStream("D:/temp/Excel行高、列宽.xlsx");
            workbook.write(outputStream);
            outputStream.close();
            workbook.close();
        }

![image.png][image.png 7]

## 5、Excel单元格边框设置+边框类型图片对比 ##

/**
         * 设置多有边框样式 + 颜色
         */
        public static void setAllBorderStyle() throws IOException {
        
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("excel单元格边框样式");
    
            // ======================= 设置边框
            int rowIndex = 0;
            for (BorderStyle borderStyle : BorderStyle.values()) {
        
                int cellIndex = 0;
                HSSFRow row = sheet.createRow(rowIndex++);
                row.setHeight((short) (35 * 20));
                // 第一列
                HSSFCell cell = row.createCell(cellIndex);
                HSSFCellStyle topBorderStyle = workbook.createCellStyle();
                // 上边框样式
                topBorderStyle.setBorderTop(borderStyle);
                cell.setCellValue("设置上边框(" + borderStyle.name() + ")");
                cell.setCellStyle(topBorderStyle);
                sheet.setColumnWidth(cellIndex, 35 * 256 + 185);
    
                cellIndex += 2;
                // 第三列
                HSSFCell cell2 = row.createCell(cellIndex);
                HSSFCellStyle bottomBorderStyle = workbook.createCellStyle();
                // 下边框样式
                bottomBorderStyle.setBorderBottom(borderStyle);
                cell2.setCellValue("设置下边框(" + borderStyle.name() + ")");
                cell2.setCellStyle(bottomBorderStyle);
                sheet.setColumnWidth(cellIndex, 35 * 256 + 185);
    
                cellIndex += 2;
                // 第五列
                HSSFCell cell3 = row.createCell(cellIndex);
                HSSFCellStyle leftBorderStyle = workbook.createCellStyle();
                // 左边框样式
                leftBorderStyle.setBorderLeft(borderStyle);
                cell3.setCellValue("设置左边框(" + borderStyle.name() + ")");
                cell3.setCellStyle(leftBorderStyle);
                sheet.setColumnWidth(cellIndex, 35 * 256 + 185);
    
                cellIndex += 2;
                // 第七列
                HSSFCell cell4 = row.createCell(cellIndex);
                HSSFCellStyle rightBorderStyle = workbook.createCellStyle();
                // 左边框样式
                rightBorderStyle.setBorderRight(borderStyle);
                cell4.setCellValue("设置右边框(" + borderStyle.name() + ")");
                cell4.setCellStyle(rightBorderStyle);
                sheet.setColumnWidth(cellIndex, 35 * 256 + 185);
            }
    
            // ================= 设置边框并设置颜色
            rowIndex += 2;
            for (BorderStyle borderStyle : BorderStyle.values()) {
        
                int cellIndex = 0;
                HSSFRow row = sheet.createRow(rowIndex++);
                row.setHeight((short) (35 * 20));
                // 第一列
                HSSFCell cell = row.createCell(cellIndex);
                HSSFCellStyle topBorderStyle = workbook.createCellStyle();
                // 上边框样式
                topBorderStyle.setBorderTop(borderStyle);
                // 上边框颜色
                topBorderStyle.setTopBorderColor(IndexedColors.RED.getIndex());
                cell.setCellValue("设置上边框(" + borderStyle.name() + ")");
                cell.setCellStyle(topBorderStyle);
                sheet.setColumnWidth(cellIndex, 35 * 256 + 185);
    
                cellIndex += 2;
                // 第三列
                HSSFCell cell2 = row.createCell(cellIndex);
                HSSFCellStyle bottomBorderStyle = workbook.createCellStyle();
                // 下边框样式
                bottomBorderStyle.setBorderBottom(borderStyle);
                // 下边框颜色
                bottomBorderStyle.setBottomBorderColor(IndexedColors.GREEN.getIndex());
                cell2.setCellValue("设置下边框(" + borderStyle.name() + ")");
                cell2.setCellStyle(bottomBorderStyle);
                sheet.setColumnWidth(cellIndex, 35 * 256 + 185);
    
                cellIndex += 2;
                // 第五列
                HSSFCell cell3 = row.createCell(cellIndex);
                HSSFCellStyle leftBorderStyle = workbook.createCellStyle();
                // 左边框样式
                leftBorderStyle.setBorderLeft(borderStyle);
                // 左边框颜色
                leftBorderStyle.setLeftBorderColor(IndexedColors.YELLOW.getIndex());
                cell3.setCellValue("设置左边框(" + borderStyle.name() + ")");
                cell3.setCellStyle(leftBorderStyle);
                sheet.setColumnWidth(cellIndex, 35 * 256 + 185);
    
                cellIndex += 2;
                // 第七列
                HSSFCell cell4 = row.createCell(cellIndex);
                HSSFCellStyle rightBorderStyle = workbook.createCellStyle();
                // 左边框样式
                rightBorderStyle.setBorderRight(borderStyle);
                // 右边框颜色
                rightBorderStyle.setRightBorderColor(IndexedColors.ORANGE.getIndex());
                cell4.setCellValue("设置右边框(" + borderStyle.name() + ")");
                cell4.setCellStyle(rightBorderStyle);
                sheet.setColumnWidth(cellIndex, 35 * 256 + 185);
            }
    
            FileOutputStream outputStream = new FileOutputStream("D:/temp/Excel单元格边框样式.xlsx");
            workbook.write(outputStream);
            outputStream.close();
            workbook.close();
        }

**边框样式对照表，括号内为BorderStyle枚举对象**

![在这里插入图片描述][f22d1cdfcc5645a9a7f124dcae2e038e.png]

## 附一：一些问题 ##

### 1、关于列宽使用磅\*20的计算方式 ###

![image.png][image.png 8]

### 2、关于行高使用磅\*256+185的计算方式 ###

![image.png][image.png 9]

### 3、关于sheet.getLastRowNum()最终行数不正确问题 ###

![image.png][image.png 10]  
如果整个Excel中存在空行（整行空），当时若干空行下面存在其他数据，这时候获取到的最终行号就不对了，尤其是存在合并单元格的情况最容易出现这类问题。  
![image.png][image.png 11]  
很明显10、11、13、14全都是空行了，这时候的lastNum就是不正确的，这种情况要尤为注意。  
  
避免这种情况的出现最直接的一种方式就是，预先知道数据存在多少行，先把所有行都生成**sheet.createRow(0)。**  
  
***同样地，空白的单元格也会出现这种情况，如果单元格不存在或没有样式，获取到的最后一个单元格列数也是不正确的。***

### 4、IDEA中按住快捷键（Shift）+鼠标悬浮到对应单词可以查看颜色 ###

![在这里插入图片描述][66870f9f31494f6cb0ca5c701bcc1dd2.png]  
![在这里插入图片描述][5d5c3f96cfb143b6a32b84cc10986e96.png]

## 附二：参考链接 ##

[像素、磅、点、缇等各种单位换算\_点和像素换算\_tanghuan的博客-CSDN博客][tanghuan_-CSDN]  
[JAVA对excle创建、读取、设置单元格颜色、背景色、跨行跨列\_java设置excel背景色\_谷同学的博客-CSDN博客][JAVA_excle_java_excel_-CSDN]  
[POI4颜色名称，颜色汉语名称，颜色对应关系\_软件工程师文艺的博客-CSDN博客][POI4_-CSDN]  
[POI设置Excel单元格背景色（setFillForegroundColor与setFillPattern的使用） - 大墨垂杨 - 博客园][POI_Excel_setFillForegroundColor_setFillPattern_ - _ -]  
[java用POI设置Excel的列宽\_sheet.setcolumnwidth\_duqian42707的博客-CSDN博客][java_POI_Excel_sheet.setcolumnwidth_duqian42707_-CSDN]  
[Java POI 设置Excel单元格的宽度和高度\_java poi 设置单元格宽度\_aosica的博客-CSDN博客][Java POI _Excel_java poi _aosica_-CSDN]

[image.png]: https://img-blog.csdnimg.cn/img_convert/8a01855ba0e9e0d07c57369e688b7947.png#averageHue=#cfc756&clientId=uce8f5839-1213-4&from=paste&height=568&id=u37d46443&originHeight=767&originWidth=375&originalType=binary&ratio=1.3499999046325684&rotation=0&showTitle=false&size=70073&status=done&style=none&taskId=ua15deea4-d296-49db-a96e-08d0678f115&title=&width=277.77779740070747
[image.png 1]: https://img-blog.csdnimg.cn/img_convert/1d8a3a41c585f23bb1cae58203a50ef1.png#averageHue=#e4c369&clientId=uce8f5839-1213-4&from=paste&height=561&id=uf470393d&originHeight=758&originWidth=376&originalType=binary&ratio=1.3499999046325684&rotation=0&showTitle=false&size=84635&status=done&style=none&taskId=u9a1ba594-1d16-48a9-8f6c-42432c29c0c&title=&width=278.51853819377607
[image.png 2]: https://img-blog.csdnimg.cn/img_convert/e1d3fe3d8d38149ca1cfe646d8638196.png#averageHue=#877e5b&clientId=uce8f5839-1213-4&from=paste&height=214&id=ub96de4c3&originHeight=289&originWidth=337&originalType=binary&ratio=1.3499999046325684&rotation=0&showTitle=false&size=30657&status=done&style=none&taskId=u0fb523f0-96e2-4fb1-b7a0-57db3ba19e6&title=&width=249.62964726410246
[image.png 3]: https://img-blog.csdnimg.cn/img_convert/6aa23f74a655640401697ff5977946eb.png#averageHue=#bc9f89&clientId=uce8f5839-1213-4&from=paste&height=307&id=uc14872ae&originHeight=415&originWidth=372&originalType=binary&ratio=1.3499999046325684&rotation=0&showTitle=false&size=60684&status=done&style=none&taskId=u543beef7-0af7-4d92-ab22-b2918745f8a&title=&width=275.55557502150185
[image.png 4]: https://img-blog.csdnimg.cn/img_convert/4607957843a3c9976e5bf01a9a60aae4.png#averageHue=#f9e7e6&clientId=uce8f5839-1213-4&from=paste&height=213&id=u0a569f60&originHeight=288&originWidth=368&originalType=binary&ratio=1.3499999046325684&rotation=0&showTitle=false&size=47162&status=done&style=none&taskId=uf058469f-d288-4449-96b5-780f88eabbb&title=&width=272.59261184922764
[image.png 5]: https://img-blog.csdnimg.cn/img_convert/f0d635d50125fc6e30051bb0f9c0af7d.png#averageHue=#fef4f3&clientId=uce8f5839-1213-4&from=paste&height=105&id=u04c35d03&originHeight=151&originWidth=806&originalType=binary&ratio=1.3499999046325684&rotation=0&showTitle=false&size=18971&status=done&style=none&taskId=ucb8faa1c-26dc-42d3-bc23-a57000b573e&title=&width=558.0370483398438
[image.png 6]: https://img-blog.csdnimg.cn/img_convert/eda3edb1cb500304d0162024f8a55b8a.png#averageHue=#fefe40&clientId=uce8f5839-1213-4&from=paste&height=89&id=i5ayc&originHeight=110&originWidth=382&originalType=binary&ratio=1.3499999046325684&rotation=0&showTitle=false&size=2204&status=done&style=none&taskId=u90c41043-2616-446e-a406-0666fc01dee&title=&width=308.9583435058594
[image.png 7]: https://img-blog.csdnimg.cn/img_convert/92eac6ae7f189cf615af681dff1747b8.png#averageHue=#c3ae97&clientId=uce8f5839-1213-4&from=paste&height=244&id=u3ee60150&originHeight=330&originWidth=511&originalType=binary&ratio=1.3499999046325684&rotation=0&showTitle=false&size=21769&status=done&style=none&taskId=u41247310-cbcc-4a2f-86eb-1853b7571ae&title=&width=378.5185452580307
[f22d1cdfcc5645a9a7f124dcae2e038e.png]: https://img-blog.csdnimg.cn/f22d1cdfcc5645a9a7f124dcae2e038e.png
[image.png 8]: https://img-blog.csdnimg.cn/img_convert/aaeac9220abf3a026ec35958d36e6108.png#averageHue=#302c2c&clientId=uce8f5839-1213-4&from=paste&height=120&id=ubfa71bd2&originHeight=162&originWidth=1023&originalType=binary&ratio=1.3499999046325684&rotation=0&showTitle=false&size=14882&status=done&style=none&taskId=uf63e8cea-c761-42b3-b62c-aeb2300a844&title=&width=757.77783130913
[image.png 9]: https://img-blog.csdnimg.cn/img_convert/ccd1a83f9594d1d5f5b44485a30186bf.png#averageHue=#2d2b2b&clientId=uce8f5839-1213-4&from=paste&height=466&id=ue945d164&originHeight=629&originWidth=808&originalType=binary&ratio=1.3499999046325684&rotation=0&showTitle=true&size=227100&status=done&style=none&taskId=u4ec29f0c-5ef0-4c2c-9fdc-c2f36386ab3&title=&width=598.518560799391
[image.png 10]: https://img-blog.csdnimg.cn/img_convert/d1c204fe23c891cbd196b4b8b033c38b.png#averageHue=#2f2d2c&clientId=uce8f5839-1213-4&from=paste&height=187&id=u8ddb163a&originHeight=253&originWidth=1162&originalType=binary&ratio=1.3499999046325684&rotation=0&showTitle=false&size=35801&status=done&style=none&taskId=u3613efb6-c05d-4805-aa69-ea496103de6&title=&width=860.7408015456589
[image.png 11]: https://img-blog.csdnimg.cn/img_convert/0f0b21c84280169af817afa634255051.png#averageHue=#eae8e7&clientId=uce8f5839-1213-4&from=paste&height=164&id=ub0814ca5&originHeight=222&originWidth=654&originalType=binary&ratio=1.3499999046325684&rotation=0&showTitle=false&size=10974&status=done&style=none&taskId=uacde8fe4-4039-4ad3-bdf8-a10591a901e&title=&width=484.44447866683385
[66870f9f31494f6cb0ca5c701bcc1dd2.png]: https://img-blog.csdnimg.cn/66870f9f31494f6cb0ca5c701bcc1dd2.png
[5d5c3f96cfb143b6a32b84cc10986e96.png]: https://img-blog.csdnimg.cn/5d5c3f96cfb143b6a32b84cc10986e96.png
[tanghuan_-CSDN]: https://blog.csdn.net/tanghuan/article/details/113539369
[JAVA_excle_java_excel_-CSDN]: https://blog.csdn.net/weixin_43845227/article/details/123580523
[POI4_-CSDN]: https://blog.csdn.net/lenovo96166/article/details/102765781
[POI_Excel_setFillForegroundColor_setFillPattern_ - _ -]: https://www.cnblogs.com/quchunhui/p/14378115.html
[java_POI_Excel_sheet.setcolumnwidth_duqian42707_-CSDN]: https://blog.csdn.net/duqian42707/article/details/51491312
[Java POI _Excel_java poi _aosica_-CSDN]: https://blog.csdn.net/aosica321/article/details/72320050