Administrator

Tuesday, 05 April 2011 06:47

PDF Image in C#, VB.NET

Demo
C# source
VB.Net source

The sample demonstrates how to insert an image to a PDF document.

using System.Drawing;
using Spire.Pdf;
using Spire.Pdf.Graphics;

namespace Image
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a pdf document.
            PdfDocument doc = new PdfDocument();

            // Create one page
            PdfPageBase page = doc.Pages.Add();

            //Draw the text
            page.Canvas.DrawString("Hello, World!",
                                   new PdfFont(PdfFontFamily.Helvetica, 30f),
                                   new PdfSolidBrush(Color.Black),
                                   10, 10);
            //Draw the image
            PdfImage image = PdfImage.FromFile(@"SalesReportChart.png");
            float width = image.Width * 0.75f;
            float height = image.Height * 0.75f;
            float x = (page.Canvas.ClientSize.Width - width) / 2;

            page.Canvas.DrawImage(image, x, 60, width, height);

            //Save pdf file.
            doc.SaveToFile("Image.pdf");
            doc.Close();

            //Launching the Pdf file.
            System.Diagnostics.Process.Start("Image.pdf");
        }
    }
}

Imports System.Drawing
Imports Spire.Pdf
Imports Spire.Pdf.Graphics

Namespace Image
    Friend Class Program
        Shared Sub Main(ByVal args() As String)
            'Create a pdf document.
            Dim doc As New PdfDocument()

            ' Create one page
            Dim page As PdfPageBase = doc.Pages.Add()

            'Draw the text
            page.Canvas.DrawString("Hello, World!", _
                                   New PdfFont(PdfFontFamily.Helvetica, 30.0F), _
                                   New PdfSolidBrush(Color.Black), 10, 10)
            'Draw the image
            Dim image As PdfImage = PdfImage.FromFile("SalesReportChart.png")
            Dim width As Single = image.Width * 0.75F
            Dim height As Single = image.Height * 0.75F
            Dim x As Single = (page.Canvas.ClientSize.Width - width) / 2

            page.Canvas.DrawImage(image, x, 60, width, height)

            'Save pdf file.
            doc.SaveToFile("Image.pdf")
            doc.Close()

            'Launching the Pdf file.
            Process.Start("Image.pdf")
        End Sub
    End Class
End Namespace

Published in Quick Guide

Tagged under

Friday, 01 April 2011 08:52

PDF HelloWorld in C#, VB.NET

Demo
C# source
VB.Net source

The sample demonstrates how to write a "HelloWorld" to a PDF document.

Download HelloWorld.pdf

using System.Drawing;
using Spire.Pdf;
using Spire.Pdf.Graphics;

namespace HelloWorld
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a pdf document.
            PdfDocument doc = new PdfDocument();

            // Create one page
            PdfPageBase page =doc.Pages.Add();

            //Draw the text
            page.Canvas.DrawString("Hello, World!",
                                   new PdfFont(PdfFontFamily.Helvetica, 30f),
                                   new PdfSolidBrush(Color.Black),
                                   10, 10);

            //Save pdf file.
            doc.SaveToFile("HelloWorld.pdf");
            doc.Close();

            //Launching the Pdf file.
            System.Diagnostics.Process.Start("HelloWorld.pdf");
        }
    }
}

Imports System.Drawing
Imports Spire.Pdf
Imports Spire.Pdf.Graphics

Namespace HelloWorld
    Friend Class Program
        Shared Sub Main(ByVal args() As String)
            'Create a pdf document.
            Dim doc As New PdfDocument()

            ' Create one page
            Dim page As PdfPageBase = doc.Pages.Add()

            'Draw the text
            page.Canvas.DrawString("Hello, World!", _
                   New PdfFont(PdfFontFamily.Helvetica, 30.0F), _
                   New PdfSolidBrush(Color.Black), 10, 10)

            'Save pdf file.
            doc.SaveToFile("HelloWorld.pdf")
            doc.Close()

            'Launching the Pdf file.
            Process.Start("HelloWorld.pdf")
        End Sub
    End Class
End Namespace

Published in Quick Guide

Tagged under

Sunday, 09 October 2022 09:42

C#/VB.NET: Create a Column Chart in Excel

A column chart is a chart that visualizes data as a set of rectangular columns, and the height of the column indicates the value of the data point. Creating column charts in Excel is a great way to compare data and show data change over time. In this article, you will learn how to programmatically create a column chart in Excel using Spire.XLS for .NET.

