Example usage for org.apache.poi.hssf.usermodel HSSFRow createCell

Introduction

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

Prototype

@Override
public HSSFCell createCell(int column) 

Source Link

Document

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

Usage

From source file:com.efficio.fieldbook.web.nursery.service.impl.ExcelExportStudyServiceImpl.java

License:Open Source License

private void writeObservationHeader(int currentRowNum, HSSFWorkbook xlsBook, HSSFSheet xlsSheet,
        List<MeasurementVariable> variables) {
    if (variables != null && !variables.isEmpty()) {
        int currentColNum = 0;
        HSSFRow row = xlsSheet.createRow(currentRowNum++);
        for (MeasurementVariable variable : variables) {
            HSSFCell cell = row.createCell(currentColNum++);
            if (variable.isFactor()) {
                cell.setCellStyle(getHeaderStyle(xlsBook, 51, 153, 102));
            } else {
                cell.setCellStyle(getHeaderStyle(xlsBook, 51, 51, 153));
            }//from w  w w . j av  a2  s. co m
            cell.setCellValue(variable.getName());
        }

    }
}

---

From source file:com.efficio.fieldbook.web.nursery.service.impl.ExcelExportStudyServiceImpl.java

License:Open Source License

private void writeObservationRow(int currentRowNum, HSSFSheet xlsSheet, MeasurementRow dataRow,
        HSSFWorkbook xlsBook) {//  w  ww  . j av  a 2 s.c o m
    HSSFRow row = xlsSheet.createRow(currentRowNum);
    int currentColNum = 0;
    CellStyle style = xlsBook.createCellStyle();
    DataFormat format = xlsBook.createDataFormat();
    style.setDataFormat(format.getFormat("0.#"));
    for (MeasurementData dataCell : dataRow.getDataList()) {
        HSSFCell cell = row.createCell(currentColNum++);
        /*
        if(AppConstants.NUMERIC_DATA_TYPE.getString().equalsIgnoreCase(dataCell.getDataType())){
           cell.setCellType(Cell.CELL_TYPE_BLANK);
           cell.setCellType(Cell.CELL_TYPE_NUMERIC);            
        }*/
        cell.setCellValue(dataCell.getValue());

    }
}

---

From source file:com.eryansky.core.excelTools.ExcelUtils.java

License:Apache License

public static void copyRow(HSSFWorkbook destwb, HSSFRow dest, HSSFWorkbook srcwb, HSSFRow src) {
    if (src == null || dest == null)
        return;//w  w w  . j  a v  a2s.  c o m
    for (int i = 0; i <= src.getLastCellNum(); i++) {
        if (src.getCell(i) != null) {
            HSSFCell cell = dest.createCell(i);
            copyCell(destwb, cell, srcwb, src.getCell(i));
        }
    }

}

---

From source file:com.eryansky.core.excelTools.JsGridReportBase.java

License:Apache License

/**
 * // ww  w.j  a v  a  2  s .  com
 * @param
 * @return void
 */
private void generateColumn(HSSFSheet sheet, TableColumn tc, int maxlevel, int rownum, int colnum,
        HSSFCellStyle headerstyle) {
    HSSFRow row = sheet.getRow(rownum);
    if (row == null)
        row = sheet.createRow(rownum);

    HSSFCell cell = row.createCell(colnum);
    cell.setCellValue(tc.getDisplay());

    if (headerstyle != null)
        cell.setCellStyle(headerstyle);
    if (tc.isComplex()) {
        CellRangeAddress address = new CellRangeAddress(rownum, rownum, colnum, colnum + tc.getLength() - 1);
        sheet.addMergedRegion(address);
        fillMergedRegion(sheet, address, headerstyle);

        int cn = colnum;
        for (int i = 0; i < tc.getChildren().size(); i++) {
            if (i != 0) {
                cn = cn + tc.getChildren().get(i - 1).getLength();
            }
            generateColumn(sheet, tc.getChildren().get(i), maxlevel, rownum + 1, cn, headerstyle);
        }
    } else {
        CellRangeAddress address = new CellRangeAddress(rownum, rownum + maxlevel - tc.level, colnum, colnum);
        sheet.addMergedRegion(address);
        fillMergedRegion(sheet, address, headerstyle);
    }
    sheet.autoSizeColumn(colnum, true);
}

---

From source file:com.eryansky.core.excelTools.JsGridReportBase.java

License:Apache License

/**
 * /*from w  ww .jav a2  s  . c om*/
 * @param
 * @return void
 */
