java POI创建Excel示例（xslx和xsl区别）

Z时代
2024-01-10
分类：综合

java

Java用来处理office类库有很多，其中POI就是比较出名的一个，它是apache的类库，现在版本到了3.10，也就是2014年2月8号这个版本。

在处理PPT，Excel和Word前，需要导入以前几个jar包：

dom4j-1.7-20060614.jar

log4j-1.2.13.jar

poi-3.7-20101029.jar

poi-ooxml-3.7-20101029.jar

poi-ooxml-schemas-3.7-20101029.jar

本文主要简介下java poi处理Excel，

2003版本的.xls一张sheet表允许存2^16 ＝次方行数据，2^8 = 256列数据，

2007版本以上的.xlsx一张sheet表允许存的数据就更大了，是百万级别的。行： 2^20 = 1048576; 列：2^14 = 16384 行。

以下代码简单操作了excel文件。

 1 package com.caoyuanzhanlang.excelhandle;
 2 
 3 import java.io.File;
 4 import java.io.FileOutputStream;
 5 
 6 import org.apache.poi.hssf.util.HSSFColor;
 7 import org.apache.poi.ss.usermodel.Cell;
 8 import org.apache.poi.ss.usermodel.CellStyle;
 9 import org.apache.poi.ss.usermodel.Font;
10 import org.apache.poi.ss.usermodel.Row;
11 import org.apache.poi.ss.usermodel.Sheet;
12 import org.apache.poi.ss.usermodel.Workbook;
13 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
14 
15 public class NewExcel {
16     
17     private String excelPath = "data.xlsx";
18 
19     public static void main(String[] args) throws Exception{
20         NewExcel excel = new NewExcel();
21         if(excel.createExcelFile()) {
22             System.out.println("data.xlsx is created successfully.");
23         }
24     }
25     public boolean createExcelFile() {
26         boolean isCreateSuccess = false;
27         Workbook workbook = null;
28         try {
29             // XSSFWork used for .xslx (>= 2007), HSSWorkbook for 03 .xsl
30             workbook = new XSSFWorkbook();//HSSFWorkbook();//WorkbookFactory.create(inputStream);
31         }catch(Exception e) {
32             System.out.println("It cause Error on CREATING excel workbook: ");
33             e.printStackTrace();
34         }
35         if(workbook != null) {
36             Sheet sheet = workbook.createSheet("testdata");
37             Row row0 = sheet.createRow(0);
38             for(int i = 0; i < 11; i++) {
39                 Cell cell_1 = row0.createCell(i, Cell.CELL_TYPE_STRING);
40                 CellStyle style = getStyle(workbook);
41                 cell_1.setCellStyle(style);
42                 cell_1.setCellValue("HELLO" + i + "Column");
43                 sheet.autoSizeColumn(i);
44             }
45             for (int rowNum = 1; rowNum < 200; rowNum++) {
46                 Row row = sheet.createRow(rowNum);
47                 for(int i = 0; i < 11; i++) {
48                     Cell cell = row.createCell(i, Cell.CELL_TYPE_STRING);
49                     cell.setCellValue("cell" + String.valueOf(rowNum+1) + String.valueOf(i+1));
50                 }
51             }
52             try {
53                 FileOutputStream outputStream = new FileOutputStream(excelPath);
54                 workbook.write(outputStream);
55                 outputStream.flush();
56                 outputStream.close();
57                 isCreateSuccess = true;
58             } catch (Exception e) {
59                 System.out.println("It cause Error on WRITTING excel workbook: ");
60                 e.printStackTrace();
61             }
62         }
63         File sss = new File(excelPath);
64         System.out.println(sss.getAbsolutePath());
65         return isCreateSuccess;
66     }
67     private CellStyle getStyle(Workbook workbook){
68         CellStyle style = workbook.createCellStyle();
69         style.setAlignment(CellStyle.ALIGN_CENTER); 
70         style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
71         // 设置单元格字体
72         Font headerFont = workbook.createFont(); // 字体
73         headerFont.setFontHeightInPoints((short)14);
74         headerFont.setColor(HSSFColor.RED.index);
75         headerFont.setFontName("宋体");
76         style.setFont(headerFont);
77         style.setWrapText(true);
78 
79         // 设置单元格边框及颜色
80         style.setBorderBottom((short)1);
81         style.setBorderLeft((short)1);
82         style.setBorderRight((short)1);
83         style.setBorderTop((short)1);
84         style.setWrapText(true);
85         return style;
86     }
87     public String getExcelPath() {
88         return this.excelPath;
89     }
90     
91     public void setExcelPath(String excelPath) {
92         this.excelPath = excelPath;
93     }
94 }