jxl 导出各类自定义格式Excel

首先创建了一个实体对象，用来封装导出时需要传入的数据、设置信息等。

import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
/**
 * <p>Title: DownLoad</p>
 * <p>Description: download实体</p>
 */
public class DownLoad
{
    private String downType;// 导出格式
    private String fname;// 导出文件名
    private String checks;// 勾选项
    private String title;// 标题
   //integer：加入有两行或多行表头，填写1,2.. 表头   key：对象对应的字段名   value：表头名称
    private Map<Integer, LinkedHashMap<String, String>> header;
    //下拉框 String：第几列创建下拉框 ，list 下拉框内容
    private Map<Integer,List<Object>> dropDownBox;
    //合并单元格  外层list：所有要合并的单元格，内层list样式【m,n,p,q】
    //作用是从(m,n)到(p,q)的单元格全部合并
    private List<List<Integer>> mergeCells;
    
    private Map<Integer, Integer> celColumn;//设置列宽，默认列宽10*250，如果有特殊的设置第几列，宽度
    
    @SuppressWarnings("rawtypes")
	private List<?> dataList = new ArrayList();//数据list

	public String getDownType() {
		return downType;
	}
	public void setDownType(String downType) {
		this.downType = downType;
	}
	public String getFname() {
		return fname;
	}
	public void setFname(String fname) {
		this.fname = fname;
	}
	public String getChecks() {
		return checks;
	}
	public void setChecks(String checks) {
		this.checks = checks;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public Map<Integer, LinkedHashMap<String, String>> getHeader() {
		return header;
	}
	public void setHeader(Map<Integer, LinkedHashMap<String, String>> header) {
		this.header = header;
	}
	public Map<Integer, List<Object>> getDropDownBox() {
		return dropDownBox;
	}
	public void setDropDownBox(Map<Integer, List<Object>> dropDownBox) {
		this.dropDownBox = dropDownBox;
	}
	public List<List<Integer>> getMergeCells() {
		return mergeCells;
	}
	public void setMergeCells(List<List<Integer>> mergeCells) {
		this.mergeCells = mergeCells;
	}
	public List<?> getDataList() {
		return dataList;
	}
	public void setDataList(List<?> dataList) {
		this.dataList = dataList;
	}
	public Map<Integer, Integer> getCelColumn() {
		return celColumn;
	}
	public void setCelColumn(Map<Integer, Integer> celColumn) {
		this.celColumn = celColumn;
	}
}

具体导出的接口如下：

import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import jxl.CellView;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFeatures;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;




/**
 * <p>Title: DownloadService</p>
 * <p>Description: 导出数据 </p>
 */
@Service
public class DownloadService {
	
	/**
	 * @category 导出功能
	 * @param download
	 * @param response
	 * @param request
	 */
	 @SuppressWarnings("unused")
	public static void downloading(DownLoad download,HttpServletResponse response, HttpServletRequest request){
	        OutputStream os = null;
	        final String userAgent = request.getHeader("USER-AGENT");
	        String fname = download.getFname();
	        if(fname==null){
	            return;
	        }
	        fname = fname.replaceAll(" ","_");
	        String filepath = request.getSession().getServletContext().getRealPath("") + "\\fonts\\";// 获得绝对路径
	        try{
	            if (StringUtils.contains(userAgent, "Mozilla")){
	                fname = new String(fname.getBytes(), "ISO8859-1");
	            } else{
	                fname = URLEncoder.encode(download.getFname(), "UTF-8");
	            }
	        } catch (UnsupportedEncodingException e){
	            e.printStackTrace();
	        }

            response.reset();
            // 生成xls文件
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename="
                    + fname + ".xls");
            try
            {
                os = response.getOutputStream();
                WritableWorkbook book = Workbook.createWorkbook(os);//创建Excel文件信息
                
                /**
                 * 标题1样式
                 */
                jxl.write.WritableFont titleFontStyle = new jxl.write.WritableFont(
                        WritableFont.ARIAL, 20, WritableFont.BOLD, false,
                        jxl.format.UnderlineStyle.NO_UNDERLINE,
                        jxl.format.Colour.BLACK);
                jxl.write.WritableCellFormat titleStyle = new jxl.write.WritableCellFormat(titleFontStyle);
                titleStyle.setBackground(jxl.format.Colour.GRAY_25);
                titleStyle.setBorder(Border.NONE, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.WHITE);
                titleStyle.setAlignment(Alignment.CENTRE);//水平居中
                titleStyle.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直居中
                
                WritableCellFormat wcfF = new WritableCellFormat(
                        NumberFormats.TEXT); //定义一个单元格样式
                        CellView cv = new CellView(); //定义一个列显示样式 
                        cv.setFormat(wcfF);//把定义的单元格格式初始化进去
                        cv.setSize(10*265);//设置列宽度（不设置的话是0，不会显示）
            	/**
                 * 表头样式
                 */
                WritableFont headFontStyle = new WritableFont(
                        WritableFont.ARIAL, 12, WritableFont.BOLD, false,
                        UnderlineStyle.NO_UNDERLINE,
                        Colour.BLACK);
                WritableCellFormat headStyle = new WritableCellFormat(headFontStyle);
                headStyle.setBackground(Colour.LIGHT_GREEN);
                headStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
                headStyle.setAlignment(Alignment.CENTRE);//水平居中
                headStyle.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直居中
                
                
                /**
                 * 数据单元格样式
                 */
                jxl.write.WritableFont dataFontStyle = new jxl.write.WritableFont(
                        WritableFont.ARIAL, 9, WritableFont.NO_BOLD, false,
                        jxl.format.UnderlineStyle.NO_UNDERLINE,
                        jxl.format.Colour.BLACK);
                jxl.write.WritableCellFormat dataStyle = new jxl.write.WritableCellFormat(
                        dataFontStyle);
                dataStyle.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); // 设置单元格样式
                dataStyle.setBackground(jxl.format.Colour.WHITE); // 设置单元格背景色
                dataStyle.setWrap(true);//自动换行
                dataStyle.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直居中
                