Install Spire.XLS for .NET

To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.

Package Manager

PM> Install-Package Spire.XLS

Create a Column Chart in Excel

The detailed steps are as follows.

Create a Workbook instance.
Get the first worksheet using Workbook.Worksheets[sheetIndex] property.
Add data to specified cells and set the cell styles.
Add a clustered column chart to the worksheet using Worksheet.Charts.Add(ExcelChartType.ColumnClustered) method.
Set data range for the chart using Chart.DataRange property.
Set position, title, category axis and value axis for the chart.
Loop through the data series of the chart, and show data labels for data points by setting the ChartSerie.DataPoints.DefaultDataPoint.DataLabels.HasValue property as true.
Set the position of chart legend using Chart.Legend.Position property.
Save the result file using Workbook.SaveToFile() method.

C#
VB.NET

using Spire.Xls;
using Spire.Xls.Charts;
using System.Drawing;

namespace ColumnChart
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();

            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Add data to specified cells
            sheet.Range["A1"].Value = "Country";
            sheet.Range["A2"].Value = "Cuba";
            sheet.Range["A3"].Value = "Mexico";
            sheet.Range["A4"].Value = "France";
            sheet.Range["A5"].Value = "German";

            sheet.Range["B1"].Value = "Jun";
            sheet.Range["B2"].NumberValue = 5000;
            sheet.Range["B3"].NumberValue = 8000;
            sheet.Range["B4"].NumberValue = 9000;
            sheet.Range["B5"].NumberValue = 8500;

            sheet.Range["C1"].Value = "Aug";
            sheet.Range["C2"].NumberValue = 3000;
            sheet.Range["C3"].NumberValue = 5000;
            sheet.Range["C4"].NumberValue = 7000;
            sheet.Range["C5"].NumberValue = 6000;

            //Set cell styles
            sheet.Range["A1:C1"].Style.Font.IsBold = true;
            sheet.Range["A1:C1"].Style.KnownColor = ExcelColors.Black;
            sheet.Range["A1:C1"].Style.Font.Color = Color.White;
            sheet.Range["A1:C5"].Style.HorizontalAlignment = HorizontalAlignType.Center;
            sheet.Range["A1:C5"].Style.VerticalAlignment = VerticalAlignType.Center;

            //Set number format
            sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0";

            //Add a column chart to the worksheet
            Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered);

            //Set data range for the chart
            chart.DataRange = sheet.Range["A1:C5"];
            chart.SeriesDataFromRange = false;

            //Set position of the chart
            chart.LeftColumn = 1;
            chart.TopRow = 7;
            chart.RightColumn = 11;
            chart.BottomRow = 29;

            //Set and format chart title
            chart.ChartTitle = "Sales market by country";
            chart.ChartTitleArea.Size = 13;
            chart.ChartTitleArea.IsBold = true;

            //Set and format category axis
            chart.PrimaryCategoryAxis.Title = "Country";
            chart.PrimaryCategoryAxis.Font.Color = Color.Blue;

            //Set and format value axis
            chart.PrimaryValueAxis.Title = "Sales(in Dollars)";
            chart.PrimaryValueAxis.HasMajorGridLines = false;
            chart.PrimaryValueAxis.MinValue = 1000;
            chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;

            //Show data labels for data points 
            foreach (ChartSerie cs in chart.Series)
            {
                cs.Format.Options.IsVaryColor = true;
                cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
            }

            //Set position of chart legend
            chart.Legend.Position = LegendPositionType.Top;

            //Save the result file
            workbook.SaveToFile("ExcelColumnChart.xlsx", ExcelVersion.Version2010);
        }
    }
}

C#/VB.NET: Create a Column Chart in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Published in Chart

Tagged under

xls net Chart

Monday, 24 January 2011 09:49

Save Excel Document in C#, VB.NET

Automation of an Excel file allows us to doing various operations in C#/VB.NET. Any loss in these operations may result in unexpected negative consequences for developers and the clients of the developers. That means we must find a solution that enables us to Save Excel with no loss in quality of our operations. This section will demonstrate how to fast save Excel file with perfect performance as directly operations in Excel files.

Spire.Xls for .NET is a professional component that enables developers directly manages Excel operation regardless whether Microsoft Excel is installed on or not. With Spire.Xls for .NET, we can save Excel to what we want it to be. Any kind of trial and evaluation on Spire.Xls for .NET is always welcomed; so now please feel free to download Spire.Xls for .NET and then follow our guide to save perfect Excel or try other function of Spire.Xls for .NET.

Spire.Xls for .NET allows us to create a new Excel file, write data in to it, edit the input data and then save Excel file.

[C#]

using Spire.Xls;
namespace Excel_save
{
   class Program
    {
        static void Main(string[] args)
        {
           //Create a new workbook
            Workbook workbook = new Workbook();
           //Initialize worksheet        
            Worksheet sheet = workbook.Worksheets[0];           
           //Append text
            sheet.Range["A1"].Text = "Demo: Save Excel in .NET";
           //Save it as Excel file
            workbook.SaveToFile("Sample.xls",ExcelVersion.Version97to2003);
           //Launch the file
           System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

[VB.NET]

Imports Spire.Xls
Namespace Excel_save
	Class Program
		Private Shared Sub Main(args As String())
			'Create a new workbook
			Dim workbook As New Workbook()
			'Initialize worksheet        
			Dim sheet As Worksheet = workbook.Worksheets(0)
			'Append text
			sheet.Range("A1").Text = "Demo: Save Excel in .NET"
			'Save it as Excel file
			workbook.SaveToFile("Sample.xls",ExcelVersion.Version97to2003)
			'Launch the file
			System.Diagnostics.Process.Start(workbook.FileName)
		End Sub
	End Class
End Namespace

Published in Document Operation

Tagged under

xls net Operation

Thursday, 27 April 2023 08:43

C#/VB.NET: Add or Read Formulas and Functions in Excel

Excel is a powerful spreadsheet software with numerous features, but formulas and functions are undoubtedly among its most critical tools. They enable users to perform a wide range of mathematical, statistical, and logical operations on their data, allowing them to derive meaningful insights quickly and accurately. In this article, we will explain how to add or read formulas and functions in Excel files in C# and VB.NET using Spire.XLS for .NET.

Add Formulas and Functions to Excel in C# and VB.NET
Read Formulas and Functions in Excel in C# and VB.NET

Install Spire.XLS for .NET

To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.

Package Manager

PM> Install-Package Spire.XLS

Add Formulas and Functions to Excel in C# and VB.NET

The Worksheet.Range[int row, int column].Formula property in Spire.XLS for .NET is used to add formulas or functions to specific cells in an Excel worksheet. The main steps are as follows:

Initialize an instance of the Workbook class.
Get a specific worksheet by its index using the Workbook.Worksheets[int index] property.
Add some text and numeric data to specific cells of the worksheet using the Worksheet.Range[int row, int column].Text and Worksheet.Range[int row, int column].NumberValue properties.
Add text and formulas to specific cells of the worksheet using the Worksheet.Range[int row, int column].Text and Worksheet.Range[int row, int column].Formula properties.
Add text and functions to specific cells of the worksheet using the Worksheet.Range[int row, int column].Text and Worksheet.Range[int row, int column].Formula properties.
Save the result file using Workbook.SaveToFile(string fileName, ExcelVersion version) method.

C#
VB.NET

using Spire.Xls;

namespace AddFormulasAndFunctions
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //Initialize an instance of the Workbook class
            Workbook workbook = new Workbook();

            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Declare two variables: currentRow, currentFormula
            int currentRow = 1;
            string currentFormula;

            //Add text to the worksheet and set cell style
            sheet.Range[currentRow, 1].Text = "Test Data:";
            sheet.Range[currentRow, 1].Style.Font.IsBold = true;
            sheet.Range[currentRow, 1].Style.FillPattern = ExcelPatternType.Solid;
            sheet.Range[currentRow, 1].Style.KnownColor = ExcelColors.LightGreen1;
            sheet.Range[currentRow, 1].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;

            //Add some numeric data to the worksheet
            sheet.Range[++currentRow, 1].NumberValue = 7.3;
            sheet.Range[currentRow, 2].NumberValue = 5;
            sheet.Range[currentRow, 3].NumberValue = 8.2;
            sheet.Range[currentRow, 4].NumberValue = 4;
            sheet.Range[currentRow, 5].NumberValue = 3;
            sheet.Range[currentRow, 6].NumberValue = 11.3;

            currentRow++;

            //Add text to the worksheet and set cell style
            sheet.Range[++currentRow, 1].Text = "Formulas";
            sheet.Range[currentRow, 2].Text = "Results";
            sheet.Range[currentRow, 1, currentRow, 2].Style.Font.IsBold = true;
            sheet.Range[currentRow, 1, currentRow, 2].Style.KnownColor = ExcelColors.LightGreen1;
            sheet.Range[currentRow, 1, currentRow, 2].Style.FillPattern = ExcelPatternType.Solid;
            sheet.Range[currentRow, 1, currentRow, 2].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;

            //Add text and formulas to the worksheet
            currentFormula = "=\"Hello\"";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            currentFormula = "=300";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            currentFormula = "=3389.639421";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            currentFormula = "=false";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            currentFormula = "=1+2+3+4+5-6-7+8-9";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            currentFormula = "=33*3/4-2+10";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            currentFormula = "=Sheet1!$B$2";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            //Add text and Functions to the worksheet
            //AVERAGE
            currentFormula = "=AVERAGE(Sheet1!$D$2:F$2)";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            //COUNT
            currentFormula = "=COUNT(3,5,8,10,2,34)";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            //NOW
            currentFormula = "=NOW()";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;
            sheet.Range[currentRow, 2].Style.NumberFormat = "yyyy-MM-DD";

            //SECOND
            currentFormula = "=SECOND(0.503)";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //MINUTE
            currentFormula = "=MINUTE(0.78125)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //MONTH
            currentFormula = "=MONTH(9)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //DAY
            currentFormula = "=DAY(10)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //TIME
            currentFormula = "=TIME(4,5,7)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //DATE
            currentFormula = "=DATE(6,4,2)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //RAND
            currentFormula = "=RAND()";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //HOUR
            currentFormula = "=HOUR(0.5)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //MOD
            currentFormula = "=MOD(5,3)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //WEEKDAY
            currentFormula = "=WEEKDAY(3)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //YEAR
            currentFormula = "=YEAR(23)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //NOT
            currentFormula = "=NOT(true)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //OR
            currentFormula = "=OR(true)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //AND
            currentFormula = "=AND(TRUE)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //VALUE
            currentFormula = "=VALUE(30)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //LEN
            currentFormula = "=LEN(\"world\")";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //MID
            currentFormula = "=MID(\"world\",4,2)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //ROUND
            currentFormula = "=ROUND(7,3)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //SIGN
            currentFormula = "=SIGN(4)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //INT
            currentFormula = "=INT(200)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //ABS
            currentFormula = "=ABS(-1.21)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //LN
            currentFormula = "=LN(15)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //EXP
            currentFormula = "=EXP(20)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //SQRT
            currentFormula = "=SQRT(40)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //PI
            currentFormula = "=PI()";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //COS
            currentFormula = "=COS(9)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //SIN
            currentFormula = "=SIN(45)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //MAX
            currentFormula = "=MAX(10,30)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //MIN
            currentFormula = "=MIN(5,7)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //AVERAGE
            currentFormula = "=AVERAGE(12,45)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //SUM
            currentFormula = "=SUM(18,29)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //IF
            currentFormula = "=IF(4,2,2)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //SUBTOTAL
            currentFormula = "=SUBTOTAL(3,Sheet1!A2:F2)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //Set width of the 1st, 2nd and 3rd columns
            sheet.SetColumnWidth(1, 32);
            sheet.SetColumnWidth(2, 16);
            sheet.SetColumnWidth(3, 16);

            //Create a cell style
            CellStyle style = workbook.Styles.Add("Style");
            //Set the horizontal alignment as left
            style.HorizontalAlignment = HorizontalAlignType.Left;
            //Apply the style to the worksheet
            sheet.ApplyStyle(style);

            //Save the result file
            workbook.SaveToFile("AddFormulasAndFunctions.xlsx", ExcelVersion.Version2016);
            workbook.Dispose();
        }
    }
}

C#/VB.NET: Add or Read Formulas and Functions in Excel

Read Formulas and Functions in Excel in C# and VB.NET

To read formulas and functions in an Excel worksheet, you need to iterate through all the cells in the worksheet, after that, find the cells containing formulas or functions using the Cell.HasFormula property, then get the formulas or functions of the cells using the CellRange.Formula property. The detailed steps are as follows:

Initialize an instance of the Workbook class.
Load an Excel file using the Workbook.LoadFromFile() method.
Get a specific worksheet by its index using the Workbook.Worksheets[sheetIndex] property.
Initialize an instance of the StringBuilder class.
Access the used range of the worksheet using the Worksheet.AllocatedRange property.
Iterate through all the cells in the used range.
Find the cells containing formulas/functions using the Cell.HasFormula property.
Get the names and the formulas/functions of the cells using the CellRange.RangeAddressLocal and CellRange.Formula properties.
Append the cell names and formulas/functions to the StringBuilder using the StringBuilder.AppendLine() method.
Write the content of the StringBuilder into a .txt file using the File.WriteAllText() method.

C#
VB.NET

using Spire.Xls;
using System.IO;
using System.Text;

namespace ReadFormulasAndFunctions
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //Initialize an instance of the Workbook class
            Workbook workbook = new Workbook();
            //Load an Excel file
            workbook.LoadFromFile("AddFormulasAndFunctions.xlsx");

            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Initialize an instance of the StringBuilder class
            StringBuilder sb = new StringBuilder();

            //Access the used range of the worksheet
            CellRange usedRange = sheet.AllocatedRange;

            //Loop through all the cells in the used range
            foreach (CellRange cell in usedRange)
            {
                //Detect if the current cell has formula/function
                if (cell.HasFormula)
                {
                    //Get the cell name
                    string cellName = cell.RangeAddressLocal;
                    //Get the formula/function
                    string formula = cell.Formula;
                    //Append the cell name and formula/function to the StringBuilder
                    sb.AppendLine(cellName + " has a formula: " + formula);
                }
            }

            //Write the content of the StringBuilder into a .txt file
            File.WriteAllText("ReadFormulasAndFunctions.txt", sb.ToString());
        }
    }
}

