Back

Excel



This plugin let's you manage an excel file in your application. Bring the power and capability of Microsoft's Excel into your DroidScript application. You can use it pretty much like a database for any informations that you want to display in a table. This library can be use from small to medium file.

Please note that this plugin is not affiliated with Microsoft.

To use excel, you must first load the plugin at the top of your script using the LoadPlugin method like this:

 app.LoadPlugin("Excel")

Then you can create an instance of excel like this:

 xls = app.CreateExcel( path, props )

path: An absolute path or relative path to an excel file. If file does not exist it will automatically create one for you.
props: The json mapping for the header of the excel file for ease of use as json object.

Example - Single Sheet Props

props = [
    {
        'Complete Name': 'name',
        'Date of Birth': 'birth',
        'Current Address': 'address',
        'Age': 'age'
    }
]
Copy

Example - Multiple Sheet Props

var props = [
    {
        'Last Name': 'lastName',
        'First Name': 'firstName',
        'Age': 'age'
    },
    {
        'Complete Name': 'comName',
        'Current Address': 'currAddress',
        'Favorite Color': 'favColor'
    }
]
Copy

Try the following example.

Create excel file and adds a sheet


app.LoadPlugin('Excel')

function OnStart()
{
    var props = [
        {
            'Name of Students': 'name',
            'Age': 'age',
            'Mobile Number': 'number'
        }
    ]
    xls = app.CreateExcel( "myFile.xlsx", props )
    xls.SetOnLoad( OnLoad )
    xls.Load()
}
function OnLoad()
{
    var rows = [
        { name: "John Doe", age: 26, number: 123456789 },
        { name: "Jane Doe", age: 27, number: 123456789 },
        { name: "Jhonny Doe", age: 28, number: 123456789 }
    ]
    sheet1 = xls.AddSheet( "Sheet1" )
    sheet1.AddRows( rows )
    xls.Save()
}
Copy

Load an existing excel file


app.LoadPlugin('Excel')

function OnStart()
{
    var props = [
        {
            'Name of Students': 'name',
            'Age': 'age',
            'Mobile Number': 'number'
        }
    ]
    xls = app.CreateExcel( "myFile.xlsx", props )
    xls.SetOnLoad( OnLoad )
    xls.Load()
}
function OnLoad()
{
    sheet1 = xls.GetSheet( "Sheet1" )
    rows = sheet1.GetRows()
    app.Alert( JSON.stringify( rows ) )
}
Copy

Reading multiple sheets


app.LoadPlugin('Excel')

function OnStart()
{
    // We assume 2 sheets with names "UserA" and "UserB"

    /*
    UserA
    Last Name | First Name | Age

    UserB
    Complete Name | Current Address | Favorite Color
    */

    var props = [
        {
            'Last Name': 'lastName',
            'First Name': 'firstName',
            'Age': 'age'
        },
        {
            'Complete Name': 'comName',
            'Current Address': 'currAddress',
            'Favorite Color': 'favColor'
        }
    ]
    xls = app.CreateExcel( "myFile.xlsx", props )
    xls.SetOnLoad( OnLoad )
    xls.Load()
}
function OnLoad()
{
    userA = xls.GetSheet( "UserA" )
    userB = xls.GetSheet( "UserB" )

    userA.AddRows([
        { lastName: "Stark", firstName: "Sansa", age: 18 },
        { lastName: "Stark", firstName: "Rob", age: 25 },
        { lastName: "Stark", firstName: "Brandon", age: 12 }
    ])

    userB.AddRows([
        {
            comName: "Arya Stark",
            currAddress: "Winterfell",
            favColor: "Black"
        }
    ])

    xls.Save()
}
Copy

Excel Methods

These are the methods of the excel object.

AddSheet( sheetName, props )

Adds a sheet into the excel file object.

sheetName: String. The name of the new sheet to be added.
props: Object. The props of the sheet. If not provided then the first prop is used.

