java不固定模板excel_固定的模版excel修改，通过Java导入到数据库中

//导入

FileInputStream inputStream =new FileInputStream("D:\\资产管理.xls");

HSSFWorkbook workbook=newHSSFWorkbook(inputStream);//需要转换的字段与转换数据建立关联关系

Map> changeMap = new HashMap();//(很重要，主要就是修改前台需要的键)//资产类型

List typelist = selectDataService.queryForList("select * from t_assettype");

Map typemap = new HashMap();for(Map map : typelist) {

typemap.put(map.get("NAME").toString(), map.get("ID").toString());

}//查出资产类型的修改键位

changeMap.put("typeid", typemap);//厂家

List factorylist = selectDataService.queryForList("select * from T_FACTORY");

Map factorymap = new HashMap();for(Map map : factorylist) {

factorymap.put(map.get("NAME").toString(), map.get("ID").toString());

}

changeMap.put("factory", factorymap);//用户

List userlist = selectDataService.queryForList("select * from t_user");

Map usermap = new HashMap();for(Map map : userlist) {

usermap.put(map.get("REALNAME").toString(), map.get("ID").toString());

}

changeMap.put("userid", usermap);//将中文字段与英文字段建立关系

List list = selectDataService.queryForList("select COLUMN_NAME,COMMENTS from user_col_comments where table_name='T_ASSET' AND comments is not null");

Map colmap = new HashMap();

String sql=" insert into t_asset (";

String colSql="";for(Map map : list) {

colmap.put(map.get("COMMENTS").toString(), map.get("COLUMN_NAME").toString());

colSql+=","+map.get("COLUMN_NAME").toString();

}

colSql=colSql.substring(1);

colSql+=",id) values( ";//获取sheet数量

int sheetNum =workbook.getNumberOfSheets();//循环读取sheet

for (int i = 0; i < sheetNum; i++) {

HSSFSheet sheet=workbook.getSheetAt(i);//获取有效行数

int rownum =sheet.getLastRowNum();//中文字段名称集合

List cnColList = newArrayList();for (int j = 1; j <=rownum; j++) {

HSSFRow row=sheet.getRow(j);

String valueSql="";if(j==1){//解析字段信息

for (int j2 = 0; j2 < row.getLastCellNum(); j2++) {

cnColList.add(row.getCell(j2).getStringCellValue());

}

}else{//解析数据行

for (int k = 0; k

String encol =colmap.get(cnColList.get(k));//获取数据值

String cellVal =row.getCell(k).getStringCellValue();if(changeMap.get(encol.toLowerCase())!=null){

cellVal=changeMap.get(encol.toLowerCase()).get(cellVal).toString();

}if(cnColList.get(k).equals("购买日期")){

cellVal="to_date('"+cellVal+"'，'yyyy-MM-dd')";

valueSql+=","+cellVal;

}else{

valueSql+=",'"+cellVal+"'";

}

}

valueSql=valueSql.substring(1);

valueSql+=",'"+WebUtils.getRandomId()+"')";

System.out.println(sql+colSql+valueSql);

assetService.saveAA(sql+colSql+valueSql);

}

}

}

}catch(Exception e) {//TODO Auto-generated catch block

e.printStackTrace();

}return null;

}