.NET (1323)
Children categories
In order to format a table in PDF, Spire.PDF provides a PdfTableStyle class that represents parameters of PDF light table and a PdfCellStyle class that represents information about cell style. This article will introduce how to set the border color of a table including table border and cell border by using the two classes mentioned above.
Code Snippets:
Step 1: Define a multidimensional array of string.
string[][] dataSource =new string[5][] {new string[] {"Name", "Capital", "Continent", "Area", "Population"},
new string[] {"Argentina","Buenos Aires", "South American", "2777815", "3230003"},
new string[] {"Bolivia","La Paz","South America","1098575","7300000"},
new string[] {"Brazil","Brasilia","South America","8511196","150400000"},
new string[] {"Canada","Ottawa","North America","9976147","26500000"}
};
Step 2: Initialize a new instance of PdfDocument class and add a page to it.
PdfDocument pdf = new PdfDocument(); PdfPageBase page = pdf.Pages.Add();
Step 3: Initialize a new instance of PdfTbale class, filling the table with the data predefined.
PdfTable table = new PdfTable(); table.DataSource = dataSource;
Step 4: Initialize an instance of PdfTableStyle class, and set the color of table border as Blue, then apply the style to PdfTable.Style.
PdfTableStyle style = new PdfTableStyle(); style.BorderPen = new PdfPen(Color.Blue, 1f); table.Style = style;
Step 5: The syntax to set style of cell border is much different from setting table border style, since PdfTable class doesn't contain a property of CellStyle. Instead, CellStyle is a property included in BeginRowLayoutEventArgs class, which is an argument of StratRowLayout event. Therefore we customize a method as below to set the color of cell border.
public static void table_BeginRowLayout(object sender, BeginRowLayoutEventArgs args)
{
PdfCellStyle cellStyle = new PdfCellStyle();
cellStyle.BorderPen = new PdfPen(Color.Red, 0.5f);
args.CellStyle = cellStyle;
}
In the Main method, "table_BeginRowLayout" must be added to BeginRowLayout event to ensure the custom method will be invoked when the event occurs.
table.BeginRowLayout += new BeginRowLayoutEventHandler(table_BeginRowLayout);
Step 6: Draw the table on PDF page and save to file.
table.Draw(page, new PointF(0,40)); pdf.SaveToFile(@"SetBorderColor.pdf");
Output:
Full Code:
using Spire.Pdf;
using Spire.Pdf.Tables;
using Spire.Pdf.Graphics;
using System.Drawing;
namespace SetBorderColorOfTable
{
class Program
{
static void Main(string[] args)
{
//input data
string[][] dataSource =new string[5][] {new string[] {"Name", "Capital", "Continent", "Area", "Population"},
new string[] {"Argentina","Buenos Aires", "South American", "2777815", "3230003"},
new string[] {"Bolivia","La Paz","South America","1098575","7300000"},
new string[] {"Brazil","Brasilia","South America","8511196","150400000"},
new string[] {"Canada","Ottawa","North America","9976147","26500000"}
};
//initialize an instance of PdfDocument
PdfDocument pdf = new PdfDocument();
PdfPageBase page = pdf.Pages.Add();
//initialize an instance of PdfTable
PdfTable table = new PdfTable();
table.DataSource = dataSource;
//set the color of table border
PdfTableStyle style = new PdfTableStyle();
style.BorderPen = new PdfPen(Color.Blue, 1f);
table.Style = style;
//add custom method to BeginRowLayout event
table.BeginRowLayout += new BeginRowLayoutEventHandler(table_BeginRowLayout);
//draw table on PDF and save file
table.Draw(page, new PointF(0,40));
pdf.SaveToFile(@"SetBorderColor.pdf");
System.Diagnostics.Process.Start(@"SetBorderColor.pdf");
}
//customize a method to set color of cell border
public static void table_BeginRowLayout(object sender, BeginRowLayoutEventArgs args)
{
PdfCellStyle cellStyle = new PdfCellStyle();
cellStyle.BorderPen = new PdfPen(Color.Red, 0.5f);
args.CellStyle = cellStyle;
}
}
}
By using Acrobat, we can edit the PDF bookmark actions and set the zoom level to "Inherit Zoom". Then no matter which bookmark you click, the PDF page will stay the same size as the previous page you are viewing and it won't be changed. Spire.PDF also enables developers to set the Bookmark actions to inherit zoom by setting PdfDestination.Zoom as 0. This article will show you how to update bookmarks in a PDF document in C#.
Firstly, view the original screenshot of the PDF bookmark property:
Here comes to the step of how to use Spire.PDF to set the PDF bookmark actions.
Step 1: Create a new PDF document and load the document from file.
PdfDocument pdfdoc = new PdfDocument();
pdfdoc.LoadFromFile("TheGreatGatsby.pdf");
Step 2: Get bookmarks collections of the PDF file.
PdfBookmarkCollection bookmarks = pdfdoc.Bookmarks;
Step 3: Set Zoom level as 0, which the value is inherit zoom.
foreach (PdfBookmark bookMark in bookmarks)
{
//value 1 is the actual size, other value is the customized size.
bookMark.Destination.Zoom =0;
}
Step 4: Save the document to file.
pdfdoc.SaveToFile("result.pdf");
Effective screenshot after setting the zoom level to Inherit zoom.
Full codes:
using Spire.Pdf;
using Spire.Pdf.Bookmarks;
namespace SetInheritZoomProperty
{
class Program
{
static void Main(string[] args)
{
PdfDocument pdfdoc = new PdfDocument();
pdfdoc.LoadFromFile("TheGreatGatsby.pdf");
PdfBookmarkCollection bookmarks = pdfdoc.Bookmarks;
foreach (PdfBookmark bookMark in bookmarks)
{
bookMark.Destination.Zoom = 0;
}
pdfdoc.SaveToFile("result.pdf");
}
}
}
In some cases, we may need to add some textboxes to a chart in excel. While Spire.XLS provides us an easy solution to add textbox to an excel chart. This article will demonstrate how to add a textbox with borderline and a textbox without borderline to an excel chart in WPF using Spire.XLS in WPF.
Detail Steps and Code Snippets:
Use namespace:
using System.Windows; using Spire.Xls; using Spire.Xls.Core.Spreadsheet.Shapes;
Step 1: Create an excel workbook and get its first worksheet.
Workbook workbook = new Workbook(); Worksheet sheet = workbook.Worksheets[0];
Step 2: Add a blank chart to the first worksheet.
Chart chart = sheet.Charts.Add();
Step 3: Add textboxes to the chart.
Invoking ITextBoxes.AddTextBox(int row, int column, int height, int width) method to add a textbox with borderline to the chart, then add some text to the textbox.
XlsTextBoxShape textbox = chart.TextBoxes.AddTextBox(50, 100, 100, 300) as XlsTextBoxShape; textbox.Text = "Textbox with borderline";
However, Spire.XLS also enables us to add textbox without borderline to an excel chart by setting the line weight of the textbox to zero:
XlsTextBoxShape textbox1 = chart.TextBoxes.AddTextBox(300, 100, 100, 300) as XlsTextBoxShape; textbox1.Text = "Textbox without borderline"; textbox1.Line.Weight = 0;
Step 4: Save and launch the file.
workbook.SaveToFile("AddTextbox.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("AddTextbox.xlsx");
Effective screenshot:
Full codes:
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.Shapes;
using System.Windows;
namespace WpfApplication1
{
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
private void button2_Click(object sender, RoutedEventArgs e)
{
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
//Add Chart
Chart chart = sheet.Charts.Add();
//Add Textbox with Borderline
XlsTextBoxShape textbox = chart.TextBoxes.AddTextBox(50, 100, 100, 300) as XlsTextBoxShape;
textbox.Text = "Textbox with borderline";
//Add Textbox without Borderline
XlsTextBoxShape textbox1 = chart.TextBoxes.AddTextBox(300, 100, 100, 300) as XlsTextBoxShape;
textbox1.Text = "Textbox without borderline";
textbox1.Line.Weight = 0;
//Save and Launch the File
workbook.SaveToFile("AddTextbox.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("AddTextbox.xlsx");
}
}
}
C#/VB.NET: Convert Excel Data to Word Tables with Formatting
2022-09-16 05:55:00 Written by AdministratorIf you're creating a written report on a company's monthly expenditures, you might need to include a spreadsheet to show the financial figures and make them easier to read. This article demonstrates how to convert Excel data into a Word table maintaining the formatting in C# and VB.NET using Spire.Office for .NET.
Install Spire.Office for .NET
To begin with, you need to add the DLL files included in the Spire.Office 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.Office
Export Excel Data to a Word Table with Formatting
Below are the steps to convert Excel data to a Word table and keep the formatting using Spire.Office for .NET.
- Create a Workbook object and load a sample Excel file using Workbook.LoadFromFile() method.
- Get a specific worksheet through Workbook.Worksheets[index] property.
- Create a Document object, and add a section to it.
- Add a table using Section.AddTable() method.
- Detect the merged cells in the worksheet and merge the corresponding cells of the Word tale using the custom method MergeCells().
- Get value of a specific Excel cell through CellRange.Value property and add it to a cell of the Word table using TableCell.AddParagraph().AppendText() method.
- Copy the font style and cell style from Excel to the Word table using the custom method CopyStyle().
- Save the document to a Word file using Document.SaveToFile() method.
- C#
- VB.NET
using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Fields;
using Spire.Xls;
namespace ConvertExcelToWord
{
internal class Program
{
static void Main(string[] args)
{
//Load an Excel file
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Create a Word document
Document doc = new Document();
Section section = doc.AddSection();
section.PageSetup.Orientation = PageOrientation.Landscape;
//Add a table
Table table = section.AddTable(true);
table.ResetCells(sheet.LastRow, sheet.LastColumn);
//Merge cells
MergeCells(sheet, table);
for (int r = 1; r <= sheet.LastRow; r++)
{
//Set row Height
table.Rows[r - 1].Height = (float)sheet.Rows[r - 1].RowHeight;
for (int c = 1; c <= sheet.LastColumn; c++)
{
CellRange xCell = sheet.Range[r, c];
TableCell wCell = table.Rows[r - 1].Cells[c - 1];
//Export data from Excel to Word table
TextRange textRange = wCell.AddParagraph().AppendText(xCell.NumberText);
//Copy font and cell style from Excel to Word
CopyStyle(textRange, xCell, wCell);
}
}
//Save the document to a Word file
doc.SaveToFile("ExportToWord.docx", Spire.Doc.FileFormat.Docx);
}
//Merge cells if any
private static void MergeCells(Worksheet sheet, Table table)
{
if (sheet.HasMergedCells)
{
//Get merged cell ranges from Excel
CellRange[] ranges = sheet.MergedCells;
//Merge corresponding cells in Word table
for (int i = 0; i < ranges.Length; i++)
{
int startRow = ranges[i].Row;
int startColumn = ranges[i].Column;
int rowCount = ranges[i].RowCount;
int columnCount = ranges[i].ColumnCount;
if (rowCount > 1 && columnCount > 1)
{
for (int j = startRow; j <= startRow + rowCount; j++)
{
table.ApplyHorizontalMerge(j - 1, startColumn - 1, startColumn - 1 + columnCount - 1);
}
table.ApplyVerticalMerge(startColumn - 1, startRow - 1, startRow - 1 + rowCount - 1);
}
if (rowCount > 1 && columnCount == 1)
{
table.ApplyVerticalMerge(startColumn - 1, startRow - 1, startRow - 1 + rowCount - 1);
}
if (columnCount > 1 && rowCount == 1)
{
table.ApplyHorizontalMerge(startRow - 1, startColumn - 1, startColumn - 1 + columnCount - 1);
}
}
}
}
//Copy cell style of Excel to Word table
private static void CopyStyle(TextRange wTextRange, CellRange xCell, TableCell wCell)
{
//Copy font style
wTextRange.CharacterFormat.TextColor = xCell.Style.Font.Color;
wTextRange.CharacterFormat.FontSize = (float)xCell.Style.Font.Size;
wTextRange.CharacterFormat.FontName = xCell.Style.Font.FontName;
wTextRange.CharacterFormat.Bold = xCell.Style.Font.IsBold;
wTextRange.CharacterFormat.Italic = xCell.Style.Font.IsItalic;
//Copy backcolor
wCell.CellFormat.Shading.BackgroundPatternColor = xCell.Style.Color;
//Copy horizontal alignment
switch (xCell.HorizontalAlignment)
{
case HorizontalAlignType.Left:
wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Left;
break;
case HorizontalAlignType.Center:
wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Center;
break;
case HorizontalAlignType.Right:
wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Right;
break;
}
//Copy vertical alignment
switch (xCell.VerticalAlignment)
{
case VerticalAlignType.Bottom:
wCell.CellFormat.VerticalAlignment = VerticalAlignment.Bottom;
break;
case VerticalAlignType.Center:
wCell.CellFormat.VerticalAlignment = VerticalAlignment.Middle;
break;
case VerticalAlignType.Top:
wCell.CellFormat.VerticalAlignment = VerticalAlignment.Top;
break;
}
}
}
}
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.
Usually there are three kinds of alignment style for a word table: left aligned, centered and right aligned. On Microsoft word, we can go to table properties to set the alignment for the whole table. Spire.Doc also offers a property table.TableFormat.HorizontalAlignment to enable developers to set the table alignment style easily in C#. This article will demonstrate how to align a table in C#.
Firstly, view the how to align a table for Microsoft word:
Here come to the code snippet of how Spire.Doc align a table.
Step 1: Create a word document and load from file.
Document doc = new Document();
doc.LoadFromFile("sample.docx");
Step 2: Get the first section and two tables from the word document.
Section section = doc.Sections[0]; Table table = section.Tables[0] as Table; Table table1 = section.Tables[1] as Table;
Step 3: Set the different alignment properties for each table.
table.Format.HorizontalAlignment = RowAlignment.Right; table.Format.LeftIndent = 34; table1.Format.HorizontalAlignment = RowAlignment.Left; table1.Format.LeftIndent = 34;
Step 4: Save the document to file:
doc.SaveToFile("result.docx", FileFormat.Docx);
Effective screenshots after align the table format:
Full codes:
using Spire.Doc;
using Spire.Doc.Documents;
namespace AlignTable
{
class Program
{
static void Main(string[] args)
{
Document doc = new Document();
doc.LoadFromFile("sample.docx");
Section section = doc.Sections[0];
Table table = section.Tables[0] as Table;
Table table1 = section.Tables[1] as Table;
table.Format.HorizontalAlignment = RowAlignment.Right;
table.Format.LeftIndent = 34;
table1.Format.HorizontalAlignment = RowAlignment.Left;
table1.Format.LeftIndent = 34;
doc.SaveToFile("result.docx", FileFormat.Docx);
}
}
}
Adding layers can help us to make the information that we don't want others to view become invisible in a pdf file. Spire.PDF, as a powerful and independent pdf library, enables us to add layers to pdf files without having Adobe Acrobat been installed on system.
This article will introduce how to add different types of layers to a pdf file in WPF using Spire.PDF for WPF.
Below is the effective screenshot after adding layers:
Detail Steps and Code Snippets:
Use namespace:
using System.Drawing; using System.Windows; using Spire.Pdf; using Spire.Pdf.Graphics;
Step 1: Create a new PDF file and add a new page to it.
PdfDocument doc = new PdfDocument(); PdfPageBase page = doc.Pages.Add();
Step 2: Add image layer.
Add a layer named Image Layer to the page, call DrawImage(PdfImage image, float x, float y, float width, float height) method to add an image to the layer.
PdfPageLayer layer = page.PageLayers.Add("Image Layer");
layer.Graphics.DrawImage(PdfImage.FromFile("image.jpg"), 0, 100, 300, 30);
Step 3: Add line layer.
Add a layer named Line Layer to the page, call DrawLine(PdfPen pen, PointF point1, PointF point2) method to draw a red line to the layer.
layer = page.PageLayers.Add("Line Layer");
layer.Graphics.DrawLine(new PdfPen(PdfBrushes.Red, 1), new PointF(0, 200), new PointF(300, 200));
Step 4: Add string layer.
Add a layer named String Layer to the page, call DrawString(string s, PdfFontBase font, PdfPen pen, float x, float y) method to draw some string to the layer.
layer = page.PageLayers.Add("String Layer");
layer.Graphics.DrawString("Add layer to pdf using Spire.PDF", new PdfFont(PdfFontFamily.Courier, 12), new PdfPen(PdfBrushes.Navy,1),0,300);
Step 5: Save and launch the file.
doc.SaveToFile("AddLayers.pdf", FileFormat.PDF);
System.Diagnostics.Process.Start("AddLayers.pdf");
Full codes:
private void button1_Click(object sender, RoutedEventArgs e)
{
PdfDocument doc = new PdfDocument();
PdfPageBase page = doc.Pages.Add();
PdfPageLayer layer = page.PageLayers.Add("Image Layer");
layer.Graphics.DrawImage(PdfImage.FromFile("image.jpg"), 0, 100, 300, 30);
layer = page.PageLayers.Add("Line Layer");
layer.Graphics.DrawLine(new PdfPen(PdfBrushes.Red, 1), new PointF(0, 200), new PointF(300, 200));
layer = page.PageLayers.Add("String Layer");
layer.Graphics.DrawString("Add layer to pdf using Spire.PDF", new PdfFont(PdfFontFamily.Courier, 12), new PdfPen(PdfBrushes.Navy,1),0,300);
doc.SaveToFile("AddLayers.pdf", FileFormat.PDF);
System.Diagnostics.Process.Start("AddLayers.pdf");
}
Change the color or remove underline from hyperlink in Word with C#
2016-05-12 08:14:23 Written by KoohjiBy default, hyperlink in Word shows up as blue and underlined. In some cases, users may want to modify the hyperlink style so as to get better looking with the whole document. This article is going to introduce how we can remove the underline or change the color of hyperlinks using Spire.Doc in C#.
Code Snippets:
Step 1: Create a new object of Document class, add a section to it.
Document document = new Document(); Section section = document.AddSection();
Step 2: Add a paragraph and append a hyperlink to the paragraph. In order to format the hyperlink, we return the value of hyperlink in a TextRange.
Paragraph para= section.AddParagraph();
TextRange txtRange = para1.AppendHyperlink("www.e-iceblue.com", "www.e-iceblue.com", HyperlinkType.WebLink);
Step 3: Format the hyperlink with the specified the font name, font size, color and underline style.
txtRange.CharacterFormat.FontName = "Times New Roman"; txtRange.CharacterFormat.FontSize = 12; txtRange.CharacterFormat.TextColor = System.Drawing.Color.Red; txtRange.CharacterFormat.UnderlineStyle = UnderlineStyle.None;
Step 4: Save the file.
document.SaveToFile("result.docx", FileFormat.Docx2013);
Output:
Full Code:
using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Fields;
namespace FormatHyperlink
{
class Program
{
static void Main(string[] args)
{
Document document = new Document();
Section section = document.AddSection();
Paragraph para1= section.AddParagraph();
para1.AppendText("Regular Link: ");
TextRange txtRange1 = para1.AppendHyperlink("www.e-iceblue.com", "www.e-iceblue.com", HyperlinkType.WebLink);
txtRange1.CharacterFormat.FontName = "Times New Roman";
txtRange1.CharacterFormat.FontSize = 12;
Paragraph blankPara1 = section.AddParagraph();
Paragraph para2 = section.AddParagraph();
para2.AppendText("Change Color: ");
TextRange txtRange2 = para2.AppendHyperlink("www.e-iceblue.com", "www.e-iceblue.com", HyperlinkType.WebLink);
txtRange2.CharacterFormat.FontName = "Times New Roman";
txtRange2.CharacterFormat.FontSize = 12;
txtRange2.CharacterFormat.TextColor = System.Drawing.Color.Red;
Paragraph blankPara2 = section.AddParagraph();
Paragraph para3 = section.AddParagraph();
para3.AppendText("Remove Underline: ");
TextRange txtRange3 = para3.AppendHyperlink("www.e-iceblue.com", "www.e-iceblue.com", HyperlinkType.WebLink);
txtRange3.CharacterFormat.FontName = "Times New Roman";
txtRange3.CharacterFormat.FontSize = 12;
txtRange3.CharacterFormat.UnderlineStyle = UnderlineStyle.None;
document.SaveToFile("result.docx", FileFormat.Docx2013);
System.Diagnostics.Process.Start("result.docx");
}
}
}
How to create a formula to apply conditional formatting in Excel in C#
2016-05-11 07:40:52 Written by KoohjiWe always use conditional formatting to highlight the cells with certain color from the whole data in the Excel worksheet. Spire.XLS also supports to create a formula to apply conditional formatting in Excel in C#. This article will show you how to apply a conditional formatting rule.
View the steps of Microsoft Excel set the conditional formatting.
Step 1: Choose the column B and then click "New Rule" under "Conditional Formatting"
Step 2: Select the rule type, enter the rule by adding the formula and then add the highlight color for the format.
Here comes to the steps of how to set the conditional formatting rule by Spire.XLS in C#.
Step 1: Create a new excel workbook and load the document from file.
Workbook wb = new Workbook();
wb.LoadFromFile("Test.xlsx");
Step 2: Get the first worksheet and the second column from the workbook.
Worksheet sheet = wb.Worksheets[0]; CellRange range = sheet.Columns[1];
Step 3: Set the conditional formatting formula and apply the rule to the chosen cell range.
XlsConditionalFormats xcfs = sheet.ConditionalFormats.Add(); xcfs.AddRange(range); IConditionalFormat conditional = xcfs.AddCondition(); conditional.FormatType = ConditionalFormatType.Formula; conditional.FirstFormula = "=($B1<$C1)"; conditional.BackKnownColor = ExcelColors.Yellow;
Step 4: Save the document to file.
wb.SaveToFile("result.xlsx", ExcelVersion.Version2010);
Effective screenshot:
Full codes:
using Spire.Xls;
namespace CreateFormula
{
class Program
{
static void Main(string[] args)
{
Workbook wb = new Workbook();
wb.LoadFromFile("Test.xlsx");
Worksheet sheet = wb.Worksheets[0];
CellRange range = sheet.Columns[1];
XlsConditionalFormats xcfs = sheet.ConditionalFormats.Add();
xcfs.AddRange(range);
IConditionalFormat conditional = xcfs.AddCondition();
conditional.FormatType = ConditionalFormatType.Formula;
conditional.FirstFormula = "=($B1<$C1)";
conditional.BackKnownColor = ExcelColors.Yellow;
wb.SaveToFile("result.xlsx", ExcelVersion.Version2010);
}
}
}
Imports Spire.Xls
Namespace CreateFormula
Class Program
Private Shared Sub Main(args As String())
Dim wb As New Workbook()
wb.LoadFromFile("Test.xlsx")
Dim sheet As Worksheet = wb.Worksheets(0)
Dim range As CellRange = sheet.Columns(1)
Dim xcfs As XlsConditionalFormats = sheet.ConditionalFormats.Add()
xcfs.AddRange(range)
Dim conditional As IConditionalFormat = xcfs.AddCondition()
conditional.FormatType = ConditionalFormatType.Formula
conditional.FirstFormula = "=($B1<$C1)"
conditional.BackKnownColor = ExcelColors.Yellow
wb.SaveToFile("result.xlsx", ExcelVersion.Version2010)
End Sub
End Class
End Namespace
Spire.Xls enables developers to quickly find specific data, highlight the data as well as replace them with new data in excel files. We've already introduced how to find and highlight excel data, so this article is aimed to demonstrate how to replace selected data in excel on WPF applications using Spire.Xls for WPF.
Detail steps and code snippets:
Step 1: Create a WPF Application, add two buttons, three text boxes, two text blocks into the Main Window and align them like below.
Step 2: Double click the Browse button, add following codes to initialize a new OpenFileDialog object and set its properties to select excel file, and save its file name to the first text box.
OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.InitialDirectory = @"E:\";
openFileDialog1.Title = "Select Excel Files";
openFileDialog1.DefaultExt = "xlsx";
openFileDialog1.Filter = "Excel files (*.xls;*.xlsx)|*.xls;*.xlsx|All files (*.*)|*.*";
openFileDialog1.FilterIndex = 1;
openFileDialog1.CheckFileExists = true;
openFileDialog1.CheckPathExists = true;
openFileDialog1.RestoreDirectory = true;
openFileDialog1.ReadOnlyChecked = true;
openFileDialog1.ShowReadOnly = true;
if (openFileDialog1.ShowDialog().Value)
{
textBox1.Text = openFileDialog1.FileName;
}
Step 3: Double click the Replace All button, add following codes to load the excel file, replace all of the text entered in the find box with new text entered in the replace box, then save the changes and launch the file.
//Load the sample excel file
Workbook workbook = new Workbook();
workbook.LoadFromFile(textBox1.Text);
//Get the first worksheet of the excel file
Worksheet sheet = workbook.Worksheets[0];
//Call Worksheet.FindAllString(string stringValue, bool formula, bool formulaValue) method to find all of the specific text from the first worksheet and save the results to a CellRange array
CellRange[] ranges = sheet.FindAllString(this.FindBox.Text, false, false);
//Loop through the array, replace the selected text with new text
foreach (CellRange range in ranges)
{
range.Text = this.ReplaceBox.Text;
}
//Save the changes and launch the file
workbook.SaveToFile("Replaced.xlsx");
System.Diagnostics.Process.Start("Replaced.xlsx");
Result:
Run the project, you will get the following dialog box, Click Browse to choose excel file, then input the text that you want to find and the text used to replace, next click the Replace All button.
Effective screenshot of the result excel file:
Full codes:
using System.Windows;
using System.Windows.Controls;
using Microsoft.Win32;
using Spire.Xls;
namespace Replace_Selected_Excel_Data_in_WPF
{
///
/// Interaction logic for MainWindow.xaml
///
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
private void BrowseBtn_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.InitialDirectory = @"E:\";
openFileDialog1.Title = "Select Excel Files";
openFileDialog1.DefaultExt = "xlsx";
openFileDialog1.Filter = "Excel files (*.xls;*.xlsx)|*.xls;*.xlsx|All files (*.*)|*.*";
openFileDialog1.FilterIndex = 1;
openFileDialog1.CheckFileExists = true;
openFileDialog1.CheckPathExists = true;
openFileDialog1.RestoreDirectory = true;
openFileDialog1.ReadOnlyChecked = true;
openFileDialog1.ShowReadOnly = true;
if (openFileDialog1.ShowDialog().Value)
{
textBox1.Text = openFileDialog1.FileName;
}
}
private void ReplaceBtn_Click(object sender, RoutedEventArgs e)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile(textBox1.Text);
Worksheet sheet = workbook.Worksheets[0];
CellRange[] ranges = sheet.FindAllString(this.FindBox.Text, false, false);
foreach (CellRange range in ranges)
{
range.Text = this.ReplaceBox.Text;
}
workbook.SaveToFile("Replaced.xlsx");
System.Diagnostics.Process.Start("Replaced.xlsx");
}
}
}
Excel version 2013 added a fantastic feature in Chart Data Label option that you can custom data labels from a column/row of data. The chart below uses labels from the data in cells C2: C5 next to the plotted values. This article will present how to add labels to data points using the values from cells in C#.
Code Snippets:
Step 1: Initialize a new instance of Workbook class and set the Excel version as 2013.
Workbook wb = new Workbook(); wb.Version = ExcelVersion.Version2013;
Step 2: Get the first sheet from workbook.
Worksheet ws = wb.Worksheets[0];
Step 3: Insert data.
ws.Range["A2"].Text = "Product 1"; ws.Range["A3"].Text = "Product 2"; ws.Range["A4"].Text = "Product 3"; ws.Range["A5"].Text = "Product 4"; ws.Range["B1"].Text = "Sales"; ws.Range["B1"].Style.Font.IsBold = true; ws.Range["B2"].NumberValue = 251; ws.Range["B3"].NumberValue = 515; ws.Range["B4"].NumberValue = 454; ws.Range["B5"].NumberValue = 874; ws.Range["C1"].Text = "+/-\nPrevious\nPeriod"; ws.Range["C1"].Style.Font.IsBold = true; ws.Range["C2"].NumberValue = -120; ws.Range["C3"].NumberValue = 31; ws.Range["C4"].NumberValue = -76; ws.Range["C5"].NumberValue = 201; ws.SetRowHeight(1, 40);
Step 4: Insert a Clustered Column Chart in Excel based on the data range from A1:B5.
Chart chart = ws.Charts.Add(ExcelChartType.ColumnClustered); chart.DataRange = ws.Range["A1:B5"]; chart.SeriesDataFromRange = false; chart.PrimaryValueAxis.HasMajorGridLines = false;
Step 5: Set chart position.
chart.LeftColumn = 5; chart.TopRow = 2; chart.RightColumn = 13; chart.BottomRow = 22;
Step 6: Add labels to data points using the values from cell range C2:C5.
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.ValueFromCell = ws.Range["C2:C5"];
Step 7: Save and launch the file.
wb.SaveToFile("result.xlsx",ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");
Full Code:
using Spire.Xls;
namespace CustomLabels
{
class Program
{
static void Main(string[] args)
{
Workbook wb = new Workbook();
wb.Version = ExcelVersion.Version2013;
Worksheet ws = wb.Worksheets[0];
ws.Range["A2"].Text = "Product 1";
ws.Range["A3"].Text = "Product 2";
ws.Range["A4"].Text = "Product 3";
ws.Range["A5"].Text = "Product 4";
ws.Range["B1"].Text = "Sales";
ws.Range["B1"].Style.Font.IsBold = true;
ws.Range["B2"].NumberValue = 251;
ws.Range["B3"].NumberValue = 515;
ws.Range["B4"].NumberValue = 454;
ws.Range["B5"].NumberValue = 874;
ws.Range["C1"].Text = "+/-\nPrevious\nPeriod";
ws.Range["C1"].Style.Font.IsBold = true;
ws.Range["C2"].NumberValue = -120;
ws.Range["C3"].NumberValue = 31;
ws.Range["C4"].NumberValue = -76;
ws.Range["C5"].NumberValue = 201;
ws.SetRowHeight(1, 40);
Chart chart = ws.Charts.Add(ExcelChartType.ColumnClustered);
chart.DataRange = ws.Range["A1:B5"];
chart.SeriesDataFromRange = false;
chart.PrimaryValueAxis.HasMajorGridLines = false;
chart.LeftColumn = 5;
chart.TopRow = 2;
chart.RightColumn = 13;
chart.BottomRow = 22;
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.ValueFromCell = ws.Range["C2:C5"];
wb.SaveToFile("result.xlsx",ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");
}
}
}
