用C#分析华表插件表格数据

 

先贴一个类的壳和成员，在逐渐贴方法。

    using System;
    using System.Collections;
    using System.Data;
    using System.Xml;

    /// <summary>
    /// 华表数据 xml 构造器。
    /// </summary>
    public class XmlBuilder
    {
        // 表页。
        private SortedList[] m_Sheets;
        private string[] m_Names;
    }

/// <summary>
        /// 构造函数，从 xml 构造华表数据。
        /// </summary>
        /// <param name="xml"> xml 字符串。</param>
        public XmlBuilder(string xml)
        {
            ArrayList sheets=new ArrayList();
            ArrayList names=new ArrayList();
            try
            {
                XmlDocument document=new XmlDocument();
                document.LoadXml(xml);
                foreach(XmlElement sheet in document.DocumentElement.SelectNodes("Worksheet"))
                {
                    SortedList rows=new SortedList();
                    foreach(XmlElement row in sheet.SelectSingleNode("Table").SelectNodes("Row"))
                    {
                        SortedList cells=new SortedList();
                        foreach(XmlElement cell in row.SelectNodes("Cell"))
                        {
                            XmlElement data=cell.FirstChild as XmlElement;
                            switch(data.GetAttribute("Type").ToLower())
                            {
                                case "string":
                                    if (data.InnerText.Trim().Length>0)
                                        cells.Add(int.Parse(cell.GetAttribute("Index")),data.InnerText);
                                    break;
                                case "number":
                                    cells.Add(int.Parse(cell.GetAttribute("Index")),decimal.Parse(data.InnerText));
                                    break;
                            }
                        }
                        rows.Add(int.Parse(row.GetAttribute("Index")),cells);
                    }
                    sheets.Add(rows);
                    names.Add(sheet.GetAttribute("Name"));
                }
                m_Sheets=sheets.ToArray(typeof(SortedList)) as SortedList[];
                m_Names=names.ToArray(typeof(string)) as string[];
            }
            catch
            {
                // 不处理。
            }
        }

/// <summary>
        /// 构造函数，构造空的华表数据（单页）。
        /// </summary>
        public XmlBuilder():this(1)
        {
        }
        /// <summary>
        /// 构造函数，构造空的华表数据（多页）。
        /// </summary>
        /// <param name="sheets">表页个数。</param>
        public XmlBuilder(int sheets)
        {
            m_Sheets=new SortedList[sheets];
            m_Names=new string[sheets];
            for(int i=0;i<sheets;i++)
            {
                m_Sheets[i]=new SortedList();
                m_Names[i]=string.Format("第{0}页",i+1);
            }
        }

 

/// <summary>
        /// 获取单元格数据（单页）。
        /// </summary>
        /// <param name="x">列号。</param>
        /// <param name="y">行号。</param>
        /// <returns>数据（string、decimal或null）。</returns>
        public object GetCellValue(int x,int y)
        {
            return GetCellValue(0,x,y);
        }
        /// <summary>
        /// 获取单元格数据（多页）。
        /// </summary>
        /// <param name="sheet">页号。</param>
        /// <param name="x">列号。</param>
        /// <param name="y">行号。</param>
        /// <returns>数据（string、decimal或null）。</returns>
        public object GetCellValue(int sheet,int x,int y)
        {
            if (sheet>=0 && m_Sheets.Length>sheet && m_Sheets[sheet].Contains(y) && (m_Sheets[sheet][y] as SortedList).Contains(x))
                return (m_Sheets[sheet][y] as SortedList)[x];
            else
                return null;
        }

 

