/**
* 导出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 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
*/
public class 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;
}
private void 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);
}
private void 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);
}
private void setDateCellStyle(Workbook wb){
// 生成单元格式样-日期类型（yyyy/mm/dd日期格式）
dateCellStyle = wb.createCellStyle();
dateCellStyle.cloneStyleFrom(this.cellStyle);
DataFormat format = wb.createDataFormat();
dateCellStyle.setDataFormat(format.getFormat("yyyy/mm/dd"));
}
private void 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);
}
private void 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();
}
}
}
org.apache.poi
poi
4.0.1
org.apache.poi
poi-ooxml
4.0.1
Original: https://www.cnblogs.com/Sora-L/p/13220596.html
Author: 雨梦大木
Title: java poi生成数据透视表
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/620276/
转载文章受原作者版权保护。转载请注明原作者出处！