none
Question on Creation of Worksheet Part

    Question

  • When I add a Worksheet Part programatically, why is the Worksheet part named Sheet.xml instead of Sheet1.xml as Excel would do. Is there someway to provide the name of the xml file? I am just trying to make my Spreadsheet ML package as close to Excel as I can.

    Public Function AddWorksheet(Spreadsheet As SpreadsheetDocument,
                                    SheetName As String,
                                    Optional FirstDataRow As Int32 = 0,
                                    Optional FreezeCells As Boolean = True,
                                    Optional TabSelected As Boolean = True,
                                    Optional DefaultRowHeight As Double = 20) As Worksheet
          Dim Sheets As Sheets = Nothing
          Dim Sheet As Sheet
          Dim WorksheetPart As WorksheetPart = Nothing
          Dim Worksheet As Worksheet = Nothing
          Try
             Sheets = Spreadsheet.WorkbookPart.Workbook.GetFirstChild(Of Sheets)()
             ' Add the worksheetpart
             WorksheetPart = Spreadsheet.WorkbookPart.AddNewPart(Of WorksheetPart)(CreateRelationshipID(Spreadsheet))
             Worksheet = New Worksheet()
             'Set Active Tab, Active Cell, Freeze Cells
             Dim Sheetviews As New SheetViews()
             Dim Sheetview As New SheetView() With {.TabSelected = New BooleanValue(TabSelected), .WorkbookViewId = 0}
             If FirstDataRow > 0 Then
                Dim ActiveCell As String = "A" + FirstDataRow.ToString
                If FreezeCells Then
                   Sheetview.Append(CreatePane(FirstDataRow, ActiveCell))
                End If
                Sheetview.Append(CreateSelection(ActiveCell, FreezeCells))
             End If
             Sheetviews.Append(Sheetview)
             Worksheet.Append(Sheetviews)
             Worksheet.Append(CreateSheetFormatProperties(DefaultRowHeight))
             Worksheet.Append(New SheetData())
             Worksheet.Append(CreatePageMargins(0.25, 0.25, 0.5, 0.5, 0.5, 0.5))
             WorksheetPart.Worksheet = Worksheet
             WorksheetPart.Worksheet.Save()
             ' Add the sheet and make relation to workbook
             Sheet = New Sheet With {
                .Id = Spreadsheet.WorkbookPart.GetIdOfPart(WorksheetPart),
                .SheetId = (Spreadsheet.WorkbookPart.Workbook.Sheets.Count() + 1),
                .Name = SheetName}
             Sheets.Append(Sheet)
             Spreadsheet.WorkbookPart.Workbook.Save()
          Catch ex As System.Exception
             sf.WriteErrorLog("AddWorksheet", Err.Number, ex.Message, ex.ToString)
             sf.RedirectToStandardError()
          End Try
          Return WorksheetPart.Worksheet
       End Function


    Ray Pietrzak

    Tuesday, March 12, 2013 7:40 PM

Answers

  • Hi Ray,

    In fact, I don't think it is necessary to modify the name of the xml file.

    >> Is there someway to provide the name of the xml file?

    The only way I can think of is using the System.IO.Packaging API.  

    The following code copy the sheet.xml to sheet1.xml and then delete sheet.xml. 

            static void Main(string[] args)
            {
                string fileName = @"D:\Temp\CSharp.xlsx";
                Package pkg = Package.Open(fileName);
                System.IO.FileInfo fi = new System.IO.FileInfo(fileName);
                string search = "/xl/worksheets/sheet.xml";
                Boolean flag = false;
    
                // Get the stream from the part
                System.IO.Stream partStream =null;
                string id = "";
                // Get the embedded files names.
                foreach (PackagePart pkgPart in pkg.GetParts())
                {
                    if (pkgPart.Uri.ToString() == search)
                    {
                        flag = true;
                        partStream = pkgPart.GetStream();
                    }
                }
                
    
                if (flag == true)
                {
                    Uri partUriDocument = PackUriHelper.CreatePartUri(
                                 new Uri(@"/xl/worksheets/sheet1.xml", UriKind.Relative));
                    PackagePart packagePartDocument = pkg.CreatePart(partUriDocument,
                                  System.Net.Mime.MediaTypeNames.Text.Xml);               
                   
                    ReadWriteStream(partStream, packagePartDocument.GetStream());
                     partUriDocument = PackUriHelper.CreatePartUri(
                                 new Uri(search, UriKind.Relative));
                     pkg.DeletePart(partUriDocument);
                }
    
                Console.WriteLine("finished");
                Console.Read();
    
            }
    
    
            /// <summary>
            /// ReadWriteStream method is used to extract the files from the document to a temporary location
            /// If the extracted file is a structured storage, it will be sent to the ExtractFile method to extract the actual content
            /// </summary>
            /// <param name="readStream"></param>
            /// <param name="writeStream"></param>
            private static void ReadWriteStream(Stream readStream, Stream writeStream)
            {
                int Length = 256;
                Byte[] buffer = new Byte[Length];
                int bytesRead = readStream.Read(buffer, 0, Length);
                // write the required bytes
                while (bytesRead > 0)
                {
                    writeStream.Write(buffer, 0, bytesRead);
                    bytesRead = readStream.Read(buffer, 0, Length);
                }
                readStream.Close();
                writeStream.Close();
            } 
    You can have a look into the package of /xl/worksheets/ file. 

    However, to let the workbook open, more work need to be done. We need to modify a lot of things in the XML package. 

    Good day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, March 14, 2013 1:24 PM

