This article demonstrates how to extract text from textbox within a worksheet using Spire.XLS for Java.

Sample Document

Extract Text from Textbox in Excel in Java

import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
import com.spire.xls.core.spreadsheet.shapes.XlsTextBoxShape;

public class ExtractTextFromTextbox {

    public static void main(String[] args) {

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

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

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

        //Get the first textbox
        XlsTextBoxShape shape = (XlsTextBoxShape)sheet.getTextBoxes().get(0);

        //Get the text inside the textbox
        String text = shape.getText();
        System.out.print(text);
    }
}

Output

Extract Text from Textbox in Excel in Java

Tuesday, 12 May 2020 08:32

Hide or Show Comments in Excel in Java

This article demonstrates how to hide or show comments in an Excel file using Spire.XLS for Java.

The sample Excel file:

Hide or Show Comments in Excel in Java

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

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

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

        //Hide the first and the second comments
        sheet.getComments().get(0).isVisible(false);
        sheet.getComments().get(1).isVisible(false);

        //Show the third comment
        sheet.getComments().get(2).isVisible(true);

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

The output Excel file:

Hide or Show Comments in Excel in Java

Tuesday, 12 May 2020 08:19

Convert HTML to Excel in C#/VB.NET

With Spire.XLS, we could save Excel to HTML easily. Starts from Spire.XLS V10.4.9, Spire.XLS starts to support saving HTML to Excel in C#/VB.NET. This article will show you how to convert HTML to Excel with the help of Spire.XLS.

Firstly, view the sample HTML file:

Convert HTML to Excel in C#/VB.NET

C# of converting HTML to Excel:

using Spire.Xls;

namespace HTML_to_Excel
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load the sample document 
            Workbook workbook = new Workbook();
            workbook.LoadFromHtml("Sample.html");

            //AutoFit rows
            Worksheet sheet = workbook.Worksheets[0];
            sheet.AllocatedRange.AutoFitRows();

            //Save the document to file
            workbook.SaveToFile("Result.xlsx",FileFormat.Version2010);
            
        }
    }
}
VB.NET
Imports Spire.Xls

Namespace HTML_to_Excel
    
    Class Program
        
        Private Shared Sub Main(ByVal args() As String)
            'Load the sample document 
            Dim workbook As Workbook = New Workbook
            workbook.LoadFromHtml("Sample.html")
            'AutoFit rows
            Dim sheet As Worksheet = workbook.Worksheets(0)
            sheet.AllocatedRange.AutoFitRows
            'Save the document to file
            workbook.SaveToFile("Result.xlsx", FileFormat.Version2010)
        End Sub
    End Class
End Namespace

Effective screenshot of converting HTML to Excel:

Convert HTML to Excel in C#/VB.NET

This article demonstrates how to set and get slide title in a PowerPoint document using Spire.Presentation for Java.

Set slide title

import com.spire.presentation.FileFormat;
import com.spire.presentation.ISlide;
import com.spire.presentation.Presentation;

public class SetSlideTitle {
    public static void main(String[] args) throws Exception {
        //Create a Presentation instance
        Presentation ppt = new Presentation();
        //Get the first slide
        ISlide slide = ppt.getSlides().get(0);
        
        //Set title for the slide
        slide.setTitle("Tile Text");
        
        //Save the result document
        ppt.saveToFile("SetTitle.pptx", FileFormat.PPTX_2013);
    }
}

Set and Get Slide Title in PowerPoint in Java

Get slide title

import com.spire.presentation.ISlide;
import com.spire.presentation.Presentation;

public class GetSlideTitle {
    public static void main(String[] args) throws Exception {
        //Create a Presentation instance
        Presentation ppt = new Presentation();
        //Load a PowerPoint document
        ppt.loadFromFile("SetTitle.pptx");

        //Get the first slide
        ISlide slide = ppt.getSlides().get(0);

        //Print out the title of the slide
        String tile = slide.getTitle();
        System.out.println(tile);
    }
}