private void fillMergedRegion(HSSFSheet sheet, CellRangeAddress address, HSSFCellStyle style) {
    for (int i = address.getFirstRow(); i <= address.getLastRow(); i++) {
        HSSFRow row = sheet.getRow(i);
        if (row == null)
            row = sheet.createRow(i);
        for (int j = address.getFirstColumn(); j <= address.getLastColumn(); j++) {
            HSSFCell cell = row.getCell(j);
            if (cell == null) {
                cell = row.createCell(j);
                if (style != null)
                    cell.setCellStyle(style);
            }
        }
    }
}

---

From source file:com.eryansky.core.excelTools.JsGridReportBase.java

License:Apache License

/**
 * //from  ww w. jav a  2 s .  c  o m
 * @param wb Excel
 * @param title Sheet??
 * @param styles ?
 * @param creator 
 * @param tableData ?
 * @throws Exception
 */
public HSSFWorkbook writeSheet(HSSFWorkbook wb, String title, HashMap<String, HSSFCellStyle> styles,
        String creator, TableData tableData) throws Exception {

    SimpleDateFormat formater = new SimpleDateFormat("yyyyMMdd HHmm");
    String create_time = formater.format(new Date());

    HSSFSheet sheet = wb.createSheet(title);// Excel
    sheet.setDisplayGridlines(false);// ?

    HSSFRow row = sheet.createRow(0);// 
    HSSFCell cell = row.createCell(0);// 
    int rownum = 0;
    cell.setCellValue(new HSSFRichTextString(title));
    HSSFCellStyle style = styles.get("TITLE");
    if (style != null)
        cell.setCellStyle(style);

    TableHeaderMetaData headerMetaData = tableData.getTableHeader();// HTML
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headerMetaData.getColumnCount() - 1));
    row = sheet.createRow(1);

    cell = row.createCell(0);
    cell.setCellValue(new HSSFRichTextString(":"));
    style = styles.get("SUB_TITLE");
    if (style != null)
        cell.setCellStyle(style);

    cell = row.createCell(1);
    cell.setCellValue(new HSSFRichTextString(creator));
    style = styles.get("SUB_TITLE2");
    if (style != null)
        cell.setCellStyle(style);

    cell = row.createCell(2);
    cell.setCellValue(new HSSFRichTextString(":"));
    style = styles.get("SUB_TITLE");
    if (style != null)
        cell.setCellStyle(style);

    cell = row.createCell(3);
    style = styles.get("SUB_TITLE2");
    cell.setCellValue(new HSSFRichTextString(create_time));
    if (style != null)
        cell.setCellStyle(style);

    rownum = 3;// rownum = 1?

    HSSFCellStyle headerstyle = styles.get("TABLE_HEADER");

    System.out.println(JsonMapper.getInstance().toJson(headerMetaData));
    int colnum = 0;
    for (int i = 0; i < headerMetaData.getOriginColumns().size(); i++) {
        TableColumn tc = headerMetaData.getOriginColumns().get(i);
        if (i != 0) {
            colnum += headerMetaData.getOriginColumns().get(i - 1).getLength();
        }
        generateColumn(sheet, tc, headerMetaData.maxlevel, rownum, colnum, headerstyle);
    }
    rownum += headerMetaData.maxlevel;

    List<TableDataRow> dataRows = tableData.getRows();

    HashMap<Integer, Integer> counter = new HashMap<Integer, Integer>();
    HashMap<Integer, String> word = new HashMap<Integer, String>();
    int index = 0;
    for (TableDataRow dataRow : dataRows) {
        row = sheet.createRow(rownum);

        List<TableDataCell> dataCells = dataRow.getCells();
        int size = headerMetaData.getColumns().size();
        index = -1;
        for (int i = 0; i < size; i++) {
            TableColumn tc = headerMetaData.getColumns().get(i);
            if (!tc.isVisible())
                continue;
            index++;

            String value = dataCells.get(i).getValue();
            if (tc.isGrouped()) {
                String w = word.get(index);
                if (w == null) {
                    word.put(index, value);
                    counter.put(index, 1);
                    createCell(row, tc, dataCells, i, index, styles);
                } else {
                    if (w.equals(value)) {
                        counter.put(index, counter.get(index) + 1);
                    } else {
                        stopGrouping(sheet, word, counter, index, size, rownum, styles.get("STRING"));

                        word.put(index, value);
                        counter.put(index, 1);
                        createCell(row, tc, dataCells, i, index, styles);
                    }
                }
            } else {
                createCell(row, tc, dataCells, i, index, styles);
            }
        }
        rownum++;
    }

    stopGrouping(sheet, word, counter, 0, index, rownum, styles.get("STRING"));
    // ???
    for (int c = 0; c < headerMetaData.getColumns().size(); c++) {
        sheet.autoSizeColumn((short) c, true);
    }
    sheet.setGridsPrinted(true);

    return wb;
}

