What else can't be done? -- Part 4: Reading and Writing Excel series articles in C #

As a seriesArticleThe last article introduces how to decompress and create an Excel openxml zip package in Silverlight. Because Silverlight has high security requirements for access to the local client file system, it is not as easy as Windows applications.ProgramIn this way, you can read and write directories and files at will. We have to consider other methods. If you use the OOB (out of browser) mode of Silverlight, you can allow the Silverlight program to read and write some local directories and files, the following article describes how to call the COM component in Silverlight OOB mode to Operate Excel.

Http://www.codeproject.com/Articles/83996/Silverlight-4-Interoperability-with-Excel-using-th

Back to the method of using COM components again, but this is not a question we want to discuss. It is okay to understand it. Let's first explain why we need to use Excel openxml in Silverlight so abnormally.

This is actually a requirement. Your data is stored in the Sharepoint list and you want to use a program to read data from the list and then fill it into an Excel template for download. This requirement is actually very simple. The key is that the user requires that the program cannot be installed on the client, and the server cannot have the custom code. These two requirements almost eliminate all of our available methods, first, you cannot use Windows form or WPF application. Second, you cannot create SharePoint feature or webpart.. NET application, the customer has no space to deploy the site. In addition, the OOB mode of Silverlight is not allowed, because the OOB mode also needs to be installed locally, although it is different from the traditional windows program installation. In this way, there is only one way to go, that is, to create a Silverlight application and deploy it on the page through the Silverlight webpart of SharePoint, in Silverlight, you can directly call Excel services to populate the data read from the list to excel. What is the relationship with Excel openxml? Haven't we written data into Excel? Yes, that's right! If you only write data to an Excel template, you do not need to perform any operations, but modify the style of the Excel file?

Do you still remember the figure in the previous article? If no part of the cell content is bold, but it is just a line break or space, we can use the Excel Formula or expression directly.

 = concatenate ( "  short-term investors (including securities loaned  " ,  char  ( 10 ), "   of $9,999 and $8,888  " ) 

 =  "  short-term investors (including securities loaned)  "  &  char  ( 10 ) & "   of  "  & text ( "9999  " ,  " $ #, #0   ") & "   and  "  & text ( "8888  " ,  " $ #, #0   ") 

The above two rowsCodeThe concatenate () function and the & connector in Excel are used to fill the cell content. Char (10) indicates the carriage return. However, we cannot set string styles through functions in Excel. All built-in functions in Excel cannot modify styles. Maybe we can try to modify the style through VBA? This method is feasible in a separate EXCEL file. We only need to write the VBA code in the open event or sheetchange event of the workbook. When the event is called, the style will be automatically modified. However, Excel services does not support Excel files with VBA or macros. When you try to read an Excel file with VBA code or macros through Excel services, an exception is thrown. Therefore, it is not feasible to try to modify the style through VBA, especially those special styles, such as bold and superlabeled numbers in cells.

Therefore, we will consider using the Excel openxml method to modify the style of the Excel file that has filled the data. According to the previous articles, you must first decompress the Excel content to a temporary directory, modify the XML file in the temporary directory, and then package it into an Excel file. However, it is not easy to operate the local file system in Silverlight, so we can only consider completing the operation in the file stream.

We need a class library that supports ZIP file operations in the Silverlight project. The previous open source class library http://www.icsharpcode.net/opensource/sharpziplib/was written using the earlier .net framework. Many types and objects cannot be found in the Silverlight framework. Fortunately, I found someone to change it to the Silverlight version. Thank you very much! The Internet is powerful.

Http://web-snippets.blogspot.com/2008/03/unpacking-zip-files-in-silverlight-2.html

I will also provide a download here, so that the space of the original author cannot be opened. Slsharpziplib_solution.zip

Here is a wiki site for the unzip ziplib sample. You can study what this class library can do.

Http://wiki.sharpdevelop.net/SharpZipLib_Updating.ashx#Updating_a_zip_file_in_memory_1

Let's look at an example of practical application.

 ///   <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 = 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 = 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/spreadsheetml2006/main\">  " + Setting. processformatting (setting. processformatting) + "  </Si>  "  ); Sientry. replacewith (newelement );}}}}}}} 

You cannot operate on the original stream. First, copy the stream of the file. The findentryfromzipstream () function uses entryname to return the specified part of the zip package. The return type is stream. Submit the found stream to the updatesharedstringsxmldoc () method for modification. This method reads all worksheet XML files one by one, find the serial number of the SI node to be modified (the serial number is stored in the sheetdata-> C-> r node of the worksheet XML file), and modify sharedstrings. the content of the XML file. Note that the sharedstrings. xml file loads only one xelement object. Pass the object to the updatesharedstringsxmldoc () method for modification, and save it to a memorystream object. The code below is to re-Add the memorystream to the zip package and use the method provided by the class library. Note that if the specified entryname exists in the original zip package, it will be replaced directly; otherwise, it will be added. The customstaticdatasource class is a custom class, which must inherit from the istaticdatasource interface as required.

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  ;}} 

All operations are completed in a stream, and no temporary directory is required to store the decompressed files. Here is the download of the entire class, excelformattingadjustor.zip

In this example, all units that need to modify the style are defined as constants, and some parameters for setting the style are stored in the Custom class excelformattingsetting, such as the original content, Worksheet name, and style replacement string, and how to modify the style delegate. Store all the instances of the excelformattingsetting class to a set and traverse the set to modify all the settings. You can define how to replace these style strings in the processformatting () method of the excelformattingsetting class. If special processing is required, define an anonymous function in the instance of this class, processing in anonymous functions.

More applications are still trying. If we can provide a rich and mature class library, we can operate on Excel from the COM component, this includes creating a new Excel file, reading data to generate reports, exporting data to an Excel file, and customizing styles. However, all this is due to openxml, which frees office files from a self-enclosed environment. XML-based files are open, therefore, all the work we do is actually operating XML, which is so simple! Isn't it?