Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $
You can buy this project and download/modify it how often you want.
com.github.zzlhy.main.ExcelExport Maven / Gradle / Ivy
package com.github.zzlhy.main;
import com.github.zzlhy.entity.*;
import com.github.zzlhy.entity.Color;
import com.github.zzlhy.func.ConvertValue;
import com.github.zzlhy.func.GeneratorDataHandler;
import com.github.zzlhy.util.Lists;
import com.github.zzlhy.util.Utils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
* Excel 导出 (支持大量数据导出)
* Created by Administrator on 2018-10-11.
*/
public class ExcelExport {
/**
* 数据导出 -- 对象方式 (说明:普通导出,数据量较少情况导出)
* @param tableParam Excel参数对象
* @param data data
* @param exportStyle 单元格样式实现
* @return Workbook
* @throws InvocationTargetException e
* @throws IllegalAccessException e
* @throws IntrospectionException e
*/
public static Workbook exportExcelByObject(TableParam tableParam, List data,ExportStyle exportStyle) throws InvocationTargetException, IllegalAccessException, IntrospectionException {
/*创建Workbook和Sheet*/
Workbook workbook;
if(ExcelType.XLSX.equals(tableParam.getExcelType())){
workbook = new XSSFWorkbook();
}else if(ExcelType.XLS.equals(tableParam.getExcelType())){
workbook = new HSSFWorkbook();
}else {
workbook = new SXSSFWorkbook(100);
}
/*创建Workbook和Sheet*/
Sheet sheet = workbook.createSheet(tableParam.getSheetName());//创建工作表(Sheet)
//冻结列
sheet.createFreezePane(tableParam.getFreezeColSplit(),tableParam.getFreezeRowSplit());
//合并单元格
if(tableParam.getMergeRegion() != null && tableParam.getMergeRegion().size() > 0){
for (CellRangeAddress cellAddresses : tableParam.getMergeRegion()) {
sheet.addMergedRegion(cellAddresses);
}
}
//开始行
Integer writeRow = tableParam.getWriteRow();
//标题行设置、下拉列表设置
rowConfig(workbook,sheet,tableParam);
//样式对象创建,可复用
for (int j=0;j> data,ExportStyle exportStyle) throws InvocationTargetException, IllegalAccessException, IntrospectionException {
/*创建Workbook和Sheet*/
Workbook workbook;
if(ExcelType.XLSX.equals(tableParam.getExcelType())){
workbook = new XSSFWorkbook();
}else if(ExcelType.XLS.equals(tableParam.getExcelType())){
workbook = new HSSFWorkbook();
}else {
workbook = new SXSSFWorkbook(100);
}
/*创建Workbook和Sheet*/
Sheet sheet = workbook.createSheet(tableParam.getSheetName());//创建工作表(Sheet)
//冻结列
sheet.createFreezePane(tableParam.getFreezeColSplit(),tableParam.getFreezeRowSplit());
//合并单元格
if(tableParam.getMergeRegion() != null && tableParam.getMergeRegion().size() > 0){
for (CellRangeAddress cellAddresses : tableParam.getMergeRegion()) {
sheet.addMergedRegion(cellAddresses);
}
}
//开始行
Integer writeRow = tableParam.getWriteRow();
//标题行设置、下拉列表设置
rowConfig(workbook,sheet,tableParam);
//样式对象创建,可复用
for (int j=0;j sheetDataTotal){
//计算需要创建sheet的个数
sheetCount = total/sheetDataTotal;
if(total%sheetDataTotal != 0L){
sheetCount+=1;
}
}
//分页查询时的每页条数,固定1000条,外部查询时也需要每次为1000条的返回
int pageSize = 1000;
//总页数计算
long page = total / pageSize;
//当前页码
int currentPage = 0;
//样式对象创建,可复用
for (int j=0;j 0){
for (CellRangeAddress cellAddresses : tableParam.getMergeRegion()) {
sheet.addMergedRegion(cellAddresses);
}
}
//开始写入的行号
Integer writeRow = tableParam.getWriteRow();
//标题行设置
rowConfig(workbook,sheet,tableParam);
//当前数据处理到的行数,开始时为标题行的下一行（每个sheet重新计算）
int currentRow = writeRow+1;
//本次执行了多少页
int index = 0;
List list;
//开始循环加入数据(分页查询数据)
for (int i = currentPage; i <= page; i++) {
list = generatorDataHandler.generatorData(i,pageSize);
int tempRow = addRows(workbook, sheet, currentRow, tableParam,exportStyle, list);
list.clear();
currentRow = tempRow;
//如果超过了限制的行数则跳出,进行新的sheet写入
if(currentRow >= sheetDataTotal ){
break;
}
index++;
}
currentPage+=index;
}
return workbook;
}
/**
* 新增数据行 说明：传入的是对象集合
* @param workbook workbook
* @param sheet sheet
* @param currentRow 当前处理到的行号
* @param tableParam 配置参数
* @param exportStyle 单元格样式实现
* @param data 数据
* @return 当前处理到的行号
* @throws InvocationTargetException e
* @throws IllegalAccessException e
* @throws IntrospectionException e
*/
private static int addRows(Workbook workbook,Sheet sheet, int currentRow, TableParam tableParam, ExportStyle exportStyle, List data) throws InvocationTargetException, IllegalAccessException, IntrospectionException {
//创建数据
for(int k = 0; k propertyType = propertyDescriptor.getPropertyType();
//执行
Object result = readMethod.invoke(data.get(k));
String format = tableParam.getCols().get(j).getFormat();//获取日期的格式化的格式
ConvertValue convertValue = tableParam.getCols().get(j).getConvertValue();//需要转换值的方法对象
setCell(cell,result,tableParam.getCols().get(j));
}
currentRow++;
}
return currentRow;
}
/**
* 新增数据行 说明：传入的是Map集合
* @param sheet sheet
* @param currentRow 当前处理到的行号(从哪行开始创建行)
* @param tableParam 配置参数
* @param exportStyle 单元格样式实现
* @param data 数据
* @return 当前处理到的行号
* @throws InvocationTargetException e
* @throws IllegalAccessException e
* @throws IntrospectionException e
*/
private static int addRowsByMap(Workbook workbook, Sheet sheet, int currentRow, TableParam tableParam, ExportStyle exportStyle, List> data) throws InvocationTargetException, IllegalAccessException, IntrospectionException {
//创建数据
for(int k = 0; k cols = tableParam.getCols();
//标题配置
if(tableParam.getCreateHeadRow()){
/*创建标题行*/
Row row = sheet.createRow(tableParam.getWriteRow());
//标题行样式
CellStyle style = workbook.createCellStyle();
Font font = workbook.createFont();
//加粗
font.setBold(tableParam.getHeadRowStyle().getHeadBold());
style.setFont(font);
//居中
style.setAlignment(tableParam.getHeadRowStyle().getHorizontalAlignment());
for(int i=0;i dropdownParams = new ArrayList();
for (int i = 0; i < cols.size(); i++) {
if(cols.get(i).getDropdownList() != null){
DropdownParam temp = new DropdownParam(tableParam.getWriteRow(),200,i,i,cols.get(i).getDropdownList());
dropdownParams.add(temp);
}
//设置列宽
sheet.setColumnWidth(i,tableParam.getCols().get(i).getWidth()*256);
}
if(dropdownParams.size() > 0) {
if (workbook instanceof XSSFWorkbook) {
setDropDownList((XSSFSheet) sheet, dropdownParams);
} else if (workbook instanceof HSSFWorkbook) {
setDropDownList((HSSFSheet) sheet, dropdownParams);
}
}
}
//默认日期转换格式
private static final String FORMAT="yyyy-MM-dd";
private static final String FORMAT2="yyyy-MM-dd HH:mm:ss";
private static SimpleDateFormat sdfDate= new SimpleDateFormat(FORMAT);
private static SimpleDateFormat sdfTime= new SimpleDateFormat(FORMAT2);
/**
* 单元格cell值设置
* @param cell cell 列
* @param result result 值
* @param col 列s属性对象
*/
private static void setCell(Cell cell, Object result, Col col){
//设置单元格值及属性
String resultStr = String.valueOf(result);
if(result instanceof String){
if(result != null){
cell.setCellValue(resultStr);
}else{
String empty = null;
cell.setCellValue(empty);
}
}else if(result instanceof Integer){
if(result != null) {
cell.setCellValue(Integer.parseInt(resultStr));
}else{
Integer empty = null;
cell.setCellValue(empty);
}
}else if(result instanceof Double){
if(result != null) {
cell.setCellValue(Double.parseDouble(resultStr));
}else{
Double empty = null;
cell.setCellValue(empty);
}
}else if(result instanceof Short){
if(result != null) {
//如果需要值替换
if (col.getConvertValue() != null) {
String val = col.getConvertValue().convert(result);
cell.setCellValue(val);
} else {
cell.setCellValue(Short.parseShort(resultStr));
}
}else{
Short empty = null;
cell.setCellValue(empty);
}
}else if(result instanceof Long){
if(result != null) {
cell.setCellValue(Long.parseLong(resultStr));
}else{
Long empty = null;
cell.setCellValue(empty);
}
}else if(result instanceof Float){
if(result != null) {
cell.setCellValue(Float.parseFloat(resultStr));
}else{
Float empty = null;
cell.setCellValue(empty);
}
}else if(result instanceof BigDecimal){
if(result != null) {
cell.setCellValue(Double.parseDouble(resultStr));
}else{
Double empty = null;
cell.setCellValue(empty);
}
}else if(result instanceof Boolean){
if(result != null) {
//如果需要值替换
if (col.getConvertValue() != null) {
String val = col.getConvertValue().convert(result);
cell.setCellValue(val);
} else {
cell.setCellValue(Boolean.parseBoolean(resultStr));
}
}else{
Boolean empty = null;
cell.setCellValue(empty);
}
}else if(result instanceof Date){
if(result != null) {
//日期直接用字符串输出
Date date = (Date) result;
String strDate;
if (Utils.notEmpty(col.getFormat())) {
SimpleDateFormat s = new SimpleDateFormat(col.getFormat());
strDate = s.format(date);
}else{
strDate = sdfTime.format(date);
}
cell.setCellValue(strDate);
}else{
Date empty = null;
cell.setCellValue(empty);
}
if(Utils.notEmpty(col.getFormat())) {
if(col.getCellStyle() != null && col.getDataFormat() != null) {
col.getCellStyle().setDataFormat(col.getDataFormat().getFormat(col.getFormat()));
}
}else{
if(col.getCellStyle() != null && col.getDataFormat() != null) {
col.getCellStyle().setDataFormat(col.getDataFormat().getFormat(FORMAT));
}
}
}else if(result instanceof LocalDate){
if(result != null) {
LocalDate localDate = (LocalDate) result;
//LocalDate转换为Date
ZoneId zoneId = ZoneId.systemDefault();
ZonedDateTime zdt = localDate.atStartOfDay(zoneId);
Date date = Date.from(zdt.toInstant());
String strDate = sdfDate.format(date);
cell.setCellValue(strDate);
}else{
String empty = null;
cell.setCellValue(empty);
}
if(Utils.notEmpty(col.getFormat())) {
if(col.getCellStyle() != null && col.getDataFormat() != null) {
col.getCellStyle().setDataFormat(col.getDataFormat().getFormat(col.getFormat()));
}
}else{
if(col.getCellStyle() != null && col.getDataFormat() != null) {
col.getCellStyle().setDataFormat(col.getDataFormat().getFormat(FORMAT));
}
}
}else if(result instanceof LocalDateTime){
if(result != null) {
LocalDateTime localDateTime = (LocalDateTime) result;
//LocalDateTime转换为Date
ZoneId zoneId = ZoneId.systemDefault();
ZonedDateTime zdt =localDateTime.atZone(zoneId);
Date date = Date.from(zdt.toInstant());
String strDate = sdfTime.format(date);
cell.setCellValue(strDate);
}else{
String empty = null;
cell.setCellValue(empty);
}
if(Utils.notEmpty(col.getFormat())) {
if(col.getCellStyle() != null && col.getDataFormat() != null) {
col.getCellStyle().setDataFormat(col.getDataFormat().getFormat(col.getFormat()));
}
}else{
if(col.getCellStyle() != null && col.getDataFormat() != null) {
col.getCellStyle().setDataFormat(col.getDataFormat().getFormat(FORMAT2));
}
}
}else{
if(result != null) {
cell.setCellValue(resultStr);
}else{
String empty = null;
cell.setCellValue(empty);
}
}
//样式
cell.setCellStyle(col.getCellStyle());
}
/**
* 设置下拉列表
* @param sheet sheet
* @param params 下拉列表配置
*/
private static void setDropDownList(XSSFSheet sheet,List params) {
DataValidationHelper helper = sheet.getDataValidationHelper();
for (DropdownParam param : params) {
CellRangeAddressList addressList = new CellRangeAddressList(param.getFirstRow(), param.getLastRow(), param.getFirstCol(),param.getLastCol());
DataValidationConstraint constraint = helper.createExplicitListConstraint(param.getDropdownList());
DataValidation dataValidation = helper.createValidation(constraint, addressList);
dataValidation.createErrorBox("数据非法提醒", "数据不规范,请选择下拉列表中的数据");
// 处理Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation) {
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
}
}
/**
* 设置下拉列表
* @param sheet sheet
* @param params 下拉列表配置
*/
private static void setDropDownList(HSSFSheet sheet,List params) {
for (DropdownParam param : params) {
CellRangeAddressList regions = new CellRangeAddressList(param.getFirstRow(), param.getLastRow(), param.getFirstCol(),param.getLastCol());
//创建下拉列表数据
DVConstraint constraint = DVConstraint.createExplicitListConstraint(param.getDropdownList());
//绑定
HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
dataValidation.createErrorBox("数据非法提醒", "数据不规范,请选择下拉列表中的数据");
sheet.addValidationData(dataValidation);
}
}
}