All replies

  • Hi Ray,

    In fact, I don't think it is necessary to modify the name of the xml file.

    >> Is there someway to provide the name of the xml file?

    The only way I can think of is using the System.IO.Packaging API.  

    The following code copy the sheet.xml to sheet1.xml and then delete sheet.xml. 

            static void Main(string[] args)
            {
                string fileName = @"D:\Temp\CSharp.xlsx";
                Package pkg = Package.Open(fileName);
                System.IO.FileInfo fi = new System.IO.FileInfo(fileName);
                string search = "/xl/worksheets/sheet.xml";
                Boolean flag = false;
    
                // Get the stream from the part
                System.IO.Stream partStream =null;
                string id = "";
                // Get the embedded files names.
                foreach (PackagePart pkgPart in pkg.GetParts())
                {
                    if (pkgPart.Uri.ToString() == search)
                    {
                        flag = true;
                        partStream = pkgPart.GetStream();
                    }
                }
                
    
                if (flag == true)
                {
                    Uri partUriDocument = PackUriHelper.CreatePartUri(
                                 new Uri(@"/xl/worksheets/sheet1.xml", UriKind.Relative));
                    PackagePart packagePartDocument = pkg.CreatePart(partUriDocument,
                                  System.Net.Mime.MediaTypeNames.Text.Xml);               
                   
                    ReadWriteStream(partStream, packagePartDocument.GetStream());
                     partUriDocument = PackUriHelper.CreatePartUri(
                                 new Uri(search, UriKind.Relative));
                     pkg.DeletePart(partUriDocument);
                }
    
                Console.WriteLine("finished");
                Console.Read();
    
            }
    
    
            /// <summary>
            /// ReadWriteStream method is used to extract the files from the document to a temporary location
            /// If the extracted file is a structured storage, it will be sent to the ExtractFile method to extract the actual content
            /// </summary>
            /// <param name="readStream"></param>
            /// <param name="writeStream"></param>
            private static void ReadWriteStream(Stream readStream, Stream writeStream)
            {
                int Length = 256;
                Byte[] buffer = new Byte[Length];
                int bytesRead = readStream.Read(buffer, 0, Length);
                // write the required bytes
                while (bytesRead > 0)
                {
                    writeStream.Write(buffer, 0, bytesRead);
                    bytesRead = readStream.Read(buffer, 0, Length);
                }
                readStream.Close();
                writeStream.Close();
            } 
    You can have a look into the package of /xl/worksheets/ file. 

    However, to let the workbook open, more work need to be done. We need to modify a lot of things in the XML package. 

    Good day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, March 14, 2013 1:24 PM
  • Thanks, I guess it is not worth the effort. I am surprised that the SDK names the first worksheet created as sheet.xml instead of sheet1.xml. If I use Excel to create a workbook with only one spreadsheet, the worksheet part is named sheet1.xml.

    WorksheetPart = Spreadsheet.WorkbookPart.AddNewPart(

    Of WorksheetPart)


    Ray Pietrzak

    Wednesday, March 20, 2013 9:05 PM