Vue3+el-table realizes row and column conversion

Row-Column Conversion
Analyze the composition of the transcript
Transcript made with vue3 + el-table
Front-end simulation data
Generate transcripts using el-table

Determine the header
Determine the data
Calculate the total and average scores of students
Calculate ranking
Calculate the average score for each subject.
Record the highest and lowest scores for each subject
Add sorting function
Increase color distinction

Row-Column Conversion

Why does the problem of row and column conversion occur? Because what the user wants to see is a large form with multiple columns, but the database only stores "single column" data. So a conversion needs to be made so that customers can see it more conveniently and clearly.

There is a way to write SQL that can support this kind of row-column conversion, but it is complicated to write and difficult to understand.
So I personally prefer to implement the conversion operation on the front end, because it can save performance resources on the back end.

Here we take the transcript as an example to demonstrate the specific implementation method.

Analyze the composition of the transcript

This love-hate thing must be familiar to everyone. This is a typical scenario that requires row and column conversion.
Let's review what the transcript looks like: (Pictures from the Internet, please delete if infringed)

Transcript

After analysis, we can get the following basic elements:

Subjects: Chinese, mathematics, physics, chemistry, etc. Determine the number of columns
Student: Determine the number of rows
Grades and classes: Class 1, 1, Class 2, etc. Classification basis
Examinations: midterm exams, final exams, etc. Classification basis
Results: Determine the data content.

Classification of elements:

Classification basis: refers to the query conditions for generating a large form, in which data of the same category are aggregated into a large form.
Subject: Determine the number of columns. The more subjects there are, the more columns there will be.
Students: Determine the number of rows. The more students there are, the more rows there will be.

Let's take a look at the design in the database. Generally, several basic tables and a score table will be designed.

Subject table
Student Table
Class Schedule
Exam Schedule
Results

Due to limited space, I will not introduce the specific fields. It would be quite complicated to introduce them in full.

Transcript made with vue3 + el-table

Transcript after row and column conversion

Subjects, students, total scores, average scores, highest scores, lowest scores, and rankings are all available. Various sorting options are also possible. Let's see how this is accomplished.

Front-end simulation data

Let's simulate the data on the front end. (Simplified mode)

// Subject - determine the column const subject = [
  { id: 1, name: 'Mathematics' },
  { id: 2, name: 'Chinese' },
  { id: 3, name: 'Physics' },
  { id: 4, name: 'Chemistry' }
]

// Student - determine the row const student = [
  { id: 1, name: '张三' },
  { id: 2, name: 'Li Si' },
  { id: 3, name: '王五' },
  { id: 4, name: 'Zhao Liu' }
]

// Classes - Classification based on const classes = [
  { id: 1, name: 'Class 1, Year 1' },
  { id: 2, name: 'Class 2, Grade 1' }
]

//Exam - classification basis const exam = [
  { id: 1, name: 'Midterm Exam' },
  { id: 2, name: 'Final Exam' }
]

// Score - determine the content const reportCard = [
  //Serial number exam ID class ID student ID subject ID score { id: 1, examId: 1, classId: 1, studentId: 1, subjectId: 1, score: 100 },
  { id: 2, examId: 1, classId: 1, studentId: 1, subjectId: 2, score: 98 },
  { id: 3, examId: 1, classId: 1, studentId: 1, subjectId: 3, score: 90 },
  { id: 4, examId: 1, classId: 1, studentId: 2, subjectId: 1, score: 90 },
  { id: 5, examId: 1, classId: 1, studentId: 2, subjectId: 2, score: 90 },
  { id: 6, examId: 1, classId: 1, studentId: 2, subjectId: 3, score: 40 },
  { id: 7, examId: 1, classId: 1, studentId: 3, subjectId: 1, score: 30 },
  { id: 8, examId: 1, classId: 1, studentId: 3, subjectId: 2, score: 90 },
  { id: 8, examId: 1, classId: 1, studentId: 3, subjectId: 3, score: 40 },
  { id: 9, examId: 1, classId: 1, studentId: 4, subjectId: 1, score: 64 },
  { id: 8, examId: 1, classId: 1, studentId: 4, subjectId: 2, score: 90 },
  { id: 9, examId: 1, classId: 1, studentId: 4, subjectId: 3, score: 70 }
]

