上一片文章演示了如何根據簡單的excel文件結構直接生成xls文件，如果涉及到合並，公式之類的復 雜操作，可以使用xml結構來直接構造xls文件，比如生成如下所示文件
上圖中D列和E列為 公式，第4行為公式合計，7、8行為合並過的單元格。完整代碼如下：
using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
namespace ConsoleApplication17
{
class Program
{
static void Main( string [] args)
{
ExcelWriter excel = new ExcelWriter();
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" );
}
}
public class ExcelWriter
{
string ssns = "urn:schemas-microsoft-com:office:spreadsheet" ;
string xmlns = "urn:schemas-microsoft-com:office:spreadsheet" ;
XmlDocument _doc = new XmlDocument();
XmlNode _currentSheet = null ;
XmlNode _currentRow = null ;
public ExcelWriter()
{
//excel的xml模版，你需要了解xml的Attributes怎麼用
StringBuilder sbody = new StringBuilder();
sbody.Append( "<?xml version=\"1.0\"?>\n" );
sbody.Append( "<?mso-application progid=\"Excel.Sheet\"?>\n" );
sbody.Append( "<Workbook xmlns=\"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( "<Style ss:ID=\"Default\" ss:Name=\"Normal\">\n" );
sbody.Append( "<Alignment ss:Vertical=\"Center\"/>\n" );
sbody.Append( "<Borders/>\n" );
sbody.Append( "<Font ss: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>
/// <param name="sheetName">工作表名稱</param>
public void CreateSheet( string sheetName)
{
System.Xml.XmlNode node = _doc.CreateNode(XmlNodeType.Element, "Worksheet" , ssns);
System.Xml.XmlAttribute xa = _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>
public void CreateRow()
{
System.Xml.XmlNode node = _doc.CreateNode(XmlNodeType.Element, "Row" , xmlns);
_currentSheet.ChildNodes[0].AppendChild(node);
_currentRow = node;
}
/// <summary>
/// 增加一列
/// </summary>
/// <param name="index">索引</param>
/// <param name="width">寬度</param>
public void CreateColumn( int index, float width)
{
System.Xml.XmlNode node = _doc.CreateNode(XmlNodeType.Element, "Column" , xmlns);
System.Xml.XmlAttribute xa = _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>
/// <param name="value">值</param>
/// <param name="Type">類型</param>
/// <param name="Expression">公式</param>
/// <param name="rowSpan">跨行</param>
/// <param name="colSpan">跨列</param>
public void CreateCell( object value, string Type, string Expression, int rowSpan, int colSpan)
{
System.Xml.XmlAttribute xa = null ;
System.Xml.XmlNode nodeCell = _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.XmlNode nodeData = _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>
/// <param name="value"></param>
public void CreateCellNumber( double value)
{
CreateCell(value, "Number" , null , 1, 1);
}
/// <summary>
/// 增加一個字符串單元格
/// </summary>
/// <param name="value"></param>
public void CreateCellString( string value)
{
CreateCell(value, "String" , null , 1, 1);
}
/// <summary>
/// 保存
/// </summary>
/// <param name="strFile"></param>
public void Save( string strFile)
{
_doc.Save(strFile);
}
}
}
上面代碼基本都是對xml文件的操作，需要你對xml的dom對象比較熟悉，尤其 是Attributes的使用。
