Sending form data to excel only working with set variables. #3104

New Issue

kriptcs · 2024-04-04T08:59:46Z

kriptcs commented

2024-04-04 08:59:46 +00:00

I am right now trying to create a form where all of the data will be sent to an excel. I found this code snippet, and it did work as pasted, but as soon as I got my own data into it it stopped working with the errors:
"Possible unhandled promise rejection id: 2" (got even id 0 and 1 also)
TypeError: js.forEach is not a function (it is undefined)

Here is my code:

import { useForm, Controller } from "react-hook-form";
import XLSX from "xlsx";
import * as FileSystem from "expo-file-system";
import * as Sharing from "expo-sharing";
import {
  ImageBackground,
  StyleSheet,
  Text,
  View,
  Button,
  TextInput,
} from "react-native";

function FormScreen({ route, navigation }) {
  const { control, handleSubmit } = useForm();

  async function onSubmit(data) {
    console.log(data);
    // Work with the form dat

    var ws = XLSX.utils.json_to_sheet(data);
    var wb = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, "Cities");
    const wbout = XLSX.write(wb, {
      type: "base64",
      bookType: "xlsx",
    });
    const uri = FileSystem.cacheDirectory + "form.xlsx";

    console.log(`Writing to ${JSON.stringify(uri)} with text: ${wbout}`);
    await FileSystem.writeAsStringAsync(uri, wbout, {
      encoding: FileSystem.EncodingType.Base64,
    });

    await Sharing.shareAsync(uri, {
      mimeType:
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      dialogTitle: "MyWater data",
      UTI: "com.microsoft.excel.xlsx",
    });
  }

  return (
    <ImageBackground
      source={require("../assets/images/htmlbg.jpg")}
      style={styles.background}
    >
      <View style={styles.formContainerTitle}>
        <Text style={styles.formTitle}>Stay in touch!</Text>
      </View>

      <View style={styles.formContainer}>
        <Controller
          control={control}
          name="Name"
          render={({ field }) => (
            <TextInput
              {...field}
              onChangeText={field.onChange}
              style={styles.formInput}
              placeholder="Name"
              //Can add other TextInput props
            />
          )}
        />
        <Controller
          control={control}
          name="Company Name"
          render={({ field }) => (
            <TextInput
              {...field}
              onChangeText={field.onChange}
              style={styles.formInput}
              placeholder="Company Name"
              //Can add other TextInput props
            />
          )}
        />
        <Controller
          control={control}
          name="Email"
          render={({ field }) => (
            <TextInput
              {...field}
              onChangeText={field.onChange}
              style={styles.formInput}
              placeholder="Email"
              //Can add other TextInput props
            />
          )}
        />
        <Controller
          control={control}
          name="Phone Number"
          render={({ field }) => (
            <TextInput
              {...field}
              onChangeText={field.onChange}
              style={styles.formInput}
              placeholder="Phone Number"
              //Can add other TextInput props
            />
          )}
        />
        <Button
          style={styles.formButton}
          title="Submit"
          color="white"
          onPress={handleSubmit(onSubmit)}
        />
      </View>
    </ImageBackground>
  );
}
export default FormScreen;

You may notice that aside from the copy pasted text I also added "async" behind the function, that is because I would get 2 syntax errors from those 2 await functions and adding async there seemed to do the trick. I am not sure if that makes the problem worse.

