java中用jxl方式实现EXCEL导入/导出（详情版）

pom导入包

    <dependency>
        <groupId>net.sourceforge.jexcelapi</groupId>
        <artifactId>jxl</artifactId>
        <version>2.6.12</version>
    </dependency>

pojo类

package com.example.demo.pojo;

import java.io.Serializable;

public class User implements Serializable {
   
    private Long id;
    private String name;
    private Integer sex;
    private String memo;
    private Integer age;

    private static final long serialVersionUID = 1L;
   
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }
  
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name == null ? null : name.trim();
    }
    
    public Integer getSex() {
        return sex;
    }
   
    public void setSex(Integer sex) {
        this.sex = sex;
    }

    public String getMemo() {
        return memo;
    }

    public void setMemo(String memo) {
        this.memo = memo == null ? null : memo.trim();
    }

    public Integer getAge() {
        return age;
    }
   
    public void setAge(Integer age) {
        this.age = age;
    }

   
    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append(getClass().getSimpleName());
        sb.append(" [");
        sb.append("Hash = ").append(hashCode());
        sb.append(", id=").append(id);
        sb.append(", name=").append(name);
        sb.append(", sex=").append(sex);
        sb.append(", memo=").append(memo);
        sb.append(", age=").append(age);
        sb.append(", serialVersionUID=").append(serialVersionUID);
        sb.append("]");
        return sb.toString();
    }
}

1–处理导入的excel数据

package com.example.demo.common;

import jxl.Sheet;
import jxl.Workbook;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

/**
 * @program: fileImportandexport
 * @description: 导入文件成excel
 * @author: KJH
 * @create: 2019-04-28 09:09
 */
public class ImportExcelCommon {

    /**
      *@Description: 导入excel
      *@Param: File file
      *@return: List<Object> list list第一个数据是行（共几行） 第二个是列（共几列）
      *@Author: KJH
      *@date:
      **/
    public  List<Object> importExcel(File file){
        List<Object> list=new ArrayList();
        try {
           //1获取文件
            Workbook book=Workbook.getWorkbook(file);
            //2获得第一个工作表对象
            Sheet sheet=book.getSheet(0);
            //3得到所有的列,行
            int rows=sheet.getRows();//行
            int colums=sheet.getColumns();//列
            list.add(rows);
            list.add(colums);
            //4遍历数据
            for (int i = 1; i < rows; i++) {
                for (int j = 0; j < colums; j++) {
                    if(Tools.notEmpty(sheet.getCell(j,i).getContents())) {
                        list.add(sheet.getCell(j,i).getContents().trim());//得到数据并添加到list中
                    }
                }
            }
            //5.关闭资源
            book.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    return  list;
    }
}

2.处理导入文件，获取结果添加到数据库

package com.example.demo.controller;


import com.example.demo.common.DateUtil;
import com.example.demo.common.ImportExcelCommon;
import com.example.demo.common.InterfaceBase;
import com.example.demo.pojo.User;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

/**
 * @program: fileImportandexport
 * @description: 需要导入的数据
 * @author: KJH
 * @create: 2019-04-28 09:13
 */
@Controller
@RequestMapping
public class ImportManageController extends InterfaceBase {
    @RequestMapping(value = "/index")
    public String index(){
        return "/import/import";
    }

