Knowledgebase (2330)
Children categories
Background
Excel is widely used to organize data manipulations like arithmetic operations. Excel provides many built-in functions which automate a number of types of calculation. Functions are pre-programmed formulate for example, the square-root function, trigonometric functions, logarithms etc. Excel has more than 300 functions covering a range of statistical, mathematical, financial and logical operations. There is no doubt that using a function offers a shortcut method.
Calculate Formulas in XLS Document
Microsoft Excel is a powerful tool which has many uses, the most basic feature of which is performing functions. The aim of this article is to help you perform simple arithmetic operations on values in programming by using excel functions. Spire.Xls for .NET can help you easily create a new excel document or load an existing excel document into program, and calculate data of designated cell by function. Applied in Console platform, WinForm and Asp.net, It provide different types of mathematical functions, statistical functions , logic functions ,and string functions to calculate data with C# codes.
The following is the method example of using Console application to show how Spire.XLS for .NET realizes the calculation formula:
Step 1: Build a console application, and add spire.XLS.dll, Spire.Common.dll assembly.
Step 2: Instantiate an object of Spire.Xls.WorkBook, and add a “WorkSheet” in WorkBook object.
Workbook workbook = new Workbook(); Worksheet sheet = workbook. Worksheets[0];
Step 3: Set the value and format in Cell A1 and Cell A3.veiwing the C# Code.
//set Column A, B, C width sheet.SetColumnWidth(1, 32); sheet.SetColumnWidth(2, 16); sheet.SetColumnWidth(3, 16); // Set value of Cell A1 sheet.Range[currentRow++, 1].Value = "Examples of formulas :"; // Set value of Cell A2. sheet.Range[++currentRow, 1].Value = "Test data:"; // Set text format Of Cell A1 CellRange range = sheet.Range["A1"]; range.Style.Font.IsBold = true; range.Style.FillPattern = ExcelPatternType.Solid; range.Style.KnownColor = ExcelColors.LightGreen1; range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;
Step 4: Set some cells value and then to sum up some cells data and the results will be displayed in one of the cells.
sheet.Range[currentRow, 2].NumberValue = 7.3; sheet.Range[currentRow, 3].NumberValue = 5; sheet.Range[currentRow, 4].NumberValue = 8.2; sheet.Range[currentRow, 5].NumberValue = 4; sheet.Range[currentRow, 6].NumberValue = 3; sheet.Range[currentRow, 7].NumberValue = 11.3; //Create arithmetic expression string about cells currentFormula = "=Sheet1!$B$3 + Sheet1!$C$3+Sheet1!$D$3+Sheet1!$E$3+Sheet1!$F$3+Sheet1!$G$3"; //Caculate arithmetic expression about cells formulaResult = workbook.CaculateFormulaValue(currentFormula); value = formulaResult.ToString(); sheet.Range[currentRow, 2].Value = value;
Step 5: Respectively set value and text format of Cell A4, B4.
sheet.Range[++currentRow, 1].Value = "Formulas"; ; sheet.Range[currentRow, 2].Value = "Results"; range = sheet.Range[currentRow, 1, currentRow, 2]; range.Style.Font.IsBold = true; range.Style.KnownColor = ExcelColors.LightGreen1; range.Style.FillPattern = ExcelPatternType.Solid; range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;
Step 6: Realize calculation simple expression.
// Create arithmetic tables enclosed type string currentFormula = "=33*3/4-2+10"; sheet.Range[++currentRow, 1].Text = currentFormula; // Caculate arithmetic expression formulaResult = workbook.CaculateFormulaValue(currentFormula); value = formulaResult.ToString(); sheet.Range[currentRow, 2].Value = value;
Step 7: Realize some mathematic functions.
//absolute value function . currentFormula = "=ABS(-1.21)"; sheet.Range[currentRow, 1].Text = currentFormula; sheet.Range[currentRow++, 2].Formula = currentFormula;
Step 8: Realize some logic function.
//NOT function //Create NOT function string currentFormula = "=NOT(true)"; sheet.Range[currentRow, 1].Text = currentFormula; //Caculate NOT function formulaResult = workbook.CaculateFormulaValue(currentFormula); value = formulaResult.ToString(); sheet.Range[currentRow, 2].Value = value; sheet.Range[currentRow, 2].HorizontalAlignment = HorizontalAlignType.Right;
Step 9: Realize some string handling functions.
//Get the substring // Build substring function currentFormula = "=MID(\"world\",4,2)"; sheet.Range[++currentRow, 1].Text = currentFormula; //Caculate substring function formulaResult = workbook.CaculateFormulaValue(currentFormula); value = formulaResult.ToString(); sheet.Range[currentRow, 2].Value = value; sheet.Range[currentRow, 2].HorizontalAlignment = HorizontalAlignType.Right;
Step 10: Realize a random function.
// Random function // Create random function string. currentFormula = "=RAND()"; sheet.Range[++currentRow, 1].Text = currentFormula; //Caculate random function formulaResult = workbook.CaculateFormulaValue(currentFormula); value = formulaResult.ToString(); sheet.Range[currentRow, 2].Value = value;
Step 11: Save workbook object as file.
workbook.SaveToFile("formulaTest.xls",ExcelVersion.Version97to2003);
Viewing the full c# code
using Spire.Xls;
namespace XlsCalculateFormula
{
class Program
{
static void Main(string[] args)
{
// Instantiate a new Workbook object
Workbook workbook = new Workbook();
// Access the first worksheet in the workbook
Worksheet sheet = workbook.Worksheets[0];
int currentRow = 1;
string currentFormula = string.Empty;
object formulaResult = null;
string value = string.Empty;
// Set column widths for columns A, B, and C (1-based index)
sheet.SetColumnWidth(1, 32);
sheet.SetColumnWidth(2, 16);
sheet.SetColumnWidth(3, 16);
// Set the value of Cell A1
sheet.Range[currentRow++, 1].Value = "Examples of formulas :";
// Set the value of Cell A2
sheet.Range[++currentRow, 1].Value = "Test data:";
// Set the style of Cell A1
CellRange range = sheet.Range["A1"];
range.Style.Font.IsBold = true;
range.Style.FillPattern = ExcelPatternType.Solid;
range.Style.KnownColor = ExcelColors.LightGreen1;
range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;
// Populate test data in row 3 (columns B to G)
sheet.Range[currentRow, 2].NumberValue = 7.3;
sheet.Range[currentRow, 3].NumberValue = 5; ;
sheet.Range[currentRow, 4].NumberValue = 8.2;
sheet.Range[currentRow, 5].NumberValue = 4;
sheet.Range[currentRow, 6].NumberValue = 3;
sheet.Range[currentRow, 7].NumberValue = 11.3;
// Define a formula that sums the values in cells B3 through G3
currentFormula = "=Sheet1!$B$3 + Sheet1!$C$3+Sheet1!$D$3+Sheet1!$E$3+Sheet1!$F$3+Sheet1!$G$3";
// Calculate the result of the formula
formulaResult = workbook.CalculateFormulaValue(currentFormula);
value = formulaResult.ToString();
sheet.Range[currentRow, 2].Value = value;
// Set the value and format of two head cell
sheet.Range[++currentRow, 1].Value = "Formulas"; ;
sheet.Range[currentRow, 2].Value = "Results";
sheet.Range[currentRow, 2].HorizontalAlignment = HorizontalAlignType.Right;
range = sheet.Range[currentRow, 1, currentRow, 2];
range.Style.Font.IsBold = true;
range.Style.KnownColor = ExcelColors.LightGreen1;
range.Style.FillPattern = ExcelPatternType.Solid;
range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;
// --- Arithmetic Expression Example ---
// Define a basic arithmetic formula
currentFormula = "=33*3/4-2+10";
sheet.Range[++currentRow, 1].Text = currentFormula;
// Calculate and display the result
formulaResult = workbook.CalculateFormulaValue(currentFormula);
value = formulaResult.ToString();
sheet.Range[currentRow, 2].Value = value;
// --- Mathematical Function: ABS (Absolute Value) ---
// Define an ABS function formula
currentFormula = "=ABS(-1.21)";
sheet.Range[++currentRow, 1].Text = currentFormula;
// Calculate and display the absolute value
formulaResult = workbook.CalculateFormulaValue(currentFormula);
value = formulaResult.ToString();
sheet.Range[currentRow, 2].Value = value;
// --- Statistical Function: SUM ---
// Define a SUM function formula
currentFormula = "=SUM(18,29)";
sheet.Range[++currentRow, 1].Text = currentFormula;
// Calculate and display the sum
formulaResult = workbook.CalculateFormulaValue(currentFormula);
value = formulaResult.ToString();
sheet.Range[currentRow, 2].Value = value;
// --- Logical Function: NOT ---
// Define a NOT function formula
currentFormula = "=NOT(true)";
sheet.Range[currentRow, 1].Text = currentFormula;
// Calculate and display the logical result
formulaResult = workbook.CalculateFormulaValue(currentFormula);
value = formulaResult.ToString();
sheet.Range[currentRow, 2].Value = value;
sheet.Range[currentRow, 2].HorizontalAlignment = HorizontalAlignType.Right;
// --- Text Function: MID (Substring Extraction) ---
// Define a MID function to extract characters from a string
currentFormula = "=MID(\"world\",4,2)";
sheet.Range[++currentRow, 1].Text = currentFormula;
// Calculate and display the substring result
formulaResult = workbook.CalculateFormulaValue(currentFormula);
value = formulaResult.ToString();
sheet.Range[currentRow, 2].Value = value;
sheet.Range[currentRow, 2].HorizontalAlignment = HorizontalAlignType.Right;
// --- Random Number Function: RAND() ---
// Define a RAND function to generate a random number between 0 and 1
currentFormula = "=RAND()";
sheet.Range[++currentRow, 1].Text = currentFormula;
// Calculate and display the random value
formulaResult = workbook.CalculateFormulaValue(currentFormula);
value = formulaResult.ToString();
sheet.Range[currentRow, 2].Value = value;
// Save the workbook to a file
workbook.SaveToFile("formulaTest2.xls", ExcelVersion.Version97to2003);
System.Diagnostics.Process.Start("formulaTest2.xls");
}
}
}
Screenshot:
C#: Dynamically Create, Load, and Save Excel Files via Stream
2024-12-30 07:36:00 Written by daisy zhangUsing stream operations in C#, developers can dynamically create, load, and save Excel files, enabling flexible and efficient data handling. This approach eliminates the need for physical file storage, improving application performance and responsiveness. Ideal for real-time data manipulation or environments with storage limitations, it streamlines data exchange and system integration. This article demonstrates how to create, load, modify, and save Excel files using streams in C# with Spire.XLS for .NET, offering agile and scalable data management solutions.
- Dynamically Create an Excel File and Save It to Stream
- Load and Read Excel Files from Stream with C#
- Modify an Excel File in Stream with C#
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
Dynamically Create an Excel File and Save It to Stream
Using Spire.XLS for .NET, developers can dynamically create Excel files in memory by initializing a Workbook object, populating it with data and formatting, and then saving the workbook to a stream using the Workbook.SaveToStream() method. This approach eliminates the need for physical file storage, enhancing both application performance and responsiveness.
Below are the steps for creating an Excel file and saving it to a stream with C#:
- Create an instance of the Workbook class to generate a new Excel workbook, which includes three default worksheets.
- Retrieve a specific worksheet using the Workbook.Worksheets[] property.
- Define the data to write to the worksheet, such as using a DataTable to organize the data.
- Insert the data into the worksheet using the Worksheet.InsertDataTable() method or the Worksheet.Range[].Value property for individual cell values.
- Format the worksheet cells, applying styles like colors, fonts, and borders, or adjusting column widths as needed.
- Save the workbook to a memory stream using the Workbook.SaveToStream() method. The stream can then be used for further processing, such as saving it to a file or transmitting it over a network.
- C#
using Spire.Xls;
using System.Data;
using System.Drawing;
namespace CreateExcelStream
{
class Program
{
static void Main(string[] args)
{
// Create a new workbook instance
Workbook workbook = new Workbook();
// Access the first worksheet in the workbook
Worksheet sheet = workbook.Worksheets[0];
// Create and populate a DataTable with sample data
DataTable dataTable = new DataTable("Data");
dataTable.Columns.Add("ID", typeof(int));
dataTable.Columns.Add("Name", typeof(string));
dataTable.Columns.Add("Age", typeof(int));
dataTable.Columns.Add("Country", typeof(string));
dataTable.Columns.Add("Salary ($)", typeof(decimal));
dataTable.Rows.Add(101, "John Smith", 28, "USA", 54000m);
dataTable.Rows.Add(102, "Maria Garcia", 34, "Spain", 65500m);
dataTable.Rows.Add(103, "Liam Johnson", 22, "Canada", 48000m);
dataTable.Rows.Add(104, "Emma Brown", 30, "Australia", 72300m);
dataTable.Rows.Add(105, "Wei Zhang", 40, "China", 58700m);
dataTable.Rows.Add(106, "Sofia Lopez", 26, "Mexico", 45200m);
// Insert data from the DataTable into the worksheet
sheet.InsertDataTable(dataTable, true, 1, 1);
// Format the worksheet
// Style the header row
sheet.Rows[0].Style.Color = Color.LightGreen;
sheet.Rows[0].Style.Font.FontName = "Arial";
sheet.Rows[0].Style.Font.Size = 12f;
sheet.Rows[0].BorderAround(); // Apply borders around the header row
sheet.Rows[0].Borders.Color = Color.Blue;
// Style the data rows
for (int i = 1; i < sheet.AllocatedRange.Rows.Count(); i++)
{
sheet.Rows[i].Style.Color = Color.LightGray;
sheet.Rows[i].Style.Font.FontName = "Arial";
sheet.Rows[i].Style.Font.Size = 11f;
}
// Adjust the column widths to fit the content
for (int j = 1; j <= sheet.AllocatedRange.Columns.Count(); j++)
{
sheet.AutoFitColumn(j);
}
// Save the workbook to a memory stream
MemoryStream stream = new MemoryStream();
workbook.SaveToStream(stream, FileFormat.Version2016);
// Write the stream content to a file
File.WriteAllBytes("output/CreateExcelByStream.xlsx", stream.ToArray());
// Release resources
workbook.Dispose();
}
}
}
Load and Read Excel Files from Stream with C#
Spire.XLS for .NET simplifies loading Excel files directly from a stream using the Workbook.LoadFromStream() method. Once the file is loaded, developers can easily access and read cell data, optimizing memory usage and enabling fast, flexible data processing without requiring file I/O operations.
The steps for loading and reading Excel files from streams with C# are as follows:
- Create a Workbook instance.
- Create a MemoryStream or FileStream object.
- Use the Workbook.LoadFromStream() method to load the Excel file from the stream into the workbook.
- Retrieve the first worksheet using the Workbook.Worksheets[] property.
- Loop through the rows and columns of the worksheet to extract the cell through the Worksheet.AllocatedRange[].Value property.
- Print the extracted data, or use the data for further operations.
- C#
using Spire.Xls;
namespace LoadExcelStream
{
class Program
{
static void Main(string[] args)
{
// Create an instance of the Workbook class
Workbook workbook = new Workbook();
// Create a memory stream
MemoryStream stream = new MemoryStream();
File.OpenRead("Sample.xlsx").CopyTo(stream);
// Load the Excel file from the stream
workbook.LoadFromStream(stream);
// Access the first worksheet in the workbook
Worksheet sheet = workbook.Worksheets[0];
// Initialize a list to store the data retrieved from the worksheet
List<List<string>> data = new List<List<string>>();
for (int i = 0; i < sheet.AllocatedRange.Rows.Count(); i++)
{
// Create a list to hold each row of data
List<string> lines = new List<string>();
for (int j = 0; j < sheet.AllocatedRange.Columns.Count(); j++)
{
// Retrieve the cell text and add it to the row
lines.Add(sheet.AllocatedRange[i + 1, j + 1].Text);
}
// Add the row to the data list
data.Add(lines);
}
// Print the retrieved data or use it for further operations
foreach (List<string> lines in data)
{
Console.WriteLine(string.Join(" | ", lines));
}
}
}
}
Modify an Excel File in Stream with C#
With Spire.XLS for .NET, developers can modify an Excel file in memory by first loading it into a Workbook object with the LoadFromStream() method. After making updates (such as changing cell values or formatting), the file can be saved back to a stream using the Workbook.SaveToStream() method. This approach allows seamless real-time changes without relying on physical storage.
Follow the steps below to modify Excel files in streams with C#:
- Create a Workbook instance to represent the Excel file.
- Create a MemoryStream or FileStream instance.
- Use the Workbook.LoadFromStream() to load the Excel file from the stream.
- Access the first worksheet through the Workbook.Worksheets[] property.
- Modify the header row and the data rows' styles (font, size, background color, etc.) through the properties in CellRange.Style.
- Autofit the columns to adjust their width based on the content using the Worksheet.AutoFitColumn() method.
- Save the changes to the stream using the Workbook.SaveToStream() method.
- C#
using Spire.Xls;
using System.Drawing;
namespace ModifyExcelStream
{
class Program
{
static void Main(string[] args)
{
// Create a new instance of the Workbook class
Workbook workbook = new Workbook();
// Create a memory stream
MemoryStream stream = new MemoryStream();
File.OpenRead("Sample.xlsx").CopyTo(stream);
// Load the Excel file from the stream
workbook.LoadFromStream(stream);
// Access the first worksheet in the workbook
Worksheet sheet = workbook.Worksheets[0];
// Modify the style of the header row
CellRange headerRow = sheet.AllocatedRange.Rows[0];
headerRow.Style.Font.FontName = "Times New Roman";
headerRow.Style.Font.Size = 12f;
headerRow.Style.Color = Color.LightBlue;
// Modify the style of the data rows
for (int i = 1; i < sheet.AllocatedRange.Rows.Count(); i++)
{
CellRange dataRow = sheet.AllocatedRange.Rows[i];
dataRow.Style.Font.FontName = "Arial";
dataRow.Style.Font.Size = 10f;
dataRow.Style.Color = Color.LightGray;
// Alternate row coloring (even rows)
if (i % 2 == 0)
{
dataRow.Style.Color = Color.LightSlateGray;
}
}
// Autofit columns to adjust their width based on content
for (int k = 1; k <= sheet.AllocatedRange.Columns.Count(); k++)
{
sheet.AutoFitColumn(k);
}
// Change the border color
sheet.AllocatedRange.Style.Borders.Color = Color.White;
// Save the modified workbook back to the stream
workbook.SaveToStream(stream);
// Write the stream content to a new file
File.WriteAllBytes("output/ModifyExcelByStream.xlsx", stream.ToArray());
// Release resources
workbook.Dispose();
}
}
}
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.
XPS (XML Paper Specification) is a fixed-layout document format designed to preserve document fidelity and provide device-independent document appearance. It is similar to PDF, but is based on XML rather than PostScript. If you want to save a Word document to a fixed-layout file format, XPS would be an option. This article will demonstrate how to convert Word documents to XPS in C# and VB.NET 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
Convert Word to XPS in C# and VB.NET
The following are the detailed steps to convert a Word document to XPS using Spire.Doc for .NET:
- Initialize an instance of Document class.
- Load a Word document using Document.LoadFromFile() method.
- Save the Word document to XPS using Document.SaveToFile(string filePath, FileFormat fileFormat) method.
- C#
- VB.NET
using Spire.Doc;
namespace ConvertWordToXps
{
class Program
{
static void Main(string[] args)
{
//Create a Document instance
Document doc = new Document();
//Load a Word document
doc.LoadFromFile("Sample.docx");
//convert the document to XPS
doc.SaveToFile("ToXPS.xps", FileFormat.XPS);
}
}
}
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.
More...
Programme Guide for Spire.Barcode