AddSheet
app.LoadPlugin( 'Excel' )

function OnStart()
{
    var props = [
        {
            'Name of Students': 'name',
            'Mobile Number': 'number',
            'Age': 'age'
        }
    ]
    xls = app.CreateExcel( '/Internal/Documents/myFile.xlsx', props )
    xls.SetOnLoad( OnLoad )
    xls.SetOnSave( OnSave )
    xls.Load()
}

function OnLoad()
{
    sheet = xls.AddSheet( "Students" )
    xls.Save()
}

function OnSave()
{
    app.ShowPopup( "Excel file is saved successfully." )
}
Copy
AddSheet with props
app.LoadPlugin( 'Excel' )

function OnStart()
{
    var props = [
        {
            'Name of Students': 'name',
            'Mobile Number': 'number',
            'Age': 'age'
        }
    ]
    xls = app.CreateExcel( '/Internal/Documents/myFile.xlsx', props )
    xls.SetOnLoad( OnLoad )
    xls.Load()
}

function OnLoad()
{
    var prop = {
        "Title of the Movie": 'title',
        "Time Duration": 'dur',
        "Leading Character": 'lead'
    }
    sheet = xls.AddSheet( "Movies", prop )
    xls.Save()
}
Copy

GetFile()

Returns the relative path or absolute path to the file.

 var xlsFile = xls.GetFile()

GetSheet( sheetName )

Returns the sheet object by passing the sheet name.

sheetName: String|Number. The name or the number of the sheet.

 sheet1 = xls.GetSheet( "Sheet1" )
Or to get the first sheet, use
 sheet1 = xls.GetSheet( 1 )

Load()

Load the excel file.

 xls.Load()

Save()

Save the excel file. You should call this method before closing your app to avoid loss of data.

 xls.Save()

SetFont( fontName )

Sets a font-family to the excel document.

fontName: String. The name of the font. You can Arial, Calibri, Times New Roman, and more...

 xls.SetFont( "Arial" )

SetHeaderStyle(backgroundColor, fontWeight, align)

Sets the headers style of the excel document.

backgroundColor: String. RGB Hex color "#rrggbb"
fontWeight: String. 'Bold' or 'Normal'
align: String. "Left" or "Center" or "Right"

 xls.SetHeaderStyle( "#673ab7", "bold", "center" )

SetOnLoad( callback )

Calls a function on load.

callback: Function. The function to be called when the file is loaded.

app.LoadPlugin( 'Excel' )

function OnStart()
{
    var props = {
        'Name of Students': 'name',
        'Mobile Number': 'number',
        'Age': 'age'
    }
    xls = app.CreateExcel( '/Internal/Documents/myFile.xlsx', props )
    xls.SetOnLoad( OnLoad )
    xls.Load()
}

function OnLoad()
{
    app.ShowPopup( "Excel object is loaded and ready." )
    // do your excel stuff here
}
Copy

SetOnSave( callback )

Calls a function on save.


callback: Function. The function to be called when file is save successfully.

SetOrientation( orient )

Sets the orientation of the excel document.

orient: String. Values can be 'portrait' or 'landscape'. Default is 'portrait'.

 xls.SetOrientation( "landscape" )

SetStickyColumns( count )

Makes the position of the column fixed on the left.

count: Number. The number of columns from the left.

 xls.SetStickyColumns( 1 )

SetStickyRows( count )

Makes the position of the row fixed on the top.

count: Number. The number of columns from the top.

 xls.SetStickyRows( 1 )


Sheet Methods

These are the methods of the sheet object.

AddRows( rows )

Adds a row to a given sheet.

rows: Array. An array of row objects.

Multiple rows

rows = [
    { name: "John Doe", age: 26, number: 123456789 },
    { name: "Jane Doe", age: 27, number: 543216789 },
    { name: "Jonn Doe", age: 28, number: 987654321 }
]
sheet1.AddRows( rows )
Copy

Single row

