Tuesday, 24 December 2019 07:26

Insert and Read Formulas in Excel in Java

This article demonstrates how to insert and read formulas in an Excel document using Spire.XLS for Java.

Insert Formulas

import com.spire.xls.*;

public class InsertFormulas {

    public static void main(String[] args) {

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

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

        //Declare two variables: currentRow、currentFormula
        int currentRow = 1;
        String currentFormula = null;

        //Set the column width
        sheet.setColumnWidth(1, 32);
        sheet.setColumnWidth(2, 16);

        //Write test data into cells
        sheet.getCellRange(currentRow,1).setValue("Test data:");
        sheet.getCellRange(currentRow,2).setNumberValue(1);
        sheet.getCellRange(currentRow,3).setNumberValue(2);
        sheet.getCellRange(currentRow,4).setNumberValue(3);
        sheet.getCellRange(currentRow,5).setNumberValue(4);
        sheet.getCellRange(currentRow,6).setNumberValue(5);

        //Write text in cells
        currentRow += 2;
        sheet.getCellRange(currentRow,1).setValue("Formulas:") ; ;
        sheet.getCellRange(currentRow,2).setValue("result:");

        //Format cells
        CellRange range = sheet.getCellRange(currentRow,1,currentRow,2);
        range.getStyle().getFont().isBold(true);
        range.getStyle().setKnownColor(ExcelColors.LightGreen1);
        range.getStyle().setFillPattern(ExcelPatternType.Solid);
        range.getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Medium);

        //Arithmetic operation
        currentFormula = "=1/2+3*4";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Date function
        currentFormula = "=TODAY()";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);
        sheet.getCellRange(currentRow,2).getStyle().setNumberFormat("YYYY/MM/DD");

        //Time function
        currentFormula = "=NOW()";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);
        sheet.getCellRange(currentRow,2).getStyle().setNumberFormat("H:MM AM/PM");

        //IF function
        currentFormula = "=IF(B1=5,\"Yes\",\"No\")";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //PI function
        currentFormula = "=PI()";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Trigonometric function
        currentFormula = "=SIN(PI()/6)";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Count function
        currentFormula = "=Count(B1:F1)";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Maximum function
        currentFormula = "=MAX(B1:F1)";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Average function
        currentFormula = "=AVERAGE(B1:F1)";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Summation function
        currentFormula = "=SUM(B1:F1)";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Save to file
        workbook.saveToFile("output/InsertFormulas.xlsx",FileFormat.Version2013);
    }
}

Insert and Read Formulas in Excel in Java

Read Formulas

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

public class ReadFormulas {

    public static void main(String[] args) {

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

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

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

        //Loop through the cells within B1:B13
        for (Object cell: sheet.getCellRange("B1:B13")
             ) {
            CellRange cellRange = (CellRange)cell;

            //Detect if a cell range has formula
            if (cellRange.hasFormula()){

                //Print out the cell containing a formula and the formula itself
                String certainCell = String.format("Cell[%d, %d] has a formula: ",cellRange.getRow(),cellRange.getColumn());
                System.out.println(certainCell + cellRange.getFormula());
            }
        }
    }
}

Insert and Read Formulas in Excel in Java

Monday, 23 December 2019 07:52

Create a table in Excel in Java

Spire.XLS for Java supports to create and format the table on Excel file. This article will show you how to create a simple table by the range of data from an existing worksheet.

Firstly, view the sample Excel worksheet:

Create a table in Excel in Java

mport com.spire.xls.*;

public class CreateTable {
    public static void main(String[] args) throws Exception {

        String inputFile = "Sample1.xlsx";
        String outputFile = "output/CreateTable.xlsx";

        //Create a workbook and load a file
        Workbook workbook = new Workbook();
        workbook.loadFromFile(inputFile);

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

        //Create table with the data in given range
        sheet.getListObjects().create("table", sheet.getCellRange(1, 1, 13, 4));

        //Add Default Style to the table.        sheet.getListObjects().get(0).setBuiltInTableStyle(TableBuiltInStyles.TableStyleLight9);

        //Save the Excel file
        workbook.saveToFile(outputFile, ExcelVersion.Version2010);
    }
}

