POI格式化Cell样式(有实例)

通过一个实例演示怎样通过POI设置Excel单元格的边框、字体、颜色、大小、下划线、合并、对齐方式。

Excel文件如下：

 

Java代码  

1. package my.excel;  
2.   
3. import java.io.FileOutputStream;  
4.   
5. import org.apache.poi.ss.usermodel.Cell;  
6. import org.apache.poi.ss.usermodel.CellStyle;  
7. import org.apache.poi.ss.usermodel.Font;  
8. import org.apache.poi.ss.usermodel.Row;  
9. import org.apache.poi.ss.usermodel.Sheet;  
10. import org.apache.poi.ss.usermodel.Workbook;  
11. import org.apache.poi.ss.util.CellRangeAddress;  
12. import org.apache.poi.xssf.usermodel.XSSFCellStyle;  
13. import org.apache.poi.xssf.usermodel.XSSFColor;  
14. import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
15.   
16. /** 
17.  * 格式化Excel中单元格的样式 
18.  * @author Administrator 
19.  *  
20.  */  
21. public class CellFormatExcel {  
22.       
23.     public static void main(String[] args) {  
24.         try {  
25.             // 创建Excel表格工作簿  
26.             Workbook wb = new XSSFWorkbook();  
27.             Sheet sheet = wb.createSheet("表格单元格格式化");  
28.               
29.             //============================  
30.             //       设置单元格的字体  
31.             //============================  
32.             Row ztRow = sheet.createRow((short)0);  
33.             Cell ztCell = ztRow.createCell(0);  
34.             ztCell.setCellValue("中国");  
35.             // 创建单元格样式对象  
36.             XSSFCellStyle ztStyle = (XSSFCellStyle) wb.createCellStyle();  
37.             // 创建字体对象  
38.             Font ztFont = wb.createFont();  
39.             ztFont.setItalic(true);                     // 设置字体为斜体字  
40.             ztFont.setColor(Font.COLOR_RED);            // 将字体设置为“红色”  
41.             ztFont.setFontHeightInPoints((short)22);    // 将字体大小设置为18px  
42.             ztFont.setFontName("华文行楷");             // 将“华文行楷”字体应用到当前单元格上  
43.             ztFont.setUnderline(Font.U_DOUBLE);         // 添加（Font.U_SINGLE单条下划线/Font.U_DOUBLE双条下划线）  
44. //          ztFont.setStrikeout(true);                  // 是否添加删除线  
45.             ztStyle.setFont(ztFont);                    // 将字体应用到样式上面  
46.             ztCell.setCellStyle(ztStyle);               // 样式应用到该单元格上  
47.               
48.             //============================  
49.             //        设置单元格边框  
50.             //============================  
51.             Row borderRow = sheet.createRow(2);  
52.             Cell borderCell = borderRow.createCell(1);  
53.             borderCell.setCellValue("中国");  
54.             // 创建单元格样式对象  
55.             XSSFCellStyle borderStyle = (XSSFCellStyle)wb.createCellStyle();  
56.             // 设置单元格边框样式  
57.             // CellStyle.BORDER_DOUBLE      双边线  
58.             // CellStyle.BORDER_THIN        细边线  
59.             // CellStyle.BORDER_MEDIUM      中等边线  
60.             // CellStyle.BORDER_DASHED      虚线边线  
61.             // CellStyle.BORDER_HAIR        小圆点虚线边线  
62.             // CellStyle.BORDER_THICK       粗边线  
63.             borderStyle.setBorderBottom(CellStyle.BORDER_THICK);  
64.             borderStyle.setBorderTop(CellStyle.BORDER_DASHED);  
65.             borderStyle.setBorderLeft(CellStyle.BORDER_DOUBLE);  
66.             borderStyle.setBorderRight(CellStyle.BORDER_THIN);  
67.               
68.             // 设置单元格边框颜色  
69.             borderStyle.setBottomBorderColor(new XSSFColor(java.awt.Color.RED));  
70.             borderStyle.setTopBorderColor(new XSSFColor(java.awt.Color.GREEN));  
71.             borderStyle.setLeftBorderColor(new XSSFColor(java.awt.Color.BLUE));  
72.               
73.             borderCell.setCellStyle(borderStyle);  
74.               
75.             //============================  
76.             //      设置单元内容的对齐方式  
77.             //============================  
78.             Row alignRow = sheet.createRow(4);  
79.             Cell alignCell = alignRow.createCell(1);  
80.             alignCell.setCellValue("中国");  
81.               
82.             // 创建单元格样式对象  
83.             XSSFCellStyle alignStyle = (XSSFCellStyle)wb.createCellStyle();  
84.               
85.             // 设置单元格内容水平对其方式  
86.             // XSSFCellStyle.ALIGN_CENTER       居中对齐  
87.             // XSSFCellStyle.ALIGN_LEFT         左对齐  
88.             // XSSFCellStyle.ALIGN_RIGHT        右对齐  
89.             alignStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);  
90.               
91.             // 设置单元格内容垂直对其方式  
92.             // XSSFCellStyle.VERTICAL_TOP       上对齐  
93.             // XSSFCellStyle.VERTICAL_CENTER    中对齐  
94.             // XSSFCellStyle.VERTICAL_BOTTOM    下对齐  
95.             alignStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);  
96.               
97.             alignCell.setCellStyle(alignStyle);  
98.               
99.             //============================  
100.             //      设置单元格的高度和宽度  
101.             //============================  
102.             Row sizeRow = sheet.createRow(6);  
103.             sizeRow.setHeightInPoints(30);                  // 设置行的高度  
104.               
105.             Cell sizeCell = sizeRow.createCell(1);    
106.             String sizeCellValue = "《Java编程思想》";            // 字符串的长度为10，表示该字符串中有10个字符，忽略中英文  
107.             sizeCell.setCellValue(sizeCellValue);      
108.             // 设置单元格的长度为sizeCellVlue的长度。而sheet.setColumnWidth使用sizeCellVlue的字节数  
109.             // sizeCellValue.getBytes().length == 16  
110.             sheet.setColumnWidth(1, (sizeCellValue.getBytes().length) * 256 );  
111.               
112.             //============================  
113.             //      设置单元格自动换行  
114.             //============================  
115.             Row wrapRow = sheet.createRow(8);  
116.             Cell wrapCell = wrapRow.createCell(2);  
117.             wrapCell.setCellValue("宝剑锋从磨砺出,梅花香自苦寒来");  
118.               
119.             // 创建单元格样式对象  
120.             XSSFCellStyle wrapStyle = (XSSFCellStyle)wb.createCellStyle();  
121.             wrapStyle.setWrapText(true);                    // 设置单元格内容是否自动换行  
122.             wrapCell.setCellStyle(wrapStyle);  
123.               
124.             //============================  
125.             //         合并单元格列  
126.             //============================  
127.             Row regionRow = sheet.createRow(12);  
128.             Cell regionCell = regionRow.createCell(0);  
129.             regionCell.setCellValue("宝剑锋从磨砺出,梅花香自苦寒来");  
130.               
131.             // 合并第十三行中的A、B、C三列  
132.             CellRangeAddress region = new CellRangeAddress(12, 12, 0, 2); // 参数都是从O开始  
133.             sheet.addMergedRegion(region);  
134.               
135.             //============================  
136.             //         合并单元格行和列  
137.             //============================  
138.             Row regionRow2 = sheet.createRow(13);  
139.             Cell regionCell2 = regionRow2.createCell(3);  
140.             String region2Value = "宝剑锋从磨砺出,梅花香自苦寒来。"  
141.                                 + "采得百花成蜜后,为谁辛苦为谁甜。"  
142.                                 + "操千曲而后晓声,观千剑而后识器。"  
143.                                 + "察己则可以知人,察今则可以知古。";  
144.             regionCell2.setCellValue(region2Value);  
145.               
146.             // 合并第十三行中的A、B、C三列  
147.             CellRangeAddress region2 = new CellRangeAddress(13, 17, 3, 7); // 参数都是从O开始  
148.             sheet.addMergedRegion(region2);  
149.               
150.             XSSFCellStyle region2Style = (XSSFCellStyle)wb.createCellStyle();  
151.             region2Style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);  
152.             region2Style.setWrapText(true);                     // 设置单元格内容是否自动换行  
153.             regionCell2.setCellStyle(region2Style);  
154.                           
155.             //============================  
156.             // 将Excel文件写入到磁盘上  
157.             //============================  
158.             FileOutputStream is = new FileOutputStream("document/CellFormatExcel.xlsx");  
159.             wb.write(is);  
160.             is.close();  
161.               
162.             System.out.println("写入成功，运行结束！");  
163.         } catch(Exception e) {  
164.             e.printStackTrace();  
165.         }  
166.     }  
167. }