CODEWITHSUNDEEP
c#.netexcelms-officeclosedxml
BruceHill
BruceHill

Reputation: 7164

Reading Excel data and cell formatting in C#

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:

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?

Upvotes: 1

Views: 10633

Answers (4)

Aghilas Yakoub
Aghilas Yakoub

Reputation: 28970

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;
        }

Upvotes: 0

LosManos
LosManos

Reputation: 7692

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.

Upvotes: 0

yannisgu
yannisgu

Reputation: 493

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

Upvotes: 0

Anatolii Gabuza
Anatolii Gabuza

Reputation: 6260

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

Upvotes: 5

Related Questions