java poi 创建excel 兼容2003\2007 模拟数据库导出 解决自动调整宽度、日期格式

必须依赖poi包

[img]http://dl.iteye.com/upload/attachment/579521/274f1318-5558-3713-b53e-19563de433ac.png[/img]


结果:

[img]http://dl.iteye.com/upload/attachment/579525/60dfa028-290e-3bbc-8476-7ab9016df838.png[/img]

package best.utils;

import java.util.Map;

import org.apache.poi.ss.usermodel.Workbook;

public interface ExcelTemplate {

	public final static String EXCEL_03="2003";

	public final static String EXCEL_07="2007";

	/**
	 * 可选
	 * 设置excel日期样式
	 * @param dataFormat
	 */
	public void setDateFormat(String dataFormat);
	/**
	 * 可选
	 * 设置工作薄名称
	 * @param sheetName
	 */
	public void setSheetName(String sheetName) ;
	/**
	 * 可选
	 * 是否自动调整列宽
	 * @param b
	 */
	public void isAutoSizeColumn(boolean b);
	/**
	 * 转换 中应为呼唤
	 * @param mess
	 */
	public void setMess(Map<String, String> mess);
	/**
	 * 同时创建excel 03、07版
	 * @return key=2003或2007 对应为excel2003、excel2007
	 */
	public Map<String,Workbook> createExcel();

	/**
	 * 创建excel 具体版
	 * @param excelVerson 2003或2007
	 * @return 
	 */
	public Workbook createExcel(String excelVerson);

}

package best.utils;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelTemplateImpl implements ExcelTemplate {

	private List<Map<String, Object>> datas;

	private final Map<String,Workbook> wbmap = new HashMap<String, Workbook>(2);

	private String dataFormat = "yyyy-MM-dd";

	private String sheetName;

	private boolean isAutoSizeColumn=false;

	private Map<String,String> mess;


	public ExcelTemplateImpl(List<Map<String, Object>> datas){
		init(datas);
	}

	/**
	 * 初始化
	 * @param datas
	 */
	private void init(List<Map<String, Object>> datas){
		this.datas = datas;
		//构造2003、2007版excel
		wbmap.put(ExcelTemplate.EXCEL_03, new HSSFWorkbook());
		wbmap.put(ExcelTemplate.EXCEL_07, new XSSFWorkbook());

	}

	public void setDateFormat(String dataFormat){

		this.dataFormat = dataFormat;
	}

	public Workbook createExcel(String excelVerson){
		Workbook wb = wbmap.get(excelVerson);
		initExcel(wb);
		return wb;
	}

	public Map<String,Workbook> createExcel(){

		Set<Entry<String, Workbook>> wbset = wbmap.entrySet();
		Iterator<Entry<String, Workbook>> eit = wbset.iterator();

		while(eit.hasNext()){
			Entry<String, Workbook> workEntry = eit.next();
			Workbook wb = workEntry.getValue();
			initExcel(wb);
		}
		return wbmap;
	}

	private void initExcel(Workbook wb){
		//设置样式
		CellStyle cellStyle = wb.createCellStyle();//创建样式
		cellStyle.setDataFormat(wb.createDataFormat().getFormat(dataFormat));//日期格式
		cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//居中

		//创建一个新的表\并创建名称
		Sheet s = null==sheetName?s = wb.createSheet():wb.createSheet(sheetName);


		for(int i=0; i<datas.size(); i++){ //遍历数据

			Map<String,Object> map = datas.get(i);

			//定义列(列名第一行)
			Row titleColl = s.createRow(0);

			//定义行(内容第二行起)
			Row row = s.createRow(i+1);

			Set<Entry<String, Object>> set = map.entrySet();

			Iterator<Entry<String, Object>> it = set.iterator();
			int x = 0;
			while(it.hasNext()){
				//列名
				Entry<String, Object> entry = it.next();
				Cell titleCell = titleColl.createCell(x);
				String titleName = entry.getKey();
				titleCell.setCellValue(null==mess||null==mess.get(titleName)?titleName:mess.get(titleName));
				//设置列名样式
				titleCell.setCellStyle(cellStyle);
				//内容
				Cell cell = row.createCell(x);

				Object value = entry.getValue();

				if(value instanceof String){
					cell.setCellValue(value.toString());
				}else if(value instanceof  Double){
					cell.setCellValue((Double)value);
				}else if(value instanceof Integer){
					cell.setCellValue((Integer)value);
				}else if(value instanceof Float){
					cell.setCellValue((Float)value);
				}else if(value instanceof Boolean){
					cell.setCellValue((Boolean)value);
				}else if(value instanceof java.util.Date|value instanceof java.sql.Date){
					cell.setCellValue((Date)value);
				}
				//设置内容样式
				cell.setCellStyle(cellStyle);


				x++;

			}
			//自动调整列宽
			if(isAutoSizeColumn) s.autoSizeColumn(i);

		}
	}

	public List<Map<String, Object>> getDatas() {
		return datas;
	}

	public void setDatas(List<Map<String, Object>> datas) {
		this.datas = datas;
	}

	public String getSheetName() {
		return sheetName;
	}

	public void setSheetName(String sheetName) {
		this.sheetName = sheetName;
	}


	public Map<String, String> getMess() {
		return mess;
	}
	public void setMess(Map<String, String> mess) {
		this.mess = mess;
	}
	public void isAutoSizeColumn(boolean b) {
		// TODO Auto-generated method stub
		this.isAutoSizeColumn = b;
	}

}

