Java实现根据在idea中创建一个excel文件

打开创建好的文件province.xls，开始定义模板，设置变量用${}形式，此处的变量要和后边java代码中的model类的字段要一致，这样才能把具体数据输出到excel模板对应的位置。
excel模板导出数据（适合导出结构复杂的excel）

导入依赖

org.jxlsjxls-poi2.10.0

org.jxlsjxls2.10.0

net.sf.jxlsjxls-core1.0.6

model类

import lombok.Data;@Data
public class AreaModel {private Integer no;private Integer areaCode;private String areaName;private Integer level;
}

util类

import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.ss.usermodel.Workbook;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.Map;public class TemplateExcelUtils {/*** 根据模板导出数据* @param fileName* @param sourcePath resource/template文件夹下路径* @param beanParams* @param response* @throws Exception*/public static void downLoadExcel(String fileName,String sourcePath, Map beanParams, HttpServletResponse response)throws Exception {try{OutputStream os = getOutputStream(fileName,response);//读取模板InputStream is = TemplateExcelUtils.class.getClassLoader().getResourceAsStream("template/"+sourcePath);XLSTransformer transformer = new XLSTransformer();//向模板中写入内容Workbook workbook = transformer.transformXLS(is, beanParams);//写入成功后转化为输出流workbook.write(os);}catch (Exception e){e.printStackTrace();throw e;}}/*** 导出文件时为Writer生成OutputStream.* @param fileName 文件名* @param response response* @return ""*/private static OutputStream getOutputStream(String fileName,HttpServletResponse response) throws Exception {try {fileName = URLEncoder.encode(fileName, "UTF-8");response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf8");response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");response.setHeader("Pragma", "public");response.setHeader("Cache-Control", "no-store");response.addHeader("Cache-Control", "max-age=0");return response.getOutputStream();} catch (IOException e) {throw new Exception("导出excel表格失败!", e);}}
}

	@Overridepublic void exportTemplateProvinceAreaData(HttpServletResponse response){List areas = areaMapper.queryProvinceArea();  // 此处是我本地项目的mapper，是为了获取省份数据List result = new ArrayList<>();areas.stream().forEach(area -> {AreaModel areaModel = new AreaModel();BeanUtils.copyProperties(area,areaModel);result.add(areaModel);});IntStream.range(0, result.size()).forEach(i -> {result.get(i).setNo(i+1);});try {Map param = new HashMap<>();param.put("title","省份数据");param.put("list",result);TemplateExcelUtils.downLoadExcel("省份数据","province.xls",param,response);} catch (Exception e) {throw new ServiceException(ResultCode.EXPORT_ERROR);}}

这是我业务层areaMapper.queryProvinceArea();所返回的省份数据

 controller层

@GetMapping("/exportTemplateData")public void exportTemplateData(HttpServletResponse response){areaService.exportTemplateProvinceAreaData(response);}

返回的excel

到这里根据模板导出就做好了。上面是一个比较简单的模板，也可以定义复杂结构的模板。

导出的数据。
注意：如果运行时报以下错，org.apache.poi.openxml4j.exceptions:Your InputStream was neith

org.apache.maven.pluginsmaven-resources-pluginxls

er an OLE2 stream, nor an OOXML stream则需要加依赖：

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！