create MS Excel files using Java
// create a new workbook | |
Workbook workbook = new Workbook(); | |
// Register macro enabled add-in along with the function name | |
int id = workbook.getWorksheets().registerAddInFunction("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 as Excel XLSX file | |
workbook.save("Excel.xlsx"); |
// Create a new workbook | |
Workbook workbook = new Workbook(); | |
// Add value in the cell | |
workbook.getWorksheets().get(0).getCells().get(0).setFormula("=H7*(1+IF(P7 =$L$3,$M$3, (IF(P7=$L$4,$M$4,0))))"); | |
// Save as Excel XLSX file | |
workbook.save("Excel.xlsx"); |
// Create a new workbook | |
Workbook workbook = new Workbook(); | |
// Add value in the cell | |
workbook.getWorksheets().get(0).getCells().get("A1").putValue("Hello World!"); | |
// Save as Excel XLSX file | |
workbook.save("Excel.xlsx"); |
// Create a new workbook | |
Workbook workbook = new Workbook("workbook.xlsx"); | |
// Obtaining the reference of the first worksheet | |
WorksheetCollection worksheets = workbook.getWorksheets(); | |
Worksheet sheet = worksheets.get(0); | |
// Adding some sample value to cells | |
Cells cells = sheet.getCells(); | |
Cell cell = cells.get("A1"); | |
cell.setValue(50); | |
cell = cells.get("A2"); | |
cell.setValue(100); | |
cell = cells.get("A3"); | |
cell.setValue(150); | |
cell = cells.get("B1"); | |
cell.setValue(4); | |
cell = cells.get("B2"); | |
cell.setValue(20); | |
cell = cells.get("B3"); | |
cell.setValue(50); | |
// get charts in worksheet | |
ChartCollection charts = sheet.getCharts(); | |
// Adding a chart to the worksheet | |
int chartIndex = charts.add(ChartType.PYRAMID, 5, 0, 15, 5); | |
Chart chart = charts.get(chartIndex); | |
// Adding NSeries (chart data source) to the chart ranging from "A1" | |
// cell to "B3" | |
SeriesCollection serieses = chart.getNSeries(); | |
serieses.add("A1:B3", true); | |
// Write the Excel file | |
workbook.save("Excel_with_Chart.xlsx"); |
// Create a new workbook | |
Workbook workbook = new Workbook("workbook.xlsx"); | |
// Get the first worksheet. | |
Worksheet sheet = workbook.getWorksheets().get(0); | |
// Obtaining Worksheet's cells collection | |
Cells cells = sheet.getCells(); | |
// Setting the value to the cells | |
Cell cell = cells.get("A1"); | |
cell.setValue("Sport"); | |
cell = cells.get("B1"); | |
cell.setValue("Quarter"); | |
cell = cells.get("C1"); | |
cell.setValue("Sales"); | |
cell = cells.get("A2"); | |
cell.setValue("Golf"); | |
cell = cells.get("A3"); | |
cell.setValue("Golf"); | |
cell = cells.get("A4"); | |
cell.setValue("Tennis"); | |
cell = cells.get("A5"); | |
cell.setValue("Tennis"); | |
cell = cells.get("A6"); | |
cell.setValue("Tennis"); | |
cell = cells.get("A7"); | |
cell.setValue("Tennis"); | |
cell = cells.get("A8"); | |
cell.setValue("Golf"); | |
cell = cells.get("B2"); | |
cell.setValue("Qtr3"); | |
cell = cells.get("B3"); | |
cell.setValue("Qtr4"); | |
cell = cells.get("B4"); | |
cell.setValue("Qtr3"); | |
cell = cells.get("B5"); | |
cell.setValue("Qtr4"); | |
cell = cells.get("B6"); | |
cell.setValue("Qtr3"); | |
cell = cells.get("B7"); | |
cell.setValue("Qtr4"); | |
cell = cells.get("B8"); | |
cell.setValue("Qtr3"); | |
cell = cells.get("C2"); | |
cell.setValue(1500); | |
cell = cells.get("C3"); | |
cell.setValue(2000); | |
cell = cells.get("C4"); | |
cell.setValue(600); | |
cell = cells.get("C5"); | |
cell.setValue(1500); | |
cell = cells.get("C6"); | |
cell.setValue(4070); | |
cell = cells.get("C7"); | |
cell.setValue(5000); | |
cell = cells.get("C8"); | |
cell.setValue(6430); | |
PivotTableCollection pivotTables = sheet.getPivotTables(); | |
// Adding a PivotTable to the worksheet | |
int index = pivotTables.add("=A1:C8", "E3", "PivotTable2"); | |
// Accessing the instance of the newly added PivotTable | |
PivotTable pivotTable = pivotTables.get(index); | |
// Unshowing grand totals for rows. | |
pivotTable.setRowGrand(false); | |
// Dragging the first field to the row area. | |
pivotTable.addFieldToArea(PivotFieldType.ROW, 0); | |
// Dragging the second field to the column area. | |
pivotTable.addFieldToArea(PivotFieldType.COLUMN, 1); | |
// Dragging the third field to the data area. | |
pivotTable.addFieldToArea(PivotFieldType.DATA, 2); | |
// Write the Excel file | |
workbook.save("Excel_with_Chart.xlsx"); |
// Create a new workbook | |
Workbook workbook = new Workbook("workbook.xls"); | |
// Get the reference of "A1" cell from the cells of a worksheet | |
Cell cell = workbook.getWorksheets().get(0).getCells().get("A1"); | |
// Set the "Hello World!" value into the "A1" cell | |
cell.setValue("updated cell value."); | |
// Write the Excel file | |
workbook.save("Excel.xls", FileFormatType.EXCEL_97_TO_2003); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment