只需要传入 之前的需要追加的 Workbook对象,如果不需要追加 传NULL即可(默认情况下用的HSSF),传入 list 跟 sheet名称 ,tableName ,为 list所在区域的名称。工具类如下:
package com.quantfn.grpc.report.utils;
import com.quantfn.grpc.core.utils.ExcelUtils;
import com.quantfn.grpc.core.utils.FeflectUtils;
import org.apache.poi.ss.usermodel.Workbook;
import java.util.List;
public class ReportUtils {
/**
* 2003版 可以追加写入 list中的数据
* @param wb
* @param sheetName
* @param list
* @param tableName
* @return
*/
public static Workbook getWorkbook(Workbook wb ,String sheetName, List<Object> list, String tableName){
String[] title = new String[0];//标题
if (list!=null && list.size()>0){
title = FeflectUtils.getFiledName(list.get(0));
}
Object[][] values = new Object[list.size()][title.length];
for (int i = 0; i < list.size(); i++) {
Object o = list.get(i);
for (int j = 0; j < title.length; j++) {
values[i][j]=FeflectUtils.getFieldValueByName(title[j],o);
}
}
Workbook result = ExcelUtils.getHSSFWorkbook(sheetName,tableName, title, values, wb);
return result;
}
}
package com.quantfn.grpc.core.utils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.logging.Logger;
/**
*
*/
public class ExcelUtils {
private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
private static final String[] sources = new String[]{"A", "B", "C", "D", "E",
"F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R",
"S", "T", "U", "V", "W", "X", "Y", "Z"};
private static Logger log = Logger.getLogger(ExcelUtils.class.getName());
public static Workbook getHSSFWorkbook(String sheetName, String tableName, String[] title, Object[][] values, Workbook wb) {
//第0步
int startRow = 0;
int startClo;
int endRow = values[0].length;
int endClo;
// 第一步,创建一个webbook,对应一个Excel文件
if (wb == null) {
//默认创建
wb = new HSSFWorkbook();
}
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
Sheet sheet = wb.getSheet(sheetName);
if (sheet == null) {
sheet = wb.createSheet(sheetName);
}
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
int lastRowNum = sheet.getLastRowNum();
log.info(sheetName);
log.info(lastRowNum + " 上一次的行号 num");
// 第四步,创建单元格,并设置值表头 设置表头居中
CellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 创建一个靠左格式
//同Sheet追加,空两行
if (lastRowNum != 0) {
lastRowNum+=3;
}
Row row = sheet.createRow(lastRowNum);
//设置起始行
startRow = lastRowNum;
Cell cell = null;
//创建标题
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(style);
endRow = cell.getRowIndex();
}
startClo = 1;
//创建内容
for (int i = 0; i < values.length; i++) {
row = sheet.createRow(lastRowNum + i + 1);
for (int j = 0; j < values[i].length; j++) {
Object current = values[i][j];
if (current instanceof String) {
String cur = String.valueOf(current);
row.createCell(j).setCellValue(cur);
} else if (current instanceof Double) {
Double cur = Double.valueOf(String.valueOf(current));
row.createCell(j).setCellValue(cur);
} else if (current instanceof Date) {
String date = sdf.format(current);
row.createCell(j).setCellValue(date);
} else if (current instanceof Long) {
Long lo = Long.valueOf(String.valueOf(current));
row.createCell(j).setCellValue(lo);
} else if (current == null) {
row.createCell(j).setCellValue("");
} else if (current instanceof BigDecimal) {
double d = ((BigDecimal) current).doubleValue();
row.createCell(j).setCellValue(d);
} else {
System.out.println(current);
throw new RuntimeException(" date type question");
}
}
endRow = sheet.getLastRowNum();
}
endClo = title.length;
//产生内容放在 Name 域当中
Name reference = wb.getName(tableName);
if (reference == null) {
reference = wb.createName();
reference.setNameName(tableName);
}
/**
* referenceString 引用位置范围
* 例如:=sheetSmalls!$A$1:$M$6
*/
String referenceString = getReferenceStringByCellRow(sheetName, startRow, startClo, endRow, endClo);
reference.setRefersToFormula(referenceString);
return wb;
}
private static String getReferenceStringByCellRow(String sheetName, int startRow, int startClo, int endRow, int endClo) {
int startR = startRow + 1;
int endR = endRow + 1;
String referenceString = sheetName + "!$" + getCorrespondingLabel(startClo) + "$" + startR + ":$" + getCorrespondingLabel(endClo) + "$" + endR;
return referenceString;
}
/**
* 返回该列号对应的字母
*
* @param columnNo (xls的)第几列(从1开始)
*/
private static String getCorrespondingLabel(int columnNo) {
if (columnNo < 1/** ||columnNo>16384 **/
)
throw new IllegalArgumentException();
StringBuilder sb = new StringBuilder(5);
int remainder = columnNo % 26;
if (remainder == 0) {
sb.append("Z");
remainder = 26;
} else {
sb.append(sources[remainder - 1]);
}
while ((columnNo = (columnNo - remainder) / 26 - 1) > -1) {
remainder = columnNo % 26;
sb.append(sources[remainder]);
}
return sb.reverse().toString();
}
}