easy excel 导入导出（easyexcel导入导出隐藏列）

　　本篇文章为你整理了easy excel 导入导出（easyexcel导入导出隐藏列）的详细内容，包含有easyexcel导入导出工具类封装 easyexcel导入导出隐藏列 easyexcel导入大量数据 easyexcel导出word easy excel 导入导出，希望能帮助你了解 easy excel 导入导出。

　　第一步：导入依赖

 dependency 
 groupId com.alibaba /groupId 
 artifactId easyexcel /artifactId 
 version 3.1.0 /version 
 /dependency 
第二步：创建导入导出工具类

public class ExcelUtil {
 /**
 * 导出数据为excel文件
 *
 * @param filename 文件名称
 * @param sheetName sheet名称
 * @param dataResult 集合内的bean对象类型要与clazz参数一致
 * @param clazz 集合内的bean对象类型要与clazz参数一致
 * @param response HttpServlet响应对象
 */
//有实体对象的导出方式
 public static void export(String filename,String sheetName, List ? dataResult, Class ? clazz, HttpServletResponse response) {
 response.setStatus(200);
 response.setContentType("application/vnd.ms-excel");
 response.setCharacterEncoding("utf-8");
 OutputStream outputStream = null;
 ExcelWriter excelWriter = null;
 try {
 if (StringUtil.isEmpty(filename)) {
 throw new RuntimeException("filename 不能为空");
 }
 String fileName = filename.concat(".xls");
 response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
 outputStream = response.getOutputStream();
 // 根据不同的策略生成不同的ExcelWriter对象
 if (dataResult == null){
 excelWriter = getTemplateExcelWriter(outputStream);
 } else {
 excelWriter = getExportExcelWriter(outputStream);
 }
 //获取表头个数
 int last = clazz.getDeclaredFields().length;
 WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).head(clazz)
 .relativeHeadRowIndex(1)
 .registerWriteHandler(new MonthSheetWriteHandler(sheetName,last))//设置大标题名称及其单元格合并
 .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())//自适应列宽
 .build();
 // 写出数据
 excelWriter.write(dataResult,writeSheet);
 } catch (Exception e) {
 log.error("导出excel数据异常：", e);
 throw new RuntimeException(e);
 } finally {
 if (excelWriter != null) {
 excelWriter.finish();
 }
 if (outputStream != null) {
 try {
 outputStream.flush();
 outputStream.close();
 } catch (IOException e) {
 log.error("导出数据关闭流异常", e);
 }
 }
 }
 }
//没有实体对象的导出方式
 public static void export(String filename,String sheetName,String headName, List ? dataResult, List List String head, HttpServletResponse response) {
 response.setStatus(200);
 response.setContentType("application/vnd.ms-excel");
 response.setCharacterEncoding("utf-8");
 OutputStream outputStream = null;
 ExcelWriter excelWriter = null;
 try {
 if (StringUtil.isEmpty(filename)) {
 throw new RuntimeException("filename 不能为空");
 }
 String fileName = filename.concat(".xls");
 response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
 outputStream = response.getOutputStream();
 // 根据不同的策略生成不同的ExcelWriter对象
 if (dataResult == null){
 excelWriter = getTemplateExcelWriter(outputStream);
 } else {
 excelWriter = getExportExcelWriter(outputStream);
 }
 WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).head(head)
 .relativeHeadRowIndex(1)
 .registerWriteHandler(new MonthSheetWriteHandler(headName,head.size()))//设置大标题名称及其单元格合并
 .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())//自适应列宽
 .build();
 // 写出数据
 excelWriter.write(dataResult,writeSheet);
 } catch (Exception e) {
 log.error("导出excel数据异常：", e);
 throw new RuntimeException(e);
 } finally {
 if (excelWriter != null) {
 excelWriter.finish();
 }
 if (outputStream != null) {
 try {
 outputStream.flush();
 outputStream.close();
 } catch (IOException e) {
 log.error("导出数据关闭流异常", e);
 }
 }
 }
 }
 /**
 * 根据不同策略生成不同的ExcelWriter对象， 可根据实际情况修改
 * @param outputStream 数据输出流
 * @return 模板下载ExcelWriter对象
 */
 private static ExcelWriter getTemplateExcelWriter(OutputStream outputStream){
 return EasyExcel.write(outputStream)
 //.registerWriteHandler(new CommentWriteHandler()) //增加批注策略
 //.registerWriteHandler(new CustomSheetWriteHandler()) //增加下拉框策略
 .registerWriteHandler(getStyleStrategy()) //字体居中策略
 .build();
 }
 /**
 * 根据不同策略生成不同的ExcelWriter对象， 可根据实际情况修改
 * @param outputStream 数据输出流
 * @return 数据导出ExcelWriter对象
 */
 private static ExcelWriter getExportExcelWriter(OutputStream outputStream){
 return EasyExcel.write(outputStream)
 .registerWriteHandler(getStyleStrategy()) //字体居中策略
 .build();
 }
 /**
 * 设置表格内容居中显示策略
 * @return
 */
 private static HorizontalCellStyleStrategy getStyleStrategy(){
 WriteCellStyle headWriteCellStyle = new WriteCellStyle();
 headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.index);
 //设置头字体
 WriteFont headWriteFont = new WriteFont();
 headWriteFont.setFontHeightInPoints((short)10);
 headWriteFont.setBold(true);
 headWriteCellStyle.setWriteFont(headWriteFont);
 headWriteFont.setFontName("宋体");
 //设置头居中
 headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
 // 内容策略
 WriteCellStyle writeCellStyle = new WriteCellStyle();
 // 设置内容水平居中
 writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
 //垂直居中,水平居中
 writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
 writeCellStyle.setBorderLeft(BorderStyle.THIN);
 writeCellStyle.setBorderTop(BorderStyle.THIN);
 writeCellStyle.setBorderRight(BorderStyle.THIN);
 writeCellStyle.setBorderBottom(BorderStyle.THIN);
 writeCellStyle.setWriteFont(headWriteFont);
 //设置 自动换行
 //contentWriteCellStyle.setWrapped(true);
 return new HorizontalCellStyleStrategy(headWriteCellStyle, writeCellStyle);
 }
 /**
 * 根据Excel模板，批量导入数据
 * @param file 导入的Excel
 * @param clazz 解析的类型
 * @return 解析完成的数据
 */
 public static List ? importExcel(MultipartFile file, Class ? clazz){
 if (file == null file.isEmpty()){
 throw new RuntimeException("没有文件或者文件内容为空！");
 }
 List Object dataList = null;
 BufferedInputStream ipt = null;
 try {
 InputStream is = file.getInputStream();
 // 用缓冲流对数据流进行包装
 ipt = new BufferedInputStream(is);
 // 数据解析监听器
 ExcelListener listener = new ExcelListener();
 // 读取数据
 EasyExcel.read(ipt, clazz,listener).headRowNumber(2).sheet().doRead();
 // 获取去读完成之后的数据
 dataList = listener.getDatas();
 } catch (Exception e){
 log.error(String.valueOf(e));
 throw new RuntimeException("数据导入失败！" + e);
 }
 return dataList;
 }
}
第三步：表格头部设置

