Example usage for org.apache.poi.hssf.usermodel HSSFSheet createRow

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet createRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. createRow

Introduction

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

Prototype

@Override
public HSSFRow createRow(int rownum)

Source Link

Document

Create a new row within the sheet and return the high level representation

Usage

From source file:com.grant.data.ItemDAO.java

public boolean getStockINReport(ReportICatogory rrc) throws FileNotFoundException, IOException {

    ResultSet rs = null;/*  www. ja  va2s .  co  m*/
    Connection dbConn = null;
    boolean ss = false;

    try {
        dbConn = dbConnManager.connect();

        Statement stmt = dbConn.createStatement();
        String query = "SELECT * FROM grant_item_in WHERE d_in_date BETWEEN '" + rrc.getItemIDateStart()
                + "' AND '" + rrc.getItemInDateEnd() + "'";
        System.out.println(query);

        rs = stmt.executeQuery(query);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("lawix10");
        HSSFRow rowhead = sheet.createRow((short) 0);

        rowhead.createCell((short) 1).setCellValue("Item ID");
        rowhead.createCell((short) 2).setCellValue("v_item_name");
        rowhead.createCell((short) 3).setCellValue("v_item_no");
        rowhead.createCell((short) 4).setCellValue("v_descrip");
        rowhead.createCell((short) 5).setCellValue("i_inwards");
        rowhead.createCell((short) 6).setCellValue("i_balance");
        rowhead.createCell((short) 7).setCellValue("v_type");
        rowhead.createCell((short) 8).setCellValue("d_in_date");
        rowhead.createCell((short) 9).setCellValue("i_unit_price");

        int i = 1;
        while (rs.next()) {
            HSSFRow row = sheet.createRow((short) i);
            //row.createCell((short) 0).setCellValue(Integer.toString(rs.getInt(0)));
            row.createCell((short) 1).setCellValue(rs.getString(1));
            row.createCell((short) 2).setCellValue(rs.getString(2));
            row.createCell((short) 3).setCellValue(rs.getString(3));
            row.createCell((short) 4).setCellValue(rs.getString(4));
            row.createCell((short) 5).setCellValue(rs.getString(5));
            row.createCell((short) 6).setCellValue(rs.getString(6));
            row.createCell((short) 7).setCellValue(rs.getString(7));
            row.createCell((short) 8).setCellValue(rs.getString(8));
            row.createCell((short) 9).setCellValue(rs.getString(9));

            i++;
        }

        FileDateTime fileDateTime = new FileDateTime();
        FileOutputStream fileOut = new FileOutputStream(fileDateTime.getFileName("Stock_In"));
        workbook.write(fileOut);
        ss = true;
    } catch (SQLException sQLException) {
        ss = false;
        System.out.println(sQLException + "-----------Insert query failed-------");
        rs = null;
    } finally {
        dbConnManager.con_close(dbConn);
    }
    return ss;
}

From source file:com.grant.data.ItemDAO.java

