导出excel 工具类

Y~~~

于 2019-01-21 11:17:04 发布

阅读量115

点赞数

分类专栏： excel导出文章标签： excel导出

本文链接：https://blog.csdn.net/qq_39930369/article/details/86571146

版权

excel导出专栏收录该内容

2 篇文章 0 订阅

订阅专栏

完美代码，通过安全扫描web渗透，一点问题没有的工具类，真香：

package cn.chinaunicom.hr.vote.common.utils.excel;

import cn.chinaunicom.hr.vote.core.exception.CodeEnum;
import cn.chinaunicom.hr.vote.core.exception.MyException;
import cn.chinaunicom.hr.vote.modules.hr.entity.TmpEvaluateObj;
import cn.chinaunicom.hr.vote.modules.hr.entity.TmpUserDetail;
import org.apache.commons.lang3.StringUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.charset.Charset;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
* @description: excel导出对象数据
* @author: yangsx
* @create: 2018-11-07 14:36
*/
public class ExportExcelUtil {

private static final Logger logger = LogManager.getLogger(ExportExcelUtil.class);

private static final String EXCEL_SUFFIX_XLSX = ".xlsx";

private static final String EXCEL_SUFFIX_XLS = ".xls";

private static final String EXCEL_SUFFIX_ZIP = ".zip";

private static final String CHARSET_UTF_8 = "UTF-8";

private static final String CHARSET_ISO = "ISO-8859-1";

private static final String RESPONSE_HEADER = "Content-disposition";

private static final String PRE = "attachment;filename=\"";

private static final String EXCEPTION_MSG1 = "流关闭异常";

private static final String[] IE_BROWSER_SIGNAL = {"MSIE", "Trident", "Edge"};

private ExportExcelUtil() {
}

/**
* 是否为空行
*
* @param row
* @return
*/
public static boolean isRowEmpty(XSSFRow row) throws MyException {
for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (StringUtils.isEmpty(getCellValue(cell)))
return true;
}
return false;
}

/**
* 确认浏览器类型
* @param request
* @return
*/
public static boolean isMSBrowser(HttpServletRequest request) {
String userAgent = request.getHeader("User-Agent");
for (String signal : IE_BROWSER_SIGNAL) {
if (userAgent.contains(signal))
return true;
}
return false;
}

/**
* 获取单元格数据
* @param cell
* @return
*/
public static String getCellValue(Cell cell) throws MyException {
String cellValue = "";
if (cell == null) {
return cellValue;
}
// 判断数据的类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: // 数字
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
SimpleDateFormat sdf;
// 验证short值
if (cell.getCellStyle().getDataFormat() == 14) {
sdf = new SimpleDateFormat("yyyy-MM-dd");
} else if (cell.getCellStyle().getDataFormat() == 21) {
sdf = new SimpleDateFormat("HH:mm:ss");
} else if (cell.getCellStyle().getDataFormat() == 22) {
sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
} else {
throw new MyException(CodeEnum.CODE_500, "日期格式错误!");
}
Date date = cell.getDateCellValue();
cellValue = sdf.format(date);
} else if (cell.getCellStyle().getDataFormat() == 0) {//处理数值格式
cell.setCellType(Cell.CELL_TYPE_STRING);
cellValue = String.valueOf(cell.getRichStringCellValue().getString());
}
break;
case Cell.CELL_TYPE_STRING: // 字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN: // Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA: // 公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK: // 空值
cellValue = null;
break;
case Cell.CELL_TYPE_ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}

/**
* 普通excel导出
* @param fileName 文件名称
* @param wb 工作簿
* @param request 请求
* @param response 响应
*/
public static void exportCommonExcel(String fileName, HSSFWorkbook wb, HttpServletRequest request, HttpServletResponse response){
fileName += EXCEL_SUFFIX_XLS;
try (OutputStream out = response.getOutputStream()) {
response.setContentType("application/vnd.ms-excel");
boolean isMSIE = isMSBrowser(request);
if (isMSIE) {
fileName = java.net.URLEncoder.encode(fileName, CHARSET_UTF_8);
} else {
fileName = new String(fileName.getBytes(CHARSET_UTF_8), CHARSET_ISO);
}
response.setHeader(RESPONSE_HEADER, PRE + fileName + "\"");
wb.write(out);
} catch (IOException e) {
logger.error(EXCEPTION_MSG1, e);
}
}