/// <summary>
        /// 获取单元格数据（单页）。
        /// </summary>
        /// <param name="x">列号。</param>
        /// <param name="y">行号。</param>
        /// <param name="type">类型（Boolean，布尔；Number，数字；String，字符；Time，时间）。</param>
        /// <returns>单元格数据（bool、decimal、string、DateTime或null）。</returns>
        public object GetCellValue(int x,int y,string type)
        {
            return GetCellValue(0,x,y,type);
        }
        /// <summary>
        /// 获取单元格数据（多页）。
        /// </summary>
        /// <param name="sheet">页号。</param>
        /// <param name="x">列号。</param>
        /// <param name="y">行号。</param>
        /// <param name="type">类型（Boolean，布尔；Number，数字；String，字符；Time，时间）。</param>
        /// <returns>单元格数据（bool、decimal、string、DateTime或null）。</returns>
        public object GetCellValue(int sheet,int x,int y,string type)
        {
            if (GetCellValue(sheet,x,y) is decimal)
            {
                try
                {
                    decimal value=(decimal)GetCellValue(sheet,x,y);
                    switch(type.ToLower())
                    {
                        case "boolean":
                            return value==0?false:true;
                        case "number":
                            return value;
                        case "string":
                            return value.ToString();
                        case "time":
                            return DateTime.FromOADate(Convert.ToDouble(value));
                        default:
                            return null;
                    }
                }
                catch
                {
                    return null;
                }
            }
            else if (GetCellValue(sheet,x,y) is string)
            {
                try
                {
                    string value=GetCellValue(sheet,x,y) as string;
                    switch(type.ToLower())
                    {
                        case "boolean":
                            return bool.Parse(value);
                        case "number":
                            return Convert.ToDecimal(value);
                        case "string":
                            return value;
                        case "time":
                            return DateTime.Parse(value);
                        default:
                            return null;
                    }
                }
                catch
                {
                    return null;
                }
            }
            else
            {
                return null;
            }
        }

 

 /// <summary>
        /// 设置单元格数据（单页）。
        /// </summary>
        /// <param name="x">列号。</param>
        /// <param name="y">行号。</param>
        /// <param name="value">值（bool、decimal、string、DateTime或null）。</param>
        public void SetCellValue(int x,int y,object value)
        {
            SetCellValue(0,x,y,value);
        }
        /// <summary>
        /// 设置单元格数据（多页）。
        /// </summary>
        /// <param name="sheet">页号。</param>
        /// <param name="x">列号。</param>
        /// <param name="y">行号。</param>
        /// <param name="value">值（bool、decimal、string、DateTime或null）。</param>
        public void SetCellValue(int sheet,int x,int y,object value)
        {
            if (sheet>=0 && m_Sheets.Length>sheet)
            {
                SortedList row=m_Sheets[sheet][y] as SortedList;
                if (row==null)
                {
                    row=new SortedList();
                    m_Sheets[sheet][y]=row;
                }
                if (value==null)
                {
                    row[x]=null;
                }
                else if (value is bool)
                {
                    row[x]=Convert.ToDecimal((bool)value);
                }
                else if (value is decimal)
                {
                    row[x]=(decimal)value;
                }
                else if (value is string)
                {
                    row[x]=value as string;
                }
                else if (value is DateTime)
                {
                    row[x]=Convert.ToDecimal(((DateTime)value).ToOADate());
                }
            }
        }

 

/// <summary>
        /// 获取行数（单页）。
        /// </summary>
        /// <returns>行数。</returns>
        public int GetRowCount()
        {
            return GetRowCount(0);
        }
        /// <summary>
        /// 获取行数（多页）。
        /// </summary>
        /// <param name="sheet">页号。</param>
        /// <returns>行数。</returns>
        public int GetRowCount(int sheet)
        {
            int count=0;
            if (sheet>=0 && m_Sheets.Length>sheet)
            {
                foreach(int index in m_Sheets[sheet].Keys)
                {
                    count=Math.Max(count,index);
                }    
            }
            return count;
        }

 

