正文
java 处理上传exl数据 并导入数据库
小程序:扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序
【扫一扫了解最新限行尾号】
复制小程序
思路:处理上传exl表格,并读取文件,数据,讲数据放去集合,循环插入数据库。
重点,读取文件时需要读数据,格式和数据是否正确,(因为只是把整条路打通,所以这块没有细弄);
项目使用ssm框架,
struts:
<action name="uploadExcel" class="com.payment.action.PaymentAction" method="uploadExcel">
<result>/center/uploadExcel.ftl</result>
</action>
action:
public String uploadExcel(){
boolean ret = false;
try{
if(excel == null){
this.getRequest().setAttribute("message", "请选择一个Excel文件!");
}else{
ret = paymentBo.excelToAps(excel,excel_type);
if(!ret){
this.getRequest().setAttribute("message", "Excel添加失败,请确定格式是否正确!");
}else{
this.getRequest().setAttribute("message", "添加成功");
}
}
}catch(Exception e){
e.printStackTrace();
}
return success;
}
dao:
public boolean excelToAps(File file,String type ) throws Exception {
System.out.println("paymentBo.excelToAps进入"); try {
Workbook workbook = getWeebWork(type,file);
Sheet sheet = workbook.getSheetAt(0);
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
int rownum = sheet.getLastRowNum();// 获取总行数
int minRowIx = sheet.getFirstRowNum();
int maxRowIx = sheet.getLastRowNum();
List<String> list = new ArrayList<String>(); //测试存放数据的list
List<PaymentDataVo> listPayment = new ArrayList<PaymentDataVo>();//将exl中的数据提取后存放的list
System.out.println("rownum:"+rownum+"minRowIx:"+minRowIx+",maxRowIx:"+maxRowIx);
for (int rowIx = 1; rowIx <= maxRowIx; rowIx++) {
Row row = sheet.getRow(rowIx);
StringBuilder sb = new StringBuilder();
short minColIx = row.getFirstCellNum();
short maxColIx = row.getLastCellNum();
PaymentDataVo payvoDataVo =new PaymentDataVo();
//System.out.println("minColIx:"+minColIx+"maxColIx:"+maxColIx );
System.out.print("第"+rowIx+"行数据:");
for (short colIx = 0; colIx < maxColIx; colIx++) {
Cell cell = row.getCell(new Integer(colIx));
CellValue cellValue = evaluator.evaluate(cell); /******/
DecimalFormat df = new DecimalFormat("######0.00"); if(colIx == 0){//日期
String date =cellValue.getNumberValue()+"" ;
System.out.print(new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(cell.getDateCellValue())+",");
payvoDataVo.setCreateTime(new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(cell.getDateCellValue()));
}else if(colIx== 1){//卡类
System.out.print((int)cellValue.getNumberValue()+",");
payvoDataVo.setPaymentId((int)cellValue.getNumberValue());
}else if(colIx == 2){//消费类型
System.out.print((int)cellValue.getNumberValue()+",");
//payvoDataVo.setConsumprionId(cellValue.getNumberValue());
}else if(colIx == 3){//明细类型
System.out.print(cellValue.getStringValue()+",");
payvoDataVo.setDataType(cellValue.getStringValue());
}else if(colIx == 4){//消费简述
System.out.print(cellValue.getStringValue()+",");
payvoDataVo.setDataName(cellValue.getStringValue());
}else if(colIx == 5){//消费金额
System.out.print(df.format(cellValue.getNumberValue()));
payvoDataVo.setDataAMT(Double.parseDouble(df.format(cellValue.getNumberValue())));
}else if(cellValue.getCellType()==0){
break;
} /***此处代码为网络摘录****/// if (cellValue == null) {
// continue;
// }
// // 经过公式解析,最后只存在Boolean、Numeric和String三种数据类型,此外就是Error了
// // 其余数据类型,根据官方文档,完全可以忽略http://poi.apache.org/spreadsheet/eval.html
// switch (cellValue.getCellType()) {
// case Cell.CELL_TYPE_BOOLEAN:
// sb.append(SEPARATOR + cellValue.getBooleanValue());
// break;
// case Cell.CELL_TYPE_NUMERIC:
// // 这里的日期类型会被转换为数字类型,需要判别后区分处理
// if (DateUtil.isCellDateFormatted(cell)) {
// sb.append(SEPARATOR + cell.getDateCellValue()+"---");
// } else {
// sb.append(SEPARATOR + cellValue.getNumberValue()+"+++");
// }
// break;
// case Cell.CELL_TYPE_STRING:
// sb.append(SEPARATOR + cellValue.getStringValue());
// break;
// case Cell.CELL_TYPE_FORMULA:
// break;
// case Cell.CELL_TYPE_BLANK:
// break;
// case Cell.CELL_TYPE_ERROR:
// break;
// default:
// break;
// }
}
System.out.println();
list.add(sb.toString());
listPayment.add(payvoDataVo);
}
paymentDao.addPaymentDataVos(listPayment); } catch (Exception e) {
return false;
//e.printStackTrace();
}
return true;
}
mybatis:
<insert id="addPaymentDataVos" parameterType="java.util.List" >
INSERT into payments_data VALUES <foreach collection="list" item="item" index="index" separator="," >
(null,#{item.createTime},#{item.paymentId},#{item.consumprionId},#{item.dataType},#{item.dataName},#{item.dataAMT})
</foreach>
</insert>
有问题会再跟进的!