Populate Table from excel sheet ?
-
I can open an excel woorkbook and select the sheet that i want to work with. After i have populated the sheet with data from a web front end i wish to display all the new data in a table on a webpage. I know in what range the data is on the worksheet but im having trouble writing it to the table. Can anyone point me in a right direction please ?? some code ... Excel._Worksheet objSheet; Excel.Sheets objSheets; public void PopulateTable() { objSheets = (Microsoft.Office.Interop.Excel.Sheets)Session["ExcelSheets"]; objSheet = (_Worksheet)objSheets.get_Item(3); /// 3rd worksheet in the workbook Range PrintQuoteRange = objSheet.get_Range("A1", "E68"); //data from cell A1:E1 to A68:E68 //// display rows, col and cells in a table //// ????? }
-
I can open an excel woorkbook and select the sheet that i want to work with. After i have populated the sheet with data from a web front end i wish to display all the new data in a table on a webpage. I know in what range the data is on the worksheet but im having trouble writing it to the table. Can anyone point me in a right direction please ?? some code ... Excel._Worksheet objSheet; Excel.Sheets objSheets; public void PopulateTable() { objSheets = (Microsoft.Office.Interop.Excel.Sheets)Session["ExcelSheets"]; objSheet = (_Worksheet)objSheets.get_Item(3); /// 3rd worksheet in the workbook Range PrintQuoteRange = objSheet.get_Range("A1", "E68"); //data from cell A1:E1 to A68:E68 //// display rows, col and cells in a table //// ????? }
You're on the right way ;) With this range you may fill a multidimensional array and put it into a table. Just use the .get_Value() method of the range object to retrieve this array.
string [,] mydata = PrintQuoteRange.get_Value(System.Type.Missing);Now you have the whole Data in the array. A simple for loop will do the rest ;) Tell me if you keep having trouble with getting the data into a table. I had to do something similar just 2 weeks ago and found this article very useful: How to automate Excel by using Visual C# to fill or to obtain data in a range by using arrays ATTENTION: if you want to set a value of a cell to more than 911 characters, you will get a com exception. To work around this issue just use the querytable instead (have a look at the COM forumpost i made recently) However, good luck with automating Excel ;) (Let me know if you are not able to kill the excel process properly - i just found a nice article to get rid of this too ;)) -
You're on the right way ;) With this range you may fill a multidimensional array and put it into a table. Just use the .get_Value() method of the range object to retrieve this array.
string [,] mydata = PrintQuoteRange.get_Value(System.Type.Missing);Now you have the whole Data in the array. A simple for loop will do the rest ;) Tell me if you keep having trouble with getting the data into a table. I had to do something similar just 2 weeks ago and found this article very useful: How to automate Excel by using Visual C# to fill or to obtain data in a range by using arrays ATTENTION: if you want to set a value of a cell to more than 911 characters, you will get a com exception. To work around this issue just use the querytable instead (have a look at the COM forumpost i made recently) However, good luck with automating Excel ;) (Let me know if you are not able to kill the excel process properly - i just found a nice article to get rid of this too ;))Thanks alot! It worked 100% .... but it looks a bit tacky. :~ I wanted it to look just like the format in Excel. So i went back to the drawing board for a new solution i want to share just incase someone else does it too. I copied the Excel sheet area i wanted to use and pasted it right in a new webform design view and saved it as Template.aspx . It looks exactly like the format in the excel sheet. Now the for cells i want to populate, i just name then with html tags. I then use a normal streamreader to read the Template.aspx file and use my session variables to change the tags to the values i want ...... and viola ! Thanks again for the table solution above, atleast it worked too. ;)