public boolean getStockOutReport(ReportICatogory ric) throws FileNotFoundException, IOException {

    ResultSet rs = null;//  ww w . jav a 2 s.co m
    Connection dbConn = null;
    boolean ss = false;

    try {
        dbConn = dbConnManager.connect();

        Statement stmt = dbConn.createStatement();

        String query = "SELECT * FROM grant_item_print WHERE d_in_date BETWEEN '" + ric.getItemIDateStart()
                + "' AND '" + ric.getItemInDateEnd() + "'";

        System.out.println(query);

        rs = stmt.executeQuery(query);

        /////////////
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("lawix10");
        HSSFRow rowhead = sheet.createRow((short) 0);
        //rowhead.createCell((short) 0).setCellValue("Item ID");

        rowhead.createCell((short) 1).setCellValue("i_itin_id");
        rowhead.createCell((short) 2).setCellValue("v_item_name");
        rowhead.createCell((short) 3).setCellValue("v_item_no");
        rowhead.createCell((short) 4).setCellValue("v_ref_code");
        rowhead.createCell((short) 5).setCellValue("v_descrip");
        rowhead.createCell((short) 6).setCellValue("v_invo_no");
        rowhead.createCell((short) 7).setCellValue("i_outwards");
        rowhead.createCell((short) 8).setCellValue("v_outtype");
        rowhead.createCell((short) 9).setCellValue("i_balance");
        rowhead.createCell((short) 10).setCellValue("i_unit_price");
        rowhead.createCell((short) 11).setCellValue("i_amount");
        rowhead.createCell((short) 12).setCellValue("i_sub_total");
        rowhead.createCell((short) 13).setCellValue("i_discount");
        rowhead.createCell((short) 14).setCellValue("i_total");
        rowhead.createCell((short) 15).setCellValue("d_in_date");
        rowhead.createCell((short) 16).setCellValue("v_cus_name");
        rowhead.createCell((short) 17).setCellValue("v_address");
        rowhead.createCell((short) 18).setCellValue("v_payType");
        rowhead.createCell((short) 19).setCellValue("v_no");
        rowhead.createCell((short) 20).setCellValue("v_order_no");

        int i = 1;
        while (rs.next()) {
            HSSFRow row = sheet.createRow((short) i);
            //row.createCell((short) 0).setCellValue(Integer.toString(rs.getInt(0)));
            row.createCell((short) 1).setCellValue(rs.getString(1));
            row.createCell((short) 2).setCellValue(rs.getString(2));
            row.createCell((short) 3).setCellValue(rs.getString(3));
            row.createCell((short) 4).setCellValue(rs.getString(4));
            row.createCell((short) 5).setCellValue(rs.getString(5));
            row.createCell((short) 6).setCellValue(rs.getString(6));
            row.createCell((short) 7).setCellValue(rs.getString(7));
            row.createCell((short) 8).setCellValue(rs.getString(8));
            row.createCell((short) 9).setCellValue(rs.getString(9));
            row.createCell((short) 10).setCellValue(rs.getString(10));
            row.createCell((short) 11).setCellValue(rs.getString(11));
            row.createCell((short) 12).setCellValue(rs.getString(12));
            row.createCell((short) 13).setCellValue(rs.getString(13));
            row.createCell((short) 14).setCellValue(rs.getString(14));
            row.createCell((short) 15).setCellValue(rs.getString(15));
            row.createCell((short) 16).setCellValue(rs.getString(16));
            row.createCell((short) 17).setCellValue(rs.getString(17));
            row.createCell((short) 18).setCellValue(rs.getString(18));
            row.createCell((short) 19).setCellValue(rs.getString(19));
            row.createCell((short) 20).setCellValue(rs.getString(20));
            i++;
        }

        FileDateTime fileDateTime = new FileDateTime();
        FileOutputStream fileOut = new FileOutputStream(fileDateTime.getFileName("Stock_Out"));
        workbook.write(fileOut);
        fileOut.close();
        ss = true;

    } catch (SQLException sQLException) {
        ss = false;
        System.out.println(sQLException + "-----------Insert query failed-------");

        rs = null;
    } finally {
        dbConnManager.con_close(dbConn);
    }
    return ss;
}

From source file:com.grant.data.ItemDAO.java

