Microsoft^® Excel File Splitting via Java

Split Excel spreadsheet into worksheets within Java based applications

There are variety of scenarios, When there is need to split Excel files like a spreadsheet containing students data with allocation of single sheet for each student. And there is need to split each sheet student wise as a separate file. To automate it via Java application, Java Excel API is there to split Excel document sheetwise. Supported formats include XLS, XLSX, XLSB, XLSM, ODS.

Split Excel Document into Multiple Files

The simplest way to split Excel file into sheet is, Access all sheets, iterate through each sheet and save one by one in the desired format. For loading the worksheet, API provides Workbook class. getWorksheets().getCount() method gets total number of sheets. Iterate through each sheet and use getWorksheets().get(sheetindex) for accessing specific sheet. Move the selected sheet data into newly created Workbook class object by using Copy method . Finally save it into required format.

Java Code to Split Excel Files

	// Load XLS spreadsheet
	Workbook wbk = new Workbook("sourceFile.xls");

	//Iterate all worksheets inside the workbook

	for(int i=0; i< wbk.getWorksheets().getCount(); i++){

	//Access the i th worksheet

	Worksheet worksheet = wbk.getWorksheets().get(i);

	Workbook bk = new Workbook();

	bk.getWorksheets().get(0).copy(worksheet);

	// Save the file.
	bk.save("path"+worksheet.getName()+"-"+i+".xls");
	}

view raw split-xls-spreadsheet.java hosted with ❤ by GitHub

Split Excel Worksheet into Panes

API also provides functionality of splitting Excel worksheet into different panes. Process is, Load the file using Workbook class. Select first worksheet or any required sheet by providing its index. Call the setActiveCell having relevant cell index as parameter. And finally split the worksheet window into different panes by calling split() method.

Java Code to Split Excel Sheet into Pane View

	//Instantiate a new workbook / Open a template file

	Workbook bk = new Workbook(dataDir + "workbook.xls");

	//Set the active cell

	bk.getWorksheets().get(0).setActiveCell("A20");

	//Split the worksheet window

	bk.getWorksheets().get(0).split();

view raw split-excel-spreadsheet-into-panes.java hosted with ❤ by GitHub

Microsoft® Excel File Splitting via Java