public class MonthSheetWriteHandler implements SheetWriteHandler {
 private String titleName="";
 private int last=0;
 public MonthSheetWriteHandler(String titleName,int last){
 this.titleName=titleName;
 this.last=last;
 }
 @Override
 public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
 Workbook workbook = writeWorkbookHolder.getWorkbook();
 Sheet sheet = workbook.getSheetAt(0);
 Row row1 = sheet.createRow(0);
 row1.setHeight((short) 800);
 Cell cell = row1.createCell(0);
 //设置标题
 cell.setCellValue(titleName+"表");
 CellStyle cellStyle = workbook.createCellStyle();
 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
 cellStyle.setAlignment(HorizontalAlignment.CENTER);
 Font font = workbook.createFont();
 font.setBold(true);
 font.setFontHeight((short) 400);
 font.setFontName("宋体");
 cellStyle.setFont(font);
 cell.setCellStyle(cellStyle);
 sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, last));
 }
}
第四步：创建导入的监听器

public class ExcelListener extends AnalysisEventListener {
 //可以通过实例获取该值
 private List Object datas = new ArrayList ();
 @Override
 public void invoke(Object o, AnalysisContext analysisContext) {
 datas.add(o);//数据存储到list，供批量处理，或后续自己业务逻辑处理。
 doSomething(o);//根据自己业务做处理
 }
 private void doSomething(Object object) {
 //1、入库调用接口
 }
 public List Object getDatas() {
 return datas;
 }
 public void setDatas(List Object datas) {
 this.datas = datas;
 }
 public void doAfterAllAnalysed(AnalysisContext analysisContext) {
 // datas.clear();//解析结束销毁不用的资源
 }
}
第五步：service层调用导入导出方法
//导出
/**

fileName:文件名称

sheetName:sheet名称

exportList:数据源
HouseExportVO.class:实体类对象，与数据源对应
response:HttpServlet响应对象

*/

//ExcelUtil.export(fileName,sheetName,exportList, HouseExportVO.class, response); 有实体类对象的调用方式
/**

fileName:文件名称

sheetName:sheet名称

headName:头部标题名称

dataList：数据源组装

headList：动态头部列组装

response:HttpServlet响应对象

*/

//ExcelUtil.export(fileName,sheetName,headName,dataList, headList, response);动态列调用方式
//导入

//读取文件，获取数据
/**

multipartFile:导入的文件，由前端传入

HouseImportExcelDTO.class：接受数据所对应的实体对象

读取到的数据源：excelData

*/
//List ? excelData = ExcelUtil.importExcel(multipartFile, HouseImportExcelDTO.class);
自此通用的easyExcel导入导出就完成了，具体导出的表格样式自行设置！

　　以上就是easy excel 导入导出（easyexcel导入导出隐藏列）的详细内容，想要了解更多 easy excel 导入导出的内容，请持续关注盛行IT软件开发工作室。

郑重声明：本文由网友发布，不代表盛行IT的观点，版权归原作者所有，仅为传播更多信息之目的，如有侵权请联系，我们将第一时间修改或删除，多谢。

相关文章阅读