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表的表头和主体内容

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

 3. 创建Excel表

HSSFWorkbook workBook = new HSSFWorkbook();

4. 创建excel页面，即sheet

HSSFSheet sheet = workBook.createSheet();

5. 创建表头行

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

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

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

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

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

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

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

 9. 最后附上全部代码

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class testExcel {
    
    @SuppressWarnings("resource")
    public static void createExcel(){
        //表头数组
        String[] title = {"id","username","password","gender","address"};
        //主体内容
        String[][] sheetBodyValue = {
                {"001","test1","123456","男","中国北京"},
                {"002","test2","123456","女","中国北京"},
                {"003","test3","123456","男","中国北京"},
                {"004","test4","123456","女","中国北京"}
            };
        
        //创建excel表
        HSSFWorkbook workBook = new HSSFWorkbook();
        //创建excel页面，即sheet
        HSSFSheet sheet = workBook.createSheet();
        //创建表头行,第index行 sheet.createRow(index)
        HSSFRow titleRow = sheet.createRow(0);
        
        //创建表头,第一行
        for (int i = 0; i < title.length; i++) {
            HSSFCell titleCell= titleRow.createCell(i);
            titleCell.setCellValue(title[i]);
        }
        
        //从第二行开始创建主体内容
        for (int i = 1; i <= sheetBodyValue.length; i++) {
            HSSFRow bodyRow = sheet.createRow(i);//创建第i行
            for (int j = 0; j < sheetBodyValue[0].length; j++) {
                HSSFCell bodyCell = bodyRow.createCell(j);
                bodyCell.setCellValue(sheetBodyValue[i-1][j]);
            }
        }
        //创建文件
        File file = new File("D:/poi_text.xls");
        try {
            file.createNewFile();
            FileOutputStream outputStream = FileUtils.openOutputStream(file);
            workBook.write(outputStream);
            outputStream.close();//记得关闭输出流
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static void main(String[] args) {
        createExcel();
    }
}

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

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

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