Java导出Excel的工具类

以你之姓@ 2022-06-09 03:08 243阅读 0赞

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>
        
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>
        
        <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);
        }
    
    }