java导入Excel包含多张sheet表的.xls .xslx .txt三种后缀格式的文件

java导入.xls .xslx .txt三种后缀格式的文件
public static void readFile(File file) throws IOException{
//判断文件类型
String fileName = file.getName();
fileName = fileName.toLowerCase();
if(fileName.endsWith("xls")){
readXls(file);
}else if(fileName.endsWith("txt")){
readTxt(file);
}else if(fileName.endsWith("xlsx")){
readXlsx(file);
}else{
throw new IOException("不支持文件类型");
}
}

//导入2003 xls文件
private static void readXls(File file)throws IOException{
String[] keys ={ "content","mobile","ext","status","responseid","error","sendtime","bak1","bak2","bak3","bak4"};

HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
int length = hwb.getActiveSheetIndex();//判断有几张活动的sheet表
for(int k=0; k<=length; k++){
HSSFSheet sheet = hwb.getSheetAt(k);
Object value = null;
HSSFRow row = null;
HSSFCell cell = null;
List<Dto> lists=null;

for(int i=sheet.getFirstRowNum()+1; i<sheet.getPhysicalNumberOfRows(); i++){
row = sheet.getRow(i);
if(row == null){
continue;
}
if(i%1000==1){
lists=new ArrayList<Dto>();
}
Map hashmap = new HashMap();

for(int j=row.getFirstCellNum(); j<row.getLastCellNum(); j++){
cell = row.getCell(j);
if(cell == null){
value = "";
hashmap.put(keys[j], value);
continue;
}
DecimalFormat df = new DecimalFormat("0");//格式化number String
//字符
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//格式化日期字符串
DecimalFormat nf = new DecimalFormat("0");//格式化数字

switch(cell.getCellType()){
case XSSFCell.CELL_TYPE_STRING:
//System.out.println(i+"行"+j+"列 is String type" );
value = cell.getStringCellValue();
//System.out.println(" "+ value +" ");
break;
case XSSFCell.CELL_TYPE_NUMERIC:
//System.out.println(i+"行"+j+"列 is Number type; DateFormt:" +cell.getCellStyle().getDataFormatString());
if("@".equals(cell.getCellStyle().getDataFormatString())){
value = df.format(cell.getNumericCellValue());

}else if("General".equals(cell.getCellStyle().getDataFormatString())){
value = nf.format(cell.getNumericCellValue());
}else{
value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));

}
//System.out.println(" "+value+" ");
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
//System.out.println(i+"行"+j+"列 is Boolean type");
value = cell.getBooleanCellValue();
//System.out.println(" "+value+" ");
break;
case XSSFCell.CELL_TYPE_BLANK:
//System.out.println(i+"行"+j+"列 is Blank type");
value = "";
//System.out.println(" "+value+" ");
break;
default:
//System.out.println(i+"行"+j+"列 is default type");
value = cell.toString();
//System.out.println(" "+value+" ");
}

hashmap.put(keys[j],value);

}
//将数据插入数据库
Dto fileDto = new BaseDto();
for (int j = 0; j < keys.length; j++) {
if (hashmap.get(keys[j]) == null
|| "".equals(hashmap.get(keys[j]))) {
if ("sendtime".equals(keys[j])) {
fileDto.put(keys[j], s_date);
} else {
fileDto.put(keys[j], "");
}
} else {
fileDto.put(keys[j], hashmap.get(keys[j]));
}
}
lists.add(fileDto);
if(i%1000==0 || i==sheet.getPhysicalNumberOfRows()-1){
myBatchService.insertBatchdx(lists);
}
// cetReadFilesService.insertFile(fileDto);
}
}

}

