poi创建、解析Excel(还有高版本的xlsx格式的Excel创建)

需要导入包poi-3.9-20121203.jar commons-io-1.3.2.jar

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

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 poiExpExcel {

    public static void main(String[] args) {

        String [] title = {"id","name","sex"};
        //创建excel工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建一个工作表sheet
        HSSFSheet sheet = workbook.createSheet();
        //创建第一行
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = null;
        //插入第一行数据
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
        }
        //追加数据
        for(int i=1;i<10;i++){
            HSSFRow nextRow = sheet.createRow(i);
            HSSFCell cell0 =  nextRow.createCell(0);
            cell0.setCellValue("a"+i);
            HSSFCell cell1 =  nextRow.createCell(1);
            cell1.setCellValue("user"+i);
            HSSFCell cell2 =  nextRow.createCell(2);
            cell2.setCellValue("男");
        }

        //创建一个文件
        File file = new File("e:/poi_test.xls");
        try {
            file.createNewFile();
            //创建输出流 
            FileOutputStream stream = FileUtils.openOutputStream(file);
            //将拼好的Excel写入到文件流
            workbook.write(stream);
            //关闭输出流
            stream.close();

        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}

poi解析Excel

import java.io.File;
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 poiReadExcel {

    /**
     * poi 解析Excel
     * @param args
     */
    public static void main(String[] args) {

        //引入需要解析的Excel文件
        File file = new File("e:/poi_test.xls");
        try {
            //创建Excel,读取文件内容
            HSSFWorkbook workbook = new HSSFWorkbook(FileUtils.openInputStream(file));
            //获取第一个工作表(以下两种方式)
//          HSSFSheet sheet = workbook.getSheet("sheet0");
            HSSFSheet sheet = workbook.getSheetAt(0);

            int firstRowNum =0;
            //获取sheet最后一行
            int lastRowNum = sheet.getLastRowNum();
            for (int i = firstRowNum; i <= lastRowNum; i++) {
                HSSFRow row = sheet.getRow(i);
                //获取当前行最后单元格列号
                int lastCellNum = row.getLastCellNum();
                for (int j = 0; j < lastCellNum; j++) {
                    HSSFCell cell = row.getCell(j);
                    String value = cell.getStringCellValue();
                    System.out.print(value+"  ");
                }
                System.out.println();
            }

        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

}

**有的需求是要生成xlsx这种高版本的Excel，这也简单（
1.在新建workbook的时候把HSSFWorkbook改为XSSFWorkbook,
2.然后把所有的“HSSF”替换为空
3.把生成文件的后缀改为“.xlsx”即可
）**

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

import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;



public class poiExpExcel2 {

    public static void main(String[] args) {

        String [] title = {"id","name","sex"};
        //创建excel工作薄
        XSSFWorkbook workbook= new XSSFWorkbook();
        //创建一个工作表sheet
        Sheet sheet = workbook.createSheet();
        //创建第一行
        Row row = sheet.createRow(0);
        Cell cell = null;
        //插入第一行数据
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
        }
        //追加数据
        for(int i=1;i<10;i++){
            Row nextRow = sheet.createRow(i);
            Cell cell0 =  nextRow.createCell(0);
            cell0.setCellValue("a"+i);
            Cell cell1 =  nextRow.createCell(1);
            cell1.setCellValue("user"+i);
            Cell cell2 =  nextRow.createCell(2);
            cell2.setCellValue("男");
        }

        //创建一个文件
        File file = new File("e:/poi_test.xlsx");
        try {
            file.createNewFile();
            //创建输出流 
            FileOutputStream stream = FileUtils.openOutputStream(file);
            //将拼好的Excel写入到文件流
            workbook.write(stream);
            //关闭输出流
            stream.close();

        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}