Java后端:
import io.micrometer.core.lang.NonNull;
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 org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
public class ExcelUtil<T> {
static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
private final static String excel2003L =".xls"; //2003- 版本的excel
private final static String excel2007U =".xlsx"; //2007+ 版本的excel
/**
* 将数据导出到Excel中,并输出Excel
* @param params 传入一个Map,键值对包含objects(数据)、headers(表头)类的字段(key)与表头(value)字段一一对应、
* sheetName(sheet名称)、filePath(Excel存放地址)
* @return 提示成功导出。
* @throws Exception IO异常
*/
public Workbook exportExcel(@NonNull Map<String, Object> params) throws Exception{
List<T> objects = (List<T>)params.get("objects");
Map<String, String> headers = (Map<String, String>) params.get("headers"); // 类的字段与表头字段一一对应
String sheetName = (String)params.get("sheetName");
Workbook workbook = new XSSFWorkbook(); //创建表格
Sheet sheet = workbook.createSheet(sheetName); //创建sheet
Set keySet = headers.keySet();
Iterator iterator = keySet.iterator();
List<String> headersParams =new ArrayList<String>();
List<String> headersName =new ArrayList<String>();
while (iterator.hasNext()) {
String key = (String) iterator.next();
headersParams.add(key);
headersName.add(headers.get(key));
}
Row headerRow = sheet.createRow(0); //创建行,行的下标从0开始
for(int i=0; i<headers.size(); i++){
Cell cell = headerRow.createCell(i); //创建单元格,下标从0开始
cell.setCellValue(headersName.get(i));
}
for(int i=1; i<=objects.size(); i++){
Row elementRow = sheet.createRow(i);
Class object = objects.get(i-1).getClass();
T t = objects.get(i-1);
for(int y=0; y<headers.size(); y++) {
String value;
Field field = object.getDeclaredField(headersParams.get(y));
field.setAccessible(true);
if(null != field.get(t)) {
value = field.get(t).toString();
}else {
value = "";
}
Cell cell = elementRow.createCell(y);
cell.setCellValue(value);
}
}
return workbook;
}
/**
* 将数据导入到程序中。
* @param headerMap 类字段与表头对应(key为表头,value为字段)
* @param param 类对象
* @return 得到的类List
* @throws Exception
*/
public List<T> ImportExcel(MultipartFile file, Map<String, String> headerMap, T param) throws Exception{
String fileName = file.getOriginalFilename();
String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
if (!("xml".equals(suffix) || "xlsx".equals(suffix))) {
throw new RuntimeException("文件类型不正确,仅能导入Excel文件");
}
Workbook workbook = new XSSFWorkbook(file.getInputStream());
Sheet sheet1 = null;
Class tclass = param.getClass();
List<T> list = new ArrayList<T>();
for(int n = 0; n< workbook.getNumberOfSheets(); n++) {
sheet1 = workbook.getSheetAt(n);
if (sheet1 == null) {
continue;
}
break;
}
Row row1 = sheet1.getRow(0);
Short cellNum = row1.getLastCellNum();
Object[] headers = new Object[cellNum];
String[] headerMap1 = new String[cellNum];
for (short y = 0; y < cellNum; y++) {
Cell cell = row1.getCell(y);
Object object = this.getCellValue(cell);
headers[y] = object;
}
for(short y = 0; y < cellNum; y++){
headerMap1[y] = headerMap.get(headers[y]);
}
for(int n = 0; n< workbook.getNumberOfSheets(); n++) {
Sheet sheet = workbook.getSheetAt(n);
if (sheet == null) {
continue;
}
int lastRowNum = sheet.getLastRowNum();
for (int i = 1; i <= lastRowNum; i++) {
T t = (T) tclass.newInstance();
Row row = sheet.getRow(i);
if(null==row){
continue;
}
short lastCellNum = row.getLastCellNum();
for (short y = 0; y < lastCellNum; y++) {
Cell cell = row.getCell(y);
if (null == cell) {
continue;
}
Object cellValue = this.getCellValue(cell);
if (null != headerMap1[y] && null != cellValue) {
Field field = tclass.getDeclaredField(headerMap1[y]);
field.setAccessible(true);
Class fieldType = field.getType();
String fieldTypeName = fieldType.getName();
if ("java.lang.String".equals(fieldTypeName)) {
field.set(t, cellValue.toString());
logger.info("cellValue" + i + ": " + cellValue);
} else if ("int".equals(fieldTypeName)) {
int f = Integer.parseInt(cellValue.toString());
field.set(t, f);
logger.info("cellValue" + i + ": " + cellValue);
} else if ("java.lang.Long".equals(fieldTypeName)) {
long f = Long.parseLong(cellValue.toString());
field.set(t, f);
} else if ("java.math.BigDecimal".equals(fieldTypeName)) {
field.set(t, new BigDecimal(cellValue.toString()));
}
}
}
list.add(t);
}
}
return list;
}
/**
* 描述:对表格中数值进行格式化
* @param cell
* @return
*/
public static Object getCellValue(Cell cell) {
Object cellValue = null;
DecimalFormat df = new DecimalFormat("0"); //格式化number String字符
SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd hh:mm:ss"); //日期格式化
DecimalFormat df2 = new DecimalFormat("0.00"); //格式化数字
if (null != cell) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
cellValue = df2.format(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
cellValue = cell.getBooleanCellValue();
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
cellValue = cell.getCellFormula();
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
cellValue = "";
break;
case HSSFCell.CELL_TYPE_ERROR: // 错误
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
return null;
}
}
// 导出Excel
@PostMapping("/api/getContractExcel")
public void getContractExcel(@RequestBody ContractQueryEntity contractQueryEntity, HttpServletResponse response) throws Exception {
ExcelUtil<ContractEntity> excelUtil = new ExcelUtil<ContractEntity>();
List<ContractEntity> contractEntityList = contractService.getContractExcel(contractQueryEntity);
Map<String, String> headers = new LinkedHashMap<String, String>();
headers.put("name", "名称");
headers.put("type", "类型");
headers.put("strRelation", "是否关联");
Map<String, Object> params = new HashMap<String, Object>();
params.put("headers", headers);
params.put("objects", contractEntityList);
params.put("sheetName", "nam表");
Workbook workbook = excelUtil.exportExcel(params);
response.reset();
String downloadFielName = new String( "name表".getBytes("UTF-8"),"iso-8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + downloadFielName + ".xlsx");
response.setHeader("Pragma", "no-cache");
response.setCharacterEncoding("utf-8");
response.setHeader("Cache-Control", "no-cache");
response.setContentType("application/vnd.openxmlformats- officedocument.spreadsheetml.sheet;charset=UTF-8");
response.setDateHeader("Expires", 0);
OutputStream output;
output = response.getOutputStream();
BufferedOutputStream(output);
workbook.write(output);
output.close();
}
// 导入Excel
@Transactional
@PostMapping("/api/addContractExcel")
public void addContractExcel(@RequestParam("file")MultipartFile file, Authentication authentication) throws Exception {
ExcelUtil<RelationPartyEntity> excelUtil = new ExcelUtil<RelationPartyEntity>();
HashMap<String, String> headerMap = new LinkedHashMap<String, String>();
headerMap.put("关联", "relationParty");
headerMap.put("珠子", "registeredCapital");
headerMap.put("备注", "relationRemarks");
// 提取Excel中的数据
List<PartyEntity> entityList = excelUtil.ImportExcel(file, headerMap, new PartyEntity());
if(null !=entityList && relationPartyEntityList.size() != 0) {
// 业务判断 操作
Iterator iterator = relationPartyEntityList.iterator();
while (iterator.hasNext()) {
PartyEntity relationPartyEntity = (PartyEntity) iterator.next();
}
List<PartyEntity> PartyEntityAll = contractService.getRelationPartyAll();
contractService.deleteRelationPartyAll();
// 将数据插入数据库
contractService.addRelationPartyList(relationPartyEntityList, createdBy);
}
}
前端:vue
import { getContractExcel } from '@/api/contract'
getContractExcel(this.contractQueryEntity).then(res => { // 调用接口
var blob = new Blob([res.data], { type: ‘application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;’ }) // type这里表示xlsx类型
let fileName = res.headers['content-disposition'].split(';')[1].split('=')[1].split('.')[0]
console.log('fileName:' + fileName)
var downloadElement = document.createElement('a')
var href = window.URL.createObjectURL(blob) // 创建下载的链接
downloadElement.href = href
downloadElement.download = 'name表.xlsx' // 下载后文件名
// downloadElement.download = decodeURIComponent(fileName)
// console.log(' decodeURIComponent(fileName):' + decodeURIComponent(fileName))
document.body.appendChild(downloadElement)
downloadElement.click() // 点击下载
document.body.removeChild(downloadElement) // 下载完成移除元素
window.URL.revokeObjectURL(href) // 释放掉blob对象
})
import httpRequest from '@/libs/httpRequest'
**export const getContractExcel = (entity) => {
return httpRequest.request({
url: '/api/getContractExcel',
method: 'post',
data: entity,
responseType: 'blob'
})
}**
office 所有后缀对应的 content-type:
后缀 | MIME Type |
---|---|
.doc | application/msword |
.dot | application/msword |
.docx | application/vnd.openxmlformats-officedocument.wordprocessingml.document |
.dotx | application/vnd.openxmlformats-officedocument.wordprocessingml.template |
.docm | application/vnd.ms-word.document.macroEnabled.12 |
.dotm | application/vnd.ms-word.template.macroEnabled.12 |
.xls | application/vnd.ms-excel |
.xlt | application/vnd.ms-excel |
.xla | application/vnd.ms-excel |
.xla | application/vnd.ms-exce |
.xlsx | application/vnd.openxmlformats-officedocument.spreadsheetml.sheet |
.xltx | application/vnd.openxmlformats-officedocument.spreadsheetml.template |
.xlsm | application/vnd.ms-excel.sheet.macroEnabled.12 |
.xltm | application/vnd.ms-excel.template.macroEnabled.12 |
.xlam | application/vnd.ms-excel.addin.macroEnabled.12 |
.xlsb | application/vnd.ms-excel.sheet.binary.macroEnabled.12 |
.ppt | application/vnd.ms-powerpoint |
.pot | application/vnd.ms-powerpoint |
.pps | application/vnd.ms-powerpoint |
.ppa | application/vnd.ms-powerpoint |
.pptx | application/vnd.openxmlformats-officedocument.presentationml.presentation |
.potx | application/vnd.openxmlformats-officedocument.presentationml.template |
.ppsx | application/vnd.openxmlformats-officedocument.presentationml.slideshow |
.ppam | application/vnd.ms-powerpoint.addin.macroEnabled.12 |
.pptm | application/vnd.ms-powerpoint.presentation.macroEnabled.12 |
.potm | application/vnd.ms-powerpoint.presentation.macroEnabled.12 |
.ppsm | application/vnd.ms-powerpoint.slideshow.macroEnabled.12 |