java表头_【每日一点】1. Java如何实现导出Excel单表头或多表头

@Getter

@Setterpublic class ExcelHelper{/*** 表格标题*/

privateString title;/*** 单元格宽度*/

private int colWidth = 20;/*** 行高度*/

private int rowHeight = 20;privateHSSFWorkbook workbook;/*** 表头样式*/

privateHSSFCellStyle headStyle;/*** 主体样式*/

privateHSSFCellStyle bodyStyle;/*** 日期格式化,默认yyyy-MM-dd HH:mm:ss*/

private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");/*** Constructor

*@paramtitle*/

publicExcelHelper(String title){this.title =title;

workbook= newHSSFWorkbook();

init();

}/*** Constructor

*@paramtitle

*@paramcolWidth

*@paramrowHeight*/

public ExcelHelper(String title, int colWidth, introwHeight){this.colWidth =colWidth;this.rowHeight =rowHeight;this.title =title;

workbook= newHSSFWorkbook();

init();

}/*** Constructor

*@paramtitle

*@paramcolWidth

*@paramrowHeight

*@paramdateFormat*/

public ExcelHelper(String title, int colWidth, introwHeight, String dateFormat) {this.title =title;this.colWidth =colWidth;this.rowHeight =rowHeight;

workbook= newHSSFWorkbook();

sdf= newSimpleDateFormat(dateFormat);

init();

}/*** 导出Excel,适用于web导出excel

*

*@paramsheet

*@paramdata*/

private void writeSheet(HSSFSheet sheet, List data,ListheaderList) {try{

sheet.setDefaultColumnWidth(colWidth);

sheet.setDefaultRowHeightInPoints(rowHeight);

createHead(headerList, sheet);

writeSheetContent(headerList, data, sheet);

}catch(Exception e) {throw newRuntimeException(e);

}

}/*** 导出表格

*@paramlistColumn

*@paramdatas

*@return*@throwsException*/

public InputStream exportExcel(List listColumn,List datas) throwsException {

splitDataToSheets(datas,listColumn);returnsave(workbook);

}/*** 导出表格 支持2级表头或单表头的Excel导出

*@paramheaders

*@paramdatas

*@paramfilePath

*@throwsFileNotFoundException

*@throwsIOException

* void*/

public void exportExcel(List headers,List datas,String filePath) throwsIOException {

splitDataToSheets(datas, headers);

save(workbook, filePath);

}/*** 把数据写入到单元格

*@paramlistColumn

*@paramdatas

*@paramsheet

*@throwsException

* void*/

private void writeSheetContent(List listColumn,List datas,HSSFSheet sheet) throwsException {

HSSFRow row;

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

}

}

}/*** 创建表头

*@paramlistColumn 表头数组

*@return返回表头总行数*/

public void createHead(ListlistColumn, HSSFSheet sheetCo){

HSSFRow row= sheetCo.createRow(0);

HSSFRow row2= sheetCo.createRow(1);for(short i = 0, n = 0; i < listColumn.size(); i++){

HSSFCell cell1=row.createCell(n);

cell1.setCellStyle(headStyle);

HSSFRichTextString text;

List columns =listColumn.get(i).getListColumn();//双标题

if(CollectionUtils.isEmpty(columns)){//单标题

HSSFCell cell2 =row2.createCell(n);

cell2.setCellStyle(headStyle);

text= newHSSFRichTextString(listColumn.get(i).getContent());

sheetCo.addMergedRegion(new CellRangeAddress(0, n, 1, n));

n++;

cell1.setCellValue(text);continue;

}

text= newHSSFRichTextString(listColumn.get(i).getContent());

cell1.setCellValue(text);//创建第一行大标题

sheetCo.addMergedRegion(new CellRangeAddress(0, 0, n, (short) (n + columns.size() -1)));//给标题赋值

for(int j = 0; j < columns.size(); j++){

HSSFCell cell2= row2.createCell(n++);

cell2.setCellStyle(headStyle);

cell2.setCellValue(newHSSFRichTextString(columns.get(j).getContent()));

}

}

}/*** 创建行

*@paramrow

*@paramcolumn

*@paramv

*@paramrowIndex

*@return*@throwsException*/

