support iceblue

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

Tuesday, 24 December 2019 07:26

Insert and Read Formulas in Excel in Java

This article demonstrates how to insert and read formulas in an Excel document using Spire.XLS for Java.

Insert Formulas

import com.spire.xls.*;

public class InsertFormulas {

    public static void main(String[] args) {

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

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

        //Declare two variables: currentRow、currentFormula
        int currentRow = 1;
        String currentFormula = null;

        //Set the column width
        sheet.setColumnWidth(1, 32);
        sheet.setColumnWidth(2, 16);

        //Write test data into cells
        sheet.getCellRange(currentRow,1).setValue("Test data:");
        sheet.getCellRange(currentRow,2).setNumberValue(1);
        sheet.getCellRange(currentRow,3).setNumberValue(2);
        sheet.getCellRange(currentRow,4).setNumberValue(3);
        sheet.getCellRange(currentRow,5).setNumberValue(4);
        sheet.getCellRange(currentRow,6).setNumberValue(5);

        //Write text in cells
        currentRow += 2;
        sheet.getCellRange(currentRow,1).setValue("Formulas:") ; ;
        sheet.getCellRange(currentRow,2).setValue("result:");

        //Format cells
        CellRange range = sheet.getCellRange(currentRow,1,currentRow,2);
        range.getStyle().getFont().isBold(true);
        range.getStyle().setKnownColor(ExcelColors.LightGreen1);
        range.getStyle().setFillPattern(ExcelPatternType.Solid);
        range.getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Medium);

        //Arithmetic operation
        currentFormula = "=1/2+3*4";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Date function
        currentFormula = "=TODAY()";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);
        sheet.getCellRange(currentRow,2).getStyle().setNumberFormat("YYYY/MM/DD");

        //Time function
        currentFormula = "=NOW()";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);
        sheet.getCellRange(currentRow,2).getStyle().setNumberFormat("H:MM AM/PM");

        //IF function
        currentFormula = "=IF(B1=5,\"Yes\",\"No\")";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //PI function
        currentFormula = "=PI()";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Trigonometric function
        currentFormula = "=SIN(PI()/6)";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Count function
        currentFormula = "=Count(B1:F1)";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Maximum function
        currentFormula = "=MAX(B1:F1)";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Average function
        currentFormula = "=AVERAGE(B1:F1)";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Summation function
        currentFormula = "=SUM(B1:F1)";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

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

Insert and Read Formulas in Excel in Java

Read Formulas

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

public class ReadFormulas {

    public static void main(String[] args) {

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

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

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

        //Loop through the cells within B1:B13
        for (Object cell: sheet.getCellRange("B1:B13")
             ) {
            CellRange cellRange = (CellRange)cell;

            //Detect if a cell range has formula
            if (cellRange.hasFormula()){

                //Print out the cell containing a formula and the formula itself
                String certainCell = String.format("Cell[%d, %d] has a formula: ",cellRange.getRow(),cellRange.getColumn());
                System.out.println(certainCell + cellRange.getFormula());
            }
        }
    }
}

Insert and Read Formulas in Excel in Java

Published in Formula

Monday, 23 December 2019 07:52

Create a table in Excel in Java

Spire.XLS for Java supports to create and format the table on Excel file. This article will show you how to create a simple table by the range of data from an existing worksheet.

Firstly, view the sample Excel worksheet:

Create a table in Excel in Java

mport com.spire.xls.*;

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

        String inputFile = "Sample1.xlsx";
        String outputFile = "output/CreateTable.xlsx";

        //Create a workbook and load a file
        Workbook workbook = new Workbook();
        workbook.loadFromFile(inputFile);

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

        //Create table with the data in given range
        sheet.getListObjects().create("table", sheet.getCellRange(1, 1, 13, 4));

        //Add Default Style to the table.        sheet.getListObjects().get(0).setBuiltInTableStyle(TableBuiltInStyles.TableStyleLight9);

        //Save the Excel file
        workbook.saveToFile(outputFile, ExcelVersion.Version2010);
    }
}

Effective screenshot after creating the Excel table:

Create a table in Excel in Java

Published in Data

Friday, 20 December 2019 07:14

Create Excel File in Java

Spire.XLS for Java is a feature rich API that supports creating, manipulating, converting and printing Excel files in Java applications. In this article, we will show you how to create an Excel file using Spire.XLS for Java.

import com.spire.xls.*;

