怎么用java把类的变量导出表头_JAVA导出特殊表头数据-POI实现

本文解决要导出的表格表头有合并行格和列格的场景，工具类方法兼容上一篇<>

导出表格需求如下：

用于处理表头的列

public class Column {

//名字

private String name;

//起始行

private int startRow;

//终止行

private int endRow;

//起始列

private int startCol;

//终止列

private int endCol;

public Column(){

}

public Column(String name,int startRow,int endRow,int startCol,int endCol){

this.name = name;

this.startRow = startRow;

this.endRow = endRow;

this.startCol = startCol;

this.endCol = endCol;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public int getStartRow() {

return startRow;

}

public void setStartRow(int startRow) {

this.startRow = startRow;

}

public int getEndRow() {

return endRow;

}

public void setEndRow(int endRow) {

this.endRow = endRow;

}

public int getStartCol() {

return startCol;

}

public void setStartCol(int startCol) {

this.startCol = startCol;

}

public int getEndCol() {

return endCol;

}

public void setEndCol(int endCol) {

this.endCol = endCol;

}

}

service 服务类

import java.util.Collection;

import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

import com.cjhx.risk.backstage.common.dataexport.domain.Column;

import com.cjhx.risk.backstage.common.dataexport.service.ExcelExportService;

import com.cjhx.risk.utils.ExcelUtils;

@Service

public class ExcelExportServiceImpl implements ExcelExportService {

@Autowired

HttpServletResponse response;

public void specialExcelData(String title, Column[][] headers, String[] fields, Collection dataset)

throws Exception {

ExcelUtils.exportSpecialExcelData(title, headers, fields, dataset,response);

response.setCharacterEncoding("UTF-8");

response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=UTF-8");

}

}

导出工具类

import java.lang.reflect.Method;

import java.text.SimpleDateFormat;

import java.util.Collection;

import java.util.Date;

import java.util.Iterator;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.BorderStyle;

import org.apache.poi.ss.usermodel.Font;

import org.apache.poi.ss.usermodel.HorizontalAlignment;

import org.apache.poi.ss.usermodel.VerticalAlignment;

import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFCellStyle;

import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.cjhx.risk.backstage.common.dataexport.domain.Column;

public class ExcelUtils {

private static String getName = "get";

private static String dataFormat = "yyyy-MM-dd";

public static void exportSpecialExcelData(String title,Column[][] headers,String[] fields,Collection dataset,HttpServletResponse response)

throws Exception {

XSSFWorkbook workBook = null;

try{

workBook = new XSSFWorkbook();

XSSFSheet sheet = workBook.createSheet(title);

sheet.setDefaultColumnWidth((short) 15);

//表头的行数和列数

int rowSize = headers.length;

int colSize = fields.length;

//表头单元格样式

XSSFCellStyle headCellStyle = workBook.createCellStyle();

Font fontStyle = workBook.createFont();

fontStyle.setBold(true);

fontStyle.setFontName("黑体");

fontStyle.setFontHeightInPoints((short) 11);

headCellStyle.setFont(fontStyle);

//设置边框和垂直水平居中

headCellStyle.setBorderBottom(BorderStyle.THIN);

headCellStyle.setBorderLeft(BorderStyle.THIN);

headCellStyle.setBorderRight(BorderStyle.THIN);

headCellStyle.setBorderTop(BorderStyle.THIN);

headCellStyle.setAlignment(HorizontalAlignment.CENTER);

headCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

//构建表头

XSSFRow headRow;

for (int i = 0; i < rowSize; i++){

headRow = sheet.createRow(i);

XSSFCell headCell;

for (int j = 0; j < colSize; j++){

headCell = headRow.createCell(j);

XSSFRichTextString text = new XSSFRichTextString(headers[i][j].getName());

headCell.setCellValue(text);

//设置表头单元格样式

headCell.setCellStyle(headCellStyle);

}

}

//合并表头

int startRow,endRow,startCol,endCol;

CellRangeAddress cra;

for (int i = 0; i < rowSize; i++){

for (int j = 0; j < colSize; j++){

startRow = headers[i][j].getStartRow();

endRow = headers[i][j].getEndRow();

startCol = headers[i][j].getStartCol();

endCol = headers[i][j].getEndCol();

//如果待合并的行数和列数同时为0,则无需合并

if (startRow == endRow && startCol == endCol){

continue;

}

cra = new CellRangeAddress(startRow, endRow, startCol, endCol);

sheet.addMergedRegion(cra);

}

}

//表格内容单元格样式

XSSFCellStyle bodyCellStyle = workBook.createCellStyle();

//设置垂直和水平居中

bodyCellStyle.setAlignment(HorizontalAlignment.CENTER);

bodyCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

// 构建表体数据

Iterator it = dataset.iterator();

int index = rowSize - 1;

while (it.hasNext()) {

index++;

XSSFRow bodyRow = sheet.createRow(index);

T t = (T) it.next();

XSSFCell bodyCell;

for (int i = 0; i < fields.length; i++) {

bodyCell = bodyRow.createCell(i);

String fieldName = fields[i];

String getMethodName = getName + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);

Class extends Object> tCls = t.getClass();

Method getMethod = tCls.getMethod(getMethodName, new Class[] {});

Object value = getMethod.invoke(t, new Object[] {});

String textValue = null;

if (value instanceof Date) {

Date date = (Date) value;

SimpleDateFormat sdf = new SimpleDateFormat(dataFormat);

textValue = sdf.format(date);

} else if (value != null) {

// 其它数据类型都当作字符串简单处理

textValue = value.toString();

} else {

textValue = " ";

}

bodyCell.setCellValue(textValue);

bodyCell.setCellStyle(bodyCellStyle);

}

}

workBook.write(response.getOutputStream());

} catch(Exception e){

throw e;

} finally {

if (workBook != null) {

workBook.close();

}

}

}

public static void exportExcelData(String title, String[] headers, String[] colums, Collection dataset,HttpServletResponse response)

throws Exception {

Column col;

int colSize = colums.length;

Column[][] newHeaders = new Column[1][colSize];

for (int i = 0; i < colSize; i++){

col = new Column();

col.setName(headers[i]);

col.setStartRow(0);

col.setEndRow(0);

col.setStartCol(i);

col.setEndCol(i);

newHeaders[0][i] = col;

}

exportSpecialExcelData(title,newHeaders,colums,dataset,response);

}

}

