What's the best way to delete a column if you don't have keywords you are looking for? I have a spreadsheet I parse very often it has 0-35 columns and I want to automate this process so it only writes 0-9 columns. (They aren't in order) I tried messing with $col_num, I learned that's not what I need. I need to also sort by date, but thats not my main concern right now. I've adapted some of my excel sub routines that use Spreadsheet::ParseXLSX and Excel::Writer::XLSX and I've searched many forms related to this issue and they've had different format/problems and approaches to those problems especially since alot of them used WIN::OLE a module I'm not very familiar with. I just didnt want to write something completely from scratch with something i'm not used to if the modules I am familiar with will work. Please guide me wise monks.. I have a couple subroutine I could post too. The main one is one ive used on a few different excel projects, I've managed to narrow it down to 60 lines functionally, I realize that may not be ideal as far as SCCE.
use Spreadsheet::ParseXLSX;
use Excel::Writer::XLSX;
my $in_file = "Input.xlsx";
my $out_file = "Output.xlsx";
# Read in all rows from existing file
my $rows = read_excel($in_file);
my $count = 0;
# Write the updated rows to new file
my $col_num = 2;
write_excel($out_file, $rows, $col_num);
++$count;
sub read_excel {
my ( $file, $sheet ) = @_;
$sheet ||= 0;
my $parser = Spreadsheet::ParseXLSX->new();
my $workbook = $parser->parse($file);
if ( not defined $workbook ) {
die $parser->error;
}
my $worksheet = $workbook->worksheet($sheet);
my ( $row_min, $row_max ) = $worksheet->row_range();
my ( $col_min, $col_max ) = $worksheet->col_range();
my @rows;
for my $row ( $row_min .. $row_max ) {
my @cells;
for my $col ( $col_min .. $col_max ) {
my $cell = $worksheet->get_cell( $row, $col );
if (not $cell) {
push(@cells,'');
next;
}
my $value = $cell->value();
push(@cells,$value);
}
push(@rows,\@cells);
}
return \@rows;
}
sub write_excel {
my ( $file, $rows, $col_max ) = @_;
my $workbook = Excel::Writer::XLSX->new( $file );
if ( not defined $workbook ) {
die "Could not open file: $!";
}
my $worksheet = $workbook->add_worksheet();
my $row_num = 0;
for my $row ( @$rows ) {
for my $col (0 .. $col_max) {
$worksheet->write( $row_num, $col, $row->[$col] );
}
$row_num++;
}
$workbook->close();
return;
}
Input:
program_name enrolled_date_cst Prospect Name Salesperson Client Name Street Address City State Zip Code program_status Termination Date Current Program Length Total Debt Enrolled Verified Debt Amount Unverified Debt Amount Original Debt Without Conditional Debt payment_frequency Original scheduled first draft date First Deposit Date Change Flag First Draft Date First Draft Status Second Draft Date Second Draft Status Third Draft Date Third Draft Status Fourth Draft Date Fourth Draft Status Most Recent Draft Failure Reason Bank Account Status Received in Program Loan Flag OBC Completed Within 2 Hrs Flag Service Entity Name Client Deposit Flag Client Deposit Month
0 BRP-255762 2023-03-21 00Q5d00002ECrhKEAT Tammy Smith Larry Buss Charlestown IN 47111 Enrolled 56 46734 46734 0 46734 Monthly 2023-03-31 N 2023-03-31 Completed 2023-04-15 Completed 2023-05-14 Scheduled 2023-06-14 Scheduled VERIFIED FALSE Y Beyond Finance Yes 2023-03-31
1 BRP-281722 2023-04-25 00Q5d00002FBoa6EAD Dan Tucker Linda Smith Morehead KY 40351 Enrolled 55 42724 42724 0 29439 Monthly 2023-04-27 N 2023-04-27 Completed 2023-05-26 Scheduled 2023-06-26 Scheduled 2023-07-26 Scheduled AUTO_VERIFIED FALSE Y Beyond Finance Yes 2023-04-30
output:
program_name enrolled_date_cst Salesperson Client Name Total Debt Enrolled Verified Debt Amount Unverified Debt Amount First Draft Date First Draft status
26 BRP-238848 2023-02-24 Tami Smith MORRIS CLEGG 18461 10315 8146 2023-02-28 Returned
44 BRP-240923 2023-02-28 Tami Smith Paul Brannon 22162 22162 0 2023-03-07 Completed
