C# – Update Word Formula Fields

Word fields are placeholders that store and display data. They perform simple tasks, such as returning the current date or current page number, but they’re much more than that. You can use them to ask questions, make decisions based on specific conditions, and perform calculations.

Just because a field contains dynamic text doesn’t mean that the field is always accurate. Occasionally, fields need updating. Assume you have an involve template (like Figure 1) which contains formula fields in the table cells where ‘0.0’ appears, you’d like to insert some necessary information in the invoice and update the fields automatically. This is what I am intending to do in this article.

2018-01-16_155023Figure 1. Template Word File

Still, I am using Spire.Doc library to complete this job. Here is the entire code.


using Spire.Doc;

namespace UpdateField
{
    class Program
    {
        static void Main(string[] args)
        {
            //create a document instacne
            Document doc = new Document();

            //load the template file
            doc.LoadFromFile("InvoiceTemplate.docx");

            //replace data in the document
            doc.Replace("a.description", "Product A", true, true);
            doc.Replace("b.description", "Product B", true, true);
            doc.Replace("c.description", "Product C", true, true);
            doc.Replace("a.quantity", "1", true, true);
            doc.Replace("b.quantity", "2", true, true);
            doc.Replace("c.quantity", "1", true, true);
            doc.Replace("a.price", "14", true, true);
            doc.Replace("b.price", "10", true, true);
            doc.Replace("c.price", "23", true, true);

            //update fields
            doc.IsUpdateFields = true;
            
            //save to file
            doc.SaveToFile("output.docx", FileFormat.Docx2013);
        }
    }
}
2018-01-16_155412
Figure 2. Result File
Advertisements

How to Split Word File into Multiple PDF Files using C#

I came cross this topic when I was browsing codeproject.com, and it aroused my interest in finding a solution. Through a few hours of effort, I got following two solutions based on Free Spire.Office for .NET.

The first solution converts Word document to PDF and splits the PDF document into multiple files using Split() method, however, this solution doesn’t allow to specify names for each generated file. The second solution is provided for someone who also wants to specify file names while splitting.

Note: After installing the library,  the Spire.Doc.dll and Spire.Pdf.dll file should be added as references in your VS project.

Solution 1:


using System;
using Spire.Doc;
using Spire.Pdf;
using System.IO;

namespace SplitWord
{
    class Program
    {
        static void Main(string[] args)
        {
            //load word document
            Document doc = new Document("WordDocument.docx");
            //save to stream in pdf file format
            var stream = new MemoryStream();
            doc.SaveToStream(stream, Spire.Doc.FileFormat.PDF);
            //load pdf from stream
            PdfDocument pdf = new PdfDocument(stream);
            //split pdf to multiple documents based on every single page
            String pattern = "SplitDocument-{0}.pdf";
            pdf.Split(pattern);
            //close documents
            doc.Close();
            pdf.Close();
        }
    }
}

2017-12-06_103437

Solution 2:


using Spire.Doc;
using Spire.Pdf;
using System.IO;


namespace SplitWord
{
    class Program
    {
        static void Main(string[] args)
        {
            //load word document
            Document doc = new Document("WordDocument.docx");
            //save to stream in pdf file format
            var stream = new MemoryStream();
            doc.SaveToStream(stream, Spire.Doc.FileFormat.PDF);
            //load pdf from stream
            PdfDocument pdf = new PdfDocument(stream);
            //get page count
            int pageCount = pdf.Pages.Count;
            PdfPageBase page;
            //define file names
            string[] destFileName = new string[] { "ABC", "DEF", "GHI" };
            for (int i = 0; i < pageCount; i++)
            {
                //create new pdf document
                PdfDocument newPdf = new PdfDocument();
                //add a page to newPdf
                page = newPdf.Pages.Add(pdf.Pages[i].Size, new Spire.Pdf.Graphics.PdfMargins(0));
                //draw content on the page
                pdf.Pages[i].CreateTemplate().Draw(page, new System.Drawing.PointF(0, 0));
                //save file with specified name
                newPdf.SaveToFile(destFileName[i] + ".pdf");
            }
            //close documents
            doc.Close();
            pdf.Close();
        }
    }
}

2017-12-06_115859

Import CSV Files to Excel, PDF, DataGridView in C#

