jsp
<c:if test="${not empty currentUserAccount and (currentUserAccount.role.contains(‘DA‘) or currentUserAccount.role.contains(‘FA‘)or currentUserAccount.role.contains(‘GMA‘) or currentUserAccount.role.contains(‘ADMIN‘))}">
<div >
<label > </label>
<input type="button" value="导出">
</div>
<form method="POST" role="form" enctype="multipart/form-data" action="/sale/ajaxUpload">
<div >
<div control-label"> </label>
<input type="file" name="upfile" value="上传文件">
</div>
</div>
<div >
<div control-label"> </label>
<input type="button" value="导入">
</div>
</div>
<div >
<div control-label"> </label>
<a href="/sale/download">EXCEL模板</a>
</div>
</div>
</form>
</c:if> ?$(‘#import‘).click(function(){
if(checkData()){
$(‘#form1‘).ajaxSubmit({ url:‘/sale/ajaxUpload‘,
dataType: ‘text‘,
success: resutlMsg,
error: errorMsg,
});
} });
function resutlMsg(msg){ alert(msg); $("#upfile").val(""); jQuery("#saleList").trigger("reloadGrid");/*window.location.reload();*/}
function errorMsg(){ alert("导入excel出错！"); }
function checkData(){
var fileDir = $("#upfile").val();
var suffix = fileDir.substr(fileDir.lastIndexOf("."));
if("" == fileDir){ alert("选择需要导入的Excel文件！"); return false; }
if(".xls" != suffix && ".xlsx" != suffix ){ alert("选择Excel格式的文件导入！"); return false; }
return true; }
});
Java
@ResponseBody
@RequestMapping(value="/ajaxUpload",method={RequestMethod.GET,RequestMethod.POST})
public void ajaxUploadExcel(@RequestParam MultipartFile upfile,ModelMap modelMap,HttpSession session,HttpServletResponse response) throws Exception {
InputStream in = null;
List<List<Object>> listob = null;
String jieguo = "成功导入数据，文件上传成功！";
if(upfile.isEmpty()){
throw new Exception("文件不存在！");
}
UserAccount userAccount = (UserAccount) modelMap.get("currentUserAccount");
if (userAccount != null) {
listMedias = mediaService.listMedias("TPASS", userAccount.getUsername());
}
List<Client> clients = clientService.listClients();
List<PayMethod> listMethods =payMethodService.listsPayMethod();
List<String> allActivatednames = userAccountService.listUserNameActivated();
in = upfile.getInputStream();
listob = new ImportExcelUtil().getBankListByExcel(in, upfile.getOriginalFilename());
//该处可调用service相应方法进行数据保存到数据库中，现只对数据输出
try {
for (int i = 0; i < listob.size(); i++) {
List<Object> lo = listob.get(i);
String a = String.valueOf(lo.get(0)).replaceAll("^[ ]+|[ ]+$", "");//业务名称
String b = String.valueOf(lo.get(1)).replaceAll("^[ ]+|[ ]+$", "");//客户名称
String c = String.valueOf(lo.get(2)).replaceAll("^[ ]+|[ ]+$", "");//加款账号
String d = String.valueOf(lo.get(3)).replaceAll("^[ ]+|[ ]+$", "");//加/退币金额
//应收金额
String e = String.valueOf(lo.get(4)).replaceAll("^[ ]+|[ ]+$", "");//加币时间
String f = String.valueOf(lo.get(5)).replaceAll("^[ ]+|[ ]+$", "");//应收款时间
String g = String.valueOf(lo.get(6)).replaceAll("^[ ]+|[ ]+$", ""); //实付金额
//收款方式
String h = String.valueOf(lo.get(7)).replaceAll("^[ ]+|[ ]+$", "");//地域
String ii = String.valueOf(lo.get(8)).replaceAll("^[ ]+|[ ]+$", "");//收款方式
String j = String.valueOf(lo.get(9)).replaceAll("^[ ]+|[ ]+$", "");//记录人员信息
String k = String.valueOf(lo.get(10)).replaceAll("^[ ]+|[ ]+$", "");//备注
Sale sale = new Sale();
sale.setUserAccountId(userAccount.getId());
long mediaId = -1;
long clientId = -1;
BigDecimal discountProvider= null;
//业务名称
for(i=0; i< listMedias.size();i++){
Media media = listMedias.get(i);
if(StringUtils.equals(a,media.getFrameworkName())) {
mediaId = media.getId();
discountProvider = media.getDiscountProvider();
sale.setDiscountProvider(discountProvider);
break;
};
}
//客户名称
for(i=0; i< clients.size();i++){
Client client = clients.get(i);
if(StringUtils.equals(b,client.getClientName())) {
clientId = client.getId();
break;
};
}
//加款账号
List<MediaAccount> mediaAccountList = mediaAccountService.listMediaAccounts(mediaId, clientId);
long mediaAccountId = -1;
String discountMethod = null;
BigDecimal discountBase = null;
for(i=0; i< mediaAccountList.size();i++){
MediaAccount mediaAccount = mediaAccountList.get(i);
if(StringUtils.equals(c,mediaAccount.getAccountName())) {
mediaAccountId = mediaAccount.getId();
discountMethod = mediaAccount.getDiscountMethod();
sale.setDiscountMethod(discountMethod);
discountBase = mediaAccount.getDiscountBase();
sale.setDiscountMedia(discountBase);
sale.setMediaAccountId(mediaAccountId);
break;
};
}
//加/退币金额
BigDecimal recharge=new BigDecimal(d);
recharge = recharge.setScale(2, BigDecimal.ROUND_HALF_UP);
sale.setRecharge(recharge);
//应收金额
BigDecimal yingshou = jisuan(discountMethod,recharge,discountBase);
yingshou = yingshou.setScale(2, BigDecimal.ROUND_HALF_UP);
sale.setRevenue(yingshou);
//加币时间
DateFormat fmt =new SimpleDateFormat("yyyy/MM/dd");
java.util.Date rechargeTime =fmt.parse(e);
sale.setRechargeTime(rechargeTime);
//应收款时间
java.util.Date revenueTime =fmt.parse(f);
sale.setRevenueTime(revenueTime);
//实付金额
BigDecimal actualAmount2=new BigDecimal(g);
actualAmount2 = actualAmount2.setScale(2, BigDecimal.ROUND_HALF_UP);
sale.setActualAmount(actualAmount2);
//地域
sale.setArea(h);
//收款方式
List<PayMethod> payMethodList = payMethodService.listsPayMethod();
for(i=0;i<payMethodList.size();i++){
PayMethod payMethod = payMethodList.get(i);
if(StringUtils.equals(ii,payMethod.getPayMethod())){
sale.setPaymentMethod(ii);
break;
}
}
//记录人员信息
List<String> userNameList = userAccountService.listUserNameActivated();
for(i=0;i<userNameList.size();i++){
if(StringUtils.equals(j,userNameList.get(i))){
sale.setAddPerson(j);
break;
}
}
//备注
sale.setNote(k);
sale.setStatus("SUBMITED");
sale = saleService.addSale(sale);
if(sale == null){
jieguo = "你导入的excel表格数据或格式有错误，文件上传失败！";
break;
}
}
} catch (Exception e) {
if (logger.isErrorEnabled()) {
logger.error("ajaxUploadExcel", e);
jieguo = "你导入的excel表格数据或格式有错误，文件上传失败！";
}
}
PrintWriter out = null;
response.setCharacterEncoding("utf-8"); //防止ajax接受到的中文信息乱码
out = response.getWriter();
out.print(jieguo);
out.flush();
out.close();
}
package cn.com.singlemountaintech.dxmanagement.common.utils;
/**
* Created by li on 17-8-2.
*/
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ImportExcelUtil {
private final static String excel2003L =".xls"; //2003- 版本的excel
private final static String excel2007U =".xlsx"; //2007+ 版本的excel
/**
* 描述：获取IO流中的数据，组装成List<List<Object>>对象
* @param in,fileName
* @return
* @throws IOException
*/
public List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{
List<List<Object>> list = null;
//创建Excel工作薄
Workbook work = this.getWorkbook(in,fileName);
if(null == work){
throw new Exception("创建Excel工作薄为空！");
}
Sheet sheet = null;
Row row = null;
Cell cell = null;
list = new ArrayList<List<Object>>();
//遍历Excel中所有的sheet
for (int i = 0; i < work.getNumberOfSheets(); i++) {
sheet = work.getSheetAt(i);
if(sheet==null){continue;}
//遍历当前sheet中的所有行
for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
row = sheet.getRow(j);
if(row==null||row.getFirstCellNum()==j){continue;}
//遍历所有的列
List<Object> li = new ArrayList<Object>();
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
li.add(this.getCellValue(cell));
}
list.add(li);
}
}
work.close();
return list;
}
/**
* 描述：根据文件后缀，自适应上传文件的版本
* @param inStr,fileName
* @return
* @throws Exception
*/
public Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
Workbook wb = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if(excel2003L.equals(fileType)){
wb = new HSSFWorkbook(inStr); //2003-
}else if(excel2007U.equals(fileType)){
wb = new XSSFWorkbook(inStr); //2007+
}else{
throw new Exception("解析的文件格式有误！");
}
return wb;
}
/**
* 描述：对表格中数值进行格式化
* @param cell
* @return
*/
public Object getCellValue(Cell cell){
Object value = null;
DecimalFormat df = new DecimalFormat("0"); //格式化number String字符
SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //日期格式化
DecimalFormat df2 = new DecimalFormat("0.00"); //格式化数字
/*if("yyyy/mm;@".equals(cell.getCellStyle().getDataFormatString()) || "m/d/yy".equals(cell.getCellStyle().getDataFormatString())
|| "yy/m/d".equals(cell.getCellStyle().getDataFormatString()) || "mm/dd/yy".equals(cell.getCellStyle().getDataFormatString())
|| "dd-mmm-yy".equals(cell.getCellStyle().getDataFormatString())|| "yyyy/m/d".equals(cell.getCellStyle().getDataFormatString())){
return new SimpleDateFormat("yyyy/MM/dd").format(cell.getDateCellValue());
}*/
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if("General".equals(cell.getCellStyle().getDataFormatString())){
value = df.format(cell.getNumericCellValue());
}else if("yyyy/mm;@".equals(cell.getCellStyle().getDataFormatString()) || "m/d/yy".equals(cell.getCellStyle().getDataFormatString())
|| "yy/m/d".equals(cell.getCellStyle().getDataFormatString()) || "mm/dd/yy".equals(cell.getCellStyle().getDataFormatString())
|| "dd-mmm-yy".equals(cell.getCellStyle().getDataFormatString())|| "yyyy/m/d".equals(cell.getCellStyle().getDataFormatString())){
return new SimpleDateFormat("yyyy/MM/dd").format(cell.getDateCellValue());
}
/*
else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
value = sdf.format(cell.getDateCellValue());
}*/else{
value = df2.format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
value = "";
break;
default:
break;
}
return value;
}
}
上面的上传要在
AppConfig添加最后一个@Bean(name="multipartResolver")packagecn.com.singlemountaintech.dxmanagement.config;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.context.MessageSource;importorg.springframework.context.annotation.*;importorg.springframework.context.support.ReloadableResourceBundleMessageSource;importorg.springframework.core.env.Environment;importorg.springframework.core.task.SimpleAsyncTaskExecutor;importorg.springframework.scheduling.annotation.EnableAsync;importorg.springframework.scheduling.annotation.EnableScheduling;importorg.springframework.web.servlet.LocaleResolver;importorg.springframework.web.servlet.i18n.SessionLocaleResolver;importorg.springframework.web.multipart.commons.CommonsMultipartResolver;importjava.util.concurrent.Executor;/**JDK8proper*applicationContext.xml**/@Configuration@EnableAsync@EnableScheduling@EnableAspectJAutoProxy@ComponentScan(basePackages="cn.com.singlemountaintech.dxmanagement")@PropertySource(value={"classpath:application.properties"})publicclassAppConfig{@AutowiredprivateEnvironmentenvironment;@BeanpublicExecutortaskExecutor(){returnnewSimpleAsyncTaskExecutor();}@Bean(name="messageSource")publicMessageSourceconfigureMessageSource(){ReloadableResourceBundleMessageSourcemessageSource=newReloadableResourceBundleMessageSource();messageSource.setBasename("classpath:messages");messageSource.setCacheSeconds(5);messageSource.setDefaultEncoding("UTF-8");messageSource.setFallbackToSystemLocale(true);returnmessageSource;}@Bean(name="localeResolver")publicLocaleResolverlocaleResolver(){SessionLocaleResolverSessionLocaleResolver=newSessionLocaleResolver();returnSessionLocaleResolver;}@Bean(name="multipartResolver")publicCommonsMultipartResolvermultipartResolver(){CommonsMultipartResolvermultipartResolver=newCommonsMultipartResolver();returnmultipartResolver;}}@ResponseBody
@RequestMapping(value="/ajaxUpload",method={RequestMethod.GET,RequestMethod.POST})
public void ajaxUploadExcel(@RequestParam MultipartFile upfile,HttpSession session,HttpServletResponse response) throws Exception {
InputStream in =null;
List<List<Object>> listob = null;
//MultipartFile file = multipartRequest.getFile("upfile");
if(upfile.isEmpty()){
throw new Exception("文件不存在！");
}
in = upfile.getInputStream();
listob = new ImportExcelUtil().getBankListByExcel(in, upfile.getOriginalFilename());
//该处可调用service相应方法进行数据保存到数据库中，现只对数据输出
String tishi = "成功导入数据，文件上传成功！";
try {
for (int i = 0; i < listob.size(); i++) {
List<Object> lo = listob.get(i);
String a = String.valueOf(lo.get(0)).replaceAll("^[ ]+|[ ]+$", "");
String b = String.valueOf(lo.get(1)).replaceAll("^[ ]+|[ ]+$", "");
String c = String.valueOf(lo.get(2)).replaceAll("^[ ]+|[ ]+$", "");
String d = String.valueOf(lo.get(3)).replaceAll("^[ ]+|[ ]+$", "");
String e = String.valueOf(lo.get(4)).replaceAll("^[ ]+|[ ]+$", "");
String f = String.valueOf(lo.get(5)).replaceAll("^[ ]+|[ ]+$", "");
String g = String.valueOf(lo.get(6)).replaceAll("^[ ]+|[ ]+$", "");
Client client = new Client();
client.setClientName(a);
client.setClientContact(b);
client.setClientNum(c);
client.setClientMail(d);
client.setClientAddr(e);
client.setStatus("SUBMITED");
client.setContractName(g);
BigDecimal deposit = new BigDecimal("0.00");
client.setDeposit(deposit);
client = clientService.addClient(client);
if(client == null){
tishi="你导入的excel表格数据和格式有错误，文件上传失败！";
break;
}
//System.out.println(client.toString() + "333333333333");
//System.out.println("打印信息-->机构:"+a+" 名称："+b+" 时间："+c+" 资产："+d+" lallalaal"+e);
}
} catch (Exception e) {
if (logger.isErrorEnabled()) {
logger.error("ajaxUploadExcel", e);
tishi="你导入的excel表格数据和格式有错误，文件上传失败！";
}
}
PrintWriter out = null;
response.setCharacterEncoding("utf-8"); //防止ajax接受到的中文信息乱码
out = response.getWriter();
out.print(tishi);
out.flush();
out.close();
}
下面这个不需要配置
@Bean(name="multipartResolver")
/* @ResponseBody
@RequestMapping(value="/ajaxUpload",method={RequestMethod.GET,RequestMethod.POST})
public void ajaxUploadExcel(HttpServletRequest request,HttpServletResponse response) throws Except