Wednesday, 05 February 2020 05:52

Java rename Excel Sheet and Set Tab Color

This article will introduce how to rename worksheet and set tab color for the sheet in Java applications by using Spire.XLS for Java.

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

public class RenameSheetandSetTabColor {
    public static void main(String[] args) {
        //Create a workbook and load a file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx");

        //Set the new worksheet name and tab color of first worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);
        worksheet.setName("Rename1");
        worksheet.setTabColor(Color.red);

        //Set the name and tab color of second sheet
        worksheet = workbook.getWorksheets().get(1);
        worksheet.setName("Rename2");
        worksheet.setTabColor(Color.green);

        //Set the name and tab color of third sheet
        worksheet = workbook.getWorksheets().get(2);
        worksheet.setName("Rename3");
        worksheet.setTabColor(Color.blue);

        //Save the document to file
        workbook.saveToFile("output/Result.xlsx", ExcelVersion.Version2010);
    }
}

Effective screenshot:

Java rename Excel Sheet and Set Tab Color

Wednesday, 05 February 2020 01:39

Apply Data Validation in Excel in Java

This article demonstrates how to apply data validation in Excel cells using Spire.XLS for Java.

import com.spire.xls.*;

public class ApplyDataValidation {

    public static void main(String[] args) {

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

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

        //Apply decimal validation in the cell B3
        sheet.getCellRange("B2").setText("Input number between 1-100:");
        CellRange rangeNumber = sheet.getCellRange("B3");
        rangeNumber.getDataValidation().setCompareOperator(ValidationComparisonOperator.Between);
        rangeNumber.getDataValidation().setFormula1("1");
        rangeNumber.getDataValidation().setFormula2("100");
        rangeNumber.getDataValidation().setAllowType(CellDataType.Decimal);
        rangeNumber.getDataValidation().setErrorMessage("Please input correct number!");
        rangeNumber.getDataValidation().setShowError(true);
        rangeNumber.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);

        //Apply date validation in the cell B6
        sheet.getCellRange("B5").setText("Input date between 1/1/1970-21/31/1970:");
        CellRange rangeDate = sheet.getCellRange("B6");
        rangeDate.getDataValidation().setAllowType(CellDataType.Date);
        rangeDate.getDataValidation().setCompareOperator(ValidationComparisonOperator.Between);
        rangeDate.getDataValidation().setFormula1("1/1/1970");
        rangeDate.getDataValidation().setFormula2("12/31/1970");
        rangeDate.getDataValidation().setErrorMessage("Please input correct date!");
        rangeDate.getDataValidation().setShowError(true);
        rangeDate.getDataValidation().setAlertStyle(AlertStyleType.Warning);
        rangeDate.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);

        //Apply text length validation in the cell B9
        sheet.getCellRange("B8").setText("Input text less than 5 characters:");
        CellRange rangeTextLength = sheet.getCellRange("B9");
        rangeTextLength.getDataValidation().setAllowType(CellDataType.TextLength);
        rangeTextLength.getDataValidation().setCompareOperator(ValidationComparisonOperator.LessOrEqual);
        rangeTextLength.getDataValidation().setFormula1("5");
        rangeTextLength.getDataValidation().setErrorMessage("Enter a Valid String!");
        rangeTextLength.getDataValidation().setShowError(true);
        rangeTextLength.getDataValidation().setAlertStyle(AlertStyleType.Stop);
        rangeTextLength.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);

        //Auto fit column width
        sheet.autoFitColumn(2);

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

Apply Data Validation in Excel in Java

This article demonstrates how to merge multiple Excel files into a single Excel file using Spire.XLS for Java.

import com.spire.xls.*;

