How to create Dropdown list generated whit names of tabs in a xls file saved in the /data folder , and when I select a name in the dropdown list access the data inside de tab
1 Like
Hey, welcome to the forum, great to have you here.
here is an example to show you the commands.
- Use key “1” to load one file and see its content (you have to implement keys for file 0 and 2 and 3 and… or replace with Dropdownlist)
- Use Escape to go back to list
the xls part
I skipped the xls part - xls is hard to read, better use csv file. Are the names of the tabs (tab as in “processing tab”? Or as in “file”) stored in the xls file? Is the xls file already there? Are you then going to load the content of the tab (= pde file)?
Maybe you can elaborate a bit.
dropdownlist
for a dropdownlist see here https://www.sojamo.de/libraries/controlP5/examples/controllers/ControlP5dropdownList/ControlP5dropdownList.pde
Sketch
// states: whether we show list or the file content
final int stateList=0;
final int stateOneFile=1;
int state=stateList;
// the list of files
String[] filenames;
// when loading a file
String currentFileName="";
String[] fileContent; // content
void setup()
{
size( 800, 800);
File folder = new File(dataPath(""));
filenames = folder.list(); // important
} // setup
void draw()
{
background(255);
if (state==stateList) {
// we show list
fill(255, 2, 2);
int i=0;
for (String s : filenames) {
text(s, 33, 33+i*20);
i++;
}
i+=2;
text("Press 1 to load a file", 33, 33+i*20);
} else {
// we show the file content
fill(1, 255, 2);
int i=0;
for (String s : fileContent) {
text(s, 33, 33+i*20);
i++;
}
i+=2;
text(currentFileName
+ " Esc to go back",
33, 33+i*20);
}
} // draw
void keyPressed() {
if (key=='1') {
// load
fileContent=loadStrings(filenames[1]); // important
currentFileName=filenames[1];
state=stateOneFile; // go on to file content
} else if (key==ESC) {
key=0; //kill ESC
state=stateList; // go back
}
}
1 Like
Hi @lupitavictoria,
Here is the Excel part as an example.
Just combine it with @Chrisir dropdownlist example for your needs …
Hope that helps …
Cheers
— mnse
// Use : https://poi.apache.org/
// download : https://archive.apache.org/dist/poi/release/bin/poi-bin-5.2.3-20220909.zip and extract to a folder.
// Drag&Drop at least the following files from extracted folder onto PDE Window:
// poi-5.2.3.jar
// poi-ooxml-5.2.3.jar
// poi-ooxml-lite-5.2.3.jar
// lib/commons-collections4-4.4.jar
// lib/log4j-api-2.18.0.jar
// lib/commons-io-2.11.0.jar
// ooxml-lib/commons-compress-1.21.jar
// ooxml-lib/xmlbeans-5.1.1.jar
import java.util.*;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
void setup() {
surface.setVisible(false);
Map<String, List<List<String>>> data = new HashMap<>();
try {
Workbook workbook = new XSSFWorkbook(createInput("Excel.xlsx"));
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
String sheetName = workbook.getSheetName(i);
Sheet sheet = workbook.getSheetAt(i);
List<List<String>> table = new ArrayList<>();
for (Row row : sheet) {
List<String> datarow = new ArrayList<>();
for (Cell cell : row) {
switch (cell.getCellType()) {
case STRING:
datarow.add(cell.getStringCellValue());
break;
case NUMERIC:
datarow.add(String.valueOf(cell.getNumericCellValue()));
break;
default:
println("implement other Cell-Types if required!");
}
}
table.add(datarow);
}
data.put(sheetName, table);
}
workbook.close();
printAsAsciiTable(data);
}
catch(Exception e) {
println("Error loading xlsx: " + e.getMessage());
}
exit();
}
void printAsAsciiTable(Map<String, List<List<String>>> data) {
for (Map.Entry<String, List<List<String>>> entry : data.entrySet()) {
String sheetName = entry.getKey();
List<List<String>> rows = entry.getValue();
int[] columnWidths = getColumnWidths(rows);
int w = (rows.get(0).size()*3)-1;
for (int i = 0; i < columnWidths.length; i++) {
w+=columnWidths[i];
}
println("Sheet: " + sheetName);
println("-".repeat(w));
for (List<String> row : rows) {
printRow(row, columnWidths);
println();
}
println();
}
}
void printRow(List<String> row, int[] columnWidths) {
for (int i = 0; i < row.size(); i++) {
String cell = row.get(i);
print(String.format("%-" + columnWidths[i] + "s | ", cell));
}
}
int[] getColumnWidths(List<List<String>> rows) {
int numColumns = rows.get(0).size();
int[] widths = new int[numColumns];
for (List<String> row : rows) {
for (int i = 0; i < row.size(); i++) {
widths[i] = Math.max(widths[i], row.get(i).length());
}
}
return widths;
}
Example output based on my simple test Excel sheet:
Sheet: Sheet2
------------------------------------------------------------------
Column1 | Column2 | Column3 | Column4 |
Data_S_2_R_1_C_1 | Data_S_2_R_1_C_2 | Data_S_2_R_1_C_3 | 214.0 |
Data_S_2_R_2_C_1 | Data_S_2_R_2_C_2 | Data_S_2_R_2_C_3 | 224.0 |
Data_S_2_R_3_C_1 | Data_S_2_R_3_C_2 | Data_S_2_R_3_C_3 | 234.0 |
Data_S_2_R_4_C_1 | Data_S_2_R_4_C_2 | Data_S_2_R_4_C_3 | 244.0 |
Sheet: Sheet1
------------------------------------------------------------------
Column1 | Column2 | Column3 | Column4 |
Data_S_1_R_1_C_1 | Data_S_1_R_1_C_2 | Data_S_1_R_1_C_3 | 114.0 |
Data_S_1_R_2_C_1 | Data_S_1_R_2_C_2 | Data_S_1_R_2_C_3 | 124.0 |
Data_S_1_R_3_C_1 | Data_S_1_R_3_C_2 | Data_S_1_R_3_C_3 | 134.0 |
Data_S_1_R_4_C_1 | Data_S_1_R_4_C_2 | Data_S_1_R_4_C_3 | 144.0 |
Sheet: Sheet3
------------------------------------------------------------------
Column1 | Column2 | Column3 | Column4 |
Data_S_3_R_1_C_1 | Data_S_3_R_1_C_2 | Data_S_3_R_1_C_3 | 314.0 |
Data_S_3_R_2_C_1 | Data_S_3_R_2_C_2 | Data_S_3_R_2_C_3 | 324.0 |
Data_S_3_R_3_C_1 | Data_S_3_R_3_C_2 | Data_S_3_R_3_C_3 | 334.0 |
Data_S_3_R_4_C_1 | Data_S_3_R_4_C_2 | Data_S_3_R_4_C_3 | 344.0 |
1 Like