                /**
                 * 尾部格式
                 */
                jxl.write.WritableFont footFontStyle = new jxl.write.WritableFont(
                        WritableFont.ARIAL, 16, WritableFont.BOLD, false,
                        jxl.format.UnderlineStyle.NO_UNDERLINE,
                        jxl.format.Colour.BLACK);
                jxl.write.WritableCellFormat fontStyle = new jxl.write.WritableCellFormat(footFontStyle);
                fontStyle.setBackground(jxl.format.Colour.WHITE);
                fontStyle.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.WHITE);
                fontStyle.setAlignment(Alignment.LEFT);//水平靠右
                fontStyle.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直居中
                
                int whichRow = 0;//目前到第几行
                
                WritableSheet sheet = book.createSheet("sheet1",0);//创建工作簿
                Label l = new Label(0, whichRow , download.getTitle() , titleStyle);//写入标题
                sheet.addCell(l);
                /**
                 * 写入表头
                 */
                Map<Integer, LinkedHashMap<String, String>> headMap = download.getHeader();
                List<String> headOne = new ArrayList<String>();//表头1
                List<String> headTwo = new ArrayList<String>();//表头2
                List<String> headOneToValue = new ArrayList<String>();//字段名
                if(headMap.containsKey(1)){
                	Map<String, String> map = headMap.get(1);
                	for(String key : map.keySet()){
                		headOneToValue.add(key);
                		headOne.add(map.get(key));
                	}
                }
                if(headMap.containsKey(2)){
                	Map<String, String> map = headMap.get(2);
                	for(String key : map.keySet()){
                		headTwo.add(key);
                	}
                }
                if(headOne!=null && headOne.size()>0){
                	sheet.mergeCells(0, 0, headOne.size()-1, 1);//合并标题两行
                	whichRow = whichRow+2;//标题加两行
                	for (int i = 0; i < headOne.size(); i++){
                		sheet.setColumnView(i, cv);//设置工作表中第n列的样式
                        l = new Label(i, whichRow , headOne.get(i), headStyle);
                        sheet.addCell(l);
                    }
                	whichRow = whichRow+1;//表头加一行
                }
                if(headTwo!=null && headTwo.size()>0){
                	for (int i = 0; i < headTwo.size(); i++){
                        l = new Label(i, whichRow , headTwo.get(i), headStyle);
                        sheet.addCell(l);
                    }
                	whichRow = whichRow+1;//第二行标题
                }
                sheet.getSettings().setVerticalFreeze(whichRow);//下载模板固定表头
                
            /**
             * 合并单元格
             */
            List<List<Integer>> mergeCells = download.getMergeCells();
            if(mergeCells!=null){
            	for (List<Integer> list : mergeCells) {
					sheet.mergeCells(list.get(0), list.get(1), list.get(2), list.get(3));//合并单元格
				}
            }
            /**
             * 创建下拉框
             */
            Map<Integer,List<Object>> dropDownBox = download.getDropDownBox();
            if(dropDownBox!=null){
            	for(Integer key : dropDownBox.keySet()){
            		for (int td = whichRow; td < 50; td++) {
    					WritableCellFeatures wrcf = new WritableCellFeatures();
    					Label subLabel = new Label(key, td, "");
    					wrcf.setDataValidationList(dropDownBox.get(key));
    					subLabel.setCellFeatures(wrcf);
    					sheet.addCell(subLabel);
    				}
            	}
            }
            /**
             * 设置列宽
             */
            Map<Integer, Integer> celColumn = download.getCelColumn();
            if(celColumn!=null){
            	for (Integer colum : celColumn.keySet()) {
            		sheet.setColumnView(colum, celColumn.get(colum));//设置工作表中第n列的样式
				}
            }
            /**
             * 插入数据
             */
            List<?> dataList = download.getDataList();
            if(dataList!=null){
            	for (int i = 0; i < dataList.size(); i++) {
            		Object obj = dataList.get(i);//将对象转换为map
            		Map<String, Object> map = new HashMap<String, Object>(); 
            		if(obj != null){      
		                Field[] declaredFields = obj.getClass().getDeclaredFields();      
		                for (Field field : declaredFields) {      
		                    field.setAccessible(true);    
		                    map.put(field.getName(), field.get(obj));  
		                }
                    }
            		for (int j = 0; j < headOneToValue.size(); j++) {//根据字段名称插入数据
            			String cellValue = map.get(headOneToValue.get(j))==null?"":map.get(headOneToValue.get(j)).toString();
            			if(headOneToValue.get(j).equals("sex") && cellValue!=""){
            				if(cellValue.equals("1")){
            					cellValue = "男";
            				}else{
            					cellValue = "女";
            				}
            			}
            		l = new Label(j,whichRow,cellValue,dataStyle);
                        sheet.addCell(l);
					}
            		whichRow = whichRow+1;//第几行插入数据
				}
            }
            book.write();//输出到文件
            book.close();
            os.close();
        } catch (Exception e)
            {
                e.printStackTrace();
            }
        }
}

这里边还有好多不完善的地方，等以后下一步完善！