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

最新推荐文章于 2025-09-04 16:26:07 发布

weixin_30369087

最新推荐文章于 2025-09-04 16:26:07 发布

阅读量237

点赞数

CC 4.0 BY-SA版权

文章标签： c# runtime

原文链接：http://www.cnblogs.com/scdsun/archive/2007/03/20/681326.html

本文介绍了一种使用WPS2005的二次开发功能结合.NET平台，从XML文档中读取成员信息并将其导出到多个Excel文件的方法。通过解析XML数据，程序能够自动生成格式化的Excel表格。

昨天我提出了利用WPS 2005二次开发功能，使用.net开发文档导出的程序。今天，我将这个想法进行了实践。

程序所实现的功能是从一个表示成员信息的xml文档中读取数据，并将这些数据导出为Excel表格，表格的样式如下图所示：

表示成员信息的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 >

程序使用.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;

}

程序执行后，在c:\下生成10份xls文档。并且提示执行导出代码所使用的时间

总共花费5.5秒，应该说这个时间还是完全可以接受的。另外，导出的文件可以完全兼容MS Excel 2003

附一份使用上面的程序导出的文档： Jack.xls
完成的程序以及members.xml： ExportWPS.rar

转载于:https://www.cnblogs.com/scdsun/archive/2007/03/20/681326.html