C#/VB.NET: Add or Read Formulas and Functions in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Published in Formula

Tagged under

xls net Formula

Friday, 11 March 2022 10:33

C#/VB.NET: Insert Images into Excel

Images are highly effective in conveying thoughts and ideas. Sometimes, you may need to insert images into an Excel report so that audiences can grasp your intentions quickly and clearly. In this article, you will learn how to insert image into Excel in C# and VB.NET using Spire.XLS for .NET library.

Insert Image from Disk into Excel in C# and VB.NET
Insert Web Image from a URL into Excel in C# and VB.NET

Install Spire.XLS for .NET

To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.

Package Manager

PM> Install-Package Spire.XLS

Insert Image from Disk into Excel in C# and VB.NET

The following are the steps to insert an image from disk into Excel:

Initialize a Workbook instance.
Get the desired worksheet using Workbook.Worksheets[sheetIndex] property.
Insert an image into the worksheet using the Worksheet.Pictures.Add() method.
Save the result file using Workbook.SaveToFile() method.

C#
VB.NET

using Spire.Xls;

namespace InsertImageInExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            //Initialize a Workbook instance
            Workbook workbook = new Workbook();
            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Insert image into the worksheet
            sheet.Pictures.Add(1, 1, @"E:\work\sample.jpg");

            //Save the result file
            workbook.SaveToFile("InsertImageFromDisk.xlsx", ExcelVersion.Version2016);
        }
    }
}

