Excel导出到文件工具

经常遇到需要将数据库的数据导入到表中的需求.封装以下代码.只为实现基本功能,并没有做到完善.

package com.jiechengkeji.manager.util;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Collection;
import java.util.UUID;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
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 abstract class ExcelBuilder<T> {

	
	protected CellStyle titleStyle;
	
	protected CellStyle contentStyle; 
	
	private int pointer =1;
	
	private Workbook workbook ;
	
	public ExcelBuilder(){
		workbook= new XSSFWorkbook();
		workbook.createSheet();
		initStyle();
	
	}
	
	public ExcelBuilder<T> name(String sheetName){
		
		workbook.setSheetName(0, sheetName);
		
		return this;
		
	}
	
	public ExcelBuilder<T> title(String[] title){
		Sheet sheet = workbook.getSheetAt(0);
		Row row =sheet.createRow(0);
		for(int i =0;i<title.length;i++){
			Cell c = row.createCell(i,Cell.CELL_TYPE_STRING);
			c.setCellValue(title[i]); 
			c.setCellStyle(titleStyle);
			
		}

		return this;
	}
	
	
	
	abstract protected String[] mapEntity(T t);
	
	public ExcelBuilder<T> append(Collection<T> content){
		
		Sheet sheet = workbook.getSheetAt(0);
		
		for(T t: content){
			Row row =sheet.createRow(pointer++);
			String[] strs = this.mapEntity(t);
			for(int i =0;i<strs.length;i++){
				Cell c = row.createCell(i,Cell.CELL_TYPE_STRING);
				c.setCellValue(strs[i]);
			}
			row.setRowStyle(contentStyle);
			
		}
		return this;
	}
	
	
	
	
	
	
	public File build(){
		File file = new File(UUID.randomUUID().toString());
		FileOutputStream fos =  null;
		try {
			file.createNewFile();
			fos = new FileOutputStream(file);
			workbook.write(fos);
		} catch (FileNotFoundException e) {
			
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			try {
				fos.flush();
				fos.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		
		return file;
	}

	protected void initStyle() {
		Font font = workbook.createFont();
		
		
		font.setFontName("宋体");
        font.setFontHeightInPoints((short) 14);// 设置字体大小
		
		titleStyle=workbook.createCellStyle();
		titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 
		titleStyle.setFillForegroundColor(IndexedColors.GREEN.index);

		//titleStyle.setFont(font);
		
		contentStyle=workbook.createCellStyle();
		//contentStyle.setFont(font);
		
		
	}
}

使用演示:

public class ExcelBuilderTest extends BaseTest {

	@Test
	public void test() throws IOException {
		
		List<User> users = ctx.getBean(IUserDao.class).getByPeriod(0, 100, DateConstants.ABSOLUTE_START,
				DateConstants.ABSOLUTE_END);
		
		ExcelBuilder<User> ueb = new ExcelBuilder<User>() {

			@Override
			protected String[] mapEntity(User user) {

				return new String[] { Integer.toString(user.getId()), user.getUsername(), user.getCreateTime().toString() };
			}

		};

		

		File excel = ueb.name("用户清单").title(new String[] { "ID", "姓名", "创建时间" })
				.append(users)
				.append(users)//可多次填充内容
				.build();

	
	}

}