POI tools 参数化生成excel表格

最新推荐文章于 2024-04-29 17:45:56 发布

aolv3500

最新推荐文章于 2024-04-29 17:45:56 发布

阅读量111

点赞数

文章标签： java

原文链接：http://www.cnblogs.com/nhz-M/p/7679445.html

版权

package com.eccom.neteagle.server.confsave.service.impl;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;


public class ProduceSheet {
    
    /*
     * 
     * 配置：表名 、项宽、列名、路径、参数、表数据 即可生成
     * 
     * @author Na
     * 
    */
    private HSSFWorkbook wb = new HSSFWorkbook();
    
    private HSSFCellStyle styletop = wb.createCellStyle();
    
    private HSSFCellStyle stylebody = wb.createCellStyle();
    
    private HSSFFont fonttop = wb.createFont();//表头样式
    
    private HSSFFont fontbody = wb.createFont();//表体样式
    
    private HSSFSheet sheet;
    
    private HSSFRow row;
    

    private String excelName="NONAME";//表名
    
    private String pathName = "NOPATH";//存放路径
    
    private List<List<String>> datas;//表体数据
    
    List<String> columnWidth_list ;//调整的列宽参数
    
    List<String> columnName_list ;//列头名称
    
    //List<String> columnParams;//列参数
    
    public void initSheet(String excelName,String pathName,List<List<String>> datas,List<String> columnWidth_list,List<String> columnName_list){
        this.excelName = excelName;
        this.pathName = pathName;
        this.datas = datas;
        this.columnName_list = columnName_list ;
        this.columnWidth_list = columnWidth_list;
//        this.columnParams = columnParams;
        initClass();
    }

    /**
     * 
     * @描述：初始化表的宽度，名称等
     * 
     * */
    public void createSheel(){
        setName();
        setCellwidth();
        setColumnName();
        assignment();
        setSavePath();
        drawTable();
    }
    
    /**
     * @描述：表的样式初始化 可以自调
     * 
     * */
    private void initClass() {
        //设置表头字体
        fonttop.setFontName("黑体");
        fonttop.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        fonttop.setFontHeightInPoints((short) 10);
        //设置表体字体
        fontbody.setFontName("Arial");
        fontbody.setFontHeightInPoints((short) 10);
        //设置表头边框
        styletop.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中
        styletop.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
        styletop.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
        styletop.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
        styletop.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
        styletop.setFont(fonttop);// 设置字体
        styletop.setFillForegroundColor(IndexedColors.TAN.getIndex()); // 表头颜色
        styletop.setFillPattern(CellStyle.SOLID_FOREGROUND); // 设置显示前置
        //设置表体边框
        stylebody.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中
        stylebody.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
        stylebody.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
        stylebody.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
        stylebody.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
        stylebody.setFont(fontbody);// 设置字体
    }
    private void setName(){
        setSheet(wb.createSheet(excelName)); 
    }
    /**
     * 
     * @描述：初始化每一列的宽度
     * 
     * */
    private void setCellwidth(){
        try {
            for(int i=0,s=columnWidth_list.size();i<s;i++){
                int length = 256*Integer.parseInt(columnWidth_list.get(i));
                System.out.println("长度："+length);
                getSheet().setColumnWidth(i,length);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 
     * @描述：初始化每一个列名称
     * 
     * */
    private void setColumnName(){
        setRow(getSheet().createRow((int) 0));
        HSSFCell cell ;
            for(int i=0,s=columnName_list.size();i<s;i++){
                cell = getRow().createCell(i);
                System.out.println("ColumnName :"+columnName_list.get(i));
                cell.setCellValue(columnName_list.get(i));  
                cell.setCellStyle(styletop);
                
            }
    }
    /**
     * 
     * @描述：从datas中逐行赋值
     * 
     * */
    private void assignment(){
        for(int i=0,length=getDatas().size();i<length;i++){
            setRow(getSheet().createRow((int) i + 1)); 
            for(int j=0,length0=getDatas().get(i).size();j<length0;j++){
                getRow().createCell(j).setCellStyle(stylebody);
                getRow().getCell(j).setCellValue(getDatas().get(i).get(j));
                System.out.println(getDatas().get(i).get(j) + " ");
            }
        }
    }
    /**
     * 
     * @描述：绘制表
     * 
     * */
    private void drawTable(){
        try {
            FileOutputStream fout;
            fout = new FileOutputStream(getPathName());
            wb.write(fout);  
            fout.close(); 
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch(IOException e){
            e.printStackTrace();
        }
    }
    /**
     * 
     * @描述：设置表名
     * 
     * */
    public String getPathName() {
        return pathName;
    }
    /**
     * 
     * @描述：设置存放路径 -> 表名 + 时间
     * 
     * */
    private void setSavePath(){
        SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd_HHmmss");
        //String[] paths = getPathName().split("-");
        //String filepath =SysConfig.getInstance().path+File.separator;
//        for (int i = 0; i < paths.length-1; i++) {
//            filepath =filepath+paths[i]+File.separator;
//        }
//        filepath = filepath.substring(0,filepath.length()- 14);
        //filepath = filepath +getExcelName()+ df.format(new Date())+".xls";
        String filepath =  "d:"+File.separator+"info";
        judeDirExists(filepath);
        filepath +=File.separator+df.format(new Date())+".xls";
        System.out.println("path:"+filepath);
        setPathName(filepath);
    }
    
    public void setPathName(String pathName) {
        this.pathName = pathName;
    }

    //判断路径是否存在 没有就创建
    private  void judeDirExists(String file_path) {
        File dir =  new File(file_path);
        if (dir.exists()) {
        }else{
            dir.mkdir();
        }
     }
    

    public List<List<String>> getDatas() {
        return datas;
    }
    
    public HSSFSheet getSheet() {
        return sheet;
    }

    public void setSheet(HSSFSheet sheet) {
        this.sheet = sheet;
    }
    
    public HSSFRow getRow() {
        return row;
    }

    public void setRow(HSSFRow row) {
        this.row = row;
    }
}

test main:

public static void main(String[] args) {
        List<List<String>>  datas = new ArrayList<List<String>>();
        for(int i=0;i<10;i++){
            List<String> list0 = new ArrayList<String>();
            for(int j=0;j<6;j++){
                list0.add("ok"+i+j);
            }
            datas.add(list0);
        }
        for(List<String> s:datas){
            for(String r :s){
                System.out.println(r +" ");
            }
        }
        ProduceSheet sheet = new ProduceSheet();
        String excelName = "配置下发准备" ;
        String pathName = "c:/" ;
        List<String> columnWidth_list = new ArrayList<String>();
        columnWidth_list.add("10");
        columnWidth_list.add("10");
        columnWidth_list.add("10");
        columnWidth_list.add("10");
        columnWidth_list.add("10");
        columnWidth_list.add("10");
        List<String> columnName_list = new ArrayList<String>();
        //变更名称      任务名称    设备    变更命令    验证命令    回退命令
        columnName_list.add("变更名称");
        columnName_list.add("任务名称");
        columnName_list.add("设备");
        columnName_list.add("变更命令");
        columnName_list.add("验证命令");
        columnName_list.add("回退命令");
        sheet.initSheet(excelName, pathName, datas,columnWidth_list, columnName_list );
        sheet.createSheel();
    }