报表格式Excel的导入，导出

╰+攻爆jí腚メ 2022-05-27 05:10 299阅读 0赞

一,导入

1，前台页面用type="file"接受要导入的文件，

例如：<input style="display: inline-block;max-width: 170px;border: 2px solid \#e6e6e6;" id="uploadFile" name="uploadFile"type="file"accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"/>;

传入后台用： fileElementId:$('input\[name=uploadFile\]')  
例如：$("\#doLeadingInGoods").click(function () \{  
var val= $("\#uploadFile").val();  
       var k = val.substr(val.indexOf("."));  
if(k=='.xlsx'||k=='.xls'||k=='.XLSX'||k=='.XLS')\{  
             ajaxLoading();  
    $.ajaxFileUpload(\{  
       url:"$\{base\}/weipu/mallgoods/saveGoods",  
       secureuri:true,//是否启用安全提交,默认为false     
       fileElementId:$('input\[name=uploadFile\]'),//文件选择框的对象,多个时用\[\]包含  
       dataType:'text',  
       async:true,  
       success:function(data, status)\{//服务器响应成功时的处理函数    
data = data.replace(/<pre.\*?>/g, '');  
data = data.replace(/<PRE.\*?>/g, '');    
data = data.replace("<PRE>", '');  
data = data.replace("</PRE>", '');    
data = data.replace("<pre>", '');    
data = data.replace("</pre>", '');  
data = jQuery.parseJSON(data);  
if(data.status=='success')\{  
     
\}else\{  
ajaxLoadEnd();  
layer.alert(data.message);  
\}   
       \},   
       error:function(data, status, e)\{ //服务器响应失败时的处理函数    
        data = data.replace(/<pre.\*?>/g, '');  
               data = data.replace(/<PRE.\*?>/g, '');    
               data = data.replace("<PRE>", '');  
               data = data.replace("</PRE>", '');    
               data = data.replace("<pre>", '');    
               data = data.replace("</pre>", '');  
          data = jQuery.parseJSON(data);  
               ajaxLoadEnd();  
        layer.alert(data.message);  
       \}  
 \});  
\}else\{  
layer.alert('上传文件格式有误！默认支持xls/xlsx');  
\}  
\});

2，后台用MultipartFile来接受前台传过来的文件，

