首页 / JAVA / java poi生成数据透视表
java poi生成数据透视表
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了java poi生成数据透视表，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8490字，纯文字阅读大概需要13分钟。
内容图文
/**
* 导出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 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】