如何通过xml配置实现一键导入Excel、TXT至数据库的ETL过程？

本文共计1166个文字，预计阅读时间需要5分钟。

使用Excel一键导入XML文件，实现以下功能：导入package com.bjsine.app；包含import语句。

excel通过xml一键导入

package com.bjsine.app; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.util.List; import java.util.Map; import org.springframework.core.io.ClassPathResource; import org.springframework.core.io.Resource; import org.springframework.util.ResourceUtils; import com.bjsine.dao.BaseDao; import com.bjsine.exception.ExcelContext; import com.bjsine.pojo.ExcelImportResult; import com.bjsine.pojo.XmlLine; import com.bjsine.utils.MapUtils; /** * * @author 申晓琛 * */ public class StartImport { //xml文件磁盘路径 private String xmlPath = ""; //Excel文件磁盘路径 private String excelPath = ""; //xml配置文件中配置的id private String excelId = ""; private int startRow; public StartImport(String xmlPath,String excelPath,String excelId,int startRow){ this.xmlPath = xmlPath; this.excelPath = excelPath; this.excelId = excelId; this.startRow = startRow; } /** * 导入数据，返回导入结果集 * @throws * @return ExcelImportResult */ public ExcelImportResult start(){ try { ExcelContext excelContext = new ExcelContext(xmlPath); InputStream fis = null; ExcelImportResult readExcel =null; try{ File file = ResourceUtils.getFile(excelPath); fis = new FileInputStream(file); readExcel = excelContext.readExcel(excelId,startRow, fis); }catch(FileNotFoundException e){ //如果没有找到文件,默认尝试从类路径加载 Resource resource = new ClassPathResource(excelPath); fis = resource.getInputStream(); readExcel = excelContext.readExcel(excelId,startRow, resource.getInputStream()); } return readExcel; } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } /** * 执行sql，基于jdbc * @param listBean dbInfo * @param dbInfo */ public void sqlStart(List > listBean,XmlLine dbInfo){ BaseDao baseDao = new BaseDao(dbInfo.getConnectionURL(),dbInfo.getClazz(),dbInfo.getUserId(),dbInfo.getPassword()); for (Map bean : listBean) { String[] table = dbInfo.getTable(); for (String string : table) { baseDao.importResult(MapUtils.mapToString(bean),string); } } } } xml配置

txt一键导入

package com.bjsine.app; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.util.List; import java.util.Map; import org.springframework.core.io.ClassPathResource; import org.springframework.core.io.Resource; import org.springframework.util.ResourceUtils; import com.bjsine.dao.BaseDao; import com.bjsine.exception.TxtContext; import com.bjsine.pojo.TxtImportResult; import com.bjsine.pojo.XmlLine; import com.bjsine.utils.MapUtils; /** * * @author 申晓琛 * */ public class StartTxt { //xml文件磁盘路径 private String xmlPath = ""; //txt文件磁盘路径 private String txtPath = ""; //xml配置文件中配置的id private String txtId = ""; private int startRow; public StartTxt(String xmlPath,String txtPath,String txtId,int startRow){ this.xmlPath = xmlPath; this.txtPath = txtPath; this.txtId = txtId; this.startRow = startRow; } /** * 导入数据，返回导入结果集 * @throws * @return ExcelImportResult */ public TxtImportResult start(){ try { TxtContext txtContext = new TxtContext(xmlPath); InputStream fis = null; TxtImportResult readTxt =null; try{ File file = ResourceUtils.getFile(txtPath); fis = new FileInputStream(file); readTxt = txtContext.readTxt(txtId,0,fis); }catch(FileNotFoundException e){ //如果没有找到文件,默认尝试从类路径加载 Resource resource = new ClassPathResource(txtPath); fis = resource.getInputStream(); readTxt = txtContext.readTxt(txtId,0,fis); } return readTxt; } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } /** * startIndividuation * @param individuation 字典表（数字对应个性化txt） * @return */ public TxtImportResult startIndividuation(int individuation){ try { TxtContext txtContext = new TxtContext(xmlPath); InputStream fis = null; TxtImportResult readTxt =null; try{ File file = ResourceUtils.getFile(txtPath); fis = new FileInputStream(file); readTxt = txtContext.readTxtIndividuation(txtId,individuation,fis); }catch(FileNotFoundException e){ //如果没有找到文件,默认尝试从类路径加载 Resource resource = new ClassPathResource(txtPath); fis = resource.getInputStream(); readTxt = txtContext.readTxtIndividuation(txtId,individuation,fis); } return readTxt; } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } /** * 执行sql，基于jdbc * @param listBean dbInfo * @param dbInfo */ public void sqlStart(List > listBean,XmlLine dbInfo){ BaseDao baseDao = new BaseDao(dbInfo.getConnectionURL(),dbInfo.getClazz(),dbInfo.getUserId(),dbInfo.getPassword()); for (Map bean : listBean) { String[] table = dbInfo.getTable(); for (String string : table) { baseDao.importResult(MapUtils.mapToString(bean),string); } } } } excel导出

package com.bjsine.app; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.util.List; import java.util.Map; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import com.bjsine.exception.ExcelContext; import com.bjsine.interfaceI.ExcelHeader; import com.bjsine.pojo.XmlLine; /** * * @author 申晓琛 * */ public class StartExport { //xml文件磁盘路径 private String xmlPath = ""; //Excel文件磁盘路径 private String excelPath = ""; //Excel配置文件中配置的id private String excelId = ""; //导出数据 private List > exportData = null; public StartExport(String xmlPath,String excelPath,String excelId,List > exportData){ this.excelId = excelId; this.excelPath = excelPath; this.exportData = exportData; this.xmlPath = xmlPath; } public void start(){ try { OutputStream ops = new FileOutputStream(excelPath); ExcelContext context = new ExcelContext(xmlPath); Workbook workbook = context.createExcel(excelId,exportData,new ExcelHeader() { @Override public void buildHeader(Sheet sheet, XmlLine excelDefinition, List > beans) { /*Row row1 = sheet.createRow(0); Cell cell = row1.createCell(0, 5); //Cell cell = row1.createCell(0); cell.setCellValue("共导出条数据"); Row row2 = sheet.createRow(1); Cell cell2 = row2.createCell(0); Cell cell3 = row2.createCell(1); cell2.setCellValue("本次批次号为:cc"); cell3.setCellValue("作者:常诚");*/ } }); workbook.write(ops); ops.close(); workbook.close(); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } }