tag:blogger.com,1999:blog-284600789737488130.post1193902824191683249..comments2024-02-07T02:22:20.642-05:00Comments on Ruby on Windows: Automating Excel with Ruby: Defining a Range and Inserting DataDavid Mullethttp://www.blogger.com/profile/05597492688893461137noreply@blogger.comBlogger10125tag:blogger.com,1999:blog-284600789737488130.post-61157721587571433132007-10-29T15:54:00.000-05:002007-10-29T15:54:00.000-05:00Well it figures as soon as I ask I figure it out. ...Well it figures as soon as I ask I figure it out. This code would draw a box around the range F2:H7<BR/><BR/> sheet.Range("F2:F7").Borders(1).Linestyle=1 sheet.Range("H2:H7").Borders(2).Linestyle=1<BR/>sheet.Range("F2:H2").Borders(3).Linestyle=1 sheet.Range("F7:H7").Borders(4).Linestyle=1Unknownhttps://www.blogger.com/profile/11190245307850774864noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-57691049898342639322007-10-29T15:43:00.000-05:002007-10-29T15:43:00.000-05:00How would one go about drawing borders on a range?...How would one go about drawing borders on a range? It's the one thing I haven't quite been able to figure out. So say I want a box around A2:B4 or whatever...the closest I've come is to have the thing draw the complete grid for a given range and I need to be more selective than that!Unknownhttps://www.blogger.com/profile/11190245307850774864noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-38010750227182768832007-08-15T19:59:00.000-05:002007-08-15T19:59:00.000-05:00@viper:The Range.Value= method expects a 2-dimensi...@viper:<BR/><BR/>The <B>Range.Value=</B> method expects a 2-dimensional array -- an array of arrays -- even if the Range object spans only a single column. So, as you discovered, you must take your data array into the Second Dimension, so to speak.<BR/><BR/>Another method for doing that is:<BR/><BR/>ws.Range("A1:A500").Value = data.collect!{|x| [*x]}<BR/><BR/><B>collect!</B> iterates over an array. <B>[*x]</B> converts an object (x) to an array. It's the replacement for the soon-to-be-obsolete <B>to_a</B> method.<BR/><BR/>DavidDavid Mullethttps://www.blogger.com/profile/05597492688893461137noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-60129271941977924142007-08-09T15:58:00.000-05:002007-08-09T15:58:00.000-05:00I had a 1-dimensional array with 100 string elemen...I had a 1-dimensional array with 100 string elements, and attempted at first to copy what you had written<BR/><BR/>sheet.Range("A2:A101").Value = array<BR/><BR/>Your example was of a 2-dimensional array, and, for some reason, all those cells were filled with only the first element in the array. If, however, I first do something like<BR/><BR/>array[0]= [array[0],'test']<BR/><BR/>then somehow a pseudo-2d array is created, and the above command works. I had attempted it by chance, and I do not understand why this hack works~viper~https://www.blogger.com/profile/17741433322215868722noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-12227836517225464632007-08-07T21:21:00.000-05:002007-08-07T21:21:00.000-05:00Thank you very much, Topher!Microsoft's docs claim...Thank you very much, Topher!<BR/><BR/>Microsoft's docs claim that the Range object has a read-only Text property. And the methos does exist, but always returns nil in my testing.<BR/><BR/>As you mentioned, iterating over rows and cells to access the Text value can be painfully slow.<BR/><BR/>I don't have much to offer on this, unfortunately. If the issue is formatting, there are possible methods for determining the format of the data (ie, decimal versus currency, versus percentage). You could, for example, query the NumberFormat property for each column:<BR/><BR/>for column in worksheet.UsedRange.Columns<BR/>formats << column.NumberFormat<BR/>end<BR/><BR/>Then use worksheet.UsedRange.Value to get your 2-d array quickly, and use the array or hash of column formats to format your data upon output.<BR/><BR/>Hope that helps!David Mullethttps://www.blogger.com/profile/05597492688893461137noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-61669224652248806672007-08-07T11:21:00.000-05:002007-08-07T11:21:00.000-05:00Hello. You have some great articles here and I rea...Hello. You have some great articles here and I really appreciate you taking the time to post the information to help us out.<BR/><BR/>I have a quick question. I really would like to use this method to extract the data out of my excel files but unfortunately, it appears that it will only let me extract the value rather than the text.<BR/><BR/>I tried "data = worksheet.UsedRange.Text" but that doesn't seem to work. I also tried a few alternatives. Do you have any idea if this is possible and how so? Right now I am using the .each and it is taking forever (about 5 seconds per file...).<BR/><BR/>Thanks much and have a great day!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-61210728068362229512007-04-28T02:33:00.000-05:002007-04-28T02:33:00.000-05:00thx a lotthx a lotUnknownhttps://www.blogger.com/profile/03721495625677917305noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-33687081557686857742007-04-25T07:30:00.000-05:002007-04-25T07:30:00.000-05:00@dsf-In response to your question, I have posted a...@dsf-<BR/><BR/>In response to your question, I have posted a <A HREF="http://rubyonwindows.blogspot.com/2007/04/ruby-excel-inserting-and-deleting-rows.html" REL="nofollow">new article</A> about inserting and deleting rows and columns.<BR/><BR/>-DavidDavid Mullethttps://www.blogger.com/profile/05597492688893461137noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-92041764580184310642007-04-24T22:26:00.000-05:002007-04-24T22:26:00.000-05:00how can i insert a row to excel doc?eg:title1 1 te...how can i insert a row to excel doc?<BR/>eg:<BR/>title1 <BR/>1 test row<BR/>title2 < i want insert a row here<BR/>2 test row<BR/><BR/>i inspect many methods of the range and worksheet,but i can't work out,<BR/>please help meUnknownhttps://www.blogger.com/profile/03721495625677917305noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-61468791793372210952007-04-15T10:34:00.000-05:002007-04-15T10:34:00.000-05:00Alternatively, you could use the handy Range.Resiz...Alternatively, you could use the handy Range.Resize() method, which would give you something like<BR/><BR/>Worksheets.Cells(1,1).Resize(100,30)<BR/><BR/>...which is nice. I suppose one could fold everything into<BR/><BR/>target.Resize(data.size, data[0].size).Value = data<BR/><BR/>And thanks for reminding me that I could do more Excel automation with Ruby - I've been working in VBA for so long that I often forget.<BR/><BR/>MikeMike Woodhousehttps://www.blogger.com/profile/01434936820855294649noreply@blogger.com