public static void main(String[] args) throws Exception {
// The path to the documents directory.
String dataDir = Utils.getDataDir(ImportingFromArrayList.class);
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Obtaining the reference of the worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
// Instantiating an ArrayList object
ArrayList list = new ArrayList();
// Add few names to the list as string values
list.add("laurence chen");
list.add("roman korchagin");
list.add("kyle huang");
list.add("tommy wang");
// Importing the contents of ArrayList to 1st row and first column vertically
worksheet.getCells().importArrayList(list, 0, 0, true);
// Saving the Excel file
workbook.save(dataDir + "DataImport.out.xls");
// Printing the name of the cell found after searching worksheet
System.out.println("Process completed successfully");
}
public static void main(String[] args) throws Exception {
// The path to the documents directory.
String dataDir = Utils.getDataDir(ApacheHideUnHideCells.class);
InputStream inStream = new FileInputStream(dataDir + "workbook.xls");
Workbook workbook = WorkbookFactory.create(inStream);
Sheet sheet = workbook.createSheet();
Row row = sheet.createRow(0);
row.setZeroHeight(true);
FileOutputStream fileOut = new FileOutputStream(dataDir + "ApacheHideUnhideCells.xls");
workbook.write(fileOut);
fileOut.close();
System.out.println("Process Completed.");
}
public static void main(String[] args) throws Exception {
// The path to the documents directory.
String dataDir = Utils.getDataDir(ProtectingSpecificColumnInWorksheet.class);
// Create a new workbook.
Workbook wb = new Workbook();
// Create a worksheet object and obtain the first sheet.
Worksheet sheet = wb.getWorksheets().get(0);
// Define the style object.
Style style;
// Define the styleflag object.
StyleFlag flag;
// Loop through all the columns in the worksheet and unlock them.
for (int i = 0; i <= 255; i++) {
style = sheet.getCells().getColumns().get(i).getStyle();
style.setLocked(false);
flag = new StyleFlag();
flag.setLocked(true);
sheet.getCells().getColumns().get(i).applyStyle(style, flag);
}
// Get the first column style.
style = sheet.getCells().getColumns().get(0).getStyle();
// Lock it.
style.setLocked(true);
// Instantiate the flag.
flag = new StyleFlag();
// Set the lock setting.
flag.setLocked(true);
// Apply the style to the first column.
sheet.getCells().getColumns().get(0).applyStyle(style, flag);
// Save the excel file.
wb.save(dataDir + "lockedcolumn.out.xls", FileFormatType.EXCEL_97_TO_2003);
// Print Message
System.out.println("Column protected successfully.");
}
public static void main(String[] args) throws Exception {
// The path to the documents directory.
String dataDir = Utils.getDataDir(AutofilterData.class);
// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "book1.xls");
// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);
// Creating AutoFilter by giving the cells range
AutoFilter autoFilter = worksheet.getAutoFilter();
CellArea area = new CellArea();
autoFilter.setRange("A1:B1");
// Saving the modified Excel file
workbook.save(dataDir + "output.xls");
// Print message
System.out.println("Process completed successfully");
}
public static void main(String[] args) throws Exception {
// The path to the documents directory.
String dataDir = Utils.getDataDir(ExportingDataFromWorksheets.class);
// Creating a file stream containing the Excel file to be opened
FileInputStream fstream = new FileInputStream(dataDir + "book1.xls");
// Instantiating a Workbook object
Workbook workbook = new Workbook(fstream);
// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);
// Exporting the contents of 7 rows and 2 columns starting from 1st cell to Array.
Object dataTable[][] = worksheet.getCells().exportArray(0, 0, 7, 2);
// Printing the name of the cell found after searching worksheet
System.out.println("No. Of Rows Imported: " + dataTable.length);
// Closing the file stream to free all resources
fstream.close();
}
public static void main(String[] args) throws Exception {
// The path to the documents directory.
String dataDir = Utils.getDataDir(AsposeFreezePanes.class);
// Instantiating a Excel object by excel file path
Workbook workbook = new Workbook();
// Accessing the first worksheet in the Excel file
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet worksheet1 = worksheets.get(0);
Worksheet worksheet2 = worksheets.add("Sheet2");
// Applying freeze panes settings
worksheet1.freezePanes(0, 2, 0, 2); // Freezing Columns
worksheet2.freezePanes(2, 0, 2, 0); // Freezing Rows
// Saving the modified Excel file in default format
workbook.save(dataDir + "AsposeFreezePanes_Out.xls");
// Print Message
System.out.println("Panes freeze successfull.");
}