Skip to content
Web

SpreadSheets

Overview

AMI supports user interactions and manipulation of spreadsheets. Users can create new spreadsheets or import existing ones for editing within the AMI environment. This section will discuss the various ways to work with spreadsheets in AMI.

Contents:

  • Parsing XLSX & CSV files
  • AMI Spreadsheet Classes
  • Downloading Spreadsheets from AMI
  • Example Workflows

Parsing CSV and XLSX

For simple use cases, AMI allows for the parsing of both XLSX and CSV file formats. Users can import these files into the AMI environment for further manipulation and analysis.

To import an existing spreadsheet, first create an empty datamodel, then add the following AMIScript to the onProcess tab.

Parsing CSV

parseCsv(String text, Boolean firstLineIsHeader)

Parse the CSV file and returns a table.

1
2
3
fileSystem fs = session.getFileSystem();
string toParse = fs.readFile("/path/to/file/.../test.csv");
table t = parseCsv(toParse,true);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
+-----------------+
|     Sheet1      |
+-------+---------+
|Symbol |Quantity |
|String |Integer  |
+-------+---------+
|MSFT   |100      |
|IBM    |200      |
|AAPL   |400      |
+-------+---------+

Parsing XLSX

parseXlsx(Binary data, Boolean firstLineIsHeader, String sheetName)

Parse the XLSX file and returns a table.

1
2
3
fileSystem fs = session.getFileSystem();
binary toParse = fs.readBinaryFile("/path/to/file/.../test.xlsx");
table t = parseXlsx(toParse,true,"Sheet1");

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
+-----------------+
|     Sheet1      |
+-------+---------+
|Symbol |Quantity |
|String |Integer  |
+-------+---------+
|MSFT   |100      |
|IBM    |200      |
|AAPL   |400      |
+-------+---------+

AMI Spreadsheet Classes

For more complex use cases, AMI provides a set of spreadsheet classes that facilitate various operations on spreadsheets. These classes offer methods for reading, writing, and modifying spreadsheet data. The classes include:

SpreadSheet Builder:

  • Purpose: Used to generate a new spreadsheet within AMI web environment.
  • Features: Required for creating both flexsheets and worksheets. Provides foundational methods for building spreadsheet structures.
  • Use Case: Initialize a new spreadsheet project, laying the groundwork for further customization and data manipulation.
  • Note: Currently, combining both flexsheets and worksheets within a single spreadsheet builder is not supported.

SpreadSheet Flexsheet:

  • Purpose: Designed to create and manage flexsheets in AMI.
  • Features: Supports workflows involving Excel to AMI and back to Excel. Facilitates flexible data handling and transformation within AMI.
  • Use Case: Best suited for scenarios where users need to import Excel spreadsheets into AMI, manipulate the data, and then export it back to Excel.

Spreadsheet Worksheet:

  • Purpose: Used to generate and manage individual worksheets in AMI.
  • Features: Focuses on the workflow from AMI to Excel, allowing detailed operations on specific worksheets.
  • Use Case: Best suited for exporting data from AMI into Excel.

SpreadSheet Builder

Method Return Type Description
addSheet(TablePanel tablePanel, String sheetName, Boolean onlySelectedRows, Boolean shouldFormat) Boolean Adds a new worksheet to the spreadsheet builder.
addFlexSheet(String name) Boolean Adds a new flexsheet to the spreadsheet builder.
deleteSheet(String sheetName) Boolean Deletes the specified spreadsheet from the spreadsheet builder.
getExcelPosition(Integer position) String Returns the alphabetical representation of the zero-based position.
loadExistingSheets(Binary data) Boolean Loads the binary data of an XLSX file into individual flex sheets.
getSheetNames() Set Returns a set containing the names of the sheets added, including hidden sheets.
getWorksheet(String worksheetName) Object Returns the spreadsheet in the SpreadSheetFlexsheet class.
getWorksheet(String worksheetName) Object Returns the spreadsheet in the SpreadSheetWorksheet class.
hideSheet(String sheetName) Boolean Hides the specified spreadsheet.
showSheet(String sheetName) Boolean Shows the specified hidden spreadsheet.
renameSheet(String sheetName, String newSheetName) Boolean Rename the specified spreadsheet.
build() Boolean Builds and returns a binary containing the spreadsheet.

addSheet(TablePanel tablePanel, String sheetName, Boolean onlySelectedRows, Boolean shouldFormat)

Adds a new worksheet to the spreadsheet builder.

1
2
3
spreadSheetBuilder builder = new SpreadSheetBuilder();
tablepanel tp = layout.getPanel("datamodel");
builder.addSheet(tp, "Sheet2", false, false);

1
true


