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