2 u013226462 u013226462 于 2016.01.25 11:32 提问

java+poi实现下面的格式 2C

图片说明

请问怎么实现框框圈起来的部分??
我的代码如下:
public class ExportToExcelServiceImpl implements ExportToExcelService {
@Autowired
private ExportToExcelDao exportToExcelDao;

public void setExportToExcelDao(ExportToExcelDao exportToExcelDao) {
    this.exportToExcelDao = exportToExcelDao;
}

/**
 * 导出合作渠道
 * 
 * @param HttpServletRequest
 *            request, HttpServletResponse response, String startData,
 *            String endtData
 * @return
 * */
@Override
public void exportChannelToExcel(HttpServletRequest request,
        HttpServletResponse response, String startData, String endtData) {
    /**
     * 取出渠道数据
     * */
    List<CooperationChannel> channelList = exportToExcelDao
            .exportChannelToExcel(startData, endtData);
    // List<CooperationChannel> channelList = exportToExcelDao
    // .exportChannelToExcel();

    /**
     * 创建渠道统计的excel
     * */
    // 下载生成的合作渠道报表到自定义的位置
    try {
        request.setCharacterEncoding("UTF-8");
    } catch (UnsupportedEncodingException e1) {
        e1.printStackTrace();
    }
    response.setCharacterEncoding("UTF-8");
    response.setContentType("application/octet-stream"); // 设置生成的文件类型
    String filedisplay = "合作渠道报表.xls";
    try {
        filedisplay = URLEncoder.encode(filedisplay, "UTF-8");
    } catch (UnsupportedEncodingException e1) {
        e1.printStackTrace();
    }
    response.addHeader("Content-Disposition", "attachment;filename="
            + filedisplay); // 设置文件头编码方式和文件名

    // 创建一个workbook 对应一个excel应用文件
    HSSFWorkbook workBook = new HSSFWorkbook();

    // 在workbook中添加一个sheet,对应Excel文件中的sheet
    CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 15);
    CellRangeAddress cra1 = new CellRangeAddress(1, 1, 0, 4);
    CellRangeAddress cra2 = new CellRangeAddress(1, 1, 5, 9);

// CellRangeAddress cra3 = new CellRangeAddress(1, 1, 10, 15);
HSSFSheet sheet = workBook.createSheet("合作渠道");

    // 在sheet里增加合并单元格
    sheet.addMergedRegion(cra);
    sheet.addMergedRegion(cra1);
    sheet.addMergedRegion(cra2);

// sheet.addMergedRegion(cra3);

    // 获取表格样式
    ExportUtil exportUtil = new ExportUtil(workBook, sheet);
    HSSFCellStyle titleStyle = exportUtil.getTitleStyle();
    HSSFCellStyle titleStyle1 = exportUtil.getTitleStyle1();
    HSSFCellStyle headStyle = exportUtil.getHeadStyle();
    HSSFCellStyle bodyStyle = exportUtil.getBodyStyle();

    // 构建表标题1
    HSSFRow head = sheet.createRow(0);
    HSSFCell cell = null;
    String headTitle = "合作渠道数据统计表";
    cell = head.createCell(0);
    cell.setCellStyle(titleStyle);
    cell.setCellValue(headTitle);

    // 构建表标题2
    HSSFRow head1 = sheet.createRow(1);
     HSSFCell cell1 = null;
     String headTitle1= "部门:市场品牌部";
     cell1 = head1.createCell(5);
     cell1.setCellStyle(titleStyle1);
     cell1.setCellValue(headTitle1);

     // 构建表标题2

// HSSFRow head2 = sheet.createRow(1);
HSSFCell cell2 = null;
String headTitle2= "日期:";
cell2 = head1.createCell(5);
cell2.setCellStyle(titleStyle1);
cell2.setCellValue(headTitle2);

// // 构建表标题2
// HSSFRow head3 = sheet.createRow(1);
// HSSFCell cell3 = null;
// String headTitle3= "单位:元";
// cell3 = head3.createCell(4);
// cell3.setCellStyle(headStyle);
// cell3.setCellValue(headTitle3);

// String[] headTitle1 = new String[] { "部门:市场品牌部", "日期:", "单位:元" };
// for (int i = 0; i < headTitle1.length; i++) {
// cell = head1.createCell(i);
// cell.setCellStyle(titleStyle1);
// cell.setCellValue(headTitle1[i]);
// }

    // 构建表头
    HSSFRow headRow = sheet.createRow(2);
    String[] titles = new String[] { "序号", "渠道名称", "点击量", "下载量", "激活数",
            "注册数", "注册率", "实名数", "实名率", "登录数", "绑卡数", "绑卡率", "投资数", "投资率",
            "充值总额", "投资总额" };
    for (int i = 0; i < titles.length; i++) {
        cell = headRow.createCell(i);
        cell.setCellStyle(headStyle);
        cell.setCellValue(titles[i]);
    }

    // 构建表体数据
    if (channelList != null && channelList.size() > 0) {
        for (int j = 2; j < channelList.size(); j++) {
            HSSFRow bodyRow = sheet.createRow(j + 1);
            CooperationChannel cooperationChannel = channelList.get(j);
            // getChannel_id,并把数据写入excel中的单元格,下同。
            cell = bodyRow.createCell(0);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(cooperationChannel.getChannel_id());
            // getChannel_name
            cell = bodyRow.createCell(1);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(cooperationChannel.getChannel_name());
            // getClick_amount
            cell = bodyRow.createCell(2);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(cooperationChannel.getClick_amount());
            // getDownload_amount
            cell = bodyRow.createCell(3);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(cooperationChannel.getDownload_amount());
            // getActivation_amount
            cell = bodyRow.createCell(4);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(cooperationChannel.getActivation_amount());
            // getRegister_amount
            cell = bodyRow.createCell(5);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(cooperationChannel.getRegister_amount());
            // getRegister_rate
            cell = bodyRow.createCell(6);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(cooperationChannel.getRegister_rate());
            // getAutonym_amount
            cell = bodyRow.createCell(7);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(cooperationChannel.getAutonym_amount());
            // getAutonym_rate
            cell = bodyRow.createCell(8);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(cooperationChannel.getAutonym_rate());
            // getLogin_amount
            cell = bodyRow.createCell(9);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(cooperationChannel.getLogin_amount());
            // getBound_card_amount
            cell = bodyRow.createCell(10);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(cooperationChannel.getBound_card_amount());
            // getBound_card_rate
            cell = bodyRow.createCell(11);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(cooperationChannel.getBound_card_rate());
            // getInvestment_amount
            cell = bodyRow.createCell(12);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(cooperationChannel.getInvestment_amount());
            // getInvestment_rate
            cell = bodyRow.createCell(13);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(cooperationChannel.getInvestment_rate());
            // getRecharge_total_amount
            cell = bodyRow.createCell(14);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(cooperationChannel.getRecharge_total_amount());
            // getInvestment_total_amount
            cell = bodyRow.createCell(15);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(cooperationChannel
                    .getInvestment_total_amount());
        }
    }

    try {
        // FileOutputStream fos = new FileOutputStream(
        // "C:\\Users\\lida\\Desktop\\testData\\fr0_markbran_001.xls");
        OutputStream ops = response.getOutputStream();
        workBook.write(ops);
        ops.flush();
        ops.close();
    } catch (IOException e) {
        e.printStackTrace();
        // } finally {
        // try {
        // // fos.close();
        // } catch (IOException e) {
        // e.printStackTrace();
        // }
    }
}

2个回答

rui888
rui888   Ds   Rxr 2016.01.25 11:56

找到单元格的坐标 ,找到cell 直接往里面填充数据。

u013226462
u013226462   2016.01.25 12:58

谢谢!是我没表达清楚,我想用poi实现上面的样式,换句话说,我想用poi画出上面的格式,并非想知道怎么往cell里set value。
该问题已解决,如网友遇到同类问题,可联系我。

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!