基于 Java+Mysql 实现的奖助学金管理系统

package com.util.export.impl; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import jxl.Workbook; import jxl.format.UnderlineStyle; import jxl.write.Label; import jxl.write.Number; import jxl.write.WritableCellFormat; import jxl.write.WritableFont; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import jxl.write.WriteException; import com.scholarship.module.account.Account; import com.scholarship.module.college.College; import com.scholarship.module.conf.AppConfig; import com.scholarship.module.datas.Datas; import com.scholarship.module.export.ExportCustom; import com.scholarship.module.grade.Grade; import com.scholarship.module.scholarship.Scholarship; import com.scholarship.service.account.AccountService; import com.scholarship.service.apply.ApplyService; import com.scholarship.service.college.CollegeService; import com.scholarship.service.datas.DatasService; import com.scholarship.service.grade.GradeService; import com.scholarship.service.scholarship.ScholarshipService; import com.util.StringUtil; public class ExportTypeCustom extends Export { // ExportCustom es = new ExportCustom(); /*** * 导出自定义所有数据 */ public ExportTypeCustom(Account account, AccountService accountService, ApplyService applyService, CollegeService collegeService, GradeService gradeService, DatasService datasService, ScholarshipService scholarshipService) { super(account, accountService, applyService, collegeService, gradeService, datasService, scholarshipService); // TODO Auto-generated constructor stub } /*** * 导出自定义所有数据 */ @SuppressWarnings({ "resource", "finally" }) @Override public InputStream export(String year) { // TODO Auto-generated method stub Map<String,String> map = new HashMap<String,String>(); if(account.getRole().getId()!=1){ map.put("roleId", String.valueOf(account.getRole().getId()));//当前账户角色可以看到的审批 } map.put("year", year);//年份 map.put("status", "2");//审批已通过 int i =1; List<String> l = new ArrayList<>(); while(null!=scholarshipServie.queryById(i)){ l.add(String.valueOf(i++)); } // String ids = es.getIds(); // String ids ="1,2,3,4,5,6,7,8,9,10,11" // String[] l; // if(StringUtil.isNotBlank(ids)){ // l = ids.split(","); // } String filePath = AppConfig.ctx+"csvTemplate\\自定义表格"+year+".xls"; InputStream input = null; try { OutputStream os = new FileOutputStream(filePath); WritableWorkbook wwb = Workbook.createWorkbook(os); if(l!=null){ System.out.println("==============Start ExportCustomerTable"); for(String id : l){ Scholarship s = scholarshipServie.queryById(Integer.parseInt(id)); String tip = s.getCategory()+s.getLevel(); map.put("scholarshipId", id); List<Account> accountList = applyService.queryAccountList(map); System.out.println("导出："+s.getCategory()+s.getLevel()+" 人数："+accountList.size()); WritableSheet sheet = wwb.createSheet(tip, 0); this.werite(accountList, sheet, year, tip,s); } System.out.println("==============End ExportCustomerTable"); } wwb.write(); wwb.close(); input = new FileInputStream(filePath); } catch (IOException | WriteException e) { // TODO Auto-generated catch block e.printStackTrace(); System.out.println("Export Table has fail--"); }finally{ return input; } } private int werite(List<Account> accountList,WritableSheet sheet, String year, String category, Scholarship s) throws WriteException{ String[] title_student = {"序号","姓名","学号","密码","性别","QQ","电话","电子邮件"}; String[] title_class = {"学院","班级","专业","学历","入学年份","年级","学年"}; String[] title_datas = {"出生年月","身份证号","银行卡号","民族","东部","中部","西部","家庭住址","住址简写","离县城远近","月生活费","生源地贷款","爷爷","爷爷收入","爷爷健康","奶奶","奶奶收入","奶奶健康","父亲","父亲收入","父亲职业","父亲身体","母亲","母亲收入","母亲职业","母亲身体","兄弟姐妹","家庭收入","主要支出","结余","主要困难原因","变故","成绩排名","素质排名"}; String[] title_scholarship = {"奖金种类","奖金等级","奖金金额"}; WritableCellFormat cellFormat = new WritableCellFormat(); Label label=null; Number number=null; int row = 4; int column = 0; for(;column<title_student.length+title_class.length+title_datas.length+title_scholarship.length;column++){ sheet.setColumnView(column, 20); // 设置列的宽度 } WritableFont wf_table = new WritableFont(WritableFont.ARIAL, 18, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);// 定义格式 字体 下划线 斜体 粗体 颜色 WritableFont wf_title = new WritableFont(WritableFont.ARIAL, 13, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK); WritableFont wf_content = new WritableFont(WritableFont.ARIAL, 11, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK); label = new Label(0, 1, "黄冈师范学院自定义导出表("+year+"年"+category+")"); cellFormat.setFont(wf_table); cellFormat.setAlignment(jxl.format.Alignment.CENTRE);//设置对齐方式 cellFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK); //设置边框 label.setCellFormat(cellFormat); sheet.addCell(label); sheet.mergeCells(0, 1, 7, 1);//合并大标题 column = 0; //表头1 for (int i = 0; i < title_student.length; i++,column++) { label = new Label(column,row,title_student[i]); cellFormat = new WritableCellFormat(); cellFormat.setFont(wf_title); cellFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK); //设置边框 // cellFormat.setWrap(true);//自动换行 label.setCellFormat(cellFormat); sheet.addCell(label); } //表头2 for (int i = 0; i < title_class.length; i++,column++) { label = new Label(column,row,title_class[i]); cellFormat = new WritableCellFormat(); cellFormat.setFont(wf_title); cellFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK); //设置边框 // cellFormat.setWrap(true);//自动换行 label.setCellFormat(cellFormat); sheet.addCell(label); } //表头3 for (int i = 0; i < title_datas.length; i++,column++) { label = new Label(column,row,title_datas[i]); cellFormat = new WritableCellFormat(); cellFormat.setFont(wf_title); cellFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK); //设置边框 // cellFormat.setWrap(true);//自动换行 label.setCellFormat(cellFormat); sheet.addCell(label); } //表头4 for (int i = 0; i < title_scholarship.length; i++,column++) { label = new Label(column,row,title_scholarship[i]); cellFormat = new WritableCellFormat(); cellFormat.setFont(wf_title); cellFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK); //设置边框 // cellFormat.setWrap(true);//自动换行 label.setCellFormat(cellFormat); sheet.addCell(label); } row++; for(Account a : accountList){ column = 0; // Syst