最近公司项目需要把数据通过Excel形式展示给用户，由于第一次接触java 生成Excel，在网上找了很多资料，但是很多都不是很理想，自己设计了一个通过自定义注解把列名配置到实体类上，然后通过代码读取自定义注解作为列名，具体实现可以看一下代码（代码是实习时候写的，有点low，大家凑合着看吧，该功能已经上生产了，代码应该问题没什么问题了，就是不够优雅）
实体类
package com.ifp.business.common.utils;
import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
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.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
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.ss.util.CellRangeAddress;
import com.ifp.common.common.utils.ColumnName;
@SuppressWarnings("all")
public class CreateExcelTool {
private static HSSFWorkbook workbook = null;
private List listAll = new ArrayList();
private List<Map> listMap = null;
private Map mapTitle = new HashMap();
private static String type[] = {"long","int","double","float","short","byte","boolean","char"};
/**
* 判断文件是否存在.
* @param fileDir 文件路径
* @return
*/
public static boolean fileExist(String fileDir){
boolean flag = false;
File file = new File(fileDir);
flag = file.exists();
return flag;
}
/**
* 判断文件的sheet是否存在.
* @param fileDir 文件路径
* @param sheetName 表格索引名
* @return
*/
public static boolean sheetExist(String fileDir,String sheetName) throws Exception{
boolean flag = false;
File file = new File(fileDir);
if(file.exists()){ //文件存在
//创建workbook
try {
workbook = new HSSFWorkbook(new FileInputStream(file));
//添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
HSSFSheet sheet = workbook.getSheet(sheetName);
if(sheet!=null)
flag = true;
} catch (Exception e) {
throw e;
}
}else{ //文件不存在
flag = false;
}
return flag;
}
/**
* 创建新excel.
* @param fileDir excel的路径
* @param sheetName 要创建的表格索引
* @param list excel的第一行即列名
*/
public static void CreateExcelTitle(String fileDir,String sheetName,List list,String name) throws Exception{
//CreateExcelUtil.deleteExcel(fileDir);
System.out.println("Excel路径：----------------------------------------"+fileDir);
String path = fileDir.substring(0,fileDir.lastIndexOf("\\")+1);
if (getCurrentOS().equals("linux")) {
path = fileDir.substring(0,fileDir.lastIndexOf("/")+1);
}
File file=new File(path);
if(!file.exists()){
file.mkdirs();
}
HSSFSheet sheet1;
Map<String, String> sheetStyle = new HashMap<String, String>();
//判断文件是否存在
if(CreateExcelUtil.fileExist(fileDir)){
workbook = new HSSFWorkbook(new FileInputStream(fileDir));
//判断sheet页是否存在
if(CreateExcelUtil.sheetExist(fileDir, sheetName)){
sheet1 = workbook.getSheet(sheetName);
}else{
//添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
sheet1 = workbook.createSheet(sheetName);
}
}else{
workbook = new HSSFWorkbook();
//添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
sheet1 = workbook.createSheet(sheetName);
}
sheet1.autoSizeColumn(1);
sheet1.autoSizeColumn(1, true);
/**合并单元格
* 参数：起始行号，终止行号， 起始列号，终止列号
* 只有 该文件中不存在sheet页才会合并单元格
* */
sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, list.size() - 1));
sheet1.addMergedRegion(new CellRangeAddress(1, 1, 0, list.size() - 1));
SimpleDateFormat format = new SimpleDateFormat("yyyy年M月");
//新建文件
FileOutputStream out = null;
try {
HSSFFont fontName = workbook.createFont();
HSSFCellStyle cellStyleName = workbook.createCellStyle();
fontName.setFontHeightInPoints((short) 14); //字体高度
fontName.setFontName("宋体"); //字体
fontName.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗
cellStyleName.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyleName.setFont(fontName);
HSSFRow row1 = workbook.getSheet(sheetName).createRow(0);
row1.setHeight((short)450);
HSSFCell cell1 = row1.createCell(0);
cell1.setCellValue(name);
cell1.setCellStyle(cellStyleName);
//设置单元格为文本格式
cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
HSSFRow row2 = workbook.getSheet(sheetName).createRow(1);
HSSFCell cell2 = row2.createCell(0);
cell2.setCellValue("制表日期:"+format.format(new Date()));
//添加列名
HSSFRow row = workbook.getSheet(sheetName).createRow(2); //创建第一行
HSSFFont font = workbook.createFont();
HSSFCellStyle cellStyle = workbook.createCellStyle();
font.setFontHeightInPoints((short) 12); //字体高度
cellStyle.setFont(font);
for(short i = 0;i < list.size();i++){
HSSFCell cell = row.createCell(i);
//设置列宽
sheet1.setColumnWidth(i,list.get(i).toString().getBytes().length*256);
cell.setCellValue("" + list.get(i));
cell.setCellStyle(cellStyle);
}
out = new FileOutputStream(fileDir);
workbook.write(out);
} catch (Exception e) {
throw e;
} finally {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 删除文件.
* @param fileDir 文件路径
*/
public static boolean deleteExcel(String fileDir) {
boolean flag = false;
File file = new File(fileDir);
// 判断目录或文件是否存在
if (!file.exists()) { // 不存在返回 false
return flag;
} else {
// 判断是否为文件
if (file.isFile()) { // 为文件时调用删除文件方法
file.delete();
flag = true;
}
}
return flag;
}
/**
* 往excel中写入
* @param fileDir 文件路径
* @param sheetName 表格索引
* @param object
* @throws Exception
*/
public void writeToExcel(String fileDir,String sheetName,List<Map> mapList) throws Exception{
//创建workbook
File file = new File(fileDir);
try {
workbook = new HSSFWorkbook(new FileInputStream(file));
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
//流
FileOutputStream out = null;
HSSFSheet sheet = workbook.getSheet(sheetName);
HSSFFont font = workbook.createFont();
HSSFCellStyle cellStyle = workbook.createCellStyle();
font.setFontHeightInPoints((short) 12); //字体高度
cellStyle.setFont(font);
/*sheet.setDefaultColumnWidth(14);//设置默认列宽
sheet.setDefaultRowHeight((short) (6 * 256)); //设置默认行高，表示2个字符的高度*/
// 获取表格的总行数
// int rowCount = sheet.getLastRowNum() + 1; // 需要加一
// 获取表头的列数
int columnCount = sheet.getRow(2).getLastCellNum();
try {
// 获得表头行对象
HSSFRow titleRow = sheet.getRow(2);
if(titleRow!=null){
int rowId = 3;
int i = 0;
int rowsIndex = 0;
int row = 3;
boolean flag = false;
while(i<mapList.size()){
Map map = mapList.get(i);
HSSFRow newRow1=sheet.createRow(row);
for (short columnIndex = 0; columnIndex < columnCount; columnIndex++) { //遍历表头
String titleName = titleRow.getCell(columnIndex).toString().trim().toString().trim();
String mapKey = (String) mapTitle.get(titleName);
List list = new ArrayList();
if(map.get(mapKey)!=null&&map.get(mapKey).getClass().equals(list.getClass())){
flag = true;
list = (List) map.get(mapKey);
int j = 0;
HSSFRow newRow;
for(int listIndex = 0;listIndex<list.size();listIndex++){
if(sheet.getRow(rowId + j)!=null){
newRow = sheet.getRow(rowId+j);
}else{
newRow=sheet.createRow(rowId+j);
}
newRow.setHeightInPoints(50);
HSSFCell cell = newRow.createCell(columnIndex);
//设置列宽
sheet.setColumnWidth(i,list.get(listIndex).toString().getBytes().length*256);
cell.setCellValue(list.get(listIndex)==null ? null : list.get(listIndex).toString());
setStyle(cell,font);
j++;
rowsIndex = j;
}
}else{
try{
HSSFCell cell = newRow1.createCell(columnIndex);
//判断字符串长度是否大于列宽，大于列宽，从新设置列宽
if(!"".equals(map.get(mapKey))&&map.get(mapKey)!=null){
//判断是否是中文
if(isContainChinese(map.get(mapKey).toString())&&sheet.getColumnWidth(columnIndex)<map.get(mapKey).toString().getBytes().length*256){
sheet.setColumnWidth(columnIndex,map.get(mapKey).toString().getBytes().length*256);
}else if(!isContainChinese(map.get(mapKey).toString())&&sheet.getColumnWidth(columnIndex)<map.get(mapKey).toString().getBytes().length*512){
sheet.setColumnWidth(columnIndex,map.get(mapKey).toString().getBytes().length*512);
}
}
cell.setCellValue(map.get(mapKey)==null ? null : map.get(mapKey).toString());
/*setStyle(cell,font);*/
cell.setCellStyle(cellStyle);
}catch(Exception e){
System.out.println("------------------------------------空---------------------------");
e.printStackTrace();
}
}
}
if(flag){
row = row+rowsIndex;
}else{
row = row+rowsIndex + 1;
}
rowId = row;
i++;
}
}
out = new FileOutputStream(fileDir);
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 将一个 JavaBean 对象转化为一个 Map
* @param bean 要转化的JavaBean 对象
* @return 转化出来的 Map 对象
* @throws ClassNotFoundException
* @throws InstantiationException
* @throws IntrospectionException 如果分析类属性失败
* @throws IllegalAccessException 如果实例化 JavaBean 失败
* @throws InvocationTargetException 如果调用属性的 setter 方法失败
*/
public static Map toMap(Object bean) throws ClassNotFoundException, InstantiationException {
Map<Object, Object> returnMap = new HashMap<Object, Object>();
Class clazz = null;
if(!bean.toString().contains("@")){
try {
clazz = Class.forName(bean + "");
bean = clazz.newInstance();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}else{
clazz = bean.getClass();
}
StringBuffer sb = new StringBuffer();
BeanInfo beanInfo = null;
try {
//得到的BeanInfo对象封装了把这个类当做JavaBean看的结果信息，即属性的信息
beanInfo = Introspector.getBeanInfo(clazz);
//获取 clazz 类型中的 propertyName 的属性描述器
PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
for (int i = 0; i < propertyDescriptors.length; i++) {
PropertyDescriptor descriptor = propertyDescriptors[i];
if("class".equals(descriptor.getName())){
continue;
}
String propertyType = descriptor.getPropertyType().toString();
boolean boo = com.ifp.common.common.utils.StringUtil.isInList(propertyType, com.ifp.common.common.utils.StringUtil.arrayToList(type));
if(propertyType.contains("java.util.List")){
try {
Method readMethod = descriptor.getReadMethod();
List result = null;
//调用 set 方法将传入的value值保存属性中去
result = (List) readMethod.invoke(bean, new Object[0]);
for(Object o:result){
toMap(o);
}
} catch (SecurityException e) {
e.printStackTrace();
}
continue;
}else if(propertyType.contains("lang") || boo){
String propertyName = descriptor.getName();
if (!propertyName.equals("class")) {
Method readMethod = descriptor.getReadMethod();
Object result = null;
//调用 set 方法将传入的value值保存属性中去
result = readMethod.invoke(bean, new Object[0]);
if (null != result) {
if(returnMap!=null&&returnMap.containsKey(propertyName)){
List list1 = new ArrayList();
List list2 = new ArrayList();
list1.add(result);
if(!returnMap.get(propertyName).getClass().equals(list2.getClass())){
list2.add(returnMap.get(propertyName));
}else{
list2 = (List) returnMap.get(propertyName);
}
list1.addAll(list2);
result = list1;
}else{
result = result.toString();
}
}
returnMap.put(propertyName, result);
}
}else{
Object obj = propertyType.substring(propertyType.indexOf(" ") + 1);
Method readMethod = descriptor.getReadMethod();
Object result = null;
//调用 set 方法将传入的value值保存属性中去
result = readMethod.invoke(bean, new Object[0]);
toMap(result);
continue;
}
}
} catch (IntrospectionException e) {
System.out.println("分析类属性失败");
} catch (IllegalAccessException e) {
System.out.println("实例化 JavaBean 失败");
} catch (IllegalArgumentException e) {
System.out.println("映射错误");
} catch (InvocationTargetException e) {
System.out.println("调用属性的 setter 方法失败");
}
return returnMap;
}
/**
* 获取父类和当前类所有属性（包括private）
* @param object
* @return
*/
public static List getAllFields(Object object){
Class clazz = object.getClass();
List fieldList = new ArrayList();
while (clazz != null){
for (Field field : clazz.getDeclaredFields()) {
if (field.getGenericType().toString().contains("java.util.List")){
Object obj = (Object)field.toString().substring(field.toString().lastIndexOf(".")+1);
Class clazzList = obj.getClass();
field.setAccessible(true);
String fieldName = field.getName();
fieldList.add(fieldName);
}
}
clazz = clazz.getSuperclass();
}
return fieldList;
}
/**
* 获得传入对象的所有属性名
* @param object
* @return
*/
public List getFields(Object object){
Class cla = null;
if(!object.toString().contains("@")){
try {
cla = Class.forName(object + "");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}else{
cla = object.getClass();
}
for(Field field : cla.getDeclaredFields()){
boolean boo = com.ifp.common.common.utils.StringUtil.isInList(field.getGenericType().toString(), com.ifp.common.common.utils.StringUtil.arrayToList(type));
//判断该字段的声明类型
if(field.getGenericType().toString().contains("java.util.List")){
Object obj = (Object)(field.getGenericType().toString().substring(field.getGenericType().toString().indexOf("<")+1,field.getGenericType().toString().indexOf(">")));
getFields(obj);
continue;
}else if(field.getGenericType().toString().contains("lang") || boo){
field.setAccessible(true);
//获取自定义注解
ColumnName anno = field.getAnnotation(ColumnName.class);
String fieldName = field.getName();
String annoValue = null;
if(anno == null){
/*annoValue = fieldName;
mapTitle.put(fieldName, fieldName);*/
//如果该字段没有注解，直接跳过，表格中不会显示此字段
continue;
}else{
annoValue = anno.value();
mapTitle.put(anno.value(),fieldName);
}
listAll.add(annoValue);
}else{
Object obj = field.getGenericType().toString().substring(field.getGenericType().toString().indexOf(" ") + 1);
getFields(obj);
continue;
}
}
return listAll;
}
/**
* 判断字符串是否包含中文
* @param str
* @return
*/
public static boolean isContainChinese(String str) {
Pattern p = Pattern.compile("[\u4e00-\u9fa5]");
Matcher m = p.matcher(str);
if (m.find()) {
return true;
}
return false;
}
//设置字体样式
public static void setStyle(HSSFCell cell,HSSFFont font){
// 设置字体
font.setFontHeightInPoints((short) 12); //字体高度
//font.setColor(HSSFFont.COLOR_RED); //字体颜色
//font.setFontName("楷体"); //字体
//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //宽度
//font.setItalic(true); //是否使用斜体
//font.setStrikeout(true); //是否使用划线
// 设置单元格类型
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(font);
// cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中
//cellStyle.setWrapText(true);
cell.setCellStyle(cellStyle);
}
public static String getCurrentOS() {
String system = "";
int OS = System.getProperty("os.name").toUpperCase().indexOf("WINDOWS");
if (OS != -1) {
system = "windows";
} else
system = "linux";
return system;
}
/**
*
* @param fileDir
* @param sheetName sheet页名字
* @param mapList
* @throws Exception
*/
public void createExcel(String fileDir,String sheetName,List<Object> list, String name ) throws Exception{
listMap = new ArrayList();
List titleList = new ArrayList();
CreateExcelTool CreateExcelTool = new CreateExcelTool();
for(Object obj : list){
listMap.add(toMap(obj));
}
//System.out.println(listMap);
titleList = CreateExcelTool.getFields(list.get(0));
System.out.println("表头："+titleList);
CreateExcelTool.CreateExcelTitle(fileDir, sheetName, titleList,name);
CreateExcelTool.writeToExcel(fileDir, sheetName,listMap);
System.out.println("---------------------------------------生成表格成功-------------------------------------");
}
public static void main(String[] args) throws Exception {
List<Object> list=new ArrayList<Object>();
List<Object> list2=new ArrayList<Object>();
Map<String,String> map=new HashMap<String,String>();
/* for(int i = 0;i<3;i++){
Person p = new Person();
p.setName("2018-01-16 00:00:00.0"+i);
p.setAge("2018-01-16 00:00:00.0");
p.setSex("2018-01-16 00:00:00.0"+i);
list.add(p);
}
for(int i = 0;i<3;i++){
person2 p = new person2();
p.setName("list2少时诵诗书所所所所所所所所所所所所所所"+i);
list2.add(p);
}*/
CreateExcelTool CreateExcelTool = new CreateExcelTool();
String path ="E:/excel/123456778.xls";
CreateExcelTool.createExcel(path, "sheet1", list, "测试1");
CreateExcelTool.createExcel(path, "sheet2", list2, "测试2");
}
}
自定义注解
package com.ifp.common.common.utils;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.ElementType;
import java.lang.annotation.Target;
/**
* 自定义注解，程序可以读取注解values(该程序主要用于生成表格时，通过读取类属性中配置的注解，生产表格列名)
* @author cdy
* @Retention(RetentionPolicy.RUNTIME) 表示该注解在jvm运行时存在
*@Target(ElementType.FIELD) 只可以修饰属性
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ColumnName {
String value();
}
4109
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
