This article demonstrates how to set different header and footer for the fisrt page using Spire.XLS for Java.

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

public class SetDifferentHeaderFooter {

    public static void main(String[] args) {

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

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

        //Insert text in A1 and J1
        sheet.getCellRange("A1").setText("page 1");
        sheet.getCellRange("J1").setText("page 2");

        //Set different first page
        sheet.getPageSetup().setDifferentFirst((byte)1);

        //Set header string and footer string for the first page
        sheet.getPageSetup().setFirstHeaderString("First header");
        sheet.getPageSetup().setFirstFooterString("First footer");

        //Set header string and footer string for other pages
        sheet.getPageSetup().setCenterHeader("Header of other pages");
        sheet.getPageSetup().setCenterFooter("Footer of other pages");

        //Save the document
        workbook.saveToFile("DifferentFirstPage.xlsx", FileFormat.Version2016);
    }
}

Set Different Header and Footer for the First Page in Java

This article will demonstrate how to add the traffic lights icons in Java applications by using Spire.XLS for Java.

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

import java.awt.*;

public class setTrafficLightsIcons {
    public static void main(String[] args) {
        //Create a workbook
        Workbook workbook = new Workbook();

        //Add a worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Add some data to the cell range and set the format for them
        sheet.getCellRange("A1").setText("Traffic Lights");
        sheet.getCellRange("A2").setNumberValue(0.95);
        sheet.getCellRange("A2").setNumberFormat("0%");
        sheet.getCellRange("A3").setNumberValue(0.5);
        sheet.getCellRange("A3").setNumberFormat("0%");
        sheet.getCellRange("A4").setNumberValue(0.1);
        sheet.getCellRange("A4").setNumberFormat("0%");
        sheet.getCellRange("A5").setNumberValue(0.9);
        sheet.getCellRange("A5").setNumberFormat("0%");
        sheet.getCellRange("A6").setNumberValue(0.7);
        sheet.getCellRange("A6").setNumberFormat("0%");
        sheet.getCellRange("A7").setNumberValue(0.6);
        sheet.getCellRange("A7").setNumberFormat("0%");

        //Set the height of row and width of column for Excel cell range
        sheet.getAllocatedRange().setRowHeight(20);
        sheet.getAllocatedRange().setColumnWidth(25);

        //Add a conditional formatting
        XlsConditionalFormats conditional = sheet.getConditionalFormats().add();
        conditional.addRange(sheet.getAllocatedRange());
        IConditionalFormat format1 = conditional.addCondition();

        //Add a conditional formatting of cell range and set its type to CellValue
        format1.setFormatType(ConditionalFormatType.CellValue);
        format1.setFirstFormula("300");
        format1.setOperator(ComparisonOperatorType.Less);
        format1.setFontColor(Color.black);
        format1.setBackColor(Color.lightGray);

        //Add a conditional formatting of cell range and set its type to IconSet
        conditional.addRange(sheet.getAllocatedRange());
        IConditionalFormat format = conditional.addCondition();
        format.setFormatType(ConditionalFormatType.IconSet);
        format.getIconSet().setIconSetType(IconSetType.ThreeTrafficLights1);

        //Save to file
        String result = "output/setTrafficLightsIcons_result.xlsx";
        workbook.saveToFile(result, ExcelVersion.Version2013);
    }
}

Effective screenshot of traffic lights icons on Excel worksheet:

Java add the traffic lights icons to Excel

Friday, 24 April 2020 08:06

Add picture to Excel chart in Java

This article will introduce how to add a picture to Excel chart in java applications by using Spire.XLS for java.

import com.spire.xls.*;
import com.spire.xls.core.IPictureShape;
import com.spire.xls.core.IShape;
import java.awt.*;

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

        //Load the document from disk
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx");

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

        //get the first chart
        Chart chart = sheet.getCharts().get(0);

        //add the picture to chart and set its format
        IShape picture = chart.getShapes().addPicture("48.png");
        ((IPictureShape) picture).getLine().setDashStyle(ShapeDashLineStyleType.DashDotDot);
        ((IPictureShape) picture).getLine().setForeColor(Color.blue);

         //save the document 
        String result = "output/AddPictureToChart.xlsx";
        workbook.saveToFile(result, ExcelVersion.Version2010);

    }
}

Effective screenshot after adding picture to Excel Chart:

Add picture to Excel chart in Java

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