springMVC 下的excel导出（直接弹出下载框）

使用poi实现页面点击导出，导出一个zip包，包内包含导出好的excel文件

目录

---

excel实体类

ExcelEntity ：构造一些基本格式等

import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

/**
 * 类描述 ： 要导出的excel文件实体
 */
public class ExcelEntity {

    // 显示的导出表的标题
    private String title;

    // 导出表的列名
    private String[] rowName;

    // 导出的文件名
    private String fileName;

    // 数据内容
    private List<Object[]> dataList = new ArrayList<Object[]>();

    // 构造方法，传入要导出的数据
    public ExcelEntity(String title, String[] rowName, List<Object[]> dataList, String fileName) {
        this.dataList = dataList;
        this.rowName = rowName;
        this.title = title;
        this.fileName = fileName;
    }

    public ExcelEntity(){

    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String[] getRowName() {
        return rowName;
    }

    public void setRowName(String[] rowName) {
        this.rowName = rowName;
    }

    public String getFileName() {
        return fileName;
    }

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    public List<Object[]> getDataList() {
        return dataList;
    }

    public void setDataList(List<Object[]> dataList) {
        this.dataList = dataList;
    }


    /*
     * 列头单元格样式
     */
    public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {

        // 设置字体
        HSSFFont font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 12);
        // 字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("微软雅黑");
        // 设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        return style;

    }

    /*
     * 列数据信息单元格样式
     */
    public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        // 设置字体名字
        font.setFontName("微软雅黑");
        // 设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }
}

SysLogEntity ：日志实体类

import java.io.Serializable;
import java.util.Date;

public class SysLogEntity implements Serializable {
    private static final long serialVersionUID = -8398719019503977930L;
    //唯一标识ID
    private int id;
    //用户ID
    private int userId;
    //操作类型
    private String logType;
    //操作动作
    private String logOpt;
    //入库时间
    private Date checkinTime;
    //表里没有，显示用 用户名
    private String userName;
    //表里没有，显示用 用户登录账号
    private String account;

    public int getUserId() {
        return userId;
    }
    public void setUserId(int userId) {
        this.userId = userId;
    }
    public String getLogType() {
        return logType;
    }
    public void setLogType(String logType) {
        this.logType = logType;
    }
    public String getLogOpt() {
        return logOpt;
    }
    public void setLogOpt(String logOpt) {
        this.logOpt = logOpt;
    }
    public Date getCheckinTime() {
        return checkinTime;
    }
    public void setCheckinTime(Date checkinTime) {
        this.checkinTime = checkinTime;
    }
    public String getUserName() {
        return userName;
    }
    public void setUserName(String userName) {
        this.userName = userName;
    }
    public String getAccount() {
        return account;
    }
    public void setAccount(String account) {
        this.account = account;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }

}

controller层

ExcelExportController ：有很多方法需要放到serviceImpl里面，
分层还是不明晰，需要改进

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

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

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

@SuppressWarnings("deprecation")
@Controller
@RequestMapping("/excelCtrl")
public class ExcelExportController {

    @Autowired
    private ExcelExportService excelExportService;

    @RequestMapping("/exportExcel")
    public void exportExcel(HttpServletRequest request,HttpServletResponse response) throws Exception{
        // 要导出的数据id
        String ids = request.getParameter("ids");
        // excel的标题
        String title = "";
        // excel的文件名
        String fileName = "";
        // excel的暂时保存的文件夹路径
        String tempFile = "";
        // 保存文件的时间戳
        String timeStm = "";
        // 要导出的数据内容 各种实体
        List<Object[]> dataList = null;
        String[] rowsName = null;
        title = "系统日志信息";
        rowsName = new String[]{"序号","用户名","账号","操作类型","操作动作","日志时间"};
        fileName= title+"-"+String.valueOf(System.currentTimeMillis()).substring(4,13)+".xls";
        tempFile = title+"-"+String.valueOf(System.currentTimeMillis()).substring(4,13);
        timeStm = String.valueOf(System.currentTimeMillis()).substring(4,13);
        dataList = excelExportService.selectExportDataList( ids,rowsName.length);
        }
        // 临时文件目录
        File temFileDire = new File("/test"+tempFile+"/");
        if(!temFileDire.exists()){
            temFileDire.mkdirs();
        }
        createExcelFile(dataList, title, rowsName, fileName, temFileDire);
        response.setContentType("APPLICATION/OCTET-STREAM");  
        response.setHeader("Content-Disposition","attachment; filename="+type+timeStm+".zip");
        ZipOutputStream out = new ZipOutputStream(response.getOutputStream());
        try {
            // 读取目录下所有生成的文件
            File[] files = temFileDire.listFiles();

            for(int i = 0; i < files.length; i++){
                File file = files[i];
                repeatCompress(file, out, "");
                response.flushBuffer();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            out.close();
            FileUtil.delFile(temFileDire);
            temFileDire.delete();
        }

    }

