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);
    }
}

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);
    }
}

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);

    }
}

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();
            }
        }
    }
}

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);
    }
}

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();
    }
}

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();
            }
        }
    }
}

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();

    }
}

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);
    }
}

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();
    }
}

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();
    }
}

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);
    }
}