Example usage for org.apache.poi.ss.usermodel Workbook createSheet

List of usage examples for org.apache.poi.ss.usermodel Workbook createSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. createSheet

Introduction

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

Prototype

Sheet createSheet(String sheetname);

Source Link

Document

Create a new sheet for this Workbook and return the high level representation.

Usage

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

public void writeSongsToExcel(List<SongsList> songList) {

    /*//from  w ww . ja  v a  2s  . co 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) {

    /*//w  w  w .ja v  a2 s  .c om
    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.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);/*from   w  ww.ja  v a  2  s . c o  m*/
    cell.setCellValue(1);
    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  av a2  s .  co  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// w w w.ja v a2s.com
 */
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 w ww.  j a  va 2s  .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.NRC.NMEA.main.NMEA.java

public static void createExcelFile() throws IOException {
    Workbook wb = new XSSFWorkbook();
    FileOutputStream fileOut = new FileOutputStream("C:\\Users\\Noah\\Desktop\\Excel stuff\\Data.xlsx");
    ;//  ww  w . j a  va2 s  .  co  m
    Sheet PSheet = wb.createSheet("Position Data");
    Sheet ESheet = wb.createSheet("Elevation Data");
    Sheet AbESheet = wb.createSheet("Absolute Elevation Data");
    Sheet DSheet = wb.createSheet("Chart Data");
    DSheet = writeStatInfoToExcel(DSheet);

    {
        AbESheet = writeAbsElevationsToExcel(pControl, AbESheet, 0);
        AbESheet = writeAbsElevationsToExcel(test1, AbESheet, 4);
        AbESheet = writeAbsElevationsToExcel(test2, AbESheet, 9);
        AbESheet = writeAbsElevationsToExcel(test3, AbESheet, 14);
        AbESheet = writeAbsElevationsToExcel(test4, AbESheet, 19);

        //           

    }
    {

        ESheet = writeElevationsToExcel(pControl, ESheet, 0);
        ESheet = writeElevationsToExcel(test1, ESheet, 4);
        ESheet = writeElevationsToExcel(test2, ESheet, 9);
        ESheet = writeElevationsToExcel(test3, ESheet, 14);
        ESheet = writeElevationsToExcel(test4, ESheet, 19);
        Set<Double> Eset2 = new HashSet();
        Eset2.add(pControl.dev.meanElevation);
        Eset2.add(test1.dev.meanElevation);
        Eset2.add(test2.dev.meanElevation);
        Eset2.add(test3.dev.meanElevation);
        Eset2.add(test4.dev.meanElevation);
        Set<Double> Eset3 = new HashSet();
        Eset3.add(pControl.dev.verticalStandardDeviation);
        Eset3.add(test1.dev.verticalStandardDeviation);
        Eset3.add(test2.dev.verticalStandardDeviation);
        Eset3.add(test3.dev.verticalStandardDeviation);
        Eset3.add(test4.dev.verticalStandardDeviation);

        Row erow21 = ESheet.createRow((short) 24);
        Row erow22 = ESheet.createRow((short) 25);
        Row erow23 = ESheet.createRow((short) 27);
        Row erow24 = ESheet.createRow((short) 28);

        Cell erow21c = erow21.createCell(0);
        erow21c.setCellValue("Means");
        Cell erow22c = erow22.createCell(0);
        erow22c.setCellValue("Height");

        Cell erow23c = erow23.createCell(0);
        erow23c.setCellValue("Standard Deviations");
        Cell erow24c = erow24.createCell(0);
        erow24c.setCellValue("Height");

        int i = 1;
        for (Double d : Eset2) {
            erow22c = erow22.createCell(i);
            erow22c.setCellValue(d);
            i++;
        }
        i = 1;
        for (Double d : Eset3) {
            erow24c = erow24.createCell(i);
            erow24c.setCellValue(d);
            i++;
        }

    }

    {
        PSheet = writePositionsToExcel(pControl, PSheet, 0);
        PSheet = writePositionsToExcel(test1, PSheet, 4);
        PSheet = writePositionsToExcel(test2, PSheet, 9);
        PSheet = writePositionsToExcel(test3, PSheet, 14);
        PSheet = writePositionsToExcel(test4, PSheet, 19);
        {
            Set<Coordinate> set = new HashSet();
            set.add(pControl.dev.center);
            set.add(test1.dev.center);
            set.add(test2.dev.center);
            set.add(test3.dev.center);
            set.add(test4.dev.center);
            Set<Double> set2 = new HashSet();
            set2.add(pControl.dev.latitudeStandardDeviation);
            set2.add(test1.dev.latitudeStandardDeviation);
            set2.add(test2.dev.latitudeStandardDeviation);
            set2.add(test3.dev.latitudeStandardDeviation);
            set2.add(test4.dev.latitudeStandardDeviation);
            Set<Double> set3 = new HashSet();
            set3.add(pControl.dev.longitudeStandardDeviation);
            set3.add(test1.dev.longitudeStandardDeviation);
            set3.add(test2.dev.longitudeStandardDeviation);
            set3.add(test3.dev.longitudeStandardDeviation);
            set3.add(test4.dev.longitudeStandardDeviation);

            Row centerrow1 = PSheet.createRow((short) 24);
            Row centerrow2 = PSheet.createRow((short) 25);
            Row centerrow3 = PSheet.createRow((short) 26);
            Cell centerrow1c = centerrow1.createCell(0);
            centerrow1c.setCellValue("Centers");
            Cell centerrow2c = centerrow2.createCell(0);
            centerrow2c.setCellValue("lat");
            Cell row3c = centerrow3.createCell(0);
            row3c.setCellValue("long");

            int i = 1;
            for (Coordinate c : set) {
                centerrow1c = centerrow1.createCell(i);
                centerrow2c = centerrow2.createCell(i);
                centerrow1c.setCellValue(c.getLatitude());
                centerrow2c.setCellValue(-c.getLongitude());
                i++;

            }
        }
    }
    wb.write(fileOut);
    fileOut.close();
}

