exportexcel.jsp

运输模块代码

<%@ page contentType="text/html; charset=GBK" %>
<%@ include file="/IncludeBegin.jsp" %>

<%@ page import="com.hexiesoft.are.sql.ASResultSet" %>
<%@ page import="com.hexiesoft.are.util.DataConvert" %>
<%@ page import="com.hexiesoft.utils.Consts" %>
<%@ page import="com.hexiesoft.utils.StringUtils" %>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFCell" %>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFRow" %>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFSheet" %>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFWorkbook" %>

<%

    //检索的LOT Code
    String sCode = DataConvert.toRealString(iPostChange, (String) CurPage.getParameter("blno"));

    int uaccess = 0;
    //从 global 表检索
    String sql = "select * from global";
    //存储检索 packnote 表的结果
    ASResultSet rs = Sqlca.getASResultSet(sql);
    rs.next();
    String cISO = "QAF-068 Rev:1.0 Rev Date:97/Aug/7";
    String cName = rs.getString("cnconame");
    String cCity = rs.getString("cncity");
    String cProvst = rs.getString("cnprovst");
    String c_blno = "", c_add1 = "", c_add2 = "", c_weight = "", c_shipname = "", c_wname = " ", d_shipdt = "", p_plant = "", c_geocode = "", custpo = "", worksno = "", desc = "", custprod = "", custspec = "";
    String hline1 = "", hline1a = "", hline2 = "", hline3 = "", hline31 = "", hline4 = "", hline5 = "";
    sql = "SELECT a.blno,a.plant,a.ordertype,a.[name],a.add1 , a.add2,a.custpo,a.pnno,a.custprod ,a.custspec,a.caseno,rtrim(left(a.[desc],10)) , cast(a.mt_gauge as varchar) +'mm' ,cast(a.mt_width as varchar) +'mm',GETDATE(),a.coils,a.gross,'',a.worksno,a.[desc],b.geocode,b.shname from packnote a,customer b where blno ='" + sCode + "' AND a.account=b.account AND a.subacct=b.subacct";
    //检索 packnote
    ASResultSet packnoteRs = Sqlca.getASResultSet(sql);
    if (packnoteRs.next()) {
        c_blno = packnoteRs.getString("blno");
        c_add1 = packnoteRs.getString("add1");
        c_add2 = packnoteRs.getString("add2");
        p_plant = packnoteRs.getString("plant");
        c_geocode = packnoteRs.getString("geocode");
        custpo = packnoteRs.getString("custpo");
        worksno = packnoteRs.getString("worksno");
        desc = packnoteRs.getString("desc");

        if (packnoteRs.getString("ordertype").equals("REG")) {
            c_shipname = packnoteRs.getString("name");
        } else {
            sql = "select * from whouse where wcode='" + packnoteRs.getString("ordertype") + "'";
            rs = Sqlca.getASResultSet(sql);
            if (rs.next()) {
                c_shipname = packnoteRs.getString("shname");
                c_wname = "VIA: " + rs.getString("wname");
            }
        }
    }

    ASResultSet shipment = Sqlca.getASResultSet("select shipDt,Islbs,[name],add1,add2  from shipment where blno='" + sCode + "'");
    if (shipment.next()) {
        d_shipdt = shipment.getString("shipdt");
        if (shipment.getString("islbs").equals("1"))
            c_weight = "LBS";
        else
            c_weight = "KGS";
    }

    if (uaccess == 1) {
        hline1a = "  ";
        hline1 = cName + "        P I C K I N G    L I S T    DATE: " + d_shipdt;
        hline3 = "TO: " + c_shipname + "   Route:" + c_geocode + "    PICK LIST: " + c_blno;
    } else {
        hline1a = cISO;
        hline1 = cName + "        P A C K I N G    L I S T       DATE: " + d_shipdt;
        hline3 = "TO: " + c_shipname + "                         BOL: " + c_blno;
    }//end if(uaccess='1')
    if (p_plant.equals("FERG"))
        hline2 = "FERGUS, ONTARIO";
    else
        hline2 = "RICHMOND HILL, ONTARIO";


    hline31 = c_wname;
    hline4 = "    " + c_add1;
    hline5 = "    " + c_add2 + "   ** Weight in " + c_weight + " **";

    //在内存中新建一个Excel文件
    HSSFWorkbook book = new HSSFWorkbook();
    //在Excel文件中新建一个sheet
    String SheetName = "shippinglist";
    HSSFSheet sheet = book.createSheet(SheetName);
    //Excel中的一行
    HSSFRow row;
    HSSFCell cell;
    row = sheet.createRow(0);
    //设置编码格式
    cell = row.createCell((short) 0);
    cell.setEncoding((short) 1);
    cell.setCellValue(hline1a);
    row = sheet.createRow(1);
    cell = row.createCell((short) 0);
    cell.setEncoding((short) 1);
    //标题赋值
    cell.setCellValue(hline1);

    row = sheet.createRow(2);
    cell = row.createCell((short) 0);
    cell.setEncoding((short) 1);
    cell.setCellValue(hline2);

    row = sheet.createRow(3);
    cell = row.createCell((short) 0);
    cell.setEncoding((short) 1);
    cell.setCellValue(hline3);

    row = sheet.createRow(4);
    cell = row.createCell((short) 0);
    cell.setEncoding((short) 1);
    cell.setCellValue(hline31);


    row = sheet.createRow(5);
    cell = row.createCell((short) 0);
    cell.setEncoding((short) 1);
    cell.setCellValue(hline4);

    row = sheet.createRow(6);
    cell = row.createCell((short) 0);
    cell.setEncoding((short) 1);
    cell.setCellValue(hline5);

    row = sheet.createRow(7);
    cell = row.createCell((short) 0);
    cell.setEncoding((short) 1);
    cell.setCellValue("Cust PO:    " + custpo);

    row = sheet.createRow(8);
    cell = row.createCell((short) 0);
    cell.setEncoding((short) 1);
    cell.setCellValue("Works No:" + worksno + "        Desc:" + desc);
    row = sheet.createRow(9);
    cell = row.createCell((short) 0);
    cell.setEncoding((short) 1);
    cell.setCellValue("                     Part No:" + custprod + "              Spec:" + custspec);

    row = sheet.createRow(10);
    cell = row.createCell((short) 1);
    cell.setEncoding((short) 1);
    cell.setCellValue("Lot");
    cell = row.createCell((short) 2);
    cell.setEncoding((short) 1);
    cell.setCellValue("PkgNO");
    cell = row.createCell((short) 3);
    cell.setEncoding((short) 1);
    cell.setCellValue("Coils");
    cell = row.createCell((short) 4);
    cell.setEncoding((short) 1);
    cell.setCellValue("Gross");
    cell = row.createCell((short) 5);
    cell.setEncoding((short) 1);
    cell.setCellValue("Tare");
    cell = row.createCell((short) 6);
    cell.setEncoding((short) 1);
    cell.setCellValue("Net");

    int rowbegin = 11;
    String PkgNO = "";
    float tcoils = 0, tgross = 0, ttare = 0, tnet = 0;
    ASResultSet rsp = Sqlca.getASResultSet("select pnno, standwgt,standno, ctrwgt,ctrno,casewgt,caseno,paltno,coils,gross,tare,net from packnote where blno ='" + sCode + "'");
    while (rsp.next()) {
        row = sheet.createRow(rowbegin++);
        cell = row.createCell((short) 1);
        cell.setEncoding((short) 1);
        cell.setCellValue(rsp.getString("pnno"));
        cell = row.createCell((short) 2);
        cell.setEncoding((short) 1);

        PkgNO = "P" + rsp.getString("paltno");
        if (rsp.getFloat("standwgt") > 0)
            PkgNO = "S" + rsp.getString("standno");
        if (rsp.getFloat("ctrwgt") > 0)
            PkgNO = "C" + rsp.getString("ctrno");
        if (rsp.getFloat("casewgt") > 0)
            PkgNO = "W" + rsp.getString("caseno");

        cell.setCellValue(PkgNO);
        cell = row.createCell((short) 3);
        cell.setEncoding((short) 1);
        cell.setCellValue(rsp.getString("Coils"));
        cell = row.createCell((short) 4);
        cell.setEncoding((short) 1);
        cell.setCellValue(rsp.getString("Gross"));
        cell = row.createCell((short) 5);
        cell.setEncoding((short) 1);
        if (uaccess == 1) {
            cell.setCellValue("");
        } else {
            cell.setCellValue(rsp.getString("Tare"));
        }
        cell = row.createCell((short) 6);
        cell.setEncoding((short) 1);
        cell.setCellValue(rsp.getString("Net"));
        tcoils += rsp.getFloat("coils");
        tgross += rsp.getFloat("gross");
        ttare += rsp.getFloat("tare");
        tnet += rsp.getFloat("net");

    }
    //works no tot;
    row = sheet.createRow(rowbegin++);
    cell = row.createCell((short) 0);
    cell.setEncoding((short) 1);
    cell.setCellValue("Works NO Tot:");

    cell = row.createCell((short) 3);
    cell.setEncoding((short) 1);
    cell.setCellValue(tcoils);
    cell = row.createCell((short) 4);
    cell.setEncoding((short) 1);
    cell.setCellValue(tgross);
    cell = row.createCell((short) 5);
    cell.setEncoding((short) 1);
    cell.setCellValue(ttare);
    cell = row.createCell((short) 6);
    cell.setEncoding((short) 1);
    cell.setCellValue(tnet);

    row = sheet.createRow(rowbegin++);
    cell = row.createCell((short) 0);
    cell.setEncoding((short) 1);
    cell.setCellValue("Cust PO Tot:");

    cell = row.createCell((short) 3);
    cell.setEncoding((short) 1);
    cell.setCellValue(tcoils);
    cell = row.createCell((short) 4);
    cell.setEncoding((short) 1);
    cell.setCellValue(tgross);
    cell = row.createCell((short) 5);
    cell.setEncoding((short) 1);
    cell.setCellValue(ttare);
    cell = row.createCell((short) 6);
    cell.setEncoding((short) 1);
    cell.setCellValue(tnet);
    //Packing List tot;
    row = sheet.createRow(rowbegin++);
    cell = row.createCell((short) 0);
    cell.setEncoding((short) 1);
    if (uaccess == 1) {
        cell.setCellValue("Pick list Total:");
    } else {

    }
    cell.setCellValue("Packing List Total:");

    cell = row.createCell((short) 3);
    cell.setEncoding((short) 1);
    cell.setCellValue(tcoils);
    cell = row.createCell((short) 4);
    cell.setEncoding((short) 1);
    cell.setCellValue(tgross);
    cell = row.createCell((short) 5);
    cell.setEncoding((short) 1);
    cell.setCellValue(ttare);
    cell = row.createCell((short) 6);
    cell.setEncoding((short) 1);
    cell.setCellValue(tnet);

    response.setContentType("application/msexcel");
    response.setContentType("charset=GBK");

    String FileName = "shippinglist.xls";
    //定义Excel文件名
    response.setHeader("Content-disposition", "inline;filename=" + FileName);
    book.write(response.getOutputStream());
    response.getOutputStream().flush();
    response.getOutputStream().close();

%>
<%@ include file="/IncludeEnd.jsp" %>