1 package publicData;
  2 
  3 /*
  4  * Created on 2013-12-5
  5  *
  6  * TODO To change the template for this generated file go to
  7  * Window - Preferences - Java - Code Style - Code Templates
  8  */
  9 
 10 import java.io.BufferedInputStream;
 11 import java.io.File;
 12 import java.io.FileInputStream;
 13 import java.io.FileNotFoundException;
 14 import java.io.IOException;
 15 import java.text.DecimalFormat;
 16 import java.text.SimpleDateFormat;
 17 import java.util.ArrayList;
 18 import java.util.Arrays;
 19 import java.util.Date;
 20 import java.util.List;
 21 
 22 import org.apache.poi.hssf.usermodel.HSSFCell;
 23 import org.apache.poi.hssf.usermodel.HSSFDateUtil;
 24 import org.apache.poi.hssf.usermodel.HSSFRow;
 25 import org.apache.poi.hssf.usermodel.HSSFSheet;
 26 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 27 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
 28 
 29 public class InputExcel {
 30     
 31     //public static String[][] result;
 32   
 33   /*
 34   public static void main(String[] args) {
 35 
 36     String path="C:\\Users\\pclxz\\Desktop\\心协软件\\初品4(在这修改)\\首访记录.xls";//文件路径
 37     try {
 38       File files = new File(path);
 39       String[][] result = getData(files, 2);
 40       if (result != null) {
 41         int rowLength = result.length;
 42         for (int i = 0; i < rowLength; i++) {
 43           for (int j = 0; j < result[i].length; j++) {
 44             System.out.println(result[i][j]+"单元格ID:"+i+" "+j);
 45           }
 46           System.out.println();
 47         }
 48       }
 49     } catch (Exception e) {
 50       e.printStackTrace();
 51     }
 52   }
 53   */
 54   
 55   public static void  getExcel(String[][] result, String path) {
 56 
 57       try {
 58           File files = new File(path);
 59           result = getData(files, 2);
 60         } catch (Exception e) {
 61           e.printStackTrace();
 62         }
 63   }
 64   
 65   public static String[][] getData(File file, int firstrow) 
 66           throws FileNotFoundException, IOException {
 67   
 68     List<String[]> result = new ArrayList<String[]>();
 69     int rowSize = 0;
 70     BufferedInputStream in = new BufferedInputStream(new FileInputStream(file));
 71     POIFSFileSystem fs = new POIFSFileSystem(in);
 72     HSSFWorkbook wb = new HSSFWorkbook(fs);//工作表
 73     HSSFCell cell = null;
 74     //遍历工作表里的所有工作簿
 75     for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
 76   
 77     HSSFSheet st = wb.getSheetAt(sheetIndex);    //得到工作簿
 78     //遍历工作簿st的row
 79     for (int rowIndex = firstrow; rowIndex <= st.getLastRowNum(); rowIndex++) {
 80     
 81       HSSFRow row = st.getRow(rowIndex);//row
 82       if (row == null) {
 83           continue;
 84       }
 85       
 86       //int tempRowSize = row.getLastCellNum() + 1;//该row的数组大小
 87       int tempRowSize = row.getLastCellNum() ;//该row的数组大小
 88       if (tempRowSize > rowSize) {
 89         rowSize = tempRowSize;
 90       }
 91     //System.out.println(rowSize);     
 92     String[] values = new String[rowSize];//存放一个row的实例
 93     Arrays.fill(values, "");
 94     boolean hasValue = false;
 95     //读取一个row的实例并存放进values
 96     //System.out.println(row.getLastCellNum()); 
 97     for (short columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) {
 98       String value = "";
 99       cell = row.getCell(columnIndex);//row对应column的单元格cell
100       //判断cell类型并相应存储
101       if (cell != null) {
102         switch (cell.getCellType()) {
103         case HSSFCell.CELL_TYPE_STRING://读取的格式为字符串
104           value = cell.getStringCellValue();
105           break;
106         case HSSFCell.CELL_TYPE_NUMERIC://读取的格式为数组
107           //如果格式为日期格式，自定义格式输出
108           if (HSSFDateUtil.isCellDateFormatted(cell)) {
109             Date date = cell.getDateCellValue();
110             if (date != null) {
111               value = new SimpleDateFormat("yyyy-MM-dd")
112                   .format(date);
113             } else {
114               value = "";
115             }
116           } else {
117             //如果格式为数值，自定义格式输出
118             value = new DecimalFormat("0.00").format(cell
119                 .getNumericCellValue());
120           }
121           break;
122         case HSSFCell.CELL_TYPE_FORMULA:
123           // 导入时如果为公式生成的数据则无值
124           value = "";
125           break;
126           // 导入时如果为空
127         case HSSFCell.CELL_TYPE_BLANK:
128           break;
129         case HSSFCell.CELL_TYPE_ERROR:
130           value = "";
131           break;
132           // 导入时如果为BOOLEAN型 自定义格式输出
133         case HSSFCell.CELL_TYPE_BOOLEAN:
134           value = (cell.getBooleanCellValue() == true ? "Y"
135               : "N");
136           break;
137         default:
138           value = "";
139         }
140       }
141 
142       values[columnIndex] = rightTrim(value);//去掉字符串右边的空格，并赋值
143       hasValue = true;
144     }
145 
146     //一个row的实例values存入result
147     if (hasValue) {
148       result.add(values);
149     }
150   
151   }
152 
153   in.close();
154   String[][] returnArray = new String[result.size()][rowSize];
155   for (int i = 0; i < returnArray.length; i++) {
156     returnArray[i] = (String[]) result.get(i);
157   }
158     return returnArray;
159     }
160   return null;
161 
162   }
163 
164   /**
165    * 去掉字符串右边的空格
166    * 
167    * @param str 要处理的字符串
168    * @return 处理后的字符串
169    */
170   public static String rightTrim(String str) {
171     if (str == null) {
172       return "";
173     }
174     int length = str.length();
175     for (int i = length - 1; i >= 0; i--) {
176       if (str.charAt(i) != 0x20) {
177         break;
178       }
179       length--;
180     }
181     return str.substring(0, length);
182   }
183 
184   
185 }