前言
提示:无法显示DateTime类型,带有秒的时间数据。
一、Controller层
import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.StyleSet;
import com.netrust.pojo.User2;
import com.netrust.utils.ExcelOutUtil;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
@RestController
@RequestMapping("")
public class ExcelOutController {
@Value("${Excel.path}")
private String path;
@GetMapping("/export")
public void export(HttpServletResponse response) throws IOException {
List<User2> list = new ArrayList<>();
list.add(new User2("001","小三","13411112222","三根毛",new Date()));
list.add(new User2("002","小四","13411112222","三根毛",new Date()));
list.add(new User2("003","","13411112222","三根毛",new Date()));
list.add(new User2("004","小六","","三根毛",new Date()));
list.add(new User2("小七三根毛大家啊嘎啊噶跟家里刚啊刚来",new Date()));
list.add(new User2("006","小八","13411112222","三根毛", new Date()));
// 通过工具类创建writer,默认创建xls格式
ExcelWriter writer = ExcelUtil.getWriter(true);
// 合并单元格后的标题行,使用默认标题样式
writer.merge(4, "新增员工信息");
//自定义标题别名
writer.addHeaderAlias("deptId", "学号");
writer.addHeaderAlias("name", "姓名");
writer.addHeaderAlias("phone", "手机号");
writer.addHeaderAlias("avatar", "图像");
writer.addHeaderAlias("createdTime", "创建时间");
//写数据 writer.write(data, true);
writer.write(list, true);
// 0表示第一行,行高。
writer.setRowHeight(0, 25);
// 0表示第一行,设置列宽。
// writer.setColumnWidth(0, 10);
/**
* 标题样式设置
*/
CellStyle headCellStyle = writer.getHeadCellStyle();
Font font = ExcelOutUtil.createFont(writer, true, false, "宋体", 18);
headCellStyle.setFont(font);
/**
* 普通单元格,全局样式设置
*/
StyleSet styleSet = writer.getStyleSet();
CellStyle cellStyle = styleSet.getCellStyle();
Font font1 = ExcelOutUtil.createFont(writer, false, false, "宋体", 14);
cellStyle.setFont(font1);
//时间格式是 14 : yyyy/MM/dd
// CellStyle cellStyleForDate = styleSet.getCellStyleForDate();
// cellStyleForDate.setDataFormat((short)14);
// 设置列宽
Sheet sheet = writer.getSheet();
int columnCount = writer.getColumnCount();
ExcelOutUtil.setColumnWidth(sheet, columnCount);
// 设置普通单元格自动换行
cellStyle.setWrapText(true);
//设置content—type
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset:utf-8");
//设置标题
String fileName= URLEncoder.encode("导入员工表","UTF-8");
response.setHeader("Content-Disposition","attachment;filename="+fileName+".xlsx");
// String currentDate = LocalDate.now().format(DateTimeFormatter.ofPattern("yyyyMMdd"));
// String yyyyMMdd = path + currentDate + File.separator;
// String newPath = yyyyMMdd + fileName + ".xlsx";
// FileOutputStream fileOutputStream = new FileOutputStream(newPath);
ServletOutputStream outputStream = null;
try {
outputStream = response.getOutputStream();
writer.flush(outputStream, true);
} catch (IOException e) {
e.printStackTrace();
} finally {
// 关闭writer,释放内存
writer.close();
}
//此处记得关闭输出Servlet流
IoUtil.close(outputStream);
}
}
二、工具类
import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.StyleSet;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
@Slf4j
public class ExcelOutUtil {
/**
* 设置基础字体样式
* @param writer
* @param bold 是否粗体
* @param italic
* @param fontName 字体名称
* @param fontSize 字体大小
* @return
*/
public static Font createFont(ExcelWriter writer, boolean bold, boolean italic, String fontName, int fontSize) {
Font font = writer.getWorkbook().createFont();
//设置字体名称 宋体 / 微软雅黑 /等
font.setFontName(fontName);
//设置是否斜体
font.setItalic(italic);
//设置字体大小 以磅为单位
font.setFontHeightInPoints((short) fontSize);
//设置是否加粗
font.setBold(bold);
return font;
}
/**
* 方法描述: 全局基础样式设置
* 默认 全局水平居中+垂直居中
* 默认 自动换行
* 默认单元格边框颜色为黑色,细线条
* 默认背景颜色为白色
*
* @param writer writer
* @param font 字体样式
* @return cn.hutool.poi.excel.StyleSet
* @author wqf
* @date 2021/5/28 10:43
*/
public static StyleSet setBaseGlobalStyle(ExcelWriter writer, Font font) {
//全局样式设置
StyleSet styleSet = writer.getStyleSet();
//设置全局文本居中
styleSet.setAlign(HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
//设置全局字体样式
styleSet.setFont(font, true);
//设置背景颜色 第二个参数表示是否将样式应用到头部
styleSet.setBackgroundColor(IndexedColors.WHITE, true);
//设置自动换行 当文本长于单元格宽度是否换行
//styleSet.setWrapText();
// 设置全局边框样式
styleSet.setBorder(BorderStyle.THIN, IndexedColors.BLACK);
return styleSet;
}
/**
* 自动列宽
* @param sheet
* @param columnNumber 列号
*/
public static void setColumnWidth(Sheet sheet, int columnNumber) {
for (int i = 0; i < columnNumber; i++) {
int orgWidth = sheet.getColumnWidth(i);
// 自动调整列宽
sheet.autoSizeColumn(i, true);
int newWidth = (int) (sheet.getColumnWidth(i) + 100);
int maxWith = 256*255;
int LimitWidth = 256*150;
//限制下最大宽度
if(newWidth > maxWith) {
sheet.setColumnWidth(i, LimitWidth);
}else if (newWidth > orgWidth) {
sheet.setColumnWidth(i, newWidth);
} else {
sheet.setColumnWidth(i, orgWidth);
}
}
}
/**
* 方法描述: 下载excel文件
*
* @param response 响应
* @param fileName 文件名称
* @param writer writer
* @return void
* @author wqf
* @date 2021/5/24 16:20
*/
private static void downloadExcel(HttpServletResponse response, String fileName, ExcelWriter writer) {
response.setContentType("application/vnd.ms-excel;charset=utf-8");
// test.xls是弹出下载对话框的文件名,不能为中文,中文请自行编码
ServletOutputStream out = null;
try {
// 设置请求头属性
response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), StandardCharsets.ISO_8859_1));
out = response.getOutputStream();
// 写出到文件
writer.flush(out, true);
// 关闭writer,释放内存
writer.close();
// 此处记得关闭输出Servlet流
IoUtil.close(out);
} catch (IOException e) {
log.error(e.getMessage());
e.printStackTrace();
}
}
}
三、实体类
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User2 {
private String deptId;
private String name;
private String phone;
private String avatar;
private Date createdTime;
}
四、pom.xml
<!-- Hutool 工具类-->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.14</version>
</dependency>
总结
.========================努力干吧!菜鸟。