---

From source file:com.eryansky.core.excelTools.JsGridReportBase.java

License:Apache License

/**
 * ?//  w  w  w.j a  va  2  s . c  om
 * 
 * @param
 * @return void
 */
private void createCell(HSSFRow row, TableColumn tc, List<TableDataCell> data, int i, int index,
        HashMap<String, HSSFCellStyle> styles) {
    TableDataCell dc = data.get(i);
    HSSFCell cell = row.createCell(index);
    switch (tc.getColumnType()) {
    case TableColumn.COLUMN_TYPE_INTEGER:
        cell.setCellValue(dc.getIntValue());
        HSSFCellStyle style = styles.get("INT");
        if (row.getRowNum() % 2 != 0)
            style = styles.get("INT_C");
        if (style != null)
            cell.setCellStyle(style);
        break;
    case TableColumn.COLUMN_TYPE_FLOAT_2:
        cell.setCellValue(dc.getDoubleValue());
        style = styles.get("D2");
        if (row.getRowNum() % 2 != 0)
            style = styles.get("D2_C");
        if (style != null)
            cell.setCellStyle(style);
        break;
    case TableColumn.COLUMN_TYPE_FLOAT_3:
        cell.setCellValue(dc.getDoubleValue());
        style = styles.get("D3");
        if (row.getRowNum() % 2 != 0)
            style = styles.get("D3_C");
        if (style != null)
            cell.setCellStyle(style);
        break;
    case TableColumn.COLUMN_TYPE_RED_BG:
        cell.setCellValue(dc.getValue());
        style = styles.get("RED_BG");
        if (style != null)
            cell.setCellStyle(style);
        break;
    case TableColumn.COLUMN_TYPE_YELLOW_BG:
        cell.setCellValue(dc.getValue());
        style = styles.get("YELLOW_BG");
        if (style != null)
            cell.setCellStyle(style);
        break;
    case TableColumn.COLUMN_TYPE_GREEN_BG:
        cell.setCellValue(dc.getValue());
        style = styles.get("GREEN_BG");
        if (style != null)
            cell.setCellStyle(style);
        break;
    default:
        if (dc.getValue().equalsIgnoreCase("&nbsp;"))
            cell.setCellValue("");
        else
            cell.setCellValue(dc.getValue());
        style = styles.get("STRING");
        if (row.getRowNum() % 2 != 0)
            style = styles.get("STRING_C");
        if (style != null)
            cell.setCellStyle(style);
    }
}

---

From source file:com.esd.cs.common.PoiCreateExcel.java

License:Open Source License

/**
 * ?//from   w  w w .  j  av  a  2 s  .  c o  m
 * 
 * @param FilePath
 * @param workerList
 * @return
 */
