groovy.lang.MissingMethodException: No signature of method:
org.apache.poi.xssf.usermodel.XSSFSheet.createTable() is applicable
....
Taking this message as a clue, I checked the published Javadoc of POI. I found the following part:
https://poi.apache.org/apidocs/dev/org/apache/poi/xssf/usermodel/XSSFSheet.html#createTable--
createTable
@Deprecated @Removal(version=“4.2.0”) public XSSFTable createTable()
Deprecated. Use #createTable(AreaReference)) instead
It seems that the version of the POI library bundled in Katalon Studio is different from the one you used in Eclipse — newer than v4.2.0 or not. That would be the reason why you saw your code worked in Eclipse and not in Katalon. It is likely to happen.
@kazurayam
Thank you for your reply and information.
Apologies, I forgot to mention Apache POI library version.
I am using POI 4.1.1.
About the Katalon studio libraries and Eclipse libraries, here I have uploaded the screenshot below kindly confirm.
createTable
@Deprecated @Removal(version=“4.2.0”) public XSSFTable createTable()
Deprecated. Use #createTable(AreaReference)) instead
About deprecated method, createTable()*with null parameter has been deprecated, but in code we are using createTable(AreaReference)) even though it is not working.
The diagnostics is different from the one you presented above. I do not see the reason why different.
Regarding this, Different version of Katalon shows different error,
For Katalon 6.3.3,
signature of method: org.apache.poi.xssf.usermodel.XSSFCreationHelper.createAreaReference() is applicable for argument types: (org.apache.poi.ss.util.CellReference,
For 7.x above
signature of method: org.apache.poi.xssf.usermodel.XSSFCreationHelper.createAreaReference() is applicable for argument types: (org.apache.poi.ss.util.CellReference,
above both errors has been showed up with my code.the code I have posted it is from Apache poi documentation. I dont understand whether its Katalon problem or what else.
Kindly give some input.
Create a new Test Case script; copy and paste the following, and run it.
import org.apache.poi.xssf.usermodel.XSSFSheet
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords as WebUI
Class klass = XSSFSheet.class
URL location = klass.getResource('/' + klass.getName().replace('.', '/') + ".class")
WebUI.comment("location of XSSFSheet.class is ${location}")
This gave me the following output to me:
location of XSSFSheet.class is jar:file:/Applications/Katalon%20Studio.app/Contents/Eclipse/configuration/resources/lib/poi-ooxml-3.17.jar!/org/apache/poi/xssf/usermodel/XSSFSheet.class
I am on Mac, and this message tells me the POI library I have is file:/Applications/Katalon%20Studio.app/Contents/Eclipse/configuration/resources/lib/poi-ooxml-3.17.jar.
Then let’s see your case. Which version of POI library do you actually use? That could be different from what you expect.
@kazurayam
Its mind blowing!!
Output:
XSSFSheet.class is jar:file:/C:/Katalon_Studio/plugins/org.apache.poi.ooxml_3.9.0.v201405241905.jar!/org/apache/poi/xssf/usermodel/XSSFSheet.class
As table requires poi-ooxml library which is already available in plugins folder with older version.
Its not using external dependecy jar file.
@ThanhTo
@devalex88
@lotakeketan wanted to use newer version of Apache POI, so he placed the ver4.1.1 in the Drivers directtory. But Katalon Studio loaded older version from the jar contained in the plugins directory. How the precedence of class loading is designed? I think that the jars in the Drivers should have higher precedence than the jars in the plugins directory.
hi,
with these .jar’s works
import static com.kms.katalon.core.checkpoint.CheckpointFactory.findCheckpoint
import static com.kms.katalon.core.testcase.TestCaseFactory.findTestCase
import static com.kms.katalon.core.testdata.TestDataFactory.findTestData
import static com.kms.katalon.core.testobject.ObjectRepository.findTestObject
import static com.kms.katalon.core.testobject.ObjectRepository.findWindowsObject
import com.kms.katalon.core.checkpoint.Checkpoint as Checkpoint
import com.kms.katalon.core.cucumber.keyword.CucumberBuiltinKeywords as CucumberKW
import com.kms.katalon.core.mobile.keyword.MobileBuiltInKeywords as Mobile
import com.kms.katalon.core.model.FailureHandling as FailureHandling
import com.kms.katalon.core.testcase.TestCase as TestCase
import com.kms.katalon.core.testdata.TestData as TestData
import com.kms.katalon.core.testobject.TestObject as TestObject
import com.kms.katalon.core.webservice.keyword.WSBuiltInKeywords as WS
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords as WebUI
import com.kms.katalon.core.windows.keyword.WindowsBuiltinKeywords as Windows
import internal.GlobalVariable as GlobalVariable
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFTable;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTable;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumns;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableStyleInfo;
ExcelTableTest.createTable()
public class ExcelTableTest {
public static void createTable() throws FileNotFoundException, IOException {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Architecture");
XSSFTable table = sheet.createTable();
//XSSFTable table = sheet.createTable(null); //since apache poi 4.0.0
CTTable cttable = table.getCTTable();
cttable.setDisplayName("Table1");
cttable.setId(1);
cttable.setName("Test");
cttable.setRef("A1:C11");
cttable.setTotalsRowShown(false);
CTTableStyleInfo styleInfo = cttable.addNewTableStyleInfo();
styleInfo.setName("TableStyleMedium2");
styleInfo.setShowColumnStripes(false);
styleInfo.setShowRowStripes(true);
CTTableColumns columns = cttable.addNewTableColumns();
columns.setCount(3);
for (int i = 1; i <= 3; i++) {
CTTableColumn column = columns.addNewTableColumn();
column.setId(i);
column.setName("Column" + i);
}
for (int r = 0; r < 2; r++) {
XSSFRow row = sheet.createRow(r);
for(int c = 0; c < 3; c++) {
XSSFCell cell = row.createCell(c);
if(r == 0) { //first row is for column headers
cell.setCellValue("Column"+ (c+1)); //content **must** be here for table column names
} else {
cell.setCellValue("Data R"+ (r+1) + "C" + (c+1));
}
}
}
try {
FileOutputStream outputStream = new FileOutputStream("ExcelTableTest.xlsx")
workbook.write(outputStream);
}catch(Exception e){
println e
}
}
}
@kazurayam
@Timo_Kuisma1
Thank your infomation and reply.
As I am not using any katalon-excel-keywords,I just replaced the already available libraries with new version, It worked.
I need your help guys here:
This is the actual result
and this is the expected result:
ExcelInsert
This is my script for keyword
(1).txt|attachment (2.4 KB)
i think my code for write into excel is wrong, anyone can help me here?
Hi,
you can take parts from this code
@Keyword
public void updateXlsxFromList(List<String> excel, int rw, int col, String path, String sheetName, String fileName){
File file = new File(path);
workbook = WorkbookFactory.create(file);
// Check the file extension
if (!path.endsWith(".xlsx")) {
throw new IllegalArgumentException("Unknown file type. Please use .xlsx");
}
try
{
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(rw); //row to update
if (row == null) {
row = sheet.createRow(rw);
}
int c = col
for (String s : excel){
Cell cell = row.getCell(c, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);//check if cell is null
if (cell == null || cell.getCellTypeEnum() == CellType.BLANK){
cell = row.createCell(c);
cell.setCellValue(s);
//worksheet.createRow(rw).createCell(c).setCellValue(s); //if cell is null then update it to new value
}
else{
//cell = row.createCell(c);
cell.setCellValue(s);
}
c++
}
}
catch(Exception ex){
logger.logInfo(ex)
}
String path2 = System.getProperty("user.dir")+"\\ExcelFiles\\xlsData_copy.xlsx";
// Write the output to the file
FileOutputStream fileOut = new FileOutputStream(path2);
workbook.write(fileOut);
fileOut.close();
// Closing the workbook
workbook.close();
//delete original file
File f = new File(path);
if(f.delete())
{
System.out.println("File deleted successfully");
}
else
{
System.out.println("Failed to delete the file");
}
//rename copied file
Path source = Paths.get(path2);
Files.move(source, source.resolveSibling(fileName));
}
}
