Excel import and export

First introduce dependencies in the pom file

    < dependencies > 
        < dependency > 
            < groupId > com.alibaba </ groupId > 
            < artifactId > easyexcel </ artifactId > 
            < version > 3.0.5 </ version > 
        </ dependency > 
    </ dependencies >

The background code realizes exporting the excel file to the specified directory (export)

First create a dog class

import  com . alibaba . excel . annotation . ExcelProperty ; 
import  lombok . AllArgsConstructor ; 
import  lombok . Data ; 
import  lombok . NoArgsConstructor ;

@Data 
@AllArgsConstructor 
@NoArgsConstructor 
public  class  Dog  {

// index indicates which column of excel this property is in (starting from 0), value indicates the header name in the current column 
    @ExcelProperty ( "dog number" ) 
    private  Integer dogNo ; 
    @ExcelProperty ( "dog name" ) 
    private  String dogName ; 
    @ExcelProperty ( "dog age" ) 
    private  Integer dogAge ;


}

then test class

import  com . alibaba . excel . EasyExcel ; 
import  java . util . List ; 
import  java . util . stream . Collectors ; 
import  java . util . stream . Stream ;

public  class  Client  { 
    public  static  void  main ( String [ ] args )  { 
        //The path to write 
        String fileName =  "D:\\Dog.xlsx" ; 
        EasyExcel . write ( fileName , Dog . class ) . sheet ( "Dog" ) .doWrite ( getLists ( ) ) ; } _
    

    //Imitate getting a collection of Dog objects from the background (I generated 5 identical dogs) 
    public  static  List < Dog >  getLists ( ) { 
        return  Stream . generate ( ( ) -> new  Dog ( 2 , "A Hua " , 28 ) ) . limit ( 5 ) . collect ( Collectors . toList ( ) ) ; 
    } 
}

After running, you will see a Dog.xlsx file in the path, the effect is as follows:
insert image description here

The background code realizes reading the excel file of the specified directory (import)

Create the EasyListener class

import  com . alibaba . excel . context . AnalysisContext ; 
import  com . alibaba . excel . event . AnalysisEventListener ; 
import  java . util . Map ;

public  class  EasyListener  extends  AnalysisEventListener < Dog >  {

    //Read excel content line by line 
    @Override 
    public  void  invoke ( Dog dog ,  AnalysisContext analysisContext )  { 
        System . out . println ( "data" + dog ) ; 
    }

    //Read the header content 
    @Override 
    public  void  invokeHeadMap ( Map < Integer ,  String > headMap ,  AnalysisContext context )  { 
        System . out . println ( "Header:" + headMap ) ; 
    }

    //After reading 
    @Override 
    public  void  doAfterAllAnalysed ( AnalysisContext analysisContext )  { 
        System . out . println ( "I finished reading" ) ; 
    }


}

Test class code:


public  class  Client  { 
    public  static  void  main ( String [ ] args )  { 
        //Writing path 
        String fileName =  "D:\\Dog.xlsx" ; 
// EasyExcel.write(fileName,Dog.class).sheet(" dog").doWrite(getLists());

        EasyExcel . read ( fileName , Dog . class , new  EasyListener ( ) ) . sheet ( "dog" ) . doRead ( ) ; 
    }

    //Imitate getting a collection of Dog objects from the background (I generated 5 identical dogs) 
    public  static  List < Dog >  getLists ( ) { 
        return  Stream . generate ( ( ) -> new  Dog ( 2 , "A Hua " , 28 ) ) . limit ( 5 ) . collect ( Collectors . toList ( ) ) ; 
    } 
}

The output is as follows:
insert image description here

Front-end vue implements exporting excel (export) in the browser

npm download the two dependent files file-saver and xlsx

npm install [email protected] [email protected] --save

The export js file code is stored here
insert image description here

The Export2Excel.js code is sorted out, just copy the past

/* eslint-disable */ 
import  { saveAs }  from  'file-saver' 
import  XLSX  from  'xlsx'

