Cotroller类:
/**
* @author chen.kai
* <p>
* 导出
* </p>
* @param stu 学生对象参数
* @param response 参数
* @return String
*/
@GetMapping("export")
public String export(Student stu, HttpServletResponse response) {
List<Student> list = studentService.list(stu);
if (CollectionUtils.isNotEmpty(list)){
String[] titles = { "编号", "姓名", "生日", "备注"};
try {
OutputStream output = response.getOutputStream();
response.setHeader("Content-disposition", "attachment; filename="+ URLEncoder.encode("2020年新学员.xls","utf-8"));
response.setContentType("application/ms-excel");
HSSFWorkbook workBook = ExeclUtil.export(titles, "学生信息", list);
workBook.write(output);
output.flush();
output.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return "success";
}
导出通用类
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.nio.charset.StandardCharsets;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
/**
* @author chen.kai
* 2019/12/10
* <p>
* execl导入导出通用类
* </p>
*/
public class ExeclUtil {
private final static String SERIAL_VERSION_UID = "serialVersionUID";
private final static String CELL_TYPE_ERROR_VALUE = "未知或非法的数据类型";
private final static DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
/**
* execl导出,通用导出类
* @param titles execl的标题,即第一行的header
* @param sheetName sheet的名称
* @param datas 导出的数据,实体类集合
* @return 返回execl对象
* @throws Exception 异常抛出
*/
public static <T> HSSFWorkbook export(String[] titles, String sheetName, List<T> datas) throws Exception{
HSSFWorkbook workBook = new HSSFWorkbook();
HSSFFont font = workBook.createFont();
font.setFontName("新宋体");
//设置字体大小
font.setFontHeightInPoints((short) 12);
//设置每一格的样式
HSSFCellStyle style = workBook.createCellStyle();
style.setFont(font); // 调用字体样式对象
style.setWrapText(true);
style.setAlignment(HorizontalAlignment.CENTER);//设置居中样式
HSSFSheet sheet = workBook.createSheet(sheetName);
//创建第一行的表头
HSSFRow header = sheet.createRow(0);
//生成title,并且赋值
for (int i= 0; i < titles.length; i++) {
HSSFCell cell = header.createCell(i);
cell.setCellValue(titles[i]);
cell.setCellStyle(style);
}
//填充数据
for (int j = 0, len = datas.size(); j < len; j++) {
T t = datas.get(j);
Class<?> clas = t.getClass();
Field[] fields = clas.getDeclaredFields();
//创建行
HSSFRow row = sheet.createRow(1 + j);
int index = 0;
for (Field field: fields) {
//如果方法是序列化的标志,直接跳过
if (field.getName().equals(SERIAL_VERSION_UID)) {
continue;
}
PropertyDescriptor pd = new PropertyDescriptor(field.getName(), clas);
/* 获得get方法 */
Method method = pd.getReadMethod();
Object fieldValue = method.invoke(t);
HSSFCell cell = row.createCell(index);
cell.setCellValue(fieldValue + "");
cell.setCellStyle(style);
index++;
}
}
//设置execl数据列宽度自适应
setSizeAutoColumn(sheet, titles.length);
return workBook;
}
/**
* 通用导出
* @param titles execl的标题
* @param sheetName sheet名称
* @param datas 数据集合,Map类型的数据集合
*/
@SuppressWarnings(value= {"rawtypes", "unchecked"})
public static HSSFWorkbook exportOfMaps(String[] titles, String sheetName, List<Map<String, Object>> datas){
HSSFWorkbook workBook = new HSSFWorkbook();
HSSFFont font = workBook.createFont();
font.setFontName("新宋体");
font.setFontHeightInPoints((short) 12);//设置字体大小
//设置每一格的样式
HSSFCellStyle style = workBook.createCellStyle();
style.setFont(font); // 调用字体样式对象
style.setWrapText(true);
style.setAlignment(HorizontalAlignment.CENTER);//设置居中样式
HSSFSheet sheet = workBook.createSheet(sheetName);
//创建第一行的表头
HSSFRow header = sheet.createRow(0);
//生成title,并且赋值
for (int i= 0; i < titles.length; i++) {
HSSFCell cell = header.createCell(i);
cell.setCellValue(titles[i]);
cell.setCellStyle(style);
}
//填充数据
for (int j = 0, len = datas.size(); j < len; j++) {
Map map = datas.get(j);
//创建行
HSSFRow row = sheet.createRow(1 + j);
Iterator<Map.Entry<String, ?>> entries = map.entrySet().iterator();
int k = 0;
while(entries.hasNext()){
Map.Entry<String, ?> entry = entries.next();
HSSFCell cell = row.createCell(k);
cell.setCellValue(entry.getValue()+"");
cell.setCellStyle(style);
k++;
}
}
setSizeAutoColumn(sheet, titles.length);
return workBook;
}
// 自适应宽度(中文支持)
private static void setSizeAutoColumn(HSSFSheet sheet, int size) {
for (int k = 0; k < size; k++) {
sheet.autoSizeColumn(k);
}
for (int columnNum = 0; columnNum < size; columnNum++) {
int columnWidth = sheet.getColumnWidth(columnNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
HSSFRow currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(columnNum) != null) {
HSSFCell currentCell = currentRow.getCell(columnNum);
if (currentCell.getCellType() == CellType.STRING) {
int length = currentCell.getStringCellValue().getBytes(StandardCharsets.UTF_8).length ;
// int length = (currentCell.getStringCellValue().getBytes(StandardCharsets.UTF_8).length + currentCell.getStringCellValue().length())/2;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
sheet.setColumnWidth(columnNum, columnWidth * 256);
}
}
/**
* @author chen.kai
* 2019/12/10
* <p>
* execl文件读取
* </p>
* @param is execl文件输入流
* @param fileName 文件名称
* @return java.util.List<java.lang.String[]>
*/
public static List<Object[]> readerExecl(InputStream is, String fileName) {
Workbook workbook = getWorkBook(is, fileName);
//获取所有的sheet页码
List<Object[]> datas = new ArrayList<>(0);
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
Sheet sheet = workbook.getSheetAt(i);
//如果sheet为null,就去除所有的内容
if (null == sheet) {
continue;
}
//数据行数
int totalRows = sheet.getPhysicalNumberOfRows();
for (int j = 0; j < totalRows; j++) {
Row row = sheet.getRow(j);
if (null == row) {
continue;
}
//获取每一行的第一列和最后一列
int endCell = row.getPhysicalNumberOfCells();
Object[] cells = new Object[endCell];
for (int k = 0; k < endCell; k++) {
Cell cell = row.getCell(k);
cells[k] = getCellValue(cell);
}
datas.add(cells);
}
}
return datas;
}
private static Object getCellValue(Cell cell) {
Object value = null;
if (null == cell) {
return value;
}
switch (cell.getCellType()) {
case NUMERIC:
//如果是日期类型的数据,就返回一个yyyy-MM-dd类型的字符串
if (HSSFDateUtil.isCellDateFormatted(cell)) {
value = formater.format(cell.getDateCellValue());
break;
}
value = cell.getNumericCellValue();
//处理身份证或者手机号一类长整形的情况
if (("" + value).contains("E") || ("" + value).contains("e") || ("" + value).contains("+")) {
BigDecimal decimal = new BigDecimal("" + value);
value = decimal.toPlainString();
}
break;
case STRING:
value = cell.getStringCellValue();
break;
case FORMULA:
value = cell.getCellFormula();
break;
case BLANK:
value = "";
break;
case BOOLEAN:
value = cell.getBooleanCellValue();
break;
default:
value = CELL_TYPE_ERROR_VALUE;
break;
}
return value;
}
//获取execl
private static Workbook getWorkBook(InputStream is, String fileName) {
if (StringUtils.isBlank(fileName)) {
throw new NullPointerException("文件名称为空");
}
Workbook workBook = null;
try {
if (fileName.endsWith(".xls")) {
workBook = new HSSFWorkbook(is);
}
if (fileName.endsWith(".xlsx")) {
workBook = new XSSFWorkbook(is);
}
} catch (
IOException e) {
e.printStackTrace();
}
if (null == workBook) {
throw new NullPointerException("文件不存在");
}
return workBook;
}
}