addFlexSheet(String name)

Adds a new flexsheet to the spreadsheet builder.

1
2
3
4
5
spreadSheetBuilder builder = new SpreadSheetBuilder();
fileSystem fs = session.getFileSystem();
binary b = fs.readBinaryFile("/path/to/file/.../test.xlsx");
builder.loadExistingSheets(b);
builder.addFlexSheet("Sheet2");

1
true


deleteSheet(String sheetName)

Deletes the specified spreadsheet from the spreadsheet builder.

1
2
3
4
5
spreadSheetBuilder builder = new SpreadSheetBuilder();
fileSystem fs = session.getFileSystem();
binary b = fs.readBinaryFile("/path/to/file/.../test.xlsx");
builder.loadExistingSheets(b);
builder.deleteSheet("Sheet1");

1
true


getExcelPosition(Integer position)

Returns the alphabetical representation of the zero-based position.

1
2
3
4
5
spreadSheetBuilder builder = new SpreadSheetBuilder();
fileSystem fs = session.getFileSystem();
binary b = fs.readBinaryFile("/path/to/file/.../test.xlsx");
builder.loadExistingSheets(b);
builder.getExcelPosition(2);

1
C


loadExistingSheets(Binary data)

Loads the binary data of an XLSX file into individual flex sheets.

1
2
3
4
spreadSheetBuilder builder = new SpreadSheetBuilder();
fileSystem fs = session.getFileSystem();
binary b = fs.readBinaryFile("/path/to/file/.../test.xlsx");
builder.loadExistingSheets(b);

1
true


getSheetNames()

Returns a set containing the names of the sheets added, including hidden sheets.

1
builder.getSheetNames();

1
["Sheet1", "Sheet2", "Sheet3"]


getWorksheet(String worksheetName)

Returns the spreadsheet in the SpreadSheetFlexsheet class.

1
2
3
4
5
spreadSheetBuilder builder = new SpreadSheetBuilder();
fileSystem fs = session.getFileSystem();
binary b = fs.readBinaryFile("/path/to/file/.../test.xlsx");
builder.loadExistingSheets(b);
spreadSheetFlexsheet flex = builder.getWorksheet("Sheet1");

1
spreadsheet.AmiWebSpreadSheetFlexsheet@7145684a


getWorksheet(String worksheetName)

Returns the spreadsheet in the SpreadSheetWorksheet class.

1
spreadSheetWorksheet ws = builder.getWorksheet("Sheet1");

1
spreadsheet.AmiWebSpreadSheetWorksheet@41a89499


hideSheet(String sheetName)

Hides the specified spreadsheet.

1
builder.hideSheet("Sheet1");

1
true


showSheet(String sheetName)

Shows the specified hidden spreadsheet.

1
builder.showSheet("Sheet1");

1
true


renameSheet(String sheetName, String newSheetName)

Rename the specified spreadsheet.

1
builder.renameSheet("Sheet1", "New1");

1
true


build()

Builds and returns a binary containing the spreadsheet.

1
2
3
4
5
6
spreadSheetBuilder builder = new SpreadSheetBuilder();
fileSystem fs = session.getFileSystem();
binary b = fs.readBinaryFile("/path/to/file/.../test.xlsx");
builder.loadExistingSheets(b);
binary out = builder.build();
fs.writeBinaryFile("/path/to/save/.../test1.xlsx", out, false);

1
true


SpreadSheet Flexsheet

Method Return Type Description
getValue(String dimension) Boolean Gets the value of the specified cell.
getValues(String dimension, Boolean hasHeader, List classes) Boolean Returns the values of the specified range of cells as a table.
getValuesNamedRange(String nameRange, Boolean hasHeader, List classes) Boolean Returns the values of the specified name range as a table.
getTitle() String Gets the name of the sheet.
getStyle(String dimension) Boolean Gets the style id associated with the specified cell.
setValue(String dimension, Object value) Set Sets the value for the specified range of cells.
setValue(String dimension, Table value, Boolean useHeader) Object Sets the value for the specified range of cells.
setValueNamedRange(String nameRange, Object value) Object Sets the values for the specified named range of cells.
setTitle(String title) Boolean Sets the name of the sheet.
setStyle(String dimension) Boolean Sets the style id for the specified range of cells.

getValue(String dimension)

Gets the value of the specified cell.

1
2
3
4
5
6
spreadSheetBuilder builder = new SpreadSheetBuilder();
fileSystem fs = session.getFileSystem();
binary b = fs.readBinaryFile("/path/to/file/.../test.xlsx");
builder.loadExistingSheets(b);
spreadSheetFlexsheet flex = builder.getWorksheet("Sheet1");
flex.getValue("A2");

