Java导出Excel表格（一）

pom文件加入依赖

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

导出Excel步骤为：
1.创建工作簿
2.创建sheet页面
3.创建单元表格
4.将单元格添加到sheet页面中
5…将数据写入
6.将工作簿关闭资源

        File file = new File("d:/student.xls");
        if (!file.exists()){  //创建file对象如果文件不存在创建指定文件
            file.createNewFile();
        }

        //1.创建工作簿
        WritableWorkbook wb = Workbook.createWorkbook(file);
        //2.创建sheet页面   参数1：sheet页的名字   参数2：sheet页的索引
        WritableSheet ws = wb.createSheet("sheet0", 0);

        //3.创建单元格   第一个参数c：列   第二个参数r：行    第三个参数cont：数据
        Label label1 = new Label(0, 0, "id");
        Label label2 = new Label(1, 0, "name");
        Label label3 = new Label(2, 0, "age");

        //4.将单元格添加到sheet页面中
        ws.addCell(label1);
        ws.addCell(label2);
        ws.addCell(label3);
        //5.将数据写入
        wb.write();

        //将工作簿关闭资源
        wb.close();

---

以下是使用了ExcelUtils封装的导出Excel
ExcelUtils工具类

package com.example.excel.Utils;

import com.example.excel.entity.Province;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

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

public class ExcelUtils {

    public static void exportExcel(String filePath, List columnList,List listAll) throws IOException, WriteException {

        File file = new File(filePath);
        if (!file.exists()){  //创建file对象如果文件不存在创建指定文件
            file.createNewFile();
        }


        //1.创建工作簿
        WritableWorkbook wb = Workbook.createWorkbook(file);
        //2.创建sheet页面   参数1：sheet页的名字   参数2：sheet页的索引
        WritableSheet ws = wb.createSheet("sheet0", 0);

        //3.创建单元格   第一个参数c：列   第二个参数r：行    第三个参数cont：数据
        for (int i = 0; i <columnList.size() ; i++) {

            Label label = new Label(i, 0, (String) columnList.get(i));
            //4.将单元格添加到sheet页面中
            ws.addCell(label);
        }


        Label label1 =null;
        Label label2 =null;
        int i = 1;
        for (Object province : listAll){
            Province province1 = (Province) province;

                label1 = new Label(0,  i,province1.getId()+"");
                label2 = new Label(1, i,province1.getName());
                i++;
            ws.addCell(label1);
            ws.addCell(label2);
        }


        //5.将数据写入
        wb.write();

        //将工作簿关闭资源
        wb.close();
    }
}

mapper层

package com.example.excel.mapper;

import com.example.excel.entity.Province;

import java.util.List;

public interface ProvinceMapper {
    List<Province> findAll();

    List<String> findColumn();
}

mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.excel.mapper.ProvinceMapper">
    <select id="findAll" resultType="com.example.excel.entity.Province">
        select * from province
    </select>
    <!--查找数据库对应的字段数据-->
    <select id="findColumn" resultType="java.lang.String">
        SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME = 'province';
    </select>
</mapper>

service层

package com.example.excel.service;

import com.example.excel.entity.Province;

import java.util.List;

public interface ProvinceService {
    List<Province> findAll();

    List<String> findColumn();
}

serviceImpl实现类

package com.example.excel.service.impl;

import com.example.excel.entity.Province;
import com.example.excel.mapper.ProvinceMapper;
import com.example.excel.service.ProvinceService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

@Service
public class ProvinceServiceImpl implements ProvinceService {

    @Resource
    private ProvinceMapper provinceMapper;


    @Override
    public List<Province> findAll() {
        List<Province> all = provinceMapper.findAll();
        return all;
    }

    @Override
    public List<String> findColumn() {
        return provinceMapper.findColumn();
    }
}

controller层

package com.example.excel.controller;

import com.example.excel.Utils.ExcelUtils;
import com.example.excel.entity.Province;
import com.example.excel.service.ProvinceService;
import lombok.SneakyThrows;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.annotation.Resource;
import java.util.List;

@Controller
@RequestMapping("/pro")
public class ProvinceController {

    @Resource
    private ProvinceService provinceService;

    @SneakyThrows
    @RequestMapping("/export")
    @ResponseBody
    public void exportExcel(){
    	//查询省份表的表字段
        List<String> column = provinceService.findColumn();
        //查找省份表的所有数据
        List<Province> all = provinceService.findAll();
        //将数据放入导出的方法中
        ExcelUtils.exportExcel("d:xin.xls", column,all);
    }
}