本文主要是介绍封装JAVA 通过反射 POI导出Excel文件(直接可用 含测试代码),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
啥也不说了 直接上代码:
Student.java主要测试使用的
package cn.sup.cd.test;import java.util.Date;public class Student {private int id;private String name;private String sex;private int age;private Date birth;public Student() {}public Student(int id, String name, int age,String sex, Date birth) {this.id = id;this.name = name;this.age = age;this.sex = sex;this.birth = birth;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public int getAge() {return age;}public void setAge(int age) {this.age = age;}public Date getBirth() {return birth;}public void setBirth(Date birth) {this.birth = birth;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}}
封装列名与属性名对应类:
package cn.sup.cd.common.util;public class ColnameToField {private String colname;//列名private String fieldName;//类属性名public ColnameToField(String fieldName, String colname){this.colname = colname;this.fieldName = fieldName;}public String getColname() {return colname;}public void setColname(String colname) {this.colname = colname;}public String getFieldName() {return fieldName;}public void setFieldName(String fieldName) {this.fieldName = fieldName;}}
下面是单元测试代码:
到时间可根据自己的情况定义
List<Student> students = null;SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd");@Beforepublic void before() {try {Student student1 = new Student(1, "小李", 20, "男", df.parse("1992-12-30"));Student student2 = new Student(2, "小张", 24, "男", df.parse("1945-02-30"));Student student3 = new Student(3, "小孙", 25, "男", df.parse("1988-11-20"));Student student4 = new Student(4, "小王", 21, "男", df.parse("1992-05-30"));Student student5 = new Student(5, "小刘", 30, "男", df.parse("2012-08-30"));Student student6 = new Student(6, "小单", 28, "男", df.parse("1992-12-30"));Student student7 = new Student(7, "小红", 22, "男", df.parse("1560-10-30"));Student student8 = new Student(8, "小明", 29, "男", df.parse("2001-07-30"));students = new ArrayList<Student>();students.add(student1);students.add(student2);students.add(student3);students.add(student4);students.add(student5);students.add(student6);students.add(student7);students.add(student8);} catch (Exception e) {}}@Testpublic void test2() throws FileNotFoundException {// 获取xml数据存放位置// String outputPath =// ServletActionContext.getServletContext().getRealPath("/");String title = "学生表";String fileName = "学生表.xls";String path = "d:\\aaaaa";StudentExportExcel excel = new StudentExportExcel(students, path,fileName, title);excel.exportExcel();}
下面是比较主要代码了.......... 主要按自己的业务封装一些导出文件的信息
package cn.sup.cd.common.exceExport;import java.util.ArrayList;
import java.util.Collection;
import java.util.List;import cn.sup.cd.common.util.ColnameToField;
import cn.sup.cd.test.Student;@SuppressWarnings("serial")
public class StudentExportExcel extends ExportExcel<Student> {/*** * @param dataset数据源* @param path* 导出路径* @param fileName* 导出文件名称* @param title* 文件标题*/public StudentExportExcel(Collection<Student> dataset, String path,String fileName, String title) {super(dataset, path);/* 设置Excel基本数据 */// 定义列名与类属性名的对应关系//List<ColnameToField> colNameToField = new ArrayList<ColnameToField>() {{add(new ColnameToField("id", "学号"));add(new ColnameToField("name", "姓名"));add(new ColnameToField("age", "年龄"));add(new ColnameToField("sex", "性别"));add(new ColnameToField("birth", "出生日期"));}};super.setTitle(title);// 标题,以查询的起止日期作为标题super.setFileName(fileName);// Excel文件名(以站点名作为文件名)super.setDateFormat("yyyy-MM-dd HH:mm:ss");// 设置列名对应的属性名集合(其顺序与表头列名属性一致)super.setColNameToField(colNameToField);}
}
最核心代码如下所示:。
package cn.sup.cd.common.exceExport;import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
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.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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;import cn.sup.cd.common.util.ColnameToField;/*** 利用开源组件POI3.0.2动态导出EXCEL文档 转载时请保留以下信息,注明出处!* * @author TL* @version v1.0* @param <T>* 应用泛型,代表任意一个符合javabean风格的类* 注意这里为了简单起见,boolean型的属性xxx的get器方式为getXxx(),而不是isXxx()* byte[]表jpg格式的图片数据*/
public class ExportExcel<T> {/*** xls文件存放路径*/public static final String EXCEL_FILE_PATH = "xls";/*** xls数据映射类*/private Class<T> classType;private Collection<T> dataset;private String fileName;// 文件名private String title;// 文档标题private String dateFormat = "yyyy-MM-dd";// 默认日期格式private String path;// 项目运行物理路径private List<ColnameToField> colNameToField;// 列名与类属性名的对应关系private Map<String, Map<String, String>> colValueToChange;// 将列值转换为另外的值/*** 构造方法,初始化数据* * @param dataset* 数据集合* @param path* Tomcat项目运行路径*/@SuppressWarnings("unchecked")public ExportExcel(Collection<T> dataset, String path) {try {// 获取泛型的实际类型Type type = this.getClass().getGenericSuperclass();if (type instanceof ParameterizedType) {// 转换成泛型类ParameterizedType pType = (ParameterizedType) type;this.classType = (Class<T>) pType.getActualTypeArguments()[0];}this.path = path;this.dataset = dataset;} catch (Exception e) {e.printStackTrace();}}/*** 生成Excel文件*/public void exportExcel() {File file = new File(path + fileName);if (!file.exists()) {try {file.createNewFile();} catch (IOException e) {e.printStackTrace();}}// 获取文件输出流OutputStream out = null;try {out = new FileOutputStream(path + fileName);} catch (FileNotFoundException e) {e.printStackTrace();return;}// 设置表格标题行的列名数组并生成ExcelString[] header = new String[colNameToField.size()];int index = 0;for (ColnameToField colField : colNameToField) {header[index] = colField.getColname();index++;}//导出exportExcel(title, header, out, dateFormat);}/*** 这是一个通用的方法,利用了JAVA的反射机制,可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上* * @param title* 表格标题名* @param headers* 表格属性列名数组* @param out* 与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中* @param pattern* 如果有时间数据,设定输出格式。默认为"yyy-MM-dd"*/@SuppressWarnings("unused")private void exportExcel(String title, String[] headers, OutputStream out,String pattern) {// 声明一个工作薄HSSFWorkbook workbook = new HSSFWorkbook();// 生成一个表格HSSFSheet sheet = workbook.createSheet(title);// 设置表格默认列宽度为15个字节sheet.setDefaultColumnWidth((short) 15);// 生成一个样式HSSFCellStyle style = workbook.createCellStyle();// 设置这些样式style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);style.setBorderBottom(HSSFCellStyle.BORDER_THIN);style.setBorderLeft(HSSFCellStyle.BORDER_THIN);style.setBorderRight(HSSFCellStyle.BORDER_THIN);style.setBorderTop(HSSFCellStyle.BORDER_THIN);style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 生成一个字体HSSFFont font = workbook.createFont();font.setColor(HSSFColor.VIOLET.index);font.setFontHeightInPoints((short) 12);font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 把字体应用到当前的样式style.setFont(font);// 生成并设置另一个样式HSSFCellStyle style2 = workbook.createCellStyle();style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);style2.setBorderRight(HSSFCellStyle.BORDER_THIN);style2.setBorderTop(HSSFCellStyle.BORDER_THIN);style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 生成另一个字体HSSFFont font2 = workbook.createFont();font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);// 把字体应用到当前的样式style2.setFont(font2);// 声明一个画图的顶级管理器HSSFPatriarch patriarch = sheet.createDrawingPatriarch();// 定义注释的大小和位置,详见文档HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,0, 0, 0, (short) 4, 2, (short) 6, 5));// 设置注释内容comment.setString(new HSSFRichTextString("可以在POI中添加注释!"));// 设置注释作者,当鼠标移动到单元格上是可以在状态栏中看到该内容.comment.setAuthor("leno");// 产生表格标题行HSSFRow row = sheet.createRow(0);for (short i = 0; i < headers.length; i++) {HSSFCell cell = row.createCell(i);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(headers[i]);cell.setCellValue(text);}// 遍历集合数据,产生数据行Iterator<T> it = dataset.iterator();int index = 0;while (it.hasNext()) {index++;row = sheet.createRow(index);T t = (T) it.next();// 取出每个对象// 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值short cellIndex = 0;// Excel列索引for (ColnameToField colField : colNameToField) {String fieldName = colField.getFieldName();// 取属性名HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style2);String getMethodName = "get"+ fieldName.substring(0, 1).toUpperCase()+ fieldName.substring(1);try {// Class tCls = classType.getClass();Method getMethod = classType.getMethod(getMethodName,new Class[] {});Object value = getMethod.invoke(t, new Object[] {});// 判断值的类型后进行强制类型转换String textValue = null;// if (value instanceof Integer) {// int intValue = (Integer) value;// cell.setCellValue(intValue);// } else if (value instanceof Float) {// float fValue = (Float) value;// textValue = new HSSFRichTextString(// String.valueOf(fValue));// cell.setCellValue(textValue);// } else if (value instanceof Double) {// double dValue = (Double) value;// textValue = new HSSFRichTextString(// String.valueOf(dValue));// cell.setCellValue(textValue);// } else if (value instanceof Long) {// long longValue = (Long) value;// cell.setCellValue(longValue);// }if (value == null) {textValue = "";} else if (value instanceof Boolean) {/** boolean bValue = (Boolean) value; textValue = "男"; if* (!bValue) { textValue = "女"; }*/} else if (value instanceof Date) {Date date = (Date) value;SimpleDateFormat sdf = new SimpleDateFormat(pattern);textValue = sdf.format(date);} else if (value instanceof byte[]) {// 有图片时,设置行高为60px;row.setHeightInPoints(60);// 设置图片所在列宽度为80px,注意这里单位的一个换算sheet.setColumnWidth(cellIndex, (short) (35.7 * 80));// sheet.autoSizeColumn(i);byte[] bsValue = (byte[]) value;HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,1023, 255, (short) 6, index, (short) 6, index);anchor.setAnchorType(2);patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));} else {// 其它数据类型都当作字符串简单处理textValue = value.toString();// 判断属性值是否需要转换为其他的内容if (this.colValueToChange != null) {// 取出属性值转换MapMap<String, String> changeMap = colValueToChange.get(fieldName);if (changeMap != null && changeMap.size() > 0) {textValue = changeMap.get(textValue);}}}// 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成if (textValue != null) {Pattern p = Pattern.compile("^//d+(//.//d+)?$");Matcher matcher = p.matcher(textValue);if (matcher.matches()) {// 是数字当作double处理cell.setCellValue(Double.parseDouble(textValue));} else {HSSFRichTextString richString = new HSSFRichTextString(textValue);HSSFFont font3 = workbook.createFont();font3.setColor(HSSFColor.BLUE.index);richString.applyFont(font3);cell.setCellValue(richString);}}} 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();} finally {// 清理资源cellIndex++;}}}try {workbook.write(out);} catch (IOException e) {e.printStackTrace();} finally {if (out != null) {try {out.close();} catch (IOException e) {e.printStackTrace();}}}}/*** 设置日期格式* * @param dateFormat*/public void setDateFormat(String dateFormat) {this.dateFormat = dateFormat;}public void setFileName(String fileName) {this.fileName = fileName;}public void setTitle(String title) {this.title = title;}public void setColNameToField(List<ColnameToField> colNameToField) {this.colNameToField = colNameToField;}public void setColValueToChange(Map<String, Map<String, String>> colValueToChange) {this.colValueToChange = colValueToChange;}
}
效果图:
这篇关于封装JAVA 通过反射 POI导出Excel文件(直接可用 含测试代码)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!