map 自动生成 多工作表excel

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.zte.volidate.domain;

import java.util.List;
import java.util.Map;

/**
 *
 * @author zhy
 */
public class ExportVo {
    private Map<String,List<Map<String,String>>> data;
    private String title;
    private String savePath;

    /**
     * @return the data
     */
    public Map<String,List<Map<String,String>>> getData() {
        return data;
    }

    /**
     * @param data the data to set
     */
    public void setData(Map<String,List<Map<String,String>>> data) {
        this.data = data;
    }

    /**
     * @return the title
     */
    public String getTitle() {
        return title;
    }

    /**
     * @param title the title to set
     */
    public void setTitle(String title) {
        this.title = title;
    }

    /**
     * @return the savePath
     */
    public String getSavePath() {
        return savePath;
    }

    /**
     * @param savePath the savePath to set
     */
    public void setSavePath(String savePath) {
        this.savePath = savePath;
    }

}



package com.zte.volidate.util;

	import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import com.zte.volidate.domain.ExportVo;

	/**
	 *
	 * @author zhy
	 */
	public class ExcelTool {
		public static void main(String[] args) {
			List<Map<String,String>> list = new ArrayList<Map<String,String>>();
			Map<String,String> map = new HashMap<String,String>();
			Map<String,String> map3 = new HashMap<String,String>();
			map.put("姓名", "zhangsan1");
			map.put("学号", "1");
			map3.put("姓名", "zhangsan2");
			map3.put("学号", "2");
	
			list.add(map3);
			list.add(map);
			List<Map<String,String>> list1 = new ArrayList<Map<String,String>>();
			Map<String,String> map11 = new HashMap<String,String>();
			map11.put("姓名", "zhangsan3");
			map11.put("学号", "3");
			Map<String,String> map12 = new HashMap<String,String>();

			map12.put("姓名", "zhangsan4");
			map12.put("学号", "4");
			list1.add(map11);
			list1.add(map12);
			Map<String, List<Map<String,String>>> map2 = new HashMap<String, List<Map<String,String>>>();
			map2.put("1班", list);
			map2.put("2班", list1);
			ExportVo vo = new ExportVo();
			vo.setData(map2);
			vo.setSavePath("C:/Users/zhy/Desktop/新建文件夹 (2)/testnew.xls");
			vo.setTitle("学生信息");

			new ExcelTool().excel(vo);
		}
		
	    public void excelCreateSheet(WritableSheet wsheet, List<Map<String,String>> list, String titleName) {
	            try {
	                    WritableFont wfont = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED);
	                    WritableFont font = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
	                    WritableCellFormat nameFormat = new WritableCellFormat(wfont);
	                    WritableCellFormat titleFormat = new WritableCellFormat(font);
	                    // 设置Excel标题头
	                    wsheet.mergeCells(0, 0, 5, 0); // 合并单元格
	                    Label excelTitle1 = new Label(0, 0, titleName, nameFormat);
	                    wsheet.addCell(excelTitle1);
	                    String[] columnNames = new String[list.get(0).keySet().size()];
	                    list.get(0).keySet().toArray(columnNames);
	                    // 设置Excel表头
	                    for (int i = 0; i < columnNames.length; i++) {
	                            Label excelTitle = new Label(i, 1, columnNames[i], titleFormat);
	                            wsheet.addCell(excelTitle);
	                    }
	                    int c = 2; 
	                    java.util.Iterator<Map<String, String>> it = list.iterator();
	                    while (it.hasNext()) {
	                            Map<String,String> rows = it.next();
	                            int rowIndex = 0;
	                            for(String columnName:columnNames){
	                                Label content = new Label(rowIndex, c, rows.get(columnName));
	                                wsheet.setColumnView(0, 40);
	                                wsheet.setColumnView(1, 12);
	                                wsheet.setColumnView(2, 13);
	                                wsheet.setColumnView(3, 14);
	                                wsheet.setColumnView(4, 50);
	                                wsheet.addCell(content);
	                                rowIndex++;
	                            }
	                            c++;
	                    }
	            } catch (Exception e) {
	                    e.printStackTrace();
	            }
	    }

	    public void excel(ExportVo vo) {
	            try {
	                    String path = vo.getSavePath();
	                    Map<String, List<Map<String,String>>> map = vo.getData();
	                    String titleName = vo.getTitle();
	                    WritableWorkbook wbook = Workbook.createWorkbook(new File(path)); // 建立excel文件
	                    for (Entry<String, List<Map<String,String>>> entry : map.entrySet()) {
	                            WritableSheet wsheet = wbook.createSheet(entry.getKey(), 0); // 工作表名称
	                            excelCreateSheet(wsheet,entry.getValue(), titleName);
	                    }
	                    wbook.write();
	                    wbook.close();
	            } catch (IOException e) {
	                    e.printStackTrace();
	            } catch (WriteException e) {
	                    e.printStackTrace();
	            }
	    }
	}