Effective screenshot after creating the Excel table:

Create a table in Excel in Java

Friday, 20 December 2019 07:14

Create Excel File in Java

Spire.XLS for Java is a feature rich API that supports creating, manipulating, converting and printing Excel files in Java applications. In this article, we will show you how to create an Excel file using Spire.XLS for Java.

import com.spire.xls.*;

import java.awt.*;

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

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);
        //Set name for the first worksheet
        sheet.setName("Data Sheet");

        //Create a CellStyle for header cells
        CellStyle style1 = workbook.getStyles().addStyle("Header Style");
        style1.getFont().setSize(12f);
        style1.getFont().setColor(Color.BLACK);
        style1.getFont().isBold(true);
        style1.setHorizontalAlignment(HorizontalAlignType.Center);
        style1.setVerticalAlignment(VerticalAlignType.Center);

        //Create a CellStyle for data cells
        CellStyle style2 = workbook.getStyles().addStyle("Data Style");
        style2.getFont().setSize(10f);
        style2.getFont().setColor(Color.BLACK);

        //Add data and apply style for header cells
        for (int column=1; column<5; column++)
        {
            CellRange header =sheet.getCellRange(1,column);
            header.setValue("Column " + column );
            header.setStyle(style1);
            header.setColumnWidth(15f);
        }

        // Add data and apply style for data cells
        for (int row=2; row<11; row++)
        {
            for (int column=1; column<5; column++)
            {
                CellRange cell = sheet.getCellRange(row, column);
                cell.setValue("Data " + row + ", " + column);
                cell.setStyle(style2);
            }
        }

        //Save the resultant file
        workbook.saveToFile("CreateExcel.xlsx", FileFormat.Version2013);
    }
}

Create Excel File in Java

Friday, 20 December 2019 03:29

Add or remove worksheet in Java

Spire.XLS for Java supports to add a new worksheet to the existing Excel workbook, it also supports to remove a worksheet from the Excel workbook in Java. This article demonstrates how to insert and remove worksheet in Java applications.

Firstly, view the sample Excel Workbook with three sheets:

Add or remove worksheet in Java

Add a worksheet to an existing Excel workbook in Java:

import com.spire.xls.*;

public class ExcelWorksheet {
    public static void main(String[] args) throws Exception {

        String inputFile = "Sample.xlsx";
        String outputFile = "output/AddWorksheet.xlsx";

        //Create a workbook and load a file
        Workbook workbook = new Workbook();
        workbook.loadFromFile(inputFile);

        //Add a new worksheet named "AddedSheet"
        Worksheet sheet = workbook.getWorksheets().add("AddNewSheet");
        sheet.getCellRange("C5").setText("This is a new sheet.");

        //Save the Excel file
        workbook.saveToFile(outputFile, ExcelVersion.Version2010);
    }
}

Effective screenshot after adding a new worksheet:

Add or remove worksheet in Java

Remove a worksheet from Excel workbook in Java:

import com.spire.xls.*;

public class ExcelWorksheet {
    public static void main(String[] args) throws Exception {

        String inputFile = "Sample.xlsx";
        String outputFile = "output/RemoveWorksheet.xlsx";

        //Create a workbook and load a file
        Workbook workbook = new Workbook();
        workbook.loadFromFile(inputFile);

        //remove the second worksheet
        Worksheet sheet1 = workbook.getWorksheets().get(1);
        sheet1.remove();

        //Save the Excel file
        workbook.saveToFile(outputFile, ExcelVersion.Version2010);
    }
}

Effective screenshot after removing the second worksheet from the Excel file:

Add or remove worksheet in Java

Thursday, 19 December 2019 09:46

Print Excel Documents in Java

This article demonstrates how to print Excel documents using Spire.XLS for Java.

import com.spire.xls.Workbook;

import javax.print.PrintService;
import java.awt.print.PageFormat;
import java.awt.print.Paper;
import java.awt.print.PrinterException;
import java.awt.print.PrinterJob;

public class PrintExcel {

