exportexcel_stand.jsp

运输模块代码

<%@ page contentType="text/html; charset=GBK" %>
<%@ include file="/IncludeBegin.jsp" %>
<%@ page import="com.hexiesoft.are.sql.ASResultSet" %>
<%@ page import="com.hexiesoft.utils.Consts" %>
<%@ 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"%>
<%@ page import="java.text.DateFormat"%>
<%@ page import="java.text.SimpleDateFormat"%>
<%@ page import="java.util.Date"%>

<%
    /**

     @ author: Liangwei Yu
     @ date:   2006-5-2
     @ filename:ExportExcel.jsp
     */


    response.reset();
    response.setContentType("application/msexcel");
    response.setContentType("charset=GBK");
    response.setHeader("Content-disposition", "filename=StandReport.xls");//定义文件名
    //costs是用来生成standard costs报表的临时表，每次运行程序时要清空
 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.next()) {

    } 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_NAME");
           String SheetName = (String)Configure.getConfigure(sIe_Language,"PrintStandReport").get("EXCEL_EXPORT_SHEET_NAME");
           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中的一行
           HSSFRow row=null;
           HSSFCell cell=null;
           HSSFSheet sheet = book.createSheet(SheetName);
           int CurExcelRow = 0;

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

               //由基本信息再次从数据库中检索数据
              if(sAccount == null){
                   continue;
               }
               String sqlTmp = "select [account]+' '+[subacct] as account,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++);
                   for (int i = 0; i < title.length; i++) {
                       //在[0,i]位置创建一个单元格
                       cell = row.createCell((short) i);
                       //写数据到单元格
                       cell.setCellValue(title[i]);

                   }

               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
               } // while
               //写入统计的总数值
               row = sheet.createRow(CurExcelRow++);
               cell = row.createCell((short) 4);
               cell.setCellValue(titleTotal + String.valueOf(count));
           }
           //写入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++);
           CurExcelRow++;
           row = sheet.createRow(CurExcelRow);
           cell = row.createCell((short) 0);
           cell.setCellValue(titleCDate + date + titleCTime + time);



        book.write(response.getOutputStream());
        response.getOutputStream().flush();
        response.getOutputStream().close();

    }


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