JxlExcelUtils.java

package com.bootdo.common.utils;

/**
 * Created with IntelliJ IDEA.
 * User: GRAND-CQU-RD001
 * Date: 14-5-8
 * Time: 上午10:31
 * To change this template use File | Settings | File Templates.
 */
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.springframework.util.StringUtils;

import jxl.CellView;
import jxl.SheetSettings;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
/**
 * jxl导出excel
 * @author jamboree
 * @date  2013-11-28
 */
public class JxlExcelUtils {

    /**--------------exportExcel2
     * @author
     * @param objData1 导出内容数组
     * @param sheetName1 导出工作表的名称
     * @param columns1 导出Excel的表头数组
     * @return
     */
    public static int exportExcel2(
    		HttpServletResponse response,
    		List<Map<String, Object>> objData1, String sheetName1,List<String> columns1,
            List<Map<String, Object>> objData2, String sheetName2,List<String> columns2
    ) {
        int flag = 0;
        //声明工作簿jxl.write.WritableWorkbook
        WritableWorkbook wwb;
        try {
            //根据传进来的file对象创建可写入的Excel工作薄
            OutputStream os = response.getOutputStream();
            wwb = Workbook.createWorkbook(os);
            /*
             * 创建一个工作表、sheetName为工作表的名称、"0"为第一个工作表
             * 打开Excel的时候会看到左下角默认有3个sheet、"sheet1、sheet2、sheet3"这样
             * 代码中的"0"就是sheet1、其它的一一对应。
             * createSheet(sheetName, 0)一个是工作表的名称，另一个是工作表在工作薄中的位置
             */
            WritableSheet ws1 = wwb.createSheet(sheetName1, 0);
            WritableSheet ws2 = wwb.createSheet(sheetName2, 1);
            SheetSettings ss1 = ws1.getSettings();
            ss1.setVerticalFreeze(1);//冻结表头
            SheetSettings ss2 = ws2.getSettings();
            ss2.setVerticalFreeze(1);//冻结表头
            CellView cellView = new CellView();
            cellView.setAutosize(true); //设置自动大小

            //创建单元格样式
            WritableFont font1 = new WritableFont(WritableFont.createFont("微软雅黑"), 10 ,WritableFont.BOLD);
            WritableFont font2 = new WritableFont(WritableFont.createFont("微软雅黑"), 9 ,WritableFont.NO_BOLD);
            WritableCellFormat wcf1 = new WritableCellFormat(font1);
            WritableCellFormat wcf2 = new WritableCellFormat(font2);  //设置样式，字体
            //背景颜色
            wcf1.setAlignment(Alignment.CENTRE);  //平行居中
            wcf1.setVerticalAlignment(VerticalAlignment.CENTRE);  //垂直居中
            wcf1.setBackground(Colour.GRAY_25);
            wcf2.setAlignment(Alignment.CENTRE);  //平行居中
            wcf2.setVerticalAlignment(VerticalAlignment.CENTRE);  //垂直居中
          //  wcf2.setBackground(Colour.LIGHT_ORANGE);
            //判断一下表头数组是否有数据   写入sheet1数据
            if (columns1 != null && columns1.size() > 0) {
                //循环写入表头
                for (int i = 0; i < columns1.size(); i++) {
                    /*
                     * 添加单元格(Cell)内容addCell()
                     * 添加Label对象Label()
                     * 数据的类型有很多种、在这里你需要什么类型就导入什么类型
                     * 如：jxl.write.DateTime 、jxl.write.Number、jxl.write.Label
                     * Label(i, 0, columns[i], wcf)
                     * 其中i为列、0为行、columns[i]为数据、wcf为样式
                    * 合起来就是说将columns[i]添加到第一行(行、列下标都是从0开始)第i列、样式为什么"色"内容居中
                     */
                    //根据内容自动设置列宽
                    ws1.setColumnView(i, new String("空格"+columns1.get(i)+"空格").length());
                    ws1.addCell(new Label(i, 0, columns1.get(i), wcf1));
                }
                //判断表中是否有数据
                if (objData1 != null && objData1.size() > 0) {
                    //循环写入表中数据
                    for (int i = 0; i < objData1.size(); i++) {
                        //转换成map集合{activyName:测试功能,count:2}
                        Map<String, Object> map = (Map<String, Object>)objData1.get(i);
                        //循环输出map中的子集：既列值
                        int j=0;
                        for(Object o:map.keySet()){
                            //ps：因为要“”通用”“导出功能，所以这里循环的时候不是get("Name"),而是通过map.get(o)
                            //根据内容自动设置列宽
                            int columnSize = new String("空格"+map.get(o)+"空格").length()<8?8:new String("空格"+map.get(o)+"空格").length();
                            ws1.setColumnView(j, columnSize);
                            ws1.addCell(new Label(j,i+1,(String.valueOf(map.get(o))==null||"null".equals(String.valueOf(map.get(o))))?"--":String.valueOf(map.get(o)),wcf2));
                            j++;
                        }
                    }
                }else{
                    flag = -1;
                }
            }

            //判断一下表头数组是否有数据     写入sheet2数据
            if (columns2 != null && columns2.size() > 0) {
                //循环写入表头
                for (int i = 0; i < columns2.size(); i++) {
                    /*
                     * 添加单元格(Cell)内容addCell()
                     * 添加Label对象Label()
                     * 数据的类型有很多种、在这里你需要什么类型就导入什么类型
                     * 如：jxl.write.DateTime 、jxl.write.Number、jxl.write.Label
                     * Label(i, 0, columns[i], wcf)
                     * 其中i为列、0为行、columns[i]为数据、wcf为样式
                    * 合起来就是说将columns[i]添加到第一行(行、列下标都是从0开始)第i列、样式为什么"色"内容居中
                     */
                    //根据内容自动设置列宽
                    ws2.setColumnView(i, new String("空格"+columns2.get(i)+"空格").length());
                    ws2.addCell(new Label(i, 0, columns2.get(i), wcf1));
                }
                //判断表中是否有数据
                if (objData2 != null && objData2.size() > 0) {
                    //循环写入表中数据
                    for (int i = 0; i < objData2.size(); i++) {
                        //转换成map集合{activyName:测试功能,count:2}
                        Map<String, Object> map = (Map<String, Object>)objData2.get(i);
                        //循环输出map中的子集：既列值
                        int j=0;
                        for(Object o:map.keySet()){
                            //ps：因为要“”通用”“导出功能，所以这里循环的时候不是get("Name"),而是通过map.get(o)
                            //根据内容自动设置列宽
                            int columnSize = new String("空格"+map.get(o)+"空格").length()<8?8:new String("空格"+map.get(o)+"空格").length();
                            ws2.setColumnView(j, columnSize);
                            ws2.addCell(new Label(j,i+1,String.valueOf(map.get(o)),wcf2));
                            j++;
                        }
                    }
                }else{
                    flag = -1;
                }
            }

            //写入Exel工作表
            wwb.write();
            //关闭Excel工作薄对象
            wwb.close();
            //关闭流
            os.flush();
            os.close();
            os =null;

        }catch (IllegalStateException e) {
            System.err.println(e.getMessage());
        }
        catch (Exception ex) {
            flag = 0;
            ex.printStackTrace();
        }
        return flag;
    }

