package com.lm.demo.util;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.CharUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.MediaType;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
public class ExcelUtil {
private final static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
private final static String EXCEL2003 = "xls";
private final static String EXCEL2007 = "xlsx";
/**
* 浏览器导出excel文件(按照模板导出,保留模板中的格式,如某一列包含下拉框等)
*
* @param response
* @param templateFilePath 模板文件全路径
* @param fileName 文件名称
* @param dataList 要导出的数据集合
* @param cls 数据对象class
* @param <T> 泛型对象
*/
public static <T> void writeExcelByTemplate(HttpServletResponse response, String templateFilePath, String fileName,
List<T> dataList, Class<T> cls) {
if (StringUtils.isBlank(templateFilePath)) {
logger.warn("未指定模板文件路径,不执行导出操作!");
return;
}
if (StringUtils.isBlank(fileName)) {
logger.warn("未指定文件名称,不执行导出操作!");
return;
}
File templateFile = new File(templateFilePath);
if (!templateFile.exists()) {
logger.warn("模板文件不存在,不执行导出操作!");
return;
}
FileInputStream is = null;
try {
// 读取模板文件中的内容
is = new FileInputStream(templateFile);
HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(is));
// 获取excel文件的第一个sheet页
HSSFSheet sheet = wb.getSheetAt(0);
if (dataList != null) {
//获取临时文件excel最后一行数据的下标(用来删除模板中多余的数据,只保留列表中查询到的数据)
int lastRowNum = sheet.getLastRowNum();
logger.info("模板中最后一行数据的下标值为: " + lastRowNum);
//当要模板中的数据多于查询列表中的值时,需要将模板中的数据先全部清空,否则导出的数据中会包含模板中的数据
if (lastRowNum > dataList.size() && lastRowNum >= 1) {
for (int rowNum = 1; rowNum <= lastRowNum; rowNum++) {
Row deleteRow = sheet.getRow(rowNum);
if (ObjectUtils.isNotEmpty(deleteRow)) {
sheet.removeRow(deleteRow);
}
}
}
fillDataToWorkbookExcludeTitle(sheet, dataList, cls);
}
//浏览器下载excel
buildExcelByBrowser(fileName, wb, response);
} catch (IOException e) {
logger.error("文件下载异常,异常原因:{}", e.getMessage());
} finally {
if (ObjectUtils.isNotEmpty(is)) {
try {
is.close();
} catch (IOException e) {
logger.error("文件流关闭异常,异常原因:{}", e.getMessage());
}
}
}
}
/**
* 读取excel文件内容
*
* @param file http请求传入的file文件
* @param cls
* @param <T>
* @return
*/
public static <T> List<T> readExcel(MultipartFile file, Class<T> cls) {
// 定义返回对象
List<T> dataList = null;
String fileName = file.getOriginalFilename();
InputStream is = null;
try {
is = file.getInputStream();
dataList = readExcel(fileName, cls, is);
} catch (IOException e) {
logger.error("IOException异常,异常原因:{}", e.getMessage());
} finally {
if (ObjectUtils.isNotEmpty(is)) {
try {
is.close();
} catch (IOException e) {
logger.error("关闭IO流异常,异常原因:{}", e.getMessage());
}
}
}
return dataList;
}
/**
* 将数据填充到excel对象中并返回
*
* @param dataList 要导出的数据集合
* @param cls 数据对象class
* @param <T> 泛型对象
*/
private static <T> Workbook fillDataToWorkbook(List<T> dataList, Class<T> cls) {
// 获取所有的字段信息
Field[] fields = cls.getDeclaredFields();
// 对字段信息按照注解中的col进行排序并返回List集合
List<Field> fieldList = Arrays.stream(fields)
.filter(field -> {
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
if (annotation != null && annotation.col() > 0) {
field.setAccessible(true);
return true;
}
return false;
}).sorted(Comparator.comparing(field -> {
int col = 0;
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
if (annotation != null) {
col = annotation.col();
}
return col;
})).collect(Collectors.toList());
// 开始填充数据到Workbook对象中
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("Sheet1");
AtomicInteger ai = new AtomicInteger();
{
Row row = sheet.createRow(ai.getAndIncrement());
AtomicInteger aj = new AtomicInteger();
//写入头部
fieldList.forEach(field -> {
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
String columnName = "";
if (annotation != null) {
columnName = annotation.value();
}
Cell cell = row.createCell(aj.getAndIncrement());
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
// 定义两个字体样式,因为表头中如果有*号,则需要把*号的颜色转换为红色
Font font = wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
Font font1 = wb.createFont();
font1.setBoldweight(Font.BOLDWEIGHT_BOLD);
font1.setColor(HSSFColor.RED.index);
XSSFRichTe
没有合适的资源?快使用搜索试试~ 我知道了~
swagger例子UI还可以
共84个文件
xml:68个
java:9个
gitignore:2个
需积分: 5 0 下载量 144 浏览量
2022-07-23
23:20:34
上传
评论
收藏 57KB ZIP 举报
温馨提示
<!-- 比较美观的swagger-ui包--> <dependency> <groupId>com.github.xiaoymin</groupId> <artifactId>swagger-bootstrap-ui</artifactId> <version>1.9.6</version> </dependency>
资源详情
资源评论
资源推荐
收起资源包目录
demo-common.zip (84个子文件)
demo-common
pom.xml 3KB
demo-common.iml 81B
src
test
java
com
lm
demo
ScheduleApplicationTests.java 205B
main
resources
application-dev.yaml 288B
templates
static
application.yaml 331B
excel
test.xls 18KB
java
com
lm
demo
CommonApplication.java 304B
model
TestExcelModel.java 418B
controll
ExcelControll.java 2KB
service
ExcelService.java 523B
a
impl
ExcelServiceImpl.java 2KB
util
ExcelUtil.java 25KB
R.java 2KB
ExcelColumn.java 576B
.idea
misc.xml 466B
jarRepositories.xml 879B
modules.xml 255B
encodings.xml 191B
compiler.xml 803B
workspace.xml 4KB
.gitignore 50B
libraries
Maven__net_minidev_json_smart_2_4_8.xml 521B
Maven__org_apache_logging_log4j_log4j_api_2_17_2.xml 573B
Maven__org_springframework_spring_webmvc_5_3_19.xml 581B
Maven__org_ow2_asm_asm_9_1.xml 458B
Maven__ch_qos_logback_logback_classic_1_2_11.xml 575B
Maven__org_junit_jupiter_junit_jupiter_api_5_8_2.xml 594B
Maven__com_fasterxml_jackson_datatype_jackson_datatype_jdk8_2_13_2.xml 681B
Maven__org_yaml_snakeyaml_1_29.xml 495B
Maven__org_springframework_boot_spring_boot_starter_json_2_6_7.xml 671B
Maven__ch_qos_logback_logback_core_1_2_11.xml 554B
Maven__org_springframework_spring_expression_5_3_19.xml 609B
Maven__org_junit_jupiter_junit_jupiter_params_5_8_2.xml 615B
Maven__org_apache_tomcat_embed_tomcat_embed_websocket_9_0_62.xml 660B
Maven__jakarta_activation_jakarta_activation_api_1_2_2.xml 633B
Maven__org_mockito_mockito_junit_jupiter_4_0_0.xml 598B
Maven__org_springframework_spring_context_5_3_19.xml 588B
Maven__org_springframework_spring_test_5_3_19.xml 567B
Maven__org_springframework_spring_jcl_5_3_19.xml 560B
Maven__com_fasterxml_jackson_core_jackson_databind_2_13_2_1.xml 644B
Maven__org_springframework_boot_spring_boot_starter_2_6_7.xml 636B
Maven__org_springframework_boot_spring_boot_autoconfigure_2_6_7.xml 678B
Maven__org_hamcrest_hamcrest_2_2.xml 497B
Maven__org_mockito_mockito_core_4_0_0.xml 535B
Maven__org_springframework_boot_spring_boot_test_2_6_7.xml 615B
Maven__org_xmlunit_xmlunit_core_2_8_4.xml 535B
Maven__net_minidev_accessors_smart_2_4_8.xml 556B
Maven__org_springframework_spring_web_5_3_19.xml 560B
Maven__org_apache_logging_log4j_log4j_to_slf4j_2_17_2.xml 608B
Maven__org_junit_platform_junit_platform_commons_1_8_2.xml 633B
Maven__org_springframework_spring_core_5_3_19.xml 567B
Maven__org_springframework_boot_spring_boot_starter_web_2_6_7.xml 664B
Maven__com_fasterxml_jackson_datatype_jackson_datatype_jsr310_2_13_2.xml 695B
Maven__org_springframework_boot_spring_boot_starter_logging_2_6_7.xml 692B
Maven__net_bytebuddy_byte_buddy_agent_1_11_22.xml 585B
Maven__org_springframework_boot_spring_boot_starter_tomcat_2_6_7.xml 685B
Maven__com_fasterxml_jackson_core_jackson_core_2_13_2.xml 602B
Maven__com_vaadin_external_google_android_json_0_0_20131108_vaadin1.xml 700B
Maven__org_springframework_boot_spring_boot_starter_test_2_6_7.xml 671B
Maven__org_assertj_assertj_core_3_21_0.xml 542B
Maven__org_skyscreamer_jsonassert_1_5_0.xml 537B
Maven__org_springframework_spring_beans_5_3_19.xml 574B
Maven__org_apache_tomcat_embed_tomcat_embed_el_9_0_62.xml 611B
Maven__jakarta_annotation_jakarta_annotation_api_1_3_5.xml 633B
Maven__org_springframework_boot_spring_boot_2_6_7.xml 580B
Maven__org_opentest4j_opentest4j_1_2_0.xml 533B
Maven__org_slf4j_jul_to_slf4j_1_7_36.xml 534B
Maven__com_jayway_jsonpath_json_path_2_6_0.xml 546B
Maven__org_junit_jupiter_junit_jupiter_5_8_2.xml 566B
Maven__org_junit_platform_junit_platform_engine_1_8_2.xml 626B
Maven__org_objenesis_objenesis_3_2.xml 508B
Maven__org_apiguardian_apiguardian_api_1_1_2.xml 572B
Maven__org_apache_tomcat_embed_tomcat_embed_core_9_0_62.xml 625B
Maven__org_junit_jupiter_junit_jupiter_engine_5_8_2.xml 615B
Maven__org_springframework_boot_spring_boot_test_autoconfigure_2_6_7.xml 713B
Maven__com_fasterxml_jackson_module_jackson_module_parameter_names_2_13_2.xml 736B
Maven__com_fasterxml_jackson_core_jackson_annotations_2_13_2.xml 651B
Maven__org_slf4j_slf4j_api_1_7_36.xml 513B
Maven__org_springframework_spring_aop_5_3_19.xml 560B
Maven__net_bytebuddy_byte_buddy_1_11_22.xml 543B
Maven__jakarta_xml_bind_jakarta_xml_bind_api_2_3_3.xml 611B
inspectionProfiles
Project_Default.xml 1KB
.gitignore 395B
HELP.md 891B
共 84 条
- 1
千百元
- 粉丝: 1706
- 资源: 16
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功
评论0