java使用POI进行Excel读写

1 jar包下载

　　参考链接：http://www.cnblogs.com/gmq/archive/0001/01/01/1521496.html

　　poi-3.7-20101029.jar和源码下载地址：http://pan.baidu.com/s/1sjNmnc5#path=%252F04technology%252F01J2EE%252Fjar

2 ExcelHelper类

package zjr.amy.excel;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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;

/**
 * Excel帮助类
 * 
 * @author zhujinrong
 * 
 */
public class ExcelHelper {

    /**
     * 读取Excel内容
     * 
     * @param fileName
     *            文件名和路径
     * @param ignoreRows
     *            忽略读取的行数
     * @return 数据
     * @throws FileNotFoundException
     *             找不到文件
     * @throws IOException
     *             输入输出异常
     */
    public static String[][] getData(String fileName, int ignoreRows)
            throws FileNotFoundException, IOException {
        File file = new File(fileName);
        List<String[]> result = new ArrayList<String[]>();
        int rowSize = 0;
        BufferedInputStream in = new BufferedInputStream(new FileInputStream(
                file));
        POIFSFileSystem fs = new POIFSFileSystem(in);
        HSSFWorkbook workbook = new HSSFWorkbook(fs);
        HSSFCell cell = null;
        for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {

            // 可能有多个sheet
            HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
            for (int rowIndex = ignoreRows; rowIndex < sheet.getLastRowNum(); rowIndex++) {
                HSSFRow row = sheet.getRow(rowIndex);
                if (row == null) {
                    continue;
                }

                // 获取行数
                int tempRowSize = row.getLastCellNum() + 1;
                if (tempRowSize > rowSize) {
                    rowSize = tempRowSize;
                }

                String[] values = new String[rowSize];
                Arrays.fill(values, "");
                boolean hasValue = false;
                for (int collumnIndex = 0; collumnIndex <= row.getLastCellNum(); collumnIndex++) {
                    String value = "";
                    cell = row.getCell(collumnIndex);
                    if (cell != null) {
                        switch (cell.getCellType()) {
                        case HSSFCell.CELL_TYPE_STRING:
                            value = cell.getStringCellValue();
                            break;
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                Date date = cell.getDateCellValue();
                                if (date != null) {
                                    value = new SimpleDateFormat("yyyy-MM-dd")
                                            .format(date);
                                } else {
                                    value = "";
                                }
                            } else {
                                double d = cell.getNumericCellValue();
                                value = d + "";
                            }
                            break;
                        case HSSFCell.CELL_TYPE_FORMULA:
                            if (!cell.getStringCellValue().equals("")) {
                                value = cell.getStringCellValue();
                            } else {
                                value = cell.getNumericCellValue() + "";
                            }
                            break;
                        case HSSFCell.CELL_TYPE_BLANK:
                            break;
                        case HSSFCell.CELL_TYPE_ERROR:
                            break;
                        case HSSFCell.CELL_TYPE_BOOLEAN:
                            value = (cell.getBooleanCellValue() == true ? "Y"
                                    : "N");
                            break;
                        default:
                            value = "";
                        }
                        if (collumnIndex == 0 && value.trim().equals("")) {
                            break;
                        }
                        values[collumnIndex] = value.trim();
                        hasValue = true;
                    }
                }
                if (hasValue) {
                    result.add(values);
                }
            }
        }

        in.close();
        String[][] returnArray = new String[result.size()][rowSize];
        for (int i = 0; i < returnArray.length; i++) {
            returnArray[i] = (String[]) result.get(i);
        }

        return returnArray;
    }

    /**
     * 写Excel数据
     * 
     * @param fileName
     *            文件名
     * @param data
     *            数据
     * @param sheetName
     *            sheet名字
     * @throws IOException
     *             输入输出异常
     */
    public static void setData(String fileName, String[][] data,
            String sheetName) throws IOException {
        FileOutputStream fos = new FileOutputStream(fileName);
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet s = wb.createSheet();
        wb.setSheetName(0, sheetName);
        if (data != null && data.length > 0) {
            for (int i = 0; i < data.length; i++) {
                // 创建第i行
                HSSFRow row = s.createRow(i);
                for (int j = 0; j < data[i].length; j++) {
                    // 第i行第j列的数据
                    HSSFCell cell = row.createCell(j);
                    cell.setCellValue(data[i][j]);
                }
            }
        }

        // 写入工作薄
        wb.write(fos);

        // 关闭输出流
        fos.close();
    }
}

3  TestExcelHelper类

package zjr.amy.excel.test;

import java.io.FileNotFoundException;
import java.io.IOException;

import zjr.amy.excel.ExcelHelper;

public class TestExcelHelper {

    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        TestGetData();
    }

    /**
     * 测试获取数据
     */
    public static void TestGetData() {
        try {
            String[][] array = ExcelHelper.getData("D:/001code/100java学习/JavaUtil/doc/税费管理20141105100144.xls", 0);
            
            if (array != null && array.length > 0){
                for(int i = 0; i < array.length; i++){
                    System.out.print(i + ": ");
                    for(int j = 0; j < array[i].length; j ++){
                        System.out.print(array[i][j] + " ");
                    }
                    System.out.println();
                }
                
                setData(array, "税费信息");
            }
            
            
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    
    /**
     * 测试写入数据
     * @param data 数据
     * @param sheetName sheet名称
     */
    public static void setData(String [][] data, String sheetName){
        String fileName="D:/001code/100java学习/JavaUtil/doc/税费管理20141105100144Copy.xls";
        try {
            ExcelHelper.setData(fileName, data, sheetName);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

4 测试结果

　　1-1 待读取文件内容（D:\001code\100java学习\JavaUtil\doc\税费管理20141105100144.xls）

　　图1-2 显示读取税费管理20141105100144.xls文件内容

　　1-3 写入文件内容（D:\001code\100java学习\JavaUtil\doc\税费管理20141105100144Copy.xls）

5 git地址

　　https://github.com/HelloAmy/JavaStudy.git