JAVA WEB用POI导出EXECL多个Sheet

前端方法：调用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);//查询到的数据写入ExeclmdsRouteExportExcel.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);;