匯出資料到Excel--多sheet

import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.alibaba.druid.support.json.JSONUtils;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import org.aspectj.util.FileUtil;

/**
 * 封裝資料
 * @param i
 * @param strTitle
 * @param object
 * @param category
 * @return
 */
public static Map getMapType(int i, Object strTitle, Object object, String category) {
    try {
        Map map = new HashMap();
        String str = "";
        if (strTitle != null) {
            str=strTitle.toString();
        }
        map.put("code",(char)((int)'A'+i));
        map.put("title",str);
        map.put("category",category);
        if(object!=null){
            map.put("value",object.toString());
        }else {
            map.put("value","");
        }
        return map;

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


/**
 * 匯出資料到Excel--多sheet
 * @param file
 * @param mapData
 * @param page
 * @param fileName
 */
public static void downLoadFile(String file,List<List<Map>> mapData,int page,String fileName){
    //大標題
    WritableFont wf_title=new WritableFont(WritableFont.ARIAL,20,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
    WritableCellFormat wcf_table=new WritableCellFormat(wf_title);
    try{
        wcf_table.setAlignment(Alignment.CENTRE);
    }catch (WriteException e){
        e.printStackTrace();
    }

    //小標題
    WritableFont wf_title1=new WritableFont(WritableFont.ARIAL,13,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
    WritableCellFormat wcf_table1=new WritableCellFormat(wf_title1);
    try{
        wcf_table1.setAlignment(Alignment.LEFT);
    }catch (WriteException e){
        e.printStackTrace();
    }

    WritableWorkbook wwb=null;
    Workbook wb=null;
    if(page!=0){
        try{
            wb=Workbook.getWorkbook(new File(file));
            wwb=Workbook.createWorkbook(new File(file),wb);
            WritableSheet sheet=wwb.createSheet(fileName,page);

            Label label=null;

            int maxSize=0;
            int num=0;
            for(int i=0;i<mapData.size();i++){
                if(mapData.get(i).size()>maxSize){
                    maxSize=mapData.get(i).size();
                    num=i;
                }
            }
            int maxCol=0;
            int minCol=0;
            for(int z=0;z<mapData.get(num).size();z++){
                label=new Label(z,1,mapData.get(num).get(z).get("title").toString(),wcf_table1);
                sheet.addCell(label);
                sheet.setColumnView(z,20);
                if("經濟責任類".equals(mapData.get(num).get(z).get("category").toString())){
                    minCol++;
                }else {
                    maxCol++;
                }

            }
            label =new Label(0,0,mapData.get(num).get(0).get("category").toString(),wcf_table);
            sheet.addCell(label);
            sheet.mergeCells(0,0,maxCol -1 ,0);
            if(minCol!=0){
                label=new Label(maxCol,0,mapData.get(num).get(maxCol).get("category").toString(),wcf_table);
                sheet.addCell(label);
                sheet.mergeCells(maxCol,0,minCol-1+maxCol,0);
            }
            for(int i=0;i<mapData.size();i++){
                List<Map> data=https://www.cnblogs.com/yysbolg/p/mapData.get(i);
                for(int j=0;j<data.size();j++){
                    label=new Label(j,i+2,data.get(j).get("value").toString());
                    sheet.addCell(label);
                }
            }

            wwb.write();

        }catch(Exception e){
            e.printStackTrace();
        }finally {
            if(wwb!=null){
                try{
                    wwb.close();
                }catch (IOException e){
                    e.printStackTrace();
                }catch (WriteException e){
                    e.printStackTrace();
                }
            }
            if(wb!=null){
                try{
                    wb.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
  }else{
        OutputStream os=null;

        try{
            os=new FileOutputStream(file);
            wwb=Workbook.createWorkbook(os);
            WritableSheet sheet=wwb.createSheet(fileName,page);

            Label label=null;

            int maxSize=0;
            int num=0;
            for(int i=0;i<mapData.size();i++){
                if(mapData.get(i).size()>maxSize){
                    maxSize=mapData.get(i).size();
                    num=i;
                }
            }
            int maxCol=0;
            int minCol=0;
            for(int z=0;z<mapData.get(num).size();z++){
                label=new Label(z,1,mapData.get(num).get(z).get("title").toString(),wcf_table1);
                sheet.addCell(label);
                sheet.setColumnView(z,20);
                if("經濟責任類".equals(mapData.get(num).get(z).get("category").toString())){
                    minCol++;
                }else {
                    maxCol++;
                }

            }
            label =new Label(0,0,mapData.get(num).get(0).get("category").toString(),wcf_table);
            sheet.addCell(label);
            sheet.mergeCells(0,0,maxCol -1 ,0);
            if(minCol!=0){
                label=new Label(maxCol,0,mapData.get(num).get(maxCol).get("category").toString(),wcf_table);
                sheet.addCell(label);
                sheet.mergeCells(maxCol,0,minCol-1+maxCol,0);
            }
            for(int i=0;i<mapData.size();i++){
                List<Map> data=https://www.cnblogs.com/yysbolg/p/mapData.get(i);
                for(int j=0;j<data.size();j++){
                    label=new Label(j,i+2,data.get(j).get("value").toString());
                    sheet.addCell(label);
                }
            }

            wwb.write();

        }catch(Exception e){
            e.printStackTrace();
        }finally {
            if(wwb!=null){
                try{
                    wwb.close();
                }catch (IOException e){
                    e.printStackTrace();
                }catch (WriteException e){
                    e.printStackTrace();
                }
            }
            if(wb!=null){
                try{
                    wb.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }

}

標籤：Java

上一篇：POI 匯出工具 實體

下一篇：歸納從檔案中讀取資料的六種方法-JAVA IO基礎總結第2篇