服务调用

List list = ......- ;

if (null == list){

list = new ArrayList<>();

}

//导出的表头

Column[][] headers = new Column[2][22];

headers[0][0] = new Column("A00",0,1,0,0);

headers[0][1] = new Column("A01",0,1,1,1);

headers[0][2] = new Column("A02",0,1,2,2);

headers[0][3] = new Column("A03",0,1,3,3);

headers[0][4] = new Column("A04",0,1,4,4);

headers[0][5] = new Column("A05",0,1,5,5);

headers[0][6] = new Column("A06",0,1,6,6);

headers[0][7] = new Column("A07",0,1,7,7);

headers[0][8] = new Column("A08",0,1,8,8);

headers[0][9] = new Column("A09",0,1,9,9);

headers[0][10] = new Column("A010",0,0,10,14);

headers[0][11] = new Column("A010",0,0,11,11);

headers[0][12] = new Column("A010",0,0,12,12);

headers[0][13] = new Column("A010",0,0,13,13);

headers[0][14] = new Column("A010",0,0,14,14);

headers[0][15] = new Column("A015",0,0,15,21);

headers[0][16] = new Column("A015",0,0,16,16);

headers[0][17] = new Column("A015",0,0,17,17);

headers[0][18] = new Column("A015",0,0,18,18);

headers[0][19] = new Column("A015",0,0,19,19);

headers[0][20] = new Column("A015",0,0,20,20);

headers[0][21] = new Column("A015",0,0,21,21);

headers[1][0] = new Column("B10",1,1,0,0);

headers[1][1] = new Column("B11",1,1,1,1);

headers[1][2] = new Column("B12",1,1,2,2);

headers[1][3] = new Column("B13",1,1,3,3);

headers[1][4] = new Column("B14",1,1,4,4);

headers[1][5] = new Column("B15",1,1,5,5);

headers[1][6] = new Column("B16",1,1,6,6);

headers[1][7] = new Column("B17",1,1,7,7);

headers[1][8] = new Column("B18",1,1,8,8);

headers[1][9] = new Column("B19",1,1,9,9);

headers[1][10] = new Column("B110",1,1,10,10);

headers[1][11] = new Column("B111",1,1,11,11);

headers[1][12] = new Column("B112",1,1,12,12);

headers[1][13] = new Column("B113",1,1,13,13);

headers[1][14] = new Column("B114",1,1,14,14);

headers[1][15] = new Column("B115",1,1,15,15);

headers[1][16] = new Column("B116",1,1,16,16);

headers[1][17] = new Column("B117",1,1,17,17);

headers[1][18] = new Column("B118",1,1,18,18);

headers[1][19] = new Column("B119",1,1,19,19);

headers[1][20] = new Column("B120",1,1,20,20);

headers[1][21] = new Column("B121",1,1,21,21);

// 导出的字段

String[] colums = { "fundCode", "fundName", "hedgeEndDate", "hedgePeriod",

"fundNetVal","presentVal","safetyMat","remainPeriod",

"fixRate","breakEvenBalance","equityAssetA","equityAssetB",

"equityAssetC","equityAssetD","equityAssetT","curMVal1",

"curMVal2","curMVal3","curMVal4","curMVal5",

"curMVal6","curMVal7"};

excelExportService.specialExcelData("hedgeFundsList", headers, colums, list);

导出表格样式

页面

//报表数据导出

$scope.exportData = function(){

var loading=dialog.loading("导出中，请等待。。。");

var param = {};

param.fundId = $scope.guaranteed.lFundId;

return $http({

url:"abnormal/monitorHedgeFund/exportdata",

method: "POST",

responseType: 'arraybuffer',

headers: { 'Content-Type': 'application/json' },

data: param

}).success(function (data) {

loading.close();

var time = new Date();

var ymd = $filter("date")(time,"yyyyMMdd");

var filename ='hedgeFunds'+ymd+time.getHours()+time.getMinutes()+time.getSeconds()+'.xlsx';

var blob = new Blob([data], {type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"});

if (window.navigator.msSaveOrOpenBlob) {// For IE:

navigator.msSaveBlob(blob, filename);

}else{ // For other browsers:

var objectUrl = URL.createObjectURL(blob);

var a = document.createElement('a');

document.body.appendChild(a);

a.setAttribute('style', 'display:none');

a.setAttribute('href', objectUrl);

a.setAttribute('download', filename);

a.click();

URL.revokeObjectURL(objectUrl);

}

});

}