Operate the Excel file to realize the data filtering of the Excel table

Operate the Excel file to realize the data filtering of the Excel table

Requirements

There are multiple tabular data, which need to be divided by month to filter out the data of patients who have done chest and abdomen more than once in each month

Technology usage

• node-xlsx: Manipulate Excel files
• fs: used to read and write files
• moment: used to format the date type

Implementation ideas

1. Use fs.readdir to read the Excel file under the Excel folder, traverse the file, divide the month according to the file name, and integrate the data of the same month.
2. Traverse the data according to the month, perform data screening, first screen the data of chest and abdominal examinations, and then perform statistics on the patient’s chest and abdomen data according to the medical record number in these data, and traverse the objects to obtain the case numbers of more than one abdominal and chest examinations Array, and then filter according to the case number array and the data of the chest and abdomen examinations previously screened, and finally get the data of patients who have undergone chest and abdomen examinations more than once a month.
3. Create and write Excel files to the data of each month according to the month

source code:

// First, we need to install the node-xlsx package using npm
// Run the following command in your terminal:
// npm install node-xlsx
const fs = require("fs");
// Import the node-xlsx package
const xlsx = require('node-xlsx');
const moment = require('moment')

const sheetObj = {};
fs. readdir('./Excel', function(err, files) {
  if (err) {
    return console.log('Unable to scan directory: ' + err);
  }
  files. forEach(function(file) {
    let _arr = xlsx. parse(`./Excel/${file}`, {
      type: 'binary',
      cellDates: true
    })[0].data
    // Check whether the form fields are uniform, otherwise it will cause data confusion
    if (!compareArrays(_arr[0], [
        ' ', 'patient name', 'patient record number', 'gender',
        'Age', 'Check Number', 'Check Item', 'Check Part',
        'Examination Type', 'Application Department', 'Examination Status', 'Report Status',
        'Check time', 'Audit time', 'Report to doctor', 'Report time',
        'Audit doctor', 'outpatient hospital number', 'bed', 'appointment number',
        'Check Group', 'Check Equipment', 'Apply for Doctor', 'Register Doctor',
        'Registration time', 'Examination doctor', 'Record doctor', 'Record time',
        'review doctor', 'review time', 'examination fee', 'report attribute',
        'Pending status', 'Filming status', 'Distribution status', 'Standby number',
        'Date of Birth', 'Contact Number', 'Contact Address', 'ID Number',
        'Lock doctor', 'check-in time', 'application number', 'clinical diagnosis',
        'Ethnicity', 'Affiliated School District', 'Feibie', 'Report Modification',
        'Diagnostic conclusion', 'Inspection remarks', 'Film cost', 'Report remarks',
        'Appointment time', 'Registration notes', 'Report printing times', 'Segmentation',
        'Height (cm)', 'Weight (kg)', 'BMI', 'Follow-up clinical diagnosis',
        'Follow-up pathological diagnosis', 'Follow-up content', 'Follow-up meets', 'Visiting number',
        'Critical Classification', 'Referral Status', 'Assigned Reporting Physician'
      ])) {
      console.log('There are inconsistent table titles that need to be processed, otherwise the data is wrong', file);
    }
    if (!sheetObj[Number. parseInt(file)]) {
      sheetObj[Number. parseInt(file)] = {
        data: _arr
      }
    } else {
      _arr. shift()
      sheetObj[Number.parseInt(file)].data.push(_arr)
    }
  });

  for (let i in sheetObj) {
    const filteredData = filterData(sheetObj[i].data, {
      // 'Patient Name': "==='Adila Abraiti' & amp; & amp; ",
      'Check location': ".match(/abdomen|chest/)"
    });

    let xlsxObj = [{
      name: 'firstSheet',
      data: filteredData
    }]
    var buffer = xlsx. build(xlsxObj);
    //data input
    fs.writeFile(`./shaixuan/${i}.XLS`, buffer, function(err) {
      if (err) {
        throw err;
      }
      // output log
      console.log('Write to xls has finished');
    })
  }
})

// Compare two array values for equality
function compareArrays(arr1, arr2) {
  if (arr1. length !== arr2. length) {
    console.log('The length of the table title is not equal');
    return false;
  }
  for (let i = 0; i < arr1. length; i ++ ) {
    if (arr1[i] !== arr2[i]) {
      console.log(i, arr1[i]);
      return false;
    }
  }
  return true;
}
// Define a function to filter the data
function filterData(data, filter) {
  const_filter = {}
  for (let i in filter) {
    const index = data[0]. indexOf(i)
    if (index !== -1) {
      _filter[index] = filter[i]
    }
  }

  let conditionString = ''
  for (let i in _filter) {
    conditionString + = `data[i][${i}]${_filter[i]}`
  }
  console. log(conditionString);
  const _dataTitle = data. shift()
  // Create an empty array to store the filtered data
  const filteredData = [];
  // filteredData. push(data[0])
  // Loop through each row of the data
  // Filter out the patient data whose examination is abdomen or chest
  for (let i = 0; i < data. length; i ++ ) {
    // Check if the row matches the filter
    try {
      if (eval(conditionString)) {
        // If it does, add it to the filtered data array
        filteredData. push(data[i]);
      }
    } catch (error) {

    }

  }
  // Filter out the patient data with one abdomen and one chest and above
  _obj = {}
  filteredData.forEach((item) => {
    let re = item[7].match(/belly|chest/)[0]
    if (!_obj[item[2]]) {
      _obj[item[2]] = {
        'abdomen': 0,
        'chest': 0
      }
      _obj[item[2]][re] + +
    } else {
      _obj[item[2]][re] + +
    }
  })
  const xiongAndFuIds = []
  for (let i in _obj) {
    if (_obj[i]['abdomen'] > 0 & amp; & amp; _obj[i]['chest'] > 0) {
      xiongAndFuIds. push(i)
    }
  }

  let result = []
  result = filteredData. filter((item) => {
    return (xiongAndFuIds. indexOf(item[2] + '') !== -1)
  })
  // Return the filtered data
  result. unshift(_dataTitle)

  // Traverse the results to process the date data
  for (let i = 0; i < result. length; i ++ ) {
    for (let j = 0; j < result[i].length; j ++ ) {
      if (result[i][j] instanceof Date) {
        result[i][j] = moment(result[i][j]).format('YYYY-MM-DD HH:mm:ss')
      }
    }
  }
  return result;
}

// Call the filterData function with the sheet data and the filter value

Pits:

1. Date format problem

When parsing table data, add { type: ‘binary’, cellDates: true } configuration item
Traverse the data of date type in the final result data, and use the moment plug-in for date formatting

 let _arr = xlsx. parse(`./Excel/${file}`, {
    type: 'binary',
    cellDates: true
  })[0].data

// Traverse the results to process the date data
for (let i = 0; i < result. length; i ++ ) {
  for (let j = 0; j < result[i].length; j ++ ) {
    if (result[i][j] instanceof Date) {
      result[i][j] = moment(result[i][j]).format('YYYY-MM-DD HH:mm:ss')
    }
  }
}

2. When writing data

Need to build buffer type data and then use fs.writeFile to create Excel and write data

let xlsxObj = [{
  name: 'firstSheet',
  data: filteredData
}]
var buffer = xlsx. build(xlsxObj);
//data input
fs.writeFile(`./shaixuan/${i}.XLS`, buffer, function(err) {
  if (err) {
    throw err;
  }
  // output log
  console.log('Write to xls has finished');
})