将多个Sheet导入到同一个Excel文件中

实体类excel

import java.util.List;

/**

 * 功能:

 * 描述:

 * @author

 * @date 2015-3-19 下午5:15:48

 */

public class Excel {

    private String fileName;//sheet的名称

    private String[] handers;//sheet里的标题

    private List<String[]>  dataset;//sheet里的数据集

    /**

     *

     */

    public Excel(String fileName,String[] handers,List<String[]> dataset)

    {

        this.fileName = fileName;

        this.handers = handers;

        this.dataset = dataset;

    }

    public String getFileName() {

        return fileName;

    }

    public void setFileName(String fileName) {

        this.fileName = fileName;

    }

    public String[] getHanders() {

        return handers;

    }

    public void setHanders(String[] handers) {

        this.handers = handers;

    }

    public List<String[]> getDataset() {

        return dataset;

    }

    public void setDataset(List<String[]> dataset) {

        this.dataset = dataset;

    }

}

具体实现

import java.io.File;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.OutputStream;

import java.util.ArrayList;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**

 * 功能:将多个sheet导出到同一个出excel表格中

 * 描述:

 * @author

 * @date 2015-3-19 下午5:20:20

 */

public class test {

    public static void main(String[] args) {

        String data1[] = {"111","2222","3333","44444"};

        String data2[] = {"111","2222","3333","44444","5555"};

        String handers1[] = {"一","二","三","四","五"};

        String handers2[] = {"one","two","three","four","five"};

        String handers3[] = {"车辆","毛衣","手机","茶杯","笔记本"};

        List<String[]> dataset = new ArrayList<String[]>();

        dataset.add(data1);

        dataset.add(data2);

        Excel e1 = new Excel("第一个sheet", handers1, dataset);

        Excel e2 = new Excel("第二个sheet", handers2, dataset);

        Excel e3 = new Excel("第三个sheet", handers3, dataset);

        List<Excel> mysheet = new ArrayList<Excel>();

        mysheet.add(e1);

        mysheet.add(e2);

        mysheet.add(e3);

        test2(mysheet);

    }

    private static void test(){

        List<Object> list = new ArrayList<Object>();

        String[] handers = {"1","2","3","4","5"};

        HSSFWorkbook wb = new HSSFWorkbook();//创建工作薄

        HSSFSheet sheet = wb.createSheet("第一个sheet");//第一个sheet

        HSSFRow rowFirst = sheet.createRow(0);//第一个sheet的第一行为标题

        //写标题

        for(int i=0;i<handers.length;i++){

            //获取第一行的每个单元格

            HSSFCell cell = rowFirst.createCell(i);

            //往单元格里写数据

            cell.setCellValue(handers[i]);

        }

        //写数据集

        //假定数据集诗歌list集合

        for(int i=0;i<list.size();i++){

            //获取list里面存在是数据集对象

            Object obj = list.get(i);

            //创建数据行

            HSSFRow row = sheet.createRow(i+1);

            //设置对应单元格的值

            row.createCell(0).setCellValue("obj 的属性0");

            row.createCell(1).setCellValue("obj 的属性1");

            row.createCell(2).setCellValue("obj 的属性2");

            row.createCell(3).setCellValue("obj 的属性3");

            row.createCell(4).setCellValue("obj 的属性4");

        }

        //写文件

        try {

            OutputStream os = new FileOutputStream(new File("filepath"));

            wb.write(os);

        } catch (FileNotFoundException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        } catch (IOException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

    }

    /**

     * 具体sheet的操作

     * @author

     * @date 2015-3-19下午6:12:57

     * @param mysheets void

     */

    private static void test2(List<Excel> mysheets){

        HSSFWorkbook wb = new HSSFWorkbook();//创建工作薄

        List<Excel> sheets = mysheets;

        for(Excel excel:sheets){

            //新建一个sheet

            HSSFSheet sheet = wb.createSheet(excel.getFileName());//获取该sheet名称

            String[] handers = excel.getHanders();//获取sheet的标题名

            HSSFRow rowFirst = sheet.createRow(0);//第一个sheet的第一行为标题

            //写标题

            for(int i=0;i<handers.length;i++){

                //获取第一行的每个单元格

                HSSFCell cell = rowFirst.createCell(i);

                //往单元格里写数据

                cell.setCellValue(handers[i]);

            }

            //写数据集

            List<String[]> dataset = excel.getDataset();

            for(int i=0;i<dataset.size();i++){

                String[] data = dataset.get(i);//获取该对象

                //创建数据行

                HSSFRow row = sheet.createRow(i+1);

                for(int j=0;j<data.length;j++){

                    //设置对应单元格的值

                    row.createCell(j).setCellValue(data[i]);

                }

            }

        }

        // 写文件

        try {

            OutputStream os = new FileOutputStream(new File("D://test.xls"));

            wb.write(os);

        } catch (FileNotFoundException e) {

            e.printStackTrace();

        } catch (IOException e) {

            e.printStackTrace();

        }

    }

}