As stated above, you cannot run a client side app (vbscript) with a server side app (php). An alternative approach is to use a server-side scripting language like Python or Node.js, which have libraries and modules specifically designed for working with Excel files. If I undertsand this correctly, your excel file will reside on the server, no hosting company will allow you to install office apps on their server.
I have done a small client/server example for you that will work as expected, you will however need to add a lot of security checks and validations in place for obvious reasons (do not use ChatGPT code as you did in your question) as it will never return teh expected results as you have experienced just now, you will probably need to read up more on the code as well regarding Javascript, Node and proper PHP executions -
Firstly, you will need to use a library that can interact with Excel files in Node which is node-xlsx, so install it. More on node-xlsx -
Learn | node-xlsx
[
^] -
npm install node-xlsx
Create a new JavaScript file which will run the script, I have called mine 'transposeExcel.js'. This file needs to be accessible by PHP so make sure your file structure allows this with proper named paths -
PHP dirname[
^]
const fs = require('fs');
const path = require('path');
const XLSX = require('node-xlsx');
const folderPath = 'C:\\xampp\\htdocs\\HTML\\SSProcess\\ExcelFiles';
const outputFolderPath = 'C:\\xampp\\htdocs\\HTML\\SSProcess\\Extracted_FullSheet';
fs.readdir(folderPath, (err, files) => {
if (err) {
console.error('Error reading directory:', err);
return;
}
files.forEach(file => {
const filePath = path.join(folderPath, file);
const fileExtension = path.extname(file).toLowerCase();
if (fileExtension === '.xlsx') {
const workbook = XLSX.readFile(filePath);
const sheetNames = workbook.SheetNames;
let maxNumber = -1;
let sheetName = '';
sheetNames.forEach(name => {
const positionOfR = name.indexOf('R');
if (positionOfR > 0) {
const numericPart = name.substring(positionOfR + 1);
if (isNumeric(numericPart)) {
const currentNumber = parseInt(numericPart, 10);
if (currentNumber > maxNumber) {
maxNumber = currentNumber;
sheetName = name;
}
}
}
});
if (sheetName !== '') {
const worksheet = workbook.Sheets[sheetName];
const range = XLSX.utils.decode_range(worksheet['!ref']);
const limitedRange = {
s: { r: range.s.r, c: 0 },
e: { r: range.e.r, c: 11 }
};
const transposedWorksheet = XLSX.utils.aoa_to_sheet([]);
for (let row = limitedRange.s.r; row <= limitedRange.e.r; row++) {
for (let col = limitedRange.s.c; col <= limitedRange.e.c; col++) {
const cell = worksheet[XLSX.utils.encode_cell({ r: row, c: col })];
const mergeCells = XLSX.utils.decode_range(cell.r);
if (mergeCells) {
const mergedCell = worksheet[XLSX.utils.encode_cell(mergeCells.s)];
XLSX.utils.sheet_add_aoa(transposedWorksheet, [[mergedCell.v]], { origin: { r: col, c: row } });
} else {
XLSX.utils.sheet_add_aoa(transposedWorksheet, [[cell.v]], { origin: { r: col, c: row } });
}
}
}
XLSX.utils.book_append_sheet(workbook, transposedWorksheet, 'Transposed');
const outputFileName = path.join(outputFolderPath, file);
XLSX.writeFile(workbook, outputFileName);
} else {
console.log(`No valid data range found in the file: ${file}`);
}
}
});
});
function isNumeric(value) {
return !isNaN(value) && isFinite(value);
}
To run the Node.js script in your PHP page, you'll need to execute the Node.js script using the 'exec()' function in PHP -
PHP exec() function[
^]
Security is key here... -
<?php
$nodePath = '/usr/bin/node';
$scriptPath = '/path/to/transposeExcel.js';
$nodePath = filter_var($nodePath, FILTER_SANITIZE_STRING);
$scriptPath = filter_var($scriptPath, FILTER_SANITIZE_STRING);
if (empty($nodePath) || empty($scriptPath)) {
echo 'Invalid paths provided.';
exit;
}
if (!file_exists($nodePath) || !is_executable($nodePath)) {
echo 'Node.js executable not found or not executable.';
exit;
}
if (!file_exists($scriptPath) || !is_readable($scriptPath)) {
echo 'Node.js script not found or not readable.';
exit;
}
$escapedScriptPath = escapeshellarg($scriptPath);
$command = $nodePath . ' ' . $escapedScriptPath;
$output = [];
$returnValue = 0;
exec($command, $output, $returnValue);
if ($returnValue !== 0) {
echo 'Error executing Node.js script. Return value: ' . $returnValue . PHP_EOL;
echo 'Output: ' . implode(PHP_EOL, $output);
} else {
echo 'Node.js script executed successfully.';
}
I have not tested the code so you might get some minor errors to sort out but this will put you on the correct path.