一:导入jar包
二:后端代码:
@Service("bookingOrderServiceImpl")
public class BookingOrderServiceImpl extends BaseServiceImpl<BookingOrder> implements BookingOrderService {
@Autowired
private BookingOrderDao bookingOrderDao;
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("YYYY-MM-DD hh:mm:ss"); //格式时间
@Override
@Transactional
public void uploadExcel(MultipartFile file) {
try {
System.out.println("---------------------------");
Workbook workbook = new HSSFWorkbook(file.getInputStream());//根据上传的输入流创建workbook
// Workbook workbook = new XSSFWorkbook(file.getInputStream());
Sheet sheet = workbook.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();//获取这个sheet中最后一行数据,为了循环
BookingOrder bookingOrder = null;
Row row = null;
for (int i = 0; i <= lastRowNum; i++) {
row = sheet.getRow(i+1);//注意这是数据的第一行起 不算表头所以加一
bookingOrder = new BookingOrder();
String predictTheSingleNumber = row.getCell(0).getStringCellValue();
bookingOrder.setPredictTheSingleNumber(predictTheSingleNumber);
String name = row.getCell(1).getStringCellValue();
System.out.println("name===="+name);
bookingOrder.setName(name);
String organ = row.getCell(2).getStringCellValue();
bookingOrder.setOrgan(organ);
String customerOrProjectName = row.getCell(3).getStringCellValue();
bookingOrder.setCustomerOrProjectName(customerOrProjectName);
String sort = row.getCell(4).getStringCellValue();
bookingOrder.setSort(sort);
String productCode = row.getCell(5).getStringCellValue();
bookingOrder.setProductCode(productCode);
String model = row.getCell(6).getStringCellValue();
bookingOrder.setModel(model);
System.out.println("model==="+model);
String amount = row.getCell(7).getStringCellValue();
System.out.println("数量====="+amount);
bookingOrder.setAmount(new Integer(amount));
String price = row.getCell(8).getStringCellValue();
bookingOrder.setPrice(Double.valueOf(price));
String sales = row.getCell(9).getStringCellValue();
bookingOrder.setSales(Double.valueOf(sales));
String month = row.getCell(10).getStringCellValue();
System.out.println("month==="+month);
bookingOrder.setMonth(simpleDateFormat.parse(month));//出货月份
String date = row.getCell(11).getStringCellValue();
System.out.println("date===="+date);
System.out.println("date====转换后"+simpleDateFormat.parse(date));
bookingOrder.setDate(simpleDateFormat.parse(date));//预估出货日期
// String importtime = row.getCell(12).getStringCellValue();
bookingOrder.setImportTime(new Timestamp(System.currentTimeMillis()));
this.save(bookingOrder);
System.out.println("保存结束");
}
} catch (Exception e) {
e.printStackTrace();
LogUtils.error("导入结束"+e);
}
}
三:前端代码:
function productPriceImport(e){
excel_import(e,{
title:"${message("预售订单")}",
url:"/b2b/booking_order/upload_Excel.jhtml",//导入excel的方法
template:"/resources/template/product/bookingorder.xls",//这是模板的路径
callback:function(resultMsg){
$("#searchBtn").click();
}
})
}
<div class="buttonWrap">
<div class="flag-wrap flagImp-wrap">
<a href="javascript:void(0);" class="iconButton" id="export1Button">
<span class="impIcon"> </span>导出/导入
</a>
<ul class="flag-list">
<li><a href="javascript:void(0)" onclick="selectToExcel('#table-m1','预售订单')"><i class="flag-imp02"></i>选择导出</a></li>
<li><a href="javascript:void(0)" onclick="productPriceImport(this)"><i class="flag-imp01"></i>${message("导入")}</a></li>
[#-- <li><a href="javascript:void(0)" onclick="openExportList('#table-m1','计划偏差率导出',10000)"><i class="flag-imp02"></i>条件导出</a></li>--]
</ul>
</div>
</div>
注意事项:导入的excel表里面的数据类型 要和后端代码保持一致
哪一行的数据不能搞错序号
POI操作报错-- java.lang.NoClassDefFoundError: org/apache/commons/math3/util/ArithmeticUtils 解决:
POI-4.1.1,操作报错如下:
Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/commons/math3/util/ArithmeticUtils
解决:需要引用 commons-math3-3.6.1.jar
位置:POI-->lib目录下