package cn.ksource.core.util;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

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

/**
 * @author YK 生成Execl
 */
public class ExeclUtil {

	/**
	 * 将List<Map>格式的数据集转换为execl
	 * @param title execl sheet的名字
	 * @param file 生成execl后存放的路径
	 * @param list 
	 * 作者：杨凯
	 */
	public static void list2Execl(String title, File file, List<Map> list) {
		try {
			WritableWorkbook book = Workbook.createWorkbook(file);
			WritableSheet sheet = book.createSheet(title, 0);
			
			if (list == null || list.size() == 0) {
				throw new RuntimeException("传入的list为null或空");
			}
			Map keyMap = list.get(0);
			
			Iterator iterator = keyMap.keySet().iterator();
			String[] keys = new String[keyMap.keySet().size()];
			int i=0;
			while (iterator.hasNext()) {
				String key = ConvertUtil.obj2Str(iterator.next());
				Label cell = new Label(i,0,key);
				sheet.addCell(cell);
				keys[i] = key;
				i++;
			}
			int rowIndex = 1;
			int columnIndex = 0;
			for (Map map : list) {
				for (String key : keys) {
					Label cell = new Label(columnIndex,rowIndex,ConvertUtil.obj2Str(map.get(key)));
					sheet.addCell(cell);
					columnIndex ++ ;
				}
				columnIndex = 0;
				rowIndex ++ ;
			}
			book.write();
			book.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	
	
	/**
	 * 将List<Map>格式的数据集转换为execl
	 * @param title execl sheet的名字
	 * @param list 
	 * 作者：jxl
	 */
	public static ByteArrayOutputStream list2Execl(String title, List<Map> list) {
		try {
			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			
			WritableWorkbook book = Workbook.createWorkbook(baos);
			WritableSheet sheet = book.createSheet(title, 0);
			
			if (list == null || list.size() == 0) {
				throw new RuntimeException("传入的list为null或空");
			}
			Map keyMap = list.get(0);
			
			
			WritableFont wfont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); 
			WritableCellFormat titleFormat = new WritableCellFormat(wfont); 
			
			Iterator iterator = keyMap.keySet().iterator();
			String[] keys = new String[keyMap.keySet().size()];
			int i=0;
			while (iterator.hasNext()) {
				String key = ConvertUtil.obj2Str(iterator.next());
				Label cell = new Label(i,0,key,titleFormat);
				sheet.addCell(cell);
				keys[i] = key;
				i++;
			}
			int rowIndex = 1;
			int columnIndex = 0;
			
			
			
			
			for (Map map : list) {
				for (String key : keys) {
					Label cell = new Label(columnIndex,rowIndex,ConvertUtil.obj2Str(map.get(key)));
					sheet.addCell(cell);
					columnIndex ++ ;
				}
				columnIndex = 0;
				rowIndex ++ ;
			}
			book.write();
			book.close();
			return baos;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	
	public static void main(String[] args) {
		List<Map> list = new ArrayList<Map>();
		
		Map map1 = new HashMap();
		map1.put("name", "江小磊");
		map1.put("age", "20");
		
		Map map2 = new HashMap();
		map2.put("name", "江小磊1");
		map2.put("age", "21");
		
		list.add(map1);
		list.add(map2);
		
		list2Execl("test",list);
	}
}