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 ;
    }