JAVA 导入Excel表格数据

JAVA 导入Excel表格数据

package com.zjpcpy.test1;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
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.ss.usermodel.CellValue;

import com.zjepe.core.convert.Xls2CsvHandler;
import com.zjepe.core.convert.Xlsx2CsvHandler;
import com.zjepe.core.utils.FileUtils;
import com.zjepe.core.utils.StringUtils;

public class TestAnalyseExcel {

public static void main(String[] args) throws FileNotFoundException {
// TODO Auto-generated method stub
/* 即每个sheet为一个Map,其中包含的key: sheetname、sheetindex、rowmaplist;<br>
*/
String upfilename="";
String upfilepath="";
String filepath = upfilename;
if (!upfilename.contains(upfilepath) && !StringUtils.isEmpty(upfilepath)){
filepath = upfilepath + File.separator + upfilename;
}
int iindex = filepath.lastIndexOf('.');
String suffix = "xls";
if (iindex>-1){
suffix = filepath.substring(iindex+1);
filepath = filepath.substring(0,iindex);
}

try {
if ("xls".equals(suffix.toLowerCase())){

}else if ("xlsx".equals(suffix.toLowerCase())){

}else{
throw new Exception("文件扩展名必须为xls、xlsx类的Excel格式文件");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

InputStream is = new FileInputStream("/D:/test.xls");
HSSFWorkbook hssfWorkbook = null;
try {
hssfWorkbook = new HSSFWorkbook(is);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// 获取第一个工作表Sheet
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
String sheetName=hssfWorkbook.getSheetAt(0).getSheetName();//sheetname
Map<String,Object> sheet = new HashMap<String, Object>();//一个sheet
sheet.put("sheetname",sheetName);
sheet.put("sheetindex",0);
//List<Map<String,Object>> list = new ArrayList<Map<String,Object>>(); 
List<Map<String,Object>> rowmaplist= new ArrayList<Map<String,Object>>();
HSSFFormulaEvaluator evaluator=new HSSFFormulaEvaluator(hssfWorkbook);
if (hssfSheet == null) {
try {
throw new IOException("传入的excel的第一张表为空！");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
HSSFRow hssfRow;
System.out.println("第一行："+hssfSheet.getFirstRowNum());
System.out.println("最后一行："+hssfSheet.getLastRowNum());
for (int rowNum = 5; rowNum <=hssfSheet.getLastRowNum(); rowNum++) {
Map<String,Object> rowsmap = new HashMap<String, Object>();//一行的数据
rowsmap.put("rowindex",rowNum);//行下标
hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {
continue;
}
String rowcontent ="";//行内容
for(int i=0;i<hssfRow.getLastCellNum();i++){
HSSFCell hssfCell=hssfRow.getCell(i);
System.out.println("++++++++++++"+i);
if ( hssfCell == null) {
System.out.println("null");
rowcontent=rowcontent+""+",";
continue;
}
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
// 返回布尔类型的值
System.out.println(String.valueOf(hssfCell.getBooleanCellValue()));
rowcontent=rowcontent+String.valueOf(hssfCell.getBooleanCellValue())+",";

} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
// 返回数值类型的值
System.out.println(String.valueOf(hssfCell.getNumericCellValue()));
rowcontent=rowcontent+String.valueOf(hssfCell.getNumericCellValue())+",";
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_FORMULA) {
//处理经excel公式算出的值 // 返回经公式计算后的
CellValue tempCellValue = evaluator.evaluate(hssfCell);
double iCellValue = tempCellValue.getNumberValue(); 
System.out.println(String.valueOf(iCellValue));
rowcontent=rowcontent+String.valueOf(iCellValue)+",";
}else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_ERROR) { 
rowcontent=rowcontent+"null"+",";
System.out.println("null");
} else {
// 返回字符串类型的值 
System.out.println(String.valueOf(hssfCell.getStringCellValue()));
rowcontent=rowcontent+String.valueOf(hssfCell.getStringCellValue())+",";
}
}
rowsmap.put("rowcontent", rowcontent);
rowmaplist.add(rowsmap);
System.out.println("=====================================content:"+rowcontent);
}

}

}