java中poi导出Excel表格,前端以流的方式接收,而非直接生成文件再下载,解决多台服务器部署后,路径地址不统一导致的下载问题。
生成Excel示例图:
2、代码说明
① 在上次的基础上增加了底部数据的写入,有时候我们可能需要在表格中实现上图所示的表格合并,但是每一行的合并格数都会有不同的情况。所以在以前的基础上做以修改,加上了自定义一行中动态显示几列的功能。
② 功能实现原理:利用list中包含HashMap的方式,一个list元素表示一行,map.size()表示这一行需要放几列。比如在红色的框中我们放三列,那么new HashMap()对象中添加三对元素(map的key表示这一行的第几列数据,value表示显示的值)。最后一行只有一列,那么在map中放一对值即可。注意map的key是从0开始哦
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.prefs.BackingStoreException;
/**
* @liul
* @date 2021/12/30
*/
public class ExcelPortUtil {
@Autowired
private static RuoYiConfig ruoYiConfig;
/**
* @param sheetName 工作表名,文件名,头部信息
* @param listName 列名
* @param list 需要写入的数据
* @param listBottom 底部写入信息:<列位置,数据>
* @param response 返回
*/
public static AjaxResult excelPort(String sheetName, List<String> listName, List<Map<String, String>> list, List<Map<Integer, String>> listBottom, HttpServletResponse response) {
OutputStream os = null;
try {
if (list.size() == 0) {
throw new BackingStoreException("数据为空");
}
// 声明一个工作簿
XSSFWorkbook wb = new XSSFWorkbook();
// 创建sheet页
XSSFSheet sheet = wb.createSheet(sheetName);
sheet.setDefaultColumnWidth(19);
// 表头
XSSFRow rowReportTitle = sheet.createRow(0);
Cell cell1 = rowReportTitle.createCell(0); // 0列
// 设置值
cell1.setCellValue(sheetName);
// 合并表头
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, listName.size() - 1));
rowReportTitle.setHeight((short) 600); // 行高
//设置表头字体
Font headFont = wb.createFont();
headFont.setFontName("宋体");
headFont.setFontHeightInPoints((short) 18);// 字体大小
CellStyle headStyle = wb.createCellStyle();
headStyle.setFont(headFont);
headStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
headStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
// 头部样式添加
cell1.setCellStyle(headStyle);
// 全局加线样式
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
cellStyle.setBorderTop(BorderStyle.THIN);//上边框
cellStyle.setBorderRight(BorderStyle.THIN);//右边框
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
// 记录标题信息
TreeMap<String, Integer> headMap = new TreeMap<>();
// 标题写入
XSSFRow row = sheet.createRow(1);
for (int i = 0; i < listName.size(); i++) {
row.setHeight((short) 450);
XSSFCell cell = row.createCell(i);
String headName = listName.get(i);
cell.setCellValue(headName); // 写入列名
headMap.put(headName, i);
cell.setCellStyle(cellStyle);
}
// 写入内容数据
int ind = 2;
for (Map<String, String> map : list) {
XSSFRow r = sheet.createRow(ind++);
for (Map.Entry<String, Integer> m : headMap.entrySet()) {
String name = m.getKey(); // 列名
String value = map.get(name); // value 不一定存在
XSSFCell cell2 = r.createCell(m.getValue());
if (value != null) {
cell2.setCellValue(value);
}
cell2.setCellStyle(cellStyle);
}
}
// 底部样式
CellStyle bottomStyle = wb.createCellStyle();
bottomStyle.setBorderBottom(BorderStyle.THIN); //下边框
bottomStyle.setBorderLeft(BorderStyle.THIN);//左边框
bottomStyle.setBorderTop(BorderStyle.THIN);//上边框
bottomStyle.setBorderRight(BorderStyle.THIN);//右边框
bottomStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
// 写入底部
if (listBottom != null) {
int columnNum = listName.size(); // 当前表有多少列
for (Map<Integer, String> map : listBottom) {
XSSFRow bottom = sheet.createRow(ind);
bottom.setHeight((short) 400); // 行高
// 当前行分几列
int size = map.size();
if (columnNum % 2 == 0 & size % 2 == 0) {
// 都是偶数执行
int c = columnNum / size; // 列大小
for (int i = 0; i < size; i++) {
CellRangeAddress cellAddresses0 = new CellRangeAddress(ind, ind, i * c, i * c + c - 1);
sheet.addMergedRegion(cellAddresses0);
XSSFCell c0 = bottom.createCell(cellAddresses0.getFirstColumn());
c0.setCellValue(map.get(i));
c0.setCellStyle(bottomStyle);
for (int n = cellAddresses0.getFirstColumn() + 1; n <= cellAddresses0.getLastColumn(); n++) {
XSSFCell cn = bottom.createCell(n);
cn.setCellStyle(bottomStyle);
}
}
} else if (!(columnNum % 2 == 0) & !(size % 2 == 0)) {
// 都是奇数执行
int c = columnNum - 1 / size;
for (int i = 0; i < size; i++) {
CellRangeAddress cellAddresses1;
if (size - i <= 1) {
cellAddresses1 = new CellRangeAddress(ind, ind, i * c, i * c + c);
} else {
cellAddresses1 = new CellRangeAddress(ind, ind, i * c, i * c + c - 1);
}
sheet.addMergedRegion(cellAddresses1);
XSSFCell c0 = bottom.createCell(cellAddresses1.getFirstColumn());
c0.setCellValue(map.get(i));
c0.setCellStyle(bottomStyle);
for (int n = cellAddresses1.getFirstColumn() + 1; n <= cellAddresses1.getLastColumn(); n++) {
XSSFCell cn = bottom.createCell(n);
cn.setCellStyle(bottomStyle);
}
}
} else {
// 奇偶不同
int c = (columnNum + 1) / size;
for (int i = 0; i < size; i++) {
CellRangeAddress cellAddresses2;
if (size - i <= 1) {
cellAddresses2 = new CellRangeAddress(ind, ind, i * c, i * c + c - 2);
} else {
cellAddresses2 = new CellRangeAddress(ind, ind, i * c, i * c + c - 1);
}
sheet.addMergedRegion(cellAddresses2);
XSSFCell c0 = bottom.createCell(cellAddresses2.getFirstColumn());
c0.setCellValue(map.get(i));
c0.setCellStyle(bottomStyle);
for (int n = cellAddresses2.getFirstColumn() + 1; n <= cellAddresses2.getLastColumn(); n++) {
XSSFCell cn = bottom.createCell(n);
cn.setCellStyle(bottomStyle);
}
}
}
ind++;
}
}
// 输出Excel文件
os = response.getOutputStream();
response.setContentType("application/x-download");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + new String(sheetName.getBytes(), "ISO8859-1") + ".xls");
wb.write(os);
os.flush();
return AjaxResult.success("管理记录.xls");
} catch (Exception e) {
e.printStackTrace();
throw new BusinessException("导出Excel失败,请联系网站管理员!");
}finally {
if(os!=null){
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
调用工具类: 没有的字段 就是的参数就是vsTravelRecordDyg中拿出来的
@GetMapping("/export")
@ResponseBody
public String export(@ModelAttribute VsTravelRecordDyg vsTravelRecordDyg , HttpServletResponse response)
{
// 列名
List<String> listName = Arrays.asList("时间", "入库数量", "使用", "库存", "运行状况", "DPD更换", "运维人员", "站点");
String[] split = dateMonth.split("-");
String startTime = getStartTime(Integer.parseInt(split[0]), Integer.parseInt(split[1]));
String endTime = getEndTime(Integer.parseInt(split[0]), Integer.parseInt(split[1]));
// 查询数据
List<DrugVO> drugVOS = this.statisticAnalysisMapper.getDrugPage(startTime, endTime, stationId);
// 列名 数据
List<Map<String, String>> list = new ArrayList<>();
for (DrugVO vo : drugVOS) {
Map<String, String> map = new HashMap<>();
map.put("时间", sdf.format(vo.getDrugTime()));
map.put("入库数量", vo.getDrugDepositNum() + "");
map.put("使用", vo.getDrugUseNum() + "");
map.put("库存", vo.getDrugInventoryNum() + "");
map.put("运行状况", vo.getDeviceState() == 1 ? "正常" : "异常");
map.put("DPD更换", vo.getDrugReplace());
map.put("运维人员", vo.getOperationName());
map.put("站点", vo.getStationName());
list.add(map);
}
ArrayList<Map<Integer, String>> bottomList = new ArrayList<>();
Map<Integer, String> p = new HashMap<>();
p.put(0, "月总用药量:150");
p.put(1, "月入库药量:166");
p.put(2, "月处理水量:166吨");
bottomList.add(p);
HashMap<Integer, String> map = new HashMap<>();
map.put(0,"ceshi");
bottomList.add(map);
// 将需要写入Excel的数据传入 如果不需要底部样式 可传NULL
ExcelPortUtil.excelPort("管理记录", listName, list, bottomList, response);
return null;
}
前端调用:
<a class="btn btn-warning" onclick="downloadExcel()">
<i class="fa fa-download"></i> 导出
</a>
js代码:
function downloadExcel(){
var startTime = $("#startTime").val();
var endTime = $("#endTime").val();
var dw = $("#dw").val();
window.location.href =ctx + "/travel/registrationSearch/export" + "?dw="+dw+"&beginRegTime=" + startTime+"&endRegTime=" + endTime;
}
点击导出按钮 则直接下载该xls文件
写完运行后 如发现可以下载,但是后台控制台打印错误:
getOutputStream() has already been called for this response
解决方法 ,参考此链接 https://blog.csdn.net/IT_51888_liang/article/details/122235797