function  generateArray ( table )  { 
  var out =  [ ] ; 
  var rows = table . querySelectorAll ( 'tr' ) ; 
  var ranges =  [ ] ; 
  for  ( var  R  =  0 ;  R  < rows . length ;  ++ R )  { 
    var outRow =  [ ] ; 
    var row =rows [ R ] ; 
    var columns = row . querySelectorAll ( 'td' ) ; 
    for  ( var  C  =  0 ;  C  < columns . length ;  ++ C )  { 
      var cell = columns [ C ] ; 
      var colspan = cell . getAttribute ( 'colspan' ) ; 
      var rowspan =cell . getAttribute ( 'rowspan' ) ; 
      cell . setAttribute ( 'mso-number-format' , '\@' ) ; //mso-number-format:'\@'; 
      var cellValue = cell . innerText ; 
      if  ( cellValue !==  ""  && cellValue ==  + cellValue ) cellValue =  + cellValue ;

      //Skip ranges 
      ranges . forEach ( function  ( range )  { 
        if  ( R  >= range . s . r &&  R  <= range . e . r && outRow . length >= range . s . c && outRow . length <= range . e . c )  { 
          for  ( var i = 0 ; i <= range . e . c - range . s . c ;  ++ i ) outRow . push ( null ) ; 
        } 
      } ) ;

      //Handle Row Span 
      if  ( rowspan || colspan )  { 
        rowspan = rowspan ||  1 ; 
        colspan = colspan ||  1 ; 
        ranges . push ( { s :  { r :  R ,  c : outRow . length } ,  e :  { r :  R  + rowspan -  1 ,  c :outRow . length + colspan -  1 } } ) ; 
      } 
      ;

      //Handle Value 
      outRow . push ( cellValue !==  ""  ? cellValue :  null ) ;

      //Handle Colspan 
      if  ( colspan )  for  ( var k =  0 ; k < colspan -  1 ;  ++ k ) outRow . push ( null ) ; 
    } 
    out . push ( outRow ) ; 
  } 
  return  [ out , ranges ] ; 
} ;

function  datenum ( v , date1904 )  { 
  if  ( date1904 ) v +=  1462 ; 
  var epoch = Date . parse ( v ) ; 
  return  ( epoch -  new  Date ( Date . UTC ( 1899 ,  11 ,  30 ) ) )  /  ( 24  *  60  *  60  *  1000 ); 
}

function  sheet_from_array_of_arrays ( data , opts )  { 
  var ws =  { } ; 
  var range =  { s :  { c :  10000000 ,  r :  10000000 } ,  e :  { c :  0 ,  r :  0 } } ; 
  for  ( var  R  =  0 ;  R  != data . length;  ++ R )  { 
    for  ( var  C  =  0 ;  C  != data [ R ] . length ;  ++ C )  { 
      if  ( range . s . r >  R ) range . s . r =  R ; 
      if  ( range . s . c >  C ) range . s . c=  C ; 
      if  ( range . e . r <  R ) range . e . r =  R ; 
      if  ( range . e . c <  C ) range . e . c =  C ; 
      var cell =  { v : data [ R ] [ C ] } ; 
      if  ( cell .v ==  null )  continue ; 
      var cell_ref =  XLSX . utils . encode_cell ( { c :  C ,  r :  R } ) ;

      if  ( typeof cell . v ===  'number' ) cell . t =  'n' ; 
      else  if  ( typeof cell . v ===  'boolean' ) cell . t =  'b' ; 
      else  if  ( cell . v instanceof  Date )  { 
        cell . t =  'n' ; 
        cell . z =  XLSX .SSF . _table [ 14 ] ; 
        cell . v =  datenum ( cell . v ) ; 
      } 
      else cell . t =  's' ;

      ws [ cell_ref ]  = cell ; 
    } 
  } 
  if  ( range . s . c <  10000000 ) ws [ '!ref' ]  =  XLSX . utils . encode_range ( range ) ; 
  return ws ; 
}

