Extract cells from Microsoft Office Excel spreadsheets Leave feedback
On this page
GroupDocs.Parser provides the functionality to extract cells from spreadsheets. The following methods are used for this purpose:
- getWorksheetInfo() - returns the information about all worksheets in the spreadsheet;
- getWorksheetInfo(int) - returns the information about the worksheet with the specified index;
- getWorksheetCells(int) - returns the collection of cells of the worksheet;
- getWorksheetCells(int, WorksheetOptions) - returns the collection of cells contained in the ranges set by WorksheetOptions.
To check whether the document supports this functionality, use the isWorksheet method of the Features class. If cells extraction isn’t supported, getWorksheetCells and getWorksheetInfo(int) methods return null.
Here are the steps to extract cells from Microsoft Office Excel spreadsheet:
- Instantiate Parser object for the initial spreadsheet;
- Call getWorksheetInfo method and obtain the collection of WorksheetInfo objects;
- Call getWorksheetCells(int) method with the worksheet index and obtain the collection of WorksheetCell objects;
- Iterate through the collection of WorksheetCell objects and get a cell text value.
The following example shows how to extract cells from Microsoft Office Excel spreadsheet:
// Create an instance of Parser class
try (Parser parser = new Parser(Constants.SampleXlsx)) {
// Check if worksheet cells extraction is supported
if (!parser.getFeatures().isWorksheet()) {
System.out.println("Worksheet cells extraction isn't supported.");
return;
}
// Get the information about worksheets
Iterable<WorksheetInfo> info = parser.getWorksheetInfo();
// Iterate over worksheet information
for (WorksheetInfo i : info) {
// Print the worksheet name
System.out.println(i.getName());
System.out.println();
// Get the worksheet cells
Iterable<WorksheetCell> cells = parser.getWorksheetCells(i.getIndex());
// Iterate over cells
for (WorksheetCell c : cells) {
// Print the cell information and text value
System.out.println(String.format("Row: %d Column: %d RowSpan: %d ColumnSpan: %d",
c.getRowIndex(), c.getColumnIndex(), c.getRowSpan(), c.getColumnSpan()));
System.out.println(c.getText());
System.out.println();
}
}
}
WorksheetInfo contains the worksheet index and name and the bounds of the data on the worksheet (getMinRowIndex, getMaxRowIndex, getMinColumnIndex, getMaxColumnIndex). Use these values to build a WorksheetRange and pass it to WorksheetOptions to extract only the cells you need. The WorksheetOptions constructor accepts one or more ranges.
The following example shows how to extract cells from Microsoft Office Excel spreadsheet with customization:
// Create an instance of Parser class
try (Parser parser = new Parser(Constants.SampleXlsx)) {
// Check if worksheet cells extraction is supported
if (!parser.getFeatures().isWorksheet()) {
System.out.println("Worksheet cells extraction isn't supported.");
return;
}
// Get the information about the first worksheet
WorksheetInfo info = parser.getWorksheetInfo(0);
// Print the worksheet name
System.out.println(info.getName());
System.out.println();
// Create the range that represents the first two rows
WorksheetRange range = new WorksheetRange(
info.getMinRowIndex(),
Math.min(info.getMinRowIndex() + 1, info.getMaxRowIndex()),
info.getMinColumnIndex(),
info.getMaxColumnIndex());
// Get the worksheet cells from the first two rows
Iterable<WorksheetCell> cells = parser.getWorksheetCells(0, new WorksheetOptions(range));
// Iterate over cells
for (WorksheetCell c : cells) {
// Print the cell information and text value
System.out.println(String.format("Row: %d Column: %d RowSpan: %d ColumnSpan: %d",
c.getRowIndex(), c.getColumnIndex(), c.getRowSpan(), c.getColumnSpan()));
System.out.println(c.getText());
System.out.println();
}
}
You may easily run the code above and see the feature in action in our GitHub examples:
Along with full featured Java library we provide simple, but powerful free Apps.
You are welcome to parse documents and extract data from PDF, DOC, DOCX, PPT, PPTX, XLS, XLSX, Emails and more with our free online Free Online Document Parser App.
Was this page helpful?
Any additional feedback you'd like to share with us?
Please tell us how we can improve this page.
Thank you for your feedback!
We value your opinion. Your feedback will help us improve our documentation.
