Java导入数据到excel java导入导出excel

转载

编程小天才 2023-06-21 19:08:06

文章标签 Java导入数据到excel excel java spring List 文章分类 Java 后端开发

java实现excel导入导出（jxl）

java 导入导出excel的几种方式
代码实现
运行结果

java 导入导出excel的几种方式

常见有2种方式，一种是jxl,一种是poi.

他们之间的区别：
jxl只能支持后缀名为xls的文件。
poi不仅支持xls还支持xlsx格式;提供API对Microsoft Office格式档案读和写的功能。

数据较小时两者没有明显差别；数据量过大时，jxl相对poi来说对jvm虚拟机内存的消耗不高，如果只是简单的单个excel的读写操作用jxl，对于复杂的建议使用poi。

代码实现

// 使用的jar包
        <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.12</version>
        </dependency>

Controller类

package com.mycompany.myapp.web.rest;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.mycompany.myapp.service.test.ExcelService;
import com.mycompany.myapp.service.test.impl.ExcelJxlServiceImpl;
import io.micrometer.core.annotation.Timed;
import io.swagger.annotations.ApiOperation;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.io.File;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @description:
 * @author: alan
 * @time: 2021/7/23 18:08
 */
@Controller
@RequestMapping("/api")
public class TestJxl {
    private final Logger log = LoggerFactory.getLogger(TestJxl .class);

    private ExcelService excelService = new ExcelJxlServiceImpl();

    @Autowired
    private ObjectMapper objectMapper;


    @PostMapping("/test/exportExcel")

    @ApiOperation(value = "导出Excel")
    public void exportExcel(HttpServletResponse response, HttpServletRequest request) {
        excelService.export(response, request);
    }

    @PostMapping("/test/importExcel")
    @ApiOperation(value = "导入Excel")
    public ResponseEntity importFile(@RequestParam("file") MultipartFile multipartFile) {
        try {
            String types = multipartFile.getContentType();
            InputStream inputStream = multipartFile.getInputStream();
            File file = new File(multipartFile.getOriginalFilename());
            List<String> fileContents = new ArrayList<String>();
            if (multipartFile.getOriginalFilename().contains("xls")) {
                fileContents = excelService.importExcel("xls", inputStream);
            } else if (multipartFile.getOriginalFilename().contains("csv")) {
                fileContents = excelService.importExcel("csv", inputStream);
            }
            String json = objectMapper.writeValueAsString(fileContents);
            log.info("导入的数据：{}", json);
        } catch (Throwable t) {
            t.printStackTrace();
        }
        return new ResponseEntity<>(null, null, HttpStatus.OK);

    }

}

接口类

package com.mycompany.myapp.service.test;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.util.List;

/**
 * @description:
 * @author: alan
 * @time: 2021/7/25 21:33
 */
public interface ExcelService {

     void  export(HttpServletResponse response, HttpServletRequest request);

     List importExcel(String type, InputStream inputStream);
}

接口实现类

package com.mycompany.myapp.service.test.impl;

import com.mycompany.myapp.service.test.ExcelService;
import com.mycompany.myapp.util.DownloadFileUtil;
import com.mycompany.myapp.util.JxlUtil;
import com.mycompany.myapp.web.rest.vm.LoginVM;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @description:
 * @author: alan
 * @time: 2021/7/25 21:38
 */
public class ExcelJxlServiceImpl implements ExcelService {

    /**
     * 导出excel
     */
    @Override
    public void export(HttpServletResponse response, HttpServletRequest request) {
        // 文件新名
        String newFileName = "UserInfo.xls";
        String pathName = "D:/usr/" + newFileName;
        String title[] = {"账号", "密码", "登记"};
        LoginVM loginOne = new LoginVM();
        loginOne.setUsername("小米");
        loginOne.setPassword("123");
        loginOne.setRememberMe(true);
        LoginVM loginTwo = new LoginVM();
        loginTwo.setUsername("小明");
        loginTwo.setPassword("123");
        loginTwo.setRememberMe(false);
        List<LoginVM> list = new ArrayList<>();
        list.add(loginOne);
        list.add(loginTwo);
        //文件保存在指定位置
        JxlUtil.exportExcel(pathName, title, list);
        //获取文件流返回给客户端
        DownloadFileUtil.downloadFile(response, request, pathName);
    }

