工具类
package com.mvc.utils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
/**
* excel export
*
* @author smallk
* @link poi.jar
* @link poi-ooxml.jar
* @link poi-ooxml-schemas.jar
* @link dom4j.jar
* @link xmlbeans.jar
* @date 2018/12/4 23:30
*/
public class ExcelExport {
public static final String W2003 = "2003";
public static final String W2007 = "2007";
public static final String XLS = "xls";
public static final String XLSX = "xlsx";
/**
* 创建 Workbook
*
* @param version 2003 2007 xls xlsx
* @return Workbook
*/
public static Workbook createWorkbook(String version) {
if (W2003.equalsIgnoreCase(version) || XLS.equalsIgnoreCase(version)) {
return new HSSFWorkbook();
} else if (W2007.equalsIgnoreCase(version) || XLSX.equalsIgnoreCase(version)) {
return new XSSFWorkbook();
} else {
return new XSSFWorkbook();
}
}
/**
* 导出简单Excel
*
* @param titleList title list
* @param dataList data list
* @param version 2003 2007 xls xlsx
* @return Workbook
*/
public static Workbook exportSimpleExcelByMap(List<String> titleList, List<Map<String, Object>> dataList, String version) {
Row row;
int rowIndex = 0;
Workbook workbook = ExcelExport.createWorkbook(version);
Sheet sheet = workbook.createSheet();
if (titleList != null && !titleList.isEmpty()) {
int cellIndex = 0;
row = sheet.createRow(rowIndex++);
for (String title : titleList) {
row.createCell(cellIndex++).setCellValue(title == null ? "" : title);
}
}
if (dataList != null && !dataList.isEmpty()) {
for (Map<String, Object> map : dataList) {
if (map == null || map.isEmpty()) {
continue;
}
int cellIndex = 0;
row = sheet.createRow(rowIndex++);
Iterator iterator = map.values().iterator();
while (iterator.hasNext()) {
Object value = iterator.next();
row.createCell(cellIndex++).setCellValue(value == null ? "" : value.toString());
}
}
}
return workbook;
}
/**
* 导出简单Excel
*
* @param titleList title list
* @param dataList data list
* @param version 2003 2007 xls xlsx
* @return Workbook
*/
public static Workbook exportSimpleExcelByObject(List<String> titleList, List<List<Object>> dataList, String version) {
Row row;
int rowIndex = 0;
Workbook workbook = ExcelExport.createWorkbook(version);
Sheet sheet = workbook.createSheet();
if (titleList != null && !titleList.isEmpty()) {
int cellIndex = 0;
row = sheet.createRow(rowIndex++);
for (String title : titleList) {
row.createCell(cellIndex++).setCellValue(title == null ? "" : title);
}
}
if (dataList != null && !dataList.isEmpty()) {
for (List<Object> objList : dataList) {
if (objList == null || objList.isEmpty()) {
continue;
}
int cellIndex = 0;
row = sheet.createRow(rowIndex++);
for (Object value : objList) {
row.createCell(cellIndex++).setCellValue(value == null ? "" : value.toString());
}
}
}
return workbook;
}
/**
* 填充模板sheet数据
*
* @param sheet Sheet
* @param dataList data list
* @param rowIndex 从第几行开始 最小值0
*/
public static void fillTemplateSheetByMap(Sheet sheet, List<Map<String, Object>> dataList, int rowIndex) {
if (dataList == null || dataList.isEmpty() || rowIndex < 0) {
return;
}
for (Map<String, Object> map : dataList) {
if (map == null || map.isEmpty()) {
continue;
}
int cellIndex = 0;
Row row = sheet.getRow(rowIndex);
if (row == null) {
row = sheet.createRow(rowIndex);
}
Iterator iterator = map.values().iterator();
while (iterator.hasNext()) {
Object value = iterator.next();
Cell cell = row.getCell(cellIndex);
if (cell == null) {
cell = row.createCell(cellIndex);
}
cell.setCellValue(value == null ? "" : value.toString());
cellIndex++;
}
rowIndex++;
}
}
/**
* 填充模板sheet数据
*
* @param sheet Sheet
* @param dataList data list
* @param rowIndex 从第几行开始 最小值0
*/
public static void fillTemplateSheetByObject(Sheet sheet, List<List<Object>> dataList, int rowIndex) {
if (dataList == null || dataList.isEmpty() || rowIndex < 0) {
return;
}
for (List<Object> objList : dataList) {
if (objList == null || objList.isEmpty()) {
continue;
}
int cellIndex = 0;
Row row = sheet.getRow(rowIndex);
if (row == null) {
row = sheet.createRow(rowIndex);
}
for (Object value : objList) {
Cell cell = row.getCell(cellIndex);
if (cell == null) {
cell = row.createCell(cellIndex);
}
cell.setCellValue(value == null ? "" : value.toString());
cellIndex++;
}
rowIndex++;
}
}
}
设置响应的方法
/**
* 设置下载响应
*/
public static void setDownLoadResponse(HttpServletRequest request, HttpServletResponse response, String fileName) throws Exception {
String msie = "msie";
String chrome = "chrome";
String windows = "windows";
String firefox = "firefox";
String browserType = request.getHeader("User-Agent").toLowerCase();
if (browserType.contains(firefox) || browserType.contains(chrome)) {
fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
} else if (browserType.contains(msie) || browserType.contains(windows)) {
fileName = URLEncoder.encode(fileName, "UTF-8");
} else {
fileName = new String(fileName.getBytes());
}
// 重置
response.reset();
// 告知浏览器不缓存
response.setHeader("pragma", "no-cache");
response.setHeader("cache-control", "no-cache");
response.setHeader("expires", "0");
// 响应编码
response.setCharacterEncoding("UTF-8");
// 用给定的名称和值添加一个响应头
response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
}
Controller
/**
* 导出学生excel
*
* @param request HttpServletRequest
* @param response HttpServletResponse
* @param model DemoStudentModel
*/
@GetMapping("/export-stu-excel")
public void exportStuExcel(HttpServletRequest request, HttpServletResponse response, DemoStudentModel model) throws Exception {
Workbook workbook = demoService.exportStuExcel(model);
HttpUtil.setDownLoadResponse(request, response, "学生.xlsx");
workbook.write(response.getOutputStream());
}
ServiceImpl
/**
* 导出学生excel
*
* @param model DemoStudentModel
* @return Workbook
*/
public Workbook exportStuExcel(DemoStudentModel model) {
List<String> titleList = new ArrayList<>();
titleList.add("ID");
titleList.add("学生姓名");
titleList.add("学生年龄");
List<Map<String, Object>> dataList = demoDao.exportStuExcel(model);
return ExcelExport.exportSimpleExcelByMap(titleList, dataList, ExcelExport.W2007);
}
JS
//导出学生excel
$('#exportStuExcel').click(function () {
var index = layer.confirm('确认导出？', {closeBtn: 0}, function () {
layer.close(index);
var stuName = $('#stuNameValue').val();
location.href = ctx + '/demo/export-stu-excel?stuName=' + encodeURIComponent(stuName);
});
});
使用map数据形式应该为：
Map<String, Object> map = new LinkedHashMap<>();