测试

package best.utils;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Test {

	/**
	 * @param args
	 */
public static void main(String[] args){
		//	模拟数据
		List<Map<String,Object>> datas = new ArrayList<Map<String,Object>>();

		Map<String,Object> map = new HashMap<String,Object>();

		map.put("用户名", "xiaomaha");
		map.put("密码", "1234567qq");
		map.put("创建日期", new Date());

		Map<String,Object> map2 = new HashMap<String,Object>();

		map2.put("用户名", "xiaomaha21111111111111111111111111111111111111中文11111112");
		map2.put("密码", "1-1234567qq11111111111111111111111111111111111111中文111111111113");
		map2.put("创建日期", new Date());

		Map<String,Object> map3 = new HashMap<String,Object>();
		map3.put("用户名", "xiaomaha3");
		map3.put("密码", "2-1234567qq");
		map3.put("创建日期", new Date());

		datas.add(map);
		datas.add(map2);
		datas.add(map3);
		for(int i=0;i<500;i++){
			Map<String,Object> maps = new HashMap<String,Object>();
			maps.put("用户名", "xiaomaha3"+i);
			maps.put("密码", "2-1234567qq"+i);
			maps.put("创建日期", new Date());
			datas.add(maps);
		}

		//创建excel
		ExcelTemplate et = new ExcelTemplateImpl(datas);
		et.setSheetName("测试");
		Map<String, String> mess = new HashMap<String, String>();
		mess.put("用户名","userName");
		mess.put("密码","pw");
		mess.put("创建日期","createDate");
		et.setMess(mess);
		et.isAutoSizeColumn(true);
		//		同时生成2003、2007版
//		Map<String, Workbook> workMap = et.createExcel();
//		Workbook wb = workMap.get(ExcelTemplate.EXCEL_07); //获取excel2007
		Workbook wb = et.createExcel(ExcelTemplate.EXCEL_03);		

		//如果愿意英汉转换


//		1:最好的方法是写一个xml！ 然后解析
//      2:json格式，当然需要相对应的json插件
//		3:Map实现
//		---------------------------------------
//		Map例子
//		Map<String,Object> map3 = new HashMap<String,Object>();
//		map3.put("userName", "xiaomaha3");
//		map3.put("passWord", "2-1234567qq");
//		map3.put("createDate", new Date());
//		Map<String,String> m = new HashMap<String,String>();
//		m.put("userName", "用户名");
//		m.put("passWord", "密码");
//		m.put("createDate", "创建日期");
//		然后ExcelTemplate 加入一个方法就可以实现了



		//测试  写入磁盘
		String filename = "d://workbook2.xls";
		if (wb instanceof XSSFWorkbook) {
		    filename = filename + "x";
		}


		FileOutputStream out;
		try {
			out = new FileOutputStream(filename);

			try {
				wb.write(out);
				out.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}


		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}


	}

}

页面下载例子

			String contenttype = "application/msexcel"  
			response.setContentType(contenttype);   
			response.setHeader("Content-Disposition", "attachment;Filename=" + filename + ".xls");//03为.xls  07为.xlsx   
			OutputStream os = response.getOutputStream();   
			//创建excel   
			ExcelTemplate et = new ExcelTemplateImpl(datas);   
			Map<String, Workbook> workMap = et.createExcel();   
			Workbook wb = workMap.get("2003"); //获取excel2003   
			wb.write(os);   

			os.flush();   
			os.close();

下载