批量处理1——文件的上传（bootstrap+Ajax+SSM）
批量处理2——Java花式处理EXCEL
批量处理3——Excel文件导出
批量处理4——java处理压缩文件
Java知多少——相对路径和绝对路径
HTTP知多少——Content-disposition（文件下载）
点击文件导出时，需要将form表单的条件传递到后台，后台使用POI生成Excel文件，导出给用户。
1. 前端Ajax实现与后台的交互
1. 将form表单转化成JSON数据
$.fn.serializeJsonObj = function () {
var o = {};
var a = this.find("input:not(.data-submit-ignore),select:not(.data-submit-ignore),textarea:not(.data-submit-ignore)").serializeArray();
$.each(a, function () {
if (o[this.name]) {
//如果o[label]不是嵌套在数组
if (!o[this.name].push) {
//将o[label]初始为嵌套数组，如o={a,[a,b,c]}
o[this.name] = [o[this.name]];
}
//将值插入o[label]
o[this.name].push($.trim(this.value) || '');
} else {
//第一次在o中插入o[label]
o[this.name] = $.trim(this.value) || '';
}
});
return o;
};
$.fn.serializeJsonStr = function () {
//将Object对象转化为JSON对象
return JSON.stringify(this.serializeJsonObj());
};
2. html代码
<form id="queryFrom" class="search-form form-horizontal">
<div class="form-body row">
</div>
<div class="form-actions">
<div class="btn-set pull-left">
<!--form表单-->
</div>
<div class="btn-set pull-right">
<button type="button" id="exportDataBtn" class="btn default"
onclick="exportData()">导出数据</button>
</div>
</div>
</form>
<!--动态生成的表格-->
<div class="table-container">
<c:choose>
<c:when test="${empty errorMsg}">
<div style="overflow-x:scroll;width:100%;white-space:nowrap;">
<table class="table table-striped table-bordered table-hover table-checkable" id="datatable_ajax">
</table>
</div>
</c:when>
</c:choose>
</div>
3. JS代码
function exportData() {
var self = $('#exportDataBtn');
if (self.hasClass("disabled")) return;
if ($(".table-container").find("tr").size() <= 1) {
layer.alert('您所查询的结果为空，请重新设置查询条件', {icon: 0});
return;
}
layer.confirm('您是否确认以当前条件导出数据？', {
icon: 3, title: '提示', btn: ['是', '否'], yes: function () {
layer.closeAll('dialog');
$.ajax({
type: "POST",
url: "XXX/exportCheck", //校验是否可以导出
data: $('#queryFrom').serializeJsonStr(), //将表单转化成JSON串
contentType: 'application/json;charset=utf-8', //设置请求头信息
dataType: "json",
success: (function (json) {
//允许导出
if (json.retcode == 200) {
$(".search-form").attr('action', './XXX/export');
$(".search-form").attr('method', 'POST');
$(".search-form").submit();
$(".search-form").attr('action', '');
} else {
layer.alert(json.message, {icon: 2});
}
self.removeClass("disabled");
}),
error: function (json) {
self.removeClass("disabled");
layer.alert("导出失败！", {icon: 2});
},
beforeSend: function () {
self.addClass("disabled");
}
});
}
});
}
2. 后台导出Excel文件
HTTP知多少——Content-disposition（文件下载）设置响应头，使得文件以下载的形式返回给用户。
- 导出文件名浏览器兼容
OutputStream out = response.getOutputStream();// 取得输出流
response.reset();// 清空输出流
response.setHeader("Content-disposition",
"attachment; filename=" + ExcelUtil.toUtf8String("客户列表" + DateUtil.getCurrentTimeStr(), request) + ".xlsx");// 设定输出文件头
response.setContentType("application/msexcel");// 定义输出类型
public static String toUtf8String(String fileName, HttpServletRequest request) throws Exception {
final String userAgent = request.getHeader("USER-AGENT");
String finalFileName = null;
if (StringUtils.contains(userAgent, "MSIE")) {// IE浏览器
finalFileName = URLEncoder.encode(fileName, "UTF8");
} else if (StringUtils.contains(userAgent, "Mozilla")) {// google,火狐浏览器
finalFileName = new String(fileName.getBytes(), "ISO8859-1");
} else {
finalFileName = URLEncoder.encode(fileName, "UTF8");// 其他浏览器
}
return finalFileName;
}
- 导出的方法
import com.tellme.Impl.TestExcel.ExcelUtil;
import lombok.Data;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
@Controller
public class ExcelController {
/**
* 通过web导出excel
*/
@GetMapping("export_doc_document")
public void exportExcel2(HttpServletRequest request, HttpServletResponse response) throws Exception {
//获取到的数据
List<Student> stus = data();
//转换为对象
List<String> header = Arrays.asList(new String[]{"序号", "学号", "姓名", "性别", "年龄"});
Workbook sheets = ExcelUtil.listToWorkbook(header, Student.convertArray(stus), ExcelUtil.ExcelType.xlsx);
assert sheets != null;
//设置响应对象
ExcelUtil.buildResponse("data", request, response);
//将内容写入响应对象中
ExcelUtil.writeToExcel(sheets, response.getOutputStream());
}
public List<Student> data() {
List<Student> stus = new ArrayList<>();
for (int i = 0; i < 10; i++) {
Student student = new Student();
student.setId(10001+i);
student.setName("数据" + i);
student.setAge(10 + i);
student.setSex("男");
stus.add(student);
}
return stus;
}
/**
* 需要导出的数据
*/
@Data
public static class Student {
private int id;
private String name;
private String sex;
private int age;
/**
* 导出excel时，每一行都需要读取List中的Object[]的值。
*/
public static List<Object[]> convertArray(List<Student> list) {
List<Object[]> res = new ArrayList<>();
int i = 1;
for (Student d : list) {
Object[] s = new Object[5];
s[0] = i;
s[1] = d.id;
s[2] = d.name;
s[3] = d.sex;
s[4] = d.age;
res.add(s);
i += 1;
}
return res;
}
}
}
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
@Slf4j
public abstract class ExcelUtil {
/**
* @param header 标头
* @param rows 数据集
* @param type Excel的类型枚举{@link ExcelType}
* @return
*/
public static Workbook listToWorkbook(List<String> header, List<?> rows, ExcelType type) {
Workbook wb;
if (ExcelType.xls.equals(type)) {
wb = new HSSFWorkbook();
} else if (ExcelType.xlsx.equals(type)) {
wb = new XSSFWorkbook();
} else {
return null;
}
int startRow = 0;
Sheet sh = wb.createSheet();
if (null != header) {
org.apache.poi.ss.usermodel.Row row = sh.createRow(startRow++);
CellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
for (int i = 0; i < header.size(); i++) {
String obj = header.get(i);
Cell cell = row.createCell(i);
cell.setCellStyle(style);
cell.setCellValue(String.valueOf(obj));
}
}
if (null != rows) {
for (int i = startRow; i < rows.size() + startRow; i++) {
Object obj = rows.get(i - startRow);
Row row = sh.createRow(i);
if (obj instanceof Collection) {
Collection<?> r = (Collection<?>) obj;
Iterator<?> it = r.iterator();
int j = 0;
while (it.hasNext()) {
Cell cell = row.createCell(j++);
String s = String.valueOf(it.next());
cell.setCellValue(s);
}
} else if (obj instanceof Object[]) {
Object[] r = (Object[]) obj;
for (int j = 0; j < r.length; j++) {
Cell cell = row.createCell(j);
String s = String.valueOf(r[j]);
cell.setCellValue(s);
}
} else {
Cell cell = row.createCell(0);
String s = String.valueOf(obj);
cell.setCellValue(s);
}
}
}
return wb;
}
/**
* 设置响应对象
*
* @param response
* @throws UnsupportedEncodingException
*/
public static void buildResponse(String fileName, HttpServletRequest request, HttpServletResponse response) throws Exception {
String contentType = "application/octet-stream";
response.setHeader("Content-disposition",
"attachment; filename=" + ExcelUtil.toUtf8String(fileName, request) + ".xlsx");// 设定输出文件头
response.setContentType(contentType);
}
/**
* 根据浏览器，适配输出文件名
*
* @param fileName 文件名
* @param request 请求对象
* @return
* @throws Exception
*/
public static String toUtf8String(String fileName, HttpServletRequest request) throws Exception {
final String userAgent = request.getHeader("USER-AGENT");
String finalFileName = null;
if (StringUtils.contains(userAgent, "MSIE")) {// IE浏览器
finalFileName = URLEncoder.encode(fileName, "UTF8");
} else if (StringUtils.contains(userAgent, "Mozilla")) {// google,火狐浏览器
finalFileName = new String(fileName.getBytes(), "ISO8859-1");
} else {
finalFileName = URLEncoder.encode(fileName, "UTF8");// 其他浏览器
}
return finalFileName;
}
/**
* 将Workbook对象中的值写入响应对象。
*/
public static void writeToExcel(Workbook wb, OutputStream out) {
try {
wb.write(out);
} catch (IOException e) {
log.error("writeToExcel error", e);
} finally {
IOUtils.closeQuietly(out);
}
}
/**
* excel的类型枚举
*/
public enum ExcelType {
xls, xlsx;
public static ExcelType parseType(String excelType) {
if ("xls".equals(excelType)) {
return xls;
} else {
return xlsx;
}
}
}
}
效果图.png
参考文章:
1. JavaScript获取表格的行数和列数
