基于POI的Excel工具类

基于POI的Excel工具类

/*
 * Excel工具类
 */
package com.chinainsurance.application.common;


import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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 org.apache.poi.ss.usermodel.Cell;

import com.chinainsurance.sysframework.common.util.DataUtils;
import com.chinainsurance.sysframework.common.util.StringUtils;
import com.chinainsurance.sysframework.exception.BusinessException;

public class ExcelUtils {
    /**
     * 从Excel文件得到二维数组，每个sheet的第一行为标题
     * 
     * @param file Excel文件
     * @return
     * @throws FileNotFoundException
     * @throws IOException
     */
    public static String[][] getData(File file) throws FileNotFoundException,
            IOException {
        return getData(file, 1);
    }

    /**
     * 从Excel文件得到二维数组
     * 
     * @param file Excel文件
     * @param ignoreRows 忽略的行数，通常为每个sheet的标题行数
     * @return
     * @throws FileNotFoundException
     * @throws IOException
     */
    public static String[][] getData(File file, int ignoreRows)
            throws FileNotFoundException, IOException {
        List<String[]> result = new ArrayList<String[]>();
        int rowSize = 0;
        BufferedInputStream in = new BufferedInputStream(new FileInputStream(
                file));
        // 打开HSSFWorkbook        
        POIFSFileSystem fs = new POIFSFileSystem(in);
        HSSFWorkbook wb = null;
        try{
        	wb = new HSSFWorkbook(fs);
        }catch (Exception e) {
        	throw new BusinessException("error.ExcelData",true);
		}
        HSSFCell cell = null;
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        DecimalFormat decimalFormat = new DecimalFormat("0.00");
        StringBuilder value = new StringBuilder();
        for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
            HSSFSheet st = wb.getSheetAt(sheetIndex); 
            // 第一行为标题，不取
            for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
                HSSFRow row = st.getRow(rowIndex);
                if (row == null) {
                    continue;
                }
                int tempRowSize = row.getLastCellNum() + 1;
                if (tempRowSize > rowSize) {
                    rowSize = tempRowSize;
                }
                String[] values = new String[rowSize];
                Arrays.fill(values, "");
                boolean hasValue = false;
                for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
                	value.setLength(0);
                    cell = row.getCell(columnIndex);
                    if (cell != null) {
                        // 注意：一定要设成这个，否则可能会出现乱码
                        //cell.setEncoding(HSSFCell.ENCODING_UTF_16);
                        switch (cell.getCellType()) {
                        case HSSFCell.CELL_TYPE_STRING:
                            value.append(cell.getStringCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                Date date = cell.getDateCellValue();
                                if (date != null) {
                                	value.append(dateFormat.format(date));
                                }
                            } else {
								value.append(decimalFormat.format(cell
										.getNumericCellValue()));
                            }
                            break;  
                        case HSSFCell.CELL_TYPE_FORMULA:
                            // 导入时如果为公式生成的数据则无值
                            if (cell.getStringCellValue().equals("")) {
								value.append(cell.getNumericCellValue());
                            } else {
                                value.append(cell.getStringCellValue());
                            }
                            break;
                        case HSSFCell.CELL_TYPE_BLANK:
                            break;
                        case HSSFCell.CELL_TYPE_ERROR:
                            break;
                        case HSSFCell.CELL_TYPE_BOOLEAN:
							value
									.append((cell.getBooleanCellValue() == true ? "Y"
											: "N"));
                            break;
                        default:
                        	value.setLength(0);
                        }
                    }
                    String s = value.toString().trim();
					if (columnIndex == 0 && s.equals("")) {//若第一列为空，则向后判断5列 若都为空则不导入该行数据
						if (row.getCell(new Short("1").shortValue()) == null
								&& row.getCell(new Short("2").shortValue()) == null
								&& row.getCell(new Short("3").shortValue()) == null
								&& row.getCell(new Short("4").shortValue()) == null
								&& row.getCell(new Short("5").shortValue()) == null) {
							break;
						}
					}
					values[columnIndex] = StringUtils.rightTrim(value
							.toString());
					hasValue = true;
					
					//到每行的第五列的时候开始判断前5列是否都为空,若都为空则不导入该行数据 (该方法需要与以上方法同时使用)
					if(columnIndex == 4){
						if("".equals(values[0]) && "".equals(values[1]) 
								&& "".equals(values[2]) && "".equals(values[3]) 
								&& "".equals(values[4])){
							hasValue = false;
							break;
						}
					}
                }

