none
Trying to create a simple spreadsheet using Open XML SDK v 2.0 and C# RRS feed

  • Question

  • I am trying to understand the new Open XML tools and I have run into what must be some misunderstanding on my part.

    I am trying to wrap the SDK (v2.0) with a simple class that would treat the spreadsheet as a glorified multidimensional array. Should be simple, but somewhere I am making a bonehead mistake.

    An example of what I have tried is the following class. You will note that much of it is taken from examples on the net. Again, I am just trying to understand the basics of the SDK.

     

    using
     System;
    using
     System.Collections.Generic;
    using
     System.IO;
    using
     System.Linq;
    using
     DocumentFormat.OpenXml;
    using
     DocumentFormat.OpenXml.Packaging;
    using
     DocumentFormat.OpenXml.Spreadsheet;
    
    namespace
     OpenSpreadSheet
    {
     public
     class
     SpreadSheet
     {
      protected
     string
     m_fileName { get
    ; set
    ; }
      protected
     SpreadsheetDocument m_spreadsheet { get
    ; set
    ; }
      public
     SpreadSheet()
      {
       //No file name given, generate a temp one for now   
    
       Initialize(Path.GetTempFileName(), true
    );
      }
      public
     SpreadSheet(string
     fileName)
      {
       Initialize(fileName, false
    );
      }
      public
     SpreadSheet(string
     fileName, bool
     overwrite)
      {
       Initialize(fileName, overwrite);
      }
      protected
     void
     Initialize(string
     fileName, bool
     overwrite)
      {
       m_fileName = fileName;
       bool
     exists = File.Exists(fileName);
    
       try
    
       {
        if
     (exists && overwrite)
        {
         File.Delete(fileName);
         exists = false
    ;
        }
        m_spreadsheet = exists
             ? SpreadsheetDocument.Open(fileName, true
    )
             : SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook);
    
        if
     (m_spreadsheet.WorkbookPart == null
    )
        {
         m_spreadsheet.AddWorkbookPart();
         m_spreadsheet.WorkbookPart.Workbook = new
     Workbook();
         m_spreadsheet.WorkbookPart.Workbook.Save();
         WorksheetPart worksheetPart = m_spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
         worksheetPart.Worksheet = new
     Worksheet(new
     SheetData());
    
        }
       }
       catch
     (Exception e)
       {
        throw
    ;
       }
      }
      public
     object
     this
    [string
     sheetName, string
     column, uint
     row]
      {
       get
    
       {
        WorksheetPart worksheetPart = GetWorksheetPartByName(sheetName, false
    );
        if
     (worksheetPart != null
    )
        {
         Cell cell = GetCell(worksheetPart.Worksheet, column, row, false
    );
         if
     (cell != null
    )
         {
          return
     cell.CellValue;
         }
        }
        return
     null
    ;
       }
       set
    
       {
        WorksheetPart worksheetPart = GetWorksheetPartByName(sheetName, true
    );
        if
     (value == null
    )
        {
         return
    ;
        }
        Cell cell = GetCell(worksheetPart.Worksheet, column, row, true
    );
        //for now lets just inline string
    
        cell.CellValue = new
     CellValue(value.ToString());
        cell.DataType = new
     EnumValue<CellValues>(GetDataType(value));
        worksheetPart.Worksheet.Save();
       }
      }
      public
     void
     Save()
      {
       foreach
     (WorksheetPart worksheetPart in
     m_spreadsheet.WorkbookPart.WorksheetParts)
       {
        worksheetPart.Worksheet.Save();
       }
      }
      protected
     WorksheetPart GetWorksheetPartByName(string
     sheetName, bool
     create)
      {
       Sheets sheetsData = m_spreadsheet.WorkbookPart.Workbook.GetFirstChild<Sheets>();
       IEnumerable<Sheet> sheets = new
     List<Sheet>();
       if
     (sheetsData != null
    )
       {
        sheets = sheetsData.Elements<Sheet>().Where(s => s.Name == sheetName);
       }
    
       if
     (sheets.Count() == 0)
       {
        return
     create ? InsertWorksheet(sheetName) : null
    ;
       }
    
       string
     relationshipId = sheets.First().Id.Value;
       WorksheetPart worksheetPart = (WorksheetPart)m_spreadsheet.WorkbookPart.GetPartById(relationshipId);
       return
     worksheetPart;
      }
      protected
     Cell GetCell(Worksheet worksheet,
             string
     columnName,
             uint
     rowIndex,
             bool
     createCell)
      {
       Row row = GetRow(worksheet, rowIndex, createCell);
    
       Cell cell = null
    ;
    
       if
     (row.Elements<Cell>().Where(c => string
    .Compare(c.CellReference.Value, columnName + rowIndex, true
    ) == 0).Count() > 0)
       {
        cell = row.Elements<Cell>().Where(c => string
    .Compare(c.CellReference.Value, columnName + rowIndex, true
    ) == 0).First();
       }
       else
     if
     (createCell)
       {
        // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
    
        Cell refCell = null
    ;
        string
     cellReference = columnName + rowIndex;
    
        foreach
     (Cell ecell in
     row.Elements<Cell>())
        {
         if
     (string
    .Compare(ecell.CellReference.Value, cellReference, true
    ) > 0)
         {
          refCell = ecell;
          break
    ;
         }
        }
    
        cell = new
     Cell() { CellReference = cellReference };
        row.InsertBefore(cell, refCell);
        worksheet.Save();
       }
    
       return
     cell;
      }
    
    
      protected
     Row GetRow(Worksheet worksheet,
             uint
     rowIndex,
             bool
     createCell)
      {
       Row row = null
    ;
    
       if
     (worksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() > 0)
       {
        row = worksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
       }
       else
     if
     (createCell)
       {
        row = new
     Row() { RowIndex = rowIndex };
        worksheet.GetFirstChild<SheetData>().Append(row);
       }
    
       return
     row;
      }
      protected
     WorksheetPart InsertWorksheet(string
     sheetName)
      {
       WorkbookPart workbookPart = m_spreadsheet.WorkbookPart;
    
       // Add a new worksheet part to the workbook.
    
       WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
       newWorksheetPart.Worksheet = new
     Worksheet(new
     SheetData());
       newWorksheetPart.Worksheet.Save();
    
       Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>() ??
           workbookPart.Workbook.AppendChild(new
     Sheets());
       string
     relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
    
       // Get a unique ID for the new sheet.
    
       uint
     sheetId = 1;
       if
     (sheets.Elements<Sheet>().Count() > 0)
       {
        sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
       }
    
       // Append the new worksheet and associate it with the workbook.
    
       Sheet sheet = new
     Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
       sheets.Append(sheet);
       workbookPart.Workbook.Save();
    
       return
     newWorksheetPart;
      }
    
      protected
     void
     InsertSharedText(Cell cell, string
     text)
      {
       int
     sharedTextIndex = InsertSharedStringItem(text);
       cell.CellValue = new
     CellValue(sharedTextIndex.ToString());
       cell.DataType = new
     EnumValue<CellValues>(CellValues.SharedString);
       return
    ;
      }
      protected
     int
     InsertSharedStringItem(string
     text)
      {
       SharedStringTablePart shareStringPart = m_spreadsheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0
        ? m_spreadsheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First()
        : m_spreadsheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
    
       // If the part does not contain a SharedStringTable, create one.
    
       if
     (shareStringPart.SharedStringTable == null
    )
       {
        shareStringPart.SharedStringTable = new
     SharedStringTable();
       }
    
       int
     i = 0;
    
       // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
    
       foreach
     (SharedStringItem item in
     shareStringPart.SharedStringTable.Elements<SharedStringItem>())
       {
        if
     (item.InnerText == text)
        {
         return
     i;
        }
    
        i++;
       }
    
       // The text does not exist in the part. Create the SharedStringItem and return its index.
    
       shareStringPart.SharedStringTable.AppendChild(new
     SharedStringItem(new
     DocumentFormat.OpenXml.Spreadsheet.Text(text)));
       shareStringPart.SharedStringTable.Save();
    
       return
     i;
      }
      protected
     CellValues GetDataType(object
     value)
      {
       if
     (value is
     DateTime)
       {
        return
     CellValues.Date;
       }
       else
     if
     (value is
     bool
    )
       {
        return
     CellValues.Boolean;
       }
       else
     if
     (value is
     double
    
         || value is
     int
    
         || value is
     short
    
         || value is
     long
    )
       {
        return
     CellValues.Number;
       }
       return
     CellValues.String;
      }
     }
    }
    

     

    A simple driver program for this might be:

     

    SpreadSheet sheet = new
     SpreadSheet(@"C:\temp\example.xlsx"
    );
    sheet["Bob"
    , "A"
    , 1] = "Your Uncle"
    ;
    sheet.Save();
    

     

    When I open the spreadsheet in Excel 2007 I get an error that it is not a valid format and would I like to repair it. I try and it fails.

     

    For some reason I just can't get my head around the structure.

    Any pointers?

    • Edited by Randy Keeling Friday, April 23, 2010 2:52 PM Included the langauge being used
    Friday, April 23, 2010 2:51 PM

Answers

  • Answering my own question:

     

    It seems that before the SpreadsheetDocument's Close method is not called on it, the file that is generated is not valid. It took tearing about the file and comparing it to a known good file to find that out.

    • Marked as answer by Randy Keeling Friday, April 23, 2010 7:57 PM
    Friday, April 23, 2010 7:57 PM

All replies