java POI读取Excel文件

java POI读取Excel文件

代码语言：javascript复制

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
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 com.neusoft.counter.vo.LoginIdStaffNo;
public class ExcelDemo {

private static final Log log = LogFactory.getLog(ExcelDemo.class);

public List parseExcel(File in) {
    List arrayList = new ArrayList();

    FileInputStream fis = null;
    POIFSFileSystem fs = null;

    try {
        fis = new FileInputStream(in);
        fs = new POIFSFileSystem(fis);

        HSSFWorkbook wb = new HSSFWorkbook(fs);
        // first sheet
        HSSFSheet sheet = wb.getSheetAt(0);
        int lastRow = sheet.getLastRowNum();

        HSSFRow row = null;
        HSSFCell cell = null;
        int columnNum = row.getLastCellNum();
        String data[] = new String[2];

        // 读取Excel表格
        for (int i = 1; i <= lastRow; i  ) { // 行循环
            row = sheet.getRow(i);

            for (int j = 0; j < columnNum; j  ) { // 列循环
                cell = row.getCell((short) j);
                if (cell != null
                        && cell.getCellType() != HSSFCell.CELL_TYPE_BLANK) {
                    data[j] = cell.getStringCellValue().trim();

                }
            }

            // TODO add to List
        }

    } catch (FileNotFoundException e) {
        log.error(e);
    } catch (IOException e) {
        log.error(e);
    }

    return arrayList;

}

public void writeToExcel(Map map, File outFile) throws IOException {
    if (map == null) {
        log.info("没有输出到excel的数据！");
        return;
    }
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();

    // 标题
    HSSFRow title = sheet.createRow(0);
    HSSFCell userCell = title.createCell((short) 0), staffCell = title
            .createCell((short) 1), _infoCell = title.createCell((short) 2);
    userCell.setEncoding(HSSFCell.ENCODING_UTF_16);
    userCell.setCellValue("后台用户");
    staffCell.setEncoding(HSSFCell.ENCODING_UTF_16);
    staffCell.setCellValue("柜员号");
    _infoCell.setEncoding(HSSFCell.ENCODING_UTF_16);
    _infoCell.setCellValue("失败原因");

    for (Iterator itr = map.keySet().iterator(); itr.hasNext();) {
        String key = (String) itr.next();
        List list = (List) map.get(key);
        String info = "";
        if ("1".equals(key))
            info = "后台用户不存在";
        else if ("2".equals(key))
            info = "柜员号重复";
        else if ("3".equals(key))
            info = "插入数据库出错";

        appendToSheet(sheet, list, info);
    }

    FileOutputStream fos = new FileOutputStream(outFile);

    wb.write(fos);
    fos.close();

}

private void appendToSheet(HSSFSheet sheet, List datas, String info) {

    if (datas == null)
        return;
    int offset = sheet.getLastRowNum();
    int size = datas.size();
    for (int i = 0; i < size; i  ) {
        LoginIdStaffNo ls = (LoginIdStaffNo) datas.get(i);
        HSSFRow row = sheet.createRow(offset   i   1);
        row.createCell((short) 0).setCellValue(ls.getUserLoginId());
        row.createCell((short) 1).setCellValue(ls.getStaffNo());

        HSSFCell infoCell = row.createCell((short) 2);
        infoCell.setEncoding(HSSFCell.ENCODING_UTF_16);
        infoCell.setCellValue(info);
    }
}
 
}</pre> 

java excel poi

0 人点赞