function  Workbook ( )  { 
  if  ( ! ( this  instanceof  Workbook ) )  return  new  Workbook ( ) ; 
  this . SheetNames =  [ ] ; 
  this . Sheets =  { } ; 
}

function  s2ab ( s )  { 
  var buf =  new  ArrayBuffer ( s . length ) ; 
  var view =  new  Uint8Array ( buf ) ; 
  for  ( var i =  0 ; i != s . length ;  ++ i ) view [ i ]  = s .charCodeAt ( i ) & 0xFF _  ; 
  return buf ; 
}

export  function  export_table_to_excel ( id )  { 
  var theTable = document . getElementById ( id ) ; 
  var oo =  generateArray ( theTable ) ; 
  var ranges = oo [ 1 ] ;

  /* original data */ 
  var data = oo [ 0 ] ; 
  var ws_name =  "SheetJS" ;

  var wb =  new  Workbook ( ) , ws =  sheet_from_array_of_arrays ( data ) ;

  /* add ranges to worksheet */ 
  // ws['!cols'] = ['apple', 'banan']; 
  ws [ '!merges' ]  = ranges ;

  /* add worksheet to workbook */ 
  wb . SheetNames . push ( ws_name ) ; 
  wb . Sheets [ ws_name ]  = ws ;

  var wbout =  XLSX . write ( wb ,  { bookType :  'xlsx' ,  bookSST :  false ,  type :  'binary' } ) ;

  saveAs ( new  Blob ( [ s2ab ( wbout ) ] ,  { type :  "application/octet-stream" } ) ,  "test.xlsx" ) 
}

export  function  export_json_to_excel ( { header , data , filename = 'excel-list' , autoWidth = true } = { } )  { 
  /* original data */ 
  data = [ ... data ] 
  data . unshift ( header ) ; 
  var ws_name =  "SheetJS" ; 
  var wb =  new  Workbook ( ) , ws=  sheet_from_array_of_arrays ( data ) ;

  if ( autoWidth ) { 
    /*Set the maximum width of each column of the worksheet*/ 
    const colWidth = data . map ( row  => row . map ( val  =>  { 
      /* First determine whether it is null/undefined*/ 
      if  ( val ==  null )  { 
        return  { 'wch' :  10 } ; 
      } 
      /*Check if it is Chinese*/ 
      else  if  ( val . toString ( ) . charCodeAt ( 0)  >  255 )  { 
        return  { 'wch' : val . toString ( ) . length *  2 } ; 
      }  else  { 
        return  { 'wch' : val . toString ( ) . length } ; 
      } 
    } ) ) 
    /* Take the first line initial value */ 
    let result = colWidth [ 0 ] ; 
    for  ( let i =  1; i < colWidth . length ; i ++ )  { 
      for  ( let j =  0 ; j < colWidth [ i ] . length ; j ++ )  { 
        if  ( result [ j ] [ 'wch' ]  < colWidth [ i ] [ j ] [ 'wch' ] )  { 
          result [j ] [ 'wch' ]  = colWidth [ i ] [ j ] [ 'wch' ] ; 
        } 
      } 
    } 
    ws [ '!cols' ]  = result ; 
  }

  /* add worksheet to workbook */ 
  wb . SheetNames . push ( ws_name ) ; 
  wb . Sheets [ ws_name ]  = ws ;

  var wbout =  XLSX . write ( wb ,  { bookType :  'xlsx' ,  bookSST :  false ,  type :  'binary' } ) ; 
  saveAs ( new  Blob ( [ s2ab ( wbout ) ] ,  { type :  "application/octet-stream" } ) , filename +  ".xlsx" ) ; 
}

Build a public export js under utils, and the code is sorted
insert image description here

/*
* @method table data export to excel
* @param {object} option parameter configuration
* @params {array} tHeader header text default []
* @params {array} filterVal corresponding field name default []
* @params {array} table table data default[]
* @params {string} fileName export excel file name default excel-file
* @param {function} export success callback
* */

const  formatJson  =  ( filterVal , jsonData )  =>  { 
  return jsonData . map ( v  => filterVal . map ( j  => v [ j ] ) ) 
}

