How to Use Coupon Code
Coupon Code for PayPal
Select "PayPal" as payment method, input coupon code as the image below. Press "Submit" Button.
Coupon Code for 2Checkout
Select "2Checkout" as payment method, when you confirm your order, input coupon code as the image below. Click "Next" button.
How to Request an invoice of your order
If you want to get invoice of your order, please follow the below steps:
Step 1: Login to E-iceblue website with your purchased account information.
Step 2: Go to "User Information" page by clicking your username.
Step 3: At the Order information area, click "Invoice" and then you will get your invoice in PDF format.
Step 4: Invoice Sample.
If you need any changes on the invoice, please get back to sales@e-iceblue.com for updates. We will help you update it manually.
C#VB.NET: Set Row Height and Column Width in Excel
When creating a spreadsheet, you can adjust the layout and appearance of it by setting the row height and column width. Microsoft Excel provides users with various methods to modify column width and row height, such as dragging the boundaries of columns or rows to the desired size, or entering specific values in the column width box or row height box. However, it is crucial for developers to understand how to achieve this functionality through programming. In this article, we will show you how to set row height and column width in Excel by 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 DLLs files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Set the Row Height in Excel
Spire.XLS for .NET supports users to set the row height programmatically by calling Worksheet.SetRowHeight() method. The following are detailed steps.
- Create an object of Workbook class.
- Load a sample file using Workbook.LoadFromFile() method.
- Get the first sheet from this file by using Workbook.Worksheets[] property.
- Set the height of the first row by calling Worksheet.SetRowHeight() method.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace SetExcelRow
{
class Program
{
static void Main(string[] args)
{
//Create an object of Workbook class
Workbook workbook = new Workbook();
//Load a sample file from disk
workbook.LoadFromFile(@"sample.xlsx");
//Get the first worksheet from the sample file
Worksheet sheet = workbook.Worksheets[0];
//Set the row height of the first row
sheet.SetRowHeight(1, 25);
//Save the result file
workbook.SaveToFile("SetRow.xlsx", ExcelVersion.Version2013);
workbook.Dispose();
}
}
}
Set the Column Width in Excel
What's more, Spire.XLS for .NET also enable users to set the column width in Excel programmatically by calling Worksheet.SetColumnWidth() method. The following are detailed steps.
- Create an object of Workbook class.
- Load a sample file using Workbook.LoadFromFile() method.
- Get the first sheet from this file by using Workbook.Worksheets[] property.
- Set the width of the fourth column by calling Worksheet.SetColumnWidth() method.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace SetExcelColumn
{
class Program
{
static void Main(string[] args)
{
//Create an object of Workbook class
Workbook workbook = new Workbook();
//Load a sample file from disk
workbook.LoadFromFile(@"sample.xlsx");
//Get the first worksheet from the sample file
Worksheet sheet = workbook.Worksheets[0];
//Set the column width of the fourth column
sheet.SetColumnWidth(4, 15);
//Save the result file
workbook.SaveToFile("SetColumn.xlsx", ExcelVersion.Version2013);
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.
C#/VB.NET: Convert Excel to XPS
XPS (XML Paper Specification) is a specification for a page description language and a fixed-document format developed by Microsoft. It defines the layout of a document and the visual appearance of each page. Sometimes you may need to convert an Excel document to XPS for distribution, archiving or printing purposes, and this article will demonstrate how to accomplish this task programmatically 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
Convert Excel to XPS
Spire.XLS for .NET allows you to convert Excel (.xls/ .xlsx) to XPS with only three lines of code. The detailed steps are as follows.
- Create a Workbook object.
- Load a sample Excel document using Workbook.LoadFromFile() method.
- Convert the Excel document to XPS using Workbook.SaveToFile(String, FileFormat) method.
- C#
- VB.NET
using Spire.Xls;
namespace ExceltoXPS
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object.
Workbook workbook = new Workbook();
//Load a sample Excel document
workbook.LoadFromFile(@"E:\Files\\sample0.xlsx", ExcelVersion.Version2010);
//Convert the document to XPS
workbook.SaveToFile("result.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.
How to Resize and Move Excel Charts in C#, VB.NET
Sometimes, we create a spreadsheet that contains a wonderful chart, we may still want to adjust the size and position of the chart in order to make the chart mostly matches the Excel page. In this article, I'll introduce you how to resize a chart to a suitable scale and how to move a chart to a desired position in C#, VB.NET via Spire.XLS.
Within the class of Spire.Xls.Chart, we can set the parameters of XlsShape.LeftColum and XlsShape.TopRow to move a chart to any location on a worksheet, while the size of the chart can be changed by setting the parameters of XlsShape.Width and XlsShape.Height. More details would be as follows:
Test File:
Code Snippet for Resize and Move Chart
Step 1: Create a new instance of workbook and load the test file.
Workbook workbook = new Workbook();
workbook.LoadFromFile("sample.xlsx", ExcelVersion.Version2010);
Step 2: Get the chart from the first worksheet.
Worksheet sheet = workbook.Worksheets[0]; Chart chart = sheet.Charts[0];
Step 3: Set position of the chart.
chart.LeftColumn = 1; chart.TopRow = 7;
Step 4: Resize the chart.
chart.Width = 400; chart.Height = 250;
Step 5: Save the changes to a new file.
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
Result:
Full Code:
using Spire.Xls;
namespace ResizeandMoveExcel
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("sample.xlsx", ExcelVersion.Version2010);
Worksheet sheet = workbook.Worksheets[0];
Chart chart = sheet.Charts[0];
chart.LeftColumn = 1;
chart.TopRow = 7;
chart.Width = 400;
chart.Height = 250;
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
}
}
}
Imports Spire.Xls
Namespace ResizeandMoveExcel
Class Program
Private Shared Sub Main(args As String())
Dim workbook As New Workbook()
workbook.LoadFromFile("sample.xlsx", ExcelVersion.Version2010)
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim chart As Chart = sheet.Charts(0)
chart.LeftColumn = 1
chart.TopRow = 7
chart.Width = 400
chart.Height = 250
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010)
End Sub
End Class
End Namespace
How to set the font and background for TextBox in Excel Chart
Textbox is been widely used in Excel workbooks to give abstract and introduce information for a part of the excel documents. Spire.XLS supports to insert textbox in Excel worksheet and edit the setting of the textbox. We have already shown you how to remove the borderline of textbox in Excel chart. This tutorial will demonstrate how to set the font and background for TextBox in Excel in C#.
Firstly, make sure that Spire.XLS for .NET has been installed on your machine. And then, adds Spire.XLS.dll as reference in the downloaded Bin folder thought the below path: "..\Spire.XLS\Bin\NET4.0\ Spire.XLS.dll".
Now it comes to the details of how to set the text font and background color for textbox in Excel in C# and view the textbox before editing:
Step 1: Create a new instance of workbook and load an Excel file with textbox from file.
Workbook workbook = new Workbook();
workbook.LoadFromFile("sample.xlsx");
Step 2: Get the worksheet named in "Product Report" which contains textbox.
Worksheet sheet = workbook.Worksheets["Product Report"];
Step 3: Get the second textbox which will be edited.
XlsTextBoxShape shape = sheet.TextBoxes[1] as XlsTextBoxShape;
Step 4: Set the font and background color for the second textbox.
//Set the font ExcelFont font = workbook.CreateFont(); font.FontName = "Century Gothic"; font.Size = 10; font.IsBold = true; font.Color = Color.Blue; (new RichText(shape.RichText)).SetFont(0, shape.Text.Length - 1, font); //set background color shape.Fill.FillType = ShapeFillType.SolidColor; shape.Fill.ForeKnownColor = ExcelColors.BlueGray;
Step 5: Save the document to file and launch it.
string output = "result.xlsx"; workbook.SaveToFile(output,ExcelVersion.Version2010); System.Diagnostics.Process.Start(output);
Effective screenshot after set the font and background color for textbox in Excel chart:
Full codes:
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.Shapes;
using System.Drawing;
namespace setFontandBackgroundforTextbox
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("sample.xlsx");
Worksheet sheet = workbook.Worksheets["Product Report"];
XlsTextBoxShape shape = sheet.TextBoxes[1] as XlsTextBoxShape;
//set font
ExcelFont font = workbook.CreateFont();
//font.IsStrikethrough = true;
font.FontName = "Century Gothic";
font.Size = 10;
font.IsBold = true;
font.Color = Color.Blue;
(new RichText(shape.RichText)).SetFont(0, shape.Text.Length - 1, font);
//set background color
shape.Fill.FillType = ShapeFillType.SolidColor;
shape.Fill.ForeKnownColor = ExcelColors.BlueGray;
string output = "result.xlsx";
workbook.SaveToFile(output,ExcelVersion.Version2010);
System.Diagnostics.Process.Start(output);
}
}
Format Data Labels of Series Chart in Presentation in C#, VB.NET
By default, Microsoft PowerPoint automatically hides the data labels when we create a series chart on presentation slide. In order to make your readers have an intuitive understanding of your chart, you can choose to set formatting of label to display series name, category name, value, percentage and adjust its displayed position. In this article, I would like to show you how to format data labels in PowerPoint presentation via Spire.Presentation.
In the class of Spire.Presentation.Charts.ChartDataLabel, it contains properties like LabelValueVisible, PercentageVisible, SeriesNameVisible, CategoryNameVisible, Position and etc , which will enable us to easily manage the data labels formatting as you desired. Look at the pie chart below, it is not that informative if it doesn’t display data labels.
Now, let's format pie chart to display percentages in data labels with following code snippet:
Step 1: Create a new instance of Presentation class and load test the file that contains the pie chart.
Presentation ppt = new Presentation();
ppt.LoadFromFile("Test.pptx");
Step 2: Get the chart from presentation slide.
IChart chart = ppt.Slides[0].Shapes[0] as IChart;
Step 3: Get chart's series.
ChartSeriesFormatCollection sers = chart.Series;
Step 4: Set the position of legend on chart.
chart.ChartLegend.Position = ChartLegendPositionType.TopRight;
Step 5: Initialize four instance of series label and set parameters of each label.
ChartDataLabel cd1 = sers[0].DataLabels.Add();
cd1.PercentageVisible = true;
cd1.Position = ChartDataLabelPosition.Center;
ChartDataLabel cd2 = sers[0].DataLabels.Add();
cd2.PercentageVisible = true;
cd2.Position = ChartDataLabelPosition.Center;
ChartDataLabel cd3 = sers[0].DataLabels.Add();
cd3.PercentageVisible = true;
cd3.Position = ChartDataLabelPosition.Center;
ChartDataLabel cd4 = sers[0].DataLabels.Add();
cd4.PercentageVisible = true;
cd4.Position = ChartDataLabelPosition.Center;
Step 6: Save the changes to a new .pptx file.
ppt.SaveToFile("Result.pptx", FileFormat.Pptx2007);
Result:
Full Code:
using Spire.Presentation;
using Spire.Presentation.Charts;
using Spire.Presentation.Collections;
namespace FormatData
{
class Program
{
static void Main(string[] args)
{
Presentation ppt = new Presentation();
ppt.LoadFromFile("Test.pptx");
IChart chart = ppt.Slides[0].Shapes[0] as IChart;
ChartSeriesFormatCollection sers = chart.Series;
chart.ChartLegend.Position = ChartLegendPositionType.TopRight;
ChartDataLabel cd1 = sers[0].DataLabels.Add();
cd1.PercentageVisible = true;
cd1.Position = ChartDataLabelPosition.Center;
ChartDataLabel cd2 = sers[0].DataLabels.Add();
cd2.PercentageVisible = true;
cd2.Position = ChartDataLabelPosition.Center;
ChartDataLabel cd3 = sers[0].DataLabels.Add();
cd3.PercentageVisible = true;
cd3.Position = ChartDataLabelPosition.Center;
ChartDataLabel cd4 = sers[0].DataLabels.Add();
cd4.PercentageVisible = true;
cd4.Position = ChartDataLabelPosition.Center;
ppt.SaveToFile("Result.pptx", FileFormat.Pptx2007);
}
}
}
Imports Spire.Presentation
Imports Spire.Presentation.Charts
Imports Spire.Presentation.Collections
Namespace FormatData
Class Program
Private Shared Sub Main(args As String())
Dim ppt As New Presentation()
ppt.LoadFromFile("Test.pptx")
Dim chart As IChart = TryCast(ppt.Slides(0).Shapes(0), IChart)
Dim sers As ChartSeriesFormatCollection = chart.Series
chart.ChartLegend.Position = ChartLegendPositionType.TopRight
Dim cd1 As ChartDataLabel = sers(0).DataLabels.Add()
cd1.PercentageVisible = True
cd1.Position = ChartDataLabelPosition.Center
Dim cd2 As ChartDataLabel = sers(0).DataLabels.Add()
cd2.PercentageVisible = True
cd2.Position = ChartDataLabelPosition.Center
Dim cd3 As ChartDataLabel = sers(0).DataLabels.Add()
cd3.PercentageVisible = True
cd3.Position = ChartDataLabelPosition.Center
Dim cd4 As ChartDataLabel = sers(0).DataLabels.Add()
cd4.PercentageVisible = True
cd4.Position = ChartDataLabelPosition.Center
ppt.SaveToFile("Result.pptx", FileFormat.Pptx2007)
End Sub
End Class
End Namespace
Merge Selected Pages from Multiple PDF Files into One in C#/VB.NET
Using Spire.PDF, you can not only merge multiple PDF files into a single file, but also select specific pages from the source files and combine them in one PDF document. The following code snippets demonstrate the same.
Step 1: Get the PDF file paths and store in a string array.
string[] files = { "Sample1.pdf", "Sample2.pdf", "Sample3.pdf" };
Step 2: Load each PDF document to an object of PdfDocument and store all these objects in PdfDocument array.
PdfDocument[] docs = new PdfDocument[files.Length];
for (int i = 0; i < files.Length; i++)
{
docs[i] = new PdfDocument(files[i]);
}
Step 3: Create an instance of PdfDocument class.
PdfDocument doc = new PdfDocument();
Step 4: Call InsertPage(PdfDocument doc, int pageIndex) method and InertPageRange(PdfDocument doc, int startIndex, int endIndex) method to insert selected pages to the new PDF document.
doc.InsertPage(docs[0], 0);
doc.InsertPage(docs[1], 1);
doc.InsertPageRange(docs[2], 2, 5);
Step 5: Save and launch the file.
doc.SaveToFile("Result.pdf");
Process.Start("Result.pdf");
Screen Shot of Effect:
The six pages in the result file are extracted from three sample PDF files.
Full Code:
using Spire.Pdf;
using System.Diagnostics;
namespace MergeSelectedPages
{
class Program
{
static void Main(string[] args)
{
string[] files = { "Sample1.pdf", "Sample2.pdf", "Sample3.pdf" };
PdfDocument[] docs = new PdfDocument[files.Length];
//open pdf documents
for (int i = 0; i < files.Length; i++)
{
docs[i] = new PdfDocument(files[i]);
}
//create a new pdf document and insert selected pages
PdfDocument doc = new PdfDocument();
doc.InsertPage(docs[0], 0);
doc.InsertPage(docs[1], 1);
doc.InsertPageRange(docs[2], 2, 5);
doc.SaveToFile("Result.pdf");
Process.Start("Result.pdf");
}
}
}
Imports Spire.Pdf
Imports System.Diagnostics
Namespace MergeSelectedPages
Class Program
Private Shared Sub Main(args As String())
Dim files As String() = {"Sample1.pdf", "Sample2.pdf", "Sample3.pdf"}
Dim docs As PdfDocument() = New PdfDocument(files.Length - 1) {}
'open pdf documents
For i As Integer = 0 To files.Length - 1
docs(i) = New PdfDocument(files(i))
Next
'create a new pdf document and insert selected pages
Dim doc As New PdfDocument()
doc.InsertPage(docs(0), 0)
doc.InsertPage(docs(1), 1)
doc.InsertPageRange(docs(2), 2, 5)
doc.SaveToFile("Result.pdf")
Process.Start("Result.pdf")
End Sub
End Class
End Namespace
Insert page break at a specified location
Spire.Doc supports to insert page breaks in C# and VB.NET. We have already had a tutorial article of how to insert a page break after a paragraph. This article we will demonstrate how to add a page break at a specified location by searching a single word.
First, download Spire.Doc and install on your system. The Spire.Doc installation is clean, professional and wrapped up in a MSI installer. Then adds Spire.Doc.dll as reference in the downloaded Bin folder though the below path: "..\Spire.Doc\Bin\NET4.0\ Spire.Doc.dll".
Here comes to the steps of how to insert a page break at a specified location.
Step 1: Create a new word document and load the document from the file.
Document doc = new Document();
doc.LoadFromFile("Test.docx");
Step 2: Find the specified word "experience" where we want to insert the page break.
TextSelection[] selections = doc.FindAllString("experience", true, true);
Step 3: Traverse each word "experience".
foreach (TextSelection ts in selections)
{
TextRange range = ts.GetAsOneRange();
Paragraph paragraph = range.OwnerParagraph;
int index = paragraph.ChildObjects.IndexOf(range);
Break pageBreak = new Break(doc, BreakType.PageBreak);
}
Step 4: Create a new instance of page break and insert a page break after the word "experience".
Break pageBreak = new Break(doc, BreakType.PageBreak); paragraph.ChildObjects.Insert(index + 1, pageBreak);
Step 5: Save the word document to file and process it.
doc.SaveToFile("Break.docx", FileFormat.Docx);
System.Diagnostics.Process.Start("Break.docx");
Effective screenshot of the inserted page break after the word "experience":
Full codes:
using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Fields;
namespace InsertPageBreak
{
class Program
{
static void Main(string[] args)
{
Document doc = new Document();
doc.LoadFromFile("Test.docx");
TextSelection[] selections = doc.FindAllString("experience", true, true);
foreach (TextSelection ts in selections)
{
TextRange range = ts.GetAsOneRange();
Paragraph paragraph = range.OwnerParagraph;
int index = paragraph.ChildObjects.IndexOf(range);
Break pageBreak = new Break(doc, BreakType.PageBreak);
paragraph.ChildObjects.Insert(index + 1, pageBreak);
}
doc.SaveToFile("Break.docx", FileFormat.Docx);
System.Diagnostics.Process.Start("Break.docx");
}
}
}
Copy Formatting from One Cell or Range to Another in C#, VB.NET
When we are dealing with Excel spreadsheets, we may get a cell or a range of cells formatted, then we can use the Format Painter to quickly copy formatting from one place and apply it to another. It saves us much time to do so if we want to format a cell or cells with the previous cell formatting. In this article, I'll introduce you how to copy formatting from a range of cells to another using Spire.XLS.
As is shown in the screenshot of test file, contents in the first column have been formatted with different styles, what we want is to correspondingly apply cell formatting from column 1 to column 3.
Code snippets:
Step 1: Create a new instance of Wordbook class and load the test file from disk.
Workbook workbook = new Workbook();
workbook.LoadFromFile("Test.xlsx");
Step 2: Get the worksheet from workbook.
Worksheet sheet = workbook.Worksheets[0];
Step 3: Copy the cell formatting from column 1 and apply to cells of column 3.
int count = sheet.Rows.Count();
for (int i = 1; i < count + 1; i++)
{
sheet.Range[string.Format("C{0}", i)].Style = sheet.Range[string.Format("A{0}", i)].Style;
}
Step 4: Save to a new Excel file.
workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010);
Result:
Full Code:
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("Test.xlsx");
Worksheet sheet = workbook.Worksheets[0];
int count = sheet.Rows.Count();
for (int i = 1; i < count + 1; i++)
{
sheet.Range[string.Format("C{0}", i)].Style = sheet.Range[string.Format("A{0}", i)].Style;
}
workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010);
}
Private Shared Sub Main(args As String())
Dim workbook As New Workbook()
workbook.LoadFromFile("Test.xlsx")
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim count As Integer = sheet.Rows.Count()
For i As Integer = 1 To count
sheet.Range(String.Format("C{0}", i)).Style = sheet.Range(String.Format("A{0}", i)).Style
Next
workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010)
End Sub