当前位置：物联沃-IOTWORD物联网 > 技术教程 > 使用easyExcel实现多个sheet的数据导出
代码收藏家代码收藏家 技术教程

使用easyExcel实现多个sheet的数据导出

easyExcel导出多个sheet

一、easyExcel导出多个sheet涉及到的jar包及其版本如下：

easyExcel-core-3.1.1
easyExcel-support-3.1.1
poi-3.17
poi-ooxml-3.17
commons-collections4-4.4
lombok-1.18.30
jar包都可以在这个官网里面下载：
https://mvnrepository.com/

以下代码可以直接导出数据

二、导出代码的实现

1.处理导出Excel中日期的Java类：

package com.*.*.util.easyExcelUtil;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

import java.text.SimpleDateFormat;
import java.util.Date;

    /**
     * @version 1.0
     * @since 2024-02-04 16:21:55
     **/
    public class DateConverter implements Converter<Date> {

        private static final String PATTERN_YYYY_MM_DD = "yyyy-MM-dd";

        @Override
        public Class<?> supportJavaTypeKey() {
            return Converter.super.supportJavaTypeKey();
        }

        @Override
        public CellDataTypeEnum supportExcelTypeKey() {
            return Converter.super.supportExcelTypeKey();
        }

        @Override
        public WriteCellData<?> convertToExcelData(Date value, ExcelContentProperty contentProperty,
                                                   GlobalConfiguration globalConfiguration) throws Exception {
            SimpleDateFormat sdf = new SimpleDateFormat(PATTERN_YYYY_MM_DD);
            String dateValue = sdf.format(value);
            return new WriteCellData<>(dateValue);
        }
    }

2.处理导出第一个sheet中Excel列的字段类：

package com.*.*.util.easyExcelUtil;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentFontStyle;
import com.alibaba.excel.annotation.write.style.HeadFontStyle;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;

/**
 * @author 
 * @date 2024/2/4
 */
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)
@HeadFontStyle(fontHeightInPoints = 12)
@ContentFontStyle(fontHeightInPoints = 11)
@ColumnWidth(20)
public class excelColumnName {

    // -------------------- 基本信息 start -------------

    @ExcelProperty({"基本信息", "公司名称"})
    private String companyName;

    @ExcelProperty({"基本信息", "省份"})
    private String province;

    @ExcelProperty(value = {"基本信息", "成立时间"},converter = DateConverter.class)
    private Date startDate;

    @ExcelProperty({"基本信息", "企业状态"})
    private String entStatus;

    @ColumnWidth(30)
    @ExcelProperty({"基本信息", "博客地址"})
    private String csdnAddress;

    // ---------------- 基本信息 end ---------------------

    // ---------------- 经营信息 start ---------------------

    @ExcelProperty({"经营信息", "员工数"})
    private String employeeMaxCount;

    @ExcelProperty({"经营信息", "网站地址"})
    private String netAddress;

    @ExcelProperty({"经营信息", "所属区域省"})
    private String businessProvinceName;

    @ExcelProperty({"经营信息", "所属区域市"})
    private String businessCityName;

    @ExcelProperty({"经营信息", "所属区域区县"})
    private String businessAreaName;

    // ---------------- 经营信息 end ---------------------
}

3.处理导出第二个sheet中Excel列的字段类：

package com.*.*.util.easyExcelUtil;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentFontStyle;
import com.alibaba.excel.annotation.write.style.HeadFontStyle;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)
@HeadFontStyle(fontHeightInPoints = 12)
@ContentFontStyle(fontHeightInPoints = 11)
@ColumnWidth(20)
public class excelContact {
    @ExcelProperty("公司名称")
    private String companyName;

    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("身份证号码")
    private String idCard;

    @ExcelProperty("电话号码")
    private String mobile;

    @ExcelProperty("职位")
    private String contactPostName;
}

4.导出Excel连接前端的controller代码：

@RequestMapping(params = "actionMethod=exportEasyExcel", method = RequestMethod.GET)
	@ResponseBody
	public void exportExcel(HttpServletResponse response) {
		ExcelWriter excelWriter = null;
		try(OutputStream out = response.getOutputStream()) {
			excelWriter = EasyExcel.write(out).build();
			response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
			response.setCharacterEncoding("utf-8");
			String fileName = URLEncoder.encode("哈哈哈测试导出", "UTF-8").replaceAll("\\+", "%20");
			response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
			WriteSheet dealerSheet = EasyExcel.writerSheet(0, "经销商信息").head(excelColumnName.class).build();
			WriteSheet contactSheet = EasyExcel.writerSheet(1, "联系人").head(excelContact.class).build();
			List<excelColumnName> getCompanyData= getCompany();
			excelWriter.write(getCompanyData, dealerSheet);
			excelWriter.write(getContact(), contactSheet);
			excelWriter.finish();
			out.flush();
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("导出失败:" + e.getMessage());
		} finally {
			try {
				excelWriter.finish();
				response.getOutputStream().close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	private void handleExcel(OutputStream out) {
		try {
			ExcelWriter excelWriter = EasyExcelFactory.write(out).build();
			WriteSheet dealerSheet = EasyExcel.writerSheet(0, "经销商信息").head(excelColumnName.class).build();
			WriteSheet contactSheet = EasyExcel.writerSheet(1, "联系人").head(excelColumnName.class).build();
			excelWriter.write(getCompany(), dealerSheet);
			excelWriter.write(getContact(), contactSheet);
		} catch (Exception e) {
			logger.error(e.getMessage());
		}
	}

	private List<excelColumnName> getCompany() {
		List<excelColumnName> companyList = new ArrayList<>();
		for (int i = 0; i < 5; i++) {
			companyList.add(excelColumnName.builder()
					.companyName("白小纯公司" + i)
					.province("上海市")
					.businessProvinceName("山东省")
					.businessCityName("临沂市")
					.businessAreaName("河东区")
					.entStatus("营业")
					.netAddress("www.baixiaochun.site")
					.csdnAddress("https://baixiaochun.blog.csdn.net")
					.employeeMaxCount("100")
					.startDate(new Date())
					.build());
		}
		return companyList;
	}

	private List<excelContact> getContact() {
		List<excelContact> contactList = new ArrayList<>();
		for (int i = 0; i < 5; i++) {
			contactList.add(excelContact.builder()
					.companyName("白小纯公司" + i)
					.name("白小纯" + i)
					.mobile("177000000000")
					.idCard("456224199011111111")
					.contactPostName("测试后端")
					.build());
		}
		return contactList;
	}

5.easyExcel导出多个sheet结果


6.实现easyExcel导出多个sheet可能存在的问题
①easyExcel导出一直报错org.apache.poi.ss.usermodel.Cell.setCellValue(Ljava/time/LocalDateTime
日期转化存在问题以上代码已经解决，创建了DateConverter 类来解决这个问题。
②.EasyExcel导出Excel报错Can not close IO等问题
easyExcel所使用的jar包版本之间依赖的不一致导致的问题，以上代码已经解决了这个问题。

作者：一个被代码耽误的歌手

物联沃分享整理
物联沃-IOTWORD物联网 » 使用easyExcel实现多个sheet的数据导出

代码收藏家 普通

分享到：

相关推荐

发表回复