export  const  exportFile  =  ( option =  { } , cb )  =>  { 
  import ( '@/vendor/Export2Excel' ) . then ( excel  =>  { 
    const tHeader = option . tHeader ||  [ ]  // title 
    console corresponding to table output .log ( tHeader ) ; const filterVal = option . filterVal || [ ] _
     
    const table = option . table ||  [ ] 
    const data =  formatJson ( filterVal , table ) 
    // const autoWidth = option. autoWidth || true //Whether the column width is adaptive 
    const autoWidth = option . autoWidth ==  undefined  ?  true  : option .autoWidth // Whether the column width is adaptive to 
    excel . export_json_to_excel ( { 
      header : tHeader , 
      data ,
      filename : option . fileName ||  'excel-file' , 
      autoWidth : autoWidth
     } ) 
    cb &&  cb ( ) 
  } ) 
}

Then add the export button on the vue page

< el-button  size = " mini "  icon = " el-icon-download "  type = " primary "  @click = " exportData "  :loading = " exportLoading " > export </ el-button >

introduce

  import  { exportFile }  from  "@/utils/exportExcel" ;  //Export 
  import moment from  'moment' ;

add in data

exportLoading : false

in methods

//Export 
      exportData ( ) { 
        this . exportLoading =  true 
        let list =  [ 
          { dogNo : 2 , dogName : "Hua" , dogAge : 28 } , 
          { dogNo : 3 , dogName : "Hua 1" , dogAge : 3 } , 
          { dogNo : 4 , dogName : "Ahua 2" , dogAge : 5} , 
          { dogNo : 5 , dogName : "Ahua 3" , dogAge : 9 } , 
        ] 
        console . log ( list ) 
        this . exportExcel ( list ) 
      } , 
      exportExcel ( table )  {   //Export template and parameter column names 
        const option =  { 
          tHeader :  [ 
            "dog number" , 
            "dog name" , 
            "dog age" , 
          ] , 
          filterVal:  [ 
            "dogNo" , 
            "dogName" , 
            "dogAge" , 
          ] , 
          table : table , 
          fileName :  "dog"  +  moment ( ) . format ( 'YYYY-MM-DD HH_mm_ss' ) 
        } ; 
        exportFile ( option ,  ( )  = >  { 
          this . exportLoading =  false ; 
        } ) ; 
      } ,

Browser effect:
insert image description here

The back-end code implements the export of excel in the browser (export)

Create a new springboot project and use mybatisplus to set up the framework first.
then add dependencies

        < dependency > 
            < groupId > org.apache.poi < / groupId > < artifactId > poi < / artifactId > < version > 3.6 < / version > < / dependency > _ _ _ _

Here I write the core code on the controller, and the rest are simple constructions of spring boot + mybatisPlus

package  com.cjw.cjwExport.contoller ; _ _ _ _ _ _

import  com.cjw.cjwExport.model.Student ; import com.cjw.cjwExport.service.ExportService ; import org.apache.poi.hssf.usermodel . * ; import org.springframework.beans.factory.annotation.Autowired ; import org _ _ _ _ _ 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ . 
 
 
 springframework.web.bind.annotation.GetMapping ; import org.springframework.web.bind.annotation.RequestMapping ; import org.springframework.web.bind.annotation.RestController ; _ _ _ _ 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
 

import  javax . servlet . http . HttpServletResponse ; 
import  java . io . OutputStream ; 
import  java . text . SimpleDateFormat ; 
import  java . util . Date ; 
import  java . util . List ;

@RestController 
@RequestMapping ( "/Export" ) 
public  class  ExportController  {

    @Autowired 
    private  ExportService exportService ;

