paulyoder / linqtoexcel Goto Github PK

Removed. Did not realize WorksheetRange was overloaded to allow for sheet selection.

Is there any way to open the file as read only?

var excelQuery = new ExcelQueryFactory(path, true (readonly flag));

Or

var excelQuery = new ExcelQueryFactory(path);
excelQuery.AsReadOnly = true;

Hi,

I want to map the column with same name to multiple properties , i couldn't figure out how to do it.

        excel.AddMapping<ExcelFaceMech>(x => x.Comp, "Comp?");
        excel.AddMapping<ExcelFaceMech>(x => x.Ready, "Ready   Y/N");
        excel.AddMapping<ExcelFaceMech>(x => x.WorkOrder, "Work Order");
        excel.AddMapping<ExcelFaceMech>(x => x.AppliesTo, "Applies To");
        excel.AddMapping<ExcelFaceMech>(x => x.StartDate, "Start Date");
        excel.AddMapping<ExcelFaceMech>(x => x.ShiftAllocated, "Shift Allocated To");
        excel.AddMapping<ExcelFaceMech>(x => x.ManualTrade, "Manual\nTrade"); //pls look the column named like this is NOT MAPPING 
        excel.AddMapping<ExcelFaceMech>(x => x.ManualPanel, "Manual\rPanel"); //same ,NOT MAPPING 
        // week 1 
        excel.AddMapping<ExcelFaceMech>(x => x.FirstMonday, "Mon");
        excel.AddMapping<ExcelFaceMech>(x => x.FirstTuesday, "Tue");
        excel.AddMapping<ExcelFaceMech>(x => x.FirstWednesday, "Wed");
        excel.AddMapping<ExcelFaceMech>(x => x.FirstThursday, "Thu");
        excel.AddMapping<ExcelFaceMech>(x => x.FirstFriday, "Fri");
        excel.AddMapping<ExcelFaceMech>(x => x.FirstSaturday, "Sat");

        // week 2

        excel.AddMapping<ExcelFaceMech>(x => x.SecondMonday, "Mon");
        excel.AddMapping<ExcelFaceMech>(x => x.SecondTueday, "Tue");


        var faceMechResult = excel.WorksheetRange<ExcelFaceMech>("A5", "AS" + _faceMechRows.ToString(), facemechSheetName).
                        Where(i => i.WorkOrder != null).Select(x => x).ToList();

what i'm currently doing , the problem with it is SecondMonday Property always get the value of Column named Mon . which is the first , i want to get the second as so on . is there any work around for it .

pls let me know
[email protected]

Thanks in advance

As an example, the below statement fails with an error message saying

'-1.Produc' is not a valid column name. Valid column names are: 'Product'

workbook.Worksheet(Of Thing)("Index").Where(Function(x) someDictionary.ContainsKey(x.Product))

I'm not sure what's going on but it looks like the query model is incorrect for certain where clauses.

I have a sheet of sports results where ties are possible, which has a position value in column 1 (it doesn't have a header). The position for each row can be an integer, where that row is the only row that holds that position. If tied positions are indicated as 2T for "tied in second place", the value is returned in the list of results, but it it is stored as T2, it's excluded. Can you give me any help on understanding how to prevent this from happening?

Is it possible to grab the first worksheet in the excel file irrespective of its name?

This will select sheet by name.

var excel = new ExcelQueryFactory("excelFileName");
var oldCompanies = from c in repo.Worksheet(1) //Queries the second worksheet in alphabetical order
where c.LaunchDate < new DateTime(1900, 1, 1)
select c;

When trying to read data from multiple sheets using two instance still have data conflict,

         string facemechSheetName = "Face Mech WO's";
         string electSheetName = "Elec WO's";

          var excel = new ExcelQueryFactory(pathToExcelFile);

              //mappings
              excel.AddMapping<ExcelFaceMech>(x => x.Comp, "Comp?");
              excel.AddMapping<ExcelFaceMech>(x => x.Ready, "Ready   Y/N");
              excel.AddMapping<ExcelFaceMech>(x => x.WorkOrder, "Work Order");
               excel.AddMapping<ExcelFaceMech>(x => x.AppliesTo, "Applies To");
               excel.AddMapping<ExcelFaceMech>(x => x.StartDate, "Start Date");
               excel.AddMapping<ExcelFaceMech>(x => x.ShiftAllocated, "Shift Allocated To");

            // This work Fine 
        var faceMechResult = excel.WorksheetRange<ExcelFaceMech>("A5", "BN" + _faceMechRows + "", facemechSheetName).
                        Where(i => i.WorkOrder != null).Select(x => x).ToList();

            /// The data in the result is always mixed with data in FIRST.
           var elecWoResult0 = excel.WorksheetRange<ExcelFaceMech>("A5", "P589",electSheetName).
        //                 Where(x => x.WorkOrder != null).Select(x => x).ToList();

i Tried with initializing a new instance also still the second sheet has problem , is this a problem with Sheet . How could i overcome this.

Based on the source, the modification to move the log4net reference from file assembly to NuGet package dependency has been made. However, this change hasn't been published to the NuGet gallery. LinqToExcel v1.9.0 (and x64) both are showing as having no package dependencies and are referencing log4net as a file assembly. This seems to be causing dependency conflicts with log4net when creating setup projects. @paulyoder, any idea when new package versions with these changes can be published?

I'm getting this error as soon as I try to access the Excel file:

"The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."

Does MS Excel have to be installed on my computer?

I'm on Win8x64, but I set the build of the current app to x86, do I have to install additional software?

Hello there, I am using linq to excel to read a excel file. Everything was fine until I read a excel file populated using formulas. I've searched enough to read the cells calculated value but failed in it. Is there anybody who can provide some idea to upload the formula calculated values from a excel sheet? Thanks in advance..

I have a number formatting related issue with LinqToExcel

I am importing a test excel sheet with 2 same rows. One row has deleted formating. On formatted row the number 12000 is imported as 12, on unformatted its 12000.

Problem is only on server, on my notebook is it OK. Problem is with both xls and xlsx.

I think there is some confusion with comma interpreted sometimes as decimal separator or thousands separator.

Code:

    var sheet = from x in excel.WorksheetNoHeader(0)
                select x;
    foreach (RowNoHeader row in sheet)
    {
          somevar = row[3].Cast<double>();

I'm trying to query a .csv file generated from an external system and some example data looks like:

"1230200",,"01","UST, LTD"

They system is putting quotes around fields that don't need them. That is causing LinqToExcel to treat those fields as arrays. I'm having to open the .csv files with excel and save them again as a .csv file to remove the unnecessary quotes so the line would look like:

1230200,,1,"UST, LTD"

which LinqToExcel reads happily.

Are there any configuration options that i'm missing to have LinqToExcel read my files correctly?

Hi,

If my excelsheet and according some data types on some columns . how would i skip the row that throws exception and read all the other data.

                var result= excel.WorksheetRange<ExcelFaceMech>("A5", "AS" + _elecWoRow.ToString(), electSheetName).
                              Where(x => x.WorkOrder != null).Select(x => x).ToList();

In here if some exception occures the entire result will be Empty , how would i skip the Column/Row and read all other data's.

LinqToExcel fails with external table format exception UNLESS I already have the spreadsheet open in excel
Working with xls 2003 files

I get an error (see below) when I run my application, UNLESS I already have the file open in Excel. Any ideas?

Unhandled Exception: System.Data.OleDb.OleDbException: External table is not in the expected format.
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInf
o, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnection
PoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory conne
ctionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at LinqToExcel.Query.ExcelQueryExecutor.GetDataResults(SqlParts sql, QueryModel queryModel)
at LinqToExcel.Query.ExcelQueryExecutor.ExecuteCollection[T](QueryModel queryModel)
at Remotion.Data.Linq.Clauses.StreamedData.StreamedSequenceInfo.ExecuteCollectionQueryModel[T](QueryModel queryModel,
IQueryExecutor executor)
at Remotion.Data.Linq.Clauses.StreamedData.StreamedSequenceInfo.ExecuteQueryModel(QueryModel queryModel, IQueryExecut
or executor)
at Remotion.Data.Linq.QueryModel.Execute(IQueryExecutor executor)
at Remotion.Data.Linq.QueryProviderBase.Execute[TResult](Expression expression)
at Remotion.Data.Linq.QueryableBase1.GetEnumerator() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)
at ImportLicMgtData.Program.readInputData() in C:\Users\nnjones\Documents\Visual Studio 2010\Projects\PDTDataImport\I
mportLicMgtData\Classes\Program.cs:line 122
at ImportLicMgtData.Program.Main(String[] args) in C:\Users\nnjones\Documents\Visual Studio 2010\Projects\PDTDataImpo
rt\ImportLicMgtData\Classes\Program.cs:line 1555

http://code.google.com/p/linqtoexcel/issues/detail?id=50

How can I return null from an empty cell instead of default value?

I use
row[15].Cast();

I see this in your code

   public T Cast<T>()
    {
        return (Value == null || Value is DBNull) ?
            default(T) :
            (T)Convert.ChangeType(Value, typeof(T));
    }

but also this in extensions

    public static object Cast(this object @object, Type castType)
    {
        //return null for DBNull values
        if (@object.GetType() == typeof(DBNull))
            return null;

        //checking for nullable types
        if (castType.IsGenericType &&
            castType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
        {
            castType = Nullable.GetUnderlyingType(castType);
        }
        return Convert.ChangeType(@object, castType);
    }

thx

I have tried this first but cell.Cast has check for null value and doesn't care if type is nullable

Hi!
I have the issue with the nuget package of the library. It contains only x86 version. Is there a reason for this and could it be possible to make package with any cpu build?

In excel file with two sheet and same column name, how property to column mapping?

Hi,

I am trying to use the WorksheetRangeNoHeader method with the input parameters like ("A1", "CAA10000",1) as a workaround on the 255 columns limit on the provider. While this method works in a others' cases( http://social.msdn.microsoft.com/Forums/en-US/cf21f1be-d6e8-4ed9-b977-d6876bf0cf06/import-data-from-excel-file-into-a-datatable-column-limit). It is not working for linqToExcel.

do you have a workaround for this limitation?

Thanks
Casbby

Conditions

1. Configure Visual Studio project to compile as 64bit, using LinqToExcel_64 and with AccessDatabaseEngine_64 installed.
2. AccessDatabaseEngine_32 is NOT installed. (My machine runs Windows 7 x64, with Office 2010x64 installed.)
3. In code, ensure DatabaseEngine is set to ACE.

var excel = new ExcelQueryFactory(fileName);
excel.DatabaseEngine = LinqToExcel.Domain.DatabaseEngine.Ace;
var worksheet = excelFile.WorksheetNoHeader(0); //<- Fails here
//The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

Issue

If calling excel.WorksheetNoHeader([index]) or excel.GetWorksheetNames(), ExcelQueryExecutor is called, which in turn calls GetWorksheetName.

GetWorksheetName calls ExcelUtilities.GetWorksheetNames([filename]. And, finally, that procedure has this code:

var args = new ExcelQueryArgs();
args.FileName = fileName;
return GetWorksheetNames(args);

The problem is that ExcelQueryArgs, by default, sets the DatabaseEngine to Jet because it's the first enum. So, the error is thrown: "The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine."

In the above case, for my needs, I changed GetWorksheetName to call GetWorksheetNames([args]).

private void GetWorksheetName()
{
    if (_args.FileName.ToLower().EndsWith("csv"))
        _args.WorksheetName = Path.GetFileName(_args.FileName);
    else if (_args.WorksheetIndex.HasValue)
    {
        //var worksheetNames = ExcelUtilities.GetWorksheetNames(_args.FileName);
        var worksheetNames = ExcelUtilities.GetWorksheetNames(_args); //need to pass full args, not just filename, or Jet will be used by default.

But I know that's not a full fix. This looks like it could be a tricky problem. At first glance, it ideally looks like in all cases the user's ExcelQueryArgs would be passed to any needed procedures. At minimum, I think the default ExcelQueryArgs constructor should set DatabaseEngine based on the machine's 32/64 bit state.

However, there are other procedures that are affected by this issue. If it were a simple fix, I'd figure out the testing issues (below), branch, fix and do a pull request. But this seems like it will require some deeper thought.

Testing Note

It was challenging getting testing working, because the older version of Mbunit (and, likely, my ignorance). But more importantly, I'm not sure your tests would catch the issue. To debug a test using Microsoft's testing framework, I had to set the local.testsettings so that the test would run as 64 bit.
http://msdn.microsoft.com/en-us/library/ee782531(v=vs.100).aspx

Thanks,
Charles

Build (web): Reference.svcmap: Could not load file or assembly 'Castle.DynamicProxy2, Version=2.1.0.0, Culture=neutral, PublicKeyToken=407dd0808d44fbdc' or one of its dependencies. The system cannot find the file specified.

Any ideas?

LinqToExcel seems like such a nice idea but it didn't work for me. Sample project: http://morpher.ru/DictionaryCompiler.zip

I am able to get the row value using row [0] but not using a strongly typed mapping. Why?

Hi,

I am currently using the GetColumnNames() and it works great except for the fact that there is a char limit on the string it returns of 64. Is there a way I can make that limit a lot higher or set it to unlimited?

Kind regards,

RandomUniquename

Hi,

Thanks for the great library.

I've been running into an issue when running approx 500 queries against the same spreadsheet by effectively calling WorksheetRangeNoHeader in a loop with different parameters.

I get an OleDbException ("System resource exceeded") for some spreadsheets, but not all. I would guess this is an issue with Ace getting overloaded with connections. What I'd like to do is share the connection across all queries as it's always against a single sheet.

Can't see anything in the API to allow me to do that? Any suggestions?

StackTrace:
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at LinqToExcel.Query.ExcelQueryExecutor.GetDataResults(SqlParts sql, QueryModel queryModel)
at LinqToExcel.Query.ExcelQueryExecutor.ExecuteCollection[T](QueryModel queryModel)
at Remotion.Data.Linq.Clauses.StreamedData.StreamedSequenceInfo.ExecuteCollectionQueryModel[T](QueryModel queryModel, IQueryExecutor executor)
at Remotion.Data.Linq.Clauses.StreamedData.StreamedSequenceInfo.ExecuteQueryModel(QueryModel queryModel, IQueryExecutor executor)
at Remotion.Data.Linq.QueryModel.Execute(IQueryExecutor executor)
at Remotion.Data.Linq.QueryProviderBase.Execute[TResult](Expression expression)
at Remotion.Data.Linq.QueryableBase1.GetEnumerator() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)

Is there any 64-bit LinqToExcel NuGet package?

Is there a way to use LinqToExcel with a stream? The code appears to be tightly coupled to a file path.

Thanks.

I wonder, if there's a way to refactor the code in such way, as to support getting the thrid sheet in an xls as it's displayed in Excel, not the alphabetical index?

This library is fantastic and meet my requirement pls what is the workaround for the
ITransactionLocal interface is not supported. as I will like to carry out the import within
a transaction scope

Thanks

If the following calls are made:

_repo.AddTransformation<Company>(p => p.StartDate, value => DateTime.Now);
_repo.AddTransformation<OtherCompany(p => p.StartDate, value => "1/1/2013");

The second call will fail because the underlying dictionary is only keyed on the property name, not the property name and the TSheetData type, and an entry with that property name already exists. This happens even though the given Func<string,object> may derive an entirely different type for the property name (as in the example above, where StartDate is a DateTime for one class and a string for another).

Hi. Please disregard the issue posted. This was solved - legacy references were pointing in the wrong locations. Apologies for the firedrill.

If have a spreadsheet that contains vectors, stored in two or three columns.

Is there a way to get LinqToExcel to map these two or three columns directly into a property?

Example: Property might be called MyPosition. Columns might be called MyPosition_x, MyPosition_y, MyPosition_z.

The ITransactionLocal interface is not supported by the 'Microsoft.Jet.OLEDB.4.0' provider. Local transactions are unavailable with the current provider.

I just ran across this and it looks promising for what I need. The only problem so far is it opens the file.

All I have so far is:

var excel = new ExcelQueryFactory(Server.MapPath("sample.xls"));
var sheetNames = excel.GetWorksheetNames();

If I just run the first line, it's find. But as soon as I run a page with the 2nd line, it loads the page, but then it also opens the file in Excel.

What am I missing?

Thanks!

I tried to use LinqToExcel to connect to an Excel file using ACE.OLEDB but get the following error:
The ITransactionLocal interface is not supported by the 'Microsoft.ACE.OLEDB.12.0' provider. Local transactions are unavailable with the current provider.

There exists a workaround by adding the following code to the connection string:
";OLE DB Services=-4;"

But unfortunately this is not possible when using LinqToExcel at the moment.

I have a class with property "AVG_BAL", in the excel file the header name is "AVG_BAL " with a trailing space.
AVG_BAL property on object result is always null. I do no have control over the file therefore I cannot change the column name.

Is there a way for LinqToExcel to ignore any leading or trailing spaces?

If I use that in a mvc5 project and deploy it to the server, I get an error message that the assembly or a dependency was not found.

Can you tell me what dependencies linq2excel has? I installed the Access 2013 runtime already on the server.

Many greetings
Tim

It tells something about "Could not find installable ISAM". I've googled it and it seems that it mostly occurs because of incorrect connection string.

I have a class in which I want to ignore some fields/properties? Is it possible?

Consider these mappings:

    instance.AddMapping<Type1>(x => x.IdentificationName, "Type1 Identification Name");
    instance.AddMapping<Type2>(x => x.IdentificationName, "Type2 Identification Name");

These conflict because the property names are both the same.
The only other workaround that i've been able to use without changing the property names is to reinstance the ExcelQueryFactory before I map to a different sheet. This however fails when you have transformations that reference other sheets in a relational manner.

Hi ,

I have published web app in my server . It works fine . I have given my client the source code , he has tested it from Visual studio , he gets the "linqtoexcel external table is not in the expected format" but he has Office Installed 2013 , and we are using .xlsx format .

How would i make this work ,
pls let me know as early as possible , this because it happens on the delivery time , so hope u understand the pressure.

[email protected]

If you pass in an XLS with the following:

Col A, Col B, Col C

Row 1: 1, 2, 3
Row 2: a, b, c

You will receive the Column Names of "F1, F2, F3". If you pass in an XSLX with the same data you will get "1, 2, 3". Should these both not read "A, B, C"?

Is it possible to have the implementation returns each row using yield rather than filling a List in memory and returning that?

This would really help me out as I am working with very large excel files.

What steps will reproduce the problem?

1. Run the attached project
2. Notice in the xlsx file (in the bin\Debug directory) the value in D12 has the value "TextPickLetters, ImageV, AudioChooseA"
3. Notice the console output. The value from D12 is not displayed.

What is the expected output? What do you see instead?
The expected output is for the 11th section object to have its media names populated. Instead an empty string is passed into the method. This is presumably because the column next to it or values above it are empty.

What version of the product are you using? On what operating system?
Latest version from NuGet. Should be in the project.
OS = Win 7

The file is attached back in the wiki
http://code.google.com/p/linqtoexcel/issues/detail?id=60

This is great - thank you! My app works great locally - but once pushed to the live server I get the following error when calling ExcelQueryFactory.GetWorksheetNames()

External table is not in the expected format. ---> System.Data.OleDb.OleDbException: External table is not in the expected format. at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open() at LinqToExcel.Query.ExcelUtilities.GetWorksheetNames(String fileName) at LinqToExcel.ExcelQueryFactory.GetWorksheetNames()

Would this be due to not actually having MS Office installed on the server? Or something else?

Hoping you have more insight. Thanks,

Andy

Hello,

i am using code from
http://stackoverflow.com/questions/15791480/filtering-of-blank-rows-in-linqtoexcel-before-parsing-transformation-mapping

FormatException on trying to call _queryFactory.Worksheet.

I would like to know in which colum the exceptions occurs. Is it possible to find it out?

This is my first time to use LinqToExcel.dll. When I created the demo and ran the app, the app threw System.BadImageFormatException. My OS is Win7 64-bit.

You should be able to use Any CPU platform target if you use the newest Microsoft Access 2010 Runtime.

http://www.microsoft.com/downloads/en/details.aspx?familyid=57A350CD-5250-4DF6-BFD1-6CED700A6715&amp;displaylang=en

When certain characters are included in column headers, those columns are not recognized. If certain characters are reserved and this is functioning as intended, this should be included in documentation.

Example:
var excel = new ExcelQueryFactory(filePath);
excel.AddMapping(x => x.TubeBarcode, "Tube Barcode");
excel.AddMapping(x => x.Freezer, "Freezer No.");
excel.AddMapping(x => x.FreezerRack, "Freezer Rack");

When querying a range with the above headers, column "Freezer No." is not recognized, while all others are.

Hello, I really like this API, it makes my code much easier to read. It will really be a great addition if you expose methods to write back to the Excel file.
so as in Linq to Sql you can make a method like SubmitChanges() which applies whatever changes the user code did to Cell objects.
If this feature is not in your plans at least you can expose properties in the Cell object that return its coordinates so that i can use other APIs that can write to Excel using these coordinates.

Thanks
Michael