Create, read, and edit an Excel file within an Android device.
Syncfusion Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. Also, converts Excel documents to PDF files.
Using this library, you can create an Excel file and save it into an Android device; read and edit an Excel file from an Android device.
Steps to create, read, and edit an Excel file in Xamarin:
Step 1: Create a new C# Xamarin.Forms application project.
Create a new C# Xamarin.Forms application project
Step 2: Select the template and required platforms to deploy the application. To share the portable assemblies across multiple platforms, .NET Standard code sharing strategy has been selected.
Select the template
Click here to know more about the code sharing.
Step 3: Install the Syncfusion.Xamarin.XlsIO NuGet package as reference to the .NET Standard project in your Xamarin application from NuGet.org.
Install NuGet package to the project
Step 4: Add new Forms XAML page in the portable project, if there is no XAML page defined in the App class. Else proceed to the Step 5.
- To add the new XAML page, right click on the project and select Add->New Item. Add a forms XAML page from the list and name it as MainPage.
- In the App class of portable project (App.xaml.cs), replace the existing constructor of App class with the following code to invoke the MainPage.
C#
public App() { InitializeComponent();
MainPage = new MainPage(); } |
Step 5: In the MainPage.xaml, add two new buttons as shown below.
XML
<?xml version="1.0" encoding="utf-8" ?> <ContentPage xmlns="http://xamarin.com/schemas/2014/forms" xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml" xmlns:local="clr-namespace:GettingStarted" x:Class="GettingStarted.MainPage"> <StackLayout VerticalOptions="Center"> <Button Text="Generate Document" Clicked="OnButtonClicked" HorizontalOptions="Center"/> <Button Text="Edit Document" Clicked="OnButtonClicked1" HorizontalOptions="Center"/> </StackLayout> </ContentPage>
On execution, it looks like the below in the Android device.
Output UI in the Android device
Step 6: Include the following namespaces in MainPage.xaml.cs file.
C#
using Syncfusion.XlsIO; using System; using System.IO; using Xamarin.Forms;
Step 7: Include the following code snippet in the click event of the button “Generate Document” in the MainPage.xaml.cs, to create an Excel file and save it into an Android device.
C#
void OnButtonClicked(object sender, EventArgs args)
{
//Create an instance of ExcelEngine.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
//Create a workbook with a worksheet
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
//Access first worksheet from the workbook instance.
IWorksheet worksheet = workbook.Worksheets[0];
Assembly executingAssembly = typeof(MainPage).GetTypeInfo().Assembly;
Stream inputStream = executingAssembly.GetManifestResourceStream("GettingStarted.AdventureCycles-Logo.png");
//Add a picture
IPictureShape shape = worksheet.Pictures.AddPicture(1, 1, inputStream);
//Disable gridlines in the worksheet
worksheet.IsGridLinesVisible = false;
//Enter values to the cells from A3 to A5
worksheet.Range["A3"].Text = "46036 Michigan Ave";
worksheet.Range["A4"].Text = "Canton, USA";
worksheet.Range["A5"].Text = "Phone: +1 231-231-2310";
//Make the text bold
worksheet.Range["A3:A5"].CellStyle.Font.Bold = true;
//Merge cells
worksheet.Range["D1:E1"].Merge();
//Enter text to the cell D1 and apply formatting.
worksheet.Range["D1"].Text = "INVOICE";
worksheet.Range["D1"].CellStyle.Font.Bold = true;
worksheet.Range["D1"].CellStyle.Font.RGBColor = Color.FromArgb(42, 118, 189);
worksheet.Range["D1"].CellStyle.Font.Size = 35;
//Apply alignment in the cell D1
worksheet.Range["D1"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
worksheet.Range["D1"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignTop;
//Enter values to the cells from D5 to E8
worksheet.Range["D5"].Text = "INVOICE#";
worksheet.Range["E5"].Text = "DATE";
worksheet.Range["D6"].Number = 1028;
worksheet.Range["E6"].Value = "12/31/2018";
worksheet.Range["D7"].Text = "CUSTOMER ID";
worksheet.Range["E7"].Text = "TERMS";
worksheet.Range["D8"].Number = 564;
worksheet.Range["E8"].Text = "Due Upon Receipt";
//Apply RGB backcolor to the cells from D5 to E8
worksheet.Range["D5:E5"].CellStyle.Color = Color.FromArgb(42, 118, 189);
worksheet.Range["D7:E7"].CellStyle.Color = Color.FromArgb(42, 118, 189);
//Apply known colors to the text in cells D5 to E8
worksheet.Range["D5:E5"].CellStyle.Font.Color = ExcelKnownColors.White;
worksheet.Range["D7:E7"].CellStyle.Font.Color = ExcelKnownColors.White;
//Make the text as bold from D5 to E8
worksheet.Range["D5:E8"].CellStyle.Font.Bold = true;
//Apply alignment to the cells from D5 to E8
worksheet.Range["D5:E8"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
worksheet.Range["D5:E5"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
worksheet.Range["D7:E7"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
worksheet.Range["D6:E6"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignTop;
//Enter value and applying formatting in the cell A7
worksheet.Range["A7"].Text = " BILL TO";
worksheet.Range["A7"].CellStyle.Color = Color.FromArgb(42, 118, 189);
worksheet.Range["A7"].CellStyle.Font.Bold = true;
worksheet.Range["A7"].CellStyle.Font.Color = ExcelKnownColors.White;
//Apply alignment
worksheet.Range["A7"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
worksheet.Range["A7"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
//Enter values in the cells A8 to A12
worksheet.Range["A8"].Text = "Steyn";
worksheet.Range["A9"].Text = "Great Lakes Food Market";
worksheet.Range["A10"].Text = "20 Whitehall Rd";
worksheet.Range["A11"].Text = "North Muskegon,USA";
worksheet.Range["A12"].Text = "+1 231-654-0000";
//Create a Hyperlink for e-mail in the cell A13
IHyperLink hyperlink = worksheet.HyperLinks.Add(worksheet.Range["A13"]);
hyperlink.Type = ExcelHyperLinkType.Url;
hyperlink.Address = "Steyn@greatlakes.com";
hyperlink.ScreenTip = "Send Mail";
//Enter details of products and prices
worksheet.Range["A15"].Text = " DESCRIPTION";
worksheet.Range["C15"].Text = "QTY";
worksheet.Range["D15"].Text = "UNIT PRICE";
worksheet.Range["E15"].Text = "AMOUNT";
worksheet.Range["A16"].Text = "Cabrales Cheese";
worksheet.Range["A17"].Text = "Chocos";
worksheet.Range["A18"].Text = "Pasta";
worksheet.Range["A19"].Text = "Cereals";
worksheet.Range["A20"].Text = "Ice Cream";
worksheet.Range["C16"].Number = 3;
worksheet.Range["C17"].Number = 2;
worksheet.Range["C18"].Number = 1;
worksheet.Range["C19"].Number = 4;
worksheet.Range["C20"].Number = 3;
worksheet.Range["D16"].Number = 21;
worksheet.Range["D17"].Number = 54;
worksheet.Range["D18"].Number = 10;
worksheet.Range["D19"].Number = 20;
worksheet.Range["D20"].Number = 30;
worksheet.Range["D23"].Text = "Total";
//Apply number format
worksheet.Range["D16:E22"].NumberFormat = "$.00";
worksheet.Range["E23"].NumberFormat = "$.00";
//Merge column A and B from row 15 to 22
worksheet.Range["A15:B15"].Merge();
worksheet.Range["A16:B16"].Merge();
worksheet.Range["A17:B17"].Merge();
worksheet.Range["A18:B18"].Merge();
worksheet.Range["A19:B19"].Merge();
worksheet.Range["A20:B20"].Merge();
worksheet.Range["A21:B21"].Merge();
worksheet.Range["A22:B22"].Merge();
//Apply incremental formula for column Amount by multiplying Qty and UnitPrice
application.EnableIncrementalFormula = true;
worksheet.Range["E16:E20"].Formula = "=C16*D16";
//Formula for Sum the total
worksheet.Range["E23"].Formula = "=SUM(E16:E22)";
//Apply borders
worksheet.Range["A16:E22"].CellStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
worksheet.Range["A16:E22"].CellStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
worksheet.Range["A16:E22"].CellStyle.Borders[ExcelBordersIndex.EdgeTop].Color = ExcelKnownColors.Grey_25_percent;
worksheet.Range["A16:E22"].CellStyle.Borders[ExcelBordersIndex.EdgeBottom].Color = ExcelKnownColors.Grey_25_percent;
worksheet.Range["A23:E23"].CellStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
worksheet.Range["A23:E23"].CellStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
worksheet.Range["A23:E23"].CellStyle.Borders[ExcelBordersIndex.EdgeTop].Color = ExcelKnownColors.Black;
worksheet.Range["A23:E23"].CellStyle.Borders[ExcelBordersIndex.EdgeBottom].Color = ExcelKnownColors.Black;
//Apply font setting for cells with product details
worksheet.Range["A3:E23"].CellStyle.Font.FontName = "Arial";
worksheet.Range["A3:E23"].CellStyle.Font.Size = 10;
worksheet.Range["A15:E15"].CellStyle.Font.Color = ExcelKnownColors.White;
worksheet.Range["A15:E15"].CellStyle.Font.Bold = true;
worksheet.Range["D23:E23"].CellStyle.Font.Bold = true;
//Apply cell color
worksheet.Range["A15:E15"].CellStyle.Color = Color.FromArgb(42, 118, 189);
//Apply alignment to cells with product details
worksheet.Range["A15"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
worksheet.Range["C15:C22"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
worksheet.Range["D15:E15"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
//Apply row height and column width to look good
worksheet.Range["A1"].ColumnWidth = 36;
worksheet.Range["B1"].ColumnWidth = 11;
worksheet.Range["C1"].ColumnWidth = 8;
worksheet.Range["D1:E1"].ColumnWidth = 18;
worksheet.Range["A1"].RowHeight = 47;
worksheet.Range["A2"].RowHeight = 15;
worksheet.Range["A3:A4"].RowHeight = 15;
worksheet.Range["A5"].RowHeight = 18;
worksheet.Range["A6"].RowHeight = 29;
worksheet.Range["A7"].RowHeight = 18;
worksheet.Range["A8"].RowHeight = 15;
worksheet.Range["A9:A14"].RowHeight = 15;
worksheet.Range["A15:A23"].RowHeight = 18;
//Save the workbook to stream in xlsx format.
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
//Save the stream as a file in the device
Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Invoice.xlsx", "application/msexcel", stream);
//To get valid license to use the library, please contact our sales or the license information page, https://www.syncfusion.com/sales/licensing
}
}
On clicking the “Generate Document” button, the following output will be generated and opened in the Android device for viewing.
Excel file created in the external storage device
Step 8: Include the following code snippet in the click event of the button “Edit Document” in the MainPage.xaml.cs, to read and edit an Excel file from an Android device.
C#
void OnButtonClicked1(object sender, EventArgs args)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
byte[] byteArray = Xamarin.Forms.DependencyService.Get<ISave>().Open("Invoice.xlsx");
Stream inputStream = new MemoryStream(byteArray);
//Opens the workbook
IWorkbook workbook = application.Workbooks.Open(inputStream);
//Access first worksheet from the workbook.
IWorksheet worksheet = workbook.Worksheets[0];
//Set Text in cell A3.
worksheet.Range["D17"].Number = worksheet.Range["D17"].Number - 2;
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
//Save the stream into XLSX file
Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("sample.xlsx", "application/msexcel", stream);
}
}
On clicking the Edit Document button, the following output will be generated and opened in the Android device for viewing.
Excel file read and edited from the external storage device
Step 9: The SaveAndView method under ISave interface is used to save the stream as file and invokes it for viewing. The save implementation for Android device should be in SaveAndroid.cs helper class.
C#
public async Task SaveAndView(string fileName, String contentType, MemoryStream stream)
{
string exception = string.Empty;
string root = null;
//Get the root path in Android device.
if (Android.OS.Environment.IsExternalStorageEmulated)
{
root = Android.OS.Environment.ExternalStorageDirectory.ToString();
}
else
root = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
//Create directory and file
Java.IO.File myDir = new Java.IO.File(root + "/Syncfusion");
myDir.Mkdir();
Java.IO.File file = new Java.IO.File(myDir, fileName);
//Remove if the file exists
if (file.Exists()) file.Delete();
try
{
FileOutputStream outs = new FileOutputStream(file);
outs.Write(stream.ToArray());
outs.Flush();
outs.Close();
}
catch (Exception e)
{
exception = e.ToString();
}
Android.Net.Uri path = Android.Net.Uri.FromFile(file);
string extension = Android.Webkit.MimeTypeMap.GetFileExtensionFromUrl(Android.Net.Uri.FromFile(file).ToString());
string mimeType = Android.Webkit.MimeTypeMap.Singleton.GetMimeTypeFromExtension(extension);
Intent intent = new Intent(Intent.ActionView);
intent.SetFlags(ActivityFlags.ClearTop | ActivityFlags.NewTask);
path = FileProvider.GetUriForFile(Android.App.Application.Context, Android.App.Application.Context.PackageName + ".provider", file);
intent.SetDataAndType(path, mimeType);
intent.AddFlags(ActivityFlags.GrantReadUriPermission);
Forms.Context.StartActivity(Intent.CreateChooser(intent, "Choose App"));
}
Also, the Open method of the SaveAndroid.cs class should contain the implementation for getting an Excel file from an Android device and return it as a byte array to be passed as input stream in the Workbook.Open() method as below.
C#
public byte[] Open(string fileName)
{
string root = null;
//Get the root path in Android device.
if (Android.OS.Environment.IsExternalStorageEmulated)
{
root = Android.OS.Environment.ExternalStorageDirectory.ToString();
}
else
root = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
//Create directory and file
Java.IO.File myDir = new Java.IO.File(root + "/Syncfusion");
myDir.Mkdir();
Java.IO.File file = new Java.IO.File(myDir, fileName);
byte[] byteArray = null;
string filePath = System.IO.Path.GetFullPath(file.Path);
byteArray = System.IO.File.ReadAllBytes(filePath);
return byteArray;
}
A complete working sample to create, read, and edit an Excel file within an Android device in Xamarin.Forms application using Syncfusion Excel (XlsIO) library can be downloaded from Read and edit Excel file in Android.zip.
Take a moment to peruse the documentation, where you can find basic worksheet data manipulation options along with features like Conditional Formatting, worksheet calculations through Formulas, adding Charts in worksheet or workbook, organizing and analyzing data through Tables and Pivot Tables, appending multiple records to worksheet using Template Markers, and most importantly PDF and Image conversions etc. with code examples.
See Also:
Create Excel file in Windows Forms
Create Excel file in ASP.NET Web Forms
Create Excel file in ASP.NET MVC
Create Excel file in ASP.NET Core
Click here to explore the rich set of Syncfusion Xamarin Excel (XlsIO) library features.
Starting with v16.2.0.x, if you reference Syncfusion assemblies from trial setup or from the NuGet feed, include a license key in your projects. Refer the link to learn about generating and registering Syncfusion license key in your application to use the components without trail message. |
Conclusion
I hope you enjoyed learning about how to Create, read, and edit an Excel file within an Android device.
You can refer to our Xamarin.Forms feature tour page to know about its other groundbreaking feature representations and documentation, and how to quickly get started for configuration specifications. You can also explore our .NET PDF library example to understand how to create and manipulate data.
For current customers, you can check out our Document Processing Libraries from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our controls.
If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums, Direct-trac, or feedback portal. We are always happy to assist you!
