Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
This Gist contains code snippets from examples of Aspose.Cells for Java.
This gist exceeds the recommended number of files (~10). To access all files, please clone this gist.
This Gist contains code snippets from examples of Aspose.Cells for Java.
Workbook workbook = new Workbook("SourceExcel.xls");
workbook.save("outputExcel.xlsx", SaveFormat.XLSX);
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the output directory.
String sourceDir = Utils.Get_SourceDirectory();Workbook workbook = new Workbook(sourceDir + "GetTextWidthSample.xlsx");
System.out.println("Text width: " + CellsHelper.getTextWidth(workbook.getWorksheets().get(0).getCells().get("A1").getStringValue(), workbook.getDefaultStyle().getFont(), 1));
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
//Source directory
String sourceDir = Utils.Get_SourceDirectory();
//Output directory
String outputDir = Utils.Get_OutputDirectory();
// Instantiating a Workbook object
Workbook workbook = new Workbook(sourceDir + "SampleChangeTickLabelDirection.xlsx");
Worksheet worksheet = workbook.getWorksheets().get(0);
// Load the chart from source worksheet
Chart chart = worksheet.getCharts().get(0);
chart.getCategoryAxis().getTickLabels().setDirectionType(ChartTextDirectionType.HORIZONTAL);
// Output the file
workbook.save(outputDir + "outputChangeTickLabelDirection.xlsx");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Instantiate a workbook
Workbook workbook = new Workbook();
// Access first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
// Set columns title
worksheet.getCells().get(0, 0).setValue("X");
worksheet.getCells().get(0, 1).setValue("Y");
// Create random data and save in the cells
for (int i = 1; i < 21; i++)
{
worksheet.getCells().get(i, 0).setValue(i);
worksheet.getCells().get(i, 1).setValue(0.8);
}
for (int i = 21; i < 41; i++)
{
worksheet.getCells().get(i, 0).setValue(i - 20);
worksheet.getCells().get(i, 1).setValue(0.9);
}
// Add a chart to the worksheet
int idx = worksheet.getCharts().add(ChartType.LINE_WITH_DATA_MARKERS, 1, 3, 20, 20);
// Access the newly created chart
Chart chart = worksheet.getCharts().get(idx);
// Set chart style
chart.setStyle(3);
// Set autoscaling value to true
chart.setAutoScaling(true);
// Set foreground color white
chart.getPlotArea().getArea().setForegroundColor(Color.getWhite());
// Set Properties of chart title
chart.getTitle().setText("Sample Chart");
// Set chart type
chart.setType(ChartType.LINE_WITH_DATA_MARKERS);
// Set Properties of categoryaxis title
chart.getCategoryAxis().getTitle().setText("Units");
//Set Properties of nseries
int s2_idx = chart.getNSeries().add("A2: A2", true);
int s3_idx = chart.getNSeries().add("A22: A22", true);
// Set IsColorVaried to true for varied points color
chart.getNSeries().setColorVaried(true);
// Set properties of background area and series markers
chart.getNSeries().get(s2_idx).getArea().setFormatting(FormattingType.CUSTOM);
chart.getNSeries().get(s2_idx).getMarker().getArea().setForegroundColor(Color.getYellow());
chart.getNSeries().get(s2_idx).getMarker().getBorder().setVisible(false);
// Set X and Y values of series chart
chart.getNSeries().get(s2_idx).setXValues("A2: A21");
chart.getNSeries().get(s2_idx).setValues("B2: B21");
// Set properties of background area and series markers
chart.getNSeries().get(s3_idx).getArea().setFormatting(FormattingType.CUSTOM);
chart.getNSeries().get(s3_idx).getMarker().getArea().setForegroundColor(Color.getGreen());
chart.getNSeries().get(s3_idx).getMarker().getBorder().setVisible(false);
// Set X and Y values of series chart
chart.getNSeries().get(s3_idx).setXValues("A22: A41");
chart.getNSeries().get(s3_idx).setValues("B22: B41");
// Save the workbook
workbook.save(outDir + "LineWithDataMarkerChart.xlsx", SaveFormat.XLSX);
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(GetChartSubTitleForODSFile.class) + "Charts/";
String filePath = dataDir + "SampleChart.ods";
Workbook workbook = new Workbook(filePath);
Worksheet worksheet = workbook.getWorksheets().get(0);
// Load the chart from source worksheet
Chart chart = worksheet.getCharts().get(0);
System.out.println("Chart Subtitle: " + chart.getSubTitle().getText());
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// directories
String outputDir = Utils.Get_OutputDirectory();
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Adding a chart to the worksheet
int index = workbook.getWorksheets().add(SheetType.CHART);
Worksheet sheet = workbook.getWorksheets().get(index);
sheet.getCharts().addFloatingChart(ChartType.COLUMN, 0, 0, 1024, 960);
sheet.getCharts().get(0).getNSeries().add("{1,2,3}", false);
// Add checkbox to the chart.
sheet.getCharts().get(0).getShapes().addShapeInChart(MsoDrawingType.CHECK_BOX, PlacementType.MOVE, 400, 400, 1000, 600);
sheet.getCharts().get(0).getShapes().get(0).setText("CheckBox 1");
// Convert chart to image with additional settings
workbook.save(outputDir + "InsertCheckboxInChartSheet_out.xlsx");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the directories.
String sourceDir = Utils.Get_SourceDirectory();
String outputDir = Utils.Get_OutputDirectory();
Workbook workbook = new Workbook(sourceDir + "ValidationsSample.xlsx");
// Access first worksheet.
Worksheet worksheet = workbook.getWorksheets().get(0);
// Accessing the Validations collection of the worksheet
Validation validation = worksheet.getValidations().get(0);
// Create your cell area.
CellArea cellArea = CellArea.createCellArea("D5", "E7");
// Adding the cell area to Validation
validation.addArea(cellArea, false, false);
// Save the output workbook.
workbook.save(outputDir + "ValidationsSample_out.xlsx");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
Workbook book = new Workbook(srcDir + "sampleValidation.xlsx");
Worksheet sheet = book.getWorksheets().get("Sheet1");
Cells cells = sheet.getCells();
Cell a2 = cells.get("A2");
Validation va2 = a2.getValidation();
if(va2.getInCellDropDown()) {
System.out.println("A2 is a dropdown");
} else {
System.out.println("A2 is NOT a dropdown");
}
Cell b2 = cells.get("B2");
Validation vb2 = b2.getValidation();
if(vb2.getInCellDropDown()) {
System.out.println("B2 is a dropdown");
} else {
System.out.println("B2 is NOT a dropdown");
}
Cell c2 = cells.get("C2");
Validation vc2 = c2.getValidation();
if(vc2.getInCellDropDown()) {
System.out.println("C2 is a dropdown");
} else {
System.out.println("C2 is NOT a dropdown");
}
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Output directory
String outputDir = Utils.Get_OutputDirectory();
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Create union range
UnionRange unionRange = workbook.getWorksheets().createUnionRange("sheet1!A1:A10,sheet1!C1:C10", 0);
// Put value "ABCD" in the range
unionRange.setValue("ABCD");
// Saving the modified Excel file in default format
workbook.save(outputDir + "CreateUnionRange_out.xlsx");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Load the source Excel file
Workbook workbook = new Workbook(srcDir + "sampleBackgroundFile.xlsx");
// Instantiate data sorter object
DataSorter sorter = workbook.getDataSorter();
// Add key for Column B, Sort it in descending order with background color red
sorter.addKey(1, SortOnType.CELL_COLOR, SortOrder.DESCENDING, Color.getRed());
// Sort the data based on the key
sorter.sort(workbook.getWorksheets().get(0).getCells(), CellArea.createCellArea("A2", "C6"));
// Save the output file
workbook.save(outDir + "outputSampleBackgroundFile.xlsx");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ExportingDataFromWorksheets.class) + "Data/";
// 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 number of rows exported
System.out.println("No. Of Rows Exported: " + dataTable.length);
// Closing the file stream to free all resources
fstream.close();
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the source directory.
String sourceDir = Utils.Get_SourceDirectory();
// The path to the output directory.
String outDir = Utils.Get_OutputDirectory();
Workbook workbook = new Workbook(sourceDir + "sampleMergedTemplate.xlsx");
Worksheet worksheet = workbook.getWorksheets().get(0);
ArrayList productList = new ArrayList();
for(int i = 0; i < 3; i++) {
productList.add(new Product("Test Product - " + i, i*2));
}
ImportTableOptions tableOptions = new ImportTableOptions();
tableOptions.setCheckMergedCells(true);
tableOptions.setFieldNameShown(false);
//Insert data to excel template
worksheet.getCells().importCustomObjects(productList, 1, 0, tableOptions);
workbook.save(outDir + "sampleMergedTemplate_out.xlsx", SaveFormat.XLSX);
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ImportingFromJson.class) + "Data/";
// Instantiating a Workbook object
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
// Read File
File file = new File(dataDir + "Test.json");
BufferedReader bufferedReader = new BufferedReader(new FileReader(file));
String jsonInput = "";
String tempString;
while ((tempString = bufferedReader.readLine()) != null) {
jsonInput = jsonInput + tempString;
}
bufferedReader.close();
// Set Styles
CellsFactory factory = new CellsFactory();
Style style = factory.createStyle();
style.setHorizontalAlignment(TextAlignmentType.CENTER);
style.getFont().setColor(Color.getBlueViolet());
style.getFont().setBold(true);
// Set JsonLayoutOptions
JsonLayoutOptions options = new JsonLayoutOptions();
options.setTitleStyle(style);
options.setArrayAsTable(true);
// Import JSON Data
JsonUtility.importData(jsonInput, worksheet.getCells(), 0, 0, options);
// Save Excel file
workbook.save(dataDir + "ImportingFromJson.out.xlsx");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the output directory.
String outputDir = Utils.Get_OutputDirectory();
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Adding a new worksheet to the Excel object
int i = workbook.getWorksheets().add();
// Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.getWorksheets().get(i);
// Accessing the "A1" cell from the worksheet
Cell cell = worksheet.getCells().get("A1");
// Adding some value to the "A1" cell
cell.putValue("Hello Aspose!");
// Obtaining the style of the cell
Style style = cell.getStyle();
// Setting the font weight to bold
style.getFont().setBold(true);
// Applying the style to the cell
cell.setStyle(style);
// Saving the Excel file
workbook.save(outputDir + "book1.out.xlsx", SaveFormat.XLSX);
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
public static void main(String[] args) throws Exception {
Workbook wb = new Workbook(srcDir + "sampleReplaceTagWithText.xlsx");
String tag = "TAG_2#TAG_1";
String replace = "1#ys";
for (int i = 0; i < tag.split("#").length; i++) {
sheetReplace(wb, "<" + tag.split("#")[i] + ">", replace.split("#")[i]);
}
PdfSaveOptions opts = new PdfSaveOptions();
wb.save(outDir + "outputReplaceTagWithText.pdf", opts);
// Print the message
System.out.println("ReplaceTagWithTextInTextBox executed successfully.");
}
public static void sheetReplace(Workbook workbook, String sFind, String sReplace) throws Exception
{
String finding = sFind;
for (Object obj : workbook.getWorksheets()) {
Worksheet sheet = (Worksheet)obj;
sheet.replace(finding, sReplace);
for (int j = 0; j < 3; j++) {
if (sheet.getPageSetup().getHeader(j) != null) {
sheet.getPageSetup().setHeader(j, sheet.getPageSetup().getHeader(j).replace(finding, sReplace));
}
if (sheet.getPageSetup().getFooter(j) != null) {
sheet.getPageSetup().setFooter(j, sheet.getPageSetup().getFooter(j).replace(finding, sReplace));
}
}
}
for (Object obj: workbook.getWorksheets()) {
Worksheet sheet = (Worksheet)obj;
sFind = sFind.replace("<", "&lt;");
sFind = sFind.replace(">", "&gt;");
for (Object obj1 : sheet.getTextBoxes()) {
TextBox mytextbox = (TextBox)obj1;
if (mytextbox.getHtmlText() != null) {
if (mytextbox.getHtmlText().indexOf(sFind) >= 0) {
mytextbox.setHtmlText(mytextbox.getHtmlText().replace(sFind, sReplace));
}
}
}
}
}
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
Workbook wb = new Workbook(srcDir + "SmartArt.xlsx");
for (Object obj : wb.getWorksheets())
{
Worksheet worksheet = (Worksheet)obj;
for (Object shp : worksheet.getShapes())
{
Shape shape = (Shape)shp;
shape.setAlternativeText("ReplacedAlternativeText"); // This works fine just as the normal Shape objects do.
if (shape.isSmartArt())
{
for (Shape smartart : shape.getResultOfSmartArt().getGroupedShapes())
{
smartart.setText("ReplacedText"); // This doesn't update the text in Workbook which I save to the another file.
}
}
}
}
com.aspose.cells.OoxmlSaveOptions options = new com.aspose.cells.OoxmlSaveOptions();
options.setUpdateSmartArt(true);
wb.save(outDir + "outputSmartArt.xlsx", options);
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the source directory.
String sourceDir = Utils.Get_SourceDirectory();
// Instantiate LoadOptions specified by the LoadFormat.
LoadOptions loadOptions = new LoadOptions(LoadFormat.FODS);
// Create a Workbook object and opening the file from its path
Workbook workbook = new Workbook(sourceDir + "SampleFods.fods", loadOptions);
// Print message
System.out.println("FODS file opened successfully!");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the source directory.
String sourceDir = Utils.Get_SourceDirectory();
// Instantiate LoadOptions specified by the LoadFormat.
LoadOptions loadOptions = new LoadOptions(LoadFormat.SXC);
// Create a Workbook object and opening the file from its path
Workbook workbook = new Workbook(sourceDir + "SampleSXC.sxc", loadOptions);
// Using the Sheet 1 in Workbook
Worksheet worksheet = workbook.getWorksheets().get(0);
// Accessing a cell using its name
Cell cell = worksheet.getCells().get("C3");
System.out.println("Cell Name: " + cell.getName() + " Value: " + cell.getStringValue());
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the source directory.
String sourceDir = Utils.Get_SourceDirectory();
// Instantiate LoadOptions specified by the LoadFormat.
LoadOptions loadOptions = new LoadOptions(LoadFormat.TSV);
// Create a Workbook object and opening the file from its path
Workbook workbook = new Workbook(sourceDir + "SampleTSVFile.tsv", loadOptions);
// Using the Sheet 1 in Workbook
Worksheet worksheet = workbook.getWorksheets().get(0);
// Accessing a cell using its name
Cell cell = worksheet.getCells().get("C3");
System.out.println("Cell Name: " + cell.getName() + " Value: " + cell.getStringValue());
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
String srcDir = Utils.Get_SourceDirectory();
String outDir = Utils.Get_OutputDirectory();
// Open an Excel file.
Workbook workbook = new Workbook(
srcDir + "sampleSetDefaultFontPropertyOfPdfSaveOptionsAndImageOrPrintOptions.xlsx");
// Rendering to PNG file format while setting the
// CheckWorkbookDefaultFont attribute to false.
// So, "Times New Roman" font would be used for any missing (not
// installed) font in the workbook.
ImageOrPrintOptions imgOpt = new ImageOrPrintOptions();
imgOpt.setImageType(ImageType.PNG);
imgOpt.setCheckWorkbookDefaultFont(false);
imgOpt.setDefaultFont("Times New Roman");
SheetRender sr = new SheetRender(workbook.getWorksheets().get(0), imgOpt);
sr.toImage(0, outDir + "outputSetDefaultFontProperty_ImagePNG.png");
// Rendering to TIFF file format while setting the
// CheckWorkbookDefaultFont attribute to false.
// So, "Times New Roman" font would be used for any missing (not
// installed) font in the workbook.
imgOpt.setImageType(ImageType.TIFF);
WorkbookRender wr = new WorkbookRender(workbook, imgOpt);
wr.toImage(outDir + "outputSetDefaultFontProperty_ImageTIFF.tiff");
// Rendering to PDF file format while setting the
// CheckWorkbookDefaultFont attribute to false.
// So, "Times New Roman" font would be used for any missing (not
// installed) font in the workbook.
PdfSaveOptions saveOptions = new PdfSaveOptions();
saveOptions.setDefaultFont("Times New Roman");
saveOptions.setCheckWorkbookDefaultFont(false);
workbook.save(outDir + "outputSetDefaultFontProperty_PDF.pdf", saveOptions);
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Create empty workbook
Workbook workbook = new Workbook();
// Register macro enabled add-in along with the function name
int id = workbook.getWorksheets().registerAddInFunction(srcDir + "TESTUDF.xlam", "TEST_UDF", false);
// Register more functions in the file (if any)
workbook.getWorksheets().registerAddInFunction(id, "TEST_UDF1"); //in this way you can add more functions that are in the same file
// Access first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
// Access first cell
Cell cell = worksheet.getCells().get("A1");
// Set formula name present in the add-in
cell.setFormula("=TEST_UDF()");
// Save workbook to output XLSX format.
workbook.save(outDir + "test_udf.xlsx", SaveFormat.XLSX);
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
public class DataObject
{
private int id;
private String name;
public DataObject(int id, String name)
{
this.id = id;
this.name = name;
}
public int getId()
{
return this.id;
}
public void setId(int value)
{
this.id = value;
}
public String getName()
{
return this.name;
}
public void setName(String value)
{
this.name = value;
}
}
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
public class Product
{
String name;
int quantity;
public Product(String name, int quantity)
{
this.quantity = quantity;
this.name = name;
}
public int getQuantity()
{
return this.quantity;
}
public void setQuantity(int value)
{
this.quantity = value;
}
public String getName()
{
return this.name;
}
public void setName(String value)
{
this.name = value;
}
}
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Load the Excel file.
Workbook wb = new Workbook(srcDir + "sampleInlineCharts.xlsx");
// Access the sheet
Worksheet ws = wb.getWorksheets().get(0);
// Set the print area.
ws.getPageSetup().setPrintArea("D2:M20");
// Initialize HtmlSaveOptions
HtmlSaveOptions options = new HtmlSaveOptions();
// Set flag to export print area only
options.setExportPrintAreaOnly(true);
//Save to HTML format
wb.save(outDir + "outputInlineCharts.html",options);
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Load the Excel file.
Workbook workbook = new Workbook(sourceDir + "Book1.xlsx");
// Initialize HtmlSaveOptions
HtmlSaveOptions options = new HtmlSaveOptions();
options.setExportHeadings(true);
//Save to HTML format
workbook.save(outputDir + "PrintHeadings_out.html", options);
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Set Html load options and keep precision true
HtmlLoadOptions loadOptions = new HtmlLoadOptions(LoadFormat.HTML);
// Load sample source file
Workbook wb = new Workbook(srcDir + "sampleSelfClosingTags.html", loadOptions);
// Save the workbook
wb.save(outDir + "outsampleSelfClosingTags.xlsx");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(AutoFitColumnsRowsLoadingHTML.class) + "LoadingSavingConvertingAndManaging/";
//Sample HTML.
String sampleHtml = "<html><body><table><tr><td>This is sample text.</td><td>Some text.</td></tr><tr><td>This is another sample text.</td><td>Some text.</td></tr></table></body></html>";
//Load html string into byte array input stream
ByteArrayInputStream bais = new ByteArrayInputStream(sampleHtml.getBytes());
//Load byte array stream into workbook.
Workbook wb = new Workbook(bais);
//Save the workbook in xlsx format.
wb.save(dataDir + "outputWithout_AutoFitColsAndRows.xlsx");
//Specify the HtmlLoadOptions and set AutoFitColsAndRows = true.
HtmlLoadOptions opts = new HtmlLoadOptions();
opts.setAutoFitColsAndRows(true);
//Load byte array stream into workbook with the above HtmlLoadOptions.
bais.reset();
wb = new Workbook(bais, opts);
//Save the workbook in xlsx format.
wb.save(dataDir + "outputWith_AutoFitColsAndRows.xlsx");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
String outputDir = Utils.Get_OutputDirectory();
// Instantiate a workbook
Workbook workbook = new Workbook();
// Put sample text in the first cell of first worksheet in the newly created workbook
workbook.getWorksheets().get(0).getCells().get("A1").setValue("DEMO TEXT ON SHEET1");
// Add second worksheet in the workbook
workbook.getWorksheets().add(SheetType.WORKSHEET);
// Set text in first cell of the second sheet
workbook.getWorksheets().get(1).getCells().get("A1").setValue("DEMO TEXT ON SHEET2");
// Set currently active sheet index to 1 i.e. Sheet2
workbook.getWorksheets().setActiveSheetIndex(1);
// Save workbook to SVG. It shall render the active sheet only to SVG
workbook.save(outputDir + "ConvertActiveWorksheetToSVG_out.svg");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
//Source directory
String sourceDir = Utils.Get_SourceDirectory();
LoadOptions loadOptions = new LoadOptions(LoadFormat.CSV);
// Load CSV file
Workbook workbook = new Workbook(sourceDir + "SampleCsv.csv", loadOptions);
Cell lastCell = workbook.getWorksheets().get(0).getCells().getLastCell();
// Set ExportRangeToJsonOptions
ExportRangeToJsonOptions options = new ExportRangeToJsonOptions();
Range range = workbook.getWorksheets().get(0).getCells().createRange(0, 0, lastCell.getRow() + 1, lastCell.getColumn() + 1);
String data = JsonUtility.exportRangeToJson(range, options);
// Print JSON
System.out.println(data);
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
//directories
String sourceDir = Utils.Get_SourceDirectory();
String outputDir = Utils.Get_OutputDirectory();
// Open the template file
Workbook workbook = new Workbook(sourceDir + "AddTooltipToHtmlSample.xlsx");
HtmlSaveOptions options = new HtmlSaveOptions();
options.setAddTooltipText(true);
// Save as Markdown
workbook.save(outputDir + "AddTooltipToHtmlSample_out.html", options);
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ConvertExcelFileToMarkdown.class) + "LoadingSavingConvertingAndManaging/";
Workbook workbook = new Workbook(dataDir + "Book1.xls");
// Save as Markdown
workbook.save(dataDir + "Book1.md", SaveFormat.MARKDOWN);
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
//Source directory
String sourceDir = Utils.Get_SourceDirectory();
//Output directory
String outputDir = Utils.Get_OutputDirectory();
// Read JSON file
String str = new String(Files.readAllBytes(Paths.get(sourceDir + "SampleJson.json")));
// Create empty workbook
Workbook workbook = new Workbook();
// Get Cells
Cells cells = workbook.getWorksheets().get(0).getCells();
// Set JsonLayoutOptions
JsonLayoutOptions importOptions = new JsonLayoutOptions();
importOptions.setConvertNumericOrDate(true);
importOptions.setArrayAsTable(true);
importOptions.setIgnoreArrayTitle(true);
importOptions.setIgnoreObjectTitle(true);
JsonUtility.importData(str, cells, 0, 0, importOptions);
// Save Workbook
workbook.save(outputDir + "SampleJson_out.csv");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the source directory.
String sourceDir = Utils.Get_SourceDirectory();
// The path to the output directory.
String outputDir = Utils.Get_OutputDirectory();
Workbook wb = new Workbook(sourceDir + "PagesBook1.xlsx");
PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();
pdfSaveOptions.setPageSavingCallback(new TestPageSavingCallback());
wb.save(outputDir + "DocumentConversionProgress.pdf", pdfSaveOptions);
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
class TestPageSavingCallback implements IPageSavingCallback {
public void pageStartSaving(PageStartSavingArgs args)
{
System.out.println("Start saving page index " + args.getPageIndex() + " of pages " + args.getPageCount());
//don't output pages before page index 2.
if (args.getPageIndex() < 2)
{
args.setToOutput(false);
}
}
public void pageEndSaving(PageEndSavingArgs args)
{
System.out.println("End saving page index " + args.getPageIndex() + " of pages " + args.getPageCount());
//don't output pages after page index 8.
if (args.getPageIndex() >= 8)
{
args.setHasMorePages(false);
}
}
}
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the source directory.
String sourceDir = Utils.Get_SourceDirectory();
// The path to the output directory.
String outputDir = Utils.Get_OutputDirectory();
Workbook wb = new Workbook(sourceDir + "sampleUseWorkbookRenderForImageConversion.xlsx");
ImageOrPrintOptions opts = new ImageOrPrintOptions();
opts.setPageSavingCallback(new TestTiffPageSavingCallback());
opts.setImageType(ImageType.TIFF);
WorkbookRender wr = new WorkbookRender(wb, opts);
wr.toImage(outputDir + "DocumentConversionProgressForTiff_out.tiff");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
class TestTiffPageSavingCallback implements IPageSavingCallback {
public void pageStartSaving(PageStartSavingArgs args)
{
System.out.println("Start saving page index " + args.getPageIndex() + " of pages " + args.getPageCount());
//don't output pages before page index 2.
if (args.getPageIndex() < 2)
{
args.setToOutput(false);
}
}
public void pageEndSaving(PageEndSavingArgs args)
{
System.out.println("End saving page index " + args.getPageIndex() + " of pages " + args.getPageCount());
//don't output pages after page index 8.
if (args.getPageIndex() >= 8)
{
args.setHasMorePages(false);
}
}
}
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ConvertTableToRangeWithOptions.class) + "LoadingSavingConvertingAndManaging/";
// Open an existing file that contains a table/list object in it
Workbook workbook = new Workbook(dataDir + "KeepSeparatorsForBlankRow.xlsx");
// Instantiate Text File's Save Options
TxtSaveOptions options = new TxtSaveOptions();
// Set KeepSeparatorsForBlankRow to true show separators in blank rows
options.setKeepSeparatorsForBlankRow(true);
// Save the file with the options
workbook.save(dataDir + "KeepSeparatorsForBlankRow.out.csv", options);
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
//Source directory
String dataDir = Utils.getSharedDataDir(OpeningCSVFilesAndReplacingInvalidCharacters.class) + "LoadingSavingConvertingAndManaging/";
LoadOptions loadOptions = new LoadOptions(FileFormatType.CSV);
//Load CSV file
Workbook workbook = new Workbook(dataDir + "[20180220142533][ASPOSE_CELLS_TEST].csv", loadOptions);
System.out.println(workbook.getWorksheets().get(0).getName()); // (20180220142533)(ASPOSE_CELLS_T
System.out.println(workbook.getWorksheets().get(0).getName().length()); // 31
System.out.println("CSV file opened successfully!");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
class TextParser implements ICustomParser
{
@Override
public Object parseObject(String s) {
return s;
}
@Override
public String getFormat() {
return "";
}
}
class DateParser implements ICustomParser {
@Override
public Object parseObject(String s) {
Date myDate = null;
SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
try {
myDate = formatter.parse(s);
} catch (ParseException e) {
e.printStackTrace();
}
return myDate;
}
@Override
public String getFormat() {
return "dd/MM/yyyy";
}
}
public class OpeningCSVFilesWithPreferredParser {
//Source directory
private static String sourceDir = Utils.Get_SourceDirectory();
private static String outputDir = Utils.Get_OutputDirectory();
public static void main(String[] args) throws Exception {
// Initialize Text File's Load options
TxtLoadOptions oTxtLoadOptions = new TxtLoadOptions(LoadFormat.CSV);
// Specify the separatot character
oTxtLoadOptions.setSeparator(',');
// Specify the encoding scheme
oTxtLoadOptions.setEncoding(Encoding.getUTF8());
// Set the flag to true for converting datetime data
oTxtLoadOptions.setConvertDateTimeData(true);
// Set the preferred parsers
oTxtLoadOptions.setPreferredParsers(new ICustomParser[] { new TextParser(), new DateParser() });
// Initialize the workbook object by passing CSV file and text load options
Workbook oExcelWorkBook = new Workbook(sourceDir + "samplePreferredParser.csv", oTxtLoadOptions);
// Get the first cell
Cell oCell = oExcelWorkBook.getWorksheets().get(0).getCells().get("A1");
// Display type of value
System.out.println("A1: " + getCellType(oCell.getType()) + " - " + oCell.getDisplayStringValue());
// Get the second cell
oCell = oExcelWorkBook.getWorksheets().get(0).getCells().get("B1");
// Display type of value
System.out.println("B1: " + getCellType(oCell.getType()) + " - " + oCell.getDisplayStringValue());
// Save the workbook to disc
oExcelWorkBook.save(outputDir + "outputsamplePreferredParser.xlsx");
System.out.println("OpeningCSVFilesWithPreferredParser executed successfully.\r\n");
}
private static String getCellType(int type){
if(type == CellValueType.IS_STRING){
return "String";
} else if(type == CellValueType.IS_NUMERIC){
return "Numeric";
} else if(type == CellValueType.IS_BOOL){
return "Bool";
} else if(type == CellValueType.IS_DATE_TIME){
return "Date";
} else if(type == CellValueType.IS_NULL){
return "Null";
} else if(type == CellValueType.IS_ERROR){
return "Error";
} else{
return "Unknown";
}
}
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Opening Microsoft Excel 97 Files
// Creating an EXCEL_97_TO_2003 LoadOptions object
LoadOptions loadOptions1 = new LoadOptions(FileFormatType.EXCEL_95);
// Creating an Workbook object with excel 97 file path and the
// loadOptions object
new Workbook(srcDir + "Excel95_5.0.xls", loadOptions1);
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(WorksheetToImage.class) + "LoadingSavingConvertingAndManaging/";
// Instantiate a new workbook with path to an Excel file
Workbook book = new Workbook(dataDir + "MyTestBook1.xlsx");
// Create an object for ImageOptions
ImageOrPrintOptions imgOptions = new ImageOrPrintOptions();
// Set the image type
imgOptions.setImageType(ImageType.PNG);
// Get the first worksheet.
Worksheet sheet = book.getWorksheets().get(0);
// Create a SheetRender object for the target sheet
SheetRender sr = new SheetRender(sheet, imgOptions);
for (int j = 0; j < sr.getPageCount(); j++) {
// Generate an image for the worksheet
sr.toImage(j, dataDir + "WToImage-out" + j + ".png");
}
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Instantiating a Workbook object
Workbook workbook = new Workbook(srcDir + "sourcePivotTable.xlsx");
// Access first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
// Access first pivot table inside the worksheet
PivotTable pivotTable = worksheet.getPivotTables().get(0);
// Access pivot table refresh by who property
System.out.println("Pivot table refresh by who = " + pivotTable.getRefreshedByWho());
// Access pivot table refresh date
System.out.println("Pivot table refresh date = " + pivotTable.getRefreshDate());
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the output directory.
String sourceDir = Utils.Get_SourceDirectory();
String outputDir = Utils.Get_OutputDirectory();
Workbook wb = new Workbook(sourceDir + "SamplePivotSort.xlsx");
// Obtaining the reference of the newly added worksheet
Worksheet sheet = wb.getWorksheets().get(0);
PivotTableCollection pivotTables = sheet.getPivotTables();
// source PivotTable
// Adding a PivotTable to the worksheet
int index = pivotTables.add("=Sheet1!A1:C10", "E3", "PivotTable2");
//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables.get(index);
// Unshowing grand totals for rows.
pivotTable.setRowGrand(false);
pivotTable.setColumnGrand(false);
// Dragging the first field to the row area.
pivotTable.addFieldToArea(PivotFieldType.ROW, 1);
PivotField rowField = pivotTable.getRowFields().get(0);
rowField.setAutoSort(true);
rowField.setAscendSort(true);
// Dragging the second field to the column area.
pivotTable.addFieldToArea(PivotFieldType.COLUMN, 0);
PivotField colField = pivotTable.getColumnFields().get(0);
colField.setNumberFormat("dd/mm/yyyy");
colField.setAutoSort(true);
colField.setAscendSort(true);
// Dragging the third field to the data area.
pivotTable.addFieldToArea(PivotFieldType.DATA, 2);
pivotTable.refreshData();
pivotTable.calculateData();
// end of source PivotTable
// sort the PivotTable on "SeaFood" row field values
// Adding a PivotTable to the worksheet
index = pivotTables.add("=Sheet1!A1:C10", "E10", "PivotTable2");
// Accessing the instance of the newly added PivotTable
pivotTable = pivotTables.get(index);
// Unshowing grand totals for rows.
pivotTable.setRowGrand(false);
pivotTable.setColumnGrand(false);
// Dragging the first field to the row area.
pivotTable.addFieldToArea(PivotFieldType.ROW, 1);
rowField = pivotTable.getRowFields().get(0);
rowField.setAutoSort(true);
rowField.setAscendSort(true);
// Dragging the second field to the column area.
pivotTable.addFieldToArea(PivotFieldType.COLUMN, 0);
colField = pivotTable.getColumnFields().get(0);
colField.setNumberFormat("dd/mm/yyyy");
colField.setAutoSort(true);
colField.setAscendSort(true);
colField.setAutoSortField(0);
//Dragging the third field to the data area.
pivotTable.addFieldToArea(PivotFieldType.DATA, 2);
pivotTable.refreshData();
pivotTable.calculateData();
// end of sort the PivotTable on "SeaFood" row field values
// sort the PivotTable on "28/07/2000" column field values
// Adding a PivotTable to the worksheet
index = pivotTables.add("=Sheet1!A1:C10", "E18", "PivotTable2");
// Accessing the instance of the newly added PivotTable
pivotTable = pivotTables.get(index);
// Unshowing grand totals for rows.
pivotTable.setRowGrand(false);
pivotTable.setColumnGrand(false);
// Dragging the first field to the row area.
pivotTable.addFieldToArea(PivotFieldType.ROW, 1);
rowField = pivotTable.getRowFields().get(0);
rowField.setAutoSort(true);
rowField.setAscendSort(true);
rowField.setAutoSortField(0);
// Dragging the second field to the column area.
pivotTable.addFieldToArea(PivotFieldType.COLUMN, 0);
colField = pivotTable.getColumnFields().get(0);
colField.setNumberFormat("dd/mm/yyyy");
colField.setAutoSort(true);
colField.setAscendSort(true);
// Dragging the third field to the data area.
pivotTable.addFieldToArea(PivotFieldType.DATA, 2);
pivotTable.refreshData();
pivotTable.calculateData();
// end of sort the PivotTable on "28/07/2000" column field values
//Saving the Excel file
wb.save(outputDir + "out_java.xlsx");
PdfSaveOptions options = new PdfSaveOptions();
options.setOnePagePerSheet(true);
wb.save(outputDir + "out_java.pdf", options);
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// directories
String sourceDir = Utils.Get_SourceDirectory();
String outputDir = Utils.Get_OutputDirectory();
// Load a template file
Workbook workbook = new Workbook(sourceDir + "PivotTableSample.xlsx");
// Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
int pivotIndex = 0;
// Get the pivot tables in the sheet
PivotTable pivotTable = sheet.getPivotTables().get(pivotIndex);
// Accessing the data fields.
PivotFieldCollection pivotFields = pivotTable.getDataFields();
// Accessing the first data field in the data fields.
PivotField pivotField = pivotFields.get(0);
// Setting data display format
pivotField.setDataDisplayFormat(PivotFieldDataDisplayFormat.RANK_LARGEST_TO_SMALLEST);
pivotTable.calculateData();
// Saving the Excel file
workbook.save(outputDir + "PivotTableDataDisplayFormatRanking_out.xlsx");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Source directory
String sourceDir = Utils.Get_SourceDirectory();
// Load sample file
Workbook workbook = new Workbook(sourceDir + "SamplePivotTableExternalConnection.xlsx");
Worksheet worksheet = workbook.getWorksheets().get(0);
// Get the pivot table
PivotTable pivotTable = worksheet.getPivotTables().get(0);
// Print External Connection Details
System.out.println("External Connection Data Source");
System.out.println("Name: " + pivotTable.getExternalConnectionDataSource().getName());
System.out.println("Type: " + pivotTable.getExternalConnectionDataSource().getType());
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the output directory.
String sourceDir = Utils.Get_SourceDirectory();
String outputDir = Utils.Get_OutputDirectory();
Workbook workbook = new Workbook(sourceDir + "PivotTableHideAndSortSample.xlsx");
Worksheet worksheet = workbook.getWorksheets().get(0);
PivotTable pivotTable = worksheet.getPivotTables().get(0);
CellArea dataBodyRange = pivotTable.getDataBodyRange();
int currentRow = 3;
int rowsUsed = dataBodyRange.EndRow;
// Sorting score in descending
PivotField field = pivotTable.getRowFields().get(0);
field.setAutoSort(true);
field.setAscendSort(false);
field.setAutoSortField(0);
pivotTable.refreshData();
pivotTable.calculateData();
// Hiding rows with score less than 60
while (currentRow < rowsUsed)
{
Cell cell = worksheet.getCells().get(currentRow, 1);
double score = (double) cell.getValue();
if (score < 60)
{
worksheet.getCells().hideRow(currentRow);
}
currentRow++;
}
pivotTable.refreshData();
pivotTable.calculateData();
// Saving the Excel file
workbook.save(outputDir + "PivotTableHideAndSort_out.xlsx");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Load template file
Workbook wb = new Workbook(srcDir + "samplePivotTable.xlsx");
// Get first pivot table in the worksheet
PivotTable pt = wb.getWorksheets().get(1).getPivotTables().get(0);
// Set pivot field
pt.showReportFilterPage(pt.getPageFields().get(0));
// Set position index for showing report filter pages
pt.showReportFilterPageByIndex(pt.getPageFields().get(0).getPosition());
// Set the page field name
pt.showReportFilterPageByName(pt.getPageFields().get(0).getName());
// Save the output file
wb.save(outDir + "outputSamplePivotTable.xlsx");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
String outDir = Utils.Get_OutputDirectory();
// Create workbook
Workbook wb = new Workbook();
// Access first worksheet - it is empty sheet
Worksheet ws = wb.getWorksheets().get(0);
// Specify image or print options
// Since the sheet is blank, we will set
// OutputBlankPageWhenNothingToPrint to true
// So that empty page gets printed
ImageOrPrintOptions opts = new ImageOrPrintOptions();
opts.setImageType(ImageType.PNG);
opts.setOutputBlankPageWhenNothingToPrint(true);
// Render empty sheet to png image
SheetRender sr = new SheetRender(ws, opts);
sr.toImage(0, outDir + "OutputBlankPageWhenNothingToPrint.png");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
//Load the sample Excel file
Workbook wb = new Workbook(srcDir + "sampleImageOrPrintOptions_PageIndexPageCount.xlsx");
//Access the first worksheet
Worksheet ws = wb.getWorksheets().get(0);
//Specify image or print options
//We want to print pages 4, 5, 6, 7
ImageOrPrintOptions opts = new ImageOrPrintOptions();
opts.setPageIndex(3);
opts.setPageCount(4);
opts.setImageType(ImageType.PNG);
//Create sheet render object
SheetRender sr = new SheetRender(ws, opts);
//Print all the pages as images
for (int i = opts.getPageIndex(); i < sr.getPageCount(); i++)
{
sr.toImage(i, outDir + "outputImage-" + (i+1) + ".png");
}
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Load the template file
Workbook wb = new Workbook(srcDir + "sampleCrosssType.xlsx");
// Initialize PDF save options
PdfSaveOptions saveOptions = new PdfSaveOptions();
// Set text criss type
saveOptions.setTextCrossType(TextCrossType.STRICT_IN_CELL);
// Save output PDF file
wb.save(outDir + "outputCrosssType.pdf", saveOptions);
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(AutofitRowsforMergedCells.class) + "RowsAndColumns/";
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);
// Create a range A1:B1
Range range = worksheet.getCells().createRange(0, 0, 1, 2);
// Merge the cells
range.merge();
// Insert value to the merged cell A1
worksheet.getCells().get(0, 0).setValue("A quick brown fox jumps over the lazy dog. A quick brown fox jumps over the lazy dog....end");
// Create a style object
Style style = worksheet.getCells().get(0, 0).getStyle();
// Set wrapping text on
style.setTextWrapped(true);
// Apply the style to the cell
worksheet.getCells().get(0, 0).setStyle(style);
// Create an object for AutoFitterOptions
AutoFitterOptions options = new AutoFitterOptions();
// Set auto-fit for merged cells
options.setAutoFitMergedCellsType(AutoFitMergedCellsType.EACH_LINE);
// Autofit rows in the sheet(including the merged cells)
worksheet.autoFitRows(options);
// Save the Excel file
workbook.save(dataDir + "AutofitRowsforMergedCells_out.xlsx");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(GroupingRowsandColumns.class) + "RowsAndColumns/";
// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "Book1.xlsx");
// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);
Cells cells = worksheet.getCells();
// Grouping first six rows (from 0 to 5) and making them hidden by
// passing true
cells.groupRows(0, 5, true);
// Grouping first three columns (from 0 to 2) and making them hidden by
// passing true
cells.groupColumns(0, 2, true);
// Setting SummaryRowBelow property to false
worksheet.getOutline().setSummaryRowBelow(true);
// Setting SummaryColumnRight property to false
worksheet.getOutline().setSummaryColumnRight(true);
// Saving the modified Excel file in default (that is Excel 2003) format
workbook.save(dataDir + "GroupingRowsandColumns_out.xlsx");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
String dataDir = Utils.getSharedDataDir(InsertingARowWithFormatting.class) + "RowsAndColumns/";
// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "Book1.xlsx");
// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);
// Setting Formatting options
InsertOptions insertOptions = new InsertOptions();
insertOptions.setCopyFormatType(CopyFormatType.SAME_AS_ABOVE);
// Inserting a row into the worksheet at 3rd position
worksheet.getCells().insertRows(2, 1, insertOptions);
// Saving the modified Excel file
workbook.save(dataDir + "InsertingARowWithFormatting_out.xlsx");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(CountNumberOfCells.class) + "RowsAndColumns/";
// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "Book1.xlsx");
// Access first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
// Set the width of the cell in pixels
worksheet.getCells().setViewColumnWidthPixel(7, 200);
workbook.save(dataDir + "SetColumnViewWidthInPixels_Out.xlsx");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(SummaryRowBelow.class) + "RowsAndColumns/";
// 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);
Cells cells = worksheet.getCells();
// Grouping first six rows (from 0 to 5) and making them hidden by passing true
cells.groupRows(0, 5, true);
// Grouping first three columns (from 0 to 2) and making them hidden by passing true
cells.groupColumns(0, 2, true);
// Setting SummaryRowBelow property to false
worksheet.getOutline().setSummaryRowBelow(false);
// Saving the modified Excel file in default (that is Excel 2003) format
workbook.save(dataDir + "SummaryRowBelow_out.xls");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(SummaryRowRight.class) + "RowsAndColumns/";
// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "BookStyles.xls");
// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);
Cells cells = worksheet.getCells();
// Grouping first six rows (from 0 to 5) and making them hidden by passing true
cells.ungroupRows(0, 5);
// Grouping first three columns (from 0 to 2) and making them hidden by passing true
cells.ungroupColumns(0, 2);
// Saving the modified Excel file in default (that is Excel 2003) format
workbook.save(dataDir + "SummaryRowRight_out.xls");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
Workbook workbook = new Workbook(sourceDir + "SampleSlicerChart.xlsx");
workbook.save(outputDir + "SampleSlicerChart.pdf", SaveFormat.PDF);
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Load sample Excel file containing slicer.
Workbook wb = new Workbook(srcDir + "sampleRenderingSlicer.xlsx");
// Access first worksheet.
Worksheet ws = wb.getWorksheets().get(0);
// Set the print area because we want to render slicer only.
ws.getPageSetup().setPrintArea("B15:E25");
// Specify image or print options, set one page per sheet and only area to true.
ImageOrPrintOptions imgOpts = new ImageOrPrintOptions();
imgOpts.setHorizontalResolution(200);
imgOpts.setVerticalResolution(200);
imgOpts.setImageType(com.aspose.cells.ImageType.PNG);
imgOpts.setOnePagePerSheet(true);
imgOpts.setOnlyArea(true);
// Create sheet render object and render worksheet to image.
SheetRender sr = new SheetRender(ws, imgOpts);
sr.toImage(0, outDir + "outputRenderingSlicer.png");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ConvertTableToRangeWithOptions.class) + "Tables/";
// Open an existing file that contains a table/list object in it
Workbook workbook = new Workbook(dataDir + "book1.xlsx");
TableToRangeOptions options = new TableToRangeOptions();
options.setLastRow(5);
// Convert the first table/list object (from the first worksheet) to normal range
workbook.getWorksheets().get(0).getListObjects().get(0).convertToRange(options);
// Save the file
workbook.save(dataDir + "ConvertTableToRangeWithOptions_out.xlsx");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the output directory.
String sourceDir = Utils.Get_SourceDirectory();
String outputDir = Utils.Get_OutputDirectory();
// Load workbook object
Workbook workbook = new Workbook(sourceDir + "SampleTableWithQueryTable.xls");
Worksheet worksheet = workbook.getWorksheets().get(0);
ListObject table = worksheet.getListObjects().get(0);
// Check the data source type if it is query table
if (table.getDataSourceType() == TableDataSourceType.QUERY_TABLE)
{
table.setShowTotals(true);
}
// Save the file
workbook.save(outputDir + "SampleTableWithQueryTable_out.xls");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(AutoFitRowsforMergedCells.class) + "TechnicalArticles/";
// Instantiate a new Workbook
Workbook workbook = new Workbook();
// Get the first (default) worksheet
Worksheet _worksheet = workbook.getWorksheets().get(0);
// Create a range A1:B1
Range range = _worksheet.getCells().createRange(0, 0, 1, 2);
// Merge the cells
range.merge();
// Insert value to the merged cell A1
_worksheet.getCells().get(0, 0).setValue(
"A quick brown fox jumps over the lazy dog. A quick brown fox jumps over the lazy dog....end");
// Create a style object
Style style = _worksheet.getCells().get(0, 0).getStyle();
// Set wrapping text on
style.setTextWrapped(true);
// Apply the style to the cell
_worksheet.getCells().get(0, 0).setStyle(style);
// Create an object for AutoFitterOptions
AutoFitterOptions options = new AutoFitterOptions();
// Set auto-fit for merged cells
options.setAutoFitMergedCellsType(AutoFitMergedCellsType.EACH_LINE);
// Autofit rows in the sheet(including the merged cells)
_worksheet.autoFitRows(options);
// Save the Excel file
workbook.save(dataDir + "AFRFMergedCells.xlsx");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
String dataDir = Utils.Get_OutputDirectory();
// Instantiate a new Workbook
Workbook workbook = new Workbook();
// Get the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
//Add some text in cell A1
worksheet.getCells().get("A1").putValue("Here");
// Add a comment to A1 cell
Comment comment = worksheet.getComments().get(worksheet.getComments().add("A1"));
// Set its vertical alignment setting
comment.getCommentShape().setTextVerticalAlignment(TextAlignmentType.CENTER);
// Set the Comment note
comment.setNote("This is my Comment Text. This is test");
Shape shape = worksheet.getComments().get("A1").getCommentShape();
shape.getFill().getSolidFill().setColor(Color.getBlack());
Font font = shape.getFont();
font.setColor(Color.getWhite());
StyleFlag styleFlag = new StyleFlag();
styleFlag.setFontColor(true);
shape.getTextBody().format(0, shape.getText().length(), font, styleFlag);
// Save the Excel file
workbook.save(dataDir + "outputChangeCommentFontColor.xlsx");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(CheckForShapes.class) + "TechnicalArticles/";
// Create an instance of Workbook and load an existing spreadsheet
Workbook workbook = new Workbook(dataDir + "SampleCheckCells.xlsx");
// Loop over all worksheets in the workbook
for (int i = 0; i < workbook.getWorksheets().getCount(); i++) {
Worksheet worksheet = workbook.getWorksheets().get(i);
// Check if worksheet has populated cells
if (worksheet.getCells().getMaxDataRow() != -1) {
System.out.println(worksheet.getName() + " is not empty because one or more cells are populated");
}
// Check if worksheet has shapes
else if (worksheet.getShapes().getCount() > 0) {
System.out.println(worksheet.getName() + " is not empty because there are one or more shapes");
}
// Check if worksheet has empty initialized cells
else {
Range range = worksheet.getCells().getMaxDisplayRange();
Iterator rangeIterator = range.iterator();
if (rangeIterator.hasNext()) {
System.out.println(worksheet.getName() + " is not empty because one or more cells are initialized");
} else {
System.out.println(worksheet.getName() + " is empty");
}
}
}
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ConversionOptions.class) + "TechnicalArticles/";
// Instantiate a new Workbook object
// Open template
Workbook book = new Workbook(dataDir + "book1.xlsx");
// Get the first worksheet
Worksheet sheet = book.getWorksheets().get(0);
// Apply different Image and Print options
ImageOrPrintOptions options = new ImageOrPrintOptions();
// Set Horizontal Resolution
options.setHorizontalResolution(300);
// Set Vertical Resolution
options.setVerticalResolution(300);
// Set Image Format
options.setImageType(ImageType.JPEG);
// If you want entire sheet as a single image
options.setOnePagePerSheet(true);
// Render the sheet with respect to specified image/print options
SheetRender sr = new SheetRender(sheet, options);
// Render/save the image for the sheet
sr.toImage(0, dataDir + "ConversionOptions_out.jpg");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ConvertWorkbooktoImage.class) + "TechnicalArticles/";
// Instantiate a new Workbook object
Workbook book = new Workbook(dataDir + "book1.xlsx");
// Apply different Image and Print options
ImageOrPrintOptions options = new ImageOrPrintOptions();
// Set Image Format
options.setImageType(ImageType.TIFF);
// If you want entire sheet as a single image
options.setOnePagePerSheet(true);
// Render to image
WorkbookRender render = new WorkbookRender(book, options);
render.toImage(dataDir + "CWorkbooktoImage_out.tiff");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ConvertWorksheettoImage.class) + "TechnicalArticles/";
//Instantiate a new Workbook object
//Open template
Workbook book = new Workbook(dataDir + "book1.xlsx");
//Get the first worksheet
Worksheet sheet = book.getWorksheets().get(0);
//Apply different Image and Print options
ImageOrPrintOptions options = new ImageOrPrintOptions();
//Set Horizontal Resolution
options.setHorizontalResolution(300);
//Set Vertical Resolution
options.setVerticalResolution(300);
//Set TiffCompression
options.setTiffCompression(TiffCompression.COMPRESSION_LZW);
//Set Image Format
options.setImageType(ImageType.TIFF);
//Set printing page type
options.setPrintingPage(PrintingPageType.DEFAULT);
//Render the sheet with respect to specified image/print options
SheetRender sr = new SheetRender(sheet, options);
//Render/save the image for the sheet
sr.toImage(0, dataDir + "CWorksheettoImage_out.tiff");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ConvertWorksheetToImageByPage.class) + "TechnicalArticles/";
// Create a new Workbook object
// Open a template excel file
Workbook book = new Workbook(dataDir + "ConvertWorksheetToImageByPage.xlsx");
// Get the first worksheet
Worksheet sheet = book.getWorksheets().get(0);
// Define ImageOrPrintOptions
ImageOrPrintOptions options = new ImageOrPrintOptions();
// Set Resolution
options.setHorizontalResolution(200);
options.setVerticalResolution(200);
options.setImageType(ImageType.TIFF);
// Sheet2Image by page conversion
SheetRender render = new SheetRender(sheet, options);
for (int j = 0; j < render.getPageCount(); j++) {
render.toImage(j, dataDir + sheet.getName() + " Page" + (j + 1) + ".tif");
}
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ConvertWorksheettoImageFile.class) + "TechnicalArticles/";
// Create a new Workbook object
// Open a template excel file
Workbook book = new Workbook(dataDir + "book1.xlsx");
// Get the first worksheet
Worksheet sheet = book.getWorksheets().get(0);
// Define ImageOrPrintOptions
ImageOrPrintOptions imgOptions = new ImageOrPrintOptions();
// Specify the image format
imgOptions.setImageType(ImageType.JPEG);
// Render the sheet with respect to specified image/print options
SheetRender render = new SheetRender(sheet, imgOptions);
// Render the image for the sheet
render.toImage(0, dataDir + "CWToImageFile.jpg");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(CreatePieChartWithLeaderLines.class) + "TechnicalArticles/";
// Create an instance of Workbook in XLSX format
Workbook workbook = new Workbook(FileFormatType.XLSX);
// Access the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
// Add two columns of data
worksheet.getCells().get("A1").putValue("Retail");
worksheet.getCells().get("A2").putValue("Services");
worksheet.getCells().get("A3").putValue("Info & Communication");
worksheet.getCells().get("A4").putValue("Transport Equip");
worksheet.getCells().get("A5").putValue("Construction");
worksheet.getCells().get("A6").putValue("Other Products");
worksheet.getCells().get("A7").putValue("Wholesale");
worksheet.getCells().get("A8").putValue("Land Transport");
worksheet.getCells().get("A9").putValue("Air Transport");
worksheet.getCells().get("A10").putValue("Electric Appliances");
worksheet.getCells().get("A11").putValue("Securities");
worksheet.getCells().get("A12").putValue("Textiles & Apparel");
worksheet.getCells().get("A13").putValue("Machinery");
worksheet.getCells().get("A14").putValue("Metal Products");
worksheet.getCells().get("A15").putValue("Cash");
worksheet.getCells().get("A16").putValue("Banks");
worksheet.getCells().get("B1").putValue(10.4);
worksheet.getCells().get("B2").putValue(5.2);
worksheet.getCells().get("B3").putValue(6.4);
worksheet.getCells().get("B4").putValue(10.4);
worksheet.getCells().get("B5").putValue(7.9);
worksheet.getCells().get("B6").putValue(4.1);
worksheet.getCells().get("B7").putValue(3.5);
worksheet.getCells().get("B8").putValue(5.7);
worksheet.getCells().get("B9").putValue(3);
worksheet.getCells().get("B10").putValue(14.7);
worksheet.getCells().get("B11").putValue(3.6);
worksheet.getCells().get("B12").putValue(2.8);
worksheet.getCells().get("B13").putValue(7.8);
worksheet.getCells().get("B14").putValue(2.4);
worksheet.getCells().get("B15").putValue(1.8);
worksheet.getCells().get("B16").putValue(10.1);
// Create a pie chart and add it to the collection of charts
int id = worksheet.getCharts().add(ChartType.PIE, 3, 3, 23, 13);
// Access newly created Chart instance
Chart chart = worksheet.getCharts().get(id);
// Set series data range
chart.getNSeries().add("B1:B16", true);
// Set category data range
chart.getNSeries().setCategoryData("A1:A16");
// Turn off legend
chart.setShowLegend(false);
// Access data labels
DataLabels dataLabels = chart.getNSeries().get(0).getDataLabels();
// Turn on category names
dataLabels.setShowCategoryName(true);
// Turn on percentage format
dataLabels.setShowPercentage(true);
// Set position
dataLabels.setPosition(LabelPositionType.OUTSIDE_END);
// Set separator
dataLabels.setSeparator(DataLablesSeparatorType.COMMA);
//Turn on leader lines
chart.getNSeries().get(0).setHasLeaderLines(true);
//Calculate chart
chart.calculate();
//You need to move DataLabels a little leftward or rightward depending on their position
//to show leader lines
int DELTA = 100;
for (int i = 0; i < chart.getNSeries().get(0).getPoints().getCount(); i++)
{
int X = chart.getNSeries().get(0).getPoints().get(i).getDataLabels().getX();
//If it is greater than 2000, then move the X position a little right
//otherwise move the X position a little left
if (X > 2000)
chart.getNSeries().get(0).getPoints().get(i).getDataLabels().setX(X + DELTA);
else
chart.getNSeries().get(0).getPoints().get(i).getDataLabels().setX(X - DELTA);
}
//In order to save the chart image, create an instance of ImageOrPrintOptions
ImageOrPrintOptions anOption = new ImageOrPrintOptions();
//Set image format
anOption.setImageType(ImageType.PNG);
//Set resolution
anOption.setHorizontalResolution(200);
anOption.setVerticalResolution(200);
//Render chart to image
chart.toImage(dataDir + "CPieChartWLLines_out.png", anOption);
//Save the workbook to see chart inside the Excel
workbook.save(dataDir + "CPieChartWLLines_out.xlsx");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(CreateTransparentImage.class) + "TechnicalArticles/";
// Create workbook object from source file
Workbook wb = new Workbook(dataDir + "aspose-sample.xlsx");
// Apply different image or print options
ImageOrPrintOptions imgOption = new ImageOrPrintOptions();
imgOption.setImageType(ImageType.PNG);
imgOption.setHorizontalResolution(200);
imgOption.setVerticalResolution(200);
imgOption.setOnePagePerSheet(true);
// Apply transparency to the output image
imgOption.setTransparent(true);
// Create image after apply image or print options
SheetRender sr = new SheetRender(wb.getWorksheets().get(0), imgOption);
sr.toImage(0, dataDir + "CTransparentImage_out.png");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
import java.util.ArrayList;
import com.aspose.cells.DateTime;
import com.aspose.cells.ICustomFunction;
public class CustomFunctionStaticValue implements ICustomFunction {
@Override
public Object calculateCustomFunction(String functionName, ArrayList paramsList, ArrayList contextObjects) {
return new Object[][] { new Object[] { new DateTime(2015, 6, 12, 10, 6, 30), 2 },
new Object[] { 3.0, "Test" } };
}
}
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
//Source directory
String dataDir = AsposeCellsExamples.Utils.getSharedDataDir(DetectFileFormatOfEncryptedFiles.class) + "TechnicalArticles/";
String filename = dataDir + "encryptedBook1.out.tmp";
FileFormatInfo fileFormatInfo = FileFormatUtil.detectFileFormat(filename,"1234"); // The password is 1234
if(fileFormatInfo.getFileFormatType() == FileFormatType.EXCEL_97_TO_2003) {
System.out.println("File Format: EXCEL_97_TO_2003");
} else if(fileFormatInfo.getFileFormatType() == FileFormatType.PPTX) {
System.out.println("File Format: PPTX");
} else if(fileFormatInfo.getFileFormatType() == FileFormatType.DOCX) {
System.out.println("File Format: DOCX");
}
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ExportRangeofCells.class) + "TechnicalArticles/";
// Create workbook from source file.
Workbook workbook = new Workbook(dataDir + "book1.xlsx");
// Access the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
// Set the print area with your desired range
worksheet.getPageSetup().setPrintArea("E8:H10");
// Set all margins as 0
worksheet.getPageSetup().setLeftMargin(0);
worksheet.getPageSetup().setRightMargin(0);
worksheet.getPageSetup().setTopMargin(0);
worksheet.getPageSetup().setBottomMargin(0);
// Set OnePagePerSheet option as true
ImageOrPrintOptions options = new ImageOrPrintOptions();
options.setOnePagePerSheet(true);
options.setImageType(ImageType.JPEG);
// Take the image of your worksheet
SheetRender sr = new SheetRender(worksheet, options);
sr.toImage(0, dataDir + "ERangeofCells_out.jpg");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ExportWorksheettoImage.class) + "TechnicalArticles/";
// Create workbook object from source file
Workbook workbook = new Workbook(dataDir + "source.xlsx");
// Access first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
/*
* Set image or print options, We want one page per sheet, The image format is in png And desired dimensions are
* 400x400
*/
ImageOrPrintOptions opts = new ImageOrPrintOptions();
opts.setOnePagePerSheet(true);
opts.setImageType(ImageType.PNG);
opts.setDesiredSize(400, 400);
// Render sheet into image
SheetRender sr = new SheetRender(worksheet, opts);
sr.toImage(0, dataDir + "EWSheetToImage_out.png");
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ExtractImagesfromWorksheets.class) + "TechnicalArticles/";
// Open a template Excel file
Workbook workbook = new Workbook(dataDir + "book3.xlsx");
// Get the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
// Get the first Picture in the first worksheet
Picture pic = worksheet.getPictures().get(0);
// Set the output image file path
String fileName = "aspose-logo.jpg";
// Note: you may evaluate the image format before specifying the image path
// Define ImageOrPrintOptions
ImageOrPrintOptions printoption = new ImageOrPrintOptions();
// Specify the image format
printoption.setImageType(ImageType.JPEG);
// Save the image
pic.toImage(dataDir + fileName, printoption);
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(FilterDataWhileLoadingWorkbook.class) + "TechnicalArticles/";
// Set the load options, we only want to load shapes and do not want to load data
LoadOptions opts = new LoadOptions(LoadFormat.XLSX);
opts.getLoadFilter().setLoadDataFilterOptions(LoadDataFilterOptions.SHAPE);
// Create workbook object from sample excel file using load options
Workbook wb = new Workbook(dataDir + "sampleFilterDataWhileLoadingWorkbook.xlsx", opts);
// Save the output in PDF format
wb.save(dataDir + "sampleFilterDataWhileLoadingWorkbook_out.pdf", SaveFormat.PDF);
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
public class FilterObjectsLoadingWorksheets {
// Implement your own custom load filter, it will enable you to filter your
// individual worksheet
class CustomLoadFilter extends LoadFilter {
public void startSheet(Worksheet sheet