Excel上传下载（后端方法）

@跃焱邵隼(其中也有前端导入方法)
一:后端ExcelKit插件导出

 一：Maven配置依赖
 <dependency>
    <groupId>org.wuwz</groupId>
    <artifactId>ExcelKit</artifactId>
    <version>1.1</version>
</dependency>

 二：实体类 HotSales
 @ExportConfig(value = "商品名称", width = 160)
   private String product;//商品名称
   @ExportConfig(value = "订单数", width = 160)
   private Integer countOrder;//订单数
   @ExportConfig(value = "商品数", width = 160)
   private Integer goodsNum; //商品数
   @ExportConfig(value = "订单额", width = 160)
   private double countMoney; //订单额
   @ExportConfig(value = "含税成本", width = 160)
   private double countBid; //含税成本
   @ExportConfig(value = "毛利", width = 160)
   private double profit;  //毛利
   @ExportConfig(value = "毛利率", width = 160)
   private String profitRate;  //毛利率

三：Controller
@Controller
public class ExportController {
     @Autowired  //注入request以获取session
    HttpServletRequest request;
    @RequestMapping(value="/export0.do")  //通过a标签进来,不要加post 等字样
    @ResponseBody
     public void exportOne(HttpServletResponse response){
        //查询需要导出的数据
      HttpSession session=request.getSession();
      List<ReportSale> list0 = (List<ReportSale>) session.getAttribute("list0"); //或者其他方式获取该list
        // 生成Excel并使用浏览器下载
        ExcelKit.$Export(ReportSale.class, response).toExcel(list0, "表一");
    }
}

 

二：导入

一：Maven配置依赖
 <dependency>
    <groupId>org.wuwz</groupId>
    <artifactId>ExcelKit</artifactId>
    <version>1.1</version>
</dependency>


<dependency>
   <groupId>commons-fileupload</groupId>
   <artifactId>commons-fileupload</artifactId>
   <version>1.3</version>
</dependency>
<dependency>
     <groupId>commons-io</groupId>
     <artifactId>commons-io</artifactId>
     <version>1.4</version>
 </dependency>

二：spring-mvc.xml添加：（配置多媒体解析器--设定文件上传的最大值5MB，5*1024*1024b ）

    <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
        <property name="defaultEncoding" value="UTF-8"></property>

        <property name="maxUploadSize" value="5242880"></property>
    </bean>


三：db.properties加上 8&allowMultiQueries=true 即：
 url=jdbc:mysql://localhost:3306/bdtest?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
driver=com.mysql.jdbc.Driver
user=root
 password=root
 initSize=2
maxActive=10

四：controller
@RequestMapping("/upLoad1.do")
   @ResponseBody
    public int uploadOrderExcel(HttpServletRequest request) {
      Integer affects = null;
      try {
         affects = uploadService.uploadOrderFile(request);
      } catch (Exception e) {
         e.printStackTrace();
      }
      return affects;
   }

五：Dao(mapper)
public interface UploadMapper {
   void insertOrders(List<OrderPay> orderPays);
   void insertGoods(Goods goods);
   Goods findRepeatGoods(Goods goods);
   Integer updateGoods(Goods goods);

}

六：entity
略;（该jar包默认不读第一行的属性名；因为一般为中文；所以 我们的bean像往日一样配置即可）

七：service
public interface IUploadService {
   int uploadOrderFile(HttpServletRequest request) throws Exception;
   int uploadGoodFile(HttpServletRequest request) throws Exception;
   Goods findRepeatGoods(Goods goods);
   Integer updateGoods(Goods goods);
}
八： serviceImp
@Service("uploadService")
public class UploadServiceImpl implements IUploadService{
   @Resource
   private UploadMapper uploadMapper;

