前言

1、将用户信息导出为excel表格（导出数据….）

2、将Excel表中的信息录入到网站数据库（习题上传….）

开发中经常会设计到excel的处理，如导出Excel，导入Excel到数据库中！

操作Excel目前比较流行的就是 Apache POI 和 阿里巴巴的 easyExcel ！

接下来说说Apache下的POI吧

<dependencies>
    <!--xls(03)-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.9</version>
    </dependency>
    <!--xlsx(07)-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.9</version>
    </dependency>
    <!--日期格式化工具-->
    <dependency>
        <groupId>joda-time</groupId>
        <artifactId>joda-time</artifactId>
        <version>2.10.1</version>
    </dependency>
    <!--test-->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
</dependencies>

注意：

1、jar包需要保持版本（version）一样

2、2003 版本和 2007 版本存在兼容性的问题！03版本最多只有 65536 行！07版本没有限制

2、Excel导入

public void importExcel(@RequestParam("file") MultipartFile multipartFile) {
        //MultipartFile转换成File
        File file=MultipartFile2FileUtil.MultipartFile2File(multipartFile);
        //需要解析的Excel文件
        try {
            //获取工作簿
            /**
             * 构造函数获取一个FileInputStream对象
             * FileUtils.openInputStream 调用了 FileInputStream 的构造函数去打开一个文件（open0是一个本地方法，打开指定文件进行读取）
             * 对FileInputStream中的open调用open0方法---> https://blog.csdn.net/zhlily1/article/details/109468154
             */
            FileInputStream fs = FileUtils.openInputStream(file);
            XSSFWorkbook workbook = new XSSFWorkbook(fs);
            //获取最Excel中的sheet数量
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int j=0;j<numberOfSheets;j++){
            //获取第一个工作表
                XSSFSheet hs = workbook.getSheetAt(j);
                //获取Sheet的第一个行号和最后一个行号
                int last = hs.getLastRowNum();
                int first = hs.getFirstRowNum() + 1;
                //遍历获取单元格里的信息
                List<AuditContentImportBo> list = new ArrayList<>();
                //注：row为行   cell为列
                for (int i = first; i <= last; i++) {
                    XSSFRow row = hs.getRow(i);
                    String cell1=new String();
                    //可以在这里遍历row，获取当前行每列的数据
                    if (!StringUtil.isNullOrEmpty(cell1)) {
                        try {
                            cell1=row.getCell(1).getStringCellValue();
                        }catch (Exception e){
                            cell1=String.valueOf(row.getCell(1).getNumericCellValue());
                        }
                    }
                }
                String name = hs.getSheetName();
                equipmentAuditContentService.importEquipmentAuditContent(name, list);
            }
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        //清空文件缓存，并且调用delete0关闭文件
        MultipartFile2FileUtil.deleteFile(file);
    }

3、MultipartFile2FileUtil工具类

public class MultipartFile2FileUtil {
    /**
     * MultipartFile转换为File
     * @param multipartFile
     * @return
     * MultipartFile继承了InputStreamSource
     */
    public static File MultipartFile2File(MultipartFile multipartFile){
        //文件上传前的名称
        String fileName = multipartFile.getOriginalFilename();
        //new 一个 file
        File file = new File(fileName);
        OutputStream out = null;
        try{
            //获取文件流，以文件流的方式输出到新文件 
//    InputStream in = multipartFile.getInputStream();
            //获取到一个file的输出流
            out = new FileOutputStream(file);
            //获取multipartFile的字节文件，并将字节文件遍历write到out输出流
            byte[] ss = multipartFile.getBytes();
            for(int i = 0; i < ss.length; i++){
                out.write(ss[i]);
            }
        }catch(IOException e){
            e.printStackTrace();
        }finally {
            //进行判断关闭输出流
            if (out != null){
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return file;
    }
    public static void deleteFile(File file){
        File f = new File(file.toURI());
        f.delete();
    }
}

4、导出Excel

public ApiResult exportAuditContent(HttpServletResponse response, @RequestBody ExportAuditContentIds exportAuditContentIds){
        OutputStream outputStream = tryCatch2gGetOutput(response);
        List<Entity> entitys=dao.getEntitys();
        HSSFWorkbook workbook = new HSSFWorkbook();
        //headers
        String[] headers=new String[]{"表头1","表头2","表头3"};
        int sheetNum=0;
        //遍历查到的list
        for (Entity entity : entitys) {
            List<List<String>> result=new ArrayList<>();
            //sheetName
            String sheetTitle=entity.getSheetName();
            for (entity exportAuditContent : auditContent.getContentName()) {
                List<String> str=new ArrayList<>();
                str.add(exportAuditContent.getXXX());
                str.add(exportAuditContent.getXXX());
                str.add(exportAuditContent.getXXX());
                result.add(str);
            }
                ExcelForMoreSheet.exportExcel(workbook,sheetNum,sheetTitle,headers,result,outputStream);
            sheetNum++;
        }
        try {
            workbook.write(outputStream);
            outputStream.close();
        } catch (Exception e) {
            throw new AnyException("导出出错");
        }
        return success("导出成功","成功");
    }

5、tryCatch2gGetOutput

private OutputStream tryCatch2gGetOutput(HttpServletResponse response){
        try {
            OutputStream output = response.getOutputStream();
            response.reset();
            String filename = "title"+new SimpleDateFormat("yyyy-MM-dd").format(new Date()) +".xls";
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment;filename="
                    .concat(String.valueOf(URLEncoder.encode(filename, "UTF-8"))));
            response.setHeader("Access-Control-Allow-Origin", "*");
            response.setHeader("Cache-Control", "no-cache");
            return output;
        } catch (Exception e) {
            throw new AnyException("导出出错");
        }
    }

6、Excel导出工具类

package com.troo;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelForMoreSheet {
    /**
     * @Title: exportExcel
     * @Description: 导出Excel的方法
     * @author: evan @ 2014-01-09
     * @param workbook
     * @param sheetNum (sheet的位置，0表示第一个表格中的第一个sheet)
     * @param sheetTitle  （sheet的名称）
     * @param headers    （表格的标题）
     * @param result   （表格的数据）
     * @param out  （输出流）
     * @throws Exception
     */
    public static void exportExcel(HSSFWorkbook workbook, int sheetNum,
                            String sheetTitle, String[] headers, List<List<String>> result,
                            OutputStream out)  {
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet();
        workbook.setSheetName(sheetNum, sheetTitle);
        // 设置表格默认列宽度为20个字节
        sheet.setDefaultColumnWidth((short) 20);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.BLACK.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 指定当单元格内容显示不下时自动换行
        style.setWrapText(true);
        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell((short) i);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text.toString());
        }
        // 遍历集合数据，产生数据行
        if (result != null) {
            int index = 1;
            for (List<String> m : result) {
                row = sheet.createRow(index);
                int cellIndex = 0;
                for (String str : m) {
                    HSSFCell cell = row.createCell((short) cellIndex);
                    if (str==null){
                        str="";
                    }
                    cell.setCellValue(str.toString());
                    cellIndex++;
                }
                index++;
            }
        }
    }
}