row = { name: "John Doe", age: 26, number: 123456789 }
sheet1.AddRows( row )
Copy

FormatHeader( header )

Adds a format or styles for the header of certain column.

header: Object. The new header style object of the given prop.

sheet1 = xls.GetSheet( "Sheet1" )
var format = {
    col: "Name of Students",
    prop: "name" // required
}
sheet1.FormatHeader( format )
Copy

GetRows()

Returns the data of the sheet.

sheet1 = xls.GetSheet( "Sheet1" )
rows = sheet1.GetRows()
app.Alert( JSON.stringify( rows ) )
Copy

GetRowsEndAt( endRow, count )

Returns all rows that ends at a specified index.

endRow: Number. The index of the ending row. Starts from zero excluding the header row.
count: Number. The number of rows to be taken.

 sheet.GetRowsEndAt( 5, 4 ) // returns 2,3,4,5

GetRowsStartAt( startRow, count )

Returns all rows that starts at a specified index.

startRow: Number. The index of the starting row. Starts from zero excluding the header row.
count: Number. The number of rows to be taken.

 sheet.GetRowsStartAt( 5, 4 ) // returns 5,6,7,8

GetRowsWhere( prop, operator, value )

Returns all rows that pass a certain condition.

prop: String. The prop to be checked.
operator: String. The conditional operator to be use. Can be '==', '>', '>=', '<' and '<=' .
value: String|Number. The value use for checking.

 sheet.GetRowsWhere( 'age', '>=', 18 )

 sheet.GetRowsWhere( 'age', '==', 26 )

GetSheetName()

Returns the name of the sheet.

 name = sheet.GetSheetName()

RemoveRow( row )

Removes a row in the sheet.

row: Number. The index of the row. Starts from zero excluding the header row.
 sheet.RemoveRow( 0 )

SetColumnWidth( prop, width )

Sets the width of a given prop or column.

prop: String. The prop name or column name.
width: Number. The number of characters.

 sheet.SetColumnWidth( 'name', 20 ) // 20 characters

SetFormula( prop )

Sets a formula to the given column or prop.

prop: String. The prop name or column name.

 sheet.SetFormula( 'total' ).value = p => p.qty * p.price

The example above sets the value of column prop 'total' to the product of column prop 'qty' and column prop 'price'.

Formula will apply only after calling xls.Save()

SetHeaders( headers, options )

Sets a header object to the sheet.

headers: Array. An array of header objects.
options: String. A comma separated options for the columns. Values can be Left,Center,Right|Top,VCenter,Bottom|Bold|Italic

Array header
sheet1 = xls.GetSheet( "Sheet1" )
var headers = [ "Complete Name:comName", "Current Address:curAddr", "Mobile Number:num" ]
sheet1.SetHeaders( headers )
Copy
Basic object header
sheet1 = xls.GetSheet( "Sheet1" )
var headers = [
    { col: "Complete Name", prop: "comName" },
    { col: "Current Address", prop: "curAddr" },
    { col: "Mobile Number", prop: "num" }
]
sheet1.SetHeaders( headers )
Copy
Complete header object
    {
        col: String,
        prop: String,
        align: String. "Left" or "Center" or "Right",
        alignVertical: String. "Top" or "Center" or "Bottom",
        height: Number,
        span: Number,
        rowSpan: Number,
        wrap: Boolean,
        fontWeight: String. "Bold",
        fontStyle: String. "Italic",
        color: RGB Hex String,
        backgroundColor: String,
        borderColor: RGB Hex String,
        borderStyle: String. "Thick"
    }
Copy

SetRow( row, data )

Sets a new data to the given row.

row: Number. The index of the row. Starts from zero excluding the header row.
data: Object. A single row data object.

sheet = xls.GetSheet( "Sheet1" )
newData = { name: "New John Doe", age: 25, number: 79000123791 }
sheet.SetRow( 1, newData )
Copy


This plugin uses the following libraries: