Spire.XLS for Java provides the getStyle() method and setStyle() method under the IXLSRange interface to get or set the style of a specific cell range. To copy formatting from one cell to another, get the style first and then apply it to another cell.

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

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

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

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

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

        //Get the number of rows used
        int rowCount = sheet.getRows().length;
        
        //Loop through the rows
        for (int i = 1; i < rowCount + 1; i++)
        {
            //Copy the formatting from a certain cell to another
            sheet.getRange().get(String.format("C%d",i)).setStyle(sheet.getRange().get(String.format("A%d",i)).getStyle());
        }

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

Copy Formatting from One Cell Range to Another in Java

Published in Worksheet

This article shows how to copy a worksheet from an Excel document to anther by using Spire.XLS for Java.

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

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

        //Create a Workbook object to load the source document
        Workbook srcWorkbook = new Workbook();
        srcWorkbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\source.xlsx");

        //Get the specific worksheet to copy
        Worksheet originalSheet = srcWorkbook.getWorksheets().get(0);

        //Create another Workbook object to load the destination document
        Workbook destWorkbook = new Workbook();
        destWorkbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\destination.xlsx");

        //Add the copy of selected sheet to destination document
        Worksheet newSheet = destWorkbook.getWorksheets().addCopy(originalSheet);
        newSheet.setName(originalSheet.getName());

        //Copy the theme of source document to destination document
        destWorkbook.copyTheme(srcWorkbook);

        //Save to another file
        destWorkbook.saveToFile("CopySheetBetweenWorkbooks.xlsx");
    }
}

Copy Worksheets from one Workbook to another in Java

Published in Worksheet

This article shows you how to duplicate a worksheet inside a workbook using Spire.XLS for Java.

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

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

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

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

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

        //Add a new worksheet
        Worksheet newSheet = workbook.getWorksheets().add(originalSheet.getName()+" - Copy");

        //Copy the worksheet to new sheet
        newSheet.copyFrom(originalSheet);

        //Save to file
        workbook.saveToFile("DuplicateSheet.xlsx");
    }
}

Duplicate Worksheets within a Workbook in Java

Published in Worksheet

This article demonstrates how to split a worksheet into several Excel documents by using Spire.XLS for Java.

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

public class SplitWorksheet {

    public static void main(String[] args) {

        //Create a Workbook object to load the original Excel document
        Workbook bookOriginal = new Workbook();
        bookOriginal.loadFromFile("C:\\Users\\Administrator\\Desktop\\Emplyees.xlsx");

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

        //Get the header row
        CellRange headerRow = sheet.getCellRange(1, 1, 1, 5);

        //Get two cell ranges
        CellRange range1 = sheet.getCellRange(2, 1, 6, 5);
        CellRange range2 = sheet.getCellRange(7, 1, 11, 5);

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

        //Copy the header row and range 1 to the new workbook
        sheet.copy(headerRow, newBook1.getWorksheets().get(0), 1, 1, true, false);
        sheet.copy(range1, newBook1.getWorksheets().get(0), 2, 1, true, false);

        //Copy the column width from the original workbook to the new workbook
        for (int i = 0; i < sheet.getLastColumn(); i++) {

            newBook1.getWorksheets().get(0).setColumnWidth(i + 1, sheet.getColumnWidth(i + 1));
        }

        //Save the new workbook to an Excel file
        newBook1.saveToFile("Sales.xlsx", ExcelVersion.Version2016);

        //Copy the header row and range 2 to another workbook, and save it to another Excel file
        Workbook newBook2 = new Workbook();
        sheet.copy(headerRow, newBook2.getWorksheets().get(0), 1, 1, true, false);
        sheet.copy(range2, newBook2.getWorksheets().get(0), 2, 1, true, false);
        for (int i = 0; i < sheet.getLastColumn(); i++) {

            newBook2.getWorksheets().get(0).setColumnWidth(i + 1, sheet.getColumnWidth(i + 1));
        }
        newBook2.saveToFile("Technicians.xlsx", ExcelVersion.Version2016);
    }
}

Split a Worksheet into Several Excel Files in Java

Published in Worksheet

This tutorial will demonstrate how to set font and background color for Excel textbox using Spire.XLS for Java.

The following is a screenshot of a sample document:

Set Font and Background Color for Excel Textbox in Java

Using the code

import java.awt.*;
import com.spire.xls.*;
import com.spire.xls.core.spreadsheet.shapes.*;

public class SetFontAndBackground {
    public static void main(String[] args) {
        //Load an Excel sample
        Workbook workbook = new Workbook();
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\Sample.xlsx");

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

        //Get the textbox which will be edited
        XlsTextBoxShape shape = (XlsTextBoxShape) sheet.getTextBoxes().get(0);

        //Set the font and background color for the textbox
        //Set font
        ExcelFont font = workbook.createFont();
        font.setFontName("Calibri");
        font.setSize(14);
        font.isBold(false);
        font.setColor(Color.MAGENTA);
        (new RichText(shape.getRichText())).setFont(0, shape.getText().length() - 1, font);

        //Set background color
        shape.getFill().setFillType(ShapeFillType.SolidColor);
        shape.getFill().setForeKnownColor(ExcelColors.Yellow);

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

Output

Set Font and Background Color for Excel Textbox in Java

Published in Worksheet
Friday, 12 June 2020 08:44

Get Worksheet Names in Java

This article demonstrates how to get names of worksheets in a workbook using Spire.XLS for Java.

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

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

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

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

        //Loop through the worksheets 
        for (Object sheet: wb.getWorksheets()
             ) {

            //Get worksheet name
            String sheetName = ((Worksheet) sheet).getName();
            System.out.println(sheetName);
        }
    }
}

Get Worksheet Names in Java

Published in Worksheet
Thursday, 07 May 2020 09:27

Hide or Show Gridlines in Excel in Java

This article demonstrates how to hide or show gridlines in a worksheet using Spire.XLS for Java.

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

public class HideOrShowGridlines {

    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\\sample.xlsx");

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

        //Hide gridlines
        worksheet.setGridLinesVisible(false);

        ////Show gridlines
        //worksheet.setGridLinesVisible(true);

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

Hide Gridlines

Hide or Show Gridlines in Excel in Java

Published in Worksheet

This article demonstrates how to delete blank rows and columns in an Excel document using Spire.XLS for Java.

Sample Document

Delete Blank Rows and Columns in Excel in Java

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

public class DeleteBlankRowsAndColumns {

    public static void main(String[] args) {

        //Load the sample document
        Workbook wb = new Workbook();
        wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx ");

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

        //Loop through the rows
        for (int i = sheet.getLastRow(); i >= 1; i--)
        {
            //Detect if a row is blank
            if (sheet.getRows()[i-1].isBlank())
            {
                //Remove blank row
                sheet.deleteRow(i);
            }
        }

        //Loop through the columns
        for (int j = sheet.getLastColumn(); j >= 1; j--)
        {
            //Detect if a column is blank
            if (sheet.getColumns()[j-1].isBlank())
            {
                //Remove blank column
                sheet.deleteColumn(j);
            }
        }

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

Output

Delete Blank Rows and Columns in Excel in Java

Published in Worksheet
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

Published in Worksheet
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

Published in Worksheet
Page 1 of 2