Knowledgebase (2311)
Children categories
Export data is equally important with import data for excel users. Export data from Exel worksheet directly to datatable escapes many troubles and saves much time. This post will introduce you a method to export data from excel worksheet to datatable for WPF with C#, VB.NET.
Spire.XLS for WPF enables you to quickly export your data from Excel worksheet to datatable by following the below three steps. Spire.XLS for WPF supports to operate Excel 97-2003, Excel 2007 and Excel 2010. However, .NET Framework and Visual Studio must be installed for using Spire.XLS for WPF.
Make sure Spire.XLS and Visual Studio are correctly installed. And then follow steps.
Step 1: Create a new project
Create a new project by choosing WPF Application in Visual Studio.
Add a button and dataGrid in MainWindow. The default button name is "Button1". You can set Button1 Content property to be "Run" in its properties by right clicking it.
Step 2: Add reference and project namespaces
Add Spire.XLS. Wpf.dll as reference in Project. The Default location of Spire.Doc for WPF is "C:\Program Files\e-iceblue\Spire.XLS for WPF".
Double click the "Run" button, you can see the following method has been added automatically:
namespace dataexport
{
///
/// Interaction logic for MainWindow.xaml
///
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
private void button1_Click(object sender, RoutedEventArgs e)
{
}
}
}
Namespace dataexport
'''
''' Interaction logic for MainWindow.xaml
'''
Public Partial Class MainWindow
Inherits Window
Public Sub New()
InitializeComponent()
End Sub
Private Sub button1_Click(sender As Object, e As RoutedEventArgs)
End Sub
End Class
End Namespace
Add below namespaces at the top of the method
using Spire.Xls; using System.Data;
Imports Spire.Xls Imports System.Data
Step 3: Export data from excel worksheet to datatable
Create a new Excel workbook and load an excel file from system
Workbook workbook = new Workbook(); workbook.LoadFromFile(@"D:\michelle\e-iceblue\Spire.XLS\Demos\Data\dataexport.xls", ExcelVersion.Version97to2003); Worksheet sheet = workbook.Worksheets[0];
Dim workbook As New Workbook()
workbook.LoadFromFile("D:\michelle\e-iceblue\Spire.XLS\Demos\Data\dataexport.xls", ExcelVersion.Version97to2003)
Dim sheet As Worksheet = workbook.Worksheets(0)
Export data from excel worksheet to datatable:
DataTable dataTable = sheet.ExportDataTable(); DataView view = new DataView(dataTable); this.dataGrid1.ItemsSource = view; this.dataGrid1.AutoGenerateColumns = true;
Dim dataTable As DataTable = sheet.ExportDataTable() Dim view As New DataView(dataTable) Me.dataGrid1.ItemsSource = view Me.dataGrid1.AutoGenerateColumns = True
Press F5 and click "Run" in MainWindow, you can see the datatable as below picture.
Effective Screeshot:
PDF Table plays a significant role of clearly displaying data information in PDF document, which cannot be replaced by words. It provides great convenience for its users. For example, a product list can be more easily recognized and checked than numerous words. Thus, it is very necessary to learn how to generate table in PDF document.
In this article, I will not only introduce users how to generate table in PDF document, but also tell you how to set table style such as font, background color and data size by using Spire.PDF for WPF.
Spire.PDF for WPF enables you to quickly realize the task of drawing a PDF table by the below steps.
Step 1: Create a new project
- Create a new project in WPF Application
- Add a button in MainWindow, and set the button Content to be "Run"
Step 2: Add references and namespaces
- Add System.Drawing and Spire.Pdf.Wpf.dll as references
- Add below namespaces at the top of the method
using System.Drawing; using Spire.Pdf; using Spire.Pdf.Graphics; using Spire.Pdf.Graphics.Fonts; using Spire.Pdf.Tables;
Imports System.Drawing Imports Spire.Pdf Imports Spire.Pdf.Graphics Imports Spire.Pdf.Graphics.Fonts Imports Spire.Pdf.Tables
Step 3: Draw table in PDF document and set the table style
Create a new PDF document and set its margin
//create a new PDF document
PdfDocument doc = new PdfDocument();
PdfUnitConvertor unitCvtr = new PdfUnitConvertor();
PdfMargins margin = new PdfMargins();
margin.Top = unitCvtr.ConvertUnits(2.54f, PdfGraphicsUnit.Centimeter, PdfGraphicsUnit.Point);
margin.Bottom = margin.Top;
margin.Left = unitCvtr.ConvertUnits(3.17f, PdfGraphicsUnit.Centimeter, PdfGraphicsUnit.Point);
margin.Right = margin.Left;
PdfPageBase page = doc.Pages.Add(PdfPageSize.A4, margin);
float y = 20;
'create a new PDF document Dim doc As New PdfDocument() Dim unitCvtr As New PdfUnitConvertor() Dim margin As New PdfMargins() margin.Top = unitCvtr.ConvertUnits(2.54F, PdfGraphicsUnit.Centimeter, PdfGraphicsUnit.Point) margin.Bottom = margin.Top margin.Left = unitCvtr.ConvertUnits(3.17F, PdfGraphicsUnit.Centimeter, PdfGraphicsUnit.Point) margin.Right = margin.Left Dim page As PdfPageBase = doc.Pages.Add(PdfPageSize.A4, margin) Dim y As Single = 20
Set table title and then, add data information in PDF document
//add PDF title
PdfBrush brush1 = PdfBrushes.Black;
PdfTrueTypeFont font1 = new PdfTrueTypeFont(new Font("Verdana", 14f, System.Drawing.FontStyle.Bold));
PdfStringFormat format1 = new PdfStringFormat(PdfTextAlignment.Center);
page.Canvas.DrawString("Part Sales Information", font1, brush1, page.Canvas.ClientSize.Width / 2, y, format1);
y = y + font1.MeasureString("Part Sales Information", format1).Height;
y = y + 10;
//add data information
String[] data
= {
"PartNo;Description;OnHand;OnOrder;Cost;ListPrice",
"900;Dive kayak;24;16;1356.75;3999.95",
"912;Underwater Diver Vehicle;5;3;504;1680",
"1313;Regulator System;165;216;117.5;250",
"1314;Second Stage Regulator;98;88;124.1;365",
"1316;Regulator System;75;70;119.35;341",
"1320;Second Stage Regulator;37;35;73.53;171",
"1328;Regulator System;166;100;154.8;430",
"1330;Alternate Inflation Regulator;47;43;85.8;260",
"1364;Second Stage Regulator;128;135;99.9;270",
"1390;First Stage Regulator;146;140;64.6;170",
"1946;Second Stage Regulator;13;10;95.79;309",
"1986;Depth/Pressure Gauge Console;25;24;73.32;188",
"2314;Electronic Console;13;12;120.9;390",
"2341;Depth/Pressure Gauge;226;225;48.3;105",
"2343;Personal Dive Sonar;46;45;72.85;235",
"2350;Compass Console Mount;211;300;10.15;29"
};
String[][] dataSource
= new String[data.Length][];
for (int i = 0; i < data.Length; i++)
{
dataSource[i] = data[i].Split(';');
}
'add PDF title
Dim brush1 As PdfBrush = PdfBrushes.Black
Dim font1 As New PdfTrueTypeFont(New Font("Verdana", 14F, System.Drawing.FontStyle.Bold))
Dim format1 As New PdfStringFormat(PdfTextAlignment.Center)
page.Canvas.DrawString("Part Sales Information", font1, brush1, page.Canvas.ClientSize.Width / 2, y, format1)
y = y + font1.MeasureString("Part Sales Information", format1).Height
y = y + 10
'add data information
Dim data As [String]() = {"PartNo;Description;OnHand;OnOrder;Cost;ListPrice",
"900;Dive kayak;24;16;1356.75;3999.95",
"912;Underwater Diver Vehicle;5;3;504;1680",
"1313;Regulator System;165;216;117.5;250",
"1314;Second Stage Regulator;98;88;124.1;365",
"1316;Regulator System;75;70;119.35;341",
"1320;Second Stage Regulator;37;35;73.53;171",
"1328;Regulator System;166;100;154.8;430",
"1330;Alternate Inflation Regulator;47;43;85.8;260",
"1364;Second Stage Regulator;128;135;99.9;270",
"1390;First Stage Regulator;146;140;64.6;170",
"1946;Second Stage Regulator;13;10;95.79;309",
"1986;Depth/Pressure Gauge Console;25;24;73.32;188",
"2314;Electronic Console;13;12;120.9;390",
"2341;Depth/Pressure Gauge;226;225;48.3;105",
"2343;Personal Dive Sonar;46;45;72.85;235",
"2350;Compass Console Mount;211;300;10.15;29"}
Dim dataSource As [String]()() = New [String](data.Length - 1)() {}
For i As Integer = 0 To data.Length - 1
dataSource(i) = data(i).Split(";"C)
Next
Set PDF table style and data format
//Set table header
PdfTable table = new PdfTable();
table.Style.CellPadding = 3;
table.Style.HeaderSource = PdfHeaderSource.Rows;
table.Style.HeaderRowCount = 1;
table.DataSource = dataSource;
table.Style.ShowHeader = true;
table.Style.HeaderStyle.BackgroundBrush = PdfBrushes.LightSeaGreen;
table.Style.HeaderStyle.Font = new PdfTrueTypeFont(new Font("Verdana", 9f, System.Drawing.FontStyle.Bold));
table.Style.HeaderStyle.StringFormat = new PdfStringFormat(PdfTextAlignment.Center);
table.Style.HeaderStyle.TextBrush = PdfBrushes.White;
//Set table style and data format
table.Style.BorderPen = new PdfPen(PdfBrushes.LightBlue, 0.5f);
table.Style.DefaultStyle.BackgroundBrush = PdfBrushes.LightYellow;
table.Style.DefaultStyle.Font = new PdfTrueTypeFont(new Font("Verdana", 8.5f));
table.Style.AlternateStyle = new PdfCellStyle();
table.Style.AlternateStyle.BackgroundBrush = PdfBrushes.AliceBlue;
table.Style.AlternateStyle.Font = new PdfTrueTypeFont(new Font("Verdana", 8.5f));
float width
= page.Canvas.ClientSize.Width
- (table.Columns.Count + 1) * table.Style.BorderPen.Width;
table.Columns[0].Width = width * 0.1f * width;
table.Columns[0].StringFormat
= new PdfStringFormat(PdfTextAlignment.Center, PdfVerticalAlignment.Middle);
table.Columns[1].Width = width * 0.28f * width;
table.Columns[1].StringFormat
= new PdfStringFormat(PdfTextAlignment.Center, PdfVerticalAlignment.Middle);
table.Columns[2].Width = width * 0.1f * width;
table.Columns[2].StringFormat
= new PdfStringFormat(PdfTextAlignment.Center, PdfVerticalAlignment.Middle);
table.Columns[3].Width = width * 0.1f * width;
table.Columns[3].StringFormat
= new PdfStringFormat(PdfTextAlignment.Center, PdfVerticalAlignment.Middle);
table.Columns[4].Width = width * 0.12f * width;
table.Columns[4].StringFormat
= new PdfStringFormat(PdfTextAlignment.Center, PdfVerticalAlignment.Middle);
table.Columns[5].Width = width * 0.12f * width;
table.Columns[5].StringFormat
= new PdfStringFormat(PdfTextAlignment.Center, PdfVerticalAlignment.Middle);
PdfLayoutResult result = table.Draw(page, new PointF(0, y));
'Set table header
Dim table As New PdfTable()
table.Style.CellPadding = 3
table.Style.HeaderSource = PdfHeaderSource.Rows
table.Style.HeaderRowCount = 1
table.DataSource = dataSource
table.Style.ShowHeader = True
table.Style.HeaderStyle.BackgroundBrush = PdfBrushes.LightSeaGreen
table.Style.HeaderStyle.Font = New PdfTrueTypeFont(New Font("Verdana", 9F, System.Drawing.FontStyle.Bold))
table.Style.HeaderStyle.StringFormat = New PdfStringFormat(PdfTextAlignment.Center)
table.Style.HeaderStyle.TextBrush = PdfBrushes.White
'Set table style and data format
table.Style.BorderPen = New PdfPen(PdfBrushes.LightBlue, 0.5F)
table.Style.DefaultStyle.BackgroundBrush = PdfBrushes.LightYellow
table.Style.DefaultStyle.Font = New PdfTrueTypeFont(New Font("Verdana", 8.5F))
table.Style.AlternateStyle = New PdfCellStyle()
table.Style.AlternateStyle.BackgroundBrush = PdfBrushes.AliceBlue
table.Style.AlternateStyle.Font = New PdfTrueTypeFont(New Font("Verdana", 8.5F))
Dim width As Single = page.Canvas.ClientSize.Width - (table.Columns.Count + 1) * table.Style.BorderPen.Width
table.Columns(0).Width = width * 0.1F * width
table.Columns(0).StringFormat = New PdfStringFormat(PdfTextAlignment.Center, PdfVerticalAlignment.Middle)
table.Columns(1).Width = width * 0.28F * width
table.Columns(1).StringFormat = New PdfStringFormat(PdfTextAlignment.Center, PdfVerticalAlignment.Middle)
table.Columns(2).Width = width * 0.1F * width
table.Columns(2).StringFormat = New PdfStringFormat(PdfTextAlignment.Center, PdfVerticalAlignment.Middle)
table.Columns(3).Width = width * 0.1F * width
table.Columns(3).StringFormat = New PdfStringFormat(PdfTextAlignment.Center, PdfVerticalAlignment.Middle)
table.Columns(4).Width = width * 0.12F * width
table.Columns(4).StringFormat = New PdfStringFormat(PdfTextAlignment.Center, PdfVerticalAlignment.Middle)
table.Columns(5).Width = width * 0.12F * width
table.Columns(5).StringFormat = New PdfStringFormat(PdfTextAlignment.Center, PdfVerticalAlignment.Middle)
Dim result As PdfLayoutResult = table.Draw(page, New PointF(0, y))
Step 4: Save and Launch
// save and launch the file
doc.SaveToFile("SimpleTable.pdf");
doc.Close();
System.Diagnostics.Process.Start("SimpleTable.pdf");
' save and launch the file
doc.SaveToFile("SimpleTable.pdf")
doc.Close()
System.Diagnostics.Process.Start("SimpleTable.pdf")
Effective Screeshot:
Footnotes and endnotes are short notes that can be used to provide explanations, comments or references to certain words or sentences in a document. Footnotes usually appear at the bottom of the page containing their reference numbers, while endnotes appear at the end of the document or section. If you are writing an academic paper in Word, inserting footnotes or endnotes may be essential. This article will demonstrate how to insert footnotes and endnotes in Word documents 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 DLLs files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.Doc
Insert a Footnote in Word in C# and VB.NET
A footnote consists of two parts - a footnote reference mark and the corresponding footnote text. To insert a footnote for a specific text, you need to search for the text and get the paragraph where the text is located, after that add a footnote to the paragraph, then insert the footnote reference mark after the found text and set the footnote text. The detailed steps are as follows:
- Initialize an instance of the Document class.
- Load a Word document using Document.LoadFromFile() method.
- Search for a specific text in the document using Document.FindString() method and get the found text as a single text range using TextSelection.GetAsOneRange() method.
- Access the owner paragraph of the text range through TextRange.OwnerParagraph property and get the index of the text range in the paragraph using Paragraph.ChildObjects.IndexOf() method.
- Add a footnote to the paragraph using Paragraph.AppendFootnote(FootnoteType.Footnote) method.
- Insert the footnote reference mark after the text range using Paragraph.ChildObjects.Insert() method.
- Set the footnote text using Footnote.TextBody.AddParagraph().AppendText() method.
- Set formatting such as font name, font size and text color for the footnote text and reference mark.
- Save the result document using Document.SaveToFile() method.
- C#
- VB.NET
using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Fields;
using System.Drawing;
namespace InsertFootnote
{
internal class Program
{
static void Main(string[] args)
{
//Initialize an instance of the Document class
Document document = new Document();
//Load a Word document
document.LoadFromFile(@"Sample.docx");
//Find a specific text in the document
TextSelection selection = document.FindString("Spire.Doc for .NET", false, true);
//Get the found text as a single text range
TextRange textRange = selection.GetAsOneRange();
//Get the owner paragraph of the text range
Paragraph paragraph = textRange.OwnerParagraph;
//Get the index of the text range in the paragraph
int index = paragraph.ChildObjects.IndexOf(textRange);
//Add a footnote to the paragraph
Footnote footnote = paragraph.AppendFootnote(FootnoteType.Footnote);
//Insert the footnote reference mark after the text range
paragraph.ChildObjects.Insert(index + 1, footnote);
//Set the footnote text
textRange = footnote.TextBody.AddParagraph().AppendText("Developed by E-iceblue Co., LTD.");
//Set format for the footnote text
textRange.CharacterFormat.FontName = "Arial Black";
textRange.CharacterFormat.FontSize = 12;
textRange.CharacterFormat.TextColor = Color.DarkGray;
//Set format for the footnote reference mark
footnote.MarkerCharacterFormat.FontName = "Calibri";
footnote.MarkerCharacterFormat.FontSize = 12;
footnote.MarkerCharacterFormat.Bold = true;
footnote.MarkerCharacterFormat.TextColor = Color.DarkGreen;
//Save the result document
document.SaveToFile("InsertFootnote.docx", FileFormat.Docx2013);
document.Close();
}
}
}
Insert an Endnote in Word in C# and VB.NET
An endnote also consists of two parts - an endnote reference mark and the corresponding endnote text. The steps to insert an endnote for a specific text are very similar to that of the above example:
- Initialize an instance of the Document class.
- Load a Word document using Document.LoadFromFile() method.
- Search for a specific text in the document using Document.FindString() method and get the found text as a single text range using TextSelection.GetAsOneRange() method.
- Access the owner paragraph of the text range through TextRange.OwnerParagraph property and get the index of the text range in the paragraph using Paragraph.ChildObjects.IndexOf() method.
- Add an endnote to the paragraph using Paragraph.AppendFootnote(FootnoteType.Endnote) method.
- Insert the endnote reference mark after the text range using Paragraph.ChildObjects.Insert() method.
- Set the endnote text using Footnote.TextBody.AddParagraph().AppendText() method.
- Set formatting such as font name, font size and text color for the endnote text and reference mark.
- Save the result document using Document.SaveToFile() method.
- C#
- VB.NET
using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Fields;
using System.Drawing;
namespace InsertEndnote
{
internal class Program
{
static void Main(string[] args)
{
//Initialize an instance of the Document class
Document document = new Document();
//Load a Word document
document.LoadFromFile(@"Sample.docx");
//Find a specific text in the document
TextSelection selection = document.FindString("Microsoft Office", false, true);
//Get the found text as a single text range
TextRange textRange = selection.GetAsOneRange();
//Get the owner paragraph of the text range
Paragraph paragraph = textRange.OwnerParagraph;
//Get the index of the text range in the paragraph
int index = paragraph.ChildObjects.IndexOf(textRange);
//Add an endnote to the paragraph
Footnote endnote = paragraph.AppendFootnote(FootnoteType.Endnote);
//Insert the endnote reference mark after the text range
paragraph.ChildObjects.Insert(index + 1, endnote);
//Set the endnote text
textRange = endnote.TextBody.AddParagraph().AppendText("Developed by Microsoft.");
//Set format for the endnote text
textRange.CharacterFormat.FontName = "Arial Black";
textRange.CharacterFormat.FontSize = 12;
textRange.CharacterFormat.TextColor = Color.DarkGray;
//Set format for the endnote reference mark
endnote.MarkerCharacterFormat.FontName = "Calibri";
endnote.MarkerCharacterFormat.FontSize = 12;
endnote.MarkerCharacterFormat.Bold = true;
endnote.MarkerCharacterFormat.TextColor = Color.DarkGreen;
//Save the result document
document.SaveToFile("InsertEndnote.docx", FileFormat.Docx2013);
document.Close();
}
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