   //上传订单信息 （有主键唯一id；自动检查存在 存在就更新 不存在就增加 ）
   public int uploadOrderFile(HttpServletRequest request) throws Exception{
      File excelFile = null;//定义文件
      int i = 0;
        String filePath = request.getSession().getServletContext().getRealPath("/")+"/statics/upload/excel/";
        CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(
                request.getSession().getServletContext());
        // 判断 request是否有文件上传
        if (multipartResolver.isMultipart(request)) {
            // 创建目录
            FileUtils.forceMkdir(new File(filePath));

            // 转换成多部分request
            MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;

            // 取得request中的所有文件名
            Iterator<String> iter = multiRequest.getFileNames();

            while (iter.hasNext()) {
                // 取得上传文件
                MultipartFile file = multiRequest.getFile(iter.next());
                // 取得当前上传文件的文件名称
                String fileName = file.getOriginalFilename();

                if (!(fileName.contains("xls") || fileName.contains("xlsx"))) {
                    throw new IllegalArgumentException("请上传EXCEL文件...");
                }
                excelFile= new File(filePath, fileName);
                file.transferTo(excelFile);
            }
        }
        // 读取并解析文件
         final List<OrderPay> exportData = new ArrayList<OrderPay>();
        try {
            ExcelKit.$Import().readExcel(excelFile, new OnReadDataHandler() {

                public void handler(List<String> row) { //第一行不读，并且一列一列的读
                    OrderPay orderPay = new OrderPay();
                    orderPay.setOrderNo(row.get(0));
                    orderPay.setTime(row.get(1));
                    orderPay.setPayType(row.get(2));
                    orderPay.setVipNo(row.get(3));
                    orderPay.setOrderTotal(Double.parseDouble(row.get(4)));
                    exportData.add(orderPay);
                }
            });
            //批量入库
            if (exportData.size() > 0) {
               //执行批量插入数据库功能 ccbOrderOutMapper.saveBatch()
               uploadMapper.insertOrders(exportData);
               System.out.println("i:"+exportData.size());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return i;
    }

   //上传订单商品信息  （没主键，没唯一id ，需要自行判断是否存在 ，存在就更新 不存在就增加）
   public int uploadGoodFile(HttpServletRequest request) throws Exception{
      File excelFile = null;//定义文件
      int i = 0;
        String filePath = request.getSession().getServletContext().getRealPath("/")+"/statics/upload/excel/";
        CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(
                request.getSession().getServletContext());
        // 判断 request是否有文件上传
        if (multipartResolver.isMultipart(request)) {
            // 创建目录
            FileUtils.forceMkdir(new File(filePath));

            // 转换成多部分request
            MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;

            // 取得request中的所有文件名
            Iterator<String> iter = multiRequest.getFileNames();

            while (iter.hasNext()) {
                // 取得上传文件
                MultipartFile file = multiRequest.getFile(iter.next());
                // 取得当前上传文件的文件名称
                String fileName = file.getOriginalFilename();

                if (!(fileName.contains("xls") || fileName.contains("xlsx"))) {
                    throw new IllegalArgumentException("请上传EXCEL文件...");
                }
                excelFile= new File(filePath, fileName);
                file.transferTo(excelFile);
            }
        }

        // 读取并解析文件
         final List<Goods> goodsList = new ArrayList<Goods>();
        try {
            ExcelKit.$Import().readExcel(excelFile, new OnReadDataHandler(){
                public void handler(List<String> row) {
                   Goods goods = new Goods();
                   goods.setOrderNo(row.get(0));
                   goods.setCode(row.get(1));
                   goods.setProduct(row.get(2));
                   goods.setNum(Integer.parseInt(row.get(3)));
                   goods.setPrice(Double.parseDouble(row.get(4)));
                   goods.setSumPro(Double.parseDouble(row.get(5)));
                   goodsList.add(goods);
                }
            });
              //逐个入库
                if (goodsList.size() > 0) {
                   for (Goods goods2 : goodsList) {
                      Goods good = findRepeatGoods(goods2);
                        if(good==null) {
                          //不存在，插入新数据
                          uploadMapper.insertGoods(goods2);
                        }else {
                          //存在更新
                   updateGoods(goods2);
                }
            }
               }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return i;
    }

   public Goods findRepeatGoods(Goods goods) {
      return uploadMapper.findRepeatGoods(goods);
   }

   public Integer updateGoods(Goods goods) {
      return uploadMapper.updateGoods(goods);
   }
}