java读取excel文件 poi_java POI读取Excel文件

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);

}

}

}