excel 1.0.3

A flutter and dart library for creating, editing and updating excel sheets with compatible both on client and server side.

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

key	description
fontColorHex	Font Color eg. "#0000FF"
backgroundColorHex	Background color of cell eg. "#faf487"
wrap	Text wrapping enum TextWrapping { WrapText, Clip } eg. TextWrapping.Clip
verticalAlign	align text vertically enum VerticalAlign { Top, Middle, Bottom } eg. VerticalAlign.Top
horizontalAlign	align text horizontally enum HorizontalAlign { Left, Center, Right } eg. HorizontalAlign.Right

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