/**
* 将excel以zip格式导出
* @param fileName 文件名称
* @param wb 工作簿
* @param request 请求
* @param response 响应
*/
public static void exportExcelToZipFIle(String fileName, HSSFWorkbook wb, HttpServletRequest request, HttpServletResponse response){
try (OutputStream out = response.getOutputStream(); ZipOutputStream zip = new ZipOutputStream(out, Charset.forName(CHARSET_UTF_8))) {
String fileName1 = fileName + EXCEL_SUFFIX_XLS;
String fileName2 = fileName + EXCEL_SUFFIX_ZIP;
response.setContentType("APPLICATION/OCTET-STREAM");
boolean isMSIE = isMSBrowser(request);
if (isMSIE) {
fileName2 = java.net.URLEncoder.encode(fileName2, CHARSET_UTF_8);
} else {
fileName2 = new String(fileName2.getBytes(CHARSET_UTF_8), CHARSET_ISO);
}
response.setHeader(RESPONSE_HEADER, PRE + fileName2 + "\"");
ZipEntry entry = new ZipEntry(fileName1);//设置压缩包中文件的名字
zip.putNextEntry(entry);
wb.write(zip);
zip.flush();
} catch (IOException e) {
logger.error(EXCEPTION_MSG1, e);
}
}

/**
* excel导出模板数据
*
* @param fileName 文件名
* @param path 文件路径
* @param response 响应
*/
public static void exportExcelTemplate(String fileName, String path, HttpServletRequest request, HttpServletResponse response) {
File file = new File(path);
try (InputStream in = new FileInputStream(file); OutputStream out = response.getOutputStream()) {
fileName += EXCEL_SUFFIX_XLSX;
response.setContentType("application/octet-stream");
boolean isMSIE = isMSBrowser(request);
if (isMSIE) {
fileName = java.net.URLEncoder.encode(fileName, CHARSET_UTF_8);
} else {
fileName = new String(fileName.getBytes(CHARSET_UTF_8), CHARSET_ISO);
}
response.setHeader(RESPONSE_HEADER, PRE + fileName + "\"");
int b;
while ((b = in.read()) != -1) {
out.write(b);
}
} catch (IOException e) {
logger.error(EXCEPTION_MSG1, e);
}
}

/**
* execl导入时基础校验
*
* @param file
* @return
*/
public static boolean importExcelBaseCheck(MultipartFile file) throws MyException {
if (null ==file || file.isEmpty()) {
logger.error("导入空文件！");
throw new MyException(CodeEnum.CODE_500, "导入空文件！");
}
String fileName = file.getOriginalFilename();
logger.info("导入excel文件名：{}", fileName);
if (!fileName.endsWith("xlsx")) {
logger.error("导入excel文件不是xlsx格式！");
throw new MyException(CodeEnum.CODE_500, "导入excel文件不是xlsx格式！");
}
return true;
}