1
100


getValues(String dimension, Boolean hasHeader, List classes)

Returns the values of the specified range of cells as a table.
If the values are of a date type in Excel, they are returned as strings.

1
2
3
spreadSheetFlexsheet flex = builder.getWorksheet("Sheet1");
list classes = new list("string", "integer");
flex.getValues("A1:B4", true, classes);

1
2
3
4
5
6
7
8
+-------+---------+
|Symbol |Quantity |
|String |Integer  |
+-------+---------+
|MSFT   |100      |
|IBM    |200      |
|AAPL   |400      |
+-------+---------+

To manage large Excel file with many columns, use the following approach: 

1
2
3
4
5
spreadSheetFlexsheet flex = builder.getWorksheet("Sheet1");
list classes = new list();
int columnscount = 100; 
for (int i = 0; i < columnscount; i++) {classes.add("string");}
table t = flex.getValues("A1:CV1001", true, classes);

1
2
3
4
5
6
7
8
+-------+---------+
|Symbol |Quantity |
|String |Integer  |
+-------+---------+
|MSFT   |100      |
|IBM    |200      |
|AAPL   |400      |
+-------+---------+

Note: In this example, all columns are set to string type. You can modify the column types using Table::alterColumn(currentColumnName, newColumnName, newTypeOrNull).


getValuesNamedRange(String nameRange, Boolean hasHeader, List classes)

Returns the values of the specified name range as a table.
namedRange refers to the Names defined in the Name Manager in Excel. 

1
2
3
spreadSheetFlexsheet flex = builder.getWorksheet("Sheet1");
list classes = new list("string", "integer");
flex.getValuesNamedRange("Sample", true, classes);

1
2
3
4
5
6
7
8
+-------+---------+
|Symbol |Quantity |
|String |Integer  |
+-------+---------+
|MSFT   |100      |
|IBM    |200      |
|AAPL   |400      |
+-------+---------+


getTitle()

Gets the value of the specified cell. 

1
2
spreadSheetFlexsheet flex = builder.getWorksheet("Sheet1");
flex.getTitle();

1
Sheet1


getStyle(String dimension)

Gets the style id associated with the specified cell. 

1
2
spreadSheetFlexsheet flex = builder.getWorksheet("Sheet1");
flex.getStyle("A2");

1
1


setValue(String dimension, Object value)

Sets the value for the specified range of cells.

1
2
3
4
spreadSheetFlexsheet flex = builder.getWorksheet("Sheet1");
list classes = new list("string", "integer");
flex.setValue("A2", "NVDA");
flex.getValues("A1:B4", true, classes);

1
2
3
4
5
6
7
8
+-------+---------+
|Symbol |Quantity |
|String |Integer  |
+-------+---------+
|NVDA   |100      |
|IBM    |200      |
|AAPL   |400      |
+-------+---------+


setValue(String dimension, Table value, Boolean useHeader)

Sets the style id associated with the specified cell. 

1
2
3
4
5
spreadSheetFlexsheet flex = builder.getWorksheet("Sheet1");
create table fruits (name string, quantity integer, price double);
insert into fruits values ("apple", 3, 2.0), ("mango", 5, 4.5), ("pear", 2, 3.5);
table fruits = select * from fruits;
flex.setValue("A1", fruits, true);

1
true


setValueNamedRange(String nameRange, Object value)

Sets the values for the specified named range of cells.
namedRange refers to the Names defined in the Name Manager in Excel. 

1
2
  spreadSheetFlexsheet fs = builder.getWorksheet("Sheet1");
  flex.setValueNamedRange("Sample", fruits);

1
true


setTitle(String title)

Sets the name of the sheet. 

1
2
spreadSheetFlexsheet flex = builder.getWorksheet("Sheet1");
flex.setTitle("Renamed");

1
true


setStyle(String dimension)

Sets the style id for the specified range of cells. 

1
2
3
spreadSheetFlexsheet flex = builder.getWorksheet("Sheet1");
int styleid = flex.getStyle("A2");
flex.setStyle("A3", styleid);

1
true


Spreadsheet Worksheet

Method Return Type Description
getTable() Table Returns the table.
getTitle() String Gets the name of the sheet.
setTitle(String title) String Sets the name of the sheet.
addColWithFormula(Integer position, String colName, String formula) Boolean Creates a new formula based column.

getTable()

Returns the table. 

1
2
3
4
5
6
spreadSheetBuilder builder = new SpreadSheetBuilder();
tablepanel tp = layout.getPanel("datamodel");
builder.addSheet(tp, "Sheet2",false, false);

