java 操作Excel文件一些感悟（内存溢出）
java中操作报表很正常：所以Excel很常用到，我介绍几个关于封装类：
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
public class CommonExcelData implements java.io.Serializable, IsDataBean{
private String reportName; //报表的大标题
private HashMap otherDatas; //其他数据
private HashMap queryParams; //查询条件
private HashMap queryParamsn; //查询条件2
private HashMap totalInfos; //汇总信息
private String[][] columnTitles; //列标题
private String[][] columnTitlesEn; //列标题
private boolean isWarrantReport;
private Collection datas; //正文数据
private HashSet orderNum;
private Long fileId;
private ReportCollection allCollection;
private String reportImage1; //图片路径
private String reportImage2; //图片路径
private HashMap NewImage; //NEW
private HashMap TimImage; //TIM
private HashMap EqtImage; //EQT
private HashMap CnlImage; //CNL
private HashMap RteImage; //RTE
private HashMap AllImage; //所有报文
private HashMap ShareImage; //所占报文
private String reportNameImage; //报表提示信息
public HashMap getQueryParamsn() {
return queryParamsn;
}
public void setQueryParamsn(HashMap queryParamsn) {
this.queryParamsn = queryParamsn;
}
// private List<ReportDataByDept> listDataByDept;
public ReportCollection getAllCollection() {
return allCollection;
}
public void setAllCollection(ReportCollection allCollection) {
this.allCollection = allCollection;
}
public String getReportName() {
return reportName;
}
public void setReportName(String reportName) {
this.reportName = reportName;
}
public HashMap getTotalInfos() {
return totalInfos;
}
public void setTotalInfos(HashMap totalInfos) {
this.totalInfos = totalInfos;
}
public Collection getDatas() {
return datas;
}
public void setDatas(Collection datas) {
this.datas = datas;
}
public String[][] getColumnTitles() {
return columnTitles;
}
public void setColumnTitles(String[][] columnTitles) {
this.columnTitles = columnTitles;
}
public HashMap getOtherDatas() {
return otherDatas;
}
public void setOtherDatas(HashMap otherDatas) {
this.otherDatas = otherDatas;
}
public HashMap getQueryParams() {
return queryParams;
}
public void setQueryParams(HashMap queryParams) {
this.queryParams = queryParams;
}
public HashSet getOrderNum() {
return orderNum;
}
public void setOrderNum(HashSet orderNum) {
this.orderNum = orderNum;
}
public Long getFileId() {
return fileId;
}
public void setFileId(Long fileId) {
this.fileId = fileId;
}
// public List<ReportDataByDept> getListDataByDept() {
// return listDataByDept;
// }
// public void setListDataByDept(List<ReportDataByDept> listDataByDept) {
// this.listDataByDept = listDataByDept;
// }
public String[][] getColumnTitlesEn() {
return columnTitlesEn;
}
public void setColumnTitlesEn(String[][] columnTitlesEn) {
this.columnTitlesEn = columnTitlesEn;
}
public boolean isWarrantReport() {
return isWarrantReport;
}
public void setWarrantReport(boolean isWarrantReport) {
this.isWarrantReport = isWarrantReport;
}
public String getReportImage1() {
return reportImage1;
}
public void setReportImage1(String reportImage1) {
this.reportImage1 = reportImage1;
}
public String getReportImage2() {
return reportImage2;
}
public void setReportImage2(String reportImage2) {
this.reportImage2 = reportImage2;
}
public HashMap getNewImage() {
return NewImage;
}
public void setNewImage(HashMap newImage) {
NewImage = newImage;
}
public HashMap getTimImage() {
return TimImage;
}
public void setTimImage(HashMap timImage) {
TimImage = timImage;
}
public HashMap getEqtImage() {
return EqtImage;
}
public void setEqtImage(HashMap eqtImage) {
EqtImage = eqtImage;
}
public HashMap getCnlImage() {
return CnlImage;
}
public void setCnlImage(HashMap cnlImage) {
CnlImage = cnlImage;
}
public HashMap getRteImage() {
return RteImage;
}
public void setRteImage(HashMap rteImage) {
RteImage = rteImage;
}
public HashMap getAllImage() {
return AllImage;
}
public void setAllImage(HashMap allImage) {
AllImage = allImage;
}
public HashMap getShareImage() {
return ShareImage;
}
public void setShareImage(HashMap shareImage) {
ShareImage = shareImage;
}
public String getReportNameImage() {
return reportNameImage;
}
public void setReportNameImage(String reportNameImage) {
this.reportNameImage = reportNameImage;
}
}
生成报表类：
import java.io.File;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
public class ReportGenerate {
private String fileName;
private CommonExcelData commonData;
private DecimalFormat df = new DecimalFormat("0.00");
public ReportGenerate(String fileName, CommonExcelData commonData) {
this.fileName = fileName;
this.commonData = commonData;
}
public void generateReport()throws Exception {
File file = new File(fileName);
WritableWorkbook workbook = null;
try {
workbook = Workbook.createWorkbook(file);
WritableSheet sheet = workbook.createSheet("sheet0", 0);
//大标题的格式
WritableFont titleNameFont = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat titleNameFormat = new WritableCellFormat(titleNameFont);
titleNameFormat.setAlignment(jxl.format.Alignment.CENTRE);
titleNameFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
//列标题的格式
WritableFont columnNameFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat columnNameFormat = new WritableCellFormat(columnNameFont);
columnNameFormat.setAlignment(jxl.format.Alignment.CENTRE);
columnNameFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
//正文数据格式
WritableFont detFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat detFormat = new WritableCellFormat(detFont);
detFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
Label l = null;
//将大标题写入报表文件
l = new Label(0, 0, commonData.getReportName(), titleNameFormat);
sheet.addCell(l);
sheet.mergeCells(0, 0, 5, 0); //大标题跨5列显示
//将查询条件写入报表文件
int column = 0; //列坐标
int row = 2; //行坐标
Map queryParams = commonData.getQueryParams();
if(queryParams != null) {
int num = 0;
Set keys = queryParams.keySet();
Iterator itor = keys.iterator();
while(itor.hasNext()) {
num++;
String key = (String) itor.next();
l = new Label(column, row, key, columnNameFormat);
sheet.addCell(l);
column++;
Object value = queryParams.get(key);
String content = getContent(value);
l = new Label(column, row, content, detFormat);
sheet.addCell(l);
column++;
if(num % 2 == 1) { //增加空格
l = new Label(column, row, " ", columnNameFormat);
sheet.addCell(l);
column++;
} else { //换行
row++;
column = 0;
}
}
row+=2;
}
//将其他数据写入报表文件
column = 0;
Map otherDatas = commonData.getOtherDatas();
if(otherDatas != null) {
int num = 0;
Set keys = otherDatas.keySet();
Iterator itor = keys.iterator();
while(itor.hasNext()) {
num++;
String key = (String) itor.next();
l = new Label(column, row, key, columnNameFormat);
sheet.addCell(l);
column++;
Object value = otherDatas.get(key);
String content = getContent(value);
l = new Label(column, row, content, detFormat);
sheet.addCell(l);
column++;
if(num % 2 == 1) { //增加空格
l = new Label(column, row, " ", columnNameFormat);
sheet.addCell(l);
column++;
} else { //换行
row++;
column = 0;
}
}
row+=2;
}
column = 0;
//将列标题写入报表文件
String[][] titles = commonData.getColumnTitles();
for (int i = 0; i < titles.length; i++) {
l = new Label(column++, row, titles[i][1], columnNameFormat);
sheet.addCell(l);
}
row++;
if(commonData.isWarrantReport()){
column = 0;
//将列英文标题写入报表文件
String[][] titles1 = commonData.getColumnTitlesEn();
for (int i = 0; i < titles.length; i++) {
l = new Label(column++, row, titles1[i][1], columnNameFormat);
sheet.addCell(l);
}
row++;
}
//写正文数据
Collection datas = commonData.getDatas();
if(datas != null) {
Iterator iter = datas.iterator();
while(iter.hasNext()) {
column = 0;
Object obj = iter.next();
Class thisClass = obj.getClass();
for(int j = 0; j < titles.length; j++) {
String fieldName = titles[j][0];
Field field = thisClass.getDeclaredField(fieldName);
Class fieldType = field.getType(); //取属性数据类型
/*
Field[] fields = thisClass.getDeclaredFields();
for(int j = 0; j < fields.length; j++) {
String fieldName = fields[j].getName(); //取属性名
Class fieldType = fields[j].getType(); //取属性数据类型
*/
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
if(fieldType.getName().equals("java.lang.Boolean")) { //布尔数据类型的属性获得方法以is开头
getMethodName = "is" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
}
Method getMethod = thisClass.getMethod(getMethodName, new Class[]{});
Object fieldValue = getMethod.invoke(obj, new Object[]{});
String content = getContent(fieldValue);
l = new Label(column++, row, content, detFormat);
sheet.addCell(l);
}
row++;
}
}
//将汇总信息写入报表文件
HashMap totalInfos = commonData.getTotalInfos();
//Object totalInfos = commonData.getTotalInfos();
if(totalInfos != null) {
Class thisClass = totalInfos.getClass();
l = new Label(0, row, "合计:", columnNameFormat);
sheet.addCell(l);
Set keys = totalInfos.keySet();
Iterator itor = keys.iterator();
while(itor.hasNext()) {
String key = ((String) itor.next()).trim();
int index = 1;
for(int i = 1; i < titles.length; i++) {
if(key.equals(titles[i][0].trim())) {
index = i;
break;
}
}
// int index = 0;
// for(int i = 0; i < titles.length; i++) {
// if(key.equals(titles[i][0].trim())) {
// index = i;
// break;
// }
// }
Object value = totalInfos.get(key);
String content = getContent(value);
l = new Label(index, row, content, detFormat);
sheet.addCell(l);
}
/*
Field[] fields = thisClass.getDeclaredFields();
column = 1;
for(int j = 1; j < fields.length; j++) {
String fieldName = fields[j].getName(); //取属性名
Class fieldType = fields[j].getType(); //取属性数据类型
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
if(fieldType.getName().equals("java.lang.Boolean")) { //布尔数据类型的属性获得方法以is开头
getMethodName = "is" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
}
Method getMethod = thisClass.getMethod(getMethodName, new Class[]{});
Object fieldValue = getMethod.invoke(totalInfos, new Object[]{});
String content = getContent(fieldValue);
l = new Label(column++, row, content, detFormat);
sheet.addCell(l);
}
*/
}
//将图片信息写入报表文件
// String reportImage = commonData.getReportImage();
// sheet.addCell(new Label(0, 3, "展示图片 jxl只支持png格式的", columnNameFormat)); // 展示图片标题
// sheet.mergeCells(0, 3, 3, 3); // 合并图片标题单元格
//
// File fileImage = new File(reportImage); // 获得图片
// WritableImage image = new WritableImage(0, 4, 3, 3, fileImage); // 设置图片显示位置
// // 4，4代表图片的高和宽占4个单元格
//
// sheet.addImage(image); // 加载图片
workbook.write();
} catch (Exception e) {
e.printStackTrace();
} finally {
if(workbook!=null){
try {
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
public void generateReportImage()throws Exception {
File file = new File(fileName);
WritableWorkbook workbook = null;
try {
workbook = Workbook.createWorkbook(file);
WritableSheet sheet = workbook.createSheet("sheet0", 0);
//大标题的格式
WritableFont titleNameFont = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat titleNameFormat = new WritableCellFormat(titleNameFont);
titleNameFormat.setAlignment(jxl.format.Alignment.CENTRE);
titleNameFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
//列标题的格式
WritableFont columnNameFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat columnNameFormat = new WritableCellFormat(columnNameFont);
columnNameFormat.setAlignment(jxl.format.Alignment.CENTRE);
columnNameFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
//正文数据格式
WritableFont detFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat detFormat = new WritableCellFormat(detFont);
detFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
Label l = null;
//将大标题写入报表文件
l = new Label(0, 0, commonData.getReportName(), titleNameFormat);
sheet.addCell(l);
sheet.mergeCells(0, 0, 5, 0); //大标题跨5列显示
sheet.addCell(new Label(0, 1, commonData.getReportNameImage(), columnNameFormat));
sheet.mergeCells(0, 1, 5, 0);
//将查询条件写入报表文件
int column = 0; //列坐标
int row = 2; //行坐标
Map queryParams = commonData.getQueryParams();
if(queryParams != null) {
int num = 0;
Set keys = queryParams.keySet();
Iterator itor = keys.iterator();
while(itor.hasNext()) {
num++;
String key = (String) itor.next();
l = new Label(column, row, key, columnNameFormat);
sheet.addCell(l);
column++;
Object value = queryParams.get(key);
String content = getContent(value);
l = new Label(column, row, content, detFormat);
sheet.addCell(l);
column++;
if(num % 2 == 1) { //增加空格
l = new Label(column, row, " ", columnNameFormat);
sheet.addCell(l);
column++;
} else { //换行
row++;
column = 0;
}
}
row+=2;
}
//将查询条件2写入报表文件
int columnn = 0; //列坐标
int rown = 3; //行坐标
Map queryParamsn = commonData.getQueryParamsn();
if(queryParams != null) {
int num = 0;
Set keys = queryParamsn.keySet();
Iterator itor = keys.iterator();
while(itor.hasNext()) {
num++;
String key = (String) itor.next();
l = new Label(columnn, rown, key, columnNameFormat);
sheet.addCell(l);
columnn++;
Object value2 = queryParamsn.get(key);
String content = getContent(value2);
l = new Label(columnn, rown, content, detFormat);
sheet.addCell(l);
columnn++;
if(num % 2 == 1) { //增加空格
l = new Label(columnn, rown, " ", columnNameFormat);
sheet.addCell(l);
columnn++;
} else { //换行
row++;
columnn = 0;
}
}
rown+=2;
}
//将其他数据写入报表文件
column = 0;
Map otherDatas = commonData.getOtherDatas();
if(otherDatas != null) {
int num = 0;
Set keys = otherDatas.keySet();
Iterator itor = keys.iterator();
while(itor.hasNext()) {
num++;
String key = (String) itor.next();
l = new Label(column, row, key, columnNameFormat);
sheet.addCell(l);
column++;
Object value = otherDatas.get(key);
String content = getContent(value);
l = new Label(column, row, content, detFormat);
sheet.addCell(l);
column++;
if(num % 2 == 1) { //增加空格
l = new Label(column, row, " ", columnNameFormat);
sheet.addCell(l);
column++;
} else { //换行
row++;
column = 0;
}
}
row+=2;
}
column = 0;
//将列标题写入报表文件
String[][] titles = commonData.getColumnTitles();
for (int i = 0; i < titles.length; i++) {
l = new Label(column++, 4, titles[i][1], columnNameFormat);
sheet.addCell(l);
}
row++;
if(commonData.isWarrantReport()){
column = 0;
//将列英文标题写入报表文件
String[][] titles1 = commonData.getColumnTitlesEn();
for (int i = 0; i < titles.length; i++) {
l = new Label(column++, row, titles1[i][1], columnNameFormat);
sheet.addCell(l);
}
row++;
}
//写正文数据
Collection datas = commonData.getDatas();
if(datas != null) {
Iterator iter = datas.iterator();
while(iter.hasNext()) {
column = 0;
Object obj = iter.next();
Class thisClass = obj.getClass();
for(int j = 0; j < titles.length; j++) {
String fieldName = titles[j][0];
Field field = thisClass.getDeclaredField(fieldName);
Class fieldType = field.getType(); //取属性数据类型
/*
Field[] fields = thisClass.getDeclaredFields();
for(int j = 0; j < fields.length; j++) {
String fieldName = fields[j].getName(); //取属性名
Class fieldType = fields[j].getType(); //取属性数据类型
*/
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
if(fieldType.getName().equals("java.lang.Boolean")) { //布尔数据类型的属性获得方法以is开头
getMethodName = "is" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
}
Method getMethod = thisClass.getMethod(getMethodName, new Class[]{});
Object fieldValue = getMethod.invoke(obj, new Object[]{});
String content = getContent(fieldValue);
l = new Label(column++, row, content, detFormat);
sheet.addCell(l);
}
row++;
}
}
//将NEW报文信息写入报表文件
HashMap NewImage = commonData.getNewImage();
//Object totalInfos = commonData.getTotalInfos();
if(NewImage != null) {
Class thisClass = NewImage.getClass();
l = new Label(0, 5, "NEW:", columnNameFormat);
sheet.addCell(l);
Set keys = NewImage.keySet();
Iterator itor = keys.iterator();
while(itor.hasNext()) {
String key = ((String) itor.next()).trim();
int index = 1;
for(int i = 1; i < titles.length; i++) {
if(key.equals(titles[i][0].trim())) {
index = i;
break;
}
}
Object value = NewImage.get(key);
String content = getContent(value);
l = new Label(index, 5, content, detFormat);
sheet.addCell(l);
}
}
//将TIM报文信息写入报表文件
HashMap TimImage = commonData.getTimImage();
//Object totalInfos = commonData.getTotalInfos();
if(TimImage != null) {
Class thisClass = TimImage.getClass();
l = new Label(0, 6, "TIM:", columnNameFormat);
sheet.addCell(l);
Set keys = TimImage.keySet();
Iterator itor = keys.iterator();
while(itor.hasNext()) {
String key = ((String) itor.next()).trim();
int index = 1;
for(int i = 1; i < titles.length; i++) {
if(key.equals(titles[i][0].trim())) {
index = i;
break;
}
}
Object value = TimImage.get(key);
String content = getContent(value);
l = new Label(index, 6, content, detFormat);
sheet.addCell(l);
}
}
//将EQT报文信息写入报表文件
HashMap EqtImage = commonData.getEqtImage();
//Object totalInfos = commonData.getTotalInfos();
if(EqtImage != null) {
Class thisClass = EqtImage.getClass();
l = new Label(0, 7, "EQT:", columnNameFormat);
sheet.addCell(l);
Set keys = EqtImage.keySet();
Iterator itor = keys.iterator();
while(itor.hasNext()) {
String key = ((String) itor.next()).trim();
int index = 1;
for(int i = 1; i < titles.length; i++) {
if(key.equals(titles[i][0].trim())) {
index = i;
break;
}
}
Object value = EqtImage.get(key);
String content = getContent(value);
l = new Label(index, 7, content, detFormat);
sheet.addCell(l);
}
}
//将CNL报文信息写入报表文件
HashMap CnlImage = commonData.getCnlImage();
//Object totalInfos = commonData.getTotalInfos();
if(CnlImage != null) {
Class thisClass = CnlImage.getClass();
l = new Label(0, 8, "CNL:", columnNameFormat);
sheet.addCell(l);
Set keys = CnlImage.keySet();
Iterator itor = keys.iterator();
while(itor.hasNext()) {
String key = ((String) itor.next()).trim();
int index = 1;
for(int i = 1; i < titles.length; i++) {
if(key.equals(titles[i][0].trim())) {
index = i;
break;
}
}
Object value = CnlImage.get(key);
String content = getContent(value);
l = new Label(index, 8, content, detFormat);
sheet.addCell(l);
}
}
//将RTE报文信息写入报表文件
HashMap RteImage = commonData.getRteImage();
//Object totalInfos = commonData.getTotalInfos();
if(RteImage != null) {
Class thisClass = RteImage.getClass();
l = new Label(0, 9, "RTE:", columnNameFormat);
sheet.addCell(l);
Set keys = RteImage.keySet();
Iterator itor = keys.iterator();
while(itor.hasNext()) {
String key = ((String) itor.next()).trim();
int index = 1;
for(int i = 1; i < titles.length; i++) {
if(key.equals(titles[i][0].trim())) {
index = i;
break;
}
}
Object value = RteImage.get(key);
String content = getContent(value);
l = new Label(index, 9, content, detFormat);
sheet.addCell(l);
}
}
//将所有报文信息写入报表文件
HashMap AllImage = commonData.getAllImage();
//Object totalInfos = commonData.getTotalInfos();
if(AllImage != null) {
Class thisClass = AllImage.getClass();
l = new Label(0, 10, "所有报文:", columnNameFormat);
sheet.addCell(l);
Set keys = AllImage.keySet();
Iterator itor = keys.iterator();
while(itor.hasNext()) {
String key = ((String) itor.next()).trim();
int index = 1;
for(int i = 1; i < titles.length; i++) {
if(key.equals(titles[i][0].trim())) {
index = i;
break;
}
}
Object value = AllImage.get(key);
String content = getContent(value);
l = new Label(index, 10, content, detFormat);
sheet.addCell(l);
}
}
//将所占比例信息写入报表文件
HashMap ShareImage = commonData.getShareImage();
if(ShareImage != null) {
Class thisClass = ShareImage.getClass();
l = new Label(0, 11, "所占比例:", columnNameFormat);
sheet.addCell(l);
Set keys = ShareImage.keySet();
Iterator itor = keys.iterator();
while(itor.hasNext()) {
String key = ((String) itor.next()).trim();
int index = 1;
for(int i = 1; i < titles.length; i++) {
if(key.equals(titles[i][0].trim())) {
index = i;
break;
}
}
Object value = ShareImage.get(key);
String content = getContent(value);
l = new Label(index, 11, content, detFormat);
sheet.addCell(l);
}
}
//将图片1信息写入报表文件
String reportImage = commonData.getReportImage1();
// sheet.addCell(new Label(0, 12, "报文处理结果", columnNameFormat)); // 展示图片标题
// sheet.mergeCells(0, 12, 5, 12); // 合并图片标题单元格
File fileImage = new File(reportImage); // 获得图片
WritableImage image = new WritableImage(0, 13, 6, 22, fileImage); // 设置图片显示位置
sheet.addImage(image); // 加载图片
//将图片2信息写入报表文件
String reportImage2 = commonData.getReportImage2();
// sheet.addCell(new Label(8, 12, "报文数量", columnNameFormat)); // 展示图片标题
// sheet.mergeCells(8, 12, 7, 0); // 合并图片标题单元格
File fileImage2 = new File(reportImage2); // 获得图片
WritableImage image2 = new WritableImage(6, 13, 6, 22, fileImage2); // 设置图片显示位置
sheet.addImage(image2); // 加载图片
workbook.write();
} catch (Exception e) {
e.printStackTrace();
} finally {
if(workbook!=null){
try {
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
private String getContent(Object fieldValue) {
String content = "";
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
if(fieldValue == null) {
content = "";
} else if(fieldValue instanceof String) {
content = (String) fieldValue;
} else if(fieldValue instanceof Integer) {
content = fieldValue.toString();
} else if(fieldValue instanceof Double) { //需要消除科学计数法
Double value = (Double) fieldValue;
value = ArithUtil.round(value, 2);
content = df.format(value);
//content = fieldValue.toString();
} else if(fieldValue instanceof Float) { //需要消除科学计数法
Float value = (Float) fieldValue;
Double dValue = new Double(value);
dValue = ArithUtil.round(dValue, 2);
content = df.format(dValue);
// content = fieldValue.toString();
} else if(fieldValue instanceof Date) {
content = format.format(fieldValue);
} else if(fieldValue instanceof Boolean) {
content = fieldValue.toString();
}
else if(fieldValue instanceof Long)
{
content = fieldValue.toString();
} else if(fieldValue instanceof BigDecimal) {
((BigDecimal)fieldValue).setScale(2,java.math.BigDecimal.ROUND_HALF_UP);
content = df.format(fieldValue);
}
return content;
}
}
当出现大数据的时候一次性导出的时候很常会内存溢出：我就想到一个直接用流导出，但是不能设置样式
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
public class ReportForLee {
private String fileName;
private CommonExcelData commonData;
private DecimalFormat df = new DecimalFormat("0.00");
public ReportForLee(String fileName, CommonExcelData commonData) {
this.fileName = fileName;
this.commonData = commonData;
}
public void generateReport( )throws Exception {
File file = new File(fileName);
FileOutputStream out = new FileOutputStream(file);
OutputStreamWriter osw = new OutputStreamWriter(out, "GB2312");
BufferedWriter bw = new BufferedWriter(osw);
StringBuffer sbTitle = new StringBuffer();
// 创建表头
String[][] titles = commonData.getColumnTitles();
for (int i = 0; i < titles.length; i++) {
sbTitle.append(titles[i][1]).append("\t");
}
sbTitle.append("\r\n");
bw.write(sbTitle.toString());
StringBuffer mess = new StringBuffer();
Collection<?> list = commonData.getDatas();
if(list != null) {
Iterator<?> iter = list.iterator();
while(iter.hasNext()) {
Object obj = iter.next();
Class<? extends Object> thisClass = obj.getClass();
for(int j = 0; j < titles.length; j++) {
String fieldName = titles[j][0];
Field field = thisClass.getDeclaredField(fieldName);
Class<?> fieldType = field.getType(); //取属性数据类型
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
if(fieldType.getName().equals("java.lang.Boolean")) { //布尔数据类型的属性获得方法以is开头
getMethodName = "is" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
}
Method getMethod = thisClass.getMethod(getMethodName, new Class[]{});
Object fieldValue = getMethod.invoke(obj, new Object[]{});
String content = getContent(fieldValue);
mess.append(content).append("\t");
}
mess.append("\r\n");
}
}
bw.write(mess.toString());
bw.close();
osw.close();
out.close();
}
private String getContent(Object fieldValue) {
String content = "";
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
if(fieldValue == null) {
content = "";
} else if(fieldValue instanceof String) {
content = (String) fieldValue;
} else if(fieldValue instanceof Integer) {
content = fieldValue.toString();
} else if(fieldValue instanceof Double) { //需要消除科学计数法
Double value = (Double) fieldValue;
value = ArithUtil.round(value, 2);
content = df.format(value);
//content = fieldValue.toString();
} else if(fieldValue instanceof Float) { //需要消除科学计数法
Float value = (Float) fieldValue;
Double dValue = new Double(value);
dValue = ArithUtil.round(dValue, 2);
content = df.format(dValue);
// content = fieldValue.toString();
} else if(fieldValue instanceof Date) {
content = format.format(fieldValue);
} else if(fieldValue instanceof Boolean) {
content = fieldValue.toString();
}
else if(fieldValue instanceof Long)
{
content = fieldValue.toString();
} else if(fieldValue instanceof BigDecimal) {
((BigDecimal)fieldValue).setScale(2,java.math.BigDecimal.ROUND_HALF_UP);
content = df.format(fieldValue);
}
return content;
}
}