7、Entity类

public class Entity{
    private String sheetName;
    private List<SubEntity> subEntitys;
}
public class ExportAuditContent {
    private String param1;
    private String param2;
    private String param3;
}

8、03版测试

String PATH = "D:idea.webProject26";
@Test
public void testWrite03() throws Exception {
    // 1、创建一个工作簿
    Workbook workbook = new HSSFWorkbook();
    // 2、创建一个工作表
    Sheet sheet = workbook.createSheet("统计表");
    // 3、创建一个行  （1,1）
    Row row1 = sheet.createRow(0);
    // 4、创建一个单元格
    Cell cell11 = row1.createCell(0);
    cell11.setCellValue("今日新增");
    // (1,2)
    Cell cell12 = row1.createCell(1);
    cell12.setCellValue(666);
    // 第二行 (2,1)
    Row row2 = sheet.createRow(1);
    Cell cell21 = row2.createCell(0);
    cell21.setCellValue("统计时间");
    // (2,2)
    Cell cell22 = row2.createCell(1);
    String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
    cell22.setCellValue(time);
    // 生成一张表（IO 流）  03 版本就是使用 xls结尾！
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "zh03.xls");
    // 输出
    workbook.write(fileOutputStream);
    // 关闭流
    fileOutputStream.close();
    System.out.println("zh03 生成完毕！");
}

9、07版测试

@org.junit.Test
    public void testWrite07() throws IOException {
        //1、创建一个工作簿
         Workbook workbook = new XSSFWorkbook();
         //2、创建一个工作表
        Sheet sheet = workbook.createSheet("统计表");
        //3、创建一个行（1,1）
        Row row1=sheet.createRow(0);
        //4、创建一个单元格
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("今日新增");

        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("今日无增");

        //创建一个行（2,1）
        Row row2 = sheet.createRow(1);
        //创建一个单元格
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("统计时间");

        Cell cell22 = row2.createCell(1);
        String time = new DateTime().toString("yyyy-mm-dd HH:mm:ss");
        cell22.setCellValue(time);

        //生成一张表 io流  注意这里是07版本
        FileOutputStream fileOutputStream = new FileOutputStream(path+"java07.xlsx");
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("07版本文件生成完毕");
    }

测试成功！

10、总结：

在ApachePoi 官方提供了对操作大数据量的导入导出的工具和解决办法，操作Excel2007使用XSSF对象，可以分为三种模式：

• 用户模式：用户模式有许多封装好的方法操作简单，但创建太多的对象，非常耗内存（之前使用的方法）
• 事件模式：基于SAX方式解析XML，SAX全称Simple API for XML，它是一个接口，也是一个软件包。它是一种XML解析的替代方法，不同于DOM解析XML文档时把所有内容一次性加载到内存中的方式，它逐行扫描文档，一边扫描，一边解析。
• SXSSF对象：是用来生成海量excel数据文件，主要原理是借助临时存储空间生成excel