C#/VB.NET: Insert Images into Excel

Insert Web Image from a URL into Excel in C# and VB.NET

The following are the steps to insert a web image from a URL into Excel:

Initialize a Workbook instance.
Get the desired worksheet using Workbook.Worksheets[sheetIndex] property.
Initialize a WebClient instance, then download the web image as a byte array from the specified URL using WebClient.DownloadData(urlAddress) method.
Initialize a MemoryStream instance from the byte array.
Create an Image object from the memory stream then insert it into the worksheet using Worksheet.Pictures.Add() method.
Save the result file using Workbook.SaveToFile() method.

C#
VB.NET

using Spire.Xls;
using System.Drawing;
using System.IO;
using System.Net;

namespace InsertWebImageInExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            //Initialize a Workbook instance
            Workbook workbook = new Workbook();

            //Get the first sheet
            Worksheet sheet = workbook.Worksheets[0];

            //Specify the image URL
            string URL = "https://www.e-iceblue.com/downloads/demo/Logo.png";

            //Initialize a WebClient instance
            WebClient webClient = new WebClient();
            //Download the image as a byte array from the URL 
            byte[] imageData = webClient.DownloadData(URL);

            //Initialize a MemoryStream instance from the byte array
            MemoryStream objImage = new MemoryStream(imageData);

            //Create an Image object from the memory stream
            Image image = Image.FromStream(objImage);

            //Insert the image into the worksheet
            sheet.Pictures.Add(3, 2, image);

            //Save the result file
            workbook.SaveToFile("InsertWebImage.xlsx", ExcelVersion.Version2016);
        }
    }
}

C#/VB.NET: Insert Images into Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Published in Image

Tagged under

xls net Image

Monday, 29 August 2022 01:26

