前言
个人工作日志:外网数据的导出。
提示:以下是本篇文章正文内容,下面案例可供参考
一、思路?
1.通过配置文件的方式,将所要操作的数据表存入;
2.创建解析XML文件的方法,将xml配置文件中的表信息解析出来;
3.解析过后将sql数据存至集合中,遍历集合数据,将数据导出至Excel表中;
4.对导出的历史数据,做成可下载方式,便于查看历史导出内容;
二、步骤
1.解析XML
创建一个实体类用于接收解析的XML中表的信息:
解析XML代码如下(示例):
/**
* 获取配置sql 集合
* @return
*/
private List<Table> getConfigSql() throws Exception{
List<Table> tableList=new ArrayList();
Element rootElement= new SAXReader().read(Thread.currentThread().getContextClassLoader().getResourceAsStream("exportExcel_to_inside.xml")).getRootElement();
Iterator it = rootElement.elementIterator();
while (it.hasNext()) {
Element table = (Element) it.next();
List<Attribute> tableAttrs = table.attributes();
Table tableEntity=new Table();
for (Attribute attr : tableAttrs) {
PropertyUtils.setProperty(tableEntity,attr.getName(),attr.getValue());
}
Iterator itt = table.elementIterator();
while (itt.hasNext()) {
Element child = (Element) itt.next();
//如果包含子table
if(child.getName().equals("chlidTables")){
Iterator chlidTableIt = child.elementIterator();
while (chlidTableIt.hasNext()) {
//维护childTable数据
Element chlidTableEle = (Element) chlidTableIt.next();
List<Attribute> childTableAttrs = chlidTableEle.attributes();
Table chlidTable=new Table();
for (Attribute attr : childTableAttrs) {
PropertyUtils.setProperty(chlidTable,attr.getName(),attr.getValue());
}
Iterator chlidTableItt = chlidTableEle.elementIterator();
while (chlidTableItt.hasNext()) {//获取childTable的下级标签值
Element chlidTableEleChildEle = (Element) chlidTableItt.next();
PropertyUtils.setProperty(chlidTable,chlidTableEleChildEle.getName(),chlidTableEleChildEle.getStringValue());
}
tableEntity.getChildTableList().add(chlidTable);
}
}else{
PropertyUtils.setProperty(tableEntity,child.getName(),child.getStringValue());
}
}
tableList.add(tableEntity);
}
return tableList;
}
2.XML配置文件
<!--数据表-->
<?xml version="1.0" encoding="UTF-8"?>
<root>
<table name="change_term_record" id="nId" isUpdateLock="true"
filed="nId,nPartyId,vcGroupNo,vcName,nPeriod,nStatus,dtBegin,dtEnd,dtOffice,dtLeave,isDelete"
property="nId,nPartyId,vcGroupNo,vcName,nPeriod,nStatus,dtBegin,dtEnd,dtOffice,dtLeave,isDelete"
condition=" nStatus=-1 and isExport=0 and isDelete=0 ">
<select>select {filed} from change_term_record where nStatus=-1 and isExport=0 and isDelete=0</select>
<chlidTables>
<chlidTable name="change_term_candidate" id="nId"
filed="nId,nPersonId,nPostId,nProcessId,nPoll,isElect,createTime,updateTime,isDelete"
property="nId,nPersonId,nPostId,nProcessId,nPoll,isElect,createTime,updateTime,isDelete">
<select>select {filed} from change_term_candidate where nProcessId in ({id})</select>
</chlidTable>
<chlidTable name="change_term_member" id="nId"
filed="nId,nProcessId,nPersonId,vcPostName,nPoll,vcDicId,createTime,updateTime,isDelete"
property="nId,nProcessId,nPersonId,vcPostName,nPoll,vcDicId,createTime,updateTime,isDelete">
<select>select {filed} from change_term_member where nProcessId in ({id})</select>
</chlidTable>
<chlidTable name="change_term_post" id="nId"
filed="nId,nProcessId,vcPostName,nOrder,nType,createTime,updateTime,isDelete"
property="nId,nProcessId,vcPostName,nOrder,nType,createTime,updateTime,isDelete">
<select>select {filed} from change_term_post where nProcessId in ({id})</select>
</chlidTable>
<chlidTable name="change_term_result" id="nId"
filed="nId,nControlId,nStepId,nProcessId,vcResult,createTime,updateTime,isDelete"
property="nId,nControlId,nStepId,nProcessId,vcResult,createTime,updateTime,isDelete">
<select>select {filed} from change_term_result where nProcessId in ({id})</select>
</chlidTable>
<!--<chlidTable name="change_term_step_record" id="nId">
<select>select * from change_term_step_record where nProcessId = </select>
</chlidTable>-->
</chlidTables>
</table>
</root>
3.导出方法
代码如下(示例):
@Override
public BaseResult exchangeToExcelData(HttpSession session, HttpServletRequest request,HttpServletResponse response) {
Exchange exchange=this.createExchange(session,1);
try {
this.doDataExport(session,request,response,exchange);
}catch (Exception e ){
e.printStackTrace();
exchange.setStatus(2);
exchange.setError(WebUtil.getErrorLog(e));
return new BaseResult(500,e instanceof Exception ?e.getLocalizedMessage():"发生错误","");
}finally {
exchangeMapper.updateExchange(exchange);
}
return new BaseResult(exchange);
}
private void doDataExport(HttpSession session,HttpServletRequest request,HttpServletResponse response, Exchange exchange)throws Exception{
List<Table> tableList=this.getConfigSql();//解析xml 并添加exchange记录
if(CollectionUtils.isNotEmpty(tableList)){
String fileName=UUID.getUUID()+"exchange.xlsx";
String filePath=exportPath+File.separator+fileName;
exchange.setFileName(fileName);
exchange.setFilePath(filePath);
String realPath = session.getServletContext().getRealPath("/");
String inputFile = realPath+excelPath;
try{
int a = exportExcel(inputFile,tableList,exchange); //导出excel表
if (a == 0){
throw new NoExchangeDataException("没有最新数据可导出");
}else if (a == -1){
throw new DataExchangeException("导出数据错误!");
}
}catch (DataExchangeException e){
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
throw e;
}
}else{
throw new DataExchangeException("未读取到导出配置文件exportExcel_to_inside.xml!");
}
}
/**
* 导出excel表
* @param tableList
* @return
*/
private int exportExcel(String inputFile,List<Table> tableList, Exchange exchange){
try {
//1 创建Excel文件
FileInputStream fis=new FileInputStream(inputFile);//创建Excel文件的输入流对象
XSSFWorkbook workbook = new XSSFWorkbook(fis);//根据模板创建Excel工作簿
//2 根据tableList创建sheet
int count = doSheet(tableList, workbook, exchange);
if (count == 0) return 0;
//6 创建Excel文件输出流对象
String outputFile= exchange.getFilePath();
OutputStream os = new FileOutputStream(outputFile);
workbook.write(os);
fis.close();
os.close();
workbook = null;
} catch (IOException e) {
e.printStackTrace();
throw new DataExchangeException("输出流文件路径异常!");
}
return 1;
}
private int doSheet(List<Table> tableList, XSSFWorkbook workbook, Exchange exchange) {
int count = 0;
int sheetIndex = 0;
String now = WebUtil.dateToString(new Date(), "yyyy-MM-dd HH:mm:ss");
for(int a = 0; a < tableList.size(); a++){
Table table = tableList.get(a);
//通过table 拿到每个导出表的集合
String sql = table.getSelect();
sql = sql.replace("{filed}",table.getFiled()); // 替换表字段
table.setSelect(sql);
List<Map<String,Object>> resultList = commonMapper.mapSql(sql);
if (resultList.size() == 0) continue;
count += resultList.size();
XSSFSheet sheet;
if (sheetIndex > (workbook.getNumberOfSheets()-1)){
sheet = workbook.createSheet();//创建工作簿
}else {
sheet = workbook.getSheetAt(sheetIndex);
}
workbook.setSheetName(sheetIndex,table.getName());//给指定的sheet命名
sheetIndex ++;
//3 维护表第一行综合信息
doSheetFirstLine(sheet, table, resultList.size());
//4 维护表第二行表头
doSheetSecondLine(sheet, table);
//5 生成基本数据
//循环resultList 嵌入导出数据
String idValue = ""; // 表中id数组
for (int i = 0; i < resultList.size(); i++) {
XSSFRow row = sheet.createRow(i + 2); // 创建行 放入数据
Map<String, Object> tableMap = resultList.get(i);
// 获取每一层数据 并循环
String[] strings =table.getProperty().split(",");
for (int j = 0; j < strings.length; j++) {
String value = tableMap.get(strings[j]) == null ? "" :tableMap.get(strings[j]).toString();
if (table.getId().equals(strings[j])){ // 当循环到主键时
if (i == 0){
idValue = value;
}else{
idValue = idValue + "," +value;
}
}
XSSFCell cell = row.createCell(j);
cell.setCellValue(value);
}
}
if (table.getChildTableList().size() > 0){ //6 对含有子表数据生成
int child = doChildSheet(sheetIndex,table.getChildTableList(), workbook,idValue);
if (child < 0) return -1;
sheetIndex = child;
}
//7 标记导出数据
int updateCount = doUpdate(table, exchange, resultList, now);
if (updateCount < 0) return -1;
}
return count;
}
private void doSheetFirstLine(XSSFSheet sheet, Table table, int size) {
XSSFRow row = sheet.createRow(0); // 创建第一行 放入数据表基本信息
// 1表名
row.createCell(0);
XSSFCell cell1 = sheet.getRow(0).getCell(0);
cell1.setCellValue(table.getName());
// 2数目
row.createCell(1);
XSSFCell cell2 = sheet.getRow(0).getCell(1);
cell2.setCellValue(size+"");
// 3主键
row.createCell(2);
XSSFCell cell3 = sheet.getRow(0).getCell(2);
cell3.setCellValue(table.getId());
// 4语句
row.createCell(3);
XSSFCell cell4 = sheet.getRow(0).getCell(3);
cell4.setCellValue(table.getSelect());
}
private void doSheetSecondLine(XSSFSheet sheet, Table table) {
//拿到属性的数组
String[] strings =table.getProperty().split(",");
XSSFRow row = sheet.createRow(1); // 创建第二行 放入数据表属性
for (int j = 0; j < strings.length; j++) {
XSSFCell cell = row.createCell(j);
cell.setCellValue(strings[j]);
}
}
private int doUpdate(Table table, Exchange exchange, List<Map<String,Object>> resultList, String now) {
if(table.getIsUpdateLock().equals("true")){
String condition = table.getCondition();
StringBuffer updateSql = new StringBuffer();
if(table.getUpdateType().equals("isExport")){
updateSql.append("UPDATE `").append(table.getName()).append("` SET isExport = ").append(exchange.getnId()).append(" WHERE ").append(condition);
}else{
updateSql.append("UPDATE `").append(table.getName()).append("` SET isExport = ").append(exchange.getnId()).append(",createTime = '").append(now).append("',updateTime = '").append(now).append("' WHERE ").append(condition);
}
int update = commonMapper.updateSql(updateSql.toString());
// 导出数据与修改数据不一致时 错误 不能继续执行
if (update != resultList.size())
return -1;
}
return 1;
}
// 子表sheet
private int doChildSheet(int sheetIndex,List<Table> tableChildList,XSSFWorkbook workbook,String idValue){
for(int a = 0; a < tableChildList.size(); a++){
Table table = tableChildList.get(a);
//通过table 拿到每个导出表的集合
String sql = table.getSelect();
sql = sql.replace("{filed}",table.getFiled()); // 替换表字段
sql = sql.replace("{id}",idValue); // 替换唯一主键内容
table.setSelect(sql);
List<Map<String,Object>> resultList = commonMapper.mapSql(sql);
if (resultList.size() == 0) continue;
XSSFSheet sheet;
if (sheetIndex > (workbook.getNumberOfSheets()-1)){
sheet = workbook.createSheet();//创建工作簿
}else {
sheet = workbook.getSheetAt(sheetIndex);
}
workbook.setSheetName(sheetIndex,table.getName());//给指定的sheet命名
sheetIndex ++;
//3 维护表第一行综合信息
doSheetFirstLine(sheet, table, resultList.size());
//4 维护表第二行表头
doSheetSecondLine(sheet, table);
//5 生成基本数据
//循环resultList 嵌入导出数据
for (int i = 0; i < resultList.size(); i++) {
XSSFRow row = sheet.createRow(i + 2); // 创建行 放入数据
Map<String, Object> tableMap = resultList.get(i);
// 获取每一层数据 并循环
String[] strings =table.getProperty().split(",");
for (int j = 0; j < strings.length; j++) {
XSSFCell cell = row.createCell(j);
String value = tableMap.get(strings[j]) == null ? "" :tableMap.get(strings[j]).toString();
cell.setCellValue(value);
}
}
}
return sheetIndex;
}
总结
提示:这里对文章进行总结:
以上就是今天要讲的内容,本文仅仅简单介绍了个人工作中对Excel导出的相关内容。具体导出Excel的方法请查看API文档。