[攒分贴]通过excel可识别的xml结构直接生成xls文件
上一片文章演示了如何根据简单的excel文件结构直接生成xls文件，如果涉及到合并，公式之类的复杂操作，可以使用xml结构来直接构造xls文件，比如生成如下所示文件
演示代码如下，调用相当简单。
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);
}
}
}
Imports System.Xml
Imports System.Text
Public Class ExcelXmlWriter
Dim ssns As String = "urn:schemas-microsoft-com:office:spreadsheet"
Dim xmlns As String = "urn:schemas-microsoft-com:office:spreadsheet"
Dim _doc As New XmlDocument
Dim _currentSheet As XmlNode = Nothing
Dim _currentRow As XmlNode = Nothing
Public Sub New()
'excel的xml模版，你需要了解xml的Attributes怎么用
Dim sbody As New StringBuilder
sbody.Append(" <?xml version=""1.0""?>" & vbCrLf)
sbody.Append(" <?mso-application progid=""Excel.Sheet""?>" & vbCrLf)
sbody.Append(" <Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""" & vbCrLf)
sbody.Append("xmlns:o=""urn:schemas-microsoft-com:office:office""" & vbCrLf)
sbody.Append("xmlns:x=""urn:schemas-microsoft-com:office:excel""" & vbCrLf)
sbody.Append("xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""" & vbCrLf)
sbody.Append("xmlns:html=""http://www.w3.org/TR/REC-html40"">" & vbCrLf)
sbody.Append(" <Styles>" & vbCrLf)
sbody.Append(" <Style ss:ID=""Default"" ss:Name=""Normal"">" & vbCrLf)
sbody.Append(" <Alignment ss:Vertical=""Center""/>" & vbCrLf)
sbody.Append(" <Borders/>" & vbCrLf)
sbody.Append(" <Font ss:FontName=""宋体"" x:CharSet=""134"" ss:Size=""10""/>" & vbCrLf)
sbody.Append(" <Interior/>" & vbCrLf)
sbody.Append(" <NumberFormat/>" & vbCrLf)
sbody.Append(" <Protection/>" & vbCrLf)
sbody.Append(" </Style>" & vbCrLf)
sbody.Append(" </Styles>" & vbCrLf)
sbody.Append(" </Workbook>" & vbCrLf)
_doc.LoadXml(sbody.ToString())
End Sub
''' <summary>
''' 增加一个工作表
''' </summary>
''' <param name="sheetName">工作表名称 </param>
Public Sub CreateSheet(ByVal sheetName As String)
Dim node As System.Xml.XmlNode = _doc.CreateNode(XmlNodeType.Element, "Worksheet", ssns)
Dim xa As System.Xml.XmlAttribute = _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
End Sub
''' <summary>
'''增加一行
''' </summary>
Public Sub CreateRow()
Dim node As System.Xml.XmlNode = _doc.CreateNode(XmlNodeType.Element, "Row", xmlns)
_currentSheet.ChildNodes(0).AppendChild(node)
_currentRow = node
End Sub
''' <summary>
'''增加一列
''' </summary>
''' <param name="index">索引 </param>
''' <param name="width">宽度 </param>
Public Sub CreateColumn(ByVal index As Integer, ByVal width As Single)
Dim node As System.Xml.XmlNode = _doc.CreateNode(XmlNodeType.Element, "Column", xmlns)
Dim xa As System.Xml.XmlAttribute = _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)
End Sub
''' <summary>
'''增加一个单元格
''' </summary>
''' <param name="value">值 </param>
''' <param name="Type">类型 </param>
''' <param name="Expression">公式 </param>
''' <param name="rowSpan">跨行 </param>
''' <param name="colSpan">跨列 </param>
Public Sub CreateCell(ByVal value As Object, ByVal type As String, ByVal expression As String, ByVal rowSpan As Integer, ByVal colSpan As Integer)
Dim xa As System.Xml.XmlAttribute = Nothing
Dim nodeCell As System.Xml.XmlNode = _doc.CreateNode(XmlNodeType.Element, "Cell", xmlns)
_currentRow.AppendChild(nodeCell)
If (Not String.IsNullOrEmpty(Expression)) Then
xa = _doc.CreateAttribute("ss", "Formula", xmlns)
xa.Value = "=" + expression
nodeCell.Attributes.Append(xa)
End If
colSpan -= 1
If (colSpan > 0) Then
xa = _doc.CreateAttribute("ss", "MergeAcross", xmlns)
xa.Value = colSpan.ToString()
nodeCell.Attributes.Append(xa)
End If
rowSpan -= 1
If (--rowSpan > 0) Then
xa = _doc.CreateAttribute("ss", "MergeDown", xmlns)
xa.Value = rowSpan.ToString()
nodeCell.Attributes.Append(xa)
End If
Dim nodeData As System.Xml.XmlNode = _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)
End Sub
''' <summary>
'''增加一个数字单元格
''' </summary>
''' <param name="value"> </param>
Public Sub CreateCellNumber(ByVal value As Double)
CreateCell(value, "Number", Nothing, 1, 1)
End Sub
''' <summary>
''' 增加一个字符串单元格
''' </summary>
''' <param name="value"> </param>
Public Sub CreateCellString(ByVal value As String)
CreateCell(value, "String", Nothing, 1, 1)
End Sub
''' <summary>
''' 保存
''' </summary>
''' <param name="strFile"> </param>
Public Sub Save(ByVal strFile As String)
_doc.Save(strFile)
End Sub
End Class
