java中用jxl方式实现导入/导出

1.文件的导出
1.1公用的导出类

  public class ExportExcelController {

    protected    Logger logger = LoggerFactory.getLogger(getClass());
    public  void exportExcel(HttpServletResponse response,Map< Integer, Object> map,Map< Integer, Object> maps) {

         String fileName = DateUtil.getSdfTimes() + ".xls";// 生成文件名;
         response.setCharacterEncoding("UTF-8");
         response.setContentType("application/x-excel");
         response.addHeader("Content-Disposition", "attachment;filename=" +fileName);
         try {
            WritableWorkbook workbook=Workbook.createWorkbook(response.getOutputStream());
            WritableCellFormat wf=new WritableCellFormat();
            wf.setAlignment(Alignment.CENTRE);
            WritableSheet sheet=null;
            SheetSettings settings=null;
            for (int i = 0; i < 1; i++) {
                sheet=workbook.createSheet("报价信息列表", i);
                settings=sheet.getSettings();
                settings.setVerticalFreeze(1);
                //添加第一行标题
                int o=0;
                for (Entry<Integer, Object> entrys : map.entrySet()) {
                    sheet.addCell(new Label(o,0,entrys.getValue()+"", wf));
                    o++;
                  }
                      if(maps.size()>0) {
                        int m=0;
                        int n=0;
                        int s=0;
                        for (Entry<Integer, Object> entry : maps.entrySet()) {
                            if (0 == s % map.size()){
                                n=0;//列
                                m++;//行
                            }
                            sheet.addCell(new Label(n++,m,entry.getValue()+"",wf));
                            s++;
                    }
                  }
            }//sheet添加结束
            workbook.write();//写入excel
            workbook.close();//关闭资源
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("表格写出失败!执行异常!");
        }
    }
}  

1.2导出的内容要求

@Controller
@RequestMapping(value = "/goods")
public class GoodsManageController extends BaseController {
@RequestMapping(value="/goodsListExport")
    public void goodsListExport(HttpServletResponse response,String goodsInFo, Integer orderBy,String sort,Long      category1Id,Long category2Id,Long brandId,Page page) {
        //1获取商品列表信息
        orderBy=orderBy==null?1:orderBy;
        String orderBys=orderBy+"";
        sort = sort == null ? "DESC" : sort;
        goodsInFo=Tools.isEmpty(goodsInFo)?null:goodsInFo;
        switch (orderBys) {//销量,库存,价格排序
        case "1":orderBys="goodsSku.real_sales";break;
        case "2":orderBys="goodsSku.goods_inventory";break;
        case "3":orderBys="goodsSku.group_price";break;}
        //根据自己需要 从数据库中获取自己需要的数据
        List<GoodsSkuWithGoodsSpu>  goodsSpusList=goodsSkuProxyService2.getGoodsSkuWithGoodsSpuByGoodsIdList(MapUtil.buildMap(
                "goodsName",goodsInFo,"category1Id",category1Id,"category2Id",category2Id,"brandId",brandId,"isDeleted", PojoConst.IS_DELETED_1,
                "orderBy",orderBys+" "+sort+" ,"+"goodsSpu.goods_id "+PojoConst.SORT_DESC+", goodsSpu.goods_state ,"+" goodsSku.update_time "+PojoConst.SORT_DESC,
                "limitIndex",page.getLimitIndex(),"index",page.getLimit()));
        //2设置第一行名称
        Map< Integer, Object> map=new TreeMap<Integer, Object>();
        Map< Integer, Object> maps=new TreeMap<Integer, Object>();
        map.put(0, "商品名称");
        map.put(1, "商品id");
        map.put(2, "商品销量");
        map.put(3, "商品库存");
        map.put(4, "商品价格");
        map.put(5, "属性分类");
        map.put(6, "上/下架");
        //3遍历数据内容
        @SuppressWarnings("unused")
        String goodsState;
        int i=0;
        int num=0;
        int pageCount=goodsSpusList.size();
        if(category1Id==null && category1Id==null && brandId==null) {
            pageCount=page.getLimit();
        }
        if(goodsSpusList.size()>0) {
            for (GoodsSkuWithGoodsSpu goodsSpusLists:goodsSpusList) {
                if(num < pageCount) {
                    maps.put(i, goodsSpusLists.getGoodsName());
                    maps.put(i+1, goodsSpusLists.getGoodsId());
                    maps.put(i+2, goodsSpusLists.getRealSales());
                    maps.put(i+3, goodsSpusLists.getGoodsInventory());
                    maps.put(i+4, goodsSpusLists.getOneselfPrice());
                    maps.put(i+5, goodsSpusLists.getGoodsSpecJson());
                    maps.put(i+6, goodsState=goodsSpusLists.getGoodsState()==GoodsConst.GOODS_STATE_2?"上架":"下架");
                    i+=map.size();
                    num++;
                }
        }
        }
        ExportExcelController exportExcelController=new ExportExcelController();
        exportExcelController.exportExcel(response, map, maps);
    }

}

2导入
2.1导入公共类

  public class ImportDBController {

    protected    Logger logger = LoggerFactory.getLogger(getClass());
    public List<Object>  importDB(String file,HttpServletResponse response,HttpServletRequest request) {
        List<Object> list=new ArrayList<Object>();
        try {
            //1获取文件
            Workbook book=Workbook.getWorkbook(new File(file));
            //2获得第一个工作表对象
            Sheet sheet=book.getSheet(0);
            //3得到所有的列,行
            int rows=sheet.getRows();
            int colums=sheet.getColumns();
            list.add(rows);
            list.add(colums);
            //4遍历数据
            for (int i = 1; i < rows; i++) {
                for (int j = 0; j < colums; j++) {
                    list.add(sheet.getCell(j,i).getContents());//得到数据并添加导list中
                }
            }
            book.close();
        }catch (Exception e) {
            e.printStackTrace();
            logger.error("表格写入失败!执行异常");
        }
        return list;
    }
}

2.2导入内容

@Controller
@RequestMapping(value="/order_xlsx")
public class OrderImportExport extends BaseController{
    @RequestMapping(value="/orderImport")
    public Result<Object> orderImport(String file,HttpServletResponse response,HttpServletRequest request) {
        Result<Object> result = new Result<Object>();
        List<Object> list=new ArrayList<Object>();//用于存放获取导入的信息
        List<OrderManage> orderManageList=new ArrayList<OrderManage>();//新增到数据库的list
        //1获取excal表中数据信息
        ImportDBController importDBController=new ImportDBController();
        list=importDBController.importDB(file, response, request);
        int rows=(int) list.get(0)-1;//得到行,并减去第一行数据(有标题-1,没有就不用-1)
        int colums=(int) list.get(1);//得到列
        int count=2;
        for (int i = 0; i <rows ; i++) {
            //根据订单编号查询订单id
            OrderManage orderManage=orderManageService.selectFirstOrderManage(MapUtil.buildMap("orderNo", list.get(count)));
//          orderManage.setOrderNo((list.get(count).toString()));//订单编号
            orderManage.setLogisticsName(list.get(count+1).toString());//物流公司
            orderManage.setLogisticsCode(list.get(count+2).toString());//运单号
            count+=colums;
            orderManageList.add(orderManage);
        }
        orderManageService.batchUpdateOrderManage(orderManageList);
        return result;
    }
}