基于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("完成");
}
}
}