PHP 脚本：将 JSON 和 CSV 文件转换为 Excel 并插入数据库

//u003c?php//n//n// //u00a0导入PHPExcel库//nrequire/_once 'PHPExcel/PHPExcel.php';//n//n// JSON转Excel函数//nfunction jsonToExcel(/$jsonFile, /$excelFile) {//n//t// 读取JSON文件内容//n/t/$jsonContent = file/_get/_contents(/$jsonFile);//n/t/$jsonData = json/_decode(/$jsonContent, true);//n/t//n/t// 创建Excel对象//n/t/$excel = new PHPExcel();//n/t/$sheet = /$excel->getActiveSheet();//n/t//n/t// 写入表头//n/t/$header = array/_keys(/$jsonData[0]);//n/t/$col = 'A';//n/tforeach (/$header as /$item) {//n/t/t/$sheet->setCellValue(/$col . '1', /$item);//n/t/t/$col++;//n/t}//n/t//n/t// 写入数据//n/t/$row = 2;//n/tforeach (/$jsonData as /$data) {//n/t/t/$col = 'A';//n/t/tforeach (/$data as /$item) {//n/t/t/t/$sheet->setCellValue(/$col . /$row, /$item);//n/t/t/t/$col++;//n/t/t}//n/t/t/$row++;//n/t}//n/t//n/t// 保存Excel文件//n/t/$writer = PHPExcel/_IOFactory::createWriter(/$excel, 'Excel2007');//n/t/$writer->save(/$excelFile);//n}//n//n// CSV转Excel函数//nfunction csvToExcel(/$csvFile, /$excelFile) {//n/t// 读取CSV文件内容//n/t/$csvData = file/_get/_contents(/$csvFile);//n/t/$csvData = explode(/'//n/', /$csvData);//n/t//n/t// 创建Excel对象//n/t/$excel = new PHPExcel();//n/t/$sheet = /$excel->getActiveSheet();//n/t//n/t// 写入数据//n/t/$row = 1;//n/tforeach (/$csvData as /$csvRow) {//n/t/t/$csvRow = str/_getcsv(/$csvRow);//n/t/t/$col = 'A';//n/t/tforeach (/$csvRow as /$item) {//n/t/t/t/$sheet->setCellValue(/$col . /$row, /$item);//n/t/t/t/$col++;//n/t/t}//n/t/t/$row++;//n/t}//n/t//n/t// 保存Excel文件//n/t/$writer = PHPExcel/_IOFactory::createWriter(/$excel, 'Excel2007');//n/t/$writer->save(/$excelFile);//n}//n//n// 插入Excel到数据库函数//nfunction insertExcelToDatabase(/$excelFile) {//n/t// 连接数据库//n/t/$dbHost = 'localhost';//n/t/$dbName = 'your/_database/_name';//n/t/$dbUser = 'your/_username';//n/t/$dbPass = 'your/_password';//n/t//n/t/$conn = new PDO(/'mysql:host=/$dbHost;dbname=/$dbName/', /$dbUser, /$dbPass);//n/t//n/t// 读取Excel文件内容//n/t/$excel = PHPExcel/_IOFactory::load(/$excelFile);//n/t/$sheet = /$excel->getActiveSheet();//n/t//n/t// 获取最大行数和最大列数//n/t/$maxRow = /$sheet->getHighestRow();//n/t/$maxCol = /$sheet->getHighestColumn();//n/t//n/t// 读取数据并插入数据库//n/tfor (/$row = 2; /$row <= /$maxRow; /$row++) {//n/t/t/$rowData = array();//n/t/tfor (/$col = 'A'; /$col <= /$maxCol; /$col++) {//n/t/t/t/$cellValue = /$sheet->getCell(/$col . /$row)->getValue();//n/t/t/t/$rowData[] = /$cellValue;//n/t/t}//n/t/t//n/t/t// 插入数据到数据库中，这里需要根据你的数据库结构进行修改//n/t/t/$stmt = /$conn->prepare(/'INSERT INTO your/_table/_name (col1, col2, col3) VALUES (?, ?, ?)/');//n/t/t/$stmt->execute(/$rowData);//n/t}//n/t//n/t// 关闭数据库连接//n/t/$conn = null;//n}//n//n// 调用示例//n/$jsonFile = 'example.json';//n/$csvFile = 'example.csv';//n/$excelFile = 'example.xlsx';//n//n// 将JSON文件转换为Excel文件//njsonToExcel(/$jsonFile, /$excelFile);//n//n// 将CSV文件转换为Excel文件//ncsvToExcel(/$csvFile, /$excelFile);//n//n// 将Excel文件插入数据库//ninsertExcelToDatabase(/$excelFile);//n//n?>