将多个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();
        }
        
    }
    
}

转载于:https://www.cnblogs.com/TankRuning/p/4351262.html