    @RequestMapping(value = "/import")
    public void exportManagee(MultipartFile file) {
        try {
            if (file.isEmpty()) {
                return;
            }
            String fileName = file.getOriginalFilename();//获取文件名称
            String prefix = fileName.substring(fileName.lastIndexOf("."));// 获取文件后缀
            File excelFile = File.createTempFile(DateUtil.getSdfTimes(), prefix);//生成文件
            file.transferTo(excelFile);//生成临时文件，结束后要删除
            List<User> userList = new ArrayList<User>();//新增到数据库的list
            ImportExcelCommon importExcelCommon = new ImportExcelCommon();
            List<Object> list = importExcelCommon.importExcel(excelFile);//用于存放获取导入的信息
            if (!list.isEmpty() && list.size() > 2) {
                //2. 对得到的内容进行处理,放到对象中
                int rows = (int) list.get(0) - 1;//得到行,并减去第一行数据(有标题-1,没有就不用-1)
                int count = 2;//list 前两个数据固定成行和列了，所以下表从2开始
                int colums = (int) list.get(1);//得到列
                for (int i = 0; i < rows; i++) {
                    User user = new User();
                    user.setName(list.get(count).toString());
                    user.setSex(Integer.parseInt( list.get(count + 1).toString()));
                    user.setAge( Integer.parseInt(list.get(count + 2).toString()));
                    user.setMemo(list.get(count + 3).toString());
                    userList.add(user);
                    count += colums;//下次添加数据从这列添加数据之后去添加 如第一列4 从list下标2，第二次从list下标 6
                }
            }
        if (userList.size()>0){
            for (User user : userList) {
                userService.insert(user);
            }
        }
         //删除临时文件
        this.deleteFile(excelFile);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /**
     * @Title: deleteFile
     * @Description: 删除临时添加的数据
     * @param   files
     * @return void
     * @author KJH
     * @date  2018年9月12日 上午11:09:12
     * @version V1.0
     * @throws
     */
    private void deleteFile(File... files) throws Exception {
        for (File file : files) {
            if (file.exists()) {
                file.delete();
            }
        }
    }
}

3–导出数据处理公共类

package com.example.demo.common;

import jxl.SheetSettings;
import jxl.Workbook;
import jxl.format.*;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.util.Map;

/**
 * @program: fileImportandexport
 * @description: 导出文件成excel
 * @author: KJH
 * @create: 2019-04-28 09:09
 */
public class ExportExcelCommon {

    /**
     *@Description: 导出公共类
     *@Param: HttpServletResponse response, Map< Integer, Object> map, Map< Integer, Object> maps
     *@return:
     *@Author: KJH
     *@date:
     **/
    public  void exportExcel(HttpServletResponse response, Map< Integer, Object> map, Map< Integer, Object> maps) {
        //1.生成文件名称
        String fileName= DateUtil.getSdfTimes()+".xls";
        //2.设置编码，格式,头
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/x-excel");
        response.addHeader("Content-Disposition", "attachment;filename=" +fileName);
        //3.创建excel，设置格式
        try {
            //创建一个excel的sheet
            WritableWorkbook workbook= Workbook.createWorkbook(response.getOutputStream());
            //定义单元格格式 可以设置字体，颜色等
            WritableCellFormat wf=new WritableCellFormat();
            wf.setAlignment(Alignment.CENTRE);//中心对齐
//            //设置背景颜色;
//            wf.setBackground(Colour.BLUE_GREY);
//            //设置边框;
//            wf.setBorder(Border.ALL, BorderLineStyle.DASH_DOT);
//            //设置自动换行;
//            wf.setWrap(true);
//            //设置文字居中对齐方式;
//            wf.setAlignment(Alignment.CENTRE);
//            //设置垂直居中;
//            wf.setVerticalAlignment(VerticalAlignment.CENTRE);
            WritableSheet sheet=null;
            SheetSettings settings=null;
            for (int i = 0; i <1; i++) {
                sheet = workbook.createSheet("用户信息列表",i);
                settings=sheet.getSettings();
                settings.setVerticalFreeze(1);
                //添加第一行标题
                int o=0;
                for (Map.Entry<Integer, Object> entrys : map.entrySet()) {
                    sheet.addCell(new Label(o,0,entrys.getValue()+"", wf));
                    o+=1;
                }
                //处理数据
                if(maps.size()>0) {
                    int h=0;//行
                    int l=0;//列
                    int s=0;//代表当一行所有列都添加进去后s++,然后重新复制行和列
                    for (Map.Entry<Integer, Object> entry : maps.entrySet()) {
                        if(0==s) {
                            l=0;//列
                            h+=1;//行
                        } else if (0 == s % map.size()){
                            l=0;//列
                            h++;//行
                        }
                        //第1行 0 0 - - 1 0 -- 2 0 - -
                        //第2行 0 1 - - 1 1 -- 3 1 - -
                        sheet.addCell(new Label(l++,h,entry.getValue()+"",wf));
                        s+=1;
                    }
                }
            }
            workbook.write();//写入excel
            workbook.close();//关闭资源
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}

4–查询获取结果，然后调用公共类处理数据

package com.example.demo.controller;

import com.example.demo.common.ExportExcelCommon;
import com.example.demo.common.InterfaceBase;
import com.example.demo.pojo.User;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

/**
 * @program: fileImportandexport
 * @description: 需要导出的数据
 * @author: KJH
 * @create: 2019-04-28 09:13
 */
@RestController
@RequestMapping
public class ExportManageController extends InterfaceBase {

    @RequestMapping(value = "/export")
    public  void  exportManagee(HttpServletResponse response,User user){
        Map< Integer, Object> map=new TreeMap<Integer, Object>();//添加导出数据第一行的内容
        Map< Integer, Object> maps=new TreeMap<Integer, Object>();//添加导出数据（从第二行开始）
//        map.put(0,"ID");
        map.put(0,"名称");
        map.put(1,"性别");
        map.put(2,"年龄");
        map.put(3,"记录");
        List<User> userList = userService.selectAll();
        try {
        //3遍历数据内容
           if (!userList.isEmpty() && userList.size()>0){
               for (int i = 0; i< userList.size(); ) {
//                       maps.put(i,userList.get(i).getId());
                       maps.put(i+0,userList.get(i).getName());
                       maps.put(i+1,userList.get(i).getSex());
                       maps.put(i+2,userList.get(i).getAge());
                       maps.put(i+3,userList.get(i).getMemo());
                       i+=map.size();
               }
           }
            ExportExcelCommon exportExcelCommon=new ExportExcelCommon();
            exportExcelCommon.exportExcel(response,map,maps);
        } catch (Exception ex) {
            ex.printStackTrace();
        }

    }
}

源码地址

git@github.com:kongjihui/study.git

版权声明：本文为博主原创文章，转载请附上博文链接！