    public static void main(String[] args) throws Exception{

        //Create a workbook and load an Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\Sample.xlsx");

        //Create a PrinterJob object
        PrinterJob printerJob = PrinterJob.getPrinterJob();

        //Specify printer name
        PrintService myPrintService = findPrintService("\\\\192.168.1.104\\HP LaserJet P1007");
        printerJob.setPrintService(myPrintService);

        //Create a PageFormat object and set it to the default size and orientation
        PageFormat pageFormat  = printerJob.defaultPage();

        //Return a copy of the Paper object associated with this PageFormat.
        Paper paper = pageFormat .getPaper();

        //Set the imageable area of this Paper.
        paper.setImageableArea(0,0,pageFormat .getWidth(),pageFormat .getHeight());

        //Set the Paper object for this PageFormat.
        pageFormat .setPaper(paper);

        //Set the number of copies
        printerJob .setCopies(1);

        //Call painter to render the pages in the specified format
        printerJob .setPrintable(workbook,pageFormat);

        //execute print
        try {
            printerJob.print();
        } catch (PrinterException e) {
            e.printStackTrace();
        }
    }
    //Get print service by printer name
    private static PrintService findPrintService(String printerName) {

        PrintService[] printServices = PrinterJob.lookupPrintServices();
        for (PrintService printService : printServices) {
            if (printService.getName().equals(printerName)) {
                return printService;
            }
        }
        return null;
    }
}
Friday, 13 December 2019 07:34

Convert Image to PDF in Java

Spire.PDF for Java supports converting multiple image formats such as BMP, JPEG, GIF, PNG, TIFF and ICO to PDF. The following example will show you how to convert an image to PDF using Spire.PDF for Java.

import com.spire.pdf.PdfDocument;
import com.spire.pdf.PdfPageBase;
import com.spire.pdf.graphics.PdfImage;

public class ImageToPDF {
    public static void main(String[] args){
        //Create a PdfDocument instance
        PdfDocument pdf = new PdfDocument();
        //Add a page
        PdfPageBase page = pdf.getPages().add();

        //Load the image
        PdfImage image = PdfImage.fromFile("Hydrangeas.jpg");

        //Draw the image to the specific rectangular area of the page 
        double widthFitRate = image.getPhysicalDimension().getWidth() / page.getCanvas().getClientSize().getWidth();
        double heightFitRate = image.getPhysicalDimension().getHeight() / page.getCanvas().getClientSize().getHeight();
        double fitRate = Math.max(widthFitRate, heightFitRate);
        double fitWidth = image.getPhysicalDimension().getWidth() / fitRate;
        double fitHeight = image.getPhysicalDimension().getHeight() / fitRate;
        page.getCanvas().drawImage(image, new Rectangle2D.Double(0, 0, fitWidth, fitHeight));

        //Save the resultant document
        pdf.saveToFile("ConvertImageToPDF.pdf");
    }
}

Convert Image to PDF in Java

This article demonstrates how to split a PowerPoint document into multiple individual slides using Spire.Presentation for Java.

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

public class SplitPowerPoint {

    public static void main(String[] args) throws Exception {

        //Load the sample PowerPoint file
        Presentation ppt = new Presentation();
        ppt.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.pptx");

        //Loop through the slides
        for (int i = 0; i < ppt.getSlides().getCount(); i++)
        {
            //Create an instance of Presentation class
            Presentation newppt = new Presentation();

            //Remove the default slide
            newppt.getSlides().removeAt(0);

            //Select a slide from the source file and append it to the new document
            newppt.getSlides().append(ppt.getSlides().get(i));

            //Save to file
            newppt.saveToFile(String.format("output/result-%d.pptx",i), FileFormat.PPTX_2013);
        }
    }
}

Output:

Split PowerPoint Document into Individual Slides in Java

Wednesday, 11 December 2019 08:23

Print PDF pages to booklet in C#

Booklets are usually used when we print huge PDF files. It saves paper and make the pages tidy. Starts from Spire.PDF V5.12.3, Spire.PDF supports to print the PDF pages to booklet directly. This article demonstrates how to print PDF pages to booklet in C#.

