读取excel文件的Java处理
需要导入的包
StringUtil.java
package com.beyou.util;
/**
* @author cwq
* @version 1.0 2016/5/20
* @since JDK1.7
*/
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Map;
import java.util.Random;
import java.util.UUID;
import java.util.regex.Pattern;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
public class StringUtil {
private static Log log = LogFactory.getLog(StringUtil.class);
public static final int GEN_UPPERCASE = 0;
public static final int GEN_LOWERCASE = 1;
public static final int GEN_CHINESE = 2;
public static final int GEN_NUMBER = 3;
public StringUtil() {
}
public static boolean isNull(String str) {
return str == null?true:str.trim().length() == 0;
}
public static String valueOf(Object obj) {
return obj == null?"":(obj instanceof String?(String)obj:(obj instanceof Object[]?join((Object[])((Object[])obj), ","):(obj instanceof String[]?join((String[])((String[])obj), ","):(obj instanceof double[]?join((double[])((double[])obj), ","):(obj instanceof long[]?join((long[])((long[])obj), ","):(obj instanceof float[]?join((float[])((float[])obj), ","):(obj instanceof int[]?join((int[])((int[])obj), ","):(obj instanceof short[]?join((short[])((short[])obj), ","):(obj instanceof byte[]?join((byte[])((byte[])obj), ","):String.valueOf(obj))))))))));
}
public static Integer getInt(String str) {
try {
return Integer.valueOf(Integer.parseInt(str.trim()));
} catch (Exception var1) {
return null;
}
}
public static Integer[] getIntArray(String str, String spliter) {
if(isNull(str)) {
return new Integer[0];
} else {
String[] strs = str.split(spliter);
ArrayList tmp = new ArrayList();
for(int i = 0; i < strs.length; ++i) {
Integer val = getInt(strs[i]);
if(val != null) {
tmp.add(val);
}
}
return (Integer[])tmp.toArray(new Integer[tmp.size()]);
}
}
public static Long getLong(String str) {
try {
return Long.valueOf(Long.parseLong(str.trim()));
} catch (Exception var1) {
return null;
}
}
public static Long[] getLongArray(String str, String spliter) {
if(isNull(str)) {
return new Long[0];
} else {
String[] strs = str.split(spliter);
ArrayList tmp = new ArrayList();
for(int i = 0; i < strs.length; ++i) {
Long val = getLong(strs[i]);
if(val != null) {
tmp.add(val);
}
}
return (Long[])tmp.toArray(new Long[tmp.size()]);
}
}
public static Boolean getBoolean(String str) {
try {
return Boolean.valueOf(Boolean.parseBoolean(str.trim()));
} catch (Exception var1) {
return null;
}
}
public static Boolean[] getBooleanArray(String str, String spliter) {
if(isNull(str)) {
return new Boolean[0];
} else {
String[] strs = str.split(spliter);
ArrayList tmp = new ArrayList();
for(int i = 0; i < strs.length; ++i) {
Boolean val = getBoolean(strs[i]);
if(val != null) {
tmp.add(val);
}
}
return (Boolean[])tmp.toArray(new Boolean[tmp.size()]);
}
}
public static Float getFloat(String str) {
try {
return Float.valueOf(Float.parseFloat(str.trim()));
} catch (Exception var1) {
return null;
}
}
public static Float[] getFloatArray(String str, String spliter) {
if(isNull(str)) {
return new Float[0];
} else {
String[] strs = str.split(spliter);
ArrayList tmp = new ArrayList();
for(int i = 0; i < strs.length; ++i) {
Float val = getFloat(strs[i]);
if(val != null) {
tmp.add(val);
}
}
return (Float[])tmp.toArray(new Float[tmp.size()]);
}
}
public static Double getDouble(String str) {
try {
return Double.valueOf(Double.parseDouble(str.trim()));
} catch (Exception var1) {
return null;
}
}
public static Double[] getDouble(String str, String spliter) {
if(isNull(str)) {
return new Double[0];
} else {
String[] strs = str.split(spliter);
ArrayList tmp = new ArrayList();
for(int i = 0; i < strs.length; ++i) {
Double val = getDouble(strs[i]);
if(val != null) {
tmp.add(val);
}
}
return (Double[])tmp.toArray(new Double[tmp.size()]);
}
}
public static Date getDate(String str, String format) {
try {
return (new SimpleDateFormat(format)).parse(str.trim());
} catch (Exception var2) {
return null;
}
}
public static String toUtf8String(String s) {
StringBuffer sb = new StringBuffer();
for(int i = 0; i < s.length(); ++i) {
char c = s.charAt(i);
if(c >= 0 && c <= 255) {
sb.append(c);
} else {
byte[] b;
try {
b = Character.toString(c).getBytes("utf-8");
} catch (Exception var7) {
log.error(var7);
b = new byte[0];
}
for(int j = 0; j < b.length; ++j) {
int k = b[j];
if(k < 0) {
k += 256;
}
sb.append("%" + Integer.toHexString(k).toUpperCase());
}
}
}
return sb.toString();
}
public static String changeEncoding(String oldString, String oldCharset, String newCharset) {
if(isNull(oldString)) {
return oldString;
} else if(isNull(newCharset)) {
return oldString;
} else {
if(oldCharset == null) {
oldCharset = "";
}
if(newCharset.trim().equalsIgnoreCase(oldCharset.trim())) {
return oldString;
} else {
try {
return isNull(oldCharset)?new String(oldString.getBytes(), newCharset):new String(oldString.getBytes(oldCharset), newCharset);
} catch (UnsupportedEncodingException var4) {
log.error("由于系统不支持编码[" + oldCharset + "]或者[" + newCharset + "]，因此未能进行转换，直接返回原字符串", var4);
return oldString;
}
}
}
}
public static boolean matchPattern(String str, String[] patterns) {
if(str != null && patterns != null) {
String[] var5 = patterns;
int var4 = patterns.length;
for(int var3 = 0; var3 < var4; ++var3) {
String p = var5[var3];
if(matchPattern(str, (String)p)) {
return true;
}
}
return false;
} else {
return false;
}
}
public static boolean matchPattern(String str, String pattern) {
return pattern == null?false:Pattern.matches(pattern, str);
}
public static String toUnicode(String strText, Map<Integer, String> mask) throws Exception {
StringBuilder sb = new StringBuilder();
for(int i = 0; i < strText.length(); ++i) {
char c = strText.charAt(i);
if(mask != null && mask.containsKey(Integer.valueOf(c))) {
sb.append((String)mask.get(Integer.valueOf(c)));
} else if(c > 128) {
sb.append("\\u" + Integer.toHexString(c));
} else {
sb.append(c);
}
}
return sb.toString();
}
public static String toUnicode(String strText) throws Exception {
return toUnicode(strText, (Map)null);
}
public static String random(int length, int... types) {
return generateRandomString(types, length);
}
public static String generateRandomString(int[] stringTypes, int length) {
int[] startChars = new int[100];
int[] endChars = new int[100];
int actLength = 0;
for(int random = 0; random < stringTypes.length && actLength <= startChars.length; ++random) {
if(stringTypes[random] == 2) {
startChars[actLength] = 19968;
endChars[actLength] = '龰';
++actLength;
} else if(stringTypes[random] == 1) {
startChars[actLength] = 97;
endChars[actLength] = 122;
++actLength;
} else if(stringTypes[random] == 0) {
startChars[actLength] = 65;
endChars[actLength] = 90;
++actLength;
} else if(stringTypes[random] == 3) {
startChars[actLength] = 48;
endChars[actLength] = 57;
++actLength;
}
}
Random var12 = new Random();
StringBuffer sb = new StringBuffer();
for(int i = 0; i < length; ++i) {
int idx = Math.abs(var12.nextInt()) % actLength;
int startChar = startChars[idx];
int endChar = endChars[idx];
char randChar = (char)(Math.abs(var12.nextInt()) % (endChar - startChar) + startChar);
sb.append(randChar);
}
return sb.toString();
}
public static boolean isEquals(String str1, String str2, boolean trim) {
str1 = trim(str1);
str2 = trim(str2);
return str1 != null?(str1.length() == 0?(trim?isNull(str2):str2 != null && str2.length() == 0):(trim?str1.equals(str2):str1.equals(str2))):str2 == null || str2.length() == 0 && trim;
}
public static String trim(String str) {
return str == null?str:str.trim();
}
public static String toTimeStr(long time, boolean showZero) {
long seconds = time / 1000L;
boolean minutes = false;
boolean hours = false;
boolean days = false;
int days1 = (int)seconds / 86400;
seconds %= 86400L;
int hours1 = (int)seconds / 3600;
seconds %= 3600L;
int minutes1 = (int)seconds / 60;
seconds %= 60L;
String str = "";
if(days1 > 0) {
str = days1 + "天" + (hours1 == 0 && !showZero?"":hours1 + "小时") + (minutes1 == 0 && !showZero?"":minutes1 + "分") + (seconds == 0L && !showZero?"":seconds + "秒");
} else if(hours1 > 0) {
str = hours1 + "小时" + (minutes1 == 0 && !showZero?"":minutes1 + "分") + (seconds == 0L && !showZero?"":seconds + "秒");
} else if(minutes1 > 0) {
str = minutes1 + "分" + (seconds == 0L && !showZero?"":seconds + "秒");
} else {
str = seconds + "秒";
}
return str;
}
public static String generateGUID() {
UUID uid = UUID.randomUUID();
return uid.toString().replaceAll("-", "").toUpperCase();
}
public static String join(byte[] arr, String spliter) {
StringBuilder sb = new StringBuilder();
if(arr != null) {
for(int i = 0; i < arr.length; ++i) {
if(i > 0) {
sb.append(spliter);
}
sb.append(arr[i]);
}
}
return sb.toString();
}
public static String join(short[] arr, String spliter) {
StringBuilder sb = new StringBuilder();
if(arr != null) {
for(int i = 0; i < arr.length; ++i) {
if(i > 0) {
sb.append(spliter);
}
sb.append(arr[i]);
}
}
return sb.toString();
}
public static String join(double[] arr, String spliter) {
StringBuilder sb = new StringBuilder();
if(arr != null) {
for(int i = 0; i < arr.length; ++i) {
if(i > 0) {
sb.append(spliter);
}
sb.append(arr[i]);
}
}
return sb.toString();
}
public static String join(float[] arr, String spliter) {
StringBuilder sb = new StringBuilder();
if(arr != null) {
for(int i = 0; i < arr.length; ++i) {
if(i > 0) {
sb.append(spliter);
}
sb.append(arr[i]);
}
}
return sb.toString();
}
public static String join(int[] arr, String spliter) {
StringBuilder sb = new StringBuilder();
if(arr != null) {
for(int i = 0; i < arr.length; ++i) {
if(i > 0) {
sb.append(spliter);
}
sb.append(arr[i]);
}
}
return sb.toString();
}
public static String join(long[] arr, String spliter) {
StringBuilder sb = new StringBuilder();
if(arr != null) {
for(int i = 0; i < arr.length; ++i) {
if(i > 0) {
sb.append(spliter);
}
sb.append(arr[i]);
}
}
return sb.toString();
}
public static String join(String[] arr, String spliter) {
StringBuilder sb = new StringBuilder();
if(arr != null) {
for(int i = 0; i < arr.length; ++i) {
if(i > 0) {
sb.append(spliter);
}
sb.append(arr[i]);
}
}
return sb.toString();
}
public static String join(Object[] arr, String spliter) {
StringBuilder sb = new StringBuilder();
if(arr != null) {
for(int i = 0; i < arr.length; ++i) {
if(i > 0) {
sb.append(spliter);
}
sb.append(valueOf(arr[i]));
}
}
return sb.toString();
}
public static int length(String str) {
if(str == null) {
return 0;
} else {
int length = 0;
char[] chars = str.toCharArray();
char[] var6 = chars;
int var5 = chars.length;
for(int var4 = 0; var4 < var5; ++var4) {
char c = var6[var4];
if(c >= 128) {
length += 2;
} else {
++length;
}
}
return length;
}
}
public static String escape(String src, String pre) {
if(src == null) {
return null;
} else {
StringBuffer tmp = new StringBuffer();
tmp.ensureCapacity(src.length() * 6);
for(int i = 0; i < src.length(); ++i) {
char j = src.charAt(i);
if(!Character.isDigit(j) && !Character.isLowerCase(j) && !Character.isUpperCase(j)) {
if(j < 256) {
tmp.append(pre);
if(j < 16) {
tmp.append("0");
}
tmp.append(Integer.toString(j, 16));
} else {
tmp.append(pre + "u");
tmp.append(Integer.toString(j, 16));
}
} else {
tmp.append(j);
}
}
return tmp.toString();
}
}
public static String escape(String src) {
return escape(src, "%");
}
public static String unescape(String src) {
return unescape(src, "%");
}
public static String unescape(String src, String pre) {
if(src == null) {
return src;
} else {
StringBuffer tmp = new StringBuffer();
tmp.ensureCapacity(src.length());
int lastPos = 0;
boolean pos = false;
while(lastPos < src.length()) {
int pos1 = src.indexOf(pre, lastPos);
if(pos1 == lastPos) {
char ch;
if(src.charAt(pos1 + 1) == 117) {
ch = (char)Integer.parseInt(src.substring(pos1 + 2, pos1 + 6), 16);
tmp.append(ch);
lastPos = pos1 + 6;
} else {
ch = (char)Integer.parseInt(src.substring(pos1 + 1, pos1 + 3), 16);
tmp.append(ch);
lastPos = pos1 + 3;
}
} else if(pos1 == -1) {
tmp.append(src.substring(lastPos));
lastPos = src.length();
} else {
tmp.append(src.substring(lastPos, pos1));
lastPos = pos1;
}
}
return tmp.toString();
}
}
public static String wildcard2Reg(String wildcard) {
StringBuilder sb = new StringBuilder();
sb.append("^");
char[] var5;
int var4 = (var5 = wildcard.toCharArray()).length;
for(int var3 = 0; var3 < var4; ++var3) {
char c = var5[var3];
switch(c) {
case '*':
sb.append(".+");
break;
case '.':
sb.append("\\.");
break;
case '?':
sb.append(".{1}");
break;
default:
sb.append(c);
}
}
sb.append("$");
return sb.toString();
}
public static void main(String[] args) throws Exception {
System.out.println("[" + escape("asdf\rkkk\nEEEE\r\n") + "]");
}
}
4个回复
chen_weiqiang 10年前 1楼
package com.beyou.util.service;
/**
* @author cwq
* @version 1.0 2016/5/20
* @since JDK1.7
*/
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;
public interface IFileRowIterator {
boolean hasNext();
int getRowNum();
Map<String, Object> nextRow();
Set<Entry<Integer, String>> getColumnInfos();
}
package com.beyou.util.service;
/**
* @author cwq
* @version 1.0 2016/5/20
* @since JDK1.7
*/
import com.beyou.util.StringUtil;
import java.io.File;
import java.util.Map;
public class FileRowIteratorFactory {
public FileRowIteratorFactory() {
}
public static IFileRowIterator buildIterator(File file, String type, Map<String, Object> params) throws Exception {
if(StringUtil.isNull(type)) {
return null;
} else {
if(type.lastIndexOf(".") != -1) {
type = type.substring(type.lastIndexOf(".") + 1);
}
if(!"xls".equalsIgnoreCase(type) && !"xlsx".equalsIgnoreCase(type)) {
throw new Exception("没有合适的文件迭代器 [" + type + "]");
} else {
return new ExcelFileRowIterator(file, params);
}
}
}
}
chen_weiqiang 10年前 2楼
package com.beyou.util.service;
import com.beyou.util.StringUtil;
/**
* @author cwq
* @version 1.0 2016/5/20
* @since JDK1.7
*/
import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelFileRowIterator implements IFileRowIterator {
private Sheet sheet;
private Integer[] headerRows;
private Integer dataStartRow = null;
private Integer dataEndRow = null;
private Integer maxCellIndex = Integer.valueOf(0);
private Map<Integer, String> colMaps = new HashMap();
private Set<Entry<Integer, String>> colEntrys = null;
private Integer nextDataRowIndex = null;
private Map<String, Object> nextRowData = new HashMap();
private SimpleDateFormat dateFmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
public ExcelFileRowIterator(File file, Map<String, Object> params) throws Exception {
Object book;
try {
book = new XSSFWorkbook(new FileInputStream(file));
} catch (Exception var6) {
try {
book = new HSSFWorkbook(new FileInputStream(file));
} catch (Exception var5) {
var5.printStackTrace();
throw new Exception("未知文件类型:" + file.getAbsolutePath());
}
}
Integer sheetIndex = StringUtil.getInt(StringUtil.valueOf(params.get("sheet_index")));
if(sheetIndex != null) {
this.sheet = ((Workbook)book).getSheetAt(sheetIndex.intValue());
} else {
this.sheet = ((Workbook)book).getSheetAt(0);
}
this.headerRows = StringUtil.getIntArray(String.valueOf(params.get("header_rows")), ",");
this.dataStartRow = StringUtil.getInt(String.valueOf(params.get("data_start_row")));
this.dataEndRow = StringUtil.getInt(String.valueOf(params.get("data_end_row")));
this.loadHeaders();
if(this.dataStartRow == null || this.dataStartRow.intValue() <= this.headerRows[this.headerRows.length - 1].intValue()) {
this.dataStartRow = this.headerRows[this.headerRows.length - 1];
}
if(this.dataEndRow != null && this.dataEndRow.intValue() < this.dataStartRow.intValue()) {
this.dataEndRow = null;
}
this.prepareNextRow(this.dataStartRow.intValue());
}
public Set<Entry<Integer, String>> getColumnInfos() {
return this.colEntrys;
}
public int getRowNum() {
return this.nextDataRowIndex.intValue();
}
public boolean hasNext() {
return this.nextRowData.size() > 0;
}
public Map<String, Object> nextRow() {
HashMap var2;
try {
var2 = new HashMap(this.nextRowData);
} finally {
this.prepareNextRow(this.nextDataRowIndex.intValue() + 1);
}
return var2;
}
private void prepareNextRow(int row) {
this.nextRowData.clear();
if(this.dataEndRow == null || row <= this.dataEndRow.intValue()) {
this.nextDataRowIndex = Integer.valueOf(row);
Row nextDataRow = this.sheet.getRow(this.nextDataRowIndex.intValue());
boolean isBlankRow = true;
if(nextDataRow != null) {
Entry entry;
Object cellVal;
for(Iterator var5 = this.colEntrys.iterator(); var5.hasNext(); this.nextRowData.put((String)entry.getValue(), cellVal == null?"":cellVal)) {
entry = (Entry)var5.next();
Cell cell = nextDataRow.getCell(((Integer)entry.getKey()).intValue());
cellVal = null;
if(cell == null) {
cellVal = null;
this.nextRowData.put((String)entry.getValue(), "");
} else if(cell.getCellType() == 0) {
if(HSSFDateUtil.isCellDateFormatted(cell)) {
Date df = cell.getDateCellValue();
if(df != null) {
cellVal = this.dateFmt.format(df);
} else {
cellVal = null;
}
} else {
DecimalFormat df1 = (DecimalFormat)DecimalFormat.getInstance();
df1.setMaximumFractionDigits(10);
df1.setGroupingUsed(false);
cellVal = df1.format(cell.getNumericCellValue());
}
} else if(cell.getCellType() == 1) {
cellVal = StringUtil.trim(cell.getStringCellValue());
} else if(cell.getCellType() == 2) {
switch(cell.getCachedFormulaResultType()) {
case 0:
cellVal = Double.valueOf(cell.getNumericCellValue());
break;
case 1:
cellVal = StringUtil.trim(cell.getStringCellValue());
break;
case 2:
case 3:
default:
cellVal = null;
break;
case 4:
cellVal = Boolean.valueOf(cell.getBooleanCellValue());
}
} else if(cell.getCellType() == 4) {
cellVal = Boolean.valueOf(cell.getBooleanCellValue());
} else {
cellVal = StringUtil.trim(cell.getStringCellValue());
}
if(cellVal != null) {
isBlankRow = false;
}
}
if(isBlankRow) {
this.nextRowData.clear();
}
}
}
}
private void loadHeaders() {
if(this.sheet != null) {
if(this.headerRows == null || this.headerRows.length == 0) {
this.headerRows = new Integer[]{Integer.valueOf(1)};
}
Arrays.sort(this.headerRows);
Integer[] var4 = this.headerRows;
int var3 = this.headerRows.length;
int var2 = 0;
while(var2 < var3) {
int r = var4[var2].intValue();
Row row = this.sheet.getRow(r - 1);
int cellIndex = 0;
while(true) {
if(cellIndex < 2147483647) {
Cell cell = row.getCell(cellIndex);
if(cell != null) {
String value = cell.getRichStringCellValue().getString();
if(!StringUtil.isNull(value)) {
this.colMaps.put(new Integer(cellIndex), StringUtil.trim(value));
}
++cellIndex;
continue;
}
if(this.maxCellIndex.intValue() < cellIndex) {
this.maxCellIndex = Integer.valueOf(cellIndex);
}
}
++var2;
break;
}
}
this.colEntrys = this.colMaps.entrySet();
}
}
public static void main(String[] args) throws Exception {
HashMap params = new HashMap();
// params.put("header_rows", "1,2");
params.put("header_rows", "1");
ExcelFileRowIterator it = new ExcelFileRowIterator(new File("C:\\Users\\Administrator\\Desktop\\成就卡_BEYOU_20160424.xls"), params);
while(it.hasNext()) {
Map row = it.nextRow();
System.out.println("------------------------------------");
System.out.println("项目 = " + row.get("项目"));
}
}
}
chen_weiqiang 10年前 3楼
前面两条有问题
package com.beyou.util.service;
/**
* @author cwq
* @version 1.0 2016/5/20
* @since JDK1.7
*/
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;
public interface IFileRowIterator {
boolean hasNext();
int getRowNum();
Map<String, Object> nextRow();
Set<Entry<Integer, String>> getColumnInfos();
}package com.beyou.util.service;
/**
* @author cwq
* @version 1.0 2016/5/20
* @since JDK1.7
*/
import com.beyou.util.StringUtil;
import java.io.File;
import java.util.Map;
public class FileRowIteratorFactory {
public FileRowIteratorFactory() {
}
public static IFileRowIterator buildIterator(File file, String type, Map<String, Object> params) throws Exception {
if(StringUtil.isNull(type)) {
return null;
} else {
if(type.lastIndexOf(".") != -1) {
type = type.substring(type.lastIndexOf(".") + 1);
}
if(!"xls".equalsIgnoreCase(type) && !"xlsx".equalsIgnoreCase(type)) {
throw new Exception("没有合适的文件迭代器 [" + type + "]");
} else {
return new ExcelFileRowIterator(file, params);
}
}
}
}
chen_weiqiang 10年前 4楼
package com.beyou.util.service;
import com.beyou.util.StringUtil;
/**
* @author cwq
* @version 1.0 2016/5/20
* @since JDK1.7
*/
import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelFileRowIterator implements IFileRowIterator {
private Sheet sheet;
private Integer[] headerRows;
private Integer dataStartRow = null;
private Integer dataEndRow = null;
private Integer maxCellIndex = Integer.valueOf(0);
private Map<Integer, String> colMaps = new HashMap();
private Set<Entry<Integer, String>> colEntrys = null;
private Integer nextDataRowIndex = null;
private Map<String, Object> nextRowData = new HashMap();
private SimpleDateFormat dateFmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
public ExcelFileRowIterator(File file, Map<String, Object> params) throws Exception {
Object book;
try {
book = new XSSFWorkbook(new FileInputStream(file));
} catch (Exception var6) {
try {
book = new HSSFWorkbook(new FileInputStream(file));
} catch (Exception var5) {
var5.printStackTrace();
throw new Exception("未知文件类型:" + file.getAbsolutePath());
}
}
Integer sheetIndex = StringUtil.getInt(StringUtil.valueOf(params.get("sheet_index")));
if(sheetIndex != null) {
this.sheet = ((Workbook)book).getSheetAt(sheetIndex.intValue());
} else {
this.sheet = ((Workbook)book).getSheetAt(0);
}
this.headerRows = StringUtil.getIntArray(String.valueOf(params.get("header_rows")), ",");
this.dataStartRow = StringUtil.getInt(String.valueOf(params.get("data_start_row")));
this.dataEndRow = StringUtil.getInt(String.valueOf(params.get("data_end_row")));
this.loadHeaders();
if(this.dataStartRow == null || this.dataStartRow.intValue() <= this.headerRows[this.headerRows.length - 1].intValue()) {
this.dataStartRow = this.headerRows[this.headerRows.length - 1];
}
if(this.dataEndRow != null && this.dataEndRow.intValue() < this.dataStartRow.intValue()) {
this.dataEndRow = null;
}
this.prepareNextRow(this.dataStartRow.intValue());
}
public Set<Entry<Integer, String>> getColumnInfos() {
return this.colEntrys;
}
public int getRowNum() {
return this.nextDataRowIndex.intValue();
}
public boolean hasNext() {
return this.nextRowData.size() > 0;
}
public Map<String, Object> nextRow() {
HashMap var2;
try {
var2 = new HashMap(this.nextRowData);
} finally {
this.prepareNextRow(this.nextDataRowIndex.intValue() + 1);
}
return var2;
}
private void prepareNextRow(int row) {
this.nextRowData.clear();
if(this.dataEndRow == null || row <= this.dataEndRow.intValue()) {
this.nextDataRowIndex = Integer.valueOf(row);
Row nextDataRow = this.sheet.getRow(this.nextDataRowIndex.intValue());
boolean isBlankRow = true;
if(nextDataRow != null) {
Entry entry;
Object cellVal;
for(Iterator var5 = this.colEntrys.iterator(); var5.hasNext(); this.nextRowData.put((String)entry.getValue(), cellVal == null?"":cellVal)) {
entry = (Entry)var5.next();
Cell cell = nextDataRow.getCell(((Integer)entry.getKey()).intValue());
cellVal = null;
if(cell == null) {
cellVal = null;
this.nextRowData.put((String)entry.getValue(), "");
} else if(cell.getCellType() == 0) {
if(HSSFDateUtil.isCellDateFormatted(cell)) {
Date df = cell.getDateCellValue();
if(df != null) {
cellVal = this.dateFmt.format(df);
} else {
cellVal = null;
}
} else {
DecimalFormat df1 = (DecimalFormat)DecimalFormat.getInstance();
df1.setMaximumFractionDigits(10);
df1.setGroupingUsed(false);
cellVal = df1.format(cell.getNumericCellValue());
}
} else if(cell.getCellType() == 1) {
cellVal = StringUtil.trim(cell.getStringCellValue());
} else if(cell.getCellType() == 2) {
switch(cell.getCachedFormulaResultType()) {
case 0:
cellVal = Double.valueOf(cell.getNumericCellValue());
break;
case 1:
cellVal = StringUtil.trim(cell.getStringCellValue());
break;
case 2:
case 3:
default:
cellVal = null;
break;
case 4:
cellVal = Boolean.valueOf(cell.getBooleanCellValue());
}
} else if(cell.getCellType() == 4) {
cellVal = Boolean.valueOf(cell.getBooleanCellValue());
} else {
cellVal = StringUtil.trim(cell.getStringCellValue());
}
if(cellVal != null) {
isBlankRow = false;
}
}
if(isBlankRow) {
this.nextRowData.clear();
}
}
}
}
private void loadHeaders() {
if(this.sheet != null) {
if(this.headerRows == null || this.headerRows.length == 0) {
this.headerRows = new Integer[]{Integer.valueOf(1)};
}
Arrays.sort(this.headerRows);
Integer[] var4 = this.headerRows;
int var3 = this.headerRows.length;
int var2 = 0;
while(var2 < var3) {
int r = var4[var2].intValue();
Row row = this.sheet.getRow(r - 1);
int cellIndex = 0;
while(true) {
if(cellIndex < 2147483647) {
Cell cell = row.getCell(cellIndex);
if(cell != null) {
String value = cell.getRichStringCellValue().getString();
if(!StringUtil.isNull(value)) {
this.colMaps.put(new Integer(cellIndex), StringUtil.trim(value));
}
++cellIndex;
continue;
}
if(this.maxCellIndex.intValue() < cellIndex) {
this.maxCellIndex = Integer.valueOf(cellIndex);
}
}
++var2;
break;
}
}
this.colEntrys = this.colMaps.entrySet();
}
}
public static void main(String[] args) throws Exception {
HashMap params = new HashMap();
// params.put("header_rows", "1,2");
params.put("header_rows", "1");
ExcelFileRowIterator it = new ExcelFileRowIterator(new File("C:\\Users\\Administrator\\Desktop\\成就卡_BEYOU_20160424.xls"), params);
while(it.hasNext()) {
Map row = it.nextRow();
System.out.println("------------------------------------");
System.out.println("项目 = " + row.get("项目"));
}
}
}