public List getItemCatogaryReport(ReportICatogory ric) throws FileNotFoundException, IOException {

    ResultSet rs = null;/*w  w w .  j a  v  a 2 s . co m*/
    Connection dbConn = null;
    List ss = null;

    try {
        dbConn = dbConnManager.connect();

        Statement stmt = dbConn.createStatement();

        String query = "SELECT * FROM grant_item_out WHERE d_in_date BETWEEN '" + ric.getItemIDateStart()
                + "' AND '" + ric.getItemInDateEnd() + "' AND v_item_name = '" + ric.getItemName() + "'";
        //SELECT * FROM grant_item_out WHERE d_in_date BETWEEN '2016-01-07' AND '2016-01-08' AND v_item_name = 'Item 1 chm'
        //String query = "INSERT INTO grant_item_out(v_item_name,v_item_no,v_ref_code,v_descrip,v_invo_no,i_outwards,i_balance,d_in_date)  " + "VALUES( '" + itemName + "','" + itemNo + "','" + refCode +  "','"+ description + "','" + invoiceNo + "','" + outwards + "','" + balance +  "','"+ itemInDate + "'" + ")";

        System.out.println(query);

        rs = stmt.executeQuery(query);

        /////////////
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("lawix10");
        HSSFRow rowhead = sheet.createRow((short) 0);
        //rowhead.createCell((short) 0).setCellValue("Item ID");
        rowhead.createCell((short) 1).setCellValue("i_itin_id");
        rowhead.createCell((short) 2).setCellValue("v_item_name");
        rowhead.createCell((short) 3).setCellValue("v_item_no");
        rowhead.createCell((short) 4).setCellValue("v_ref_code");
        rowhead.createCell((short) 5).setCellValue("v_descrip");
        rowhead.createCell((short) 6).setCellValue("v_invo_no");
        rowhead.createCell((short) 7).setCellValue("i_outwards");
        rowhead.createCell((short) 8).setCellValue("v_outtype");
        rowhead.createCell((short) 9).setCellValue("i_balance");
        rowhead.createCell((short) 10).setCellValue("i_unit_price");
        rowhead.createCell((short) 11).setCellValue("i_amount");
        rowhead.createCell((short) 12).setCellValue("i_sub_total");
        rowhead.createCell((short) 13).setCellValue("i_discount");
        rowhead.createCell((short) 14).setCellValue("i_total");
        rowhead.createCell((short) 15).setCellValue("d_in_date");
        rowhead.createCell((short) 16).setCellValue("v_cus_name");
        rowhead.createCell((short) 17).setCellValue("v_address");
        rowhead.createCell((short) 18).setCellValue("v_payType");
        rowhead.createCell((short) 19).setCellValue("v_no");
        rowhead.createCell((short) 20).setCellValue("v_order_no");
        int i = 1;
        while (rs.next()) {
            HSSFRow row = sheet.createRow((short) i);
            //row.createCell((short) 0).setCellValue(Integer.toString(rs.getInt(0)));
            row.createCell((short) 1).setCellValue(rs.getString(1));
            row.createCell((short) 2).setCellValue(rs.getString(2));
            row.createCell((short) 3).setCellValue(rs.getString(3));
            row.createCell((short) 4).setCellValue(rs.getString(4));
            row.createCell((short) 5).setCellValue(rs.getString(5));
            row.createCell((short) 6).setCellValue(rs.getString(6));
            row.createCell((short) 7).setCellValue(rs.getString(7));
            row.createCell((short) 8).setCellValue(rs.getString(8));
            row.createCell((short) 9).setCellValue(rs.getString(9));
            row.createCell((short) 10).setCellValue(rs.getString(10));
            row.createCell((short) 11).setCellValue(rs.getString(11));
            row.createCell((short) 12).setCellValue(rs.getString(12));
            row.createCell((short) 13).setCellValue(rs.getString(13));
            row.createCell((short) 14).setCellValue(rs.getString(14));
            row.createCell((short) 15).setCellValue(rs.getString(15));
            row.createCell((short) 16).setCellValue(rs.getString(16));
            row.createCell((short) 17).setCellValue(rs.getString(17));
            row.createCell((short) 18).setCellValue(rs.getString(18));
            row.createCell((short) 19).setCellValue(rs.getString(19));
            row.createCell((short) 20).setCellValue(rs.getString(20));
            i++;
        }
        String yemi = "C:/Users/Isura Amarasinghe/Desktop/test.xls";
        //C:/Users/Isura Amarasinghe/Desktop
        FileOutputStream fileOut = new FileOutputStream(yemi);
        workbook.write(fileOut);
        fileOut.close();
        ///////////

    } catch (SQLException sQLException) {

        System.out.println(sQLException + "-----------Insert query failed-------");

        rs = null;
    } finally {
        dbConnManager.con_close(dbConn);
    }
    return ss;
}

