exportexcel.jsp

运输模块代码

<%--
  Created by IntelliJ IDEA.
  User: cqin
  Date: 2006-5-29
  Time: 16:27:31
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html; charset=GBK" %>
<%@ include file="/IncludeBegin.jsp" %>
<%@ page import="java.util.Date" %>
<%@ page import="java.text.SimpleDateFormat" %>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFSheet" %>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFWorkbook" %>
<%@ page import="javax.servlet.ServletOutputStream" %>
<%@ page import="com.hexiesoft.utils.StringUtils"%>
<%@ page import="com.hexiesoft.utils.Consts"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFCell"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFRow"%>
<%@ page import="java.io.FileOutputStream"%>

<%
    String error1 = (String) Configure.getConfigure(sIe_Language,"PrintStandReport").get("ERROR_NO_STAND_RECORD");
    //从 stands 表检索
    String sql = "select * from stands";

    //存储检索 stands 表的结果
    ASResultSet standsRs;
    ASResultSet standsCtRs;
    //检索 stands
    standsRs = Sqlca.getASResultSet(sql);
    if (standsRs == null || standsRs.getRowCount() == 0) {
        //检索不到库存记录，给出提示
%>
<script type="text/javascript">
    alert("<%= error1%>");
</script>

<%
    } else {
        //得到基本数据列
        String sql1 = "select count(*) as icount,rtrim(account) as account,rtrim(subacct) as subacct from stands group by account,subacct order by account,subacct";
        standsCtRs = Sqlca.getASResultSet(sql1);
        //从语言包中得到基本参数
        String filename = (String)Configure.getConfigure(sIe_Language,"PrintStandReport").get("EXCEL_EXPORT_FILE_BASENAME");
        String titleTotal = (String)Configure.getConfigure(sIe_Language,"PrintStandReport").get("EXCEL_EXPORT_TITLE_TOTAL");
        String titleCDate = (String)Configure.getConfigure(sIe_Language,"PrintStandReport").get("EXCEL_EXPORT_TITLE_CDATE");
        String titleCTime = (String)Configure.getConfigure(sIe_Language,"PrintStandReport").get("EXCEL_EXPORT_TITLE_CTIME");
        String DateFmt =  (String)Configure.getConfigure(sIe_Language,"PrintStandReport").get("EXCEL_EXPORT_DATE_FORMAT");
        //一些临时参数
        String sAccount = "";
        String sSubacct = "";
        int count = 0;

        //基本Excel参数
        //在内存中新建一个Excel文件
        HSSFWorkbook book = new HSSFWorkbook();
        //在Excel文件中新建一个sheet
        String SheetName = "stands";
        //Excel中的一行
        HSSFRow row;
        HSSFCell cell;
        HSSFSheet sheet = book.createSheet(SheetName);
        int CurExcelRow = 0;

        //在原表中进行检索打印
        while(standsCtRs.next()){
            if(CurExcelRow != 0){
                row = sheet.createRow(CurExcelRow);
                CurExcelRow ++;
            }
            //得到按照Account和SubAccount排序和聚合后的基本信息
            count = standsCtRs.getInt("icount");
            sAccount = standsCtRs.getString("account");
            sSubacct = standsCtRs.getString("subacct");

            //由基本信息再次从数据库中检索数据
           if(sAccount == null){
                continue;
            }
            String sqlTmp = "select account,subacct,blno,worksno,CONVERT(nvarchar,shipdt,120) as shipdt,standno from stands where rtrim(account)='" + sAccount +"' and rtrim(subacct)='" + sSubacct +"' order by account,subacct,blno,worksno";
            ASResultSet standRsTmp = Sqlca.getASResultSet(sqlTmp);

            //获取报表的列数目，定义在Consts中
            int ColNum = Consts.REPORTER_STANDS_COLNUM;

            //从语言配置文件获取读取相应参数。
            String title[] = new String[ColNum];
            for (int i = 0; i < title.length; i++) {
                //报表的标题，在Report_EN,Report_ZH_CN中
                title[i] = (String) Configure.getConfigure(sIe_Language, "PrintStandReport").get("EXCEL_HEADLINE_TITLE" + (i + 1));
            }

            //创建标题行
            row = sheet.createRow(CurExcelRow);
            if (title != null) {
                for (int i = 0; i < title.length; i++) {
                    //在[0,i]位置创建一个单元格
                    cell = row.createCell((short) i);
                    //写数据到单元格
                    cell.setCellValue(title[i]);

                }
                CurExcelRow ++;
            } else {
//              log.debug("出错了，无法读取Excel标题。");
            }

            while (standRsTmp != null && standRsTmp.next()) {
                row = sheet.createRow(CurExcelRow);
                for (int col = 0; col < ColNum; col++) {
                    cell = row.createCell((short) col);
                    switch (Consts.REPORTER_STANDS_COL_TYPE[col]) {
                    //根据每个字段的类型使用不同的方法获取数据
                    //每个字段对应的类型在Consts中定义了数组
                        case Consts.DB_COL_TYPE_STRING:
                            cell.setCellValue(standRsTmp.getString(col + 1));
                            break;
                        case Consts.DB_COL_TYPE_INT:
                            cell.setCellType(standRsTmp.getInt(col + 1));
                            break;
                        case Consts.DB_COL_TYPE_DATE:
                            cell.setCellValue(standRsTmp.getStringValue(col + 1));
                            break;
                        case Consts.DB_COL_TYPE_FLOAT:
                            cell.setCellValue(standRsTmp.getFloat(col + 1));
                            break;
                        case Consts.DB_COL_TYPE_DOUBLE:
                            cell.setCellValue(standRsTmp.getDouble(col + 1));
                            break;
                        default:
                            cell.setCellValue(standRsTmp.getString(col + 1));
                    }
                } //for col
                CurExcelRow ++;
            } // while
            //写入统计的总数值
            row = sheet.createRow(CurExcelRow);
            cell = row.createCell((short) 5);
            cell.setCellValue(titleTotal + String.valueOf(count));
            CurExcelRow ++;
        }
        //写入Excel创建日期和时间
        Date now = new Date();
        SimpleDateFormat formater = new SimpleDateFormat("yyyy/MM/dd");
        String date = formater.format(now);
        String time = DateFormat.getTimeInstance().format(now);
        row = sheet.createRow(CurExcelRow++);
        row = sheet.createRow(CurExcelRow);
        cell = row.createCell((short) 0);
        cell.setCellValue(titleCDate + date + titleCTime + time);

        response.reset();
        response.setContentType("application/msexcel");
        response.setContentType("charset=GBK");
        response.setHeader("Content-disposition", "filename=stands.xls");//定义文件名
        book.write(response.getOutputStream());
        response.getOutputStream().flush();
        response.getOutputStream().close();
    }
%>


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