我们在做项目的时候,尤其是实现新增功能的时候,有时需要将excel模板中的内容直接存入数据库,这时我们可以用到POI技术实现这一功能,他的原理就是
先将文件导入,再读取文件内容,提取数据,生成对象,保存到数据库。
具体代码如下:
if(file !=null && !file.equals("")){
String listPath = Constants.UPLOADFILE_PATH;
InWebPropertiesUtil util = new InWebPropertiesUtil("sysConfig/config.properties");
String result = util.getIsCodeGetDomainName();
File listFile=new File(result+"/"+listPath);
if(!listFile.exists()){
listFile.mkdirs();
}
File newFile= new File(listFile+"/"+fileFileName);
try {
if(!newFile.exists()){
newFile.createNewFile();
}
FileUtils.copyFile(file, newFile);
} catch (IOException e) {
e.printStackTrace();
}
File target = new File(newFile.toString());
List<EvaEquipImport> roleList = new ArrayList<EvaEquipImport>();
try {
FileInputStream fi = new FileInputStream(target);
Workbook wb = null;
if(fileFileName.toLowerCase().endsWith("xls")){
wb = new HSSFWorkbook(fi);
}else if(fileFileName.toLowerCase().endsWith("xlsx")){
wb = new XSSFWorkbook(fi);
}
Sheet sheet = wb.getSheetAt(0);
int rowNum = sheet.getLastRowNum()+1;
if(rowNum==1){
remsg="请录入数据后提交";
chuZhi();
return null;
}
for(int i=1;i<rowNum;i++){
EvaEquipImport evaEquireImport = new EvaEquipImport();//封装成的实体类
Row row = sheet.getRow(i);
int cellNum = row.getLastCellNum();
String msg = "";
String results = "";
for(int j=0;j<cellNum;j++){
Cell cell = row.getCell(j);
String cellValue = null;
if(j==4){
try {
if(cell!=null){
int cellType = cell.getCellType();
if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
Date date = cell.getDateCellValue();
results = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)
+ "-" + date.getDate();
} else if (cellType == HSSFCell.CELL_TYPE_STRING) {
String date = cell.getStringCellValue();
results = date.replaceAll("[年月]", "-").replace("日", "").trim();
} else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
results = "";
}
}else{
results = "";
}
} catch (Exception e) {
e.printStackTrace();
}
}else{
if(cell!=null){
switch(cell.getCellType()){ //判断excel单元格内容的格式,并对其进行转换,以便插入数据库
case 0 : cellValue = String.valueOf((int)cell.getNumericCellValue()); break;
case 1 : cellValue = cell.getStringCellValue(); break;
case 2 : cellValue = String.valueOf(cell.getDateCellValue()); break;
case 3 : cellValue = ""; break;
case 4 : cellValue = String.valueOf(cell.getBooleanCellValue()); break;
case 5 : cellValue = String.valueOf(cell.getErrorCellValue()); break;
}
}else{
cellValue="";
}
}
switch(j){//通过列数来判断对应插如的字段
case 0 : evaEquireImport.setEquipType(cellValue);break;
case 1 : evaEquireImport.setDemandName(cellValue);break;
case 2 : evaEquireImport.setBrandName(cellValue);break;
case 3 : evaEquireImport.setPurNum(cellValue);break;
case 4 : evaEquireImport.setPurDate(results);break;
case 5 : evaEquireImport.setBudget(cellValue);break;
case 6 : evaEquireImport.setIsEvaluate(cellValue);break;
case 7 : evaEquireImport.setEvaluate(cellValue);break;
case 8 : evaEquireImport.setIsBusNego(cellValue);break;
case 9 : evaEquireImport.setBusNego(cellValue);break;
case 10: evaEquireImport.setRemark(cellValue);break;
}
jar包:POI中poi-3.9-20121203.jar,poi-ooxml-3.9-20121203.jar,xbean.jar
具体代码如下:
if(file !=null && !file.equals("")){
String listPath = Constants.UPLOADFILE_PATH;
InWebPropertiesUtil util = new InWebPropertiesUtil("sysConfig/config.properties");
String result = util.getIsCodeGetDomainName();
File listFile=new File(result+"/"+listPath);
if(!listFile.exists()){
listFile.mkdirs();
}
File newFile= new File(listFile+"/"+fileFileName);
try {
if(!newFile.exists()){
newFile.createNewFile();
}
FileUtils.copyFile(file, newFile);
} catch (IOException e) {
e.printStackTrace();
}
File target = new File(newFile.toString());
List<EvaEquipImport> roleList = new ArrayList<EvaEquipImport>();
try {
FileInputStream fi = new FileInputStream(target);
Workbook wb = null;
if(fileFileName.toLowerCase().endsWith("xls")){
wb = new HSSFWorkbook(fi);
}else if(fileFileName.toLowerCase().endsWith("xlsx")){
wb = new XSSFWorkbook(fi);
}
Sheet sheet = wb.getSheetAt(0);
int rowNum = sheet.getLastRowNum()+1;
if(rowNum==1){
remsg="请录入数据后提交";
chuZhi();
return null;
}
for(int i=1;i<rowNum;i++){
EvaEquipImport evaEquireImport = new EvaEquipImport();//封装成的实体类
Row row = sheet.getRow(i);
int cellNum = row.getLastCellNum();
String msg = "";
String results = "";
for(int j=0;j<cellNum;j++){
Cell cell = row.getCell(j);
String cellValue = null;
if(j==4){
try {
if(cell!=null){
int cellType = cell.getCellType();
if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
Date date = cell.getDateCellValue();
results = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)
+ "-" + date.getDate();
} else if (cellType == HSSFCell.CELL_TYPE_STRING) {
String date = cell.getStringCellValue();
results = date.replaceAll("[年月]", "-").replace("日", "").trim();
} else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
results = "";
}
}else{
results = "";
}
} catch (Exception e) {
e.printStackTrace();
}
}else{
if(cell!=null){
switch(cell.getCellType()){ //判断excel单元格内容的格式,并对其进行转换,以便插入数据库
case 0 : cellValue = String.valueOf((int)cell.getNumericCellValue()); break;
case 1 : cellValue = cell.getStringCellValue(); break;
case 2 : cellValue = String.valueOf(cell.getDateCellValue()); break;
case 3 : cellValue = ""; break;
case 4 : cellValue = String.valueOf(cell.getBooleanCellValue()); break;
case 5 : cellValue = String.valueOf(cell.getErrorCellValue()); break;
}
}else{
cellValue="";
}
}
switch(j){//通过列数来判断对应插如的字段
case 0 : evaEquireImport.setEquipType(cellValue);break;
case 1 : evaEquireImport.setDemandName(cellValue);break;
case 2 : evaEquireImport.setBrandName(cellValue);break;
case 3 : evaEquireImport.setPurNum(cellValue);break;
case 4 : evaEquireImport.setPurDate(results);break;
case 5 : evaEquireImport.setBudget(cellValue);break;
case 6 : evaEquireImport.setIsEvaluate(cellValue);break;
case 7 : evaEquireImport.setEvaluate(cellValue);break;
case 8 : evaEquireImport.setIsBusNego(cellValue);break;
case 9 : evaEquireImport.setBusNego(cellValue);break;
case 10: evaEquireImport.setRemark(cellValue);break;
}