Wednesday, 18 March 2020 08:08

Java set zoom factor on Excel worksheet

The excel zoom factor could help us to display the data on Excel worksheet clearly or completely. This article will demonstrate how to set the zoom factor on Excel work sheet in Java application by Spire.XLS for Java.

import com.spire.xls.*;

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

        //Create a workbook and load a file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx");

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

        //Set the zoom factor of the sheet to 150
        sheet.setZoom(150);

        //Save the Excel file
        workbook.saveToFile("Zoomfactor.xlsx", ExcelVersion.Version2010);
    }
}

Effective screenshot after setting the zoom factor of the sheet to 150.

Java set zoom factor on Excel worksheet

This article demonstrates how to create chart with non-contiguous data in Excel using Spire.XLS for Java.

The example Excel file:

Create Chart with Non-Contiguous Data in Excel in Java

import com.spire.xls.*;
import com.spire.xls.charts.ChartSerie;

import java.awt.*;

public class ChartWithNonContiguousData {
    public static void main(String[] args){
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load the Excel file
        workbook.loadFromFile("NonContiguousData.xlsx");

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

        //Add a clustered column chart to the worksheet
        Chart chart = sheet.getCharts().add(ExcelChartType.ColumnClustered);
        chart.setSeriesDataFromRange(false);
        //Set chart position
        chart.setLeftColumn(1);
        chart.setTopRow(10);
        chart.setRightColumn(10);
        chart.setBottomRow(24);

        //Add a series to the chart
        ChartSerie cs1 = (ChartSerie)chart.getSeries().add();
        //Set series name
        cs1.setName(sheet.getCellRange("B1").getValue());
        //Set category labels for the series using non-contiguous data
        cs1.setCategoryLabels(sheet.getCellRange("A2:A3").addCombinedRange(sheet.getCellRange("A5:A6"))
.addCombinedRange(sheet.getCellRange("A8:A9")));
        //Set values for the series using non-contiguous data
        cs1.setValues(sheet.getCellRange("B2:B3").addCombinedRange(sheet.getCellRange("B5:B6"))
.addCombinedRange(sheet.getCellRange("B8:B9")));
        //Specify the series type
        cs1.setSerieType(ExcelChartType.ColumnClustered);

        //Add a series to the chart
        ChartSerie cs2 = (ChartSerie)chart.getSeries().add();
        //Set series name
        cs2.setName(sheet.getCellRange("C1").getValue());
        //Set category labels for the series using non-contiguous data
        cs2.setCategoryLabels(sheet.getCellRange("A2:A3").addCombinedRange(sheet.getCellRange("A5:A6"))
.addCombinedRange(sheet.getCellRange("A8:A9")));
        //Set values for the series using non-contiguous data
        cs2.setValues(sheet.getCellRange("C2:C3").addCombinedRange(sheet.getCellRange("C5:C6"))
.addCombinedRange(sheet.getCellRange("C8:C9")));
        //Specify the series type
        cs2.setSerieType(ExcelChartType.ColumnClustered);

        //Set chart title
        chart.setChartTitle("Chart");
        chart.getChartTitleArea().getFont().setSize(20);
        chart.getChartTitleArea().setColor(Color.black);
        
        chart.getPrimaryValueAxis().hasMajorGridLines(false);

        //Save the result file
        workbook.saveToFile("Chart.xlsx", ExcelVersion.Version2013);
    }
}

Output:

Create Chart with Non-Contiguous Data in Excel in Java

This article demonstrates how to freeze or unfreeze rows and columns in Excel using Spire.XLS for Java.

Freeze top row

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

//Freeze top row
sheet.freezePanes(2,1);

//Save to file
workbook.saveToFile("FreezeTopRow.xlsx", ExcelVersion.Version2016);

Freeze or Unfreeze Excel Rows and Columns in Java

Freeze fisrt column

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

//Freeze frist column
sheet.freezePanes(1,2);

//Save to file
workbook.saveToFile("FreezeFirstColumn.xlsx", ExcelVersion.Version2016);

Freeze or Unfreeze Excel Rows and Columns in Java

Freeze few rows and columns

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

//Freeze the second row and the second column
sheet.freezePanes(3,3);

//Save to file
workbook.saveToFile("FreezeFewRowsAndColumns.xlsx", ExcelVersion.Version2016);

Freeze or Unfreeze Excel Rows and Columns in Java

Unfreeze panes

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

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

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

//Unfreeze panes
sheet.removePanes();

//Save to file
workbook.saveToFile("UnfreezePanes.xlsx", ExcelVersion.Version2016);
Wednesday, 04 March 2020 02:55

Insert Image Hyperlinks in Excel in Java

This article demonstrates how to insert an image into Excel and add a hyperlink to it using Spire.XLS for Java.

import com.spire.xls.*;

public class InsertImageHyperlink {

