在日常工作中， 常常需要收集统计一些数据， 然后整理到excel， 这种重复性的操作可以自己写个工具来实现。 采用HtmlUnitDriver 访问页面， 抓取数据， 再把数据列表通过调用POI放到excel。 这里先把操作excel 操作部分抽取出来， 拿到数据后， 可以直接调用该类实现存取操作。

package com.rc.qa.base.utils;

import java.io.FileOutputStream;

import java.util.Calendar;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.CreationHelper;

import org.apache.poi.ss.usermodel.Font;

import org.apache.poi.ss.usermodel.IndexedColors;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.apache.poi.ss.usermodel.Hyperlink;

/**

 * @author jennifer.huang

 * @date 2014/10/23

 *

 */

public class ExcelOperation {

    private Workbook wb;

    private CreationHelper createHelper;

    private Sheet sheet;

    private CellStyle titleCellStyle,dataCellStyle,linkCellStyle;

    private int rowHeight;

    public ExcelOperation() {

        init();

    }

    public ExcelOperation(String []columns){

        init();

        this.createTitleRow(columns);

    }

    public ExcelOperation(String []columns,CellStyle titleCellStyle){

        init();

        this.createTitleRow(columns, titleCellStyle);

    }

    private void init(){

        wb = new XSSFWorkbook();

        createHelper = wb.getCreationHelper();

        sheet = wb.createSheet();

        this.createTitleStyle();

        this.createDataStyle();

        this.createLinkStyle();

        rowHeight = 400;

    }

    /**

     * createTitleRow

     * row: excel first rowId=0. createRow((short)0)

     * @param columns

     */

    public void createTitleRow(String []columns){

        createTitleRow(columns, titleCellStyle);

    }

    public void createTitleRow(String []columns,CellStyle titleCellStyle){

        sheet.createFreezePane( 0, 1, 0, 1 );

        setColumnWidth(columns);

        Row row = sheet.createRow((short)0);

        setRowHeight(row,rowHeight);

        for(int i=0;i<columns.length;i++){

            createCell(row, i, columns[i], titleCellStyle);

        }

    }

    /**

     * createDataRow

     * @param rowId (can start from 1)

     * @param columns

     */

    public void createDataRow(int rowId,String []columns){

        createDataRow(rowId,columns,dataCellStyle);

    }

    public void createDataRow(int rowId, String []columns,CellStyle dataCellStyle){

        setColumnWidth(columns);

        Row tmpRow = sheet.createRow((short)rowId);

        setRowHeight(tmpRow,rowHeight);

        for(int i=0;i<columns.length;i++){

            createCell(tmpRow, i, columns[i], dataCellStyle);

        }

    }

    /**

     * setHylinkForCell

     * @param rowId

     * @param columnId

     * @param link

     */

    public void setHylinkForCell(int rowId, int columnId,Hyperlink link){

        setHylinkForCell(rowId, columnId, link, linkCellStyle);

    }

    public void setHylinkForCell(int rowId, int columnId,Hyperlink link,CellStyle linkCellStyle){

        Row row = sheet.getRow(rowId);

        Cell cell = row.getCell(columnId);

        cell.setHyperlink(link);

        cell.setCellStyle(linkCellStyle);

    }

    /**

     * saveToExcel

     * @param savePath

     */

    public void saveToExcel(String savePath){

          Calendar c = Calendar.getInstance();

          FileOutputStream fileOut;

          try {

              fileOut = new FileOutputStream(String.format(savePath+"\\Tasks_%d-%d-%d-%d-%d.xlsx", c.get(Calendar.YEAR), c.get(Calendar.MONTH)+1, c.get(Calendar.DATE), c.get(Calendar.HOUR_OF_DAY), c.get(Calendar.MINUTE)));

              wb.write(fileOut);

              fileOut.close();

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

    private void setColumnWidth(String []columns){

        for(int i=0;i<columns.length;i++){

            sheet.autoSizeColumn((short)i);

        }

    }

    private void setRowHeight(Row row, int height){

        row.setHeight((short)height);

    }

    private CellStyle createTitleStyle(){

        titleCellStyle = wb.createCellStyle();

        Font titleFont = wb.createFont();

        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

        titleFont.setFontHeightInPoints((short)12);

        titleCellStyle.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());

        titleCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

        titleCellStyle.setBorderBottom(CellStyle.BORDER_THIN);

        titleCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());

        titleCellStyle.setBorderLeft(CellStyle.BORDER_THIN);

        titleCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());

        titleCellStyle.setBorderRight(CellStyle.BORDER_THIN);

        titleCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

        titleCellStyle.setBorderTop(CellStyle.BORDER_THIN);

        titleCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());

        titleCellStyle.setAlignment(CellStyle.VERTICAL_CENTER);

        titleCellStyle.setFont(titleFont);

        return titleCellStyle;

    }

    private CellStyle createDataStyle(){

        dataCellStyle = wb.createCellStyle();

        dataCellStyle.setBorderBottom(CellStyle.BORDER_THIN);

        dataCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());

        dataCellStyle.setBorderLeft(CellStyle.BORDER_THIN);

        dataCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());

        dataCellStyle.setBorderRight(CellStyle.BORDER_THIN);

        dataCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

        dataCellStyle.setBorderTop(CellStyle.BORDER_THIN);

        dataCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());

        dataCellStyle.setAlignment(CellStyle.VERTICAL_CENTER);

        return dataCellStyle;

    }

    private CellStyle createLinkStyle(){

        linkCellStyle = wb.createCellStyle();

        linkCellStyle.cloneStyleFrom(dataCellStyle);

        Font hlink_font = wb.createFont();

        hlink_font.setUnderline(Font.U_SINGLE);

        hlink_font.setColor(IndexedColors.BLUE.getIndex());

        linkCellStyle.setFont(hlink_font);

        return linkCellStyle;

    }

    private Cell createCell(Row row, int cellId, String cellValue, CellStyle cellStyle){

        Cell cell = row.createCell((short)cellId);

        cell.setCellValue(cellValue);

        cell.setCellStyle(cellStyle);

        return cell;

    }

    public Workbook getWb() {

        return wb;

    }

    public void setWb(Workbook wb) {

        this.wb = wb;

    }

    public CreationHelper getCreateHelper() {

        return createHelper;

    }

    public void setCreateHelper(CreationHelper createHelper) {

        this.createHelper = createHelper;

    }

    public Sheet getSheet() {

        return sheet;

    }

    public void setSheet(Sheet sheet) {

        this.sheet = sheet;

    }

    public CellStyle getTitleCellStyle() {

        return titleCellStyle;

    }

    public void setTitleCellStyle(CellStyle titleCellStyle) {

        this.titleCellStyle = titleCellStyle;

    }

    public CellStyle getDataCellStyle() {

        return dataCellStyle;

    }

    public void setDataCellStyle(CellStyle dataCellStyle) {

        this.dataCellStyle = dataCellStyle;

    }

    public CellStyle getLinkCellStyle() {

        return linkCellStyle;

    }

    public void setLinkCellStyle(CellStyle linkCellStyle) {

        this.linkCellStyle = linkCellStyle;

    }

    public int getRowHeight() {

        return rowHeight;

    }

    public void setRowHeight(int rowHeight) {

        this.rowHeight = rowHeight;

    }

}

上面已经默认对excel 风格做了初始化， 可以直接调用做保存操作：

    String []columns=new String[]{"Backend User Story","Test Case Key","User Stories","Site","Priority","Automation Keyword"};

    ExcelOperation excelOperation = new ExcelOperation(columns);

    public void saveToExcel(String savePath,List<Task> tasks){

        Hyperlink link = excelOperation.getCreateHelper().createHyperlink(Hyperlink.LINK_URL);

        int i=1;

         for(Task task:tasks){

             String []dataColumns=new String[]{task.getMainKeyword(),task.getTestCaseKey(),task.getUserStories(), task.getSite(),task.getPriority(),task.getAutomationStatus()};

             link.setAddress(String.format(testCaseURL, task.getTestCaseId()));

             excelOperation.createDataRow(i, dataColumns);

             excelOperation.setHylinkForCell(i, 1, link);

             i++;

            }

         excelOperation.saveToExcel(savePath);

    }

POI需要的jar包：
POI3.7

poi-3.7-20101029.jar

poi-examples-3.7-20101029.jar

poi-ooxml-3.7-20101029.jar

poi-ooxml-schemas-3.7-20101029.jar

poi-scratchpad-3.7-20101029.jar

最新文章

  1. 纪念品分组 2007年NOIP全国联赛普及组
  2. 2008ISBN号码
  3. APP定制开发：APP软件开发的语言和开发环境
  4. WPF中viewmodel层怎样得到view层的TabControl控件对象？
  5. 高效线程池(threadpool)的实现
  6. storyBoard中的Segue跳转
  7. 通过js根据后台数据动态生成一个页面
  8. （转载）Excel文档保存的时候，提示“文档未保存”
  9. erlang 练手 进程环
  10. BZOJ 2330 SCOI 2011 糖果
  11. 多进程用户并发处理Demo（C#版）
  12. UVa 1608，Non-boring sequences
  13. bestcoder round 74 div2
  14. 003 win7如何配置adb环境变量
  15. 【Alpha】项目展示
  16. Android里透明的ListView
  17. python之成员(面向对象)
  18. GIT中常用的命令
  19. FP ABPPMGR表 其它常用存储过程
  20. ASP.NET Web API实践系列01,以ASP.NET Web Form方式寄宿

热门文章

  1. xmlBean学习二
  2. 图片攻击-BMP图片中注入恶意JS代码 &lt;转载&gt;
  3. cocos2dx 3.0 触摸机制
  4. 使用TopShelf轻松开发Window服务
  5. pjsip视频通信开发(上层应用)之数字键盘的制作
  6. 使用导入导出进行备份和恢复OCR（10g）
  7. Discuz x 默认模板文件目录说明
  8. 10891 - Game of Sum
  9. DOM+Javascript一些实例
  10. [转]Maintain File Upload Control on Postbacks