using Spire.Pdf;
using Spire.Pdf.Actions;
using Spire.Pdf.General;
using Spire.Pdf.Print;

namespace PDFPrintBookLet
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load the sample document
            PdfDocument doc = new PdfDocument();
            doc.LoadFromFile("Sample.pdf");

            //Set booklet layout when print the pdf files
            PdfBookletSubsetMode bookletSubset = PdfBookletSubsetMode.BothSides;
            PdfBookletBindingMode bookletBinding = PdfBookletBindingMode.Left;
            doc.PrintSettings.SelectBookletLayout(bookletSubset, bookletBinding);

            //Print PDF to virtual printer
            doc.PrintSettings.PrinterName = "Microsoft XPS Document Writer";
            doc.PrintSettings.PrintToFile("XpsBooklet.xps");
            doc.Print();  
        }
    }
}

Screenshot after printing to XPS:

Print PDF pages to booklet in C#

Spire.Presentation 4.11 and the later versions provide the following methods to replace text in PowerPoint document:

ReplaceFirstText(string matchedString, string newValue, bool caseSensitive): replace the first occurrence of searched text only.

ReplaceAllText(string matchedString, string newValue, bool caseSensitive): replace all the occurrences of searched text.

In the below examples, we will show you how to use the above methods to replace text in a PowerPoint document.

Replace the first occurrence of searched text only

using Spire.Presentation;

namespace ReplaceFirstText
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Presentation instance
            Presentation ppt = new Presentation();
            //Load the PowerPoint document
            ppt.LoadFromFile("Input.pptx");

            //Get the first slide
            ISlide slide = ppt.Slides[0];

            //Replace the first occurrence of “Spire.Presentation” with “New Text” by using ReplaceFirstText method
            slide.ReplaceFirstText("Spire.Presentation", "New Text", false);

            //Save the resultant document
            ppt.SaveToFile("RplaceFirstText.pptx", FileFormat.Pptx2013);
        }
    }
}

New Method to Replace Text in PowerPoint in C#

Replace all the occurrences of searched text

using Spire.Presentation;

namespace ReplaceAllText
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Presentation instance
            Presentation ppt = new Presentation();
            //Load the PowerPoint document
            ppt.LoadFromFile("Input.pptx");

            //Get the first slide
            ISlide slide = ppt.Slides[0];

            //Replace all the occurrences of “Spire.Presentation” with “New Text” by using ReplaceAllText method
            slide.ReplaceAllText("Spire.Presentation", "New Text", false);

            //Save the resultant document
            ppt.SaveToFile("RplaceAllText.pptx", FileFormat.Pptx2013);
        }
    }
}

New Method to Replace Text in PowerPoint in C#

Wednesday, 04 December 2019 03:42

Highlight Text in PowerPoint in C#

This article demonstrates how to highlight the specific text within a PowerPoint document by using Spire.Presentation for .NET.

using Spire.Presentation;
using System.Drawing;

namespace HighlightTextInPPT
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load the sample PowerPoint file
            Presentation presentation = new Presentation();
            presentation.LoadFromFile(@"C:\Users\Administrator\Desktop\sample.pptx");

            //Loop through the slides
            for (int i = 0; i < presentation.Slides.Count; i++)
            {
                //Get the specific slide
                ISlide slide = presentation.Slides[i];

                //Loop through the shapes
                for (int j = 0; j < slide.Shapes.Count; j++)
                {
                    if (slide.Shapes[j] is IAutoShape)
                    {
                        //Get the specific shape
                        IAutoShape shape = slide.Shapes[j] as IAutoShape;

                        //Initialize an object of TextHighLightingOptions
                        TextHighLightingOptions options = new TextHighLightingOptions();
                        options.CaseSensitive = true;
                        options.WholeWordsOnly = true;

                        //Highligh the specific text within the shape with color
                        shape.TextFrame.HighLightText("Spire.Presentation", Color.LightYellow, options);
                    }
                   
                }
            }

            //Save to file
            presentation.SaveToFile("Highlight Text.pptx", FileFormat.Pptx2013);
        }
    }
}

Highlight Text in PowerPoint in C#

Page 9 of 123