    @RequestMapping ( "/test01" ) 
    @GetMapping 
    public  void  exportTest01 ( HttpServletResponse response ) { 
        exportService . exportTest01 ( ) ; 
        System . out . println ( "=====export====" ) ;

        List < Student > list = exportService . list ( ) ;    // do a simple query

        // Excel title 
        String [ ] title =  { "id" ,  "course id" ,  "title" ,  "textbook" , "time" } ; 
        // Excel file name 
        String fileName =   "test.xls" ; 
        // sheet name 
        String sheetName =  "Test 1" ; 
        // put data into array 
        String [ ] [ ] content =  new  String [ list . size ( ) ] [ title .length] ; 
        for  ( int i =  0 ; i < list . size ( ) ; i ++ )  { 
            Date date =  new  Date ( ) ; 
            SimpleDateFormat dateFormat =  new  SimpleDateFormat ( "yMd H:m:s" ) ; 
            String format = dateFormat .format ( date ) ; Student monthReportModel = _
            list . get ( i ) ; 
            content [ i ] [ 0 ]  = monthReportModel . getId ( ) ; 
            content [ i ] [ 1 ]  = monthReportModel . getCourseId ( ) ; 
            content [ i ] [ 2 ]  = monthReportModel . getTitle ( ) ; 
            content[ i ] [ 3 ]  = monthReportModel . getSort ( ) . toString ( ) ; 
            content [ i ] [ 4 ]  = format ; 
        } 
        // export to Excel 
        try  { 
            HSSFWorkbook hssfWorkbook =  getHSSFWorkbook ( sheetName , title , content ,  null ) ; 
            fileName =  new  String (fileName . getBytes ( ) ,  "ISO8859-1" ) ; 
            response . setContentType ( "application/octet-stream;charset=ISO8859-1" ) ; 
            response . setHeader ( "Content-Disposition" ,  "attachment;filename="  + fileName ) ; 
            response . addHeader ( "Pargam" ,  "no-cache" ) ; 
            response . addHeader ( "Cache-Control", "no-cache" ) ;

            OutputStream outputStream = response . getOutputStream ( ) ; 
            hssfWorkbook . write ( outputStream ) ; 
            outputStream . flush ( ) ; 
            outputStream . close ( ) ; 
        }  catch  ( Exception e )  { 
            e . printStackTrace ( ) ; 
        } 
    }



    public  HSSFWorkbook  getHSSFWorkbook ( String sheetName ,  String [ ] title ,  String [ ] [ ] values ,  HSSFWorkbook workbook )  { 
        // Create a HSSFWorkbook, corresponding to an Excel file 
        if  ( workbook ==  null )  { 
            workbook =  new  HSSFWorkbook ( ) ; 
        } 
        / / Add a sheet to the workbook, corresponding to the sheet 
        HSSFSheet sheet = workbook in the Excel file .createSheet ( sheetName ) ; 
        // Add header row 0 to the sheet 
        HSSFRow row = sheet . createRow ( 0 ) ; 
        // Create a cell and set the value header to center the header 
        HSSFCellStyle cellStyle = workbook . createCellStyle ( ) ; 
        cellStyle . setAlignment ( HSSFCellStyle . ALIGN_CENTER ) ; 
        // declare column object 
        HSSFCell cell =  null ; 
        // create title 
        for  ( int i=  0 ; i < title . length ; i ++ )  { 
            cell = row . createCell ( i ) ; 
            cell . setCellValue ( title [ i ] ) ; 
            cell . setCellStyle ( cellStyle ) ; 
        } 
        // create content 
        for  ( int i =  0 ; i < values .length ; i ++ )  { 
            row = sheet . createRow ( i +  1 ) ; 
            for  ( int j =  0 ; j < values [ i ] . length ; j ++ )  { 
                // assign contents to corresponding columns in order object 
                row.createCell ( j ) .setCellValue ( values [ i ] [ j _ _ _] ) ; 
            } 
        } 
        return workbook ;

    }


}

Enter the address test in the browser: the
insert image description here
result:

Navigation

The background code realizes exporting the excel file to the specified directory (export)

The background code realizes reading the excel file of the specified directory (import)

Front-end vue implements exporting excel (export) in the browser

The back-end code implements the export of excel in the browser (export)

Tags: Excel import and export

Related: Excel import and export