java poi 生成excel双表头

使用poi生成双表头，写的不是很好，还有很多可以改进的地方，希望大家谅解，此方法采用了java的反射机制来排除一些不需要导出的字段，如果一张表有一百多个字段的话写的有点累人，也容易出现问题，但是用用还是可以的，导出的效率也还有可以改进的地方

1,：TableExcel类

import java.io.ByteArrayInputStream;import java.io.ByteArrayOutputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.lang.reflect.Field;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFRichTextString;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.hssf.util.Region;public class TableExcel<T> {//表格标题private String title;//单元格宽度private int colWidth = 20;//行高度private int rowHeight = 20;private HSSFWorkbook workbook;//表头样式private HSSFCellStyle styleHead;//主体样式private HSSFCellStyle styleBody;//日期格式化,默认yyyy-MM-dd HH:mm:ssprivate SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");public TableExcel(String title,int colWidth,int rowHeight,String dateFormat){this.colWidth = colWidth;this.rowHeight = rowHeight;this.title = title;workbook = new HSSFWorkbook(); sdf = new SimpleDateFormat(dateFormat);        init();}public TableExcel(String title,int colWidth,int rowHeight){this.colWidth = colWidth;this.rowHeight = rowHeight;this.title = title;workbook = new HSSFWorkbook();         init();}public TableExcel(String title){this.title = title;workbook = new HSSFWorkbook();         init();}private void init(){// 生成一个样式  styleHead = workbook.createCellStyle();          // 设置这些样式  //styleHead.setFillForegroundColor(HSSFColor.SKY_BLUE.index); styleHead.setFillForegroundColor(HSSFColor.AQUA.index); styleHead.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  styleHead.setBorderBottom(HSSFCellStyle.BORDER_THIN);  styleHead.setBorderLeft(HSSFCellStyle.BORDER_THIN);  styleHead.setBorderRight(HSSFCellStyle.BORDER_THIN);  styleHead.setBorderTop(HSSFCellStyle.BORDER_THIN);  styleHead.setAlignment(HSSFCellStyle.ALIGN_CENTER);          // 生成一个字体          HSSFFont font = workbook.createFont();          font.setColor(HSSFColor.VIOLET.index);          font.setFontHeightInPoints((short) 12);          font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);          // 把字体应用到当前的样式          styleHead.setFont(font);          // 生成并设置另一个样式          styleBody = workbook.createCellStyle();          styleBody.setFillForegroundColor(HSSFColor.WHITE.index);          styleBody.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);          styleBody.setBorderBottom(HSSFCellStyle.BORDER_THIN);          styleBody.setBorderLeft(HSSFCellStyle.BORDER_THIN);          styleBody.setBorderRight(HSSFCellStyle.BORDER_THIN);          styleBody.setBorderTop(HSSFCellStyle.BORDER_THIN);          styleBody.setAlignment(HSSFCellStyle.ALIGN_CENTER);          styleBody.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);          // 生成另一个字体          HSSFFont font2 = workbook.createFont();          font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);          // 把字体应用到当前的样式          styleBody.setFont(font2); }/** * 拆分sheet，因为每个sheet不能超过6526，否则会报异常 * @param data * @param listColumn * void */private void splitDataToSheets(List<T> data,List<Column> listColumn){int dataCount = data.size();int maxColumn = 65535;int pieces = dataCount/maxColumn;for(int i = 1; i <= pieces;i++){HSSFSheet sheet = workbook.createSheet(this.title+i);List<T> subList = data.subList((i-1)*maxColumn, i*maxColumn);writeSheet(sheet,subList,listColumn);}HSSFSheet sheet = workbook.createSheet(this.title+(pieces+1));writeSheet(sheet, data.subList(pieces*maxColumn, dataCount),listColumn);}/** * 导出Excel,适用于web导出excel *  * @param sheet * @param data */private void writeSheet(HSSFSheet sheet, List<T> data,List<Column> listColumn) {try {sheet.setDefaultColumnWidth(colWidth);sheet.setDefaultRowHeightInPoints(rowHeight);createHead(listColumn,sheet);writeSheetContent(listColumn,data,sheet);} catch (Exception e) {throw new RuntimeException(e);}}/** * 导出表格 * @param listColumn * @param datas * @return * @throws Exception  */public InputStream exportExcel(List<Column> listColumn,List<T> datas) throws Exception {splitDataToSheets(datas,listColumn);return save(workbook);}/** * 导出表格 * @param listColumn * @param datas * @param FilePath * @throws FileNotFoundException * @throws IOException * void */public void exportExcel(List<Column> listColumn,List<T> datas,String FilePath) throws FileNotFoundException, IOException{splitDataToSheets(datas,listColumn);save(workbook,FilePath);}/** * 把数据写入到单元格 * @param listColumn * @param datas * @param sheet * @throws Exception * void */private void writeSheetContent(List<Column> listColumn,List<T> datas,HSSFSheet sheet) throws Exception{HSSFRow row = null;List<Column> listCol = getColumnList(listColumn);for(int i = 0 , index = 2; i < datas.size(); i++ , index++){        row = sheet.createRow(index);//创建行                for(int j = 0; j < listCol.size(); j++){        Column c = listCol.get(j);        createCol(row,c,datas.get(i),j);         }        }}/** * 把column的columnList整理成一个list<column> * @param listColumn * @return * List<Column> */private List<Column> getColumnList(List<Column> listColumn){List<Column> listCol = new ArrayList<Column>();for(int i = 0; i < listColumn.size(); i++){List<Column> list = listColumn.get(i).getListColumn();if(list.size() > 0){for(Column c : list){if(c.getFieldName() != null){listCol.add(c);}}}else{if(listColumn.get(i).getFieldName() != null){listCol.add(listColumn.get(i));}}}return listCol;}/** * 保存Excel到InputStream，此方法适合web导出excel *  * @param workbook * @return */private InputStream save(HSSFWorkbook workbook) {ByteArrayOutputStream bos = new ByteArrayOutputStream();try {workbook.write(bos);InputStream bis = new ByteArrayInputStream(bos.toByteArray());return bis;} catch (Exception e) {e.printStackTrace();throw new RuntimeException(e);}}private void save(HSSFWorkbook workbook,String filePath) throws FileNotFoundException, IOException{workbook.write(new FileOutputStream(filePath));}/** * 创建行 * @param row * @param column * @param v * @param j * @return * @throws Exception */    public int createRowVal(HSSFRow row,Column column,T v,int j) throws Exception{    //便利标题    if(column.getListColumn() != null && column.getListColumn().size() > 0){    for(int i = 0; i < column.getListColumn().size(); i++){    createRowVal(row,column.getListColumn().get(i),v,j);    }    }else{    createCol(row,column,v,j++);        }    return j;        }        /**     * 创建单元格     * @param row     * @param column     * @param v     * @param j     * @throws Exception     */    public void createCol(HSSFRow row,Column column,T v,int j) throws Exception{    HSSFCell cell = row.createCell(j);  //创建单元格cell.setCellStyle(styleBody); //设置单元格样式Class cls = v.getClass();Field field = cls.getDeclaredField(column.getFieldName());field.setAccessible(true); //设置些属性是可以访问的 if(field.get(v) != null){Object value = field.get(v);if(value instanceof Date){value = parseDate((Date)value);}HSSFRichTextString richString = new HSSFRichTextString(value.toString());    cell.setCellValue(richString);}    }            /**     * 时间转换     * @param date     * @return     * String     */    private  String parseDate(Date date){String dateStr = "";try{dateStr = sdf.format(date);} catch (Exception e){e.printStackTrace();}return dateStr;}            /**     * 创建表头     * @param listColumn     */    public void createHead(List<Column> listColumn,HSSFSheet sheetCo){     HSSFRow row = sheetCo.createRow(0);           HSSFRow row2 = sheetCo.createRow(1);                  for(short i = 0, n = 0; i < listColumn.size(); i++){//i是headers的索引，n是Excel的索引           HSSFCell cell1 = row.createCell(n);          cell1.setCellStyle(styleHead); //设置表头样式          HSSFRichTextString text = null;           List<Column> columns = listColumn.get(i).getListColumn();         if(columns != null && columns.size() > 0){//双标题         text = new HSSFRichTextString(listColumn.get(i).getContent());          sheetCo.addMergedRegion(new Region(0, n, 0, (short) (n + columns.size() -1)));//创建第一行大标题                  short tempI = n;          for(int j = 0; j < columns.size(); j++){//添加标题样式         HSSFCell cellT = row.createCell(tempI++);                       cellT.setCellStyle(styleHead);          }         for(int j = 0; j < columns.size(); j++){  //给标题复制             HSSFCell cell2 = row2.createCell(n++);                                         cell2.setCellStyle(styleHead);                      cell2.setCellValue(new HSSFRichTextString(columns.get(j).getContent()));                  }         }else{//单标题         //sheetCo.setColumnWidth(i, columns.get(i).getContent().getBytes().length*2*256);          HSSFCell cell2 = row2.createCell(n);           cell2.setCellStyle(styleHead);           text = new HSSFRichTextString(listColumn.get(i).getContent());           sheetCo.addMergedRegion(new Region(0, n, 1, n));           n++;         }         cell1.setCellValue(text);         }            }public int getColWidth() {return colWidth;}public void setColWidth(int colWidth) {this.colWidth = colWidth;}public int getRowHeight() {return rowHeight;}public void setRowHeight(int rowHeight) {this.rowHeight = rowHeight;}public HSSFWorkbook getWorkbook() {return workbook;}public void setWorkbook(HSSFWorkbook workbook) {this.workbook = workbook;}public String getTitle() {return title;}public void setTitle(String title) {this.title = title;}public HSSFCellStyle getStyleHead() {return styleHead;}public void setStyleHead(HSSFCellStyle styleHead) {this.styleHead = styleHead;}public HSSFCellStyle getStyleBody() {return styleBody;}public void setStyleBody(HSSFCellStyle styleBody) {this.styleBody = styleBody;}}

2：单元格类Column
import java.util.ArrayList;import java.util.List;public class Column {//单元格内容private String content;//字段名称，用户导出表格时反射调用private String fieldName;//这个单元格的集合private List<Column> listColumn = new ArrayList<Column>();public Column(String content,String fieldName){this.content = content;this.fieldName = fieldName;}public String getContent() {return content;}public void setContent(String content) {this.content = content;}public String getFieldName() {return fieldName;}public void setFieldName(String fieldName) {this.fieldName = fieldName;}public List<Column> getListColumn() {return listColumn;}public void setListColumn(List<Column> listColumn) {this.listColumn = listColumn;}}
/**     * 测试方法     * @param args     * @throws Exception     * void     */    public static void main(String[] args) throws Exception {                  List<Column> listColumn = new ArrayList<Column>();//用于存放第一行单元格                List<Column> list2 = new ArrayList<Column>();//用于存放第一列第二行的单元格        list2.add(new Column("标题1","value1"));//创建一列，value1 表示这一列需要导出字段的值        list2.add(new Column("标题2","value1"));        list2.add(new Column("标题3","value1"));        list2.add(new Column("标题4","value1"));        list2.add(new Column("标题5","value1"));                List<Column> list3  = new ArrayList<Column>();//用于存放第二列第二行的单元格        list3.add(new Column("标题6","value2"));        list3.add(new Column("标题7","value2"));        list3.add(new Column("标题8","value2"));        list3.add(new Column("标题9","value2"));                Column c1 = new Column("标题1",null);//创建第一行大标题,大标题的fieldName 为 null        c1.setListColumn(list2);//所属c1的单元格都赋值给c1        Column c2 = new Column("标题2",null);        c2.setListColumn(list3);        listColumn.add(c1);        listColumn.add(c2);                        List<ValueObj> valueList = new ArrayList<ValueObj>();//需要导出的数据        valueList.add(new ValueObj("1","11"));        valueList.add(new ValueObj("2","22"));        valueList.add(new ValueObj("3","33"));        valueList.add(new ValueObj("4","44"));        valueList.add(new ValueObj("5","55"));        valueList.add(new ValueObj("6","66"));        valueList.add(new ValueObj("7","77"));                TableExcel<ValueObj> ta = new TableExcel<ValueObj>("表格",15,20);        ta.exportExcel(listColumn, valueList,"D://outExcel.xls");    }  

导出的结果：


                                             
        0        0           
	
				
	
					   
java poi 生成excel双表头
	  	   
poi 双表头excel制作
	  	   
java poi生成excel
	  	   
java 实现生成excel表头
	  	   
利用poi导出excel,并自动生成表头
	  	   
poi 实现 java生成excel
	  	   
java用poi生成excel
	  	   
java poi 生成excel模板
	  	   
生成Excel树形表头
	  	   
Java 生成 EXCEL POI文档说明
	  	   
Java 生成 EXCEL POI文档说明
	  	   
Java利用POI生成Excel强制换行
	  	   
JAVA POI excel文档解析与生成
	  	   
java利用poi生成excel报表
	  	   
Java poi 在 Excel中生成统计图
	  	   
Java 使用 poi生成 Excel的方法
	  	   
Java利用POI生成Excel强制换行
	  	   
Java对poi操作生成Excel表
	     		  
	  	   
对于GNU编译器中“-mthumb-interwork”和“-mthumb”的理解
	  	   
OpenWrt笔记---lua环境搭建
	  	   
linux挂载新硬盘
	  	   
iOS 打包后 Export 导出4 个选项的含义
	  	   
Mac 下使用Xcode 配置Lua 环境并使用
	  	   
java poi 生成excel双表头
	  	   
欢迎使用CSDN-markdown编辑器
	  	   
spring aop拦截Controller做参数校验
	  	   
创建一个WiFi热点
	  	   
2016yiqu
	  	   
Android引用Maven库AAR
	  	   
c/c++程序内存分配
	  	   
linux中的22个特殊字符
	  	   
RCNN算法详解