C# Need advice on reading large Excel workbook
My program is required to read a large Excel workbook which has 4-5 sheets with roughly 35000 cells. The biggest problem that almost every cell is linked to cell in other large workbooks in a computer network.
It's is also required that user chooses a sheet to read data from first.
I tried Excel Interop, but the program just freezes while trying to open a workbook.
c# excel interop
asked Oct 24 '18 at 10:42
TechnicalTechnical
376
add a comment |
My program is required to read a large Excel workbook which has 4-5 sheets with roughly 35000 cells. The biggest problem that almost every cell is linked to cell in other large workbooks in a computer network.
It's is also required that user chooses a sheet to read data from first.
I tried Excel Interop, but the program just freezes while trying to open a workbook.
c# excel interop
asked Oct 24 '18 at 10:42
TechnicalTechnical
376
Try "Syncfusion". .
– Waleed Naveed
Oct 24 '18 at 10:45
I got good results with NPOI and it is Open Source.
– Marc Forment
Oct 24 '18 at 10:47
I would consider re-engineering the entire solution. This should like a perfect case of "just because you can, doesn't mean you should." It's impossible to suggest a solution without knowing more about your process and content, but I think your best bet in most case would be to stage the data from the spreadsheet somewhere else (like a DBMS) and mine it from there.
– Hambone
Oct 24 '18 at 18:13
How are you reading the book? If you are looping over all cells and callingValueeach time, the performance will be horrendous. Try reading the range into an array by callingValueon the range, then readin the values from the array.
– D Stanley
Nov 14 '18 at 16:59
add a comment |
My program is required to read a large Excel workbook which has 4-5 sheets with roughly 35000 cells. The biggest problem that almost every cell is linked to cell in other large workbooks in a computer network.
It's is also required that user chooses a sheet to read data from first.
I tried Excel Interop, but the program just freezes while trying to open a workbook.
c# excel interop
asked Oct 24 '18 at 10:42
TechnicalTechnical
376
My program is required to read a large Excel workbook which has 4-5 sheets with roughly 35000 cells. The biggest problem that almost every cell is linked to cell in other large workbooks in a computer network.
It's is also required that user chooses a sheet to read data from first.
I tried Excel Interop, but the program just freezes while trying to open a workbook.
c# excel interop
c# excel interop
asked Oct 24 '18 at 10:42
TechnicalTechnical
376
asked Oct 24 '18 at 10:42
TechnicalTechnical
376
asked Oct 24 '18 at 10:42
TechnicalTechnical
376
asked Oct 24 '18 at 10:42
TechnicalTechnical
376
asked Oct 24 '18 at 10:42
TechnicalTechnical
376
376
Try "Syncfusion". .
– Waleed Naveed
Oct 24 '18 at 10:45
I got good results with NPOI and it is Open Source.
– Marc Forment
Oct 24 '18 at 10:47
I would consider re-engineering the entire solution. This should like a perfect case of "just because you can, doesn't mean you should." It's impossible to suggest a solution without knowing more about your process and content, but I think your best bet in most case would be to stage the data from the spreadsheet somewhere else (like a DBMS) and mine it from there.
– Hambone
Oct 24 '18 at 18:13
How are you reading the book? If you are looping over all cells and callingValueeach time, the performance will be horrendous. Try reading the range into an array by callingValueon the range, then readin the values from the array.
– D Stanley
Nov 14 '18 at 16:59
add a comment |
Try "Syncfusion". .
– Waleed Naveed
Oct 24 '18 at 10:45
I got good results with NPOI and it is Open Source.
– Marc Forment
Oct 24 '18 at 10:47
I would consider re-engineering the entire solution. This should like a perfect case of "just because you can, doesn't mean you should." It's impossible to suggest a solution without knowing more about your process and content, but I think your best bet in most case would be to stage the data from the spreadsheet somewhere else (like a DBMS) and mine it from there.
– Hambone
Oct 24 '18 at 18:13
How are you reading the book? If you are looping over all cells and callingValueeach time, the performance will be horrendous. Try reading the range into an array by callingValueon the range, then readin the values from the array.
– D Stanley
Nov 14 '18 at 16:59
Try "Syncfusion". .
– Waleed Naveed
Oct 24 '18 at 10:45
Try "Syncfusion". .
– Waleed Naveed
Oct 24 '18 at 10:45
I got good results with NPOI and it is Open Source.
– Marc Forment
Oct 24 '18 at 10:47
I got good results with NPOI and it is Open Source.
– Marc Forment
Oct 24 '18 at 10:47
I would consider re-engineering the entire solution. This should like a perfect case of "just because you can, doesn't mean you should." It's impossible to suggest a solution without knowing more about your process and content, but I think your best bet in most case would be to stage the data from the spreadsheet somewhere else (like a DBMS) and mine it from there.
– Hambone
Oct 24 '18 at 18:13
I would consider re-engineering the entire solution. This should like a perfect case of "just because you can, doesn't mean you should." It's impossible to suggest a solution without knowing more about your process and content, but I think your best bet in most case would be to stage the data from the spreadsheet somewhere else (like a DBMS) and mine it from there.
– Hambone
Oct 24 '18 at 18:13
How are you reading the book? If you are looping over all cells and calling
Value each time, the performance will be horrendous. Try reading the range into an array by calling Value on the range, then readin the values from the array.– D Stanley
Nov 14 '18 at 16:59
How are you reading the book? If you are looping over all cells and calling
Value each time, the performance will be horrendous. Try reading the range into an array by calling Value on the range, then readin the values from the array.– D Stanley
Nov 14 '18 at 16:59
add a comment |
1 Answer
1
active
oldest
votes
Try ClosedXML: https://github.com/closedxml/closedxml This doesn't solve the size problem but I don't know of anything that will except using a database.
answered Nov 14 '18 at 16:49
Clayton HarbichClayton Harbich
272413
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f52966943%2fc-sharp-need-advice-on-reading-large-excel-workbook%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Try ClosedXML: https://github.com/closedxml/closedxml This doesn't solve the size problem but I don't know of anything that will except using a database.
answered Nov 14 '18 at 16:49
Clayton HarbichClayton Harbich
272413
add a comment |
Try ClosedXML: https://github.com/closedxml/closedxml This doesn't solve the size problem but I don't know of anything that will except using a database.
answered Nov 14 '18 at 16:49
Clayton HarbichClayton Harbich
272413
add a comment |
Try ClosedXML: https://github.com/closedxml/closedxml This doesn't solve the size problem but I don't know of anything that will except using a database.
answered Nov 14 '18 at 16:49
Clayton HarbichClayton Harbich
272413
Try ClosedXML: https://github.com/closedxml/closedxml This doesn't solve the size problem but I don't know of anything that will except using a database.
answered Nov 14 '18 at 16:49
Clayton HarbichClayton Harbich
272413
answered Nov 14 '18 at 16:49
Clayton HarbichClayton Harbich
272413
answered Nov 14 '18 at 16:49
Clayton HarbichClayton Harbich
272413
answered Nov 14 '18 at 16:49
Clayton HarbichClayton Harbich
272413
272413
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f52966943%2fc-sharp-need-advice-on-reading-large-excel-workbook%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Try "Syncfusion". .
– Waleed Naveed
Oct 24 '18 at 10:45
I got good results with NPOI and it is Open Source.
– Marc Forment
Oct 24 '18 at 10:47
I would consider re-engineering the entire solution. This should like a perfect case of "just because you can, doesn't mean you should." It's impossible to suggest a solution without knowing more about your process and content, but I think your best bet in most case would be to stage the data from the spreadsheet somewhere else (like a DBMS) and mine it from there.
– Hambone
Oct 24 '18 at 18:13
How are you reading the book? If you are looping over all cells and calling
Valueeach time, the performance will be horrendous. Try reading the range into an array by callingValueon the range, then readin the values from the array.– D Stanley
Nov 14 '18 at 16:59