From source file:com.grant.data.ItemDAO.java

public List getRefPerfReport(ReportRefCode rrc) throws FileNotFoundException, IOException {

    ResultSet rs = null;/*from ww  w .  ja  va2  s  .  c o m*/
    Connection dbConn = null;
    List ss = null;

    try {
        dbConn = dbConnManager.connect();

        Statement stmt = dbConn.createStatement();

        String query = "SELECT * FROM grant_item_out WHERE d_in_date BETWEEN '" + rrc.getItemIDateStart()
                + "' AND '" + rrc.getItemInDateEnd() + "' AND v_ref_code = '" + rrc.getRefCode() + "'";
        //SELECT * FROM grant_item_out WHERE d_in_date BETWEEN '2016-01-07' AND '2016-01-08' AND v_item_name = 'Item 1 chm'
        //String query = "INSERT INTO grant_item_out(v_item_name,v_item_no,v_ref_code,v_descrip,v_invo_no,i_outwards,i_balance,d_in_date)  " + "VALUES( '" + itemName + "','" + itemNo + "','" + refCode +  "','"+ description + "','" + invoiceNo + "','" + outwards + "','" + balance +  "','"+ itemInDate + "'" + ")";

        System.out.println(query);

        rs = stmt.executeQuery(query);

        /////////////
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("lawix10");
        HSSFRow rowhead = sheet.createRow((short) 0);
        //rowhead.createCell((short) 0).setCellValue("Item ID");
        rowhead.createCell((short) 1).setCellValue("i_itin_id");
        rowhead.createCell((short) 2).setCellValue("v_item_name");
        rowhead.createCell((short) 3).setCellValue("v_item_no");
        rowhead.createCell((short) 4).setCellValue("v_ref_code");
        rowhead.createCell((short) 5).setCellValue("v_descrip");
        rowhead.createCell((short) 6).setCellValue("v_invo_no");
        rowhead.createCell((short) 7).setCellValue("i_outwards");
        rowhead.createCell((short) 8).setCellValue("v_outtype");
        rowhead.createCell((short) 9).setCellValue("i_balance");
        rowhead.createCell((short) 10).setCellValue("i_unit_price");
        rowhead.createCell((short) 11).setCellValue("i_amount");
        rowhead.createCell((short) 12).setCellValue("i_sub_total");
        rowhead.createCell((short) 13).setCellValue("i_discount");
        rowhead.createCell((short) 14).setCellValue("i_total");
        rowhead.createCell((short) 15).setCellValue("d_in_date");
        rowhead.createCell((short) 16).setCellValue("v_cus_name");
        rowhead.createCell((short) 17).setCellValue("v_address");
        rowhead.createCell((short) 18).setCellValue("v_payType");
        rowhead.createCell((short) 19).setCellValue("v_no");
        rowhead.createCell((short) 20).setCellValue("v_order_no");
        int i = 1;
        while (rs.next()) {
            HSSFRow row = sheet.createRow((short) i);
            //row.createCell((short) 0).setCellValue(Integer.toString(rs.getInt(0)));
            row.createCell((short) 1).setCellValue(rs.getString(1));
            row.createCell((short) 2).setCellValue(rs.getString(2));
            row.createCell((short) 3).setCellValue(rs.getString(3));
            row.createCell((short) 4).setCellValue(rs.getString(4));
            row.createCell((short) 5).setCellValue(rs.getString(5));
            row.createCell((short) 6).setCellValue(rs.getString(6));
            row.createCell((short) 7).setCellValue(rs.getString(7));
            row.createCell((short) 8).setCellValue(rs.getString(8));
            row.createCell((short) 9).setCellValue(rs.getString(9));
            row.createCell((short) 10).setCellValue(rs.getString(10));
            row.createCell((short) 11).setCellValue(rs.getString(11));
            row.createCell((short) 12).setCellValue(rs.getString(12));
            row.createCell((short) 13).setCellValue(rs.getString(13));
            row.createCell((short) 14).setCellValue(rs.getString(14));
            row.createCell((short) 15).setCellValue(rs.getString(15));
            row.createCell((short) 16).setCellValue(rs.getString(16));
            row.createCell((short) 17).setCellValue(rs.getString(17));
            row.createCell((short) 18).setCellValue(rs.getString(18));
            row.createCell((short) 19).setCellValue(rs.getString(19));
            row.createCell((short) 20).setCellValue(rs.getString(20));
            i++;
        }
        String yemi = "C:/Users/Isura Amarasinghe/Desktop/ref.xls";
        //C:/Users/Isura Amarasinghe/Desktop
        FileOutputStream fileOut = new FileOutputStream(yemi);
        workbook.write(fileOut);
        fileOut.close();
        ///////////

    } catch (SQLException sQLException) {

        System.out.println(sQLException + "-----------Insert query failed-------");

        rs = null;
    } finally {
        dbConnManager.con_close(dbConn);
    }
    return ss;
}

From source file:com.haulmont.mp2xls.writer.LocalizationBatchExcelWriter.java

License:Apache License

public static void exportToXls(LocalizationsBatch localizations, String outputXls) throws IOException {
    FileOutputStream fileOut = new FileOutputStream(outputXls);
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet worksheet = workbook.createSheet("localizations");

    HSSFCellStyle systemStyle = workbook.createCellStyle();
    systemStyle.setFillForegroundColor(HSSFColor.RED.index);
    systemStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    HSSFRow row = worksheet.createRow(0);
    row.createCell(0).setCellValue("Project path");
    row.createCell(1).setCellValue(localizations.getProjectDirectory());

    HSSFRow headLine = worksheet.createRow(5);
    headLine.createCell(0).setCellValue("Path to File");
    headLine.createCell(1).setCellValue("Parameter Name");

    Map<String, Integer> localeColumn = new HashMap<>();
    int colCount = 1;
    for (String localeId : localizations.getLocalizationIds()) {
        if (localizations.getScanLocalizationIds().contains(localeId == null ? "en" : localeId)) {
            String id = localeId == null ? "default" : localeId;
            headLine.createCell(++colCount).setCellValue(id);
            localeColumn.put(localeId, colCount);
        }//from w w  w  .j a  v a2s.c  o  m
    }

    Integer currentRow = headLine.getRowNum();
    for (String folder : localizations.getMessagesLocalizations().keySet()) {
        Set<MessagesLocalization> locales = localizations.getMessagesLocalizations().get(folder);

        Set<String> parameters = new HashSet<>();
        for (MessagesLocalization locale : locales) {
            if (localizations.getScanLocalizationIds()
                    .contains(locale.getLocaleId() == null ? "en" : locale.getLocaleId()))
                parameters.addAll(locale.getMessages().keySet());
        }

        for (String parameter : parameters) {
            row = worksheet.createRow(++currentRow);
            HSSFCell cell = row.createCell(0);
            cell.setCellValue(folder);
            if (MessagesFolderReader.systemKeys.contains(parameter)) {
                cell.setCellStyle(systemStyle);
                row.setZeroHeight(true);
            }

            cell = row.createCell(1);
            cell.setCellValue(parameter);
            if (MessagesFolderReader.systemKeys.contains(parameter)) {
                cell.setCellStyle(systemStyle);
            }

            for (MessagesLocalization locale : locales) {
                if (localizations.getScanLocalizationIds()
                        .contains(locale.getLocaleId() == null ? "en" : locale.getLocaleId())) {
                    Integer columnNum = localeColumn.get(locale.getLocaleId());
                    cell = row.createCell(columnNum);
                    cell.setCellValue(locale.getMessages().get(parameter));
                }
            }

        }
    }

    worksheet.setAutoFilter(new CellRangeAddress(headLine.getRowNum(), worksheet.getLastRowNum(), 0, colCount));
    worksheet.createFreezePane(0, headLine.getRowNum() + 1);
    /*
            for (int i = 0; i < colCount; i++){
    worksheet.autoSizeColumn(i);
    worksheet.setColumnWidth(i, worksheet.getColumnWidth(i) + 100);
            }
    */
    workbook.write(fileOut);
    fileOut.flush();
    fileOut.close();
}

