reading excel from c#, anything new?

Question

My question is very simillar to the one asked here: Reading Excel files from C#

My question is whether there is a new better way of doing this now (2 years later), if not does anybody have some examples of using the ACE OLEDB 12.0 witch seem to be the best way to go with it?

What I need to do is an application that reads an xlsx file every night, wrap the data in the excel sheet, and then save it to the database, any other tips?

Thanks

Check out my answer at this SO Question - http://stackoverflow.com/questions/3812857/need-a-library-for-creating-binary-excel-files-from-asp-net-anyone-know-a-good-on/3812943#3812943 — Mikael Svenson, Nov 02 '10 at 21:04
@Mikael: Hi, I really like the codeplex you posted, have you been working with it alot? There isn't that much information from the codeplex site about reading, do you know if there is any method like read until there's now rows left? A read from excel exampel would be much appreciated, I liked the one you posted about write. — Fore, Nov 03 '10 at 15:20
since you asked so nicely I made an example and added it as an answer to your question. Basically I started with http://excelpackage.codeplex.com/wikipage?title=Reading%20data%20from%20an%20Excel%20spreadsheet&referringTitle=Home and had to add some stuff. Be sure to give me a vote :) — Mikael Svenson, Nov 03 '10 at 20:18

score 2 · Accepted Answer · answered Nov 03 '10 at 20:16

Using ExcelPackage you can iterate all sheets and columns like this:

public class ExcelRead
{
    public void ReadExcel()
    {
        FileInfo existingFile = new FileInfo(@"C:\temp\book1.xlsx");
        using (ExcelPackage xlPackage = new ExcelPackage(existingFile))
        {
            foreach (ExcelWorksheet worksheet in xlPackage.Workbook.Worksheets)
            {
                var dimension = worksheet.Dimension();
                for (int row = dimension.StartRow; row <= dimension.EndRow; row++)
                {
                    for (int col = dimension.StartColumn; col <= dimension.EndColumn; col++)
                    {
                        Console.WriteLine(row + ":" + col + " - " + worksheet.Cell(row, col));
                    }
                }
            }
        }
    }
}

public class Dimension
{
    public int StartRow { get; set; }
    public int StartColumn { get; set; }
    public int EndRow { get; set; }
    public int EndColumn { get; set; }
}

public static class ExcelHelper
{
    private static readonly char[] _numbers = new[] {'0', '1', '2', '3', '4', '5', '6', '7', '8', '9'};

    public static Dimension Dimension(this ExcelWorksheet worksheet)
    {
        string range =
            worksheet.WorksheetXml.SelectSingleNode("//*[local-name()='dimension']").Attributes["ref"].Value;
        string[] rangeCoordinates = range.Split(':');

        int idx = rangeCoordinates[0].IndexOfAny(_numbers);
        int startRow = int.Parse(rangeCoordinates[0].Substring(idx));
        int startCol = ConvertFromExcelColumnName(rangeCoordinates[0].Substring(0, idx));

        if (rangeCoordinates.Length == 1)
            return new Dimension
                       {StartRow = startRow, StartColumn = startCol, EndRow = startRow, EndColumn = startCol};

        idx = rangeCoordinates[1].IndexOfAny(_numbers);
        int endRow = int.Parse(rangeCoordinates[1].Substring(idx));
        int endCol = ConvertFromExcelColumnName(rangeCoordinates[1].Substring(0, idx));
        return new Dimension {StartRow = startRow, StartColumn = startCol, EndRow = endRow, EndColumn = endCol};
    }

    public static int ConvertFromExcelColumnName(string name)
    {
        name = name.ToUpper();
        int result = 0;
        for (int i = 0; i < name.Length - 1; i++)
        {
            int val = name[i] - 64;
            int columnVal = (int) Math.Pow(26, name.Length - i - 1);
            result += val*columnVal;
        }
        result += name[name.Length - 1] - 64;
        return result;
    }
}

score 1 · Answer 2 · answered Nov 02 '10 at 20:58

1

I don't think it was listed in the other thread. I work for Infragistics and we have a library for reading and writing excel files. You can download a trial here

answered Nov 02 '10 at 20:58

Mike Dour

3,616
2
22
24

score 1 · Answer 3 · answered Nov 02 '10 at 21:04

1

I've had good luck with EPPlus for writing out to excel files, and I think it can read them nicely as well.

answered Nov 02 '10 at 21:04

hoffmanc

614
9
16

score 0 · Answer 4 · edited May 23 '17 at 12:29

0

Apache POI is a free Java library for accessing MS Office documents without having Excel installed. According to this post, it is possible to use IKVM.NET to make POI available for .NET programs.

edited May 23 '17 at 12:29

Community

1
1

answered Nov 02 '10 at 21:48

Doc Brown

19,739
7
52
88

score 0 · Answer 5 · answered Nov 02 '10 at 22:37

0

I've had very good experiences with Aspose Cells

http://www.aspose.com/categories/.net-components/aspose.cells-for-.net/default.aspx

answered Nov 02 '10 at 22:37

ulty4life

2,972
1
25
31

reading excel from c#, anything new?

5 Answers5