support iceblue

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

Published in Data Import/Export

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

Published in Cells

Monday, 13 January 2020 07:21

Java convert Excel to PDF

This tutorial helps to demonstrate how to convert Excel to PDF using Spire.XLS for Java from the following two parts:

Convert the whole Excel workbook to PDF.
Convert a single Excel worksheet to PDF.

Spire.XLS for Java offers a method of workbook.saveToFile() to save the whole Excel workbook to PDF in Java.

import com.spire.xls.*;

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

        //Fit to page
        workbook.getConverterSetting().setSheetFitToPage(true);

        workbook.saveToFile("ToPDF.pdf",FileFormat.PDF);
    }
}

Effective screenshot of converting Excel workbook to PDF:

Java convert Excel to PDF

Spire.XLS for Java offers a method of worksheet.saveToPdf() to save a single Excel worksheet to PDF in Java.

import com.spire.xls.*;

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

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

        worksheet.saveToPdf("ToPDF2.pdf");
    }
}

Effective screenshot of converting Excel worksheet to PDF:

Java convert Excel to PDF

Published in Conversion

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

Published in Data

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

Published in Chart

Monday, 06 January 2020 08:10

Create Excel column chart in Java

Spire.XLS for Java supports to generate many kinds of charts in Excel files. This article demonstrates how to create Excel column chart using Spire.XLS for Java. There are two main column charts, ColumnClustered chart and ColumnStacked chart. Here comes to the code snippets.

Java generate Column stacked chart. ColumnClustered represents Clustered Column Chart.

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

public class ColumnChart{
    public static void main(String[] args) {
        executeStackedColumn(true);
    }
    private static void executeStackedColumn(boolean is3D)
    {
        //Create a Workbook
        Workbook workbook = new Workbook();

        //Get the first sheet and set its name
        Worksheet sheet = workbook.getWorksheets().get(0);
        sheet.setName("StackedColumn");

        //Set chart data
        createChartData(sheet);

        //Add a chart
        Chart chart = sheet.getCharts().add();

        //Set region of chart data
        chart.setDataRange(sheet.getCellRange("A1:C5"));
        chart.setSeriesDataFromRange(false);

        //Set position of chart
        chart.setLeftColumn(1);
        chart.setTopRow(6);
        chart.setRightColumn(11);
        chart.setBottomRow(29);

        if (is3D)
        {
            chart.setChartType(ExcelChartType.Column3DStacked);
        }
        else
        {
            chart.setChartType(ExcelChartType.ColumnStacked);
        }

        //Chart title
        chart.setChartTitle("Sales market by country");
        chart.getChartTitleArea().isBold(true);
        chart.getChartTitleArea().setSize(12);

        //Chart Axis
        chart.getPrimaryCategoryAxis().setTitle("Country");
        chart.getPrimaryCategoryAxis().getFont().isBold(true);
        chart.getPrimaryCategoryAxis().getTitleArea().isBold(true);

        chart.getPrimaryValueAxis().setTitle("Sales(in Dollars)");
        chart.getPrimaryValueAxis().hasMajorGridLines(false);
        chart.getPrimaryValueAxis().setMinValue(1000);
        chart.getPrimaryValueAxis().getTitleArea().isBold(true);
        chart.getPrimaryValueAxis().getTitleArea().setTextRotationAngle(90);

        ChartSeries series = chart.getSeries();
        for (int i = 0;i < series.size();i++)
        {
            ChartSerie cs = series.get(i);
            cs.getFormat().getOptions().isVaryColor(true);
            cs.getDataPoints().getDefaultDataPoint().getDataLabels().hasValue(true);
        }

        //Chart legend
        chart.getLegend().setPosition(LegendPositionType.Top);

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

    }