From source file:com.haulmont.mp2xls.writer.LocalizationLogExcelWriter.java

License:Apache License

public static void exportToXls(List<LocalizationLog> differences, String outputXls) throws IOException {
    FileOutputStream fileOut = new FileOutputStream(outputXls);
    HSSFWorkbook workbook = new HSSFWorkbook();
    try {/*from  ww  w. j  a v  a 2s. c  o  m*/
        HSSFSheet worksheet = workbook.createSheet("localizations");

        HSSFCellStyle systemStyle = workbook.createCellStyle();
        systemStyle.setFillForegroundColor(HSSFColor.RED.index);
        systemStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        Integer currentRow = 0;
        HSSFRow headLine = worksheet.createRow(++currentRow);
        headLine.createCell(0).setCellValue("File");
        headLine.createCell(1).setCellValue("Property");
        headLine.createCell(2).setCellValue("Source Value");
        headLine.createCell(3).setCellValue("Excel Value");

        HSSFRow row;

        for (LocalizationLog.Type type : LocalizationLog.Type.values()) {
            List<LocalizationLog> logs = getLogsByType(differences, type);
            if (logs.size() > 0) {
                row = worksheet.createRow(++currentRow);
                HSSFCell cell = row.createCell(0);
                cell.setCellValue(LogHelper.getMessageByType(type));
                cell.setCellStyle(LogHelper.getStyleByType(workbook, type));
                for (int i = 1; i < 4; i++) {
                    row.createCell(i);
                }

                CellRangeAddress region = new CellRangeAddress(currentRow, currentRow, 0, 3);
                worksheet.addMergedRegion(region);

                for (LocalizationLog log : logs) {
                    createNewLogRow(worksheet, ++currentRow, log);
                }

                row = worksheet.createRow(++currentRow);
                for (int i = 0; i < 4; i++) {
                    row.createCell(i);
                }

                region = new CellRangeAddress(currentRow, currentRow, 0, 3);
                worksheet.addMergedRegion(region);
            }
        }

        worksheet.setAutoFilter(new CellRangeAddress(headLine.getRowNum(), worksheet.getLastRowNum(), 0, 3));
        worksheet.createFreezePane(0, headLine.getRowNum() + 1);

        for (int i = 0; i < worksheet.getLastRowNum(); i++) {
            worksheet.autoSizeColumn(i);
        }
    } finally {
        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    }
}

From source file:com.haulmont.mp2xls.writer.LocalizationLogExcelWriter.java

License:Apache License

protected static HSSFRow createNewLogRow(HSSFSheet worksheet, Integer currentRow, LocalizationLog log) {
    HSSFRow row;//from w w w  .j  ava 2  s.c om
    row = worksheet.createRow(currentRow);

    HSSFCell cell = row.createCell(0);
    cell.setCellValue(log.getFile());

    cell = row.createCell(1);
    cell.setCellValue(log.getParameterName());

    cell = row.createCell(2);
    cell.setCellValue(log.getSourceValue());

    cell = row.createCell(3);
    cell.setCellValue(log.getExcelValue());
    return row;
}

From source file:com.haulmont.yarg.formatters.impl.xls.HSSFCellHelper.java

License:Apache License

public static HSSFCell getCellFromReference(HSSFSheet templateSheet, int colIndex, int rowIndex) {
    HSSFRow row = templateSheet.getRow(rowIndex);
    row = row == null ? templateSheet.createRow(rowIndex) : row;
    HSSFCell cell = row.getCell(colIndex);
    cell = cell == null ? row.createCell(colIndex) : cell;
    return cell;/*www . j a  v  a 2s  .co m*/
}

From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java

License:Apache License

/**
 * Method writes horizontal band// w ww . j av  a  2  s  .c  o  m
 * Note: Only one band for row is supported. Now we think that many bands for row aren't usable.
 *
 * @param band          - band to write
 * @param templateSheet - template sheet
 * @param resultSheet   - result sheet
 */
