java学习

java学习

 

poi读取excel

package com.scpii.ent.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.scpii.ent.mode.bean.DataSet;

public class ExcelOperClass {
 private static String EXCEL_2003 = ".xls";
 private static String EXCEL_2007 = ".xlsx";

 public static void readExcelJXL() {

 }

 /**
  * 通过POI方式读取Excel
  *
  * @param excelFile
  */
 public static DataSet readExcelPOI(String filePath, Integer cons) throws Exception {
  File excelFile = new File(filePath);
  if (excelFile != null) {
   String fileName = excelFile.getName();
   fileName = fileName.toLowerCase();
   if (fileName.toLowerCase().endsWith(EXCEL_2003)) {
    DataSet dataSet = readExcelPOI2003(excelFile, cons);
    return dataSet;
   }
   if (fileName.toLowerCase().endsWith(EXCEL_2007)) {
    DataSet dataSet = readExcelPOI2007(excelFile, cons);
    return dataSet;
   }
  }
  return null;
 }

 /**
  * 读取Excel2003的表单
  *
  * @param excelFile
  * @return
  * @throws Exception
  */
 private static DataSet readExcelPOI2003(File excelFile, Integer rCons)
   throws Exception {
  List<String[]> datasList = new ArrayList<String[]>();
  Set<String> colsSet = new HashSet<String>();
  InputStream input = new FileInputStream(excelFile);
  HSSFWorkbook workBook = new HSSFWorkbook(input);
  // 获取Excel的sheet数量
  Integer sheetNum = workBook.getNumberOfSheets();
  // 循环Sheet表单
  for (int i = 0; i < sheetNum; i++) {
   HSSFSheet sheet = workBook.getSheetAt(i);
   if (sheet == null) {
    continue;
   }
   // 获取Sheet里面的Row数量
   Integer rowNum = sheet.getLastRowNum() + 1;
   for (int j = 0; j < rowNum; j++) {
     if (j>rCons) {
     System.out.println("===========");
     HSSFRow row = sheet.getRow(j);
     if (row == null) {
      continue;
     }

     Integer cellNum = row.getLastCellNum() + 1;
     String[] datas = new String[cellNum];
     for (int k = 0; k < cellNum; k++) {
      HSSFCell cell = row.getCell(k);
      if (cell == null) {
       continue;
      }
      if (cell != null) {
       cell.setCellType(HSSFCell.CELL_TYPE_STRING);
       String cellValue = "";
       int cellValueType = cell.getCellType();
       if (cellValueType == cell.CELL_TYPE_STRING) {
        cellValue = cell.getStringCellValue();
       }
       if (cellValueType == cell.CELL_TYPE_NUMERIC) {
        Double number = cell.getNumericCellValue();
        
        System.out.println("字符串+++=========="+number.intValue());
        cellValue = cell.getNumericCellValue() + "";
       }

       if (rCons==k) {
        colsSet.add(cellValue);
       }

       System.out.println(cellValue);
       datas[k] = cellValue;
      }
     }
     datasList.add(datas);
    }
   }
  }
  DataSet dataSet = new DataSet(null, null, datasList, colsSet);
  return dataSet;
 }

 /**
  * 读取Excel2007的表单
  *
  * @param excelFile
  * @return
  * @throws Exception
  */
 private static DataSet readExcelPOI2007(File excelFile, Integer rCons) throws Exception {
  List<String[]> datasList = new ArrayList<String[]>();
  Set<String> cosSet = new HashSet<String>();
  InputStream input = new FileInputStream(excelFile);
  XSSFWorkbook workBook = new XSSFWorkbook(input);
  // 获取Sheet数量
  Integer sheetNum = workBook.getNumberOfSheets();
  for (int i = 0; i < sheetNum; i++) {
   XSSFSheet sheet = workBook.getSheetAt(i);
   if (sheet == null) {
    continue;
   }
   // 获取行值
   Integer rowNum = sheet.getLastRowNum() + 1;
   for (int j = 0; j < rowNum; j++) {
    if (j > rCons) {
     System.out.println("=============");
     XSSFRow row = sheet.getRow(j);
     if (row == null) {
      continue;
     }
     Integer cellNum = row.getLastCellNum() + 1;
     String[] datas = new String[cellNum];
     for (int k = 0; k < cellNum; k++) {
      XSSFCell cell = row.getCell(k);
      if (cell==null) {
       continue;
      }
      if (cell != null) {
       cell.setCellType(XSSFCell.CELL_TYPE_STRING);
       String cellValue = "";
       int cellValueType = cell.getCellType();
       if (cellValueType == cell.CELL_TYPE_STRING) {
        cellValue = cell.getStringCellValue();
       }
       if (cellValueType == cell.CELL_TYPE_NUMERIC) {
        Double number = cell.getNumericCellValue();
        System.out.println("字符串+++=========="+number.toString());
        cellValue = cell.getNumericCellValue() + "";
       }
       System.out.println(cellValue);
       if (rCons == k) {
        cosSet.add(cellValue);
       }
       datas[k] = cellValue;
      }
     }
     datasList.add(datas);
    }
   }
  }
  DataSet dataSet = new DataSet(null, null, datasList,cosSet);
  return dataSet;
 }

 public static void main(String[] args) {
//  try {
//   DataSet dataSet = readExcelPOI("D:\\部门员工资料.xls", 0);
//   System.out.println("================================");
//   Set<String> datas = dataSet.getConStrctSet();
//   String[] datastr = new String[datas.size()];
//   datastr = datas.toArray(datastr);
//   for (int i = 0; i < datastr.length; i++) {
//    System.out.println(datastr[i]);
//   }
//  } catch (Exception e) {
//   e.printStackTrace();
//  }
  
  System.out.println(52%4);
 }
}


package com.scpii.ent.mode.bean;

import java.util.ArrayList;
import java.util.List;
import java.util.Set;

public class DataSet {
 private String[] headers;
 private String[] rowHeaders;
 private List<String[]> datasList = new ArrayList<String[]>();
 private Set<String> conStrctSet;

 public DataSet(String[] headers, String[] rowHeaders,
   List<String[]> datasList, Set<String> conStrctSet) {
  this.headers = headers;
  this.rowHeaders = rowHeaders;
  this.datasList = datasList;
  this.conStrctSet = conStrctSet;
 }

 public DataSet(String[] header, String[] rowsHeader,
   List<String[]> datasList2) {
  this.headers = header;
  this.rowHeaders = rowsHeader;
  this.datasList = datasList2;
 }

 public String[] getHeaders() {
  return headers;
 }

 public void setHeaders(String[] headers) {
  this.headers = headers;
 }

 public String[] getRowHeaders() {
  return rowHeaders;
 }

 public void setRowHeaders(String[] rowHeaders) {
  this.rowHeaders = rowHeaders;
 }

 public List<String[]> getDatasList() {
  return datasList;
 }

 public void setDatasList(List<String[]> datasList) {
  this.datasList = datasList;
 }

 public Set<String> getConStrctSet() {
  return conStrctSet;
 }

 public void setConStrctSet(Set<String> conStrctSet) {
  this.conStrctSet = conStrctSet;
 }
}

posted on 2013-06-20 09:23 杨军威 阅读(509) 评论(0)  编辑  收藏


只有注册用户登录后才能发表评论。


网站导航:
 

导航

统计

常用链接

留言簿

随笔档案

搜索

最新评论

阅读排行榜

评论排行榜