public class MergeExcels {
    public static void main(String[] args){
        //Input Excel files
        String[] inputFiles = new String[]{"file1.xlsx","file2.xlsx"};

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

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

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

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

Before:

Merge Multiple Excel Files into a Single File in Java

After:

Merge Multiple Excel Files into a Single File in Java

Tuesday, 21 January 2020 08:59

Create Nested Groups in Excel in Java

This article demonstrates how to create a nested group in a worksheet using Spire.XLS for Java.

import com.spire.xls.*;

import java.awt.*;

public class CreateNestedGroup {

    public static void main(String[] args) {

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

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

        //Create a cell style
        CellStyle style = workbook.getStyles().addStyle("style");
        style.getFont().setColor(Color.blue);
        style.getFont().isBold(true);

        //Write data to cells
        sheet.get("A1").setValue("Project plan for project X");
        sheet.get("A1").setCellStyleName(style.getName());
        sheet.get("A3").setValue("Set up");
        sheet.get("A3").setCellStyleName(style.getName());
        sheet.get("A4").setValue("Task 1");
        sheet.get("A5").setValue("Task 2");
        sheet.getCellRange("A4:A5").borderAround(LineStyleType.Thin);
        sheet.getCellRange("A4:A5").borderInside(LineStyleType.Thin);
        sheet.get("A7").setValue("Launch");
        sheet.get("A7").setCellStyleName(style.getName());
        sheet.get("A8").setValue("Task 1");
        sheet.get("A9").setValue("Task 2");
        sheet.getCellRange("A8:A9").borderAround(LineStyleType.Thin);
        sheet.getCellRange("A8:A9").borderInside(LineStyleType.Thin);

        //Pass false to isSummaryRowBelow method , which indicates the summary rows appear above detail rows
        sheet.getPageSetup().isSummaryRowBelow(false);

        //Group the rows using groupByRows method
        sheet.groupByRows(2,9,false);
        sheet.groupByRows(4,5,false);
        sheet.groupByRows(8,9,false);

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

 

Create Nested Groups in Excel in Java

This article demonstrates how to use Spire.XLS for Java to encrypt the Excel files from the following three parts:

  • Encrypt the whole excel workbook with password in Java
  • Protect a certain worksheet with password in Java
  • Lock some certain cells on the worksheet in Java

Encrypt the whole Excel workbook:

import com.spire.xls.*;

public class EncryptWorkbook {
    public static void main(String[] args) {
        //Create a workbook and load a file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx");

        //Protect the workbook with the password you want
        workbook.protect("eiceblue");

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

    }
}

Effective screenshot after protected workbook:

How to protect Excel files in Java applications

Only protect the first worksheet on the excel workbook.

import com.spire.xls.*;
import java.util.EnumSet;

public class ProtectWorksheet {
    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);

        //Protect this sheet with a password.
        sheet.protect("TestPassword", EnumSet.of(SheetProtectionType.All));

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

    }
}

The screenshot of the protected worksheet:

How to protect Excel files in Java applications

Lock some certain cells on the Excel Worksheet.

import com.spire.xls.*;
import java.util.EnumSet;

public class ProtectCell {
    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);

        //Protect cell
        sheet.getCellRange("B3").getCellStyle().setLocked(true);
        sheet.getCellRange("C3").getCellStyle().setLocked(false);

        //Protect sheet
        sheet.protect("TestPassword", EnumSet.of(SheetProtectionType.All));

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

    }
}

Here is the protected Excel cells:

How to protect Excel files in Java applications

Wednesday, 15 January 2020 07:55

Insert Arrays into Excel in Java

This article demonstrates how to insert arrays, including one-dimensional and two-dimensional arrays, into Excel cells using Spire.XLS for Java.

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

public class InsertArray {

