package com.highdatas.mdm.util;
|
|
import com.highdatas.mdm.entity.SysField;
|
import com.highdatas.mdm.entity.ViewMappingItem;
|
import lombok.extern.slf4j.Slf4j;
|
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.xssf.streaming.SXSSFSheet;
|
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
|
import javax.servlet.http.HttpServletResponse;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.io.UnsupportedEncodingException;
|
import java.math.BigDecimal;
|
import java.math.RoundingMode;
|
import java.net.URLEncoder;
|
import java.text.SimpleDateFormat;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.List;
|
import java.util.Map;
|
|
/**
|
* @author kimi
|
* @description
|
* @date 2020-04-17 12:16
|
*/
|
|
@Slf4j
|
public class ExcelUtil {
|
|
|
/**
|
* @param in
|
* @param fileName
|
* @return
|
*/
|
public static List<ViewMappingItem> getViewMappingByExcel(InputStream in, String fileName) {
|
|
List list = new ArrayList<>();
|
Workbook work = null;
|
try {
|
// 创建excel工作簿
|
work = getWorkbook(in, fileName);
|
if (null == work) {
|
throw new Exception("创建Excel工作薄为空！");
|
}
|
|
Sheet sheet = null;
|
Row row = null;
|
Cell cell = null;
|
|
for (int i = 0; i < work.getNumberOfSheets(); i++) {
|
|
sheet = work.getSheetAt(i);
|
if (sheet == null) {
|
continue;
|
}
|
|
// 滤过第一行标题
|
for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
|
row = sheet.getRow(j);
|
if (row == null || row.getFirstCellNum() == j) {
|
continue;
|
}
|
|
ViewMappingItem item = new ViewMappingItem();
|
Cell preCell = row.getCell(0);
|
if (preCell != null) {
|
item.setPre(preCell.getStringCellValue());
|
}
|
|
Cell fixCell = row.getCell(1);
|
if (fixCell != null) {
|
item.setFix(getValByCell(fixCell).toString());
|
}
|
|
list.add(item);
|
}
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
return null;
|
} finally {
|
try {
|
work.close();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
return list;
|
}
|
|
private static Object getValByCell(Cell cell) {
|
if (cell == null) {
|
return "";
|
}
|
Object obj = null;
|
switch (cell.getCellTypeEnum()) {
|
case BOOLEAN:
|
obj = cell.getBooleanCellValue();
|
break;
|
case ERROR:
|
obj = cell.getErrorCellValue();
|
break;
|
case FORMULA:
|
try {
|
obj = String.valueOf(cell.getStringCellValue());
|
} catch (IllegalStateException e) {
|
String valueOf = String.valueOf(cell.getNumericCellValue());
|
BigDecimal bd = new BigDecimal(Double.valueOf(valueOf));
|
bd = bd.setScale(2, RoundingMode.HALF_UP);
|
obj = bd;
|
}
|
break;
|
case NUMERIC:
|
obj = cell.getNumericCellValue();
|
break;
|
case STRING:
|
String value = String.valueOf(cell.getStringCellValue());
|
value = value.replace(" ", "");
|
value = value.replace("\n", "");
|
value = value.replace("\t", "");
|
obj = value;
|
break;
|
default:
|
break;
|
}
|
return obj;
|
|
}
|
|
/**
|
* 判断文件格式
|
*
|
* @param in
|
* @param fileName
|
* @return
|
*/
|
private static Workbook getWorkbook(InputStream in, String fileName) throws Exception {
|
|
Workbook book = null;
|
String filetype = fileName.substring(fileName.lastIndexOf("."));
|
|
if (".xls".equals(filetype)) {
|
book = new HSSFWorkbook(in);
|
} else if (".xlsx".equals(filetype)) {
|
book = new XSSFWorkbook(in);
|
} else {
|
throw new Exception("请上传excel文件！");
|
}
|
|
return book;
|
}
|
|
public static void export(HttpServletResponse response, List<Map<String, Object>> importlist, ArrayList<SysField> fieldList) throws UnsupportedEncodingException {
|
//获取数据集
|
List<Map<String, Object>> datalist = importlist;
|
|
//声明一个工作薄
|
SXSSFWorkbook workbook = new SXSSFWorkbook();
|
//生成一个表格
|
SXSSFSheet sheet = workbook.createSheet();
|
//设置表格默认列宽度为15个字节
|
sheet.setDefaultColumnWidth((short) 15);
|
|
//获取字段名数组
|
// String[] tableAttributeName = attributeNames;
|
// //获取对象属性
|
// Field[] fields = ClassUtil.getClassAttribute(importlist.get(0));
|
// //获取对象get方法
|
// List<Method> methodList = ClassUtil.getMethodGet(importlist.get(0));
|
|
//循环字段名数组，创建标题行
|
Row row = sheet.createRow(0);
|
|
for (int j = 0; j < fieldList.size(); j++) {
|
//创建列
|
SysField sysField = fieldList.get(j);
|
Cell cell = row.createCell(j);
|
//设置单元类型为String
|
cell.setCellType(CellType.STRING);
|
cell.setCellValue(sysField.getAlias());
|
}
|
//创建普通行
|
for (int i = 0; i < datalist.size(); i++) {
|
//因为第一行已经用于创建标题行，故从第二行开始创建
|
row = sheet.createRow(i + 1);
|
//如果是第一行就让其为标题行
|
|
Map<String, Object> oneData = datalist.get(i);
|
for (int j = 0; j < fieldList.size(); j++) {
|
//创建列
|
SysField sysField = fieldList.get(j);
|
|
Cell cell = row.createCell(j);
|
cell.setCellType(CellType.STRING);
|
//
|
Object value = oneData.get(sysField.getField());
|
cell.setCellValue(transCellType(value));
|
}
|
}
|
response.setContentType("application/octet-stream;charset=UTF-8");
|
response.setCharacterEncoding("UTF-8");
|
response.setHeader("content-type", "application/octet-stream;charset=UTF-8");
|
//默认Excel名称
|
response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("mapping数据.xlsx", "UTF-8"));
|
|
try {
|
response.flushBuffer();
|
workbook.write(response.getOutputStream());
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
|
}
|
|
private static String transCellType(Object value) {
|
String str = null;
|
if (value instanceof Date) {
|
Date date = (Date) value;
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
str = sdf.format(date);
|
} else {
|
str = String.valueOf(value);
|
if (str == "null") {
|
str = "";
|
}
|
}
|
|
return str;
|
}
|
|
}
|
