POI格式化Cell样式

摘要

java中利用Apache的POI来格式化Cell的样式,包括居中,加粗,自动换行,或者是合并单元格和边框设置

package my.excel;     import java.io.FileOutputStream;     import org.apache.poi.ss.usermodel.Cell;   import org.apache.poi.ss.usermodel.CellStyle;   import org.apache.poi.ss.usermodel.Font;   import org.apache.poi.ss.usermodel.Row;   import org.apache.poi.ss.usermodel.Sheet;   import org.apache.poi.ss.usermodel.Workbook;   import org.apache.poi.ss.util.CellRangeAddress;   import org.apache.poi.xssf.usermodel.XSSFCellStyle;   import org.apache.poi.xssf.usermodel.XSSFColor;   import org.apache.poi.xssf.usermodel.XSSFWorkbook;      public class CellFormatExcel {              public static void main(String[] args) {           try {               // 创建Excel表格工作簿               Workbook wb = new XSSFWorkbook();               Sheet sheet = wb.createSheet("表格单元格格式化");                              //============================               //       设置单元格的字体               //============================               Row ztRow = sheet.createRow((short)0);               Cell ztCell = ztRow.createCell(0);               ztCell.setCellValue("中国");               // 创建单元格样式对象               XSSFCellStyle ztStyle = (XSSFCellStyle) wb.createCellStyle();               // 创建字体对象               Font ztFont = wb.createFont();               ztFont.setItalic(true);                     // 设置字体为斜体字               ztFont.setColor(Font.COLOR_RED);            // 将字体设置为“红色”               ztFont.setFontHeightInPoints((short)22);    // 将字体大小设置为18px               ztFont.setFontName("华文行楷");             // 将“华文行楷”字体应用到当前单元格上               ztFont.setUnderline(Font.U_DOUBLE);         // 添加（Font.U_SINGLE单条下划线/Font.U_DOUBLE双条下划线）              ztFont.setBoldweight(Font.BOLDWEIGHT_BOLD); //字体加粗//          ztFont.setStrikeout(true);                  // 是否添加删除线               ztStyle.setFont(ztFont);                    // 将字体应用到样式上面               ztCell.setCellStyle(ztStyle);               // 样式应用到该单元格上                              //============================               //        设置单元格边框               //============================               Row borderRow = sheet.createRow(2);               Cell borderCell = borderRow.createCell(1);               borderCell.setCellValue("中国");               // 创建单元格样式对象               XSSFCellStyle borderStyle = (XSSFCellStyle)wb.createCellStyle();               // 设置单元格边框样式               // CellStyle.BORDER_DOUBLE      双边线               // CellStyle.BORDER_THIN        细边线               // CellStyle.BORDER_MEDIUM      中等边线               // CellStyle.BORDER_DASHED      虚线边线               // CellStyle.BORDER_HAIR        小圆点虚线边线               // CellStyle.BORDER_THICK       粗边线               borderStyle.setBorderBottom(CellStyle.BORDER_THICK);               borderStyle.setBorderTop(CellStyle.BORDER_DASHED);               borderStyle.setBorderLeft(CellStyle.BORDER_DOUBLE);               borderStyle.setBorderRight(CellStyle.BORDER_THIN);                              // 设置单元格边框颜色               borderStyle.setBottomBorderColor(new XSSFColor(java.awt.Color.RED));               borderStyle.setTopBorderColor(new XSSFColor(java.awt.Color.GREEN));               borderStyle.setLeftBorderColor(new XSSFColor(java.awt.Color.BLUE));                              borderCell.setCellStyle(borderStyle);                              //============================               //      设置单元内容的对齐方式               //============================               Row alignRow = sheet.createRow(4);               Cell alignCell = alignRow.createCell(1);               alignCell.setCellValue("中国");                              // 创建单元格样式对象               XSSFCellStyle alignStyle = (XSSFCellStyle)wb.createCellStyle();                              // 设置单元格内容水平对其方式               // XSSFCellStyle.ALIGN_CENTER       居中对齐               // XSSFCellStyle.ALIGN_LEFT         左对齐               // XSSFCellStyle.ALIGN_RIGHT        右对齐               alignStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);                              // 设置单元格内容垂直对其方式               // XSSFCellStyle.VERTICAL_TOP       上对齐               // XSSFCellStyle.VERTICAL_CENTER    中对齐               // XSSFCellStyle.VERTICAL_BOTTOM    下对齐               alignStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);                              alignCell.setCellStyle(alignStyle);                              //============================               //      设置单元格的高度和宽度               //============================               Row sizeRow = sheet.createRow(6);               sizeRow.setHeightInPoints(30);                  // 设置行的高度                              Cell sizeCell = sizeRow.createCell(1);                 String sizeCellValue = "《Java编程思想》";            // 字符串的长度为10，表示该字符串中有10个字符，忽略中英文               sizeCell.setCellValue(sizeCellValue);                   // 设置单元格的长度为sizeCellVlue的长度。而sheet.setColumnWidth使用sizeCellVlue的字节数               // sizeCellValue.getBytes().length == 16               sheet.setColumnWidth(1, (sizeCellValue.getBytes().length) * 256 );                              //============================               //      设置单元格自动换行               //============================               Row wrapRow = sheet.createRow(8);               Cell wrapCell = wrapRow.createCell(2);               wrapCell.setCellValue("宝剑锋从磨砺出,梅花香自苦寒来");                              // 创建单元格样式对象               XSSFCellStyle wrapStyle = (XSSFCellStyle)wb.createCellStyle();               wrapStyle.setWrapText(true);                    // 设置单元格内容是否自动换行               wrapCell.setCellStyle(wrapStyle);                              //============================               //         合并单元格列               //============================               Row regionRow = sheet.createRow(12);               Cell regionCell = regionRow.createCell(0);               regionCell.setCellValue("宝剑锋从磨砺出,梅花香自苦寒来");                              // 合并第十三行中的A、B、C三列               CellRangeAddress region = new CellRangeAddress(12, 12, 0, 2); // 参数都是从O开始    &nnbsp;          sheet.addMergedRegion(region);                              //============================               //         合并单元格行和列               //============================               Row regionRow2 = sheet.createRow(13);               Cell regionCell2 = regionRow2.createCell(3);               String region2Value = "宝剑锋从磨砺出,梅花香自苦寒来。"                                  + "采得百花成蜜后,为谁辛苦为谁甜。"                                  + "操千曲而后晓声,观千剑而后识器。"                                  + "察己则可以知人,察今则可以知古。";               regionCell2.setCellValue(region2Value);                              // 合并第十三行中的A、B、C三列               CellRangeAddress region2 = new CellRangeAddress(13, 17, 3, 7); // 参数都是从O开始               sheet.addMergedRegion(region2);                              XSSFCellStyle region2Style = (XSSFCellStyle)wb.createCellStyle();               region2Style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);               region2Style.setWrapText(true);                     // 设置单元格内容是否自动换行               regionCell2.setCellStyle(region2Style);                                          //============================               // 将Excel文件写入到磁盘上               //============================               FileOutputStream is = new FileOutputStream("document/CellFormatExcel.xlsx");               wb.write(is);               is.close();                              System.out.println("写入成功，运行结束！");           } catch(Exception e) {               e.printStackTrace();           }       }   }

如何在合并单元格之后设置边框:

可以看到在合并单元格后设置边框,会导致有些没有边框,这是因为我们只是设置了合并单元格的部分边框,要把被合并的表格的边框也设置上,该表格的值可以设置为空字符串,也可以不设置,因为设置值也没什么用,因为合并之后,只有第一个被设置的值才有效.

 XSSFCellStyle ztStyle = (XSSFCellStyle) wb.createCellStyle();  ztStyle.setBorderBottom(CellStyle.BORDER_THIN);   //下边框边框 ztStyle.setBorderTop(CellStyle.BORDER_THIN);   //上边框 ztStyle.setBorderLeft(CellStyle.BORDER_THIN);   //左边框 ztStyle.setBorderRight(CellStyle.BORDER_THIN);   //有边框 HSSFRow row=sheet.createRow(2);   //第三行 sheet.addMergedRegion(new CellRangeAddress(2, 2,1, 5 ); //第三行的 第2列到第6列（即B到F）  合并单元格 row.createCell(1).setCellValue("答案选项"); //赋值row.createCell(2); row.createCell(3);//该单元格虽然被合并了,但是也要创建出来,并设置边框 row.createCell(4);//该单元格虽然被合并了,但是也要创建出来,并设置边框 row.createCell(5);//该单元格虽然被合并了,但是也要创建出来,并设置边框 row.createCell(6).setCellValue("回复情况");//该单元格虽然被合并了,但是也要创建出来,并设置边框 for (Cell cell : row) {     cell.setCellStyle(styleBorderThin);//设置该行所有cell的边框}或者是:sheet.addMergedRegion(new CellRangeAddress(2, 2,1, 5 );  cell = row.createCell(1);  cell.setCellValue("答案选项"); // 被合并的起始单元格设置值才有效,这里即(1,5)中的单元格,createCell(1)设置值才有效 cell.setCellStyle(styleBorderThin); cell = row.createCell(2); cell.setCellStyle(styleBorderThin); cell = row.createCell(3); cell.setCellStyle(styleBorderThin); cell = row.createCell(4); cell.setCellStyle(styleBorderThin); cell = row.createCell(5); cell.setCellStyle(styleBorderThin); cell = row.createCell(6); cell.setCellStyle(styleBorderThin); 或者是: sheet.addMergedRegion(new CellRangeAddress(2, 2,1, 5 );  for (int i = 1;i<=5,i++) {     cell = row.createCell(i);     if(i == 1){         cell.setCellValue("答案选项");      }     cell.setCellStyle(styleBorderThin);//设置该行所有cell的边框}//如果一个单元格同时同时要设置字体居中,加粗或者是自动换行,那么上面style放到一起就可以了XSSFCellStyle Style = (XSSFCellStyle)wb.createCellStyle();Font ztFont = wb.createFont();   ztFont.setFontHeightInPoints((short)12);    // 将字体大小设置为18px   ztFont.setFontName("仿宋_GB2312");          //字体格式ztFont.setBoldweight(Font.BOLDWEIGHT_BOLD);//加粗ztStyle.setFont(ztFont);     //字体应用样式ztStyle.setBorderBottom(CellStyle.BORDER_THIN);   //下边框边框ztStyle.setBorderTop(CellStyle.BORDER_THIN);   //上边框ztStyle.setBorderLeft(CellStyle.BORDER_THIN);   //左边框ztStyle.setBorderRight(CellStyle.BORDER_THIN);   //有边框 ztStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);//内容水平居中对齐   ztStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);  //内容居中对齐ztStyle.setWrapText(true);                     // 设置单元格内容是否自动换行          HSSFRow row=sheet.createRow(2);Cell cell = row.getCell(0);cell.setCellStyle(ztStyle);