.NET (1322)
Children categories
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");
}
}
}
Manually adjusting table columns can be time-consuming, especially if you have a large document with multiple tables. This is where the AutoFit tables feature in Word comes into play. It allows you to adjust the size of your table automatically, eliminating the need for manual adjustments. By setting AutoFit, the table will always adapt to display the content in the most suitable way. In this article, you will learn how to autofit tables in a Word document in C# using Spire.Doc for .NET.
- Set Tables to AutoFit to Contents in Word in C#
- Set Tables to AutoFit to Window in Word in C#
- Set Tables to Fixed Column Width in Word in C#
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 Tables to AutoFit to Contents in Word in C#
The AutoFit to Contents option in Word adjusts the size of table columns and rows according to the content within the cells. Once set, each column is automatically resized to ensure that all content is displayed completely without excessive empty space.
With Spire.Doc for .NET, you can use the Table.AutoFit(AutoFitBehaviorType.AutoFitToContents) method to autofit tables to content. The following are the detailed steps:
- Create a Document instance.
- Load a Word document using Document.LoadFromFile() method.
- Get a specified section in the document through Document.Sections[] property.
- Get a specified table in the section through Section.Tables[] property.
- AutoFit the table to contents using Table.AutoFit(AutoFitBehaviorType.AutoFitToContents) method.
- Save the result document using Document.SaveToFile() method.
- C#
using Spire.Doc;
namespace AutoFitToContents
{
class Program
{
static void Main(string[] args)
{
// Create a Document instance
Document doc = new Document();
// Load a Word document
doc.LoadFromFile("TableTemp.docx");
// Get the first section in the document
Section section = doc.Sections[0];
// Get the first table in the section
Table table = section.Tables[0] as Table;
// AutoFit the table to contents
table.AutoFit(AutoFitBehaviorType.AutoFitToContents);
// Save the result document
doc.SaveToFile("AutoFitToContents.docx", FileFormat.Docx);
}
}
}
Set Tables to AutoFit to Window in Word in C#
The AutoFit to Window option in Word enables the table to automatically adjust its width to fit the width of the Word window. Once set, the table will expand or contract to fill the entire page width (between the left and right margins).
To autofit tables to page, use the Table.AutoFit(AutoFitBehaviorType.AutoFitToWindow) method. The following are the detailed steps:
- Create a Document instance.
- Load a Word document using Document.LoadFromFile() method.
- Get a specified section in the document through Document.Sections[] property.
- Get a specified table in the section through Section.Tables[] property.
- AutoFit the table to Word window using Table.AutoFit(AutoFitBehaviorType.AutoFitToWindow) method.
- Save the result document using Document.SaveToFile() method.
- C#
using Spire.Doc;
namespace AutoFitToWindow
{
class Program
{
static void Main(string[] args)
{
// Create a Document instance
Document doc = new Document();
// Load a Word document
doc.LoadFromFile("TableTemp.docx");
// Get the first section in the document
Section section = doc.Sections[0];
// Get the first table in the section
Table table = section.Tables[0] as Table;
// AutoFit the table to page
table.AutoFit(AutoFitBehaviorType.AutoFitToWindow);
// Save the result document
doc.SaveToFile("AutoFitToWindow.docx", FileFormat.Docx);
}
}
}
Set Tables to Fixed Column Width in Word in C#
The Fixed Column Width option in Word allows you to maintain a specific, unchanging width for each column in the table. Once set, the column width of the table will remain fixed regardless of any changes to the content within the cells or the size of the document window.
The Table.AutoFit(AutoFitBehaviorType.FixedColumnWidths) method can be used to set fixed column width for Word tables. The following are the detailed steps to:
- Create a Document instance.
- Load a Word document using Document.LoadFromFile() method.
- Get a specified section in the document through Document.Sections[] property.
- Get a specified table in the section through Section.Tables[] property.
- Fix the column widths of the table using Table.AutoFit(AutoFitBehaviorType.FixedColumnWidths) method.
- Iterate through each row and then set the new column widths using Table.Rows[index].Cells[index].SetCellWidth() method.
- Save the result document using Document.SaveToFile() method.
- C#
using Spire.Doc;
namespace FixedColumnWidth
{
class Program
{
static void Main(string[] args)
{
// Create a Document instance
Document doc = new Document();
// Load a Word document
doc.LoadFromFile("TableTemp.docx");
// Get the first section in the document
Section section = doc.Sections[0];
// Get the first table in the section
Table table = section.Tables[0] as Table;
// Set to fixed column width
table.AutoFit(AutoFitBehaviorType.FixedColumnWidths);
// Iterate through each row in the table
for (int i = 0; i < table.Rows.Count; i++)
{
// Reset the width of the first column
table.Rows[i].Cells[0].SetCellWidth(120f, CellWidthType.Point);
// Reset the width of the second column
table.Rows[i].Cells[1].SetCellWidth(60f, CellWidthType.Point);
// Reset the width of the third column
table.Rows[i].Cells[2].SetCellWidth(40f, CellWidthType.Point);
// Reset the width of the fourth column
table.Rows[i].Cells[3].SetCellWidth(90f, CellWidthType.Point);
}
// Save the result document
doc.SaveToFile("FixedColumnWidth.docx", FileFormat.Docx);
}
}
}
Get a Free License
To fully experience the capabilities of Spire.Doc for .NET without any evaluation limitations, you can request a free 30-day trial license.
