Java导出Excel的工具类

1.pom和需要导入的jar信息

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.tiglle</groupId>
  <artifactId>testExcel</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>war</packaging>

  <dependencies>
    <!-- poi的包 -->
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.16</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->
    <dependency>
        <groupId>javax.servlet</groupId>
        <artifactId>javax.servlet-api</artifactId>
        <version>4.0.0</version>
        <scope>provided</scope>
    </dependency>

  </dependencies>

  <build>
    <finalName>tiglle</finalName>
    <plugins>
        <!-- 打包时不忽略空文件夹 -->
        <plugin>  
            <groupId>org.apache.maven.plugins</groupId>  
            <artifactId>maven-war-plugin</artifactId>  
            <version>2.6</version>  
            <configuration>  
                <includeEmptyDirectories>true</includeEmptyDirectories>  
            </configuration>  
        </plugin>
    </plugins>
  </build>

</project>

2.ExcelUtils

package com.tiglle.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
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.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import com.tiglle.model.Person;

public class ExcelUtil {

    /**
     * 将多个文件压缩成一个文件
     * @param srcfile 需要进行压缩的文件名称的数组(每个文件为所在磁盘的全路径:D:\\file\a.xls)
     * @param zipfile 压缩后的文件名称
     * @throws IOException
     */
     public static void ZipFiles(File[] srcfile, File zipfile) throws Exception {
         if(!zipfile.exists()){
                zipfile.createNewFile();
            }
            byte[] buf = new byte[1024];  
            try {  
                ZipOutputStream out = new ZipOutputStream(new FileOutputStream(  
                        zipfile));  
                for (int i = 0; i < srcfile.length; i++) {  
                    File tempFile = srcfile[i];
                    FileInputStream in = new FileInputStream(tempFile);  
                    out.putNextEntry(new ZipEntry(srcfile[i].getName()));  
                    int len;  
                    while ((len = in.read(buf)) > 0) {  
                        out.write(buf, 0, len);  
                    }  
                    out.closeEntry();  
                    in.close(); 
                    //删除文件,免得占用服务器内存
                    tempFile.delete(); 
                }  
                out.close();  
            } catch (IOException e) {  
                e.printStackTrace();  
            } 
        } 

     /**
      * 设置下载文件的响应头和下载的文件名称
      * @param response 
      * @param defaultFileName 默认返回浏览器的文件名
      * @throws Exception 
      */
     public static void setResponseHeader(HttpServletResponse response,String defaultFileName) throws Exception {  
        response.setContentType("application/octet-stream;charset=UTF-8");  
        response.setHeader("Content-Disposition", "attachment;filename=" + java.net.URLEncoder.encode(defaultFileName, "UTF-8")  + ".zip");  
        response.addHeader("Pargam", "no-cache");  
        response.addHeader("Cache-Control", "no-cache");  
     }

     /**
      * 根据指定名称+自定义时间格式生成字符串
      * @param name 最终下载的名称
      * @param partener 指定时间格式:yyyy-MM-dd HH mm ss
      * @return
      */
     public static String generateName(String name,String partener){
        Date date = new Date();  
        SimpleDateFormat format = new SimpleDateFormat(partener);  
        return name + format.format(date);
     }

    /**
     * 生成Excel
     * @param list 装实体类数据的list
     * @param length 多少条生成一个Excel
     * @param fileName 下载后的zip的名称和每个Excel的名称
     * @param sheetName Excel中的选项卡的名称
     * @param topCloumnNames Excel的头名称信息数组
     * @param out PrintWrite对象
     * @return
     * @throws IOException
     */
     public static List<String> toExcel(List<Person> list,int length, String fileName,String sheetName,String[] topCloumnNames, OutputStream out) throws IOException {  
            List<String> fileNames = new ArrayList<String>();// 用于存放生成的文件名称s  
            // 生成excel（一个对象一行）  
            for (int j = 0, n = list.size() / length + 1; j < n; j++) {  
                Workbook book = new HSSFWorkbook(); 
                //创建Excel的第一页并指定名称
                Sheet sheet = book.createSheet(sheetName); 
                //将每个xls+j用于区分
                String tempFileName = fileName + "-" + j + ".xls";
                //将名字纪录起来
                fileNames.add(tempFileName);
                FileOutputStream o = null;  
                try {  
                    o = new FileOutputStream(tempFileName);  
                    // sheet.addMergedRegion(new  
                    // CellRangeAddress(list.size()+1,0,list.size()+5,6));
                    //创建头，并设置头名称
                    Row row = sheet.createRow(0);
                    for(int i=0;i<topCloumnNames.length;i++){
                        row.createCell(i).setCellValue(topCloumnNames[i]);  
                    }

                    int m = 1;  

                    //格式
                    CellStyle cellStyle = book.createCellStyle();
                    //居中
                    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

                    for (int i = 1, min = (list.size() - j * length + 1) > (length + 1) ? (length + 1)  : (list.size() - j * length + 1); i < min; i++) {  
                        m++;
                        /*
                         * 根据业务映射字段
                         */
                        Person user = list.get(length * (j) + i - 1);  
                        Double dd = user.getMoney();  
                        if (dd == null) {  
                            dd = 0.0;  
                        }
                        //创建数据的每一行
                        row = sheet.createRow(i);
                        Cell cell0 = row.createCell(0);
                        cell0.setCellStyle(cellStyle);
                        cell0.setCellValue(user.getId());
                        row.createCell(1).setCellValue(user.getName());  
                        row.createCell(2).setCellValue(user.getAddress());  
                        row.createCell(3).setCellValue(user.getTel());  
                        row.createCell(4).setCellValue(dd);  

                    }

                    //多创建一行
                    row = sheet.createRow(m);  
                    //合并最后一行(x,y,从第几格合并,合并几格)
                    sheet.addMergedRegion(new CellRangeAddress(m, m, 0, 4)); 
                } catch (Exception e) { 
                    e.printStackTrace(); 
                } 
                try { 
                    book.write(o); 
                } catch (Exception ex) { 
                    ex.printStackTrace(); 
                } finally { 
                    book.close(); 
                    o.flush(); 
                    o.close(); 
                } 
            } 
            return fileNames; 
     } 

