com.alibaba.excel.support.ExcelTypeEnum Java Examples

private void writeExcelToOutput(List<ExcelTemplateRule> templateRules, List<ExcelCustomRule> customRules,
                                List<ExcelMultiTemplateRule> multiTemplateRules, OutputStream outputStream) throws WriteExcelException, IOException {
    try {
        LOGGER.info("Start to write excel");
        ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX, true);
        Sheet templateSheet = new Sheet(1, 0, ExcelTemplateRule.class);
        templateSheet.setSheetName(ExcelSheetName.TEMPLATE_RULE_NAME);
        writer.write(templateRules, templateSheet);

        Sheet customSheet = new Sheet(2, 0, ExcelCustomRule.class);
        customSheet.setSheetName(ExcelSheetName.CUSTOM_RULE_NAME);
        writer.write(customRules, customSheet);

        Sheet multiTemplateSheet = new Sheet(3, 0, ExcelMultiTemplateRule.class);
        multiTemplateSheet.setSheetName(ExcelSheetName.MULTI_TEMPLATE_RULE_NAME);
        writer.write(multiTemplateRules, multiTemplateSheet);

        writer.finish();
        LOGGER.info("Finish to write excel");
    } catch (Exception e) {
        throw new WriteExcelException(e.getMessage());
    } finally {
        outputStream.close();
    }
}
 

/**
 * 根据参数和版本枚举导出excel文件
 *
 * @param excelParams 参数实体
 * @param excelType   excel类型枚举 03 or 07
 * @throws IOException IOException
 */
private static void exportExcel(EasyExcelParams excelParams, ExcelTypeEnum excelType) throws IOException {
    HttpServletResponse response = excelParams.getResponse();

    ServletOutputStream out = response.getOutputStream();
    prepareResponds(response, excelParams.getExcelNameWithoutExt(), excelType);
    ExcelWriter writer = null;
    try {
        writer = EasyExcel.write(out, excelParams.getDataModelClazz()).excelType(excelType).build();
        WriteSheet writeSheet = EasyExcel.writerSheet(excelParams.getSheetName()).build();
        writer.write(excelParams.getData(), writeSheet);
    } finally {
        //必须保证写出结束后关闭IO
        Optional.ofNullable(writer).ifPresent(ExcelWriter::finish);
    }

}
 

public AnalysisContextImpl(ReadWorkbook readWorkbook, ExcelTypeEnum actualExcelType) {
    if (readWorkbook == null) {
        throw new IllegalArgumentException("Workbook argument cannot be null");
    }
    switch (actualExcelType) {
        case XLS:
            readWorkbookHolder = new XlsReadWorkbookHolder(readWorkbook);
            break;
        case XLSX:
            readWorkbookHolder = new XlsxReadWorkbookHolder(readWorkbook);
            break;
        default:
            break;
    }
    currentReadHolder = readWorkbookHolder;
    analysisEventProcessor = new DefaultAnalysisEventProcessor();
    if (LOGGER.isDebugEnabled()) {
        LOGGER.debug("Initialization 'AnalysisContextImpl' complete");
    }
}
 

/**
 * Create new reader
 *
 * @param in
 *            the POI filesystem that contains the Workbook stream
 * @param excelTypeEnum
 *            03 or 07
 * @param customContent
 *            {@link AnalysisEventListener#invoke(Object, AnalysisContext) }AnalysisContext
 * @param eventListener
 *            Callback method after each row is parsed.
 * @param trim
 *            The content of the form is empty and needs to be empty. The purpose is to be fault-tolerant, because
 *            there are often table contents with spaces that can not be converted into custom types. For example:
 *            '1234 ' contain a space cannot be converted to int.
 * @deprecated please use {@link EasyExcelFactory#read()} build 'ExcelReader'
 */
