POI HSSF - Excel实例

2019-04-15 19:18发布

站内文章 / PIC单片机
12822 0 1092
生成Excel效果图如下：
[img]http://dl.iteye.com/upload/picture/pic/94896/282433e9-f6cb-3396-8612-8b47caef2297.png[/img]


package com.kjlink.util;

import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

public class HSSFUtil {

private HSSFWorkbook workbook = null; // Excel工作簿
private HSSFSheet sheet = null;
private HSSFRow row = null;
private HSSFCell cell = null;
private HSSFCellStyle style = null;
private HSSFFont font = null;
private List list = null;

private String[] header = null; // 标题行
private String[][] body =null; // body内容
private String[] properties = null; // List集合中的对象属性名集合

private static HSSFUtil hssf = new HSSFUtil();

public static final String FILE_PATH = "D:\领还记录.xls";

private HSSFUtil() {
this.workbook = new HSSFWorkbook();
this.sheet = workbook.createSheet("sheet1");
this.style = workbook.createCellStyle();
this.font = workbook.createFont();
}


public void exportExcel(String[] header,List list, String[] properties) {
this.header = header;
this.list = list;
this.properties = properties;

this.createHead();
this.createBody();
this.writeXls();

System.out.println("生成Excel成功！");
}

private void createHead() { // Excel格式头
if (header!=null&&header.length>0) {
row = sheet.createRow(0);
for (int i = 0; i
sheet.setColumnWidth(i, 5000);

cell = row.createCell(i);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(header[i]);
this.defaultStyle();
this.defaultFont();
cell.setCellStyle(this.style);
}
}
}

private void createBody() {
if (this.listToArray()!=null) {
int lastnum = sheet.getLastRowNum();
for (int i = 0; i < body.length; i++) {
row = sheet.createRow(++lastnum);
for (int j = 0; j < body[i].length; j++) {
cell = row.createCell(j);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(body[i][j]);
}
}
}
}

private void writeXls() {
FileOutputStream out = null;
try {
out = new FileOutputStream(FILE_PATH);
workbook.write(out);
} catch (IOException e) {
throw new RuntimeException(e);
} finally {
if (out!=null) {
try {
out.flush();
out.close();
} catch (IOException e) {
throw new RuntimeException(e);
}
}
}
}

private void defaultStyle () {
style.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
}

private void defaultFont () {
font.setColor(HSSFColor.WHITE.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

style.setFont(font);
}

private String[][] listToArray() {
if (list!=null&&!list.isEmpty()) {
if (properties!=null&&properties.length>0) {
body = new String[list.size()][properties.length]; // 二维数组Body
for (int i = 0; i < body.length; i++) {
for (int j = 0; j < body[i].length; j++) {
Object obj = list.get(i); // 获取当前对象
Class clzz = obj.getClass(); // 获取当前对象的Clas对象
try {
String getterName = properties[j];
if (getterName.indexOf(".")!=-1) { // getter名称为【对象.属性】
String[] subPro = getterName.split("\.");
String subName = null;
Object subObj = obj; // 子对象
for (int k = 0; k < subPro.length; k++) {
subName = "get" + firstWordUpper(subPro[k]);
Method method = subObj.getClass().getDeclaredMethod(subName);

if (!method.isAccessible()) {
subObj = method.invoke(subObj);
if (subObj==null) break;
if (k==subPro.length-1) {
body[i][j] = subObj.toString();
}
}
}
} else { // getter名称为【属性】
getterName = "get" + firstWordUpper(properties[j]); // getter方法名
Method method = clzz.getDeclaredMethod(getterName); // 获取指定方法名的Method对象

if(!method.isAccessible()) { // 表示该方法可以访问 修饰符不是private
Object value = method.invoke(obj); // 调用obj对象的getter方法

if (value!=null) {
if (value instanceof java.sql.Timestamp||value instanceof java.util.Date) {
body[i][j] = timeToStr(value);
} else {
body[i][j] = value.toString();
}
}
}
}

} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
}
}
return body;
}

private String firstWordUpper(String word) {
if (word!=null&&word.length()>1) {
return word.substring(0,1).toUpperCase() + word.substring(1);
}
return word;
}

private String timeToStr(Object obj) {
if (obj!=null) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
return sdf.format(obj);
}
return null;
}

public static void main(String[] args) {
List list = new ArrayList();

Consum consum1 = new Consum();
consum1.setAsset(new Asset("KJ-AA-0001"));
consum1.setUser(new User("小兵"));
consum1.setOperator(new User("信息中心"));
consum1.setDate(new Date());
consum1.setRemark("去北京出差");

Consum consum2 = new Consum();
consum2.setAsset(new Asset("KJ-AA-0002"));
consum2.setUser(new User("博海"));
consum2.setOperator(new User("信息中心"));
consum2.setDate(new Date());
consum2.setRemark("去上海出差");

list.add(consum1);
list.add(consum2);

String[] header = new String[] {"固定资产名称","领用人","操作人","领用日期","备注"};
String[] properties = new String[] {"asset.name", "user.name", "operator.name", "date", "remark"};

hssf.exportExcel(header, list, properties);

}
}

热门文章