java 多sheet导入导出

首先导入

public void personUpload(MultipartFile file) {
        try {
            //file从前端传过来的 直接接收  或者自己读取一个InputStream
            Workbook wb = new XSSFWorkbook(file.getInputStream());


            //获取第0个sheet  下标从0开始
            Sheet sheet1 = wb.getSheetAt(0);
            for (int i = 1;i<= sheet1.getLastRowNum(); i++) {
                //获取第i行数据   下标从0开始   这里i从1开始是因为第0行为字段名 不需要
                Row r = sheet1.getRow(i);
                //设置第i行的第0列为String类型  不然可能是数字类型
                if(r.getCell(0)!=null){
                    r.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                }
                String personID = r.getCell(0).getStringCellValue();
                String personName = r.getCell(1).getStringCellValue();
                Map param = new HashMap();
                param.put("personName",personName );
                param.put("personID",personID );
                updateOnePerson(param);
            }

            //获取第1个sheet
            Sheet sheet2 = wb.getSheetAt(1);
            List<TaskMessage> list = new ArrayList<>();
            for (int i = 1;i<= sheet2.getLastRowNum(); i++) {
                
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

然后导出
 

public void getDownLoad(HttpServletRequest request, HttpServletResponse response,String PERSONID) {
        List<String[]> head0s = new ArrayList<>();
        List<String[]> headCodes = new ArrayList<>();
        List<List<Map>> deviceList = new ArrayList<>();
        
        String[] sheets = new String[2];
        //sheet名字
        sheets[0] = "个人信息";
        sheets[1] = "任务情况";
        //表头1
        String[] head01 = {"人员ID","姓名", "性别","单位"};
        //表头对应的字段名1
        String[] headCode1 = {"PERSONID", "PERSONNAME", "GENDER","ORGFULLNAME"};
        //数据  map的key必须和headCode1的一致
        List<Map> person = mapper.getDownLoad(PERSONID);


        //表头2
        String[] head02 = {"人员id", "任务名称", "任务类型",  "任务状态"};
        //表头对应的字段名2
        String[] headCode2 = {"PERSONID", "RWMC", "RWLX", "ZXZT"};
        //数据 空数据也行
        List<Map> problem = new ArrayList<>();

        head0s.add(head01);
        head0s.add(head02);
        headCodes.add(headCode1);
        headCodes.add(headCode2);
        deviceList.add(person);
        deviceList.add(problem);

        exportExcelUtils.someSheetOfEX(request,response,"个人能力",head0s,headCodes,deviceList,sheets);
    }

然后是2个工具类 （来自其他地方 做了些小改动  时间久远  忘记在哪里复制的了）
 

package com.xxx.util;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Created by y on 2019/6/22.
 * 多sheet导出
 */
@Component
public class ExportExcelUtils {

    /**
     * @param workbook
     * @param sheetNum   (sheet的位置，0表示第一个表格中的第一个sheet)
     * @param sheetTitle （sheet的名称）
     * @param headers    （表格的标题）
     * @param result     （表格的数据）
     * @param out        （输出流）
     * @throws Exception
     * @Title: exportExcel
     * @Description: 导出Excel的方法
     * @author: evan @ 2014-01-09
     */
    public void exportExcel(XSSFWorkbook workbook, int sheetNum,
                            String sheetTitle, String[] headers, String[] headCode, List<Map> result,
                            OutputStream out, String filename) throws Exception {
        // 生成一个表格
        XSSFSheet sheet = workbook.createSheet();
        workbook.setSheetName(sheetNum, sheetTitle);
        // 设置表格默认列宽度为20个字节
        sheet.setDefaultColumnWidth((short) 20);
        // 生成一个样式
        XSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillPattern((short) 1);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.index);
        // 生成一个字体
        XSSFFont font = workbook.createFont();
        font.setColor((short)8);
        font.setFontHeightInPoints((short) 12);
        // 把字体应用到当前的样式
        style.setFont(font);

        // 指定当单元格内容显示不下时自动换行
        style.setWrapText(true);

        // 产生表格标题行
        XSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            XSSFCell cell = row.createCell((short) i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text.toString());
        }
        // 遍历集合数据，产生数据行
        if (result != null) {
            int index = 1;
            for (Map m : result) {
                row = sheet.createRow(index);
                int cellIndex = 0;
                for (String key : headCode) {
                    XSSFCell cell = row.createCell((short) cellIndex);
                    cell.setCellValue(m.get(key) == null ? "" : String.valueOf(m.get(key)));
                    cellIndex++;
                }
                index++;
            }
        }
    }

    public void someSheetOfEX(HttpServletRequest request, HttpServletResponse response, String filename, List<String[]> head0s, List<String[]> headCodes, List<List<Map>> deviceList, String[] sheets) {
        try {
            String excelName = request.getSession().getServletContext().getRealPath("/") + filename + ".xlsx";
            OutputStream out = new FileOutputStream(excelName);
            System.out.println("tempExcelName: " + excelName);
            XSSFWorkbook workbook = new XSSFWorkbook();
            for (int i = 0; i < deviceList.size(); i++) {
                List<Map> data = deviceList.get(i);
                String[] headers = head0s.get(i);
                String[] headCode = headCodes.get(i);
                exportExcel(workbook, i, sheets[i], headers, headCode, data, out, filename);
            }
            //原理就是将所有的数据一起写入，然后再关闭输入流。
            workbook.write(out);
            out.close();
            DownloadUtil.downFile(excelName, request, response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

 

package com.xxx.util;

import org.apache.poi.xssf.usermodel.XSSFCell;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 下载文件
 *
 */
public class DownloadUtil {

    private static Pattern p = Pattern.compile(".*MSIE.*?;.*");

    public static void downFile(String filepath, HttpServletRequest request, HttpServletResponse response) {
        try {
            File temFile = new File(filepath);
            if (!temFile.exists()) {
                response.getWriter().write("ERROR:File Not Found");
                return;
            }
            String fileName = temFile.getName();
            String browser = request.getHeader("user-agent");
            Matcher m = p.matcher(browser);
            if (m.matches()) {
                 fileName = new String(fileName.getBytes("UTF-8"),
                        "ISO8859-1");
                response.setHeader("Content-Disposition", "attachment; filename=\""
                        + fileName + "\"");
            } else {
                response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO8859-1").replace(" ", ""));
            }
            response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
            response.setHeader("Cache-Control", "max-age=" + 100);
            response.setContentLength((int) temFile.length());
            response.setContentType("application/x-download;charset=utf-8");
//            response.setContentType("application/octet-stream;charset=utf-8");
            response.setHeader("windows-Target", "_blank");
            OutputStream ot = response.getOutputStream();
            BufferedInputStream bis = new BufferedInputStream(new FileInputStream(temFile));
            BufferedOutputStream bos = new BufferedOutputStream(ot);
            byte[] buffer = new byte[4096];
            int length = 0;
            while ((length = bis.read(buffer)) > 0) {
                bos.write(buffer, 0, length);
            }
            bos.close();
            bis.close();
            ot.close();
            System.out.println("下载完成");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void setCellValue(XSSFCell cell, Object object) {
        if (object == null) {
            cell.setCellValue("");
        } else {
            if (object instanceof String) {
                cell.setCellValue(String.valueOf(object));
            } else if (object instanceof Long) {
                Long temp = (Long) object;
                String cellvalue = new DecimalFormat("#0.00").format(temp.doubleValue());
                cell.setCellValue(cellvalue);
            } else if (object instanceof Double) {
                Double temp = (Double) object;
                String cellvalue = new DecimalFormat("#0.00").format(temp.doubleValue());
                cell.setCellValue(cellvalue);
            } else if (object instanceof Float) {
                Float temp = (Float) object;
                String cellvalue = new DecimalFormat("#0.00").format(temp.doubleValue());
                cell.setCellValue(cellvalue);
            } else if (object instanceof Integer) {
                Integer temp = (Integer) object;
                cell.setCellValue(temp);
            } else if (object instanceof BigDecimal) {
                BigDecimal temp = (BigDecimal) object;
                String cellvalue = new DecimalFormat("#0").format(temp.doubleValue());
                cell.setCellValue(cellvalue);
            } else if (object instanceof Date) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                String cellvalue = sdf.format(object);
                cell.setCellValue(cellvalue);
            } else {
                cell.setCellValue("");
            }
        }
    }
}