java的表格导出功能

java的表格导出功能

本文参考http://blog.csdn.net/evangel_z/article/details/7332535，如果有问题，请与我联系。

需要的扩展包

扩展包下载地址：https://pan.baidu.com/s/1c1ZEndu

代码块

示例：

/**
 * 一个简单的model类
 * @author zj
 *
 */
public class Student {

    private String id;
    private String name;
    private String sex;
    private String age;
    private String action;


    public Student() {
        super();
    }
    public Student(String id, String name, String sex, String age, String action) {
        super();
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.age = age;
        this.action = action;
    }

    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public String getAge() {
        return age;
    }
    public void setAge(String age) {
        this.age = age;
    }
    public String getAction() {
        return action;
    }
    public void setAction(String action) {
        this.action = action;
    }

    @Override
    public String toString() {
        return "Student [id=" + id + ", name=" + name + ", sex=" + sex + ", age=" + age + ", action=" + action + "]";
    }
}





import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import com.java.model.Student;

/**
 * 导出表格示例
 * 导出表格的方法体主体
 * @author ZJ
 *
 */
public class ExcelService {

    //传进来一个model类的list列表（表格数据的来源），输出流对象（决定你的文件导出在何处）
    @SuppressWarnings("unused")
    public void exportXlsx(List<Student> list, OutputStream out) {

        //声明一个输入流对象
        InputStream ins = null;

        //poi中专门用来处理大量数据写入Excel的类
        SXSSFWorkbook workbook = null;

        try {

            //关闭流
            if (ins!=null) {
                try {
                    ins.close();
                    ins = null;
                } catch (Exception e) {
                    System.out.println(e.getMessage());
                }
            }

            //设置最大内存缓存行
            workbook = new SXSSFWorkbook(100);
            //声明并获得工作表对象
            Sheet sheet = workbook.createSheet();

            //单元格行坐标（设置为1导不出表格，具体原因未知）
            int intRow = 2;

            //单元格行对象
            Row row = null;

            //单元格纵坐标
            int intCol = 0;

            if(list != null){

                //获取单元格行对象
                row = sheet.createRow(0);

                //标题字段字符串数组
                String[] titleinit={"学号","姓名","性别","年龄","动作"};
                //行宽数组
                int[] widthtitle={3000,3000,3000,3000,3000};

                row = sheet.createRow(1);
                //设置标题行表格样式（颜色那些......）
                CellStyle title = workbook.createCellStyle();
                title.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
                title.setFillPattern(CellStyle.SOLID_FOREGROUND);
                Cell titleCell1 = null;

                //插入表格标题并设置行宽
                for(int i=0;i<titleinit.length;i++){

                    titleCell1=row.createCell(i);
                    titleCell1.setCellValue(titleinit[i]);
                    titleCell1.setCellStyle(title);
                    sheet.setColumnWidth(i, widthtitle[i]);
                }

                //插入数据
                for(int i = 0; i<list.size(); i++) {

                    intCol = 0;
                    row = sheet.createRow(intRow++);

                    // 学号
                    row.createCell(intCol++).setCellValue(list.get(i).getId());
                    // 姓名
                    row.createCell(intCol++).setCellValue(list.get(i).getName());
                    // 性别
                    row.createCell(intCol++).setCellValue(list.get(i).getSex());
                    // 年龄
                    row.createCell(intCol++).setCellValue(list.get(i).getAge());
                    // 动作
                    row.createCell(intCol++).setCellValue(list.get(i).getAction());
                }
            }

            //文件保存
            workbook.write(out);
            out.flush();
        } catch (Throwable e) {

            System.out.println("文件导出失败, throw Exception " + e.getMessage());
        } finally {

            // 关闭流
            if (ins!=null) {
                try {
                    ins.close();
                } catch (Exception e) {
                    System.out.println(e.getMessage());
                }
            }
        }
    }
}




import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import com.java.model.Student;
import com.java.service.ExcelService;

public class Test2 {

    public static void main(String[] args) {

        List<Student> list = new ArrayList<Student>();
        ExcelService excelService = new ExcelService();

        Student s1 = new Student("1001", "张三", "男", "20", "坐公交车");
        Student s2 = new Student("1002", "李四", "女", "20", "骑自行车");
        Student s3 = new Student("1003", "王五", "男", "21", "自己开车");

        list.add(s1);
        list.add(s2);
        list.add(s3);

        try {

            //声明一个输出流对象，设置文件导出存放路径
            OutputStream out = new FileOutputStream("C://Users//Desktop//Test.xlsx");
            excelService.exportXlsx(list, out);
        } catch (Exception e) {

            System.out.println("文件导出失败:" + e.getMessage());
        }
    }

}