@Deprecated
public ExcelReader(InputStream in, ExcelTypeEnum excelTypeEnum, Object customContent,
    AnalysisEventListener eventListener, boolean trim) {
    ReadWorkbook readWorkbook = new ReadWorkbook();
    readWorkbook.setInputStream(in);
    readWorkbook.setExcelType(excelTypeEnum);
    readWorkbook.setCustomObject(customContent);
    if (eventListener != null) {
        List<ReadListener> customReadListenerList = new ArrayList<ReadListener>();
        customReadListenerList.add(eventListener);
        readWorkbook.setCustomReadListenerList(customReadListenerList);
    }
    readWorkbook.setAutoTrim(trim);
    readWorkbook.setAutoCloseStream(Boolean.FALSE);
    readWorkbook.setMandatoryUseInputStream(Boolean.TRUE);
    readWorkbook.setReadCache(new MapCache());
    readWorkbook.setConvertAllFiled(Boolean.FALSE);
    readWorkbook.setDefaultReturnMap(Boolean.FALSE);
    // The previous logic was that Article 0 started reading
    readWorkbook.setHeadRowNumber(0);
    excelAnalyser = new ExcelAnalyserImpl(readWorkbook);
}
 

/**
 * 异步导出 Excel ：一个 sheet，带表头
 *
 * @param
 * @param list      数据 list，每个元素为一个 BaseRowModel
 * @param sheetName 导入文件的 sheet 名
 * @param sheetName 导入文件的 sheet 名
 * @param object    映射实体类，Excel 模型
 */
public static String asyWriteExcel(List<? extends BaseRowModel> list,
                                String sheetName, BaseRowModel object) {
    // 现将数据导出excel到本地
    try {
        String fileName = URLEncoder.encode(createFileName(), "UTF-8");
        ExcelWriter writer = new ExcelWriter(getFileOutputStream(fileName), ExcelTypeEnum.XLSX);
        Sheet sheet = new Sheet(1, 0, object.getClass());
        sheet.setSheetName(sheetName);
        writer.write(list, sheet);
        writer.finish();
        // 读取该excel,并上传到oss，返回下载链接
        // File file = readFileByLines(fileName + ".xlsx");
        // return FileUploadUtil.upload(file, fileName + ".xlsx");
    } catch (UnsupportedEncodingException e) {
        throw new RuntimeException("创建excel失败!");
    }
    return null;
}
 

private void readAndWriteStream(File file, ExcelTypeEnum excelType) throws Exception {
    FileOutputStream fileOutputStream = new FileOutputStream(file);
    EasyExcel.write(fileOutputStream, EncryptData.class).password("123456").excelType(excelType).sheet()
        .doWrite(data());
    fileOutputStream.close();

    FileInputStream fileInputStream = new FileInputStream(file);
    EasyExcel.read(fileInputStream, EncryptData.class, new EncryptDataListener()).password("123456").sheet()
        .doRead();
}
 

private void writeExcelToOutput(List<ExcelProject> excelProjects, List<ExcelTemplateRuleByProject> excelTemplateRuleByProjects, List<ExcelCustomRuleByProject> excelCustomRuleByProjects,
                                List<ExcelMultiTemplateRuleByProject> excelMultiTemplateRuleByProjects, OutputStream outputStream) throws WriteExcelException, IOException {
    try {
        LOGGER.info("Start to write excel");
        ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX, true);
        Sheet templateRuleSheet = new Sheet(1, 0, ExcelTemplateRuleByProject.class);
        templateRuleSheet.setSheetName(ExcelSheetName.TEMPLATE_RULE_NAME);
        writer.write(excelTemplateRuleByProjects, templateRuleSheet);

        Sheet projectSheet = new Sheet(2, 0, ExcelProject.class);
        projectSheet.setSheetName(ExcelSheetName.PROJECT_NAME);
        writer.write(excelProjects, projectSheet);

        Sheet customRuleSheet = new Sheet(3, 0, ExcelCustomRuleByProject.class);
        customRuleSheet.setSheetName(ExcelSheetName.CUSTOM_RULE_NAME);
        writer.write(excelCustomRuleByProjects, customRuleSheet);

        Sheet multiRuleSheet = new Sheet(4, 0, ExcelMultiTemplateRuleByProject.class);
        multiRuleSheet.setSheetName(ExcelSheetName.MULTI_TEMPLATE_RULE_NAME);
        writer.write(excelMultiTemplateRuleByProjects, multiRuleSheet);
        writer.finish();
        LOGGER.info("Finish to write excel");
    } catch (Exception e) {
        throw new WriteExcelException(e.getMessage());
    } finally {
        outputStream.close();
    }
}
 