/// <summary>
        /// 获取表页名称。
        /// </summary>
        /// <param name="sheet">页号。</param>
        /// <returns>表页名称。</returns>
        public string GetSheetName(int sheet)
        {
            if (sheet>=0 && m_Sheets.Length>sheet)
                return m_Names[sheet];
            else
                return null;
        }
        /// <summary>
        /// 设置表页名称。
        /// </summary>
        /// <param name="sheet">页号。</param>
        /// <param name="name">名称。</param>
        public void SetSheetName(int sheet,string name)
        {
            if (sheet>=0 && m_Sheets.Length>sheet)
            {
                // 华表帮助曰：设置表页页签的时候，一定要注意表页页签的命名规则。
                // 规则1，页签名称字符串中不能含有“: ///*![]?”字符；
                // 规则2，页签名称字符串的第一个字母不能是数字；
                // 规则3，页签名称不能重名。
                if (name==null || name.Trim().Length==0)
                    name="未知表";
                else
                    name=name.Trim();
                name=name.Replace(":","：").Replace("/","／").Replace("//","＼").Replace("*","＊");
                name=name.Replace("!","！").Replace("[","(").Replace("]",")").Replace("?","？");
                if (name[0]>='0' || name[0]<='9')
                    name="表"+name;

                ArrayList names=new ArrayList(m_Names);
                if (names.Contains(name)==false)
                {
                    m_Names[sheet]=name;
                }
                else
                {
                    for(int i=1;;i++)
                    {
                        if (names.Contains(string.Format("{0}{1}",name,i))==false)
                        {
                            m_Names[sheet]=string.Format("{0}{1}",name,i);
                            break;
                        }
                    }
                }
            }
        }

/// <summary>
        /// 构造 xml 华表数据。
        /// </summary>
        /// <returns>xml 字符串。</returns>
        public string BuilderXml()
        {
            // Xml 文档
            XmlDocument document=new XmlDocument();
            // 根 Workbook
            XmlElement book=document.CreateElement("Workbook");
            // Workbook 的子节点 DocumentProperties
            XmlElement property=document.CreateElement("DocumentProperties");
            // DocumentProperties 的子节点 Created
            XmlElement created=document.CreateElement("Created");
            created.InnerText=DateTime.Now.ToString();
            property.AppendChild(created);
            // DocumentProperties 的子节点 Version
            XmlElement version=document.CreateElement("Version");
            version.InnerText="03.1015";
            property.AppendChild(version);
            book.AppendChild(property);
            for(int i=0;i<m_Sheets.Length;i++)
            {
                // Workbook 的子节点 Worksheet
                XmlElement sheet=document.CreateElement("Worksheet");
                sheet.SetAttribute("Name",m_Names[i]);
                // Worksheet 的子节点 Table
                XmlElement table=document.CreateElement("Table");
                // Table 的子节点 Row
                foreach(DictionaryEntry row_entry in m_Sheets[i])
                {
                    XmlElement row=document.CreateElement("Row");
                    row.SetAttribute("Index",((int)row_entry.Key).ToString());
                    // Row 的子节点 Cell
                    foreach(DictionaryEntry cell_entry in (row_entry.Value as SortedList))
                    {
                        XmlElement cell=document.CreateElement("Cell");
                        cell.SetAttribute("Index",((int)cell_entry.Key).ToString());
                        // Cell 的子节点 Data
                        XmlElement data=document.CreateElement("Data");
                        if (cell_entry.Value is string)
                        {
                            data.SetAttribute("Type","String");
                            data.InnerText=cell_entry.Value as string;
                        }
                        else if (cell_entry.Value is decimal)
                        {
                            data.SetAttribute("Type","Number");
                            data.InnerText=((decimal)cell_entry.Value).ToString();
                        }
                        else
                        {
                            continue;
                        }
                        cell.AppendChild(data);
                        row.AppendChild(cell);
                    }
                    if (row.ChildNodes.Count>0)
                        table.AppendChild(row);
                }
                sheet.AppendChild(table);
                book.AppendChild(sheet);
            }
            document.AppendChild(book);
            return document.InnerXml;
        }