英文:
How to remove Undefined values in JAVASCRIPT from excel values (when it's blank, use blank rather than undefined)?
问题
I found this code that works fine but when there is a blank value in excel, it shows undefined, while i would like it to remain blank.
Any help would be great, however I just started programming a few days ago, so details on the syntax would be great (like what to remove out of the code and what to replace).
Thanks
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Excel to HTML Table | Javacodepoint</title>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.5/xlsx.min.js"></script>
</head>
<body>
<h1>Upload an excel file to display in HTML Table</h1>
<!-- Input element to upload an excel file -->
<input type="file" id="file_upload" />
<button onclick="upload()">Upload</button>
<br>
<br>
<!-- table to display the excel data -->
<table id="display_excel_data" border="1"></table>
<script>
// Method to upload a valid excel file
function upload() {
var files = document.getElementById('file_upload').files;
if(files.length==0){
alert("Please choose any file...");
return;
}
var filename = files[0].name;
var extension = filename.substring(filename.lastIndexOf(".")).toUpperCase();
if (extension == '.XLS' || extension == '.XLSX') {
//Here calling another method to read excel file into json
excelFileToJSON(files[0]);
}else{
alert("Please select a valid excel file.");
}
}
//Method to read excel file and convert it into JSON
function excelFileToJSON(file){
try {
var reader = new FileReader();
reader.readAsBinaryString(file);
reader.onload = function(e) {
var data = e.target.result;
var workbook = XLSX.read(data, {
type : 'binary'
});
var result = {};
var firstSheetName = workbook.SheetNames[0];
//reading only first sheet data
var jsonData = XLSX.utils.sheet_to_json(workbook.Sheets[firstSheetName]);
//displaying the json result into HTML table
displayJsonToHtmlTable(jsonData);
}
}catch(e){
console.error(e);
}
}
//Method to display the data in HTML Table
function displayJsonToHtmlTable(jsonData){
var table=document.getElementById("display_excel_data");
if(jsonData.length>0){
var htmlData='<tr><th>Student Name</th><th>Address</th><th>Email ID</th><th>Age</th></tr>';
for(var i=0;i<jsonData.length;i++){
var row=jsonData[i];
htmlData+='<tr><td>'+(row["hgyu"] || '')+'</td><td>'+(row["Address"] || '')+
'</td><td>'+(row["Email ID"] || '')+'</td><td>'+(row["Age"] || '')+'</td></tr>';
}
table.innerHTML=htmlData;
}else{
table.innerHTML='There is no data in Excel';
}
}
</script>
</body>
</html>
英文:
I found this code that works fine but when there is a blank value in excel, it shows undefined, while i would like it to remain blank.
Any help would be great, however I just started programming a few days ago, so details on the syntax would be great (like what to remove out of the code and what to replace).
Thanks
<!-- begin snippet: js hide: false console: true babel: false -->
<!-- language: lang-html -->
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Excel to HTML Table | Javacodepoint</title>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.5/xlsx.min.js"></script>
</head>
<body>
<h1>Upload an excel file to display in HTML Table</h1>
<!-- Input element to upload an excel file -->
<input type="file" id="file_upload" />
<button onclick="upload()">Upload</button>
<br>
<br>
<!-- table to display the excel data -->
<table id="display_excel_data" border="1"></table>
<script>
// Method to upload a valid excel file
function upload() {
var files = document.getElementById('file_upload').files;
if(files.length==0){
alert("Please choose any file...");
return;
}
var filename = files[0].name;
var extension = filename.substring(filename.lastIndexOf(".")).toUpperCase();
if (extension == '.XLS' || extension == '.XLSX') {
//Here calling another method to read excel file into json
excelFileToJSON(files[0]);
}else{
alert("Please select a valid excel file.");
}
}
//Method to read excel file and convert it into JSON
function excelFileToJSON(file){
try {
var reader = new FileReader();
reader.readAsBinaryString(file);
reader.onload = function(e) {
var data = e.target.result;
var workbook = XLSX.read(data, {
type : 'binary'
});
var result = {};
var firstSheetName = workbook.SheetNames[0];
//reading only first sheet data
var jsonData = XLSX.utils.sheet_to_json(workbook.Sheets[firstSheetName]);
//displaying the json result into HTML table
displayJsonToHtmlTable(jsonData);
}
}catch(e){
console.error(e);
}
}
//Method to display the data in HTML Table
function displayJsonToHtmlTable(jsonData){
var table=document.getElementById("display_excel_data");
if(jsonData.length>0){
var htmlData='<tr><th>Student Name</th><th>Address</th><th>Email ID</th><th>Age</th></tr>';
for(var i=0;i<jsonData.length;i++){
var row=jsonData[i];
htmlData+='<tr><td>'+row["hgyu"]+'</td><td>'+row["Address"]
+'</td><td>'+row["Email ID"]+'</td><td>'+row["Age"]+'</td></tr>';
}
table.innerHTML=htmlData;
}else{
table.innerHTML='There is no data in Excel';
}
}
</script>
</body>
</html>
<!-- end snippet -->
答案1
得分: 0
以下是您要翻译的内容：
"发生的情况是当您请求一些可能不存在于每一行中的数据时。当发生这种情况时，该数据将为undefined，因此它将转换为字符串并显示。绕过此问题的方法是执行row['Age'] || ''，它会将row['Age']视为某种布尔值，如果它解析为"假值"，如undefined，那么它将获取或语句的另一侧并返回一个空字符串，正如您所希望的那样。
所以这是需要更改的全部内容：
for(var i=0;i<jsonData.length;i++){
var row=jsonData[i];
var hgyu = row["hgyu"] || '';
var address = row["Address"] || '';
var email = row["Email ID"] || '';
var age = row["Age"] || '';
htmlData+='<tr><td>'+hgyu+'</td><td>'+address
+'</td><td>'+email+'</td><td>'+age+'</td></tr>';
}
以下是整个工作的示例。
// 上传有效的Excel文件的方法
function upload() {
var files = document.getElementById('file_upload').files;
if(files.length==0){
alert("请选择任何文件...");
return;
}
var filename = files[0].name;
var extension = filename.substring(filename.lastIndexOf(".")).toUpperCase();
if (extension == '.XLS' || extension == '.XLSX') {
//在这里调用另一个方法将Excel文件转换为JSON
excelFileToJSON(files[0]);
}else{
alert("请选择有效的Excel文件。");
}
}
//将Excel文件读取并转换为JSON的方法
function excelFileToJSON(file){
try {
var reader = new FileReader();
reader.readAsBinaryString(file);
reader.onload = function(e) {
var data = e.target.result;
var workbook = XLSX.read(data, {
type : 'binary'
});
var result = {};
var firstSheetName = workbook.SheetNames[0];
//仅读取第一个工作表的数据
var jsonData = XLSX.utils.sheet_to_json(workbook.Sheets[firstSheetName]);
//将JSON结果显示在HTML表格中
displayJsonToHtmlTable(jsonData);
}
}catch(e){
console.error(e);
}
}
//在HTML表格中显示数据的方法
function displayJsonToHtmlTable(jsonData){
var table=document.getElementById("display_excel_data");
if(jsonData.length>0){
var htmlData='';
for(var i=0;i<jsonData.length;i++){
var row=jsonData[i];
var hgyu = row["hgyu"] || '';
var address = row["Address"] || '';
var email = row["Email ID"] || '';
var age = row["Age"] || '';
htmlData+='
+'
';
}
table.innerHTML=htmlData;
}else{
table.innerHTML='Excel中没有数据';
}
}
上传Excel文件以在HTML表格中显示
编辑
以下示例演示如何为每个Excel行添加一个新表格，如在此答案的评论中讨论的。
// 上传有效的Excel文件的方法
function upload() {
var files = document.getElementById('file_upload').files;
if(files.length==0){
alert("请选择任何文件...");
return;
}
var filename = files[0].name;
var extension = filename.substring(filename.lastIndexOf(".")).toUpperCase();
if (extension == '.XLS' || extension == '.XLSX') {
//在这里调用另一个方法将Excel文件转换为JSON
excelFileToJSON(files[0]);
}else{
alert("请选择有效的Excel文件。");
}
}
//将Excel文件读取并转换为JSON的方法
function excelFileToJSON(file){
try {
var reader = new FileReader();
reader.readAsBinaryString(file);
reader.onload = function(e) {
var data = e.target.result;
var workbook = XLSX.read(data, {
type : 'binary'
});
var result = {};
var firstSheetName = workbook.SheetNames[0];
//仅读取第一个工作表的数据
var jsonData = XLSX.utils.sheet_to_json(workbook.Sheets[firstSheetName]);
//将JSON结果显示在HTML表格中
displayJsonToHtmlTable(jsonData);
}
}catch(e){
console.error(e);
}
}
//在HTML表格中显示数据的方法
function displayJsonToHtmlTable(jsonData){
var table=document.getElementById("display_excel_data");
if(jsonData.length>0){
var htmlData='';
for(var i=0;i<jsonData.length;i++){
var row=jsonData[i];
var hgyu = row["hgyu"] || '';
var address = row["Address"] || '';
var email = row["Email ID"] || '';
var age = row["Age"] || '';
htmlData+='<table border="1">';
htmlData+='<tr><th>学生姓名</th><th>地址</th><th>电子邮件</th><th>年龄</th></tr>';
htmlData+='<tr><td>'+hgyu+'</td>';
htmlData+='<td>'+address+'</td>';
htmlData+='<td>'+email+'</td>';
htmlData+='<td>'+age+'</td>';
htmlData+='</tr></table>';
}
table.innerHTML=htmlData;
}else{
table.innerHTML='Excel中没有数据';
}
}
上传Excel文件以在HTML表格中显示
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论