poi导出excel
1:需要的jar
百度云： https://pan.baidu.com/s/1TP9YWhTQu8QHpB41AU3I5Q 提取码: ymtj
去掉ooxml-schemas-1.3.jar，不去也不影响
maven：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
2：创建三个类，一个实体类 ，一个excel实体类，一个测试类， 创建Java工程，引入夹包，直接把代码粘过去，运行测试类的main方法，看d盘生成的文件
//*************************************实体类的写法开始*************************************
package com;
public class Test1 {
private String test1;
private Integer test2;
private String test3;
private Long test4;
private String test5;
private String test6;
private Integer test7;
private String test8;
private Integer test9;
private String test10;
public Test1(String test1, Integer test2, String test3, Long test4,
String test5, String test6, Integer test7, String test8,
Integer test9, String test10) {
super();
this.test1 = test1;
this.test2 = test2;
this.test3 = test3;
this.test4 = test4;
this.test5 = test5;
this.test6 = test6;
this.test7 = test7;
this.test8 = test8;
this.test9 = test9;
this.test10 = test10;
}
public String getTest1() {
return test1;
}
public void setTest1(String test1) {
this.test1 = test1;
}
public Integer getTest2() {
return test2;
}
public void setTest2(Integer test2) {
this.test2 = test2;
}
public String getTest3() {
return test3;
}
public void setTest3(String test3) {
this.test3 = test3;
}
public Long getTest4() {
return test4;
}
public void setTest4(Long test4) {
this.test4 = test4;
}
public String getTest5() {
return test5;
}
public void setTest5(String test5) {
this.test5 = test5;
}
public String getTest6() {
return test6;
}
public void setTest6(String test6) {
this.test6 = test6;
}
public Integer getTest7() {
return test7;
}
public void setTest7(Integer test7) {
this.test7 = test7;
}
public String getTest8() {
return test8;
}
public void setTest8(String test8) {
this.test8 = test8;
}
public Integer getTest9() {
return test9;
}
public void setTest9(Integer test9) {
this.test9 = test9;
}
public String getTest10() {
return test10;
}
public void setTest10(String test10) {
this.test10 = test10;
}
}
//*************************************实体类的写法结束*************************************
//*************************************excel实体类的写法开始*************************************
package com;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class TestExcel<T> {
//工作表sheet名称,可以为空
private String sheetName;
//导出的excel名称
private String fileName;
//sheet各个列的表头
private String[] headList;
//各个列的key值--对应实体字段名
private String[] headKey;
//sheet需要填充的数据信息
private List<T> data;
public TestExcel(){
}
public TestExcel(String sheetName,String[] headList,String[] headKey,List<T> data){
this.sheetName = sheetName;
this.headList = headList;
this.headKey = headKey;
this.data = data;
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
public String[] getHeadList() {
return headList;
}
public void setHeadList(String[] headList) {
this.headList = headList;
}
public String[] getHeadKey() {
return headKey;
}
public void setHeadKey(String[] headKey) {
this.headKey = headKey;
}
public List<T> getData() {
return data;
}
public void setData(List<T> data) {
this.data = data;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
//生成 XSSFWorkbook
public XSSFWorkbook exportExcel() {
if (this.headList == null || this.headList.length == 0) {
System.out.println("没有表头对应数据");
return null;
}
if (this.headKey == null || this.headKey.length == 0) {
System.out.println("没有表头对应字段数据");
return null;
}
if (this.headList.length != this.headKey.length) {
System.out.println("表头和对应的key长度不一致");
return null;
}
if (this.data == null || this.data.isEmpty()) {
System.out.println("没有要导出的数据");
return null;
}
//表头行有多少列
int titleLength = 0;
// 声明一个工作薄
XSSFWorkbook workbook = new XSSFWorkbook();
// 生成一个表格
XSSFSheet sheet = null;
if (this.sheetName != null && !"".equals(this.sheetName)) {
sheet = workbook.createSheet(this.sheetName);
} else {
sheet = workbook.createSheet();
}
//创建excel的表头数据
titleLength = this.headList.length;
XSSFRow titleRow = sheet.createRow(0);
for (int i = 0; i < this.headList.length; i++) {
XSSFCell cell = titleRow.createCell(i);
cell.setCellValue(this.headList[i]);
}
// CellRangeAddress 对象的构造方法需要传入合并单元格的首行、最后一行、首列、最后一列。
//CellRangeAddress cra = new CellRangeAddress(0, 0, 0, heardName.length - 1);
//遍历数据
Iterator<T> it = this.data.iterator();
int dateRows = 0;
while (it.hasNext()) {
XSSFRow dataRow = sheet.createRow(++dateRows);
T t = (T) it.next();//获取Java对象
for(int i=0;i<titleLength;i++){
String fieldKeyName = this.headKey[i];//循环获取Java实体变量名
Object value =null;
try {
PropertyDescriptor pd = new PropertyDescriptor(fieldKeyName, t.getClass());//获取t 对象中指定变量的get方法值
Method m = pd.getReadMethod();
value = m.invoke(t, new Object[] {});//获取指定变量对应的get方法的值
}catch (Exception ex) {
ex.printStackTrace();
}
XSSFCell dataCell = dataRow.createCell(i);
dataCell.setCellValue(value==null?"":value.toString());
}
}
return workbook;
}
}
//*************************************excel实体类的写法结束*************************************
//*************************************测试类的写法开始*************************************
package com;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Test {
public static void main(String[] args) {
String[] headList={"表头1","表头2","表头3","表头4","表头5","表头6","表头7","表头8","表头9","表头10"};
String[] headKey={"test1","test2","test3","test4","test5","test6","test7","test8","test9","test10"};
String filename = "导出excel表格";
TestExcel<Test1> ex = new TestExcel<Test1>(null,headList,headKey,dataList());
try {
XSSFWorkbook workbook = ex.exportExcel();
FileOutputStream out = new FileOutputStream("d:\\部门通讯录.xlsx");
workbook.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("导出成功!!!!!!!!");
}
public static List<Test1> dataList() {
List<Test1> list = new ArrayList<Test1>();
for(int i=0;i<20;i++){
Test1 t1 = new Test1("1",2,"3",4l,"5","6",3,"7",3,"8");
list.add(t1);
}
return list;
}
}
//*************************************测试类的写法结束*************************************
3：运行结果
4:在web项目中controller中的大概写法，这样前端直接调用这个方法，就会在页面上直接下载了
//**********************************开始****************************************
public String exportWord(HttpServletRequest request, HttpServletResponse response) {
String[] headList={"表头1","表头2","表头3","表头4","表头5","表头6","表头7","表头8","表头9","表头10"};//表头
String[] headKey={"test1","test2","test3","test4","test5","test6","test7","test8","test9","test10"};//对应的实体类字段,可以是部分字段
String filename = "部门信息";
List<Test1> list = testServiceImpl.selectList();//获取数据,一般是从库里查询的数据，这里可以改成自己造的数据
ExcelExport<Test1> ex = new ExcelExport<Test1>(null,headList,headKey,list);
XSSFWorkbook workbook= ex.exportExcel();
try {
response.setHeader("Content-Disposition","attachment;filename="+new String(filename.getBytes(), "iso8859-1")+".xlsx");
OutputStream out = response.getOutputStream();
workbook.write(out);
out.close();
} catch (IOException e) {
return "导出数据异常！！！";
}
return null;
}
//**********************************结束***************************************
