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("Code"));
    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");
    int isimperial = 0;
    String c_blno = "", c_add1 = "", c_add2 = "", c_weight = "", c_shipname = "", c_wname = " ", d_shipdt = "", p_plant = "", c_geocode = "", custpo = "", worksno = "", desc = "", custprod = "", custspec = "", mt_gauge = "", mt_width = "";
    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' AS Expr1 ,cast(a.mt_width as varchar) +'mm' AS Expr2,GETDATE(),a.coils,a.gross,'',a.worksno,a.[desc],b.geocode,b.shname,b.isimperial 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()) {
        mt_gauge = packnoteRs.getString("Expr1");
        mt_width = packnoteRs.getString("Expr2");
        isimperial = packnoteRs.getInt("isimperial");
        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");
        //c_shipname =packnoteRs.getString("name");
        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()) {
        // c_add1     = shipment.getString("name")+" "+shipment.getString("add1")+" "+shipment.getString("add2");//shipment.getString("add1");
        //c_add2     = shipment.getString("add2");
        d_shipdt = shipment.getString("shipdt");
        if (shipment.getString("islbs").equals("1"))
            c_weight = "LBS";
        else
            c_weight = "KGS";
    }
    // (alltrim(CurPacknote.ordertype) = 'REG')

    if (uaccess == 1) {//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;
    }                                         //(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 + " **";
    if (packnoteRs == null || packnoteRs.getRowCount() == 0) {
        //检索不到记录，给出提示
%>
<script type="text/javascript">
    alert("No records satisfy your request!");
    sReturn_isEmpty = PopPage("/ShippingManager/PackingNote/printPackingNote/CheckCode.jsp?Code=" + sCode + "&rand=" + randomNumber(), "", "");

</script>

<%
    } else {

        //packnoteRs.next();
        //在内存中新建一个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) 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");
        /*
               FUNCTION mkldtl

       = changepage(1)
       ? SPACE(18) + CurPacknote.pnno + "  " +;
          IIF( CurPacknote.standwgt > 0 ,"S" + CurPacknote.standno, IIF( CurPacknote.ctrwgt > 0, "C" + CurPacknote.ctrno, IIF( CurPacknote.casewgt > 0, "W" + CurPacknote.caseno, "P" + CurPacknote.paltno )))+;
          " "+STR(CurPacknote.coils,4)+"  "+STR(CurPacknote.gross,7)+"  "+;
          IIF(uaccess='1',SPACE(7),STR(CurPacknote.tare,7))+"  "+STR(CurPacknote.net,7)+;
          IIF(uaccess='1',' ___','')
       wcoi   = wcoi   + CurPacknote.coils
       wtare  = wtare  + CurPacknote.tare
       wgross = wgross + CurPacknote.gross