1.下载:poi-3.7-20101029.jar包并导入项目。
2.ExtportUtil.java
package com.wondersgroup.qyws.sjzk.action.sjhc; import java.math.BigDecimal; 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.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; public class ExtportUtil { public static int writeRecords(HSSFRow row, HSSFSheet sheet, int nColumn, HSSFCell cell, HSSFWorkbook workbook, int iRow, List rs) { int size = rs.size(); for (int j = 0; j < size; j++) { row = sheet.createRow(iRow); Object obj[] = new Object[]{}; obj = (Object[]) rs.get(j); for (int k = 0; k < nColumn; k++) { cell = row.createCell(k); if (obj[k] != null) { cell.setCellValue(new HSSFRichTextString(obj[k]+"")); } else { cell.setCellValue(new HSSFRichTextString("无效数据")); } HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); //针对导出上传接口表的红色背景样式: if( obj.length==5 && obj[3].toString().equals("s") && ((BigDecimal)obj[4]).intValue()==0 ){ cellStyle.setFillForegroundColor(HSSFColor.RED.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); } // 设置显示格式,避免点击后变成科学计数法了 cellStyle.setDataFormat((short) 0x31); cellStyle.setWrapText(true); cell.setCellStyle(cellStyle); } iRow = iRow + 1; } return iRow-1; } public static int writeRecordsWithNo(HSSFRow row, HSSFSheet sheet, int nColumn, HSSFCell cell, HSSFWorkbook workbook, int iRow, List rs) { int size = rs.size(); for (int j = 0; j < size; j++) { row = sheet.createRow(iRow); Object obj[] = new Object[]{}; obj = (Object[]) rs.get(j); for (int k = 0; k < nColumn; k++) { if(k==0){ //序号单元格 cell = row.createCell(k); cell.setCellValue(new HSSFRichTextString((j+1)+"")); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setDataFormat((short) 0x31); cellStyle.setWrapText(true); //针对导出上传接口表的红色背景样式: if( obj.length==5 && obj[3].toString().equals("s") && ((BigDecimal)obj[4]).intValue()==0 ){ cellStyle.setFillForegroundColor(HSSFColor.RED.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); } cell.setCellStyle(cellStyle); }else{ //数据单元格 cell = row.createCell(k); if (obj[k-1] != null) { cell.setCellValue(new HSSFRichTextString(obj[k-1]+"")); } else { cell.setCellValue(new HSSFRichTextString("无效数据")); } HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); //针对导出上传接口表的红色背景样式: if( obj.length==5 && obj[3].toString().equals("s") && ((BigDecimal)obj[4]).intValue()==0 ){ cellStyle.setFillForegroundColor(HSSFColor.RED.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); } // 设置显示格式,避免点击后变成科学计数法了 cellStyle.setDataFormat((short) 0x31); cellStyle.setWrapText(true); cell.setCellStyle(cellStyle); } } iRow = iRow + 1; } return iRow-1; } // 大标题样式 public static HSSFCellStyle getStyle1(HSSFWorkbook workbook) { HSSFCellStyle style = workbook.createCellStyle(); // 设置单元格样式 HSSFFont font = workbook.createFont(); font.setFontName(HSSFFont.FONT_ARIAL);// 字体 font.setFontHeightInPoints((short) 18);// 字号 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setFont(font); return style; } // 小标题样式 public static HSSFCellStyle getStyle2(HSSFWorkbook workbook) { // 一、数据质量报告 HSSFCellStyle style = workbook.createCellStyle(); // 设置单元格样式 HSSFFont font = workbook.createFont(); font.setFontName(HSSFFont.FONT_ARIAL);// 字体 font.setFontHeightInPoints((short) 12);// 字号 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗 style.setAlignment(HSSFCellStyle.ALIGN_LEFT); style.setFont(font); return style; } // 描述文本样式 public static HSSFCellStyle getStyle3(HSSFWorkbook workbook) { // 一、数据质量报告 HSSFCellStyle style = workbook.createCellStyle(); // 设置单元格样式 HSSFFont font = workbook.createFont(); font.setFontName(HSSFFont.FONT_ARIAL);// 字体 font.setFontHeightInPoints((short) 10);// 字号 style.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY); style.setFont(font); return style; } // 数据表格标题样式 public static HSSFCellStyle getThStyle(HSSFWorkbook workbook) { HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setAlignment(HSSFCellStyle.ALIGN_GENERAL); // cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setWrapText(true); return cellStyle; } public static String getValue(String content) { String temp = ""; if(content.startsWith("0")){ temp = content.substring(1); }else{ temp = content.substring(0); } return temp; } }
3.应用:
package com.wondersgroup.qyws.sjzk.action.sjhc; import java.io.IOException; import java.io.UnsupportedEncodingException; import java.net.URLEncoder; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.List; import java.util.Map; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; 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.ss.util.CellRangeAddress; import org.apache.struts2.ServletActionContext; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.context.annotation.Scope; import org.springframework.stereotype.Controller; import com.opensymphony.xwork2.ActionContext; import com.wondersgroup.qyws.sjzk.service.FxbgService; import com.wondersgroup.qyws.sjzk.util.Constants; @Controller() @Scope("prototype") public class ExtportFxbgAction { @Autowired private FxbgService fxbgService; // 描述分数 private double hczhdf; private double sclxxdf; private double sjscfwdf; private double sjzldf; // 数据表格 private List hczhdfList; private List sclxxdfList; private List sjscfwdfList; private List sjzldfList; private List zbdbList; // 评分分级 private List zhdfGradeList; private List lxxGradeList; private List fwdfGradeList; private List zldfGradeList; //查询条件和条件展示 private String sd = "";// 起始日期 - 查询条件 private String ed = "";// 截止日期 - 查询条件 private String ssd = "";// 起始日期 - 展示条件 private String sed = "";// 截止日期 - 展示条件 //报告描述 private String msg; private String ds; /** * 导出分析报告 * * @throws UnsupportedEncodingException * */ public String exportFxbg() throws UnsupportedEncodingException { ActionContext context = ActionContext.getContext(); HttpServletResponse response = (HttpServletResponse) context .get(ServletActionContext.HTTP_RESPONSE); response.setContentType("application/vnd.ms-excel"); if(ds == null){ ds = (String) context.getSession().get("ds"); }else{ context.getSession().put("ds", ds); } String csd = ""; String ced = ""; if (sd.isEmpty()) { //默认执行 int cha = ds.equals(Constants.CEN_INTER) ? 1 : 2; Calendar now = Calendar.getInstance(); now.add(Calendar.DATE, -cha); ssd = now.get(Calendar.YEAR)+"年"+(now.get(Calendar.MONTH) + 1)+"月1日"; sed = now.get(Calendar.YEAR)+"年"+(now.get(Calendar.MONTH) + 1)+"月"+(now.get(Calendar.DATE))+"日"; ced = new SimpleDateFormat("yyyyMMdd").format(now.getTime()); csd = ced.substring(0,6)+"01";//该月一号 ed = new SimpleDateFormat("yyyy-MM-dd").format(now.getTime()); sd = ed.substring(0,8)+"01"; }else{ //条件查询 String sm = ""; String se = ""; String em = ""; String ee = ""; sm = ExtportUtil.getValue(sd.substring(4, 6)); se = ExtportUtil.getValue(sd.substring(6, 8)); em = ExtportUtil.getValue(ed.substring(4, 6)); ee = ExtportUtil.getValue(ed.substring(6, 8)); ssd = sd.substring(0, 4)+"年"+sm+"月"+se+"日"; sed = ed.substring(0, 4)+"年"+em+"月"+ee+"日"; csd = sd.substring(0, 4)+sd.substring(5, 7)+sd.substring(8, 10); ced = ed.substring(0, 4)+ed.substring(5, 7)+ed.substring(8, 10); } Map vals = fxbgService.getFxbg(csd, ced, ds); hczhdf = ((Double) vals.get("hczhdf")).doubleValue(); sclxxdf = ((Double) vals.get("sclxxdf")).doubleValue(); sjscfwdf = ((Double) vals.get("sjscfwdf")).doubleValue(); sjzldf = ((Double) vals.get("sjzldf")).doubleValue(); hczhdfList = (List) vals.get("hczhdfList"); sclxxdfList = (List) vals.get("sclxxdfList"); sjscfwdfList = (List) vals.get("sjscfwdfList"); sjzldfList = (List) vals.get("sjzldfList"); //[[0,''],[3,'a,b,c'],[4,'d,e,f,g],[0,'']] // 综合得分机构评级:[3,'jg1,jg2,jg3'] zhdfGradeList = (List) vals.get("zhdfGradeList"); // 数据上传连续性得分机构评级 lxxGradeList = (List) vals.get("lxxGradeList"); // 数据上传范围得分机构评级 fwdfGradeList = (List) vals.get("fwdfGradeList"); // 数据质量得分机构评级 zldfGradeList = (List) vals.get("zldfGradeList"); // // 指标对比zbdbList zbdbList = (List) vals.get("zbdbList"); // // 数据状况描述: msg = (String)vals.get("msg"); String xlsName = "数据上传核查报告.xls"; // Excel文档对象,会把数据导入到这个文档 HSSFWorkbook workbook = new HSSFWorkbook(); response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(xlsName, "utf-8")); int iRow = 0;// 行下标,整个sheet的行标控制 workbook = transHczhdfListToExcel(workbook, iRow); ServletOutputStream fOut; try { fOut = response.getOutputStream(); workbook.write(fOut); fOut.flush(); fOut.close(); } catch (IOException e) { e.printStackTrace(); } return null; } private HSSFWorkbook transHczhdfListToExcel(HSSFWorkbook workbook, int iRow) { String[] theme = { "一、综合得分报告:", "二、上传连续性报告", "三、上传表范围报告", "四、数据质量报告","五、业务指标核对报告","六、评价与建议" }; String[] titles1 = { "序号", "医疗机构", "得分" }; String[] titles2 = { "序号", "医疗机构", "期内上传数据天数", "期内天数", "得分" }; String[] titles3 = { "序号", "医疗机构", "实际上传表范围", "应上传表范围", "得分" }; String[] titles4 = { "序号", "字典核查得分", "非空核查得分", "关联核查得分", "可追溯性核查得分", "数据质量得分" }; String[] titles5 = { "数据来源\\指标", "门诊人次", "门诊总收入", "门诊药品收入", "门诊检验人次", "门诊检查人次","门诊手术人次", "出院人次", "出院病人床日数", "出院病人总收入", "出院病人药品收入", "住院检验人次", "住院检查人次", "住院手术人次" }; // 创建工作表(sheet) HSSFSheet sheet = workbook.createSheet(); workbook.setSheetName(0, "数据上传核查报告");// 下标0为第一个sheet // ------------------------ 大标题 ---------------------------------- // 给sheet创建一个行 HSSFRow row = sheet.createRow(iRow);// iRow = 0 HSSFCell cell = null; cell = row.createCell(0);// 创建一个单元格 // 设置字体样式 HSSFCellStyle cellStyle1 = ExtportUtil.getStyle1(workbook); // 设置单元格样式 cell.setCellStyle(cellStyle1); // 设置文件名第一行展示,这里题目名和sheet名一样 cell.setCellValue(new HSSFRichTextString("数据上传核查报告")); sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0, 4)); // ------------------------ 日期说明 ---------------------------------- iRow = iRow + 1;// 第二行 iRow= 1 row = sheet.createRow(iRow);// 创建行 HSSFCell rq = null; rq = row.createCell(0);// 创建单元格 HSSFCellStyle rq_sty = ExtportUtil.getStyle3(workbook); // 设置单元格样式 rq_sty.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中 rq.setCellStyle(rq_sty); rq.setCellValue(new HSSFRichTextString(ssd+" — "+sed)); // new CellRangeAddress(y1,x1,y2,x2) sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0, 4)); // "其中得分为优的机构有2家医院,其中得分为良的机构有2家医院 ," // + "其中得分为中的机构有4家医院,其中得分为差的机构有2家医院;详细列表如下:"; String desc1 = ""; String gs1 = ""; if (zhdfGradeList.size() != 0) { desc1 = getHczhdfMs("数据质量核查综合", hczhdf, zhdfGradeList); gs1 = "注:得分= 上传连续性得分*权重 + 上传表范围得分*权重 + 数据质量得分*权重"; } List la = setDescData(workbook, row, iRow, cell, theme[0], titles1, hczhdfList, desc1, gs1, true); String desc2 = ""; String gs2 = ""; if (sclxxdfList.size() != 0) { desc2 = getHczhdfMs("上传连续性", sclxxdf, lxxGradeList); gs2 = "注:得分=期内上传数据天数/期内天数*100"; } la = setDescData((HSSFWorkbook) la.get(1), row, ((Integer) la.get(0)) .intValue(), cell, theme[1], titles2, sclxxdfList, desc2, gs2, true); String desc3 = ""; String gs3 = ""; if (sjscfwdfList.size() != 0) { desc3 = getHczhdfMs("数据上传范围", sjscfwdf, fwdfGradeList); gs3 = "注:得分=实际上传表范围/应上传表范围*100"; } la = setDescData((HSSFWorkbook) la.get(1), row, ((Integer) la.get(0)) .intValue(), cell, theme[2], titles3, sjscfwdfList, desc3, gs3, true); String desc4 = ""; String gs4 = ""; if (sjzldfList.size() != 0) { desc4 = getHczhdfMs("数据质量", sjzldf, zldfGradeList); gs4 = "注:分项得分 = (数据总条数—错误数)/数据总条数*100; 数据质量得分 = 字典核查分项得分*权重 + 非空核查分项得分*权重 + 关联核查分项得分*权重 + 可追溯性核查分项得分*权重;"; } la = setDescData((HSSFWorkbook) la.get(1), row, ((Integer) la.get(0)) .intValue(), cell, theme[3], titles4, sjzldfList, desc4, gs4, true); //指标对比 la = setDescData((HSSFWorkbook) la.get(1), row, ((Integer) la.get(0)) .intValue(), cell, theme[4], titles5, zbdbList, "zbdb", "", false); // 六、评价与建议: iRow = ((Integer) la.get(0)).intValue() + 2; row = sheet.createRow(iRow); cell = row.createCell(0); cell.setCellStyle(ExtportUtil.getStyle2(workbook)); cell.setCellValue(new HSSFRichTextString(theme[5])); sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0, 4)); iRow = iRow + 1; row = sheet.createRow(iRow); cell = row.createCell(0); cell.setCellStyle(ExtportUtil.getStyle3(workbook)); cell.setCellValue(new HSSFRichTextString(msg)); sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0, 4)); return workbook; } private String getHczhdfMs(String type, double fs, List list) { StringBuffer sb = new StringBuffer("本期" + type + "得分" + fs + ","); String[] grade = { "优", "良", "中", "差" }; for (int i = 0; i < 4; i++) { int jgs = (Integer) list.get(2 * i); sb.append("本项得分为" + grade[i] + "的机构有" + list.get(2 * i) + "家医院"); if (jgs != 0) { sb.append(":" + list.get(2 * i + 1) + ""); } sb.append(";"); } sb.append("详细列表如下:"); return sb.toString(); } private List setDescData(HSSFWorkbook workbook, HSSFRow row, int iRow, HSSFCell cell, String title, String[] ths, List list, String descc, String gs, boolean isOrder) { HSSFSheet sheet = workbook.getSheetAt(0); // ------------------------ 小标题 ---------------------------------- iRow = iRow + 2;// 空一行 row = sheet.createRow(iRow);// 创建行 HSSFCell xbt = null; xbt = row.createCell(0);// 创建单元格 HSSFCellStyle style2 = ExtportUtil.getStyle2(workbook); // 设置单元格样式 xbt.setCellStyle(style2); xbt.setCellValue(new HSSFRichTextString(title)); // new CellRangeAddress(y1,x1,y2,x2) sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0, 3)); HSSFCellStyle thStyle = ExtportUtil.getThStyle(workbook); HSSFCellStyle desc_sty = ExtportUtil.getStyle3(workbook); // 设置单元格样式 if (list.size() == 0) { iRow = iRow + 1; row = sheet.createRow(iRow); HSSFCell desc = null; desc = row.createCell(0); thStyle.setFillForegroundColor(HSSFColor.RED.index); desc.setCellStyle(thStyle); desc.setCellValue(new HSSFRichTextString("期内没有符合条件的数据。")); sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0, 3)); } else { // ------------------------ 描述 -------------------------------- if(!descc.isEmpty()){ iRow = iRow + 1;// 第3行 iRow= 2 row = sheet.createRow(iRow); HSSFCell desc = null; desc = row.createCell(0); desc.setCellStyle(desc_sty); desc.setCellValue(new HSSFRichTextString(descc)); sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0, ths.length - 1)); } // ------------------------ 数据表格 ---------------------------------- iRow = iRow + 1;// 第4行 iRow= 3 row = sheet.createRow(iRow); int nColumn = ths.length; for (int i = 1; i <= nColumn; i++) { sheet.setColumnWidth(i - 1, 20 * 256);// 设置单元格宽度:18字符 cell = row.createCell((i - 1)); // --- 设置单元格样式 --- cell.setCellStyle(thStyle); // --- 给单元格赋值 --- cell.setCellValue(new HSSFRichTextString(ths[i - 1].toString())); } // --- 写入各条记录 --- iRow = iRow + 1; //导入的数据表格第一列是否有序号列 if(isOrder){ iRow = ExtportUtil.writeRecordsWithNo(row, sheet, nColumn, cell, workbook, iRow, list); }else{ iRow = ExtportUtil.writeRecords(row, sheet, nColumn, cell, workbook, iRow, list); } // ------------------------- 注: ------------------- if(!gs.isEmpty()){ iRow = iRow + 1; row = sheet.createRow(iRow); HSSFCell ann = null; ann = row.createCell(0); HSSFCellStyle ann_sty = ExtportUtil.getStyle3(workbook); ann.setCellStyle(desc_sty); ann.setCellValue(new HSSFRichTextString(gs)); }else{ iRow = iRow + 1; } sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0,ths.length - 1)); } List result = new ArrayList(); result.add(iRow); result.add(workbook); return result; } public FxbgService getFxbgService() { return fxbgService; } public void setFxbgService(FxbgService fxbgService) { this.fxbgService = fxbgService; } public double getHczhdf() { return hczhdf; } public void setHczhdf(double hczhdf) { this.hczhdf = hczhdf; } public double getSclxxdf() { return sclxxdf; } public void setSclxxdf(double sclxxdf) { this.sclxxdf = sclxxdf; } public double getSjscfwdf() { return sjscfwdf; } public void setSjscfwdf(double sjscfwdf) { this.sjscfwdf = sjscfwdf; } public double getSjzldf() { return sjzldf; } public void setSjzldf(double sjzldf) { this.sjzldf = sjzldf; } public List getHczhdfList() { return hczhdfList; } public void setHczhdfList(List hczhdfList) { this.hczhdfList = hczhdfList; } public List getSclxxdfList() { return sclxxdfList; } public void setSclxxdfList(List sclxxdfList) { this.sclxxdfList = sclxxdfList; } public List getSjscfwdfList() { return sjscfwdfList; } public void setSjscfwdfList(List sjscfwdfList) { this.sjscfwdfList = sjscfwdfList; } public List getSjzldfList() { return sjzldfList; } public void setSjzldfList(List sjzldfList) { this.sjzldfList = sjzldfList; } public List getZhdfGradeList() { return zhdfGradeList; } public void setZhdfGradeList(List zhdfGradeList) { this.zhdfGradeList = zhdfGradeList; } public List getLxxGradeList() { return lxxGradeList; } public void setLxxGradeList(List lxxGradeList) { this.lxxGradeList = lxxGradeList; } public List getFwdfGradeList() { return fwdfGradeList; } public void setFwdfGradeList(List fwdfGradeList) { this.fwdfGradeList = fwdfGradeList; } public List getZldfGradeList() { return zldfGradeList; } public void setZldfGradeList(List zldfGradeList) { this.zldfGradeList = zldfGradeList; } public List getZbdbList() { return zbdbList; } public void setZbdbList(List zbdbList) { this.zbdbList = zbdbList; } public String getMsg() { return msg; } public void setMsg(String msg) { this.msg = msg; } public String getSd() { return sd; } public void setSd(String sd) { this.sd = sd; } public String getEd() { return ed; } public void setEd(String ed) { this.ed = ed; } public String getSsd() { return ssd; } public void setSsd(String ssd) { this.ssd = ssd; } public String getSed() { return sed; } public void setSed(String sed) { this.sed = sed; } public String getDs() { return ds; } public void setDs(String ds) { this.ds = ds; } }
4.
package com.wondersgroup.qyws.sjzk.action.sjhc; import java.io.IOException; import java.io.UnsupportedEncodingException; import java.net.URLEncoder; import java.text.SimpleDateFormat; import java.util.Calendar; import java.util.List; import java.util.Map; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; 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.ss.util.CellRangeAddress; import org.apache.struts2.ServletActionContext; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.context.annotation.Scope; import org.springframework.stereotype.Controller; import com.opensymphony.xwork2.ActionContext; import com.wondersgroup.qyws.sjzk.service.FxbgService; import com.wondersgroup.qyws.sjzk.util.Constants; @Controller() @Scope("prototype") public class ExportDwfxbgAction { @Autowired private FxbgService fxbgService; private List zhlxxdf; private List itbsList; private List zbdbList; private List sjzldfList; private List lxxGradeList; private List sjscfwdfList; private List zhdfGradeList; private List fwdfGradeList; private List zldfGradeList; private String sd = "";// 起始日期 - 查询条件 private String ed = "";// 截止日期 - 查询条件 private String ssd = "";// 起始日期 - 展示条件 private String sed = "";// 截止日期 - 展示条件 private String jgdm = ""; private String ds; private String msg; public String exportDwfxbg() throws UnsupportedEncodingException { ActionContext context = ActionContext.getContext(); ds = (String) context.getSession().get("ds"); HttpServletResponse response = (HttpServletResponse) context.get(ServletActionContext.HTTP_RESPONSE); response.setContentType("application/vnd.ms-excel"); String csd = ""; String ced = ""; if (sd.isEmpty()) { // 默认执行 int cha = ds.equals(Constants.CEN_INTER) ? 1 : 2; Calendar now = Calendar.getInstance(); now.add(Calendar.DATE, -cha); ssd = now.get(Calendar.YEAR)+"年"+(now.get(Calendar.MONTH) + 1)+"月1日"; sed = now.get(Calendar.YEAR)+"年"+(now.get(Calendar.MONTH) + 1)+"月"+(now.get(Calendar.DATE))+"日"; ced = new SimpleDateFormat("yyyyMMdd").format(now.getTime()); csd = ced.substring(0,6)+"01";//该月一号 ed = new SimpleDateFormat("yyyy-MM-dd").format(now.getTime()); sd = ed.substring(0,8)+"01"; } else { // 条件查询 String sm = ""; String se = ""; String em = ""; String ee = ""; sm = ExtportUtil.getValue(sd.substring(4, 6)); se = ExtportUtil.getValue(sd.substring(6, 8)); em = ExtportUtil.getValue(ed.substring(4, 6)); ee = ExtportUtil.getValue(ed.substring(6, 8)); ssd = sd.substring(0, 4)+"年"+sm+"月"+se+"日"; sed = ed.substring(0, 4)+"年"+em+"月"+ee+"日"; csd = sd.substring(0, 4)+sd.substring(5, 7)+sd.substring(8, 10); ced = ed.substring(0, 4)+ed.substring(5, 7)+ed.substring(8, 10); } Map vals = fxbgService.getDwFxbg(jgdm, csd, ced, ds); zhlxxdf = (List) vals.get("zhLxx"); itbsList = (List) vals.get("itbsList"); sjscfwdfList = (List) vals.get("sjscfwdfList"); sjzldfList = (List) vals.get("zlhcList"); zbdbList = (List) vals.get("zbdbList"); // 综合得分机构评级:[3,'jg1,jg2,jg3'] zhdfGradeList = (List) vals.get("zhdfGradeList"); // 数据上传连续性得分机构评级 lxxGradeList = (List) vals.get("lxxGradeList"); // 数据上传范围得分机构评级 fwdfGradeList = (List) vals.get("fwdfGradeList"); // 数据质量得分机构评级 zldfGradeList = (List) vals.get("zldfGradeList"); // 指标对比zbdbList zbdbList = (List) vals.get("zbdbList"); // 数据状况描述: msg = (String)vals.get("msg"); String xlsName = zhlxxdf.get(6)+"数据上传核查报告.xls"; // Excel文档对象,会把数据导入到这个文档 HSSFWorkbook workbook = new HSSFWorkbook(); response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(xlsName, "utf-8")); int iRow = 0;// 行下标,整个sheet的行标控制 workbook = transHczhdfListToExcel(workbook, iRow); ServletOutputStream fOut; try { fOut = response.getOutputStream(); workbook.write(fOut); fOut.flush(); fOut.close(); } catch (IOException e) { e.printStackTrace(); } return null; } private HSSFWorkbook transHczhdfListToExcel(HSSFWorkbook workbook, int iRow) { // 创建工作表(sheet) HSSFSheet sheet = workbook.createSheet(); workbook.setSheetName(0, zhlxxdf.get(6)+"数据上传核查报告");// 下标0为第一个sheet List datas = zhlxxdf; // ------------------------ 大标题 ---------------------------------- // 给sheet创建一个行 HSSFRow row = sheet.createRow(iRow);// iRow = 0 HSSFCell cell = null; cell = row.createCell(0);// 创建一个单元格 // 设置字体样式 HSSFCellStyle cellStyle1 = ExtportUtil.getStyle1(workbook); // 标题单元格样式 HSSFCellStyle style2 = ExtportUtil.getStyle2(workbook); // 二级标题单元格样式 HSSFCellStyle rq_sty = ExtportUtil.getStyle3(workbook); // 日期文本单元格样式 HSSFCellStyle desc_sty = ExtportUtil.getStyle3(workbook); // 描述文本单元格样式 HSSFCellStyle thStyle = ExtportUtil.getThStyle(workbook); // 表格标题单元格样式 cell.setCellStyle(cellStyle1); // 设置文件名第一行展示,这里题目名和sheet名一样 cell.setCellValue(new HSSFRichTextString(zhlxxdf.get(6)+"数据上传核查报告")); sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0, 4)); // ------------------------ 日期说明 ---------------------------------- iRow = iRow + 1; row = sheet.createRow(iRow); cell = row.createCell(0); rq_sty.setAlignment(HSSFCellStyle.ALIGN_CENTER); cell.setCellStyle(rq_sty); cell.setCellValue(new HSSFRichTextString(ssd+" — "+sed)); sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0, 4)); // 一、综合得分报告: iRow = iRow + 2; row = sheet.createRow(iRow); cell = row.createCell(0); cell.setCellStyle(style2); cell.setCellValue(new HSSFRichTextString("一、综合得分报告")); sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0, 3)); iRow = iRow + 1; setDescRow(iRow, row, cell, sheet, desc_sty, "本期数据质量核查综合得分为" + datas.get(0) + "分。 其中上传连续性得分为" + "" + datas.get(1) + "分。 上传范围得分为" + datas.get(2) + "分。 数据质量得分为" + datas.get(3) + "分。"); iRow = iRow + 1; setDescRow(iRow, row, cell, sheet, desc_sty, "注:得分 = 上传连续性得分*权重 + 上传表范围得分*权重 + 数据质量得分*权重"); // 二、上传连续性报告: iRow = iRow + 2; setDescRow(iRow, row, cell, sheet, style2, "二、上传连续性报告:"); iRow = iRow + 1; setDescRow(iRow, row, cell, sheet, desc_sty, "本期上传连续性得分" +datas.get(1) + "分。期内上传天数为" +datas.get(4)+ " 天,期内天数为" +datas.get(5)+ "天。"); iRow = iRow + 1; setDescRow(iRow, row, cell, sheet, desc_sty, "注:得分=期内上传数据天数/期内天数*100"); // 三、数据上传范围报告: iRow = iRow + 2; setDescRow(iRow, row, cell, sheet, style2, "三、上传表范围报告:"); // ------------------------ 数据表格 ---------------------------------- if (datas.get(2) == null) { iRow = iRow + 1; row = sheet.createRow(iRow); setDescRow(iRow, row, cell, sheet, desc_sty, "本期数据上传总条数为0条。应上传表范围如下:"); } else { iRow = iRow + 1; setDescRow(iRow, row, cell, sheet, desc_sty, "本期数据上传总条数为" +datas.get(7) + "条。应上传表范围如下((红色记录表示期内从未上传过数据的接口表)): "); iRow = iRow + 1; row = sheet.createRow(iRow); String[] ths1 = {"序号", "接口表", "接口表名称", "接口表类型" }; int nColumn1 = ths1.length; for (int i = 1; i <= nColumn1; i++) { sheet.setColumnWidth(i - 1, 20 * 256);// 设置单元格宽度:18字符 cell = row.createCell((i - 1)); cell.setCellStyle(thStyle); cell.setCellValue(new HSSFRichTextString(ths1[i - 1].toString())); } // --- 写入表格数据记录 --- iRow = iRow + 1; iRow = ExtportUtil.writeRecordsWithNo(row, sheet, nColumn1, cell, workbook, iRow, itbsList); // 本项得分为 58.25 分。详细列表如下: iRow = iRow + 1; setDescRow(iRow, row, cell, sheet, desc_sty, "本项得分为" +datas.get(2)+ "分。详细列表如下:"); iRow = iRow + 1; row = sheet.createRow(iRow); String[] ths2 = { "序号", "日期", "实际上传表范围", "应上传表范围", "得分" }; int nColumn2 = ths2.length; for (int i = 1; i <= nColumn2; i++) { sheet.setColumnWidth(i - 1, 20 * 256);// 设置单元格宽度:18字符 cell = row.createCell((i - 1)); cell.setCellStyle(thStyle); cell.setCellValue(new HSSFRichTextString(ths2[i - 1].toString())); } iRow = iRow + 1; iRow = ExtportUtil.writeRecordsWithNo(row, sheet, nColumn2, cell, workbook, iRow, sjscfwdfList); } iRow = iRow + 1; setDescRow(iRow, row, cell, sheet, desc_sty, "注:得分 = 实际上传表范围/应上传表范围*100"); // 四、数据质量报告: iRow = iRow + 2; setDescRow(iRow, row, cell, sheet, style2, "四、数据质量报告:"); iRow = iRow + 1; row = sheet.createRow(iRow); if (datas.get(0) == null) { setDescRow(iRow, row, cell, sheet, desc_sty, "详细列表如下:"); } else { String[] titles5 = { "序号","检查指标", "总条数", "错误数", "得分" }; int tlsz5 = titles5.length; for (int i = 1; i <= tlsz5; i++) { sheet.setColumnWidth(i - 1, 20 * 256);// 设置单元格宽度:18字符 cell = row.createCell((i - 1)); cell.setCellStyle(thStyle); cell.setCellValue(new HSSFRichTextString(titles5[i - 1].toString())); } iRow = iRow + 1; iRow = ExtportUtil.writeRecordsWithNo(row, sheet, tlsz5, cell, workbook, iRow, sjzldfList); } iRow = iRow + 1; setDescRow(iRow, row, cell, sheet, desc_sty, "注:分项得分=(数据总条数—错误数)/数据总条数*100"); iRow = iRow + 1; setDescRow(iRow, row, cell, sheet, desc_sty, "数据质量得分=字典核查分项得分*权重 + 非空核查分项得分*权重 + 关联核查分项得分*权重 + 可追溯性核查分项得分*权重"); // 五、业务指标核对报告: iRow = iRow + 2; setDescRow(iRow, row, cell, sheet, style2, "五、业务指标核对报告:"); String[] titles6 = { "数据来源\\指标", "门诊人次", "门诊总收入", "门急诊药品收入", "门诊检验人次", "门诊检查人次", "门诊手术人次", "出院人次", "出院病人床日数", "出院病人总收入", "出院病人药品收入", "住院检验人次", "住院检查人次", "住院手术人次" }; int tlsz6 = titles6.length; iRow = iRow + 1; row = sheet.createRow(iRow); for (int i = 1; i <= tlsz6; i++) { sheet.setColumnWidth(i - 1, 20 * 256);// 设置单元格宽度:18字符 cell = row.createCell(i - 1); cell.setCellStyle(thStyle); cell.setCellValue(new HSSFRichTextString(titles6[i - 1])); } iRow = iRow + 1; iRow = ExtportUtil.writeRecords(row, sheet, tlsz6, cell, workbook, iRow, zbdbList); // 六、评价与建议: iRow = iRow + 2; setDescRow(iRow, row, cell, sheet, style2, "六、评价与建议:"); iRow = iRow + 1; setDescRow(iRow,row,cell,sheet,desc_sty, msg); return workbook; } private void setDescRow(int iRow, HSSFRow row, HSSFCell cell, HSSFSheet sheet, HSSFCellStyle descSty, String string) { row = sheet.createRow(iRow); cell = row.createCell(0); cell.setCellStyle(descSty); cell.setCellValue(new HSSFRichTextString(string)); sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0, 4)); } public FxbgService getFxbgService() { return fxbgService; } public void setFxbgService(FxbgService fxbgService) { this.fxbgService = fxbgService; } public List getZhlxxdf() { return zhlxxdf; } public void setZhlxxdf(List zhlxxdf) { this.zhlxxdf = zhlxxdf; } public List getItbsList() { return itbsList; } public void setItbsList(List itbsList) { this.itbsList = itbsList; } public List getSjscfwdfList() { return sjscfwdfList; } public void setSjscfwdfList(List sjscfwdfList) { this.sjscfwdfList = sjscfwdfList; } public List getSjzldfList() { return sjzldfList; } public void setSjzldfList(List sjzldfList) { this.sjzldfList = sjzldfList; } public List getZbdbList() { return zbdbList; } public void setZbdbList(List zbdbList) { this.zbdbList = zbdbList; } public List getZhdfGradeList() { return zhdfGradeList; } public void setZhdfGradeList(List zhdfGradeList) { this.zhdfGradeList = zhdfGradeList; } public List getLxxGradeList() { return lxxGradeList; } public void setLxxGradeList(List lxxGradeList) { this.lxxGradeList = lxxGradeList; } public List getFwdfGradeList() { return fwdfGradeList; } public void setFwdfGradeList(List fwdfGradeList) { this.fwdfGradeList = fwdfGradeList; } public List getZldfGradeList() { return zldfGradeList; } public void setZldfGradeList(List zldfGradeList) { this.zldfGradeList = zldfGradeList; } public String getSd() { return sd; } public void setSd(String sd) { this.sd = sd; } public String getEd() { return ed; } public void setEd(String ed) { this.ed = ed; } public String getSsd() { return ssd; } public void setSsd(String ssd) { this.ssd = ssd; } public String getSed() { return sed; } public void setSed(String sed) { this.sed = sed; } public String getDs() { return ds; } public void setDs(String ds) { this.ds = ds; } public String getJgdm() { return jgdm; } public void setJgdm(String jgdm) { this.jgdm = jgdm; } public String getMsg() { return msg; } public void setMsg(String msg) { this.msg = msg; } }
相关推荐
这个实例提供了两种方法来导出Excel文件:通过Servlet和通过main方法。这两种方法都是在Java环境中操作Excel数据的有效方式。 首先,让我们详细了解一下Apache POI库。POI提供了一个API,允许开发者在Java应用程序...
"Excel通用导出excel文件"这个主题涵盖了如何在不同情况下高效地实现这一目标,不论你是要处理小规模的数据还是大规模的数据集。下面,我们将深入探讨Excel的通用导出功能,以及如何利用它来优化你的工作流程。 ...
"C# 导出Excel文件 打开Excel文件格式与扩展名指定格式不一致的解决办法" 在导出Excel文件时,经常会遇到“文件格式与扩展名指定格式不一致”的问题,这是因为大多数人在导出Excel文件时,都是默认保存Excel的格式...
本篇文章将深入探讨如何使用C#来创建和导出Excel文件。 首先,你需要一个库来处理Excel文件。Microsoft提供了`Microsoft.Office.Interop.Excel`命名空间,这是一个COM接口,可以直接与Excel应用程序交互。但是,...
本文实例为大家分享了Winform实现导入导出Excel文件的具体代码,供大家参考,具体内容如下 /// /// 导出Excel文件 /// /// /// <param name=dataSet></param> /// 数据集 /// 导出后是否打开文件 /// ...
QT导出EXCEL,从tableWidget列表获取数据导出到excel文件。导出过程使用了excel模板文件,可以从tableWidget读取固定的几列数据导出到excel文件中。其中excel事先设置好文件格式,后续直接读数写数即可。开发过程...
自定义的一个excel文件导入导出类,支持Excel2003到2010的格式。里面也有简单的配置文件的创建。
C#快速导出Excel文件,3秒可导10万行数据
导出Excel文件涉及到从数据库或内存中的数据生成新的Excel文件。这个过程与导入类似,但方向相反。 1. **创建Excel工作簿**:使用EPPlus创建一个新的ExcelPackage对象,然后添加工作簿和工作表。 2. **填充数据**...
easyexcel 导出excel文件带多个图片JAVA代码包
"Html导出Excel文件(兼容所有浏览器)"的主题着重于实现这个功能,并确保在各种不同的浏览器环境下都能正常工作。这通常涉及到前端技术与后端技术的结合,以及对浏览器兼容性的深入理解。 首先,我们要明白HTML表格...
在本示例"SSM框架利用poi导入导出Excel文件 demo"中,我们将探讨如何在SSM项目中使用Apache POI库来实现Excel文件的导入与导出功能。 Apache POI是一个流行的开源库,它允许开发者在Java应用程序中创建、修改和显示...
Visual C++源代码 194 如何使用剪贴板导出Excel文件Visual C++源代码 194 如何使用剪贴板导出Excel文件Visual C++源代码 194 如何使用剪贴板导出Excel文件Visual C++源代码 194 如何使用剪贴板导出Excel文件Visual ...
Visual C++源代码 150 如何以数据源方式导出Excel文件Visual C++源代码 150 如何以数据源方式导出Excel文件Visual C++源代码 150 如何以数据源方式导出Excel文件Visual C++源代码 150 如何以数据源方式导出Excel文件...
Visual C++源代码 195 如何使用自动化导出Excel文件Visual C++源代码 195 如何使用自动化导出Excel文件Visual C++源代码 195 如何使用自动化导出Excel文件Visual C++源代码 195 如何使用自动化导出Excel文件Visual ...
在MVC架构下,处理导入和导出Excel文件是常见的需求,尤其在数据管理、报表生成以及数据交换场景中。下面我们将详细探讨如何在MVC应用中实现这个功能。 首先,导入Excel文件涉及到读取Excel数据并将其转化为可操作...
在Asp.net开发中,有时候我们需要将数据导出到Excel文件以便用户下载或者进行进一步的数据处理。... 1. **导出CSV文件** ...通过实践,开发者可以更好地掌握在Asp.net中导出Excel文件的技巧,提高项目开发效率。
本项目“poi多线程大数据导出excel文件”提供了一个解决方案,利用多线程来提高Excel的大数据导出效率。 Apache POI 3.1版本是较早的版本,而项目中使用了更新的4.1版本,这意味着它可能利用了更多优化和新特性。在...
Java实现导出Excel文件和XML文件 Java实现导出Excel文件和XML文件是Java语言中的一种常见需求,本文将详细介绍如何使用Java实现导出Excel文件和XML文件的功能。 一、功能简述 点击“导出”按钮,弹出文件选择...