    /**
     * 下载excel------2
     * @author
     * @param response
     * @param filename 文件名 ,如:20110808.xls
     * @param listData1 数据源
     * @param sheetName1 表头名称
     * @param columns1 列名称集合,如：{物品名称，数量，单价}
     */
    public static void exportTwoSheetExcle(
    		HttpServletResponse response,
    		String filename,
    		List<Map<String, Object>> listData1,String sheetName1,List<String> columns1,
            List<Map<String, Object>> listData2,String sheetName2,List<String> columns2
    )
    {
        //调用上面的方法、生成Excel文件
        response.setContentType("application/vnd.ms-excel");
        //response.setHeader("Content-Disposition", "attachment;filename="+filename);
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes("gb2312"), "ISO8859-1") + ".xls");
            exportExcel2(response, listData1, sheetName1, columns1, listData2, sheetName2, columns2);
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
    }



    /**
     * 下载excel------1
     * @author
     * @param response
     * @param filename 文件名 ,如:20110808.xls
     * @param listData1 数据源
     * @param sheetName1 表头名称
     * @param columns1 列名称集合,如：{物品名称，数量，单价}
     */
    public static void exportOwoSheetExcle(
    		HttpServletResponse response,
    		String filename,
    		List<Map<String, Object>> listData1,String sheetName1,List<String> columns1
    		)
    {
    	//调用上面的方法、生成Excel文件
    	response.setContentType("application/vnd.ms-excel");
    	//response.setHeader("Content-Disposition", "attachment;filename="+filename);
    	try {
    		response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes("gb2312"), "ISO8859-1") + ".xls");
    		exportExcel1(response, listData1, sheetName1, columns1);
    	} catch (UnsupportedEncodingException e) {
    		e.printStackTrace();
    	}
    }

    
    /**
     * 
     * @param response 
     * @param filename 文件名
     * @param listData 数据
     * @param sheetName1 表格名
     * @param columns 列数组
     * @param columnKeys 列关键字数组
     * 
     *  如下翻译处理，
     *  String statusRemark = "authStatus|1:审核中|2:审核通过|3:审核不通过|其他";
     * 
     */
    public static void exportOwoSheetExcleFinal(
    		HttpServletResponse response,
    		String filename,
    		List<Map<String, Object>> listData,String sheetName,String [] columns,String [] columnKeys){
    	
    	try {
	    	List<String> columnNames = new ArrayList<String>();
	    	for(String column:columns) {
	    		columnNames.add(column);
	    	}
	    	
	        List<Map<String, Object>> listDataFinal = new ArrayList<Map<String, Object>>();
	        for (int i = 0; i < listData.size(); i++) {
	            Map<String, Object> stringObjectMap = (Map<String, Object>)listData.get(i);
	            Map<String, Object> map1 = new LinkedHashMap<String, Object>();
	            
	            for(int j=0;j<columnNames.size();j++) {
	            	
	            	if(!StringUtils.isEmpty(columnKeys[j]) && columnKeys[j].contains("|")) {//有需要转换 翻译的列值
	            		String [] colKeyStr = columnKeys[j].split("[|]");//第一个位置为key，最后一个为缺省值，中间为需要翻译的值
	            		String colVal = String.valueOf(stringObjectMap.get(colKeyStr[0]));
	            		
	            		String colValFinal = null;
	            		for(int k=1;k<colKeyStr.length-1;k++) {
	            			if(colKeyStr[k].split(":")[0].equals(colVal)){
	            				colValFinal = colKeyStr[k].split(":")[1];
	            			}
	            		}
	            		if(StringUtils.isEmpty(colValFinal)) {//如果未找到翻译值，就取最后一个
	            			colValFinal = colKeyStr[colKeyStr.length-1];
	            		}
	            		
	            		map1.put(columnNames.get(j), colValFinal);
	            		continue;
	            	}
	            	map1.put(columnNames.get(j), stringObjectMap.get(columnKeys[j]));
	            	
	            }
	            listDataFinal.add(map1);
	        }
	        JxlExcelUtils.exportOwoSheetExcle(response, filename, listDataFinal, sheetName, columnNames);
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
    }
    
    
    


    /**-------exportExcel1
     * @author
     * @param objData1 导出内容数组
     * @param sheetName1 导出工作表的名称
     * @param columns1 导出Excel的表头数组
     * @return
     */
    public static int exportExcel1(HttpServletResponse response, List<Map<String, Object>> objData, String sheetName,List<String> columns) {
    	int flag = 0;
        //声明工作簿jxl.write.WritableWorkbook
        WritableWorkbook wwb;
        try {
            //根据传进来的file对象创建可写入的Excel工作薄
            OutputStream os = response.getOutputStream();

            wwb = Workbook.createWorkbook(os);
            /*
             * 创建一个工作表、sheetName为工作表的名称、"0"为第一个工作表
             * 打开Excel的时候会看到左下角默认有3个sheet、"sheet1、sheet2、sheet3"这样
             * 代码中的"0"就是sheet1、其它的一一对应。
             * createSheet(sheetName, 0)一个是工作表的名称，另一个是工作表在工作薄中的位置
             */
            WritableSheet ws = wwb.createSheet(sheetName, 0);
            SheetSettings ss = ws.getSettings();
            ss.setVerticalFreeze(1);//冻结表头
            CellView cellView = new CellView();
            cellView.setAutosize(true); //设置自动大小
            //创建单元格样式
            WritableFont font1 = new WritableFont(WritableFont.createFont("微软雅黑"), 10 ,WritableFont.BOLD);
            WritableFont font2 = new WritableFont(WritableFont.createFont("微软雅黑"), 9 ,WritableFont.NO_BOLD);
            WritableCellFormat wcf1 = new WritableCellFormat(font1);
            WritableCellFormat wcf2 = new WritableCellFormat(font2);  //设置样式，字体
            //背景颜色
            wcf1.setAlignment(Alignment.CENTRE);  //平行居中
            wcf1.setVerticalAlignment(VerticalAlignment.CENTRE);  //垂直居中
            wcf1.setBackground(Colour.GRAY_25);
            wcf2.setAlignment(Alignment.CENTRE);  //平行居中
            wcf2.setVerticalAlignment(VerticalAlignment.CENTRE);  //垂直居中
           // wcf2.setBackground(Colour.LIGHT_ORANGE);
            //判断一下表头数组是否有数据
            if (columns != null && columns.size() > 0) {
                //循环写入表头
                for (int i = 0; i < columns.size(); i++) {
                    /*
                     * 添加单元格(Cell)内容addCell()
                     * 添加Label对象Label()
                     * 数据的类型有很多种、在这里你需要什么类型就导入什么类型
                     * 如：jxl.write.DateTime 、jxl.write.Number、jxl.write.Label
                     * Label(i, 0, columns[i], wcf)
                     * 其中i为列、0为行、columns[i]为数据、wcf为样式
                    * 合起来就是说将columns[i]添加到第一行(行、列下标都是从0开始)第i列、样式为什么"色"内容居中
                     */
                    //根据内容自动设置列宽
                    ws.setColumnView(i, new String("空格"+columns.get(i)+"空格").length());
                    ws.addCell(new Label(i, 0, columns.get(i), wcf1));
                }
                //判断表中是否有数据
                if (objData != null && objData.size() > 0) {
                    //循环写入表中数据
                    for (int i = 0; i < objData.size(); i++) {
                        //转换成map集合{activyName:测试功能,count:2}
                        Map<String, Object> map = (Map<String, Object>)objData.get(i);
                        //循环输出map中的子集：既列值
                        int j=0;
                        for(Object o:map.keySet()){
                            //ps：因为要“”通用”“导出功能，所以这里循环的时候不是get("Name"),而是通过map.get(o)
                            //根据内容自动设置列宽
                            int columnSize = new String("空格"+map.get(o)+"空格").length()<8?8:new String("空格"+map.get(o)+"空格").length();
                            ws.setColumnView(j, columnSize);
                            ws.addCell(new Label(j,i+1,(String.valueOf(map.get(o))==null||"null".equals(String.valueOf(map.get(o))))?"--":String.valueOf(map.get(o)),wcf2));
                            j++;
                        }
                    }
                }else{
                    flag = -1;
                }
                //写入Exel工作表
                wwb.write();
                //关闭Excel工作薄对象
                wwb.close();
                //关闭流
                os.flush();
                os.close();
                os =null;
            }
        }catch (IllegalStateException e) {
            System.err.println(e.getMessage());
        }
        catch (Exception ex) {
            flag = 0;
            ex.printStackTrace();
        }
        return flag;
    }

}