2018-12-30
今天根据客户要求做了个导出功能，客户选择订单，导出的是该订单的产品明细
注意：在逗号拼接字符串的时候我老是忘了截取最后一个逗号
function exportproduct(){
var checkStatus = table.checkStatus('orderList');
if(checkStatus.data.length > 0){
var ids ="";
for(var i in checkStatus.data){
ids+=(checkStatus.data[i].id + ",");
}
ids = ids.substr(0, ids.length - 1);
var url='${pageContext.request.contextPath}/order/exportProduct.action?ids='+ids;
$("#downloadFrame").attr("src",url);
}else{
layer.open({
icon: 2,
title: '操作失败',
content: '请先勾选要导出的订单'
});
}
}
public void exportProduct(String ids, HttpServletRequest request, HttpServletResponse response) throws Exception {
List<V_CGDDMX> list = orderDao.getCgddmx(ids);
String fileName = "采购订单明细表";
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
Map<String, Object> map = new HashMap<String, Object>();
map.put("sheetName", "sheet1");
listMap.add(map);
try {
for (V_CGDDMX obj : list) {
map = new HashMap<String, Object>();
map.put("ddh", obj.getDdh());
if(obj.getPtbz().equals("1")){
map.put("ptbz", "平台");
}else if(obj.getPtbz().equals("0")){
map.put("ptbz", "非平台");
}else{
map.put("ptbz", "无合同");
}
map.put("cjsj", obj.getCjsj());
map.put("ksm", obj.getKsm());
map.put("cpmc", obj.getCpmc());
map.put("cpmbm", obj.getCpmbm());
map.put("ghszbm", obj.getGhszbm());
map.put("gg", obj.getGg());
map.put("cjmc", obj.getCjmc());
map.put("dgsl", obj.getDgsl());
DecimalFormat df = new DecimalFormat("#0.0000");
DecimalFormat dg = new DecimalFormat("#0.00");
Double data = obj.getCgdj();
map.put("cgdj", df.format(data));
Double hj= obj.getDgsl()*obj.getCgdj();
map.put("hj", dg.format(hj));
map.put("ghsmc", obj.getGhsmc());
listMap.add(map);
}
String columnNames[] = { "订单号", "采购方式", "分院","供应商名称","创建时间", "产品名称", "产品编码", "供方货号", "规格", "厂家名称", "订购数", "单价","合计" };// 列名
String keys[] = { "ddh", "ptbz", "ksm","ghsmc","cjsj", "cpmc", "cpmbm", "ghszbm", "gg", "cjmc", "dgsl", "cgdj","hj" };// map中的key
ByteArrayOutputStream os = new ByteArrayOutputStream();
ExcelUtil.createWorkBook(listMap, keys, columnNames).write(os);
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数，可以打开下载页面
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xls").getBytes("GBK"), "iso-8859-1"));
ServletOutputStream out = response.getOutputStream();
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (bis != null)
try {
bis.close();
} catch (IOException e) {
e.printStackTrace();
}
if (bos != null)
try {
bos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
<select id="getCgddmx" resultMap="V_CGDDMX3" parameterClass="String">
SELECT <include refid="base_columns" />,
cp.cpmc as CPMC,
cp.cpmbm as CPMBM,
decode(cgdd.ptbz, 0, dx.JGMC, ghs.GHSMC) as GHSMC,
cp.gg as GG,
cgdd.ddh as DDH,
cgdd.ptbz as PTBZ,
cpdzbm.GHSZBM GHSZBM,
cgdd.ddzt as DDZT,
ks.ksm as KSM,
ghs.ghsmc as GHSMC,
nvl (cj.CJJC, cj.CJMC) AS CJMC
FROM t_cgddmx t
LEFT JOIN t_cgdd cgdd ON cgdd. ID = t .ddid
LEFT JOIN T_GHS ghs on ghs.ID = cgdd.GHFID and cgdd.PTBZ != 0
left join T_YWDX dx on dx.ID = cgdd.GHFID and cgdd.PTBZ=0
LEFT JOIN t_cp cp ON cp. ID = t .cpid
LEFT JOIN T_YLJGKS ks ON ks. ID = cgdd.KSID
LEFT JOIN T_GHSCPDZ cpdzbm ON cpdzbm.CPID = t .cpid
AND cpdzbm.GHSID = cgdd.ghfid
AND cpdzbm.YXBZ = 1
LEFT JOIN T_SCCJ cj ON cj. ID = cp.SCQYID
AND cj.YXBZ = 1
where t.ddid in ($ids$)
</select>
