[joy]POI导出Excel简略扩展(一)ITeye - 牛牛娱乐

[joy]POI导出Excel简略扩展(一)ITeye

2019-01-12 06:53:17 | 作者: 绍晖 | 标签: 导出,数据,生成 | 浏览: 1785

 



 

 整个程序的履行进程:



 

详细代码:

/**
 * 该类只供给生成workbook、填充数据到workbook 
 * 子类可对支撑的数据格局进行拓宽 子类可在生成前后加逻辑进行拓宽
 * 支撑的数据格局: List Map 、List DynaBean 、List List 
 * 其他数据格局可自己扩展 Collention ? + ?[]
 * (Collention各种+数组各种) SimpleExportGenerator -- 简略导出,支撑常见的格局
 * ComplexExportGenerator(implements simple) -- 杂乱导出,支撑Collention ? 
 * 生成workbook履行次序(子类可重写) 
 * 1、postProcessBeforeGenerate() 
 * 2、generate()
 * 3、postProcessAfterGenerate()
public class ExportGenerator {
 private List Map dataList; //所要导出的数据
 private HSSFWorkbook workBook; //workBook实体类
 private ExportConfig exportConfig; //导出装备信息
 public ExportGenerator() {
 this(new ArrayList Map 
 public ExportGenerator(List Map dataList) {
 this(dataList==null?new ArrayList Map ():dataList, new ExportConfig());
 public ExportGenerator(List Map dataList, ExportConfig exportConfig) {
 this.dataList = dataList==null?new ArrayList Map ():dataList;
 this.exportConfig = exportConfig==null?new ExportConfig():exportConfig;
 public ExportConfig getExportConfig() {
 return this.exportConfig;
 public void setExportConfig(ExportConfig exportConfig) {
 this.exportConfig = exportConfig;
 public List Map getDataList() {
 return this.dataList;
 public void setDataList(List Map dataList) {
 this.dataList = dataList;
 public HSSFWorkbook getWorkBook() {
 return workBook;
 public void setWorkBook(HSSFWorkbook workBook) {
 this.workBook = workBook;
 * 获取 HSSFWorkbook 的源头 子类能够进行拓宽
 protected HSSFWorkbook initWorkbook() {
 return new HSSFWorkbook();
 * 生成excel模板办法 首要确认办法履行次序
 public void generateWorkBook() {
 workBook = initWorkbook();
 postProcessBeforeGenerate(dataList, workBook);
 generate(dataList, workBook);
 postProcessAfterGenerate(dataList, workBook);
 * 填充数据前调用该办法
 public void postProcessBeforeGenerate(List Map dataList,HSSFWorkbook workBook) {};
 * 填充完数据后调用该办法
 public void postProcessAfterGenerate(List Map dataList,HSSFWorkbook workBook) {};
 * 获取sheet
 protected HSSFSheet getOrCreateSheet(int index,HSSFWorkbook workBook){
 if(workBook.getNumberOfSheets() 0){
 return workBook.getSheetAt(0);
 return workBook.createSheet();
 * 详细生成workBook办法
 protected void generate(List Map dataList, HSSFWorkbook workBook) {
 HSSFSheet sheet = getOrCreateSheet(0,workBook);
 int rowIndex = exportConfig.getRowStartIndex(), colIndex = exportConfig.getColStartIndex();
 for (Map m : dataList) {
 HSSFRow row = sheet.createRow(rowIndex);
 for (Object k : m.keySet()) {
 HSSFCell cell = row.createCell(colIndex);
 Object value = processCellValue(m.get(k), row, cell);
 setValue(cell, value);
 cell.setCellStyle(processCellStyle(value, row, cell));
 colIndex++;
 colIndex = 0;
 rowIndex++;
 * 对poi中的cell进行赋值
 protected void setValue(HSSFCell cell, Object value) {
 if (null == value) {
 cell.setCellValue((java.lang.String) value);//假如值为null的话,poi自己会处理
 } else if (value instanceof java.lang.String) {
 cell.setCellValue((java.lang.String) value);
 } else if (value instanceof java.lang.Double) {
 cell.setCellValue((double) value);
 } else if (value instanceof java.lang.Float) {
 cell.setCellValue((float) value);
 } else if (value instanceof java.lang.Long) {
 cell.setCellValue((long) value);
 } else if (value instanceof java.lang.Integer) {
 cell.setCellValue((int) value);
 } else if (value instanceof java.lang.Short) {
 cell.setCellValue((short) value);
 } else if (value instanceof java.lang.Boolean) {
 cell.setCellValue((boolean) value);
 } else if (value instanceof java.util.Date) {
 cell.setCellValue((java.util.Date) value);
 } else if (value instanceof java.util.Calendar) {
 cell.setCellValue((java.util.Calendar) value);
 } else if (value instanceof org.apache.poi.ss.usermodel.RichTextString) {
 cell.setCellValue((org.apache.poi.ss.usermodel.RichTextString) value);
 } else {
 cell.setCellValue(value.toString());// 无匹配类型按String处理
 * 依据Processor获取值
 protected Object processCellValue(Object obj, HSSFRow row, HSSFCell cell) {
 CellValueProcessor cvp = exportConfig.getCellValueProcessor();
 if (null == cvp) {
 return obj;
 return cvp.processCellValue(obj, row, cell);
 * 依据Processor获取值 返回空不影响已有款式
 protected CellStyle processCellStyle(Object value, HSSFRow row,HSSFCell cell) {
 CellValueProcessor cvp = exportConfig.getCellValueProcessor();
 if (null == cvp) {
 return null;
 return cvp.processCellStyle(value, row, cell);
 public HSSFWorkbook getWorkBookInstance() {
 return getWorkBookInstance(false);
 * 向外部供给获取 HSSFWorkbook 的办法 refresh 是否从头生成HSSFWorkbook
 public HSSFWorkbook getWorkBookInstance(boolean refresh) {
 if (refresh || null == workBook) {
 generateWorkBook();
 return workBook;
}

 

/**
 * 导出装备,便利传递各种参数
public class ExportConfig {
 * 填充数据的开端行坐标
 private int rowStartIndex=0;
 * 填充数据的开端列坐标
 private int colStartIndex=0;
 * 填充单元格时的值处理器
 CellValueProcessor cellValueProcessor;
 public CellValueProcessor getCellValueProcessor(){
 return cellValueProcessor;
 public void setCellValueProcessor(CellValueProcessor cellValueProcessor){
 this.cellValueProcessor = cellValueProcessor;
 public int getRowStartIndex() {
 return rowStartIndex;
 public void setRowStartIndex(int rowStartIndex) {
 this.rowStartIndex = rowStartIndex;
 public int getColStartIndex() {
 return colStartIndex;
 public void setColStartIndex(int colStartIndex) {
 this.colStartIndex = colStartIndex;
}

 

/**
 * 值产生器接口
public interface CellValueProcessor {
 Object processCellValue(Object value,HSSFRow row,HSSFCell cell);
 CellStyle processCellStyle(Object value,HSSFRow row,HSSFCell cell);
}

 

简略扩展:

public class YearReportExportGenerator extends ExportGenerator{
 public YearReportExportGenerator(List Map dataList) {
 super(dataList);
 public YearReportExportGenerator(List Map dataList, ExportConfig exportConfig) {
 super(dataList, exportConfig);
 * 增加表头数据
 @Override
 public void postProcessBeforeGenerate(List Map dataList,HSSFWorkbook workBook) {
 createHead();
 @Override
 public void postProcessAfterGenerate(List Map dataList,HSSFWorkbook workBook) {
 createBottom();
 createBorder();
 * 生成一个简易的表头
 public void createHead(){
 HSSFSheet sheet = getWorkBook().createSheet();
 HSSFRow row = sheet.createRow(0);
 HSSFCell cell = row.createCell(0);
 row.setHeight((short)500);
 cell.setCellValue("2015年XXX全年报表");
 for(int i=1;i i++){
 row.createCell(i);
 sheet.addMergedRegion(new CellRangeAddress(0,0,0,12));
 HSSFCellStyle style = getWorkBook().createCellStyle();
 HSSFFont font = getWorkBook().createFont();
 font.setBold(true);
 font.setFontHeightInPoints((short)16);
 style.setFont(font);
 style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
 cell.setCellStyle(style);
 HSSFRow row2 = sheet.createRow(1);
 HSSFCellStyle centerStyle = getWorkBook().createCellStyle();
 centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
 for(int i=0;i i++){
 HSSFCell c = row2.createCell(i);
 if(i 0){
 c.setCellValue(i+"月");
 c.setCellStyle(centerStyle);
 ExportConfig cfg = getExportConfig();
 cfg.setRowStartIndex(cfg.getRowStartIndex()+2);
 * 增加边框
 public void createBorder(){
 HSSFSheet sheet = getWorkBook().getSheetAt(0);
 int rows = sheet.getLastRowNum();
 HSSFCellStyle style;
 for(int i=0;i rows+1;i++){
 HSSFRow row = sheet.getRow(i);
 Iterator Cell iter = row.cellIterator();
 while(iter.hasNext()){
 Cell c = iter.next();
 style = row.getSheet().getWorkbook().createCellStyle();
 style.cloneStyleFrom(c.getCellStyle());
 style.setBorderTop((short)1);
 style.setBorderBottom((short)1);
 style.setBorderLeft((short)1);
 style.setBorderRight((short)1);
 c.setCellStyle(style);
 * 创立底部信息
 public void createBottom(){
 HSSFSheet sheet = getWorkBook().getSheetAt(0);
 int rows = sheet.getLastRowNum();
 HSSFRow row = sheet.createRow(rows+1);
 for(int i=1;i i++){
 row.createCell(i);
 Cell c = row.createCell(0);
 c.setCellValue("陈述人:多啦a芊 陈述日期:2015-12-17");
 HSSFCellStyle style = row.getSheet().getWorkbook().createCellStyle();
 style.cloneStyleFrom(c.getCellStyle());
 style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
 c.setCellStyle(style);
 sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(),row.getRowNum(),0,12));
}

 

以下为调用端代码:

public class Client {
 public static void main(String[] args) throws IOException {
 test01();
 test02();
 test03();
 * 只导出数据Excel
 public static void test01() throws IOException{
 List Map l = getList();
 ExportGenerator generator = new ExportGenerator(l);
 HSSFWorkbook workBook = generator.getWorkBookInstance();
 FileOutputStream out = new FileOutputStream("D:\\excel1.xls");
 workBook.write(out);
 * 导出带表头、带边框的Excel
 public static void test02() throws IOException{
 List Map l = getList();
 ExportGenerator generator = new YearReportExportGenerator(l);
 HSSFWorkbook workBook = generator.getWorkBookInstance();
 FileOutputStream out = new FileOutputStream("D:\\excel2.xls");
 workBook.write(out);
 * 导出带表头、带边框和行款式的Excel
 public static void test03() throws IOException{
 List Map l = getList();
 CellValueProcessor cvp = new CellValueProcessor(){
 @Override
 public Object processCellValue(Object value, HSSFRow row,HSSFCell cell) {
 return value==null?"-":value;
 @Override
 public CellStyle processCellStyle(Object value, HSSFRow row,HSSFCell cell) {
 HSSFCellStyle style = null;
 if(value instanceof Number){
 int v = (int)value;
 if(v 0){
 style = row.getSheet().getWorkbook().createCellStyle();
 style.cloneStyleFrom(cell.getCellStyle());
 style.setFillForegroundColor(HSSFColor.RED.index);
 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
 }else if(v 100){
 style = row.getSheet().getWorkbook().createCellStyle();
 style.cloneStyleFrom(cell.getCellStyle());
 style.setFillForegroundColor(HSSFColor.BRIGHT_GREEN.index);
 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
 }else{
 style = row.getSheet().getWorkbook().createCellStyle();
 style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
 return style;
 ExportConfig cfg = new ExportConfig();
 cfg.setCellValueProcessor(cvp);
 ExportGenerator generator = new YearReportExportGenerator(l,cfg);
 HSSFWorkbook workBook = generator.getWorkBookInstance();
 FileOutputStream out = new FileOutputStream("D:\\excel3.xls");
 workBook.write(out);
 * 获取假数据
 public static List Map getList(){
 List Map l = new ArrayList Map 
 Random r = new Random();
 Map m;
 for(int i=0;i i++){
 m = new LinkedMap();
 m.put("name", "目标"+i);
 for(int j=0;j j++){
 if(r.nextInt(20) 1){
 m.put(j, null);
 }else{
 m.put(j, r.nextInt(120)-10); 
 l.add(m);
 return l;
}

 

test01()生成成果:


 

test02()生成成果:


 

test03()生成成果:


 

 

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表牛牛娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1
  • 2

    第02章 根底中心ITeye

    目标,根底,中心
  • 3
  • 4
  • 5

    Java中字符串的使用ITeye

    字符串,字符,比较
  • 6
  • 7

    UTLITeye

    数据,目录,文件
  • 8
  • 9
  • 10

    java与函数式编程ITeye

    函数,编程,一些