Thursday, 27 February 2020 08:56

Convert Excel to Image in Java

This article demonstrates how to convert Excel to Image using Spire.XLS for Java.

Below screenshot shows the input Excel file we used for demonstration:

Convert Excel to Image in Java

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

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

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

        //Save the sheet to image
        sheet.saveToImage("image.png");
    }
}

The output image file:

Convert Excel to Image in Java

Wednesday, 26 February 2020 02:05

Java freeze panes for Excel worksheet

When we view an Excel with a large table of data, freezing rows or columns can help us to check the content conveniently. The freeze pane can keep rows or columns visible while scrolling through the rest of the worksheet. This article will demonstrate how to freeze pane for Excel worksheet in Java applications.

It supports to freeze the top row, freeze the top column or freeze some rows and columns together. We will use freeze top row for example.

import com.spire.xls.*;

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

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

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

        //Freeze Top Row
        sheet.freezePanes(2,1);

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

Effective screenshot after freezing the first row:

Java freeze panes for Excel worksheet

Tuesday, 25 February 2020 14:37

Set Number Format in Excel in Java

Number formatting is a way to control how a number is displayed using numeric format string. For example, you can use format string '0.00' to format 1234.5678 as 1234.57. Numeric format strings often consist of one or more custom numeric specifiers listed as below:

  • "#" - Digit placeholder
  • "0" - Zero placeholder
  • "," - Decimal point
  • "." - Decimal separator
  • "[Red]" - Color specifier
  • "%" - Percentage placeholder

The following example demonstrates how to set number format in Excel using Spire.XLS for Java.

import com.spire.xls.*;

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

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

        //Add a string to cell “B1”
        sheet.getCellRange("B1").setText("NUMBER FORMATTING");
        sheet.getCellRange("B1").getCellStyle().getExcelFont().isBold(true);
        sheet.getCellRange("B1:C1").merge();
        sheet.getCellRange("B1:C1").setHorizontalAlignment(HorizontalAlignType.Center);

        
        //Add a string to cell “B3”
        sheet.getCellRange("B3").setText("0");
       //Add a number to cell “C3” and set the number format
        sheet.getCellRange("C3").setNumberValue(1234.5678);
        sheet.getCellRange("C3").setNumberFormat("0");

        //Repeat the above step to add string and number to other cells and set the number format
        sheet.getCellRange("B4").setText("0.00");
        sheet.getCellRange("C4").setNumberValue(1234.5678);
        sheet.getCellRange("C4").setNumberFormat("0.00");

        sheet.getCellRange("B5").setText("#,##0.00");
        sheet.getCellRange("C5").setNumberValue(1234.5678);
        sheet.getCellRange("C5").setNumberFormat("#,##0.00");

        sheet.getCellRange("B6").setText("$#,##0.00");
        sheet.getCellRange("C6").setNumberValue(1234.5678);
        sheet.getCellRange("C6").setNumberFormat("$#,##0.00");

        sheet.getCellRange("B7").setText("0;[Red]-0");
        sheet.getCellRange("C7").setNumberValue(-1234.5678);
        sheet.getCellRange("C7").setNumberFormat("0;[Red]-0");

        sheet.getCellRange("B8").setText("0.00;[Red]-0.00");
        sheet.getCellRange("C8").setNumberValue(-1234.5678);
        sheet.getCellRange("C8").setNumberFormat("0.00;[Red]-0.00");

        sheet.getCellRange("B9").setText("#,##0;[Red]-#,##0");
        sheet.getCellRange("C9").setNumberValue(-1234.5678);
        sheet.getCellRange("C9").setNumberFormat("#,##0;[Red]-#,##0");

        sheet.getCellRange("B10").setText("#,##0.00;[Red]-#,##0.000");
        sheet.getCellRange("C10").setNumberValue(-1234.5678);
        sheet.getCellRange("C10").setNumberFormat("#,##0.00;[Red]-#,##0.00");

        sheet.getCellRange("B11").setText("0.00E+00");
        sheet.getCellRange("C11").setNumberValue(1234.5678);
        sheet.getCellRange("C11").setNumberFormat("0.00E+00");

        sheet.getCellRange("B12").setText("0.00%");
        sheet.getCellRange("C12").setNumberValue(1234.5678);
        sheet.getCellRange("C12").setNumberFormat("0.00%");

        //Set background color for specified cells 
        sheet.getCellRange("B3:B12").getCellStyle().setKnownColor(ExcelColors.Gray25Percent);
        sheet.getCellRange("C3:C12").getCellStyle().setKnownColor(ExcelColors.Gray50Percent);

        //Set column width for specified columns
        sheet.setColumnWidth(2, 24);
        sheet.setColumnWidth(3, 24);

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

