作為這一系列文章的最後一篇,向大家介紹下如何在Silverlight中解壓和建立Excel OpenXml ZIP壓縮包。由於Silverlight對本地用戶端檔案系統訪問的安全層級要求比較高,不太容易像Windows應用程式那樣可以隨意地讀寫目錄和檔案,我們不得不考慮使用一些其它的辦法。如使用Silverlight的OOB(Out of Browser)模式,可以允許Silverlight程式讀寫本地的部分目錄和檔案,下面這篇文章介紹了如何在Silverlight OOB模式下調用COM組件來操作Excel。
http://www.codeproject.com/Articles/83996/Silverlight-4-Interoperability-with-Excel-using-th
又回到使用COM組件的方式了,不過這個不是我們要討論的問題,瞭解一下也無妨。先說說為什麼我們要如此變態地在Silverlight中使用Excel OpenXML。
這其實是一個需求,使用者的資料存放在SharePoint List中,想通過一個程式從List讀取資料然後填充到一個Excel模板中提供下載。這個需求本身其實非常簡單,關鍵是使用者要求程式不能在用戶端安裝,伺服器端不能有Custom Code,這兩點要求幾乎扼殺了我們所有可以使用的方法,第一不能使用諸如Windows Form或WPF application,第二不能建立SharePoint Feature或Webpart,當然就更別提建立單獨的ASP.NET應用程式了,客戶根本就沒有提供空間去部署網站。另外Silverlight的OOB模式也不允許,因為OOB模式也是要在本地安裝的,儘管它不同於傳統意義上的Windows程式安裝。這樣,我們只有一條路可走,那就是建立Silverlight應用程式然後通過SharePoint的Silverlight Webpart部署到頁面上,在Silverlight中直接調用Excel Services把從List中讀取到的資料填充到Excel中。那跟Excel OpenXML有什麼關係?我們不是已經往Excel裡寫入資料了嗎?對,沒錯!如果你只是單純往Excel模板中寫入資料根本不需要再做任何操作,可是修改Excel檔案的樣式呢?
還記得在上一篇文章中的那個圖嗎?如果儲存格中沒有部分內容加粗,而只是單純的換行或空格,我們可以直接通過Excel的公式或運算式來實現。
=CONCATENATE(" Short-term investments (including securities loaned", CHAR(10), " of $9,999 and $8,888")
=" Short-term investments (including securities loaned)" & CHAR(10)&" of " & TEXT("9999", "$#,##0") & " and " & TEXT("8888", "$#,##0")
上面兩行代碼分別使用了Excel中的Concatenate()函數和&串連符來填充儲存格內容,其中CHAR(10)表示的就是斷行符號符。但是我們無法在Excel中通過函數設定字串的樣式,Excel所有內建的函數都不能修改樣式。或許我們可以嘗試通過VBA來修改樣式呢?在單獨的Excel檔案中這個辦法是可行的,我們只需要在Workbook的Open事件或SheetChange事件中寫入VBA代碼,當事件被調用的時候樣式會被自動修改。不過Excel Services不支援帶有VBA或宏的Excel檔案,當你嘗試通過Excel Services讀取一個帶有VBA代碼或宏的Excel檔案時會拋出異常。所以,嘗試通過VBA來修改樣式是行不通的,尤其是那些特殊的樣式,如中儲存格內數字加粗和上標等。
因此,我們會考慮通過Excel OpenXML方式將已經填充好資料的Excel檔案進行樣式修改。按照前面幾篇文章的介紹,修改Excel內容需要首先將其解壓到一個臨時目錄,然後修改臨時目錄中相應的XML檔案,最後再重新打包成一個Excel檔案。可是在Silverlight中不太容易操作本地檔案系統,因此我們只能考慮在檔案Stream中完成操作了。
我們需要一個能支援在Silverlight工程中操作ZIP檔案的類庫,之前的那個開源類庫http://www.icsharpcode.net/OpenSource/SharpZipLib/是使用較早的.NET Framework編寫的,有許多類型和對象在Silverlight Framework中找不到無法編譯通過。幸好這裡我找到有人將其修改成Silverlight版本了,非常感謝!互連網是強大的。
http://web-snippets.blogspot.com/2008/03/unpacking-zip-files-in-silverlight-2.html
我這裡也提供一個下載吧,以免原作者的空間打不開。SLSharpZipLib_Solution.zip
這裡還有一個關於ShareZipLib樣本的WiKi網站,可以研究下這個類庫都能幹些什麼。
http://wiki.sharpdevelop.net/SharpZipLib_Updating.ashx#Updating_a_zip_file_in_memory_1
來看一個實際應用的例子。
/// <summary>/// Format exported Excel file with a stream./// </summary>/// <param name="zipfileStream">A stream of Excel zip file.</param>/// <returns>Return a MemoryStream of updated Excel zip file.</returns>public Stream FormatExcelWithStream(Stream zipfileStream){ // copy the current stream to a new stream MemoryStream msZip = new MemoryStream(); long pos = 0; pos = zipfileStream.Position; zipfileStream.CopyTo(msZip); zipfileStream.Position = pos; XElement eleSheet = null; // load sharedStrings xml document for updating XElement eleSharedStrings = null; using (Stream mainStream = FindEntryFromZipStream(zipfileStream, "xl/sharedStrings.xml")) { if (mainStream == null) { return msZip; } eleSharedStrings = XElement.Load(mainStream); } // distinct sheet xml document for searching IEnumerable<ExcelFormattingSetting> noduplicates = ExcelFormattingSettings.Distinct(new ExcelFormattingSettingCompare()); foreach (ExcelFormattingSetting sheet in noduplicates) { zipfileStream.Position = 0; using (Stream stream = FindEntryFromZipStream(zipfileStream, sheet.SheetName)) { if (stream != null) { eleSheet = XElement.Load(stream); // update sharedStrings.xml document UpdateSharedStringsXMLDoc(sheet.SheetName, eleSharedStrings, eleSheet); } } } // update to stream MemoryStream msEntry = new MemoryStream(); eleSharedStrings.Save(msEntry); // The zipStream is expected to contain the complete zipfile to be updated ZipFile zipFile = new ZipFile(msZip); zipFile.BeginUpdate(); // To use the entryStream as a file to be added to the zip, // we need to put it into an implementation of IStaticDataSource. CustomStaticDataSource sds = new CustomStaticDataSource(); sds.SetStream(msEntry); // If an entry of the same name already exists, it will be overwritten; otherwise added. zipFile.Add(sds, "xl/sharedStrings.xml"); // Both CommitUpdate and Close must be called. zipFile.CommitUpdate(); // Set this so that Close does not close the memorystream zipFile.IsStreamOwner = false; zipFile.Close(); return msZip;}/// <summary>/// Find a specific stream with the entry name of the Excel zip file package./// </summary>/// <param name="inputStream">The Excel zip file stream.</param>/// <param name="entryName">Entry name in the Excel zip file package.</param>/// <returns>Return the sepcific stream.</returns>private Stream FindEntryFromZipStream(Stream inputStream, string entryName){ ZipInputStream zipStream = new ZipInputStream(inputStream); ZipEntry zippedFile = zipStream.GetNextEntry(); //Do until no more zipped files left while (zippedFile != null) { byte[] buffer = new byte[2048]; int bytesRead; MemoryStream memoryStream = new MemoryStream(); // read through the compressed data while ((bytesRead = zipStream.Read(buffer, 0, buffer.Length)) != 0) { memoryStream.Write(buffer, 0, bytesRead); } memoryStream.Position = 0; if (zippedFile.Name.Equals(entryName)) { return memoryStream; } zippedFile = zipStream.GetNextEntry(); } return null;}/// <summary>/// Update formatted strings to the sharedStrings.xml document in Excel zip file./// </summary>/// <param name="sheetName"></param>/// <param name="navSharedStrings"></param>/// <param name="navSheet"></param>private void UpdateSharedStringsXMLDoc(string sheetName, XElement eleSharedStrings, XElement eleSheet){ XNamespace nsSharedStrings = eleSharedStrings.GetDefaultNamespace(); XNamespace nsSheet = eleSheet.GetDefaultNamespace(); int i; string sContent; // update each formatting settings to the sharedStrings xml document foreach (ExcelFormattingSetting setting in ExcelFormattingSettings.Where(s => s.SheetName.Equals(sheetName))) { // find out which si element need to update from the sheet xml document. var siIndex = eleSheet.Element(nsSheet + "sheetData") .Descendants(nsSheet + "c") .Where(d => d.Attribute("r").Value == setting.ExcelPositionString).FirstOrDefault(); if (siIndex != null) { if (int.TryParse(siIndex.Value, out i)) { var siEntry = eleSharedStrings.Elements(nsSharedStrings + "si").ElementAt(i); if (siEntry != null) { var child = siEntry.Element(nsSharedStrings + "t"); if (child != null) { setting.OriginalText = child.Value; sContent = setting.ProcessFormatting(setting.processFormatting); // note, cannot set empty content to the new XElement. if (!sContent.Equals(string.Empty)) { XElement newElement = XElement.Parse("<si xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" + setting.ProcessFormatting(setting.processFormatting) + "</si>"); siEntry.ReplaceWith(newElement); } } } } } }}
不能在原Stream上進行操作。首先將檔案的Stream Copy出來一份。函數FindEntryFromZipStream()通過entryName來返回ZIP壓縮包中指定的部分,傳回型別為Stream。將找到的Stream交給UpdateSharedStringsXMLDoc()方法進行修改,該方法會逐一讀取所有工作表XML檔案,找到需要修改的部分的si節點的序號(該序號存放在工作表XML檔案的sheetData->c->r節點中),然後修改sharedStrings.xml檔案的內容。注意sharedStrings.xml檔案載入的XElement對象只有一個,傳入該對象到UpdateSharedStringsXMLDoc()方法進行修改,之後將其存入到一個MemoryStream對象中。接下來的代碼便是將該MemoryStream重新加到ZIP包裡,使用了類庫提供的方法,注意如果指定的entryName在原ZIP包中存在則會直接替換,否則就添加。CustomStaticDataSource類是自訂的一個類,按照要求該類必須繼承自IStaticDataSource介面。
public class CustomStaticDataSource : IStaticDataSource{ private Stream _stream; // Implement method from IStaticDataSource public Stream GetSource() { return _stream; } // Call this to provide the memorystream public void SetStream(Stream inputStream) { _stream = inputStream; _stream.Position = 0; }}
所有的操作都在一個Stream裡完成,不需要臨時目錄存放解壓後的檔案。這裡是上面整個類的完整下載,ExcelFormattingAdjustor.zip
例子中將所有需要修改樣式的單元定義成常量,然後在自訂類ExcelFormattingSetting中儲存設定樣式的一些參數,如原內容、工作表名稱、樣式替換字串,以及如何修改樣式的委託等。將所有ExcelFormattingSetting類的執行個體存放到一個集合裡,遍曆該集合對所有的設定項進行修改。你可以在ExcelFormattingSetting類的ProcessFormatting()方法中定義如何替換這些樣式字串,如果遇到需要特殊處理的情況,就在該類的執行個體中定義一個匿名函數,在匿名函數中進行處理。
更多的應用還在不斷嘗試中,如果能夠提供一個功能豐富且成熟的類庫,我們完全可以脫離COM組件來操作Excel,包括建立一個全新的Excel檔案、讀取資料產生報表、匯出資料到Excel檔案並自訂樣式等等。但所有這一切都應該歸功於OpenXML,它使得Office檔案從一個自封閉的環境中解脫出來了,基於XML結構的檔案是開放的,因此我們做的所有工作其實就是在操作XML,如此簡單!不是嗎?