package com.xxiang.common.utils;
/**
* @Author:小样儿
* @PackageName:com.xxiang.common.utils
* @ClassName:ExcelUtils
* @Description:
**/
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;
@Component
public class ExcelUtils {
@Value("${xxiang.template}")
private String template;
/**
* 下载
*
* @param fileName 文件名称
* @param response
* @param workbook excel数据
*/
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8"));
workbook.write(response.getOutputStream());
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* excel 导入
*
* @param filePath excel文件路径
* @param titleRows 标题行
* @param headerRows 表头行
* @param pojoClass pojo类型
* @param <T>
* @return
*/
/*public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
if (StringUtils.isBlank(filePath)) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
params.setNeedSave(true);
params.setSaveUrl("/excel/");
try {
return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
} catch (NoSuchElementException e) {
throw new IOException("模板不能为空");
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}*/
public static<T> List<T> importExcel2(MultipartFile file
, Class<T> pojoClass
, String[] strings
, Integer titleRows
, Integer headerRows
, Integer startRows
, Integer startSheetIndex
, Integer sheetNum
, Integer readRows){
if (file == null) {
return null;
}
try {
List<String> list = Arrays.asList(new String[]{ExcelTypeEnum.XLS.getValue(), ExcelTypeEnum.XLSX.getValue()});
if (!list.contains(file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."))))
throw new RuntimeException("请上传正确格式的excel文件");
else
return importExcel(file, pojoClass, strings
, titleRows, headerRows
, startRows, startSheetIndex, sheetNum, readRows);
} catch (NoSuchElementException e) {
throw new RuntimeException("excel数据不能为空,核查后请重新导入");
}
}
/**
* excel 导入
*
* @param file excel文件
* @param titleRows 标题行
* @param headerRows 表头行
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static<T> List<T> importExcel(MultipartFile file
, Class<T> pojoClass
, String[] strings
, Integer titleRows
, Integer headerRows
, Integer startRows
, Integer startSheetIndex
, Integer sheetNum
, Integer readRows){
return importExcel(file, pojoClass, strings
, titleRows, headerRows
, startRows, startSheetIndex, sheetNum, readRows, true);
}
/**
* excel 导入
*
* @param file 上传的文件
* @param titleRows 标题行
* @param headerRows 表头行
* @param needVerfiy 是否检验excel内容
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static<T> List<T> importExcel(MultipartFile file
, Class<T> pojoClass
, String[] strings
, Integer titleRows
, Integer headerRows
, Integer startRows
, Integer startSheetIndex
, Integer sheetNum
, Integer readRows
, boolean needVerfiy){
List<T> list = null;
try {
list = importExcel(file.getInputStream(), pojoClass, strings
, titleRows, headerRows
, startRows, startSheetIndex, sheetNum, readRows, needVerfiy);
} catch (IOException e) {
e.printStackTrace();
}
return list;
}
/**
* excel 导入
*
* @param inputStream 文件输入流
* @param titleRows 标题行
* @param headerRows 表头行
* @param needVerfiy 是否检验excel内容
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static<T> List<T> importExcel(InputStream inputStream, Class<T> pojoClass
, String[] strings
, Integer titleRows
, Integer headerRows
, Integer startRows
, Integer startSheetIndex
, Integer sheetNum
, Integer readRows
, boolean needVerfiy) {
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
params.setStartRows(startRows);
params.setStartSheetIndex(startSheetIndex);
params.setSheetNum(sheetNum);
params.setReadRows(readRows);
//params.setSaveUrl("/excel/");
//params.setNeedSave(true);
params.setNeedVerify(needVerfiy);
params.setImportFields(strings);
params.setStartRows(0);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(inputStream, pojoClass, params);
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
/**
* Excel 类型枚举
*/
enum ExcelTypeEnum {
/**
* 文件类型
*/
XLS(".xls"), XLSX(".xlsx");
private String value;
ExcelTypeEnum(String value) {
this.value = value;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
}
public static <T> ExcelImportResult<T> importExcelMore(MultipartFile file
, Class<T> pojoClass
, String[] strings
, Integer titleRows
, Integer headerRows
, Integer startRows
, Integer startSheetIndex
, Integer readRows) throws Exception {
ImportParams params = new ImportParams();
//开启验证，代表导入这里是需要验证的（根据字段上的注解）
params.setNeedVerify(true);
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
params.setStartRows(startRows);
params.setStartSheetIndex(startSheetIndex);
params.setReadRows(readRows);
params.setImportFields(strings);
try {
return ExcelImportUtil.importExcelMore(file.getInputStream(), pojoClass, params);
}catch (NoSuchElementException e) {
throw new RuntimeException("excel数据不能为空,核查后请重新导入");
}catch (Exception e){
/*e.printStackTrace();
return null;*/
throw new RuntimeException("excel模板错误,核查后请重新导入");
}
}
public static Workbook getWorkBook(MultipartFile file) throws IOException {
//这样写excel能兼容03和07
InputStream is = file.getInputStream();
Workbook hssfWorkbook = null;
try {
hssfWorkbook = new HSSFWorkbook(is);
} catch (Exception ex) {
is =file.getInputStream();
hssfWorkbook = new XSSFWorkbook(is);
}
return hssfWorkbook;
}
/**
* 下载模板文件
*/
public static void downLoadFile(String path, String fileName, String contentType){
File file=new File(path);
try (
InputStream in=new FileInputStream(file);
BufferedOutputStream bout=new BufferedOutputStream(ServletUtils.getResponse().getOutputStream());
){
HttpServletResponse response=ServletUtils.getResponse();
response.setContentType(contentType);
response.setCharacterEncoding(StandardCharsets.UTF_8.name());
response.setContentLength(in.available());
response.setHeader("Content-Disposition","attachment;filename="+new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
byte[] buffer=new byte[8192];
int len = 0;
while ((len = in.read(buffer)) != -1) {
bout.write(buffer, 0, len);
}
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* @param str：原字符串
* @param keyword：字符
* @param before：在字符前需要插入的字段
*/
public static String replacementInfo(String str, String keyword, String before) {
StringBuilder sb = new StringBuilder(str);
String rs = str;
try {
//字符第一次出现的位置
int index = sb.indexOf(keyword);
while (index != -1) {
sb.insert(index, before);
//下一次出现的位置，
index = sb.indexOf(keyword, index + before.length() + 1);
}
rs = sb.toString();
} catch (Exception e) {
System.out.println("更换字符错误！！！");
e.printStackTrace();
}
return rs;
}
public static void isExcelFile(MultipartFile file){
if (file == null) {
throw new RuntimeException("请上传excel文件");
}
List<String> list = Arrays.asList(new String[]{ExcelTypeEnum.XLS.getValue(), ExcelTypeEnum.XLSX.getValue()});
if (!list.contains(file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."))))
throw new RuntimeException("请上传正确格式的excel文件");
}
public static HttpServletResponse download(String path, HttpServletResponse response) {
try {
// path是指欲下载的文件的路径。
File file = new File(path);
// 取得文件名。
String filename = file.getName();
// 取得文件的后缀名。
String ext = filename.substring(filename.lastIndexOf(".") + 1).toUpperCase();
// 以流的形式下载文件。
InputStream fis = new BufferedInputStream(new FileInputStream(path));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
// 清空response
response.reset();
// 设置response的Header
response.addHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes()));
response.addHeader("Content-Length", "" + file.length());
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/octet-stream");
toClient.write(buffer);
toClient.flush();
toClient.close();
} catch (IOException ex) {
ex.printStackTrace();
}
return response;
}
public void downLoadExcel(String excelName, HttpServletResponse response) {
response.setCharacterEncoding("UTF-8");
File file=new File(excelName);
response.setContentType("text/html;charset=UTF-8");
try {
response.setHeader("Content-disposition","attachment;filename="+new String(excelName.getBytes(),"iso-8859-1"));
} catch (UnsupportedEncodingException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
OutputStream os = null;
FileInputStream fileInputStream = null;
DataInputStream dataInputStream = null;
try {
os = response.getOutputStream();
fileInputStream = new FileInputStream(file);
dataInputStream = new DataInputStream(fileInputStream);
int r;
while((r=dataInputStream.read())!=-1) {
os.write(r);
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
fileInputStream.close();
dataInputStream.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
浙公网安备 33010602011771号