please forgive me for i know this is basic but i simply don't know how to add stuff a list like this
-
I'm wanting to add all the values i get from my for statements into a list of type string. I tried the following but i get a casting error :(
List<string> str = new List<string>();
int rCount = 0;//row counter int cCount = 0;//column counter range = xlWorkSheet.UsedRange; for (rCount = 1; rCount <= range.Rows.Count; rCount++) { for (cCount = 1; cCount <= range.Columns.Count; cCount++) { str = (List<string>)(range.Cells\[rCount, cCount\] as Excel.Range).Value2; } }one day I hope to not be the one asking the questions but the one answering them. Until then please forgive me for my basic questions :(
-
Replace it with something like this:
for(rCount...){
for(cCount...){
str.Add(range.Cells[rCount, cCount].Value2.ToString());
}
}ahh thank you very much...
-
ahh thank you very much...
-
Hey one last thing if you have the time... What I'm trying to do using an example of course. is read my excel sheet columns a,b,c,d,e one row at a time into a list so I can take those values and hopefully insert them into a database. I have the database insert method working fine I just need to figure out how to get the data into a format so I can pass it to the stored procedure I made. I'm aiming to get index[0].text index[1].text and so forth for each row... What I have so far gets the values of each row but one column at a time so lets say I have tony smith testing somethingelse 1.99 i get the following results [0] tony [1] smith [2] testing [3] somethingelse [4] 1.99 what I'd like to do is label the results if all possible like first = [0], last =[0] that way when i wanted to use the data i could simply do something like this row.first row.last row.whatever here is the code i have those for but I'm confused how to accomplish what i'm trying to do
private void importDataFromExcelToolStripMenuItem\_Click(object sender, EventArgs e) { /\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* \* allows to read file from excel \* reference: http://csharp.net-informations.com/excel/csharp-read-excel.htm \* \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ OpenExcelFile.Title = "SELECT EXCEL FILE TO IMPORT FROM "; OpenExcelFile.Filter = "XLS FILES|\*.XLS"; OpenExcelFile.InitialDirectory = @"c:\\"; string filepath = null; if (OpenExcelFile.ShowDialog() == DialogResult.OK) { filepath = OpenExcelFile.FileName; } Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; Excel.Range range; xlApp = new Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Open(filepath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\\t", false, false, 0, true, 1, 0); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get\_Item(1); List<string> strList= new List<string>(); string str; int rCount = 0;//row counter int cCount = 0;//column counter -
Hey one last thing if you have the time... What I'm trying to do using an example of course. is read my excel sheet columns a,b,c,d,e one row at a time into a list so I can take those values and hopefully insert them into a database. I have the database insert method working fine I just need to figure out how to get the data into a format so I can pass it to the stored procedure I made. I'm aiming to get index[0].text index[1].text and so forth for each row... What I have so far gets the values of each row but one column at a time so lets say I have tony smith testing somethingelse 1.99 i get the following results [0] tony [1] smith [2] testing [3] somethingelse [4] 1.99 what I'd like to do is label the results if all possible like first = [0], last =[0] that way when i wanted to use the data i could simply do something like this row.first row.last row.whatever here is the code i have those for but I'm confused how to accomplish what i'm trying to do
private void importDataFromExcelToolStripMenuItem\_Click(object sender, EventArgs e) { /\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* \* allows to read file from excel \* reference: http://csharp.net-informations.com/excel/csharp-read-excel.htm \* \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ OpenExcelFile.Title = "SELECT EXCEL FILE TO IMPORT FROM "; OpenExcelFile.Filter = "XLS FILES|\*.XLS"; OpenExcelFile.InitialDirectory = @"c:\\"; string filepath = null; if (OpenExcelFile.ShowDialog() == DialogResult.OK) { filepath = OpenExcelFile.FileName; } Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; Excel.Range range; xlApp = new Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Open(filepath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\\t", false, false, 0, true, 1, 0); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get\_Item(1); List<string> strList= new List<string>(); string str; int rCount = 0;//row counter int cCount = 0;//column counterHy, It's all wrong. 1)Create a class like
public class Something{
public string Name{get;set;}
public string Surname{get;set;}
.
.
.
}And then create a
List<Something>instead of list of string(s); 2) When loading data if you keep loading it using Interop create something like:for(RCount...{
curr = new Something();
for(cCount...){
if(c==1){
curr.Name = (string)(range.Cells[rCount, cCount] as Excel.Range).Value2;
}
if(c==2){
...
}
.
.
}
//now(outside the column for) add it to the list of something
lst.Add(curr);
}But I Really don't recomend this approach. Use something like this. It's a lot faster and it does not require Excel to be installed
//the actual connection
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + yourFileNameHere+ ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\";";
OleDbConnection con = null;
lst = new List<Something>();
Something curr = new Something();con = new OleDbConnection(connString); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = con; cmd.CommandText = "SELECT Name,SurName,etc.. " + "FROM \[Sheet1$\]"; OleDbDataReader dr = null; try { con.Open(); dr = cmd.ExecuteReader(); while (dr.Read()) { curr = new Something(); curr.Name = dr.IsDBNull(0) ? "Empty" : dr.GetString(0); curr.SurName = dr.IsDBNull(1) ? "Empty" : dr.GetString(1); . . if (curr.IsOK())//this is really usefull { lst.Add(curr); } } } catch (Exception ex) { MessageBox.Show(ex.Message); //posible log and stuff } finally { if (con.State != ConnectionState.Closed) -
Hy, It's all wrong. 1)Create a class like
public class Something{
public string Name{get;set;}
public string Surname{get;set;}
.
.
.
}And then create a
List<Something>instead of list of string(s); 2) When loading data if you keep loading it using Interop create something like:for(RCount...{
curr = new Something();
for(cCount...){
if(c==1){
curr.Name = (string)(range.Cells[rCount, cCount] as Excel.Range).Value2;
}
if(c==2){
...
}
.
.
}
//now(outside the column for) add it to the list of something
lst.Add(curr);
}But I Really don't recomend this approach. Use something like this. It's a lot faster and it does not require Excel to be installed
//the actual connection
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + yourFileNameHere+ ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\";";
OleDbConnection con = null;
lst = new List<Something>();
Something curr = new Something();con = new OleDbConnection(connString); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = con; cmd.CommandText = "SELECT Name,SurName,etc.. " + "FROM \[Sheet1$\]"; OleDbDataReader dr = null; try { con.Open(); dr = cmd.ExecuteReader(); while (dr.Read()) { curr = new Something(); curr.Name = dr.IsDBNull(0) ? "Empty" : dr.GetString(0); curr.SurName = dr.IsDBNull(1) ? "Empty" : dr.GetString(1); . . if (curr.IsOK())//this is really usefull { lst.Add(curr); } } } catch (Exception ex) { MessageBox.Show(ex.Message); //posible log and stuff } finally { if (con.State != ConnectionState.Closed)WOW!!!! Thanks I didn't expect you to go through all this trouble but I really appreciate it. As you can tell I'm learning and trying. Honestly hate asking so many questions but I guess that is the only way to learn. Once again I totally appreciate your time.
-
WOW!!!! Thanks I didn't expect you to go through all this trouble but I really appreciate it. As you can tell I'm learning and trying. Honestly hate asking so many questions but I guess that is the only way to learn. Once again I totally appreciate your time.
-
One again you're welcome. But it didn't took me much time as I have some small "inhouse aaps/tricks" to load all kind of excel data to the DBs and vice versa. So I just copy and paste, renamed the columns/fields deleted logic and that's all.
You ever have the problem of copying stuff from access into an excel spreadsheet. and for whatever reason the code you posted does not read it? It keeps on tripping up on the getstring(). In my excel file I have a field called Price which I add a number to like 2.99 3.99 and so forth. if i type the stuff out manually in this format id authorfirst authorlast title price keywords 1 tony smith a book 1.99 blah,blah it works fine but if i paste from access into excel the price files get the little thing convert text to number. if i leave it alone it throws exception saying it can't cast double. so i changed it to getdouble same error. i converted it to number. tried again same exception. if i take and click on the field and edit it manually to say 3.99 then bam works fine. Just curious if you ever ran into this issue and if so how do you resolve it ?
-
You ever have the problem of copying stuff from access into an excel spreadsheet. and for whatever reason the code you posted does not read it? It keeps on tripping up on the getstring(). In my excel file I have a field called Price which I add a number to like 2.99 3.99 and so forth. if i type the stuff out manually in this format id authorfirst authorlast title price keywords 1 tony smith a book 1.99 blah,blah it works fine but if i paste from access into excel the price files get the little thing convert text to number. if i leave it alone it throws exception saying it can't cast double. so i changed it to getdouble same error. i converted it to number. tried again same exception. if i take and click on the field and edit it manually to say 3.99 then bam works fine. Just curious if you ever ran into this issue and if so how do you resolve it ?
Nope, not really but I see what you mean. Could be many reasons: 1) Check to see if your excel data doesn't contain any "'" sign at the beging 2) Try no formatting => leave the cells to general. 3) If all this fails then two options: a) check your regional and language settings. See what's the decimal separator. While C# by default uses the Culture Invariant/Neutral, excel uses the system settings. Ex: in US Language and settings 300.05 => OK it loads it as a number in Romanian 300,05 => to load it as a number. b) if all this fails and hell gets lose than go to the last resource and fight back with some VBA => macros. Create a macro that transforms your cells.
-
Nope, not really but I see what you mean. Could be many reasons: 1) Check to see if your excel data doesn't contain any "'" sign at the beging 2) Try no formatting => leave the cells to general. 3) If all this fails then two options: a) check your regional and language settings. See what's the decimal separator. While C# by default uses the Culture Invariant/Neutral, excel uses the system settings. Ex: in US Language and settings 300.05 => OK it loads it as a number in Romanian 300,05 => to load it as a number. b) if all this fails and hell gets lose than go to the last resource and fight back with some VBA => macros. Create a macro that transforms your cells.
i got it to work. simply took and typed in a number in another field. selected it and copied. then selected all the price values i wanted to use. right clicked paste special and selected formulas and number formating and it worked just fine. thanks again. Now If i can only figure out this dang treenode thing I'll be cookin with gas.