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

xiaomaha

浏览: 20637 次
性别:
来自: 成都

最近访客更多访客>>

liuchun009

csdncb

xuyongff

l258775875

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

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

博客分类：

java

poi java excel2007 excel2003 页面下载excel

必须依赖poi包

结果:

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();

下载