@Test
public void testread() throws Exception {
    FileInputStream fileInputStream = new FileInputStream("D://test/t1.xlsx");

    ExcelReader excelReader = new ExcelReader(fileInputStream, ExcelTypeEnum.XLSX, null, new TestListener());
    excelReader.read();
}
 

public XlsReadWorkbookHolder(ReadWorkbook readWorkbook) {
    super(readWorkbook);
    this.boundSheetRecordList = new ArrayList<BoundSheetRecord>();
    this.needReadSheet = Boolean.TRUE;
    setExcelType(ExcelTypeEnum.XLS);
    if (getGlobalConfiguration().getUse1904windowing() == null) {
        getGlobalConfiguration().setUse1904windowing(Boolean.FALSE);
    }
    ignoreRecord = Boolean.FALSE;
}
 

private void clearEncrypt03() {
    if (StringUtils.isEmpty(analysisContext.readWorkbookHolder().getPassword())
        || !ExcelTypeEnum.XLS.equals(analysisContext.readWorkbookHolder().getExcelType())) {
        return;
    }
    Biff8EncryptionKey.setCurrentUserPassword(null);
}
 

/**
 * To encrypt
 */
private boolean doOutputStreamEncrypt07() throws Exception {
    if (StringUtils.isEmpty(writeWorkbookHolder.getPassword())
        || !ExcelTypeEnum.XLSX.equals(writeWorkbookHolder.getExcelType())) {
        return false;
    }
    if (writeWorkbookHolder.getFile() != null) {
        return false;
    }
    File tempXlsx = FileUtils.createTmpFile(UUID.randomUUID().toString() + ".xlsx");
    FileOutputStream tempFileOutputStream = new FileOutputStream(tempXlsx);
    try {
        writeWorkbookHolder.getWorkbook().write(tempFileOutputStream);
    } finally {
        try {
            writeWorkbookHolder.getWorkbook().close();
            tempFileOutputStream.close();
        } catch (Exception e) {
            if (!tempXlsx.delete()) {
                throw new ExcelGenerateException("Can not delete temp File!");
            }
            throw e;
        }
    }
    POIFSFileSystem fileSystem = null;
    try {
        fileSystem = openFileSystemAndEncrypt(tempXlsx);
        fileSystem.writeFilesystem(writeWorkbookHolder.getOutputStream());
    } finally {
        if (fileSystem != null) {
            fileSystem.close();
        }
        if (!tempXlsx.delete()) {
            throw new ExcelGenerateException("Can not delete temp File!");
        }
    }
    return true;
}
 

private void clearEncrypt03() {
    if (StringUtils.isEmpty(writeWorkbookHolder.getPassword())
        || !ExcelTypeEnum.XLS.equals(writeWorkbookHolder.getExcelType())) {
        return;
    }
    Biff8EncryptionKey.setCurrentUserPassword(null);
}
 

/**
 * 设置response相关参数
 *
 * @param response response
 * @param fileName 文件名
 * @param typeEnum excel类型
 * @throws UnsupportedEncodingException e
 */
private static void prepareResponds(HttpServletResponse response, String fileName, ExcelTypeEnum typeEnum) throws UnsupportedEncodingException {
    //contentType默认是.xls类型
    response.setContentType("application/vnd.ms-excel;charset=utf-8");

    if (".xlsx".equals(typeEnum.getValue())) {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
    }

    response.setHeader("Content-disposition", String.format("attachment; filename=%s", URLEncoder.encode(fileName, "UTF-8") + typeEnum.getValue()));
}
 

