配置文件key/value的读写以及Excel的定时导出

来源：互联网发布：西门吹牛中淘宝店编辑：程序博客网时间：2024/06/02 01:07

读取配置文件key/value形式

根据key得到value

//获取valueprivate static String getParaValue(String propertiesUrl, String key) {InputStream str = null;String value = null;try {if (key != null) {str = PropertiesUtil.class.getClassLoader()       //获取类的加载器 进而可以获得所有resouce.getResourceAsStream(propertiesUrl);Properties ps = new Properties();ps.load(str);value = ps.getProperty(key);}} catch (IOException e) {e.printStackTrace();} finally {if (str != null)try {str.close();} catch (IOException e) {e.printStackTrace();}}return value;}

获取所有的key和value

//获取全部key和valuepublic static Map<String, String> getParaKeyAndValue(String path) {InputStream str = null;Map<String, String> map = new LinkedHashMap<String, String>();try {str = PropertiesUtil.class.getClassLoader().getResourceAsStream(path);Properties ps = new Properties();//继承hashtable，并且实现了map接口ps.load(str);@SuppressWarnings("unchecked")Iterator<String> it = (Iterator<String>) ps.propertyNames();while (it.hasNext()) {String key = it.next();map.put(key, ps.getProperty(key));}} catch (IOException e) {e.printStackTrace();} finally {if (str != null)try {str.close();} catch (IOException e) {e.printStackTrace();}}return map;}

基于spring Excel的定时导出

首先创建一个定时任务，在定时任务里面调用相应的导出方法。

spring配置

<task:annotation-driven/>

java类的实现

