Twileloop.SpreadSheet 1.4.0

The ID prefix of this package has been reserved for one of the owners of this package by NuGet.org. Prefix Reserved
There is a newer version of this package available.
See the version list below for details.
dotnet add package Twileloop.SpreadSheet --version 1.4.0
NuGet\Install-Package Twileloop.SpreadSheet -Version 1.4.0
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="Twileloop.SpreadSheet" Version="1.4.0" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add Twileloop.SpreadSheet --version 1.4.0
#r "nuget: Twileloop.SpreadSheet, 1.4.0"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install Twileloop.SpreadSheet as a Cake Addin
#addin nuget:?package=Twileloop.SpreadSheet&version=1.4.0

// Install Twileloop.SpreadSheet as a Cake Tool
#tool nuget:?package=Twileloop.SpreadSheet&version=1.4.0

<br /> <div align="center"> <a href="https://github.com/sangeethnandakumar/Twileloop.SpreadSheet"> <img src="https://iili.io/HUaMukB.png" alt="Logo" width="80" height="80"> </a>

<h2 align="center"> Twileloop SpreadSheet</h2> <h4 align="center"> Free | Cross-Format | Fast </h4> </div>

About

A cross format spreadsheet accessor that empowers you to effortlessly read, write, copy, and move data across popular spreadsheet formats like Google Sheets and Microsoft Excel.

License

Twileloop.SpreadSheet is licensed under the MIT License. See the LICENSE file for more details.

This library is absolutely free. If it gives you a smile, A small coffee would be a great way to support my work. Thank you for considering it!

"Buy Me A Coffee"

Usage

To get started, You have to install atleast 2 packages:

  • The core Twileloop.SpreadSheet package
  • A driver package for your desired spreadsheet (Microsoft Excel, Google Sheet etc...)

Note In the backstage, Twileloop.SpreadSheet uses NPOI to connect with Excel files and Google.Apis.Sheets.v4 to connect with Google Sheets

<hr/>

1. Install Core Package

dotnet add package Twileloop.SpreadSheet --version <LATEST VERSION>

2. Install Driver Packages (One or More)

There is no need to install all these driver packages, If you only prefer to work with Microsoft Excel, ignore the Google Sheets driver package

Driver To Use Install Package
<img src="https://iili.io/HUaMOEG.png" alt="Logo" height="30"> Google Sheet dotnet add package Twileloop.SpreadSheet.GoogleSheet --version <LATEST VERSION>
<img src="https://iili.io/HUaM8Yl.png" alt="Logo" height="30"> Microsoft Excel dotnet add package Twileloop.SpreadSheet.MicrosoftExcel --version <LATEST VERSION>

Supported Features

Feature Microsoft Excel Google Sheets
Plan Text Reads
Plan Text Writes
Switch Sheets
Text Formatting
Cell Formatting
Border Formatting 🚧 🚧
Image Reads 🚧 🚧
Image Writes 🚧 🚧
Formulas
Draw Graph

✅ - Available     🚧 - Work In Progress     ❌ - Not Available

3. Initialize Driver(s)

Once installed packages, Initialize your drivers

    using Twileloop.SpreadSheet.GoogleSheet;
    using Twileloop.SpreadSheet.MicrosoftExcel;

    //Step 1: Initialize your prefered spreadsheet drivers
    var excelDriver = new MicrosoftExcelDriver(new MicrosoftExcelOptions
    {
        FileLocation = "<YOUR_EXCEL_FILE_LOCATION>"
    });
    
    var googleSheetsDriver = new GoogleSheetDriver(new GoogleSheetOptions
    {
        SheetsURI = new Uri("<YOUR_GOOGLE_SHEETS_URL>"),
        Credential = "secrets.json" //Location of your credential file
    });

Warning If planning to use Excel fomat, Avoid opening your spreadsheet in Microsoft Excel at the same time while Twileloop.SpreadSheet is using it

Warning If planning to use Google Sheets, You have to:

  1. Create a service account
  2. Download the credentials secrets.json from GCP console
  3. Enable Google Sheets API in your GCP portal
  4. Then share your Google Sheet with that service account's email id with Editor permission (for write access)

The above process is out of scope to explain here in detail.

