Donate (Be the First to Donate) #

Please consider donating if you think excel is helpful to you or that my work is valuable. I will be happy if you can help me upgrade my lazy laptop. Paypal Me on paypal.me/kawal7415

Excel #

Excel is a flutter and dart library for creating and updating excel-sheets for XLSX files.

Usage #

Adding dependency in pubspec.yaml #

    
dependencies:
    excel: ^1.0.3
        

Imports #

    import 'dart:io';
    import 'package:path/path.dart';
    import 'package:excel/excel.dart';
    

Read XLSX File #

    var file = "Path_to_pre_existing_Excel_File/excel_file.xlsx";
    var bytes = File(file).readAsBytesSync();
    var excel = Excel.decodeBytes(bytes, update: true);
    
    for (var table in excel.tables.keys) {
      print(table); //sheet Name
      print(excel.tables[table].maxCols);
      print(excel.tables[table].maxRows);
      for (var row in excel.tables[table].rows) {
        print("$row");
      }
    }
    

Create XLSX File #

    var excel = Excel.createExcel(); //automatically creates 3 empty sheets Sheet1, Sheet2 and Sheet3 
     
    //find desired sheet name in excel/file;
    for (var tableName in excel.tables.keys) {
      if( desiredSheetName.toString() == tableName.toString() ){
        sheet = tableName.toString();
        break;
       }
    }
    

Update Cell values #

     /* 
     * excel.updateCell('sheetName', cell, value, options?);
     * if sheet === 'sheetName' does not exist in excel, it will be created automatically after calling updateCell method
     * cell can be identified with Cell Address or by 2D array having row and column Index;
     * Cell options are optional
     */
     
     var sheet = 'SheetName';
     
     //update cell with cellAddress
     excel.updateCell(sheet, CellIndex.indexByString("A1"), "Here value of A1");
       
     //update cell with row and column index
     excel.updateCell(sheet, CellIndex.indexByColumnRow(columnIndex: 2, rowIndex: 0), "Here value of C1");
       
     //update cell and it's background color
     deocder.updateCell(sheet, CellIndex.indexByString("A2"), "Here value of A2", backgroundColorHex: "#1AFF1A")
     
     //update alignment
     excel.updateCell(sheet, CellIndex.indexByString("E5"), "Here value of E5", horizontalAlign: HorizontalAlign.Right);

     // Insert column at index = 17;
     excel.insertColumn(sheet, 17);
   
     // Remove column at index = 2
     excel.removeColumn(sheet, 2);
   
     // Insert row at index = 2;
     excel.insertRow(sheet, 2);
   
     // Remove row at index = 17
     excel.removeRow(sheet, 2);
   

Cell Options #

Merge Cells #

    /* 
    * excel.merge('sheetName', starting_cell, ending_cell, 'customValue');
    * sheet === 'sheetName' in which merging of rows and columns is to be done
    * starting_cell and ending_cell can be identified with Cell Address or by 2D array having row and column Index;
    * customValue is optional
    */

     excel.merge(sheet, CellIndex.indexByString("A1"), CellIndex.indexByString("E4"), customValue: "Put this text after merge");
   

Get Merged Cells List #

     // Check which cells are merged

     excel.getMergedCells(sheet).forEach((cells) {
       print("Merged:" + cells.toString());
     });
   

Un-Merge Cells #

    /* 
    * excel.unMerge(sheet, cell);
    * sheet === 'sheetName' in which un-merging of rows and columns is to be done
    * cell should be identified with string only with an example as "A1:E4"
    * to check if "A1:E4" is un-merged or not
    * call the method excel.getMergedCells(sheet); and verify that it is not present in it.
    */

     excel.unMerge(sheet, "A1:E4");
   

Get Default Opening Sheet #

    /* 
    * Asynchronous method which returns the name of the default sheet
    * excel.getDefaultSheet();
    */

     excel.getDefaultSheet().then((value) {
       print("Default Sheet:" + value.toString());
     });
     
     or
     
     var defaultSheet = await excel.getDefaultSheet();
     print("Default Sheet:" + defaultSheet.toString());
   

Set Default Opening Sheet #

    /* 
    * Asynchronous method which sets the name of the default sheet
    * returns bool if successful then true else false
    * excel.setDefaultSheet(sheet);
    * sheet = 'SheetName'
    */

     excel.setDefaultSheet(sheet).then((isSet) {
       if (isSet) {
           print("$sheet is set to default sheet.");
       } else {
           print("Unable to set $sheet to default sheet.");
       }
     });
     
     or
     
     var isSet = await excel.setDefaultSheet(sheet);
     if (isSet) {
       print("$sheet is set to default sheet.");
     } else {
       print("Unable to set $sheet to default sheet.");
     }
   

Saving XLSX File #

     // Save the Changes in file

     excel.encode().then((onValue) {
       File(join("Path_to_destination/excel.xlsx"))
       ..createSync(recursive: true)
       ..writeAsBytesSync(onValue);
   });
   

Features coming in next version #

On-going implementation for future:

• Formulas
• Font Family
• Text Size
• Italic
• Underline
• Bold
• Add row / column from Iterables

Paypal Me #

Paypal Me