通过poi取出excel中的数据

由于项目需要，需要将用户UPLOAD的EXCEL表中的数据取出来，放进DB中，所以简单先写了个测试程序。

 

package com.uploadexcel;

import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Locale;

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;

public class TestUploadExcel {
	public static final int POI_NORMAL_FIELD = 0;

	public static final int POI_DATE_FIELD = 1;

	public static final int POI_NUM_AS_STR_FIELD = 2;

	public static final int PO_NUM_AS_DATE_FIELD = 3;

	public final static String NUMBER_FORMAT_0_PLACES = "######0";

	public static final String INPUT_DATE_FORMAT = "dd/MM/yyyy";

	public static final String INPUT_DATE_FORMAT_2 = "yyMMdd";

	public final static String INPUT_DATETIME_FORMAT = "dd/MM/yyyy HH:mm";

	public static final String INPUT_MONTHYEAR_FORMAT = "yyMM";

	public static final String OUTPUT_DATE_FORMAT = "dd/MM/yyyy";

	public final static String OUTPUT_DATETIME_FORMAT = "dd/MM/yyyy HH:mm";

	public static final String OUTPUT_MONTHYEAR_FORMAT = "yyMM";

	public final static Locale DEFAULT_LOCALE = Locale.US;

	public static boolean isEmpty(String str) {
		return (str == null || str.trim().length() == 0);
	}

	public static boolean isEmpty(Object obj) {
		String str = String.valueOf(obj);
		return (str == null || str.equals("null") || str.trim().length() == 0);
	}

	public static String formatNumber0Places(BigDecimal inNum) {
		return formatNumber(inNum, NUMBER_FORMAT_0_PLACES);
	}

	public static String dateToString(java.util.Date date) {
		if (date == null) {
			return "";
		}
		SimpleDateFormat formatter = new SimpleDateFormat(OUTPUT_DATE_FORMAT,
				DEFAULT_LOCALE);

		return formatter.format(date);
	}

	public static String formatNumber(BigDecimal inNum, String format) {
		if (inNum == null) {
			return "";
		}

		if (isEmpty(inNum)) {
			return "";
		}

		if (isEmpty(format)) {
			return "";
		}

		try {
			DecimalFormat myFormatter = new DecimalFormat(format);
			return myFormatter.format(inNum.doubleValue());
		} catch (Exception e) {
			return "";
		}
	}

	protected String poiReadCell(HSSFCell cell, int type) {
		try {
			String retStr = "";
			// System.out.println(">-------------------poiReadCell():" +
			// cell.getCellType());
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_STRING:
				if (type == TestUploadExcel.POI_NORMAL_FIELD) {
					retStr = cell.getStringCellValue();
				}
				break;
			case HSSFCell.CELL_TYPE_NUMERIC:
				if (type == TestUploadExcel.POI_NORMAL_FIELD) {
					retStr = TestUploadExcel
							.formatNumber0Places(new java.math.BigDecimal(cell
									.getNumericCellValue()));
				} else if (type == TestUploadExcel.POI_DATE_FIELD) {
					retStr = TestUploadExcel.dateToString(cell
							.getDateCellValue());
				}
				break;
			case HSSFCell.CELL_TYPE_FORMULA:
			case HSSFCell.CELL_TYPE_ERROR:
			case HSSFCell.CELL_TYPE_BLANK:
			case HSSFCell.CELL_TYPE_BOOLEAN:
				break;
			default:
				retStr = TestUploadExcel.dateToString(cell.getDateCellValue());
			}
			return retStr;
		} catch (Exception e) {
			System.err.println("err when poiReadCell : " + e.getMessage());
			return "";
		}
	}

	protected void extractExcel(String fileName) {
		try {
			java.io.FileInputStream fin = new java.io.FileInputStream(fileName);
			POIFSFileSystem posFile = new POIFSFileSystem(fin);
			HSSFWorkbook hssfBook = new HSSFWorkbook(posFile);

			HSSFSheet sheet = hssfBook.getSheetAt(0);
			int totalRow = sheet.getLastRowNum();
			System.out
					.println(">-------------------extractExcel(): lastRowNum="
							+ totalRow);
			// 客户表中的数据，有可能中间有连续几行没有数据，是空白ROW， 之后接着又有数据
			// 这里默认用户数据中空白的行最多允许有3行，多于3行的话，认为EXCEL到尾部。

			int nullFlag1 = 0;
			int nullFlag2 = 1;
			for (int j = 4; j <= totalRow; j++) {
				HSSFRow eRow = sheet.getRow(j);
				if (eRow == null
						|| isEmpty(poiReadCell(eRow.getCell((short) 0),
								TestUploadExcel.POI_NORMAL_FIELD))) {
					nullFlag2 = 1;
				} else {
					nullFlag1 = 0;
					nullFlag2 = 0;
				}
				nullFlag1 = nullFlag1 + nullFlag2;
				System.out.println(">&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& line"
						+ j);
				System.out.println(">&&&&&&&&&&&&&&&&& nullFlag1=" + nullFlag1);
				if (eRow == null && nullFlag1 <= 3)
					continue;
				if (nullFlag1 > 3)
					break;
				if (isEmpty(poiReadCell(eRow.getCell((short) 0),
						TestUploadExcel.POI_NORMAL_FIELD)))
					continue;
				for (int i = 0; i < 3; i++) {
					String cellValue = poiReadCell(eRow.getCell((short) i),
							TestUploadExcel.POI_NORMAL_FIELD);
					System.out.println(">------------------ cellValue" + i
							+ "=" + cellValue);
				}
			}
		} catch (Exception e) {
			System.err.println("err when extract Excel file : "
					+ e.getMessage());
		}
	}

	public static void main(String args[]) {
		TestUploadExcel test = new TestUploadExcel();
		test.extractExcel("D:/PorjectResearch/test.xls");
	}
}