    public static void main(String[] args) {

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

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

        //Define a one-dimensional array 
        String[] oneDimensionalArray = new String[]{"Apple", "Pear", "Grape", "Banana"};

        // Write the array to the worksheet from the specified cell (true means vertically insert)
        sheet.insertArray(oneDimensionalArray, 1, 1, true);

        //Define a two-dimensional array 
        String[][] twoDimensionalArray = new String[][]{
                {"Name", "Age", "Sex", "Dept."},
                {"John", "25", "Male", "Development"},
                {"Albert", "24", "Male", "Support"},
                {"Amy", "26", "Female", "Sales"}
        };

        //Write the array to the worksheet from the specified cell
        sheet.insertArray(twoDimensionalArray, 1, 3);

        //Save the file 
        wb.saveToFile("InsertArrays.xlsx", ExcelVersion.Version2016);
    }
}

Insert Arrays into Excel in Java

Tuesday, 14 January 2020 02:39

Merge and Unmerge Cells in Excel in Java

This article demonstrates how to merge and unmerge cells in an Excel file using Spire.XLS for Java.

Merge cells

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

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

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);
        //Merge cells by range
        sheet.getRange().get("A1:C1").merge();
        
        //Save the resultant file
        workbook.saveToFile("MergeCells.xlsx", FileFormat.Version2013);
    }
}

Merge and Unmerge Cells in Excel in Java

Unmerge cells

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

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

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);
        //Unmerge cells by range
        sheet.getRange().get("A1:C1").unMerge();

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

Merge and Unmerge Cells in Excel in Java

Monday, 13 January 2020 07:21

Convert Excel to PDF in Java

Spire.XLS for Java offers two methods called Workbook.saveToFile() and Worksheet.saveToPdf() to convert a whole Excel file or a specified worksheet to PDF. Besides, it allows to control conversion parameters using the ConvertSettings class, for example, you can let worksheets to fit to page.

The following screenshot is an Excel file including two worksheets. The following code samples will demonstrate how to convert it to PDF with the help of Spire.XLS for Java.

Convert Excel to PDF in Java

Convert a whole Excel file to PDF

import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;

public class ConvertExcelToPdf {

    public static void main(String[] args) {

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

        //Set worksheets to fit to page when converting
        workbook.getConverterSetting().setSheetFitToPage(true);

        //Save the resulting document to a specified path
        workbook.saveToFile("output/ExcelToPdf.pdf", FileFormat.PDF);
    }
}

Convert Excel to PDF in Java

Convert a specified worksheet to PDF

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

public class ConvertWorksheetToPdf {

    public static void main(String[] args) {

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

        //Set worksheets to fit to width when converting
        workbook.getConverterSetting().setSheetFitToWidth(true);

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

        //Convert to PDF and save the resulting document to a specified path
        worksheet.saveToPdf("output/WorksheetToPdf.pdf");
    }
}

Convert Excel to PDF in Java

Thursday, 09 January 2020 09:45

Find and Replace Data in Excel in Java

This article demonstrates how to find and replace data in an Excel file using Spire.XLS for Java.

Below is the sample Excel file we used for demonstration:

Find and Replace Data in Excel in Java

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

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

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

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

        //Find all the text "Total" in the worksheet
        CellRange[] ranges = worksheet.findAllString("Total", true, true);

        for (CellRange range : ranges)
        {
            //Replace the text with new text
            range.setText("Sum");
        }

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

Output:

Find and Replace Data in Excel in Java

Wednesday, 08 January 2020 03:01

Create Pie Charts in Excel in Java

This article demonstrates how to create pie charts and doughnut charts in an Excel document using Spire.XLS for Java.

Pie Chart

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

import java.awt.*;

public class CreatePieChart {

