工具类
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();
}
}
}
/**
* 文件导出
* fieldList 需要导出字段集合
* lists 导出数据集合 比如List<FieldDefinitionExcelVo>
* object 导出接口对象 比如 FieldDefinitionExcelVo
* excelName 导出报表名称
* response 响应对象
* @return
*/
public static void export(List<String> fieldList,List lists,Object object,String excelName,HttpServletResponse response) {
IExcelWriter xlsWriter = ExcelFactory.getXlsxWriter();
try {
Field[] declared = object.getClass().getDeclaredFields();
Map<String, Boolean> requireMap = getRequireMap(declared, fieldList);
Map<String, Integer> 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();
}
}
/**
* 获取 writeRequire 映射
* @param declared
* @param fieldList
* @return
*/
private static Map<String,Boolean> getRequireMap(Field[] declared, List<String> fieldList){
Map<String,Boolean> require = new HashMap();
for (Field field : declared) {
require.put(field.getName(),fieldList.contains(field.getName()));
}
return require;
}
private static <T>T commonFieldExcel(T vo, Map<String, Boolean> requireMap, Map<String, Integer> 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;
}
/**
* 获取 order 映射
* @param declared
* @param fieldList
* @return
*/
private static Map<String,Integer> getOrderMap(Field[] declared, List<String> fieldList){
Map<String,Integer> 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;
}
/**
* 设置order值
* @param order
* @param value
*/
private static void putMapValue(Map<String, Integer> 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<String, Integer> getModelMap(Sheet sheet) {
Map<String, Integer> map = new HashMap<String, Integer>(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<List<ResourceInfoResult>> 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;
/**
* Created by Administrator on 2019/5/14.
*/
public class ExcelResolveUtils {
/**
* 获取合并单元格的值
*
* @param sheet
* @param row
* @param column
* @return
*/
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;
}
/**
* 判断合并了行
*
* @param sheet
* @param row
* @param column
* @return
*/
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;
}
/**
* 判断指定的单元格是否是合并单元格
*
* @param sheet
* @param row 行下标
* @param column 列下标
* @return
*/
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;
}
/**
* 获取合并区域
*
* @param sheet
* @return
*/
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;
}
/**
* 判断sheet页中是否含有合并单元格
*
* @param sheet
* @return
*/
public static boolean hasMerged(Sheet sheet) {
return sheet.getNumMergedRegions() > 0 ? true : false;
}
/**
* 合并单元格
*
* @param sheet
* @param firstRow 开始行
* @param lastRow 结束行
* @param firstCol 开始列
* @param lastCol 结束列
*/
public static void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
}
/**
* 获取单元格的值
*
* @param cell
* @return
*/
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 " ";
}
/**
* 解决数值类型后面带.0问题
*
* @param cell
* @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 " ";
}
/**
* 解决数值类型后面带.0问题
*
* @param cell
* @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 " ";
}
/**
* 合并单元格处理,获取合并行
*
* @param sheet
* @return List<CellRangeAddress>
*/
public List<CellRangeAddress> getCombineCell(Sheet sheet) {
List<CellRangeAddress> 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<CellRangeAddress> 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;
}
/**
* 判断单元格是否为合并单元格，是的话则将单元格的值返回
*
* @param listCombineCell 存放合并单元格的list
* @param cell 需要判断的单元格
* @param sheet sheet
* @return
*/
public String isCombineCell(List<CellRangeAddress> 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);
}
}
}
