1、Excel表格的导出:
1、搭建一个springboot项目:
2、maven中加入EasyExcel所需包
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
2、模拟学生实体类Student
package com.lemon.entity;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
/**
* 学生类
* @author lemon
* @since 2020-05-26
*/
@ExcelIgnoreUnannotated()
public class Student {
private Integer myId;
@ExcelProperty(value = "学号",index = 0) //0 对应导出Excel表格的第一列
private Integer id;
@ExcelProperty(value = "姓名",index = 1)
private String name;
@ExcelProperty(value = "性别",index = 2)
private String sex;
@ExcelProperty(value = "班级",index = 3)
private String grade;
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", grade='" + grade + '\'' +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getGrade() {
return grade;
}
public void setGrade(String grade) {
this.grade = grade;
}
}
3、导出功能controller:
package com.lemon.controller;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.StringUtils;
import com.lemon.easyutils.EasyExcelListener;
import com.lemon.entity.Student;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* 测试controller
* @author lemon
* @since 2020/5/26 0026
*/
@Controller
@RequestMapping("/test")
public class TestController {
/**
* 主页面
* @author lemon
* @since 2020/5/26 0026
*/
@RequestMapping("/index")
public String index(){
return "/views/index";
}
/**
* 导出
* @author lemon
* @since 2020/5/26 0026
*/
@RequestMapping("/export")
public void export(HttpServletRequest request, HttpServletResponse response) throws Exception {
//模拟需要导出的数据
List<Student> list = new ArrayList<Student>();
Student student = new Student();
student.setId(1);
student.setName("1321");
student.setSex("男");
student.setGrade("一年级");
for (int i = 0; i < 5; i++) {
list.add(student);
}
//设置并导出
// 这里注意 有同学反应使用swagger 会导致各种问题,请直接用浏览器或者用postman
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
//设置文件名
SimpleDateFormat fDate = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");
String fileName = "导出测试表" + fDate.format(new Date()) + ".xlsx";
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
EasyExcel.write(response.getOutputStream(), Student.class).sheet("sheet1").doWrite(list);
}
}
4、导出功能的页面:
<html>
<head>
<title>测试</title>
</head>
<body>
<button onclick="myExport()" >导出</button>
</body>
</html>
<script>
function myExport() {
window.location.href = "/test/export"
}
</script>
5、测试:
点击导出 选择导出位置:
一下为输出内容格式设置:
6、设置单元格格式为文本
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import java.util.List;
/**
* 设置单元格格式
* @author lemon
* @since 2020/8/11 0011
*/
public class EasyExcelRowWriteHandlerUtils implements CellWriteHandler {
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
//设置单元格格式为文本
Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
DataFormat dataFormat = workbook.createDataFormat();
cellStyle.setDataFormat(dataFormat.getFormat("@"));
cell.setCellStyle(cellStyle);
//设置0-30列为文本形式
for (int i = 0; i < 30; i++) {
writeSheetHolder.getSheet().setDefaultColumnStyle(i, cellStyle);
}
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
}
用法:
EasyExcelFactory.write(response.getOutputStream(),
Student.class).registerWriteHandler(new
EasyExcelRowWriteHandlerUtils()).sheet().doWrite(new ArrayList());
7、导出表格带下拉框
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import java.util.Map;
/**
* 设置单元格下拉框
* @author lemon
* @since 2020/7/27
*/
public class EasyExcelSheetSelectUtils implements SheetWriteHandler {
//第0列开始 设置某列的下拉内容
private Map<Integer,String[]> mapDropDown;
public EasyExcelSheetSelectUtils(Map<Integer, String[]> mapDropDown) {
this.mapDropDown = mapDropDown;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Sheet sheet = writeSheetHolder.getSheet();
///开始设置下拉框 //设置下拉框
DataValidationHelper helper = sheet.getDataValidationHelper();
for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {
/***起始行、终止行、起始列、终止列**/
CellRangeAddressList addressList = new CellRangeAddressList(1, 1000, entry.getKey(), entry.getKey());
/***设置下拉框数据**/
DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
DataValidation dataValidation = helper.createValidation(constraint, addressList);
/***处理Excel兼容性问题**/
if (dataValidation instanceof XSSFDataValidation) {
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
}
}
}
用法:
//性别下拉框设置
String[] sexStr = new String[]{"男","女"};
//设置下拉框内容
Map<Integer,String []> mapDropDown = new HashMap<>();
//设置在第五列
mapDropDown.put(5,sexStr);
//设置在第7列
mapDropDown.put(7,sexStr);
EasyExcelFactory.write(response.getOutputStream(), Student.class).registerWriteHandler(new EasyExcelSheetSelectUtils(mapDropDown)).sheet().doWrite(new ArrayList());