Skip to main content

OpenXLS Developers Guide

Table of Contents​

Introduction​

Welcome to the OpenXLS Developers Guide, where we take the mundane world of spreadsheets and inject a little life into it! OpenXLS is a powerful Java API designed for reading, modifying, and creating Excel-compatible spreadsheets. Built entirely in Java, it offers seamless integration with your applications while maintaining high performance and compatibility.

Key Features​

  • Pure Java Implementation: No native dependencies or DLLs required. You can run this baby anywhere Java runs—like a bad penny, it just keeps coming back!
  • Excel Compatibility: Create and modify Excel-compatible files. Because who doesn’t want to play with spreadsheets?
  • Template Support: Use existing spreadsheets as templates. It’s like recycling, but for your data!
  • Multiple Output Formats: Generate XLS, XML, and HTML outputs. Variety is the spice of life, right?
  • High Performance: Optimized for handling large datasets. We’re talking about data so big it needs its own zip code!
  • Cross-Platform: Works on any Java-supported platform. It’s like the universal remote of spreadsheets!

Getting Started​

Prerequisites​

  • Java Development Kit (JDK) 1.4 or higher. If you’re still on 1.3, it’s time to upgrade, my friend.
  • Valid OpenXLS license. No license, no fun!
  • OpenXLS.jar in your project classpath. It’s like the secret sauce that makes everything work.

Installation​

  1. Add OpenXLS.jar to your project directory.
  2. Include openxls.lic in the same directory.
  3. Add OpenXLS.jar to your project's classpath.
<!-- Maven dependency example -->
<dependency>
    <groupId>com.valkyrlabs</groupId>
    <artifactId>openxls</artifactId>
    <version>latest</version>
</dependency>

Quick Start Guide​

  1. Import the required packages:
import com.valkyrlabs.OpenXLS.*;
  1. Create or load a workbook:
// Create new workbook
WorkBookHandle newBook = new WorkBookHandle();

// Or load existing workbook
WorkBookHandle existingBook = new WorkBookHandle("path/to/template.xls");
  1. Access worksheets:
WorkSheetHandle sheet = book.getWorkSheet("Sheet1");
  1. Manipulate cells:
// Add new cell
sheet.add("Hello World", "A1");

// Access existing cell
CellHandle cell = sheet.getCell("B2");
cell.setVal("New Value");

WorkSheet Operations​

Creating and Managing Worksheets​

// Create new worksheet
book.createWorkSheet("Sales Data");

// Copy worksheet
book.copyWorkSheet("Sheet1", "Sheet1 Copy");

// Remove worksheet
book.removeWorkSheet("Sheet1");

Working with Rows and Columns​

// Insert row
sheet.insertRow(5);

// Insert row with formatting
sheet.insertRow(5, true); // true preserves formatting

// Remove row
sheet.removeRow(5);

// Insert column
sheet.insertCol("C");

// Remove column
sheet.removeCol("C", true); // true shifts remaining columns

Sheet Protection​

// Protect worksheet
sheet.setProtected(true);

// Unprotect worksheet
sheet.setProtected(false);

Cell Operations​

Cell Manipulation​

// Add new cell
CellHandle cell = sheet.add("Value", "A1");

// Move cell
cell.moveToCol("B");
cell.moveToRow(2);

// Remove cell
cell.remove(true);

Cell Formatting​

// Create new format
FormatHandle format = new FormatHandle(book);
format.setFont("Arial", Font.PLAIN, 10);
format.setForegroundColor(FormatHandle.COLOR_LT_BLUE);
format.setBackgroundPattern(FormatHandle.PATTERN_HOR_STRIPES);

// Apply format to cell
cell.setFormat(format);

Advanced Features​

Charts and Visualization​

// Get chart handle
ChartHandle chart = sheet.getChart("Performance");

// Modify chart series
chart.changeSeriesRange("Sheet1!C23:E23", "Sheet1!C23:G23");

// Update chart title
chart.setChartTitle("Sales Performance 2024");

// Copy chart to another sheet
book.copyChartToSheet("New Chart", "Sheet2");

Named Ranges​

// Create cell range
CellRange range = new CellRange("Sheet1!A1:B8", book);

