业务场景,用户数据的统计 导出excel
/**
* 导出访客列表
* zhongyaun
*/
@ApiOperation("导出访客列表")
@GetMapping("/export")
public void exportVisitRecord(HttpServletResponse response,@ApiParam("企业id") @RequestParam Integer companyId) throws IOException {
List<Map<String,Object>> exportVisit =jyVisitorService.exportVisitRecord(companyId);
//这里是表格的标头,
String[] cellTitle = new String[]{"序号", "头像","昵称", "手机号", "访问时间"};
// 下面试映查出字段的值,需要注意的是需要与标头对应
List<String> keyList = new ArrayList<>();
keyList.add("id");
keyList.add("head_url");
keyList.add("nick_name");
keyList.add("phone_num");
keyList.add("visit_time");
ExcelUtil.exportExcel(cellTitle, exportVisit, keyList, "导出访客列表", ExcelType.EXCEL_XLSX, response);
}
实现成代码不多解释了
@Override
public List<Map<String, Object>> exportVisitRecord(Integer companyId) {
return baseMapper.exportVisitRecord(companyId);
}
mapper层
<select id="exportVisitRecord" resultType="java.util.Map">
select
id,head_url,nick_name,phone_num,visit_time
from jy_visitor
where company_id=#{companyId}
</select>
下面用到的工具类
package com.qqwl.gold.utils.excel;
import lombok.AllArgsConstructor;
import lombok.Getter;
/**
* excel类型枚举
*
* @Author beichen
* @Create on 2021/4/17 15:42
*/
@Getter
@AllArgsConstructor
public enum ExcelType {
EXCEL_XLS("xls"),
EXCEL_XLSX("xlsx");
private String type;
}
package com.qqwl.gold.utils.excel;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* Excel导入导出通用工具类
*
* @Author beichen
* @Create on 2021/4/17 15:42
*/
public class ExcelUtil {
/**
* 导入Excel文件
*
* @param sourceFile
* @return
* @throws IOException
*/
public static List<Map<String, Object>> importExcel(MultipartFile sourceFile) throws IOException {
String filename = sourceFile.getOriginalFilename();
if (StringUtils.isBlank(filename)) {
return null;
}
if (filename.endsWith(ExcelType.EXCEL_XLS.getType())) {
return readXls(sourceFile.getInputStream());
} else if (filename.endsWith(ExcelType.EXCEL_XLSX.getType())) {
return readXlsx(sourceFile.getInputStream());
} else {
return null;
}
}
/**
* 读取Excel文件
*
* @param path
* @return
* @throws IOException
*/
public static List<Map<String, Object>> readExcel(String path) throws IOException {
if (path.endsWith(ExcelType.EXCEL_XLS.getType())) {
return readXls(path);
} else if (path.endsWith(ExcelType.EXCEL_XLSX.getType())) {
return readXlsx(path);
} else {
return null;
}
}
/**
* 读取xls表格
*
* @param inputStream
* @return
* @throws IOException
*/
public static List<Map<String, Object>> readXls(InputStream inputStream) throws IOException {
return readXlsInputStream(inputStream);
}
/**
* 读取xls表格
*
* @param path
* @return
* @throws IOException
*/
public static List<Map<String, Object>> readXls(String path) throws IOException {
InputStream inputStream = new FileInputStream(path);
return readXlsInputStream(inputStream);
}
/**
* 读取xlsx表格
*
* @param inputStream
* @return
* @throws IOException
*/
public static List<Map<String, Object>> readXlsx(InputStream inputStream) throws IOException {
return readXlsxInputStream(inputStream);
}
/**
* 读取xlsx表格
*
* @param path
* @return
* @throws IOException
*/
public static List<Map<String, Object>> readXlsx(String path) throws IOException {
InputStream inputStream = new FileInputStream(path);
return readXlsx(inputStream);
}
/**
* 数据导出excel
*
* @param cellTitle 列名集合
* @param dataList key:value数据集合
* @param keyList 关键字集合
* TODO 关键字为dataList中的键名,顺序需与cellTitle数据字段映射顺序对应
* @param fileName 文件名
* @param type 文件类型
* @param response
* @throws IOException
*/
public static void exportExcel(String[] cellTitle, List<Map<String, Object>> dataList,
List<String> keyList, String fileName, ExcelType type,
HttpServletResponse response) throws IOException {
if (type.equals(ExcelType.EXCEL_XLS)) {
exportXls(cellTitle, dataList, keyList, fileName, response);
} else {
exportXlsx(cellTitle, dataList, keyList, fileName, response);
}
}
/**
* 读取xls文件流
*
* @param inputStream
* @return
* @throws IOException
*/
private static List<Map<String, Object>> readXlsInputStream(InputStream inputStream) throws IOException {
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);
List<Map<String, Object>> list = new ArrayList<>();
// 读取行
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 读取列
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
Map<String, Object> map = new HashMap<>();
for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {
if (hssfSheet.getRow(0).getCell(cellNum) == null)
continue;
map.put((hssfSheet.getRow(0).getCell(cellNum) + "").trim(), getHSSfCellValue(hssfRow.getCell(cellNum)).trim());
}
list.add(map);
}
}
}
hssfWorkbook.close();
inputStream.close();
return list;
}
/**
* 读取xlsx文件流
*
* @param inputStream
* @return
* @throws IOException
*/
private static List<Map<String, Object>> readXlsxInputStream(InputStream inputStream) throws IOException {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
List<Map<String, Object>> list = new ArrayList<>();
// 读取行
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
// 读取列
for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow != null) {
Map<String, Object> map = new HashMap<>();
for (int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++) {
if (xssfSheet.getRow(0).getCell(cellNum) == null || xssfRow.getCell(cellNum) == null)
continue;
map.put((xssfSheet.getRow(0).getCell(cellNum) + "").trim(), getXSSFCellValue(xssfRow.getCell(cellNum)).trim());
}
list.add(map);
}
}
}
xssfWorkbook.close();
inputStream.close();
return list;
}
@SuppressWarnings({"deprecation"})
private static String getHSSfCellValue(HSSFCell hssfCell) {
if (hssfCell == null) {
return "";
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue()).trim();
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
return String.valueOf(dataFormatter.formatCellValue(hssfCell)).trim();
} else {
return String.valueOf(hssfCell.getStringCellValue()).trim();
}
}
@SuppressWarnings({"deprecation"})
private static String getXSSFCellValue(XSSFCell xssfRow) {
if (xssfRow == null) {
return "";
} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfRow.getBooleanCellValue()).trim();
} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
return String.valueOf(dataFormatter.formatCellValue(xssfRow)).trim();
} else {
return String.valueOf(xssfRow.getStringCellValue()).trim();
}
}
/**
* 数据导出为excel,文件后缀(xlsx)
*
* @param cellTitle 列名集合
* @param dataList key:value数据集合
* @param keyList 关键字集合
* TODO 关键字为dataList中的键名,顺序需与cellTitle数据字段映射顺序对应
* @param fileName 文件名
* @param response
* @throws IOException
*/
private static void exportXlsx(String[] cellTitle, List<Map<String, Object>> dataList,
List<String> keyList, String fileName,
HttpServletResponse response) throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet excelSheet = workbook.createSheet("sheet");
HSSFRow row = excelSheet.createRow(0);
HSSFCellStyle cellStyle = workbook.createCellStyle();
HSSFCell cell;
// 填充列名
for (int i = 0; i < cellTitle.length; i++) {
// 创建标题数量的单元格
cell = row.createCell(i);
// 填充该单元格数据为列标题
cell.setCellValue(cellTitle[i]);
cell.setCellStyle(cellStyle);
}
Map<String, Object> map;
for (int i = 0; i < dataList.size(); i++) {
// 创建数据条数
row = excelSheet.createRow(i + 1);
map = dataList.get(i);
for (int j = 0; j < keyList.size(); j++) {
// 创建数据单元格数量
row.createCell(j).setCellValue(map.get(keyList.get(j)).toString());
}
}
response.setContentType("application/vnd.ms-excel;charset=utf-8");
String exportFileName = fileName + ".xlsx";
String fileNameURL = URLEncoder.encode(exportFileName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileNameURL + ";" + "filename*=utf-8''" + fileNameURL);
response.flushBuffer();
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
workbook.close();
outputStream.flush();
outputStream.close();
}
/**
* 数据导出为excel,文件后缀(xls)
*
* @param cellTitle 列名集合
* @param dataList key:value数据集合
* @param keyList 关键字集合
* TODO 关键字为dataList中的键名,顺序需与cellTitle数据映射顺序对应
* @param fileName 文件名
* @param response
* @throws IOException
*/
private static void exportXls(String[] cellTitle, List<Map<String, Object>> dataList,
List<String> keyList, String fileName,
HttpServletResponse response) throws IOException {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet excelSheet = workbook.createSheet("sheet");
XSSFRow row = excelSheet.createRow(0);
XSSFCellStyle cellStyle = workbook.createCellStyle();
XSSFCell cell;
// 填充列名
for (int i = 0; i < cellTitle.length; i++) {
// 创建标题数量的单元格
cell = row.createCell(i);
// 填充该单元格数据为列标题
cell.setCellValue(cellTitle[i]);
cell.setCellStyle(cellStyle);
}
Map<String, Object> map;
for (int i = 0; i < dataList.size(); i++) {
// 创建数据条数
row = excelSheet.createRow(i + 1);
map = dataList.get(i);
for (int j = 0; j < keyList.size(); j++) {
// 创建数据单元格数量
row.createCell(j).setCellValue(map.get(keyList.get(j)).toString());
}
}
response.setContentType("application/vnd.ms-excel;charset=utf-8");
String exportFileName = fileName + ".xls";
String fileNameURL = URLEncoder.encode(exportFileName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileNameURL + ";" + "filename*=utf-8''" + fileNameURL);
response.flushBuffer();
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
workbook.close();
outputStream.flush();
outputStream.close();
}
}
效果如上