Generate transcripts using el-table

Element-plus provides a very powerful table component - el-table, which can realize many basic functions, such as sorting, adjusting width, setting color, filtering and other functions. Then we can use this component to implement the transcript.

Determine the header

A major feature of row-column conversion is that the table header (how many columns there are) is not fixed but needs to be generated dynamically.
Taking the transcript as an example, the number of columns in the table header is determined by the subject. The more subjects there are, the more headers there are.

First, we set the table header according to the requirements of el-table:

/**
 * Create a table header based on the subject* * Student ID, name, [subjects], total score, average score, ranking*/
const createTableHead = () => {
  // Add students const head = [
  {
    prop: 'id',
    label: 'student number',
    width: 120
  },
  {
    prop: 'name',
    label: 'Name',
    width: 120
  }]

  // Add subject for (const key in subject) {
    const sub = subject[key]
    head.push({
      prop: 'sub_' + sub.id,
      label: sub.name,
      width: 120
    })
  }

  head.push({
    prop: 'totalScore',
    label: 'Total score',
    width: 120
  })
  head.push({
    prop: 'averageScore',
    label: 'average score',
    width: 120
  })
  head.push({
    prop: 'ranking',
    label: 'Rank',
    width: 120
  })
  
  return head
}

There are two types of headers here, one is fixed and the other is dynamically generated based on the subject.
We adopt a simple and crude approach, first directly adding fixed columns, and then traversing the subjects to add dynamic columns.

Determine the data

After the table header is determined, we need to determine the data part and officially start the row and column conversion.
Let's define the data format according to the needs of el-table:

{
  id: 1,
  name: 'Zhang San',
  sub_1: 100,
  sub_2: 100,
  ...
  totalScore: 200,
  averageScore: 100,
  ranking: 1
}

There can be various subjects in the middle, and the attribute naming rule is: prefix "sub_" + subject ID.
This makes it easier to add data later.

Traverse the grades table to fill in the data.

/**
 * Row and column conversion */
const rowToCol = () => {
  // Object-based score list const _code = {}
  // List of scores in array form const _arr = []

  // Traverse the report card for (const key in reportCard) {
    const rep = reportCard[key]
    if(typeof _code[rep.studentId] === 'undefined') {
      // No records. Create a row of student scores and add fixed column data_code[rep.studentId] = {
        id: rep.studentId, // student ID
        name: (student.filter((item)=>item.id === rep.studentId)[0] || {name:''}).name, // Get the student name based on id totalScore: 0, // The total score of the student in each subject, which will be modified later averageScore: 0, // The average score of the student in each subject, which will be modified later ranking: 1 // The ranking, which will be modified later }
    }
    // Record the scores of each subject_code[rep.studentId]['sub_' + rep.subjectId] = rep.score
  }

  // Calculate the total score and average score as described below // Calculate the ranking as described below return _arr
}

Traverse the transcript data, first create an object based on the student ID, and then determine the grades of each subject based on the subject.

Calculate the total and average scores of students

Generally, the transcript also needs to get the student's total score and average score. We can traverse the grades to calculate the total score and average score.

  ...
  // Calculate total score and average score for(const key in _code) {
    const code = _code[key]
    // Traverse the subjects let total = 0
    let ave = 0
    let count = 0
    for (const key in subject) {
      const fenshu = code['sub_' + subject[key].id]
      if (typeof fenshu !== 'undefined') {
        // There are scores, calculate the total score and average score count++
        total += fenshu
        ave = Math.floor(total / count * 10) / 10 // Keep one decimal place.
        // If you want to keep two decimal places, you can do this (total / count).toFixed(2)
      }
    }
    code.totalScore = total
    code.averageScore = ave
    //Convert object data to array data_arr.push(code)
  }

Calculate ranking

Now that we have the transcript, we still need to make a ranking.
If you don't need ranking, you can skip this step.

  let _ranking = 0
  _arr.sort((a, b) => {
    // return b.totalScore - a.totalScore in reverse order of total score
  }).forEach((item) => {
    // Set ranking _ranking++
    _arr.find((a) => a.id === item.id).ranking = _ranking
  })

