Has anyone been able to read an Excel file directly from an Epicor function?
I’m working on converting some classic C# customization to a server-side Epicor function in Kinetic. The original code used OleDbConnection to read Excel files, but in the function environment, this throws errors like
CS1069 / CS0731: OleDbConnection not found or causes type forwarder cycle...
It seems like System.Data.OleDb is not supported in the Epicor Function sandbox,
I wanted to ask:
Has anyone managed to read Excel files from within a function?
Any workarounds using only allowed Epicor DLLs or accepted techniques?
What’s the best practice you follow — parse externally and pass data to the function?
I would appreciate any examples, guidance, or even confirmation that it’s not possible. Thanks!
If you have them save the excel file as a csv, then you can parse through it in a function. I wasn’t able to get this working with an excel file directly.
Hello @aosemwengie1,
Yes, you’re absolutely right — currently I’m also using that workaround: asking users to save the Excel file as CSV and then parsing it within the function. That approach works fine for now.
However, the challenge I’m facing is that the users specifically want to upload Excel files with multiple sheets, and unfortunately that’s not supported directly within the Epicor Function environment. So I’m exploring external options to handle .xlsx parsing and then feed the data into Epicor.
Thanks for confirming your experience — it’s helpful to know others have hit the same limitation.
I what the library that is used to export to excel is in Kinetic… I am going to guess that it may work in reverse as well. So if you can add that to a function then you should be able to use whatever methods that are available to iterate through the sheets. Perhaps even converting to JSON etc.
For posterity. It appears that the Erp.Internal.Lib.ExcelLib assembly has excelreader and excelwritter namespaces… So you should be able to use that as the basis of your function.
Thank you, @Hally
I think this might work, it looks like the DLL includes the ExcelReader option, which is exactly what I was hoping for. I’ll give it a try and see how it goes. Appreciate your help!
Just another thought, sometimes we try to do too much in one function making the routine less cohesive. For example, why should Epicor take on the job of reading Excel files? Why take on that dependency? Instead, you could write a regular ASP.NET core function that does that work and in turn posts to a Kinetic Function that updates Kinetic. Now I have two functions that are easier to test and diagnose when problems arise. I am not introducing an extra dependency into Kinetic. I could even use that import Kinetic Function for sources that are not Excel.
Valid point there @Mark_Wonsil, from point of view… I’ve always tried to see what’s available, and capable within my very narrow skillset (and mind I suppose) and trying to keep everything in one place rather than over the shop… Is that the correct approach? I think there will be a few schools of thought on that.
I do see your point. Time to broaden my horizons. @ScotHanselman got a few minutes to help an old dog out???
Maybe that makes sense if you already have a ASP.net stack live. But it seems like if Epicor already has the excel dependency, going the asp.net core route will add a lot more dependencies in total?
Exactly. Epicor natively exports to excel so it shouldnt be any additional dependency. I was not able to get anything to work with those excel libraries. Hoping somebody else can figure it out.
We do it to “consume” (I hate that word) files from other systems into Epicor. We can grab CSV or Excel files. Makes it really easy since Epicor has built in libraries for both.
If they are separate, are they really dependencies?
Maybe Epicor includes the dependency you want this time, but what if you want to use others? Do we wait? What if I want Open Telemetry now? Or want to use System.Text.Json with code generators? Or other features from the latest .NET?
How easy is it to test? (trick question, everyone tests in live. ) Did it fail in the parsing or updating Kinetic?
What happens if you want to import the Excel file from a process and not a user and Epicor is down? Splitting the functions gives you the ability to rerun or retry the import. Putting everything in one routine makes it fragile.
As the video points out, there are trade-offs for coupling and cohesion. Too coupled is hard to test, hard to manage, hard to recover. Too much cohesion and you have code all over the place and it can be hard to see where it’s used. It depends on the problem space. I know my Kinetic projects have tended far too much in the coupling area and has made maintenance and resiliance tough.
Thanks, I really appreciate your perspective, I completely agree that keeping responsibilities separated makes things cleaner and easier to manage. Trying to read Excel directly inside Epicor definitely adds unnecessary complexity, especially with the function environment being as limited as it is.
I’ve already suggested a similar approach to the client, as it aligns well with best practices. We’ve noted it for future improvements where we can gradually move toward a more modular and service-based setup.
Right now, though, the client’s existing system only outputs Excel files, and it’s tightly integrated into their current workflow. Since it’s working well for them, they’re hesitant to change it at this stage. But we’ve already had that conversation and they’re open to evolving the process step by step later on.
) Did it fail in the parsing or updating Kinetic?