在项目中导出一个表格文件
项目中有个要求,就是需要一个点击事件,自动把表数据以Excel文件的格式下载的本地电脑。
我从网上查到的好多,自己修改了一下,终于在项目中能实现功能了
项目环境:
- SpringMVC Spring Mybatis框架
- JDK8版本
- 水平拆分项目
需要导入的依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-examples</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.17</version>
</dependency>
工具类
我交给了spring
@Service
public class ExportExcel<T> {
public void exportExcel(String[] headers,Collection<T> dataset, String fileName,HttpServletResponse response) {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(fileName);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 20);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
for (short i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
try {
// 遍历集合数据,产生数据行
Iterator<T> it = dataset.iterator();
int index = 0;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
T t = (T) it.next();
// 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值
Field[] fields = t.getClass().getDeclaredFields();
for (short i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
Field field = fields[i];
String fieldName = field.getName();
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
// 判断值的类型后进行强制类型转换
String textValue = null;
// 其它数据类型都当作字符串简单处理
if(value != null && value != ""){
textValue = value.toString();
}
if (textValue != null) {
HSSFRichTextString richString = new HSSFRichTextString(textValue);
cell.setCellValue(richString);
}
}
}
System.out.println(workbook.toString());
getExportedFile(workbook, fileName,response);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
*
* 方法说明: 指定路径下生成EXCEL文件
* @return
*/
public void getExportedFile(HSSFWorkbook workbook, String name,HttpServletResponse response) throws Exception {
BufferedOutputStream fos = null;
try {
String fileName = name + ".xls";
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition", "attachment;filename=" + new String( fileName.getBytes("gb2312"), "ISO8859-1" ));
fos = new BufferedOutputStream(response.getOutputStream());
workbook.write(fos);
// response.flushBuffer();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fos != null) {
fos.close();
}
}
}
}
需要的包
import java.io.BufferedOutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Collection;
import java.util.Iterator;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.springframework.stereotype.Service;
entity对象
导出的数据库中的数据的载体,添加序列化可能会报错
public class SysUser {
private Integer id;
private String username;
private String password;
private String salt;//盐值
private String email;
private String mobile;
private Integer roleId;
private Integer valid=1;
private Date createdTime;
private Date modifiedTime;
private String createdUser;
private String modifiedUser;
mapper层
public interface SysUserDao {
/**
* 查询user表所有数据
*/
List<SysUser> getSysUserList();
}
service
@Override
public List<SysUser> getSysUserList() {
List<SysUser> list=sysUserDao.getSysUserList();
return list;
}
controller
public void export(HttpServletRequest request, HttpServletResponse response) {
List<SysUser> encryptList = sysUserService.getSysUserList();
ExportExcel<SysUser> ee= new ExportExcel<SysUser>();
String[] headers = {"员工编号","员工姓名",
"登录密码","电子邮箱","联系电话","密保","权限",
"创建时间","修改时间","创建人",
"修改人","角色编号"};
String fileName = "员工信息表";
ee.exportExcel(headers,encryptList,fileName,response);
}