@Componentpublic class ExportExcelTimer {private static final String suffix= ".xls";private static final Logger logger = Logger.getLogger(ExportExcelTimer.class);private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");/** * 没有1号00:01:00开始导出月报表 * 秒，分，时，天，月，年 */   // @Scheduled(cron = "0 10 12 * * *")@Scheduled(cron="0/30 * *  * * ? ")    public void TaskJob() {    createExcel();    }        //获取配置项public Map<String,List<String>> obtainExcelProperties(){  Map<String,String> params = PropertiesUtil.getParaKeyAndValue("config/monthReport.properties");  Map<String,List<String>> map = new LinkedHashMap<String, List<String>>();  for (String key : params.keySet()) {  map.put(key, changeToList(params.get(key)));  }  return map;  }    public List<String> changeToList(String name){List<String> list = new ArrayList<String>();String[] names = name.split("\\|");for (int i = 0; i < names.length; i++){list.add(names[i]);}return list;}    public void createExcel() {  Map<String,List<String>> proMap = obtainExcelProperties();  Map<String,String> rootPathMap = PropertiesUtil.getParaKeyAndValue("config/sendMail.properties");  String rootPath = rootPathMap.get("locate_path");  for(String name : proMap.keySet()){  Calendar cal = Calendar.getInstance();  cal.add(Calendar.MONTH, -1);  String month = sdf.format(cal.getTime());//统计月份  String xlsName = null;  try {    List<String> list = proMap.get(name);    xlsName = list.get(2);    logger.info(String.format("开始导出%s%s统计报表...", xlsName, month));        String serviceName = list.get(0);    String methodName = list.get(1);    String fileName = xlsName+month+suffix;//excel文件名        Object bean = SpringBeanFactoryUtils.getBean(serviceName);    Method method = ReflectionUtils.findMethod(bean.getClass(), methodName, String.class);        HSSFWorkbook wb = (HSSFWorkbook) ReflectionUtils.invokeMethod(method, bean, month);    File file = new File(rootPath+File.separator+fileName);    if (!file.getParentFile().exists()) {    file.getParentFile().mkdirs();    }    if(file.exists()){    file.delete();            }     FileOutputStream fos = new FileOutputStream(file);    wb.write(fos);    fos.close();    logger.info(String.format("%s%s统计报表导出成功并写入本地！！！", xlsName, month));    //SendExcelMails.sendMail(month);  } catch (Exception e) {  logger.error(String.format("%s%s统计报表导出失败！！！", xlsName, month));  logger.error(e.getMessage(), e);}  }  }}

Excel的设计

public ActionResult excelPrint() {       HSSFWorkbook workbook = newHSSFWorkbook();// 创建一个Excel文件       HSSFSheet sheet =workbook.createSheet();// 创建一个Excel的Sheet       sheet.createFreezePane(1, 3);// 冻结        // 设置列宽        sheet.setColumnWidth(0, 1000);        sheet.setColumnWidth(1, 3500);        sheet.setColumnWidth(2, 3500);        sheet.setColumnWidth(3, 6500);        sheet.setColumnWidth(4, 6500);        sheet.setColumnWidth(5, 6500);        sheet.setColumnWidth(6, 6500);        sheet.setColumnWidth(7, 2500);        // Sheet样式        HSSFCellStyle sheetStyle =workbook.createCellStyle();        // 背景色的设定       sheetStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);        // 前景色的设定       sheetStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);        // 填充模式       sheetStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);        // 设置列的样式        for (int i = 0; i <= 14; i++){          sheet.setDefaultColumnStyle((short)i, sheetStyle);        }       // 设置字体        HSSFFont headfont =workbook.createFont();        headfont.setFontName("黑体");       headfont.setFontHeightInPoints((short) 22);// 字体大小       headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗        // 另一个样式        HSSFCellStyle headstyle =workbook.createCellStyle();        headstyle.setFont(headfont);       headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中       headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中        headstyle.setLocked(true);        headstyle.setWrapText(true);// 自动换行        // 另一个字体样式        HSSFFont columnHeadFont =workbook.createFont();        columnHeadFont.setFontName("宋体");       columnHeadFont.setFontHeightInPoints((short) 10);        columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);        // 列头的样式        HSSFCellStyle columnHeadStyle =workbook.createCellStyle();        columnHeadStyle.setFont(columnHeadFont);       columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中       columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中        columnHeadStyle.setLocked(true);        columnHeadStyle.setWrapText(true);       columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框的颜色        columnHeadStyle.setBorderLeft((short)1);// 边框的大小      columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);// 右边框的颜色       columnHeadStyle.setBorderRight((short) 1);// 边框的大小       columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体       columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色        // 设置单元格的背景颜色（单元格的样式会覆盖列或行的样式）       columnHeadStyle.setFillForegroundColor(HSSFColor.WHITE.index);            HSSFFont font =workbook.createFont();        font.setFontName("宋体");        font.setFontHeightInPoints((short)10);        // 普通单元格样式        HSSFCellStyle style =workbook.createCellStyle();        style.setFont(font);       style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中       style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中        style.setWrapText(true);       style.setLeftBorderColor(HSSFColor.BLACK.index);        style.setBorderLeft((short) 1);       style.setRightBorderColor(HSSFColor.BLACK.index);        style.setBorderRight((short) 1);       style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体       style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色．       style.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色．        // 另一个样式        HSSFCellStyle centerstyle =workbook.createCellStyle();        centerstyle.setFont(font);       centerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中       centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中        centerstyle.setWrapText(true);       centerstyle.setLeftBorderColor(HSSFColor.BLACK.index);        centerstyle.setBorderLeft((short)1);        centerstyle.setRightBorderColor(HSSFColor.BLACK.index);        centerstyle.setBorderRight((short)1);        centerstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 设置单元格的边框为粗体      centerstyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色．       centerstyle.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色．            try {         // 创建第一行          HSSFRow row0 =sheet.createRow(0);          // 设置行高          row0.setHeight((short) 900);          // 创建第一列          HSSFCell cell0 =row0.createCell(0);          cell0.setCellValue(newHSSFRichTextString("中非发展基金投资项目调度会工作落实情况对照表"));         cell0.setCellStyle(headstyle);          /**         * 合并单元格        *    第一个参数：第一个单元格的行数（从0开始）         *    第二个参数：第二个单元格的行数（从0开始）         *    第三个参数：第一个单元格的列数（从0开始）         *    第四个参数：第二个单元格的列数（从0开始）         */         CellRangeAddress range = newCellRangeAddress(0, 0, 0, 7);          sheet.addMergedRegion(range);          // 创建第二行          HSSFRow row1 =sheet.createRow(1);          HSSFCell cell1 =row1.createCell(0);          cell1.setCellValue(newHSSFRichTextString("本次会议时间：2009年8月31日       前次会议时间：2009年8月24日"));        cell1.setCellStyle(centerstyle);         // 合并单元格          range = new CellRangeAddress(1, 2,0, 7);          sheet.addMergedRegion(range);          // 第三行          HSSFRow row2 =sheet.createRow(3);          row2.setHeight((short) 750);          HSSFCell cell =row2.createCell(0);          cell.setCellValue(newHSSFRichTextString("责任者"));        cell.setCellStyle(columnHeadStyle);         cell = row2.createCell(1);          cell.setCellValue(newHSSFRichTextString("成熟度排序"));        cell.setCellStyle(columnHeadStyle);         cell = row2.createCell(2);          cell.setCellValue(newHSSFRichTextString("事项"));        cell.setCellStyle(columnHeadStyle);         cell = row2.createCell(3);          cell.setCellValue(newHSSFRichTextString("前次会议要求\n/新项目的项目概要"));        cell.setCellStyle(columnHeadStyle);         cell = row2.createCell(4);          cell.setCellValue(newHSSFRichTextString("上周工作进展"));        cell.setCellStyle(columnHeadStyle);         cell = row2.createCell(5);          cell.setCellValue(newHSSFRichTextString("本周工作计划"));        cell.setCellStyle(columnHeadStyle);         cell = row2.createCell(6);          cell.setCellValue(newHSSFRichTextString("问题和建议"));        cell.setCellStyle(columnHeadStyle);         cell = row2.createCell(7);          cell.setCellValue(newHSSFRichTextString("备 注"));        cell.setCellStyle(columnHeadStyle);         // 访问数据库，得到数据集          List<DeitelVO> deitelVOList =getEntityManager().queryDeitelVOList();         int m = 4;          int k = 4;          for (int i = 0; i <deitelVOList.size(); i++) {            DeitelVO vo =deitelVOList.get(i);            String dname = vo.getDname();            List<Workinfo> workList =vo.getWorkInfoList();            HSSFRow row = sheet.createRow(m);            cell = row.createCell(0);            cell.setCellValue(new HSSFRichTextString(dname));           cell.setCellStyle(centerstyle);           // 合并单元格            range = new CellRangeAddress(m, m+ workList.size() - 1, 0, 0);           sheet.addMergedRegion(range);            m = m + workList.size();                for (int j = 0; j <workList.size(); j++) {              Workinfo w =workList.get(j);              // 遍历数据集创建Excel的行             row = sheet.getRow(k + j);              if (null == row) {                row = sheet.createRow(k +j);              }              cell = row.createCell(1);             cell.setCellValue(w.getWnumber());            cell.setCellStyle(centerstyle);             cell = row.createCell(2);              cell.setCellValue(newHSSFRichTextString(w.getWitem()));             cell.setCellStyle(style);              cell = row.createCell(3);              cell.setCellValue(newHSSFRichTextString(w.getWmeting()));             cell.setCellStyle(style);              cell = row.createCell(4);              cell.setCellValue(newHSSFRichTextString(w.getWbweek()));             cell.setCellStyle(style);              cell = row.createCell(5);              cell.setCellValue(new HSSFRichTextString(w.getWtweek()));              cell.setCellStyle(style);              cell = row.createCell(6);              cell.setCellValue(newHSSFRichTextString(w.getWproblem()));             cell.setCellStyle(style);              cell = row.createCell(7);              cell.setCellValue(newHSSFRichTextString(w.getWremark()));             cell.setCellStyle(style);            }           k = k + workList.size();          }         // 列尾          int footRownumber =sheet.getLastRowNum();          HSSFRow footRow = sheet.createRow(footRownumber+ 1);          HSSFCell footRowcell =footRow.createCell(0);          footRowcell.setCellValue(newHSSFRichTextString("                   审  定：XXX      审  核：XXX     汇  总：XX"));         footRowcell.setCellStyle(centerstyle);         range = new CellRangeAddress(footRownumber+ 1, footRownumber + 1, 0, 7);         sheet.addMergedRegion(range);              HttpServletResponse response =getResponse();          HttpServletRequest request =getRequest();          String filename = "未命名.xls";//设置下载时客户端Excel的名称         // 请见：http://zmx.iteye.com/blog/622529          filename =Util.encodeFilename(filename, request);        response.setContentType("application/vnd.ms-excel");         response.setHeader("Content-disposition","attachment;filename=" + filename);         OutputStream ouputStream =response.getOutputStream();          workbook.write(ouputStream);          ouputStream.flush();          ouputStream.close();            } catch (Exception e) {          e.printStackTrace();        }       return null;      }

阅读全文

0 0