记一次简单的POI导出 excel 表格 超简单 一学就会

首先我们创建一个springboot项目, 或者一个web项目.这一步就省略了,,,,,,

maven项目

项目目录

1  引入依赖

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.11</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.11</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.11</version>
        </dependency>
        <dependency>
            <groupId>net.sf.jxls</groupId>
            <artifactId>jxls-core</artifactId>
            <version>1.0.6</version>
        </dependency>

2 引入插件(为了防止maven项目打包的时候将xls 等格式文件一并都打了包)

            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-resources-plugin</artifactId>
                <configuration>
                    <nonFilteredFileExtensions>
                        <nonFilteredFileExtension>xlsx</nonFilteredFileExtension>
                        <nonFilteredFileExtension>xls</nonFilteredFileExtension>
                        <nonFilteredFileExtension>zip</nonFilteredFileExtension>
                        <nonFilteredFileExtension>cer</nonFilteredFileExtension>
                        <nonFilteredFileExtension>pfx</nonFilteredFileExtension>
                        <nonFilteredFileExtension>py</nonFilteredFileExtension>
                        <nonFilteredFileExtension>keystore</nonFilteredFileExtension>
                    </nonFilteredFileExtensions>
                </configuration>
            </plugin>

3 写模版(jxls语法自行百度 点击这里提供一个简单用法参考 )

姓名	性别	年龄	身高
<jx:forEach items="${list}" var="user">
${user.name}	${user.sex}	${user.age}	${user.tall}
</jx:forEach>

 

4 设置简单的实体类

public class User implements Serializable {
    String name;
    String age;
    String sex;
    String tall;
/*getter and setter*/
}

5 controller层代码

下面给了两种方式获取流, 结果都是一样的.

package com.trade.serviceeureka;

import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.cloud.netflix.eureka.server.EnableEurekaServer;
import org.springframework.util.ResourceUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;

@Controller
@RequestMapping
public class Controller {
    
    @RequestMapping("/getExecl")
    public void get(HttpServletRequest request, HttpServletResponse response) throws IOException, InvalidFormatException {
        // 文件流
        File file = ResourceUtils.getFile("classpath:template/template1.xlsx");
        InputStream in = new FileInputStream(file);

        //resource 流
//        Resource resource = new ClassPathResource("template/template1.xls");
//        InputStream in = resource.getInputStream();

        Map<String, Object> beanParams = new HashMap<>();
        List<User> list = new ArrayList<>();
        User u = new User("aa", "1", "男人", "180cm");
        User u2 = new User("bb", "2", "女人", "120cm");
        User u3 = new User("cc", "3", "人妖", "130cm");
        User u4 = new User("dd", "4", "阴阳人", "140cm");
        list.add(u);
        list.add(u2);
        list.add(u3);
        list.add(u4);
        beanParams.put("list", list);
        // 构造标准的poi导出格式
        XLSTransformer transformer = new XLSTransformer();
        Workbook workbook = transformer.transformXLS(in, beanParams);


        // 设置输出流
        ServletOutputStream out = response.getOutputStream();
        // 设置导出名称
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd-HHmmss");
        String exportFileName = "out-" + sdf.format(new Date()) + ".xls";
        // 设置导出格式
        response.setHeader("Content-Disposition", "attachment;filename=" + exportFileName);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        // 将内容写入输出流并把缓存的内容全部发出去
        workbook.write(out);
        // 关闭流
        in.close();
        out.flush();
        out.close();
    }
}

6 运行结果

 

总结:

      Workbook workbook = transformer.transformXLS(in, beanParams);  这个方法非常强大,  不管你是03年版本的excel 还是07年的, 它都能准确的判断, 不再需要像以前那样判断 文件的格式是xls还是xlsx.

看下源码:

 

根据 流 判断 到底创建HSSF 还是 XSSF book的. 一般来说都能正常运行, 

最常见的异常就是  下面

 

Your InpuStream was neither an OLE2 stream, nor an OOXML stream   看下所有步骤有没有漏掉的.