Excel 工具类Ⅱ
使用范围
该工具类要求 jdk 1.8以上,org.apache.poi 4.0 以上。由于我项目用的是gradle 就不贴导包的路径信息了,此工具类应该可以满足日常开发绝大部分需求,分享给大家,有错误之处,还望大神指教。
一、DownloadUtils
import com.google.common.base.Strings;
import com.zynsun.framework.web.domain.ResourceException;
import com.zynsun.framework.web.utils.Excels;
import com.zynsun.framework.web.utils.Validation;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.InputStreamResource;
import org.springframework.core.io.ResourceLoader;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.concurrent.ConcurrentHashMap;
/**
-
文件下载的辅助类
-
为了保证文件下载的安全性,确保下载链接不会被盗用,每次生成新的下载id
*/
public class DownloadUtils {
public static final MediaType XLSX_MEDIA_TYPE = MediaType.parseMediaType(“application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”);
private static final ConcurrentHashMap<String, DownloadIdParameter> downloadIdCache = new ConcurrentHashMap<>();/**
-
根据下载id获取缓存的参数
-
@param downloadId
-
@param
-
@return
*/
public static T getParameter(String downloadId) {
clearExpiredCacheItems();Validation.notNullOrEmpty(downloadId, “下载id”);
DownloadIdParameter cacheItem = downloadIdCache.get(downloadId);
if (cacheItem == null) {
throw ResourceException.badRequest(“下载id无效”);
}
downloadIdCache.remove(downloadId);
//还原当前用户
/* TODO 权限未做
if(User.getCurrentUser() == null){
User.setCurrentUser(cacheItem.currentUser);
}*/
return (T) cacheItem.parameter;
}
/**
-
缓存参数,获取下载id
-
@param parameter
-
@return
*/
public static String putParameter(Object parameter) {
clearExpiredCacheItems();String downloadId = UUID.randomUUID().toString();
downloadIdCache.put(downloadId, new DownloadIdParameter(parameter));
return downloadId;
}
/**
- 设置文件下载的头
- @param builder BodyBuilder
- @param fileName 文件名
- @return
/
public static ResponseEntity.BodyBuilder setAttachmentHeader(ResponseEntity.BodyBuilder builder, String fileName) {
String value;
try {
value = "attachment;filename=UTF-8’’"
+ URLEncoder.encode(fileName, “UTF-8”);
} catch (UnsupportedEncodingException e) {
throw new ResourceException(e, “获取编码失败”);
}
return builder.header(“Content-Disposition”, value);
}
private static boolean isDownloadIdCacheItemExpired(DownloadIdParameter item) {
//有效期为30s
return item.time.plusSeconds(30).isBefore(LocalDateTime.now());
}private static void clearExpiredCacheItems() {
for (Map.Entry<String, DownloadIdParameter> entry : downloadIdCache.entrySet()) {
if (isDownloadIdCacheItemExpired(entry.getValue())) {
downloadIdCache.remove(entry.getKey());
}
}
}/**
-
下载Id关联的参数
/
private static class DownloadIdParameter {
/ TODO 权限未做
private final User currentUser;
*/
private final Object parameter;
private final LocalDateTime time;public DownloadIdParameter(Object parameter) {
//缓存当前请求的用户,防止下载时没有用户
/* TODO 权限未做
this.currentUser = User.getCurrentUser();
*/
this.parameter = parameter;
this.time = LocalDateTime.now();
}
}
/**
-
导出excel
-
@param thead 表头
-
@param datas 数据
-
@param fileName 导出的文件名,可以为空
-
@return
*/
public static ResponseEntity exportExcel(String[] thead, List<List> datas, String fileName) {
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet();
// 创建表头
createHead(wb, sheet, thead);// 设置数据
for (int i = 0; i < datas.size(); i++) {
Row row = sheet.createRow(sheet.getPhysicalNumberOfRows());
for (int j = 0; j < datas.get(i).size(); j++) {
setCell(row, null, j, datas.get(i).get(j));
}
}
// 自动列宽
Excels.autoSizeColumns(wb);byte[] template = Excels.toByteArray(wb);
InputStreamResource resource = new InputStreamResource(new ByteArrayInputStream(template));
ResponseEntity.BodyBuilder builder = ResponseEntity.ok()
.contentType(DownloadUtils.XLSX_MEDIA_TYPE)
.contentLength(template.length);fileName = String.format("%s.xlsx", Strings.isNullOrEmpty(fileName) ?
LocalDateTime.now().format(DateTimeFormatter.ISO_LOCAL_DATE_TIME) : fileName);
builder = DownloadUtils.setAttachmentHeader(builder, fileName);
return builder.body(resource);
}
/**
- 设置表头
- @param sheet
- @param wb
- @param thead
*/
public static void createHead(Workbook wb, Sheet sheet, String[] thead) {
// 设置字体样式
CellStyle cellStyle = wb.createCellStyle();
Font font = wb.createFont();
font.setBold(true); // 粗体显示
cellStyle.setFont(font);
Row headRow = sheet.createRow(sheet.getPhysicalNumberOfRows());
// 第1行为表头行
for (int i = 0; i < thead.length; i++) {
Cell cell = headRow.createCell(i);
cell.setCellStyle(cellStyle);
cell.setCellValue(thead[i]);
}
}
/**
- 设置单元格
- @param row
- @param cellStyle
- @param index
- @param value
*/
public static void setCell(Row row, CellStyle cellStyle, int index, String value) {
Cell cell = row.createCell(index);
cell.setCellValue(value);
if (null != cellStyle) {
cell.setCellStyle(cellStyle);
}
}
/**
-
下载模板
-
@param response 响应对象
-
@param sourcePath 资源路径
-
@param resourceLoader 资源加载对象
-
@param fileExtension 文件扩展名,如.xls
*/
public static void downloadTemplate(HttpServletResponse response, String sourcePath, ResourceLoader resourceLoader,
String fileExtension) {
InputStream inputStream = null;
BufferedOutputStream buff = null;response.setCharacterEncoding(“UTF-8”);
response.setContentType(“application/octet-stream”);String downloadName = “template” + new Date().getTime() + fileExtension;
response.addHeader(“Content-Disposition”, “attachment;filename=”" + downloadName + “”");
try {
// 获取静态资源文件
org.springframework.core.io.Resource resource = resourceLoader.getResource(“classpath:” + sourcePath);
inputStream = resource.getInputStream();buff = new BufferedOutputStream(response.getOutputStream()); int ch; while ((ch = inputStream.read()) != -1) { buff.write(ch); }
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (null != inputStream) {
inputStream.close();
}
if (null != buff) {
buff.flush();
buff.close();
}
response.flushBuffer();
response.getOutputStream().flush();
response.getOutputStream().close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
-
二、ImportUtils
import com.zynsun.framework.webcommon.exception.ResourceException;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
public class ImportUtils {
public static Sheet getAndValidateDataSheet(MultipartFile file) {
if (file == null || file.isEmpty()) {
throw ResourceException.badRequest("导入的文件不能为空");
}
Workbook wb;
try {
wb = WorkbookFactory.create(file.getInputStream());
} catch (IOException e) {
e.printStackTrace();
throw new ResourceException(e, "导入文件的格式不正确:%s", e.getMessage());
}
if (wb.getNumberOfSheets() == 0) {
throw ResourceException.badRequest("导入的文件未包括任何表格");
}
//默认第一个sheet为导入数据
Sheet sheet = wb.getSheetAt(0);
if (sheet.getPhysicalNumberOfRows() <= 1) {
throw ResourceException.badRequest("导入的文件未包含有效的数据行");
}
return sheet;
}
}
2.调用示例
@Override
public ResponseEntity exportExcel() {
List<ExpenseType> expenseTypes = expenseTypeRepository.findAll();
expenseTypes.sort(Comparator.naturalOrder());
List<List<String>> lists = new ArrayList<>();
for (ExpenseType expenseType : expenseTypes) {
Optional<ExpenseType> parentOptional = expenseTypes.stream().filter(parent -> parent.getId().equals(expenseType.getParentId())).findFirst();
List<String> list = new ArrayList<>();
list.add(expenseType.getCode());
list.add(expenseType.getName());
list.add(parentOptional.isPresent() ? parentOptional.get().getName() : "");
list.add(expenseType.getReimburseModel().getDesc());
list.add(expenseType.getBudgetControlStrategyDesc());
list.add(expenseType.isAllowUseParentOrgBudget() ? "是" : "否");
list.add(expenseType.isAllowUseParentExpenseTypeBudget() ? "是" : "否");
list.add(expenseType.isRelationProject() ? "是" : "否");
list.add(expenseType.isRelationContract() ? "是" : "否");
list.add(expenseType.isAllowBorrow() ? "是" : "否");
list.add(expenseType.isAllowExcess() ? "是" : "否");
list.add(expenseType.isBudgetControl() ? "是" : "否");
list.add(expenseType.isDisabled() ? "禁用" : "启用");
lists.add(list);
}
return DownloadUtils.exportExcel(THEAD, lists, null);
}
@Override
public OperateResult importData(MultipartFile file) {
// 1)获取导入文件,格式校验
Sheet sheet = ImportUtils.getAndValidateDataSheet(file);
Row row;
Cell cell;
StringBuffer buffer = new StringBuffer();
// 员工编号
String employeeCode;
Set<String> codes = new HashSet<>();
// 借款金额
String amountCellValue;
BigDecimal amount = BigDecimal.ZERO;
// 公司编号
String companyCode;
Set<String> companyCodes = new HashSet<>();
// 借款类型
BorrowType borrowType = null;
// 保存的对象
List<BorrowRecord> borrowRecords = new ArrayList<>();
BorrowRecord borrowRecord;
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
row = sheet.getRow(i);
// 员工编号
cell = row.getCell(0);
if (null == cell) {
break;
}
cell.setCellType(CellType.STRING);
employeeCode = cell.getStringCellValue();
if (Strings.isNullOrEmpty(employeeCode)) {
break;
}
codes.add(employeeCode);
BorrowType[] values = BorrowType.values();
for (int j = 0; j < values.length; j++) {
if (values[j].name().equals(borrowTypeCode)) {
borrowType = values[j];
}
}
if (null == borrowType) {
buffer.append("第【" + (i + 1) + "】行第【" + 2 + "】列【借款类型】设值不正确,请输入、" +
"EMPLOYEE(aaa)、" +
"RESERVE_FUND(aaa)中的一种;");
}
borrowTypeCodes.add(borrowTypeCode);
// 借款金额
cell = row.getCell(2);
cell.setCellType(CellType.STRING);
amountCellValue = cell.getStringCellValue();
if (Strings.isNullOrEmpty(amountCellValue)) {
buffer.append("第【" + (i + 1) + "】行第【" + 3 + "】列【导入爱共享借款余额】不能为空;");
} else {
// 防止取出的值为格式化了的金额,如:1,000.00
if (amountCellValue.contains(",")) {
amountCellValue = amountCellValue.replaceAll(",", "");
}
amount = new BigDecimal(amountCellValue);
}
// 公司编号
cell = row.getCell(3);
cell.setCellType(CellType.STRING);
companyCode = cell.getStringCellValue();
companyCodes.add(companyCode);
borrowRecord = new BorrowRecord();
borrowRecord.setInitialized(true);
borrowRecord.setRefId("初始化导入");
borrowRecord.setRecordType(BorrowRecord.RecordType.INITIALIZED);
borrowRecord.setBorrowConfigId("");
borrowRecord.setEmployeeCode(employeeCode);
borrowRecord.setBorrowDate(LocalDate.now());
borrowRecord.setAmount(amount);
borrowRecord.setBorrowType(borrowType);
borrowRecord.setReason("初始化导入");
borrowRecord.setRepaidAmount(BigDecimal.ZERO);
borrowRecord.setPlanRepayDate(LocalDate.now());
borrowRecord.setCreateTime(LocalDateTime.now());
borrowRecord.setOnRepaidAmount(BigDecimal.ZERO);
borrowRecord.setCompanyCode(companyCode);
borrowRecords.add(borrowRecord);
}
if (Strings.isNullOrEmpty(buffer.toString())) {
repository.saveAll(borrowRecords);
return OperateResult.ok();
}
return OperateResult.badRequest(buffer.toString());
}