import java.awt.*;

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

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);
        //Set name for the first worksheet
        sheet.setName("Data Sheet");

        //Create a CellStyle for header cells
        CellStyle style1 = workbook.getStyles().addStyle("Header Style");
        style1.getFont().setSize(12f);
        style1.getFont().setColor(Color.BLACK);
        style1.getFont().isBold(true);
        style1.setHorizontalAlignment(HorizontalAlignType.Center);
        style1.setVerticalAlignment(VerticalAlignType.Center);

        //Create a CellStyle for data cells
        CellStyle style2 = workbook.getStyles().addStyle("Data Style");
        style2.getFont().setSize(10f);
        style2.getFont().setColor(Color.BLACK);

        //Add data and apply style for header cells
        for (int column=1; column<5; column++)
        {
            CellRange header =sheet.getCellRange(1,column);
            header.setValue("Column " + column );
            header.setStyle(style1);
            header.setColumnWidth(15f);
        }

        // Add data and apply style for data cells
        for (int row=2; row<11; row++)
        {
            for (int column=1; column<5; column++)
            {
                CellRange cell = sheet.getCellRange(row, column);
                cell.setValue("Data " + row + ", " + column);
                cell.setStyle(style2);
            }
        }

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

Create Excel File in Java

Published in Document Operation

Friday, 20 December 2019 03:29

Add or remove worksheet in Java

Spire.XLS for Java supports to add a new worksheet to the existing Excel workbook, it also supports to remove a worksheet from the Excel workbook in Java. This article demonstrates how to insert and remove worksheet in Java applications.

Firstly, view the sample Excel Workbook with three sheets:

Add or remove worksheet in Java

Add a worksheet to an existing Excel workbook in Java:

import com.spire.xls.*;

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

        String inputFile = "Sample.xlsx";
        String outputFile = "output/AddWorksheet.xlsx";

        //Create a workbook and load a file
        Workbook workbook = new Workbook();
        workbook.loadFromFile(inputFile);

        //Add a new worksheet named "AddedSheet"
        Worksheet sheet = workbook.getWorksheets().add("AddNewSheet");
        sheet.getCellRange("C5").setText("This is a new sheet.");

        //Save the Excel file
        workbook.saveToFile(outputFile, ExcelVersion.Version2010);
    }
}

Effective screenshot after adding a new worksheet:

Add or remove worksheet in Java

Remove a worksheet from Excel workbook in Java:

import com.spire.xls.*;

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

        String inputFile = "Sample.xlsx";
        String outputFile = "output/RemoveWorksheet.xlsx";

        //Create a workbook and load a file
        Workbook workbook = new Workbook();
        workbook.loadFromFile(inputFile);

        //remove the second worksheet
        Worksheet sheet1 = workbook.getWorksheets().get(1);
        sheet1.remove();

        //Save the Excel file
        workbook.saveToFile(outputFile, ExcelVersion.Version2010);
    }
}

Effective screenshot after removing the second worksheet from the Excel file:

Add or remove worksheet in Java

Published in Worksheet

Thursday, 19 December 2019 09:46

Print Excel Documents in Java

This article demonstrates how to print Excel documents using Spire.XLS for Java.

import com.spire.xls.Workbook;

import javax.print.PrintService;
import java.awt.print.PageFormat;
import java.awt.print.Paper;
import java.awt.print.PrinterException;
import java.awt.print.PrinterJob;

public class PrintExcel {

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

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

        //Create a PrinterJob object
        PrinterJob printerJob = PrinterJob.getPrinterJob();

        //Specify printer name
        PrintService myPrintService = findPrintService("\\\\192.168.1.104\\HP LaserJet P1007");
        printerJob.setPrintService(myPrintService);

        //Create a PageFormat object and set it to the default size and orientation
        PageFormat pageFormat  = printerJob.defaultPage();

        //Return a copy of the Paper object associated with this PageFormat.
        Paper paper = pageFormat .getPaper();

        //Set the imageable area of this Paper.
        paper.setImageableArea(0,0,pageFormat .getWidth(),pageFormat .getHeight());

        //Set the Paper object for this PageFormat.
        pageFormat .setPaper(paper);

        //Set the number of copies
        printerJob .setCopies(1);

        //Call painter to render the pages in the specified format
        printerJob .setPrintable(workbook,pageFormat);

        //execute print
        try {
            printerJob.print();
        } catch (PrinterException e) {
            e.printStackTrace();
        }
    }
    //Get print service by printer name
    private static PrintService findPrintService(String printerName) {

        PrintService[] printServices = PrinterJob.lookupPrintServices();
        for (PrintService printService : printServices) {
            if (printService.getName().equals(printerName)) {
                return printService;
            }
        }
        return null;
    }
}

Published in Print

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

Insert and Read Formulas in Excel in Java

Create a table in Excel in Java

Create Excel File in Java

Add or remove worksheet in Java

Print Excel Documents in Java

Purchase

Partnership

Products

Corporation