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