1、需求背景
现有excel导入工具可实现数据的读取、入库操作，但是由于代码没有实现通用导入功能和数据处理的功能解耦，导致每增加一种数据处理的需求，就要新建数据导入方法，导致工具类的代码日渐臃肿，不利于代码的维护，又极易出现生产故障，所以这里引入设计模式之观察者模式，使用观察者类接收数据导入通知，实现数据处理和通用代码之间相互解耦。
2、模型设计
观察者模式优化数据导入.jpg
3、优化前代码
public static void importDataBitchFromExcel(InputStream is,Map<String, String> mapping) throws Exception {
// 返回数据
List<Map<String, Object>> ls = new ArrayList<Map<String, Object>>();
// 根据文件名来创建Excel工作薄
Workbook work = null;
try {
work = StreamingReader.builder()
.rowCacheSize(100)
.bufferSize(2048)
.open(is);
//只取第一个sheet
Sheet sheet = work.getSheetAt(0);
System.out.println("---打印总行数------------------"+sheet.getLastRowNum());
//取第一行标题
Iterator<Row> row = sheet.iterator();
// 遍历Excel中所有的sheet
String[] title = null;
int a = 0;
while (row.hasNext()) {
Row rowLine = row.next();
Cell cell = null;
if (title == null) {
title = new String[rowLine.getLastCellNum()];
for (int y = rowLine.getFirstCellNum(); y < rowLine.getLastCellNum(); y++) {
cell = rowLine.getCell(y);
title[y] = (String) getCellValue(cell);
}
}
Map<String, Object> dataMap = new HashMap<>(16);
// 遍历所有的列
for (int s = rowLine.getFirstCellNum(); s < rowLine.getLastCellNum(); s++) {
cell = rowLine.getCell(s);
String key = title[s];
dataMap.put(mapping.get(key), getCellValue(cell));
}
ls.add(dataMap);
//可以看到此处代码属于个性化开发
//不管是打印控制台、入库、入文件，都是和工具代码耦合在一起的。
if(ls.size() == 10000){
for (Map<String, Object> l : ls) {
System.out.println((++a)+":::" + l);
}
ls.clear();
}
}
if(ls.size() != 0){
for (Map<String, Object> l : ls) {
System.out.println((++a)+":::" + l);
}
ls.clear();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (work != null) {
try {
work.close();
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
4、优化后代码
4.1、观察者
public abstract class ExcelImportListener {
public Map<String, String> mapping = new HashMap<>();
public ExcelImportListener(Map<String, String> mapping){
this.mapping = mapping;
}
public abstract void processStart(List<Map<String, Object>> ls);
public abstract void processEnd();
}
4.2、具体观察者
private static class ExcelDataImport extends ExcelImportListener {
private static final Logger logger = LoggerFactory.getLogger(ExcelDataImport.class);
ExcelDataImport(Map<String, String> mapping){
super(mapping);
}
@Override
public void processStart(List<Map<String, Object>> ls) {
// 此处代码只包含业务数据处理逻辑
PrintWriter pw = null;
try {
File file = new File("D:\\xlsxout.txt");
if (!file.exists()) {
file.createNewFile();
}
pw = new PrintWriter(new FileWriter(file, true));
for (int i = 1; i < ls.size(); i++) {
pw.println(ls.get(i));
}
} catch (Exception e) {
logger.error("数据导入异常：：",e);
} finally {
if (pw != null) {
pw.close();
}
}
}
@Override
public void processEnd() {
}
}
4.3、被观察者
public static void main(String[] args) {
try (InputStream is = new FileInputStream(new File("E://6666666.xlsx"))) {
// key=表格中的第一行标题，value=数据库表中的字段名
TreeMap<String, String> mapping = new TreeMap<>();
mapping.put("col1", "col1");
mapping.put("col2", "col2");
mapping.put("col3", "col3");
mapping.put("col4", "col4");
mapping.put("col5", "col5");
mapping.put("col6", "col6");
mapping.put("col7", "col7");
mapping.put("col8", "col8");
mapping.put("col9", "col9");
mapping.put("col10", "col10");
mapping.put("col11", "col11");
mapping.put("col12", "col12");
mapping.put("col13", "col13");
mapping.put("col14", "col14");
ExcelImportListener edi = new ExcelDataImport(mapping);
// 引入观察者类，进行导入数据时发送消息通知
OfficeExcelhandler.getInstance().addListener(edi).importDataBitchFromExcel(is);
} catch (Exception e) {
e.printStackTrace();
}
}
4.5、引入观察者（OfficeExcelhandler）
private static final OfficeExcelhandler INSTANCE = new OfficeExcelhandler();
private ExcelImportListener excelImportListener;
public OfficeExcelhandler addListener(ExcelImportListener excelImportListener) {
this.excelImportListener = excelImportListener;
return INSTANCE;
}
public static OfficeExcelhandler getInstance() {
return INSTANCE;
}
4.4、改造原数据导入方法
public void importDataBitchFromExcel(InputStream is) {
List<Map<String, Object>> ls = new ArrayList<>();
Workbook work = null;
Map<String, String> mapping = this.excelImportListener.mapping;
try {
work = StreamingReader.builder()
.rowCacheSize(100)
.bufferSize(2048)
.open(is);
//只取第一个sheet
Sheet sheet = work.getSheetAt(0);
logger.info(":::打印总行数:::" + sheet.getLastRowNum());
//取第一行标题
Iterator<Row> row = sheet.iterator();
// 遍历Excel中所有的sheet
String[] title = null;
while (row.hasNext()) {
Row rowLine = row.next();
Cell cell = null;
if (title == null) {
title = new String[rowLine.getLastCellNum()];
for (int y = rowLine.getFirstCellNum(); y < rowLine.getLastCellNum(); y++) {
cell = rowLine.getCell(y);
title[y] = (String) getCellValue(cell);
}
}
Map<String, Object> dataMap = new HashMap<>(16);
// 遍历所有的列
for (int s = rowLine.getFirstCellNum(); s < rowLine.getLastCellNum(); s++) {
cell = rowLine.getCell(s);
String key = title[s];
dataMap.put(mapping.get(key), getCellValue(cell));
}
ls.add(dataMap);
if (ls.size() == 10000) {
// 此处代码已经被抽象观察者取代，实现数据导入和业务数据处理的解耦
this.excelImportListener.processStart(ls);
ls.clear();
}
}
if (ls.size() != 0) {
this.excelImportListener.processStart(ls);
ls.clear();
}
this.excelImportListener.processEnd();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (work != null) {
try {
work.close();
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
