PDF sorted table images and excel image url

bee57

Im trying to show images of each data from a table to pdf. The image are shown but the problem is, when user sort the DataTable using the table headers in the user interface, the sorting is applied to the displayed rows, but images in the PDF export is not sorted or being updated to match the new order. Also, i tried making the images shown as url in excel but it doesnt work. How do i fix this? thanks!

    <table class="table table-hover" id="table-report" width="100%" style="white-space: nowrap;">
                  <thead align="center">
                    <th>table header</th>
                    <-- other table headers -->
                    <th>table header</th>
                  </thead>
                  <tbody align="center">
                    <?php
                    $year1 = $_POST['year1'];
                    $year2 = $_POST['year2'];
                    $data = query("SELECT * FROM report WHERE YEAR(date) BETWEEN '$year1' AND '$year2' ORDER BY date DESC");
                    $imageSources = array();

                    foreach ($data as $d) :
                      $imagePath = '../uploads/' . $d['photo'];
                      $type = pathinfo($imagePath, PATHINFO_EXTENSION);
                      $imageData  = file_get_contents($imagePath);
                      $imgData = base64_encode($imageData );
                      $src = 'data:image/' . $type . ';base64,'.$imgData;
                      $imageSources[] = $src;
                    ?>
                          <tr>
                              <td><?= $d['atr']; ?></td>
                              <td><?= $d['atr']; ?></td>
                              <td><?= $d['atr']; ?></td>
                              <td><?= $d['atr']; ?></td>
                              <td><?= $d['atr']; ?></td>
                              <td><?= $d['atr']; ?></td>
                              <td><?= $d['atr']; ?></td>
                              <td>
                              <img src="<?= $imagePath; ?>" alt="Image" class="img-fluid" style="max-height: 100px; max-width: 100px;">
                              </td> 
                              <td><?= $d['atr']; ?></td>
                              <td><?= $d['atr']; ?></td>
                              <td><?= $d['atr']; ?></td>
                              <td><?= $d['atr']; ?></td>
                              <td><?= $d['atr'] ? $d['atr'] : 'text'; ?></td>
                              <td><?= $d['atr'] ? $d['atr'] : 'text'; ?></td>
                          </tr>
                      <?php
                      endforeach;
                      ?>
                  </tbody>
                </table>


    <?php
    include "templates/footer.php";
    <script>


    var imageSources = <?php echo json_encode($imageSources); ?>;
    var imagePath = <?php echo json_encode($imagePath); ?>;

    $('#table-report').DataTable({
        dom: '<"datatable-buttons"B>ltip',
        lengthMenu: [5, 10, 25, 50, 100],
        order: [[1, 'desc']],
        buttons: [
            'colvis', 
            {
                extend: 'excel',
                exportOptions: {
                    orthogonal: 'export',
                },
                customize: function(xlsx) {
                        var sheet = xlsx.xl.worksheets['sheet1.xml'];
                        $('row c[r^="H"]', sheet).each(function(index) {
                            var imageIndex = $(this).index() - 7; // Calculate the image index based on column position
                            var rowIndex = $(this).parent().index() - 2; // Adjust the row index based on header and Excel's 1-indexing
                            var imgData = imageSources[rowIndex] || ''; // Get the image URL
                            var url = imgData ? (imagePath + imgData) : ''; // Construct the URL using $imagePath and imgData
                            $(this).find('is t').text(url); // Set the URL as the cell value
                            $(this).attr('s', '2'); // Set style to display as a URL
                        });
                    }
            },
            {
                extend: 'pdf',
                orientation: 'landscape',
                pageSize: 'legal',
                exportOptions: {
                  columns: ':visible'
                },
                customize: function(doc) {
                  doc.defaultStyle.fontSize = 10;
                  doc.pageMargins = [40, 40, 40, 40];
                  var arr2 = imageSources.map(function(imageSource) {
                        return imageSource;
                    });

                  for (var i = 0, c = 1; i < arr2.length; i++, c++) {
                                    doc.content[1].table.body[c][7] = {
                                      image: arr2[i],
                                      width: 100
                                    }
                                      }
                },
              stripHtml: true
            },
          ],
        scrollX: true,
            // fixedHeader: true,
        });

    </script>

allan

I can't help with the Excel part I'm afraid, as I'm not familiar with how Excel's XML operates for image embedding.

For the pdf, I'm surprised from your code that the image order is not retained from the DataTable. Can you link to a test case showing the issue so I can look into it further please?

Thanks,
Allan