Set and Get Slide Title in PowerPoint in Java

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

This article will introduce how to extract text from SmartArt in PowerPoint in Java applications.

Firstly view the sample document:

Java extract text from SmartArt in PowerPoint

import com.spire.presentation.Presentation;
import com.spire.presentation.diagrams.ISmartArt;
import java.io.*;

public class extractTextFromSmartArt {
    public static void main(String[] args) throws Exception {
        Presentation presentation = new Presentation();
        presentation.loadFromFile("Sample.pptx");

        //Create a new TXT File
        String result = "output/extractTextFromSmartArt.txt";
        File file=new File(result);
        if(file.exists()){
            file.delete();
        }
        file.createNewFile();
        FileWriter fw =new FileWriter(file,true);
        BufferedWriter bw =new BufferedWriter(fw);

        bw.write("Below is extracted text from SmartArt:" + "\r\n");

        //Traverse through all the slides of the PPT file and find the SmartArt shapes.
        for (int i = 0; i < presentation.getSlides().getCount(); i++)
        {
            for (int j = 0; j < presentation.getSlides().get(i).getShapes().getCount(); j++)
            {
                if (presentation.getSlides().get(i).getShapes().get(j) instanceof ISmartArt)
                {
                    ISmartArt smartArt = (ISmartArt)presentation.getSlides().get(i).getShapes().get(j);

                    //Extract text from SmartArt and append to the StringBuilder object.
                    for (int k = 0; k < smartArt.getNodes().getCount(); k++)
                    {
                        bw.write(smartArt.getNodes().get(k).getTextFrame().getText() + "\r\n");
                    }
                }
            }
        }
        bw.flush();
        bw.close();
        fw.close();

    }
}

Effective screenshot of the extracting Text from SmartArt:

Java extract text from SmartArt in PowerPoint

This article demonstrates how to change the font color of the text in paragraphs using Spire.Doc for Java.

The following screenshot shows the example Word document before changing font color:

Change the Font Color of Paragraph Text in Word in Java

import com.spire.doc.*;
import com.spire.doc.documents.*;
import com.spire.doc.fields.TextRange;

import java.awt.*;

public class ChangeFontColor {
    public static void main(String[] args){
        //Create a Document instance
        Document doc = new Document();
        //Load the Word document
        doc.loadFromFile("FontColorExample.docx");

        //Get the first section and second paragraph
        Section section = doc.getSections().get(0);
        Paragraph p1 = section.getParagraphs().get(1);

        //Iterate through the childObjects of the second paragraph 
        for (int i = 0; i < p1.getChildObjects().getCount(); i ++)
        {
            //Change font color of the text in the second paragraph
            if ( p1.getChildObjects().get(i) instanceof TextRange)
            {
                TextRange tr = (TextRange) p1.getChildObjects().get(i);
                tr.getCharacterFormat().setTextColor(Color.green);
            }
        }

        //Get the third paragraph
        Paragraph p2 = section.getParagraphs().get(2);

        //Iterate through the childObjects of the third paragraph
        for (int j = 0; j < p2.getChildObjects().getCount(); j ++)
        {
            //Change font color of the text in the third paragraph
            if ( p2.getChildObjects().get(j) instanceof TextRange)
            {
                TextRange tr = (TextRange) p2.getChildObjects().get(j);
                tr.getCharacterFormat().setTextColor(Color.blue);
            }
        }
        
        //Save the resultant document
        doc.saveToFile("ChangeFontColor.docx", FileFormat.Docx_2013);
    }
}

The following screenshot shows the output Word document after changing font color:

Change the Font Color of Paragraph Text in Word in Java

This article demonstrates how to set different header and footer for the fisrt page using Spire.XLS for Java.

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

public class SetDifferentHeaderFooter {

