Compare And Edit Excel Rows C#
0
I am attempting to iterate through and excel file row by row..
First checking if the rows first column has a Name in a list
If so then update the seventh column of that row to 0.
The code below doesn't crash and seems to be doing what I am asking it to do.
There are 2 issues...
- After the code runs no changes are saved to the Excel file. I have tried several methods to .Save() and nothing takes. Could it be because I am using a the free version of VS?
I have roughly 150 names in the PersonsToExcludeMAIN and roughly 250 rows to compare too and the code take roughly 8 min to run. Why?
DB_PATH = @"C:UsersnDownloadsNick1.xlsx";
File.SetAttributes(DB_PATH, FileAttributes.Normal);
MyApp = new Excel.Application();
MyApp.Visible = false;
MyBook = MyApp.Workbooks.Open(DB_PATH);
MySheet = (Excel.Worksheet)MyBook.Sheets[1]; // Explicit cast is not required here
lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
//Get the used Range
Excel.Range usedRange = MySheet.UsedRange;
//Iterate the rows in the used range
foreach (Excel.Range row in usedRange.Rows)
{
//PersonsToExcludeMAIN has the Names Of all the People (in the "Name" property) in the DB_PATH (the excel file I am trying to edit) I want to set their Column "7" to "0"
List<Person> tempList = PersonsToExcludeMAIN.Where(item => item.Name == (row.Columns[1].Value2.ToString())).ToList();
if (tempList.Count > 0)
{
//This gets reached and seems to be executing properly
row.Columns[7].Value2 = "0";
}
}
//This Save() doesn't do anything
MyBook.Save();
MyBook.Close(true);
MyApp.Quit();
c# excel
asked Dec 29 '18 at 19:06
Nick LaMarcaNick LaMarca
3,1682680137
add a comment |
0
I am attempting to iterate through and excel file row by row..
First checking if the rows first column has a Name in a list
If so then update the seventh column of that row to 0.
The code below doesn't crash and seems to be doing what I am asking it to do.
There are 2 issues...
- After the code runs no changes are saved to the Excel file. I have tried several methods to .Save() and nothing takes. Could it be because I am using a the free version of VS?
I have roughly 150 names in the PersonsToExcludeMAIN and roughly 250 rows to compare too and the code take roughly 8 min to run. Why?
DB_PATH = @"C:UsersnDownloadsNick1.xlsx";
File.SetAttributes(DB_PATH, FileAttributes.Normal);
MyApp = new Excel.Application();
MyApp.Visible = false;
MyBook = MyApp.Workbooks.Open(DB_PATH);
MySheet = (Excel.Worksheet)MyBook.Sheets[1]; // Explicit cast is not required here
lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
//Get the used Range
Excel.Range usedRange = MySheet.UsedRange;
//Iterate the rows in the used range
foreach (Excel.Range row in usedRange.Rows)
{
//PersonsToExcludeMAIN has the Names Of all the People (in the "Name" property) in the DB_PATH (the excel file I am trying to edit) I want to set their Column "7" to "0"
List<Person> tempList = PersonsToExcludeMAIN.Where(item => item.Name == (row.Columns[1].Value2.ToString())).ToList();
if (tempList.Count > 0)
{
//This gets reached and seems to be executing properly
row.Columns[7].Value2 = "0";
}
}
//This Save() doesn't do anything
MyBook.Save();
MyBook.Close(true);
MyApp.Quit();
c# excel
asked Dec 29 '18 at 19:06
Nick LaMarcaNick LaMarca
3,1682680137
You shouldn't use Interop for something like this. Use some library. SO has many examples. Then all your problems will go away.
– T.S.
Dec 29 '18 at 20:46
@T.S. The data given is in excel files. Do I change the files to another format and then manipulate them?
– Nick LaMarca
Dec 29 '18 at 21:27
No. Just load them with the given library or driver of your choice. Point is, data manipulation without loadingExcel.Application. nugetmusthaves.com/Tag/Excel +microsoft.ace.oledballows you manipulate Excel data using ADO.net just like any table. + no need for excel installation, + no problem with server/web server/ mult instances deployment
– T.S.
Dec 29 '18 at 23:21
add a comment |
0
0
0
I am attempting to iterate through and excel file row by row..
First checking if the rows first column has a Name in a list
If so then update the seventh column of that row to 0.
The code below doesn't crash and seems to be doing what I am asking it to do.
There are 2 issues...
- After the code runs no changes are saved to the Excel file. I have tried several methods to .Save() and nothing takes. Could it be because I am using a the free version of VS?
I have roughly 150 names in the PersonsToExcludeMAIN and roughly 250 rows to compare too and the code take roughly 8 min to run. Why?
DB_PATH = @"C:UsersnDownloadsNick1.xlsx";
File.SetAttributes(DB_PATH, FileAttributes.Normal);
MyApp = new Excel.Application();
MyApp.Visible = false;
MyBook = MyApp.Workbooks.Open(DB_PATH);
MySheet = (Excel.Worksheet)MyBook.Sheets[1]; // Explicit cast is not required here
lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
//Get the used Range
Excel.Range usedRange = MySheet.UsedRange;
//Iterate the rows in the used range
foreach (Excel.Range row in usedRange.Rows)
{
//PersonsToExcludeMAIN has the Names Of all the People (in the "Name" property) in the DB_PATH (the excel file I am trying to edit) I want to set their Column "7" to "0"
List<Person> tempList = PersonsToExcludeMAIN.Where(item => item.Name == (row.Columns[1].Value2.ToString())).ToList();
if (tempList.Count > 0)
{
//This gets reached and seems to be executing properly
row.Columns[7].Value2 = "0";
}
}
//This Save() doesn't do anything
MyBook.Save();
MyBook.Close(true);
MyApp.Quit();
c# excel
asked Dec 29 '18 at 19:06
Nick LaMarcaNick LaMarca
3,1682680137
I am attempting to iterate through and excel file row by row..
First checking if the rows first column has a Name in a list
If so then update the seventh column of that row to 0.
The code below doesn't crash and seems to be doing what I am asking it to do.
There are 2 issues...
- After the code runs no changes are saved to the Excel file. I have tried several methods to .Save() and nothing takes. Could it be because I am using a the free version of VS?
I have roughly 150 names in the PersonsToExcludeMAIN and roughly 250 rows to compare too and the code take roughly 8 min to run. Why?
DB_PATH = @"C:UsersnDownloadsNick1.xlsx";
File.SetAttributes(DB_PATH, FileAttributes.Normal);
MyApp = new Excel.Application();
MyApp.Visible = false;
MyBook = MyApp.Workbooks.Open(DB_PATH);
MySheet = (Excel.Worksheet)MyBook.Sheets[1]; // Explicit cast is not required here
lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
//Get the used Range
Excel.Range usedRange = MySheet.UsedRange;
//Iterate the rows in the used range
foreach (Excel.Range row in usedRange.Rows)
{
//PersonsToExcludeMAIN has the Names Of all the People (in the "Name" property) in the DB_PATH (the excel file I am trying to edit) I want to set their Column "7" to "0"
List<Person> tempList = PersonsToExcludeMAIN.Where(item => item.Name == (row.Columns[1].Value2.ToString())).ToList();
if (tempList.Count > 0)
{
//This gets reached and seems to be executing properly
row.Columns[7].Value2 = "0";
}
}
//This Save() doesn't do anything
MyBook.Save();
MyBook.Close(true);
MyApp.Quit();
c# excel
c# excel
asked Dec 29 '18 at 19:06
Nick LaMarcaNick LaMarca
3,1682680137
asked Dec 29 '18 at 19:06
Nick LaMarcaNick LaMarca
3,1682680137
edited Dec 29 '18 at 19:14
Nick LaMarca
asked Dec 29 '18 at 19:06
Nick LaMarcaNick LaMarca
3,1682680137
asked Dec 29 '18 at 19:06
Nick LaMarcaNick LaMarca
3,1682680137
asked Dec 29 '18 at 19:06
Nick LaMarcaNick LaMarca
3,1682680137
3,1682680137
You shouldn't use Interop for something like this. Use some library. SO has many examples. Then all your problems will go away.
– T.S.
Dec 29 '18 at 20:46
@T.S. The data given is in excel files. Do I change the files to another format and then manipulate them?
– Nick LaMarca
Dec 29 '18 at 21:27
No. Just load them with the given library or driver of your choice. Point is, data manipulation without loadingExcel.Application. nugetmusthaves.com/Tag/Excel +microsoft.ace.oledballows you manipulate Excel data using ADO.net just like any table. + no need for excel installation, + no problem with server/web server/ mult instances deployment
– T.S.
Dec 29 '18 at 23:21
add a comment |
You shouldn't use Interop for something like this. Use some library. SO has many examples. Then all your problems will go away.
– T.S.
Dec 29 '18 at 20:46
@T.S. The data given is in excel files. Do I change the files to another format and then manipulate them?
– Nick LaMarca
Dec 29 '18 at 21:27
No. Just load them with the given library or driver of your choice. Point is, data manipulation without loadingExcel.Application. nugetmusthaves.com/Tag/Excel +microsoft.ace.oledballows you manipulate Excel data using ADO.net just like any table. + no need for excel installation, + no problem with server/web server/ mult instances deployment
– T.S.
Dec 29 '18 at 23:21
You shouldn't use Interop for something like this. Use some library. SO has many examples. Then all your problems will go away.
– T.S.
Dec 29 '18 at 20:46
You shouldn't use Interop for something like this. Use some library. SO has many examples. Then all your problems will go away.
– T.S.
Dec 29 '18 at 20:46
@T.S. The data given is in excel files. Do I change the files to another format and then manipulate them?
– Nick LaMarca
Dec 29 '18 at 21:27
@T.S. The data given is in excel files. Do I change the files to another format and then manipulate them?
– Nick LaMarca
Dec 29 '18 at 21:27
No. Just load them with the given library or driver of your choice. Point is, data manipulation without loading
Excel.Application. nugetmusthaves.com/Tag/Excel + microsoft.ace.oledb allows you manipulate Excel data using ADO.net just like any table. + no need for excel installation, + no problem with server/web server/ mult instances deployment– T.S.
Dec 29 '18 at 23:21
No. Just load them with the given library or driver of your choice. Point is, data manipulation without loading
Excel.Application. nugetmusthaves.com/Tag/Excel + microsoft.ace.oledb allows you manipulate Excel data using ADO.net just like any table. + no need for excel installation, + no problem with server/web server/ mult instances deployment– T.S.
Dec 29 '18 at 23:21
add a comment |
1 Answer
1
active
oldest
votes
-1
- Looking at the docs (here) for
Workbook.Save()method, it has a remark that says
The first time you save a workbook, use the SaveAs method to specify a name for the file.
Maybe that's the case?
- I see in your code that you are creating the name list inside the
foreachloop. I believe that's why it's taking too long. If you create it outside the loop, it might be faster.
answered Dec 29 '18 at 20:06
krobelusmeetsyndrakrobelusmeetsyndra
14615
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
});
}
});
draft saved
draft discarded
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%2f53972562%2fcompare-and-edit-excel-rows-c-sharp%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
-1
- Looking at the docs (here) for
Workbook.Save()method, it has a remark that says
The first time you save a workbook, use the SaveAs method to specify a name for the file.
Maybe that's the case?
- I see in your code that you are creating the name list inside the
foreachloop. I believe that's why it's taking too long. If you create it outside the loop, it might be faster.
answered Dec 29 '18 at 20:06
krobelusmeetsyndrakrobelusmeetsyndra
14615
add a comment |
-1
- Looking at the docs (here) for
Workbook.Save()method, it has a remark that says
The first time you save a workbook, use the SaveAs method to specify a name for the file.
Maybe that's the case?
- I see in your code that you are creating the name list inside the
foreachloop. I believe that's why it's taking too long. If you create it outside the loop, it might be faster.
answered Dec 29 '18 at 20:06
krobelusmeetsyndrakrobelusmeetsyndra
14615
add a comment |
-1
-1
-1
- Looking at the docs (here) for
Workbook.Save()method, it has a remark that says
The first time you save a workbook, use the SaveAs method to specify a name for the file.
Maybe that's the case?
- I see in your code that you are creating the name list inside the
foreachloop. I believe that's why it's taking too long. If you create it outside the loop, it might be faster.
answered Dec 29 '18 at 20:06
krobelusmeetsyndrakrobelusmeetsyndra
14615
- Looking at the docs (here) for
Workbook.Save()method, it has a remark that says
The first time you save a workbook, use the SaveAs method to specify a name for the file.
Maybe that's the case?
- I see in your code that you are creating the name list inside the
foreachloop. I believe that's why it's taking too long. If you create it outside the loop, it might be faster.
answered Dec 29 '18 at 20:06
krobelusmeetsyndrakrobelusmeetsyndra
14615
answered Dec 29 '18 at 20:06
krobelusmeetsyndrakrobelusmeetsyndra
14615
answered Dec 29 '18 at 20:06
krobelusmeetsyndrakrobelusmeetsyndra
14615
answered Dec 29 '18 at 20:06
krobelusmeetsyndrakrobelusmeetsyndra
14615
14615
add a comment |
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53972562%2fcompare-and-edit-excel-rows-c-sharp%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
You shouldn't use Interop for something like this. Use some library. SO has many examples. Then all your problems will go away.
– T.S.
Dec 29 '18 at 20:46
@T.S. The data given is in excel files. Do I change the files to another format and then manipulate them?
– Nick LaMarca
Dec 29 '18 at 21:27
No. Just load them with the given library or driver of your choice. Point is, data manipulation without loading
Excel.Application. nugetmusthaves.com/Tag/Excel +microsoft.ace.oledballows you manipulate Excel data using ADO.net just like any table. + no need for excel installation, + no problem with server/web server/ mult instances deployment– T.S.
Dec 29 '18 at 23:21