准备
git整体项目下载地址
准备jar包,包含:
<!-- export csv file need jar start -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-compress</artifactId>
<version>1.3</version>
</dependency>
<dependency>
<groupId>ant</groupId>
<artifactId>ant</artifactId>
<version>1.7.0</version>
</dependency>
<dependency>
<groupId>net.sf.opencsv</groupId>
<artifactId>opencsv</artifactId>
<version>2.3</version>
</dependency>
<!-- export csv file need jar end -->
工具类ExportByOpenCsvUtils.java
package com.paincupid.springmvc.util.export;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map.Entry;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import au.com.bytecode.opencsv.CSVWriter;
/**
* 通用csv导出类
* @author arthur.dy.lee
* @since 2016.08.28
*/
public class ExportByOpenCsvUtils {
private static final Logger logger = LoggerFactory.getLogger(ExportByOpenCsvUtils.class);
/**
* 导出Csv
*
* @param csvName
* 要导出的excel名称
* @param list
* 要导出的数据集合
* @param fieldMap
* 中英文字段对应Map,即要导出的excel表头
* @param response
* 使用response可以导出到浏览器
* @return
*/
public static <T> void export(String csvName, List<T> list, LinkedHashMap<String, String> fieldMap,
HttpServletResponse response) {
// 设置默认文件名为当前时间:年月日时分秒
if (StringUtils.isBlank(csvName)) {
csvName = new SimpleDateFormat("yyyy-MM-dd hhmmss").format(new Date()).toString();
}else{
csvName = csvName + new SimpleDateFormat("_yyyy-MM-dd hhmmss").format(new Date()).toString();
}
try {
// 设置response头信息
response.reset(); // 清空输出流
response.setContentType("APPLICATION/OCTET-STREAM"); // 定义输出类型
csvName = new String(csvName.getBytes("gbk"), "ISO-8859-1");
response.setHeader("Content-disposition", "attachment; filename=" + csvName + ".csv");
response.setCharacterEncoding("gbk");
} catch (UnsupportedEncodingException e1) {
logger.info(e1.getMessage());
}
try {
CSVWriter writer = new CSVWriter(response.getWriter());
// 组装文件并输出
fillCsv(writer, list, fieldMap);
writer.flush();
writer.close();
} catch (Exception e) {
logger.info("导出Csv失败!");
logger.error(e.getMessage());
}
}
/**
* 根据字段名获取字段对象
*
* @param fieldName
* 字段名
* @param clazz
* 包含该字段的类
* @return 字段
*/
private static Field getFieldByName(String fieldName, Class<?> clazz) {
//logger.info("根据字段名获取字段对象:getFieldByName(), fieldName: "+fieldName);
// 拿到本类的所有字段
Field[] selfFields = clazz.getDeclaredFields();
// 如果本类中存在该字段,则返回
for (Field field : selfFields) {
// 如果本类中存在该字段,则返回
if (field.getName().equals(fieldName)) {
return field;
}
}
// 否则,查看父类中是否存在此字段,如果有则返回
Class<?> superClazz = clazz.getSuperclass();
if (superClazz != null && superClazz != Object.class) {
// 递归
return getFieldByName(fieldName, superClazz);
}
// 如果本类和父类都没有,则返回空
return null;
}
/**
* 根据字段名获取字段值
*
* @param fieldName
* 字段名
* @param o
* 对象
* @return 字段值
* @throws Exception
* 异常
*
*/
private static Object getFieldValueByName(String fieldName, Object o) throws Exception {
//logger.info("根据字段名获取字段值:getFieldValueByName()");
Object value = null;
// 根据字段名得到字段对象
Field field = getFieldByName(fieldName, o.getClass());
// 如果该字段存在,则取出该字段的值
if (field != null) {
field.setAccessible(true);// 类中的成员变量为private,在类外边使用属性值,故必须进行此操作
value = field.get(o);// 获取当前对象中当前Field的value
} else {
throw new Exception(o.getClass().getSimpleName() + "类不存在字段名 " + fieldName);
}
logger.info("value: "+ value);
return value;
}
/**
* 根据带路径或不带路径的属性名获取属性值,即接受简单属性名,
* 如userName等,又接受带路径的属性名,如student.department.name等
*
* @param fieldNameSequence
* 带路径的属性名或简单属性名
* @param o
* 对象
* @return 属性值
* @throws Exception
* 异常
*
*/
private static Object getFieldValueByNameSequence(String fieldNameSequence, Object o) throws Exception {
//logger.info("根据带路径或不带路径的属性名获取属性值,即接受简单属性名:getFieldValueByNameSequence(): "+fieldNameSequence);
Object value = null;
// 将fieldNameSequence进行拆分
String[] attributes = fieldNameSequence.split("\\.");
if (attributes.length == 1) {
value = getFieldValueByName(fieldNameSequence, o);
} else {
// 根据数组中第一个连接属性名获取连接属性对象,如student.department.name
Object fieldObj = getFieldValueByName(attributes[0], o);
// 截取除第一个属性名之后的路径
String subFieldNameSequence = fieldNameSequence.substring(fieldNameSequence.indexOf(".") + 1);
// 递归得到最终的属性对象的值
value = getFieldValueByNameSequence(subFieldNameSequence, fieldObj);
}
return value;
}
/**
* 向csv中填充数据
*
* @param writer
* csv文件的名称
* @param list
* 数据源
* @param fieldMap
* 中英文字段对应关系的Map
* @param style
* 表格中的格式
* @throws Exception
* 异常
*
*/
private static <T> void fillCsv(CSVWriter writer, List<T> list, LinkedHashMap<String, String> fieldMap) throws Exception {
//logger.info("向csv file中填充数据");
// 定义存放英文字段名和中文字段名的数组
String[] enFields = new String[fieldMap.size()];
String[] cnFields = new String[fieldMap.size()];
// 填充数组
int count = 0;
for (Entry<String, String> entry : fieldMap.entrySet()) {
enFields[count] = entry.getKey();
cnFields[count] = entry.getValue();
count++;
}
// 填充表头
String [] includeHeaders = new String[count];
for (int i = 0; i < cnFields.length; i++) {
includeHeaders[i] = cnFields[i];
}
writer.writeNext(includeHeaders);
// 填充内容
String [] data = new String[count];
for (int index = 0; index < list.size(); index++) {
// 获取单个对象
T item = list.get(index);
for (int i = 0; i < enFields.length; i++) {
Object objValue = getFieldValueByNameSequence(enFields[i], item);
String fieldValue = objValue == null ? "" : objValue.toString();
data[i] = fieldValue;
}
writer.writeNext(data);
}
}
}
使用
@ResponseBody
@RequestMapping(value="/downloadCsv", method=RequestMethod.POST)
public void downloadCsv(String param1, String param2){
List<Person> list = CreatMockData.createPersonList(20,1);
LinkedHashMap<String, String> fieldMap = new LinkedHashMap<String, String>();
fieldMap.put("id", "id");
fieldMap.put("name", "名称");
fieldMap.put("age", "年龄");
fieldMap.put("tel", "电话");
fieldMap.put("prov", "省份");
fieldMap.put("city", "城市");
fieldMap.put("sex", "性别");
fieldMap.put("location", "地址");
fieldMap.put("company", "公司");
fieldMap.put("comment", "备注");
ExportByOpenCsvUtils.export("李-test-excel", list, fieldMap, response);
}
download.jsp
这里只是一提供了一个下载按钮,表示了一下而已。下载的话用的是form的形式。
Ajax下载Excel是有点问题,因为涉及到流下载,不过没有试过jquery.form.js
以下是jsp页面:
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path;
%>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta name="description" content="">
<meta name="author" content="">
<title>paincupid admin</title>
<!-- Bootstrap Core CSS -->
<link href="<%=basePath%>/resources/themes/bower_components/bootstrap/dist/css/bootstrap.min.css" rel="stylesheet">
<!-- MetisMenu CSS -->
<link href="<%=basePath%>/resources/themes/bower_components/metisMenu/dist/metisMenu.min.css" rel="stylesheet">
<!-- Custom CSS -->
<link href="<%=basePath%>/resources/themes/dist/css/sb-admin-2.css" rel="stylesheet">
<!-- Custom Fonts -->
<link href="<%=basePath%>/resources/themes/bower_components/font-awesome/css/font-awesome.min.css" rel="stylesheet" type="text/css">
<link rel="stylesheet" href="<%=basePath%>/resources/css/zTreeStyle/zTreeStyle.css" type="text/css">
<!-- HTML5 Shim and Respond.js IE8 support of HTML5 elements and media queries -->
<!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
<!--[if lt IE 9]>
<script src="https://oss.maxcdn.com/libs/html5shiv/3.7.0/html5shiv.js"></script>
<script src="https://oss.maxcdn.com/libs/respond.js/1.4.2/respond.min.js"></script>
<![endif]-->
<style>
</style>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-4 col-md-offset-4">
<div class="login-panel panel panel-default">
<div class="panel-heading">
<h3 class="panel-title">下载</h3>
</div>
<div class="panel-body">
<form>
<fieldset>
<div class="form-group">
</div>
<div class="form-group">
</div>
<!-- Change this to a button or input when using this as a form -->
<a class="btn btn-lg btn-success btn-block" id="btnDownloadExcel" type="submit" onclick="btnDownloadExcel()">下载Excel</a>
<a class="btn btn-lg btn-success btn-block" id="btnDownloadCsv" type="submit" onclick="btnDownloadCsv()">下载Csv</a>
</fieldset>
</form>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
<script type="text/javascript" src="<%=basePath%>/resources/js/jquery/jquery-1.11.3.min.js"></script>
<script type="text/javascript" src="<%=basePath%>/resources/js/jquery-ui/jquery-ui.js"></script>
<script>
$(function(){
});
function btnDownloadExcel(){
var form = document.forms[0];
var url = "${pageContext.request.contextPath}/widget/downloadExcel";
form.action = url;
form.method = "post";
form.submit();
}
function btnDownloadCsv(){
var form = document.forms[0];
var url = "${pageContext.request.contextPath}/widget/downloadCsv";
form.action = url;
form.method = "post";
form.submit();
}
</script>
转载请注明来源:paincupid