Identify Merged Cells in Excel in Java

When there are merged cells in your worksheet, you may experience problems performing certain operations, such as sorting data. For such cases, Microsoft Excel provides a built-in feature – “Find and Replace” – that allows you to find merged cells within a worksheet. Once the merged cells are found, you can process them further, for example, highlighting them or unmerging them. In this article, I will demonstrate how to achieve this task programmatically in Java using Free Spire.XLS for Java API.

Add Dependencies

To begin with, you need to add needed dependencies for including Free Spire.XLS for Java library into your Java project. You can either download the library’s jar from the official website or install it from Maven by adding the following code to your maven-based project’s pom.xml file.

<repositories>    
    <repository>    
        <id>com.e-iceblue</id>    
        <name>e-iceblue</name>    
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>    
    </repository>    
</repositories>    
<dependencies>    
    <dependency>    
        <groupId> e-iceblue </groupId>    
        <artifactId>spire.xls.free</artifactId>    
        <version>3.9.1</version>    
    </dependency>    
</dependencies>

Identify Merged Cells in Excel

The following are the steps to identify merged cells in an Excel worksheet:

• Create an instance of Workbook class and load the Excel file using Workbook.loadFromFile() method.
• Get the desired worksheet using Workbook.getWorksheets().get() method.
• Get the merged range of cells into a CellRange array using Worksheet.getMergedCells() method.
• Loop through the array, highlight the merged cells using CellRange.getCellStyle().setColor() method.
• Save the result file using Workbook.saveToFile() method.

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

import java.awt.*;

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

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

        //Get the merged cell ranges in the worksheet into a CellRange array
        CellRange[] range = sheet.getMergedCells();

        //Traverse through the array
        for(CellRange cell : range){
            //Highlight the merged cells
            cell.getCellStyle().setColor(Color.YELLOW);

            //Unmerge the merged cells
            //cell.unMerge();
        }

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

The result file after highlighting merged cells:

Identify Merged Cells in Excel in Java

See Also: Merge and Unmerge Excel Cells in Java

Merge and Unmerge Excel Cells in Java

In Microsoft Excel, you can easily combine two or more cells into a single cell by clicking the “Merge and Center” button, you can also unmerge them into separate cells by clicking the button again. In this article, I will introduce how to implement this task programmatically in Java using Free Spire.XLS for Java API.

Add Dependencies

If you use maven, specify the following dependencies for Free Spire.XLS for Java API in your project’s pom.xml file.

<repositories>    
    <repository>    
        <id>com.e-iceblue</id>    
        <name>e-iceblue</name>    
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>    
    </repository>    
</repositories>    
<dependencies>    
    <dependency>    
        <groupId> e-iceblue </groupId>    
        <artifactId>spire.xls.free</artifactId>    
        <version>3.9.1</version>    
    </dependency>    
</dependencies>

For non-maven projects, download Free Spire.XLS for Java pack from the official website and add Spire.Xls.jar in the lib folder into your project as a dependency.

Merge and Unmerge Cells

The following are the steps to merge or unmerge cells in an Excel file:

• Create a Workbook instance.
• Load an Excel file using Workbook.loadFromFile() method.
• Get the desired worksheet using Workbook.getWorksheets().get() method.
• Get the desired range of cells using Worksheet.getRanges().get() method.
• Merge or unmerge the cells using XlsRange.merge() or XlsRange.unMerge() method.
• Save the result file using Workbook.saveToFile() method.

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

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

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

        //Get the desired range of cells
        CellRange range = worksheet.getRange().get("A1:E1");
        //Merge the cells
        range.merge();

        //Get the desired range of cells
        range = worksheet.getRange().get("A3:A7");
        //Unmerge the cells
        range.unMerge();

        //Merge a specific (seventh) column
        //worksheet.getColumns()[6].merge();

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

Merge and Unmerge Excel Cells in Java

See Also: Identify Merged Cells in Excel in Java

Read Values from Formulas in Excel in Java

Introduction

When reading Excel files, you may come across situations where one or more cells contain formulas instead of raw data. For such cases, this article will demonstrate how to read data (including formulas and the actual values) from these cells programmatically in Java using Free Spire.XLS for Java library.

