POI导出Excel公用方法（通过配置xml文件）

基于springBoot的POI导出excel通用方法

介绍：

该方法是根据xml配置内容去导出传入的对象集合，导出需要导出的数据不同对象配置不同的xml。

pom.xml配置

<!-- 继承默认值为Spring Boot  -->
	<parent> 
		<groupId> org.springframework.boot </groupId> 
		<artifactId> spring-boot-starter-parent </artifactId> 
		<version> 2.0.4.RELEASE </version> 
	</parent>

	<!-- 添加Web应用程序的典型依赖项 -->
	<dependencies> 
		<dependency> 
			<groupId> org.springframework.boot </groupId> 
			<artifactId> spring-boot-starter-web </artifactId> 
		</dependency> 
		
		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi</artifactId>
		    <version>3.14</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi-ooxml</artifactId>
		    <version>3.14</version>
		</dependency>
	
		<dependency>
			<groupId>jdom</groupId>
			<artifactId>jdom</artifactId>
			<version>1.1</version>
		</dependency>
	</dependencies>

	<!-- 作为可执行jar的包  -->
	<build> 
		<plugins> 
			<plugin> 
				<groupId> org.springframework.boot </groupId> 
				<artifactId> spring-boot-maven-plugin </artifactId> 
			</plugin > 
		</plugins> 
	</build>

读取XML文件的工具类

xml对应的bean

package com.utils.xml.bean;

import javax.xml.bind.annotation.XmlAccessType;
import javax.xml.bind.annotation.XmlAccessorType;
import javax.xml.bind.annotation.XmlElement;

@XmlAccessorType(XmlAccessType.FIELD)
public class FieldSetting {
	@XmlElement(name="fieldCnName")
	private String fieldCnName;//中文名
	@XmlElement(name="fieldDbName")
	private String fieldDbName;//数据库对应字段
	@XmlElement(name="fieldWidth")
	private Integer fieldWidth;//单元格宽度，0-200 不写默认自适应
	public String getFieldCnName() {
		return fieldCnName;
	}
	public void setFieldCnName(String fieldCnName) {
		this.fieldCnName = fieldCnName;
	}
	public String getFieldDbName() {
		return fieldDbName;
	}
	public void setFieldDbName(String fieldDbName) {
		this.fieldDbName = fieldDbName;
	}
	public Integer getFieldWidth() {
		return fieldWidth;
	}
	public void setFieldWidth(Integer fieldWidth) {
		this.fieldWidth = fieldWidth;
	}
	@Override
	public String toString() {
		return "FieldSetting [fieldCnName=" + fieldCnName + ", fieldDbName=" + fieldDbName + ", fieldWidth="
				+ fieldWidth + "]";
	}
	
}

package com.utils.xml.bean;

import java.util.List;

import javax.xml.bind.annotation.XmlAccessType;
import javax.xml.bind.annotation.XmlAccessorType;
import javax.xml.bind.annotation.XmlElement;
import javax.xml.bind.annotation.XmlElementWrapper;
import javax.xml.bind.annotation.XmlRootElement;

@XmlAccessorType(XmlAccessType.FIELD)
@XmlRootElement(name="FieldsSettings")
public class FieldsSettings {
	
	@XmlElementWrapper(name="Settings")
	@XmlElement(name = "fieldSetting")  
	private List<FieldSetting> fieldSetting;

	
	public List<FieldSetting> getFieldSetting() {
		return fieldSetting;
	}


	public void setFieldSetting(List<FieldSetting> fieldSetting) {
		this.fieldSetting = fieldSetting;
	}


	@Override
	public String toString() {
		return "FieldsSettings [fieldSetting=" + fieldSetting + "]";
	}

}

xml转换成bean的方法

package com.utils.xml;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.StringReader;
import java.io.StringWriter;
import java.util.List;

import javax.xml.bind.JAXBContext;
import javax.xml.bind.Marshaller;
import javax.xml.bind.Unmarshaller;

import org.jdom.Document;
import org.jdom.input.SAXBuilder;
import org.jdom.output.Format;
import org.jdom.output.XMLOutputter;

import com.utils.xml.bean.FieldsSettings;

/*
 * 使用JAXB 对xml和javabean进行转换
 */
public class JaxbUtil {

	/**
	 * JavaBean转换成xml 默认编码UTF-8
	 * @param obj
	 * @return
	 */
	public static String convertToXml(Object obj) {
		return convertToXml(obj, "UTF-8");
	}

