POI解析excel表格 读与写

import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.util.ArrayList;
import java.util.List;

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;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.fastjson.JSONObject;

/**
 * @author Hongten
 * @created 2014-5-20
 */
public class ExcelUtils {

    private static final Logger log = LoggerFactory.getLogger(ExcelUtils.class);

    /**
     * read the Excel file
     * 
     * @param path
     *            the path of the Excel file
     * @return
     * @throws IOException
     */
    public List<String> readExcel(InputStream inputStream) throws IOException {
        if (!inputStream.markSupported()) {
            inputStream = new PushbackInputStream(inputStream, 8);
        }
        if (POIFSFileSystem.hasPOIFSHeader(inputStream)) {
            return readXls(inputStream);
        } else {
            // if (POIXMLDocument.hasOOXMLHeader(inputStream))
            return readXlsx(inputStream);
        }
    }

    /**
     * Read the Excel 2010
     * 
     * @param path
     *            the path of the excel file
     * @return
     * @throws IOException
     */
    public List<String> readXlsx(InputStream inputStream) throws IOException {
        log.info("readXlsX begin ");
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
        String entity = null;
        List<String> list = new ArrayList<String>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) {
                    XSSFCell userId = xssfRow.getCell(0);
                    XSSFCell mustIn = xssfRow.getCell(1);
                    JSONObject obj = new JSONObject();
                    obj.put("uId", getValue(userId,0));
                    obj.put("mustIn", getValue(mustIn,0));
                    entity = obj.toJSONString();
                    list.add(entity);
                }
            }
        }
        return list;
    }

    /**
     * Read the Excel 2003-2007
     * 
     * @param path
     *            the path of the Excel
     * @return
     * @throws IOException
     */
    public List<String> readXls(InputStream inputStream) throws IOException {
        log.info("readXls begin readXls");
        InputStream is = inputStream;
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        String entity = null;
        List<String> list = new ArrayList<String>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    HSSFCell userId = hssfRow.getCell(0);
                    HSSFCell mustIn = hssfRow.getCell(1);
                    JSONObject obj = new JSONObject();
                    obj.put("uId", getValue(userId,0));//此处有特殊处理 
                    obj.put("mustIn", getValue(mustIn,0));//此处有特殊处理
                    entity = obj.toJSONString();
                    list.add(entity);
                }
            }
        }
        return list;
    }

/**
 * 
 * @param hssfCell
 * @param status 0 只获取字符串值 
 * @return
 */
    @SuppressWarnings("static-access")
    private String getValue(HSSFCell hssfCell,int status) {
        if (0!=status) {
            if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
                return String.valueOf(hssfCell.getBooleanCellValue());
            } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
                return String.valueOf(hssfCell.getNumericCellValue());
            } else {
                return String.valueOf(hssfCell.getStringCellValue());
            }
        }else {
            hssfCell.setCellType(hssfCell.CELL_TYPE_STRING);
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

    @SuppressWarnings("static-access")
    private String getValue(XSSFCell xssfRow,int status) {
        if (0!=status) {
            if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
                return String.valueOf(xssfRow.getBooleanCellValue());
            } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
                return String.valueOf(xssfRow.getNumericCellValue());
            } else {
                return String.valueOf(xssfRow.getStringCellValue());
            }
        }else {
            xssfRow.setCellType(xssfRow.CELL_TYPE_STRING);
            return String.valueOf(xssfRow.getStringCellValue());
        }
    }
    /**
     * 写入数据到已有文件的某个单元格中
     * @param file
     * @param data
     * @param rowNum
     */
    public void writeExcel(File file,String data,int rowNum){
        try {  
            //传入的文件  
            FileInputStream fileInput = new FileInputStream(file);  
            // 创建一个webbook，对应一个Excel文件            
            XSSFWorkbook workbook = new XSSFWorkbook(fileInput);  
            //对应Excel文件中的sheet，0代表第一个             
            XSSFSheet sh = workbook.getSheetAt(0);  
            //修改excle表的第5行数据              
                //对第五行的数据修改  
            XSSFRow row = sh.getRow(rowNum);
             XSSFCell cell = row.getCell(4);
             //单元格是否为空
             if (cell!=null) {

                 cell .setCellValue(data);  
            }else{
                //创建角标为4的单元格
                cell = row.createCell(4);
            }
             //设置参数
             cell.setCellValue(data);
            //将修改后的文件写出到D:\\xxxx目录下   路径中 无此文件会报错
            FileOutputStream os = new FileOutputStream("D:\\xxxx.xlsx");  
            os.flush();  
            //将Excel写出        
            workbook.write(os);  
            //关闭流  
            fileInput.close();  
            os.close();  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
    }
}

maven 配置:

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-api -->
<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-api</artifactId>
    <version>1.7.21</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.44</version>
</dependency>