POI对Excel的操作

1. 先导包

commons-io-2.6.jar包，用于对文件的操作。

下载地址：http://commons.apache.org/proper/commons-io/download_io.cgi

poi-3.17.jar包，对Excel表的操作。

下载地址：http://poi.apache.org/download.html#POI-3.17

2. 假设Excel表的表头和主体内容

 1 //表头数组
 2 String[] title = {"id","username","password","gender","address"};
 3 //主体内容
 4 String[][] sheetBodyValue = {
 5     {"001","test1","123456","男","中国北京"},
 6     {"002","test2","123456","女","中国北京"},
 7     {"003","test3","123456","男","中国北京"},
 8     {"004","test4","123456","女","中国北京"}
 9 };
10

3. 创建Excel表

1 HSSFWorkbook workBook = new HSSFWorkbook();

4. 创建excel页面，即sheet

1 HSSFSheet sheet = workBook.createSheet();

5. 创建表头行

1 //创建第index行 sheet.createRow(index)
2 HSSFRow titleRow = sheet.createRow(0);

6. 创建表头单元格，并遍历表头数组，设置表头

1 for (int i = 0; i < title.length; i++) {
2     HSSFCell titleCell= titleRow.createCell(i);
3     titleCell.setCellValue(title[i]);
4 }

7. 从第二行开始创建主体内容

1 for (int i = 1; i <= sheetBodyValue.length; i++) {
2     //创建第i行
3     HSSFRow bodyRow = sheet.createRow(i);
4     //创建单元格，并为每个单元格赋值
5     for (int j = 0; j < sheetBodyValue[0].length; j++) {
6         HSSFCell bodyCell = bodyRow.createCell(j);
7         bodyCell.setCellValue(sheetBodyValue[i-1][j]);
8     }
9 }

8. 创建文件，用文件输出流输出数据

1 File file = new File("D:/poi_text.xls");
2 try {
3     file.createNewFile();
4     FileOutputStream outputStream = FileUtils.openOutputStream(file);
5     workBook.write(outputStream);
6     outputStream.close();//记得关闭输出流
7 } catch (IOException e) {
8     e.printStackTrace();
9 }

9. 最后附上全部代码

 1 import java.io.File;
 2 import java.io.FileOutputStream;
 3 import java.io.IOException;
 4 
 5 import org.apache.commons.io.FileUtils;
 6 import org.apache.poi.hssf.usermodel.HSSFCell;
 7 import org.apache.poi.hssf.usermodel.HSSFRow;
 8 import org.apache.poi.hssf.usermodel.HSSFSheet;
 9 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
10 
11 public class testExcel {
12     
13     @SuppressWarnings("resource")
14     public static void createExcel(){
15         //表头数组
16         String[] title = {"id","username","password","gender","address"};
17         //主体内容
18         String[][] sheetBodyValue = {
19                 {"001","test1","123456","男","中国北京"},
20                 {"002","test2","123456","女","中国北京"},
21                 {"003","test3","123456","男","中国北京"},
22                 {"004","test4","123456","女","中国北京"}
23             };
24         
25         //创建excel表
26         HSSFWorkbook workBook = new HSSFWorkbook();
27         //创建excel页面，即sheet
28         HSSFSheet sheet = workBook.createSheet();
29         //创建表头行,第index行 sheet.createRow(index)
30         HSSFRow titleRow = sheet.createRow(0);
31         
32         //创建表头,第一行
33         for (int i = 0; i < title.length; i++) {
34             HSSFCell titleCell= titleRow.createCell(i);
35             titleCell.setCellValue(title[i]);
36         }
37         
38         //从第二行开始创建主体内容
39         for (int i = 1; i <= sheetBodyValue.length; i++) {
40             HSSFRow bodyRow = sheet.createRow(i);//创建第i行
41             for (int j = 0; j < sheetBodyValue[0].length; j++) {
42                 HSSFCell bodyCell = bodyRow.createCell(j);
43                 bodyCell.setCellValue(sheetBodyValue[i-1][j]);
44             }
45         }
46         //创建文件
47         File file = new File("D:/poi_text.xls");
48         try {
49             file.createNewFile();
50             FileOutputStream outputStream = FileUtils.openOutputStream(file);
51             workBook.write(outputStream);
52             outputStream.close();//记得关闭输出流
53         } catch (IOException e) {
54             e.printStackTrace();
55         }
56     }
57     public static void main(String[] args) {
58         createExcel();
59     }
60 }

View Code

本人也是初次接触POI对Excel表的操作，还有很多关于POI操作的知识点需要去掌握。

写一下个人对导出Excel表的操作小结。

希望与君共勉，共同进步。