Terminal信息导出到Excel模板--【sql+entity+dao丶Service+Controller】
来源：互联网 发布：智人 美洲 知乎 编辑：程序博客网 时间：2024/05/19 05:04
EdcterminalDao.xml当中写的是sql语句，这部分需要手写。id写的是方法名，也就是在dao，service中即将调用的方法名，resultType中写的是方法的返回值类型。接下来是要查询的字段，这些字段都要用AS与其所创建的entity相对应，下来就是以变更记录表为主表，与其它表进行左连接，where后面当然是查询条件，后面写什么取决你要显示哪部分内容，这里 r.type = '2' 表示只显示Terminal变更的记录。
下来是是Dao层和Service中的内容，这部分没什么可说的..
下来是Controller，这部分比较重要，主要获取Excel所在的路径，以及将数据库中的数据写到表格中。
其实导出并不难，最主要的是sql语句要写对。。
-----------EdcTerminalDao.xml+CreateUnionFileController+ExportTerminal+EdcTerminalDao+EdcTerminalService
<select id="getEdcTerminal" resultType="ExportTerminal"> SELECT b.manager AS "manager", b.telephone AS "telephone", b.address_chn AS "addressChn", b.org_acq_id AS "orgAcqId", i.firm AS "firm", i.model AS "model", A.merchant_id AS "merchantId", A.terminal_id AS "terminalId", A.terminal_stat AS "terminalStat", p.ic_in AS "icIn", p.term_enc_tp AS "termEncTp", p.term_enc_md AS "termEncMd", p.term_pri_enc_key_len AS "termPriEncKeyLen", p.pik_len AS "pikLen", p.mak_len AS "makLen", p.mac_algo AS "macAlgo", p.current_enc_key_index AS "currentEncKeyIndex", p.para_download_in AS "paraDownloadIn", p.tms_para_download_in AS "tmsParaDownloadIn", p.pub_key_download_in AS "pubKeyDownloadIn", p.ic_para_download_in AS "icParaDownloadIn", p.emv_para_download_in AS "emvParaDownloadIn", p.pan_blkbill_download_in AS "panBlkbillDownloadIn", p.dial_tp AS "dialTp", p.conn_md AS "connMd", p.dft_trans_curr_cd AS "dftTransCurrCd", p.term_place_addr AS "termPlaceAddr", p.pos_sn AS "posSn", p.term_seq_id AS "termSeqId", p.cntry_cd AS "cntryCd", p.open_dt AS "openDt", p.close_dt AS "closeDt", r.aud_detail AS "audDetail" FROM audit_record r LEFT JOIN merchant_base b ON r.merchant_id=b.merchant_id LEFT JOIN edc_terminal A ON A.terminal_id=r.terminal_id LEFT JOIN terminal_param p ON P.terminal_id=A.terminal_id LEFT JOIN pos_sn_info i ON i.pos_sn = P.pos_sn WHERE r.TYPE = '2' and r.aud_date= to_char(sysdate, 'yyyymmdd' ) <if test="connMd != null and connMd != ''"> AND TRIM(p.conn_md) = #{connMd} </if></select>
/** * 终端变更记录导出entity */public class ExportTerminal extends DataEntity<ExportTerminal> { private String telephone; private String addressChn; private String firm; private String model; private String orgAcqId; private String manager; private String merchantId; private String terminalId; private String terminalStat; private String icIn; private String termEncTp; private String termEncMd; private String termPriEncKeyLen; private String pikLen; private String makLen; private String macAlgo; private String paraDownloadIn; private String tmsParaDownloadIn; private String pubKeyDownloadIn; private String icParaDownloadIn; private String emvParaDownloadIn; private String panBlkbillDownloadIn; private String dialTp; private String dftTransCurrCd; private String openDt; private String closeDt; private String audDetail; public String getAudDetail() { return audDetail; } public void setAudDetail(String audDetail) { this.audDetail = audDetail; } public String getTelephone() { return telephone; } public void setTelephone(String telephone) { this.telephone = telephone; }
public List<ExportTerminal> getEdcTerminal(EdcTerminal edcTerminal);
@Autowired private EdcTerminalDao edcTerminalDao;public EdcTerminal get(String id) { return super.get(id);} public List<ExportTerminal> getEdcTerminal(EdcTerminal edcTerminal) {return edcTerminalDao.getEdcTerminal(edcTerminal);}
@RequiresPermissions("tranbase:createUnionFile:view")@RequestMapping(value = {"terminalTxtD"})public String exportExcel(EdcTerminal edcTerminal,HttpServletResponse res, HttpServletRequest req) { List<ExportTerminal> list = edcTerminalService.getEdcTerminal(edcTerminal); res.setContentType("application/vnd.ms-excel;charset=UTF-8"); res.setCharacterEncoding("UTF-8"); try { String headName = "TerminalRegisterReport"; String path = req.getSession().getServletContext().getRealPath("") + "/reportTemp/TerminalRegisterReport.xls"; // excel模板 InputStream in = new FileInputStream(new File(path)); HSSFWorkbook work = new HSSFWorkbook(in); HSSFSheet sheet = work.getSheetAt(0); // 得到excel的第1个sheet HSSFRow row = sheet.createRow((short) 1); //设置表头和制表时间 HSSFCellStyle style = work.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font = work.createFont(); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); font.setFontName("宋体"); font.setFontHeightInPoints((short) 12); // 设置字体大小 style.setFont(font); int startRow = 1; //表头往上多少行,开始循环填数据行数，从0开始 ExportTerminal edc = null; for (int i = 0; i < list.size(); i++) { edc = list.get(i); if (edc != null) { row = sheet.createRow(startRow + i); POIUtils.createCell(row, (short) 0, edc.getAudDetail(),style); POIUtils.createCell(row, (short) 1, edc.getMerchantId(), style); POIUtils.createCell(row, (short) 2, edc.getTerminalId(), style); POIUtils.createCell(row, (short) 3, edc.getOrgAcqId(), style); POIUtils.createCell(row, (short) 4, edc.getOrgAcqId(), style); POIUtils.createCell(row, (short) 5, edc.getOrgAcqId(), style); POIUtils.createCell(row, (short) 6, "00", style); POIUtils.createCell(row, (short) 7, edc.getTerminalStat() == null ? "" : edc.getTerminalStat(), style); POIUtils.createCell(row, (short) 8, "110109", style); POIUtils.createCell(row, (short) 9, "1", style); POIUtils.createCell(row, (short) 10, edc.getIcIn() == null ? "" : edc.getIcIn(), style); POIUtils.createCell(row, (short) 12, "1", style); POIUtils.createCell(row, (short) 15, "1.1E+29", style); POIUtils.createCell(row, (short) 20, edc.getTermEncTp() == null ? "" : edc.getTermEncTp(), style); POIUtils.createCell(row, (short) 21, edc.getTermEncMd() == null ? "" : edc.getTermEncMd(), style); POIUtils.createCell(row, (short) 22, edc.getTermPriEncKeyLen() == null ? "" : edc.getTermPriEncKeyLen(), style); POIUtils.createCell(row, (short) 23, edc.getPikLen() == null ? "" : edc.getPikLen(), style); POIUtils.createCell(row, (short) 24, edc.getMakLen() == null ? "" : edc.getMakLen(), style); POIUtils.createCell(row, (short) 25, edc.getMacAlgo() == null ? "" : edc.getMacAlgo(), style); POIUtils.createCell(row, (short) 28, "需要", style); POIUtils.createCell(row, (short) 29, "需要", style); POIUtils.createCell(row, (short) 34, "1", style); POIUtils.createCell(row, (short) 35, "0", style); POIUtils.createCell(row, (short) 36, edc.getParaDownloadIn() == null ? "" : edc.getParaDownloadIn(), style); POIUtils.createCell(row, (short) 37, edc.getTmsParaDownloadIn() == null ? "" : edc.getTmsParaDownloadIn(), style); POIUtils.createCell(row, (short) 38, edc.getPubKeyDownloadIn() == null ? "" : edc.getPubKeyDownloadIn(), style); POIUtils.createCell(row, (short) 39, edc.getIcParaDownloadIn() == null ? "" : edc.getIcParaDownloadIn(), style); POIUtils.createCell(row, (short) 40, edc.getEmvParaDownloadIn() == null ? "" : edc.getEmvParaDownloadIn(), style); POIUtils.createCell(row, (short) 41, edc.getPanBlkbillDownloadIn() == null ? "" : edc.getPanBlkbillDownloadIn(), style); POIUtils.createCell(row, (short) 43, edc.getDialTp() == null ? "" : edc.getDialTp(), style); POIUtils.createCell(row, (short) 44, "P", style); POIUtils.createCell(row, (short) 46, edc.getDftTransCurrCd() == null ? "" : edc.getDftTransCurrCd(), style); POIUtils.createCell(row, (short) 47, edc.getManager() == null ? "" : edc.getManager(), style); POIUtils.createCell(row, (short) 48, edc.getTelephone() == null ? "" : edc.getTelephone(), style); POIUtils.createCell(row, (short) 49, edc.getAddressChn() == null ? "" : edc.getAddressChn(), style); POIUtils.createCell(row, (short) 74, "156", style); POIUtils.createCell(row, (short) 75, edc.getOpenDt() == null ? "" : edc.getOpenDt(), style); POIUtils.createCell(row, (short) 76, edc.getCloseDt() == null ? "" : edc.getCloseDt(), style); POIUtils.createCell(row, (short) 111, edc.getFirm() == null ? "" : edc.getFirm(), style); POIUtils.createCell(row, (short) 112, edc.getModel() == null ? "" : edc.getModel(), style); POIUtils.createCell(row, (short) 114, "1", style); POIUtils.createCell(row, (short) 115, "1", style); POIUtils.createCell(row, (short) 116, "1", style); POIUtils.createCell(row, (short) 117, "1", style); POIUtils.createCell(row, (short) 118, "0", style); POIUtils.createCell(row, (short) 119, "处理", style); } } sheet.addMergedRegion(new Region(list.size() + startRow, (short) (0), list.size() + startRow, (short) (8))); /**************************** 输出流 *****************************************/ OutputStream os = res.getOutputStream();// 取得输出流 res.setHeader("Content-disposition", "attachment;filename=" + java.net.URLEncoder.encode(headName, "UTF-8") + ".xls"); work.write(os); if (in != null) { in.close(); } os.close(); } catch(Exception e){ e.printStackTrace(); } return null;}
阅读全文
0 0
- Terminal信息导出到Excel模板--【sql+entity+dao丶Service+Controller】
- controller-service-dao-entity
- 实现Entity,Dao,Service,Controller，JSP代码生成神器
- Controller Service Dao总结
- dao、service、controller、view
- Action(controller) service DAO
- sql 导出到Excel
- 导出到EXCEL模板问题
- 导出信息到Excel实践
- 导出信息到excel表
- Controller+Service+DAO初试水
- SQL快速导出到Excel
- EXCEl导入导出到SQL
- EXCEl导入导出到SQL
- sql备份文件导出到excel
- C#导出Excel|导出数据到Excel模板文件里
- asp 导出Excel ,导入Excel到SQL
- 根据模板导出到Excel(lp)
- Spring之AOP由浅入深
- 常用字符集总结
- 如何让努力变得更加有效
- mybatis 一对多映射中的一些小问题
- thinkphp3.2加入验证码
- Terminal信息导出到Excel模板--【sql+entity+dao丶Service+Controller】
- （转）tensorflow中使用指定的GPU及GPU显存
- 斯坦福机器学习 ex1 Python实现
- opencv如何读取文件夹中的图片
- 安装使用Drill-1.10
- 分治——棋盘覆盖
- Cx_Redis深度学习（一）--发布订阅
- 44、翻转单词顺序列
- MapReduce On YARN的工作原理
