Excel导入和导出

1
2
3
4
5
6
7
8
    <dependencies>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
        </dependency>
    </dependencies>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Dog {

//    index 表示这个属性是在excel的第几列（从0开始），value 表示在当前列的表头名称
    @ExcelProperty("狗编号")
    private Integer dogNo;
    @ExcelProperty("狗名称")
    private String dogName;
    @ExcelProperty("狗年龄")
    private Integer dogAge;


}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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) {
        //写入的路径
        String fileName = "D:Dog.xlsx";
        EasyExcel.write(fileName,Dog.class).sheet("狗").doWrite(getLists());
    }

    //模仿从后台拿到一个Dog对象的集合 (我生成了5个一样的dog狗)
    public static List<Dog> getLists(){
        return Stream.generate(()->new Dog(2,"阿花",28)).limit(5).collect(Collectors.toList());
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.Map;

public class EasyListener extends AnalysisEventListener<Dog> {

    //一行一行读取excel内容
    @Override
    public void invoke(Dog dog, AnalysisContext analysisContext) {
        System.out.println("数据"+dog);
    }

    //读取表头内容
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("表头:"+headMap);
    }

    //读取完之后
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("我读完了");
    }


}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
public class Client {
    public static void main(String[] args) {
        //写入的路径
        String fileName = "D:Dog.xlsx";
//        EasyExcel.write(fileName,Dog.class).sheet("狗").doWrite(getLists());

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

    //模仿从后台拿到一个Dog对象的集合 (我生成了5个一样的dog狗)
    public static List<Dog> getLists(){
        return Stream.generate(()->new Dog(2,"阿花",28)).limit(5).collect(Collectors.toList());
    }
}

1
2
npm install file-saver@1.3.8 xlsx@0.14.1 --save

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
/* 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){
    /*设置worksheet每列的最大宽度*/
    const colWidth = data.map(row => row.map(val => {
      /*先判断是否为null/undefined*/
      if (val == null) {
        return {'wch': 10};
      }
      /*再判断是否为中文*/
      else if (val.toString().charCodeAt(0) > 255) {
        return {'wch': val.toString().length * 2};
      } else {
        return {'wch': val.toString().length};
      }
    }))
    /*以第一行为初始值*/
    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");
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
/*
* @method 表格数据导出excel
* @param {object} option 参数配置
*   @params {array} tHeader 表头文字 默认 []
*   @params {array} filterVal 对应字段名 默认 []
*   @params {array} table 表格数据 默认 []
*   @params {string} fileName 导出excel文件名 默认 excel-file
* @param {function} 导出成功回调
* */

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.log(tHeader);
    const filterVal = option.filterVal || []
    const table = option.table || []
    const data = formatJson(filterVal, table)
    // const autoWidth = option.autoWidth || true //列宽是否自适应
    const autoWidth = option.autoWidth == undefined ? true : option.autoWidth //列宽是否自适应
    excel.export_json_to_excel({
      header: tHeader,
      data,
      filename: option.fileName || 'excel-file',
      autoWidth:autoWidth
    })
    cb && cb()
  })
}

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

1
2
3
  import { exportFile } from "@/utils/exportExcel"; //导出
  import moment from 'moment';

1
2
exportLoading:false

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
//导出
      exportData(){
        this.exportLoading = true
        let list = [
          {dogNo:2,dogName:"阿花",dogAge:28},
          {dogNo:3,dogName:"阿花1",dogAge:3},
          {dogNo:4,dogName:"阿花2",dogAge:5},
          {dogNo:5,dogName:"阿花3",dogAge:9},
        ]
        console.log(list)
        this.exportExcel(list)
      },
      exportExcel(table) {  //导出模板和参数列名
        const option = {
          tHeader: [
            "狗编号",
            "狗名称",
            "狗年龄",
          ],
          filterVal: [
            "dogNo",
            "dogName",
            "dogAge",
          ],
          table: table,
          fileName: "狗" + moment().format('YYYY-MM-DD HH_mm_ss')
        };
        exportFile(option, () => {
          this.exportLoading = false;
        });
      },

1
2
3
4
5
6
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.6</version>
        </dependency>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
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();   //做一个简单的查询

        // Excel标题
        String[] title = {"id", "课程id", "标题", "课本","时间"};
        // Excel文件名
        String fileName =  "测试.xls";
        // sheet名
        String sheetName = "测试1";
        // 将数据放到数组中
        String[][] content = new String[list.size()][title.length];
        for (int i = 0; i < list.size(); i++) {
            Date date = new Date();
            SimpleDateFormat dateFormat = new SimpleDateFormat("y-M-d 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;
        }
        // 导出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) {
        // 创建一个HSSFWorkbook，对应一个Excel文件
        if (workbook == null) {
            workbook = new HSSFWorkbook();
        }
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 在sheet中添加表头第0行
        HSSFRow row = sheet.createRow(0);
        // 创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 声明列对象
        HSSFCell cell = null;
        // 创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(cellStyle);
        }
        // 创建内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < values[i].length; j++) {
                // 将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(values[i][j]);
            }
        }
        return workbook;

    }


}