1.前台部分
1.1 上传按钮
<div class="form-group form-group-xs" style="margin-bottom: 0.2em;">
<div class="normalSearch" style="display: inline-block;">
<input type="file" name="uploadFile" multiple id="uploadFile" accept=".xls,.xlsx"/ style="font-size: 10px;font-family: Microsoft YaHei;border-style: ridge;border-width: 1px;padding: 1px;">
</div>
</div>
<div class="form-group form-group-xs" style="margin-bottom: 0.2em;">
<a οnclick="importClick()" href="javascript:void(0);" class="easyui-linkbutton ebsButton" data-options="iconCls:'fa fa-upload fa-1x',plain:true">上传</a>
</div>
1.2js代码
function importClick(){
if($("#uploadFile").val()==""){
layer.msg('请选择要上传的文件', {icon: 0});
return;
}
layer.confirm('上传不会覆盖原有的信息,是否继续操作?', {
btn: ['确认','取消'] //按钮
}, function(){
var index = layer.load();
$.ajaxFileUpload({
url:"${pageContext.request.contextPath}/impt/ZB/ZBRemittanceDetail/import.do",
secureuri: false,
fileElementId:"uploadFile",
dataType: 'text',
type:"POST",
success: function (data, status){
layer.close(index);
var res = data.substring((data.indexOf('>')+1),(data.indexOf('}')+1));
var json = $.parseJSON(res);
if (json.success) {
layer.msg(json.msg, {icon: 6});
}else{
layer.msg(json.msg, {icon: 5});
}
},
error: function (data, status, e){
}
});
}, function(){
}
);
}
2.后台部分
2.1bean
与Excel表中字段保持一致(如果表中有唯一字段内容可以作为主键,如果没有另外加一个id 自增即可)
2.2service
public DataGrid queryList(ZBRemittanceDetailBean bean, PageParams pageParams) throws Exception;
public void importByExcel(CommonsMultipartFile uplodaFile, Long userId) throws Exception ;
//实现
@Override
public DataGrid queryList(ZBRemittanceDetailBean bean, PageParams pageParams) throws Exception {
StringBuffer hql = new StringBuffer("from ZBRemittanceDetail o ");
hql.append(" where 1=1 ");
StringBuffer params = new StringBuffer();
//根据关键字
if (StringUtils.isNotEmpty(bean.getKeyWords())) {
params.append(" and (( o.contractCode like '%" + bean.getKeyWords() + "%')");
params.append(" ) ");
}
hql.append(params);
List<ZBRemittanceDetail> rows = queryByPage(hql.toString(), pageParams);
List<ZBRemittanceDetailBean> result = new ArrayList<ZBRemittanceDetailBean>();
if (rows != null && rows.size() > 0) {
for (ZBRemittanceDetail entity : rows) {
ZBRemittanceDetailBean model = convertModelToBean(entity);
result.add(model);
}
}
StringBuffer countHql = new StringBuffer("select count(*) from ZBRemittanceDetail o where 1=1");
countHql.append(params);
long total = queryTotal(countHql.toString());
return new DataGrid(result, total);
}
/**
* Model对象转换为Bean值对象
*/
private ZBRemittanceDetailBean convertModelToBean(ZBRemittanceDetail entity) {
ZBRemittanceDetailBean bean = new ZBRemittanceDetailBean();
//项目名称
bean.setName(entity.getName());
//合同号
bean.setContractCode(entity.getContractCode());
//总金额
bean.setAllMoney(entity.getAllMoney());
//分公司
bean.setBranchCompany(entity.getBranchCompany());
//品牌分类
bean.setBrandType(entity.getBranchCompany());
//地市
bean.setCity(entity.getCity());
//区县
bean.setCounty(entity.getCounty());
//跨区域
bean.setCrossRegion(entity.getCrossRegion());
//分配金额
bean.setDistributionMoney(entity.getDistributionMoney());
//分配办事处
bean.setDistributionOffice(entity.getDistributionOffice());
//分配比例
bean.setDistributionRatio(entity.getDistributionRatio());
//分配类型
bean.setDistributionType(entity.getDistributionType());
//经销商
bean.setDistributor(entity.getDistributor());
//销售工程师
bean.setEngineer(entity.getEngineer());
//项目分类
bean.setProjectType(entity.getProjectType());
//项目用途
bean.setProjectUses(entity.getProjectUses());
//省份
bean.setProvince(entity.getProvince());
//房地产公司
bean.setRealEstateCompany(entity.getRealEstateCompany());
//大区
bean.setRegional(entity.getRegional());
//回款日期
bean.setReturnMoneyDate(entity.getReturnMoneyDate());
//签约日期
bean.setSiqningDate(entity.getSiqningDate());
//回款类型
bean.setReturnMoneyType(entity.getReturnMoneyType());
//签约方式
bean.setSiqningType(entity.getSiqningType());
//战略商
bean.setStrategist(entity.getStrategist());
//垫资商
bean.setUpholsterer(entity.getUpholsterer());
//年份
bean.setYear(entity.getYear());
return bean;
}
@Override
public void importByExcel(CommonsMultipartFile uplodaFile, Long userId) throws Exception {
try { List<ZBRemittanceDetail> list = new ArrayList<ZBRemittanceDetail>();
// 根据选择的文件读取流
InputStream inputStream = uplodaFile.getInputStream();
Workbook workbook = null;
Sheet sheet = null;
Row row = null;
int rowCount = 0;
int sheetCount = 0;
// 获取文件后缀名
String fileTxt = uplodaFile.getFileItem().getName().substring(uplodaFile.getFileItem().getName().lastIndexOf(".")+1);
if("xls".equals(fileTxt)){// 2003版
workbook = new HSSFWorkbook(inputStream);
}else if("xlsx".equals(fileTxt)){// 2007版
workbook = new XSSFWorkbook(inputStream);
}
if(sheetCount >0){
//获取工作表
for (int j = 0; j < sheetCount; j++) {
sheet = workbook.getSheetAt(j);
rowCount = sheet.getPhysicalNumberOfRows();//获取行数
for (int i = 1; i < rowCount; i++) {
row = sheet.getRow(i);// 获取列对象
if (row == null) {
continue;
}
ZBRemittanceDetail detail;
try {
detail = getZBProjectDetailsByRow(row, userId);
}catch(Exception e) {
throw new Exception(sheet.getSheetName() + "页面中第" + (row.getRowNum()+1) +"行,格式错误!");
}
list.add(detail);
}
}
}
for (ZBRemittanceDetail entity : list) {
this.save(entity);
}
} finally {
if(uplodaFile != null){
uplodaFile.getFileItem().getInputStream().close();
}
}
}
private ZBRemittanceDetail getZBProjectDetailsByRow(Row row, Long userId) throws Exception {
//大区
Cell regionalCell = row.getCell(0);
regionalCell.setCellType(Cell.CELL_TYPE_STRING);
String regional = regionalCell.getStringCellValue().trim();
//分公司
Cell branchCompanyCell = row.getCell(1);
branchCompanyCell.setCellType(Cell.CELL_TYPE_STRING);
String branchCompany = branchCompanyCell.getStringCellValue().trim();
//合同号
Cell contractCodeCell = row.getCell(2);
contractCodeCell.setCellType(Cell.CELL_TYPE_STRING);
String contractCode = contractCodeCell.getStringCellValue().trim();
//签约日期
Cell siqningDateCell = row.getCell(3);
siqningDateCell.setCellType(Cell.CELL_TYPE_NUMERIC);
Date siqningDate = siqningDateCell.getDateCellValue();
//经销商distributor
Cell distributorCell = row.getCell(4);
distributorCell.setCellType(Cell.CELL_TYPE_STRING);
String distributor = distributorCell.getStringCellValue().trim();
//项目名称name
Cell nameCell = row.getCell(5);
nameCell.setCellType(Cell.CELL_TYPE_STRING);
String name = nameCell.getStringCellValue().trim();
//销售工程师 engineer
Cell engineerCell = row.getCell(6);
engineerCell.setCellType(Cell.CELL_TYPE_STRING);
String engineer = engineerCell.getStringCellValue().trim();
//省份province
Cell provinceCell = row.getCell(7);
provinceCell.setCellType(Cell.CELL_TYPE_STRING);
String province = provinceCell.getStringCellValue().trim();
//地市
Cell cityCell = row.getCell(8);
cityCell.setCellType(Cell.CELL_TYPE_STRING);
String city = cityCell.getStringCellValue().trim();
//区县
Cell countyCell = row.getCell(9);
countyCell.setCellType(Cell.CELL_TYPE_STRING);
String county = countyCell.getStringCellValue().trim();
//项目分类
Cell projectTypeCell = row.getCell(10);
projectTypeCell.setCellType(Cell.CELL_TYPE_STRING);
String projectType = projectTypeCell.getStringCellValue().trim();
//项目用途
Cell projectUsesCell = row.getCell(11);
projectUsesCell.setCellType(Cell.CELL_TYPE_STRING);
String projectUses = projectUsesCell.getStringCellValue().trim();
//回款日期returnMoneyDate
Cell returnMoneyDateCell = row.getCell(12);
returnMoneyDateCell.setCellType(Cell.CELL_TYPE_NUMERIC);
Date returnMoneyDate = returnMoneyDateCell.getDateCellValue();
//总金额 allMoney
Cell allMoneyCell = row.getCell(13);
allMoneyCell.setCellType(Cell.CELL_TYPE_STRING);
String allMoney = allMoneyCell.getStringCellValue().trim();
//跨区域 crossRegion
Cell crossRegionCell = row.getCell(14);
crossRegionCell.setCellType(Cell.CELL_TYPE_STRING);
String crossRegion = crossRegionCell.getStringCellValue().trim();
//回款类型 returnMoneyType
Cell returnMoneyTypeCell = row.getCell(15);
returnMoneyTypeCell.setCellType(Cell.CELL_TYPE_STRING);
String returnMoneyType = returnMoneyTypeCell.getStringCellValue().trim();
//分配类型 distributionType
Cell distributionTypeCell = row.getCell(16);
distributionTypeCell.setCellType(Cell.CELL_TYPE_STRING);
String distributionType = distributionTypeCell.getStringCellValue().trim();
//分配办事处 distributionOffice
Cell distributionOfficeCell = row.getCell(17);
distributionOfficeCell.setCellType(Cell.CELL_TYPE_STRING);
String distributionOffice = distributionOfficeCell.getStringCellValue().trim();
//分配比例 distributionRatio
Cell distributionRatioCell = row.getCell(18);
distributionRatioCell.setCellType(Cell.CELL_TYPE_STRING);
String distributionRatio = distributionRatioCell.getStringCellValue().trim();
//分配金额 distributionMoney
Cell distributionMoneyCell = row.getCell(19);
distributionMoneyCell.setCellType(Cell.CELL_TYPE_STRING);
String distributionMoney = distributionMoneyCell.getStringCellValue().trim();
//房地产公司
Cell realEstateCompanyCell = row.getCell(20);
realEstateCompanyCell.setCellType(Cell.CELL_TYPE_STRING);
String realEstateCompany = realEstateCompanyCell.getStringCellValue().trim();
//战略商
Cell strategistCell = row.getCell(21);
strategistCell.setCellType(Cell.CELL_TYPE_STRING);
String strategist = strategistCell.getStringCellValue().trim();
//年份
Cell yearCell = row.getCell(22);
yearCell.setCellType(Cell.CELL_TYPE_STRING);
String year = yearCell.getStringCellValue().trim();
//签约方式
Cell siqningTypeCell = row.getCell(23);
siqningTypeCell.setCellType(Cell.CELL_TYPE_STRING);
String siqningType = siqningTypeCell.getStringCellValue().trim();
//垫资商
Cell upholstererCell = row.getCell(24);
upholstererCell.setCellType(Cell.CELL_TYPE_STRING);
String upholsterer = upholstererCell.getStringCellValue().trim();
//品牌分类
Cell brandTypeCell = row.getCell(25);
brandTypeCell.setCellType(Cell.CELL_TYPE_STRING);
String brandType = brandTypeCell.getStringCellValue().trim();
ZBRemittanceDetail detail =findBycontractCode(contractCode);
detail.setAllMoney(Double.parseDouble(allMoney));
detail.setRegional(regional);
detail.setBranchCompany(branchCompany);
detail.setContractCode(contractCode);
detail.setSiqningDate(siqningDate);
detail.setDistributor(distributor);
detail.setName(name);
detail.setEngineer(engineer);
detail.setProvince(province);
detail.setCity(city);
detail.setCounty(county);
detail.setProjectType(projectType);
detail.setProjectUses(projectUses);
detail.setReturnMoneyDate(returnMoneyDate);
if (crossRegion!=null&&crossRegion!="") {
detail.setCrossRegion(crossRegion);
}
detail.setReturnMoneyType(returnMoneyType);
detail.setDistributionType(distributionType);
detail.setDistributionOffice(distributionOffice);
detail.setDistributionRatio(Double.parseDouble(distributionRatio));
detail.setDistributionMoney(Double.parseDouble(distributionMoney));
if (realEstateCompany!=null&&realEstateCompany!="") {
detail.setRealEstateCompany(realEstateCompany);
}
if (strategist!=null&&strategist!="") {
detail.setStrategist(strategist);
}
if (year!=null&&year!="") {
detail.setYear(year);
}
if (siqningType!=null&&siqningType!="") {
detail.setSiqningType(siqningType);
}
if (upholsterer!=null&&upholsterer!="") {
detail.setUpholsterer(upholsterer);
}
detail.setBrandType(brandType);
return detail ;
}
private ZBRemittanceDetail findBycontractCode(String contractCode) {
ZBRemittanceDetail detail;
StringBuffer hql = new StringBuffer("from ZBProjectDetails o where 1=1 ");
hql.append(" and o.contractCode='" + contractCode + "' ");
List<ZBRemittanceDetail> rows = query(hql.toString());
if (rows.size() > 0) {
detail = rows.get(0);
}else {
detail = new ZBRemittanceDetail();
}
return detail;
}
大体代码就完了,细节需要注意的就是 字段类型和表中字段内容是否为空(判断)
欢迎讨论,学习