    /**
     *  导入
     * @param type
     * @param inputStream
     * @return
     */
    @Override
    public List importExcel(String type, InputStream inputStream) {
        List list = new ArrayList();
        if (type.equals("xls")) {
            list = JxlUtil.xlsContent(inputStream);
        } else {
            list = JxlUtil.csvContent(inputStream);
        }
        return list;
    }
}

jxl工具类

package com.mycompany.myapp.util;

import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @description:
 * @author: alan
 * @time: 2021/7/23 18:26
 */
public class JxlUtil {

    /**
     * 导出Excel
     *
     * @param fileName 文件地址名称
     * @param Title 导出excel的标题
     * @param listContent 导出的list
     * @return
     */
    public final static boolean exportExcel(String fileName, String[] Title, List<?> listContent) {
        final Logger logger = LoggerFactory.getLogger(JxlUtil.class);
        WritableWorkbook workbook = null;
        // 以下开始输出到EXCEL
        try {
            String filePathName = fileName.substring(0,fileName.lastIndexOf("/"));
            File f = new File(filePathName);
            if(!f.exists()){
                f.mkdirs();//创建目录
            }
            // 创建可写入的Excel工作簿
            File file = new File(fileName);
            if (!file.exists()) {
                boolean bool = file.createNewFile();
                logger.info("创建Excel工作簿结果",bool);
            }
            /** **********创建工作簿************ */
            workbook = Workbook.createWorkbook(file);
            /** **********创建工作表************ */
            WritableSheet sheet = workbook.createSheet("Sheet1", 0);
            /** **********设置纵横打印（默认为纵打）、打印纸***************** */
            jxl.SheetSettings sheetset = sheet.getSettings();
            sheetset.setProtected(false);
            /** ************设置单元格字体************** */
            WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
            WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
            /** ************以下设置三种单元格样式，灵活备用************ */
            // 用于标题居中
            WritableCellFormat wcf_center = new WritableCellFormat(BoldFont);
            wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
            wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
            wcf_center.setAlignment(Alignment.CENTRE); // 文字水平对齐
            wcf_center.setWrap(false); // 文字是否换行
            // 用于正文居左
            WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);
            wcf_left.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条
            wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
            wcf_left.setAlignment(Alignment.LEFT); // 文字水平对齐
            wcf_left.setWrap(false); // 文字是否换行

            /** ***************以下是EXCEL开头大标题，暂时省略********************* */
            // sheet.mergeCells(0, 0, colWidth, 0);
            // sheet.addCell(new Label(0, 0, "XX报表", wcf_center));
            /** ***************以下是EXCEL第一行列标题********************* */
            for (int i = 0; i < Title.length; i++) {
                sheet.addCell(new Label(i, 0, Title[i], wcf_center));
            }
            /** ***************以下是EXCEL正文数据********************* */
            Field[] fields = null;
            int i = 1;
            for (Object obj : listContent) {
                fields = obj.getClass().getDeclaredFields();
                int j = 0;
                for (Field v : fields) {
                    v.setAccessible(true);
                    Object va = v.get(obj);
                    if (va == null) {
                        va = "";
                    }
                    if (va.getClass().getSimpleName().equals("Double")) {
                        sheet.addCell(new Label(j, i, BigDecimal.valueOf((Double) va) + "", wcf_left));
                    } else if (va.getClass().getSimpleName().equals("Float")) {
                        Double vDouble = ((Float) va).doubleValue();

                        sheet.addCell(new Label(j, i, new BigDecimal(new DecimalFormat("#.00").format(vDouble)) + "",
                            wcf_left));
                    } else {
                        sheet.addCell(new Label(j, i, va.toString() + "", wcf_left));
                    }
                    j++;
                }
                i++;
            }
            /** **********将以上缓存中的内容写到EXCEL文件中******** */
            workbook.write();

        } catch (Throwable t) {
            logger.error("系统提示：Excel文件导出失败，原因：",t.getMessage());
            t.printStackTrace();
        }finally {
            try {
                /** *********关闭文件************* */
                workbook.close();
            }catch (Exception e){
                logger.error("系统提示：关闭文件失败，原因：",e.getMessage());
            }
        }
        return true;
    }
    /**
     * 获取CSV文件中的内容
     *
     * @param inputStream
     * @return
     */
    public static List<String> csvContent(InputStream inputStream) {
        List<String> allString = new ArrayList<>();

        if (inputStream != null) {
            InputStreamReader inputStreamReader;
            BufferedReader br = null;
            //FileInputStream fins = new FileInputStream(csv);
            try {
                inputStreamReader = new InputStreamReader(inputStream, "GBK");
                br = new BufferedReader(inputStreamReader);
                String line = "";
                String everyLine = "";
                while ((line = br.readLine()) != null) { // 读取到的内容给line变量
                    everyLine = line + " ,";
                    allString.add(everyLine);
                }

            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    br.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
        return allString;
    }

    /**
     * 获取xls文件中的内容
     *
     * @param inputStream
     * @return
     */
    public static List<String> xlsContent(InputStream inputStream) {
        List<String> allString = new ArrayList<String>();
        try {
            // 创建输入流，读取Excel
            //InputStream is = new FileInputStream(xls.getAbsolutePath());
            // jxl提供的Workbook类
            Workbook wb = Workbook.getWorkbook(inputStream);
            // Excel的页签数量
            int sheet_size = wb.getNumberOfSheets();
            for (int index = 0; index < sheet_size; index++) {
                // 每个页签创建一个Sheet对象
                Sheet sheet = wb.getSheet(index);
                // sheet.getRows()返回该页的总行数
                for (int i = 0; i < sheet.getRows(); i++) {
                    // sheet.getColumns()返回该页的总列数
                    StringBuffer sb = new StringBuffer();
                    for (int j = 0; j < sheet.getColumns(); j++) {
                        String cellinfo = sheet.getCell(j, i).getContents();
                        sb.append(cellinfo + ";");
                    }
                    allString.add(sb.toString().substring(0, sb.length() - 1));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return allString;
    }
}

下载工具类

package com.mycompany.myapp.util;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;

/**
 * @description:
 * @author: alan
 * @time: 2020/9/27 18:01
 */
public class DownloadFileUtil {
    /**
     * 下载文件
     *
     * @param response
     * @param request
     * @param filePath 文件地址
     * @throws Exception
     */
    public static void downloadFile(HttpServletResponse response, HttpServletRequest request, String filePath) {
        try {
            //获取文件
            File file = new File(filePath);
            String fileName = file.getName();
            response.reset();
            ServletOutputStream out = response.getOutputStream();
            request.setCharacterEncoding("UTF-8");
            int BUFFER = 1024 * 10;
            byte data[] = new byte[BUFFER];
            BufferedInputStream bis = null;
            //获取文件输入流
            InputStream inputStream = new BufferedInputStream(new FileInputStream(filePath));
            // 以流的形式下载文件。
            DataInputStream fis = new DataInputStream(inputStream);
            int read;
            bis = new BufferedInputStream(fis, BUFFER);
            response.setContentType("application/OCTET-STREAM");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
            while ((read = bis.read(data)) != -1) {
                out.write(data, 0, read);
            }
            fis.close();
            bis.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            File file = new File(filePath);
            //删除临时文件
            if (file.exists()) {
                file.delete();
            }
        }
    }
}