    private static void createChartData(Worksheet sheet)
    {
        //Set value of specified cell
        sheet.getCellRange("A1").setValue("Country");
        sheet.getCellRange("A2").setValue("Cuba");
        sheet.getCellRange("A3").setValue("Mexico");
        sheet.getCellRange("A4").setValue("France");
        sheet.getCellRange("A5").setValue("German");

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

        sheet.getCellRange("C1").setValue("Aug");
        sheet.getCellRange("C2").setNumberValue(3000);
        sheet.getCellRange("C3").setNumberValue(2000);
        sheet.getCellRange("C4").setNumberValue(2300);
        sheet.getCellRange("C5").setNumberValue(4200);

        //Style
        sheet.getCellRange("A1:C1").setRowHeight(15);
        sheet.getCellRange("A1:C1").getCellStyle().setColor(Color.darkGray);
        sheet.getCellRange("A1:C1").getCellStyle().getExcelFont().setColor(Color.white);
        sheet.getCellRange("A1:C1").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
        sheet.getCellRange("A1:C1").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);

        sheet.getCellRange("B2:C5").getCellStyle().setNumberFormat("\"$\"#,##0");
    }
}

Effective screenshot of column stacked chart. ColumnStacked represents Stacked Column Chart.

Create Excel column chart in Java

Java generate Column clustered chart:

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

public class ColumnChart {
    public static void main(String[] args) {
        executeClusteredColumn(true);
    }
    private static void executeClusteredColumn(boolean is3D)
    {
        //Create a Workbook
        Workbook workbook = new Workbook();

        //Get the first sheet and set its name
        Worksheet sheet = workbook.getWorksheets().get(0);
        sheet.setName("ClusteredColumn");

        //Set chart data
        createChartData(sheet);

        //Add a chart
        Chart chart = sheet.getCharts().add();

        //Set region of chart data
        chart.setDataRange(sheet.getCellRange("A1:C5"));
        chart.setSeriesDataFromRange(false);

        //Set position of chart
        chart.setLeftColumn(1);
        chart.setTopRow(6);
        chart.setRightColumn(11);
        chart.setBottomRow(29);

        if (is3D)
        {
            chart.setChartType(ExcelChartType.Column3DClustered);
        }
        else
        {
            chart.setChartType(ExcelChartType.ColumnClustered);
        }

        //Chart title
        chart.setChartTitle("Sales market by country");
        chart.getChartTitleArea().isBold(true);
        chart.getChartTitleArea().setSize(12);

        //Chart Axis
        chart.getPrimaryCategoryAxis().setTitle("Country");
        chart.getPrimaryCategoryAxis().getFont().isBold(true);
        chart.getPrimaryCategoryAxis().getTitleArea().isBold(true);

        chart.getPrimaryValueAxis().setTitle("Sales(in Dollars)");
        chart.getPrimaryValueAxis().hasMajorGridLines(false);
        chart.getPrimaryValueAxis().setMinValue(1000);
        chart.getPrimaryValueAxis().getTitleArea().isBold(true);
        chart.getPrimaryValueAxis().getTitleArea().setTextRotationAngle(90);

        ChartSeries series = chart.getSeries();
        for (int i = 0;i < series.size();i++)
        {
            ChartSerie cs = series.get(i);
            cs.getFormat().getOptions().isVaryColor(true);
            cs.getDataPoints().getDefaultDataPoint().getDataLabels().hasValue(true);
        }

        //Chart legend
        chart.getLegend().setPosition(LegendPositionType.Top);

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

    }

    private static void createChartData(Worksheet sheet)
    {
        //Set value of specified cell
        sheet.getCellRange("A1").setValue("Country");
        sheet.getCellRange("A2").setValue("Cuba");
        sheet.getCellRange("A3").setValue("Mexico");
        sheet.getCellRange("A4").setValue("France");
        sheet.getCellRange("A5").setValue("German");

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

        sheet.getCellRange("C1").setValue("Aug");
        sheet.getCellRange("C2").setNumberValue(3000);
        sheet.getCellRange("C3").setNumberValue(2000);
        sheet.getCellRange("C4").setNumberValue(2300);
        sheet.getCellRange("C5").setNumberValue(4200);

        //Style
        sheet.getCellRange("A1:C1").setRowHeight(15);
        sheet.getCellRange("A1:C1").getCellStyle().setColor(Color.darkGray);
        sheet.getCellRange("A1:C1").getCellStyle().getExcelFont().setColor(Color.white);
        sheet.getCellRange("A1:C1").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
        sheet.getCellRange("A1:C1").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);