protected void writeHorizontalBand(BandData band, HSSFSheet templateSheet, HSSFSheet resultSheet) {
    String rangeName = band.getName();
    AreaReference templateRange = getAreaForRange(templateWorkbook, rangeName);
    if (templateRange == null) {
        throw wrapWithReportingException(String.format("No such named range in xls file: %s", rangeName));
    }
    CellReference[] crefs = templateRange.getAllReferencedCells();

    CellReference topLeft, bottomRight;
    AreaReference resultRange;

    int rowsAddedByHorizontalBandBackup = rowsAddedByHorizontalBand;
    int rownumBackup = rownum;

    if (crefs != null) {
        addRangeBounds(band, crefs);

        ArrayList<HSSFRow> resultRows = new ArrayList<HSSFRow>();

        int currentRowNum = -1;
        int currentRowCount = -1;
        int currentColumnCount = 0;
        int offset = 0;

        topLeft = new CellReference(rownum + rowsAddedByHorizontalBand, 0);
        // no child bands - merge regions now
        if (band.getChildrenList().isEmpty()) {
            copyMergeRegions(resultSheet, rangeName, rownum + rowsAddedByHorizontalBand,
                    getCellFromReference(crefs[0], templateSheet).getColumnIndex());
        }

        for (CellReference cellRef : crefs) {
            HSSFCell templateCell = getCellFromReference(cellRef, templateSheet);
            HSSFRow resultRow;
            if (templateCell.getRowIndex() != currentRowNum) { //create new row
                resultRow = resultSheet.createRow(rownum + rowsAddedByHorizontalBand);
                copyPageBreaks(templateSheet, resultSheet, templateCell.getRowIndex(), resultRow.getRowNum());
                rowsAddedByHorizontalBand += 1;

                //todo move to options
                if (templateCell.getCellStyle().getParentStyle() != null
                        && templateCell.getCellStyle().getParentStyle().getUserStyleName() != null
                        && templateCell.getCellStyle().getParentStyle().getUserStyleName()
                                .equals(DYNAMIC_HEIGHT_STYLE)) {
                    //resultRow.setHeight(templateCell.getRow().getHeight());
                } else {
                    resultRow.setHeight(templateCell.getRow().getHeight());
                }
                resultRows.add(resultRow);

                currentRowNum = templateCell.getRowIndex();
                currentRowCount++;
                currentColumnCount = 0;
                offset = templateCell.getColumnIndex();
            } else { // or write cell to current row
                resultRow = resultRows.get(currentRowCount);
                currentColumnCount++;
            }

            copyCellFromTemplate(templateCell, resultRow, offset + currentColumnCount, band);
        }

        bottomRight = new CellReference(rownum + rowsAddedByHorizontalBand - 1, offset + currentColumnCount);
        resultRange = new AreaReference(topLeft, bottomRight);

        areaDependencyManager.addDependency(new Area(band.getName(), Area.AreaAlign.HORIZONTAL, templateRange),
                new Area(band.getName(), Area.AreaAlign.HORIZONTAL, resultRange));
        bandsToResultRanges.put(band,
                new Range(resultSheet.getSheetName(), resultRange.getFirstCell().getCol() + 1,
                        resultRange.getFirstCell().getRow() + 1, resultRange.getLastCell().getCol() + 1,
                        resultRange.getLastCell().getRow() + 1));
    }

    for (BandData child : band.getChildrenList()) {
        writeBand(child);
    }

    // scheduled merge regions
    if (!band.getChildrenList().isEmpty() && crefs != null) {
        copyMergeRegions(resultSheet, rangeName, rownumBackup + rowsAddedByHorizontalBandBackup,
                getCellFromReference(crefs[0], templateSheet).getColumnIndex());
    }

    rownum += rowsAddedByHorizontalBand;
    rowsAddedByHorizontalBand = 0;
    rownum += rowsAddedByVerticalBand;
    rowsAddedByVerticalBand = 0;
}

