package cn.test;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
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;
/**
* @function:excel导入的工具类
* @author: zhangchunping
* @email: zhangch_hk@163.com
* @createTime:Jun 7, 2012 10:04:09 AM
* @ModifyTime:Jun 7, 2012 10:04:09 AM
* @modifyUser:zhangchunping
* @modifyEmail:zhangch_hk@163.com
*/
public final class ExcelUtil {
public static int resultCode; // 返回的结果状态1成功,0失败
public static String resultMessage; // 返回的结果消息
//private static final String[] tables = new String[13];
static {
resultCode = 0;
resultMessage = "No Init";
}
public static List<De_InsurancecardSchema> importExecl(String path) {
if(null==path&&"".equals(path)){
resultCode = 0;
resultMessage = "HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format!";
return null;
}
String suffix=path.substring(path.lastIndexOf('.')+1,path.length()); //后缀
if(null==path&&"".equals(path)){
resultCode = 0;
resultMessage = " file suffix format no rigth!";
return null;
}
if(suffix.toUpperCase().equals("XLS")){ //97-2007(不包括2007)的
return readRequestExcel2003(path);
}else if(suffix.toUpperCase().equals("XLSX")){//包括2007的
return readRequestExcel2007(path);
}
return null;
}
public static List<De_InsurancecardSchema> readCancelRequestExcel2003(String path){
// 实例化集合对象
List<De_InsurancecardSchema> deInsurancecardSchemas = new ArrayList<De_InsurancecardSchema>();
// excel读取对象
HSSFWorkbook hb = null;
// 读入流对象
InputStream ins = null;
// 文件上传对象
De_InsurancecardSchema schema = null;
// 定义 row、cell
HSSFRow row;
String cell;
try {
// 导入文件
ins = new FileInputStream(new File(path));
hb = new HSSFWorkbook(ins);
HSSFSheet sheet = hb.getSheetAt(0); // 只是第一个sheet
// 根本就没有sheet
if (sheet != null) {
int lastRowNum = sheet.getPhysicalNumberOfRows(); // 总共有多少行数据
if (lastRowNum >1) {
//第二行开始
output: //行的开始
for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
int rowNum = row.getRowNum(); // 行数
if(i>0){
schema = new De_InsurancecardSchema(); // 每次循环一行是都实例化一个scheme
//只有一列,所以等与1
for (int j = 0; j < 1; j++) {
if(row.getCell(j)!=null){
cell = row.getCell(j).toString();
}else{
cell = null;
}
int cellNum =j;
boolean flag = false;
switch (cellNum) {
case 0:// 保单号(必填)
if (cell != null && !"".equals(cell)) {
schema.setContNo(cell);
flag = true;
}
break;
}
if (!flag) {
resultCode = 0;
resultMessage = "第"+(rowNum+1)+"行的第"
+ (cellNum + 1) + "列 保单号的格式不正确!";
deInsurancecardSchemas = null;
break output; // 跳到最外面
}
}
if(null!=schema.getContNo()&&!"".equals(schema.getContNo())){
// 增加到集合里
deInsurancecardSchemas.add(schema);
resultCode = 1;
resultMessage = "上传成功!";
}else{
//数据的完整性不正确!
deInsurancecardSchemas=null;
resultCode = 0;
//当提示这里的时候,只能说明你的excel的最后一行或最后一列有编辑过,但是没有数据,选择右键删除最后一行或最后一列,就可以了
resultMessage = "请求保单号的数据完整性不正确!";
}
}
}
}else if(lastRowNum<=1){
resultCode = 0;
resultMessage = "Excel数据为空!";
deInsurancecardSchemas = null;
}
} else {
deInsurancecardSchemas = null;
resultCode = 0;
resultMessage = "导入的EXCEL为空!";
}
} catch (FileNotFoundException e) {
resultCode = 0;
resultMessage = e.getMessage();
deInsurancecardSchemas = null; // 出错了
} catch (IOException e) {
resultCode = 0;
resultMessage = e.getMessage();
deInsurancecardSchemas = null; // 出错了
} finally {
if (ins != null) {
try {
ins.close();
} catch (IOException e) {
resultCode = 0;
resultMessage = e.getMessage();
deInsurancecardSchemas = null; // 出错了
}
}
}
return deInsurancecardSchemas;
}
@SuppressWarnings("deprecation")
public static List<De_InsurancecardSchema> readCancelRequestExcel2007(
String path) {
// 实例化集合对象
List<De_InsurancecardSchema> deInsurancecardSchemas = new ArrayList<De_InsurancecardSchema>();
// 文件上传对象
De_InsurancecardSchema schema = null;
// 构造 XSSFWorkbook 对象,strPath 传入文件路径
XSSFWorkbook xwb;
// 定义 row、cell
XSSFRow row;
String cell;
try {
xwb = new XSSFWorkbook(path);
// 读取第一章表格内容 ,只是第一个sheet
XSSFSheet sheet = xwb.getSheetAt(0);
if (sheet != null) {
int lastRowNum = sheet.getPhysicalNumberOfRows(); // 总共有多少行数据
if (lastRowNum >1) {
//第二行开始
output: //行的开始
for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
int rowNum = row.getRowNum(); // 行数
if(i>0){
schema = new De_InsurancecardSchema(); // 每次循环一行是都实例化一个scheme
for (int j = 0; j < 1; j++) {
if(row.getCell(j)!=null){
cell = row.getCell(j).toString();
}else{
cell = null;
}
int cellNum =j;
boolean flag = false;
switch (cellNum) {
case 0:// 保单号(必填)
if (cell != null && !"".equals(cell)) {
schema.setContNo(cell);
flag = true;
}
break;
}
if (!flag) {
resultCode = 0;
resultMessage = "第"+(rowNum+1)+"行的第"
+ (cellNum + 1) + "列 保单号的格式不正确!";
deInsurancecardSchemas = null;
break output; // 跳到最外面
}
}
if(null!=schema.getContNo()&&!"".equals(schema.getContNo())){
// 增加到集合里
deInsurancecardSchemas.add(schema);
resultCode = 1;
resultMessage = "上传成功!";
}else{
//数据的完整性不正确!
deInsurancecardSchemas=null;
resultCode = 0;
//当提示这里的时候,只能说明你的excel的最后一行或最后一列有编辑过,但是没有数据,选择右键删除最后一行或最后一列,就可以了
resultMessage = "请求保单号的数据完整性不正确!";
}
}
}
}else if(lastRowNum<=1){
resultCode = 0;
resultMessage = "Excel数据为空!";
deInsurancecardSchemas = null;
}
} else {
deInsurancecardSchemas = null;
resultCode = 0;
resultMessage = "导入的EXCEL为空!";
}
} catch (FileNotFoundException e) {
resultCode = 0;
resultMessage = e.getMessage();
deInsurancecardSchemas = null; // 出错了
} catch (IOException e) {
resultCode = 0;
resultMessage = e.getMessage();
deInsurancecardSchemas = null; // 出错了
} catch (UnsupportedOperationException e) {
resultCode = 0;
resultMessage = e.getMessage();
deInsurancecardSchemas = null; // java.lang.UnsupportedOperationException: Unknown Ptg in Formula: 0xffffffff (-1)
}catch (Exception e) {
resultCode = 0;
resultMessage = e.getMessage();
deInsurancecardSchemas = null; // 出错了
}
// 返回
return deInsurancecardSchemas;
}
public static void main(String[] args) {
cancelTest2003();
cancelTest2007();
}
public static void cancelTest2003(){
String f="C:\\Users\\Administrator\\Desktop\\cancelRequest-templet.xls";
List<De_InsurancecardSchema> lists=ExcelUtil.readCancelRequestExcel2003(f);
if(lists!=null)
for(De_InsurancecardSchema schema:lists){
System.out.println(schema.toString());
}
System.out.println(ExcelUtil.resultCode+"\t"+ExcelUtil.resultMessage);
}
public static void cancelTest2007(){
String f="C:\\Users\\Administrator\\Desktop\\cancelRequest-templet-2007.xlsx";
List<De_InsurancecardSchema> lists=ExcelUtil.readCancelRequestExcel2007(f);
if(lists!=null)
for(De_InsurancecardSchema schema:lists){
System.out.println(schema.toString());
}
System.out.println(ExcelUtil.resultCode+"\t"+ExcelUtil.resultMessage);
}
}
poi3.8的下载地址 http://poi.apache.org/download.html