废话不多说直接码代码
一、使用的pom包
<!-- 使用pom包即可 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.0</version>
</dependency>
二、Excel工具类
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
public class ExcelExportUtil {
public static void ExportExcel(Map<String, Object> param, HttpServletResponse response) {
try {
String title = param.get("title") + "";
URLEncoder.encode(title+ ".xls", "UTF-8"));
List<String> colum = (List<String>) param.get("colum");
List<String> columnm = (List<String>) param.get("columnm");
List<Map<String, Object>> dataList = (List<Map<String, Object>>) param.get("list");
ServletOutputStream os = response.getOutputStream();
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(title);
// 产生表格标题行
HSSFRow rowm = sheet.createRow(0);
HSSFCell cellTitle = rowm.createCell(0);
//sheet样式定义【】
HSSFCellStyle columnTopStyle = ExcelExportUtil.getColumnTopStyle(workbook, 13);
//HSSFCellStyle style=this.getStyle(workbook);
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (colum.size() - 1)));
cellTitle.setCellStyle(columnTopStyle);
cellTitle.setCellValue(title);
// 定义所需列数
int columnNum = colum.size();
HSSFRow rowRowName = sheet.createRow(2);
// 将列头设置到sheet的单元格中
for (int n = 0; n < columnNum; n++) {
HSSFCell cellRowName = rowRowName.createCell(n);
cellRowName.setCellType(CellType.STRING);
HSSFRichTextString text = new HSSFRichTextString(colum.get(n));
cellRowName.setCellValue(text);
//cellRowName.setCellStyle(columnTopStyle);
}
// 将查询到的数据设置到sheet对应的单元格中
for (int i = 0; i < dataList.size(); i++) {
Map<String, Object> obj = dataList.get(i);// 遍历每个对象
HSSFRow row = sheet.createRow(i + 3);// 创建所需的行数
for (int j = 0; j < colum.size(); j++) {
HSSFCell cell = row.createCell(j, CellType.STRING);
cell.setCellValue(null == obj.get(columnm.get(j)) ? "" : obj.get(columnm.get(j)) + "");
}
}
// 让列宽随着导出的列长自动适应
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() == 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);
}
}
// 清除缓存
response.reset();
// 指定下载的文件名
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(title + ".xls", "UTF-8"));
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Access-Control-Allow-Origin", "*");
response.setHeader("Access-Control-Allow-Credentials", "true");
response.setHeader("Access-Control-Allow-Methods", "POST,OPTIONS, GET, PATCH, DELETE, PUT");
response.setHeader("Access-Control-Max-Age", "3600");
response.setHeader("Access-Control-Allow-Headers", "Origin,authorization, X-Requested-With, Content-Type, Accept");
if (workbook != null) {
try {
workbook.write(os);
} catch (Exception e) {
e.printStackTrace();
} finally {
os.flush();
os.close();
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook, int fontsize) {
// 设置字体
HSSFFont font = workbook.createFont();
// 设置字体大小
font.setFontHeightInPoints((short) fontsize);
// 字体加粗
font.setBold(true);
// 设置字体名字
font.setFontName("Courier New");
// 设置样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置低边框
//style.setBorderBottom(BorderStyle.DOUBLE);
//style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// 设置低边框颜色
//style.setBottomBorderColor(HSSFColor.BLACK.index);
// 设置右边框
// style.setBorderRight(HSSFCellStyle.BORDER_THIN);
// 设置顶边框
// style.setTopBorderColor(HSSFColor.BLACK.index);
// 设置顶边框颜色
// style.setTopBorderColor(HSSFColor.BLACK.index);
// 在样式中应用设置的字体
style.setFont(font);
// 设置自动换行
style.setWrapText(false);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
}
三、前端传入示例
const colum= ['行政区划', '行政村名称', '流域代码', '预警级别', '洪峰流量', '峰现时间', '预警阈值']
const columnm = ['ADNMX', 'ADNM', 'WSCD', 'WARNLEVEL', 'MAXQ', 'MAXQTM', 'CZZF']
title: '',
list: this.tableData //数据集