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());
}