C#/VB.NET: Create a Pie Chart in Excel

A pie chart is a circular chart for visually representation of data. It divides a circular statistical graph into sectors or slices and each sector represents a specific portion of the total percentage. In this article, you will learn how to programmatically create a pie chart in Excel using Spire.XLS for .NET.

Install Spire.XLS for .NET

To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.

Package Manager

PM> Install-Package Spire.XLS

Create a Pie Chart in Excel

The detailed steps are as follows:

Create a Workbook instance.
Get a specified worksheet using Workbook.Worksheets[sheetIndex] property.
Add some data to specified cells and set the cell styles and borders.
Add a pie chart to the worksheet using Worksheet.Charts.Add(ExcelChartType.Pie) method.
Set data range for the chart using Chart.DataRange property.
Set the position and title of the chart.
Get a specified series in the chart and set category labels and values for the series using ChartSerie.CategoryLabels and ChartSerie.Values properties.
Show data labels for data points by setting the ChartSerie.DataPoints.DefaultDataPoint.DataLabels.HasValue property as true.
Save the result file using Workbook.SaveToFile() method.

C#
VB.NET

using Spire.Xls;
using Spire.Xls.Charts;
using System.Drawing;

namespace CreatePieChart
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();

            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Set sheet name
            sheet.Name = "Chart data";

            //Add data to specified cells
            sheet.Range["A1"].Value = "Year";
            sheet.Range["A2"].Value = "2002";
            sheet.Range["A3"].Value = "2003";
            sheet.Range["A4"].Value = "2004";
            sheet.Range["A5"].Value = "2005";

            sheet.Range["B1"].Value = "Sales";
            sheet.Range["B2"].NumberValue = 4000;
            sheet.Range["B3"].NumberValue = 6000;
            sheet.Range["B4"].NumberValue = 7000;
            sheet.Range["B5"].NumberValue = 8500;

            //Set cell styles
            sheet.Range["A1:B1"].Style.Font.IsBold = true;
            sheet.Range["A1:B1"].Style.KnownColor = ExcelColors.Black;
            sheet.Range["A1:B1"].Style.Font.Color = Color.White;
            sheet.Range["A1:B5"].Style.HorizontalAlignment = HorizontalAlignType.Center;
            sheet.Range["A1:B5"].Style.VerticalAlignment = VerticalAlignType.Center;

            //Set number format
            sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0";

            //Set cell borders
            sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;

            //Add a pie chart to the worksheet
            Chart chart = sheet.Charts.Add(ExcelChartType.Pie);

            //Set data range for the chart
            chart.DataRange = sheet.Range["B2:B5"];
            chart.SeriesDataFromRange = false;

            //Set position of the chart
            chart.LeftColumn = 1;
            chart.TopRow = 7;
            chart.RightColumn = 9;
            chart.BottomRow = 28;

            //Set and format chart title
            chart.ChartTitle = "Sales by Year";
            chart.ChartTitleArea.IsBold = true;
            chart.ChartTitleArea.Size = 14;

            // Get a specified series in the chart
            ChartSerie cs = chart.Series[0];

            //Set category labels for the series
            cs.CategoryLabels = sheet.Range["A2:A5"];

            //Set values for the series
            cs.Values = sheet.Range["B2:B5"];

            // Show data labels for data points
            cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;

            //Save the result file
            workbook.SaveToFile("PieChart.xlsx", ExcelVersion.Version2016);
        }
    }
}

C#/VB.NET: Create a Pie Chart in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Published in Chart

Tagged under

xls net Chart

Friday, 19 August 2022 08:42

C#/VB.NET: Create a Table in Word

In MS Word, the tables can organize and present data in rows and columns, which makes the information easier to understand and analyze. In this article, you will learn how to programmatically create a table with data in a Word document using Spire.Doc for .NET.

Install Spire.Doc for .NET

To begin with, you need to add the DLL files included in the Spire.Doc for.NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.

Package Manager

PM> Install-Package Spire.Doc

Create a Simple Table in Word

Below are some of the core classes and methods provided by Spire.Doc for .NET for creating and formatting tables in Word.

Name	Description
Table Class	Represents a table in a Word document.
TableRow Class	Represents a row in a table.
TableCell Class	Represents a specific cell in a table.
Section.AddTbale() Method	Adds a new table to the specified section.
Table.ResetCells() Method	Resets row number and column number.
Table.Rows Property	Gets the table rows.
TableRow.Height Property	Sets the height of the specified row.
TableRow.Cells Property	Returns the cells collection.
TableRow.RowFormat Property	Gets the format of the specified row.