     /**
      * 对外的接口
      * @param request 
      * @param response
      * @param list 装实体类数据的list
      * @param zipFileName 最终生成下载的文件名和每个Excel的名称
      * @param partener 是否根据上面的名称+时间格式生成最终名称,null或""不追加时间格式:yyyyMMddHHmmss
      * @param dirName 项目根目录下用来装临时Excel和zip文件的文件夹名称，最终正常情况下，此文件夹始终为空
      * @param count 多少条生成一个Excel
      * @param sheetName 选项卡名称
      * @param topColumnName excel的第一行的名称信息数组,如果数据多余名称，将显示空
      * @throws Exception
      */
     public static void generateExcel(HttpServletRequest request,HttpServletResponse response,List<Person> list,String zipFileName,String partener,String dirName,int count,String sheetName,String[] topColumnName) throws Exception{
            //是否根据当前时间组装文件名
            if(null!=partener&&"".equals(partener)){
                zipFileName = ExcelUtil.generateName(zipFileName,partener);
            }

            //设置响应头信息
           ExcelUtil.setResponseHeader(response, zipFileName); 

           //基于项目跟路径下的文件夹
           String directoryPath = request.getRealPath("/"+dirName);
           File file = new File(directoryPath);
           if(!file.exists()){
               file.mkdirs();
           }
           //每个xls文件的全路径(根据tomcat所在路径获取的,无后缀)
           String fileName = directoryPath + "/" + zipFileName;

           OutputStream out = response.getOutputStream();
           //生成n多个Excel,并将多个xls文件名+n用于区分,返回每个生成的xls的文件全路径
           List<String> fileNames = ExcelUtil.toExcel(list,count,fileName,sheetName,topColumnName,out);  

            //将生成的n个xls压缩成一个zip文件,并删除xls文件
            File zip = new File(fileName + ".zip");// 压缩文件  
            File srcfile[] = new File[fileNames.size()];  
            for (int i = 0, n = fileNames.size(); i < n; i++) {  
                srcfile[i] = new File(fileNames.get(i));//根据路径生成File对象  
            } 
            //压缩成zip
            ExcelUtil.ZipFiles(srcfile, zip);

            //将zip写给客户端
            FileInputStream inStream = new FileInputStream(zip);  
            byte[] buf = new byte[4096];  
            int readLength;  
            while (((readLength = inStream.read(buf)) != -1)) {  
                out.write(buf, 0, readLength);  
            }
            inStream.close();  
            //删除zip文件,免得占用服务器内存
            zip.delete();

            out.flush();  
            out.close();  
     }
}

3.测试Servlet：ExcelServlet

package com.tiglle.serlvet;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

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

import com.tiglle.model.Person;
import com.tiglle.utils.ExcelUtil;

/**
 * Servlet implementation class ExportServlet
 */
public class ExportServlet extends HttpServlet {

    /**
     * 
     */
    private static final long serialVersionUID = 1L;

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) {
        System.out.println("开始导出..........................");
        List<Person> list = new ArrayList<Person>();
        int count = Integer.parseInt(request.getParameter("count"));
        for(int i=0;i<count;i++){
            Person p = new Person(i,"名称"+i,"地址"+i,"电话"+i,i+0.0);
            list.add(p);
        }
        try {
            ExcelUtil.generateExcel(request, response, list, "测试导出", "yyyy-MM", "filesss", 20000, "测试导出", new String[]{"第一行","第二行"});
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) {

        doGet(request, response);
    }

}