jsp页面
<link rel="stylesheet" type="text/css" href="<%=path%>/public/uploadify/uploadify.css">
<script type="text/javascript">
var newPath=path;//重新定义一个path值,因为uploadify会将名为path的变量值改掉
</script>
<script type="text/javascript" src="<%=path%>/public/uploadify/jquery.uploadify.min.js"></script>
<a target="_blank" href="<%=path%>/download/产品清单模板.xls" class="easyui-linkbutton" plain="true">模板下载</a>
<a href="#" class="easyui-linkbutton" iconCls="icon-upload" data-options="plain:true"><input id="uploadify" name="uploadify" type="file">批量上传</a>
js代码
$('#uploadify')
.uploadify(
{
// buttonImageUrl:"",
fileObjName : "file",
auto : true,
swf : newPath + '/public/uploadify/uploadify.swf',
// fileSizeLimit:1024,
multi : false,
progressData : 'percentage',
queueID : 'footer',
removeCompleted : true,
buttonClass : 'uploadButton',
uploader : newPath + '/file/improtExcelList;JSESSIONID='+getCookie('sid'),
fileTypeExts : '*.xls;*.xlsx',
formData : {
"proItem" : proItemId,
"tenderItemId" : tenderItemId,
},
onUploadSuccess : function(file, data, response) {
console.log(data);
data = JSON.parse(data);
if (data.code == 2) {
alert(data.msg);
} else if ("411".localeCompare(data.code) == 0) {
alert(data.msg);
} else if (1 == data.code) {
$('#productMentTable').datagrid('reload');
}
$("#footer").dialog('close');
},
onUploadError : function(file, errorCode, errorMsg,
errorString) {
alert('The file ' + file.name
+ ' could not be uploaded: ' + errorString);
},
onFallback : function() {
alert("您未安装FLASH控件,无法上传图片!请安装FLASH控件后再试。");
},
onUploadStart : function() {
$("#footer").dialog('open', 'true');
}
});
Controller层
//货物类 批量导入Excel数据到数据库
@RequestMapping(value = "/improtExcelList", method = RequestMethod.POST)
@ResponseBody
public JSONObject improtExcelList(@RequestParam("file") MultipartFile file,
HttpServletRequest request, HttpServletResponse response)
throws Exception {
return this.productListService.insertPriceList(file, request);
}
Service层
@Override
public JSONObject insertPriceList(MultipartFile file,
HttpServletRequest request) {
JSONObject json = new JSONObject();
try {
String proItemId = request.getParameter("proItem");
String tenderItemId = (null == request.getParameter("tenderItemId") || "".equals(request.getParameter("tenderItemId"))) ? null : request.getParameter("tenderItemId");
Party party=(Party) SecurityUtils.getSubject().getSession().getAttribute(SystemConstant.SESSION_USER);
List<List<Object>> list = ImportExcelUntil.getExcelList(file.getInputStream(), file.getOriginalFilename());
//List<String> x=new ArrayList<String>();
for (int i = 0; i < list.size(); i++) {
List<Object> lo = list.get(i);
if (null != proItemId && !"".equals(proItemId) && null != lo&& 0 < lo.size()) {
if (null != lo.get(0).toString() && !"".equals(lo.get(0).toString())
&& null != lo.get(1).toString() && !"".equals(lo.get(1).toString())
&& null != lo.get(2).toString() && !"".equals(lo.get(2).toString())) {
NProductList productList = new NProductList();
productList.setCreateDate(this.sysParamsMapper.selectDbDate());// 创建时间
productList.setUserIp(request.getRemoteAddr());// IP地址
productList.setIsEdit("0");
productList.setName(lo.get(0).toString());
productList.setQuantity(new BigDecimal(lo.get(1).toString()));
productList.setUnit(lo.get(2).toString());
productList.setIsAcceptImport(SystemConstant.N_PRODUCT_NOACCEPT_TYPE);
productList.setState("10A");
productList.setProcurementItemId(Long.parseLong(proItemId));
productList.setTenderItemId((null != tenderItemId && !"null".equals(tenderItemId)) ? Long.valueOf(tenderItemId):null);
Long sortIndex = ((this.productListMapper.selectNextSortIndex(productList) == null || this.productListMapper
.selectNextSortIndex(productList) == 0) ? 1L
: this.productListMapper.selectNextSortIndex(productList)) + 1;
productList.setSortIndex(sortIndex);
productList.setCreateBy(party.getPartyId());
productList.setCreateDate(this.sysParamsMapper.selectDbDate());
productList.setCreateName(party.getPartyName());
productListMapper.insertProductList(productList);
NProductReq nProductReq=new NProductReq();
nProductReq.setProductListId(productList.getProductListId());
nProductReq.setProcurementItemId(Long.parseLong(proItemId));
nProductReq.setTenderItemId((null != tenderItemId && !"null".equals(tenderItemId)) ? Long.valueOf(tenderItemId):null);
nProductReq.setSubstanceReq(lo.get(3).toString());
nProductReq.setnSubstanceReq(lo.get(4).toString());
nProductReq.setCatalogType(SystemConstant.CATALOG_TYPE_BUYER_TECH_PARAM);
nProductReq.setState("10A");
nProductReq.setCreateBy(party.getPartyId());
nProductReq.setCreateDate(this.sysParamsMapper.selectDbDate());
nProductReq.setCreateName(party.getPartyName());
this.nProductReqMapper.insertSelective(nProductReq);
//}
}
}
json.put("code", "1");
json.put("msg", "插入成功");
/*for (int j = 0; j < lo.size(); j++) {
Object o = list.get(i).get(j);
System.out.print(o + "\t\t");
}
System.out.println();*/
}
} catch (Exception e) {
json.put("code", "411");
json.put("msg", "文件存在错误行如单元格数据未填或空行等,请先更正后再提交");
e.printStackTrace();
return json;
}
return json;
}
Excel工具类
package com.conzsoft.trading.utils;
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;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
/**
* Excel读取工具类
*/
public class ImportExcelUntil {
private final static String excel2003L = ".xls";
private final static String excel2007U = ".xlsx";
// 读取文件数据
public static List<List<Object>> getExcelList(InputStream is,
String fileName) throws Exception {
List<List<Object>> list = new ArrayList<List<Object>>();
Workbook workbook = null;
// 验证文件格式
String suffix = fileName.substring(fileName.lastIndexOf("."));
if (suffix.equals(excel2003L)) {
workbook = new HSSFWorkbook(is);
} else if (suffix.equals(excel2007U)) {
workbook = new XSSFWorkbook(is);
}
Sheet sheet = null;
Row row = null;
Cell cell = null;
sheet = workbook.getSheetAt(0);
List<Object> errorRow=new ArrayList<Object>();
int beginRow=7;//文件中除去模版数据,有效数据的起始行,从0开始
// 遍历当前sheet中全部行
for (int j = beginRow; j <= sheet.getLastRowNum(); j++) {
row = sheet.getRow(j);
if (row == null)
continue;
// 循环当前row中全部列
List<Object> li = new ArrayList<Object>();
int k = 0;
for (; k < 3; k++) {
cell = row.getCell(k);
if (cell != null && Cell.CELL_TYPE_BLANK!=cell.getCellType()) {
li.add(getCellValue(cell));
}else{
errorRow.add(j);
break;
}
}
Cell cell_3=row.getCell(3);
Cell cell_4=row.getCell(4);
if((cell_3!=null && cell_4!=null && Cell.CELL_TYPE_BLANK!=cell_3.getCellType() && Cell.CELL_TYPE_BLANK!=cell_4.getCellType()) ||
((cell_3==null || Cell.CELL_TYPE_BLANK==cell_3.getCellType()) && cell_4!=null && Cell.CELL_TYPE_BLANK!=cell_4.getCellType()) ||
((cell_4==null || Cell.CELL_TYPE_BLANK==cell_4.getCellType()) && cell_3!=null && Cell.CELL_TYPE_BLANK!=cell_3.getCellType())){
if(cell_3==null){
li.add("");
}else{
li.add(getCellValue(cell_3));
}
if(cell_4==null){
li.add("");
}else{
li.add(getCellValue(cell_4));
}
}else{
errorRow.add(j);
break;
}
if(k==3){
list.add(li);
}
}
if(!errorRow.isEmpty()){//有错误行
throw new Exception();
}
return list;
}
// 单元格数据类型格式化
public static Object getCellValue(Cell cell) {
Object value = null;
DecimalFormat decimalFormat1 = new DecimalFormat("0");
DecimalFormat decimalFormat2 = new DecimalFormat("0.00");
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
if ("General".equals(cell.getCellStyle().getDataFormatString())) {
value = decimalFormat2.format(cell.getNumericCellValue());
} else if ("m/d/yy".equals(cell.getCellStyle()
.getDataFormatString())) {
value = dateFormat.format(cell.getDateCellValue());
} else {
value = decimalFormat2.format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
value = "";
break;
case Cell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
}
return value;
}
}