#XSSFSheet

[khyati-sehgal]

Working with Microsoft Excel in Selenium

Working with Microsoft Excel in Selenium

Excel provides huge number of capabilities, we can make multiple use out of it like storing data, maintenance, calculations, retrieval, etc. We can use the same capabilities of Excel in Selenium with the help of programming language like Java.

In this post I will be sharing how we can use excel with the help of a class example which can be directly used in any project as a Utility. I will be…

[knowledgewiki]

Write to specific cell in excel in java

How to write text/image to specific merge cell using Apache POI. Specific cell means, I write a text or image directly to B3:D7. The code below is manual per index and not specific name and number of cell. I want to put via cell name and number.

ClientAnchor anchor = helper.createClientAnchor(); //create an anchor with upper left cell _and_ bottom right cell anchor.setCol1(1); //Column B anchor.setRow1(2); //Row 3 anchor.setCol2(2); //Column C anchor.setRow2(3); //Row 4

2 Answers

here an example

public class MMM { static void mergeCells(XSSFSheet sheet, String cells) { String regex = "([A-Z]+)(\d+):([A-Z]+)(\d+)"; Pattern pattern = Pattern.compile(regex); Matcher matcher = pattern.matcher(cells); if(matcher.matches()) { int col1 = CellReference.convertColStringToIndex(matcher.group(1)); int col2 = CellReference.convertColStringToIndex(matcher.group(3)); int row1 = Integer.parseInt(matcher.group(2)) - 1; int row2 = Integer.parseInt(matcher.group(4)) - 1; sheet.addMergedRegion(new CellRangeAddress(row1, row2, col1, col2)); } } public static void main(String[] args) throws IOException { OutputStream outputStream = new FileOutputStream("wwww2.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet(); mergeCells(sheet, "AAD10:ADD23"); workbook.write(outputStream); outputStream.close(); } }

You can use sheet.addMergedRegion(new CellRangeAddress(firstRow,lastRow,firstCol,lastCol)); for creating a merged region. In order to get the indexes you can use CellReference.convertColStringToIndex("B"); in order to get the index of the column. The index of the row is easy, it is simply the number - 1, e.g. for B3 the index is 2.

Example solution for D3:G16:

int firstRow = 2; // 3-1 int lastRow = 15; // 16-1 int firstCol = CellReference.convertColStringToIndex("D"); int lastCol = CellReference.convertColStringToIndex("G"); sheet.addMergedRegion(new CellRangeAddress(firstRow,lastRow,firstCol,lastCol));

Archive from: https://stackoverflow.com/questions/59064165/write-to-specific-cell-in-excel-in-java

from https://knowledgewiki.org/write-to-specific-cell-in-excel-in-java/

[knolspeak]

Data Driven Test Automation with Apache POI (Part-1)

Data Driven Test Automation with Apache POI (Part-1)

What is Data Driven Test Automation?

To test the functionality of an application it is requires to test with multiple input values. Data driven test automation allows to create test scripts where test data or input/output values are read from an external data files instead of using hard code each time the test run. Data driven test framework allows user to separate their data to the code for…