Spreadsheet created using c# shows empty in OpenOffice

[woodgeek]

I'm creating a spreadsheet from a datatable using c# code. When I open the spreadsheet in OpenOffice, all cells are empty but when I open it in Excel, all of my data is there. There are no errors. If I save a copy in Excel and open in OpenOffice, all of my data is there as expected. Below is my code:

Code: Select all

MemoryStream memoryStream = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook))
{

    WorkbookPart workbookPart = document.AddWorkbookPart();
    workbookPart.Workbook = new Workbook();

    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
    SheetData sheetData = new SheetData();
    worksheetPart.Worksheet = new Worksheet(sheetData);

    Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());
    Sheet sheet = new Sheet()
    {
        Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
        SheetId = 1,
        Name = dataTable.TableName
    };
    sheets.Append(sheet);

    // Add column headers
    Row headerRow = new Row();
    foreach (DataColumn column in dataTable.Columns)
    {
        Cell cell = new Cell
        {
            DataType = CellValues.String,
            CellValue = new CellValue(column.ColumnName)
        };
        // Apply bold font style to the header cell.
        cell.StyleIndex = 1; // Assuming the bold font style is at index 1.
        headerRow.AppendChild(cell);
    }
    sheetData.AppendChild(headerRow);

    // Add rows
    foreach (DataRow dataRow in dataTable.Rows)
    {
        Row newRow = new Row();
        foreach (DataColumn column in dataTable.Columns)
        {
            Cell cell = new Cell
            {
                
                DataType = CellValues.String,
                CellValue = new CellValue(dataRow[column].ToString())
            };
            newRow.AppendChild(cell);
        }
        sheetData.AppendChild(newRow);
    }
    // Create a new Stylesheet and add it to the WorkbookStylesPart.
    WorkbookStylesPart stylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
    stylesPart.Stylesheet = CreateStylesheet();
    workbookPart.Workbook.Save();

[ms777]

This looks to me like calls to the Excel C# API, not to the OpenOffice C# API, right?
Then you better post this question on a Microsoft forum ...

[woodgeek]

This is code for the OpenOffice API, not Excel API. I followed a few online examples for creating an OpenOffice spreadsheet. If anyone can point me to a more generic example for creating an OpenOffice spreadsheet using the c# API I'll compare that to my code.

[woodgeek]

I downloaded the newest version of OpenOffice and now the data in my cells is appearing. The strange thing is that the formatting I've applied to the cells programmatically only appears when the spreadsheet is opened in Google Sheets or Microsoft Excel.

[ms777]

This is code for the OpenOffice API, not Excel API. I followed a few online examples for creating an OpenOffice spreadsheet. If anyone can point me to a more generic example for creating an OpenOffice spreadsheet using the c# API I'll compare that to my code.

Hi. it looks like you are using types like https://learn.microsoft.com/en-us/dotne ... cumenttype, which are certainly part of MS's OpenXML library, not of LO or AOO libraries. Not sure if that matters very much, though ...
Typicall c# code for creating a spreadsheet document looks like this when using LO or AOO libraries

Code: Select all

using System;
using  System.Collections.Generic;
using  System.Drawing;
using  System.IO;
using  System.Threading;
using unoidl.com.sun.star.awt;
using unoidl.com.sun.star.document;
using unoidl.com.sun.star.lang;
using unoidl.com.sun.star.uno;
using unoidl.com.sun.star.bridge;
using unoidl.com.sun.star.frame;
using unoidl.com.sun.star.sheet;
using unoidl.com.sun.star.beans;
using unoidl.com.sun.star.container;
using unoidl.com.sun.star.drawing;
using unoidl.com.sun.star.embed;
using unoidl.com.sun.star.table;
using unoidl.com.sun.star.text;
using unoidl.com.sun.star.util;

namespace CSharpDemo {

    public class OpenOfficeApp  {

        [STAThread]

        public static void Main() {

            Console.WriteLine("CSharpDemo Start");

    // create the desktop
            XComponentContext XCC = uno.util.Bootstrap.bootstrap();
            XMultiComponentFactory XMCF = (XMultiComponentFactory)XCC.getServiceManager();
            XMultiServiceFactory XMSF1 = (XMultiServiceFactory)XCC.getServiceManager();
            XComponentLoader XCL = (XComponentLoader)XMSF1.createInstance("com.sun.star.frame.Desktop");

    // open the spreadsheet document
            PropertyValue[] pPV = new PropertyValue[2];
            pPV[0] = new PropertyValue();
            pPV[0].Name = "Hidden";
            pPV[0].Value = new uno.Any(false);
            pPV[1] = new PropertyValue();
            pPV[1].Name = "ReadOnly";
            pPV[1].Value = new uno.Any(false);
            XComponent XCo = XCL.loadComponentFromURL("private:factory/scalc","_blank",0,pPV);
        }
    }
}