The following screenshot shows the output Excel files after setting number format:

Set Number Format in Excel in Java

Monday, 24 February 2020 07:45

Alternate Row Colors in Excel in Java

This article demonstrates how to set alternate colors for Excel rows through conditional formatting using Spire.XLS for Java.

import com.spire.xls.*;

import java.awt.*;

public class ConditionalFormatting {

    public static void main(String[] args) {

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

        //Load a sample document
        workbook.loadFromFile("C:\\Users\\Jack\\Desktop\\Example.xlsx");

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

        //Get the data range
        CellRange dataRange = sheet.getAllocatedRange();

        //Set the back color of even rows to light gray using conditional formatting
        ConditionalFormatWrapper format1 = dataRange.getConditionalFormats().addCondition();
        format1.setFirstFormula("=MOD(ROW(),2)=0");
        format1.setFormatType(ConditionalFormatType.Formula);
        format1.setBackColor(Color.lightGray);

        //Set the back color of odd rows to yellow using conditional formatting
        ConditionalFormatWrapper format2 = dataRange.getConditionalFormats().addCondition();
        format2.setFirstFormula("=MOD(ROW(),2)=1");
        format2.setFormatType(ConditionalFormatType.Formula);
        format2.setBackColor(Color.yellow);

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

Alternate Row Colors in Excel in Java

Friday, 21 February 2020 07:13

Add Borders to Cells in Excel in Java

This article demonstrates how to add borders to cells in an Excel file using Spire.XLS for Java.

The following screenshot shows the input Excel file before adding borders:

Add Borders to Cells in Excel in Java

import com.spire.xls.*;
import java.awt.*;

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

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

        //Get the cell range that you want to apply border style
        CellRange cr = sheet.getCellRange(sheet.getFirstRow(), sheet.getFirstColumn(), sheet.getLastRow(), sheet.getLastColumn());

        //Apply border style 
        cr.getBorders().setLineStyle(LineStyleType.Double);
        cr.getBorders().getByBordersLineType(BordersLineType.DiagonalDown).setLineStyle(LineStyleType.None);
        cr.getBorders().getByBordersLineType(BordersLineType.DiagonalUp).setLineStyle(LineStyleType.None);
        cr.getBorders().setColor(Color.BLUE);

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

The following screenshot shows the output Excel file after adding borders:

Add Borders to Cells in Excel in Java

Wednesday, 19 February 2020 08:35

Align Text in Excel Cells in Java

This article demonstrates how to align text in Excel cells using Spire.XLS for Java.

import com.spire.xls.*;

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

        //Load an Excel file
        workbook.loadFromFile("Sample.xlsx");

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

        //Set the vertical alignment to Top
        sheet.getCellRange("B1").getCellStyle().setVerticalAlignment(VerticalAlignType.Top);
        //Set the vertical alignment to Center
        sheet.getCellRange("B2").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
        //Set the vertical alignment to Bottom
        sheet.getCellRange("B3").getCellStyle().setVerticalAlignment(VerticalAlignType.Bottom);

        //Set the horizontal alignment to General
        sheet.getCellRange("B4").getCellStyle().setHorizontalAlignment(HorizontalAlignType.General);
        //Set the horizontal alignment to Left
        sheet.getCellRange("B5").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Left);
        //Set the horizontal alignment to Center
        sheet.getCellRange("B6").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);
        //Set the horizontal alignment to Right
        sheet.getCellRange("B7").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Right);

        //Set the text orientation by using setRotation method
        sheet.getCellRange("B8").getCellStyle().setRotation(45);
        sheet.getCellRange("B9").getCellStyle().setRotation(90);

        //Set the text indentation
        sheet.getCellRange("B10").getCellStyle().setIndentLevel(6);

        //Set the text direction
        sheet.getCellRange("B11").getCellStyle().setReadingOrder(ReadingOrderType.LeftToRight);

        //Set the row height 
        sheet.getCellRange("B8").setRowHeight(60);
        sheet.getCellRange("B9").setRowHeight(60);

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

Output:

Align Text in Excel Cells in Java

Wednesday, 19 February 2020 06:28

Java unprotect the Excel file

We have already demonstrated how to protect the Excel file in Java; this article will show you how to unprotect the Excel workbook or a single worksheet in Java applications.

Unprotect the Excel workbook:

import com.spire.xls.*;

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

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

        //Use the password to open the sample document
        workbook.setOpenPassword("E-iceblue");
        workbook.loadFromFile("ProtectWorkbook.xlsx");

        //Unprotect the whole workbook
        workbook.unProtect();

        //Save the document to file
        workbook.saveToFile("UnprotectWb.xlsx");
        workbook.dispose();
    }
}