Files with the .csv extension are very similar to plain text files. CSV files are commonly used to transfer data from one database or spreadsheet format to another. This post gives examples of how to import data from CSV files into Excel worksheet, PDF form and DataGridView, by using .NET Excel component with C#.

CVS to Excel


//create a Workbook object
Workbook wb = new Workbook();

//load sample csv file
wb.LoadFromFile("SampleCSVFile.csv",",",1,1);

//save as excel file
wb.SaveToFile("toExcel.xlsx", ExcelVersion.Version2013);

2017-11-15_113237

CVS to PDF


//create a Workbook object
Workbook wb = new Workbook();

//load sample csv file
wb.LoadFromFile("SampleCSVFile.csv",",",1,1);

//render content of a worksheet into a single PDF page
wb.ConverterSetting.SheetFitToPage = true;

//autofit a column if the characters in the column exceed column width
Worksheet sheet = wb.Worksheets[0];
for (int i = 1; i < sheet.Columns.Length; i++)
{
    sheet.AutoFitColumn(i);
}

//save to PDF file
sheet.SaveToPdf("toPDF.pdf");

2017-11-15_113308

CVS to DataGridView


private void btnImport_Click(object sender, EventArgs e)
{
    //create a OpenFileDialog instance
    OpenFileDialog dialog = new OpenFileDialog();
    
    //set filter
    dialog.Filter = "CSV document(*.csv)|*.csv";

    //show dialog
    DialogResult result = dialog.ShowDialog();
    if (result == DialogResult.OK)
    {
        //get cvs file name
        string csvFile = dialog.FileName;

        //load csv to Wordbook instance
        Workbook wb = new Workbook();
        wb.LoadFromFile(csvFile,",");

        //export to datatable
        System.Data.DataTable datatable = wb.Worksheets[0].ExportDataTable();

        //display data in datagridview
        this.dataGridView1.DataSource = datatable;
    }
}

2017-11-15_113120

Adding Shapes to Word in C#

Introduction

Shapes are drawing elements, such as rectangles, circles, polygons, and lines. A shape can be filled and/or outlined. Multiple shapes can be grouped so that you can move, resize or arrange them together, as though they were a single shape or object. This tip gives you examples of how to create and customize shape and shape group using .NET Word API with C#.

Background

This library offers a ShapeObject class and a ShapeGroup class to work with a single shape and a shape group respectively. A shape group consists of at least 2 single shapes. Both shape and shape group are attached to an anchoring paragraph. You can call AppendShape() and AppendShapeGroup() method of Paragraph class to add them to the specified paragraph. The following sections will dive into the details about how to:

  • insert, position and format shape
  • reorder the overlapping shapes
  • create multiple shapes and group them together

Using the code

Part I. Insert a shape

Following code snippet adds a 5-pointed star (predefined in ShapeType enumeration) at the specified position in Word document, and sets the fill color, line style, line weight and line color as well.


//initialize an instance of Document class
Document doc = new Document();

//add section, add a paragraph to section
Section section = doc.AddSection();
Paragraph paragraph = section.AddParagraph();

//append a shape (5-pointed star) to paragraph
ShapeObject shape = paragraph.AppendShape(80, 80, ShapeType.Star);

//set the relative position to page margin
shape.VerticalOrigin = VerticalOrigin.Margin;
shape.HorizontalOrigin = HorizontalOrigin.Margin;
shape.VerticalPosition = 50;
shape.HorizontalPosition = 0;

//set the line style of shape
shape.LineStyle = ShapeLineStyle.Triple;

//set the weight and color of outline
shape.StrokeWeight = 5;
shape.StrokeColor = System.Drawing.Color.Purple;

//set the fill color of shape
shape.FillColor = System.Drawing.Color.Yellow;

//save to file
doc.SaveToFile("output.docx", FileFormat.Docx2013);

InsertAShape20171011

Part II. Change the order of overlapping shapes

Sometimes, you may need to stack a few shapes on top of each other and control the order in which they appear in the stack. Just like in MS Word, the newly created shape always comes in front of the previous one. To change the order of overlapping shapes, set the ZOrder property of shape object.


//initialize an instance of Document class
Document doc = new Document();
//add section, add a paragraph to section
Section section = doc.AddSection();
Paragraph paragraph = section.AddParagraph();

//append one shape to paragraph
ShapeObject shape1 = paragraph.AppendShape(60, 60, ShapeType.Rectangle);
shape1.VerticalPosition = 0;
shape1.HorizontalPosition = 0;
shape1.FillColor = System.Drawing.Color.Blue;

//append another shape to paragraph
ShapeObject shape2 = paragraph.AppendShape(80, 80, ShapeType.Ellipse);
shape2.VerticalPosition = 30;
shape2.HorizontalPosition = 30;
shape2.FillColor = System.Drawing.Color.Green;

//change the order of the shapes by assigning different values to ZOrder
shape1.ZOrder = 2;
shape2.ZOrder = 1;

//save to file
doc.SaveToFile("output.docx", FileFormat.Docx2013);

ChangeZOrder20171011

Part III. Group your shapes

Shape group itself is a square box with no actual meaning. After appending a group object to the specified paragraph, you need to create more shapes and add them to group for forming a grouped shape. In this instance, three textboxes and two line shapes has been grouped as one unit.


//initialize a Document class
Document doc = new Document();

//add section, add a paragraph to section
Section section = doc.AddSection();
Paragraph paragraph = section.AddParagraph();

//create an instance of ShapeGroup class
ShapeGroup group = paragraph.AppendShapeGroup(500, 100);
group.VerticalPosition = 50;

//create three TextBox instances and set the content and formatting separately
TextBox textbox1 = new TextBox(doc);
textbox1.Body.AddParagraph().AppendText("Visual C#");
textbox1.Body.Paragraphs[0].Format.HorizontalAlignment = HorizontalAlignment.Center;
textbox1.Format.TextAnchor = ShapeVerticalAlignment.Center;
textbox1.Width = 150;
textbox1.Height = 300;
textbox1.FillColor = System.Drawing.Color.LightGray;
textbox1.StrokeColor = System.Drawing.Color.Black;
textbox1.HorizontalPosition = 0;
textbox1.StrokeWeight = 1;

TextBox textbox2 = new TextBox(doc);
textbox2.Body.AddParagraph().AppendText("Java");
textbox2.Body.Paragraphs[0].Format.HorizontalAlignment = HorizontalAlignment.Center;
textbox2.Format.TextAnchor = ShapeVerticalAlignment.Center;
textbox2.Body.Paragraphs[0].Format.TextAlignment = TextAlignment.Bottom;
textbox2.Width = 150;
textbox2.Height = 300;
textbox2.FillColor = System.Drawing.Color.LightGray;
textbox2.StrokeColor = System.Drawing.Color.Black;
textbox2.HorizontalPosition = 260;
textbox2.StrokeWeight = 1;

TextBox textbox3 = new TextBox(doc);
textbox3.Body.AddParagraph().AppendText("Php");
textbox3.Body.Paragraphs[0].Format.HorizontalAlignment = HorizontalAlignment.Center;
textbox3.Format.TextAnchor = ShapeVerticalAlignment.Center;
textbox3.Width = 150;
textbox3.Height = 300;
textbox3.FillColor = System.Drawing.Color.LightGray;
textbox3.StrokeColor = System.Drawing.Color.Black;
textbox3.HorizontalPosition = 520;
textbox3.StrokeWeight = 1;

//add textboxes to shape group as child objects
group.ChildObjects.Add(textbox1);
group.ChildObjects.Add(textbox2);
group.ChildObjects.Add(textbox3);

//add two line shapes to shape group
group.ChildObjects.Add(new Spire.Doc.Fields.ShapeObject(doc, ShapeType.Line)
{
    Width = 100,
    FillColor = System.Drawing.Color.Red,
    HorizontalPosition = 155,
    VerticalPosition = 150,
    StrokeWeight = 1,
});

group.ChildObjects.Add(new Spire.Doc.Fields.ShapeObject(doc, ShapeType.Line)
{
    Width = 100,
    FillColor = System.Drawing.Color.Red,
    HorizontalPosition = 415,
    VerticalPosition = 150,
    StrokeWeight = 1,
});

//save to file
doc.SaveToFile("output.docx", FileFormat.Docx2013);

GroupShape20171011

Create Grids in PDF using Free API in C#

Introduction

