import com.jniwrapper.Int32; import com.jniwrapper.win32.automation.IDispatch; import com.jniwrapper.win32.automation.types.Variant; import com.jniwrapper.win32.com.types.LocaleID; import com.jniwrapper.win32.excel._Worksheet; import com.jniwrapper.win32.excel.impl.PivotTableImpl; import com.jniwrapper.win32.jexcel.Application; import com.jniwrapper.win32.jexcel.Cell; import com.jniwrapper.win32.jexcel.Workbook; import com.jniwrapper.win32.jexcel.Worksheet; import java.io.File; /** * <p> This sample demonstrates how to trigger Pivot Table update after the raw data has been changed * using JExcel. * * <p> This sample requires following preset to run successfully: * <ol><li>the standard jexcel.jar must be replaced with jexcel-full.jar which can be retrieved from * <a href="https://sites.google.com/a/teamdev.com/jexcel-support/hotfixes"> our hotfixes </a> page </li> * <li>the MS Excel workbook (PivotTest.xlsx) which comes with this sample must be present in the * project working directory</li></ol> */ public class ExcelWithPivotTable { //Please, change the file path according to your environment private static final String filePath = "PivotTest.xlsx"; private static final String dataSheetName = "Data"; private static final String pivotTableSheetName = "Pivot"; public static void main(String[] args) { Application application = null; Workbook workbook = null; try { application = new Application(); //Open Workbook workbook = application.openWorkbook(new File(filePath)); //Get dataSheet with raw data by name Worksheet dataSheet = workbook.getWorksheet(dataSheetName); //Get dataSheet with pivot table by name Worksheet pivotTableSheet = workbook.getWorksheet(pivotTableSheetName); //Get cell with data calculated by pivot table Cell result = pivotTableSheet.getCell("C6"); System.out.println("Original value in pivot table " + result.getNumber()); //Get cell with raw data Cell inputData = dataSheet.getCell("B2"); System.out.println("Original value in data cell " + inputData.getString()); //Update the source data inputData.setValue("UA"); final _Worksheet pivotTableSheetPeer = pivotTableSheet.getPeer(); //Put into OLE Message Loop action that will trigger the pivot table recalculation pivotTableSheet.getOleMessageLoop().doInvokeAndWait(new Runnable(){ public void run() { //Get the pivot table Dispatch interface. In this case we've got only one pivot table. In //more complicated case we will need to look for the pivot table. The 'getPeer' method //used to access wrappers of the MS Excel Objects. IDispatch dispatch = pivotTableSheetPeer.pivotTables(new Variant(1), new Int32(LocaleID.LOCALE_USER_DEFAULT)); //Query for PivotTable interface. This is the one of the ways to do it using ComfyJ, //which is the base for JExcel PivotTableImpl pivotTableObject = new PivotTableImpl(dispatch); //Call refresh table pivotTableObject.refreshTable(); //Release IDispatch dispatch.setAutoDelete(false); dispatch.release(); //Release pivot table pivotTableObject.setAutoDelete(false); pivotTableObject.release(); } }); System.out.println("Result value in data cell " + inputData.getString()); System.out.println("Result value in pivot table " + result.getNumber()); //Save the workbook with the same name workbook.save(); } catch (Exception e) { e.printStackTrace(); } finally{ //Close the workbook and the application if (workbook != null) { workbook.close(false); } if (application != null) { application.close(); } } } } |
Samples >