Here's a good video tutorial (Upto 3:07): https://www.youtube.com/watch?v=fxGeppjO0Mg

4. Get An Accessor

Once driver(s) are initialized, Create an accessor to access the spreadsheet

    using Twileloop.SpreadSheet.Factory;

    //Step 2: Use that driver to build a spreadsheet accessor
    var excelAccessor = SpreadSheetFactory.CreateAccessor(excelDriver);
    var googleSheetAccessor = SpreadSheetFactory.CreateAccessor(googleSheetsDriver);

An accessor wil give you 3 handles:

  • Reader ⇒ Use this handle to read from your spreadsheets
  • Writer ⇒ Use this handle to write to your spreadsheets
  • Controller ⇒ Use this handle to control your spreadsheets

5. Load WorkSheet

First step is to load your prefered sheet by controlling the spreadsheet

You must load a worksheet using the Controller before reading or writing to your spreadsheet

    //Step 3: Now this accessor can Read/Write and Control spreadsheet. Let's open Sheet1
    using (excelAccessor)
    {
        excelAccessor.Controller.LoadSheet("Sheet1");
    }
    
    using (googleSheetAccessor)
    {
        excelAccessor.Controller.LoadSheet("Sheet1");
    }

6. Read SpreadSheet

Reading is as simple as this

    //Step 4: Different Ways To Read Data
    using (excelAccessor)
    {
        //Load prefered sheet
        excelAccessor.Controller.LoadSheet("Sheet1");
    
        //Read a single cell
        string data1 = excelAccessor.Reader.ReadCell(1, 1);
        string data2 = excelAccessor.Reader.ReadCell("A10");
    
        //Read a full row in bulk
        string[] data3 = excelAccessor.Reader.ReadRow(1);
        string[] data4 = excelAccessor.Reader.ReadRow("C9");
    
        //Read a full column in bulk
        string[] data5 = excelAccessor.Reader.ReadColumn(1);
        string[] data6 = excelAccessor.Reader.ReadColumn("D20");
    
        //Select an area and extract data in bulk
        DataTable data7 = excelAccessor.Reader.ReadSelection(1, 1, 10, 10);
        DataTable data8 = excelAccessor.Reader.ReadSelection("A1", "J10");
    }

If you're using Google Sheet, It's recommended to use any bulk reads/writes operations, Because in case of Google Sheets calling ReadCell() multiple times is not efficient as it fires multiple API calls to Google to read cells.

Bulk reads/writes will fire only once and get data in one go. If you just need to read a single cell, Feel free to use ReadCell() since it makes sense in a read and drop situation

7. Write SpreadSheet

Writing is as simple as this

    //Step 5: Different Ways To Write Data
    using (googleSheetAccessor)
    {
        googleSheetAccessor.Controller.LoadSheet("Sheet1");
    
        //Write a single cell
        googleSheetAccessor.Writer.WriteCell(1, 1, "Country");
        googleSheetAccessor.Writer.WriteCell("C17", "Country");
    
        //Write a full row in bulk
        googleSheetAccessor.Writer.WriteRow(1, new string[] { "USA", "China", "Russia", "India" });
        googleSheetAccessor.Writer.WriteRow("A1", new string[] { "USA", "China", "Russia", "India" });
    
        //Write a full column in bulk
        googleSheetAccessor.Writer.WriteColumn(1, new string[] { "USA", "China", "Russia", "India" });
        googleSheetAccessor.Writer.WriteColumn("B22", new string[] { "USA", "China", "Russia", "India" });
    
        //Select an area and write a grid in bulk
        DataTable grid = new DataTable();
        grid.Columns.Add("Rank");
        grid.Columns.Add("Powerfull Militaries");
    
        grid.Rows.Add(1, "USA");
        grid.Rows.Add(2, "China");
        grid.Rows.Add(3, "Russia");
        grid.Rows.Add(4, "India");
        grid.Rows.Add(5, "France");
    
        googleSheetAccessor.Writer.WriteSelection(1, 1, grid);
        googleSheetAccessor.Writer.WriteSelection("D20", grid);
    }

8. Read/Write Multiple SpreadSheets In One Go

