Why does ExcelQueryFactory only return 255 columns

Question

I have a spreadsheet that has about 500 columns. When I execute the linq query below, it only returns 255 columns. Is that a limitation on ExcelQueryFactory? Could it be an issue with my spreadsheet?

var book = new ExcelQueryFactory(filePath); var data = from x in book.Worksheet("MyStatisticSheet") select x;

score 3 · Accepted Answer · answered Feb 20 '14 at 19:20

3

It's a limitation of the Jet and Access Database Engines that LinqToExcel uses.

answered Feb 20 '14 at 19:20

Paul

18,349
7
49
56

score 2 · Answer 2 · edited Jan 09 '16 at 19:08

i was facing same issue and i found a workaround for this

var excel = new LinqToExcel.ExcelQueryFactory(InputFileName);
var EData1 = (from c in excel.WorksheetRangeNoHeader("A1","IU100000", InputFileWorkSheet) select c).ToList<LinqToExcel.RowNoHeader>();
var EData2 = (from c in excel.WorksheetRangeNoHeader("IV1","ZZ100000", InputFileWorkSheet) select c).ToList<LinqToExcel.RowNoHeader>();

var ExcelData = new List<LinqToExcel.RowNoHeader>();
for (var i = 0; i < EData1.Count; i++)
{
    var RowWithoutHeader = EData1[i].Concat(EData2[i]);
    ExcelData.Add(new LinqToExcel.RowNoHeader(RowWithoutHeader));

}

EDATA1 contains first 255 columns
EDATA2 contains remaining
we can have other hit EDATA3 if columns are more then 510.
ExcelData now contains all the columns and rows are limited to 100000 but that can also be increased/decreased

score 0 · Answer 3 · answered Mar 16 '18 at 10:20

An improved answer based on @Sunil's :

void Main()
{
    var excel = new LinqToExcel.ExcelQueryFactory(filePath);
    var eData1 = (from c in excel.WorksheetRange<Foo>("A1", "IU100000", "SheetName") select c).ToList<Foo>();
    var eData2 = (from c in excel.WorksheetRange<Foo>("IV1", "ZZ100000", "SheetName") select c).ToList<Foo>();

    var excelData = new List<Foo>();
    for (var i = 0; i < eData1.Count; i++)
    {
        Merge(eData2[i], eData1[i]);
        excelData.Add(eData1[i]);
    }
}

public class Foo
{ 
    public string Field1 { get; set; }  
    public string Field2 { get; set; } // located in column > 255
}

public void Merge<T>(T source, T target)
{
    foreach (var p in typeof(T).GetProperties()) 
    {
        if (p.GetValue(target) == null) {
            p.SetValue(target, p.GetValue(source));
        }
    }   
}

Why does ExcelQueryFactory only return 255 columns

3 Answers3