首页 / JAVA / java poi生成数据透视表
java poi生成数据透视表
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了java poi生成数据透视表,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含8490字,纯文字阅读大概需要13分钟。
内容图文
![java poi生成数据透视表](/upload/InfoBanner/zyjiaocheng/1078/293f0b9a008b40999734fc45c0c9e8d8.jpg)
/** * 导出EXCEL---poi */ @Override public void pivot(HttpServletResponse response) throws Exception{ //创建excel在内存中 .xls Workbook wb = new XSSFWorkbook(); //创建一个sheet页 Sheet sheet = wb.createSheet("患者信息一览表"); ////获取单元格格式信息 ExcelCellPojo pojo = new ExcelCellPojo(wb); //表头 String[] headStr={"id","入ICU时间","出院时间","转科时间","诊断情况","转归","呼吸机使用","高流量","PICCO","ECMO","CRRT"}; //开始的行号int rowIndex=0; //创建标题栏 Row title =sheet.createRow(rowIndex); //设置行高 title.setHeightInPoints(75); title.createCell(0).setCellValue("患者信息一览表"); title.getCell(0).setCellStyle(pojo.getTitleCellStyle()); //创建合并单元格 CellRangeAddress cra=new CellRangeAddress(0,0,0,headStr.length-1); sheet.addMergedRegion(cra); // 下边框 RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet); // 左边框 RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet); // 有边框 RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet); // 上边框 RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet); //创建表头的一行 Row header = sheet.createRow(++rowIndex); //设置行高 header.setHeightInPoints(60); //循环创建表头赋值for (int i = 0; i < headStr.length; i++) { //设置列宽度 -第1列 sheet.setColumnWidth(i,5400); //创建表头第一列并且赋值 header.createCell(i).setCellValue(headStr[i]); //设置单元格样式 header.getCell(i).setCellStyle(pojo.getHeaderStyle()); } //查询数据 List<TestPatientInf> patientList = super.selectAll(); for (TestPatientInf patientInf:patientList) { ++rowIndex; //从第2行开始创建 Row row = sheet.createRow(rowIndex); row.setHeightInPoints(40); row.createCell(0).setCellValue(patientInf.getId()); row.getCell(0).setCellStyle(pojo.getCellStyle()); row.createCell(1).setCellValue(patientInf.getIcuTimeIn()); row.getCell(1).setCellStyle(pojo.getDateCellStyle()); row.createCell(2).setCellValue(patientInf.getDischargeTime()); row.getCell(2).setCellStyle(pojo.getDateCellStyle()); row.createCell(3).setCellValue(patientInf.getCollegeTime()); row.getCell(3).setCellStyle(pojo.getDateCellStyle()); row.createCell(4).setCellValue(patientInf.getDiagnosis()); row.getCell(4).setCellStyle(pojo.getCellStyle()); row.createCell(5).setCellValue(patientInf.getOutcome()); row.getCell(5).setCellStyle(pojo.getCellStyle()); row.createCell(6).setCellValue(StringUtil.isNullOrBlank(patientInf.getHxjsy())?"无":patientInf.getHxjsy()); row.getCell(6).setCellStyle(pojo.getCellStyle()); row.createCell(7).setCellValue(StringUtil.isNullOrBlank(patientInf.getGll())?"无":patientInf.getGll()); row.getCell(7).setCellStyle(pojo.getCellStyle()); row.createCell(8).setCellValue(patientInf.getPicco()==null?0:patientInf.getPicco()); row.getCell(8).setCellStyle(pojo.getDoubleCellStyle()); row.createCell(9).setCellValue(patientInf.getEcmo()==null?0:patientInf.getEcmo()); row.getCell(9).setCellStyle(pojo.getCellStyle()); row.createCell(10).setCellValue(patientInf.getCrrt()==null?0:patientInf.getCrrt()); row.getCell(10).setCellStyle(pojo.getCellStyle()); } //创建数据透视表--新的sheet页码 //为需要汇总和创建分析的数据创建缓存 XSSFSheet pivotSheet = (XSSFSheet)wb.createSheet("患者信息透视表"); //左边起始单元格 CellReference leftStart=new CellReference(ExcelUtils.getCellLocation(2,1)); //右边结束单元格 CellReference rightEnd=new CellReference(ExcelUtils.getCellLocation(rowIndex+1,headStr.length)); // 数据透视表生产的起点单元格位置 CellReference ptStartCell = new CellReference("A4"); //创建数据透视表格 AreaReference area = new AreaReference(leftStart, rightEnd, SpreadsheetVersion.EXCEL2007); XSSFPivotTable pivotTable = pivotSheet.createPivotTable(area,ptStartCell,sheet); //头上的列标签 pivotTable.addColLabel(1); //透视表 列值 pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 0,"计数项:入ICU时间"); pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 8,"求和项:PICCO"); //透视表 行标签 pivotTable.addRowLabel(0); //透视表 行的值 pivotTable.addRowLabel(1); pivotTable.addRowLabel(2); pivotTable.addRowLabel(3); pivotTable.addRowLabel(4); ExcelUtils.exportExcel(wb,"test_aaa.xlsx",response); }
package cn.com.zhengya.framework.utils.excel.entity; import org.apache.poi.hssf.usermodel.HSSFDataFormat; import org.apache.poi.ss.usermodel.*; /** * Excel默认样式 * @author luwl */publicclass ExcelCellPojo { /** * 表头样式 */private CellStyle headerStyle; /** * 默认单元格样式 */private CellStyle cellStyle; /** * 标题栏样式 */private CellStyle titleCellStyle; /** * 日期单元格样式 */private CellStyle dateCellStyle; /** * 数字类型单元格样式 */private CellStyle doubleCellStyle; public ExcelCellPojo(Workbook wb){ this.setHeaderStyle(wb); this.setCellStyle(wb); this.setDateCellStyle(wb); this.setTitleCellStyle(wb); this.setDoubleCellStyle(wb); } public CellStyle getHeaderStyle() { return headerStyle; } public CellStyle getCellStyle() { return cellStyle; } public CellStyle getDateCellStyle() { return dateCellStyle; } public CellStyle getTitleCellStyle() { return titleCellStyle; } public CellStyle getDoubleCellStyle() { return doubleCellStyle; } privatevoid setHeaderStyle(Workbook wb){ // 生成表头单元样式 headerStyle = wb.createCellStyle(); //表头样式 headerStyle.setFillForegroundColor((short)1); headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //表头边框 headerStyle.setBorderBottom(BorderStyle.THIN); headerStyle.setBorderLeft(BorderStyle.THIN); headerStyle.setBorderRight(BorderStyle.THIN); headerStyle.setBorderTop(BorderStyle.THIN); //水平方向-居中对齐 headerStyle.setAlignment(HorizontalAlignment.CENTER); //垂直方向-垂直居中 headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); //字体 Font font = wb.createFont(); //字体大小 font.setFontHeightInPoints((short) 16); //字体加粗 font.setBold(true); headerStyle.setFont(font); } privatevoid setCellStyle(Workbook wb){ // 生成单元格式样(基础式样) cellStyle = wb.createCellStyle(); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); //水平方向-居中对齐 cellStyle.setAlignment(HorizontalAlignment.CENTER); //垂直方向-垂直居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setWrapText(true); } privatevoid setDateCellStyle(Workbook wb){ // 生成单元格式样-日期类型(yyyy/mm/dd日期格式) dateCellStyle = wb.createCellStyle(); dateCellStyle.cloneStyleFrom(this.cellStyle); DataFormat format = wb.createDataFormat(); dateCellStyle.setDataFormat(format.getFormat("yyyy/mm/dd")); } privatevoid setTitleCellStyle(Workbook wb) { //生成标题单元格样式 titleCellStyle = wb.createCellStyle(); //表头样式 titleCellStyle.setFillForegroundColor((short)1); titleCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //表头边框 titleCellStyle.setBorderBottom(BorderStyle.THIN); titleCellStyle.setBorderLeft(BorderStyle.THIN); titleCellStyle.setBorderRight(BorderStyle.THIN); titleCellStyle.setBorderTop(BorderStyle.THIN); //水平方向-居中对齐 titleCellStyle.setAlignment(HorizontalAlignment.CENTER); //垂直方向-垂直居中 titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //字体 Font font = wb.createFont(); //字体大小 font.setFontHeightInPoints((short) 20); titleCellStyle.setFont(font); } privatevoid setDoubleCellStyle(Workbook wb) { // 生成单元格式样(基础式样) doubleCellStyle = wb.createCellStyle(); doubleCellStyle.setBorderBottom(BorderStyle.THIN); doubleCellStyle.setBorderLeft(BorderStyle.THIN); doubleCellStyle.setBorderRight(BorderStyle.THIN); doubleCellStyle.setBorderTop(BorderStyle.THIN); //水平方向-居中对齐 doubleCellStyle.setAlignment(HorizontalAlignment.CENTER); //垂直方向-垂直居中 doubleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); doubleCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00")); doubleCellStyle.setWrapText(true); } }
/** * Http导出Excel * @param response */ public static void exportExcel(Workbook wb,String fileName,HttpServletResponse response){ response.reset(); response.setContentType("application/vnd.ms-excel;charset=utf-8"); OutputStream out = null; try { // 通过流将excel写出 ByteArrayOutputStream bos = new ByteArrayOutputStream(); wb.write(bos); byte[] bytes = bos.toByteArray(); // 获取输出流 out = response.getOutputStream(); // 设置头信息 response.setContentLength(bytes.length); response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("GBK"), "ISO8859-1")); // 通过流将excel写出 wb.write(out); wb.close(); out.flush(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (out != null) { out.close(); } } catch (IOException e) { e.printStackTrace(); } } }
<!--poi--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.0.1</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.0.1</version> </dependency>
原文:https://www.cnblogs.com/Sora-L/p/13220596.html
内容总结
以上是互联网集市为您收集整理的java poi生成数据透视表全部内容,希望文章能够帮你解决java poi生成数据透视表所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。