A grid or a table is a great way to present data into groups. Manual generation of grids can appear as a boring task one could rather prefer to be automated. As a part of this article, I’ll dive into details of automatic grid creation and generation and show how to create perfect grids using a few small pieces of C# code and free .NET PDF component as the main tool.

The component used for generating grids is a simple and tiny class library that helps programmers to process PDF files on .NET platform. A grid in the library is represented by the PdfGrid class, helping to automate the creation of tabulated data in PDF. Its rows are represented by the PdfGridRow objects and cells by the PdfGirdCell objects. Each of them has a Style property that allows us to format the gird elements easily.

The following sections will demonstrate:

  • How to create a simple grid
  • How to merge cells, format text and cells in grid
  • How to dynamically create a grid using the data exported from database

Part 1. Simple grid with uniform cells

Let’s create the document containing a simple grid with uniform cells, using the code below.


//create a pdf object, add a page
PdfDocument doc = new PdfDocument();
PdfPageBase page = doc.Pages.Add();
//create a 2x4 gird object 
PdfGrid grid = new PdfGrid();
grid.Style.CellPadding = new PdfPaddings(1, 1, 1, 1);
PdfGridRow row1 = grid.Rows.Add();
PdfGridRow row2 = grid.Rows.Add();
grid.Columns.Add(4);
//set column width
foreach (PdfGridColumn col in grid.Columns)
{
    col.Width = 60f;
}
//insert data
for (int i = 0; i < grid.Columns.Count; i++)
{
    row1.Cells[i].Value = String.Format("col{0}", i+1);
    row2.Cells[i].Value = String.Format("{0}", i + 1);
}
//draw grid on pdf page
PdfLayoutResult result = grid.Draw(page, new PointF(10, 10));
//save document
doc.SaveToFile("Simple_Grid.pdf");

2017090401

Part 2. Grid with formatted cells, row spans and column spans

When creating a grid in a real PDF report, you may need to change the grid layout by merging certain cells or emphasize the important information by applying styles. The following code snippets will show you how to create an eye-catching and meaningful grid.


//create a pdf object, add a page
PdfDocument doc = new PdfDocument();
PdfPageBase page = doc.Pages.Add();
//create a 4x4 gird object 
PdfGrid grid = new PdfGrid();
grid.Style.CellPadding = new PdfPaddings(1, 1, 1, 1);
PdfGridRow row1 = grid.Rows.Add();
PdfGridRow row2 = grid.Rows.Add();
PdfGridRow row3 = grid.Rows.Add();
PdfGridRow row4 = grid.Rows.Add();
grid.Columns.Add(4);
//set column width
foreach (PdfGridColumn col in grid.Columns)
{
    col.Width = 60f;
}
//insert data
row1.Cells[0].Value = "Orders and Payments";
row2.Cells[0].Value = "Order";
row2.Cells[1].Value = "Date";
row2.Cells[2].Value = "Customer";
row2.Cells[3].Value = "Paid";
row3.Cells[0].Value = "00223";
row3.Cells[1].Value = "02/06/2016";
row3.Cells[2].Value = "JDX Co., Ltd";           
row3.Cells[3].Value = "Yes";
row4.Cells[0].Value = "00224";
row4.Cells[1].Value = "03/06/2016";
row4.Cells[3].Value = "No";
//horizontally and vertically merge certain cells through ColumnSpan and RowSpan
row1.Cells[0].ColumnSpan = 4;
row3.Cells[2].RowSpan = 2;
//align text
row1.Cells[0].StringFormat = new PdfStringFormat(PdfTextAlignment.Center);
row3.Cells[2].StringFormat = new PdfStringFormat(PdfTextAlignment.Justify, PdfVerticalAlignment.Middle);
//set background color of cells
row1.Cells[0].Style.BackgroundBrush = PdfBrushes.Gray;
row3.Cells[3].Style.BackgroundBrush = PdfBrushes.Green;
row4.Cells[3].Style.BackgroundBrush = PdfBrushes.MediumVioletRed;
//set font style
row1.Style.Font = new PdfTrueTypeFont(new Font("Arial", 9f, FontStyle.Bold));
row2.Style.Font = new PdfTrueTypeFont(new Font("Aril", 8f));
row3.Style.Font = new PdfTrueTypeFont(new Font("Aril", 8f));
row4.Style.Font = new PdfTrueTypeFont(new Font("Aril", 8f));
//format cells border
PdfBorders borders = new PdfBorders();
borders.All = new PdfPen(Color.Black, 0.1f);
foreach (PdfGridRow pgr in grid.Rows)
{
    foreach (PdfGridCell pgc in pgr.Cells)
    {
        pgc.Style.Borders = borders;
    }
}
//draw grid on pdf page
PdfLayoutResult result = grid.Draw(page, new PointF(10, 10));
//save document
doc.SaveToFile("Merge_Format_Cells.pdf");

2017090402

Part 3. Dynamically generate a grid from Access data

Often your application will pull data from a database and store it in the form of a DataTable. You may wish to easily insert this data into your document as a grid and quickly apply formatting to the whole grid. This part will demonstrate how to dynamically create a grid from Access data.

The MDB file looks as follows:
2017090403


//create a pdf document object, insert a page
PdfDocument doc = new PdfDocument();
PdfPageBase page = doc.Pages.Add();
//initialize a pdf grid
PdfGrid grid = new PdfGrid();
grid.Style.CellPadding = new PdfPaddings(2, 2, 2, 2);
//export data from database to grid
DataTable dataTable = new DataTable(); 
DataTable newTable = new DataTable();
using (OleDbConnection conn = new OleDbConnection())
{
    conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=demo.mdb";
    OleDbCommand command = new OleDbCommand();
    command.CommandText
        = " select Name, '' as Flag, Capital, Continent, Area, Population, Flag as FlagData from country ";
    command.Connection = conn;
    using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command))
    {
        dataAdapter.Fill(dataTable);
        dataAdapter.Fill(newTable);                 
        newTable.Columns.RemoveAt(newTable.Columns.Count - 1); 
        grid.DataSource = newTable;//export data from data table to grid
    }
}
//transfer binary flag data as images and insert them to grid cells
for (int i = 0; i < dataTable.Rows.Count; i++)
{
    PdfGridCellTextAndStyleList lst = new PdfGridCellTextAndStyleList();
    PdfGridCellTextAndStyle textAndStyle = new PdfGridCellTextAndStyle();
    byte[] imageData = dataTable.Rows[i][dataTable.Columns.Count - 1] as byte[];
    using (MemoryStream ms = new MemoryStream(imageData))
    {
        textAndStyle.Image = PdfImage.FromStream(ms);
        textAndStyle.ImageSize = new SizeF(10f, 10f);
        textAndStyle.ContentAlign = PdfGridCellContentAndAlign.Center;
        lst.List.Add(textAndStyle);
        grid.Rows[i].Cells[1].Value = lst;
        lst = null;
    }
}
//format header
grid.Headers[0].Style.BackgroundBrush = PdfBrushes.Gray;
grid.Headers[0].Style.Font= new PdfTrueTypeFont(new Font("Arial", 9f, FontStyle.Bold));
foreach (PdfGridCell cell in grid.Headers[0].Cells)
{
    cell.StringFormat = new PdfStringFormat(PdfTextAlignment.Center,PdfVerticalAlignment.Middle);     
    cell.Style.TextBrush = PdfBrushes.White;
}
//set column width
foreach (PdfGridColumn col in grid.Columns)
{
    col.Width = 80f;          
}         
//horizontally and vertically align text
for (int rowIndex = 0; rowIndex < grid.Rows.Count; rowIndex++)
{
    for (int i = 0; i < grid.Rows[rowIndex].Cells.Count; i++)
    {
        grid.Rows[rowIndex].Cells[i].StringFormat= new PdfStringFormat(PdfTextAlignment.Center, PdfVerticalAlignment.Middle);
    }
    //apply alternate style for odd and even rows
    if (rowIndex % 2 == 0)
    {
        grid.Rows[rowIndex].Style.BackgroundBrush = PdfBrushes.LightYellow;
    }
    else
        grid.Rows[rowIndex].Style.BackgroundBrush = PdfBrushes.LightBlue;
}
//draw grid on pdf page
PdfLayoutResult result = grid.Draw(page, new PointF(10, 10));
//save document
doc.SaveToFile("Generate_Grid_From_Database.pdf");

2017090404