一、首先需要引入依赖：
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.0.0</version>
</dependency>
注意 poi 版本号，版本过高，可能会出现问题。
二、ExportExcelUtil 工具类
package com.mf.common.mfUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellType;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
/**
* @className: ExportExcelUtil
* @author: chang
* @description: 导出 excel 工具类
* @date: 2022/12/3 19:01
*/
public class ExportExcelUtil {
/**
* 显示的导出表的标题
**/
private String title;
/**
* 导出表的列名
**/
private String[] rowName;
/**
* 显示的导出表的数据
**/
private List<Object[]> dataList = new ArrayList<Object[]>();
/**
* 构造函数，传入要导出的数据
* @Param title 列表标题
* @Param rowName 列名
* @Param dataList 表的数据
* @Author chang
* @Date 2022/12/3 19:04
* @return
**/
public ExportExcelUtil(String title, String[] rowName, List<Object[]> dataList) {
this.dataList = dataList;
this.rowName = rowName;
this.title = title;
}
/**
* 导出数据
* @Param out 输出流
* @Author chang
* @Date 2022/12/3 19:06
* @return
**/
public void export(OutputStream out) throws Exception {
try {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(title);
// sheet样式定义
HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);
HSSFCellStyle style = this.getStyle(workbook);
// 设置合并单元格（根据需要设置）
// sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length - 1)));
// 产生表格标题行
// HSSFRow rowm = sheet.createRow(0);
// HSSFCell cellTitle = rowm.createCell(0);
// cellTitle.setCellStyle(columnTopStyle);
// cellTitle.setCellValue(title);
// 定义所需列数
int columnNum = rowName.length;
// HSSFRow rowRowName = sheet.createRow(2);
HSSFRow rowRowName = sheet.createRow(0);
// 将列头设置到sheet的单元格中
for (int n = 0; n < columnNum; n++) {
HSSFCell cellRowName = rowRowName.createCell(n);
// cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
cellRowName.setCellType(CellType.STRING);
HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
cellRowName.setCellValue(text);
cellRowName.setCellStyle(columnTopStyle);
}
// 将查询到的数据设置到sheet对应的单元格中
for (int i = 0; i < dataList.size(); i++) {
// 遍历每个对象
Object[] obj = dataList.get(i);
// 创建所需的行数
HSSFRow row = sheet.createRow(i + 1);
for (int j = 0; j < obj.length; j++) {
HSSFCell cell = null;
if (j == 0) {
// cell = row.createCell(j, HSSFCell.CELL_TYPE_NUMERIC);
cell = row.createCell(j, CellType.NUMERIC);
cell.setCellValue(i + 1);
} else {
// cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
cell = row.createCell(j, CellType.STRING);
if (!"".equals(obj[j]) && obj[j] != null) {
cell.setCellValue(obj[j].toString());
}
}
cell.setCellStyle(style);
}
}
// 让列宽随着导出的列长自动适应
for (int colNum = 0; colNum < columnNum; colNum++) {
int columnWidth = sheet.getColumnWidth(colNum) / 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(colNum) != null) {
HSSFCell currentCell = currentRow.getCell(colNum);
// if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
if (currentCell.getCellType() == CellType.STRING) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
if (colNum == 0) {
sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
} else {
sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
}
}
if (workbook != null) {
try {
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 列头单元格样式
* @Param workbook workbook
* @Author chang
* @Date 2022/12/3 19:13
* @return HSSFCellStyle cellStyle
**/
public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
// 设置字体大小
font.setFontHeightInPoints((short) 11);
// 设置字体名字
font.setFontName("Courier New");
// 设置样式
HSSFCellStyle style = workbook.createCellStyle();
style.setFont(font);
// 设置自动换行
style.setWrapText(false);
return style;
}
/**
* 设置 CellStyle 样式
* @Param workbook workbook
* @Author chang
* @Date 2022/12/3 21:07
* @return
**/
public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
// 设置字体大小
font.setFontHeightInPoints((short) 10);
// 设置字体名字
font.setFontName("Courier New");
// 设置样式;
HSSFCellStyle style = workbook.createCellStyle();
// 在样式用应用设置的字体;
style.setFont(font);
// 设置自动换行;
style.setWrapText(false);
return style;
}
}
三、使用方式：
@RequestMapping("/testExportExcel")
public void testExportExcel(HttpServletResponse response) {
// excel标题
String title = "测试信息表";
// excel列头信息
String[] rowsName = new String[] { "列一", "列二", "列三", "列四", "列五" };
List<Object[]> dataList = new ArrayList<Object[]>();
Object[] objs = null;
for (int i = 0; i < 2; i++) {
objs = new Object[rowsName.length];
objs[0] = "1";
objs[1] = "2";
objs[2] = "3";
objs[3] = "4";
objs[4] = "5";
dataList.add(objs);
}
// 给文件命名。随机命名
String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
// 告诉浏览器数据格式，将头和数据传到前台
String headStr = "attachment; filename=\"" + fileName + "\"";
response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content-Disposition", headStr);
OutputStream out = null;
try {
out = response.getOutputStream();
// 调用poi的工具类
ExportExcelUtil ex = new ExportExcelUtil(title, rowsName, dataList);
try {
ex.export(out);
} catch (Exception e) {
e.printStackTrace();
}
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
网友评论