From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java

License:Apache License

/**
 * Method writes vertical band/* w  w w.jav  a2  s.  c  o  m*/
 * Note: no child support for vertical band ;)
 *
 * @param band          - band to write
 * @param templateSheet - template sheet
 * @param resultSheet   - result sheet
 */
protected void writeVerticalBand(BandData band, HSSFSheet templateSheet, HSSFSheet resultSheet) {
    String rangeName = band.getName();
    CellReference[] crefs = getRangeContent(templateWorkbook, rangeName);

    Set<Integer> addedRowNumbers = new HashSet<Integer>();

    if (crefs != null) {
        addRangeBounds(band, crefs);

        Bounds thisBounds = templateBounds.get(band.getName());
        Bounds parentBounds = templateBounds.get(band.getParentBand().getName());
        Range parentRange = bandsToResultRanges.get(band.getParentBand());

        int localRowNum = parentBounds != null && parentRange != null
                ? parentRange.getFirstRow() - 1 + thisBounds.row0 - parentBounds.row0
                : rownum;

        colnum = colnum == 0 ? getCellFromReference(crefs[0], templateSheet).getColumnIndex() : colnum;
        copyMergeRegions(resultSheet, rangeName, localRowNum, colnum);

        int firstRow = crefs[0].getRow();
        int firstColumn = crefs[0].getCol();

        for (CellReference cref : crefs) {//create necessary rows
            int currentRow = cref.getRow();
            final int rowOffset = currentRow - firstRow;
            if (!rowExists(resultSheet, localRowNum + rowOffset)) {
                HSSFRow resultRow = resultSheet.createRow(localRowNum + rowOffset);
                copyPageBreaks(templateSheet, resultSheet, cref.getRow(), resultRow.getRowNum());
            }
            addedRowNumbers.add(cref.getRow());
        }

        CellReference topLeft = null;
        CellReference bottomRight = null;
        for (CellReference cref : crefs) {
            int currentRow = cref.getRow();
            int currentColumn = cref.getCol();
            final int rowOffset = currentRow - firstRow;
            final int columnOffset = currentColumn - firstColumn;

            HSSFCell templateCell = getCellFromReference(cref, templateSheet);
            resultSheet.setColumnWidth(colnum + columnOffset,
                    templateSheet.getColumnWidth(templateCell.getColumnIndex()));
            HSSFCell resultCell = copyCellFromTemplate(templateCell,
                    resultSheet.getRow(localRowNum + rowOffset), colnum + columnOffset, band);
            if (topLeft == null) {
                topLeft = new CellReference(resultCell);
            }
            bottomRight = new CellReference(resultCell);
        }

        colnum += crefs[crefs.length - 1].getCol() - firstColumn + 1;

        AreaReference templateRange = getAreaForRange(templateWorkbook, rangeName);
        AreaReference resultRange = new AreaReference(topLeft, bottomRight);
        areaDependencyManager.addDependency(new Area(band.getName(), Area.AreaAlign.VERTICAL, templateRange),
                new Area(band.getName(), Area.AreaAlign.VERTICAL, resultRange));
        bandsToResultRanges.put(band,
                new Range(resultSheet.getSheetName(), resultRange.getFirstCell().getCol() + 1,
                        resultRange.getFirstCell().getRow() + 1, resultRange.getLastCell().getCol() + 1,
                        resultRange.getLastCell().getRow() + 1));
    }

    //for first level vertical bands we should increase rownum by number of rows added by vertical band
    //nested vertical bands do not add rows, they use parent space
    if (BandData.ROOT_BAND_NAME.equals(band.getParentBand().getName())) {
        List<BandData> sameBands = band.getParentBand().getChildrenByName(band.getName());
        if (sameBands.size() > 0 && sameBands.get(sameBands.size() - 1) == band) {//check if this vertical band is last vertical band with same name
            rownum += addedRowNumbers.size();
            //      rowsAddedByVerticalBand = 0;
        }
    }
}