I have tried using the data var provided in the snippet and everything works properly. As soon as I remove that and I use the data from the form it doesn't like. It doesn't cross the var ws line. It is seemingly getting stuck at that point.
In terms of my form it does function normally. The console log at the top of the function works (even though it logs the fiels in the wrong order, it's 2 - 3 - 1 - 4 instead of 1 - 2 - 3 - 4)

Thank you in advance for all the help

I am right now trying to create a form where all of the data will be sent to an excel. I found this code snippet, and it did work as pasted, but as soon as I got my own data into it it stopped working with the errors: "Possible unhandled promise rejection id: 2" (got even id 0 and 1 also) TypeError: js.forEach is not a function (it is undefined) Here is my code: ``` import { useForm, Controller } from "react-hook-form"; import XLSX from "xlsx"; import * as FileSystem from "expo-file-system"; import * as Sharing from "expo-sharing"; import { ImageBackground, StyleSheet, Text, View, Button, TextInput, } from "react-native"; function FormScreen({ route, navigation }) { const { control, handleSubmit } = useForm(); async function onSubmit(data) { console.log(data); // Work with the form dat var ws = XLSX.utils.json_to_sheet(data); var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Cities"); const wbout = XLSX.write(wb, { type: "base64", bookType: "xlsx", }); const uri = FileSystem.cacheDirectory + "form.xlsx"; console.log(`Writing to ${JSON.stringify(uri)} with text: ${wbout}`); await FileSystem.writeAsStringAsync(uri, wbout, { encoding: FileSystem.EncodingType.Base64, }); await Sharing.shareAsync(uri, { mimeType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", dialogTitle: "MyWater data", UTI: "com.microsoft.excel.xlsx", }); } return ( <ImageBackground source={require("../assets/images/htmlbg.jpg")} style={styles.background} > <View style={styles.formContainerTitle}> <Text style={styles.formTitle}>Stay in touch!</Text> </View> <View style={styles.formContainer}> <Controller control={control} name="Name" render={({ field }) => ( <TextInput {...field} onChangeText={field.onChange} style={styles.formInput} placeholder="Name" //Can add other TextInput props /> )} /> <Controller control={control} name="Company Name" render={({ field }) => ( <TextInput {...field} onChangeText={field.onChange} style={styles.formInput} placeholder="Company Name" //Can add other TextInput props /> )} /> <Controller control={control} name="Email" render={({ field }) => ( <TextInput {...field} onChangeText={field.onChange} style={styles.formInput} placeholder="Email" //Can add other TextInput props /> )} /> <Controller control={control} name="Phone Number" render={({ field }) => ( <TextInput {...field} onChangeText={field.onChange} style={styles.formInput} placeholder="Phone Number" //Can add other TextInput props /> )} /> <Button style={styles.formButton} title="Submit" color="white" onPress={handleSubmit(onSubmit)} /> </View> </ImageBackground> ); } export default FormScreen; ``` You may notice that aside from the copy pasted text I also added "async" behind the function, that is because I would get 2 syntax errors from those 2 await functions and adding async there seemed to do the trick. I am not sure if that makes the problem worse. I have tried using the data var provided in the snippet and everything works properly. As soon as I remove that and I use the data from the form it doesn't like. It doesn't cross the var ws line. It is seemingly getting stuck at that point. In terms of my form it does function normally. The console log at the top of the function works (even though it logs the fiels in the wrong order, it's 2 - 3 - 1 - 4 instead of 1 - 2 - 3 - 4) Thank you in advance for all the help

sheetjs commented

2024-04-04 09:05:53 +00:00

Are you sure that data is an array of objects? After the console line, before json_to_sheet, test if data is an array:

import { Alert } from 'react-native';

// ...

  async function onSubmit(data) {
    if(!Array.isArray(data)) return Alert.alert('Bad data', 'data is not an array');

    var ws = XLSX.utils.json_to_sheet(data);
    // ...

Are you sure that `data` is an array of objects? After the console line, before `json_to_sheet`, test if `data` is an array: ```js import { Alert } from 'react-native'; // ... async function onSubmit(data) { if(!Array.isArray(data)) return Alert.alert('Bad data', 'data is not an array'); var ws = XLSX.utils.json_to_sheet(data); // ... ```

kriptcs commented

2024-04-06 12:58:16 +00:00

It is in

Are you sure that data is an array of objects? After the console line, before json_to_sheet, test if data is an array:

import { Alert } from 'react-native';

// ...

  async function onSubmit(data) {
    if(!Array.isArray(data)) return Alert.alert('Bad data', 'data is not an array');

    var ws = XLSX.utils.json_to_sheet(data);
    // ...

it is indeed not an array of object. I thought they're the same type because typeof() returned the same values.
Anyway I ran
var newArrayDataOfOjbect = Object.entries(data); and now it seems to work. I just have a small little issue if you can also help me regarding that. Now, with this line, whenever I create a form the first 2 entries I get are 0 and 1. Do you happen to know why that would be?

I am also looking to create 1 form in the beginning and keep adding data to it. I am thinking if introducing a boolean before var wb that if there is already a form to not execute the command. Would that mean though that the data would be going to the same form? As of right now just doing this just gives me the same errors as befor

Thank you for your time and help.

It is in > Are you sure that `data` is an array of objects? After the console line, before `json_to_sheet`, test if `data` is an array: > > ```js > import { Alert } from 'react-native'; > > // ... > > async function onSubmit(data) { > if(!Array.isArray(data)) return Alert.alert('Bad data', 'data is not an array'); > > var ws = XLSX.utils.json_to_sheet(data); > // ... > ``` it is indeed not an array of object. I thought they're the same type because typeof() returned the same values. Anyway I ran ` var newArrayDataOfOjbect = Object.entries(data); ` and now it seems to work. I just have a small little issue if you can also help me regarding that. Now, with this line, whenever I create a form the first 2 entries I get are 0 and 1. Do you happen to know why that would be? I am also looking to create 1 form in the beginning and keep adding data to it. I am thinking if introducing a boolean before `var wb` that if there is already a form to not execute the command. Would that mean though that the data would be going to the same form? As of right now just doing this just gives me the same errors as befor Thank you for your time and help.

Screenshot 2024-04-06 at 14.54.48.png

19 KiB

sheetjs commented

2024-04-06 17:12:47 +00:00

Object.entries returns an array of arrays.

Consider a data object like

var data = {
  Name: "My Name",
  Email: "my@email.com"
};

Then Object.entries(data) will look like

[
    [ "Name",  "My Name" ],
    [ "Email", "my@email.com" ]
]

Each row object has keys "0" and "1" (corresponding to the two columns), so json_to_sheet generates the worksheet you see.

If you are happy with the shape but don't want that "0" or "1", you can use aoa_to_sheet:

var aoa = Object.entries(data);
var ws = XLSX.utils.aoa_to_sheet(aoa);

If you want a sheet with two rows and one column per field, like

[
  [ "Name",    "Email"],
  [ "My Name", "my@email.com"]
]

You can pass an array containing only the data object:

var ws = XLSX.utils.json_to_sheet([data]);

If data happens to be a FormData object (you can test with data instanceof FormData), you can iterate and construct a plain object:

var ws = XLSX.utils.json_to_sheet([...data.entries()]);

If you want to append data to an existing worksheet, use sheet_add_json or sheet_add_aoa

`Object.entries` returns an array of arrays. Consider a data object like ```js var data = { Name: "My Name", Email: "my@email.com" }; ``` Then `Object.entries(data)` will look like ```js [ [ "Name", "My Name" ], [ "Email", "my@email.com" ] ] ``` Each row object has keys "0" and "1" (corresponding to the two columns), so `json_to_sheet` generates the worksheet you see. If you are happy with the shape but don't want that "0" or "1", you can use `aoa_to_sheet`: ```js var aoa = Object.entries(data); var ws = XLSX.utils.aoa_to_sheet(aoa); ``` --- If you want a sheet with two rows and one column per field, like ```js [ [ "Name", "Email"], [ "My Name", "my@email.com"] ] ``` You can pass an array containing only the data object: ```js var ws = XLSX.utils.json_to_sheet([data]); ``` If `data` happens to be a `FormData` object (you can test with `data instanceof FormData`), you can iterate and construct a plain object: ```js var ws = XLSX.utils.json_to_sheet([...data.entries()]); ``` --- If you want to append data to an existing worksheet, use [`sheet_add_json` or `sheet_add_aoa`](https://docs.sheetjs.com/docs/api/utilities/array)

sheetjs closed this issue

2024-04-06 17:12:48 +00:00

Sign in to join this conversation.