Apache poi excel调用

 HSSF  提供读写Microsoft Excel格式档案的功能。 
  XSSF  提供读写Microsoft Excel OOXML格式档案的功能。 
  HWPF  提供读写Microsoft Word格式档案的功能。 
  HSLF  提供读写Microsoft PowerPoint格式档案的功能。 

  HDGF  提供读写Microsoft Visio格式档案的功能。 

编写excel：

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class TestPoi {

    public static String outputFile = "D:\\test4.xls";
    public static void main(String[] args) throws IOException {
        FileOutputStream out = null;
        Poi i1 = new Poi();
        i1.setAge(1);
        i1.setName("name1");
        Poi i2 = new Poi();
        i2.setAge(2);
        i2.setName("name2");
        
        List<Poi> list = new ArrayList<Poi>();
        list.add(i1);
        list.add(i2);
        try{
            //创建新的excle的工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            //在excle中建一工作表，其名为缺省值
            doss(workbook,list,"biao1","姓名","年年");   
            doss(workbook,list,"biao2","姓名1","年年1");  
            out = new FileOutputStream(outputFile);
            workbook.write(out);
            
            out.flush();
            
            System.out.println("文件生成");
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            out.close();
        }
    }
    
    public static void doss(HSSFWorkbook workbook,List<Poi> list,String name1,String name2,String name3){
      //在excle中建一工作表，其名为缺省值
        HSSFSheet sheet = workbook.createSheet(name1);
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFFont.COLOR_RED);
        
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);
        
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = row.createCell(0);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(name2);
        cell.setCellStyle(cellStyle);
        
        HSSFCell cell1 = row.createCell(1);
        cell1.setCellType(HSSFCell.<span style="font-family: Arial, Helvetica, sans-serif;">CELL_TYPE_STRING</span>);
        cell1.setCellValue(name3);
        cell1.setCellStyle(cellStyle);
        
        int size = list.size();
        HSSFRow ro = null;
        HSSFCell ce0 = null;
        HSSFCell ce1 = null;
        for(int i =0;i<size;i++){
            ro = sheet.createRow(i+1);
            ce0 = ro.createCell(0);
            ce0.setCellType(HSSFCell.CELL_TYPE_STRING);
            ce0.setCellValue(list.get(i).getName());
            
            ce1 = ro.createCell(1);
            ce1.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            ce1.setCellValue(list.get(i).getAge());
        }
    }
}

如果想生成一个加密的文件：可以用以下2中方法

 workbook.writeProtectWorkbook("password", "user");


 fileInput = new FileInputStream(fname);   
   bufferInput = new BufferedInputStream(fileInput);   
   poiFileSystem = new POIFSFileSystem(bufferInput);   
   Biff8EncryptionKey.setCurrentUserPassword("secret");   
   HSSFWorkbook workbook = new HSSFWorkbook(poiFileSystem, true);  

对应文件的解密：

 InputStream inp = new FileInputStream(excelPath);  
  
        if (prefix.toUpperCase().equals("XLS")) {  
            org.apache.poi.hssf.record.crypto.Biff8EncryptionKey  
                    .setCurrentUserPassword(password);  
            workbook = WorkbookFactory.create(inp);  
            inp.close();  
        } else {  
            POIFSFileSystem pfs = new POIFSFileSystem(inp);  
            inp.close();  
            EncryptionInfo encInfo = new EncryptionInfo(pfs);  
            Decryptor decryptor = Decryptor.getInstance(encInfo);  
            decryptor.verifyPassword(password);  
            workbook = new XSSFWorkbook(decryptor.getDataStream(pfs));  
        }  
  

读取excel：

 public static String outputFile = "D:\\test4.xls";
    public static void main(String[] args) throws IOException {
        try{
            //创建新的excle的工作簿
            HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(outputFile));
            
            HSSFSheet sheet = workbook.getSheet("biao1");
            HSSFRow row = sheet.getRow(0);
            int reoNum = sheet.getLastRowNum();
            int size = row.getLastCellNum();
            for(int i=0;i<size;i++){
                for(int j=0;j<reoNum;j++){
                    System.out.println(sheet.getRow(j).getCell(i).getStringCellValue());
                }
            }
        }catch(Exception e){
            e.printStackTrace();
        }
    }

如果我们需要读取该excel的所有文本：

public static String outputFile = "D:\\test4.xls";
    public static void main(String[] args) throws IOException {
        try{
            InputStream inp = new FileInputStream(outputFile);
            HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
            ExcelExtractor extractor = new ExcelExtractor(wb);
            //filter formulas from the results
            extractor.setFormulasNotResults(true);
            //filter Sheet name from the results
            extractor.setIncludeSheetNames(false);
            System.out.println(extractor.getText());
        }catch(Exception e){
            e.printStackTrace();
        }
    }