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);    }}