我需要做的是使用PHP将txt文件上传到databe中，根据该表进行一些计算，然后在Excel文件中返回结果。
这段代码运行得很好，但我并没有从.txt文件中获取所有数据，因为当它找到逗号时，它看起来会停止。
这是代码：
require_once('connection.php.inc');
require_once 'Classes/PHPExcel/IOFactory.php';
require_once 'Classes/PHPExcel.php';
function HeaderingExcel($filename) {
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$filename" );
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
header("Pragma: public");
}
//-------upload---------------------------------------
error_reporting(E_ALL);
/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . './Classes/');
if (!file_exists($_FILES['excel_fajl']['tmp_name'])) {
exit("Nije dobar fajl!");
}
$file = $_FILES['excel_fajl']['tmp_name'];
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array( 'memoryCacheSize' => '128MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod,$cacheSettings);
$inputFileType = PHPExcel_IOFactory::identify($file);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($file);
$objPHPExcel-> setActiveSheetIndex(0);
$sheet = $objPHPExcel->getActiveSheet();
//kreiranje privremene tabele koju punimo:
$query1="create temp table visa_tc
(sequence char(170))
with no log";
odbc_exec($conn,$query1);
$i=1;
foreach ($sheet->getRowIterator() as $row) {
$sequence = trim($sheet->getCellByColumnAndRow(0, $row->getRowIndex())->getValue());
$query1 = "insert into visa_tc values ('".$sequence."')";
odbc_exec($conn,$query1);
$i++;
unset($sequence);
}
//--------kraj upload-a-------------------------------------
$query = "
SELECT sequence[17,20] Reason_code, sequence[24,27] Event_date, sequence[47,58]/100 dest_amount, sequence[59,61] destination_currency,
sequence[62,73]/100 source_amount, sequence[74,76] source_currency, sequence[77,146] message_text,
round(sequence[62,73]/sequence[47,58], 5) eur_usd, round(sequence[47,58]/sequence[62,73], 5) usd_eur
FROM visa_tc
WHERE sequence[17,20] <> ''";
$odbc_result = odbc_exec($conn,$query);
$excel = PHPExcel_IOFactory::createReader('Excel2007');
$excel = $excel->load('./TC_Template.xlsx'); // Empty Sheet
$excel->setActiveSheetIndex(0);
$i=3;
while (odbc_fetch_row($odbc_result))
{
$excel->getActiveSheet(0)->getCellByColumnAndRow(1, $i)->setValueExplicit(iconv('ISO-8859-2', 'utf-8//TRANSLIT',odbc_result($odbc_result,1)), PHPExcel_Cell_DataType::TYPE_STRING);
$excel->getActiveSheet(0)->getCellByColumnAndRow(2, $i)->setValueExplicit(iconv('ISO-8859-2', 'utf-8//TRANSLIT',odbc_result($odbc_result,2)), PHPExcel_Cell_DataType::TYPE_STRING);
$excel->getActiveSheet(0)->getCellByColumnAndRow(3, $i)->setValue(odbc_result($odbc_result,3));;
$excel->getActiveSheet(0)->getCellByColumnAndRow(4, $i)->setValueExplicit(iconv('ISO-8859-2', 'utf-8//TRANSLIT',odbc_result($odbc_result,4)), PHPExcel_Cell_DataType::TYPE_STRING);
$excel->getActiveSheet(0)->getCellByColumnAndRow(5, $i)->setValue(odbc_result($odbc_result,5));
$excel->getActiveSheet(0)->getCellByColumnAndRow(6, $i)->setValueExplicit(iconv('ISO-8859-2', 'utf-8//TRANSLIT',odbc_result($odbc_result,6)), PHPExcel_Cell_DataType::TYPE_STRING);
$excel->getActiveSheet(0)->getCellByColumnAndRow(7, $i)->setValue(odbc_result($odbc_result,7));
$excel->getActiveSheet(0)->getCellByColumnAndRow(8, $i)->setValue(odbc_result($odbc_result,8));
$excel->getActiveSheet(0)->getCellByColumnAndRow(9, $i)->setValue(odbc_result($odbc_result,9));
$i++;
}
$k =$i+1;
$excel->getActiveSheet(0)->getCellByColumnAndRow(2, $k)->setValue('TOTAL:');
$excel->getActiveSheet(0)->SetCellValue('D'.$k,"=SUM(D3:D".($k-1).")");
$excel->getActiveSheet(0)->SetCellValue('F'.$k,"=SUM(F3:F".($k-1).")");
$filename = 'TC_VISA.xlsx';
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$objWriter->save('php://output');
exit;
odbc_close($conn);
?>
这是必须上传到表visa_tc的.txt文件的内容
10004083524334965010 06150000000000000000000000000000343978000000000415840VGBP BILLING FOR MAY 2021, INV 210500-13239- 0004, 408357 0611166479225087 11660
10004083524334965010 06150000000000000000000000000001845978000000002231840VGBP BILLING FOR MAY 2021, INV 210500-13239- 0005, 408358 0611166479225088 11660
10004083524334965010 06150000000000000000000000000003581978000000004330840VGBP BILLING FOR MAY 2021, INV 210500-13239- 0012, 1000659089421357 0611166479225095 11660
10004083524334965010 06150000000000000000000000000010337978000000012500840VGBP BILLING FOR JUN 2021, INV 210601-07684- 0001, 10064776499 0611166479232817 11660
10004083524334965010 06150000000000000000000000000014208978000000017180840VGBP BILLING FOR MAY 2021, INV 210500-13239- 0006, 421357 0611166479225089 11660
10004083524334965010 06150000000000000000000000000022773978000000027537840VGBP BILLING FOR MAY 2021, INV 210500-13239- 0008, 1000324233408353 0611166479225091 11660
10004083524334965010 06150000000000000000000000000041522978000000050208840VGBP BILLING FOR MAY 2021, INV 210500-13239- 0002, 408352 0611166479225085 11660
10004083524334965010 06150000000000000000000000000138358978000000167303840VGBP BILLING FOR MAY 2021, INV 210500-13239- 0009, 1000328311408358 0611166479225092 11660
10004083524334965010 06150000000000000000000000000153530978000000185649840VGBP BILLING FOR MAY 2021, INV 210500-13239- 0011, 1000506582408357 0611166479225094 11660
10004083524334965010 06150000000000000000000000000434600978000000525518840VGBP BILLING FOR MAY 2021, INV 210500-13239- 0007, 1000324232408352 0611166479225090 11660
10004083524334965010 06150000000000000000000000000814400978000000984773840VGBP BILLING FOR MAY 2021, INV 210500-13239- 0013, 9000366491421357 0611166479225096 11660
我从sql查询中获得的这列"sequence[77146]"的输出基于以下条目的示例：
10004083524334965010 06150000000000000000000000000014208978000000017180840VGBP BILLING FOR MAY 2021, INV 210500-13239- 0006, 421357 0611166479225089 11660
我应该得到的是：
VGBP BILLING FOR MAY 2021, INV 210500-13239- 0006, 421357
但我得到的是：
VGBP BILLING FOR MAY 2021
当找到逗号时，序列似乎停止了。
我在代码中尝试了这种补救措施(使用preg_replace(
foreach ($sheet->getRowIterator() as $row) {
$sequence = trim($sheet->getCellByColumnAndRow(0, $row->getRowIndex())->getValue());
$query1 = "insert into visa_tc values ('".preg_replace('/[ ,]+/',' ',$sequence)."')";
odbc_exec($conn,$query1);
$i++;
unset($sequence);
}
and
$i=1;
foreach ($sheet->getRowIterator() as $row) {
$sequence = trim($sheet->getCellByColumnAndRow(0, $row->getRowIndex())->getValue());
$sequence1 = preg_replace('/[,]+/', ' ', $sequence);
$query1 = "insert into visa_tc values ('".$sequence1."')";
odbc_exec($conn,$query1);
$i++;
unset($sequence);
}
现在我希望我的问题更简单。
谢谢。
我能够制定一个解决方案：
exit("Nije dobar fajl!");
}
$file = $_FILES['excel_fajl']['tmp_name'];
$handle = fopen($file, "r");
//kreiranje privremene tabele koju punimo:
$query1="create temp table visa_tc
(sequence char(170))
with no log";
odbc_exec($conn,$query1);
while (($buffer = fgets($handle, 4096)) !== false) {
$query1 = "insert into visa_tc values ('".$buffer."')";
odbc_exec($conn,$query1);
}
所以基本上，与原始代码相比，唯一的区别是我添加了以下内容：
$handle = fopen($file, "r");
while (($buffer = fgets($handle, 4096)) !== false) {
$query1 = "insert into visa_tc values ('".$buffer."')";
odbc_exec($conn,$query1);
}
对我来说似乎不是一个很大的要求。
谢谢！