Excel

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

Road-map:

Breaking changes from 4.x.x to 5.x.x

Breaking changes from 3.x.x to 4.x.x

Breaking changes from 2.x.x to 3.x.x

If you find this tool useful, please drop a ⭐️

Usage

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].maxColumns);
 print(excel.tables[table].maxRows);
 for (var row in excel.tables[table].rows) {
 for (var cell in row) {
 print('cell ${cell.rowIndex}/${cell.columnIndex}');
 final value = cell.value;
 final numFormat = cell.cellStyle?.numberFormat ?? NumFormat.standard_0;
 switch(value){
 case null:
 print(' empty cell');
 print(' format: ${numFormat}');
 case TextCellValue():
 print(' text: ${value.value}');
 case FormulaCellValue():
 print(' formula: ${value.formula}');
 print(' format: ${numFormat}');
 case IntCellValue():
 print(' int: ${value.value}');
 print(' format: ${numFormat}');
 case BoolCellValue():
 print(' bool: ${value.value ? 'YES!!' : 'NO..' }');
 print(' format: ${numFormat}');
 case DoubleCellValue():
 print(' double: ${value.value}');
 print(' format: ${numFormat}');
 case DateCellValue():
 print(' date: ${value.year} ${value.month} ${value.day} (${value.asDateTimeLocal()})');
 case TimeCellValue():
 print(' time: ${value.hour} ${value.minute} ... (${value.asDuration()})');
 case DateTimeCellValue():
 print(' date with time: ${value.year} ${value.month} ${value.day} ${value.hour} ... (${value.asDateTimeLocal()})');
 }
 print('$row');
 }
 }
}

Read XLSX in Flutter Web

/// Use FilePicker to pick files in Flutter Web

FilePickerResult pickedFile = await FilePicker.platform.pickFiles(
 type: FileType.custom,
 allowedExtensions: ['xlsx'],
 allowMultiple: false,
);
/// file might be picked

if (pickedFile != null) {
 var bytes = pickedFile.files.single.bytes;
 var excel = Excel.decodeBytes(bytes);
 for (var table in excel.tables.keys) {
 print(table); //sheet Name
 print(excel.tables[table].maxColumns);
 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 ......other important..... 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);
for (var table in excel.tables.keys) {
 print(table); //sheet Name
 print(excel.tables[table].maxColumns);
 print(excel.tables[table].maxRows);
 for (var row in excel.tables[table].rows) {
 print('$row');
 }
}

Create New XLSX File

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

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 = null; // removing any value
cell.value = TextCellValue('Some Text');
cell.value = IntCellValue(8);
cell.value = BoolCellValue(true);
cell.value = DoubleCellValue(13.37);
cell.value = DateCellValue(year: 2023, month: 4, day: 20);
cell.value = TimeCellValue(hour: 20, minute: 15, second: 5, millisecond: ...);
cell.value = DateTimeCellValue(year: 2023, month: 4, day: 20, hour: 15, ...);
cell.cellStyle = cellStyle;
// setting the number style
cell.cellStyle = (cell.cellStyle ?? CellStyle()).copyWith(
 /// for IntCellValue, DoubleCellValue and BoolCellValue use; 
 numberFormat: CustomNumericNumFormat('#,##0.00 \\m\\2'),
 /// for DateCellValue and DateTimeCellValue use:
 numberFormat: CustomDateTimeNumFormat('m/d/yy h:mm'),
 /// for TimeCellValue use:
 numberFormat: CustomDateTimeNumFormat('mm:ss'),
 /// a builtin format for dates
 numberFormat: NumFormat.standard_14,
 
 /// a builtin format that uses a red text color for negative numbers
 numberFormat: NumFormat.standard_38,
 // The numberFormat changes automatially if you set a CellValue that 
 // does not work with the numberFormat set previously. So in case you
 // want to set a new value, e.g. from a date to a decimal number, 
 // make sure you set the new value first and then your custom
 // numberFormat).
);
// printing cell-type
print('CellType: ' + switch(cell.value) {
 null => 'empty cell',
 TextCellValue() => 'text',
 FormulaCellValue() => 'formula',
 IntCellValue() => 'int',
 BoolCellValue() => 'bool',
 DoubleCellValue() => 'double',
 DateCellValue() => 'date',
 TimeCellValue => 'time',
 DateTimeCellValue => 'date with time',
});
///
/// 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.insertRow(82);
// remove row at index = 80
sheetObject.removeRow(80);

Cell-Style Options

Borders

/*
 *
 * Defines thin borders on the left and right of the cell, red thin border on the top
 * and blue medium border on the bottom.
 *
 */
CellStyle cellStyle = CellStyle(
 leftBorder: Border(borderStyle: BorderStyle.Thin),
 rightBorder: Border(borderStyle: BorderStyle.Thin),
 topBorder: Border(borderStyle: BorderStyle.Thin, borderColorHex: 'FFFF0000'),
 bottomBorder: Border(borderStyle: BorderStyle.Medium, borderColorHex: 'FF0000FF'),
);

Make sheet RTL

/*
 * set rtl to true for making sheet to right-to-left
 * default value of rtl = false ( which means the fresh or default sheet is ltr )
 *
 */
var sheetObject = excel['SheetName'];
sheetObject.rtl = true;

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, TextCellValue('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: TextCellValue('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<CellValue> dataList = [TextCellValue('Google'), TextCellValue('loves'), TextCellValue('Flutter'), TextCellValue('and'), TextCellValue('Flutter'), TextCellValue('loves'), TextCellValue('Excel')];
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([TextCellValue('Flutter'), TextCellValue('till'), TextCellValue('Eternity')]);

Get Default Opening Sheet

/*
 * method which returns the name of the default sheet
 * excel.getDefaultSheet();
 */
var defaultSheet = excel.getDefaultSheet();
print('Default Sheet:' + defaultSheet.toString());

Set Default Opening Sheet

/*
 * method which sets the name of the default sheet
 * returns bool if successful then true else false
 * excel.setDefaultSheet(sheet);
 * sheet = 'SheetName'
 */
var isSet = excel.setDefaultSheet(sheet);
if (isSet) {
 print('$sheet is set to default sheet.');
} else {
 print('Unable to set $sheet to default sheet.');
}

Saving

On Flutter Web

// when you are in flutter web then save() downloads the excel file.
// Call function save() to download the file
var fileBytes = excel.save(fileName: 'My_Excel_File_Name.xlsx');

On Android / iOS

var fileBytes = excel.save();
var directory = await getApplicationDocumentsDirectory();
File(join('$directory/output_file_name.xlsx'))
 ..createSync(recursive: true)
 ..writeAsBytesSync(fileBytes);