        sheet.getCellRange("B2:C5").getCellStyle().setNumberFormat("\"$\"#,##0");
    }
}

Effective screenshot of column clustered chart:

Create Excel column chart in Java

Published in Chart

Friday, 03 January 2020 01:45

Add and Read Comments in Excel in Java

This article demonstrates how to add regular comment and rich text comment to an Excel file and read the comments using Spire.XLS for Java.

Add comments

import com.spire.xls.*;

public class InsertComments {
    public static void main(String[] args){
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Create fonts
        ExcelFont font = workbook.createFont();
        font.setFontName("Arial");
        font.setSize(11);
        font.setKnownColor(ExcelColors.Orange);
        ExcelFont fontBlue = workbook.createFont();
        fontBlue.setKnownColor(ExcelColors.LightBlue);
        ExcelFont fontGreen = workbook.createFont();
        fontGreen.setKnownColor(ExcelColors.LightGreen);

        //Add regular comment to specific cell range
        CellRange range = sheet.getCellRange("A1");
        range.setText("Regular comment");
        range.getComment().setText("Regular comment");
        range.autoFitColumns();

        //Add rich text comment to specific cell range
        range = sheet.getCellRange("A2");
        range.setText("Rich text comment");
        range.getRichText().setFont(0, 16, font);
        range.autoFitColumns();
        range.getComment().getRichText().setText("Rich text comment");
        range.getComment().getRichText().setFont(0, 4, fontGreen);
        range.getComment().getRichText().setFont(5, 9, fontBlue);

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

Regular comment:

Add and Read Comments in Excel in Java

Rich text comment:

Add and Read Comments in Excel in Java

Read comments

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

public class ReadComments {
    public static void main(String[] args){
        //Load Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("AddComments.xlsx");
        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);
        //Print out the comment
        System.out.println("A1 Comment = " + sheet.getCellRange("A1").getComment().getText());
        System.out.println("A2 Comment = " + sheet.getCellRange("A2").getComment().getRichText().getRtfText());
    }
}

Add and Read Comments in Excel in Java

Published in Comment

Thursday, 02 January 2020 07:59

Java insert, hide and delete Excel rows and columns

This article will demonstrate how to insert, hide and delete rows and columns on the Excel in Java applications.

Insert rows and columns on Excel:

import com.spire.xls.*;

public class InsertRowsandColumns {
    public static void main(String[] args) throws Exception {
        //Load the sample Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx");

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

        //Insert a row into the worksheet.
        worksheet.insertRow(2);

        //Insert a column into the worksheet.
        worksheet.insertColumn(2);

        //Insert multiple rows into the worksheet.
        worksheet.insertRow(5, 2);

        //Insert multiple columns into the worksheet.
        worksheet.insertColumn(5, 2);

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

    }
}

Effective screenshot after insert the empty rows and columns:

Java insert hide and delete Excel rows and columns

Hide rows and columns on Excel in Java:

import com.spire.xls.*;

public class HideRowsandColumns {
    public static void main(String[] args) throws Exception {
        //Load the sample Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("InsertRowsAndColumns.xlsx");

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

        //Hide the column of the worksheet.
        worksheet.hideColumn(2);

        //Hide the row of the worksheet
        worksheet.hideRow(3);

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

    }
}

Effective screenshot after hiding the second column and the third row:

Java insert hide and delete Excel rows and columns

Delete rows and columns on Excel in Java:

import com.spire.xls.*;

public class DeleteRowsandColumns {
    public static void main(String[] args) throws Exception {
        //Load the sample Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("InsertRowsAndColumns.xlsx");

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

        //Delete a row from the worksheet.
        worksheet.deleteRow(2);

        //Delete multiple columns from the worksheet.
        worksheet.deleteColumn(5, 2);

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

    }
}

Effective screenshot after deleting the rows and columns:

Java insert hide and delete Excel rows and columns

Published in Cells

Monday, 30 December 2019 09:15

Insert and Read Hyperlinks in Excel in Java

This article demonstrates how to add hyperlinks to Excel cells and how to read hyperlinks from an existing Excel document by using Spire.XLS for Java.

Insert Hyperlinks

import com.spire.xls.*;

public class InsertHyperlinks {