Calculate the average score for each subject.

This is also a common requirement.
el-table provides an automatic summation function, as well as a custom summation method, which we can use to achieve the average score of the subject.

The following is the method given by the official website. It is originally for summation, but with a slight modification you can get the average score of the subject.

 // Calculate the average score of each subject let i = 0
const getSummaries = (param) => {
  i++
  if (i < 7) return []

  const { columns, data } = param;
  const sums = [];
  
  columns.forEach((column, index) => {
    if (index === 0) {
      sums[index] = 'Average score'
      return
    }
    const values = data.map(item => Number(item[column.property]));
    if (!values.every(value => isNaN(value))) {
      sums[index] = values.reduce((prev, curr) => {
        const value = Number(curr)
        if (!isNaN(value)) {
          return prev + curr
        } else {
          return prev
        }
      }, 0);
      sums[index] = Math.floor(sums[index] / values.length * 10) / 10
    } else {
      sums[index] = 'N/A';
    }
  })
  
  return sums

After tracing it, I found that this function will be called seven times, and this number seems to have nothing to do with the number of rows and columns.
Moreover, the first few returns have no effect, only the last one will be effective, so a judgment is added.

Record the highest and lowest scores for each subject

A transcript can also calculate various data, such as the highest and lowest scores in a subject.
Generally, you can also pay attention to these statistical data. Similarly, we can use a custom summation method to meet the needs. Let's improve getSummaries.

// Calculate the average score, highest score and lowest score for each subject const getSummaries = ({ columns }) => {
  i++
  if (i < 7) return []

  const sums = [];
   
  columns.forEach((item, index) => {
    if (item.property.indexOf('sub_') >= 0 ){
      const subjectId = item.property.replace('sub_', '')*1
      // Subject, calculate the average let ave = 0
      let sum = 0
      let max = 0
      let min = 99999
      const _arr = reportCard.filter((r) => r.subjectId === subjectId)
      _arr.forEach((item, index) => {
        sum += item.score // sum if (max < item.score) max = item.score // record the highest score if (min > item.score) min = item.score // record the lowest score })
      if (_arr.length === 0) {
        sums[index] = '-' // No score} else {
        // Calculate the average score ave = Math.floor(sum/_arr.length * 10) / 10
        sums[index] = `${ave}(${max}-${min})`
      }
    } else {
      // Do not calculate sums[index] = ''
    }
  })
  sums[0] = 'Statistics'
  return sums

}

This time we directly use reportCard to calculate the average score, the highest score, and the lowest score.
Similarly, we can also count the number of people who passed the test and the number of people in each segment.

Add sorting function

This is a built-in function of el-table, we just need to add it.

<el-table
    :data="tableData"
    style="width: 100%;height: 300px;"
    :default-sort = "{prop: 'totalScore', order: 'descending'}"
    :row-class-name="tableRowClassName"
    border
    show-summary
    :summary-method="getSummaries"
  >
    <el-table-column
      v-for="(item, index) in tableHead"
      :key="'s'+ index"
      fixed
      sortable
      :prop="item.prop"
      :label="item.label"
      :width="item.width">
    </el-table-column>
  </el-table>

el-table property settings. default-sort By default, the ranking is displayed in descending order of total score.

Increase color distinction

What if you want to highlight those with average scores below 60? el-table also provides functions, we just need to make a judgment and set the css.

// Color const tableRowClassName = ({row, rowIndex}) => {
  if (row.averageScore < 60) { // Students whose average scores fail return 'warning-row';
  } else if (row.averageScore > 95) { // Students with average scores above 95 return 'success-row';
  }
  return '';
}

Source code
https://gitee.com/naturefw/nf-vite2-element

summary

advantage:

The backend does not need to perform row-column conversion, only basic data needs to be provided, saving backend performance resources.
The functions are quite comprehensive, and basically everything I can think of is included.

shortcoming:

It has not been made into a universal form, and other row-column conversion requirements still require code writing.

This is the end of this article about vue3+el-table to achieve row and column conversion. For more relevant vue3 row and column conversion content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in: