上一片文章演示了如何根據簡單的Excel文件結構直接生成xls文件，如果涉及到合並，公式之類的復雜操作，可以使用XML結構來直接構造xls文件，比如生成如下所示文件
上圖中D列和E列為公式，第4行為公式合計，7、8行為合並過的單元格。完整代碼如下：
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Text;
usingSystem.XML;
namespaceConsoleApplication17
{
classProgram
{
staticvoidMain(string[]args)
{
ExcelWriterexcel=newExcelWriter();
Excel.CreateSheet("XMLData");//sheetName
//增加一列，默認可以不加
Excel.CreateColumn(5,100);
//新增表頭行
Excel.CreateRow();
Excel.CreateCellString("Name");
Excel.CreateCellString("Score1");
Excel.CreateCellString("Score1");
Excel.CreateCellString("Score0");
Excel.CreateCellString("說明");
//新增兩行數據
Excel.CreateRow();
Excel.CreateCellString("jinjazz");
Excel.CreateCellNumber(100);
Excel.CreateCellNumber(98);
Excel.CreateCell(0,"Number","RC[-2]+RC[-1]",1,1);//公式，-2和-1代表當前cell的水平偏移量
Excel.CreateCell(0,"String","RC[-4]&":"&RC[-1]",1,1);//公式
Excel.CreateRow();
Excel.CreateCellString("游客");
Excel.CreateCellNumber(33);
Excel.CreateCellNumber(14);
Excel.CreateCell(0,"Number","RC[-2]+RC[-1]",1,1);
Excel.CreateCell(0,"String","RC[-4]&":"&RC[-1]",1,1);
//新增匯總行
Excel.CreateRow();
Excel.CreateCellString("總計");
Excel.CreateCell(0,"Number","SUM(R[-2]C:R[-1]C)",1,1);//公式，-2和-1代表cell的垂直偏移量
Excel.CreateCell(0,"Number","SUM(R[-2]C:R[-1]C)",1,1);
Excel.CreateCell(0,"Number","SUM(R[-2]C:R[-1]C)",1,1);
//增加三個空行
Excel.CreateRow();
Excel.CreateRow();
Excel.CreateRow();
//增加一個合並過的單元格
Excel.CreateCell("http://blog.csdn.Net/jinjazz","String",null,2,5);
Excel.Save(@"c:testData.xls");
}
}
publicclassExcelWriter
{
stringssns="urn:schemas-microsoft-com:Office:spreadsheet";
stringXMLns="urn:schemas-microsoft-com:Office:spreadsheet";
XmlDocument_doc=newXMLDocument();
XMLNode_currentSheet=null;
XMLNode_currentRow=null;
publicExcelWriter()
{
//Excel的xml模版，你需要了解XML的Attributes怎麼用
StringBuildersbody=newStringBuilder();
sbody.Append("<?XMLversion="1.0"?>n");
sbody.Append("<?mso-applicationprogid="Excel.Sheet"?>n");
sbody.Append("<WorkbookXMLns="urn:schemas-microsoft-com:Office:spreadsheet"n");
sbody.Append("XMLns:o="urn:schemas-microsoft-com:office:Office"n");
sbody.Append("XMLns:x="urn:schemas-microsoft-com:Office:Excel"n");
sbody.Append("XMLns:ss="urn:schemas-microsoft-com:Office:spreadsheet"n");
sbody.Append("XMLns:html="http://www.w3.org/TR/REC-Html40">n");
sbody.Append("<Styles>n");
sbody.Append("<Styless:ID="Default"ss:Name="Normal">n");
sbody.Append("<Alignmentss:Vertical="Center"/>n");
sbody.Append("<Borders/>n");
sbody.Append("<Fontss:FontName="宋體"x:CharSet="134"ss:Size="10"/>n");
sbody.Append("<Interior/>n");
sbody.Append("<NumberFormat/>n");
sbody.Append("<Protection/>n");
sbody.Append("</Style>n");
sbody.Append("</Styles>n");
sbody.Append("</Workbook>n");
_doc.LoadXML(sbody.ToString());
}
///<summary>
///增加一個工作表
///</summary>
///<paramname="sheetName">工作表名稱</param>
publicvoidCreateSheet(stringsheetName)
{
System.Xml.XmlNodenode=_doc.CreateNode(XMLNodeType.Element,"Worksheet",ssns);
System.Xml.XmlAttributexa=_doc.CreateAttribute("ss","Name",XMLns);
xa.Value=sheetName;
node.Attributes.Append(xa);
_doc.ChildNodes[2].AppendChild(node);
node.AppendChild(_doc.CreateNode(XmlNodeType.Element,"Table",XMLns));
_currentSheet=node;
}
///<summary>
///增加一行
///</summary>
publicvoidCreateRow()
{
System.Xml.XmlNodenode=_doc.CreateNode(XmlNodeType.Element,"Row",XMLns);
_currentSheet.ChildNodes[0].AppendChild(node);
_currentRow=node;
}
///<summary>
///增加一列
///</summary>
///<paramname="index">索引</param>
///<paramname="width">寬度</param>
publicvoidCreateColumn(intindex,floatwidth)
{
System.Xml.XmlNodenode=_doc.CreateNode(XmlNodeType.Element,"Column",XMLns);
System.Xml.XmlAttributexa=_doc.CreateAttribute("ss","Index",XMLns);
xa.Value=index.ToString();
node.Attributes.Append(xa);
xa=_doc.CreateAttribute("ss","Width",XMLns);
xa.Value=width.ToString();
node.Attributes.Append(xa);
_currentSheet.ChildNodes[0].AppendChild(node);
}
///<summary>
///增加一個單元格
///</summary>
///<paramname="value">值</param>
///<paramname="Type">類型</param>
///<paramname="Expression">公式</param>
///<paramname="rowSpan">跨行</param>
///<paramname="colSpan">跨列</param>
publicvoidCreateCell(objectvalue,stringType,stringExpression,introwSpan,intcolSpan)
{
System.Xml.XMLAttributexa=null;
System.Xml.XmlNodenodeCell=_doc.CreateNode(XmlNodeType.Element,"Cell",XMLns);
_currentRow.AppendChild(nodeCell);
if(!string.IsNullOrEmpty(Expression))
{
xa=_doc.CreateAttribute("ss","Formula",XMLns);
xa.Value="="+Expression;
nodeCell.Attributes.Append(xa);
}
if(--colSpan>0)
{
xa=_doc.CreateAttribute("ss","MergeAcross",XMLns);
xa.Value=colSpan.ToString();
nodeCell.Attributes.Append(xa);
}
if(--rowSpan>0)
{
xa=_doc.CreateAttribute("ss","MergeDown",XMLns);
xa.Value=rowSpan.ToString();
nodeCell.Attributes.Append(xa);
}
System.Xml.XmlNodenodeData=_doc.CreateNode(XmlNodeType.Element,"Data",XMLns);
xa=_doc.CreateAttribute("ss","Type",XMLns);
xa.Value=Type;
nodeData.Attributes.Append(xa);
nodeData.InnerText=value.ToString();
nodeCell.AppendChild(nodeData);
}
///<summary>
///增加一個數字單元格
///</summary>
///<paramname="value"></param>
publicvoidCreateCellNumber(doublevalue)
{
CreateCell(value,"Number",null,1,1);
}
///<summary>
///增加一個字符串單元格
///</summary>
///<paramname="value"></param>
publicvoidCreateCellString(stringvalue)
{
CreateCell(value,"String",null,1,1);
}
///<summary>
///保存
///</summary>
///<paramname="strFile"></param>
publicvoidSave(stringstrFile)
{
_doc.Save(strFile);
}
}
}
上面代碼基本都是對xml文件的操作，需要你對XML的dom對象比較熟悉，尤其是Attributes的使用。