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"); }