Open multiple spreadsheets in one go by cascading accessors then move data in between

    //Read and write both spreadsheets at once
    using (excelAccessor)
    {
        using (googleSheetAccessor)
        {
            //Step 1: Open both spreadsheets
            excelAccessor.Controller.LoadSheet("Sheet1");
            googleSheetAccessor.Controller.LoadSheet("Sheet1");
    
            //Step 2: Read from excel
            DataTable excelData = excelAccessor.Reader.ReadSelection("A1", "D10");
    
            //Step 3: Then write it to Google Sheet
            googleSheetAccessor.Writer.WriteSelection("C1", excelData);                    
        }
    }

9. Sheets Controls

Create one or more sheets, Get all sheets or find active sheet name

    //Create one or more new sheets
    excelAccessor.Controller.CreateSheets("Sheet1", "Sheet2", "Sheet3");
    googleSheetAccessor.Controller.CreateSheets("Sheet1", "Sheet2");

    //Get list of sheets
    var allExcelSheets = excelAccessor.Controller.GetSheets();
    var allGoogleSheetSheet = googleSheetAccessor.Controller.GetSheets();

    //Get active sheet name
    var activeExcelSheet = excelAccessor.Controller.GetActiveSheet();
    var googleSheetSheet = googleSheetAccessor.Controller.GetActiveSheet();

10. Styling And Formatting

Styling is easy as hell. Just define all your different styles/formatting globally and apply it for a selected cell range

A formatting can have 3 types

  • Text Formatting
  • Cell Formatting
  • Border Formatting

Keep NULL for whichever format type you don't want to change


    //Define your formatting, Let's say for titles
    var titleFormat = new Formatting
    {
        //Text formatting
        TextFormating = new TextFormating
        {
            Bold = false,
            Italic = true,
            Underline = false,
            Size = 15,
            HorizontalAlignment = HorizontalAllignment.RIGHT,
            VerticalAlignment = VerticalAllignment.BOTTOM,
            Font = "Impact",
            Color = System.Drawing.Color.White,
        },
        //Cell formatting
        CellFormating = new CellFormating
        {
            BackgroundColor = System.Drawing.Color.IndianRed
        },
        //Border formatting
        BorderFormating = new BorderFormating
        {
            TopBorder = true,
            LeftBorder = true,
            RightBorder = true,
            BottomBorder = true,
            BorderType = BorderType.SOLID,
            Thickness = 5
        }
    };

    //Then simply apply it as needed for a cell range
    excelAccessor.Writer.ApplyFormatting(1, 1, 10, 4, titleFormat);
    googleSheetAccessor.Writer.ApplyFormatting(1, 1, 10, 4, titleFormat);
Product Compatible and additional computed target framework versions.
.NET net5.0 was computed.  net5.0-windows was computed.  net6.0 was computed.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 was computed.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 was computed.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed. 
.NET Core netcoreapp3.0 was computed.  netcoreapp3.1 was computed. 
.NET Standard netstandard2.1 is compatible. 
MonoAndroid monoandroid was computed. 
MonoMac monomac was computed. 
MonoTouch monotouch was computed. 
Tizen tizen60 was computed. 
Xamarin.iOS xamarinios was computed. 
Xamarin.Mac xamarinmac was computed. 
Xamarin.TVOS xamarintvos was computed. 
Xamarin.WatchOS xamarinwatchos was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages (2)

Showing the top 2 NuGet packages that depend on Twileloop.SpreadSheet:

Package Downloads
Twileloop.SpreadSheet.GoogleSheet The ID prefix of this package has been reserved for one of the owners of this package by NuGet.org.

Powerfull driver that can latch to Twileloop.SpreadSheet and control, read and write to Google Sheets. Simplify your Google Sheets workflow with ease

Twileloop.SpreadSheet.MicrosoftExcel The ID prefix of this package has been reserved for one of the owners of this package by NuGet.org.

Powerfull driver that can latch to Twileloop.SpreadSheet and control, read and write to Microsoft Excel files. Simplify your Excel workflow with ease

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
2.1.0 225 2/13/2024
2.0.0 114 2/4/2024
1.4.0 273 5/13/2023
1.3.0 186 5/12/2023
1.2.0 189 5/12/2023
1.1.0 129 5/12/2023
1.0.0 122 5/12/2023