代码
/** * @ClassName ExcelUtil * @Description TODO Excel工具类 * @Date 2021/1/11 17:26 */
@Slf4j
public class ExcelUtil<T> implements Serializable {
private static final long serialVersionUID = 230702500721813925L;
/** * Excel类型 * HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls * XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx */
private static final String EXCEL_TYPE_XLS = "application/vnd.ms-excel";
private static final String EXCEL_TYPE_XLSX = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
/** * HttpServletResponse */
private HttpServletResponse response;
/** * ServletOutputStream */
private ServletOutputStream outputStream;
/** * 默认列宽 */
private short columnWidth = (short) 5000;
/** * 默认字体 */
private String fontName = "宋体";
/** * 默认字体大小 */
private short fontHigth = (short) 12;
/** * 首行小标题 */
private String[] romTitle;
/** * 样式 */
private CellStyle cellStyle;
/** * 字体 */
private Font font;
/** * 使用默认样式 */
public ExcelUtil() {
}
/** * 自定义样式 */
public ExcelUtil(short columnWidth, String fontName, short fontHigth) {
this.columnWidth = columnWidth;
this.fontName = fontName;
this.fontHigth = fontHigth;
}
/** * 在每次创建对象时获取HttpServletResponse对象 */ {
try {
response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
outputStream = response.getOutputStream();
} catch (IOException e) {
log.error("在类加载时获取HttpServletResponse对象失败");
}
}
/** * 设置首行小标题，标题之间用逗号隔开 */
public void setRowTitle(String titles) {
this.romTitle = titles.split(",");
}
/** * @return org.apache.poi.ss.usermodel.Workbook * @Description TODO 导出2007版本Excel * @Param [excelName 导出Excel名称,titles 首行小标题, content 具体数据] * @date 2021/1/5 14:33 * @auther liubo */
public void exportXlsxExcel(String excelName, List<T> content) throws Exception {
XSSFWorkbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
// 设置列宽
for (int i = 0; i < romTitle.length; i++) {
sheet.setColumnWidth((short) i, columnWidth);
}
cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
font = workbook.createFont();
font.setFontName(fontName);
font.setColor(Font.COLOR_NORMAL);
font.setFontHeightInPoints(fontHigth);
cellStyle.setFont(font);
try {
setSheetFirstRowTitle(sheet);
// 具体内容
if (null != content && content.size() > 0) {
for (int i = 0; i < content.size(); i++) {
Row row = sheet.createRow(sheet.getLastRowNum() + 1);
Class<?> contentsClass = content.get(i).getClass();
Field[] fields = contentsClass.getDeclaredFields();
if (null != fields && fields.length > 0) {
for (int j = 0; j < fields.length; j++) {
Field field = fields[j];
System.out.println(field.getName());
System.out.println();
field.setAccessible(true);
if (null != field.get(content.get(i))) {
row.createCell(j).setCellValue(String.valueOf(field.get(content.get(i))));
}
}
}
}
}
response.reset();
response.setHeader("Content-Type", "application/x-msdownload");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excelName + ".xlsx", "UTF-8"));
response.setHeader("Cache-Control", "no-cache");
response.setHeader("Pragma", "no-cache");
workbook.write(outputStream);
} catch (IllegalAccessException e) {
throw new IllegalAccessException(e.getMessage());
} finally {
workbook.close();
outputStream.close();
}
}
/** * @return void * @Description TODO 导出Excel模板 * @Param [excelName] * @date 2021/1/14 13:33 * @author liubo */
public void expoetXlxsExcelTemplate(String excelName) throws Exception {
XSSFWorkbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
// 设置列宽
for (int i = 0; i < romTitle.length; i++) {
sheet.setColumnWidth((short) i, columnWidth);
}
cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
font = workbook.createFont();
font.setFontName(fontName);
font.setColor(Font.COLOR_NORMAL);
font.setFontHeightInPoints(fontHigth);
cellStyle.setFont(font);
try {
setSheetFirstRowTitle(sheet);
response.reset();
response.setHeader("Content-Type", "application/x-msdownload");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excelName + ".xlsx", "UTF-8"));
response.setHeader("Cache-Control", "no-cache");
response.setHeader("Pragma", "no-cache");
workbook.write(outputStream);
} catch (Exception e) {
} finally {
workbook.close();
outputStream.close();
}
}
/** * @return T * @Description TODO 导入2007版Excel进行解析(结果为集合) * @Param [obj, file] * @date 2021/1/13 10:37 * @author liubo */
public List<T> importXlsxExcel(T obj, MultipartFile file) {
List<T> list = new ArrayList<>();
Class<?> aClass = obj.getClass();
Field[] fields = aClass.getDeclaredFields();
try {
// 获取文件输入流
InputStream inputStream = file.getInputStream();
// 创建XSSFWorkbook将输入流作为参数
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
//获取第一行页 sheet
Sheet sheet = workbook.getSheetAt(0);
// 获取第一行
Row row = sheet.getRow(0);
//获取最大行数
int rowNum = sheet.getPhysicalNumberOfRows();
//获取最大列
int maxColNum = row.getPhysicalNumberOfCells();
// 行
for (int i = 1; i < rowNum; i++) {
T o = (T) aClass.newInstance();
// 每一行
Row row1 = sheet.getRow(i);
// 列
for (int j = 0; j < maxColNum; j++) {
Cell cell = row1.getCell(j);
if (null != fields && fields.length > 0) {
fields[j].setAccessible(true);
if (fields[j].getType().getSimpleName().equals("String")) {
fields[j].set(o, cell.getStringCellValue());
} else {
System.out.println(cell.getNumericCellValue());
fields[j].set(o, cell.getNumericCellValue());
}
}
}
list.add(o);
}
} catch (Exception e) {
throw new WarehouseException("Excel导入失败");
}
return list;
}
/** * @return void * @Description TODO 设置首行小标题 * @Param [sheet] * @date 2021/1/14 13:25 * @author liubo */
private void setSheetFirstRowTitle(Sheet sheet) {
// 小标题
if (Objects.nonNull(romTitle)) {
Row row = sheet.createRow(0);
for (int i = 0; i < romTitle.length; i++) {
Cell cell = row.createCell(i);
cell.setCellStyle(cellStyle);
cell.setCellValue(romTitle[i]);
}
}
}
}
demo链接
关于
近期在开发中遇到一个问题，就是将数据库中的数据导出成excel，就此问题编写了一个Excel小工具类，如有不对，可以一起探讨。
京公网安备 11010502036488号