The detailed steps are as follows

Create a Document object and add a section to it.
Prepare the data for the header row and other rows, storing them in a one-dimensional string array and a two-dimensional string array respectively.
Add a table to the section using Section.AddTable() method.
Insert data to the header row, and set the row formatting, including row height, background color, and text alignment.
Insert data to the rest of the rows and apply formatting to these rows.
Save the document to another file using Document.SaveToFile() method.

C#
VB.NET

using System;
using System.Drawing;
using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Fields;

namespace WordTable
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Document object
            Document doc = new Document();
            //Add a section
            Section s = doc.AddSection();

            //Define the data for the table
            String[] Header = { "Date", "Description", "Country", "On Hands", "On Order" };
            String[][] data = {
                                  new String[]{ "08/07/2021","Dive kayak","United States","24","16"},
                                  new String[]{ "08/07/2021","Underwater Diver Vehicle","United States","5","3"},
                                  new String[]{ "08/07/2021","Regulator System","Czech Republic","165","216"},
                                  new String[]{ "08/08/2021","Second Stage Regulator","United States","98","88"},
                                  new String[]{ "08/08/2021","Personal Dive Sonar","United States","46","45"},
                                  new String[]{ "08/09/2021","Compass Console Mount","United States","211","300"},
                                  new String[]{ "08/09/2021","Regulator System","United Kingdom","166","100"},
                                  new String[]{ "08/10/2021","Alternate Inflation Regulator","United Kingdom","47","43"},
                              };
            //Add a table
            Table table = s.AddTable(true);
            table.ResetCells(data.Length + 1, Header.Length);

            //Set the first row as table header
            TableRow FRow = table.Rows[0];
            FRow.IsHeader = true;

            //Set the height and color of the first row
            FRow.Height = 23;
            FRow.Cells[i].CellFormat.Shading.BackgroundPatternColor = Color.LightSeaGreen;
            for (int i = 0; i < Header.Length; i++)
            {
                //Set alignment for cells 
                Paragraph p = FRow.Cells[i].AddParagraph();
                FRow.Cells[i].CellFormat.VerticalAlignment = VerticalAlignment.Middle;
                p.Format.HorizontalAlignment = HorizontalAlignment.Center;

                //Set data format
                TextRange TR = p.AppendText(Header[i]);
                TR.CharacterFormat.FontName = "Calibri";
                TR.CharacterFormat.FontSize = 12;
                TR.CharacterFormat.Bold = true;
            }

            //Add data to the rest of rows and set cell format
            for (int r = 0; r < data.Length; r++)
            {
                TableRow DataRow = table.Rows[r + 1];
                DataRow.Height = 20;
                for (int c = 0; c < data[r].Length; c++)
                {
                    DataRow.Cells[c].CellFormat.VerticalAlignment = VerticalAlignment.Middle;
                    Paragraph p2 = DataRow.Cells[c].AddParagraph();
                    TextRange TR2 = p2.AppendText(data[r][c]);
                    p2.Format.HorizontalAlignment = HorizontalAlignment.Center;

                    //Set data format
                    TR2.CharacterFormat.FontName = "Calibri";
                    TR2.CharacterFormat.FontSize = 11;
                }
            }

            //Save the document
            doc.SaveToFile("WordTable.docx", FileFormat.Docx2013);
        }
    }
}

C#/VB.NET: Create a Table in Word

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Published in Table

Tagged under

doc net Table

Monday, 28 February 2022 06:50

C#/VB.NET: Group or Ungroup Rows and Columns in Excel

Grouping rows and columns in an Excel worksheet can separate data into groups, and each group presents information about one item. You can then expand or collapse certain groups to display only the most important information. In this article, you'll learn how to group or ungroup Excel rows and columns using Spire.XLS for .NET in C# and VB.NET.

Group Rows and Columns
Ungroup Rows and Columns

Install Spire.XLS for .NET

To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.

Package Manager

PM> Install-Package Spire.XLS

Group Rows and Columns

The following are the steps to group rows and columns using Spire.XLS for .NET.

Create a Workbook object.
Load a sample Excel file using Workbook.LoadFromFile() method.
Get the specific sheet through Workbook.Worksheets[index] property.
Group rows using Worksheet.GroupByRows() method.
Group columns using Worksheet.GroupByColumns() method.
Save the result to another Excel file using Workbook.SaveToFile() method.

