解析xlsx與xls--使用2012poi.jar

y_keven發表於2013-10-10
1.匯入Jar包:poi-3.8-20120326.jar、poi-ooxml-3.8-20120326.jar、poi-ooxml-schemas-3.8-20120326.jar、xbean.jar、dom4j-1.6.1.jar
類開始================
package servlet;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcleTest {
 public static void main(String[] args) {
  String path = "E:/yy.xls";
  //String path = "E:/yy.xlsx";
  Map<String, List<String[]>> map = getExcle(path);
  List<String[]> list = map.get("Sheet1");
  for (int i = 0; i < list.size(); i++) {
   String[] str = list.get(i);
   for (int j = 0; j < str.length; j++) {
    System.out.print(str[j] + "   |   ");
   }
   System.out.println("");
  }
 }
 /**
  *獲得excle的資料
  *
  * @param pathname
  * @return
  */
 public static Map<String, List<String[]>> getExcle(String pathname) {
  InputStream fs = null;
  Workbook wb = null;
  Map<String, List<String[]>> map = new HashMap<String, List<String[]>>();
  try {
   // excle的型別
   String readType = pathname.substring(pathname.lastIndexOf("."));
   File file = new File(pathname);
   if (file.exists()) {
    fs = new FileInputStream(file);
   } else {
    System.out.println("檔案不存在!");
   }
   if (readType.equals(".xls")) {
    wb = new HSSFWorkbook(fs);
   } else  {
    wb = new XSSFWorkbook(fs);
   }
   map = getExcleData_xls(wb);
  } catch (Exception e) {
   e.printStackTrace();
  }
  return map;
 }
 /**
  * 獲得excle xls格式的資料
  *
  * @param wb
  * @return
  */
 public static Map<String, List<String[]>> getExcleData_xls(Workbook wb) {
  Map<String, List<String[]>> map = new HashMap<String, List<String[]>>();
  try {
   if (wb != null) {
    // sheet個數
    int numSheet = wb.getNumberOfSheets();
    for (int i = 0; i < numSheet; i++) {
     Sheet sheet = wb.getSheetAt(i);
     String sheetname = sheet.getSheetName();
     List<String[]> listData = getSheetData(sheet); // 讀取sheet裡的資料
     listData = setMergedRegion(sheet, listData);
     map.put(sheetname, listData);
    }
   }
  } catch (Exception e) {
   e.printStackTrace();
  }
  return map;
 }
 /**
  * 獲得sheet 裡的資料
  *
  * @param sheet
  * @return
  */
 public static List<String[]> getSheetData(Sheet sheet) {
  List<String[]> listData = new ArrayList<String[]>();
  try {
   if (sheet != null) {
    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
     Row row = sheet.getRow(i);
     String[] rowData = getRowData(row);
     listData.add(rowData);
    }
   }
  } catch (Exception e) {
   e.printStackTrace();
  }
  return listData;
 }
 /**
  * 獲得row 的資料
  *
  * @param row
  * @return
  */
 public static String[] getRowData(Row row) {
  String[] rowData = null;
  try {
   if (row != null) {
    int numcell = row.getLastCellNum();
    rowData = new String[numcell];
    for (int i = 0; i < numcell; i++) {
     Cell cell = row.getCell(i);
     rowData[i] = getCellData(cell);
    }
   }
  } catch (Exception e) {
   e.printStackTrace();
  }
  return rowData;
 }
 /**
  * 獲得單元格的值
  * @param cell
  * @return
  */
 public static String getCellData(Cell cell) {
  String value = null;
  try {
   if (cell != null) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_NUMERIC: // 數值型
     if (DateUtil.isCellDateFormatted(cell)) {
      // 如果是Date型別則 ,獲取該Cell的Date值
      value =new SimpleDateFormat("yyyy-MM-dd").format(DateUtil
        .getJavaDate(cell.getNumericCellValue()));
     } else {// 純數字,這裡要判斷是否為小數的情況,因為整數在寫入時會被加上小數點
      String t = cell.getNumericCellValue() + "";
      BigDecimal n = new BigDecimal(cell
        .getNumericCellValue());
      // 判斷是否有小數點
      if (t.indexOf(".") < 0) {
       value = n.intValue() + "";
      } else {
       // 數字格式化物件
       NumberFormat nf = NumberFormat.getInstance();
       // 小數點最大兩位
       nf.setMaximumFractionDigits(2);
       // 執行格式化
       value = nf.format(n.doubleValue());
      }
     }
     break;
    case Cell.CELL_TYPE_STRING: // 字串型
     value = cell.getRichStringCellValue().toString();
     break;
    case Cell.CELL_TYPE_FORMULA:// 公式型
     // 讀公式計算值
     value = String.valueOf(cell.getNumericCellValue());
     break;
    case Cell.CELL_TYPE_BOOLEAN:// 布林
     value = " " + cell.getBooleanCellValue();
     break;
    /* 此行表示該單元格值為空 */
    case Cell.CELL_TYPE_BLANK: // 空值
     value = " ";
     break;
    case Cell.CELL_TYPE_ERROR: // 故障
     value = " ";
     break;
    default:
     value = cell.getRichStringCellValue().toString();
    }
   }
  } catch (Exception e) {
    e.printStackTrace();
  }
  return value;
 }
 /**
  * 處理單元格值相等的單元格
  *
  * @param sheet
  */
 @SuppressWarnings("unused")
 public static List<String[]> setMergedRegion(Sheet sheet,
   List<String[]> list) {
  int num = sheet.getNumMergedRegions();
  List<String[]> listDate = new ArrayList<String[]>();
  try {
   for (int i = 0; i < num; i++) {
    CellRangeAddress rangeAddress = sheet.getMergedRegion(i);
    int firstcell = rangeAddress.getFirstColumn();
    int firstrow = rangeAddress.getFirstRow();
    int lastcell = rangeAddress.getLastColumn();
    int lastrow = rangeAddress.getLastRow();
    // 處理合並行的值
    if (firstcell == lastcell) {
     for (int j = firstrow; j <= lastrow; j++) {
      list.get(j)[firstcell] = list.get(firstrow)[firstcell];
     }
    }
    // 處理合並列的值
    if (firstrow == lastrow) {
     for (int j = firstcell; j <= lastcell; j++) {
      list.get(firstrow)[j] = list.get(firstrow)[j];
     }
    }
    // 處理合並行列
    if (firstcell != lastcell && firstrow != lastrow) {
     for (int j = firstrow; j <= lastrow; j++) {
      for (int k = firstcell; k <= lastcell; k++) {
       list.get(j)[k] = list.get(firstrow)[firstcell];
      }
     }
    }
   }
   listDate = list;
  } catch (Exception e) {
   e.printStackTrace();
  }
  return list;
 }
}
類結束================
 

相關文章