Tuesday, 21 April 2020 09:46

How to Mail Merge Image in Word in Java

This article demonstrates how to mail merge image in Word document in Java using Spire.Doc for Java.

The template document:

How to Mail Merge Image in Word in Java

import com.spire.doc.Document;
import com.spire.doc.FileFormat;
import com.spire.doc.reporting.MergeImageFieldEventArgs;
import com.spire.doc.reporting.MergeImageFieldEventHandler;

import java.text.SimpleDateFormat;
import java.util.Date;

public class SimpleMailMerge {
    public static void main(String[] args) throws Exception {
        //create a Document instance
        Document document = new Document();
        //load the template document
        document.loadFromFile("template - Copy.docx");

        //specify the merge field name
        String[] filedNames = new String[]{"image"};
        //specify the path of image 
        String[] filedValues = new String[]{"logo.png"};
        //invoke the mail merge event to load image 
        document.getMailMerge().MergeImageField = new MergeImageFieldEventHandler() {
            public void invoke(Object sender, MergeImageFieldEventArgs args) {
                mailMerge_MergeImageField(sender, args);
            }
        };
        //execute mail merge
        document.getMailMerge().execute(filedNames, filedValues);

        //save file
        document.saveToFile("MailMergeImage.docx", FileFormat.Docx_2013);
    }
    //create a mail merge event to load image
    private static void mailMerge_MergeImageField(Object sender, MergeImageFieldEventArgs field) {
        String filePath = field.getImageFileName();
        if (filePath != null && !"".equals(filePath)) {
            try {
                field.setImage(filePath);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

The output document:

How to Mail Merge Image in Word in Java

This article demonstrates how to highlight the values below average or above average, and how to calculate the number of these values respectively using Spire.XLS for Java.

Sample Document

Highlight Values Below or Above Average in Excel in Java

import com.spire.xls.*;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;

import java.awt.*;

public class HighlightBelowAboveAverage {

    public static void main(String[] args) {

        //Create a Workbook object
        Workbook workbook = new Workbook();

        //Load a sample Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\data.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Use conditional formatting to highlight the values below average in the range "B2:B9"
        XlsConditionalFormats format1 = sheet.getConditionalFormats().add();
        format1.addRange(sheet.getCellRange("B2:B9"));
        IConditionalFormat cf1 = format1.addAverageCondition(AverageType.Below);
        cf1.setBackColor(Color.red);

        //Use conditional formatting to highlight the values above average in the range "B2:B9"
        XlsConditionalFormats format2 = sheet.getConditionalFormats().add();
        format2.addRange(sheet.getCellRange("B2:B9"));
        IConditionalFormat cf2 = format1.addAverageCondition(AverageType.Above);
        cf2.setBackColor(Color.yellow);

        //Get the count of values below average
        sheet.getCellRange("D13").setFormula("=COUNTIF(B2:B9,\"<\"&AVERAGE(B2:B9))");
        
        //Get the count of values above average
        sheet.getCellRange("D14").setFormula("=COUNTIF(B2:B9,\">\"&AVERAGE(B2:B9))");

        //Save the file
        workbook.saveToFile("BolowOrAboveAverage.xlsx", ExcelVersion.Version2016);
    }
}

Output

Highlight Values Below or Above Average in Excel in Java

This article demonstrates how to set and get document properties in PowerPoint using Spire.Presentation for Java.

Set Document Properties

import com.spire.presentation.FileFormat;
import com.spire.presentation.Presentation;

public class SetAndGetDocumentProperties {
    public static void main(String[] args) throws Exception {
        //create a Presentation instance
        Presentation presentation = new Presentation();

        //load a PowerPoint document
        presentation.loadFromFile("example.pptx");

        //set document properties for the PowerPoint document
        presentation.getDocumentProperty().setApplication("Spire.Presentation");
        presentation.getDocumentProperty().setAuthor("E-iceblue");
        presentation.getDocumentProperty().setCompany("E-iceblue Co., Ltd.");
        presentation.getDocumentProperty().setKeywords("Demo File");
        presentation.getDocumentProperty().setComments("For internal use only.");
        presentation.getDocumentProperty().setCategory("Demo");
        presentation.getDocumentProperty().setTitle("This is a demo file.");
        presentation.getDocumentProperty().setSubject("Test");

        //save the document
        presentation.saveToFile("addProperties.pptx", FileFormat.PPTX_2013);
        presentation.dispose();
    }
}

Set and Get Document Properties in PowerPoint in Java

Get Document Properties

import com.spire.presentation.Presentation;

import java.io.FileWriter;
import java.io.IOException;

public class GetDocumentProperties {
    public static void main(String[] args) throws Exception {
        //create a Presentation instance
        Presentation presentation = new Presentation();

        //load a PowerPoint document
        presentation.loadFromFile("addProperties.pptx");

        //get the document properties of the PowerPoint document
        String application = presentation.getDocumentProperty().getApplication();
        String author = presentation.getDocumentProperty().getAuthor();
        String company = presentation.getDocumentProperty().getCompany();
        String keywords = presentation.getDocumentProperty().getKeywords();
        String comments = presentation.getDocumentProperty().getComments();
        String category = presentation.getDocumentProperty().getCategory();
        String title = presentation.getDocumentProperty().getTitle();
        String subject = presentation.getDocumentProperty().getSubject();

        //Create a StringBuilder to save the document properties
        StringBuilder content = new StringBuilder();
        content.append("DocumentProperty.Application: " + application);
        content.append("\r\nDocumentProperty.Author: " + author);
        content.append("\r\nDocumentProperty.Company " + company);
        content.append("\r\nDocumentProperty.Keywords: " + keywords);
        content.append("\r\nDocumentProperty.Comments: " + comments);
        content.append("\r\nDocumentProperty.Category: " + category);
        content.append("\r\nDocumentProperty.Title: " + title);
        content.append("\r\nDocumentProperty.Subject: " + subject);

        //save to a .txt document
        writeStringToTxt(content.toString(),"getProperties.txt");
    }
    public static void writeStringToTxt(String content, String txtFileName) throws IOException {
        FileWriter fWriter= new FileWriter(txtFileName,true);
        try {
            fWriter.write(content);
        }catch(IOException ex){
            ex.printStackTrace();
        }finally{
            try{
                fWriter.flush();
                fWriter.close();
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }
    }
}

Set and Get Document Properties in PowerPoint in Java

Spire.PDF supports to convert PDF to HTML and save the resulted HTML file to stream by calling the method PdfDocument.saveToStream(). When converting PDF to HTML, it also supports to set the convert options with embedded SVG/Image on the resulted HTML file. This article will demonstrate how to convert the PDF pages to HTML with embedded SVG and embedded image.

import com.spire.pdf.*;
import java.io.*;

public class PDFtoHTML {
    public static void main(String[] args) throws FileNotFoundException {

        String inputFile = "Sample.pdf";
        String outputFile = "output/toHTML_out.html";

        //Load the sample document file
        PdfDocument pdf = new PdfDocument();

        pdf .loadFromFile(inputFile);

        //Set the bool useEmbeddedSvg and useEmbeddedImg as true 
        pdf .getConvertOptions().setPdfToHtmlOptions(true,true);

        //Save to stream
        File outFile = new File(outputFile);
        OutputStream outputStream = new FileOutputStream(outFile);
        pdf.saveToStream(outputStream, FileFormat.HTML);
        pdf.close();

    }
}

This article demonstrates how to highlight the highest and lowest value in a cell rang through conditional formatting. You can also highlight the top 5 or bottom 5 values by passing 5 to setRank() method in the code snippet below.

import com.spire.xls.*;

import java.awt.*;

public class HighlightTopBottom {

    public static void main(String[] args) {

        //Create a Workbook object
        Workbook workbook = new Workbook();

        //Load the sample Excel file
        workbook.loadFromFile("G:\\360MoveData\\Users\\Administrator\\Desktop\\sales report.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Apply conditional formatting to range "B2:E5" to highlight the highest value
        ConditionalFormatWrapper format1 = sheet.getCellRange("B2:E5").getConditionalFormats().addCondition();
        format1.setFormatType(ConditionalFormatType.TopBottom);
        format1.getTopBottom().setType(TopBottomType.Top);
        format1.getTopBottom().setRank(1);
        format1.setBackColor(Color.red);

        //Apply conditional formatting to range "B2:E5" to highlight the lowest value
        ConditionalFormatWrapper format2 = sheet.getCellRange("B2:E5").getConditionalFormats().addCondition();
        format2.setFormatType(ConditionalFormatType.TopBottom);
        format2.getTopBottom().setType(TopBottomType.Bottom);
        format2.getTopBottom().setRank(1);
        format2.setBackColor(Color.yellow);

        //Save the document
        workbook.saveToFile("output/HighestLowestValue.xlsx", ExcelVersion.Version2016);
    }
}

Highlight Highest and Lowest Value in Excel in Java

Thursday, 09 April 2020 03:33

Add annotation to PDF in Java

This tutorial introduces how to add annotation to a PDF document using Spire.PDF for Java. Spire.PDF supports many kinds of annotation, such as text annotation, file link annotation, document link annotation, text box annotation and pop up annotation, etc. Here we will add a text box annotation and popup annotation for example.

Add a pop-up annotation to a new PDF page:

import com.spire.pdf.*;
import com.spire.pdf.annotations.*;
import com.spire.pdf.graphics.*;
import java.awt.*;
import java.awt.geom.*;

public class PDFAnnotation {
    public static void main(String[] args)  {

        PdfDocument doc = new PdfDocument();
        //Set the margin of PDF
        PdfUnitConvertor unitCvtr = new PdfUnitConvertor();
        PdfMargins margin = new PdfMargins();
        margin.setTop(unitCvtr.convertUnits(2.54f, PdfGraphicsUnit.Centimeter, PdfGraphicsUnit.Point));
        margin.setBottom(margin.getTop());
        margin.setLeft(unitCvtr.convertUnits(3f, PdfGraphicsUnit.Centimeter, PdfGraphicsUnit.Point));
        margin.setRight(margin.getLeft());
        //Create one page
        PdfPageBase page = doc.getPages().add(PdfPageSize.A4, margin);

        //Add text
        PdfBrush brush1 = PdfBrushes.getBlack();
        PdfTrueTypeFont font1 = new PdfTrueTypeFont(new Font("Arial", Font.BOLD + Font.ITALIC,13), true);
        PdfStringFormat format1 = new PdfStringFormat(PdfTextAlignment.Left);
        float y = 50;
        String s = "The sample demonstrates how to add annotations to PDF document.";
        page.getCanvas().drawString(s, font1, brush1, 0, y - 5, format1);
        y = y + (float)font1.measureString(s, format1).getHeight();

        //set the annotation font, string, size, position and style
        PdfTrueTypeFont font = new PdfTrueTypeFont(new Font("Arial",0, 12));
        PdfStringFormat format = new PdfStringFormat();
        format.setMeasureTrailingSpaces(true);
        String prompt = "Popup Annotation: ";
        Dimension2D size = font.measureString(prompt, format);
        page.getCanvas().drawString(prompt, font, PdfBrushes.getDodgerBlue(), 0, y);
        float x = (float)size.getWidth();
        String label = "demo of Pop-up annotation";
        page.getCanvas().drawString(label, font, PdfBrushes.getOrangeRed(), x, y);
        x = x + (float)font.measureString(label, format).getWidth();
        String markupText = "What is Spire.PDF for Java";
        Rectangle2D rectangle2D = new Rectangle.Float();
        rectangle2D.setFrame(new Point2D.Double(x,y),new Dimension());
        PdfPopupAnnotation annotation = new PdfPopupAnnotation(rectangle2D, markupText);
        annotation.setIcon(PdfPopupIcon.Paragraph);
        annotation.setOpen(true);
        annotation.setColor(new PdfRGBColor(Color.YELLOW));
        ((PdfNewPage) page).getAnnotations().add(annotation);
        
        //Save the document to file
        doc.saveToFile("output/annotation.pdf");
        doc.close();
    }
}

Effective screenshot of pop-up annotation:

Add annotation to PDF in Java

Add text box annotation to an existing PDF page:

import com.spire.pdf.*;
import com.spire.pdf.annotations.*;
import com.spire.pdf.graphics.*;
import java.awt.*;
import java.awt.geom.*;
import com.spire.pdf.general.find.PdfTextFind;
public class PDFAnnotation {
    public static void main(String[] args)  {

        //load the document from file
        PdfDocument doc = new PdfDocument();
        doc.loadFromFile("Sample0.pdf");

        //get the first page
        PdfPageBase page = doc.getPages().get(0);

        //search a string and get its location where to add the annotation
        PdfTextFind[] find = page.findText("humans").getFinds();
        float x = (float)(find[0].getPosition().getX() - doc.getPageSettings().getMargins().getLeft() + find[0].getSize().getWidth()+20);
        float y = (float)(find[0].getPosition().getY() - doc.getPageSettings().getMargins().getTop()+20);

        //define a text annotation
        Rectangle2D.Float rect = new Rectangle2D.Float(x, y, 150, 20);
        PdfFreeTextAnnotation textAnnotation = new PdfFreeTextAnnotation(rect);

        // set the annotation text, font and format
        textAnnotation.setMarkupText("Spire.PDF text annotation");
        PdfTrueTypeFont font = new PdfTrueTypeFont(new Font("Arial",0, 12));;
        textAnnotation.setFont(font);
        PdfAnnotationBorder border = new PdfAnnotationBorder(0.5f);
        textAnnotation.setBorder(border);
        textAnnotation.setBorderColor(new PdfRGBColor(Color.pink));
        textAnnotation.setColor(new PdfRGBColor(Color.YELLOW));
        textAnnotation.setOpacity(0.75f);
        textAnnotation.setTextMarkupColor(new PdfRGBColor(Color.black));

        //add the annotation to the PDF page
        page.getAnnotationsWidget().add(textAnnotation);

        //Save the document to file
        doc.saveToFile("output/FreeTextAnnotation.pdf");
        doc.close();
    }
}

Effective screenshot after adding a text box annotation to an existing PDF:

Add annotation to PDF in Java

This article demonstrates how to highlight the duplicate and unique values in a selected range through conditional formatting using Spire.XLS for Java.

import com.spire.xls.*;

import java.awt.*;

public class HighlightDuplicates {
    public static void main(String[] args) {

        //Create a Workbook instance
        Workbook workbook = new Workbook();

        //Load a sample Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Use conditional formatting to highlight duplicate values in the range "A2:A11" with red 
        ConditionalFormatWrapper format1 = sheet.getCellRange("A2:A11").getConditionalFormats().addCondition();
        format1.setFormatType(ConditionalFormatType.DuplicateValues);
        format1.setBackColor(Color.red);
        
        //Use conditional formatting to highlight unique values in the range "A2:A11" with yellow
        ConditionalFormatWrapper format2 = sheet.getCellRange("A2:A11").getConditionalFormats().addCondition();
        format2.setFormatType(ConditionalFormatType.UniqueValues);
        format2.setBackColor(Color.yellow);

        //Save the document
        workbook.saveToFile("HighlightDuplicates.xlsx", ExcelVersion.Version2016);
    }
}

Highlight Dulicate and Unique Values in Excel in Java

This article demonstrates how to remove watermark including text watermark and image watermark from a Word document using Spire.Doc for Java.

The following screenshot shows the example Word document which contains an image watermark:

Remove Watermark from Word Document in Java

import com.spire.doc.Document;
import com.spire.doc.FileFormat;

public class RemoveWatermark {
    public static void main(String[] args){
        //Create a Document instance
        Document doc = new Document();
        //Load the Word document
        doc.loadFromFile("ImageWatermark.docx");

        //Set the watermark as null to remove the image (or text) watermark
        doc.setWatermark(null);

        //Save the document
        doc.saveToFile("RemoveWatermark.docx", FileFormat.Docx_2013);
    }
}

The following screenshot shows the output Word document after removing watermark:

Remove Watermark from Word Document in Java

Tuesday, 31 March 2020 08:37

Create Pivot Table in Excel in Java

This article will demonstrate how to create Pivot Table in Excel by Spire.XLS for Java in Java applications.

import com.spire.xls.*;

public class CreatePivotTable {
    public static void main(String[] args)  {

        //Load a sample Excel workbook
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Select the data source range
        CellRange dataRange = sheet.getCellRange("A1:C19");
        PivotCache cache = workbook.getPivotCaches().add(dataRange);

        //Add a PivotTable to the worksheet and set the location and cache of it
        PivotTable pt = sheet.getPivotTables().add("Pivot Table", sheet.getCellRange("E10"), cache);

        //Drag the fields to the row area.
        PivotField pf=null;
        if (pt.getPivotFields().get("Name") instanceof PivotField){
            pf= (PivotField) pt.getPivotFields().get("Name");
        }
        pf.setAxis(AxisTypes.Row);

        PivotField pf2 =null;
        if (pt.getPivotFields().get("Area") instanceof PivotField){
            pf2= (PivotField) pt.getPivotFields().get("Area");
        }
        pf2.setAxis(AxisTypes.Row);

        //Drag the field to the data area.
        pt.getDataFields().add(pt.getPivotFields().get("Population"), "SUM of Count", SubtotalTypes.Sum);

        //Set PivotTable style
        pt.setBuiltInStyle(PivotBuiltInStyles.PivotStyleMedium12);

        //Save the document
        workbook.saveToFile("output/CreatePivotTable.xlsx", ExcelVersion.Version2013);
    }
}

Effective screenshot after creating Pivot Table in Excel:

Create Pivot Table in Excel in Java

This article demonstrates how to delete blank rows and columns in an Excel document using Spire.XLS for Java.

Sample Document

Delete Blank Rows and Columns in Excel in Java

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class DeleteBlankRowsAndColumns {

    public static void main(String[] args) {

        //Load the sample document
        Workbook wb = new Workbook();
        wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx ");

        //Get the first worksheet
        Worksheet sheet = wb.getWorksheets().get(0);

        //Loop through the rows
        for (int i = sheet.getLastRow(); i >= 1; i--)
        {
            //Detect if a row is blank
            if (sheet.getRows()[i-1].isBlank())
            {
                //Remove blank row
                sheet.deleteRow(i);
            }
        }

        //Loop through the columns
        for (int j = sheet.getLastColumn(); j >= 1; j--)
        {
            //Detect if a column is blank
            if (sheet.getColumns()[j-1].isBlank())
            {
                //Remove blank column
                sheet.deleteColumn(j);
            }
        }

        //Save the document
        wb.saveToFile("DeleteBlankRowsAndColumns.xlsx", ExcelVersion.Version2016);
    }
}

Output

Delete Blank Rows and Columns in Excel in Java

Page 3 of 121