/**
 * 导出 Excel ：一个 sheet，带表头
 *
 * @param response  HttpServletResponse
 * @param list      需要导出的数据
 * @param fileName  导出的文件名
 * @param sheetName 导入文件的 sheet 名
 * @param object    映射实体类，Excel 模型
 */
public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list,
                              String fileName, String sheetName, BaseRowModel object) {
    // WriteModel 是 写入 Excel 的数据模型对象
    ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
    Sheet sheet = new Sheet(1, 0, object.getClass());
    sheet.setSheetName(sheetName);
    // 异常处理
    writer.write(list, sheet);
    writer.finish();
}
 

@Test
public void test03() {
    EasyExcel.write(pathName1 + "write" + ExcelTypeEnum.XLS.getValue(), Order.class)
            .excelType(ExcelTypeEnum.XLS)
            .sheet("sheet1").doWrite(data(1000));

}
 

/**
 * 获取OutputStream
 * @param fileName fileName
 * @param request request
 * @param response response
 * @param excelTypeEnum excelTypeEnum
 * @return OutputStream
 */
private static OutputStream getOutputStream(String fileName, HttpServletRequest request,
		HttpServletResponse response, ExcelTypeEnum excelTypeEnum) {
	try {
		// 设置响应头，处理浏览器间的中文乱码问题
		response.addHeader(HttpHeaders.CONTENT_DISPOSITION,
				Servlets.getDownName(request, fileName + excelTypeEnum.getValue()));
		return response.getOutputStream();
	} catch (IOException e) {
		throw new ExcelException("get OutputStream error!");
	}
}
 

/**
 * 导出Excel
 * @param request request
 * @param response response
 * @param dataList 数据list
 * @param fileName 文件名
 * @param sheetName sheet 名
 * @param clazz clazz
 */
public static <T> void writeExcel(HttpServletRequest request, HttpServletResponse response, List<T> dataList,
		String fileName, String sheetName, Class<T> clazz) {
	ExcelWriter excelWriter = null;
	try {
		excelWriter = EasyExcelFactory
				.write(getOutputStream(fileName, request, response, ExcelTypeEnum.XLSX), clazz).build();
		WriteSheet writeSheet = EasyExcelFactory.writerSheet(sheetName).build();
		excelWriter.write(dataList, writeSheet);
	} finally {
		if (excelWriter != null)
			excelWriter.finish();
	}
}
 

@Test
public void t03ReadAndWriteStream07() throws Exception {
    readAndWriteStream(file07OutputStream, ExcelTypeEnum.XLSX);
}
 

public XlsxReadWorkbookHolder(ReadWorkbook readWorkbook) {
    super(readWorkbook);
    this.saxParserFactoryName = readWorkbook.getXlsxSAXParserFactoryName();
    setExcelType(ExcelTypeEnum.XLSX);
}
 

public void setExcelType(ExcelTypeEnum excelType) {
    this.excelType = excelType;
}
 

public ExcelTypeEnum getExcelType() {
    return excelType;
}
 

public void setExcelType(ExcelTypeEnum excelType) {
    this.excelType = excelType;
}
 

public ExcelTypeEnum getExcelType() {
    return excelType;
}
 

public void setExcelType(ExcelTypeEnum excelType) {
    this.excelType = excelType;
}
 

public ExcelTypeEnum getExcelType() {
    return excelType;
}
 

public ExcelTypeEnum getExcelType() {
    return excelType;
}
 

public void setExcelType(ExcelTypeEnum excelType) {
    this.excelType = excelType;
}
 

@Override
public ExcelTypeEnum getExcelType() {
    return readWorkbookHolder.getExcelType();
}
 

public ExcelWriterFactory(OutputStream outputStream, ExcelTypeEnum typeEnum) {
    super(outputStream, typeEnum);
    this.outputStream = outputStream;
}
 

public DefaultXlsReadContext(ReadWorkbook readWorkbook, ExcelTypeEnum actualExcelType) {
    super(readWorkbook, actualExcelType);
}