Create spreadsheet by C# without having excel installed

Question

I am developing a program that should export data to an Excel spreadsheet. I need to know how to build a spreadsheet with C#, but only found tutorials that require excel installed on the computer.

I would like to be able to export the data without relying on excel installed.

Answer 1

Use Spreadsheet Light

SLDocument sl = new SLDocument();
sl.SetCellValue("A1", "Hello World"); 

sl.SaveAs("HelloWorld.xlsx");

Answer 2

You will need the SDK from Office. I leave it to you to install it.

That said, the code may seem complex at first - until you know the structure of the files. I’ll put a step by step.

To work with any Office files, we need the following namespaces:

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;

And for Excel, specifically:

using DocumentFormat.OpenXml.Spreadsheet;

You start by preparing a stream. The best way to do that is with a stream memory, but you can use other forms also depending on your courage and disposition.

MemoryStream stream = new MemoryStream();

Now you create the object that will actually be the file. Note the use of the command using. All other code must be inside the block declared by using.

using (SpreadsheetDocument foo = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
    // você vai montar a estrutura do arquivo aqui.
}

An excel file is made of work "books" (workbooks). Each book is an XML element that will contain spreadsheets. Let’s insert them.

WorkbookPart workbookPart = foo.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
workbookPart.Workbook.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

Remember where the above code should be placed ;)

Now we need to assemble the spreadsheets themselves. It’s simple:

Sheets sheets = workbookPart.Workbook.AppendChild<Sheets>(new Sheets());

(sheet is spreadsheet in English)

Each sheet you insert needs to have a unique, textual identifier. This identifier can be arbitrary. As I have already used the term foo for the main object, I will use bar for the key.

string idPlanilha = "bar";

And now we’ve added a spreadsheet itself. If you want to add more, the code is the same. Just change the key.

WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>(idPlanilha);
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheet planilha1 = new Sheet()
{
    Id = idPlanilha,
    SheetId = 1, // incremente na próxima
    Name = "minhaPrimeiraPlanilha"
};
sheets.Append(planilha1);

Now we already have a spreadsheet in the object that represents the file. Just fill it. This is the most boring part.

Let’s get a spreadsheet data set:

SheetData dadosPlanilha = worksheetPart.Worksheet.GetFirstChild<SheetData>();

Supposing you have one DataTable calling for dados. You can pass her data to the dataset of the spreadsheet as follows:

UInt32Value indice = 1;
foreach (DataRow rowDados in dados.Rows)
{
    Row rowExcel = new Row()
    {
        RowIndex = indice
    };

    char letraColuna = 'A';
    foreach (DataColumn column in dados.Columns)
    {
        object valorOriginal = rowDados[column];
        string valorTratado = valorOriginal != null ? valorOriginal.ToString() : ""; // Sanitizaçãozinha básica
        string coordenada = letraColuna.ToString() + indice.ToString();
        Cell celula = new Cell()
        {
            CellReference = coordenada,
            CellValue = new CellValue(valorTratado),
            DataType = CellValues.String
        };

        rowExcel.Append(celula);
        letraColuna = (char)(((int)letraColuna) + 1);
    }
    dadosPlanilha.Append(rowExcel);
    indice++;
}

Note that we are passing all the values of DataTable for the spreadsheet as strings. Again I leave to your discretion how to use other formats (for dates and numbers).

Now, we save everything and close the object that represents the file.

worksheetPart.Worksheet.Save();
workbookPart.Workbook.Save();
foo.Close();

You know that stream that we opened to represent the file? It contains the binary of the file now! If it was a FileStream, for example, the file would be practically written by now. How do we use a MemoryStream, we have the file in memory. You are now free to use it as you wish - recommend doing only the following, before the stream to write to disk, save to a database or force a download:

stream.Position = 0;

Ah, just one more thing... Since the file format will be OOXML (in this case, anything .xlsx), you need Office at least 2013 to have full compatibility. But you should be able to open with Office from 2007 (if you’re in some public office that still has this version), or with other tools like Google Drive.

Okay. That’s it for today, guys.