public static boolean createExcel(String FilePath, List<WorkerTemp> workerList) {
    // Excel Workbook,excel
    HSSFWorkbook wb = new HSSFWorkbook();
    // Excelsheet,exceltab
    HSSFSheet sheet = wb.createSheet("sheet1");
    // excel?
    sheet.setColumnWidth(0, 4000);
    sheet.setColumnWidth(1, 3500);

    // Excel?
    HSSFRow headRow = sheet.createRow(0);
    HSSFCell headell = headRow.createCell(0);
    // ???
    headell = headRow.createCell(0);
    headell.setCellValue("??");
    headell = headRow.createCell(1);
    headell.setCellValue("??");
    sheet.setColumnWidth(1, 8000);
    headell = headRow.createCell(2);
    headell.setCellValue("");
    sheet.setColumnWidth(2, 13000);

    for (int i = 1; i <= workerList.size(); i++) {
        WorkerTemp worker = workerList.get(i - 1);
        // Excel?
        HSSFRow row = sheet.createRow(i);
        HSSFCell cell = row.createCell(0);
        // ???
        cell = row.createCell(0);
        cell.setCellValue(worker.getWorkerName());
        cell = row.createCell(1);
        cell.setCellValue(worker.getWorkerHandicapCode());
        cell = row.createCell(2);
        cell.setCellValue(worker.getRemark());
    }
    try {
        FileOutputStream os = new FileOutputStream(FilePath);
        wb.write(os);
        os.close();

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

---

From source file:com.esd.cs.common.PoiCreateExcel.java

License:Open Source License

/**
 * ???//  w  w w  . ja  va  2 s .  c  o  m
 * 
 * @param FilePath
 * @param companyList
 * @return
 */
public static boolean createComapnyExcel(String FilePath, List<Company> companyList) {
    // Excel Workbook,excel
    HSSFWorkbook wb = new HSSFWorkbook();
    // Excelsheet,exceltab
    HSSFSheet sheet = wb.createSheet("sheet1");
    // excel?
    sheet.setColumnWidth(0, 4000);
    sheet.setColumnWidth(1, 3500);

    // Excel?
    HSSFRow headRow = sheet.createRow(0);
    HSSFCell headell = headRow.createCell(0);
    // ???
    headell = headRow.createCell(0);
    headell.setCellValue("?");
    headell = headRow.createCell(1);
    headell.setCellValue("?");
    headell = headRow.createCell(2);
    headell.setCellValue("????");
    sheet.setColumnWidth(2, 12000); // 

    headell = headRow.createCell(3);
    headell.setCellValue("");
    headell = headRow.createCell(4);
    headell.setCellValue("?");

    headell = headRow.createCell(5);
    headell.setCellValue("???");

    headell = headRow.createCell(6);
    headell.setCellValue("??");

    headell = headRow.createCell(7);
    headell.setCellValue("");
    headell = headRow.createCell(8);
    headell.setCellValue("???");
    sheet.setColumnWidth(8, 12000);

    for (int i = 1; i <= companyList.size(); i++) {
        Company company = companyList.get(i - 1);
        // Excel?
        HSSFRow row = sheet.createRow(i);
        HSSFCell cell = row.createCell(0);
        // ???
        // ?
        cell = row.createCell(0);
        cell.setCellValue(company.getCompanyCode());
        // ?
        cell = row.createCell(1);
        cell.setCellValue(company.getCompanyTaxCode());
        // ???
        cell = row.createCell(2);
        cell.setCellValue(company.getCompanyName());
        // 
        cell = row.createCell(3);
        cell.setCellValue(company.getCompanyLegal());
        // ?
        cell = row.createCell(4);
        cell.setCellValue(company.getCompanyContactPerson());
        // ???
        cell = row.createCell(5);
        cell.setCellValue(company.getCompanyPhone());
        // ??
        cell = row.createCell(6);
        cell.setCellValue(company.getCompanyMobile());
        // 
        cell = row.createCell(7);
        cell.setCellValue(company.getCompanyZipCode());
        // ???
        cell = row.createCell(8);
        cell.setCellValue(company.getCompanyAddress());
    }
    try {
        FileOutputStream os = new FileOutputStream(FilePath);
        wb.write(os);
        os.flush();
        os.close();
        companyList.clear();
        companyList = null;
        os = null;
        wb = null;
        System.gc();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return true;
}

---

From source file:com.esd.cs.common.PoiCreateExcel.java

License:Open Source License

/**
 * // www  . j a v  a2 s .  com
 * 
 * @param FilePath
 * @param companyList
 * @return
 */
public static boolean createRepeaExcel(String FilePath, List<ReportViewModel> companyList, ReportModel model) {
    // Excel Workbook,excel
    HSSFWorkbook wb = new HSSFWorkbook();
    // Excelsheet,exceltab
    HSSFSheet sheet = wb.createSheet("sheet1");
    // excel?
    sheet.setColumnWidth(0, 4000);
    sheet.setColumnWidth(1, 3500);

    // Excel?
    HSSFRow headRow0 = sheet.createRow(0);
    HSSFCell headCell = headRow0.createCell(0);
    // ??
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 13));// ???
    headCell = headRow0.createCell(0);
    // 
    headCell.setCellValue(model.getTitle());
    // ?
    HSSFCellStyle style = wb.createCellStyle();
    style.setFillBackgroundColor(HSSFColor.GREEN.index);
    style.setAlignment(CellStyle.ALIGN_CENTER);// 
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 
    // 
    HSSFFont font = wb.createFont();
    font.setFontHeightInPoints((short) 12);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 
    style.setFont(font);
    headCell.setCellStyle(style);

    // ? ??
    HSSFRow RowTow = sheet.createRow(1);
    HSSFCell CellTow = headRow0.createCell(1);
    // ??
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 5));// ???
    CellTow = RowTow.createCell(0);
    // 
    CellTow.setCellValue(model.getCreateCompany());

    // ? 
    // ??
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 6, 13));// ???
    CellTow = RowTow.createCell(6);
    HSSFCellStyle style1 = wb.createCellStyle();
    style1.setFillBackgroundColor(HSSFColor.GREEN.index);
    style1.setAlignment(CellStyle.ALIGN_RIGHT);// ?
    CellTow.setCellStyle(style1);
    // 
    CellTow.setCellValue(model.getCreateData());

    // ?
    HSSFRow headRow = sheet.createRow(2);
    HSSFCell headell = headRow.createCell(2);
    // ???
    headell = headRow.createCell(0);
    headell.setCellValue(model.getType());

    headell = headRow.createCell(1);
    headell.setCellValue("??");

    headell = headRow.createCell(2);
    headell.setCellValue("??");
    sheet.setColumnWidth(2, 3000); // 

    headell = headRow.createCell(3);
    headell.setCellValue("???");
    sheet.setColumnWidth(3, 3000); // 

    headell = headRow.createCell(4);
    headell.setCellValue("???");
    sheet.setColumnWidth(4, 4000); // 

    headell = headRow.createCell(5);
    headell.setCellValue("???");
    sheet.setColumnWidth(5, 4000); // 

    headell = headRow.createCell(6);
    headell.setCellValue("???");
    sheet.setColumnWidth(6, 4500); // 

    headell = headRow.createCell(7);
    headell.setCellValue("");
    sheet.setColumnWidth(8, 4000);

    headell = headRow.createCell(8);
    headell.setCellValue("");
    sheet.setColumnWidth(8, 4000);

    headell = headRow.createCell(9);
    headell.setCellValue("");
    sheet.setColumnWidth(9, 4000);

    headell = headRow.createCell(10);
    headell.setCellValue("?");
    sheet.setColumnWidth(10, 4000);

    headell = headRow.createCell(11);
    headell.setCellValue("???");

    headell = headRow.createCell(12);
    headell.setCellValue("?");

    headell = headRow.createCell(13);
    headell.setCellValue("?");

    for (int i = 0; i < companyList.size(); i++) {
        ReportViewModel company = companyList.get(i);
        // Excel?
        HSSFRow row = sheet.createRow(i + 3);
        HSSFCell cell = row.createCell(i + 3);
        // ???
        // ????
        cell = row.createCell(0);
        cell.setCellValue(company.getReportName());
        // ??
        cell = row.createCell(1);
        cell.setCellValue(company.getUnitNum());
        // ??
        cell = row.createCell(2);
        cell.setCellValue(company.getEmpTotal());

        // ???
        cell = row.createCell(3);
        cell.setCellValue(company.getUnAudit());

        // ?, ???
        cell = row.createCell(4);
        cell.setCellValue(company.getUnReAudit());

        // ?, ??
        cell = row.createCell(5);
        cell.setCellValue(company.getAuditOk());

        // ?, ??
        cell = row.createCell(6);
        cell.setCellValue(company.getUnauditOk());

        // 
        cell = row.createCell(7);
        cell.setCellValue(company.getShouldTotal().toString());

        // ?
        cell = row.createCell(8);
        cell.setCellValue(company.getAlreadyTotal().toString());

        // 
        cell = row.createCell(9);
        cell.setCellValue(company.getLessTotal().toString());
        // ?
        cell = row.createCell(10);
        cell.setCellValue(company.getAmountPayable().toString());
        // ???
        cell = row.createCell(11);
        cell.setCellValue(company.getReductionAmount().toString());
        // ?
        cell = row.createCell(12);
        cell.setCellValue(company.getActualAmount().toString());
        // ?
        cell = row.createCell(13);
        cell.setCellValue(company.getAlreadyAmount().toString());
    }

    // ? 
    HSSFRow row = sheet.createRow(companyList.size() + 3);
    HSSFCell cell = row.createCell(companyList.size() + 3);
    // ???
    // ????
    sheet.addMergedRegion(new CellRangeAddress(companyList.size() + 3, companyList.size() + 3, 0, 13));// ???
    cell = row.createCell(0);
    // ?
    HSSFCellStyle styleFoot = wb.createCellStyle();
    styleFoot.setAlignment(CellStyle.ALIGN_RIGHT);// ?
    cell.setCellStyle(styleFoot);
    // 
    cell.setCellValue(model.getCreatePeople());

    try {
        FileOutputStream os = new FileOutputStream(FilePath);
        wb.write(os);
        os.flush();
        os.close();
        companyList.clear();
        companyList = null;
        os = null;
        wb = null;
        System.gc();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return true;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »