Knowledgebase (2343)
Children categories
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");
}
