当前位置: 首页 > 新闻动态

转载:使用“WPS表格”导出Excel文档

2011-12-16 强讯科技 供稿 【
  使用“WPS表格”导出Excel文档,非常实用的例子,希望更多的人看到,可以帮助到大家。

非常实用的例子,希望更多的人看到:

下面代码中的ET就是WPS的表格,你导入WPS表格的COM组建后,就可以看到这个命名空。

程序使用.net 2.0/c#开发,代码(省略Design部分代码):
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Xml;

namespace ExportWPS
{
    public partial class Form1 : Form
    {
        ET.Application objApp = null;
        ET._Workbook objWorkBook = null;
        ET.Worksheet objWorkSheet = null;

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            DateTime dt = DateTime.Now;

            try
            {
                objApp = new ET.Application();

                XmlDocument doc = new XmlDocument();
                doc.Load(@"c:\members.xml");

                XmlElement root = doc.DocumentElement;
                XmlNodeList nodes = root.SelectNodes("member");


                foreach (XmlElement memberElement in nodes)
                {
                    ExportMember(memberElement);
                }
            }
            finally
            {
                ReleaseComObject(objApp);
            }

            TimeSpan ts = DateTime.Now - dt;
            MessageBox.Show(ts.ToString());
        }

        private void ExportMember(XmlElement memberElement)
        {
            object missing = Type.Missing;
            ET.Range objRange = null;

            try
            {

                objWorkBook = objApp.Workbooks.Add(Type.Missing);
                objWorkSheet = objWorkBook.ActiveSheet;
               
                //设置标题
                objWorkSheet.get_Range("A1", "G1").Merge(true);        //先进行单元合并
                objRange = objWorkSheet.get_Range("A1", "A1");
                objRange.Value2 = "表 格 示 例";    //设置合并后的单元格的文本
                objRange.RowHeight = 40;        //设置行高
                //设置字体
                objRange.Font.Name = "宋体";
                objRange.Font.Size = 20;
                objRange.Font.Bold = true;
                objRange.HorizontalAlignment = ET.ETHAlign.etHAlignCenter;    //设置字体对其方向

                objRange = objWorkSheet.get_Range("A2", "G7");
                objRange.Borders.LineStyle = ET.ETLineStyle.etContinuous;
                objRange.Borders.Weight = ET.ETBorderWeight.etThin;
                objRange.RowHeight = 17;
                objRange.Borders.Color = 0;        //设置边框的颜色,颜色的值可以按照B,G,R的方式合成
                //设置表格的外边框,加粗
                objRange.Borders[ET.ETBorderIndex.etEdgeBottom].Weight = ET.ETBorderWeight.etMedium;
                objRange.Borders[ET.ETBorderIndex.etEdgeLeft].Weight = ET.ETBorderWeight.etMedium;
                objRange.Borders[ET.ETBorderIndex.etEdgeRight].Weight = ET.ETBorderWeight.etMedium;
                objRange.Borders[ET.ETBorderIndex.etEdgeTop].Weight = ET.ETBorderWeight.etMedium;
                objRange.HorizontalAlignment = ET.ETHAlign.etHAlignCenter;
                objRange.Font.Name = "宋体";
                objRange.Font.Size = 10;

                string name = memberElement.SelectSingleNode("name").ChildNodes[0].Value;

                objWorkSheet.get_Range("A2", "A2").Value2 = "姓    名";
                objWorkSheet.get_Range("A2", "A2").ColumnWidth = 60;

                objWorkSheet.get_Range("B2", "B2").ColumnWidth = 75;
                objWorkSheet.get_Range("B2", "B2").Value2 = name;

                objWorkSheet.get_Range("C2", "C2").Value2 = "性    别";
                objWorkSheet.get_Range("C2", "C2").ColumnWidth = 60;

                objWorkSheet.get_Range("D2", "D2").ColumnWidth = 75;
                objWorkSheet.get_Range("D2", "D2").Value2 = memberElement.SelectSingleNode("sex").ChildNodes[0].Value;

                objWorkSheet.get_Range("E2", "E2").Value2 = "出生年月";
                objWorkSheet.get_Range("E2", "E2").ColumnWidth = 60;

                objWorkSheet.get_Range("F2", "F2").ColumnWidth = 75;
                objWorkSheet.get_Range("F2", "F2").Value2 = memberElement.SelectSingleNode("birthday").ChildNodes[0].Value;

                objWorkSheet.get_Range("G2", "G7").Merge(false);
                objWorkSheet.get_Range("G2", "G2").ColumnWidth = 80;
                objWorkSheet.get_Range("G2", "G2").Value2 = "照片";

                objWorkSheet.get_Range("A3", "A3").Value2 = "民    族";
                objWorkSheet.get_Range("B3", "B3").Value2 = memberElement.SelectSingleNode("nationality").ChildNodes[0].Value;

                objWorkSheet.get_Range("C3", "C3").Value2 = "政治面貌";
                objWorkSheet.get_Range("D3", "D3").Value2 = memberElement.SelectSingleNode("political_status").ChildNodes[0].Value;

                objWorkSheet.get_Range("E3", "E3").Value2 = "籍    贯";
                objWorkSheet.get_Range("F3", "F3").Value2 = memberElement.SelectSingleNode("native_place").ChildNodes[0].Value;

                objWorkSheet.get_Range("A4", "A4").Value2 = "学    历";
                objWorkSheet.get_Range("B4", "B4").Value2 = memberElement.SelectSingleNode("qualification").ChildNodes[0].Value;

                objWorkSheet.get_Range("C4", "C4").Value2 = "固定电话";
                objWorkSheet.get_Range("D4", "D4").Value2 = memberElement.SelectSingleNode("telephone").ChildNodes[0].Value;

                objWorkSheet.get_Range("E4", "E4").Value2 = "移动电话";
                objWorkSheet.get_Range("F4", "F4").Value2 = memberElement.SelectSingleNode("mobile").ChildNodes[0].Value;

                objWorkSheet.get_Range("A5", "A5").Value2 = "毕业院校";
                objWorkSheet.get_Range("B5", "F5").Merge(false);
                objWorkSheet.get_Range("B5", "B5").Value2 = memberElement.SelectSingleNode("graduating_school").ChildNodes[0].Value;

                objWorkSheet.get_Range("A6", "A6").Value2 = "家庭住址";
                objWorkSheet.get_Range("B6", "F6").Merge(false);
                objWorkSheet.get_Range("B6", "B6").Value2 = memberElement.SelectSingleNode("address").ChildNodes[0].Value;

                objWorkSheet.get_Range("A7", "A7").Value2 = "Email";
                objWorkSheet.get_Range("B7", "F7").Merge(false);
                objWorkSheet.get_Range("B7", "B7").Value2 = memberElement.SelectSingleNode("email").ChildNodes[0].Value;

                objWorkBook.SaveAs(string.Format("c:\\{0}.xls", name), missing, missing, missing, missing, missing, ET.ETSaveAsAccessMode.etExclusive, missing, missing, missing, missing);
                objWorkBook.Close(missing, missing, missing);
            }
            finally
            {
                ReleaseComObject(objRange);
                ReleaseComObject(objWorkSheet);
                ReleaseComObject(objWorkBook);
            }
        }

