1-前端框架EasyUI,导入弹出框样式
<!--导入弹出框 -->
<div id="imp" class="easyui-dialog" data-options="modal:true"
title="导入" style="width: 300px; height: 250px; padding: 5px 2px"
closed="true">
<div align="center" style="margin-top: 10px;">
<span>导入模板：</span>
<td><a
href="<%=this.getServletContext().getContextPath()%>/xls/Price.xls">Price</a></td>
</div>
<div align="center" style="margin-top: 20px;">
<form id="impFile" method="post" enctype="multipart/form-data">
<p>
<input id="importExcel" name="importExcel" type="file"
class="easyui-filebox" style=" border:1px solid #c0c0c0;" />
</p>
<div align="center" style="margin-top: 20px;">
<span>请选择价格信息年份：</span> <input id="query_combobox"
class="easyui-combobox" name="queryCombobox" style="width:106px;"></input>
</div>
</form>
</div>2-js代码验证导入信息：根据自己需要判断的逻辑和后台返回的数据去验证
function impExcel(){
var year=$("#query_combobox").combobox('getValue');
var importFile=$("#importExcel").val();
if(importFile==''){
$.messager.alert('提示',"请选择导入文件");
return false;
}
if(year==''){
$.messager.alert('提示',"请选择价格年份");
return false;
}
var reg=".xls$|.xlsx$";
var patrn=new RegExp(reg);
//匹配文件后缀
if(patrn.exec(importFile)){
$("#impFile").form('submit',{
url:'import?year='+year,
onSubmit:function(){
},
success:function(data){
data=eval('('+data+')');
var flag=data.flag;
alert(flag);
if(flag){
$("#imp").window("close");
$.messager.alert('提示',"导入成功！");
All();
}else{
$("#imp").window("close");
$.messager.alert('提示',"批量新增失败！请核对导入的信息是否是选择的年份信息！");
All();
}
}
});
}else{
$.messager.alert("提示","请导入.xls形式的Excel!");
return false;
}
}
3-后台controller,导入验证excel版本2007版本以上以下兼容。
@RequestMapping("/import")
@onMethod(remark="价格信息导入")
public void fileUpload(HttpServletRequest request, HttpServletResponse response,String year) throws IOException {
PrintWriter out = response.getWriter();
Workbook wb = getWorkbookFromRequest(request, response);
Map<String, Object> map = tPriceMainService.importExcel(wb,year);
JSONObject json = new JSONObject(map);
String jsonString = json.toJSONString();
out.print(jsonString);
out.flush();
out.close();
}
private Workbook getWorkbookFromRequest(HttpServletRequest request, HttpServletResponse response)
throws IOException {
InputStream is = null;
response.setCharacterEncoding("UTF-8");
response.setContentType("text/json;charset=UTF-8");
PrintWriter out = response.getWriter();
CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(
request.getSession().getServletContext());
if (multipartResolver.isMultipart(request)) {
MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
Iterator<String> iterator = multiRequest.getFileNames();
while (iterator.hasNext()) {
MultipartFile multipartFile = multiRequest.getFile(iterator.next());
if (multipartFile != null) {
is = new ByteArrayInputStream(multipartFile.getBytes());
}
}
}
Workbook wb=null;
try {
//WorkbookFactory.create兼容HSSFWorkbook和XSSFWorkbook两种Excel
wb = WorkbookFactory.create(is);
} catch (EncryptedDocumentException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
return wb;
}4-导入实现类：根据自己的需求，在里面ha一下代码，删除花里胡哨的。
public Map<String, Object> importExcel(Workbook wb, String year) {
Map<String, Object> map = new HashMap<String, Object>();
List<TPriceMain> list = new ArrayList<TPriceMain>();
Sheet sheet = wb.getSheetAt(0);
// 获取总行数
int rows = sheet.getPhysicalNumberOfRows();
for (int start = 1; start < rows; start++) {
// 从第二行开始
Row row = sheet.getRow(start);
if(row.getCell(0).toString()==null||"".equals(row.getCell(0).toString())){
break;
}
System.out.println(row.getCell(0).toString().substring(0, 4));
if (!row.getCell(0).toString().substring(0, 4).equals(year)) {
map.put("flag", false);
return map;
} else {
TPriceMain tPriceMain = new TPriceMain();
for (int i = 0; i < 15; i++) {
Cell cell = row.getCell(i);
String cellValue = getCellValue(cell);
switch (i) {
case 0:
if (cellValue == null || "".equals(cellValue)) {
tPriceMain.setYear(null);
} else {
tPriceMain.setYear(cellValue);
}
break;
case 1:
if (cellValue == null || "".equals(cellValue)) {
tPriceMain.setValidityDate(null);
} else {
tPriceMain.setValidityDate(cellValue);
}
break;
case 2:
if (cellValue == null || "".equals(cellValue)) {
tPriceMain.setSendingDateFirmorder(null);
} else {
tPriceMain.setSendingDateFirmorder(cellValue);
}
break;
case 3:
if (cellValue == null || "".equals(cellValue)) {
tPriceMain.setVehicleDescription(null);
} else {
tPriceMain.setVehicleDescription(cellValue);
}
break;
case 4:
if (cellValue == null || "".equals(cellValue)) {
tPriceMain.setVehicleDescriptionI(null);
} else {
tPriceMain.setVehicleDescriptionI(cellValue);
}
break;
case 5:
if (cellValue == null || "".equals(cellValue)) {
tPriceMain.setListOfVehicles(null);
} else {
tPriceMain.setListOfVehicles(cellValue);
}
break;
case 6:
if (cellValue == null || "".equals(cellValue)) {
tPriceMain.setSpeficicOperations(null);
} else {
tPriceMain.setSpeficicOperations(cellValue);
}
break;
case 7:
if (cellValue == null || "".equals(cellValue)) {
tPriceMain.setListVehiclesAttributesmax(null);
} else {
tPriceMain.setListVehiclesAttributesmax(cellValue);
}
break;
case 8:
if (cellValue == null || "".equals(cellValue)) {
tPriceMain.setVovrType(null);
} else {
tPriceMain.setVovrType(cellValue);
}
break;
case 9:
if (cellValue == null || "".equals(cellValue)) {
tPriceMain.setVoType(null);
} else {
tPriceMain.setVoType(cellValue);
}
break;
case 10:
if (cellValue == null || "".equals(cellValue)) {
tPriceMain.setPlantCode(null);
} else {
tPriceMain.setPlantCode(cellValue);
}
break;
case 11:
if (cellValue == null || "".equals(cellValue)) {
tPriceMain.setOriginType(null);
} else {
tPriceMain.setOriginType(cellValue);
}
break;
case 12:
if (cellValue == null || "".equals(cellValue)) {
tPriceMain.setVehicleCodificationCqca(null);
} else {
tPriceMain.setVehicleCodificationCqca(cellValue);
}
break;
case 13:
if (cellValue == null || "".equals(cellValue)) {
tPriceMain.setPrice(null);
} else {
tPriceMain.setPrice(new BigDecimal(cellValue));
}
break;
case 14:
if (cellValue == null || "".equals(cellValue)) {
tPriceMain.setPriceYear(null);
} else {
tPriceMain.setPriceYear(cellValue);
}
break;
default:
break;
}
}
if (tPriceMain != null) {
list.add(tPriceMain);
}
}
}
for (TPriceMain tp : list) {
TPriceMainExample example = new TPriceMainExample();
Criteria cr = example.createCriteria();
cr.andYearEqualTo(tp.getYear());
cr.andVehicleCodificationCqcaEqualTo(tp.getVehicleCodificationCqca());
int count = tPriceMainMapper.countByExample(example);
if (count == 0) {
tPriceMainMapper.insertSelective(tp);
} else {
tPriceMainMapper.updateByExample(tp, example);
}
}
map.put("flag", true);
return map;
}
private String getCellValue(Cell cell) {
String result = "";
if (cell != null) {
switch (cell.getCellType()) {
// 数字类型 +日期类型
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
}
Date date = cell.getDateCellValue();
result = sdf.format(date);
} else if (cell.getCellStyle().getDataFormat() == 58) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
result = sdf.format(date);
} else {
DecimalFormat df = new DecimalFormat();
df.setGroupingUsed(false);
result = String.valueOf(df.format(cell.getNumericCellValue()));
}
break;
// String类型
case HSSFCell.CELL_TYPE_STRING:
result = String.valueOf(cell.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
result = "";
default:
result = "";
break;
}
}
return result;
}
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
