Important Update
ByteScout Spreadsheet SDK is the library (SDK) that is capable of writing, reading, modifying and calculating Excel and CSV spreadsheets. Most popular formulas can be calculated and reculculated with Excel installed. You may import or export data to and from CSV, XML, JSON as well as to and from databases, arrays.
On-demand (REST Web API) version:
Web API (on-demand version)
On-premise offline SDK for Windows:
60 Day Free Trial (on-premise)
Imports System.Drawing
Imports Bytescout.Spreadsheet
Module Program
Sub Main()
Try
' Data to be written in spreadsheet
Dim lstProducts = New List(Of InvoiceProduct) From {
New InvoiceProduct With {
.ProductName = "Product 1",
.Price = 30,
.Quantity = 10
},
New InvoiceProduct With {
.ProductName = "Product 2",
.Price = 40,
.Quantity = 30
},
New InvoiceProduct With {
.ProductName = "Product 3",
.Price = 50,
.Quantity = 15
},
New InvoiceProduct With {
.ProductName = "Product 4",
.Price = 20,
.Quantity = 20
}
}
' Generate Workbook in memory
Dim document = _GetSpreadsheet(lstProducts)
' Export to XLSX
document.SaveAsXLSX("Invoice.xlsx")
'document.SaveAsXLS("Invoice.xls")
' Open output file
Process.Start("Invoice.xlsx")
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
Console.WriteLine("Press enter key to exit...")
Console.ReadLine()
End Sub
''' <summary>
''' Get Spreadsheet
''' </summary>
Private Function _GetSpreadsheet(ByVal lstProducts As List(Of InvoiceProduct)) As Spreadsheet
' Create new Spreadsheet
Dim document As Spreadsheet = New Spreadsheet()
' Enable auto-calculation of formulas
document.Workbook.AutoCalculation = True
document.Workbook.DefaultFont = New SpreadsheetFont("Arial", 10)
' Add new worksheet
Dim Sheet As Worksheet = document.Workbook.Worksheets.Add("Invoice")
Dim startRow As Integer = 0
' Add Product Listing
Dim startAddress As String = ""
Dim endAddress As String = ""
Sheet.Cell((startRow), 0).Value = "Item"
_AddAllBorders(Sheet.Cell(startRow, 0))
Sheet.Cell(startRow, 0).Font = New Font("Arial", 11, FontStyle.Bold)
Sheet.Cell(startRow, 1).Value = "Quantity"
_AddAllBorders(Sheet.Cell(startRow, 1))
Sheet.Cell(startRow, 1).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right
Sheet.Cell(startRow, 1).Font = New Font("Arial", 11, FontStyle.Bold)
Sheet.Cell(startRow, 2).Value = "Price"
_AddAllBorders(Sheet.Cell(startRow, 2))
Sheet.Cell(startRow, 2).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right
Sheet.Cell(startRow, 2).Font = New Font("Arial", 11, FontStyle.Bold)
Sheet.Cell(startRow, 3).Value = "Total"
_AddAllBorders(Sheet.Cell(startRow, 3))
Sheet.Cell(startRow, 3).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right
Sheet.Cell(startRow, 3).Font = New Font("Arial", 11, FontStyle.Bold)
For i As Integer = 0 To lstProducts.Count - 1
Sheet.Cell((System.Threading.Interlocked.Increment(startRow)), 0).Value = lstProducts(i).ProductName
_AddAllBorders(Sheet.Cell(startRow, 0))
Sheet.Cell(startRow, 1).Value = lstProducts(i).Quantity
Sheet.Cell(startRow, 1).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right
_AddAllBorders(Sheet.Cell(startRow, 1))
Sheet.Cell(startRow, 2).Value = lstProducts(i).Price
Sheet.Cell(startRow, 2).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right
_AddAllBorders(Sheet.Cell(startRow, 2))
' Calculated cell
Dim formulaMultiplication As String = String.Format("={0}*{1}", Sheet.Cell(startRow, 1).GetAddress().ToString(), Sheet.Cell(startRow, 2).GetAddress().ToString())
Sheet.Cell(startRow, 3).Formula = formulaMultiplication
Sheet.Cell(startRow, 3).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right
_AddAllBorders(Sheet.Cell(startRow, 3))
' Address to be used for total sum
If i = 0 Then
startAddress = Sheet.Cell(startRow, 3).GetAddress().ToString()
End If
endAddress = Sheet.Cell(startRow, 3).GetAddress().ToString()
Next
Sheet.Cell((System.Threading.Interlocked.Increment(startRow)), 2).Value = "TOTAL"
Sheet.Cell(startRow, 2).AlignmentHorizontal = Constants.AlignmentHorizontal.Right
Sheet.Cell(startRow, 2).Font = New Font("Arial", 11, FontStyle.Bold)
Dim formulaTotalSum As String = String.Format("=SUM({0}:{1})", startAddress, endAddress)
Sheet.Cell(startRow, 3).Formula = formulaTotalSum
Sheet.Cell(startRow, 3).Font = New Font("Arial", 11, FontStyle.Bold)
Sheet.Cell(startRow, 3).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right
' AutoFit all columns
Sheet.Columns.Item(0).AutoFit()
Sheet.Columns.Item(1).AutoFit()
Sheet.Columns.Item(2).AutoFit()
Sheet.Columns.Item(3).AutoFit()
' Return all formatted document
Return document
End Function
''' <summary>
''' Add borders to cell
''' </summary>
Private Sub _AddAllBorders(ByVal cell As Cell)
cell.LeftBorderStyle = Bytescout.Spreadsheet.Constants.LineStyle.Thin
cell.RightBorderStyle = Bytescout.Spreadsheet.Constants.LineStyle.Thin
cell.TopBorderStyle = Bytescout.Spreadsheet.Constants.LineStyle.Thin
cell.BottomBorderStyle = Bytescout.Spreadsheet.Constants.LineStyle.Thin
End Sub
Public Class InvoiceProduct
Public Property ProductName As String
Public Property Quantity As Integer
Public Property Price As Decimal
End Class
End Module
60 Day Free Trial or Visit ByteScout Spreadsheet SDK Home Page
Explore ByteScout Spreadsheet SDK Documentation
Explore Samples
Sign Up for ByteScout Spreadsheet SDK Online Training
Get Your API Key
Explore Web API Docs
Explore Web API Samples
<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="15.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<Import Project="$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props" Condition="Exists('$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props')" />
<PropertyGroup>
<Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
<Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
<ProjectGuid>{846F275E-BE99-4254-85ED-B8CBBB4546A9}</ProjectGuid>
<OutputType>Exe</OutputType>
<StartupObject>WorkbookAutoCalculation.Program</StartupObject>
<RootNamespace>WorkbookAutoCalculation</RootNamespace>
<AssemblyName>WorkbookAutoCalculation</AssemblyName>
<FileAlignment>512</FileAlignment>
<MyType>Console</MyType>
<TargetFrameworkVersion>v2.0</TargetFrameworkVersion>
</PropertyGroup>
<PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
<PlatformTarget>AnyCPU</PlatformTarget>
<DebugSymbols>true</DebugSymbols>
<DebugType>full</DebugType>
<DefineDebug>true</DefineDebug>
<DefineTrace>true</DefineTrace>
<OutputPath>bin\Debug\</OutputPath>
<NoWarn>42016,41999,42017,42018,42019,42032,42036,42020,42021,42022</NoWarn>
</PropertyGroup>
<PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' ">
<PlatformTarget>AnyCPU</PlatformTarget>
<DebugType>pdbonly</DebugType>
<DefineDebug>false</DefineDebug>
<DefineTrace>true</DefineTrace>
<Optimize>true</Optimize>
<OutputPath>bin\Release\</OutputPath>
<NoWarn>42016,41999,42017,42018,42019,42032,42036,42020,42021,42022</NoWarn>
</PropertyGroup>
<PropertyGroup>
<OptionExplicit>On</OptionExplicit>
</PropertyGroup>
<PropertyGroup>
<OptionCompare>Binary</OptionCompare>
</PropertyGroup>
<PropertyGroup>
<OptionStrict>Off</OptionStrict>
</PropertyGroup>
<PropertyGroup>
<OptionInfer>On</OptionInfer>
</PropertyGroup>
<ItemGroup>
<Reference Include="Bytescout.Spreadsheet, Version=3.1.0.1715, Culture=neutral, PublicKeyToken=f7dd1bd9d40a50eb, processorArchitecture=MSIL">
<SpecificVersion>False</SpecificVersion>
<HintPath>c:\Program Files\Bytescout Spreadsheet SDK\net2.00\Bytescout.Spreadsheet.dll</HintPath>
</Reference>
<Reference Include="System" />
<Reference Include="System.Data" />
<Reference Include="System.Deployment" />
<Reference Include="System.Drawing" />
<Reference Include="System.Xml" />
</ItemGroup>
<ItemGroup>
<Import Include="Microsoft.VisualBasic" />
<Import Include="System" />
<Import Include="System.Collections" />
<Import Include="System.Collections.Generic" />
<Import Include="System.Data" />
<Import Include="System.Diagnostics" />
</ItemGroup>
<ItemGroup>
<Compile Include="Program.vb" />
</ItemGroup>
<Import Project="$(MSBuildToolsPath)\Microsoft.VisualBasic.targets" />
</Project>
60 Day Free Trial or Visit ByteScout Spreadsheet SDK Home Page
Explore ByteScout Spreadsheet SDK Documentation
Explore Samples
Sign Up for ByteScout Spreadsheet SDK Online Training
Get Your API Key
Explore Web API Docs
Explore Web API Samples
60 Day Free Trial or Visit ByteScout Spreadsheet SDK Home Page
Explore ByteScout Spreadsheet SDK Documentation
Explore Samples
Sign Up for ByteScout Spreadsheet SDK Online Training
Get Your API Key
Explore Web API Docs
Explore Web API Samples