Excel导出到文件工具

来源：互联网发布：口红保质期知乎编辑：程序博客网时间：2024/05/16 09:19

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

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 {@Testpublic 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>() {@Overrideprotected 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();}}

1 0