	/**
	 * JavaBean转换成xml
	 * @param obj
	 * @param encoding
	 * @return
	 */
	public static String convertToXml(Object obj, String encoding) {
		StringBuffer result = new StringBuffer();
		try {
			if(obj instanceof List){
				@SuppressWarnings("rawtypes")
				List list = (List)obj;
				for(Object o:list){
					result.append(toConvertToXml(o, encoding));
				}
			}else{
				result.append(toConvertToXml(obj, encoding));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result.toString();
	}
	
	public static String toConvertToXml(Object obj, String encoding) {
		String result = "";
		try {
			JAXBContext context = JAXBContext.newInstance(obj.getClass());
			Marshaller marshaller = context.createMarshaller();
			//决定是否在转换成xml时同时进行格式化（即按标签自动换行，否则即是一行的xml）
			marshaller.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, true);
			//xml的编码方式
			marshaller.setProperty(Marshaller.JAXB_ENCODING, encoding);

			StringWriter writer = new StringWriter();
			marshaller.marshal(obj, writer);
			return writer.toString();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	/**
	 * xml转换成JavaBean
	 * @param xml
	 * @param c
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static <T> T converyToJavaBean(String xml, Class<T> c) {
		T t = null;
		try {
			JAXBContext context = JAXBContext.newInstance(c);
			Unmarshaller unmarshaller = context.createUnmarshaller();
			t = (T) unmarshaller.unmarshal(new StringReader(xml));
		} catch (Exception e) {
			e.printStackTrace();
		}
		return t;
	}
	/**
	 * 根据流读取
	 * @param inputStream
	 * @param c
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static <T> T converyToJavaBean(InputStream inputStream, Class<T> c) {
		T t = null;
		try {
			JAXBContext context = JAXBContext.newInstance(c);
			Unmarshaller unmarshaller = context.createUnmarshaller();
			t = (T) unmarshaller.unmarshal(inputStream);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return t;
	}

	public static String xmlToStr(String url) {
		Document document = null;
		try {
			SAXBuilder reader = new SAXBuilder();
			document = reader.build(new File(url));
		} catch (Exception e) {
			e.printStackTrace();
		}
		Format format = Format.getPrettyFormat();
		format.setEncoding("UTF-8");// 设置编码格式
		StringWriter out = new StringWriter(); // 输出对象
		XMLOutputter outputter = new XMLOutputter();
		try {
			outputter.output(document, out);
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		return out.toString();
	}
	
	public static void main(String args[]){
    	
    	String xml = xmlToStr(JaxbUtil.class.getResource("/").getPath()+"1.xml");
    	
    	System.out.println(xml);
    	
    	FieldsSettings converyToJavaBean = JaxbUtil.converyToJavaBean(xml, FieldsSettings.class);
    	System.out.println(converyToJavaBean);
    }
}

excel导出方法

package com.utils;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
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.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.utils.xml.bean.FieldSetting;

public class ExcelUtil<T> {
	/**
	 * 导出
	 * @param title 导出excel的文件名字
	 * @param headers excel中内容的标题
	 * @param fields xml中配置要显示的属性对象
	 * @param dataset 要导出的数据
	 * @param out 输出流（文件输出位置）
	 * @param pattern 日期的格式
	 */
	public void exportExcel(String title, String[] headers,List<FieldSetting> fields,Collection<T> dataset, OutputStream out, String pattern) {
		// 声明一个工作薄  
        HSSFWorkbook workbook = new HSSFWorkbook(); 
		try {
	        // 生成一个表格  
	        HSSFSheet sheet = workbook.createSheet(title);
	        
	        //-----------标题的样式
	        // 生成一个样式  
	        HSSFCellStyle style = workbook.createCellStyle();  
	        // 设置这些样式  
	        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中
	        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中
	        // 生成一个字体  
	        HSSFFont font = workbook.createFont(); 
	        font.setFontHeightInPoints((short) 12);  
	        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
	        // 把字体应用到当前的样式  
	        style.setFont(font);  
	        
	        
	        //------------内容的样式
	        // 生成并设置另一个样式  
	        HSSFCellStyle style2 = workbook.createCellStyle();  
	        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中
	        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中
	        // 生成另一个字体  
	        HSSFFont font2 = workbook.createFont();  
	        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);  
	        // 把字体应用到当前的样式  
	        style2.setFont(font2); 
	        style2.setWrapText(true);
	        
	        // 产生表格标题行  
	        HSSFRow row = sheet.createRow(0);  
	        for (int 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()方法得到属性值  
	            for (int i = 0; i < fields.size(); i++) {
	            	// 创建单元格
	            	HSSFCell cell = row.createCell(i);
	            	cell.setCellStyle(style2);//设置单元格样式
	            	cell.setCellType(HSSFCell.CELL_TYPE_STRING);//指定单元格格式：数值、公式或字符串
	            	FieldSetting field = fields.get(i);
	            	//字段名
	            	String fieldName = field.getFieldDbName();
	            	//如果设置宽度了，则取配置的值
	            	if(field.getFieldWidth()!=null&&!field.getFieldWidth().equals(0)) {
	            		sheet.setColumnWidth(i, field.getFieldWidth() * 256);
	            	}else {
	            		//设置单元格自适应宽度
	    	            sheet.autoSizeColumn(i);
	            	}
	            	//拼接的方法名
	            	String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);  
	            	@SuppressWarnings("rawtypes")
					Class tCls = t.getClass();  
	                @SuppressWarnings("unchecked")
					Method getMethod = tCls.getMethod(getMethodName, new Class[]{});
					Object value = getMethod.invoke(t, new Object[]{}); 
					// 判断值的类型后进行强制类型转换
                    String textValue = null;
                    if (value instanceof Date) {
                        Date date = (Date) value;
                        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                        textValue = sdf.format(date);
                    } else {
                        // 其它数据类型都当作字符串简单处理
                        if (value == null) {
                            value = "";
                        }
                        textValue = value.toString();
                    }
                    if (textValue != null) {
                        Pattern p = Pattern.compile("^//d+(//.//d+)?{1}quot;");
                        Matcher matcher = p.matcher(textValue);
                        if (matcher.matches()) {
                            // 是数字当作double处理
                            cell.setCellValue(Double.parseDouble(textValue));
                        } else {
                            cell.setCellValue(textValue);
                        }
                    }
	            }
	        }
	        out.flush();
	        workbook.write(out);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				workbook.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
			if(out!=null) {
				try {
					out.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
}

测试使用的对象

package com.model;

import java.util.Date;

public class Emp {
	private int id;
	private String name;
	private String title;
	private int tellPhone;
	private Date brith;
	
	public Emp(int id, String name, String title, int tellPhone, Date brith) {
		super();
		this.id = id;
		this.name = name;
		this.title = title;
		this.tellPhone = tellPhone;
		this.brith = brith;
	}
	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 String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public int getTellPhone() {
		return tellPhone;
	}
	public void setTellPhone(int tellPhone) {
		this.tellPhone = tellPhone;
	}
	public Date getBrith() {
		return brith;
	}
	public void setBrith(Date brith) {
		this.brith = brith;
	}
	
}

xml有关导出excel的配置

<?xml version="1.0"?>
<FieldsSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Settings>
    <fieldSetting>
      <fieldCnName>姓名</fieldCnName><!-- excel的标题 -->
      <fieldDbName>name</fieldDbName><!-- 对象对应的属性名 -->
      <fieldWidth></fieldWidth><!-- 单元格宽度1-200 （不建议写200，不写默认自适应） -->
    </fieldSetting>
    <fieldSetting>
      <fieldCnName>生日</fieldCnName>
      <fieldDbName>brith</fieldDbName>
      <fieldWidth></fieldWidth><!-- 1-200 （不建议写200，不写默认自适应） -->
    </fieldSetting>
    <fieldSetting>
      <fieldCnName>职务</fieldCnName>
      <fieldDbName>title</fieldDbName>
      <fieldWidth>20</fieldWidth><!-- 1-200 （不建议写200，不写默认自适应） -->
    </fieldSetting>
  </Settings>
</FieldsSettings>

测试代码

package com.utils;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.core.io.DefaultResourceLoader;
import org.springframework.core.io.ResourceLoader;

import com.model.Emp;
import com.utils.xml.JaxbUtil;
import com.utils.xml.bean.FieldSetting;
import com.utils.xml.bean.FieldsSettings;

public class Test {

		
	public static void main(String[] args) throws IOException {
		List<Emp> empList = new ArrayList<Emp>();
        Emp e = new Emp(101, "小a", "经理111111111111111111111111111111111111111111111111111111111", 1234, new Date());
        Emp e1 = new Emp(102, "小b", "销售", 1234, new Date());
        Emp e2 = new Emp(103, "小c", "测试", 1234, new Date());
        Emp e3 = new Emp(104, "小d", "开发", 1234, new Date());
        Emp e4 = new Emp(105, "小e", "实施", 1234, new Date());
        empList.add(e);
        empList.add(e1);
        empList.add(e2);
        empList.add(e3);
        empList.add(e4);
        
        //建议使用这种读取方式，因为springboot打包时，使用文件方式读取不到xml文件
        String fileName = "classpath:1.xml";
		ResourceLoader resourceLoader = new DefaultResourceLoader();
		InputStream inputStream = resourceLoader.getResource(fileName).getInputStream();
    	FieldsSettings fieldsSettings = JaxbUtil.converyToJavaBean(inputStream, FieldsSettings.class);
        
    	if(fieldsSettings!=null) {
    		List<FieldSetting> fieldSetting = fieldsSettings.getFieldSetting();
    		String[] headers = new String[fieldsSettings.getFieldSetting().size()];
    		List<FieldSetting> fields = new ArrayList<>(fieldsSettings.getFieldSetting().size());
    		for(int i=0;i<fieldSetting.size();i++) {
    			headers[i] = fieldSetting.get(i).getFieldCnName();
    			fields.add(fieldSetting.get(i));
    		}
    		
    		ExcelUtil<Emp> ex = new ExcelUtil<Emp>();
    		FileOutputStream out = new FileOutputStream("d://test.xls"); // 向d://test.xls中写数据
        	ex.exportExcel("测试", headers, fields, empList, out, "yyyy-MM-dd HH:mm:ss");
        	System.out.println("完成");
    	}      
	}
}

导出结果