node读取excel

//生成sql‘


const mysqlx = require("@mysql/xdevapi");

let xlsx = require('node-xlsx');
let sheets = xlsx.parse('G:\\data.xlsx');

item_data();


async function item_data()
{
   sheets.forEach(function(sheet)
  {
    let data = sheet.data;

    //截取有效数字
    data = data.slice(0, 1);

    //去除undefined
    for (let i = data.length - 1; i >= 0; i--) {
      if (data[i][0] === undefined) {
        data.splice(i, 1);
      }
    }

    console.log(data.length);

    data.map(p =>
    {
      data = "'" + p.join("', '") + "'";
      let names = ['name', 'sex', 'age', 'complain', 'fever', 'temperate', 'cough', 'expectoration', 'pharyngalgia', 'dry_pharynx', 'myalgia', 'tired', 'weak', 'chest', 'short_of_breath', 'diarrhea', 'dyspnea', 'headache', 'diabetes', 'coronary', 'hypertesion', 'chronic_obstructive_pulmonary_disease', 'hepatopathy_history', 'nephroma_history', 'tuberculosis_history', 'disease_time', 'visit_time', 'time_space', 'diagnosis_time', 'wuhan_live_history', 'patient_community_live_history', 'wuhan_people_contach_history', 'patient_community_respiratory_symptoms', 'cluster_onset', 'virus_patient_contact_history', 'no_contact_history', 'incubation_period', 'double_lungs', 'one_lungs', 'lesions_location', 'ct_no_exception', 'pleural_effussion', 'real_change', 'spot_like_shadow', 'class_milling', 'shadow_of_infiltration', 'cable_shadow', 'checkout_date', 'WBC', 'RBC', 'HB_or_HGB', 'PLT', 'NEUT_OR_N', 'percentage_NEUT_OR_N', 'LY', 'percentage_LY', 'M_OR_MONO', 'PERCENTAGE_M_OR_MONO', 'E', 'PERCENTAGE_E', 'B', 'PERCENTAGE_B', 'CRP', 'ESR', 'PCT', 'CK_MB', 'oxygen_concentration', 'oxygen_number', 'PH', 'PaO2', 'SaO2', 'PCO2', 'Lac', 'GPT_OR_ALT', 'GOT_OR_AST', 'LDH', 'severity_of_disease', 'remark', 'photo_logging', 'mutiple'];

      str_name = names.join(",");

      const sql = `insert into dicom.pest_data2(${str_name}) values(${data}) ;`;
      console.log(sql);
    })
  })
};


async function insertData(data)
{
  data = "'" + data.join("', '") + "'";

  const session = await mysqlx.getSession
  ({
    user: 'root',
    password: '123456',
    host: 'localhost',
    port: '33060'
  });

  let names = ['name', 'sex', 'age', 'complain', 'fever', 'temperate', 'cough', 'expectoration', 'pharyngalgia', 'dry_pharynx', 'myalgia', 'tired', 'weak', 'chest', 'short_of_breath', 'diarrhea', 'dyspnea', 'headache', 'diabetes', 'coronary', 'hypertesion', 'chronic_obstructive_pulmonary_disease', 'hepatopathy_history', 'nephroma_history', 'tuberculosis_history', 'disease_time', 'visit_time', 'time_space', 'diagnosis_time', 'wuhan_live_history', 'patient_community_live_history', 'wuhan_people_contach_history', 'patient_community_respiratory_symptoms', 'cluster_onset', 'virus_patient_contact_history', 'no_contact_history', 'incubation_period', 'double_lungs', 'one_lungs', 'lesions_location', 'ct_no_exception', 'pleural_effussion', 'real_change', 'spot_like_shadow', 'class_milling', 'shadow_of_infiltration', 'cable_shadow', 'checkout_date', 'WBC', 'RBC', 'HB_or_HGB', 'PLT', 'NEUT_OR_N', 'percentage_NEUT_OR_N', 'LY', 'percentage_LY', 'M_OR_MONO', 'PERCENTAGE_M_OR_MONO', 'E', 'PERCENTAGE_E', 'B', 'PERCENTAGE_B', 'CRP', 'ESR', 'PCT', 'CK_MB', 'oxygen_concentration', 'oxygen_number', 'PH', 'PaO2', 'SaO2', 'PCO2', 'Lac', 'GPT_OR_ALT', 'GOT_OR_AST', 'LDH', 'severity_of_disease', 'remark', 'photo_logging', 'mutiple'];

  str_name = names.join(",");

  const sql = `insert into dicom.pest_data2(${str_name}) values(${data})`;

  console.log(sql);

  await session.sql(sql).execute(r => console.log(r)).catch(e=>console.log(e));

  session.close();

  return 1;
}