Excel生成的主要的类
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import com.hnair.callcenter.common.StringUtil;
/**
* 创建Excel
*
* @author masque.java@gmail.com
*
*/
@SuppressWarnings("all")
public class CreateExcel {
private static CreateExcel ce = new CreateExcel();
private HSSFWorkbook wb = null;
private Sheet sheet = null;
public final static String DATA_START = "data";
/* 初始化列数 */
private int initColIndex;
/* 初始化行数 */
private int initRowIndex;
/* 当前列数 */
private int curColIndex;
/* 当前行数 */
private int curRowIndex;
/* 最后一行的数据 */
private int lastRowIndex;
private Row curRow;
private CellStyle style;
private CreateExcel() {
}
public static CreateExcel getInstance() {
return ce;
}
public CreateExcel initDataNoTemplate() {
// 没有模板时初始化创建
wb = new HSSFWorkbook();
sheet = this.wb.createSheet("sheet1");
initRowIndex = 0;
initColIndex = 0;
initStyle();
return this;
}
public CreateExcel initStyle(){
// 初始化样式
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER); // 居中
return this;
}
/**
* 初始化当前的行列
* @return
*/
private CreateExcel initConfigDataNoTemplate() {
curRowIndex = initRowIndex;
curColIndex= initColIndex ;
return this;
}
/**
* 创建新的行
*
* @return
*/
private CreateExcel createRow() {
curColIndex = initColIndex;
curRow = this.sheet.createRow(curRowIndex);
curRowIndex++;
return this;
}
/**
* 创建表头
*
* @param title
* 表头内容
* @param colSpan
* 表头宽度(占据的单元格的列的数量)
* @return
*/
public CreateExcel createTitle(String title, int colSpan) {
createRow();
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, colSpan));
// 头部占据2行所以要继续加一行
curRowIndex++;
Cell c = curRow.createCell(curColIndex);
c.setCellValue(title);
// 重置样式防止出现样式不对
CellStyle style = wb.createCellStyle();
// 居中
style.setAlignment(CellStyle.ALIGN_CENTER);
Font f = wb.createFont();
// 字体
f.setFontHeightInPoints((short) 14);
style.setFont(f);
c.setCellStyle(style);
return this;
}
/**
* 新建表头
*
* @param head
* 包含表头字符串的集合
* @return
*/
public CreateExcel createHead(List<String> head) {
createRow();
Cell c = null;
for (String query : head) {
c = curRow.createCell(curColIndex);
curColIndex++;
this.setCellValue(String.class, query, c);
c.setCellStyle(style);
}
return this;
}
/**
* 表的内容
*
* @param body
* 包含行数据的集合
* @return
*/
public CreateExcel createBody(List<?> body) {
for (Object t : body) {
createRow();
Map<String, ColProperty> indexMap = findBeanProperty(t.getClass());
Method m = null;
try {
for (int i = 0; i < indexMap.size(); i++) {
Iterator<String> it = indexMap.keySet().iterator();
while (it.hasNext()) {
String key = it.next();
ColProperty value = indexMap.get(key);
// 从1开始查找字段
if (value.getIndex() != (i + 1)) {
continue;
}
// 得到get方法
m = t.getClass().getMethod(
"get" + StringUtil.toUpperCaseFirstOne(key),
null);
Class<?> clazz = value.getType();
// 反射得到值
Object oo = m.invoke(t, null);
setCellValue(clazz, oo,createCell());
}
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
return this;
}
public CreateExcel createBodyCoalition(List<?> body) {
if(null == body||body.size() == 0){
throw new RuntimeException("表内容不能为空!");
}
for (Object t : body) {
curColIndex = 0;
createRow();
Method m = null;
try {
m = t.getClass().getMethod("getL",null);
Object oo = m.invoke(t, null);
List<Object> ol = (List)oo;
Field fs[] = t.getClass().getDeclaredFields();
for (Field f : fs) {
String methodName = f.getName().substring(0, 1).toUpperCase()
+ f.getName().substring(1);
if(f.getType() != java.util.List.class){
m = t.getClass().getDeclaredMethod("get" + methodName);
Object oper = m.invoke(t,null);
System.out.println(oper);
setCellValue(f.getType(), oper, createCell(ol.size()-1, 1));
}
}
for(int i = 0;i<ol.size();i++){
if(i != 0){
createRow();
curColIndex = 3;
}
Field fields[] = ol.get(i).getClass().getDeclaredFields();
for (Field field : fields) {
String methodNameL = field.getName().substring(0, 1).toUpperCase()
+ field.getName().substring(1);
m = ol.get(i).getClass().getDeclaredMethod("get" + methodNameL);
Object value = m.invoke(ol.get(i), null);
setCellValue(value.getClass(), value, createCell());
}
System.out.println("");
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
return this;
}
public CreateExcel createBodyCoalition(Map<String, Map<String, List<List<Object>>>> result) {
if(result == null || result.size() == 0)
throw new RuntimeException("表内容不能为空!");
Iterator<String> resultIt = result.keySet().iterator();
while(resultIt.hasNext()){
curColIndex = 0;
createRow();
String resultKey = resultIt.next();
Map<String, List<List<Object>>> map = result.get(resultKey);
setCellValue(String.class, resultKey, createCell(countMapList(map)-1, 1));
boolean isSecondColFirst = true;
Iterator<String> mapIt = map.keySet().iterator();
while(mapIt.hasNext()){
if(!isSecondColFirst){
createRow();
curColIndex = 1;
}
isSecondColFirst = false;
String mapKey = mapIt.next();
List<List<Object>> l = map.get(mapKey);
setCellValue(String.class, mapKey, createCell(l.size()-1, 1));
for(int i=0;i<l.size();i++){
if(i!=0){
createRow();
curColIndex = 2;
}
for(int j=0;j<l.get(i).size();j++){
setCellValue(String.class, l.get(i).get(j), createCell());
}
}
}
}
return this;
}
private int countMapList(Map<String, List<List<Object>>> map){
int count = 0;
Iterator<String> mapIt = map.keySet().iterator();
while(mapIt.hasNext()){
count += map.get(mapIt.next()).size();
}
return count;
}
/**
* 创建新内容
*
* @param clazz
* 单元格内容的类型
* @param value
* 单元格内容的数据
* @return
*/
public CreateExcel setCellValue(Class<?> clazz, Object value,Cell c) {
if(null == value){
c.setCellValue("");
return this;
}
// 根据数据的类型set值
if (clazz == java.lang.String.class) {
c.setCellValue(value.toString());
} else if (clazz == java.lang.Integer.class || clazz == int.class
|| clazz == java.lang.Double.class || clazz == double.class
|| clazz == java.lang.Long.class || clazz == long.class
|| clazz == java.lang.Short.class || clazz == short.class
|| clazz == java.lang.Float.class || clazz == float.class) {
c.setCellValue(Double.parseDouble(value.toString()));
} else if (clazz == java.lang.Boolean.class || clazz == boolean.class) {
c.setCellValue(Boolean.parseBoolean(value.toString()));
} else if (clazz == java.util.Date.class) {
CreationHelper createHelper = wb.getCreationHelper();
if(value.toString().length()>=8&&value.toString().length()<=10)
this.style.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd"));
else
this.style.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd hh:mm:ss"));
c.setCellValue((Date)value);
} else {
throw new RuntimeException("setCellValue:未知的参数类型!");
}
c.setCellStyle(this.style);
initStyle();
return this;
}
/**
* 创建新的单元格(合并单元格)
* @param rowSpan
* 跨行数
* @param colSpan
* 跨列数
* @return
*/
public Cell createCell(int rowSpan,int colSpan){
Cell c = curRow.createCell(curColIndex);
sheet.addMergedRegion(new CellRangeAddress(curRowIndex-1, curRowIndex+rowSpan-1, curColIndex, curColIndex+colSpan-1));
curColIndex+=colSpan;
c.setCellStyle(style);
return c;
}
/**
* 创建新的单元格
* @return
*/
public Cell createCell(){
Cell c = curRow.createCell(curColIndex);
curColIndex++;
return c;
}
/**
* 得到实体的带有注解的字段的内容
*
* @param class1
* @return
*/
private Map<String, ColProperty> findBeanProperty(Class<?> clazz) {
Field fs[] = clazz.getDeclaredFields();
Map<String, ColProperty> indexMap = new HashMap<String, ColProperty>();
for (Field f : fs) {
// 寻找有注解的字段
TabCol tabCol = f.getAnnotation(TabCol.class);
if (null == tabCol) {
continue;
} else {
ColProperty colProperty = new ColProperty(f.getType(),
tabCol.Index(), tabCol.title());
indexMap.put(f.getName(), colProperty);
}
}
return indexMap;
}
/**
* 得到表头的集合
* @param map
* @return
*/
private List<String> getHead(Map<String, ColProperty> map) {
List<String> heads = new ArrayList<String>();
for(int i=0;i<map.size();i++){
Iterator<String> it = map.keySet().iterator();
while (it.hasNext()) {
String key = it.next();
ColProperty value = map.get(key);
if(value.getIndex() != (i+1)){
continue;
}
heads.add(value.getTitle());
}
}
return heads;
}
/**
* 创建统计数据
* @param t
* 包含统计数据的实体
* @return
*/
public CreateExcel createCounts(Object t) {
createRow();
Map<String, CountProperty> map = getCountProperty(t.getClass());
Method m = null;
for (int i = 0; i < map.size(); i++) {
Iterator<String> it = map.keySet().iterator();
while (it.hasNext()) {
String key = it.next();
//查找注解字段
CountProperty countProperty = map.get(key);
if (countProperty.getIndex() != (i + 1)) {
continue;
}
try {
m = t.getClass().getMethod(
"get" + StringUtil.toUpperCaseFirstOne(key), null);
Class<?> clazz = countProperty.getType();
Object oo;
//反射得到值
oo = m.invoke(t, null);
if (countProperty.getColSpan() == 1) {
this.setCellValue(clazz, oo,createCell());
} else {
// 根据注解判断是否合并单元格
if (countProperty.getIsCoalition()) {
setCellValue(clazz, oo, createCell(0, countProperty.getColSpan()));
} else {
setCellValue(clazz, oo,createCell());
//填充空的单元格
for (int j = 0; j < countProperty.getColSpan() - 1; j++) {
setCellValue(String.class, "",createCell());
}
}
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
return this;
}
/**
* 处理统计数据的属性,得到值
* @param clazz
* @return
*/
public Map<String, CountProperty> getCountProperty(Class<?> clazz) {
Field fs[] = clazz.getDeclaredFields();
Map<String, CountProperty> indexMap = new HashMap<String, CountProperty>();
for (Field f : fs) {
// 寻找有注解的字段
CountData countData = f.getAnnotation(CountData.class);
if (null == countData) {
continue;
} else {
CountProperty countProperty = new CountProperty(f.getType(),
countData.index(), countData.colSpan(),
countData.isCoalition());
indexMap.put(f.getName(), countProperty);
}
}
return indexMap;
}
/**
* 生成Excel文件
* @param filePath
*/
public void writeToFile(String filePath) {
FileOutputStream fos = null;
try {
fos = new FileOutputStream(new File(filePath));
wb.write(fos);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 返回workbook对象
* @return
*/
public Workbook getWorkbook() {
return wb;
}
/**
* 生成带有统计数据的excel
* @param title
* 表头数据
* @param body
* 表的内容-->带有注解的泛型实体 注解见:com.hnair.callcenter.common.excel.TabCol
* @param t
* 统计数据
* @return
*/
public CreateExcel CreateNewExcelNoTemplate(String title, List<?> body, Object t) {
if (null == title || "".equals(title)) {
throw new RuntimeException("表头不能为空!");
}
if (null == body || body.size() == 0) {
throw new RuntimeException("表内容为空!");
}
if (null == t) {
throw new RuntimeException("统计数据为空!");
}
initDataNoTemplate();
initConfigDataNoTemplate();
List<String> heads = getHead(findBeanProperty(body.get(0).getClass()));
createTitle(title, heads.size()-1);
createHead(heads);
createBody(body);
createCounts(t);
return this;
};
public CreateExcel CreateNewExcelNoTemplate(String title, List<?> body) {
if (null == title || "".equals(title)) {
throw new RuntimeException("表头不能为空!");
}
if (null == body || body.size() == 0) {
throw new RuntimeException("表内容为空!");
}
initDataNoTemplate();
initConfigDataNoTemplate();
List<String> heads = getHead(findBeanProperty(body.get(0).getClass()));
createTitle(title, heads.size());
createHead(heads);
createBody(body);
return this;
};
public CreateExcel CreateNewExcelWithTemplate(String title, List<?> body,
Object o, String filePath) {
if (null == title || "".equals(title)) {
throw new RuntimeException("表头不能为空!");
}
if (null == body || body.size() == 0) {
throw new RuntimeException("表内容为空!");
}
if (null == o) {
throw new RuntimeException("统计数据为空!");
}
readTemplate(filePath);
initConfigData();
List<String> heads = getHead(findBeanProperty(body.get(0).getClass()));
createTitle(title, heads.size()-1);
createHead(heads);
createBody(body);
createCounts(o);
return this;
}
public CreateExcel CreateNewExcelWithTemplate(String title, List<?> body,
String filePath) {
if (null == title || "".equals(title)) {
throw new RuntimeException("表头不能为空!");
}
if (null == body || body.size() == 0) {
throw new RuntimeException("表内容为空!");
}
readTemplate(filePath);
initConfigData();
List<String> heads = getHead(findBeanProperty(body.get(0).getClass()));
createTitle(title, heads.size()-1);
createHead(heads);
createBody(body);
CreateNewExcelNoTemplate(title, body);
return this;
}
/**
* 读取表头模板
*
* @param filePath
* @return
*/
public CreateExcel readTemplate(String filePath) {
try {
wb = new HSSFWorkbook(new FileInputStream(new File(filePath)));
sheet = wb.getSheetAt(0);
} catch (FileNotFoundException e) {
e.printStackTrace();
throw new RuntimeException("未找到相关模板!");
} catch (IOException e) {
e.printStackTrace();
}
return this;
}
/**
* 查找数据的起始标记
*/
@SuppressWarnings("static-access")
private void initConfigData() {
boolean findData = false;
for (Row row : sheet) {
if (findData)
break;
for (Cell cell : row) {
if (cell.getCellType() != cell.CELL_TYPE_STRING)
continue;
String str = cell.getStringCellValue();
// DATA_START就是默认的起始标记
if (str.equals(DATA_START)) {
initColIndex = cell.getColumnIndex();
initRowIndex = row.getRowNum();
// 初始化数据
curColIndex = initColIndex;
curRowIndex = initRowIndex;
findData = true;
break;
}
}
}
}
}
封装的实体
public class ColProperty {
private Class<?> type;
private Integer index;
private String title;
public ColProperty() {
}
public ColProperty(Class<?> type, Integer index, String title) {
this.type = type;
this.index = index;
this.title = title;
}
public Class<?> getType() {
return type;
}
public void setType(Class<?> type) {
this.type = type;
}
public Integer getIndex() {
return index;
}
public void setIndex(Integer index) {
this.index = index;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
}
public class CountProperty {
private int index;
private int colSpan;
private Class<?> type;
private boolean isCoalition;
public CountProperty() {
}
public CountProperty(Class<?> type, int index, int colSpan, boolean isCoalition) {
this.type = type;
this.index = index;
this.colSpan = colSpan;
this.isCoalition = isCoalition;
}
public Class<?> getType() {
return type;
}
public void setType(Class<?> type) {
this.type = type;
}
public int getIndex() {
return index;
}
public void setIndex(int index) {
this.index = index;
}
public int getColSpan() {
return colSpan;
}
public void setColSpan(int colSpan) {
this.colSpan = colSpan;
}
public boolean getIsCoalition() {
return isCoalition;
}
public void setIsCoalition(boolean isCoalition) {
this.isCoalition = isCoalition;
}
}
CountData统计字段的实体注解信息(一般统计有合并单元格的操作)
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
@Retention(RetentionPolicy.RUNTIME)
public @interface CountData {
int index();
int colSpan();
boolean isCoalition();
}
表内容对应的实体注解的信息
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
@Retention(RetentionPolicy.RUNTIME)
/**
* 表内容对应的实体的信息
* @author masque.java@gmail.com
*
*/
public @interface TabCol {
/**
* 字段在表中列的顺序 1 开始
* @return
*/
int Index();
/**
* 字段在表头
* @return
*/
String title();
}