Unprotect a single Excel worksheet:

import com.spire.xls.*;

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

        //Create a workbook and load a sample file with protected worksheet
        Workbook workbook = new Workbook();
        workbook.loadFromFile("ProtectWS.xlsx");
        
        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Unprotect the first worksheet
        sheet.unprotect("iceblue");

        //Save the document to file
        workbook.saveToFile("Unprotectworksheet.xlsx");
        workbook.dispose();
    }
}
Monday, 17 February 2020 09:24

Apply Fonts in Excel in Java

This article demonstrates how to apply font styles to Excel cells using Spire.XLS for Java.

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

import java.awt.*;

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

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

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

        //Set font name
        sheet.getCellRange("B1").setText("Font name: Comic Sans MS");
        sheet.getCellRange("B1").getCellStyle().getExcelFont().setFontName("Comic Sans MS");

        //Set font size
        sheet.getCellRange("B2").setText("Font size: 20");
        sheet.getCellRange("B2").getCellStyle().getExcelFont().setSize(20);

        //Set font color
        sheet.getCellRange("B3").setText("Font color: Blue");
        sheet.getCellRange("B3").getCellStyle().getExcelFont().setColor(Color.blue);

        //Set to bold
        sheet.getCellRange("B4").setText("Font style: Bold");
        sheet.getCellRange("B4").getCellStyle().getExcelFont().isBold(true);

        //Set to underline
        sheet.getCellRange("B5").setText("Font style: Underline");
        sheet.getCellRange("B5").getCellStyle().getExcelFont().setUnderline(FontUnderlineType.Single);

        //Set to italic
        sheet.getCellRange("B6").setText("Font style: Italic");
        sheet.getCellRange("B6").getCellStyle().getExcelFont().isItalic(true);

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

Apply Fonts in Excel in Java

Monday, 17 February 2020 08:49

Convert Excel Chart to Image in Java

This article demonstrates how to convert an Excel chart to image using Spire.XLS for Java.

The example Excel file:

Convert Excel Chart to Image in Java

import com.spire.xls.Workbook;

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

public class ChartToImage {
    public static void main(String[] args) throws IOException {
        Workbook workbook = new Workbook();
        //Load the Excel file
        workbook.loadFromFile("Chart.xlsx");

        //Save the first chart in the first worksheet as image
        BufferedImage image= workbook.saveChartAsImage(workbook.getWorksheets().get(0), 0);
        ImageIO.write(image,"png", new File("ChartToImage.png"));
    }
}

Output:

Convert Excel Chart to Image in Java

Friday, 14 February 2020 07:39

Add Comment to Specific Text in Word in Java

Spire.Doc for Java supports adding comment to a paragraph as well as adding comment to a specific text in a Word document. This article demonstrates how to add comment to a specific text in a Word document using Spire.Doc for Java.

import com.spire.doc.Document;
import com.spire.doc.FileFormat;
import com.spire.doc.documents.CommentMark;
import com.spire.doc.documents.CommentMarkType;
import com.spire.doc.documents.Paragraph;
import com.spire.doc.documents.TextSelection;
import com.spire.doc.fields.Comment;

public class AddComments {
    public static void main(String[] args){
        //Load a Word document
        Document doc = new Document();
        doc.loadFromFile("input.docx");

        //Find the string “Spire.Doc for Java”  in the document
        TextSelection[] finds = doc.findAllString("Spire.Doc for Java", false, true);

        for (TextSelection find : finds) {
            //Create comment start mark and comment end mark
            CommentMark commentmarkStart = new CommentMark(doc);
            commentmarkStart.setType(CommentMarkType.Comment_Start);
            CommentMark commentmarkEnd = new CommentMark(doc);
            commentmarkEnd.setType(CommentMarkType.Comment_End);

            //Create a comment
            Comment comment = new Comment(doc);
            comment.getBody().addParagraph().setText("Developed by E-iceblue");
            comment.getFormat().setAuthor("Shaun");

            //Find the paragraph where the string is located in
            Paragraph para = find.getAsOneRange().getOwnerParagraph();
            //Get the index of the string in the paragraph
            int index = para.getChildObjects().indexOf(find.getAsOneRange());

            //Add the comment to the paragraph
            para.getChildObjects().add(comment);
            //Insert the comment start mark and comment end mark to the paragraph based on the index
            para.getChildObjects().insert(index, commentmarkStart);
            para.getChildObjects().insert(index + 2, commentmarkEnd);
        }

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

Output:

Add Comment to Specific Text in Word in Java