采用JDBC的設(shè)計模式開發(fā)導(dǎo)出數(shù)據(jù)表格為excel表功能
1、需求分析
- 提供統(tǒng)一的控制器Controller抖韩，統(tǒng)一的生成Excel表的Service方法蛀恩。
- 每個模塊的開發(fā)者根據(jù)自身業(yè)務(wù)需求組裝excel的表頭和表身數(shù)據(jù)以Map(表頭數(shù)據(jù))和JSONArray組裝的JSONObject(表身數(shù)據(jù))
2、實(shí)現(xiàn)思想
- 控制器接受各個模塊的實(shí)現(xiàn)類茂浮、page双谆、rows、sort等基本分頁信息
- service中使用反射Class.forName將模塊實(shí)現(xiàn)類加載到JVM中席揽，隨后取出實(shí)現(xiàn)類的數(shù)據(jù)交給生成Excel方法進(jìn)行excel表格文件生成顽馋。
3、實(shí)現(xiàn)代碼以及步驟
- 聲明一個接受實(shí)現(xiàn)類名幌羞，page寸谜、row、sort等屬性的實(shí)體類
public class PrintVo {
@NotNull
public String className;
public Integer page;
public Integer rows;
public String sort;
public String order;
public String filterRules;
// 節(jié)省篇幅不寫getter/setter方法
}
- 聲明一個獲取數(shù)據(jù)的接口
public interface GetDataForExcel {
public ExcelDataVo getData(PrintVo print);
}
- 用于獲取對應(yīng)的實(shí)現(xiàn)類的manager
public class GetDataForExcelManager {
public static GetDataForExcel getDataForExcel = null;
// 注冊獲取excel方法信息
public static void registerGetDataForExcel(GetDataForExcel data) {
getDataForExcel = data;
}
// 獲取excel方法
public static GetDataForExcel getGetDataForExcel() {
return getDataForExcel;
}
}
- 控制器層接受參數(shù)并調(diào)用service
public Result print(PrintVo printVo, HttpServletResponse response) {
if (printVo.getClassName() == null) {
return new Result(false, -1, "打印失敗","打印參數(shù)有誤","打印參數(shù)有誤");
}
InputStream in = exportExcelService.generate(printVo);
if (in == null) {
return new Result(false, -1, "導(dǎo)出失敗", "導(dǎo)出表格失敗", "導(dǎo)出失敗属桦，請稍后重新嘗試");
}
response.setContentType("application/vnd.ms-excel");
try {
response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode("表格導(dǎo)出.xlsx", "UTF-8"));
FileCopyUtils.copy(in, response.getOutputStream());
} catch (UnsupportedEncodingException e1) {
e1.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
// 這句代碼執(zhí)行不到咯
return new Result(false, -1, "導(dǎo)出成功", "導(dǎo)出表格成功", "導(dǎo)出成功");
}
- Service層使用Class.forName加載實(shí)現(xiàn)類到虛擬機(jī)中并獲取數(shù)據(jù)執(zhí)行生成excel操作
public InputStream generate(PrintVo printVo) {
try {
// 1熊痴、反射調(diào)用對應(yīng)的實(shí)現(xiàn)類
Class.forName(printVo.getClassName());
GetDataForExcel data = GetDataForExcelManager.getGetDataForExcel();
// 2、傳參到對應(yīng)方法并獲取表頭表身信息
ExcelDataVo excelData = data.getData(printVo);
InputStream in = toExcel("POITest", "table export Excel", excelData.getHead(), excelData.getBody());
return in;
} catch (ClassNotFoundException e) {
e.printStackTrace();
return null;
}
}
- 以下是舉例某一模塊的代碼聂宾，也就是不管有多少模塊果善，都只需要遵循實(shí)現(xiàn)這個接口，并注冊一個manager靜態(tài)類即可
@Service
public class ContractMainPrintImpl implements GetDataForExcel{
@Autowired
private ContractMaintenanceService mContractMaintenanceService;
@Override
public ExcelDataVo getData(PrintVo print) {
PageVo vo = new PageVo();
vo.setPage(print.getPage());
vo.setRows(print.getRows());
vo.setSort(print.getSort());
vo.setOrder(print.getOrder());
// 這些代碼是為了調(diào)用其它的service查詢對應(yīng)的數(shù)據(jù)
List<Map> queryList = new ArrayList<Map>();
String rules = print.getFilterRules();
if(StringUtils.isNotBlank(rules)){
ObjectMapper mapper = new ObjectMapper();
try {
queryList = (List<Map>)mapper.readValue(rules, queryList.getClass());
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
PageInfo<ContractMaintenanceVo> contractPage = mContractMaintenanceService.listAllPage(vo, queryList);
// 組裝表頭
Map<String, String> heads = new HashMap<>();
heads.put("0", "表頭1");
heads.put("1", "表頭2");
heads.put("2", "表頭3");
heads.put("3", "表頭4");
heads.put("4", "表頭5");
// 組裝表身
JSONArray bodys = new JSONArray();
List<ContractMaintenanceVo> contractMaintenances = contractPage.getList();
contractMaintenances.stream().forEach(c -> {
JSONObject jsonObject = new JSONObject();
jsonObject.put("0", c.getInstitutionName());
jsonObject.put("1", c.getAccountingCode());
jsonObject.put("2", c.getInstitutionCode());
jsonObject.put("3", c.getLicenseRegistrationCode());
jsonObject.put("4", c.getLnstitutionalLevel());
bodys.add(jsonObject);
});
ExcelDataVo excelData = new ExcelDataVo();
excelData.setType("ContractMaintenanceVo");
excelData.setBody(bodys);
excelData.setFileName("合同簽約主體信息表");
excelData.setHead(heads);
return excelData;
}
}
// 在spring啟動的時候會將對應(yīng)實(shí)現(xiàn)類注入進(jìn)來
@Component
public class ContractMainPrintManager {
private static ContractMainPrintImpl mContractMainPrintImpl;
@Autowired
public void setContractMainPrintImpl(ContractMainPrintImpl contractMainPrintImpl) {
ContractMainPrintManager.mContractMainPrintImpl = contractMainPrintImpl;
GetDataForExcelManager.registerGetDataForExcel(mContractMainPrintImpl);
}
}
前臺發(fā)送的請求只需要帶上實(shí)現(xiàn)類的類名以及對應(yīng)實(shí)現(xiàn)類需要的參數(shù)即可
tips
使用POI生成excel的方法在此暫不展示了系谐，畢竟這個不屬于這個話題中研究的問題巾陕，如有需要poi使用的可以在評論中留言。
小結(jié)
很早之前就知道JDBC加載驅(qū)動的方式就是聲明接口蔚鸥，然后將實(shí)現(xiàn)類讓各個數(shù)據(jù)庫的廠商進(jìn)行實(shí)現(xiàn)惜论，因此在遇到這種情況的時候也可以想起使用這種模式，聲明一個接口止喷，交給對應(yīng)的實(shí)現(xiàn)類去實(shí)現(xiàn)馆类，最后由統(tǒng)一方法進(jìn)行數(shù)據(jù)整合輸出即可！
