Java (482)
Worksheet names often convey the purpose or content of the sheet. Extracting these names can help in documenting the structure of a workbook. New users or collaborators can quickly understand what data is stored in each worksheet just by looking at the extracted names. This article will demonstrate how to get worksheet names in Excel in Java using Spire.XLS for Java.
Install Spire.XLS for Java
First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.
<repositories>
<repository>
<id>com.e-iceblue</id>
<name>e-iceblue</name>
<url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls</artifactId>
<version>16.3.2</version>
</dependency>
</dependencies>
Get All Worksheet Names in Excel in Java
Spire.XLS for Java provides the Worksheet.getName() method to retrieve the name of a Worksheet. To get the names of all worksheets in Excel (including hidden ones), you can iterate through each worksheet and get their names with this method. The following are the detailed steps:
- Create a Workbook instance.
- Load an Excel file using Workbook.loadFromFile() method.
- Create a StringBuilder instance to store the retrieved worksheet names.
- Iterate through each worksheet.
- Get the name of each worksheet using Worksheet.getName() method and then append it to the StringBuilder instance.
- Write the contents of the StringBuilder to a txt file.
- Java
import java.io.*;
import com.spire.xls.*;
public class getWorksheetNames {
public static void main(String[] args) throws IOException {
// Create a Workbook object
Workbook workbook = new Workbook();
// Load an Excel document
workbook.loadFromFile("BudgetSum.xlsx");
// Create a StringBuilder to store the worksheet names
StringBuilder stringBuilder = new StringBuilder();
// Iterate through each worksheet in the workbook
for (Object worksheet : workbook.getWorksheets()) {
// Get the current worksheet
Worksheet sheet = (Worksheet) worksheet;
// Get the worksheet name and append it to the StringBuilder
stringBuilder.append(sheet.getName() + "\r\n");
}
// Write the contents of the StringBuilder to a text file
FileWriter fw = new FileWriter("GetWorksheetNames.txt", true);
BufferedWriter bw = new BufferedWriter(fw);
bw.append(stringBuilder);
bw.close();
fw.close();
// Release resources
workbook.dispose();
}
}
Get Hidden Worksheet Names in Excel in Python
If you only need to retrieve the names of the hidden worksheets, you can first iterate through each worksheet to find the hidden worksheets and then get their names through the Worksheet.getName() method. The following are the detailed steps:
- Create a Workbook instance.
- Load an Excel file using Workbook.loadFromFile() method.
- Create a StringBuilder instance to store the retrieved worksheet names.
- Iterate through each worksheet to find the hidden worksheets.
- Get the name of each hidden worksheet using Worksheet.getName() method and then append it to the StringBuilder instance.
- Write the contents of the StringBuilder to a txt file.
- Java
import java.io.*;
import com.spire.xls.*;
public class getHiddenWorksheetNames {
public static void main(String[] args) throws IOException {
// Create a Workbook object
Workbook workbook = new Workbook();
// Load an Excel document
workbook.loadFromFile("BudgetSum.xlsx");
// Create a StringBuilder to store the worksheet names
StringBuilder stringBuilder = new StringBuilder();
// Iterate through each worksheet in the workbook
for (Object worksheet : workbook.getWorksheets()) {
// Get the current worksheet
Worksheet sheet = (Worksheet) worksheet;
// Detect the hidden worksheet
if (sheet.getVisibility() == WorksheetVisibility.Hidden) {
// Get the hidden worksheet name and append it to the StringBuilder
stringBuilder.append(sheet.getName() + "\r\n");
}
}
// Write the contents of the StringBuilder to a text file
FileWriter fw = new FileWriter("GetHiddenWorksheetNames.txt", true);
BufferedWriter bw = new BufferedWriter(fw);
bw.append(stringBuilder);
bw.close();
fw.close();
// Release resources
workbook.dispose();
}
}
Get a Free License
To fully experience the capabilities of Spire.XLS for Java without any evaluation limitations, you can request a free 30-day trial license.
This article demonstrates how to adjust the header and footer distance in a Word document using Spire.Doc for Java.
import com.spire.doc.Document;
import com.spire.doc.FileFormat;
import com.spire.doc.Section;
public class AdjustHeaderFooterDistance {
public static void main(String[] args){
//Create a Document instance
Document doc = new Document();
//Load a Word document
doc.loadFromFile("Headers and Footers.docx");
//Get the first section
Section section = doc.getSections().get(0);
//Adjust the header distance
section.getPageSetup().setHeaderDistance(100);
//Adjust the footer distance
section.getPageSetup().setFooterDistance(100);
//Save the result document
doc.saveToFile("Output.docx", FileFormat.Docx);
}
}
Screenshot
Header:
Footer:
This article demonstrates how to extract OLE objects from an Excel document using Spire.XLS for Java.
import com.spire.xls.*;
import com.spire.xls.core.IOleObject;
import java.io.*;
public class ExtractOLEObjects {
public static void main(String[] args){
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load the Excel document
workbook.loadFromFile("OLEObjectsExample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//Extract ole objects
if (sheet.hasOleObjects()) {
for (int i = 0; i < sheet.getOleObjects().size(); i++) {
IOleObject object = sheet.getOleObjects().get(i);
OleObjectType type = sheet.getOleObjects().get(i).getObjectType();
switch (type) {
//Word document
case WordDocument:
byteArrayToFile(object.getOleData(), "output/extractOLE.docx");
break;
//PowerPoint document
case PowerPointSlide:
byteArrayToFile(object.getOleData(), "output/extractOLE.pptx");
break;
//PDF document
case AdobeAcrobatDocument:
byteArrayToFile(object.getOleData(), "output/extractOLE.pdf");
break;
//Excel document
case ExcelWorksheet:
byteArrayToFile(object.getOleData(), "output/extractOLE.xlsx");
break;
}
}
}
}
public static void byteArrayToFile(byte[] datas, String destPath) {
File dest = new File(destPath);
try (InputStream is = new ByteArrayInputStream(datas);
OutputStream os = new BufferedOutputStream(new FileOutputStream(dest, false));) {
byte[] flush = new byte[1024];
int len = -1;
while ((len = is.read(flush)) != -1) {
os.write(flush, 0, len);
}
os.flush();
} catch (IOException e) {
e.printStackTrace();
}
}
}
The following screenshot shows the extracted OLE documents:
