C#研究OpenXML之路(1-新建工作簿文件)

一、写在开头

一直想沉下心来研究研究OpenXML编程,可是由于公司编程项目一笔接一笔,很难静下来,所以一直是采用的COM操作Excel。现在终于得闲,特将心得历程记录下来。

今天的第一个实例代码是来源于MSDN,关于如何创建一个空的工作簿文件。不过在正式开始堆砌代码前,首先你的配置好你的开发环境。

1、你得安装VS2010或以上版本,去网上下吧,我不提供链接了;

2、你得安装OpenXMLSDKV25,去网上下吧,我不提供链接了;

二、项目准备

接下来你就需要再VS中创建一个项目,编写代码了。但是你必须注意这么几件事情:

1、在项目中添加对DocumentFormat.OpenXml库的引用;

2、VS2010的默认框架是4.0,如果你未修改这个设置,需要添加WindowsBase.dll的引用;如果你选择的是3.5的就不用管了,否则你会收到类似下面所示的错误;

类型“System.IO.Packaging.Package”在未被引用的程序集中定义。必须添加对程序集“WindowsBase, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35”的引用。

三、静态方法CreateSpreadsheetWorkbook

我创建的是一个WinForm的C#项目,并新添加了一个静态类OpenXMLHelper,在后续的同主题文章我会不断扩充这个OpenXMLHelper的代码,以形成常用的操作Excel的类。接下来我们将从MSDN获取的函数代码拷贝进去,注意一定要自己手动添加5-7行的using语句:

以下代码的重点是CreateSpreadsheetWorkbook方法,它接受一个路径字符串参数,(我们可以考虑改造改方法返回创建的工作簿对象,因为没有必要创建完毕后,还要再去用代码找这个新创建的工作簿):

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using DocumentFormat.OpenXml;
 6 using DocumentFormat.OpenXml.Packaging;
 7 using DocumentFormat.OpenXml.Spreadsheet;
 8 
 9 namespace OpenXMLTest
10 {
11     static class OpenXMLHelper
12     {
13         public static void CreateSpreadsheetWorkbook(string filepath)
14         {
15             // Create a spreadsheet document by supplying the filepath.
16             // By default, AutoSave = true, Editable = true, and Type = xlsx.
17             SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
18 
19             // Add a WorkbookPart to the document.
20             WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
21             workbookpart.Workbook = new Workbook();
22 
23             // Add a WorksheetPart to the WorkbookPart.
24             WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
25             worksheetPart.Worksheet = new Worksheet(new SheetData());
26 
27             // Add Sheets to the Workbook.
28             Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
29 
30             // Append a new worksheet and associate it with the workbook.
31             Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };
32             sheets.Append(sheet);
33 
34             workbookpart.Workbook.Save();
35 
36             // Close the document.
37             spreadsheetDocument.Close();
38         }
39     }
40 }

也可以参考一下来自于CodeProject网站的一段代码

 1 public static SpreadsheetDocument CreateWorkbook(string fileName) {
 2    SpreadsheetDocument spreadSheet = null;
 3    SharedStringTablePart sharedStringTablePart;
 4    WorkbookStylesPart workbookStylesPart;
 5 
 6    try {
 7       // Create the Excel workbook
 8       spreadSheet = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook, false);
 9 
10       // Create the parts and the corresponding objects
11       // Workbook
12       spreadSheet.AddWorkbookPart();
13       spreadSheet.WorkbookPart.Workbook = new Workbook();
14       spreadSheet.WorkbookPart.Workbook.Save();
15 
16       // Shared string table
17       sharedStringTablePart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
18       sharedStringTablePart.SharedStringTable = new SharedStringTable();
19       sharedStringTablePart.SharedStringTable.Save();
20 
21       // Sheets collection
22       spreadSheet.WorkbookPart.Workbook.Sheets = new Sheets();
23       spreadSheet.WorkbookPart.Workbook.Save();
24 
25       // Stylesheet
26       workbookStylesPart = spreadSheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();
27       workbookStylesPart.Stylesheet = new Stylesheet();
28       workbookStylesPart.Stylesheet.Save();
29    } catch (System.Exception exception) {
30       System.Windows.MessageBox.Show(exception.Message, "Excel OpenXML basics", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Hand);
31    }
32 
33    return spreadSheet;
34 } 
四、调用方法

我在窗体上添加了一个按钮控件,并未改按钮添加了相应的代码,窗体的完整代码如下:

 1 using System;
 2 using System.Collections.Generic;
 3 using System.ComponentModel;
 4 using System.Data;
 5 using System.Drawing;
 6 using System.Linq;
 7 using System.Text;
 8 using System.Windows.Forms;
 9 
10 namespace OpenXMLTest
11 {
12     public partial class Form1 : Form
13     {
14         public Form1()
15         {
16             InitializeComponent();
17         }
18 
19         private void button1_Click(object sender, EventArgs e)
20         {
21             OpenXMLHelper.CreateSpreadsheetWorkbook(@"C:\Users\Administrator\Desktop\OpenXMLTest\OpenXMLTest.xlsx");
22         }
23     }
24 }