方法一(根据实体注解 @Excel() 映射list 导出列表)
package com.wenzheng.gzw.personnel.persistence.entity;
import cn.afterturn.easypoi.excel.annotation.Excel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import javax.persistence.Transient;
@Data
public class ExportEnterPersonInfo {
private static final long serialVersionUID = -76555548734764501L;
/**
* 编号
*/
@ApiModelProperty(value = "编号")
@Excel(name = "编号")
private String serialNo;
/**
* 姓名
*/
@ApiModelProperty(value = "姓名")
@Excel(name = "姓名")
private String name;
/**
* 身份证号
*/
@ApiModelProperty(value = "身份证号")
@Excel(name = "身份证号")
private String idNumber;
/**
* 性别
*/
@ApiModelProperty(value = "性别")
@Excel(name = "性别")
private String gender;
/**
* 年龄
*/
@ApiModelProperty(value = "年龄")
@Excel(name = "年龄")
private Integer age;
/**
* 民族
*/
@ApiModelProperty(value = "民族")
@Excel(name = "民族")
private String nation;
/**
* 学历
*/
@ApiModelProperty(value = "学历")
@Excel(name = "是否党员")
private String isDy;
}
/**依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.1.0</version>
</dependency>
**/
@Override
public Object exportEnterPerson(HttpServletResponse response, LoginUser user, String depId) {
//查询list 集合 select * from 表名
List<ExportEnterPersonInfo> mapList = pmGzwPersonnelInfoDao.selectByOrgCodeInfo(depId, user.getFiscalYear(), user.getMofDivCode());
if (CollectionUtils.isEmpty(mapList)) {
return null;
}
String orgName = mapList.get(0).getOrganizationCompany();
try (OutputStream out = response.getOutputStream()) {
ExportParams exportParams = new ExportParams();
//表标题第一列
exportParams.setTitle(orgName+"人员信息统计表");
//表第二列
exportParams.setSecondTitle("企业名称:" + mapList.get(0).getOrganizationCompany());
//excel下面的sheet 的标题
exportParams.setSheetName("企业人员清单");
//把list集合根据实体Enty.class 映射到表里面去
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, ExportEnterPersonInfo.class, mapList);
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" +
URLEncoder.encode(orgName+"人员信息统计表"+".xls", "UTF-8"));
ByteArrayOutputStream baos = new ByteArrayOutputStream();
workbook.write(baos);
response.setHeader("Content-Length", String.valueOf(baos.size()));
out.write( baos.toByteArray() );
} catch (Exception e) {
try {
throw new Exception(e.getMessage());
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
return null;
}
}
方法二导出图片,用模板导出
/**依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.1.0</version>
</dependency>
**/
/**
* 导入图片文件
* @param multipartFile
*/
@PostMapping("/importAll")
public void importAll(MultipartFile multipartFile) {
if (multipartFile.isEmpty()) {
return;
}
ImportParams importParams = new ImportParams();
importParams.setTitleRows(1);
importParams.setHeadRows(1);
File file = new File(multipartFile.getOriginalFilename());
try {
FileUtils.copyInputStreamToFile(multipartFile.getInputStream(), file);
} catch (IOException e) {
e.printStackTrace();
}
List<Goods> goodsList = ExcelImportUtil.importExcel(file, Goods.class, importParams);
System.out.println(goodsList.size());
}
/**
* 使用模板导出
* @param response
*/
@GetMapping("/exportByTemp")
public void exportByTemp(HttpServletResponse response) {
TemplateExportParams templateExportParams = new TemplateExportParams("templates/专项支出用款申请书.xlsx");
Map<String, Object> map = new HashMap<>();
map.put("date", "2021-01-17");
map.put("username", "张三");
map.put("telephone", "13012345678");
map.put("money", "5000");
ArrayList<Map<String, Object>> list = new ArrayList<>();
for (int i = 0; i < 10; i++) {
Map<String, Object> itemMap = new HashMap<>();
itemMap.put("no", i);
itemMap.put("accountType", "资金性质");
itemMap.put("code", "Code1000" + i);
itemMap.put("name", "名称");
itemMap.put("projectName", "项目名称");
itemMap.put("fullName", "全称");
itemMap.put("account", "银行账户");
itemMap.put("bankName", "开户银行");
itemMap.put("amount", "申请金额");
itemMap.put("realAmount", "核定金额");
list.add(itemMap);
}
map.put("list", list);
Workbook workbook = ExcelExportUtil.exportExcel(templateExportParams, map);
response.setHeader("content-Type", "application/vnd.ms-excel");
try {
response.setHeader("content-Disposition", "attachment;filename=" + URLEncoder.encode("专项支出用款申请书.xlsx", "utf-8"));
workbook.write(response.getOutputStream());
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
方法二:直接上代码
package zq.oa.test;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.IndexedColors;
public class Test {
public static void main(String[] args) throws IOException {
Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
CellStyle hlink_style = wb.createCellStyle();
Font hlink_font = wb.createFont();
hlink_font.setUnderline(Font.U_SINGLE);
hlink_font.setColor(IndexedColors.BLUE.getIndex());
hlink_style.setFont(hlink_font);
Cell cell;
Sheet sheet = wb.createSheet("Hyperlinks");
//URL
cell = sheet.createRow(0).createCell((short)0);
cell.setCellValue("超链接");
Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);
link.setAddress("http://www.baidu.com/");
cell.setHyperlink(link);
cell.setCellStyle(hlink_style);
//link to a file in the current directory
cell = sheet.createRow(1).createCell((short)0);
cell.setCellValue("File Link");
link = createHelper.createHyperlink(Hyperlink.LINK_FILE);
link.setAddress("link1.xls");
cell.setHyperlink(link);
cell.setCellStyle(hlink_style);
//e-mail link
cell = sheet.createRow(2).createCell((short)0);
cell.setCellValue("Email Link");
link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL);
//设置路径
link.setAddress("mailto:poi@apache.org?subject=Hyperlinks");
cell.setHyperlink(link);
cell.setCellStyle(hlink_style);
//create a target sheet and cell
Sheet sheet2 = wb.createSheet("Target Sheet");
sheet2.createRow(0).createCell((short)0).setCellValue("Target Cell");
cell = sheet.createRow(3).createCell((short)0);
cell.setCellValue("Worksheet Link");
Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
link2.setAddress("'Target Sheet'!A1");
cell.setHyperlink(link2);
cell.setCellStyle(hlink_style);
FileOutputStream out = new FileOutputStream("D:/hyperinks.xlsx");
wb.write(out);
out.close();
}
}