例如：public Object saveGoods(@RequestParam(value="uploadFile") MultipartFile myfile, HttpServletRequest request) \{

MultipartHttpServletRequest multipartHttpservletRequest = (MultipartHttpServletRequest) request;

myfile = multipartHttpservletRequest.getFile("uploadFile");  
if (myfile != null) \{  
        uploadFilePath = saveAttachFile(myfile);//上传文件返回文件路径  
                \}  
                saveAttachFile()\{                          
                File logo = new File(newFilePath)；  
        multipartFile.transferTo(logo);上传文件  
                \}

//获取导入模板  
                String goodsTemplateXMLPath = getRealPath()+ TemplatePathInterface.leadingin\_goodsTemplateXML;  
                // 构建xml文件输入流  
                InputStream inputXML = new BufferedInputStream(new FileInputStream(goodsTemplateXMLPath));  
                // 绑定xml文件  
                XLSReader reader = ReaderBuilder.buildFromXML(inputXML);  
                // 构建文件输入流  
                InputStream inputXLS = new BufferedInputStream(new FileInputStream(uploadFilePath));//根据路径读取文件  
                List<Goods> goodses = new ArrayList<Goods>();  
                Map beans = new HashMap();  
                beans.put("goodses", goodses);  
                // 通过XSLReader 的read方法，它会自动映射pojo类，得到数据集合  
                reader.read(inputXLS, beans);  
                List<Goods> goodsList = new ArrayList<Goods>();  
                for (Goods progoods : goodses) \{

goods = new Goods();  
    goodsList.add(goods)；

// 批量保存商品信息goodsService.save(tenantId, goodsList);

\};

二,导出

1，前台页面

例如：//导出信息(导出选中的)  
$("\#exportList").click(function () \{  
window.location.href="$\{base\}/weipu/download/orderModeByTimeExport?data="+dataRows;  
\})  
//导出信息（导出所有的）  
$("\#exportList").click(function()\{  
   var property = $('\#property').val();   
   var keyword = $("\#keyword").val();  
   $.fileDownload('$\{base\}/weipu/download/goodsList?property='+property+'&keyword='  
    +keyword)  
   .done(function () \{  layer.alert('下载成功！'); \})  
   .fail(function () \{  layer.alert('下载失败！'); \});

\});

2.后台导出的方法:

@RequestMapping(value = "goodsList", method = \{ RequestMethod.GET \})  
@ResponseBody  
public void goodsList(HttpServletRequest request, HttpServletResponse response, Model model) \{  
    String tenantId = getCurrentUser().getTenantId();  
    String property =request.getParameter("property");  
    String keyword =request.getParameter("keyword");  
    String isMarketable = request.getParameter("isMarketable");  
    Criteria criteria = new Criteria();  
    if(StringUtils.isNotEmpty(property)&&StringUtils.isNotEmpty(keyword))\{  
    if(property.equals("brand"))\{  
    criteria.add(Restrictions.like("b.name", "%"+keyword+"%"));  
    \}else if(property.equals("tag"))\{  
    criteria.add(Restrictions.like("t.name", "%"+keyword+"%"));  
    \}else if(property.equals("category"))\{  
    criteria.add(Restrictions.like("c.name", "%"+keyword+"%"));  
    \}else\{  
    criteria.add(Restrictions.like("g."+property, "%"+keyword+"%"));  
    \}  
    \}  
    if (isMarketable != null && !"".equals(isMarketable)) \{  
criteria.add(Restrictions.eq("isMarketable", isMarketable));  
    \}   
    criteria.add(Restrictions.group("g.id"));  
    List<Goods> list=new ArrayList<Goods>();  
    try \{  
list = goodsService.goodsListExport(tenantId, criteria);  
    \} catch (Exception e1) \{  
    e1.printStackTrace();  
    \}  
    /\*\*\*\*\*list查询结束\*\*\*\*\*\*/  
    String fileExtName = "xls";  
    String time = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss").format(new Date()).toString();  
    String newFileName = time + String.format("%04d", new Random().nextInt(1000)) + "." + fileExtName;  
    String downDir = request.getServletContext().getRealPath("/") + "/down";// WEB-INF  
    String downLoadFilePath = downDir + File.separator + newFileName;  
    File downLoadFile = new File(downLoadFilePath);  
    if (!downLoadFile.getParentFile().exists()) \{  
    downLoadFile.mkdirs();  
    \}  
    // 创建工作表与sheet的引用  
    WritableWorkbook workbook = null;  
    try \{  
if (!downLoadFile.exists()) \{  
downLoadFile.createNewFile();  
\}  
    OutputStream os = new FileOutputStream(downLoadFile);  
    workbook = Workbook.createWorkbook(os);  
    // 创建第一页的sheet  
    WritableSheet sheet1 = workbook.createSheet("商品详情表", 0); // 可添加第一个工作  
    // 文件第一行写入Title  
            String titleContent = "序号\#商品编号\#条形码\#全名称\#销售价\#品牌\#分销佣金比例\#库存\#商品分类\#是否上架\#商品描述";  
    String\[\] title = titleContent.split("\#");  
    for (int i = 0; i < title.length; i++) \{  
// Label(列号,行号 ,内容 )  
Label label = new jxl.write.Label(i, 0, title\[i\]);  
sheet1.addCell(label);  
    \}  
  
    int j = 1;  
    if(list.size()>0)\{  
for (Goods map : list) \{  
Label label0 = new jxl.write.Label(0, j, String.valueOf(j));  
sheet1.addCell(label0);  
Label label1 = new jxl.write.Label(1, j, map.getSn());  
sheet1.addCell(label1);  
Label label2 = new jxl.write.Label(2, j, map.getBarcode());  
sheet1.addCell(label2);  
Label label3 = new jxl.write.Label(3, j, map.getFullName());  
sheet1.addCell(label3);  
Label label4 = new jxl.write.Label(4, j, String.valueOf(map.getPrice()));  
sheet1.addCell(label4);  
Label label5 = new jxl.write.Label(5, j, map.getBrandId());  
sheet1.addCell(label5);  
if(null!=map.getPoint())\{  
Double point=map.getPoint()\*100;  
Label label6 = new jxl.write.Label(6, j, point+"%");  
sheet1.addCell(label6);  
\}else\{  
Label label6 = new jxl.write.Label(6, j, "0%");  
sheet1.addCell(label6);  
\}  
Label label7 = new jxl.write.Label(7, j, String.valueOf(map.getStock()));  
sheet1.addCell(label7);  
Label label8 = new jxl.write.Label(8, j, map.getCategoryPath());  
sheet1.addCell(label8);  
Label label9 = new jxl.write.Label(9, j, isMarketableMap.get(String.valueOf(map.getIsMarketable())));  
sheet1.addCell(label9);  
Label label10= new jxl.write.Label(10, j, map.getDescription());  
sheet1.addCell(label10);  
j++;//换行  
\}  
            \}  
    // \*\*\*\*读取数据，写入文件\*\*\*\*//  
    workbook.write();// 输出到文件  
    \} catch (FileNotFoundException ex) \{  
logger.error("数据写入文件时，发生异常：", ex);  
    \} catch (IOException ex) \{  
logger.error("数据写入文件时，发生异常：", ex);  
    \} catch (WriteException ex) \{  
logger.error("数据写入文件时，发生异常：", ex);  
    \} catch (Exception ex) \{  
     logger.error("数据写入文件时，发生异常：", ex);  
    \} finally \{  
if (workbook != null) \{  
try \{  
workbook.close();  
\} catch (Exception e) \{  
logger.error("关闭文件写入流时，发生异常：", e);  
\}  
\}  
    \}  
    try \{  
String contentType = "application/octet-stream";  
String storeName = "商品管理" + newFileName;  
String realName = newFileName;  
if (downLoadFile.exists()) \{  
FileOperateUtil.downloadex(request, response, downLoadFilePath, contentType, storeName,                                 realName);  
\}  
    \} catch (Exception e) \{  
logger.error("导出报表时，发生异常：", e);  
    \}

public static void downloadex(HttpServletRequest request,  
HttpServletResponse response,String downLoadFilePath,String contentType, String storeName,   
String realName) throws Exception \{  
response.setContentType("text/html;charset=UTF-8");  
request.setCharacterEncoding("UTF-8");  
BufferedInputStream bis = null;  
BufferedOutputStream bos = null;  
  
long fileLength = new File(downLoadFilePath).length();  
  
  
response.setContentType(contentType);  
response.setHeader("Content-disposition", "attachment; filename="  
\+ new String(realName.getBytes("utf-8"), "ISO8859-1"));  
response.setHeader("Content-Length", String.valueOf(fileLength));  
  
bis = new BufferedInputStream(new FileInputStream(downLoadFilePath));  
bos = new BufferedOutputStream(response.getOutputStream());  
byte\[\] buff = new byte\[2048\];  
int bytesRead;  
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) \{  
bos.write(buff, 0, bytesRead);  
\}  
bis.close();  
bos.close();

解析模板

<?xml version="1.0" encoding="UTF-8"?>  
<workbook>  
<worksheet name="Sheet1">  
     
<section startRow="0" endRow="4" />  
  
  
<loop startRow="5" endRow="5" items="goodses" var="goods"  
varType="net.viservice.entity.weipu.Goods">  
<section startRow="5" endRow="5">  
  
<mapping cell="A6">goods.categoryId</mapping>  
  
<mapping cell="B6">goods.barcode</mapping>  
  
<mapping cell="C6">goods.name</mapping>  
  
<mapping cell="D6">goods.sn</mapping>  
  
<mapping cell="E6">goods.cost</mapping>  
  
<mapping cell="F6">goods.brandId</mapping>  
  
<mapping cell="G6">goods.unit</mapping>  
  
<mapping cell="H6">goods.weight</mapping>  
  
<mapping cell="I6">goods.keywords</mapping>  
  
<mapping cell="J6">goods.price</mapping>  
  
<mapping cell="K6">goods.marketPrice</mapping>  
  
<mapping cell="L6">goods.stock</mapping>  
  
<mapping cell="M6">goods.isMarketable</mapping>  
  
<mapping cell="N6">goods.isTop</mapping>  
  
<mapping cell="O6">goods.isIndex</mapping>  
  
<mapping cell="P6">goods.isGlobal</mapping>  
  
<mapping cell="Q6">goods.isWx</mapping>  
  
<mapping cell="R6">goods.introduction</mapping>  
</section>  
<loopbreakcondition>  
<rowcheck offset="0">  
<cellcheck offset="0" />  
</rowcheck>  
  
</loopbreakcondition>  
</loop>  
</worksheet>  
</workbook>