From source file:com.oleke.facebookcrawler.ExcelAPI.java

License:Apache License

/**
 * This method creates an Excel Workbook and an Excel Sheet
 *
 * @param filename The output filename//  w w w .j a v a  2 s  .co m
 * @param sheetname the name of the sheet
 * @return Returns a new workbook
 */
public Workbook createExcel(String filename, String sheetname) {
    try {
        FileOutputStream out = new FileOutputStream(filename + ".xls");
        Workbook wb = new HSSFWorkbook();
        wb.createSheet(sheetname);
        wb.write(out);
        out.close();
        return wb;
    } catch (IOException ex) {
        Logger.getLogger(App.class.getName()).log(Level.SEVERE, null, ex);
    }
    return null;

}

From source file:com.opendoorlogistics.core.tables.io.PoiIO.java

License:Open Source License

public static boolean exportDatastore(ODLDatastore<? extends ODLTableReadOnly> ds, File file, boolean xlsx,
        ProcessingApi processing, ExecutionReport report) {
    //tmpFileBugFix();

    Workbook wb = null;
    SXSSFWorkbook sxssfwb = null;/*from   w  ww .j a  v a  2 s.c o  m*/
    HSSFWorkbook hssfwb = null;
    if (xlsx == false) {
        hssfwb = new HSSFWorkbook();
        hssfwb.createInformationProperties();
        hssfwb.getSummaryInformation().setAuthor(AppConstants.ORG_NAME);
        wb = hssfwb;
    } else {
        //   sxssfwb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
        sxssfwb = new SXSSFWorkbook(null, 100, false, true);
        wb = sxssfwb;

        //   XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        ///   POIXMLProperties xmlProps = sxssfwb.
        //POIXMLProperties.CoreProperties coreProps = xmlProps.getCoreProperties();
        //   coreProps.setCreator(AppConstants.ORG_NAME);
        //   wb = xssfWorkbook;
    }

    try {
        // save schema
        addSchema(ds, wb);

        for (ODLTableDefinition table : TableUtils.getAlphabeticallySortedTables(ds)) {
            ODLTableReadOnly tro = (ODLTableReadOnly) table;
            Sheet sheet = wb.createSheet(tro.getName());
            if (sheet == null) {
                return false;
            }

            exportTable(sheet, tro, 0, processing, report);

            if (processing != null && processing.isCancelled()) {
                return false;
            }
        }

        if (processing != null) {
            processing.postStatusMessage("Saving whole workbook to disk.");
        }

        saveWorkbook(file, wb);

    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        if (sxssfwb != null) {
            sxssfwb.dispose();
        }

        if (hssfwb != null) {
            try {
                hssfwb.close();
            } catch (Exception e2) {
                // TODO: handle exception
            }
        }
    }

    return true;
}

From source file:com.opendoorlogistics.core.tables.io.PoiIO.java

License:Open Source License

private static void addSchema(ODLDatastore<? extends ODLTableDefinition> ds, Workbook wb) {
    ODLTableReadOnly table = SchemaIO.createSchemaTable(ds);
    Sheet sheet = wb.createSheet(SCHEMA_SHEET_NAME);

    // write out key-value table
    Row row = sheet.createRow(0);//from   w  ww .  j a  va 2  s  . c om
    row.createCell(0).setCellValue(SchemaIO.KEY_COLUMN);
    row.createCell(1).setCellValue(SchemaIO.VALUE_COLUMN);
    row = sheet.createRow(1);
    row.createCell(0).setCellValue(SchemaIO.APP_VERSION_KEY);
    row.createCell(1).setCellValue(AppConstants.getAppVersion().toString());

    // write schema table
    exportTable(sheet, table, sheet.getLastRowNum() + 2, null, null);

    // hide the sheet from users
    wb.setSheetHidden(wb.getNumberOfSheets() - 1, Workbook.SHEET_STATE_VERY_HIDDEN);
}