import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFWorkbook;
public class ImportExecl {
private String errorInfo;
// @描述:main测试方法
public static void main(String[] args) throws Exception {
System.out.println((System.currentTimeMillis()+ "").length());
ImportExecl poi = new ImportExecl();
List<Pointcard> list = poi.read("E:/book.xlsx");
if (list != null) {
for (int i = 0; i < list.size(); i++) {
System.out.print("第" + (i) + "行");
Pointcard cellList = list.get(i);
System.out.print(" " + cellList.getUserkey());
System.out.print(" " + cellList.getToken());
System.out.print(" " + cellList.getSearNo());
System.out.print(" " + cellList.getCardNo());
System.out.print(" " + cellList.getPsw());
System.out.print(" " + cellList.getSourceKey());
System.out.print(" " + cellList.getNum());
System.out.print(" " + cellList.getStartTime());
System.out.print(" " + cellList.getEndTime());
System.out.print(" " + cellList.getTvId());
System.out.println();
}
}
}
// @描述:读取数据
private List<Pointcard> read(Workbook wb) {
List<Pointcard> dataLst = new ArrayList<Pointcard>();
/** 得到第一个shell */
Sheet sheet = wb.getSheetAt(0);
/** 得到Excel的行数 */
final int totalRows = sheet.getLastRowNum();
int totalCells = 0;
System.out.println("Excel的行数::::" + sheet.getLastRowNum());
/** 得到Excel的列数 */
if (totalRows >= 1 && sheet.getRow(0) != null) {
totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
/** 循环Excel的行 */
for (int r = 1; r <= totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
//定义点卡对象 每一行代表一个对象
Pointcard pointcard = new Pointcard();
/** 循环Excel的列 */
inner: for (int c = 0; c < totalCells; c++) {
Cell cell = row.getCell(c);
String cellValue = "";
// 如果为空 本行数据不合法
if (null == cell) {
System.out.println("数据不合法:::理由::数据为空::第"+r+"行");
// 跳出内层循环
break inner;
}else {
if (c < 6) {// 每列前六个数据 为String类型
// 如果类型匹配 则加入到Pointcard对象中 否则终止内层循环
if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
cellValue = cell.getStringCellValue();
if (cellValue.length() <= 32) {
switch (c) {
case 0:
pointcard.setUserkey(cellValue);
break;
case 1:
pointcard.setToken(cellValue);
break;
case 2:
pointcard.setSearNo(cellValue);
break;
case 3:
pointcard.setCardNo(cellValue);
break;
case 4:
pointcard.setPsw(cellValue);
break;
case 5:
pointcard.setSourceKey(cellValue);
break;
}
}else {
System.out.println("数据不合法:::理由::数据类型不匹配 c<6");
break inner;
}
}else{
System.out.println("数据不合法:::理由::数据类型匹配 但是字段过长 c<6");
break inner;
}
}else if (c == 6) {// 每列的第七个数据为int类型
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
int amount = (int)(cell.getNumericCellValue());
pointcard.setNum(amount);
} else {
System.out.println("数据不合法:::理由::数据类型不匹配 不为int类型 c=6");
break inner;
}
}else if (c > 6 && c < 9) {//数据类型为Date类型
if (cell.getCellType() == HSSFCell.ENCODING_COMPRESSED_UNICODE) {
java.util.Date date = cell.getDateCellValue();
System.out.println(date.getTime());
if (c == 7) {
pointcard.setStartTime(date.getTime());
} else {
pointcard.setEndTime(date.getTime());
}
}else {
System.out.println("数据不合法:::理由::数据类型不匹配 不为long类型 c > 6 && c < 9");
break inner;
}
}else if (c==9) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
cellValue = cell.getStringCellValue();
pointcard.setTvId(cellValue);
}else {
System.out.println("数据不合法:::理由::数据类型不匹配 不为long类型 c =9");
break inner;
}
}
}
}
/** 保存第r行pointcard对象 */
if(pointcard.getUserkey()!=null&&pointcard.getToken()!=null&&pointcard.getSearNo()!=null
&&pointcard.getCardNo()!=null&&pointcard.getPsw()!=null
&&pointcard.getSourceKey()!=null&&pointcard.getNum()!=0
&&pointcard.getStartTime()!=null
&&pointcard.getEndTime()!=null
&&pointcard.getTvId()!=null){
dataLst.add(pointcard);
}
}
return dataLst;
}
// @描述:根据文件名读取excel文件
public List<Pointcard> read(String filePath) {
List<Pointcard> dataLst = new ArrayList<Pointcard>();
InputStream is = null;
try {
/** 验证文件是否合法 */
if (!validateExcel(filePath)) {
System.out.println(errorInfo);
return null;
}
/** 判断文件的类型,是2003还是2007 */
boolean isExcel2003 = true;
if (WDWUtil.isExcel2007(filePath)) {
isExcel2003 = false;
}
/** 调用本类提供的根据流读取的方法 */
File file = new File(filePath);
is = new FileInputStream(file);
dataLst = read(is, isExcel2003);
is.close();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (is != null) {
try {
is.close();
} catch (IOException e) {
is = null;
e.printStackTrace();
}
}
}
/** 返回最后读取的结果 */
return dataLst;
}
// @描述:根据流读取Excel文件
public List<Pointcard> read(InputStream inputStream,
boolean isExcel2003) {
List<Pointcard> dataLst = null;
try {
/** 根据版本选择创建Workbook的方式 */
Workbook wb = null;
if (isExcel2003) {
wb = new HSSFWorkbook(inputStream);
} else {
wb = new XSSFWorkbook(inputStream);
}
dataLst = read(wb);
} catch (IOException e) {
e.printStackTrace();
}
return dataLst;
}
// @描述:验证excel文件
public boolean validateExcel(String filePath) {
/** 检查文件名是否为空或者是否是Excel格式的文件 */
if (filePath == null
|| !(WDWUtil.isExcel2003(filePath) || WDWUtil
.isExcel2007(filePath))) {
errorInfo = "文件名不是excel格式";
return false;
}
/** 检查文件是否存在 */
File file = new File(filePath);
if (file == null || !file.exists()) {
errorInfo = "文件不存在";
return false;
}
return true;
}
}
// @描述:工具类
class WDWUtil {
// @描述:是否是2003的excel,返回true是2003
public static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\\.(?i)(xls)$");
}
// @描述:是否是2007的excel,返回true是2007
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
}
excel 格式为