Add Dependencies

If you use maven, specify the following dependencies for Free Spire.XLS for Java library in your project’s pom.xml file.

<repositories>    
    <repository>    
        <id>com.e-iceblue</id>    
        <name>e-iceblue</name>    
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>    
    </repository>    
</repositories>    
<dependencies>    
    <dependency>    
        <groupId> e-iceblue </groupId>    
        <artifactId>spire.xls.free</artifactId>    
        <version>3.9.1</version>    
    </dependency>    
</dependencies>

For non-maven projects, download Free Spire.XLS for Java pack from the official website and add Spire.Xls.jar in the lib folder into your project as a dependency.

Read Formulas and the Actual Values

To read formulas, you can use the CellRange.getFormula() method. To get actual values from formulas, you can use the CellRange.getFormulaValue() method.

The following are the steps to read all of the formulas and the actual values from an Excel file:

• Create a Workbook instance and load an Excel file using Workbook.loadFromFile() method.
• Get the desired worksheet using Workbook.getWorksheets.get() method.
• Loop through the rows and columns in the worksheet.
• Get the current cell using Worksheet.getCellRange(int row, int column) method.
• Detect if the cell has formula using CellRange.hasFormula() method, if yes, get the formula using CellRange.getFormula() method and the actual values using CellRange.getFormulaValue() method.

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

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

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

        //Get the row count
        int maxRow = sheet.getLastRow();
        //Get the column count
        int maxColumn = sheet.getLastColumn();

        //Loop through the rows
        for (int row = 1; row <= maxRow; row++)
        {
            //Loop through the columns
            for (int col = 1; col <= maxColumn; col++)
            {
                //Get the current cell
                CellRange cell = sheet.getCellRange(row, col);
                //Detect if the cell has formula
                if(cell.hasFormula()) {
                    //Print out the formula and the actual data value
                    System.out.print("Formula: " + cell.getFormula() + "\t\t" +  "Value: " + cell.getFormulaValue().toString() + "\t\t");
                }
            }
            System.out.print("\n");
        }
    }
}

Output:

Read formulas and the actual values from Excel in Java

Create Combo Chart in Excel in Java

Combo chart, also known as combination chart, is a combination of two or more chart types. You can use it to display different types of information on a single graphic, for example, actual expenses against budget. In this article, I will demonstrate how to create a combo chart in Excel programmatically in Java using Free Spire.XLS for Java library.

Add Dependencies

To begin with, you need to add needed dependencies for including Free Spire.XLS for Java library into your Java project. You can either download the library’s jar from the official website or install it from Maven by adding the following code to your maven-based project’s pom.xml file.

<repositories>    
    <repository>    
        <id>com.e-iceblue</id>    
        <name>e-iceblue</name>    
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>    
    </repository>    
</repositories>    
<dependencies>    
    <dependency>    
        <groupId> e-iceblue </groupId>    
        <artifactId>spire.xls.free</artifactId>    
        <version>3.9.1</version>    
    </dependency>    
</dependencies>

Create a Combo Chart

To create a combo chart, you need to make sure your worksheet has at least two data series. Besides, you can create a combo chart with just a single Y axis, as well as with two Y axes.

The following are the steps to create a combo chart with two Y axes.

1. Create a Workbook instance and load an Excel file using Workbook.loadFromFile() method.
2. Get the desired worksheet using Workbook.getWorksheets().get() method.
3. Add a chart to the worksheet using Worksheet.getCharts.add() method, then set chart title, data range and location.
4. Get the data series of the chart using Chart.getSeries().get() method, and apply different chart types to different data series using ChartSerie.setSerieType() method.
5. Add a secondary Y axis for a desired data series using ChartSerie.setUsePrimaryAxis(false) method.
6. Save the Excel file using Workbook.saveToFile() method.

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

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

        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load a sample Excel file
        workbook.loadFromFile("Sample.xlsx");

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

        //Add a chart based on the data from A2 to C7
        Chart chart = sheet.getCharts().add();
        chart.setChartTitle("Combo Chart");
        chart.setDataRange(sheet.getCellRange("A2:C7"));
        chart.setSeriesDataFromRange(false);

        //Set position of the chart
        chart.setLeftColumn(6);
        chart.setTopRow(2);
        chart.setRightColumn(14);
        chart.setBottomRow(15);

        //Apply different chart types to different data series
        ChartSerie cs1 = (ChartSerie)chart.getSeries().get(0);
        cs1.setSerieType(ExcelChartType.ColumnClustered);
        ChartSerie cs2 = (ChartSerie)chart.getSeries().get(1);
        cs2.setSerieType(ExcelChartType.LineMarkers);

        //Add a secondary Y axis to the chart
        cs2.setUsePrimaryAxis(false);

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