    /**
     * 根据数据条数进行写excel
     * @param dataList 数据条目
     * @param title 标题
     * @param rowsName 文档中的每行标题
     * @param fileName 文件名
     * @param temFileDire 暂存目录
     */
    public void createExcelFile(List<Object[]> dataList, String title, String[] rowsName, String fileName, File temFileDire){
        // excel 大于5000条数据做特殊处理
        if(dataList.size() > 5000){
            // 总记录数
            int totalNum = dataList.size(); 
            // 需要分成几个excel
            int repNum = (totalNum%5000 == 0)? totalNum/5000:totalNum/5000+1;
            for(int n = 0; n < repNum; n++){
                // 结尾的条数
                int endNum = totalNum;
                endNum = endNum > (n+1)*5000? (n+1)*5000 : endNum;
                // 根据最终条数截取数据条数
                List<Object[]> subDataList = dataList.subList(n*5000, endNum); 
                // 生成excel构造
                ExcelEntity excel = new ExcelEntity(title, rowsName, subDataList, fileName);
                HSSFWorkbook workbook = null;
                try {
                    workbook = writeIntoExcel(excel);
                } catch (Exception e) {
                    e.printStackTrace();
                }
                exportExcelOpen( (n+1)+"_"+excel.getFileName(), workbook, temFileDire);
            }
        }else{
            ExcelEntity excel = new ExcelEntity(title, rowsName, dataList, fileName);
            HSSFWorkbook workbook = null;
            try {
                workbook = writeIntoExcel(excel);
            } catch (Exception e) {
                e.printStackTrace();
            }
            exportExcelOpen( excel.getFileName(), workbook, temFileDire);
        }

    }

    /**
     * 遍历读取文件
     * @param inFile 目标文件或者文件夹
     * @param out zip输出流
     * @param dir 目录文件名称
     * @throws IOException
     */
    public void repeatCompress(File inFile, ZipOutputStream out, String dir) throws IOException {
        if ( inFile.isDirectory() ) {
            File[] files = inFile.listFiles();
            if (files!=null && files.length>0) {
                for (File file : files) {
                    String name = inFile.getName();
                    if (!"".equals(dir)) {
                        name = dir + "/" + name;
                    }
                    repeatCompress(file, out, name);
                }
            }
        } else {
            doZip(inFile, out, dir);
        }
    }

    /**
     * 压缩成zip包
     * @param inFile 目标文件
     * @param out zip输出流
     * @param dir 文件所在目录
     * @throws IOException
     */
    public void doZip(File inFile, ZipOutputStream out, String dir) throws IOException {
         String entryName = null;
         if (!"".equals(dir)) {
             entryName = dir + "/" + inFile.getName();
         } else {
             entryName = inFile.getName();
         }
         ZipEntry entry = new ZipEntry(entryName);
         out.putNextEntry(entry);
         int len = 0 ;
         byte[] buffer = new byte[1024];
         FileInputStream fis = new FileInputStream(inFile);
         while ((len = fis.read(buffer)) > 0) {
             out.write(buffer, 0, len);
             out.flush();
         }
         out.closeEntry();
         fis.close();
    }