    public static void main(String[] args) {

        //Create a Workbook object
        Workbook workbook = new Workbook();
        
        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Add a link to B1 that links to a website
        HyperLink urlLink = sheet.getHyperLinks().add(sheet.getCellRange("B1"));
        urlLink.setTextToDisplay("Home Page");
        urlLink.setAddress("http://www.e-iceblue.com");

        //Add a link to B2 that links to an email address
        HyperLink mailLink = sheet.getHyperLinks().add(sheet.getCellRange("B2"));
        mailLink.setTextToDisplay("Contact Us");
        mailLink.setAddress("mailto:support@e-iceblue.com");

        //Add a link to B3 that links to an external file
        HyperLink fileLink = sheet.getHyperLinks().add(sheet.getCellRange("B3"));
        fileLink.setTextToDisplay("Open File");
        fileLink.setAddress("C:\\Users\\Administrator\\Desktop\\report.doc");

        //Add a link to B4 that links to another worksheet
        HyperLink linkToSheet = sheet.getHyperLinks().add(sheet.getCellRange("B4"));
        linkToSheet.setTextToDisplay("Jump to sheet2");
        linkToSheet.setAddress("Sheet2!B5");

        //Add a link to B5 that links to an ip address
        HyperLink uncLink = sheet.getHyperLinks().add(sheet.getCellRange("B5"));
        uncLink.setTextToDisplay("Go to 192.168.1.108");
        uncLink.setAddress("\\\\192.168.1.108");

        //Set the default row height
        sheet.setDefaultRowHeight(15f);
        
        //Set the width of specific column
        sheet.setColumnWidth(2,20f);

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

Insert and Read Hyperlinks in Excel in Java

Read Hyperlinks

import com.spire.xls.*;

public class ReadHyperlinks {

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

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

        //Loop through the cells within all located range
        for (Object cell : sheet.getAllocatedRange()) { 

            CellRange cellRange = (CellRange) cell;
            //Detect if a cell range has hyperlinks
            if (cellRange.getHyperlinks().getCount() > 0) {

                //Print out the cell containing a hyperlink and the link address
                String certainCell = String.format("Cell[%d, %d] has a hyperlink: ", cellRange.getRow(), cellRange.getColumn());
                System.out.println(certainCell + cellRange.getHyperlinks().get(0).getAddress());
            }
        }
    }

Insert and Read Hyperlinks in Excel in Java

Published in Link

Thursday, 26 December 2019 09:22

Insert and Extract Image in Excel in Java

This article demonstrates how to insert and extract image in Excel file using Spire.XLS for Java.

Insert image

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

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

        //Add an image to the specific cell
        ExcelPicture pic = sheet.getPictures().add(4, 1,"image.jpg");
        //Set image width and height
        pic.setWidth(500);
        pic.setHeight(300);

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

Insert and Extract Image in Excel in Java

Extract image

import com.spire.xls.ExcelPicture;
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 ReadImage {
    public static void main(String[] args) throws IOException {
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("InsertImage.xlsx");

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

        //Get the first image in the worksheet
        ExcelPicture pic = sheet.getPictures().get(0);
        BufferedImage loImage = pic.getPicture();
        //Save to disk
        ImageIO.write(loImage,"jpg",new File("output/ReadImage.jpg"));
    }
}

Insert and Extract Image in Excel in Java

Published in Image

Insert Arrays into Excel in Java

Merge and Unmerge Cells in Excel in Java

Java convert Excel to PDF

Find and Replace Data in Excel in Java

Create Pie Charts in Excel in Java

Create Excel column chart in Java

Add and Read Comments in Excel in Java

Java insert, hide and delete Excel rows and columns

Insert and Read Hyperlinks in Excel in Java

Insert and Extract Image in Excel in Java

Purchase

Partnership

Products

Corporation