        private void ReleaseComObject(object obj)
        {
            if (obj != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
        }
    }
}


表示成员信息的xml文档(保存在c:\members.xml供下面的代码调用)内容如下:
<?xml version="1.0" encoding="UTF-8"?>
<members>
    <member>
        <name>张三</name>
        <sex>男</sex>
        <birthday>1980年12月4日</birthday>
        <nationality>汉</nationality>
        <political_status>党员</political_status>
        <native_place>江苏南京</native_place>
        <qualification>本科</qualification>
        <telephone>025-8000000</telephone>
        <mobile>138138*****</mobile>
        <graduating_school>南京大学</graduating_school>
        <address>南京市中山东路*******</address>
        <email>zhang.san@sample.com</email>
    </member>
    <member>
        <name>李四</name>
        <sex>男</sex>
        <birthday>1981年9月12日</birthday>
        <nationality>汉</nationality>
        <political_status>团员</political_status>
        <native_place>江苏苏州</native_place>
        <qualification>本科</qualification>
        <telephone>025-8000000</telephone>
        <mobile>138138*****</mobile>
        <graduating_school>东南大学</graduating_school>
        <address>南京市某某路*******</address>
        <email>li.si@sample.com</email>
    </member>
    <member>
        <name>小美</name>
        <sex>女</sex>
        <birthday>1982年1月18日</birthday>
        <nationality>汉</nationality>
        <political_status>团员</political_status>
        <native_place>安徽黄山</native_place>
        <qualification>本科</qualification>
        <telephone>025-8000000</telephone>
        <mobile>138138*****</mobile>
        <graduating_school>南京理工大学</graduating_school>
        <address>南京市某某路*******</address>
        <email>xiao.mei@sample.com</email>
    </member>
    <member>
        <name>王二</name>
        <sex>男</sex>
        <birthday>1981年4月14日</birthday>
        <nationality>汉</nationality>
        <political_status>团员</political_status>
        <native_place>山东烟台</native_place>
        <qualification>研究生</qualification>
        <telephone>025-8000000</telephone>
        <mobile>138138*****</mobile>
        <graduating_school>南京航空航天大学</graduating_school>
        <address>南京市某某路*******</address>
        <email>wan.er@sample.com</email>
    </member>
    <member>
        <name>小莉</name>
        <sex>女</sex>
        <birthday>1982年3月18日</birthday>
        <nationality>汉</nationality>
        <political_status>党员</political_status>
        <native_place>江苏常熟</native_place>
        <qualification>研究生</qualification>
        <telephone>025-8000000</telephone>
        <mobile>138138*****</mobile>
        <graduating_school>南京大学</graduating_school>
        <address>南京市某某路*******</address>
        <email>xiao.li@sample.com</email>
    </member>
    <member>
        <name>Jack</name>
        <sex>男</sex>
        <birthday>1980年12月4日</birthday>
        <nationality>汉</nationality>
        <political_status>党员</political_status>
        <native_place>江苏南京</native_place>
        <qualification>本科</qualification>
        <telephone>025-8000000</telephone>
        <mobile>138138*****</mobile>
        <graduating_school>南京大学</graduating_school>
        <address>南京市中山东路*******</address>
        <email>jack@sample.com</email>
    </member>
    <member>
        <name>Joe</name>
        <sex>男</sex>
        <birthday>1981年9月12日</birthday>
        <nationality>汉</nationality>
        <political_status>团员</political_status>
        <native_place>江苏苏州</native_place>
        <qualification>本科</qualification>
        <telephone>025-8000000</telephone>
        <mobile>138138*****</mobile>
        <graduating_school>东南大学</graduating_school>
        <address>南京市某某路*******</address>
        <email>jeo@sample.com</email>
    </member>
    <member>
        <name>Katty</name>
        <sex>女</sex>
        <birthday>1982年1月18日</birthday>
        <nationality>汉</nationality>
        <political_status>团员</political_status>
        <native_place>安徽黄山</native_place>
        <qualification>本科</qualification>
        <telephone>025-8000000</telephone>
        <mobile>138138*****</mobile>
        <graduating_school>南京理工大学</graduating_school>
        <address>南京市某某路*******</address>
        <email>Katty@sample.com</email>
    </member>
    <member>
        <name>Scott</name>
        <sex>男</sex>
        <birthday>1981年4月14日</birthday>
        <nationality>汉</nationality>
        <political_status>团员</political_status>
        <native_place>山东烟台</native_place>
        <qualification>研究生</qualification>
        <telephone>025-8000000</telephone>
        <mobile>138138*****</mobile>
        <graduating_school>南京航空航天大学</graduating_school>
        <address>南京市某某路*******</address>
        <email>scott@sample.com</email>
    </member>
    <member>
        <name>Lily</name>
        <sex>女</sex>
        <birthday>1982年3月18日</birthday>
        <nationality>汉</nationality>
        <political_status>党员</political_status>
        <native_place>江苏常熟</native_place>
        <qualification>研究生</qualification>
        <telephone>025-8000000</telephone>
        <mobile>138138*****</mobile>
        <graduating_school>南京大学</graduating_school>
        <address>南京市某某路*******</address>
        <email>Lily@sample.com</email>
    </member>
</members>

分享到:

北京强讯科技有限公司-呼叫中心专家 版权所有© 2013     客户服务热线:400-700-8003   值班电话:151-2001-5266

  • 联系电话    北京总部:(010)82015266  上海:(021)64865166  广州:(020)83503506