java实现报表导出_java 实现导出excel报表

最新推荐文章于 2024-07-03 14:13:18 发布

飞傲James

最新推荐文章于 2024-07-03 14:13:18 发布

阅读量633

点赞数

文章标签： java实现报表导出

本文链接：https://blog.csdn.net/weixin_30240263/article/details/114092236

版权

import org.apache.poi.hssf.usermodel.*;importorg.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.*;importorg.apache.poi.ss.util.CellRangeAddress;importorg.apache.poi.xssf.streaming.SXSSFWorkbook;importorg.apache.poi.xssf.usermodel.XSSFRichTextString;importjava.lang.reflect.Field;importjava.lang.reflect.InvocationTargetException;importjava.lang.reflect.Method;importjava.text.SimpleDateFormat;import java.util.*;/*** excel工具类

*@authorwangkecheng

*@param*/

public class ExcelUtil{public static final int EXPORT_07_LEAST_SIZE = 50000;/*** 功能 :获取表单导出数据

* 开发：wangkecheng

*@paramlist 数据列表

*@paramtitle 首行标题

*@paramclassName 实体对象class

*@paramexportType 模板标号

*@return*@throwsException*/

public HSSFWorkbook exportExcel(List list, String title, Class className,Integer exportType) throwsException {//获取属性

Field[] fields =className.getDeclaredFields();

List fieldList = new ArrayList();for(Field fie : fields) {if (fie.isAnnotationPresent(ExcelAnnotation.class)) {

fieldList.add(fie);

}

}//按照id进行排序

Collections.sort(fieldList, new Comparator() {public intcompare(Field f1, Field f2) {return f1.getAnnotation(ExcelAnnotation.class).id() - f2.getAnnotation(ExcelAnnotation.class).id();

}

});int columnsize = fieldList.size(), rowindex = 0;//创建一个HSSFWorbook对象(excel的文档对象)

HSSFWorkbook hWorkbook = newHSSFWorkbook();//创建一个HSSFSheet对象(excll的表单)

HSSFSheet hSheet =hWorkbook.createSheet();//创建行(excel的行)

HSSFRow hRow = hSheet.createRow(rowindex++);//设置行高度

hRow.setHeight((short)380);//创建单元格(从0开始)

HSSFCell hCell = hRow.createCell((short) 0);//样式对象

HSSFCellStyle cellStyle = getCellStyle(hWorkbook, (short) 300, (short) 500);//将上面获得的样式对象给对应单元格

hCell.setCellStyle(cellStyle);//设置标题行

hCell.setCellValue(title);if (getHuoResult(fieldList.isEmpty(),list == null,list.isEmpty())) {returnhWorkbook;

}//创建第二行，代表列名

hRow = hSheet.createRow(rowindex++);

cellStyle= getCellStyle(hWorkbook, (short) 270, (short) 500);

generateTitle(exportType, fieldList, columnsize, hSheet, hRow, cellStyle);//组装excel的数据

cellStyle = getCellStyle(hWorkbook, (short) 220, (short) 500);//设置单元格格式

generateData(list, fieldList, columnsize, rowindex, hSheet, cellStyle);/*** 第1个参数：从哪一行开始

* 第2个参数：到哪一行结束

* 第3个参数：从哪一列开始

* 第4个参数：到哪一列结束*/hSheet.addMergedRegion(new CellRangeAddress(0,0,0,columnsize-1));//固定表头(前一个参数代表列，后一个参数单表行)

hSheet.createFreezePane(0, 1);returnhWorkbook;

}/*** 功能：組裝列明

*@paramexportType 模板编号

*@paramfieldList 列名

*@paramcolumnsize 列数

*@paramhSheet sheet页

*@paramhRow 行

*@paramcellStyle 样式*/

private void generateTitle(Integer exportType, List fieldList, intcolumnsize, HSSFSheet hSheet, HSSFRow hRow,

HSSFCellStyle cellStyle) {

HSSFCell hCell;for (int i = 0; i < columnsize; i++) {

Field field=fieldList.get(i);if (field.isAnnotationPresent(ExcelAnnotation.class)) {//获取该字段的注解对象

ExcelAnnotation anno = field.getAnnotation(ExcelAnnotation.class);

hCell= hRow.createCell((short) i);

String colName= field.getAnnotation(ExcelAnnotation.class).name().length>exportType?field.getAnnotation(ExcelAnnotation.class).name()[exportType]

:field.getAnnotation(ExcelAnnotation.class).name()[0];

hCell.setCellValue(colName);

hCell.setCellStyle(cellStyle);

hSheet.setColumnWidth((short) i, (short) anno.width());

}

}/*** 组装excel的数据

*@paramlist 具体数据

*@paramfieldList 列名

*@paramcolumnsize 列数

*@paramrowindex 行数计数

*@paramhSheet sheet页

*@paramcellStyle 样式

*@return*@throwsNoSuchMethodException

*@throwsIllegalAccessException

*@throwsInvocationTargetException*/

private int generateData(List list, List fieldList, int columnsize, introwindex, HSSFSheet hSheet,

HSSFCellStyle cellStyle)throwsNoSuchMethodException, IllegalAccessException, InvocationTargetException {

HSSFRow hRow;

HSSFCell hCell;for(Object model : list) {

hRow= hSheet.createRow(rowindex++);//获取该类

Class clazz =model.getClass();for (int i = 0; i < columnsize; i++) {

Field field=fieldList.get(i);//获取方法名

String methodName = "get" + field.getName().substring(0, 1).toUpperCase()+field.getName().substring(1);

Method method=clazz.getMethod(methodName);try{//获取该字段的注解对象

Object result =method.invoke(model);

hCell= hRow.createCell((short) i);if (result != null) {if (result.getClass().isAssignableFrom(Date.class)) {

SimpleDateFormat format= new SimpleDateFormat("yyyy年MM月dd日");

result=format.format(result);

}

hCell.setCellValue(newHSSFRichTextString(result.toString()));

}else{

hCell.setCellValue(new HSSFRichTextString("-"));

}

hCell.setCellStyle(cellStyle);

}catch(IllegalArgumentException e) {

System.out.println(e.getMessage());

}

}returnrowindex;

}/*** 生成07格式的excel对象使用流方式防止内存溢出

*@paramlist

*@paramtitle

*@paramclassName

*@paramexportType

*@return*@throwsException*/

public SXSSFWorkbook exportExcel07S(List list, String title, Class className,Integer exportType) throwsException {//获取属性

Field[] fields =className.getDeclaredFields();

List fieldList = new ArrayList();for(Field fie : fields) {if (fie.isAnnotationPresent(ExcelAnnotation.class)){

fieldList.add(fie);

}

}//按照id进行排序

Collections.sort(fieldList, new Comparator() {public intcompare(Field f1, Field f2) {return f1.getAnnotation(ExcelAnnotation.class).id() - f2.getAnnotation(ExcelAnnotation.class).id();

}

});int columnsize = fieldList.size(), rowindex = 0;//创建一个HSSFWorbook对象s

SXSSFWorkbook hWorkbook = newSXSSFWorkbook();//创建一个HSSFSheet对象(sheet页)

Sheet hSheet =hWorkbook.createSheet();//创建第一行(此行作为头)

Row hRow = hSheet.createRow(rowindex++);

hRow.setHeight((short)380);//创建单元格(第一(0)个)

Cell hCell = hRow.createCell((short) 0);//设置样式

CellStyle cellStyle = getCellStyle07S(hWorkbook, (short) 300, (short) 500);//将上面获得的样式对象给对应单元格

hCell.setCellStyle(cellStyle);//设置标题行

hCell.setCellValue(title);if (getHuoResult(fieldList.isEmpty(),list == null,list.isEmpty())) {returnhWorkbook;

}//创建第二列，列名

hRow = hSheet.createRow(rowindex++);

cellStyle= getCellStyle07S(hWorkbook, (short) 270, (short) 500);

createTitle07S(exportType, fieldList, columnsize, hSheet, hRow, cellStyle);//生成数据

cellStyle = getCellStyle07S(hWorkbook, (short) 220, (short) 500);//设置单元格格式

dealCreateRow07S(list, fieldList, columnsize, rowindex, hSheet, cellStyle);/*** 第1个参数：从哪一行开始

* 第2个参数：到哪一行结束

* 第3个参数：从哪一列开始

* 第4个参数：到哪一列结束*/hSheet.addMergedRegion(new CellRangeAddress(0,0,0,columnsize-1));//固定表头(前一个参数代表列，后一个参数单表行)

hSheet.createFreezePane(0, 1);returnhWorkbook;

}private int dealCreateRow07S(List list, List fieldList, int columnsize, introwindex, Sheet hSheet,

CellStyle cellStyle)throwsNoSuchMethodException, IllegalAccessException, InvocationTargetException {

Row hRow;

Cell hCell;for(Object model : list) {

hRow= hSheet.createRow(rowindex++);//获取该类并获取自身方法

Class clazz =model.getClass();for (int i = 0; i < columnsize; i++) {

Field field=fieldList.get(i);

String methodName= "get" + field.getName().substring(0, 1).toUpperCase()+ field.getName().substring(1);

Method method=clazz.getMethod(methodName);try{//获取该字段的注解对象

Object result =method.invoke(model);

hCell= hRow.createCell((short) i);if (result != null) {if (result.getClass().isAssignableFrom(Date.class)) {

SimpleDateFormat format= new SimpleDateFormat("yyyy年MM月dd日");

result=format.format(result);

}

hCell.setCellValue(newXSSFRichTextString(result.toString()));

}else{

hCell.setCellValue(new XSSFRichTextString("-"));

}

hCell.setCellStyle(cellStyle);

}catch(IllegalArgumentException e) {

System.out.println(e.getMessage());

}

}returnrowindex;

}/*** 生成列名

*@paramexportType 模板编号

*@paramfieldList 列名

*@paramcolumnsize 列数

*@paramhSheet

*@paramhRow

*@paramcellStyle*/

private void createTitle07S(Integer exportType, List fieldList, intcolumnsize, Sheet hSheet, Row hRow,

CellStyle cellStyle) {

Cell hCell;for (int i = 0; i < columnsize; i++) {

Field field=(Field) fieldList.get(i);if (field.isAnnotationPresent(ExcelAnnotation.class)) {//获取该字段的注解对象

ExcelAnnotation anno = field.getAnnotation(ExcelAnnotation.class);

hCell= hRow.createCell((short) i);

String colName= field.getAnnotation(ExcelAnnotation.class).name().length>exportType?field.getAnnotation(ExcelAnnotation.class).name()[exportType]

:field.getAnnotation(ExcelAnnotation.class).name()[0];

hCell.setCellValue(colName);

hCell.setCellStyle(cellStyle);

hSheet.setColumnWidth((short) i, (short) anno.width());

}

}/*** 功能 :设置excel表格默认样式

*@paramhWorkbook 需导出Excel数据

*@paramfontHeight 字体粗度

*@paramboldWeight 表格线的粗度

*@return

public HSSFCellStyle getCellStyle(HSSFWorkbook hWorkbook, short fontHeight, shortboldWeight) {

HSSFCellStyle cellStyle;

HSSFFont font;

cellStyle=hWorkbook.createCellStyle();

cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

cellStyle.setTopBorderColor(HSSFColor.BLACK.index);

cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);

cellStyle.setRightBorderColor(HSSFColor.BLACK.index);

cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);

font=hWorkbook.createFont();

font.setFontHeight(fontHeight);

font.setBoldweight(boldWeight);

font.setFontName("宋体");

cellStyle.setFont(font);

cellStyle.setWrapText(true);

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);returncellStyle;

}/*** 功能 :设置excel 07表格默认样式

*@paramhWorkbook 需导出Excel数据

*@paramfontHeight 字体粗度

*@paramboldWeight 表格线的粗度

*@return

public CellStyle getCellStyle07S(SXSSFWorkbook hWorkbook, short fontHeight, shortboldWeight) {

CellStyle cellStyle;

Font font;

cellStyle=hWorkbook.createCellStyle();

cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

cellStyle.setTopBorderColor(HSSFColor.BLACK.index);

cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);

cellStyle.setRightBorderColor(HSSFColor.BLACK.index);

cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);

font=hWorkbook.createFont();

font.setFontHeight(fontHeight);

font.setBoldweight(boldWeight);

font.setFontName("宋体");

cellStyle.setFont(font);

cellStyle.setWrapText(true);

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);returncellStyle;

}/** 获取或运算结果*/

private static booleangetHuoResult(Boolean... bs){for(booleanb:bs){if(b){returnb;

}

}return false;

}

飞傲James

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
java实现报表导出_java 实现导出excel报表

import org.apache.poi.hssf.usermodel.*;importorg.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.*;importorg.apache.poi.ss.util.CellRangeAddress;importorg.apache.poi.xssf.streaming.S...
复制链接

扫一扫