# grails_excel_import

**Repository Path**: GntLee/grails_excel_import

## Basic Information

- **Project Name**: grails_excel_import
- **Description**: grails导入excel数据
- **Primary Language**: Groovy
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 1
- **Forks**: 0
- **Created**: 2017-08-05
- **Last Updated**: 2021-04-10

## Categories & Tags

**Categories**: excel-utils

**Tags**: None

## README

# grails导入Excel表格中的数据

引入依赖：
    
```groovy
compile 'org.grails.plugins:excel-import:3.0.0.RC4'
```
    
注入serivce

```groovy
def excelImportService
```
    
创建导入方法：
    
```groovy

def excelImportService

    /**
     *  从指定行startRow开始读取，startRow从0开始算
     *  不指定lastRow，导入从指定行开始到最后一行的数据
     */
    def excelImport = {
        InputStream inputStream = null
        try {
            //获取文件，此处可通过上传获取文件
            File file = new File("c:\\file\\用户信息.xlsx")
            //定义表头
            Map CONFIG_BOOK_COLUMN_MAP = [
                    sheet:'Sheet1',
                    startRow: 1,
                    columnMap:  [
                            //Col, Map-Key
                            'A':'name',
                            'B':'age',
                            'C':'sex',
                            'D':'addr'
                    ]
            ]
            //将文件放到输入流中
            inputStream = new FileInputStream(file)
            Workbook workbook = WorkbookFactory.create(inputStream)

            //解析Excel的行存入list
            List list = excelImportService.columns(workbook,CONFIG_BOOK_COLUMN_MAP)
            list.each { v->
                User user = new User()
                user.name = v.name
                user.age = v.age as Integer
                user.sex = v.sex
                user.addr = v.addr
                println "用户信息："+user
                user.save(flush:true)
                println (user as JSON)
            }
            render ([msg:"Excel导入成功"]) as JSON
        } catch (Exception e) {
            e.printStackTrace()
            render ([msg:"Excel导入失败"]) as JSON
        }finally{
            //关闭输入流
            if(!inputStream){
                inputStream.close()
            }
        }
    }

    /**
     *  从指定行startRow开始读取，截止到lastRow最后行
     *  startRow的下标从0开始
     *  lastRow下标从1开始
     *  lastRow必须大于startRow(lastRow > startRow)，若lastRow==-1，则导入读取指定行开始之后的所有数据
     */
    def rangeImport = {
        InputStream inputStream = null
        try {
            //获取文件，此处可通过上传获取文件
            File file = new File("c:\\file\\用户信息.xlsx")
            //定义表头
            Map CONFIG_BOOK_COLUMN_MAP = [
                    sheet:'Sheet1',
                    startRow: 1,
                    lastRow: -2,
                    columnMap:  [
                            //Col, Map-Key
                            'A':'name',
                            'B':'age',
                            'C':'sex',
                            'D':'addr'
                    ]
            ]
            //将文件放到输入流中
            inputStream = new FileInputStream(file)
            Workbook workbook = WorkbookFactory.create(inputStream)

            //解析Excel的行存入list
            List list = excelImportService.columns(workbook,CONFIG_BOOK_COLUMN_MAP)
            list.each { v->
                User user = new User()
                user.name = v.name
                user.age = v.age as Integer
                user.sex = v.sex
                user.addr = v.addr
                println "用户信息："+user
                user.save(flush:true)
                println (user as JSON)
            }
            render ([msg:"Excel导入成功"]) as JSON
        } catch (Exception e) {
            e.printStackTrace()
            render ([msg:"Excel导入失败"]) as JSON
        }finally{
            //关闭输入流
            if(!inputStream){
                inputStream.close()
            }
        }
    }

```