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.getSharedDataDir(UsingHTMLProperty.class) + "SmartMarkers/";
Workbook workbook = new Workbook();
WorkbookDesigner designer = new WorkbookDesigner();
designer.setWorkbook(workbook);
workbook.getWorksheets().get(0).getCells().get("A1").putValue("&=$VariableArray(HTML)");
designer.setDataSource(
"VariableArray", new String[] {"Hello <b>World</b>", "Arabic", "Hindi", "Urdu", "French"});
designer.process();
workbook.save(dataDir + "UHProperty-out.xls");
}
public static void main(String[] args) throws Exception {
String dataDir = Utils.getSharedDataDir(InsertingARow.class) + "rows_cloumns/";
// 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);
// Inserting a row into the worksheet at 3rd position
worksheet.getCells().insertRows(2, 1);
// Saving the modified Excel file in default (that is Excel 2000) format
workbook.save(dataDir + "InsertingARow_out.xls");
}
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 {
// Path to source file
String dataDir =
Utils.getSharedDataDir(AccessingMaximumDisplayRangeofWorksheet.class) + "data/";
// Instantiate a workbook from source file
Workbook workbook = new Workbook(dataDir + "Book1.xlsx");
// Access the first workbook
Worksheet worksheet = workbook.getWorksheets().get(0);
// Access the Maximum Display Range
Range range = worksheet.getCells().getMaxDisplayRange();
// Print the Maximum Display Range RefersTo property
System.out.println("Maximum Display Range: " + range.getRefersTo());
}
public static void main(String[] args) throws Exception {
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(CopyingSingleRow.class) + "articles/";
// Create an instance of Workbook class by loading the existing spreadsheet
Workbook workbook = new Workbook(dataDir + "aspose-sample.xlsx");
// Get the cells collection of first worksheet
Cells cells = workbook.getWorksheets().get(0).getCells();
// Copy the first row to next 10 rows
for (int i = 1; i <= 10; i++) {
cells.copyRow(cells, 0, i);
}
// Save the result on disc
workbook.save(dataDir + "CSingleRow_out.xlsx");
}
public static void main(String[] args) throws Exception {
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(SettheCommentofTableorListObject.class) + "articles/";
// Open the template file.
Workbook workbook = new Workbook(dataDir + "source.xlsx");
// Access first worksheet.
Worksheet worksheet = workbook.getWorksheets().get(0);
// Access first list object or table.
ListObject lstObj = worksheet.getListObjects().get(0);
// Set the comment of the list object
lstObj.setComment("This is Aspose.Cells comment.");
// Save the workbook in xlsx format
workbook.save(dataDir + "STheCofTOrListObject_out.xlsx", SaveFormat.XLSX);
}
public static void main(String[] args) throws Exception {
// The path to the documents directory.
String dataDir =
Utils.getSharedDataDir(ConvertingsingleWorksheetToXPS.class) + "loading_saving/";
// Open an Excel file
Workbook workbook = new Workbook(dataDir + "Book1.xlsx");
// Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
// Apply different Image and Print options
ImageOrPrintOptions options = new ImageOrPrintOptions();
// Set the format
options.setSaveFormat(SaveFormat.XPS);
// Render the sheet with respect to specified printing options
SheetRender render = new SheetRender(sheet, options);
render.toImage(0, dataDir + "CSingleWorksheetToXPS_out.xps");
}
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.getSharedDataDir(SetBackgroundPictureforWorksheet.class) + "articles/";
// Instantiate a new Workbook.
Workbook workbook = new Workbook();
// Get the first worksheet.
Worksheet sheet = workbook.getWorksheets().get(0);
// Get the image file.
File file = new File(dataDir + "school.jpg");
// Get the picture into the streams.
byte[] imageData = new byte[(int) file.length()];
FileInputStream fis = new FileInputStream(file);
fis.read(imageData);
// Set the background image for the sheet.
sheet.setBackgroundImage(imageData);
// Save the excel file
workbook.save(dataDir + "SBPforWorksheet.xls");
}
public static void main(String[] args) throws Exception {
String dataDir = Utils.getSharedDataDir(ReadingAndWritingQueryTable.class) + "articles/";
// Create workbook from source excel file
Workbook workbook = new Workbook(dataDir + "Sample.xlsx");
// Access first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
// Access first Query Table
QueryTable qt = worksheet.getQueryTables().get(0);
// Print Query Table Data
System.out.println("Adjust Column Width: " + qt.getAdjustColumnWidth());
System.out.println("Preserve Formatting: " + qt.getPreserveFormatting());
// Now set Preserve Formatting to true
qt.setPreserveFormatting(true);
// Save the workbook
workbook.save(dataDir + "RAWQueryTable_out.xlsx");
}
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.");
}
public static void main(String[] args) throws Exception {
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ActivatingSheetsandActivatingCell.class) + "articles/";
// Instantiate a new Workbook
Workbook workbook = new Workbook();
// Get the first worksheet in the workbook
Worksheet worksheet = workbook.getWorksheets().get(0);
// Get the cells in the worksheet
Cells cells = worksheet.getCells();
// Input data into B2 cell
cells.get(1, 1).putValue("Hello World!");
// Set the first sheet as an active sheet
workbook.getWorksheets().setActiveSheetIndex(0);
// Set B2 cell as an active cell in the worksheet
worksheet.setActiveCell("B2");
// Set the B column as the first visible column in the worksheet
worksheet.setFirstVisibleColumn(1);
// Set the 2nd row as the first visible row in the worksheet
worksheet.setFirstVisibleRow(1);
// Save the excel file
workbook.save(dataDir + "ASAActivatingCell_out.xls");
}
public static void main(String[] args) throws Exception {
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(InsertLinkedPicturefromWebAddress.class) + "articles/";
// Instantiate a new Workbook.
Workbook workbook = new Workbook();
// Insert a linked picture (from Web Address) to B2 Cell.
Picture pic =
(Picture)
workbook
.getWorksheets()
.get(0)
.getShapes()
.addLinkedPicture(1, 1, 100, 100, "http://www.aspose.com/Images/aspose-logo.jpg");
// Set the source of the inserted image.
pic.setSourceFullName("http://www.aspose.com/images/aspose-logo.gif");
// Set the height and width of the inserted image.
pic.setHeightInch(1.04);
pic.setWidthInch(2.6);
// Save the Excel file.
workbook.save(dataDir + "ILPfromWebAddress_out.xlsx");
}
public static void main(String[] args) throws Exception {
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(WrapTextinCell.class) + "articles/";
// Create Workbook Object
Workbook wb = new Workbook();
// Open first Worksheet in the workbook
Worksheet ws = wb.getWorksheets().get(0);
// Get Worksheet Cells Collection
Cells cell = ws.getCells();
// Increase the width of First Column Width
cell.setColumnWidth(0, 35);
// Increase the height of first row
cell.setRowHeight(0, 65);
// Add Text to the First Cell
cell.get(0, 0)
.setValue("I am using the latest version of Aspose.Cells to test this functionality");
// Get Cell's Style
Style style = cell.get(0, 0).getStyle();
// Set Text Wrap property to true
style.setTextWrapped(true);
// Set Cell's Style
cell.get(0, 0).setStyle(style);
// Save Excel File
wb.save(dataDir + "WrapTextinCell_out.xls");
}