.NET (1316)
Children categories
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.
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);
}
}
}
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
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.
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();
}
}
}
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());
}
}
}
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.
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.
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);
}
}
}
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);
}
}
}
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.
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.
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);
}
}
}
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.
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.
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);
}
}
}
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.
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.
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.
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);
}
}
}
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);
}
}
}
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.
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.
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);
}
}
}
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.
A page break is a marker that controls where one page ends and where a new page begins. If you want to move the content after a certain place to the next page in your Word document, you can insert a page break. In this article, you will learn how to insert page break into Word documents in C# and VB.NET using Spire.Doc for .NET library.
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.
PM> Install-Package Spire.Doc
Insert Page Break after a Specific Paragraph
The following are the steps to insert page break after a specific paragraph:
- Create a Document instance.
- Load a Word document using Document.LoadFromFile() method.
- Get the desired section using Document.Sections[sectionIndex] property.
- Get the desired paragraph using Section.Paragraphs[paragraphIndex] property.
- Add a page break to the paragraph using Paragraph.AppendBreak(BreakType.PageBreak) method.
- Save the result document using Document.SaveToFile() method.
- C#
- VB.NET
using Spire.Doc;
using Spire.Doc.Documents;
namespace InsertPageBreakAfterParagraph
{
class Program
{
static void Main(string[] args)
{
//Create a Document instance
Document document = new Document();
//Load a Word document
document.LoadFromFile("Sample.docx");
//Get the first section
Section section = document.Sections[0];
//Get the 2nd paragraph in the section
Paragraph paragraph = section.Paragraphs[1];
//Append a page break to the paragraph
paragraph.AppendBreak(BreakType.PageBreak);
//Save the result document
document.SaveToFile("InsertPageBreak.docx", FileFormat.Docx2013);
}
}
}
Insert Page Break after a Specific Text
The following are the steps to insert a page break after a specific text:
- Create a Document instance.
- Load a Word document using Document.LoadFromFile() method.
- Find a specific text using Document.FindString() method.
- Access the text range of the searched text using TextSelection.GetAsOneRange() method.
- Get the paragraph where the text range is located using ParagraphBase.OwnerParagraph property.
- Get the position index of the text range in the paragraph using Paragraph.ChildObjects.IndexOf() method.
- Initialize an instance of Break class to create a page break.
- Insert the page break after the searched text using Paragraph.ChildObjects.Insert() method.
- Save the result document using Document.SaveToFile() method.
- C#
- VB.NET
using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Fields;
using System;
namespace InsertPageBreakAfterText
{
class Program
{
static void Main(string[] args)
{
//Create a Document instance
Document document = new Document();
//Load a Word document
document.LoadFromFile("Sample.docx");
//Search a specific text
TextSelection selection = document.FindString("celebration", true, true);
//Get the text range of the seached text
TextRange range = selection.GetAsOneRange();
//Get the paragraph where the text range is located
Paragraph paragraph = range.OwnerParagraph;
//Get the position index of the text range in the paragraph
int index = paragraph.ChildObjects.IndexOf(range);
//Create a page break
Break pageBreak = new Break(document, BreakType.PageBreak);
//Insert the page break after the searched text
paragraph.ChildObjects.Insert(index + 1, pageBreak);
//Save the result document
document.SaveToFile("InsertPageBreakAfterText.docx", FileFormat.Docx2013);
}
}
}
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.
The comment feature in Microsoft Word provides an excellent way for people to add their insights or opinions to a Word document without having to change or interrupt the content of the document. If someone comments on a document, the document author or other users can reply to the comment to have a discussion with him, even if they're not viewing the document at the same time. This article will demonstrate how to add, reply to or delete comments in Word in C# and VB.NET using Spire.Doc for .NET library.
- Add a Comment to Paragraph in Word in C# and VB.NET
- Add a Comment to Text in Word in C# and VB.NET
- Reply to a Comment in Word in C# and VB.NET
- Delete Comments in Word in C# and VB.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.
PM> Install-Package Spire.Doc
Add a Comment to Paragraph in Word in C# and VB.NET
Spire.Doc for .NET provides the Paragraph.AppendComment() method to add a comment to a specific paragraph. The following are the detailed steps:
- Initialize an instance of the Document class.
- Load a Word document using Document.LoadFromFile() method.
- Access a specific section in the document by its index through Document.Sections[int] property.
- Access a specific paragraph in the section by its index through Section.Paragraphs[int] property.
- Add a comment to the paragraph using Paragraph.AppendComment() method.
- Set the author of the comment through Comment.Format.Author property.
- Save the result document using Document.SaveToFile() method.
- C#
- VB.NET
using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Fields;
namespace AddComments
{
internal class Program
{
static void Main(string[] args)
{
//Initialize an instance of the Document class
Document document = new Document();
//Load a Word document
document.LoadFromFile(@"Sample.docx");
//Get the first section in the document
Section section = document.Sections[0];
//Get the first paragraph in the section
Paragraph paragraph = section.Paragraphs[0];
//Add a comment to the paragraph
Comment comment = paragraph.AppendComment("This comment is added using Spire.Doc for .NET.");
//Set comment author
comment.Format.Author = "Eiceblue";
comment.Format.Initial = "CM";
//Save the result document
document.SaveToFile("AddCommentToParagraph.docx", FileFormat.Docx2013);
document.Close();
}
}
}
Add a Comment to Text in Word in C# and VB.NET
The Paragraph.AppendComment() method is used to add comments to an entire paragraph. By default, the comment marks will be placed at the end of the paragraph. To add a comment to a specific text, you need to search for the text using Document.FindString() method, then place the comment marks at the beginning and end of the text. The following are the detailed steps:
- Initialize an instance of the Document class.
- Load a Word document using Document.LoadFromFile() method.
- Find the specific text in the document using Document.FindString() method.
- Create a comment start mark and a comment end mark, which will be placed at the beginning and end of the found text respectively.
- Initialize an instance of the Comment class to create a new comment. Then set the content and author for the comment.
- Get the owner paragraph of the found text. Then add the comment to the paragraph as a child object.
- Insert the comment start mark before the text range and the comment end mark after the text range.
- Save the result document using Document.SaveToFile() method.
- C#
- VB.NET
using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Fields;
namespace AddCommentsToText
{
internal class Program
{
static void Main(string[] args)
{
//Initialize an instance of the Document class
Document document = new Document();
//Load a Word document
document.LoadFromFile(@"CommentTemplate.docx");
//Find a specific string
TextSelection find = document.FindString("Microsoft Office", false, true);
//Create the comment start mark and comment end mark
CommentMark commentmarkStart = new CommentMark(document);
commentmarkStart.Type = CommentMarkType.CommentStart;
CommentMark commentmarkEnd = new CommentMark(document);
commentmarkEnd.Type = CommentMarkType.CommentEnd;
//Create a comment and set its content and author
Comment comment = new Comment(document);
comment.Body.AddParagraph().Text = "Developed by Microsoft.";
comment.Format.Author = "Shaun";
//Get the found text as a single text range
TextRange range = find.GetAsOneRange();
//Get the owner paragraph of the text range
Paragraph para = range.OwnerParagraph;
//Add the comment to the paragraph
para.ChildObjects.Add(comment);
//Get the index of text range in the paragraph
int index = para.ChildObjects.IndexOf(range);
//Set comment ID for the comment mark start and comment mark end
commentmarkStart.CommentId = comment.Format.CommentId;
commentmarkEnd.CommentId = comment.Format.CommentId;
//Insert the comment start mark before the text range
para.ChildObjects.Insert(index, commentmarkStart);
//Insert the comment end mark after the text range
para.ChildObjects.Insert(index + 2, commentmarkEnd);
//Save the result document
document.SaveToFile("AddCommentForText.docx", FileFormat.Docx2013);
document.Close();
}
}
}
Reply to a Comment in Word in C# and VB.NET
To add a reply to an existing comment, you can use the Comment.ReplyToComment() method. The following are the detailed steps:
- Initialize an instance of the Document class.
- Load a Word document using Document.LoadFromFile() method.
- Get a specific comment in the document through Document.Comments[int] property.
- Initialize an instance of the Comment class to create a new comment. Then set the content and author for the comment.
- Add the new comment as a reply to the specific comment using Comment.ReplyToComment() method.
- Save the result document using Document.SaveToFile() method.
- C#
- VB.NET
using Spire.Doc;
using Spire.Doc.Fields;
namespace ReplyToComments
{
internal class Program
{
static void Main(string[] args)
{
//Initialize an instance of the Document class
Document document = new Document();
//Load a Word document
document.LoadFromFile(@"AddCommentToParagraph.docx");
//Get the first comment in the document
Comment comment1 = document.Comments[0];
//Create a new comment and specify its author and content
Comment replyComment1 = new Comment(document);
replyComment1.Format.Author = "Michael";
replyComment1.Body.AddParagraph().AppendText("Spire.Doc is a wonderful Word library.");
//Add the comment as a reply to the first comment
comment1.ReplyToComment(replyComment1);
//Save the result document
document.SaveToFile("ReplyToComment.docx", FileFormat.Docx2013);
document.Close();
}
}
}
Delete Comments in Word in C# and VB.NET
Spire.Doc for .NET offers the Document.Comments.RemoveAt(int) method to remove a specific comment from a Word document and the Document.Comments.Clear() method to remove all comments from a Word document. The following are the detailed steps:
- Initialize an instance of the Document class.
- Load a Word document using Document.LoadFromFile() method.
- Delete a specific comment or all comments in the document using Document.Comments.RemoveAt(int) method or Document.Comments.Clear() method.
- Save the result document using Document.SaveToFile() method.
- C#
- VB.NET
using Spire.Doc;
namespace DeleteComments
{
internal class Program
{
static void Main(string[] args)
{
//Initialize an instance of the Document class
Document document = new Document();
//Load a Word document
document.LoadFromFile(@"AddCommentToParagraph.docx");
//Delete the first comment in the document
document.Comments.RemoveAt(0);
//Delete all comments in the document
//document.Comments.Clear();
//Save the result document
document.SaveToFile("DeleteComment.docx", FileFormat.Docx2013);
document.Close();
}
}
}
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.
Word Decryption is a process to decode encrypted Word document. It requires a password or secret key. If readers want to open and read a protected Word, they need to decrypt this Word document firstly. This guide demonstrates an easy and convenient solution to decrypt Word in C# and VB.NET via Spire.Doc for .NET.
Spire.Doc for .NET, specially developed for programmers to manipulate Word without Word Automation, provides users a method Document.LoadFromFile(String fileName, FileFormat fileFormat, String password) of Document class to open encrypted Word document. It also provides another method Document.RemoveEncryption() to decrypt Word without any protection. Through these two methods, users can decrypt Word easily with Spire.Doc for .NET. Download and install Spire.Doc for .NET. Then follow the code to decrypt.
using Spire.Doc;
namespace DecryptWord
{
class Decryption
{
static void Main(string[] args)
{
//Load Encrypted Word
Document document = new Document();
document.LoadFromFile(@"E:\Work\Documents\Student Transcript.docx", FileFormat.Docx,"123456");
//Decrypt
document.RemoveEncryption();
//Save and Launch
document.SaveToFile("decryption.docx", FileFormat.Docx);
System.Diagnostics.Process.Start("decryption.docx");
}
}
}
Imports Spire.Doc
Namespace DecryptWord
Friend Class Decryption
Shared Sub Main(ByVal args() As String)
'Load Encrypted Word
Dim document As New Document()
document.LoadFromFile("E:\Work\Documents\Student Transcript.docx", FileFormat.Docx, "123456")
'Decrypt
document.RemoveEncryption()
'Save and Launch
document.SaveToFile("decryption.docx", FileFormat.Docx)
System.Diagnostics.Process.Start("decryption.docx")
End Sub
End Class
End Namespace
Spire.Doc, professional Word component, is specially designed for developers to fast generate, write, modify and save Word documents in .NET, Silverlight and WPF with C# and VB.NET. Also, it supports conversion between Word and other popular formats, such as PDF, HTML, Image, Text and so on, in .NET and WPF platform.
