Excel #

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

Also checkout our new animations library: AnimatedText

Table of Contents #

• Installing
• Usage
  • Breaking Changes
  • Imports
  • Read xlsx file
  • Read xlsx file from Asset Folder
  • Create xlsx file
  • Update Cell
    • Cell Style options
  • Copy Sheet
  • Rename Sheet
  • Delete Sheet
  • Link Sheet
  • Un-Link Sheet
  • Merge Cells
  • Get Merged Cells
  • Un-Merge Cells
  • Find and Replace
  • Insert Row Iterables
    • Iterables Options
  • Append Row
  • Get default sheet
  • Set default sheet
  • Saving
• Frequent Issues
• Upcoming Features
• Donate (Be the First one)

Lets Get Started #

1. Depend on it #

Add this to your package's pubspec.yaml file:

dependencies:
  excel: ^1.1.5

2. Install it #

You can install packages from the command line:

with pub:

$  pub get

with Flutter:

$  flutter packages get

3. Import it #

Now in your Dart code, you can use:

    import 'package:excel/excel.dart';

Usage #

Breaking Changes for those moving from 1.0.8 and below ----> 1.0.9 and above versions #

The necessary changes to be made to updateCell function in order to prevent the code from breaking.

    
    excel.updateCell('SheetName', CellIndex.indexByString("A2"), "Here value", backgroundColorHex: "#1AFF1A", horizontalAlign: HorizontalAlign.Right);
    
    // Now in the above code wrap the optional arguments with CellStyle() and pass it to optional cellStyle parameter.
    // So the resulting code will look like
    
    excel.updateCell('SheetName', CellIndex.indexByString("A2"), "Here value", cellStyle: CellStyle( backgroundColorHex: "#1AFF1A", horizontalAlign: HorizontalAlign.Right ) );
    

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);
    
    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");
      }
    }
    

Read XLSX from Flutter's Asset Folder #

    import 'package:flutter/services.dart' show ByteData, rootBundle;
    
    /* Your blah blah code here */
    
    ByteData data = await rootBundle.load("assets/existing_excel_file.xlsx");
    var bytes = data.buffer.asUint8List(data.offsetInBytes, data.lengthInBytes);
    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 New XLSX File #

    var excel = Excel.createExcel(); // automatically creates 1 empty sheet: Sheet1
    

Update Cell values #

    /* 
     * sheetObject.updateCell(cell, value, { CellStyle (Optional)});
     * sheetObject created by calling - // Sheet sheetObject = excel['SheetName'];
     * cell can be identified with Cell Address or by 2D array having row and column Index;
     * Cell Style options are optional
     */
     
     Sheet sheetObject = excel['SheetName'];
     
     CellStyle cellStyle = CellStyle(backgroundColorHex: "#1AFF1A", fontFamily : getFontFamily(FontFamily.Calibri));
     
     cellStyle.underline = Underline.Single; // or Underline.Double
     
     
     var cell = sheetObject.cell(CellIndex.indexByString("A1"));
     cell.value = 8; // dynamic values support provided;
     cell.cellStyle = cellStyle;
     
     // printing cell-type
     print("CellType: "+ cell.cellType.toString());
     
     ///
     /// Inserting and removing column and rows
     
     // insert column at index = 8
     sheetObject.insertColumn(8);
     
     // remove column at index = 18
     sheetObject.removeColumn(18);
     
     // insert row at index = 82
     sheetObject.removeRow(82);
     
     // remove row at index = 80
     sheetObject.removeRow(80);
     
     

Cell-Style Options #

Copy sheet contents to another sheet #

    /*
     * excel.copy(String 'existingSheetName', String 'anotherSheetName');
     * existingSheetName should exist in excel.tables.keys in order to successfully copy
     * if anotherSheetName does not exist then it will be automatically created.
     *
     */
     
     excel.copy('existingSheetName', 'anotherSheetName');
     

Rename sheet #

    /*
     * excel.rename(String 'existingSheetName', String 'newSheetName');
     * existingSheetName should exist in excel.tables.keys in order to successfully rename
     *
     */
     
     excel.rename('existingSheetName', 'newSheetName');
     

Delete sheet #

    /*
     * excel.delete(String 'existingSheetName');
     * (existingSheetName should exist in excel.tables.keys) and (excel.tables.keys.length >= 2), in order to successfully delete.
     * 
     */
     
     excel.delete('existingSheetName');
     

Link sheet #

    /*
     * excel.link(String 'sheetName', Sheet sheetObject);
     * 
     * Any operations performed on (object of 'sheetName') or sheetObject then the operation is performed on both.
     * if 'sheetName' does not exist then it will be automatically created and linked with the sheetObject's operation.
     *
     */
     
     excel.link('sheetName', sheetObject);
     

Un-Link sheet #

    /*
     * excel.unLink(String 'sheetName');
     * In order to successfully unLink the 'sheetName' then it must exist in excel.tables.keys
     *
     */
     
     excel.unLink('sheetName');
     
     // After calling the above function be sure to re-make a new reference of this.
     
     Sheet unlinked_sheetObject = excel['sheetName'];
     

Merge Cells #

   /* 
    * sheetObject.merge(CellIndex starting_cell, CellIndex ending_cell, dynamic 'customValue');
    * sheetObject created by calling - // Sheet sheetObject = excel['SheetName'];
    * starting_cell and ending_cell can be identified with Cell Address or by 2D array having row and column Index;
    * customValue is optional
    */

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

Get Merged Cells List #

     // Check which cells are merged
     
     sheetObject.spannedItems.forEach((cells) {
       print("Merged:" + cells.toString());
     });
   

Un-Merge Cells #

   /* 
    * sheetObject.unMerge(cell);
    * sheetObject created by calling - // Sheet sheetObject = excel['SheetName'];
    * 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.
    */
     
     sheetObject.unMerge("A1:E4");
   

Find and Replace #

   /* 
    * int replacedCount = sheetObject.findAndReplace(source, target);
    * sheetObject created by calling - // Sheet sheetObject = excel['SheetName'];
    * source is the string or ( User's Custom Pattern Matching RegExp )
    * target is the string which is put in cells in place of source
    * 
    * it returns the number of replacements made
    */
     
     int replacedCount = sheetObject.findAndReplace(Flutter', 'Google');
     

Insert Row Iterables #

    /* 
     * sheetObject.insertRowIterables(list-iterables, rowIndex, iterable-options?);
     * sheetObject created by calling - // Sheet sheetObject = excel['SheetName'];
     * list-iterables === list of iterables which has to be put in specific row
     * rowIndex === the row in which the iterables has to be put
     * Iterable options are optional
     */
     
     /// It will put the list-iterables in the 8th index row
     List<String> dataList = ["Google", "loves", "Flutter", "and", "Flutter", "loves", "Google"];
     
     sheetObject.insertRowIterables(dataList, 8);
   

Iterable Options #

Append Row #

   /* 
    * sheetObject.appendRow(list-iterables);
    * sheetObject created by calling - // Sheet sheetObject = excel['SheetName'];
    * list-iterables === list of iterables
    */
    
     sheetObject.appendRow(["Flutter", "till", "Eternity"]);
   

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);
   });
   

Frequent Issues #

Having Trouble using excel i.e: every version of some_other_library depends on version..... blah blah blah #

Similar issues have been solved in the past : #26, #25, #11

Features coming in next version #

On-going implementation for future:

• Formulas
• Password Protection
• Conversion to PDF

Donate (Be the first one) #

Buy me some food to survive, I will add more features, If I remain alive.