📄 outputexcelservlet.java
字号:
String[] strConfInfoList=strDBList[i].split(",");
if(strConfInfoList.length<9) continue;
DBConfList[i].TableName=strConfInfoList[0].trim();
DBConfList[i].AliasName=strConfInfoList[1].trim();
DBConfList[i].ParentAliasName=strConfInfoList[6].trim();
DBConfList[i].StartRow=Integer.parseInt(strConfInfoList[2]);
DBConfList[i].StartCol=Integer.parseInt(strConfInfoList[3]);
DBConfList[i].EndRow=Integer.parseInt(strConfInfoList[4]);
DBConfList[i].EndCol=Integer.parseInt(strConfInfoList[5]);
DBConfList[i].IncRow=Integer.parseInt(strConfInfoList[7]);
DBConfList[i].IncCol=Integer.parseInt(strConfInfoList[8]);
System.out.println(strConfInfoList[0]);
}
xmlConfigNode.setText("");
//去掉对行列的限制属性
Namespace defaultNameSpace=Namespace.getNamespace("ss","urn:schemas-microsoft-com:office:spreadsheet");
XPath xmlTablePath=this.newInstance("//ss:Table");
Element xmlTableNode=(Element)xmlTablePath.selectSingleNode(doc);
xmlTableNode.removeAttribute("ExpandedColumnCount",defaultNameSpace);
xmlTableNode.removeAttribute("ExpandedRowCount",defaultNameSpace);
System.out.println("开始读取字段配置");
//设置表格的行号和列号,读取字段数据绑定规则
XPath xmlRowPath=this.newInstance("//ss:Row");
List xmlRowNodes = xmlRowPath.selectNodes(doc);
Iterator listRow = xmlRowNodes.iterator();
for(int i=1;listRow.hasNext();i++)
{
Element xmlRowNode = (Element) listRow.next();
xmlRowNode.setAttribute("Index",String.valueOf(i));
List xmlColNodes = xmlRowNode.getChildren("Cell",defaultNameSpace);
Iterator listCol = xmlColNodes.iterator();
for(int iColIndex=0;listCol.hasNext();)
{
//设置单元格的列号
Element xmlColNode = (Element) listCol.next();
Attribute attr=xmlColNode.getAttribute("Index",defaultNameSpace);
if(null!=attr)
iColIndex=Integer.parseInt(attr.getValue());
else
iColIndex++;
xmlColNode.setAttribute("Index", String.valueOf(iColIndex),defaultNameSpace);
attr=xmlColNode.getAttribute("MergeAcross",defaultNameSpace);
if(null!=attr)
iColIndex += Integer.parseInt(attr.getValue());
Element xmlData=xmlColNode.getChild("Data",defaultNameSpace);
if(null==xmlData) continue;
//首先宏替换,再读取字段信息
String strData=xmlData.getTextTrim();
if(strData=="") continue;
//对单元格数据宏替换
for(int k=0;k<strMacroList.length;k++)
if(strData.indexOf("&"+strMacroList[k]+"#")>=0 && htMacroList.containsKey(strMacroList[k]))
strData = strData.replaceAll("&" + strMacroList[k] + "#",htMacroList.get(strMacroList[k]).toString());
xmlData.setText(strData);
//字段配置
for(int k=0;k<this.DBConfList.length;k++)
{
if(strData.startsWith(this.DBConfList[k].AliasName+"."))
{
FieldConfig field=new FieldConfig();
this.DBConfList[k].FieldList.add(field);
field.FieldName=strData.replaceFirst(this.DBConfList[k].AliasName+".","");
field.TableAliasName=this.DBConfList[k].AliasName;
field.iRow=i;
field.iCol=iColIndex;
xmlData.setText("");
break;
}
}
}//for(int iColIndex=0;listCol.hasNext();)
}
//循环对没有父项的查询表进行绑定,在一个行集范围内绑定完的数据要翻页继续数据绑定
//暂且按照数据区域在页内不变化
System.out.println("开始绑定数据");
int detailRow=0,iPageSize=1;
Element xmlFirstRow=(Element)xmlRowNodes.get(0);
while(detailRow>=0)
{
detailRow=-1;
List xmlRowsClone=this.CloneXmlList(xmlRowNodes);
this.AppendXmlList(xmlRowsClone,xmlFirstRow);
for (int i =0; i < this.DBConfList.length; i++)
{
if (this.DBConfList[i].ParentAliasName.length() > 0)
continue;
this.BandData(this.DBConfList[i], xmlRowsClone);
if (this.DBConfList[i].CurRow >= 0)
detailRow = this.DBConfList[i].CurRow;
}
System.out.println("页码："+(iPageSize++));
}
//去掉原来的模板行
Parent xmlParent=xmlFirstRow.getParent();
for(int i=xmlRowNodes.size()-1;i>-1;i--)
xmlParent.removeContent((Element)xmlRowNodes.get(i));
System.out.println("结束绑定数据");
return doc;
}
//对查询表在指定的行集上数据绑定,并返回当前数据集绑定记录号,绑定完的返回-1;
//对查询表的绑定，同时递归了从数据的绑定
private int BandData(TableConfig curTabConf,List xmlRows) throws Exception
{
ResultSet rsTable=(ResultSet)this.htResultList.get(curTabConf.TableName);
//当前行号-1表示这个绑定是新绑定,如果是从数据需要根据父表过滤
if(curTabConf.CurRow<0)
{
for(int i=0;i<this.DBConfList.length;i++)
if(curTabConf.ParentAliasName.equalsIgnoreCase(DBConfList[i].AliasName))
{
//根据父记录过滤出当前记录集,根据具体应用扩充
rsTable = (ResultSet)this.htResultList.get(curTabConf.TableName);
break;
}
}
rsTable.first();
for(int i=1;i<curTabConf.CurRow;i++)
rsTable.next();
//从当前行开始循环绑定数据
Namespace defaultNameSpace=Namespace.getNamespace("ss","urn:schemas-microsoft-com:office:spreadsheet");
for(int i=0;!rsTable.isAfterLast();i++)
{
//对字段绑定数据,单元格没有新建单元格,Data节点没有的新建
for(int j=0;j<curTabConf.FieldList.size();j++)
{
FieldConfig field=(FieldConfig)curTabConf.FieldList.get(j);
int iRow=field.iRow+i*curTabConf.IncRow;
int iCol=field.iCol+i*curTabConf.IncCol;
//超限处理
if(iRow>curTabConf.EndRow || iCol>curTabConf.EndCol)
{
curTabConf.CurRow=rsTable.getRow();
System.out.println(curTabConf.TableName+"的当前行号："+curTabConf.CurRow);
return rsTable.getRow();
}
String strxPath="ss:Cell[@ss:Index='"+String.valueOf(iCol)+"']";
XPath xmlCellPath=this.newInstance(strxPath);
Element xmlRow=(Element)xmlRows.get(iRow-1);
Element xmlCell=(Element)xmlCellPath.selectSingleNode(xmlRow);
if(null==xmlCell) //新建单元格
{
List cellList=xmlRow.getChildren("Cell",defaultNameSpace);
int iIndexCell=0;
for(int k=0;k<cellList.size();k++)
{
Element ele=(Element)cellList.get(k);
if(Integer.parseInt(ele.getAttributeValue("Index",defaultNameSpace))>iCol)
{
iIndexCell = xmlRow.indexOf(ele);
break;
}
}
xmlCell=new Element("Cell",defaultNameSpace);
xmlCell.setAttribute("Index",String.valueOf(iCol),defaultNameSpace);
xmlRow.addContent(iIndexCell,xmlCell);
}
Element xmlData=xmlCell.getChild("Data",defaultNameSpace);
if(null==xmlData) //新建单元格值节点
{
xmlData=new Element("Data",defaultNameSpace);
xmlData.setAttribute("Type","String",defaultNameSpace);
xmlCell.addContent(0,xmlData);
}
xmlData.setText(rsTable.getString(field.FieldName).trim());
System.out.println("单元格位置："+iRow+","+iCol+" 字段值:"+xmlData.getTextTrim());
}//for(int j=0;j<curTabConf.FieldList.size();j++)
//找到从数据进行绑定,并记下当前输出记录位置
//detailRow=-1表示从数据绑定完成,否则表示从数据需要翻页绑定
int detailRow=-1;
for(int j=0;j<this.DBConfList.length;j++)
{
if(!this.DBConfList[j].ParentAliasName.equalsIgnoreCase(curTabConf.AliasName))
continue;
this.BandData(this.DBConfList[j], xmlRows);
if (this.DBConfList[j].CurRow >= 0)
detailRow = this.DBConfList[j].CurRow;
}
//从数据绑定完才可以下一条,从表记录重新过滤和绑定
if(detailRow<0)
{
for(int j=0;j<this.DBConfList.length;j++)
if(this.DBConfList[j].ParentAliasName.equalsIgnoreCase(curTabConf.AliasName))
this.DBConfList[j].CurRow=-1;
rsTable.next();
}
}//for(int i=0;rsTable.isAfterLast();i++)
curTabConf.CurRow=-1;
return -1;
}
//克隆一个XML节点集
private List CloneXmlList(List xmlSrcList)
{
List xmlCloneList=new ArrayList();
for(int i=0;i<xmlSrcList.size();i++)
{
Element xmlNode = (Element) xmlSrcList.get(i);
Element xmlNodeClone=(Element)xmlNode.clone();
xmlCloneList.add(xmlNodeClone);
}
return xmlCloneList;
}
//把当前XML节点集加入nextNode节点之前
private List AppendXmlList(List xmlList,Element nextNode)
{
Parent parentNode=nextNode.getParent();
for(int i=0;i<xmlList.size();i++)
{
Element xmlNode = (Element) xmlList.get(i);
parentNode.addContent(parentNode.indexOf(nextNode),xmlNode);
}
return xmlList;
}
//得到XPath查询
//http://bbs.salala.com.cn/read.php?tid=2405
private XPath newInstance(String strXPath) throws Exception
{
XPath xmlValuePath = XPath.newInstance(strXPath);
xmlValuePath.addNamespace("","urn:schemas-microsoft-com:office:spreadsheet");
xmlValuePath.addNamespace("o","urn:schemas-microsoft-com:office:office");
xmlValuePath.addNamespace("x","urn:schemas-microsoft-com:office:excel");
xmlValuePath.addNamespace("ss","urn:schemas-microsoft-com:office:spreadsheet");
xmlValuePath.addNamespace("html","http://www.w3.org/TR/REC-html40");
return xmlValuePath;
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -