Example usage for org.apache.poi.ss.usermodel Row createCell

List of usage examples for org.apache.poi.ss.usermodel Row createCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. createCell

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Row createCell.

Prototype

Cell createCell(int column);

Source Link

Document

Use this to create new cells within the row and return it.

Usage

From source file:com.mycompany.gannaraputakehomeexam.WritingToExcel.java

public void writeSongsToExcel(List<SongsList> songList) {

    /*//from   w  w w  .  j  av  a 2  s .  c o m
    Use XSSF for xlsx format and for xls use HSSF
    */
    Workbook workbook = new XSSFWorkbook();

    /*
    create new sheet 
    */
    Sheet songsSheet = workbook.createSheet("Gannarapu_Output");

    XSSFCellStyle my_style = (XSSFCellStyle) workbook.createCellStyle();
    /* Create XSSFFont object from the workbook */
    XSSFFont my_font = (XSSFFont) workbook.createFont();
    XSSFFont font = (XSSFFont) workbook.createFont();

    /*
    setting cell color
    */
    CellStyle style = workbook.createCellStyle();
    style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);

    /*
     setting Header color
    */
    CellStyle style2 = workbook.createCellStyle();
    font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    style2.setFont(font);
    style2.setAlignment(CellStyle.ALIGN_CENTER);
    //   style2.setFillForegroundColor(IndexedColors.DARK_RED.getIndex());
    //   style2.setFillPattern(CellStyle.SOLID_FOREGROUND);
    //            

    Row rowName = songsSheet.createRow(1);

    /*
    Merging the cells
    */
    songsSheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 3));

    /*
    Applying style to attribute name
    */
    int nameCellIndex = 1;
    Cell namecell = rowName.createCell(nameCellIndex++);
    namecell.setCellValue("Name");
    namecell.setCellStyle(style);

    Cell cel = rowName.createCell(nameCellIndex++);
    cel.setCellValue("Gannarapu, Anirudh");

    /*
    Applying underline to Name
    */
    my_font.setUnderline(XSSFFont.U_DOUBLE);
    my_style.setFont(my_font);
    /* Attaching the style to the cell */
    CellStyle combined = workbook.createCellStyle();
    combined.cloneStyleFrom(my_style);
    combined.cloneStyleFrom(style);
    cel.setCellStyle(combined);

    /*
    Applying  colors to header 
    */

    Row rowMain = songsSheet.createRow(3);
    SheetConditionalFormatting sheetCF = songsSheet.getSheetConditionalFormatting();
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("5");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.RED.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A4:F4") };

    sheetCF.addConditionalFormatting(regions, rule1);

    /*
    setting new rule to apply alternate colors to cells having same Genre
    */
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule("4");
    PatternFormatting fill2 = rule2.createPatternFormatting();
    fill2.setFillBackgroundColor(IndexedColors.LEMON_CHIFFON.index);
    fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regionsAction = { CellRangeAddress.valueOf("A5:F5"), CellRangeAddress.valueOf("A6:F6"),
            CellRangeAddress.valueOf("A7:F7"), CellRangeAddress.valueOf("A8:F8"),
            CellRangeAddress.valueOf("A13:F13"), CellRangeAddress.valueOf("A14:F14"),
            CellRangeAddress.valueOf("A15:F15"), CellRangeAddress.valueOf("A16:F16"),
            CellRangeAddress.valueOf("A23:F23"), CellRangeAddress.valueOf("A24:F24"),
            CellRangeAddress.valueOf("A25:F25"), CellRangeAddress.valueOf("A26:F26")

    };

    /*        
    setting new rule to apply alternate colors to cells having same Genre
     */
    ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule("4");
    PatternFormatting fill3 = rule3.createPatternFormatting();
    fill3.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);
    fill3.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regionsAdv = { CellRangeAddress.valueOf("A9:F9"), CellRangeAddress.valueOf("A10:F10"),
            CellRangeAddress.valueOf("A11:F11"), CellRangeAddress.valueOf("A12:F12"),
            CellRangeAddress.valueOf("A17:F17"), CellRangeAddress.valueOf("A18:F18"),
            CellRangeAddress.valueOf("A19:F19"), CellRangeAddress.valueOf("A20:F20"),
            CellRangeAddress.valueOf("A21:F21"), CellRangeAddress.valueOf("A22:F22"),
            CellRangeAddress.valueOf("A27:F27"), CellRangeAddress.valueOf("A28:F28"),
            CellRangeAddress.valueOf("A29:F29") };

    /*
    Applying above created rule formatting to cells
    */
    sheetCF.addConditionalFormatting(regionsAction, rule2);
    sheetCF.addConditionalFormatting(regionsAdv, rule3);

    /*
     Setting coloumn header values
    */
    int mainCellIndex = 0;

    Cell SNO = rowMain.createCell(mainCellIndex++);
    SNO.setCellValue("SNO");
    SNO.setCellStyle(style2);
    Cell gen = rowMain.createCell(mainCellIndex++);
    gen.setCellValue("Genre");
    gen.setCellStyle(style2);
    Cell credit = rowMain.createCell(mainCellIndex++);
    credit.setCellValue("Credit Score");
    credit.setCellStyle(style2);
    Cell name = rowMain.createCell(mainCellIndex++);
    name.setCellValue("Album Name");
    name.setCellStyle(style2);
    Cell art = rowMain.createCell(mainCellIndex++);
    art.setCellValue("Artist");
    art.setCellStyle(style2);
    Cell release = rowMain.createCell(mainCellIndex++);
    release.setCellValue("Release Date");
    release.setCellStyle(style2);

    /*
    populating cell values
    */
    int rowIndex = 4;
    int sno = 1;
    for (SongsList song : songList) {
        if (song.getSno() != 0) {

            Row row = songsSheet.createRow(rowIndex++);
            int cellIndex = 0;

            /*
            first place in row is Sno
            */
            row.createCell(cellIndex++).setCellValue(sno++);

            /*
            second place in row is  Genre
            */
            row.createCell(cellIndex++).setCellValue(song.getGenre());

            /*
            third place in row is Critic score
            */
            row.createCell(cellIndex++).setCellValue(song.getCriticscore());

            /*
            fourth place in row is Album name
            */
            row.createCell(cellIndex++).setCellValue(song.getAlbumname());

            /*
            fifth place in row is Artist
            */
            row.createCell(cellIndex++).setCellValue(song.getArtist());

            /*
            sixth place in row is marks in date
            */
            if (song.getReleasedate() != null) {

                Cell date = row.createCell(cellIndex++);

                DataFormat format = workbook.createDataFormat();
                CellStyle dateStyle = workbook.createCellStyle();
                dateStyle.setDataFormat(format.getFormat("dd-MMM-yyyy"));
                date.setCellStyle(dateStyle);

                date.setCellValue(song.getReleasedate());

                /*
                auto-resizing columns
                */
                songsSheet.autoSizeColumn(6);
                songsSheet.autoSizeColumn(5);
                songsSheet.autoSizeColumn(4);
                songsSheet.autoSizeColumn(3);
                songsSheet.autoSizeColumn(2);
            }

        }
    }

    /*
    writing this workbook to excel file.
    */
    try {
        FileOutputStream fos = new FileOutputStream(FILE_PATH);
        workbook.write(fos);
        fos.close();

        System.out.println(FILE_PATH + " is successfully written");
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

}

From source file:com.mycompany.gayamtakehomeexam.WritetoExcel.java

public void writeSongsListToExcel(List<Song> songList) {

    /*//from w ww  .  ja  va  2s. com
    Use XSSF for xlsx format and for xls use HSSF
    */
    Workbook workbook = new XSSFWorkbook();

    /*
    create new sheet
    */
    Sheet songsSheet = workbook.createSheet("Albums");

    XSSFCellStyle my_style = (XSSFCellStyle) workbook.createCellStyle();
    /* Create XSSFFont object from the workbook */
    XSSFFont my_font = (XSSFFont) workbook.createFont();

    /*
    setting cell color
    */
    CellStyle style = workbook.createCellStyle();
    style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);

    CellStyle style1 = workbook.createCellStyle();//Create style
    Font font = workbook.createFont();//Create font
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);//Make font bold
    style1.setFont(font);//set it to bold
    style1.setAlignment(CellStyle.ALIGN_CENTER);

    CellStyle style13 = workbook.createCellStyle();//Create style
    Font font13 = workbook.createFont();//Create font
    style13.setAlignment(CellStyle.ALIGN_LEFT);

    CellStyle style14 = workbook.createCellStyle();//Create style
    Font font14 = workbook.createFont();//Create font
    style14.setAlignment(CellStyle.ALIGN_RIGHT);
    /*
     setting Header color
    */
    CellStyle style2 = workbook.createCellStyle();
    style2.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
    style2.setFillBackgroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
    style2.setFillPattern(CellStyle.SOLID_FOREGROUND);

    Row rowName = songsSheet.createRow(1);

    /*
    Merging the cells
    */
    songsSheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 3));

    /*
    Applying style to attribute name
    */
    int nameCellIndex = 1;
    Cell namecell = rowName.createCell(nameCellIndex++);
    namecell.setCellValue("Name");
    namecell.setCellStyle(style);

    Cell cel = rowName.createCell(nameCellIndex++);

    /*
    Applying underline to Name
    */
    Font underlineFont = workbook.createFont();
    underlineFont.setUnderline(HSSFFont.U_SINGLE);

    /* Attaching the style to the cell */
    CellStyle combined = workbook.createCellStyle();
    combined.setFont(underlineFont);
    cel.setCellStyle(combined);
    cel.setCellValue("Gayam, Prathibha");

    /*
    Applying  colors to header
    */
    Row rowMain = songsSheet.createRow(3);
    SheetConditionalFormatting sheetCF = songsSheet.getSheetConditionalFormatting();
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("3");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.LIGHT_ORANGE.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A4:G4") };

    sheetCF.addConditionalFormatting(regions, rule1);

    /*
    setting new rule to apply alternate colors to cells having same Genre
    */
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule("4");
    PatternFormatting fill2 = rule2.createPatternFormatting();
    fill2.setFillBackgroundColor(IndexedColors.LEMON_CHIFFON.index);
    fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regionsAction = { CellRangeAddress.valueOf("A5:G5"), CellRangeAddress.valueOf("A6:G6"),
            CellRangeAddress.valueOf("A7:G7"), CellRangeAddress.valueOf("A8:G8"),
            CellRangeAddress.valueOf("A13:G13"), CellRangeAddress.valueOf("A14:G14"),
            CellRangeAddress.valueOf("A15:G15"), CellRangeAddress.valueOf("A16:G16"),
            CellRangeAddress.valueOf("A23:G23"), CellRangeAddress.valueOf("A24:G24"),
            CellRangeAddress.valueOf("A25:G25"), CellRangeAddress.valueOf("A26:G26")

    };

    /*        
    setting new rule to apply alternate colors to cells having same Genre
     */
    ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule("4");
    PatternFormatting fill3 = rule3.createPatternFormatting();
    fill3.setFillBackgroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.index);
    fill3.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regionsAdv = { CellRangeAddress.valueOf("A9:G9"), CellRangeAddress.valueOf("A10:G10"),
            CellRangeAddress.valueOf("A11:G11"), CellRangeAddress.valueOf("A12:G12"),
            CellRangeAddress.valueOf("A17:G17"), CellRangeAddress.valueOf("A18:G18"),
            CellRangeAddress.valueOf("A19:G19"), CellRangeAddress.valueOf("A20:G20"),
            CellRangeAddress.valueOf("A21:G21"), CellRangeAddress.valueOf("A22:G22"),
            CellRangeAddress.valueOf("A27:G27"), CellRangeAddress.valueOf("A28:G28"),
            CellRangeAddress.valueOf("A29:G29") };

    /*
    Applying above created rule formatting to cells
    */
    sheetCF.addConditionalFormatting(regionsAction, rule2);
    sheetCF.addConditionalFormatting(regionsAdv, rule3);

    /*
     Setting coloumn header values
    */
    int mainCellIndex = 0;

    Cell cell0 = rowMain.createCell(mainCellIndex++);
    cell0.setCellValue("SNO");
    cell0.setCellStyle(style1);
    Cell cell1 = rowMain.createCell(mainCellIndex++);
    cell1.setCellValue("Genre");
    cell1.setCellStyle(style1);
    Cell cell2 = rowMain.createCell(mainCellIndex++);
    cell2.setCellValue("Credit Score");
    cell2.setCellStyle(style1);
    Cell cell3 = rowMain.createCell(mainCellIndex++);
    cell3.setCellValue("Album Name");
    cell3.setCellStyle(style1);
    Cell cell4 = rowMain.createCell(mainCellIndex++);
    cell4.setCellValue("Artist");
    cell4.setCellStyle(style1);
    Cell cell5 = rowMain.createCell(mainCellIndex++);
    cell5.setCellValue("Release Date");
    cell5.setCellStyle(style1);

    /*
    populating cell values
    */
    int rowIndex = 4;
    int sno = 1;
    for (Song song : songList) {
        if (song.getSno() != 0) {

            Row row = songsSheet.createRow(rowIndex++);
            int cellIndex = 0;

            /*
            first place in row is Sno
            */

            Cell cell20 = row.createCell(cellIndex++);
            cell20.setCellValue(sno++);
            cell20.setCellStyle(style14);

            /*
            second place in row is  Genre
            */
            Cell cell21 = row.createCell(cellIndex++);
            cell21.setCellValue(song.getGenre());
            cell21.setCellStyle(style13);

            /*
            third place in row is Critic score
            */
            Cell cell22 = row.createCell(cellIndex++);
            cell22.setCellValue(song.getCriticscore());
            cell22.setCellStyle(style14);

            /*
            fourth place in row is Album name
            */
            Cell cell23 = row.createCell(cellIndex++);
            cell23.setCellValue(song.getAlbumname());
            cell23.setCellStyle(style13);

            /*
            fifth place in row is Artist
            */
            Cell cell24 = row.createCell(cellIndex++);
            cell24.setCellValue(song.getArtist());
            cell24.setCellStyle(style13);

            /*
            sixth place in row is marks in date
            */
            if (song.getReleasedate() != null) {

                Cell date = row.createCell(cellIndex++);

                DataFormat format = workbook.createDataFormat();
                CellStyle dateStyle = workbook.createCellStyle();
                dateStyle.setDataFormat(format.getFormat("dd-MMM-yyyy"));
                date.setCellStyle(dateStyle);

                date.setCellValue(song.getReleasedate());

                /*
                auto-resizing columns
                */
                songsSheet.autoSizeColumn(6);
                songsSheet.autoSizeColumn(5);
                songsSheet.autoSizeColumn(4);
                songsSheet.autoSizeColumn(3);
                songsSheet.autoSizeColumn(2);
            }

        }
    }

    /*
    writing this workbook to excel file.
    */
    try {
        FileOutputStream fos = new FileOutputStream(FILE_PATH);
        workbook.write(fos);
        fos.close();

        System.out.println(FILE_PATH + " is successfully written");
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

}

From source file:com.mycompany.mavenproject1.MainExecutor.java

public static void main(String[] args) throws Exception {
    FileInputStream fio = new FileInputStream(new File(pathToInput));

    //Read//from  www.j  a v  a  2  s. c om
    HSSFWorkbook inputExcel = new HSSFWorkbook(fio);

    // Write 
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("Sample sheet");

    LinkedHashSet<String> players = getAllPlayerNames(inputExcel);
    System.out.println(players.size());
    Iterator<String> playersIterator = players.iterator();

    Map<String, Object[]> data = getNewWorkbookData(inputExcel, playersIterator);
    Set<String> keyset = data.keySet();
    int rownum = 0;
    for (String key : keyset) {
        System.out.println("com.mycompany.mavenproject1.MainExecutor.main");
        System.out.println(rownum);
        Row row = sheet.createRow(rownum++);
        Object[] objArr = data.get(key);
        int cellnum = 0;
        for (Object obj : objArr) {
            Cell cell = row.createCell(cellnum++);
            if (obj instanceof String) {
                cell.setCellValue((String) obj);
            } else if (obj instanceof Double) {
                cell.setCellValue((Double) obj);
            }
        }
    }

    try {
        FileOutputStream out = new FileOutputStream(new File(pathToModel1));
        workbook.write(out);
        out.close();
        System.out.println("Excel written successfully..");

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

From source file:com.mycompany.mavenproject1.ragaiproject.ExcelExport.java

public void writeSheet() {
    Workbook wb = new HSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    HSSFSheet s1 = (HSSFSheet) wb.createSheet("Sheet 1");

    Row row = s1.createRow((short) 0);
    Cell cell = row.createCell(0);
    cell.setCellValue(1);//from ww w.j  ava 2 s  .  co m
    FileOutputStream fileOut = null;
    try {
        fileOut = new FileOutputStream("workbook.xls");
        try {
            wb.write(fileOut);
            fileOut.close();
        } catch (IOException ex) {
            Logger.getLogger(ExcelExport.class.getName()).log(Level.SEVERE, null, ex);
        }

    } catch (FileNotFoundException ex) {
        Logger.getLogger(ExcelExport.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:com.mycompany.mavenproject1.ragaiproject.PDFManipulation.java

public void convert(List<PDDocument> pdfList, List<String> selectedFields, String fileName) {

    Workbook wb = new HSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    HSSFSheet s1 = (HSSFSheet) wb.createSheet("Sheet 1");

    Row header = s1.createRow((short) 0);

    //initialize column headers
    for (int i = 0; i < selectedFields.size(); i++) {
        Cell headerCell = header.createCell(i);
        headerCell.setCellValue(selectedFields.get(i));
    }//from   w w w.  j  a va  2s .  c  o  m

    //for(int i = 0; i < selectedFields.size();i++){ //fills out row
    //Cell dataCell = data.createCell(i);

    for (int y = 0; y < pdfList.size(); y++) {
        PDDocumentCatalog docCatalog = pdfList.get(y).getDocumentCatalog();
        PDAcroForm acroForm = docCatalog.getAcroForm();
        java.util.List<PDField> fields = acroForm.getFields();
        Row data = s1.createRow((short) y + 1);
        for (int i = 0; i < selectedFields.size(); i++) {
            Cell dataCell = data.createCell(i);
            for (PDField field : fields) {
                System.out.println("Field Value: " + field.getValueAsString());
                if (field.getPartialName().equals(selectedFields.get(i))) {

                    dataCell.setCellValue(field.getValueAsString());
                }

            }
        }

        /* for(int j = 0; j < this.fieldLabelPairs.size();j++){
        if(this.fieldLabelPairs.get(j).getLabel().equals(selectedFields.get(i))){
            dataCell.setCellValue(this.fieldLabelPairs.get(j).getValue());
                    
        }
        }*/
    }

    //}
    /*for (int i = 0; i < selectedFields.size(); i++){
        Row data = s1.createRow(i+1);
                
        for(int j = 0; j< this.fieldLabelPairs.length; j++){
       Cell dataCell  = data.createCell(i);
       if(this.fieldLabelPairs[j].getLabel().equals(selectedFields.get(i))){
           dataCell.setCellValue(this.fieldLabelPairs[j].getValue());
       }
               
               
    }
    }*/

    FileOutputStream fileOut = null;
    try {
        fileOut = new FileOutputStream(fileName + ".xls");
        try {
            wb.write(fileOut);
            fileOut.close();
        } catch (IOException ex) {
            Logger.getLogger(ExcelExport.class.getName()).log(Level.SEVERE, null, ex);
        }

    } catch (FileNotFoundException ex) {
        Logger.getLogger(ExcelExport.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:com.mycompany.modelinglab2.helperModel.ExcelHelper.ExcelHelper.java

/**
 * distance = x higth = y//from   w ww  . j ava 2 s. c  o  m
 */
public void formExcelFile(List distance, List higth) {

    File file = new File("C:\\Users\\Roman\\Desktop\\Experiment.xls");
    //   
    Workbook wb = new HSSFWorkbook();
    try {
        Sheet sheet = wb.createSheet("Laboratory 2 (Modeling balls)");
        // ? 
        Font font = wb.createFont();
        font.setBoldweight(font.ANSI_CHARSET);

        // ? ?  ?
        Row row = sheet.createRow(0);

        row.createCell(0).setCellValue("???");
        row.createCell(1).setCellValue("?");

        for (int i = 0; i < distance.size(); i++) {
            row = sheet.createRow(i + 1);
            Cell distCell = row.createCell(0);
            Cell higthCell = row.createCell(1);
            distCell.setCellValue(distance.get(i).toString().replace('.', ','));
            higthCell.setCellValue(higth.get(i).toString().replace('.', ','));
        }
        //   
        sheet.autoSizeColumn(0);
        // ?  ??
        wb.write(new FileOutputStream(file));
        wb.close();
    } catch (IOException ex) {
        Logger.getLogger(ExcelHelper.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:com.nc.common.utils.ExcelUtil.java

License:Open Source License

/**
 * <pre>//from ww  w .  j  a  va2s . c  o m
 * 1.  : POI UTIL
 * 2.  : POI WORKBOOK ? / String , List<Map<String, Object>> ? , List<Map<String, Object>> 
 * </pre>
 *
 * @method Name : createWorkBook
 * @param workbook, sheetName, list, colNames
 * @return Workbook
 * @throws Exception
 * 
 */
@SuppressWarnings("deprecation")
public static Workbook createWorkBook(Workbook workbook, String sheetName, List<Map<String, Object>> list,
        List<Map<String, Object>> colNames) throws Exception {
    Row row;
    Cell cell;

    /*  ? ? START */
    /* Workbook workbook = new Workbook(); */

    CellStyle titleStyle = workbook.createCellStyle();
    CellStyle cellStyle = workbook.createCellStyle();
    CellStyle contentStyle = workbook.createCellStyle();
    CellStyle contentStyle_2 = workbook.createCellStyle();

    /* ?  */
    /* ? ? */
    Font titleFont = workbook.createFont();

    titleFont.setFontHeightInPoints((short) 13);
    titleFont.setFontName("? ");

    /*  ? */
    Font colNameFont = workbook.createFont();

    colNameFont.setFontHeightInPoints((short) 10);
    colNameFont.setFontName("? ");

    /*  ? */
    Font contentFont = workbook.createFont();

    /* ? ? ?  */
    titleStyle.setFont(titleFont);

    /*   ? / ? ?  */
    cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); /*  ? */
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//? 
    cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    cellStyle.setFont(colNameFont);

    /*   ? / ?  */
    contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); /* ?  */
    contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    contentStyle.setFont(contentFont);

    /*   ? / ?   */
    contentStyle_2.setBorderRight(HSSFCellStyle.BORDER_THIN); /* ?  */
    contentStyle_2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    contentStyle_2.setBorderTop(HSSFCellStyle.BORDER_THIN);
    contentStyle_2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    contentStyle_2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    contentStyle_2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    contentStyle_2.setFont(contentFont);

    /*  ? */
    Sheet sheet = workbook.createSheet(sheetName);

    /*  ?? */
    int sheet1_row = 0;

    List<Map<String, Object>> colList = colNames;

    /* ? START */
    row = sheet.createRow(sheet1_row);
    for (int i = 0; i < colList.size(); i++) {

        cell = row.createCell(i);
        cell.setCellValue(String.valueOf(colList.get(i).get("item" + i)));
        cell.setCellStyle(cellStyle);
    }

    sheet1_row++;

    /* ? END */
    for (Map<String, Object> map : list) {

        row = sheet.createRow(sheet1_row);

        for (int j = 0; j < colList.size(); j++) {
            cell = row.createCell(j);
            cell.setCellStyle(contentStyle);

            if (null != map.get(colList.get(j).get("item" + j))) {
                cell.setCellValue((double) map.get(String.valueOf(colList.get(j).get("item" + j))));
            } else {
                cell.setCellValue("");
            }

            /*  ? ??  */
            sheet.autoSizeColumn((short) j);
            sheet.setColumnWidth(j, (sheet.getColumnWidth(j)) + 312);
        }
        sheet1_row++;
    }
    return workbook;
}

From source file:com.ncc.excel.test.ExcelUtil.java

License:Apache License

/** 
 * Excel? //from  ww w .j a  va 2s  . c  o m
 *  
 * @Title: WriteExcel 
 * @Date : 2014-9-11 ?01:33:59 
 * @param wb 
 * @param rowList 
 * @param xlsPath 
 */
private void writeExcel(Workbook wb, List<Row> rowList, String xlsPath) {

    if (wb == null) {
        out("???");
        return;
    }

    Sheet sheet = wb.getSheetAt(0);// sheet  

    // ???????  
    int lastRowNum = isOverWrite ? startReadPos : sheet.getLastRowNum() + 1;
    int t = 0;//  
    out("???" + rowList.size());
    for (Row row : rowList) {
        if (row == null)
            continue;
        // ???  
        int pos = findInExcel(sheet, row);

        Row r = null;// ??????  
        if (pos >= 0) {
            sheet.removeRow(sheet.getRow(pos));
            r = sheet.createRow(pos);
        } else {
            r = sheet.createRow(lastRowNum + t++);
        }

        //??  
        CellStyle newstyle = wb.createCellStyle();

        //?  
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            Cell cell = r.createCell(i);// ??  
            cell.setCellValue(getCellValue(row.getCell(i)));// ???  
            // cell.setCellStyle(row.getCell(i).getCellStyle());//  
            if (row.getCell(i) == null)
                continue;
            copyCellStyle(row.getCell(i).getCellStyle(), newstyle); // ????  
            cell.setCellStyle(newstyle);// ?  
            // sheet.autoSizeColumn(i);//  
        }
    }
    out("???:" + (rowList.size() - t) + " ?" + t);

    // ??  
    setMergedRegion(sheet);

    try {
        // ??Excel  
        FileOutputStream outputStream = new FileOutputStream(xlsPath);
        wb.write(outputStream);
        outputStream.flush();
        outputStream.close();
    } catch (Exception e) {
        out("Excel?? ");
        e.printStackTrace();
    }
}

From source file:com.netsteadfast.greenstep.bsc.command.KpiPeriodTrendsExcelCommand.java

License:Apache License

@SuppressWarnings("unchecked")
private void putTables(XSSFWorkbook wb, XSSFSheet sh, Context context) throws Exception {

    XSSFCellStyle cellHeadStyle = wb.createCellStyle();
    cellHeadStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#f5f5f5")));
    cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    cellHeadStyle.setBorderBottom(BorderStyle.THIN);
    cellHeadStyle.setBorderTop(BorderStyle.THIN);
    cellHeadStyle.setBorderRight(BorderStyle.THIN);
    cellHeadStyle.setBorderLeft(BorderStyle.THIN);
    XSSFFont cellHeadFont = wb.createFont();
    cellHeadFont.setBold(true);//from ww w. j  ava2 s  .  c  o m
    cellHeadStyle.setFont(cellHeadFont);

    sh.setColumnWidth(0, 12000);

    int row = 0;

    Row nowRow = sh.createRow(row);
    Cell cell1 = nowRow.createCell(0);
    cell1.setCellStyle(cellHeadStyle);
    cell1.setCellValue("KPI");
    Cell cell2 = nowRow.createCell(1);
    cell2.setCellStyle(cellHeadStyle);
    cell2.setCellValue("Maximum");
    Cell cell3 = nowRow.createCell(2);
    cell3.setCellStyle(cellHeadStyle);
    cell3.setCellValue("Target");
    Cell cell4 = nowRow.createCell(3);
    cell4.setCellStyle(cellHeadStyle);
    cell4.setCellValue("Minimum");
    Cell cell5 = nowRow.createCell(4);
    cell5.setCellStyle(cellHeadStyle);
    cell5.setCellValue("Current score");
    Cell cell6 = nowRow.createCell(5);
    cell6.setCellStyle(cellHeadStyle);
    cell6.setCellValue("Previous score");
    Cell cell7 = nowRow.createCell(6);
    cell7.setCellStyle(cellHeadStyle);
    cell7.setCellValue("Change(%)");

    row++;

    List<PeriodTrendsData<KpiVO>> periodDatas = (List<PeriodTrendsData<KpiVO>>) context.get("periodDatas");
    for (PeriodTrendsData<KpiVO> periodData : periodDatas) {
        nowRow = sh.createRow(row);

        cell1 = nowRow.createCell(0);
        cell1.setCellValue(periodData.getCurrent().getName());
        cell2 = nowRow.createCell(1);
        cell2.setCellValue(periodData.getCurrent().getMax());
        cell3 = nowRow.createCell(2);
        cell3.setCellValue(periodData.getCurrent().getTarget());
        cell4 = nowRow.createCell(3);
        cell4.setCellValue(periodData.getCurrent().getMin());
        cell5 = nowRow.createCell(4);
        cell5.setCellValue(BscReportSupportUtils.parse2(periodData.getCurrent().getScore()));
        cell6 = nowRow.createCell(5);
        cell6.setCellValue(BscReportSupportUtils.parse2(periodData.getPrevious().getScore()));
        cell7 = nowRow.createCell(6);
        cell7.setCellValue(BscReportSupportUtils.parse2(periodData.getChange()));

        row++;
    }

    nowRow = sh.createRow(row);

    cell1 = nowRow.createCell(0);
    cell1.setCellValue("Current period: " + (String) context.get("currentPeriodDateRange")
            + " , Previous period: " + (String) context.get("previousPeriodDateRange"));

}

From source file:com.netsteadfast.greenstep.bsc.command.KpiReportExcelCommand.java

License:Apache License

private int createHead(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision) throws Exception {
    Row headRow = sh.createRow(row);
    headRow.setHeight((short) 700);

    int cell = 0;

    XSSFColor bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor(vision.getBgColor()));
    XSSFColor fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor(vision.getFontColor()));

    XSSFCellStyle cellHeadStyle = wb.createCellStyle();
    cellHeadStyle.setFillForegroundColor(bgColor);
    cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    XSSFFont cellHeadFont = wb.createFont();
    cellHeadFont.setBold(true);/*from   w  ww.j  a v a2 s.  c o  m*/
    cellHeadFont.setColor(fnColor);
    cellHeadStyle.setFont(cellHeadFont);
    cellHeadStyle.setBorderBottom(BorderStyle.THIN);
    cellHeadStyle.setBorderTop(BorderStyle.THIN);
    cellHeadStyle.setBorderRight(BorderStyle.THIN);
    cellHeadStyle.setBorderLeft(BorderStyle.THIN);
    cellHeadStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellHeadStyle.setAlignment(HorizontalAlignment.CENTER);
    cellHeadStyle.setWrapText(true);

    int cols = 12;
    for (int i = 0; i < cols; i++) {
        sh.setColumnWidth(i, 4000);
        Cell headCell1 = headRow.createCell(cell++);
        headCell1.setCellValue(
                vision.getTitle() + "\nscore: " + BscReportSupportUtils.parse2(vision.getScore()));
        headCell1.setCellStyle(cellHeadStyle);
    }

    sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1));

    // ------------------------------------------------------------------------
    bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor(BscReportPropertyUtils.getBackgroundColor()));
    fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor(BscReportPropertyUtils.getFontColor()));

    cellHeadStyle = wb.createCellStyle();
    cellHeadStyle.setFillForegroundColor(bgColor);
    cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    cellHeadFont = wb.createFont();
    cellHeadFont.setBold(true);
    cellHeadFont.setColor(fnColor);
    cellHeadStyle.setFont(cellHeadFont);
    cellHeadStyle.setBorderBottom(BorderStyle.THIN);
    cellHeadStyle.setBorderTop(BorderStyle.THIN);
    cellHeadStyle.setBorderRight(BorderStyle.THIN);
    cellHeadStyle.setBorderLeft(BorderStyle.THIN);
    cellHeadStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellHeadStyle.setAlignment(HorizontalAlignment.CENTER);
    cellHeadStyle.setWrapText(true);

    row++;
    headRow = sh.createRow(row);
    cell = 0;
    int titleCols = 4;
    for (int i = 0; i < titleCols; i++) {
        Cell headCell1 = headRow.createCell(cell++);
        headCell1.setCellValue(BscReportPropertyUtils.getPerspectiveTitle());
        headCell1.setCellStyle(cellHeadStyle);
    }
    for (int i = 0; i < titleCols; i++) {
        Cell headCell1 = headRow.createCell(cell++);
        headCell1.setCellValue(BscReportPropertyUtils.getObjectiveTitle());
        headCell1.setCellStyle(cellHeadStyle);
    }
    for (int i = 0; i < titleCols; i++) {
        Cell headCell1 = headRow.createCell(cell++);
        headCell1.setCellValue(BscReportPropertyUtils.getKpiTitle());
        headCell1.setCellStyle(cellHeadStyle);
    }

    sh.addMergedRegion(new CellRangeAddress(row, row, 0, 3));
    sh.addMergedRegion(new CellRangeAddress(row, row, 4, 7));
    sh.addMergedRegion(new CellRangeAddress(row, row, 8, 11));

    // ------------------------------------------------------------------------

    return 2;
}