    public static void main(String[] args) {

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

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

        //Insert data 
        sheet.getCellRange("A1").setValue("Year");
        sheet.getCellRange("A2").setValue("2002");
        sheet.getCellRange("A3").setValue("2003");
        sheet.getCellRange("A4").setValue("2004");
        sheet.getCellRange("A5").setValue("2005");

        sheet.getCellRange("B1").setValue("Sales");
        sheet.getCellRange("B2").setNumberValue(4000);
        sheet.getCellRange("B3").setNumberValue(6000);
        sheet.getCellRange("B4").setNumberValue(7000);
        sheet.getCellRange("B5").setNumberValue(8500);

        //Set cell style 
        sheet.getCellRange("A1:B1").setRowHeight(15);
        sheet.getCellRange("A1:B1").getCellStyle().setColor(Color.darkGray);
        sheet.getCellRange("A1:B1").getCellStyle().getExcelFont().setColor(Color.white);
        sheet.getCellRange("A1:B1").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
        sheet.getCellRange("A1:B1").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);
        sheet.getCellRange("B2:C5").getCellStyle().setNumberFormat("\"$\"#,##0");

        //Add a pie chart to the sheet 
        Chart chart = sheet.getCharts().add(ExcelChartType.Pie);

        //Set data range of chart 
        chart.setDataRange(sheet.getCellRange("B2:B5"));
        chart.setSeriesDataFromRange(false);

        //Set position of the chart 
        chart.setLeftColumn(3);
        chart.setTopRow(1);
        chart.setRightColumn(11);
        chart.setBottomRow(20);

        //Set chart title 
        chart.setChartTitle("Sales by year");
        chart.getChartTitleArea().isBold(true);
        chart.getChartTitleArea().setSize(12);

        //Set series data label 
        ChartSerie cs = chart.getSeries().get(0);
        cs.setCategoryLabels(sheet.getCellRange("A2:A5"));
        cs.setValues(sheet.getCellRange("B2:B5"));
        cs.getDataPoints().getDefaultDataPoint().getDataLabels().hasValue(true);
        chart.getPlotArea().getFill().setVisible(false);

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

Create Pie Charts in Excel in Java

Doughnut Chart

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

import java.awt.*;

public class CreateDoughnutChart {

    public static void main(String[] args) {

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

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

        //insert data
        sheet.getCellRange("A1").setValue("Country");
        sheet.getCellRange("A2").setValue("Cuba");
        sheet.getCellRange("A3").setValue("Mexico");
        sheet.getCellRange("A4").setValue("German");
        sheet.getCellRange("A5").setValue("Japan");


        sheet.getCellRange("B1").setValue("Sales");
        sheet.getCellRange("B2").setNumberValue(6000);
        sheet.getCellRange("B3").setNumberValue(8000);
        sheet.getCellRange("B4").setNumberValue(9000);
        sheet.getCellRange("B5").setNumberValue(8500);

        //Set cell style 
        sheet.getCellRange("A1:B1").setRowHeight(15);
        sheet.getCellRange("A1:B1").getCellStyle().setColor(Color.darkGray);
        sheet.getCellRange("A1:B1").getCellStyle().getExcelFont().setColor(Color.white);
        sheet.getCellRange("A1:B1").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
        sheet.getCellRange("A1:B1").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);
        sheet.getCellRange("B2:C5").getCellStyle().setNumberFormat("\"$\"#,##0");

        //Add a pie chart to the sheet 
        Chart chart = sheet.getCharts().add(ExcelChartType.Doughnut);
        
        //Set data range of chart 
        chart.setDataRange(sheet.getCellRange("A1:B5"));
        chart.setSeriesDataFromRange(false);

        //Set position of the chart 
        chart.setLeftColumn(3);
        chart.setTopRow(1);
        chart.setRightColumn(11);
        chart.setBottomRow(20);

        //Set chart title 
        chart.setChartTitle("Market share by country");
        chart.getChartTitleArea().isBold(true);
        chart.getChartTitleArea().setSize(12);

        //Set series color 
        ChartSeries series = chart.getSeries();
        for (int i = 0 ; i < series.size() ; i++) {
            ChartSerie cs = series.get(i);
            cs.getDataPoints().getDefaultDataPoint().getDataLabels().hasPercentage(true);
        }
        
        //Set legend position 
        chart.getLegend().setPosition(LegendPositionType.Top);

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

Create Pie Charts in Excel in Java