/**
* 外部账号导入数据封装转储list
* @param file
* @return
*/
public static List<TmpUserDetail> transferExcelToList1(MultipartFile file) throws MyException {
List<TmpUserDetail> tmpUserDetails = new ArrayList<>();
//获得重复userCode
List<String> allUsers = new ArrayList<>();
Set<String> repeatUsers = new HashSet<>();
importExcelBaseCheck(file);
try (InputStream is = file.getInputStream()) {
XSSFWorkbook wb = new XSSFWorkbook(is);
//默认取第一个sheet
XSSFSheet sheet = wb.getSheetAt(0);
//获得总列数
int totalCols = sheet.getRow(0).getPhysicalNumberOfCells();
if (totalCols != 7) {
throw new MyException(CodeEnum.CODE_500, "文件总列数应为7，目前是" + totalCols + "！");
}
// 获得总行数
int totalRows = sheet.getLastRowNum();
//默认表头一行
if (totalRows < 1) {
throw new MyException(CodeEnum.CODE_500, "文件总行数不足，应至少保证除默认一行表头外还有其他行数据！");
} else {
for (int i = 1; i <= totalRows; i++) {
XSSFRow row = sheet.getRow(i);
if (isRowEmpty(row)) {
break;
}
TmpUserDetail tmpUserDetail = new TmpUserDetail();
tmpUserDetail.setDeptId(getCellValue(row.getCell(0)));
tmpUserDetail.setDeptName(getCellValue(row.getCell(1)));
tmpUserDetail.setSubGroup(getCellValue(row.getCell(2)));
tmpUserDetail.setUserName(getCellValue(row.getCell(3)));
String value = getCellValue(row.getCell(4));
tmpUserDetail.setUserCode(value);
tmpUserDetail.setPwd(getCellValue(row.getCell(5)));
tmpUserDetail.setIsLeader(getCellValue(row.getCell(6)));
tmpUserDetails.add(tmpUserDetail);
if (allUsers.contains(value)) {
repeatUsers.add(value);
} else {
allUsers.add(value);
}
}
}
} catch (IOException e) {
throw new MyException(CodeEnum.CODE_500, "外部账号导入数据出现异常，异常信息：" + e.getMessage());
}
if (!repeatUsers.isEmpty()) {
throw new MyException(CodeEnum.CODE_500, "待导入excel中考评账号重复", repeatUsers);
}
return tmpUserDetails;
}

/**
* 评价信息导入数据封装转储list2
* @param file
* @return
*/
public static List<TmpEvaluateObj> transferExcelToList2(MultipartFile file) throws MyException {
List<TmpEvaluateObj> tmpEvaluateObjs = new ArrayList<>();
importExcelBaseCheck(file);
try (InputStream is = file.getInputStream()) {
XSSFWorkbook wb = new XSSFWorkbook(is);
//默认取第一个sheet
XSSFSheet sheet = wb.getSheetAt(0);
//获得总列数
int totalCols = sheet.getRow(0).getPhysicalNumberOfCells();
if (totalCols != 6) {
throw new MyException(CodeEnum.CODE_500, "文件总列数应为6，目前是" + totalCols + "！");
}
// 获得总行数
int totalRows = sheet.getLastRowNum();
//默认表头一行
if (totalRows < 1) {
throw new MyException(CodeEnum.CODE_500, "文件总行数不足，应至少保证除默认一行表头外还有其他行数据！");
} else {
for (int i = 1; i <= totalRows; i++) {
XSSFRow row = sheet.getRow(i);
if (isRowEmpty(row)) {
break;
}
TmpEvaluateObj tmpEvaluateObj = new TmpEvaluateObj();
tmpEvaluateObj.setDeptId(row.getCell(0).toString());
tmpEvaluateObj.setDeptName(row.getCell(1).toString());
tmpEvaluateObj.setPost(row.getCell(2).toString());
tmpEvaluateObj.setObjectId(Long.parseLong(row.getCell(3).toString()));
tmpEvaluateObj.setObjectName(row.getCell(4).toString());
tmpEvaluateObj.setSubGroup(row.getCell(5).toString());
tmpEvaluateObjs.add(tmpEvaluateObj);
}
}
} catch (IOException e) {
throw new MyException(CodeEnum.CODE_500, "评价信息导入数据异常：" + e.getMessage());
}
return tmpEvaluateObjs;
}

}

Y~~~

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
导出excel 工具类

完美代码，通过安全扫描web渗透，一点问题没有的工具类，真香：package cn.chinaunicom.hr.vote.common.utils.excel;import cn.chinaunicom.hr.vote.core.exception.CodeEnum;import cn.chinaunicom.hr.vote.core.exception.MyException;i...
复制链接

扫一扫