The created Combo chart:

Create Combo chart in Excel in Java

Add Superscript and Subscript to PowerPoint in Java

When you add a trademark, copyright, or other symbol to your presentation, you might want the symbol to appear slightly above or below a certain text. In Microsoft PowerPoint, you can implement this effect simply by applying superscript or subscript formatting to the text. In this article, you will see how to achieve this task programmatically in Java using Free Spire.Presentation for Java library.

Add Dependencies

You can either download the jar of Free Spire.Presentation for Java from the official website or install it from maven by adding the following configurations to your maven-based project’s pom.xml file.

<repositories>    
    <repository>    
        <id>com.e-iceblue</id>    
        <name>e-iceblue</name>    
        <url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>    
    </repository>    
</repositories>    
<dependencies>    
    <dependency>    
        <groupId> e-iceblue </groupId>    
        <artifactId>spire.presentation.free</artifactId>    
        <version>3.9.0</version>    
    </dependency>    
</dependencies>

Add Superscript and Subscript

Free Spire.Presentation for Java provides the PortionFormatEx.setScriptDistance(float value) method for applying superscript or subscript formatting to text. The value can be set as positive (superscript) or negative (subscript).

import com.spire.presentation.*;
import com.spire.presentation.drawing.*;

import java.awt.*;

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

        //Create a Presentation instance
        Presentation presentation = new Presentation();

        //Load a PowerPoint document
        presentation.loadFromFile("template.pptx");
        //Get the first slide
        ISlide slide = presentation.getSlides().get(0);
        //Add a shape to the slide
        IAutoShape shape = presentation.getSlides().get(0).getShapes().appendShape(ShapeType.RECTANGLE, new Rectangle(150, 100, 200, 50));
        shape.getFill().setFillType(FillFormatType.NONE);
        shape.getShapeStyle().getLineColor().setColor(Color.white);
        shape.getTextFrame().getParagraphs().clear();

        //Append text to the shape
        shape.appendTextFrame("X");
        PortionEx tr = new PortionEx("2");
        //Apply superscript format to text
        tr.getFormat().setScriptDistance(40);
        //Append text to the shape
        shape.getTextFrame().getParagraphs().get(0).getTextRanges().append(tr);

        //Set text color and font
        tr = shape.getTextFrame().getParagraphs().get(0).getTextRanges().get(0);
        tr.getFill().setFillType(FillFormatType.SOLID);
        tr.getFill().getSolidColor().setColor(new Color(128,0,128));
        tr.setFontHeight(20);
        tr.setLatinFont(new TextFont("Arial"));
        tr = shape.getTextFrame().getParagraphs().get(0).getTextRanges().get(1);
        tr.getFill().setFillType(FillFormatType.SOLID);
        tr.getFill().getSolidColor().setColor(Color.BLUE);
        tr.setLatinFont(new TextFont("Arial"));

        //Add a shape to the slide
        shape = presentation.getSlides().get(0).getShapes().appendShape(ShapeType.RECTANGLE, new Rectangle(150, 150, 200, 50));
        shape.getFill().setFillType(FillFormatType.NONE);
        shape.getShapeStyle().getLineColor().setColor(Color.white);
        shape.getTextFrame().getParagraphs().clear();

        //Append text to the shape
        shape.appendTextFrame("H");
        tr = new PortionEx("2");
        //Apply subscript formatting to text
        tr.getFormat().setScriptDistance(-25);
        //Append text to shape
        shape.getTextFrame().getParagraphs().get(0).getTextRanges().append(tr);

        //Set text color and font
        tr = shape.getTextFrame().getParagraphs().get(0).getTextRanges().get(0);
        tr.getFill().setFillType(FillFormatType.SOLID);
        tr.getFill().getSolidColor().setColor(new Color(128,0,128));
        tr.setFontHeight(20);
        tr.setLatinFont(new TextFont("Arial"));
        tr = shape.getTextFrame().getParagraphs().get(0).getTextRanges().get(1);
        tr.getFill().setFillType(FillFormatType.SOLID);
        tr.getFill().getSolidColor().setColor(Color.BLUE);
        tr.setLatinFont(new TextFont("Arial"));

        //Save the document
        presentation.saveToFile("AddSuperscriptAndSubscript.pptx", FileFormat.PPTX_2013);
    }
}