C#
VB.NET

using Spire.Xls;

namespace GroupRowsAndColumns
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook object
            Workbook workbook = new Workbook();

            //Load a sample Excel file
            workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\sample.xlsx");

            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Group rows
            sheet.GroupByRows(2, 5, false);
            sheet.GroupByRows(7, 10, false);

            //Group columns
            sheet.GroupByColumns(5, 6, false);

            //Save to another Excel file
            workbook.SaveToFile("GroupRowsAndColumns.xlsx", ExcelVersion.Version2016);
        }
    }
}

C#/VB.NET: Group or Ungroup Rows and Columns in Excel

Ungroup Rows and Columns

The following are the steps to ungroup rows and columns using Spire.XLS for .NET.

Create a Workbook object.
Load a sample Excel file using Workbook.LoadFromFile() method.
Get the specific sheet through Workbook.Worksheets[index] property.
Ungroup rows using Worksheet.UngroupByRows() method.
Ungroup columns using Worksheet.UngroupByColumns() method.
Save the result to another Excel file using Workbook.SaveToFile() method.

C#
VB.NET

using Spire.Xls;

namespace UngroupRowsAndColumns
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook object
            Workbook workbook = new Workbook();

            //Load a sample Excel file
            workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\sample.xlsx");

            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Ungroup rows
            sheet.UngroupByRows(2, 5);
            sheet.UngroupByRows(7, 10);

            //Ungroup columns
            sheet.UngroupByColumns(5, 6);

            //Save to a different Excel file
            workbook.SaveToFile("UngroupRowsAndColumns.xlsx", ExcelVersion.Version2016);
        }
    }
}

C#/VB.NET: Group or Ungroup Rows and Columns in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Published in Data

Tagged under

xls net Data

Thursday, 07 April 2022 06:38

C#/VB.NET: Set Paragraph Indents in Word

In Word documents, indentation is a paragraph format used to adjust the distance between paragraph body and page margin. It includes left indent, right indent, first line indent and hanging indent. Left indent and right indent can be applied to all lines of a paragraph, while first line indent can only be applied to first line of a paragraph. As for the hanging indent, it can be applied to every line of the paragraph except the first one. This article introduces how to programmatically set paragraph indents in a Word document using Spire.Doc for .NET.

Install Spire.Doc for .NET

To begin with, you need to add the DLL files included in the Spire.Doc for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.

Package Manager

PM> Install-Package Spire.Doc

Set Paragraph Indents in Word

The table below lists some of the core classes and properties that are used to set different paragraph indents in a Word document.

Name	Description
ParagraphFormat Class	Represents the format of a paragraph.
ParagraphFormat.LeftIndent Property	Returns or sets the value that represents the left indent for paragraph.
ParagraphFormat.RightIndent Property	Returns or sets the value that represents the right indent for paragraph.
ParagraphFormat.FirstLineIndent Property	Gets or sets the value for first line or hanging indent. Positive value represents first-line indent, and Negative value represents hanging indent.

The detailed steps are as follows:

Create a Document instance.
Load a sample Word document using Document.LoadFromFile() method.
Get a specified section using Document.Sections[] property.
Get a specified paragraph using Section.Paragraphs[] property.
Get the paragraph format using Paragraph.Format property, and then set the paragraph indent using the above listed properties of ParagraphFormat class.
Save the document to another file using Document.SaveToFile() method.

C#
VB.NET

using Spire.Doc;
using Spire.Doc.Documents;

namespace WordIndent
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Document instance
            Document doc = new Document();

            //Load a sample Word document
            doc.LoadFromFile("sample.docx");

            //Get the first paragraph and set left indent
            Paragraph para1 = doc.Sections[0].Paragraphs[0];
            para1.Format.LeftIndent = 30;

            //Get the second paragraph and set right indent
            Paragraph para2 = doc.Sections[0].Paragraphs[1];
            para2.Format.RightIndent = 30;

            //Get the third paragraph and set first line indent
            Paragraph para3 = doc.Sections[0].Paragraphs[2];
            para3.Format.FirstLineIndent = 30;

            //Get the fourth paragraph and set hanging indent
            Paragraph para4 = doc.Sections[0].Paragraphs[3];
            para4.Format.FirstLineIndent = -30;

            //Save the document to file
            doc.SaveToFile("Indent.docx", FileFormat.Docx2010);
        }
    }
}

C#/VB.NET: Set Paragraph Indents in Word

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Published in Paragraph

Tagged under

doc net Paragraph