spreadSheetworksheet ws = builder.getWorksheet("Sheet2");   
ws.getTable();

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
+---------------+
|    Sheet2     |
+------+--------+
|Symbol|Quantity|
|String|String  |
+------+--------+
|MSFT  |100     |
|IBM   |200     |
|AAPL  |400     |
+------+--------+


getTitle()

Gets the name of the sheet. 

1
2
spreadSheetworksheet ws = builder.getWorksheet("Sheet2");   
ws.getTitle();

1
Sheet2


setTitle(String title)

Sets the name of the sheet. 

1
2
spreadSheetworksheet ws = builder.getWorksheet("Sheet2");   
ws.setTitle("Renamed");

1
true


addColWithFormula(Integer position, String colName, String formula)

Creates a new formula based column. 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
spreadSheetBuilder builder = new SpreadSheetBuilder();
tablepanel tp = layout.getPanel("datamodel2");
builder.addSheet(tp, "fruits", false, false);

spreadSheetworksheet ws = builder.getWorksheet("fruits");
ws.getTable();
+----------------------+
|        fruits        |
+------+--------+------+
|Name  |Quantity|Price |
|String|String  |String|
+------+--------+------+
|apple |3       |2.0   |
|mango |5       |4.5   |
|pear  |2       |3.5   |
+------+--------+------+

ws.addColWithFormula(3, "total", "`Price`*`Quantity`");   
session.downloadToBrowser("fruits.xlsx",builder.build());


Downloading of SpreadSheets

Users can download their modified spreadsheets from AMI. This feature ensures that any changes made within the AMI environment can be saved and utilized outside of it.

From AMI Tables

To download an existing table in AMI as a spreadsheet, click on the Download as Spread Sheet option.

From Datamodels

To download from Datamodels, use either of the following AMIScripts:

Session.downloadToBrowser(String fileName, String fileData)

1
2
3
4
5
6
7
spreadSheetBuilder builder = new SpreadSheetBuilder();  
fileSystem fs = session.getFileSystem();                            
binary b = fs.readBinaryFile("/path/to/file/.../test.xlsx");    
builder.loadExistingSheets(b);  
spreadSheetFlexsheet flex = builder.getWorksheet("Sheet1");
binary out = builder.build();   
session.downloadToBrowser("test.xlsx",builder.build());

FileSystem.writeFile(String file, String data, Boolean shouldAppend)

1
2
3
4
5
6
7
spreadSheetBuilder builder = new SpreadSheetBuilder();  
fileSystem fs = session.getFileSystem();                            
binary b = fs.readBinaryFile("/path/to/file/.../test.xlsx");    
builder.loadExistingSheets(b);  
spreadSheetFlexsheet flex = builder.getWorksheet("Sheet1"); 
binary out = builder.build();       
fs.writeBinaryFile("/path/to/file/.../test2.xlsx", out, false);

Example Workflows

This section provides example workflows that demonstrate how to use AMI's spreadsheet features.

Modify contents of a single cell

 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
  // Create a new SpreadSheetBuilder
  SpreadSheetBuilder builder = new SpreadSheetBuilder();    

  FileSystem fs = session.getFileSystem();                  

  // Grabs the Excel file and saves the Binary          
  Binary b = fs.readBinaryFile("/path/to/file/.../test.xlsx");  

  if(b ==null){
    session.alert("file doesnt exist");
  }

  // Loads the Excel file using the Binary
  builder.loadExistingSheets(b);    

  // Adds a new FlexSheet to enable cell edits
  SpreadSheetFlexsheet  fs2 = builder.getWorksheet("Sheet1");   

  // Adds "LOAD EDIT CELL" to cell E5
  fs2.setValue("E5:E5", "LOAD EDIT CELL");  

  // Builds a Binary from the edited SpreadSheet
  Binary out = builder.build();     

  // Exports the edited SpreadSheet from the built Binary
  fs.writeBinaryFile("/path/to/file/.../test1.xlsx", out,false);

Modify column content with conditions

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
  spreadSheetBuilder builder = new SpreadSheetBuilder();    
  fileSystem fs = session.getFileSystem();                          
  binary b = fs.readBinaryFile("/path/to/file/.../test.xlsx");  
  builder.loadExistingSheets(b);    
  spreadSheetFlexsheet flex = builder.getWorksheet("Sheet1");   

  // Sets the column header
  flex.setValue("C1", ">300 ?"); 

  // Sets the column content
  for (int i=2; i<5; i++){
    int qty = flex.getValue("B"+{i});
    if( qty > 300 ) {
      flex.setValue("C"+{i}, "Yes");
    } else 
      flex.setValue("C"+{i}, "No");
  }

  binary out = builder.build();     
  fs.writeBinaryFile("/path/to/file/.../test2.xlsx", out, false);