                if (hasValue) {
                    result.add(values);
                }
            }
        }
        in.close();
        String[][] returnArray = new String[result.size()][rowSize];
        for (int i = 0; i < returnArray.length; i++) {
            returnArray[i] = (String[]) result.get(i);
        }
        return returnArray;
    }
    
    /**
     * 解析一个Excel文件的某个特定的sheet
     * sheet号码从1开始
     * 
     * @param file excel文件
     * @param ignoreRows	忽略的行数
     * @param index	sheet的页码
     * @return
     * @throws FileNotFoundException
     * @throws IOException
     */
    public static String[][] getData(File file, int ignoreRows, int index)
			throws FileNotFoundException, IOException {
		ArrayList result = new ArrayList();
		int rowSize = 0;
		BufferedInputStream in = new BufferedInputStream(new FileInputStream(
				file));
		// 打开HSSFWorkbook
		POIFSFileSystem fs = new POIFSFileSystem(in);
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFCell cell = null;
		HSSFSheet st = wb.getSheetAt(index - 1);
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		DecimalFormat decimalFormat = new DecimalFormat("0.00");
		// 第一行为标题，不取
		for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
			HSSFRow row = st.getRow(rowIndex);
			if (row == null) {
				continue;
			}
			int tempRowSize = row.getLastCellNum() + 1;
			if (tempRowSize > rowSize) {
				rowSize = tempRowSize;
			}
			String[] values = new String[rowSize];
			Arrays.fill(values, "");
			boolean hasValue = false;
			StringBuilder value = new StringBuilder();
			for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
				value.setLength(0);
				cell = row.getCell(columnIndex);
				if (cell != null) {
					// 注意：一定要设成这个，否则可能会出现乱码
					//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					switch (cell.getCellType()) {
					case HSSFCell.CELL_TYPE_STRING:
						value.append(cell.getStringCellValue());
						break;
					case HSSFCell.CELL_TYPE_NUMERIC:
						if (HSSFDateUtil.isCellDateFormatted(cell)) {
							Date date = cell.getDateCellValue();
							if (date != null) {
								value.append(dateFormat.format(date));
							}
						} else {
							value.append(decimalFormat.format(cell
									.getNumericCellValue()));
						}
						break;
					case HSSFCell.CELL_TYPE_FORMULA:
						// 导入时如果为公式生成的数据则无值
						if (cell.getStringCellValue().equals("")) {
							value.append(cell.getNumericCellValue());
						} else {
							value.append(cell.getStringCellValue());
						}
						break;
					case HSSFCell.CELL_TYPE_BLANK:
						break;
					case HSSFCell.CELL_TYPE_ERROR:
						break;
					case HSSFCell.CELL_TYPE_BOOLEAN:
						value.append(cell.getBooleanCellValue() == true ? "Y"
								: "N");
						break;
					default:
						value.setLength(0);
					}
				}
				if (columnIndex == 0 && value.toString().trim().equals("")) {
					break;
				}
				values[columnIndex] = StringUtils.rightTrim(value.toString());
				hasValue = true;
			}

			if (hasValue) {
				result.add(values);
			}
		}

		in.close();
		String[][] returnArray = new String[result.size()][rowSize];
		for (int i = 0; i < returnArray.length; i++) {
			returnArray[i] = (String[]) result.get(i);
		}
		return returnArray;
	}
    /**
     * 
     * @param file
     * @param ignoreRows
     * @return
     * @throws FileNotFoundException
     * @throws IOException 
     */
    public static String[][] getDataForClaim(File file, int ignoreRows)
			throws FileNotFoundException, IOException {
		ArrayList result = new ArrayList();
		int rowSize = 0;
		BufferedInputStream in = new BufferedInputStream(new FileInputStream(
				file));
		// 打开HSSFWorkbook        
		POIFSFileSystem fs = new POIFSFileSystem(in);
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFCell cell = null;
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		DecimalFormat decimalFormat = new DecimalFormat("0.00");
		for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
			HSSFSheet st = wb.getSheetAt(sheetIndex);
			// 第一行为标题，不取
			for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
				HSSFRow row = st.getRow(rowIndex);
				if (row == null) {
					continue;
				}
				int tempRowSize = row.getLastCellNum() + 1;
				if (tempRowSize > rowSize) {
					rowSize = tempRowSize;
				}
				String[] values = new String[rowSize];
				Arrays.fill(values, "");
				boolean hasValue = false;
				StringBuilder value = new StringBuilder();
				for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
					value.setLength(0);
					cell = row.getCell(columnIndex);
					if (cell != null) {
						// 注意：一定要设成这个，否则可能会出现乱码
						//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
						switch (cell.getCellType()) {
						case HSSFCell.CELL_TYPE_STRING:
							value.append(cell.getStringCellValue());
							break;
						case HSSFCell.CELL_TYPE_NUMERIC:
							if (HSSFDateUtil.isCellDateFormatted(cell)) {
								Date date = cell.getDateCellValue();
								if (date != null) {
									value.append(dateFormat.format(date));
								}
							} else {
								value.append(decimalFormat.format(cell
										.getNumericCellValue()));
							}
							break;
						case HSSFCell.CELL_TYPE_FORMULA:
							// 导入时如果为公式生成的数据则无值
							if (cell.getStringCellValue().equals("")) {
								value.append(cell.getNumericCellValue());
							} else {
								value.append(cell.getStringCellValue());
							}
							break;
						case HSSFCell.CELL_TYPE_BLANK:
							break;
						case HSSFCell.CELL_TYPE_ERROR:
							break;
						case HSSFCell.CELL_TYPE_BOOLEAN:
							value
									.append((cell.getBooleanCellValue() == true ? "Y"
											: "N"));
							break;
						default:
							value.setLength(0);
						}
					}
					if (columnIndex == 0 && value.toString().trim().equals("")) {
						break;
					}
					values[columnIndex] = StringUtils.rightTrim(value
							.toString());
					hasValue = true;
				}

				if (hasValue) {
					result.add(values);
				}
			}
		}
		in.close();
		String[][] returnArray = new String[result.size()][rowSize];
		for (int i = 0; i < returnArray.length; i++) {
			returnArray[i] = (String[]) result.get(i);
		}
		return returnArray;
	}
    
    /**
     * 从Excel文件得到二维数组,和getDataForMotor的不同在于对数值型数据的处理上本方法默认格式为0.000
     * 
     * @param file Excel文件
     * @param ignoreRows 忽略的行数，通常为每个sheet的标题行数
     * @return
     * @throws FileNotFoundException
     * @throws IOException
     */
    public static String[][] getDataForMotor(File file, int ignoreRows)
            throws FileNotFoundException, IOException {
        ArrayList result = new ArrayList();
        int rowSize = 0;
        BufferedInputStream in = new BufferedInputStream(new FileInputStream(
                file));
        // 打开HSSFWorkbook        
        POIFSFileSystem fs = new POIFSFileSystem(in);
        HSSFWorkbook wb = null;
        try{
        	wb = new HSSFWorkbook(fs);
        }catch (Exception e) {
        	throw new BusinessException("error.ExcelData",true);
		}
        HSSFCell cell = null;
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        DecimalFormat decimalFormat = new DecimalFormat("0.000");
        StringBuffer value = new StringBuffer();
        for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
            HSSFSheet st = wb.getSheetAt(sheetIndex); 
            // 第一行为标题，不取
            for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
                HSSFRow row = st.getRow(rowIndex);
                if (row == null) {
                    continue;
                }
                int tempRowSize = row.getLastCellNum() + 1;
                if (tempRowSize > rowSize) {
                    rowSize = tempRowSize;
                }
                String[] values = new String[rowSize];
                Arrays.fill(values, "");
                boolean hasValue = false;
                for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
                	value.setLength(0);
                    cell = row.getCell(columnIndex);
                    if (cell != null) {
                        // 注意：一定要设成这个，否则可能会出现乱码
                        //cell.setEncoding(HSSFCell.ENCODING_UTF_16);
                        switch (cell.getCellType()) {
                        case HSSFCell.CELL_TYPE_STRING:
                            value.append(cell.getStringCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                Date date = cell.getDateCellValue();
                                if (date != null) {
                                	value.append(dateFormat.format(date));
                                }
                            } else {
								value.append(new Double(cell
										.getNumericCellValue()));
                            }
                            break;  
                        case HSSFCell.CELL_TYPE_FORMULA:
                            // 导入时如果为公式生成的数据则无值
                            if (cell.getStringCellValue().equals("")) {
								value.append(cell.getNumericCellValue());
                            } else {
                                value.append(cell.getStringCellValue());
                            }
                            break;
                        case HSSFCell.CELL_TYPE_BLANK:
                            break;
                        case HSSFCell.CELL_TYPE_ERROR:
                            break;
                        case HSSFCell.CELL_TYPE_BOOLEAN:
							value
									.append((cell.getBooleanCellValue() == true ? "Y"
											: "N"));
                            break;
                        default:
                        	value.setLength(0);
                        }
                    }
                    String s = value.toString().trim();
					if (columnIndex == 0 && s.equals("")) {//若第一列为空，则看第3列数据是否为空,为空则不导入该行数据
						if (row.getCell(new Short("2").shortValue()) == null) {
							break;
						}
					}
					values[columnIndex] = StringUtils.rightTrim(value
							.toString());
					hasValue = true;
                }

                if (hasValue) {
                    result.add(values);
                }
            }
        }
        in.close();
        String[][] returnArray = new String[result.size()][rowSize];
        for (int i = 0; i < returnArray.length; i++) {
            returnArray[i] = (String[]) result.get(i);
        }
        return returnArray;
    }
    
    /**
	 * 
	 * @param file
	 * @param ignoreRows
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public static String[][] getDataForReins(File file, int ignoreRows)
			throws FileNotFoundException, IOException {
		// 存在getLastRowNum取到的最終行數不正確的情況，所以增加一個標誌協助判斷，即某一列的數據是必須錄入的，一旦該列沒有數據，即認為行數到此終止//意外醫療判斷人員序號有沒有值。
		boolean endFalg = false;
		ArrayList result = new ArrayList();
		int rowSize = 0;
		BufferedInputStream in = new BufferedInputStream(new FileInputStream(
				file));
		// 打开HSSFWorkbook
		POIFSFileSystem fs = new POIFSFileSystem(in);
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFCell cell = null;
		for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
			HSSFSheet st = wb.getSheetAt(sheetIndex);
			// 第一行为标题，不取
			for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
				HSSFRow row = st.getRow(rowIndex);
				if (row == null) {
					continue;
				}
				int tempRowSize = row.getLastCellNum() + 1;
				if (tempRowSize > rowSize) {
					rowSize = tempRowSize;
				}
				String[] values = new String[rowSize];
				Arrays.fill(values, "");
				boolean hasValue = false;
				for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
					String value = "";
					cell = row.getCell(columnIndex);
					if (cell != null) {
						// 注意：一定要设成这个，否则可能会出现乱码
//						cell.setEncoding(HSSFCell.ENCODING_UTF_16);
						switch (cell.getCellType()) {
						case HSSFCell.CELL_TYPE_STRING:
							value = cell.getStringCellValue();
							break;
						case HSSFCell.CELL_TYPE_NUMERIC:
							if (HSSFDateUtil.isCellDateFormatted(cell)) {
								Date date = cell.getDateCellValue();
								if (date != null) {
									value = new SimpleDateFormat("yyyy-MM-dd")
											.format(date);
								} else {
									value = "";
								}
							} else {
								Double doubleValue = DataUtils.round(cell.getNumericCellValue(), 9);
								if(doubleValue%1==0) {
									value = String.valueOf(doubleValue.intValue());
								} else {
									value = doubleValue.toString();
								}
							}
							break;
						case HSSFCell.CELL_TYPE_FORMULA:
							// 导入时如果为公式生成的数据则无值
							Double doubleValue = DataUtils.round(cell.getNumericCellValue(), 9);
							if(doubleValue%1==0) {
								value = String.valueOf(doubleValue.intValue());
							} else {
								value = doubleValue.toString();
							}
//							if (!cell.getStringCellValue().equals("")) {
//								value = cell.getStringCellValue();
//							} else {
//								value = cell.getNumericCellValue() + "";
//							}
							break;
						case HSSFCell.CELL_TYPE_BLANK:
							break;
						case HSSFCell.CELL_TYPE_ERROR:
							value = "";
							break;
						case HSSFCell.CELL_TYPE_BOOLEAN:
							value = (cell.getBooleanCellValue() == true ? "Y"
									: "N");
							break;
						default:
							value = "";
						}
					}
					if (columnIndex == 0 && value.trim().equals("")) {
						endFalg = true;
						break;
					}
					values[columnIndex] = StringUtils.rightTrim(value);
					hasValue = true;
				}
				if (endFalg) {
					break;
				}
				if (hasValue) {
					result.add(values);
				}
			}
		}
		in.close();
		String[][] returnArray = new String[result.size()][rowSize];
		for (int i = 0; i < returnArray.length; i++) {
			returnArray[i] = (String[]) result.get(i);
		}
		return returnArray;
	}
	
	 	/**
		 * 
		 * @param file
		 * @param ignoreRows
		 * @return
		 * @throws FileNotFoundException
		 * @throws IOException
		 */
		public static String[][] getDataForPlatform(File file, int ignoreRows)
				throws FileNotFoundException, IOException {
			// 存在getLastRowNum取到的最終行數不正確的情況，所以增加一個標誌協助判斷，即某一列的數據是必須錄入的，一旦該列沒有數據，即認為行數到此終止//意外醫療判斷人員序號有沒有值。
			boolean endFalg = false;
			ArrayList result = new ArrayList();
			int rowSize = 0;
			BufferedInputStream in = new BufferedInputStream(new FileInputStream(
					file));
			// 打开HSSFWorkbook
			POIFSFileSystem fs = new POIFSFileSystem(in);
			HSSFWorkbook wb = new HSSFWorkbook(fs);
			HSSFCell cell = null;
			for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
				HSSFSheet st = wb.getSheetAt(sheetIndex);
				// 第一行为标题，不取
				for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
					HSSFRow row = st.getRow(rowIndex);
					if (row == null) {
						continue;
					}
					int tempRowSize = row.getLastCellNum() + 1;
					if (tempRowSize > rowSize) {
						rowSize = tempRowSize;
					}
					String[] values = new String[rowSize];
					Arrays.fill(values, "");
					boolean hasValue = false;
					for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
						String value = "";
						cell = row.getCell(columnIndex);
						if (cell != null) {
							// 注意：一定要设成这个，否则可能会出现乱码
//							cell.setEncoding(HSSFCell.ENCODING_UTF_16);
							switch (cell.getCellType()) {
							case HSSFCell.CELL_TYPE_STRING:
								value = cell.getStringCellValue();
								break;
							case HSSFCell.CELL_TYPE_NUMERIC:
								if (HSSFDateUtil.isCellDateFormatted(cell)) {
									Date date = cell.getDateCellValue();
									if (date != null) {
										value = new SimpleDateFormat("yyyy-MM-dd")
												.format(date);
									} else {
										value = "";
									}
								} else {
									Double doubleValue = DataUtils.round(cell.getNumericCellValue(), 9);
									if(doubleValue%1==0) {
										value = String.valueOf(doubleValue.intValue());
									} else {
										value = doubleValue.toString();
									}
								}
								break;
							case HSSFCell.CELL_TYPE_FORMULA:
								// 导入时如果为公式生成的数据则无值
								Double doubleValue = DataUtils.round(cell.getNumericCellValue(), 9);
								if(doubleValue%1==0) {
									value = String.valueOf(doubleValue.intValue());
								} else {
									value = doubleValue.toString();
								}
//								if (!cell.getStringCellValue().equals("")) {
//									value = cell.getStringCellValue();
//								} else {
//									value = cell.getNumericCellValue() + "";
//								}
								break;
							case HSSFCell.CELL_TYPE_BLANK:
								break;
							case HSSFCell.CELL_TYPE_ERROR:
								value = "";
								break;
							case HSSFCell.CELL_TYPE_BOOLEAN:
								value = (cell.getBooleanCellValue() == true ? "Y"
										: "N");
								break;
							default:
								value = "";
							}
						}
						if (columnIndex == 2 && value.trim().equals("")) {
							endFalg = true;
							break;
						}
						values[columnIndex] = StringUtils.rightTrim(value);
						hasValue = true;
					}
					if (endFalg) {
						break;
					}
					if (hasValue) {
						result.add(values);
					}
				}
			}
			in.close();
			String[][] returnArray = new String[result.size()][rowSize];
			for (int i = 0; i < returnArray.length; i++) {
				returnArray[i] = (String[]) result.get(i);
			}
			return returnArray;
		}
		/**
		 * 解决Excle导入后面多列为空的BUG
		 * @param file Excel文件
		 * @param ignoreRows 忽略行数
		 * @return
		 * @throws FileNotFoundException
		 * @throws IOException
		 */
		 public static String[][] getDataNew(File file, int ignoreRows)
		            throws FileNotFoundException, IOException {
		        List<String[]> result = new ArrayList<String[]>();
		        BufferedInputStream in = new BufferedInputStream(new FileInputStream(
		                file));
		        // 打开HSSFWorkbook        
		        POIFSFileSystem fs = new POIFSFileSystem(in);
		        HSSFWorkbook wb = null;
		        try{
		        	wb = new HSSFWorkbook(fs);
		        }catch (Exception e) {
		        	throw new BusinessException("error.ExcelData",true);
				}
		        HSSFCell cell = null;
		        int cellLength = 0;
		        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		        DecimalFormat decimalFormat = new DecimalFormat("0.00");
		        StringBuilder value = new StringBuilder();
		        for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
		            HSSFSheet st = wb.getSheetAt(sheetIndex); 
		            // 第一行为标题，不取
		            for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
		                HSSFRow row = st.getRow(rowIndex);
		                if (row == null) {
		                    continue;
		                }
		                String[] values = null;
		                try {
		                	Field field = row.getClass().getDeclaredField("cells");
		                	field.setAccessible(true);
							Cell[] cells = (Cell[])field.get(row);
							values = new String[cells.length];
							cellLength = cells.length;
							 Arrays.fill(values, "");
				                for (int i = 0; i < cells.length; i++ ) {
				                	value.setLength(0);
				                    cell = (HSSFCell) cells[i];
				                    if (cell != null) {
				                        // 注意：一定要设成这个，否则可能会出现乱码
				                        //cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				                        switch (cell.getCellType()) {
				                        case HSSFCell.CELL_TYPE_STRING:
				                            value.append(cell.getStringCellValue());
				                            break;
				                        case HSSFCell.CELL_TYPE_NUMERIC:
				                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
				                                Date date = cell.getDateCellValue();
				                                if (date != null) {
				                                	value.append(dateFormat.format(date));
				                                }
				                            } else {
												value.append(decimalFormat.format(cell
														.getNumericCellValue()));
				                            }
				                            break;  
				                        case HSSFCell.CELL_TYPE_FORMULA:
				                            // 导入时如果为公式生成的数据则无值
				                            if (cell.getStringCellValue().equals("")) {
												value.append(cell.getNumericCellValue());
				                            } else {
				                                value.append(cell.getStringCellValue());
				                            }
				                            break;
				                        case HSSFCell.CELL_TYPE_BLANK:
				                            break;
				                        case HSSFCell.CELL_TYPE_ERROR:
				                            break;
				                        case HSSFCell.CELL_TYPE_BOOLEAN:
											value
													.append((cell.getBooleanCellValue() == true ? "Y"
															: "N"));
				                            break;
				                        default:
				                        	value.setLength(0);
				                        }
				                    }
									values[i] = StringUtils.rightTrim(value
											.toString());
				                }
						} catch (IllegalArgumentException e) {
							e.printStackTrace();
						} catch (SecurityException e) {
							e.printStackTrace();
						} catch (IllegalAccessException e) {
							e.printStackTrace();
						} catch (NoSuchFieldException e) {
							e.printStackTrace();
						}
		               
		                result.add(values);
		            }
		        }
		        in.close();
		        String[][] returnArray = new String[result.size()][cellLength];
		        for (int i = 0; i < returnArray.length; i++) {
		            returnArray[i] = (String[]) result.get(i);
		        }
		        return returnArray;
		    }

		/**
	     * 解析一个Excel文件的某个特定的sheet
	     * sheet号码从1开始
	     * 
	     * @param file excel文件
	     * @param ignoreRows	忽略的行数
	     * @param index	sheet的页码
	     * @return
	     * @throws FileNotFoundException
	     * @throws IOException
	     */
	    public static String[][] getDataIncludeEmpty(File file, int ignoreRows, int index)
				throws FileNotFoundException, IOException {
			ArrayList result = new ArrayList();
			int rowSize = 0;
			BufferedInputStream in = new BufferedInputStream(new FileInputStream(
					file));
			// 打开HSSFWorkbook
			POIFSFileSystem fs = new POIFSFileSystem(in);
			HSSFWorkbook wb = new HSSFWorkbook(fs);
			HSSFCell cell = null;
			HSSFSheet st = wb.getSheetAt(index - 1);
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
			DecimalFormat decimalFormat = new DecimalFormat("0.00");
			// 第一行为标题，不取
			for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
				HSSFRow row = st.getRow(rowIndex);
				if (row == null) {
					continue;
				}
				int tempRowSize = st.getRow(0).getLastCellNum() + 1;
				if (tempRowSize > rowSize) {
					rowSize = tempRowSize;
				}
				String[] values = new String[rowSize];
				Arrays.fill(values, "");
				boolean hasValue = false;
				StringBuilder value = new StringBuilder();
				for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
					value.setLength(0);
					cell = row.getCell(columnIndex);
					if (cell != null) {
						// 注意：一定要设成这个，否则可能会出现乱码
						//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
						value.append(cell.getStringCellValue());
					}
					/*if (columnIndex == 0 && value.toString().trim().equals("")) {
						break;
					}*/
					values[columnIndex] = StringUtils.rightTrim(value.toString());
					hasValue = true;
				}

				if (hasValue) {
					result.add(values);
				}
			}

			in.close();
			String[][] returnArray = new String[result.size()][rowSize];
			for (int i = 0; i < returnArray.length; i++) {
				returnArray[i] = (String[]) result.get(i);
			}
			return returnArray;
		}
}

本文地址：https://blog.csdn.net/qq_33735635/article/details/109289441