    public static void main(String[] args) {

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

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

        //Insert text in A1 and J1
        sheet.getCellRange("A1").setText("page 1");
        sheet.getCellRange("J1").setText("page 2");

        //Set different first page
        sheet.getPageSetup().setDifferentFirst((byte)1);

        //Set header string and footer string for the first page
        sheet.getPageSetup().setFirstHeaderString("First header");
        sheet.getPageSetup().setFirstFooterString("First footer");

        //Set header string and footer string for other pages
        sheet.getPageSetup().setCenterHeader("Header of other pages");
        sheet.getPageSetup().setCenterFooter("Footer of other pages");

        //Save the document
        workbook.saveToFile("DifferentFirstPage.xlsx", FileFormat.Version2016);
    }
}

Set Different Header and Footer for the First Page in Java

This article will demonstrate how to add the traffic lights icons in Java applications by using Spire.XLS for Java.

import com.spire.xls.*;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;

import java.awt.*;

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

        //Add a worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Add some data to the cell range and set the format for them
        sheet.getCellRange("A1").setText("Traffic Lights");
        sheet.getCellRange("A2").setNumberValue(0.95);
        sheet.getCellRange("A2").setNumberFormat("0%");
        sheet.getCellRange("A3").setNumberValue(0.5);
        sheet.getCellRange("A3").setNumberFormat("0%");
        sheet.getCellRange("A4").setNumberValue(0.1);
        sheet.getCellRange("A4").setNumberFormat("0%");
        sheet.getCellRange("A5").setNumberValue(0.9);
        sheet.getCellRange("A5").setNumberFormat("0%");
        sheet.getCellRange("A6").setNumberValue(0.7);
        sheet.getCellRange("A6").setNumberFormat("0%");
        sheet.getCellRange("A7").setNumberValue(0.6);
        sheet.getCellRange("A7").setNumberFormat("0%");

        //Set the height of row and width of column for Excel cell range
        sheet.getAllocatedRange().setRowHeight(20);
        sheet.getAllocatedRange().setColumnWidth(25);

        //Add a conditional formatting
        XlsConditionalFormats conditional = sheet.getConditionalFormats().add();
        conditional.addRange(sheet.getAllocatedRange());
        IConditionalFormat format1 = conditional.addCondition();

        //Add a conditional formatting of cell range and set its type to CellValue
        format1.setFormatType(ConditionalFormatType.CellValue);
        format1.setFirstFormula("300");
        format1.setOperator(ComparisonOperatorType.Less);
        format1.setFontColor(Color.black);
        format1.setBackColor(Color.lightGray);

        //Add a conditional formatting of cell range and set its type to IconSet
        conditional.addRange(sheet.getAllocatedRange());
        IConditionalFormat format = conditional.addCondition();
        format.setFormatType(ConditionalFormatType.IconSet);
        format.getIconSet().setIconSetType(IconSetType.ThreeTrafficLights1);

        //Save to file
        String result = "output/setTrafficLightsIcons_result.xlsx";
        workbook.saveToFile(result, ExcelVersion.Version2013);
    }
}

Effective screenshot of traffic lights icons on Excel worksheet:

Java add the traffic lights icons to Excel

Friday, 24 April 2020 08:06

Add picture to Excel chart in Java

This article will introduce how to add a picture to Excel chart in java applications by using Spire.XLS for java.

import com.spire.xls.*;
import com.spire.xls.core.IPictureShape;
import com.spire.xls.core.IShape;
import java.awt.*;

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

        //Load the document from disk
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx");

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

        //get the first chart
        Chart chart = sheet.getCharts().get(0);

        //add the picture to chart and set its format
        IShape picture = chart.getShapes().addPicture("48.png");
        ((IPictureShape) picture).getLine().setDashStyle(ShapeDashLineStyleType.DashDotDot);
        ((IPictureShape) picture).getLine().setForeColor(Color.blue);

         //save the document 
        String result = "output/AddPictureToChart.xlsx";
        workbook.saveToFile(result, ExcelVersion.Version2010);

    }
}

Effective screenshot after adding picture to Excel Chart:

Add picture to Excel chart in Java

Page 2 of 121