java生成excel存放到web-inf

来源：互联网发布：哪个网络好用编辑：程序博客网时间：2024/06/05 10:27
package cn.gov.customs.xls;import java.io.File;import java.io.FileOutputStream;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Calendar;import java.util.Date;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFPalette;import org.apache.poi.hssf.usermodel.HSSFRichTextString;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.hssf.util.Region;import cn.gov.customs.CadpSession;import cn.gov.customs.report.bizview.V_BIZQUERYLS;import cn.gov.customs.report.bizview.V_BIZQUERYZS;import cn.gov.customs.toolkits.ToolKit;import com.eos.das.entity.criteria.CriteriaType;import com.eos.das.entity.criteria.ExprType;import com.eos.data.datacontext.DataContextManager;import com.eos.data.datacontext.IMUODataContext;import com.eos.foundation.PageCond;import com.eos.foundation.common.utils.FileUtil;import com.eos.foundation.database.DatabaseExt;import com.eos.spring.DASDaoSupport;import com.eos.spring.TraceLogger;import com.eos.system.exception.EOSException;import commonj.sdo.DataObject;public class XlsService extends DASDaoSupport implements Ixls {    TraceLogger logger = new TraceLogger("");    @Override    public String exportoffline(CriteriaType criteriaType) {        String headtitle = "备案管理查询";        int datalength = 0;        int szc = 0;        int ssx = 0;        int szx = 0;        // 关区名称 1列 ， 类别 12*3列 合计 1*3列        String[] namerow1 = { "关区名称", "类别", "合计" };        String[] namerow2 = { "码头类监管场所", "公路转关监管点", "陆路边境口岸监管场所", "货栈类监管场所", "堆场类监管场所", "仓库类监管场所", "储罐类监管场所", "快件类监管场所", "边民互市贸易类监管场所", "台轮停泊点类监管场所", "旅客通关类监管场所", "国际邮件类监管场所" };        String[] namerow3 = { "在运营", "《注册登记证书》已过期", "已注销" };        String[] namerow2key = { "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12" };        String[] namekeylx = { "zc", "sx", "zx" };        String[] namekeyhj = { "szc", "ssx", "szx" };        int datarow = 6;        HSSFWorkbook wb = new HSSFWorkbook();        HSSFSheet sheet = wb.createSheet(headtitle);        sheet.setDefaultColumnWidth((short) 18);        HSSFRow row = sheet.createRow(0);        HSSFRow row5 = sheet.createRow(5);        sheet.setColumnWidth((short) 0, (short) 4600);// 设置列宽        HSSFCellStyle headerStyle = wb.createCellStyle();        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中        headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直        HSSFFont headerFont = wb.createFont(); // 创建字体样式        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗        headerFont.setFontName("Times New Roman"); // 设置字体类型        headerFont.setColor(HSSFFont.COLOR_RED);        headerFont.setFontHeightInPoints((short) 18); // 设置字体大小        headerStyle.setFont(headerFont); // 为标题样式设置字体样式        HSSFCellStyle midStyle = wb.createCellStyle();        midStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式        midStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);        midStyle.setBottomBorderColor(HSSFColor.BLACK.index);        midStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);        midStyle.setLeftBorderColor(HSSFColor.BLACK.index);        midStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);        midStyle.setRightBorderColor(HSSFColor.BLACK.index);        midStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);        midStyle.setTopBorderColor(HSSFColor.BLACK.index);        HSSFFont midFont = wb.createFont(); // 创建字体样式        midFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗        HSSFPalette customPalette = wb.getCustomPalette();        customPalette.setColorAtIndex((short) 9, (byte) (192), (byte) (192), (byte) (192));        midFont.setColor((short) 9);        midFont.setFontName("Times New Roman"); // 设置字体类型        midFont.setFontHeightInPoints((short) 13); // 设置字体大小        midStyle.setFont(midFont); // 为标题样式设置字体样式        midStyle.setFillBackgroundColor(HSSFColor.OLIVE_GREEN.index);        HSSFCellStyle row2style = wb.createCellStyle();        row2style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框        row2style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框        row2style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框        row2style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框        row2style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中        row2style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中         HSSFCell celltmp = null;           for (int i = 3; i < 6; i++) {                row = sheet.createRow(i);                for (int j = 1; j < 41; j++) {                    celltmp = row.createCell((short)j);                    celltmp.setCellStyle(row2style);                }            }  //      Region region1ROW = new Region((short) 0, (short) 1, (short) 3, (short) (namerow2.length * 3 + 4));// 第0行,        // sheet.addMergedRegion(region1ROW); 目前是不要        // 得到所有区域        sheet.getNumMergedRegions();        HSSFRow row3 = sheet.createRow(3);// 关区 ， 分类 ， 合计        HSSFRow row4 = sheet.createRow(4);// 12分类所在行        HSSFCell celllgqnm = row3.createCell((short) 1);        celllgqnm.setCellValue(new HSSFRichTextString(namerow1[0]));        celllgqnm.setCellStyle(row2style);        HSSFCell celllb = null;        HSSFCell cellzt = null;        HSSFCell gqnmcell = row3.createCell((short) 1);        HSSFCell hjcell = row3.createCell((short) 1);        Region gqnmrgn = new Region((short) 3, (short) (1), (short) 5, (short) (1));//        sheet.addMergedRegion(gqnmrgn);        gqnmcell = row3.createCell((short) (1));        gqnmcell.setCellValue(new HSSFRichTextString(namerow1[0]));        gqnmcell.setCellStyle(row2style);        Region flrgn = new Region((short) 3, (short) (2), (short) 3, (short) (37));//        sheet.addMergedRegion(flrgn);        HSSFCell flcell = row3.createCell((short) 2);        flcell = row3.createCell((short) (2));        flcell.setCellValue(new HSSFRichTextString(namerow1[1]));        flcell.setCellStyle(row2style);        Region hjrgn = new Region((short) 3, (short) (38), (short) 4, (short) (40));//        sheet.addMergedRegion(hjrgn);        hjcell = row3.createCell((short) (38));        hjcell.setCellValue(new HSSFRichTextString(namerow1[2]));        hjcell.setCellStyle(row2style);        for (int i = 0; i < namerow2.length; i++) {            Region everybizrgn = new Region((short) 4, (short) (2 + (i) * 3), (short) 4, (short) ((i + 1) * 3 + 1));//            sheet.addMergedRegion(everybizrgn);            if (i == namerow2.length - 1) {                for (int j = 0; j < namerow3.length; j++) {                    cellzt = row5.createCell((short) (38 + j));// 类别                    cellzt.setCellValue(new HSSFRichTextString(namerow3[j]));                    cellzt.setCellStyle(row2style);                }            }        }        for (int i = 0; i < namerow2.length; i++) {            celllb = row4.createCell((short) (2 + i * 3));// 类别            celllb.setCellValue(new HSSFRichTextString(namerow2[i]));            celllb.setCellStyle(row2style);            for (int j = 0; j < namerow3.length; j++) {                cellzt = row5.createCell((short) (2 + i * 3 + j));// 类别                cellzt.setCellValue(new HSSFRichTextString(namerow3[j]));                cellzt.setCellStyle(row2style);            }        }        // 第五步，写入实体数据 实际应用中这些数据从数据库得到，        try {            PageCond pg = PageCond.FACTORY.create();            pg.setLength(Integer.MAX_VALUE);            commonj.sdo.DataObject[] cos = DatabaseExt.queryEntitiesByCriteriaEntityWithPage("default", this.getCriteriaType(criteriaType), pg);            if ( !ToolKit.isEmpty(cos)) {                datalength = cos.length;                for (int i = datarow; i < cos.length + datarow; i++) {                    HSSFRow rowdata = sheet.createRow(datarow);                    HSSFCell celldata = null;                    DataObject stu = cos[i - datarow];                    // 第四步，创建单元格，并设置值                    for (int j = 0; j < namerow2key.length; j++) {                        String string = null;                        String key = "a";                        for (int k = 0; k < namekeylx.length; k++) {                            string = stu.getString(key + namerow2key[j] + namekeylx[k]);                            celldata = rowdata.createCell((short) (j * 3 + 2 + k));// 第2列                            if (Integer.parseInt(string) == 0) {                                celldata.setCellValue(new HSSFRichTextString("0"));                            }                            else {                                celldata.setCellValue(new HSSFRichTextString(string));                            }                            celldata.setCellStyle(row2style);                            if (namekeylx[k].equals("zc")) {                                szc = szc + Integer.parseInt(string);                            }                            if (namekeylx[k].equals("sx")) {                                ssx = ssx + Integer.parseInt(string);                            }                            if (namekeylx[k].equals("zx")) {                                szx = szx + Integer.parseInt(string);                            }                        }                    }                    for (int l = 0; l < namekeyhj.length; l++) {                        celldata = rowdata.createCell((short) (12 * 3 + 2 + l));// 第2列                        celldata.setCellStyle(row2style);                        String s = stu.getString(namekeyhj[l]);                        celldata.setCellValue(new  HSSFRichTextString(s));                    }                    HSSFCell cellgqnm = rowdata.createCell((short) (1));                    cellgqnm.setCellStyle(row2style);                    String s = stu.getString("nm");                    cellgqnm.setCellValue(new  HSSFRichTextString(s));                }            //合计行                   HSSFRow rowdata = sheet.createRow(6+datalength);                    HSSFCell cellhj = rowdata.createCell((short) (12 * 3 + 2 + 0));                cellhj.setCellStyle(row2style);                cellhj.setCellValue(szc);                cellhj = rowdata.createCell((short) (12 * 3 + 2 + 1));                cellhj.setCellValue(ssx);                cellhj.setCellStyle(row2style);                cellhj = rowdata.createCell((short) (12 * 3 + 2 + 2));                cellhj.setCellValue(szx);                cellhj.setCellStyle(row2style);            }        }        catch (Exception e1) {            logger.info(e1.getMessage());        }        // //第一个明细 END        String d = "";        // 第六步，将文件存到指定位置        try {            String rpath="";            ClassLoader cldr=getClass().getClassLoader();            if(null!=cldr){                rpath = cldr.getResource("/").getPath();            }else{                rpath="###";            }            String os = System.getProperty("os.name");            if (os != null && os.startsWith("Windows")) {                if (rpath.startsWith("/")) {                    rpath = rpath.substring(1, rpath.length());                }            }            if (rpath.contains("WEB-INF")) {                rpath = rpath.substring(0, rpath.indexOf("WEB-INF")) + "upload";            }            String rpathfinal = rpath + File.separator;            Long nowl = System.currentTimeMillis();            Date nowd = new Date(nowl);            Date cread = nowd;            Calendar calendar = Calendar.getInstance();            calendar.setTime(nowd);            calendar.add(Calendar.MINUTE, -5);// 24小时制            nowd = calendar.getTime();            File olddir = new File(rpathfinal);            if (olddir.exists()) {                if (olddir.isDirectory()) {                    File[] files = olddir.listFiles();                    for (File ff : files) {                        Long time = ff.lastModified();                        cread = new Date(time);                        if (nowd.before(cread)) {                            if (ff.isFile()) {                                FileUtil.deleteFile(ff.getAbsolutePath());                            }                            else {                                FileUtil.deleteDir(ff.getAbsolutePath());                            }                        }                    }                }            }            File f = new File(rpathfinal);            if ( !f.exists() && !f.isDirectory()) {            boolean bln_mk= f.mkdir();            if(!bln_mk){                logger.info("创建失败");            }            }            d = new SimpleDateFormat("yyyyMMddhhmmss").format(new Date());            String filepath = rpathfinal + headtitle + "(" + d + ").xls";            File filetemp = new File(filepath);            if (filetemp.exists()) {                filetemp.delete();            }            FileOutputStream fout = new FileOutputStream(filepath);            wb.write(fout);            fout.close();        }        catch (Exception e) {            TraceLogger logger = new TraceLogger("");            logger.info(e.getMessage());        }        String fileurl = headtitle + "(" + d + ").xls";        return fileurl;    }    private DataObject getCriteriaType(CriteriaType ct) {        IMUODataContext muo = DataContextManager.current().getMUODataContext();        Object obj = muo.get("cufs");        CadpSession cufs = new CadpSession();        if (null == obj) {            try {                throw new EOSException("本次会话已经失效，请重新登录！");            }            catch (EOSException e) {                logger.info(e.getException());;            }        }        else {            cufs = (CadpSession) obj;            if (null == cufs.getCustomcode()) {                try {                    throw new EOSException("本次会话已经失效，请重新登录！");                }                catch (EOSException e) {                    logger.info(e.getException());;                }            }        }        String cusmcode_query = cufs.getCustomcode();        if (cusmcode_query.endsWith("00")) {            // 如果关区代码是 00结尾 ， 用 V_BIZQUERYZS            ct.set_entity(V_BIZQUERYZS.QNAME);        }        else {            // 如果关区代码不是00结尾 ，用 V_BIZQUERYLS            ct.set_entity(V_BIZQUERYLS.QNAME);        }        List<ExprType> exprlist = ct.get_expr();        if (ToolKit.isEmpty(exprlist)) {            exprlist = new ArrayList<ExprType>();        }        else {            if ( !ToolKit.isEmpty(exprlist.get(1))) {                if ( !ToolKit.isEmpty(exprlist.get(1).get("lshg"))) {                    ct.set_entity(V_BIZQUERYLS.QNAME);                }            }        }        ExprType expr_cusm_code = ExprType.FACTORY.create();        expr_cusm_code.set_property("lshg");        if (cusmcode_query.endsWith("00")) {            expr_cusm_code.set_value(cusmcode_query.substring(0, 2));        }        else {            expr_cusm_code.set_value(cusmcode_query);        }        expr_cusm_code.set_op("like");        expr_cusm_code.set_likeRule("end");        if (cufs.getCustomcode().equals("0000")) {        }        else {            exprlist.add(expr_cusm_code);        }        ct.set_expr(exprlist);        return ct;    }}
阅读全文
0 0