本文解决要导出的表格表头有合并行格和列格的场景，工具类方法兼容上一篇<<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 <T> void specialExcelData(String title, Column[][] headers, String[] fields, Collection<T> 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 <T> void exportSpecialExcelData(String title,Column[][] headers,String[] fields,Collection<T> 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<T> 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 <T> void exportExcelData(String title, String[] headers, String[] colums, Collection<T> 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<HedgeFundReport> 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);
}
});
}
232
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
