导出excel文件

工具类

package com.se.utils.excel;/*
* 创建人：cwq
* 创建时间 ：20160105
* 创建用于 用于临时文件的下载
*
* */

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.*;

public class DownloadExcelUtils {
    private File resultFile = null;
    private String sheetName = "sheet";
    private int sheetRow = 0;
    private String fileName=null;
    private String [] gaugeHead = {"第一列","第二列"};
    private String [][] cellValue = {{"1","2"},{"1","2"},{"3","4"}};
    private Map<String, String> params = new HashMap<String, String>();
    private String gauge = null;
    private String cell = null;
    private String fileTime = null;

    public DownloadExcelUtils(){

    }

    /*
    *
    * */
    public Map<String,String> Excel(String sheetName,int sheetRow,String [] gaugeHead,String [][] cellValue)throws IOException {
        return export(sheetName,sheetRow,gaugeHead,cellValue);
    }


    /*
    * 转为二数组
    * */

    private String[][] ToList( List list){
        int listLength = list.size();
        Object[] listArray = list.toArray();
        String[][] arrList = new String[listLength][];
        for(int i=0;i<listArray.length;i++){
            arrList[i]=(String[]) listArray[i];
        }
       return arrList;
    }

    /*
   * 用于导出xlsx 文件
   * @sheetName 页的名字
   * @sheetRow 多少行一个页
   * @gaugeHead 表头
   * @cellValue 内容
   * */
    public void exportTo(String sheetName,int sheetRow,String [] gaugeHead,String [][] cellValue,OutputStream os) {
        // 创建工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        //工作簿的样式
        CellStyle cellType= CellStyle(wb);
        if(sheetRow>0) {
            int len = cellValue.length;//数据的行数
            int yuShu = len%sheetRow;//取余数
            int ye = (yuShu==0)?(int)Math.ceil(len/sheetRow):((int)Math.ceil(len/sheetRow)+1);
//            System.out.println(ye);
            for(int i=0;i<ye;i++){
                String name=  (i==0)? sheetName : sheetName+ String.valueOf(i);//页的名字
                wb.createSheet(name);//创建页
                gaugeHead(wb.getSheet(name), cellType, gaugeHead);//表头
                CellValue(wb.getSheet(name), cellType,setPage(i,cellValue,sheetRow,ye,yuShu));//设置内容
            }
        }else {
            // 创建页
            HSSFSheet sheet = wb.createSheet(sheetName);
            gaugeHead(sheet, cellType, gaugeHead);//表头
            CellValue(sheet, cellType, cellValue);//设置内容
        }
        //保存工作簿
        try {
            wb.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
      /*
    * 用于导出xlsx 文件
    * @sheetName 页的名字
    * @sheetRow 多少行一个页
    * @gaugeHead 表头
    * @cellValue 内容
    * */
    private Map<String, String> export(String sheetName,int sheetRow,String [] gaugeHead,String [][] cellValue) throws IOException {
        fileName="result"+createTime()+createRandom()+"-"+getRandomString(8);
        //结果存放文件以及地址
//        String path = createFolder("xwmr", File.separator+"src"+File.separator+"main"+File.separator+"webapp"+File.separator+"upload"+File.separator+"download"+File.separator);
//
//        deleteFile(new File(path.split("download")[0]+"download"),60*60*24);
//        System.out.println("---------------"+path+"------------");
        resultFile = new File(fileName+".xls");
        // 创建工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        //工作簿的样式
        CellStyle cellType= CellStyle(wb);
        if(sheetRow>0) {
            int len = cellValue.length;//数据的行数
            int yuShu = len%sheetRow;//取余数
            int ye = (yuShu==0)?(int)Math.ceil(len/sheetRow):((int)Math.ceil(len/sheetRow)+1);
//            System.out.println(ye);
            for(int i=0;i<ye;i++){
                String name=  (i==0)? sheetName : sheetName+ String.valueOf(i);//页的名字
                wb.createSheet(name);//创建页
                gaugeHead(wb.getSheet(name), cellType, gaugeHead);//表头
                CellValue(wb.getSheet(name), cellType,setPage(i,cellValue,sheetRow,ye,yuShu));//设置内容
            }
        }else {
            // 创建页
            HSSFSheet sheet = wb.createSheet(sheetName);
            gaugeHead(sheet, cellType, gaugeHead);//表头
            CellValue(sheet, cellType, cellValue);//设置内容
        }
        FileOutputStream os = new FileOutputStream(resultFile);
        //保存工作簿
        wb.write(os);
        os.flush();
        os.close();
        params.put("fileName", fileName);
        params.put("url","/upload/download/"+fileTime+"/"+fileName+".xls");
        return  params;
    }

    /*
    * 处理分页的数据
    * @page 页码
    * @arr 存进来的数据
    * @sheetRow 多少行一页
    * @ye 共多少页
    * @yuShu 最后一页多少个
    * */

     private String[][] setPage(int page,String[][] arr,int sheetRow,int ye,int yuShu){
//         System.out.println("page:"+page+",arr:"+arr[0][0]+",sheetRow:"+sheetRow)
         int startNum =page*sheetRow;//开始数据
         int endNum =(page+1)*sheetRow;//结束数据
         int l = 0;
         if(page==(ye-1)){//最后一页
             if(yuShu!=0){//不是整除
                 l = yuShu;
                 endNum = page*sheetRow+yuShu;
             }else{
                 l = (page+1)*sheetRow-page*sheetRow;
             }
         }else{
             l = (page+1)*sheetRow-page*sheetRow;
         }
//         System.out.println("长度："+l+"endNum:"+endNum);
         String[][] newArr = new String[l][];
         int index = 0;
         for(int i=startNum;i<endNum;i++){
             int len = arr[i].length;
             String[] arrRow = new String[len];
             for(int j=0;j<len;j++){
                 arrRow[j]=arr[i][j];
             }
             newArr[index]=arrRow;
             index++;
         }
        return newArr;
    }
    private static String randString = "abcdefghigklmnopkrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ0123456789";//随机产生的字符串
    /*
    * 获取随机的字符
    */
    public static String getRandomString(int length){
        StringBuilder sf=new StringBuilder();
        Random random = new Random();
        for(int i=0;i<length;i++){
            int number=random.nextInt(62);//0~61
            sf.append(randString.charAt(number));
        }
        return sf.toString();
    }
    /*
    * 删除文件
    * @file 文件
    * @time 删前时间 单位s
    * */

    private void deleteFile(File file,int time) {
//        System.out.println(file.isFile());
        time = (time==0)?30:time;
        try {
            if (!file.isFile()) {//不是文件
//                System.out.println(file.listFiles());
                File[] tempList =file.listFiles();
                for (int i = 0; i < tempList.length; i++) {
                    if (tempList[i].isFile()) {
                        if (canDeleteFile(tempList[i],time)) {
                            delete(tempList[i],"文件");
                        }
                    }
                    if (tempList[i].isDirectory()) {
                        if (canDeleteFile(tempList[i],time)) {
                            delete(tempList[i],"文件夹");
                        }
                    }
                }

            }else{
                delete(file,"文件");
            }
        } catch (Exception e) {
            System.out.println("删除文件失败！"+ e);
        }
    }

    /*
    * 文件删除动作
    * */

    private void delete(File file,String name){
        if (file.delete()) {
            System.out.println(name+":" + file.getName() + "删除成功!");
        } else {
            if(deleteDir(file)){
                System.out.println(name+":" + file.getName() + "删除成功!");
            }else {
                System.out.println(name+":" + file.getName() + "删除失败!");
            }
        }
    }

    /**
     * 递归删除目录下的所有文件及子目录下所有文件
     * @param dir 将要删除的文件目录
     */
    private  boolean deleteDir(File dir) {
        if (dir.isDirectory()) {
            String[] children = dir.list();//递归删除目录中的子目录下
            for (int i=0; i<children.length; i++) {
                boolean success = deleteDir(new File(dir, children[i]));
                if (!success) {
                    return false;
                }
            }
        }
        return dir.delete();
    }
     /*
    * 计算文件时间
    * @file 文件
    * @time 删前时间 单位s
    * */

    private boolean canDeleteFile(File file,int time) {
        Date date = new Date();
        long lastUpTime = file.lastModified() / 1000;
        long secondsOfOneMinute = time;
        //可删除文件的时间
        long canDelTime = date.getTime() / 1000 - secondsOfOneMinute;

        if(lastUpTime <= canDelTime) {
            return true;
        }
        return false;
    }
       /*
    * 创建时间
    * 时分秒
    * */

     private String createTime(){
        SimpleDateFormat format=new SimpleDateFormat("yyyyMMddHHmmss");
        return format.format(new Date());
    }
    private int createRandom(){
        return (int)(Math.random()*10);
    }
    /*
    * 创建工作簿的样式
    * @wb 工作簿
    * */

     private CellStyle CellStyle(Workbook wb){
        CellStyle cellType = null;
        cellType = wb.createCellStyle();
        cellType.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellType.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellType.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellType.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellType.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER); // 设置单元格垂直方向对其方式
        cellType.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平
         cellType.setFillForegroundColor(HSSFColor.WHITE.index);
        cellType.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        return cellType;
    }

    /*
    * 设置导出数据
    * @sheet 工作簿的页
    * @cellType 工作簿的样式
    * @cellValue 表头数组
    * */

   private void CellValue(HSSFSheet sheet,CellStyle cellType,String[][] cellValue){
      HSSFRow row = null;
      HSSFCell cell = null;
      for (int i=0;i<cellValue.length;i++){
          row = sheet.createRow(i+1);  // 第二行
          int len = cellValue[i].length;
          for(int j=0;j<len;j++){
              //列
              cell = row.createCell(j);
              // 单元格样式
              cell.setCellStyle(cellType);
              // 单元格的值
              cell.setCellValue(cellValue[i][j]);
          }
      }
  }

    /*
    * 设置导出的表头
    * @sheet 工作簿的页
    * @cellType 工作簿的样式
    * @gaugeHead 表头数组
    * */

     private void gaugeHead(HSSFSheet sheet,CellStyle cellType,String[] gaugeHead){
        HSSFRow row = null;
        HSSFCell cell = null;
        row = sheet.createRow(0);  // 第一行
        for (int i=0;i<gaugeHead.length;i++){
            sheet.autoSizeColumn(i, true);
            sheet.setColumnWidth(i,gaugeHead[i].getBytes().length*2*256);
            // 列
            cell = row.createCell(i);
            // 单元格样式
            cell.setCellStyle(cellType);
            // 单元格的值
            cell.setCellValue(gaugeHead[i]);
        }
    }

 }

实例：

 public static void main(String[] args) {
     String[] headers = {"",""};  //设置表头
     List<String[]> Subject = new ArrayList<>();
     Subject.add(new String[]{" "," "});
     int size = Subject.size();
     String[][] arrList = new String[size][];
     int index = 0;
     for (String[] l : Subject) {
         arrList[index] = l;
         index++;
     }
     OutputStream out = null;
     try {
         Date date = new Date();
         DateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
         String filename = "test" + format.format(date) + ".xls";
         String recommendedName = new String(filename.getBytes(), "iso_8859_1");                                  
         getResponse().setContentType("application/vnd.ms-excel");//设置导出文件格式
         getResponse().setHeader("Content-Disposition", "attachment; filename=" + recommendedName);//
         out = getResponse().getOutputStream();
         new DownloadExcelUtils().exportTo("Sheet", 10000, headers, arrList, out);
         out.flush();
         out.close();

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
 }