Yunus Emre
Software Engineer
Flutter Excel Operations
If you want to read, update or create excel sheets in a flutter application, there are several libraries for that. In this article I will explain how to use the library I use for flutter excel operations.
Creating Flutter Excel
The first library that comes to mind for Excel processes is flutter excel library. I often use this library according to the details of the project. Especially if you want to copy a template and make updates on it, this library will do your job. However, adding an image to a cell in this library is not possible in the current versions. In Excel, you can add an image via url by typing the "=IMAGE(url)" function in the cell, but this is not a complete solution. Because if excel is Turkish, "PICTURE" is used instead of "IMAGE" and this time the function gives an error. In addition, since excel pulls information from outside with url, other users are constantly given a security warning. For this reason, the solution is a different library.
Syncfusion Flutter Xlsio
Syncfusion library to the application. I have shared some sample code below.
// Excel dosyasını oluştur.
final Workbook workbook = Workbook();
// İlk sayfayı eriş.
final Worksheet sheet = workbook.worksheets[0];
You can use the following code to merge the area between A1 and C5. The 2nd and 4th digits in parentheses represent the column rows.
sheet.getRangeByIndex(1, 1, 5, 3).merge();
We use the following code to insert an image in cell A1.
final ByteData imageData = await rootBundle.load('assets/images/logo.png');
final Uint8List imageBytes = imageData.buffer.asUint8List();
final Picture picture = sheet.pictures.addStream(1, 1, imageBytes1); //A1
picture.width = 173; //px
You can use the following code to insert an image with url information into an excel sheet. I load the relevant image in cell C69.
final response = await http.get(Uri.parse(url));
if (response.statusCode == 200) {
final Uint8List imageBytes = response.bodyBytes;
final Picture picture = sheet.pictures.addStream(69, 3, imageBytes);
picture.height = 50;
}
With the style of a cell we can change it as you like.
final Range mergedRange = sheet.getRangeByName('B1');
mergedRange.setText("Mr.\nYED");
mergedRange.cellStyle.bold = true; //kalınlık
mergedRange.cellStyle.hAlign = HAlignType.center; //ortala
mergedRange.cellStyle.vAlign = VAlignType.center; //ortala
You can set all the details such as the cell's background color, width and height in the same way.
You can set the margins of the page as follows. Measurements are in cm.
sheet.pageSetup.topMargin = 1.5;
sheet.pageSetup.bottomMargin = 1.5;
sheet.pageSetup.leftMargin = 0.7;
sheet.pageSetup.rightMargin = 0.7;
You can also set the page width via the code. For example, I want my template to be a single page and I want the user to print it without any editing, so I can fit the whole area on one page.
sheet.pageSetup.fitToPagesWide = 1; // Sayfayı genişliğine sığdır
sheet.pageSetup.fitToPagesTall = 0; // Yükseklik ayarını devre dışı bırak
sheet.pageSetup.printArea = 'A1:P75'; //A1 ile P75 aralığını yazdırma alanı olarak belirliyorum
I can also determine the bordering process through the code. In the example code, the A1 to P71 interval will be completely bordered.
Range range = sheet.getRangeByName('A1:P71');
range.cellStyle.borders.all.lineStyle = LineStyle.thin;
At the last stage, I want to save my excel file and get the url information where I can access this document.
// Dosyayı kaydet.
final List<int> bytes = workbook.saveAsStream();
workbook.dispose();
// Firebase Storage'a yükle
final Uint8List excelData = Uint8List.fromList(bytes);
final Reference ref = storage.ref("myDoc.xlsx");
final UploadTask uploadTask = ref.putData(excelData);
// Yükleme tamamlandığında URL'yi al
final TaskSnapshot snapshot = await uploadTask.whenComplete(() {});
final String newExcelURL = await snapshot.ref.getDownloadURL();
I uploaded the document to firebase storage and put the information to access this document in the newExcelURL variable. You can use this variable as you wish.
Note: When it comes to the Flutter excel process, the library I shared at the beginning of the article comes to mind first. However, the library I have explained how to use is very useful and fast. Especially for images, the solution is very simple. The only disadvantage is that it cannot use an excel template. If you have an excel template and you want to copy it and edit it, this is not possible in the current version. It creates a blank excel and you have to design it from scratch.
If you are working on reporting in enterprise applications, the libraries I mentioned will be useful for you. See you in the next article content...
1 Comment