Reading Excel data and cell formatting in C#

Question

I am looking for a library that allows me to read an Excel document in C#, but I am needing to be able to read the cell formatting as well as the data in the spreadsheet. I found this useful question that lists a lot of different alternatives for reading Excel documents, but a lot of the solutions offered here only allow the content of the spreadsheet to be read, not the cell properties (eg font, background colour, etc). For my particular problem, though, I am needing to read a document that contains a lot of cell formatting and I need to be able to read this formatting information.

The Excel document that I am needing to reading is an xls document, so I do not need to read xlsx at this stage. I am looking for a library that can be used on a server without Excel being installed, so it must be a standalone solution and preferably open-source.

Does anyone have experience with reading cell formatting information from an xls document and can suggest a library that will help accomplish this task?

UPDATE:

I am looking at using ClosedXml because from what I have read about it, it seems to provide the functionality that I am needing. I am working from this sample code:

http://closedxml.codeplex.com/wikipage?title=Finding%20and%20extracting%20the%20data&referringTitle=Documentation

and have been able to read the content of a Excel document without issues. I have used just the Categories example from this code, but have formatted two of the categoryname cells to have background colours. Now what I am trying to do with ClosedXml is determine:

if a Fill background colour has been defined on a specific cell
if it has been defined, retrieve the colour of the Fill (hex value will do)

Here is some code that I have tried to use:

// Get all categories
while (!categoryRow.Cell(coCategoryId).IsEmpty())
{
    IXLCell categoryName = categoryRow.Cell(coCategoryName);
    try
    {
        categories.Add(categoryName.GetString() + " " + categoryName.Style.Fill.BackgroundColor.Color.ToHex());
    }
    catch
    {
        categories.Add(categoryName.GetString() + " None");
    }
    categoryRow = categoryRow.RowBelow();
}

But the code

categoryName.Style.Fill.BackgroundColor.Color.ToHex()

always throws the expection "The given key was not present in the dictionary.", even for cells that do have background colours defined. Anyone have any ideas how to get this working with ClosedXml?

I think it will be more convenient to ask separate question regarding the `Color.ToHex()` exception. — Anatolii Gabuza, Jun 26 '12 at 07:20
Thanks, AnatoliiG. I came to the same conclusion and have already asked a separate question about this. The question is here: http://stackoverflow.com/questions/11198653/cannot-convert-theme-color-to-color-in-closedxml — BruceHill, Jun 26 '12 at 07:27

score 5 · Accepted Answer · answered Jun 25 '12 at 12:05

5

OpenXml and its wrapper - ClosedXml gives you lot useful functions.

answered Jun 25 '12 at 12:05

Anatolii Gabuza

6,184
2
36
54

score 0 · Answer 2 · answered Jun 25 '12 at 12:07

0

If it doesn't need to be free I strongly recommend Aspose.Cells

answered Jun 25 '12 at 12:07

yannisgu

483
2
8

score 0 · Answer 3 · answered Jun 25 '12 at 12:10

0

I am partly dodging your requirements but you could convert xls->xlsx on the server and then search (XPath) the XML. Maybe xls->openoffice calc could do approximately the same.

answered Jun 25 '12 at 12:10

LosManos

7,195
6
56
107

score 0 · Answer 4 · answered Jun 25 '12 at 12:10

you can use Microsoft.Office.Interop.Excel

var workbookPath = "";
var worksheetName = "";

var applicationClass = new Application();
var workbook = applicationClass.Workbooks.Open(workbookPath, Type.Missing, Type.Missing,     Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

var worksheet = workbook.GetWorksheet(worksheetName);
var usedRange = worksheet.UsedRange;
var columnRangeIndexColumn= 1;


            for (int i = beginIndexRow; i <= usedRange.Rows.Count; i++)
            {
                var columnRange = usedRange.Cells[i, columnRangeIndexColumn];
                var value= columnRange.Value2;
             }




use this extension

public static Worksheet GetWorksheet(this Workbook value, string name)
        {
            foreach (Worksheet worksheet in value.Worksheets)
            {
                if (worksheet.Name == name)
                {
                    return worksheet;
                }
            }
            return value.ActiveSheet;
        }

The for loop will work OK if the row count is small, but it's really not recommended to loop through each cell like this. You can read the entire value as an array, it's much more efficient. — Colm Bhandal, May 29 '20 at 07:32
Also, the OP asks for cell formatting as well as data. As far as I can tell, this only pulls the values. — Colm Bhandal, May 29 '20 at 07:33

Reading Excel data and cell formatting in C#

4 Answers4

Linked