    public static void main(String[] args) {

        //Create a Workbook instance and get the first worksheet
        Workbook wb = new Workbook();
        Worksheet sheet = wb.getWorksheets().get(0);

        //Insert text in the cell A1
        sheet.getCellRange("A1").setText("Image Hyperlink");
        sheet.getCellRange("A1").getStyle().setVerticalAlignment(VerticalAlignType.Top);

        //Insert an image to the cell A1
        String picPath = "C:\\Users\\Administrator\\Desktop\\logo.png";
        ExcelPicture picture = sheet.getPictures().add(1,1,picPath);

        //Set a hyperlink for the image
        picture.setHyperLink("Http://www.e-iceblue.com", true);

        //Set the column width and row height
        sheet.getColumns()[0].setColumnWidth(22);
        sheet.getRows()[0].setRowHeight(140);
        picture.setTopRowOffset(25);

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

Insert Image Hyperlinks in Excel in Java

This article demonstrates how to set background color and background image for Excel using Spire.XLS for Java.

Set background color

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

import java.awt.*;

public class BackgroundColor{
    public static void main(String[] args){
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("input.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);
        //Set background color for the used cell range in the worksheet
        sheet.getAllocatedRange().getStyle().setColor(Color.green);
        //Set background color for a specified cell range in the worksheet
        //sheet.getCellRange("A1:E19").getStyle().setColor(Color.yellow);

        //Save the file
        workbook.saveToFile("SetBackColor.xlsx", ExcelVersion.Version2013);
    }
}

Set Background Color and Image for Excel in Java

Set background image

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

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;

public class BackgroundImage {
    public static void main(String[] args) throws IOException {
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("input.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);
        //Load an image
        BufferedImage image = ImageIO.read( new File("background.jpg"));
        //Set the image as the background image of the worksheet
        sheet.getPageSetup().setBackgoundImage(image);

        //Save the file
        workbook.saveToFile("SetBackImage.xlsx", ExcelVersion.Version2013);
    }
}

Set Background Color and Image for Excel in Java

Monday, 02 March 2020 10:04

Java insert textbox to Excel worksheet

This article will demonstrate how to add textbox into Excel worksheet with Spire.XLS for Java. We could fill in the textbox with text and image.

import com.spire.xls.*;
import com.spire.xls.core.ITextBox;
import com.spire.xls.core.ITextBoxShape;

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

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

        //Get the first sheet
        Worksheet sheet = workbook.getWorksheets().get(0);        
        
        //Insert the textbox with text
        ITextBox textBox = sheet.getTextBoxes().addTextBox(5, 3, 128, 196);
        textBox.setText("Insert TextBox in Excel");
        textBox.setHAlignment(CommentHAlignType.Center);
        textBox.setVAlignment(CommentVAlignType.Center);

        //Insert the textbox with picture
        ITextBoxShape shape = sheet.getTextBoxes().addTextBox(5, 8, 128, 196);
        shape.getFill().customPicture("logo.png");
        shape.getFill().setFillType(ShapeFillType.Picture);

        //Save the Excel file
        workbook.saveToFile("output/TextBox.xlsx", ExcelVersion.Version2010);
    }
}

Effective screenshot after adding textbox with text and picture in Excel worksheet:

Java insert textbox to Excel worksheet

Thursday, 06 June 2019 09:15

Merge Word Documents in Java

This article demonstrates how to merge Word documents using Spire.Doc for Java.

Document class provides an insertTextFromFile() method that let us merge Word documents by inserting content from a source document into a destination document. In the result document, the inserted content will start from a new page.

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

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

        //File path of the first document
        String filePath1 = "merge1.docx";
        //File path of the second document
        String filePath2 = "merge2.docx";

        //Load the first document
        Document document = new Document(filePath1);

        //Insert content of the second document into the first document
        document.insertTextFromFile(filePath2, FileFormat.Docx_2013);

        //Save the resultant document
        document.saveToFile("Output.docx", FileFormat.Docx_2013);       

    }
}

Output:

Merge Word Documents in Java

If you want the inserted content to follow the last paragraph of the destination document instead of starting from a new page, use the below method to clone sections of the source document and then append to the last section of the destination document.

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

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

        //File path of the first document
        String filePath1 = "merge1.docx";
        //File path of the second document
        String filePath2 = "merge2.docx";

        //Load the first document
        Document document1 = new Document(filePath1);
        //Load the second document
        Document document2 = new Document(filePath2);

        //Get the last section of the first document
        Section lastSection = document1.getLastSection();

        //Add the sections of the second document to the last section of the first document
        for (Section section:(Iterable )document2.getSections()) {
            for (DocumentObject obj:(Iterable )section.getBody().getChildObjects()
                 ) {
                lastSection.getBody().getChildObjects().add(obj.deepClone());
            }
        }

        //Save the resultant document
        document1.saveToFile("Output.docx", FileFormat.Docx_2013);

    }
}

Output:

Merge Word Documents in Java

Wednesday, 05 June 2019 08:57

Insert footnote to word in Java

This article demonstrates how to insert footnote to a word document in Java applications with the help of Spire.Doc. We will show you how to add footnote by the following two parts.

  • Add a footnote to a paragraph in the word document.
  • Find a specified text string and insert footnote behinds it in the word document.

Add a footnote to the first paragraph in word:

import com.spire.doc.*;
import com.spire.doc.documents.*;
import com.spire.doc.fields.*;
import java.awt.*;