    /**
     * excel文件中间保存
     * @param fileName 文件名称
     * @param workbook 写好的excel
     * @param temFileDire excel文件夹暂时保存的路径
     */
    private void exportExcelOpen( String fileName, HSSFWorkbook workbook, File temFileDire) {
        try {
            FileOutputStream outputStream = new FileOutputStream(temFileDire.getAbsolutePath()+"/"+fileName);
            workbook.write(outputStream);
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 利用poi向excel中写数据
     * @param excel
     * @return
     * @throws Exception
     */
    public HSSFWorkbook writeIntoExcel(ExcelEntity excel) throws Exception {
        String title = excel.getTitle();
        String[] rowName = excel.getRowName();
        List<Object[]> dataList = excel.getDataList();
        HSSFWorkbook workbook = null;
        try {
            workbook = new HSSFWorkbook(); // 创建工作簿对象
            HSSFSheet sheet = workbook.createSheet(title); // 创建工作表

            // 产生表格标题行
            HSSFRow rowm = sheet.createRow(0);
            HSSFCell cellTiltle = rowm.createCell(0);

            // sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】
            HSSFCellStyle columnTopStyle = excel.getColumnTopStyle(workbook);// 获取列头样式对象
            HSSFCellStyle style = excel.getStyle(workbook); // 单元格样式对象

            sheet.addMergedRegion(new CellRangeAddress(0, 1, 0,
                    (rowName.length - 1)));
            cellTiltle.setCellStyle(columnTopStyle);
            cellTiltle.setCellValue(title);

            // 定义所需列数
            int columnNum = rowName.length;
            HSSFRow rowRowName = sheet.createRow(2); // 在索引2的位置创建行(最顶端的行开始的第二行)

            // 将列头设置到sheet的单元格中
            for (int n = 0; n < columnNum; n++) {
                HSSFCell cellRowName = rowRowName.createCell(n); // 创建列头对应个数的单元格
                cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 设置列头单元格的数据类型
                HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
                cellRowName.setCellValue(text); // 设置列头单元格的值
                cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式
            }

            // 将查询出的数据设置到sheet对应的单元格中
            for (int i = 0; i < dataList.size(); i++) {
                Object[] obj = dataList.get(i);// 遍历每个对象
                HSSFRow row = sheet.createRow(i + 3);// 创建所需的行数
                for (int j = 0; j < obj.length; j++) {
                    HSSFCell cell = null; // 设置单元格的数据类型
                    if (j == 0) {
                        cell = row.createCell(j, HSSFCell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(i + 1);
                    } else {
                        cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
                        if (!"".equals(obj[j]) && obj[j] != null) {
                            cell.setCellValue(obj[j].toString()); // 设置单元格的值
                        }else{
                            cell.setCellValue(" "); // 设置单元格的值
                        }
                    }
                    cell.setCellStyle(style); // 设置单元格样式
                }
            }
            // 让列宽随着导出的列长自动适应
            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() == HSSFCell.CELL_TYPE_STRING) {
                            if(currentCell==null||currentCell.equals("")||currentCell.getCellType() ==HSSFCell.CELL_TYPE_BLANK){
                                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);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return workbook;
    }


}

service层接口

ExcelExportService ：具体处理根据需要自行修改

/**
 * excel文件导出Service接口
 */
public interface ExcelExportService {

    /**
     * 导出数据集
     * @param ids 数据ids
     * @param rowsLength 列数
     * @return 导出结果
     */
    List<Object[]> selectExportDataList(String ids int rowsLength);

}

serviceImpl

ExcelExportServiceImpl

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.dao.SysLogDao;
import com.entity.SysLogEntity;


@Service
public class ExcelExportServiceImpl implements ExcelExportService {
    private static Logger logger = Logger.getLogger(ExcelExportServiceImpl.class);

    @Autowired
    private SysLogDao sysLogDao;

    /**
     * 导出数据集
     * @param ids 数据ids
     * @param rowsLength 列数
     * @return 导出结果
     */
    @Override
    public List<Object[]> selectExportDataList(String ids, int rowLength) {
        List<Object[]> resultList = new ArrayList<Object[]>();
        // 全导出标识
        boolean allExportFlag = false;
        // 如果没有选定id，就是全部导出
        if (!(null == ids|| "".equals(ids.trim()) || "null".equalsIgnoreCase(ids.trim()))) {
            allExportFlag = true;
        }
        // 序号
        int count = 0;
        // 构造插入参数值
        Map<String, Object> queryMap = new HashMap<String, Object>();
        // 如果不是全导出
        if (!allExportFlag) {
            queryMap.put("idList", ids.split(","));
        }
        // 系统日志的导出
        List<SysLogEntity> syslogList = new ArrayList<SysLogEntity>();
        try {
            // 获取所有数据
            syslogList = sysLogDao.selectExportLogData(queryMap);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("查询系统日志信息出错");
        }
        // 遍历所有数据，放到每行保存
        for (SysLogEntity syslog : syslogList) {
            // 每行的数据存放
            Object[] obj = new Object[rowLength];
            obj[0] = count;
            obj[1] = syslog.getUserName();
            obj[2] = syslog.getAccount();
            obj[3] = syslog.getLogType();
            obj[4] = syslog.getLogOpt();
            obj[5] = TimeUtils.getTimeWithMinutes(syslog.getCheckinTime());
            count++;
            resultList.add(obj);
        }
        syslogList.clear();
        return resultList;
    }
}

工具类

FileUtil

import java.io.File;
import java.io.IOException;
import net.lingala.zip4j.core.ZipFile;
import net.lingala.zip4j.exception.ZipException;

/**
 * 类功能描述：文件操作工具类
 */
public class FileUtil {

    /**
     *  方法功能描述：解压zip文件到指定的目录下
     * @param zipFile
     * @param dest
     * @param passwd
     * @throws ZipException
     */
    public static void unzip(File zipFile, String dest) {
        try {
            if (!zipFile.exists())
                throw new ZipException("zip file not exist!");
            ZipFile zFile = new ZipFile(zipFile);
            zFile.setFileNameCharset("UTF-8");
            if (!zFile.isValidZipFile()) {
                throw new ZipException("Illegal zip file.");
            }
            File destDir = new File(dest);
            if (!destDir.exists()) {
                destDir.mkdirs();
            }
            zFile.extractAll(dest);
        } catch (ZipException e) {
            e.printStackTrace();
            zipFile.delete();
        }
    }

    /**
     * 方法功能描述：从给定的文件夹获取指定类型的文件
     * @param src
     * @param type
     * @return
     */
    public static File getTypeFile(File src, String type) {
        File res = null;
        if (src.exists() && src.isDirectory()) {
            File[] files = src.listFiles();
            for (int i = 0; i < files.length; i++) {
                if (files[i].getName().endsWith(type) && !files[i].isDirectory()) {
                    res = files[i];
                    break;
                } else {
                    res = getTypeFile(files[i], type);
                }
            }
        }
        return res;
    }

    /**
     * 方法功能描述：递归删除文件
     * @param src
     * @return
     */
    public static void delFile(File src) {
        if (src.exists() && src.isDirectory()) {
            File[] files = src.listFiles();
            for (int i = 0; i < files.length; i++) {
                if (files[i].isFile()) {
                    //logger.debug(files[i].getAbsolutePath());
                    files[i].delete();
                } else {
                    delFile(files[i]);
                    files[i].delete();
                }
            }
        }
    }

    public static void createFile(String path, String fileName) {
        File dir = new File(path);
        if (!dir.exists() || !dir.isDirectory()) {
            dir.mkdirs();
        }
        File file = new File(path + fileName);
        if (!file.exists()) {
            try {
                file.createNewFile();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

页面触发


var ctx = '<%=path%>';
// 导出后台交互
function beginExport(typeValue,ids){
var eleForm = document.body.appendChild(document.createElement('form'));
eleForm.action = ctx+"excelCtrl/exportExcel.do";
var hiddenInput1 = document.createElement('input');
hiddenInput1.type = 'hidden';
hiddenInput1.name = "ids";
hiddenInput1.value = ids;
eleForm.appendChild(hiddenInput1);
this.eleForm = eleForm;
this.eleForm.method = 'post';
this.eleForm.submit();
}