前端方法：调用exportInfoPid这个方法并传入要查询的id即可，也可以用其他参数看个人需求
function exportInfoPid(id){
window.location.href = 服务地址+"/exportMdsRoutePid/"+id;
}后端控制层代码
@Controller
@Scope("prototype")
@RequestMapping("mms/mds/mdsroute/mdsRouteController")
public class MdsRouteController implements LoaderConstant {
@Autowired
private MdsRouteAPI mdsRouteAPI;
@Autowired
private MdsRouteExportExcel mdsRouteExportExcel;
/**
* 工艺导出
*
* @param pid
* @param request
* @return
* @throws Exception
*/
@RequestMapping(value = "/operation/exportMdsRoutePid/{pid}")
public Boolean exportMdsRoutePid(@PathVariable String pid, HttpServletRequest request,HttpServletResponse response) throws Exception {
ModelAndView mav = new ModelAndView();
//查询数据
MdsRouteDTO mdsRouteDTO = mdsRouteAPI.getRouteSeqTime(pid);
//查询到的数据写入Execl
mdsRouteExportExcel.mdsExportExcel(response,mdsRouteDTO);
return true;
}
}通过一下方法创建多个Sheet表，要几个创建几个
Sheet sheet2 = wb.createSheet("工序信息");
Execl写入并返回信息到服务端
package avicit.mms.mds.mdsroute.controller;
import avicit.mms.mds.mdsoperationseq.dto.MdsOperationSeqDTO;
import avicit.mms.mds.mdsroute.dto.MdsRouteDTO;
import com.google.common.net.HttpHeaders;
import org.apache.commons.compress.utils.Charsets;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Component;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import static io.netty.handler.codec.http.HttpHeaders.Names.CONTENT_TYPE;
/**
* Execl导出
*/
@Component
public class MdsRouteExportExcel {
public void mdsExportExcel(HttpServletResponse response,MdsRouteDTO mdsRouteDTO) {
Workbook wb = new XSSFWorkbook();
//设置单元格式
//表头设置
Font fontHead = wb.createFont();
fontHead.setColor(Font.COLOR_NORMAL);
fontHead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
CellStyle cellStyleHead = wb.createCellStyle();
cellStyleHead.setFont(fontHead);
cellStyleHead.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyleHead.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
//数据内容设置
Font font = wb.createFont();
font.setColor(Font.COLOR_NORMAL);
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFont(font);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
//创建第一个工作表
Sheet sheet = wb.createSheet("主工艺信息");
List<String[]> rowValues = new ArrayList<>();
String[] rowHeadValue = {
"*指令号",
"*指令名称",
"*工艺版本",
"*BOM分类",
"*零组件号",
"*主制部门编号",
"主制部门部门",
"*指令类型",
"*标记"
};
rowValues.add(rowHeadValue);
String[] rowValue = {
null == mdsRouteDTO.getRouteCode() ? "" :mdsRouteDTO.getRouteCode(),
null == mdsRouteDTO.getRouteName() ? "" : mdsRouteDTO.getRouteName(),
null == mdsRouteDTO.getRevisionNo() ? "" : mdsRouteDTO.getRevisionNo(),
"",
null == mdsRouteDTO.getMdsItemCode() ? "" : mdsRouteDTO.getMdsItemCode(),
null == mdsRouteDTO.getMainDeptCode() ? "" : mdsRouteDTO.getMainDeptCode(),
null == mdsRouteDTO.getMainDeptName() ? "" : mdsRouteDTO.getMainDeptName(),
null == mdsRouteDTO.getRouteType() ? "" : mdsRouteDTO.getRouteType(),
null == mdsRouteDTO.getRouteStage() ? "" : mdsRouteDTO.getRouteStage()
};
rowValues.add(rowValue);
Sheet sheet2 = wb.createSheet("工序信息");
for (int i = 0; i < rowValues.size(); i++){
Row currentRow = sheet.createRow(i);
// 获取当前行的数据
String[] cellValues = rowValues.get(i);
for (int j = 0; j < cellValues.length; j++) {
// 设置列宽
sheet.setColumnWidth(j, 4200);
Cell cell = currentRow.createCell(j);
if (i==0) {
cell.setCellStyle(cellStyleHead);
}else {
cell.setCellStyle(cellStyle);
}
//每个单元格的值目前做 String 处理
cell.setCellValue(cellValues[j]);
}
}
//创建第二个工作表
List<String[]> rowValues2 = new ArrayList<>();
//第二个工作表表头
String[] rowHeadValue2 = {
"*零组件号",
"*工序号",
"*工序名称",
"*工序内码",
"*加工车间（车间代码）",
"车间名称",
"*检验标识",
"*采集标识",
"*计划准结时间（分钟）",
"*计划加工时间（分钟）"
};
rowValues2.add(rowHeadValue2);
//服务端数据整理
if(mdsRouteDTO.getMdsOperationSeqDTOList()!=null) {
for (MdsOperationSeqDTO seqDTO : mdsRouteDTO.getMdsOperationSeqDTOList()) {
String[] rowValue2 = {
null == mdsRouteDTO.getMdsItemCode() ? "" : mdsRouteDTO.getMdsItemCode(),
null == seqDTO.getOperationSeqNo() ? "" : seqDTO.getOperationSeqNo(),
null == seqDTO.getOperationSeqName() ? "" : seqDTO.getOperationSeqName(),
0 == seqDTO.getInnerOrder() ? "" : seqDTO.getInnerOrder() + "",
null == seqDTO.getMainDeptCode() ? "" : seqDTO.getMainDeptCode(),
null == seqDTO.getMainDeptName() ? "" : seqDTO.getMainDeptName(),
null == seqDTO.getCheckFlag() ? "" : seqDTO.getCheckFlag(),
null == seqDTO.getRecordFlag() ? "" : seqDTO.getRecordFlag(),
null == seqDTO.getPlanSetupTime() ? "" : seqDTO.getPlanSetupTime().toString(),
null == seqDTO.getPlanRunTime() ? "" : seqDTO.getPlanRunTime().toString()
};
rowValues2.add(rowValue2);
}
}
for (int i = 0; i < rowValues2.size(); i++){
Row currentRow2 = sheet2.createRow(i);
// 获取当前行的数据
String[] cellValues = rowValues2.get(i);
for (int j = 0; j < cellValues.length; j++) {
// 设置列宽
sheet2.setColumnWidth(j, 5200);
Cell cell = currentRow2.createCell(j);
if (i==0) {
cell.setCellStyle(cellStyleHead);
}else {
cell.setCellStyle(cellStyle);
}
//每个单元格的值目前做 String 处理
cell.setCellValue(cellValues[j]);
}
}
writeToExcel(response, wb, "导出工艺数据表");
}
/**
* 数据写入Execl返回请求
* @param response
* @param workbook
* @param fileName
*/
public void writeToExcel(HttpServletResponse response, Workbook workbook, String fileName) {
OutputStream os = null;
try {
response.setContentType(CONTENT_TYPE);
response.setCharacterEncoding(Charsets.UTF_8.name());
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
response.setHeader(HttpHeaders.CONTENT_DISPOSITION, String.format("attachment; filename=%s", new String(URLEncoder.encode(fileName + ".xlsx" , Charsets.UTF_8.name())
.getBytes(Charsets.UTF_8), Charsets.ISO_8859_1)));
os = response.getOutputStream();
workbook.write(os);
os.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (os != null) {
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
?单元格背景色的设置目前没试验出来，可能是我用的版本有冲突吧，网上查的结果是这两个函数，可以自己尝试，单元格的设置可以了解一下CellStyle
cellStyleHead.setFillBackgroundColor(IndexedColors.CORNFLOWER_BLUE.getIndex());
cellStyleHead.setFillPattern(FillPatternType.SOLID_FOREGROUND);;