public class WordFootnote {
    public static void main(String[] args) throws Exception {
        //load sample word document
        Document doc = new Document();
        doc.loadFromFile("Sample.docx", FileFormat.Docx_2010);

        //get the first paragraph from the first section
        Paragraph para = doc.getSections().get(0).getParagraphs().get(0);

        //Add footnote to the first paragraph
        Footnote footnote = para.appendFootnote(FootnoteType.Footnote);

        //Add the text and format for it
        TextRange text = footnote.getTextBody().addParagraph().appendText("Demo of Spire.Doc");
        text.getCharacterFormat().setFontName("Arial Black");
        text.getCharacterFormat().setFontSize(10);
        text.getCharacterFormat().setTextColor(new Color(255, 140, 0));

        //set the format for footnote marker
        footnote.getMarkerCharacterFormat().setFontName("Calibri");
        footnote.getMarkerCharacterFormat().setFontSize(12);
        footnote.getMarkerCharacterFormat().setBold(true);
        footnote.getMarkerCharacterFormat().setTextColor(new Color(0, 0, 139));

        // save the document to file
        doc.saveToFile("output/Addfootnote.docx", FileFormat.Docx_2010);

        }
    }

Effective screenshot after insert a footnote in the paragraph:

Insert footnote to word in Java

Find “Spire.Doc” and insert footnote behinds it in the word document

import com.spire.doc.*;
import com.spire.doc.documents.*;
import com.spire.doc.fields.*;
import java.awt.*;


public class WordFootnotes {
    public static void main(String[] args) throws Exception {
        //load sample word document
        Document doc = new Document();
        doc.loadFromFile("Sample.docx", FileFormat.Docx_2010);
        
        //find text string Spire.Doc in the whole word document
        TextSelection[] selections = doc.findAllString("Spire.Doc", false, true);
        for (TextSelection selection : selections) {
            TextRange range = selection.getAsOneRange();
            Paragraph para = range.getOwnerParagraph();

            //Add footnote behind the searched text strings
            Footnote footnote = para.appendFootnote(FootnoteType.Footnote);
            int index = para.getChildObjects().indexOf(range);

            para.getChildObjects().insert(index + 1, footnote);

            //Add the text and format for it
            TextRange text = footnote.getTextBody().addParagraph().appendText("Demo of Spire.Doc");
            text.getCharacterFormat().setFontName("Arial Black");
            text.getCharacterFormat().setFontSize(10);
            text.getCharacterFormat().setTextColor(new Color(255, 140, 0));
            //set the format for footnote marker
            footnote.getMarkerCharacterFormat().setFontName("Calibri");
            footnote.getMarkerCharacterFormat().setFontSize(12);
            footnote.getMarkerCharacterFormat().setBold(true);
            footnote.getMarkerCharacterFormat().setTextColor(new Color(0, 0, 139));

            // save the document to file
            doc.saveToFile("output/Addfootnote.docx", FileFormat.Docx_2010);

        }
    }
}

Effective screenshot after insert footnotes to the searched text strings:

Insert footnote to word in Java

Tuesday, 04 June 2019 05:53

Print Word Documents in Java

This article gives you an example of how to print Word documents using Spire.Doc for Java.

import com.spire.doc.Document;
import java.awt.print.PageFormat;
import java.awt.print.Paper;
import java.awt.print.PrinterException;
import java.awt.print.PrinterJob;

public class PrintWord {

    public static void main(String[] args) {

        //load a Word document that you want to print
        Document document = new Document();
        document.loadFromFile("C:\\Users\\Administrator\\Desktop\\DocoumentToPrint.docx");

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

        //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 number of copies
        printerJob.setCopies(1);

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

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

        //Execute print
        try {
            printerJob.print();
        } catch (PrinterException e) {
            e.printStackTrace();
        }
    }
}
Thursday, 08 November 2018 07:59

How to convert Word to PostScript in C#

PostScript is a page description language that is an industry standard for outputting high-resolution text and graphics. From Version 6.11.2, Spire.Doc supports to convert doc/docx to a postscript file in both WinForm app and ASP.NET app. This article will demonstrate how to convert word to PostScript in C# and VB.NET.

Firstly, view the sample word document:

How to convert Word to PostScript in C#

[C#]
using Spire.Doc;

namespace Word
{
    class Program
    {
        static void Main(string[] args)
        {            
            Document doc = new Document();
            doc.LoadFromFile("Sample.docx", FileFormat.Docx2010);
            doc.SaveToFile("Result.ps", FileFormat.PostScript);                 
        }
    }
}
[VB.NET]
Imports Spire.Doc
Namespace Word
    Class Program
        Private Shared Sub Main(ByVal args As String())
            Dim doc As Document = New Document()
            doc.LoadFromFile("Sample.docx", FileFormat.Docx2010)
            doc.SaveToFile("Result.ps", FileFormat.PostScript)
        End Sub
    End Class
End Namespace

Effective screenshot of the resulted PostScript file converted from .docx document:

How to convert Word to PostScript in C#

Page 1 of 7