public int createRowVal(HSSFRow row, Column column,T v,int rowIndex) throwsException{//遍历标题

if(column.getListColumn() != null && column.getListColumn().size() > 0){for(int i = 0; i < column.getListColumn().size(); i++){

createRowVal(row,column.getListColumn().get(i),v,rowIndex);

}

}else{

createCol(row,column,v,rowIndex++);

}returnrowIndex;

}/*** 创建单元格

*@paramrow

*@paramcolumn

*@paramv

*@paramcolumnIndex

*@throwsException*/

public void createCol(HSSFRow row,Column column,T v,int columnIndex) throwsException{//创建单元格

HSSFCell cell =row.createCell(columnIndex);//设置单元格样式

cell.setCellStyle(bodyStyle);

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 instanceofDate){

value=parseDate((Date)value);

}

HSSFRichTextString richString= newHSSFRichTextString(value.toString());

cell.setCellValue(richString);

}

}/*** init*/

private voidinit(){//生成表头样式

headStyle =workbook.createCellStyle();

headStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.AQUA.getIndex());

headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

headStyle.setBorderBottom(BorderStyle.THIN);

headStyle.setBorderLeft(BorderStyle.THIN);

headStyle.setBorderRight(BorderStyle.THIN);

headStyle.setBorderTop(BorderStyle.THIN);

headStyle.setAlignment(HorizontalAlignment.CENTER);

headStyle.setVerticalAlignment(VerticalAlignment.CENTER);//生成一个字体

HSSFFont headFont =workbook.createFont();

headFont.setColor(HSSFColor.HSSFColorPredefined.VIOLET.getIndex());

headFont.setFontHeightInPoints((short) 12);

headFont.setBold(true);//把字体应用到当前的样式

headStyle.setFont(headFont);//生成并设置另一个样式

bodyStyle =workbook.createCellStyle();

bodyStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());

bodyStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

bodyStyle.setBorderBottom(BorderStyle.THIN);

bodyStyle.setBorderLeft(BorderStyle.THIN);

bodyStyle.setBorderRight(BorderStyle.THIN);

bodyStyle.setBorderTop(BorderStyle.THIN);

bodyStyle.setAlignment(HorizontalAlignment.CENTER);

bodyStyle.setVerticalAlignment(VerticalAlignment.CENTER);//生成另一个字体

HSSFFont bodyFont =workbook.createFont();

bodyFont.setBold(false);//把字体应用到当前的样式

bodyStyle.setFont(bodyFont);

}/*** 时间转换

*@paramdate

*@return* String*/

privateString parseDate(Date date){

String dateStr= "";try{

dateStr=sdf.format(date);

}catch(Exception e){

e.printStackTrace();

}returndateStr;

}/*** 拆分sheet，因为每个sheet不能超过6526，否则会报异常

*@paramdata

*@paramlistColumn

* void*/

private void splitDataToSheets(List data,ListlistColumn) {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 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);

}/*** 把column的columnList整理成一个list

*@paramlistColumn

*@return* List*/

private List getColumnList(ListlistColumn){

List listCol = new ArrayList<>();for(int i = 0; i < listColumn.size(); i++){

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

}

}

}returnlistCol;

}/*** 保存Excel到InputStream，此方法适合web导出excel

*

*@paramworkbook

*@return

*/

privateInputStream save(HSSFWorkbook workbook) {

ByteArrayOutputStream bos= newByteArrayOutputStream();try{

workbook.write(bos);

InputStream bis= newByteArrayInputStream(bos.toByteArray());returnbis;

}catch(Exception e) {

e.printStackTrace();throw newRuntimeException(e);

}

}/*** 保存文件

*@paramworkbook

*@paramfilePath

*@throwsIOException*/

private void save(HSSFWorkbook workbook,String filePath) throwsIOException {

workbook.write(newFileOutputStream(filePath));

}