Output:

Add Superscript and Subscript to PowerPoint in Java

Merge Multiple Excel Workbooks or Worksheets into One in Java

Introduction

When processing data in Excel, you may encounter a situation where your data is spread across multiple workbooks or worksheets. It would be better to merge the workbooks or worksheets into one instead of switching between different sources. In this article, I will introduce how to achieve this function programmatically in Java using Free Spire.XLS for Java library.

Add Dependencies

To begin with, you need to add needed dependencies for including Free Spire.XLS for Java library into your Java project. You can either download the library’s jar from this link or install it from Maven by adding the following code to your maven-based project’s pom.xml file.

<repositories>    
    <repository>    
        <id>com.e-iceblue</id>    
        <name>e-iceblue</name>    
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>    
    </repository>    
</repositories>    
<dependencies>    
    <dependency>    
        <groupId> e-iceblue </groupId>    
        <artifactId>spire.xls.free</artifactId>    
        <version>3.9.1</version>    
    </dependency>    
</dependencies>

Merge Multiple Excel Worksheets into One

Free Spire.XLS for Java allows you to merge multiple worksheets into a single worksheet by copying cell ranges using the CellRange.copy(CellRange destRange) method. Data and formatting will be copied with high precision.

You can merge worksheets in the same or different workbooks into one. In the following code example, you will see how to merge two Excel worksheets in the same workbook into a single worksheet:

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

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

        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("Sample.xlsx");
        
        //Get the first worksheet
        Worksheet sheet1 = workbook.getWorksheets().get(0);
        //Get the second worksheet
        Worksheet sheet2 = workbook.getWorksheets().get(1);
        
        //Specify the source range (used range) in the second worksheet
        CellRange sourceRange = sheet2.getAllocatedRange();
        //Specify the destination range in the first worksheet
        CellRange destRange = sheet1.getRange().get(sheet1.getLastRow() +1, 1);

        //Copy data along with formatting from the source range to the destination range
        sourceRange.copy(destRange);

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

Merge Multiple Excel Workbooks into One

The library enables you to merge multiple workbooks into one by copying the worksheets in different workbooks into a single workbook using the XlsWorksheetsCollection.addCopy() method.

The following code example shows how to merge two workbooks into one:

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

public class MergeWorkbooks {
    public static void main(String []args) throws Exception {
        //Create a String array for Excel workbooks
        String[] inputFiles = new String[]{"file1.xlsx","file2.xlsx"};

        //Create a new workbook
        Workbook newBook = new Workbook();
        //Clear the default worksheets
        newBook.getWorksheets().clear();

        //Create another temp workbook
        Workbook tempBook = new Workbook();

        //Loop through the array of Excel workbooks, copy worksheets in each Excel workbook into the new workbook
        for (String file : inputFiles)
        {
            tempBook.loadFromFile(file);
            for (Worksheet sheet : (Iterable<Worksheet>)tempBook.getWorksheets())
            {
                newBook.getWorksheets().addCopy(sheet, WorksheetCopyType.CopyAll);
            }
        }

        //Save the resultant file
        newBook.saveToFile("MergeWorkbooks.xlsx", ExcelVersion.Version2013);
    }
}