- 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);
- }
- }
- }
来源: http://www.phpxs.com/code/1002371/