Delete entire row without using Win32::OLE

ravi45722 has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: Delete entire row without using Win32::OLE by Corion (Patriarch) on Sep 01, 2015 at 12:59 UTC
Spreadsheet::ParseExcel cannot read XLSX files. Have you tried simply writing out a new Excel file and omitting the lines you don't want in it? I guess that would be the simplest approach. Another simple approach would be to use splice on the internal datastructures of your object to remove the rows you don't want. But as you don't show any code and mention the wrong module, I cannot suggest a way of how to do this except that you investigate this yourself.	[reply]
Re^2: Delete entire row without using Win32::OLE by ravi45722 (Pilgrim) on Sep 01, 2015 at 13:08 UTC
In my excel sheet some test cases errors are there. Each error or modification has its own err_id. After rectifying it with the base of err_id (col 1) i want to delete that row from the excel sheet. But i cant able to use Win32::OLE. Can you suggest me how to use splice here.	[reply]
Re^3: Delete entire row without using Win32::OLE by Corion (Patriarch) on Sep 01, 2015 at 13:11 UTC
As I already said: But as you don't show any code and mention the wrong module, I cannot suggest a way of how to do this except that you investigate this yourself.	[reply]
Re^4: Delete entire row without using Win32::OLE by ravi45722 (Pilgrim) on Sep 01, 2015 at 13:18 UTC
Re^5: Delete entire row without using Win32::OLE by Corion (Patriarch) on Sep 01, 2015 at 13:25 UTC
Re: Delete entire row without using Win32::OLE by CountZero (Bishop) on Sep 01, 2015 at 18:41 UTC
There are yet no modules, other than Win32::OLE, that allow you to directly delete a row in an Excel spreadsheet. You can emulate this by reading each row (with Spreadsheet::ParseXLSX for instance) and then checking if that row is to be kept or deleted. If the row is to be kept, write it to a new Excel-file (with Excel::Writer::XLSX for instance) and once you have processed all sheets and rows in the original spreadsheet, your new spreadsheet should contain only the data you need. CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James My blog: Imperial Deltronics	[reply]
Re^2: Delete entire row without using Win32::OLE by ravi45722 (Pilgrim) on Sep 02, 2015 at 06:04 UTC
Thanks CountZero Now i am clear that no other module is available to delete entire row. I need other more information on these. I am working now with Spreadsheet::ParseXLSX to read values(Formatted) like this. `$income = $cell->value();` [download] Is there any other one to read values(formatted). I also read about Spreadsheet::Read which cannot work with formatted values (working with unformatted)	[reply] [d/l]
Re^3: Delete entire row without using Win32::OLE by poj (Abbot) on Sep 02, 2015 at 06:33 UTC
I also read about Spreadsheet::Read which cannot work with formatted values What ? `my @row = Spreadsheet::Read::row ($book->[1], 3) Get full row of formatted values (like $sheet->{A3} .. $sheet->{G3})` [download] poj	[reply] [d/l]
Re^4: Delete entire row without using Win32::OLE by ravi45722 (Pilgrim) on Sep 02, 2015 at 11:29 UTC
Re^5: Delete entire row without using Win32::OLE by Corion (Patriarch) on Sep 02, 2015 at 13:06 UTC
Some notes below your chosen depth have not been shown here
Re^5: Delete entire row without using Win32::OLE by poj (Abbot) on Sep 02, 2015 at 18:14 UTC
Some notes below your chosen depth have not been shown here
Re: Delete entire row without using Win32::OLE by locked_user sundialsvc4 (Abbot) on Sep 01, 2015 at 13:51 UTC
I frankly suggest that you (and your workgroup ...) are using altogether the wrong approach. You are using Microsoft Excel spreadsheets as a database. Why not use a database ... within Excel? Not “using an Excel spreadsheet as a database” (which it’s not, but, “using an actual database (of your choice), within, and by, Excel. Excel has many powerful but seldom-used capabilities for accessing external data as live views. See for example this article. The task that you describe appears to be a classic database task ... made even weaker by the fact that ~~you are~~ this application-design appears (as you describe it here) to be deleting information from the workbook. Information that, of all rights, should be important and valuable to the business. You would want a completed test to disappear from view, because it will no longer be `SELECT`ed, but not to disappear altogether. Furthermore, these data should be stored in a venue considerably more robust than “a file that is constantly being destroyed and replaced.” If you do have to continue this task, in this way, then let me please be quite direct and say that I think you should be doing it in VBA, within Excel. Or, at the very least, that you (I will say ...) must be doing it using OLE. There’s a reason for that: in both cases, “Excel is the one who’s actually doing it.” Therefore, also doing all of the calculations that are called-for and so forth. Maybe not the politically correct thing to say on a “Perl” forum, but in the interest of helping to sketch out what (I think) is the best strategy: “this strategy is extremely common, but, ‘this isn’t it.’ ” If you have the power and the standing to suggest that Excel could do this job better and differently with another approach being taken, I would suggest doing so.
Re^2: Delete entire row without using Win32::OLE by CountZero (Bishop) on Sep 01, 2015 at 18:46 UTC
The OP's problem is probably that he does not have Excel installed on his box and hence going the "OLE-way" or using VBA is of no use for him. CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James My blog: Imperial Deltronics	[reply]