Previously, I walked through the full treatment of developing new reports from the ground up, starting in SQL Server Management Studio, then adding the View version to the new reports screen we added. Today, we’ll finish that off, by adding the Download version.
The ship-to reports,, both view and download, along with the new Customer Reports screen, plus new Contract Reports and Warehouse Reports. With the addition of the new Customer Reports screen, all three need to have all menu entries, necessitating the replacement screens. The whole set is available bendustries.co/files/customerreports.zip.
Once again, we’ll start with an existing file. The download version of will work. It really doesn’t make a whole lot of difference which one you start with, the process is largely the same across the board.
The first thing I like to do in creating new Excel reports is the file name, mostly so I won’t forget to do it. For this report, we do something like this:
$date = (date('Y-m-d'));
$filename = $stInput.'_ship-to_'.$date;
We use the account number as the first part, along with what the report is, _ship-to_, and the date.
Same as we did in the view version, we change our check_submit value to 3, as that’s what the js file is setting it to for our ship-to reports. We gut the existing SQL query out and replace it with the corresponding query from our view version, changing our variables along the way – as we did previously, we need an $stInput variable derived from our $_POST['stInput'] value.
The heart of recycling an existing report is, of course, making the structure right. We count out the number of columns we’re returning, which is 15. Fortunately, the report we started with had an equal number of columns, so we don’t need to adjust there, which is often the most prone to mistakes. We need to then simply change the column titles, until they look like this:
$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Slsm');
$objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Account');
$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Name');
$objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Dept');
$objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Dept Description');
$objPHPExcel->getActiveSheet()->SetCellValue('F1', 'Suite');
$objPHPExcel->getActiveSheet()->SetCellValue('G1', 'Street');
$objPHPExcel->getActiveSheet()->SetCellValue('H1', 'City');
$objPHPExcel->getActiveSheet()->SetCellValue('I1', 'St');
$objPHPExcel->getActiveSheet()->SetCellValue('J1', 'ZIP');
$objPHPExcel->getActiveSheet()->SetCellValue('K1', 'District');
$objPHPExcel->getActiveSheet()->SetCellValue('L1', 'Tax');
$objPHPExcel->getActiveSheet()->SetCellValue('M1', 'Rate');
$objPHPExcel->getActiveSheet()->SetCellValue('N1', 'Inv Fmt');
$objPHPExcel->getActiveSheet()->SetCellValue('O1', 'Last Order');
Below that, we rename the Excel sheet itself, $objPHPExcel->getActiveSheet()->setTitle(trim($stInput).' - Ship-to');.
Now we’re to the part that gets the trickiest, populating the data columns themselves. We’ll trim the values first, as leading spaces can cause issues in Excel spreadsheets. We’ll also set the datatype as explicit, to avoid Excel dropping any leading zeroes that might be in our data. What we end up with looks like so:
$i = 2;
while ($row = sqlsrv_fetch_array($getReport, SQLSRV_FETCH_ASSOC)) {
$objPHPExcel->getActiveSheet()->getCell('A' . $i)->setValueExplicit(trim($row['UserId']), $type);
$objPHPExcel->getActiveSheet()->getCell('B' . $i)->setValueExplicit(trim($row['Account']), $type);
$objPHPExcel->getActiveSheet()->getCell('C' . $i)->setValueExplicit(trim($row['Name']), $type);
$objPHPExcel->getActiveSheet()->getCell('D' . $i)->setValueExplicit(trim($row['Department']), $type);
$objPHPExcel->getActiveSheet()->getCell('E' . $i)->setValueExplicit(trim($row['DepartmentDescription']), $type);
$objPHPExcel->getActiveSheet()->getCell('F' . $i)->setValueExplicit(trim($row['Address1']), $type);
$objPHPExcel->getActiveSheet()->getCell('G' . $i)->setValueExplicit(trim($row['Address2']), $type);
$objPHPExcel->getActiveSheet()->getCell('H' . $i)->setValueExplicit(trim($row['City']), $type);
$objPHPExcel->getActiveSheet()->getCell('I' . $i)->setValueExplicit(trim($row['State']), $type);
$objPHPExcel->getActiveSheet()->getCell('J' . $i)->setValueExplicit(trim($row['Zip']), $type);
$objPHPExcel->getActiveSheet()->getCell('K' . $i)->setValueExplicit(trim($row['Region']), $type);
$objPHPExcel->getActiveSheet()->getCell('L' . $i)->setValueExplicit(trim($row['Taxable']), $type);
$objPHPExcel->getActiveSheet()->getCell('M' . $i)->setValueExplicit(trim($row['TaxDistrict']), $type);
$objPHPExcel->getActiveSheet()->getCell('N' . $i)->setValueExplicit(trim($row['InvoiceFormat']), $type);
$objPHPExcel->getActiveSheet()->getCell('O' . $i)->setValueExplicit(trim($row['LastDate']), $type);
$i++;
}
That done, we come below to setting our column widths. We start with something like so:
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setAutoSize(true);
// set certain columns to fixed width (AutoSize not close enough in this case)
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(7);
Really, at this point we’re ready to test. I have no idea how wide my columns need to be, so we’ll kill two birds here and run this report.
First time up, we get this:
Click for full size
Not bad, really. Looks like the only problem really is column B being set to a width of 7. The rest look fine, so we’ll just set B to AutoWidth as well, and call it good.
So this is how it goes for the download versions of the PSyOPs reports. To be honest, it usually doesn’t go this smoothly. I may make a separate post later of debugging Excel reports, although it goes very similarly to debugging the view reports, once you wade through the cruft that spits out into a bad Excel report.
Recent Comments