转自:http://merrygrass.iteye.com/blog/558274
1、注解
- package lml.excel.annotation;
- import java.lang.annotation.Retention;
- import java.lang.annotation.RetentionPolicy;
- /**
- * @author MerryGrass
- * 自定义注解
- */
- @Retention(RetentionPolicy.RUNTIME)
- public @interface PropertyAnnotation
- {
- /**
- * 属性名称描述 (默认值为 "Unknown")
- * @return String
- */
- String PropertyName() default "Unknown";
- /**
- * 属性索引键 (默认值为 "-1")
- * @return int
- */
- int PropertySortKey() default -1;
- }
2、接口
- /**
- *
- */
- package lml.excel.service;
- import java.util.List;
- public interface CreateExcel {
- /**
- * 创建一个sheet
- * @param sheetName
- * sheet名字
- */
- public void createSheet(String sheetName);
- /**
- * 创建一行
- */
- public void createRow();
- /**
- * 创建一个单元格
- * @param cellNum
- * 单元格所属位置
- */
- public void createCell(int cellNum);
- /**
- * 设置一个单元格内容
- * @param data
- * 单元格内容
- * @param cellNum
- * 单元格所属位置
- */
- public void setCell(String data, int cellNum);
- /**
- * 设置标题内容
- * @param title
- * 标题
- * @return CreateExcel
- */
- public CreateExcel setTitle(String title);
- /**
- * 设置表头内容
- * @return CreateExcel
- */
- public CreateExcel setHead();
- /**
- * 设置单元格内容
- * @param datas
- * 单元格数据
- * @return CreateExcel
- */
- public CreateExcel setCellData(List<?> datas) throws Exception;
- }
- /**
- *
- */
- package lml.excel.service;
- import java.util.List;
- public interface ExcelInfo {
- /**
- * 获取表头数据
- * @return String[] 表头数据所组成的数组
- */
- public List<String> getHeaders();
- /**
- * 获取有效属性所对应的值
- * @param obj
- * 对象
- * @param methodName
- * 方法名
- * @return String
- */
- public String getContent(Object obj, String methodName) throws Exception;
- }
- package lml.excel.service;
- import java.io.InputStream;
- import java.util.List;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- public interface ExcelService {
- /**
- * 获取Excel
- * @param clazz
- * 类名
- * @param datas
- * 数据
- * @param title
- * 标题
- * @return
- */
- public InputStream getExcelInputStream(String clazz, List<?> datas, String title,List<String> fieldsName) throws Exception;
- /**
- * 创建Excel
- * @param clazz
- * 类名
- * @param datas
- * 数据
- * @param title
- * 标题
- * @return
- */
- public HSSFWorkbook createExcel(String clazz, List<?> datas, String title,List<String> fieldsName) throws Exception;
- }
3、实现类
- package lml.excel.service.impl;
- import java.util.List;
- import lml.excel.service.CreateExcel;
- 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.Region;
- public class CreateExcelImpl implements CreateExcel {
- private HSSFWorkbook wb = null;
- private HSSFSheet sheet = null;
- private HSSFRow row = null;
- private HSSFCell cell = null;
- private ExcelInfoImpl excelInfo = null;
- private int rowNum = 0; //当前需要创建的行数,初始化为0
- /**
- * 初始化HSSFWorkbook,并默认创建一个sheet
- * @param className
- * 类的全路径
- */
- public CreateExcelImpl(String className,List<String> fieldsName){
- wb = new HSSFWorkbook();
- createSheet("sheet1");
- excelInfo = new ExcelInfoImpl(className,fieldsName);
- }
- /**
- * 创建一个单元格
- * @param cellNum
- * 单元格所属位置
- */
- public void createCell(int cellNum) {
- cell = row.createCell((short)cellNum);
- }
- /**
- * 创建一行,并把当前行加1
- */
- public void createRow() {
- row = sheet.createRow(rowNum);
- rowNum++;
- }
- /**
- * 创建一个sheet
- * @param sheetName
- * sheet名字
- */
- public void createSheet(String sheetName) {
- sheet = wb.createSheet(sheetName);
- }
- /**
- * 设置一个单元格内容
- * @param data
- * 单元格内容
- * @param cellNum
- * 单元格所属位置
- */
- public void setCell(String data, int cellNum) {
- createCell(cellNum);
- cell.setEncoding(HSSFCell.ENCODING_UTF_16);
- cell.setCellValue(data);
- }
- /**
- * 设置单元格内容
- * @param datas
- * 单元格数据
- * @return 当前对象
- */
- public CreateExcelImpl setCellData(List<?> datas) throws Exception{
- List<String> list = excelInfo.getPropertys();
- for(int j = 0; j < datas.size(); j++){
- Object obj = datas.get(j);
- createRow();
- for(int i = 0; i < list.size(); i++){
- setCell(excelInfo.getContent(obj, list.get(i)),i);
- }
- }
- return this;
- }
- /**
- * 设置表头内容
- * @return 当前对象
- */
- public CreateExcelImpl setHead() {
- List<String> datas = excelInfo.getPropertyNames();
- if(datas.size() == 0){
- datas = excelInfo.getHeaders();
- }
- createRow();
- for(int i = 0; i < datas.size(); i++){
- setCell((String)datas.get(i), i);
- }
- return this;
- }
- /**
- * 设置标题内容
- * @param title
- * 标题
- * @return 当前对象
- */
- public CreateExcelImpl setTitle(String title) {
- this.createRow();
- this.createCell(0);
- HSSFFont titlefont = wb.createFont();
- titlefont.setFontName("Arial");
- titlefont.setBoldweight(titlefont.BOLDWEIGHT_BOLD);
- titlefont.setFontHeight((short)400);
- HSSFCellStyle titleStyle = wb.createCellStyle();
- titleStyle.setFont(titlefont);
- titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
- cell.setCellStyle(titleStyle);
- cell.setEncoding(HSSFCell.ENCODING_UTF_16);
- cell.setCellValue(title);
- sheet.addMergedRegion(new Region(0, (short)0, 1, (short)(excelInfo.getNum()-1)));
- rowNum++;
- return this;
- }
- /**
- * 获取HSSFWorkbook
- * @return HSSFWorkbook
- */
- public HSSFWorkbook getWb() {
- return wb;
- }
- }
- package lml.excel.service.impl;
- import java.lang.reflect.Field;
- import java.lang.reflect.Method;
- import java.util.ArrayList;
- import java.util.HashMap;
- import java.util.List;
- import java.util.Map;
- import lml.excel.annotation.PropertyAnnotation;
- import lml.excel.service.ExcelInfo;
- /**
- * 获取相应类实例的有效信息
- *
- */
- @SuppressWarnings("unchecked")
- public class ExcelInfoImpl implements ExcelInfo {
- private Class clazz = null;
- //有效属性个数
- private int num = 0;
- //有效属性数组
- private List<String> propertys = new ArrayList<String>();
- //有效属性信息数组
- List<String> propertyNames = new ArrayList<String>();
- // 需导出字段
- List<String> fieldsName = new ArrayList<String>();
- //有效属性排序键
- // List<Integer> sortKey = new ArrayList<Integer>();
- /**
- * 得到类实例
- * @param className
- * 类的全路径
- */
- public ExcelInfoImpl(String className,List<String> fieldsName){
- this.fieldsName = fieldsName;
- try{
- clazz = Class.forName(className);
- }catch(Exception e){
- e.printStackTrace();
- }
- }
- /**
- * 获取有效属性所对应的值
- * @param methodName
- * 有效属性
- * @return String
- */
- @SuppressWarnings("unchecked")
- public String getContent(Object obj, String methodName) throws Exception {
- String realMethodName = "get" + methodName.substring(0,1).toUpperCase() + methodName.substring(1);
- Method method = clazz.getMethod(realMethodName, new Class[]{});
- if(null == method.invoke(obj, new Object[]{}) || "".equals(method.invoke(obj, new Object[]{}))){
- return "";
- }else{
- return method.invoke(obj, new Object[]{}).toString();
- }
- }
- /**
- * 获取表头数据
- * @return String[] 表头数据所组成的数组
- */
- @SuppressWarnings("unchecked")
- public List<String> getHeaders() {
- //得到类实例的所有属性
- Field[] fields = clazz.getDeclaredFields();
- //属性信息
- String propertyName = "";
- int PropertySortKey = 0;
- Map mapPropertys = new HashMap();
- Map mapPropertyNames = new HashMap();
- boolean flag = false;
- //通过循环比较得到有效属性
- if(0 != this.fieldsName.size()){
- for(int i = 0;i < fields.length;i++){
- if(null == fields[i].getAnnotation(PropertyAnnotation.class)){
- continue;
- }
- propertyName = fields[i].getAnnotation(PropertyAnnotation.class).PropertyName();
- for(String str : fieldsName){
- if(str.equals(propertyName)){
- propertys.add(fields[i].getName());
- propertyNames.add(propertyName);
- }
- }
- }
- }else{
- //通过循环比较得到有效属性
- for(int i = 0; i < fields.length; i++){
- //得到相应属性的信息
- if(null == fields[i].getAnnotation(PropertyAnnotation.class)){
- continue;
- }
- propertyName = fields[i].getAnnotation(PropertyAnnotation.class).PropertyName();
- PropertySortKey = fields[i].getAnnotation(PropertyAnnotation.class).PropertySortKey();
- //判断是否为有效属性
- if(null != propertyName){
- if(!flag){
- if(PropertySortKey == -1){
- if("Unknown".equals(propertyName)){
- propertys.add(fields[i].getName());
- propertyNames.add(fields[i].getName());
- }else{
- propertys.add(fields[i].getName());
- propertyNames.add(propertyName);
- }
- }else{
- flag = true;
- }
- }
- if(flag){
- if("Unknown".equals(propertyName)){
- mapPropertys.put(PropertySortKey, fields[i].getName());
- mapPropertyNames.put(PropertySortKey, fields[i].getName());
- }else{
- mapPropertys.put(PropertySortKey, fields[i].getName());
- mapPropertyNames.put(PropertySortKey, propertyName);
- }
- }
- }
- }
- if(flag){
- sortPropertys(mapPropertys,mapPropertyNames);
- }
- }
- return propertyNames;
- }
- //有效属性排序
- public void sortPropertys(Map mapPropertys, Map mapPropertyNames){
- for(int i = 1; i <= mapPropertys.size(); i++){
- propertys.add((String)mapPropertys.get(i));
- propertyNames.add((String)mapPropertyNames.get(i));
- }
- }
- /**
- * 获取有效属性数组
- * @return
- */
- public List<String> getPropertys() {
- return propertys;
- }
- /**
- * 获取有效属性信息
- * @return
- */
- public List<String> getPropertyNames() {
- return propertyNames;
- }
- /**
- * 获取有效属性个数
- * @return
- */
- public int getNum() {
- num = getPropertys().size();
- if(num == 0){
- getHeaders();
- num = getPropertys().size();
- }
- return num;
- }
- }
- package lml.excel.service.impl;
- import java.io.ByteArrayInputStream;
- import java.io.ByteArrayOutputStream;
- import java.io.IOException;
- import java.io.InputStream;
- import java.util.List;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- import lml.excel.service.ExcelService;
- /**
- * 创建并获取Excel的服务类
- *
- */
- public class ExcelServiceImpl implements ExcelService {
- /**
- * 创建Excel
- * @param clazz
- * 类名
- * @param datas
- * 数据
- * @param title
- * 标题
- * @return
- */
- public HSSFWorkbook createExcel(String clazz, List<?> datas, String title,List<String> fieldsName) throws Exception{
- CreateExcelImpl createExcel = new CreateExcelImpl(clazz,fieldsName);
- //创建标题、表头、以及单元格数据
- createExcel.setTitle(title).setHead().setCellData(datas);
- return createExcel.getWb();
- }
- /**
- * 获取Excel
- * @param clazz
- * 类名
- * @param datas
- * 数据
- * @param title
- * 标题
- * @return
- */
- public InputStream getExcelInputStream(String clazz, List<?> datas,
- String title,List<String> fieldsName) throws Exception {
- ByteArrayOutputStream os = new ByteArrayOutputStream();
- try
- {
- createExcel(clazz, datas, title,fieldsName).write(os);
- }
- catch (IOException e)
- {
- e.printStackTrace();
- }
- byte[] content = os.toByteArray();
- InputStream is = new ByteArrayInputStream(content);
- return is;
- }
- }
4、Demo Action
- package lml.sf.pojo.action;
- import java.io.InputStream;
- import java.io.UnsupportedEncodingException;
- import java.util.ArrayList;
- import java.util.List;
- import lml.base.BaseDAO;
- import lml.excel.service.impl.ExcelServiceImpl;
- import lml.sf.pojo.V_SF_STUINFO;
- import com.opensymphony.xwork2.ActionSupport;
- public class STUINFOAction extends ActionSupport {
- private String filename;
- private String Tempstr;
- private List<String> fieldsName = new ArrayList<String>();
- private InputStream downStream;
- public String getFilename() {
- try {
- filename = new String(filename.getBytes(),"ISO8859-1");
- } catch (UnsupportedEncodingException e) {
- e.printStackTrace();
- }
- return filename + ".xls";
- }
- public void setFilename(String filename) {
- this.filename = filename;
- }
- public List<String> getFieldsName() {
- return fieldsName;
- }
- public void setFieldsName(List<String> fieldsName) {
- this.fieldsName = fieldsName;
- }
- public InputStream getDownStream() throws Exception{
- return downStream;
- }
- public void setDownStream(InputStream downStream) {
- this.downStream = downStream;
- }
- public String getTempstr() {
- return Tempstr;
- }
- public void setTempstr(String tempstr) {
- Tempstr = tempstr;
- }
- public String initExcel(){
- return SUCCESS;
- }
- @Override
- public String execute() throws Exception {
- return "excelinit";
- }
- public String doexport() throws Exception{
- if(null != Tempstr && !"".equals(Tempstr)){
- String[] strArray = Tempstr.split(",");
- for(int i=0;i<strArray.length;i++){
- if(null != strArray[i] && !"".equals(strArray[i])){
- this.fieldsName.add(strArray[i]);
- }
- }
- this.filename = "收费学生信息";
- BaseDAO DAO = new BaseDAO();
- List<V_SF_STUINFO> datas = new ArrayList<V_SF_STUINFO>();
- datas = DAO.getAll(V_SF_STUINFO.class);
- ExcelServiceImpl excel = new ExcelServiceImpl();
- this.downStream = excel.getExcelInputStream("lml.sf.pojo.V_SF_STUINFO", datas, "收费学生信息导出Excel_Demo",fieldsName);
- return SUCCESS;
- }
- return ERROR;
- }
- public String ioexport() throws Exception{
- this.filename = "收费学生信息";
- BaseDAO DAO = new BaseDAO();
- List<V_SF_STUINFO> datas = new ArrayList<V_SF_STUINFO>();
- datas = DAO.getAll(V_SF_STUINFO.class);
- ExcelServiceImpl excel = new ExcelServiceImpl();
- this.downStream = excel.getExcelInputStream("lml.sf.pojo.V_SF_STUINFO", datas, "收费学生信息导出Excel_Demo",new ArrayList<String>());
- return SUCCESS;
- }
- }
5、struts.xml 配置
- <?xml version="1.0" encoding="UTF-8" ?>
- <!DOCTYPE struts PUBLIC
- "-//Apache Software Foundation//DTD Struts Configuration 2.0//EN"
- "http://struts.apache.org/dtds/struts-2.0.dtd">
- <struts>
- <constant name="struts.custom.il8n.resources" value="message"></constant>
- <constant name="struts.i18n.encoding" value="UTF-8"></constant>
- <package name="excel" extends="struts-default">
- <action name="Export" class="lml.sf.pojo.action.STUINFOAction">
- <result name="excelinit">/STUINFO.jsp</result>
- </action>
- <action name="Ioexport" class="lml.sf.pojo.action.STUINFOAction" method="doexport">
- <result name="success" type="stream">
- <param name="contentType">application/vnd.ms-excel</param>
- <param name="contentDisposition">attachment;filename=${filename}</param>
- <param name="inputName">downStream</param>
- </result>
- <result name="error">/STUINFO.jsp</result>
- </action>
- </package>
- </struts>
- package lml.sf.pojo;
- import lml.excel.annotation.PropertyAnnotation;
- /**
- * @author 报到收费学生信息视图
- * @hibernate.class table="V_SF_STUINFO"
- */
- public class V_SF_STUINFO {
- /**
- * 学生ID
- *
- * @hibernate.id generator-class="assigned"
- */
- @PropertyAnnotation(PropertyName="学生ID",PropertySortKey=1)
- private String STUID;
- /**
- * 学生姓名
- *
- * @hibernate.property
- */
- @PropertyAnnotation(PropertyName="学生姓名",PropertySortKey=2)
- private String XM;
- /**
- * 学生学号
- *
- * @hibernate.property
- */
- @PropertyAnnotation(PropertyName="学生学号",PropertySortKey=3)
- private String XH;
- /**
- * 学生性别
- *
- * @hibernate.property
- */
- @PropertyAnnotation(PropertyName="学生性别",PropertySortKey=4)
- private Integer XB;
- /**
- * 出生年月
- *
- * @hibernate.property
- */
- @PropertyAnnotation(PropertyName="出生年月",PropertySortKey=5)
- private String CSNY;
- /**
- * 籍贯
- *
- * @hibernate.property
- */
- @PropertyAnnotation(PropertyName="籍贯",PropertySortKey=6)
- private String JG;
- /**
- * 民族
- *
- * @hibernate.property
- */
- @PropertyAnnotation(PropertyName="民族",PropertySortKey=7)
- private String MZ;
- /**
- * 政治面貌
- *
- * @hibernate.property
- */
- @PropertyAnnotation(PropertyName="政治面貌",PropertySortKey=8)
- private String ZZMM;
- /**
- * 家庭住址
- *
- * @hibernate.property
- */
- @PropertyAnnotation(PropertyName="家庭住址",PropertySortKey=9)
- private String JTZZ;
- /**
- * 邮政编码
- *
- * @hibernate.property
- */
- @PropertyAnnotation(PropertyName="邮政编码",PropertySortKey=10)
- private String YZBM;
- /**
- * 个人电话
- *
- * @hibernate.property
- */
- @PropertyAnnotation(PropertyName="个人电话",PropertySortKey=11)
- private String GRDH;
- /**
- * 家庭电话
- *
- * @hibernate.property
- */
- @PropertyAnnotation(PropertyName="家庭电话",PropertySortKey=12)
- private String JTDH;
- /**
- * 是否走读
- *
- * @hibernate.property
- */
- @PropertyAnnotation(PropertyName="是否走读",PropertySortKey=13)
- private Integer ISZD = new Integer(0);
- /**
- * 年级代码
- *
- * @hibernate.property
- */
- @PropertyAnnotation(PropertyName="年级代码",PropertySortKey=14)
- private String NJDM;
- /**
- * 年级名称
- *
- * @hibernate.property
- */
- @PropertyAnnotation(PropertyName="年级名称",PropertySortKey=15)
- private String NJMC;
- /**
- * 所属班级ID
- *
- * @hibernate.property
- */
- @PropertyAnnotation(PropertyName="所属班级ID",PropertySortKey=16)
- private String BJDM;
- /**
- * 所属班级名字
- *
- * @hibernate.property
- */
- @PropertyAnnotation(PropertyName="所属班级名称",PropertySortKey=17)
- private String BJMC;
- /**
- * 所属专业ID
- *
- * @hibernate.property
- */
- @PropertyAnnotation(PropertyName="所属专业ID",PropertySortKey=18)
- private String ZYDM;
- /**
- * 所属专业名字
- *
- * @hibernate.property
- */
- @PropertyAnnotation(PropertyName="所属专业名字",PropertySortKey=19)
- private String ZYMC;
- public V_SF_STUINFO() {
- }
- public String getSTUID() {
- return STUID;
- }
- public void setSTUID(String stuid) {
- STUID = stuid;
- }
- public String getXM() {
- return XM;
- }
- public void setXM(String xm) {
- XM = xm;
- }
- public String getXH() {
- return XH;
- }
- public void setXH(String xh) {
- XH = xh;
- }
- public String getCSNY() {
- return CSNY;
- }
- public void setCSNY(String csny) {
- CSNY = csny;
- }
- public String getJG() {
- return JG;
- }
- public void setJG(String jg) {
- JG = jg;
- }
- public String getMZ() {
- return MZ;
- }
- public void setMZ(String mz) {
- MZ = mz;
- }
- public String getGRDH() {
- return GRDH;
- }
- public void setGRDH(String grdh) {
- GRDH = grdh;
- }
- public String getJTDH() {
- return JTDH;
- }
- public void setJTDH(String jtdh) {
- JTDH = jtdh;
- }
- public Integer getISZD() {
- if (ISZD == null) {
- return 0;
- } else {
- return ISZD;
- }
- }
- public void setISZD(Integer iszd) {
- ISZD = iszd;
- }
- public String getNJDM() {
- return NJDM;
- }
- public void setNJDM(String njdm) {
- NJDM = njdm;
- }
- public String getNJMC() {
- return NJMC;
- }
- public void setNJMC(String njmc) {
- NJMC = njmc;
- }
- public String getBJDM() {
- return BJDM;
- }
- public void setBJDM(String bjdm) {
- BJDM = bjdm;
- }
- public String getBJMC() {
- return BJMC;
- }
- public void setBJMC(String bjmc) {
- BJMC = bjmc;
- }
- public String getZYDM() {
- return ZYDM;
- }
- public void setZYDM(String zydm) {
- ZYDM = zydm;
- }
- public String getZYMC() {
- return ZYMC;
- }
- public void setZYMC(String zymc) {
- ZYMC = zymc;
- }
- public String getZZMM() {
- return ZZMM;
- }
- public void setZZMM(String zzmm) {
- ZZMM = zzmm;
- }
- public String getJTZZ() {
- return JTZZ;
- }
- public void setJTZZ(String jtzz) {
- JTZZ = jtzz;
- }
- public String getYZBM() {
- return YZBM;
- }
- public void setYZBM(String yzbm) {
- YZBM = yzbm;
- }
- public Integer getXB() {
- return XB;
- }
- public void setXB(Integer xb) {
- XB = xb;
- }
- }