Reading data from Excel

In This Topic

You can exchange data between the Eplan platform and Microsoft Excel. One way is by using the "Open XML SDK" framework, which is available as NuGet package DocumentFormat.OpenXml.

It is designed to work with Open XML file formats introduced with Microsoft Office 2007, like .xlsx, .xlsm (macro-enabled), .xltx (templates) and .xlsb. However, it does not support the older binary Excel format .xls.

The prerequisites are as follows:

1) Install the NuGet package DocumentFormat.OpenXml

Make sure that the package was installed correctly:

If the installation was successful, references to DocumentFormat.OpenXml and DocumentFormat.OpenXml.Framework are added to your Visual Studio project.

2) Declare the necessary namespaces

C#	Copy Code
using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet;

3) Use Open XML structures to access the necessary data from your Excel file

The following example shows how to write Eplan project messages into an .xlsx file. New rows are appended starting at the first row overwriting existing content, no header is created.

Excel Import Open XML 1

var filePath = @"c:\temp\ProjectMessages.xlsx";
var wereMessagesInserted = false;

using (var document = SpreadsheetDocument.Open(filePath, true))
{
    var workbookPart = document.WorkbookPart;
    if (workbookPart?.Workbook.Sheets?.FirstOrDefault() is Sheet sheet)
    {
        if ((sheet.Id != null) && (sheet.Id.Value != null))
        {
            var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id.Value);
            var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
            if (sheetData == null)
            {
                sheetData = new SheetData();
                worksheetPart.Worksheet.Append(sheetData);
            }

            sheetData.RemoveAllChildren<Row>();
            uint nextRowIndex = 1;

            // Iterate through the project messages
            foreach (var item in new PrjMessagesCollection(project))
            {
                if (!(item is ProjectMessage message)) continue;

                var columnGroup = message.GetGroup().ToString();
                var columnMessage = message.GetText();
                sheetData.Append(CreateRow(nextRowIndex++, columnGroup, columnMessage));
                wereMessagesInserted = true;
            }
            worksheetPart.Worksheet.Save();
        }
        workbookPart.Workbook.Save();
    }
}

if (!wereMessagesInserted)
{
    new Decider().Decide(EnumDecisionType.eOkDecision, "No project messages found.", "", EnumDecisionReturn.eOK, EnumDecisionReturn.eOK);
}
else
{
    new Decider().Decide(EnumDecisionType.eOkDecision, "XLSX updated (no header, starting at row 1): " + filePath, "", EnumDecisionReturn.eOK, EnumDecisionReturn.eOK);
}

The implementation of the CreateRow and CreateTextCell methods could look like this:

Excel Import Open XML 2

private Row CreateRow(uint index, string colA, string colB)
{
    var row = new Row { RowIndex = index };
    row.Append(CreateTextCell("A" + index, colA));
    row.Append(CreateTextCell("B" + index, colB));
    return row;
}

private Cell CreateTextCell(string cellRef, string text)
{
    return new Cell
    {
        CellReference = cellRef,
        DataType = CellValues.String,
        CellValue = new CellValue(text ?? string.Empty)
    };
}

For reading or writing files of the older binary Excel fromat .xls, you will need to use other libraries like Microsoft.Office.Interop.Excel.