工具类
public class ExcelUtils {
private static final Logger logger = LoggerFactory.getLogger(FieldService.class);
public static Response downLoad(String modelName, HttpServletResponse response) throws IOException {
ResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
org.springframework.core.io.Resource[] resources = null;
String fileName = "";
resources = resourcePatternResolver.getResources(CLASS_PATH + TEMPLATE + modelName);
fileName = modelName;
org.springframework.core.io.Resource resource = resources[0];
URI uri = resource.getURI();
logger.info(uri.toString());
InputStream inputStream = null;
OutputStream out = null;
try {
//根据文件在服务器的路径读取该文件转化为流
inputStream = resource.getInputStream();
//创建一个Buffer字符串
byte[] buffer = new byte[1024];
//设置文件ContentType类型，这样设置，会自动判断下载文件类型
response.setContentType(MULTIPART_FORM_DATE);
//设置文件头：最后一个参数是设置下载文件名（设置编码格式防止下载的文件名乱码）
response.setHeader(CONTENT_DISPOSITION, ATTACHMENT_FILENAME+new String( fileName.getBytes(UTF_8), CHARSET_NAME ));
out = response.getOutputStream();
int b = 0;
while (b != -1){
b = inputStream.read(buffer);
//写到输出流(out)中
out.write(buffer,0,b);
}
return new Response(RespCode.DOWNLOAD_MODEL_FINISH);
}catch (Exception e){
e.printStackTrace();
return new Response(RespCode.DOWNLOAD_MODEL_FAIL);
}finally {
try {
inputStream.close();
out.close();
out.flush();
}catch (Exception e){
e.printStackTrace();
}
}
}
public static void export(List fieldList,List lists,Object object,String excelName,HttpServletResponse response) {
IExcelWriter xlsWriter = ExcelFactory.getXlsxWriter();
try {
Field[] declared = object.getClass().getDeclaredFields();
Map requireMap = getRequireMap(declared, fieldList);
Map orderMap = getOrderMap(declared, fieldList);
for (Object vo : lists) {
commonFieldExcel(vo,requireMap,orderMap);
}
xlsWriter.target(response,excelName);
xlsWriter.write(lists);
}catch (Exception e){
logger.error("导出失败");
throw new BizException(RespCode.EXPORT_FAILED);
}finally {
xlsWriter.flush();
}
}
private static Map getRequireMap(Field[] declared, List fieldList){
Map require = new HashMap();
for (Field field : declared) {
require.put(field.getName(),fieldList.contains(field.getName()));
}
return require;
}
private static T commonFieldExcel(T vo, Map requireMap, Map orderMap) throws NoSuchFieldException, IllegalAccessException {
Field[] declaredFields = vo.getClass().getDeclaredFields();
for (Field voo : declaredFields) {
if(requireMap.containsKey(voo.getName())){
Boolean bool = requireMap.get(voo.getName());
Integer order = orderMap.get(voo.getName());
Field f = vo.getClass().getDeclaredField(voo.getName());
ExcelField annotation = f.getAnnotation(ExcelField.class);
if(StringUtils.isEmpty(annotation)){
continue;
}
InvocationHandler handler = Proxy.getInvocationHandler(annotation);
Field hField = handler.getClass().getDeclaredField(MEMBER_VALUES);
hField.setAccessible(true);
Map memberValues = (Map) hField.get(handler);
memberValues.put(WRITE_REQUIRE, bool);
memberValues.put(ORDER, order);
}
}
return vo;
}
private static Map getOrderMap(Field[] declared, List fieldList){
Map orderMap = new HashMap();
for (int i = 0; i < fieldList.size(); i++) {
orderMap.put(fieldList.get(i),i);
}
for (Field field : declared) {
putMapValue(orderMap,field.getName());
}
return orderMap;
}
private static void putMapValue(Map order, String value){
if(!order.containsKey(value)){
order.put(value,ZERO);
}
}
}
//判断模板是否匹配
private Boolean scanModelType(Sheet sheet,String moduleName) {
try {
InputStream inputStream;
inputStream = new ClassPathResource(TEMPLATE + moduleName).getInputStream();
assert inputStream != null;
Workbook sheets = WorkbookFactory.create(inputStream);
Sheet model = sheets.getSheetAt(0);
String modelType = getModelType(model);
Row cells = sheet.getRow(1);
for (int i = 1; i < cells.getLastCellNum(); i++) {
String s = ExcelResolveUtils.getCellValue(cells.getCell(i));
if (!modelType.contains(s)) {
return false;
}
}
return true;
} catch (Exception e) {
e.printStackTrace();
}
return false;
}
private Map getModelMap(Sheet sheet) {
Map map = new HashMap(10);
Row cells = sheet.getRow(1);
for (int i = 0; i < cells.getLastCellNum(); i++) {
String s = ExcelResolveUtils.getCellValue(cells.getCell(i));
map.put(s.trim(), i);
}
return map;
}
通用导入
Workbook workbook=null;
try {
workbook = WorkbookFactory.create(file.getInputStream());
// 获取当前sheet index索引
int activeSheetIndex;
activeSheetIndex = workbook.getActiveSheetIndex();
Sheet sheet = workbook.getSheetAt(activeSheetIndex);
// 新的模板判断
Boolean bool = Workbook workbook=null;
try {
workbook = WorkbookFactory.create(file.getInputStream());
int activeSheetIndex;
activeSheetIndex = workbook.getActiveSheetIndex();
Sheet sheet = workbook.getSheetAt(activeSheetIndex);
// 新的模板判断
Boolean bool = scanModelType(sheet);
if (!bool) {
logger.error("批量创建失败");
return new Response(RespCode.DOWNLOAD_MODEL_Submit);
}
// 判断类型 ，选择分支
List> fieldDefinitionList = getList(sheet, actionType);
return new Response(fieldDefinitionList);
} catch (Exception e) {
logger.error("导入失败");
throw new BizException(RespCode.UPLOAD_IN_FALSE);
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (Exception e) {
logger.error("批量创建失败：{}", e.getMessage());
}
}
工具类
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class ExcelResolveUtils {
public static String getMergedRegionValue(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
Row fRow = sheet.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
return getCellValue(fCell);
}
}
}
return null;
}
public static boolean isMergedRow(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (row == firstRow && row == lastRow) {
if (column >= firstColumn && column <= lastColumn) {
return true;
}
}
}
return false;
}
public static boolean isMergedRegion(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
return true;
}
}
}
return false;
}
public static MergedRegion getMergedRegion(Sheet sheet, Cell firstCell) {
MergedRegion mergedRegion = new MergedRegion();
int row = firstCell.getRowIndex();
int column = firstCell.getColumnIndex();
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
mergedRegion.setLength(lastColumn - firstColumn + 1);
mergedRegion.setWidth(lastRow - firstRow + 1);
return mergedRegion;
}
} else {
mergedRegion.setLength(1);
mergedRegion.setWidth(1);
}
}
return mergedRegion;
}
public static boolean hasMerged(Sheet sheet) {
return sheet.getNumMergedRegions() > 0 ? true : false;
}
public static void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
}
public static String getCellValue(Cell cell) {
if (cell == null) return BLANK_SPACE;
if (cell.getCellType() == CellType.STRING) {
return cell.getStringCellValue();
} else if (cell.getCellType() == CellType.BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == CellType.FORMULA) {
try {
return String.valueOf(cell.getNumericCellValue());
} catch (IllegalStateException e) {
return String.valueOf(cell.getRichStringCellValue());
}
} else if (cell.getCellType() == CellType.NUMERIC) {
return String.valueOf(cell.getNumericCellValue());
}
return " ";
}
public static String getCellValueNoDouble(Cell cell) {
if (cell == null) return BLANK_SPACE;
if (cell.getCellType() == CellType.STRING) {
return cell.getStringCellValue();
} else if (cell.getCellType() == CellType.BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == CellType.FORMULA) {
return cell.getCellFormula();
} else if (cell.getCellType() == CellType.NUMERIC) {
if ((long) cell.getNumericCellValue() == cell.getNumericCellValue()) {
return String.valueOf((long) cell.getNumericCellValue());
} else {
return String.valueOf(cell.getNumericCellValue());
}
}
return " ";
}
public static String getCellValueNoDoubleAndFormula(Cell cell) {
if (cell == null) return BLANK_SPACE;
if (cell.getCellType() == CellType.STRING) {
return cell.getStringCellValue();
} else if (cell.getCellType() == CellType.BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == CellType.FORMULA) {
return String.valueOf(cell.getNumericCellValue());
} else if (cell.getCellType() == CellType.NUMERIC) {
if ((long) cell.getNumericCellValue() == cell.getNumericCellValue()) {
return String.valueOf((long) cell.getNumericCellValue());
} else {
return String.valueOf(cell.getNumericCellValue());
}
}
return " ";
}
public List getCombineCell(Sheet sheet) {
List list = new ArrayList<>();
//获得一个 sheet 中合并单元格的数量
int sheetmergerCount = sheet.getNumMergedRegions();
//遍历所有的合并单元格
for (int i = 0; i < sheetmergerCount; i++) {
//获得合并单元格保存进list中
CellRangeAddress ca = sheet.getMergedRegion(i);
list.add(ca);
}
return list;
}
public static int getRowNum(List listCombineCell, Cell cell, Sheet sheet) {
int xr = 0;
int firstC = 0;
int lastC = 0;
int firstR = 0;
int lastR = 0;
for (CellRangeAddress ca : listCombineCell) {
//获得合并单元格的起始行, 结束行, 起始列, 结束列
firstC = ca.getFirstColumn();
lastC = ca.getLastColumn();
firstR = ca.getFirstRow();
lastR = ca.getLastRow();
if (cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {
if (cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) {
xr = lastR;
}
}
}
return xr;
}
public String isCombineCell(List listCombineCell, Cell cell, Sheet sheet)
throws Exception {
int firstC = 0;
int lastC = 0;
int firstR = 0;
int lastR = 0;
String cellValue = null;
for (CellRangeAddress ca : listCombineCell) {
//获得合并单元格的起始行, 结束行, 起始列, 结束列
firstC = ca.getFirstColumn();
lastC = ca.getLastColumn();
firstR = ca.getFirstRow();
lastR = ca.getLastRow();
if (cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {
if (cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) {
Row fRow = sheet.getRow(firstR);
Cell fCell = fRow.getCell(firstC);
cellValue = getCellValue(fCell);
break;
}
} else {
cellValue = "";
}
}
return cellValue;
}
public static boolean isContainChinese(String str) {
Pattern p = Pattern.compile(REGEX_CHINESE);
Matcher m = p.matcher(str);
return m.find();
}
public static BigDecimal doubleToBigDecimalDefault0(Double value) {
if (null == value) {
return BigDecimal.ZERO;
} else {
return BigDecimal.valueOf(value);
}
}
public static BigDecimal doubleToBigDecimalDefault1(Double value) {
if (null == value) {
return BigDecimal.ONE;
} else {
return BigDecimal.valueOf(value);
}
}
public static BigDecimal doubleToBigDecimalDefaultNull(Double value) {
if (null == value) {
return null;
} else {
return BigDecimal.valueOf(value);
}
}
}