// Create named range
NameHandle namedRange = new NameHandle("SalesData", range);

// Access named range
NameHandle existing = book.getNamedRange("SalesData");
CellHandle[] cells = existing.getCells();
// Add hyperlink to cell
cell.setURL("https://example.com");

// Add hyperlink with specific text
sheet.add("Click Here", "A1");
CellHandle linkCell = sheet.getCell("A1");
linkCell.setURL("https://example.com");

Performance Optimization​

String Handling​

// Optimize for Unicode content
book.setStringEncodingMode(WorkBookHandle.STRING_ENCODING_UNICODE);

// Optimize for non-Unicode content
book.setStringEncodingMode(WorkBookHandle.STRING_ENCODING_COMPRESSED);

// Enable automatic optimization
book.setStringEncodingMode(WorkBookHandle.STRING_ENCODING_AUTO);

Memory Management​

// Disable blank cell conversion
System.getProperties().put("com.valkyrlabs.openxls.convertmulblanks", "false");

// Control string duplication
book.setDupeStringMode(WorkBookHandle.ALLOWDUPES);

Debug Level Configuration​

// Set debug level (0-100)
book.setDebugLevel(10);

Date Handling​

// Add current date
java.util.Date now = new java.util.Date();
cell.setVal(now);

// Set date format
FormatHandle dateFormat = new FormatHandle(book);
dateFormat.setFormatPattern("m/d/yy h:mm");
cell.setFormat(dateFormat);

Template Guidelines​

Best Practices for Templates​

  1. Use Unique Placeholders

    • Avoid duplicate placeholder values. Seriously, nobody likes a copycat!
    • Use meaningful placeholder text. Make it count!
    • Consider data types when creating placeholders. Don’t throw spaghetti at the wall and see what sticks.

  2. Optimize File Size

    // Disable conversion of blank cells if needed
    System.getProperties().put("com.valkyrlabs.openxls.convertmulblanks", "false");

  3. Handle Missing Cells

    try {
        CellHandle cell = sheet.getCell("A1");
    } catch (CellNotFoundException e) {
        // Create cell if it doesn't exist
        cell = sheet.add("New Value", "A1");
    }

Compatibility Notes​

  1. Visual Basic Macros

    • OpenXLS preserves but cannot execute VB code. It’s like keeping a pet rock—nice to have, but not much use.
    • Macros remain functional when opened in Excel.

  2. File Format

    • Use Excel 97 or later formats (BIFF8). If you’re still using Excel 95, it’s time to upgrade!
    • Avoid saving as "Excel 95/97" compatibility mode. That’s like trying to fit a square peg in a round hole.
    • XML formats are not supported. Sorry, XML fans!

Troubleshooting Guide​

Common Issues and Solutions​

IssueSymptomsSolution
Data IssuesMissing or incorrect valuesCheck system logs, verify data source
PerformanceSlow processingReview string encoding and blank cell settings
MemoryOutOfMemoryErrorAdjust JVM heap size, optimize string handling
Format IssuesIncorrect appearanceVerify template compatibility, check format IDs

Common Error Messages​

try {
    // Your code here
} catch (WorkSheetNotFoundException e) {
    logger.error("Sheet not found: " + e.getMessage());
} catch (CellNotFoundException e) {
    logger.error("Cell not found: " + e.getMessage());
} catch (CellPositionConflictException e) {
    logger.error("Cell position conflict: " + e.getMessage());
}

API Reference​

Key Classes​

  • WorkBookHandle: Main entry point for workbook operations. The big cheese!
  • WorkSheetHandle: Sheet-level operations. The unsung hero of the spreadsheet world.
  • CellHandle: Cell-level operations. The little guy that does all the heavy lifting.
  • FormatHandle: Formatting and styling. Because looks matter!
  • ChartHandle: Chart manipulation. For when you want to make your data look pretty.
  • NameHandle: Named range operations. It’s all about the names, baby!

Resources​

Support​

For technical support:

License​

OpenXLS requires a valid license for use. Contact Valkyr Labs for licensing information.

This documentation is maintained by Valkyr Labs. For support, contact support@valkyrlabs.com