读取excel文件的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") + "]");
    }
}

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

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("项目"));
        }

    }
}