Update excel data via GridViewTable
Spire.XLS for .NET is a professional Excel component which enables developers/programmers to fast generate, read, write and modify Excel document for .NET. Spire.XLS for .NET doesn't need Microsoft Office Excel Automation. It allows user to operate Excel document directly such as save to stream, save as web response, copy, lock/unlock worksheet, set up workbook properties, etc. As a professional .NET Excel component, it also includes many useful features, for example, functionalities of importing data from Excel to dataTable and exporting dataTable to Excel from Database.
In this article introduces a method of updating excel data by dataTable via using sheet.ExportDataTable() method and sheet.InsertDataTable() method to import data from excel to dataTable and export dataTable to excel from Database.
The main steps of method are:
Step 1: Load the excel document and use sheet.ExportDataTable() method extract data to dataTable and show by dataGridView control.
private void Form1_Load(object sender, EventArgs e) { //load excel document to workbook workbook.LoadFromFile(@"DatatableSample.xls"); Worksheet sheet = workbook.Worksheets[0]; sheet.Name = "Original table"; //extract data to dataTable from sheet DataTable dataTable = sheet.ExportDataTable(); //show the data to dataGridView this.dataGridView.DataSource = dataTable; }
The effect screenshot:
Step 2: Create a new sheet to save the updata data and use sheet.InsertDataTable() method to insert dataTable to the sheet.
//create a new sheet to save Updata data. Worksheet sheet = workbook.CreateEmptySheet("Updata Table"); //extract data from dataGridView DataTable dataTable = this.dataGridView.DataSource as DataTable; // insert dataTable to sheet sheet.InsertDataTable(dataTable, true, 1, 1);
Step 3: Save the result excel document.
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2007);
The effect screenshot:
Download and install Spire.XLS for .NET and use below code to experience this method to update excel data by dataTable.
The full code:
using System; using System.Data; using System.Windows.Forms; using Spire.Xls; namespace UpdataExcelDataByDataTable { public partial class UpdataExcelData : Form { private Workbook workbook = new Workbook(); private void Form1_Load(object sender, EventArgs e) { workbook.LoadFromFile(@"DatatableSample.xls"); Worksheet sheet = workbook.Worksheets[0]; sheet.Name = "Original table"; DataTable dataTable = sheet.ExportDataTable(); this.dataGridView.DataSource = dataTable; } private void Updata_Click(object sender, EventArgs e) { Worksheet sheet = workbook.CreateEmptySheet("Updata Table"); DataTable dataTable = this.dataGridView.DataSource as DataTable; sheet.InsertDataTable(dataTable, true, 1, 1); workbook.SaveToFile("result.xlsx", ExcelVersion.Version2007); System.Diagnostics.Process.Start("result.xlsx"); } } }
Imports System.Data Imports System.Windows.Forms Imports Spire.Xls Public Class Form1 Private workbook As New Workbook() Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load 'load excel document to workbook workbook.LoadFromFile("DatatableSample.xls") Dim sheet As Worksheet = workbook.Worksheets(0) sheet.Name = "Original table" 'extract data to dataTable from sheet Dim dataTable As DataTable = sheet.ExportDataTable() 'show the data to dataGridView Me.DataGridView.DataSource = dataTable End Sub
If you couldn't successfully use the Spire.Xls, please refer Spire.XLS Quick Start which can guide you quickly use the Spire.Xls.
Insert an Image to PDF Grid Cell in C#
This sample demo has demonstrated how to draw nested grid in PDF document and set grid row&cell format. In the following section, we are going to create a simple PDF grid and show you how to insert an image to a specific PDF grid cell in C#. Before we can follow the code snippet below to accomplish the task, we have to prepare the environment first.
Download Spire.PDF and install it on system, create or open a .NET class application in Visual Studio 2005 or above versions, add Spire.PDF.dll to your .NET project assemblies.Then let's code step by step to make a better understanding about the whole procedure.
Step 1: Create a PDF document and add a new page.
PdfDocument doc = new PdfDocument(); PdfPageBase page = doc.Pages.Add();
Step 2: Create a 2×2 grid to PDF.
PdfGrid grid = new PdfGrid(); PdfGridRow row = grid.Rows.Add(); row = grid.Rows.Add(); grid.Columns.Add(2);
Step 3: Set the cell padding of the PDF grid.
grid.Style.CellPadding = new PdfPaddings(1, 1, 1, 1);
Step 4: Set the width of the columns.
float width = page.Canvas.ClientSize.Width - (grid.Columns.Count + 1); grid.Columns[0].Width = width * 0.25f; grid.Columns[1].Width = width * 0.25f;
Step 5: Load an image from disk.
PdfGridCellContentList lst = new PdfGridCellContentList(); PdfGridCellContent textAndStyle = new PdfGridCellContent(); textAndStyle.Image = PdfImage.FromFile("..\\..\\image1.jpg");
Step 6: Set the size of image and insert it to the first cell.
textAndStyle.ImageSize = new SizeF(50, 50); lst.List.Add(textAndStyle); grid.Rows[0].Cells[0].Value = lst; grid.Rows[1].Height = grid.Rows[0].Height;
Step 7: Draw PDF grid into page at the specific location.
PdfLayoutResult result = grid.Draw(page, new PointF(10, 30));
Step 8: Save to a PDF file and launch the file.
doc.SaveToFile(outputFile, FileFormat.PDF); System.Diagnostics.Process.Start(outputFile);
Result:
Full C# Code:
using Spire.Pdf; using Spire.Pdf.Graphics; using Spire.Pdf.Grid; using System.Drawing; namespace InsertImage { class Program { static void Main(string[] args) { string outputFile = @"..\..\output.pdf"; //Create a pdf document PdfDocument doc = new PdfDocument(); //Add a page for the pdf document PdfPageBase page = doc.Pages.Add(); //Create a pdf grid PdfGrid grid = new PdfGrid(); //Set the cell padding of pdf grid grid.Style.CellPadding = new PdfPaddings(1, 1, 1, 1); //Add a row for pdf grid PdfGridRow row = grid.Rows.Add(); //Add two columns for pdf grid grid.Columns.Add(2); float width = page.Canvas.ClientSize.Width - (grid.Columns.Count + 1); //Set the width of the first column grid.Columns[0].Width = width * 0.25f; grid.Columns[1].Width = width * 0.25f; //Add a image PdfGridCellContentList lst = new PdfGridCellContentList(); PdfGridCellContent textAndStyle = new PdfGridCellContent(); textAndStyle.Image = PdfImage.FromFile("..\\..\\image1.jpg"); //Set the size of image textAndStyle.ImageSize = new SizeF(50, 50); lst.List.Add(textAndStyle); //Add a image into the first cell. row.Cells[0].Value = lst; //Draw pdf grid into page at the specific location PdfLayoutResult result = grid.Draw(page, new PointF(10, 30)); //Save to a pdf file doc.SaveToFile(outputFile, FileFormat.PDF); System.Diagnostics.Process.Start(outputFile); } } }
C#: Get the Number of Pages in a PDF File
Counting the number of pages in a PDF file is essential for various purposes, such as determining document length, organizing content, and evaluating printing requirements. Apart from knowing page count information using PDF viewers, you can also automate the task through programming. In this article, you will learn how to use C# to get the number of pages in a PDF file using Spire.PDF for .NET.
Install Spire.PDF for .NET
To begin with, you need to add the DLL files included in the Spire.PDF 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.PDF
Get the Number of Pages in a PDF File in C#
Spire.PDF for .NET offers the PdfDocument.Pages.Count property to quickly count the number of pages in a PDF file without opening it. The following are the detailed steps.
- Create a PdfDocument object.
- Load a sample PDF file using PdfDocument.LoadFromFile() method.
- Count the number of pages in the PDF file using PdfDocument.Pages.Count property.
- Output the result and close the PDF.
- C#
using Spire.Pdf; namespace GetNumberOfPages { class Program { static void Main(string[] args) { //Create a PdfDocument object PdfDocument pdf = new PdfDocument(); //Load a sample PDF file pdf.LoadFromFile("Contract.pdf"); //Count the number of pages in the PDF int PageNumber = pdf.Pages.Count; Console.WriteLine("The PDF file has {0} pages", PageNumber); //Close the PDF pdf.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.
Remove Table from PowerPoint document
Spire.Presentation is a powerful and easy-to-use .NET component, especially designed for developers. Using Spire.Presentation you can generate, modify, convert, render, and print documents without installing Microsoft PowerPoint on your machine. There is a document in our website introducing you how to insert table. And in this document, I will introduce you how to remove tables within a PPT document.
Step 1: Create Presentation instance and load file.
Presentation presentation = new Presentation(); presentation.LoadFromFile("sample.ppt");
Step 2: Get the tables within the PPT document.
List shape_tems = new List(); foreach (IShape shape in presentation.Slides[0].Shapes) { if (shape is ITable) { //add new table to table list shape_tems.Add(shape); } }
Step 3: Remove all tables.
foreach (IShape shape in shape_tems) { presentation.Slides[0].Shapes.Remove(shape); }
Step 4: Save the document.
presentation.SaveToFile("result.pptx", FileFormat.Pptx2010);
Download and install Spire.Presentation for .NET and refer to below code to remove tables within PPT document.
Screenshots:
Before:
After:
Full Code:
//create Presentation instance and load file Presentation presentation = new Presentation(); presentation.LoadFromFile("sample.ppt"); //get the tables in PowerPoint document List shape_tems = new List(); foreach (IShape shape in presentation.Slides[0].Shapes) { if (shape is ITable) { //add new table to table list shape_tems.Add(shape); } } //remove all tables foreach (IShape shape in shape_tems) { presentation.Slides[0].Shapes.Remove(shape); } //save the document presentation.SaveToFile("result.pptx", FileFormat.Pptx2010); System.Diagnostics.Process.Start("result.pptx");
'create Presentation instance and load file Dim presentation As New Presentation() presentation.LoadFromFile("sample.ppt") 'get the tables in PowerPoint document Dim shape_tems As New List(Of IShape)() For Each shape As IShape In presentation.Slides(0).Shapes If TypeOf shape Is ITable Then 'add new table to table list shape_tems.Add(shape) End If Next 'remove all tables For Each shape As IShape In shape_tems presentation.Slides(0).Shapes.Remove(shape) Next 'save the document presentation.SaveToFile("result.pptx", FileFormat.Pptx2010) System.Diagnostics.Process.Start("result.pptx")
If you couldn't successfully use Spire.Presentation, please refer Spire.Presentation Quick Start which can guide you quickly use Spire.Presentation.
C#/VB.NET: Edit Hyperlinks in Word
In MS Word, a hyperlink is a clickable link that allows you to jump to a web page, a file, an email address, or even another location in the same document. It is undeniable that adding hyperlinks in Word documents is one of the most common operations in daily work, but there are times when you may also need to change the address or update the display text of an existing hyperlink. This article will demonstrate how to programmatically edit a hyperlink in a Word document using Spire.Doc for .NET.
Install Spire.Doc for .NET
To begin with, you need to add the DLL files included in the Spire.Doc for.NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.Doc
Edit a Hyperlink in a Word Document in C# and VB.NET
A hyperlink consists of two basic parts: the hyperlink address (URL) and its display text. With Spire.Doc for .NET, you are allowed to modify both the address and display text of an existing hyperlink using Field.Code and Field.FieldText properties. The detailed steps are as follows.
- Create a Document object.
- Load a sample Word document using Document.LoadFromFile() method.
- Create an object of List<Field>.
- Traverse through all body child objects of sections in the sample document to find all hyperlinks.
- Modify the address (URL) of a specified hyperlink using Field.Code property.
- Modify the display text of a specified hyperlink using Field.FieldText property.
- Save the document to another file using Document.SaveToFile() method.
- C#
- VB.NET
using Spire.Doc; using Spire.Doc.Documents; using Spire.Doc.Fields; using System.Collections.Generic; namespace ModifyHyperlink { class Program { static void Main(string[] args) { //Create a Document object Document doc = new Document(); //Load a sample Word document doc.LoadFromFile("Hyperlink.docx"); //Create an object of List List<Field> hyperlinks = new List<Field>(); //Loop through the items in the sections to find all hyperlinks foreach (Section section in doc.Sections) { foreach (DocumentObject sec in section.Body.ChildObjects) { if (sec.DocumentObjectType == DocumentObjectType.Paragraph) { //Loop through all paragraphs in the sections foreach (DocumentObject para in (sec as Paragraph).ChildObjects) { if (para.DocumentObjectType == DocumentObjectType.Field) { Field field = para as Field; if (field.Type == FieldType.FieldHyperlink) { hyperlinks.Add(field); } } } } } } //Modify the address (URL) of the first hyperlink hyperlinks[0].Code = "HYPERLINK \"" + "https://www.e-iceblue.com/Introduce/word-for-net-introduce.html" + "\""; //Modify the display text of the first hyperlink hyperlinks[0].FieldText = "Spire.Doc for .NET"; //Save the result document doc.SaveToFile("EditHyperlinks.docx", FileFormat.Docx); } } }
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.
Finding Hyperlinks in a word document in C#
Hyperlinks can point to files, emails, websites, imagers or video when readers click on it. Hyperlinks are widely used in word document for it is very convenient to direct readers to related, useful content. By using Spire.Doc, developers can add hyperlinks, finding hyperlinks and modify hyperlinks. This article will show you how to find all the existing hyperlinks in a word document in C#.
Download and install Spire.Doc for .NET and then add 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 details of how to finding hyperlinks in C#.
Firstly, view the word document which contains many hyperlinks:
Please check the code of how to find all the hyperlinks in word document:
using Spire.Doc; using Spire.Doc.Documents; using Spire.Doc.Fields; using System.Collections.Generic; using System.Drawing; namespace FindHyperlink { class Program { static void Main(string[] args) { Document doc = new Document(); doc.LoadFromFile("Spire.docx"); List hyperlinks = new List(); foreach (Section section in doc.Sections) { foreach (DocumentObject sec in section.Body.ChildObjects) { if (sec.DocumentObjectType == DocumentObjectType.Paragraph) { foreach (DocumentObject para in (sec as Paragraph).ChildObjects) { if (para.DocumentObjectType == DocumentObjectType.Field) { Field field = para as Field; if (field.Type == FieldType.FieldHyperlink) { hyperlinks.Add(field); } } } } } } }
The effective screenshot of the finding hyperlinks:
Save PDF file to Stream and Load PDF file from Stream in C#
As a standalone component, compatible with all .NET developing platforms, Spire.PDF for .NET enables developers to create, read, write, edit and handle PDF files without any external PDF reader or software its alike. In this section, I’ll introduce you how to create a PDF file and save it to stream, and how to load PDF file from stream on the contrary.
Part I. Create PDF file and save it to stream
Step 1: New a PDF instance.
PdfDocument doc = new PdfDocument();
Step 2: Create one page.
PdfPageBase page = doc.Pages.Add();
Step 3: Add text to that page.
page.Canvas.DrawString("Hello, World!", new PdfFont(PdfFontFamily.Helvetica, 30f), new PdfSolidBrush(Color.Black), 10, 10);
Step 4: Save PDF file to Stream.
FileStream to_strem = new FileStream("To_stream.pdf", FileMode.Open); doc.SaveToStream(to_stream); to_stream.Close(); doc.Close();
Part II. Load PDF file from stream
Step 1: New a PDF instance.
PdfDocument doc = new PdfDocument();
Step 2: Load PDF file from stream.
FileStream from_stream = File.OpenRead("sample.pdf"); doc.LoadFromStream(from_stream);
Step 3: Save the PDF document.
doc.SaveToFile("From_stream.pdf",FileFormat.PDF); System.Diagnostics.Process.Start("From_stream.pdf");
Full Code:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using Spire.Pdf; using System.IO; using Spire.Pdf.Graphics; using System.Drawing; namespace PdfAndStream { class Program { static void Main(string[] args) { //A: create PDF file and save it to stream //create a pdf document. PdfDocument doc = new PdfDocument(); // create one page PdfPageBase page = doc.Pages.Add(); //draw the text page.Canvas.DrawString("Hello, World!", new PdfFont(PdfFontFamily.Helvetica, 30f), new PdfSolidBrush(Color.Black), 10, 10); //save pdf file to Stream FileStream to_stream = new FileStream("To_stream.pdf", FileMode.Open); doc.SaveToStream(to_stream); to_stream.Close(); doc.Close(); System.Diagnostics.Process.Start("To_stream.pdf"); //B: Load PDF file from Stream //create a pdf document. PdfDocument docFrom = new PdfDocument(); //load PDF file from stream FileStream from_stream = File.OpenRead("sample.pdf"); docFrom.LoadFromStream(from_stream); //save the pdf document docFrom.SaveToFile("From_stream.pdf",FileFormat.PDF); System.Diagnostics.Process.Start("From_stream.pdf"); } } }
Besides loading PDF document from stream, Spire.PDF also provide easy access to load PDF document from file and byte array. See Spire.PDF Program Guide, to get more info regarding processing PDF in C#, VB.NET.
C#/VB.NET: Convert XLS to XLSX and Vice versa
XLS and XLSX are two different file formats for Microsoft Excel spreadsheets. XLS is the default file format for Microsoft Excel 2003 and earlier versions, while XLSX is the default file format for Microsoft Excel 2007 and later versions. In some cases, developers may need to convert between Excel XLS and XLSX file formats. In this article, we will explain how to convert XLS to XLSX or XLSX to XLS in C# and VB.NET 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 XLS to XLSX in C# and VB.NET
The following are the steps to convert an XLS file to XLSX format using Spire.XLS for .NET:
- Create a Workbook instance.
- Load the XLS file using Workbook.LoadFromFile() method.
- Save the XLS file to XLSX format using Workbook.SaveToFile(string, ExcelVersion) method.
- C#
- VB.NET
using Spire.Xls; namespace ConvertXlsToXlsx { class Program { static void Main(string[] args) { //Create a Workbook instance Workbook workbook = new Workbook(); //Load an XLS file workbook.LoadFromFile("Input.xls"); //Convert the file to XLSX format workbook.SaveToFile("ToXlsx.xlsx", ExcelVersion.Version2016); } } }
Convert XLSX to XLS in C# and VB.NET
The following are the steps to convert an XLSX file to XLS format using Spire.XLS for .NET:
- Create a Workbook instance.
- Load the XLSX file using Workbook.LoadFromFile() method.
- Save the XLSX file to XLS format using Workbook.SaveToFile(string, ExcelVersion) method.
- C#
- VB.NET
using Spire.Xls; namespace ConvertXlsxToXls { class Program { static void Main(string[] args) { //Create a Workbook instance Workbook workbook = new Workbook(); //Load an XLSX file workbook.LoadFromFile("Input.xlsx"); //Convert the file to XLS format workbook.SaveToFile("ToXls.xls", ExcelVersion.Version97to2003); } } }
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.
Traverse through the cells of Table
Spire.Presentation is a powerful and easy-to-use .NET component, especially designed for developers. Using Spire.Presentation you can generate, modify, convert, render, and print documents without installing Microsoft PowerPoint on your machine. There are documents on our site introducing how to insert table and edit table in PowerPoint file. In this document, I will introduce you how to traverse through the cells of table.
It is very easy to traverse through the cells of table using Spire.Presentation. Just get the row collection using the property - TableRows of Table. Then traverse through each cell of each row. Or you can get the column collection using the property – ColumnsList of Table. Then traverse through each cell of each column.
Step 1: Create Presentation instance and load file.
Presentation presentation = new Presentation(); presentation.LoadFromFile("table.pptx");
Step 2: Get the table in PowerPoint file.
foreach (IShape shape in presentation.Slides[0].Shapes) { if (shape is ITable) { table = (ITable)shape; } }
Step 3: Traverse through the rows in row collection and traverse through each cell in each row.
foreach (TableRow row in table.TableRows) { foreach (Cell cell in row) { //print the data in cell Console.Write("{0,15}", cell.TextFrame.Text); } Console.WriteLine(); }
Download and install Spire.Presentation for .NET and refer to below code to traverse through the cells in PowerPoint document.
Screenshots and Full code:
using Spire.Presentation; using System; namespace Trasverse { class Program { static void Main(string[] args) { //create Presentation instance and load file Presentation presentation = new Presentation(); presentation.LoadFromFile("table.pptx"); ITable table = null; //get the table foreach (IShape shape in presentation.Slides[0].Shapes) { if (shape is ITable) { table = (ITable)shape; //traverse through the cells of table foreach (TableRow row in table.TableRows) { foreach (Cell cell in row) { //print the data in cell Console.Write("{0,15}", cell.TextFrame.Text); } Console.WriteLine(); } } } Console.ReadLine(); } } }
If you couldn't successfully use Spire.Presentation, please refer Spire.Presentation Quick Start which can guide you quickly use Spire.Presentation.
How to Remove Row or Column in Table
We have previously introduced how to insert a custom table and how to edit a table in PowerPoint documents. Now, we are going to make a brief introduction about how to remove the rows or columns that belong to an existing table.
Spire.Presentation for .NET, built to provide flexible PowerPoint document handling capabilities, allows developers to add a table to a slide and perform some basic operations as removing rows and columns in the table in an easy way.
Step 1: Create a PowerPoint instance and load a sample file.
Presentation presentation = new Presentation(); presentation.LoadFromFile("table.pptx");
Step 2: Find the table and remove the second column and second row.
ITable table = null; foreach (IShape shape in presentation.Slides[0].Shapes) { if (shape is ITable) { table = (ITable)shape; table.ColumnsList.RemoveAt(1, false) table.TableRows.RemoveAt(1, false); } }
Step 3: Save the document.
presentation.SaveToFile("result.pptx", FileFormat.Pptx2010); System.Diagnostics.Process.Start("result.pptx");
The sample table with the second row and second column highlighted.
Result:
Full C# code:
using Spire.Presentation; namespace RemoveRow { class Program { static void Main(string[] args) { //create a PPT document Presentation presentation = new Presentation(); presentation.LoadFromFile("table.pptx"); //get the table in PPT document ITable table = null; foreach (IShape shape in presentation.Slides[0].Shapes) { if (shape is ITable) { table = (ITable)shape; //remove the second column table.ColumnsList.RemoveAt(1, false); //remove the second row table.TableRows.RemoveAt(1, false); } } //save the document presentation.SaveToFile("result.pptx", FileFormat.Pptx2010); System.Diagnostics.Process.Start("result.pptx"); } } }