HSSFWorkbook 创建Excel文件

1.项目代码实例

　　

@Override

    public OutputStream exportAucLotData(String id, String password, OutputStream out) throws Exception {

        if (id == null)

            return null;

        if (StringUtils.isBlank(password))

            password = "123456";

        if (out == null)

            return null;

        AucLot lot=    aucLotRepository.findOne(id);

        if(lot==null)

            return null;

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

        List<Object> sheet1 =sheet1Data(lot,sdf);//标题下面的数据

        List<AucPrice> ps=aucPriceRepository.findAllByAucLotIdOrderByIdDesc(lot.id());

        List<Object> sheet2 =sheet2Data(sdf, ps);

        List<AucBrand>  bs=aucBrandRepository.findAllByAucLotIdOrderByCrtTimeDesc(lot.id());

        List<Object> sheet3 = sheet3Data(sdf, bs);

        logger.debug(sheet1.toString());

        logger.debug(sheet2.toString());

        logger.debug(sheet3.toString());

        HSSFWorkbook book = new HSSFWorkbook();

        book = createExcel(book, sheet1Heads(), sheet1Fileds(), sheet1, "拍卖信息");

        book = createExcel(book, sheet2Heads(), sheet2Fileds(), sheet2, "出价信息");

        book = createExcel(book, sheet3Heads(), sheet3Fileds(), sheet3, "报名信息");

        File tempFile=File.createTempFile("temp",".xls");

        tempFile.deleteOnExit();

        OutputStream tempout = new FileOutputStream(tempFile);

        book.write(tempout);

　　　　//下载Excel ，打开的时候进行加密处理

        EncryptExcellUtil.encrypt(tempFile,password, out);

        logger.debug("-----excel 文档加密 ok-----------");

        return out;

    }    

2.设置表格的题头

private static List<String>  sheet1Heads(){

        List<String> heads = new ArrayList<String>();

        heads.add("标的内部代码");

        heads.add("拍卖名称");

        heads.add("标的大类名称");

        heads.add("拍卖开始时间");

        heads.add("拍卖结束时间");

        heads.add("保证金");

        heads.add("拍卖模式");

        heads.add("上拍阶段");

        heads.add("拍卖状态");

        heads.add("发布状态");

        heads.add("所属机构");

        heads.add("城市名称");

        return  heads;

    }

3.获取题头下面对应的数据

private  List<Object>   sheet1Data(AucLot lot,SimpleDateFormat sdf){

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

        Map<String, String> map = new HashMap<String, String>();

        List<String> key = sheet1Fileds();

        map.put(key.get(0), lot.goodsNo());// goodsNo

        map.put(key.get(1), lot.goodsName());// goodsName

        map.put(key.get(2), lot.firstTypeName());// firstTypeName

        map.put(key.get(3), (lot.startTime() != null) ? sdf.format(lot.startTime()) : "");// startTime

        map.put(key.get(4), (lot.endTime() != null) ? sdf.format(lot.endTime()) : "");// endTime

        map.put(key.get(5), (lot.bail() != null) ? lot.bail().toString() : "");// bail

        map.put(key.get(6), (lot.type() == 1) ? "网络拍" : (lot.type() == 2 ? "同步拍" : ""));

        map.put(key.get(7), stage(lot.stage()));// stage

        map.put(key.get(8), status(lot.status()));// status

        map.put(key.get(9), (lot.isPublished() == 1) ? "未发布" : "已发布");// isPublished

        map.put(key.get(10), agencyName(lot.agencyId()));// agencyId to name

        map.put(key.get(11), lot.cityName());// cityName

        data.add(map);

        return data;

    }

4.???

private static List<String>  sheet1Fileds(){

        List<String> fileds = new ArrayList<String>();

        fileds.add("goodsNo");

        fileds.add("goodsName");

        fileds.add("firstTypeName");

        fileds.add("startTime");

        fileds.add("endTime");

        fileds.add("bail");

        fileds.add("type");

        fileds.add("stage");

        fileds.add("status");

        fileds.add("isPublished");

        fileds.add("agencyId");

        fileds.add("cityName");

        return  fileds;

    }

5.创建Excel表格对象

public  HSSFWorkbook createExcel(HSSFWorkbook book,

            List<String> heads, List<String> fieldList, List<Object> dataList,String sheettName) {

        HSSFWorkbook  workbook = book;

        if(workbook ==null)

            workbook= new HSSFWorkbook();

        if(sheettName==null||"".equals(sheettName))

            sheettName="sheet"+Math.random();

        HSSFSheet sheet = workbook.createSheet(sheettName); //

        sheet.setDefaultColumnWidth(20);

        sheet.setDefaultRowHeightInPoints(20);

        // 在索引0的位置创建行（最顶端的行）

        HSSFRow row = sheet.createRow(0);

        HSSFCell cell=null;

       //创建表格第一行的标题

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

            // 在索引0的位置创建单元格（左上端）

            cell = row.createCell(i);

            // 定义单元格为字符串类型

            cell.setCellType(HSSFCell.CELL_TYPE_STRING);

            // 在单元格中输入一些内容

            cell.setCellValue(heads.get(i));

        }

　　　　//循环赋值

        for (int n = 0; n < dataList.size(); n++) {

            // 在索引1的位置创建行（最顶端的行）

            HSSFRow row_value = sheet.createRow(n + 1);

            Map<String, String> dataMap =(Map<String, String>)dataList.get(n);

            HSSFCell cell_v=null;

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

                // 在索引0的位置创建单元格（左上端）

                cell_v = row_value.createCell(i);

                // 定义单元格为字符串类型

                cell_v.setCellType(HSSFCell.CELL_TYPE_STRING);

                // 在单元格中输入一些内容

                cell_v.setCellValue(dataMap.get(fieldList.get(i)));

            }

        }

        return  workbook ;

    }