//导入2007 xlsx文件
private static void readXlsx(File file) throws IOException{
String[] keys = { "content","mobile","ext","status","responseid","error","sendtime","bak1","bak2","bak3","bak4"};
//购置XXSFWorkbook对象，传入file文件
XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
//格式化日期字符串
SimpleDateFormat sdf = new SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss");
//读取第一张表格内容
int length = xwb.getActiveSheetIndex();
for(int k=0; k<=length; k++){
XSSFSheet sheet = xwb.getSheetAt(0);
Object value = null;
XSSFRow row = null;
XSSFCell cell = null;
List<Dto> lists=null;

for(int i=sheet.getFirstRowNum()+1; i<sheet.getPhysicalNumberOfRows(); i++){
row = sheet.getRow(i);
if(row==null){
continue;
}
if(i%1000==1){
lists=new ArrayList<Dto>();
}

Map hashmap = new HashMap();

for(int j=row.getFirstCellNum(); j<row.getLastCellNum(); j++){
cell = row.getCell(j);
if(cell == null){
value = "";
hashmap.put(keys[j], value);
continue;
}
DecimalFormat df = new DecimalFormat("0");//格式化 number String
//字符
DecimalFormat nf = new DecimalFormat("0");//格式化数字

switch(cell.getCellType()){
case XSSFCell.CELL_TYPE_STRING:
//System.out.println(i+"行"+j+"列 is String type" );
value = cell.getStringCellValue();
//System.out.println(" "+ value +" ");
break;
case XSSFCell.CELL_TYPE_NUMERIC:
//System.out.println(i+"行"+j+"列 is Number type; DateFormt:" +cell.getCellStyle().getDataFormatString());
if("@".equals(cell.getCellStyle().getDataFormatString())){
value = df.format(cell.getNumericCellValue());

}else if("General".equals(cell.getCellStyle().getDataFormatString())){
value = nf.format(cell.getNumericCellValue());
}else{
value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));

}
//System.out.println(" "+value+" ");
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
//System.out.println(i+"行"+j+"列 is Boolean type");
value = cell.getBooleanCellValue();
//System.out.println(" "+value+" ");
break;
case XSSFCell.CELL_TYPE_BLANK:
//System.out.println(i+"行"+j+"列 is Blank type");
value = "";
//System.out.println(" "+value+" ");
break;
default:
//System.out.println(i+"行"+j+"列 is default type");
value = cell.toString();
//System.out.println(" "+value+" ");
}

hashmap.put(keys[j], value);
}
//将数据插入数据库
Dto fileDto = new BaseDto();
for (int j = 0; j < keys.length; j++) {
if (hashmap.get(keys[j]) == null
|| "".equals(hashmap.get(keys[j]))) {
if ("sendtime".equals(keys[j])) {
fileDto.put(keys[j], s_date);
} else {
fileDto.put(keys[j], "");
}
} else {
fileDto.put(keys[j], hashmap.get(keys[j]));
}
}
lists.add(fileDto);
if(i%1000==0 || i==sheet.getPhysicalNumberOfRows()-1){
myBatchService.insertBatchdx(lists);
}
}
}
}
/**
* 导入TXT文件
*/
private static void readTxt(File file) {
//count用于计数
int count = 0;
String[] keys = {"content","mobile","ext","status","responseid","error","sendtime","bak1","bak2","bak3","bak4"};
FileReader reader;
List<Dto> lists=null;

try {
reader = new FileReader(file);
BufferedReader bf=new BufferedReader(reader);//一行一行读

try {
String t1=bf.readLine();
while(t1 != null){
count++;
if(count%1000==1){
lists=new ArrayList<Dto>();
}

String[] strs = t1.split("\\|");
Map hashmap = new HashMap();
for(int i=0; i<strs.length; i++){
hashmap.put(keys[i], strs[i]);

}
//将数据插入数据库
if(count != 1){
Dto fileDto = new BaseDto();
for (int j = 0; j < keys.length; j++) {
if (hashmap.get(keys[j]) == null
|| "".equals(hashmap.get(keys[j]))) {
if ("sendtime".equals(keys[j])) {
fileDto.put(keys[j], s_date);
} else {
fileDto.put(keys[j], "");
}
} else {
fileDto.put(keys[j], hashmap.get(keys[j]));
}
}
lists.add(fileDto);
if(count%1000==0 ){
myBatchService.insertBatchdx(lists);
}
}
t1=bf.readLine();
if(t1==null){
myBatchService.insertBatchdx(lists);
}
}
}catch (IOException e){
e.printStackTrace();
}
}catch(FileNotFoundException e){
e.printStackTrace();
}
}