Last updated on March 22, 2022 by Usman Aziz
Blog Home

Convert Excel Files to Google Sheets in Java

This article shows how to convert MS Excel XLS or XLSX to Google Sheets programmatically in Java.

Convert Excel XLS or XLSX to Google Sheets in Java

Google Sheets is a popular online application that lets you create and manipulate spreadsheet documents. Furthermore, it allows you to share the spreadsheets with multiple people in real-time. Therefore, often you have to export data from Excel workbooks to a spreadsheet in Google Sheets programmatically. In this article, we will give a complete walk-through of how to export Excel data to a Google Sheets’ spreadsheet in Java.

Prerequisites – Convert Excel File to Google Sheets in Java

Google Cloud Project

Google Cloud is a cloud computing platform, which provides various types of services that we can utilize in our applications. To use Google Sheets API, we will have to create a project on the Google Cloud console and enable Google Sheets API. You can read the step-by-step guide on how to create a Google Cloud project and enable Sheets API.

Java APIs for Excel to Google Sheets Conversion

To export data from Excel XLS/XLSX files to Google Sheets, we will need the following APIs.

  • Aspose.Cells for Java – To read the data from Excel files.
  • Google Client APIs for Java – To create and update spreadsheets on Google Sheets.

To install these APIs, you can use the following configurations in your pom.xml file.

<repositories>
	<repository>
		<id>AsposeJavaAPI</id>
		<name>Aspose Java API</name>
		<url>http://repository.aspose.com/repo/</url>
	</repository>
</repositories>
<dependencies>
	<dependency>
		<groupId>com.google.api-client</groupId>
		<artifactId>google-api-client-gson</artifactId>
		<version>1.33.0</version>
	</dependency>
	<dependency>
		<groupId>com.google.oauth-client</groupId>
		<artifactId>google-oauth-client-jetty</artifactId>
		<version>1.32.1</version>
	</dependency>
	<dependency>
		<groupId>com.google.apis</groupId>
		<artifactId>google-api-services-sheets</artifactId>
		<version>v4-rev20210629-1.32.1</version>
	</dependency>
	<dependency>
		<groupId>com.aspose</groupId>
		<artifactId>aspose-cells</artifactId>
		<version>22.2</version>
	</dependency>
</dependencies>

Export Excel Data to Google Sheets in Java

The following are the steps to read an Excel file and then export its data to Google Sheets in a Java console application.

1. Create a new Java application (desktop).

2. Install Aspose.Cells for Java and Google Client APIs in the project, as mentioned in the previous section.

3. Copy the JSON file (the one we have downloaded after creating credentials in Google Cloud) and paste it into the project’s directory.

4. Specify the scopes of the application that define the access permissions to the sheets. Also, create variables for the token directory path and JSON factory.

/**
* Global instance of the scopes required. If modifying these scopes, delete
* your previously saved tokens/ folder.
*/
private static final String APPLICATION_NAME = "Google Sheets API Java Quickstart";
private static final JsonFactory JSON_FACTORY = GsonFactory.getDefaultInstance();
private static final String TOKENS_DIRECTORY_PATH = "tokens";
private static final List<String> SCOPES = Collections.singletonList(SheetsScopes.SPREADSHEETS);
view raw variables.java hosted with ❤ by GitHub

5. Create a getCredentials method that authorizes the user using the credentials file.

private static Credential getCredentials(final NetHttpTransport HTTP_TRANSPORT) throws IOException {
// Load client secrets
InputStream in = new FileInputStream(new File("credentials.json"));
GoogleClientSecrets clientSecrets = GoogleClientSecrets.load(JSON_FACTORY, new InputStreamReader(in));
// Build flow and trigger user authorization request
GoogleAuthorizationCodeFlow flow = new GoogleAuthorizationCodeFlow.Builder(HTTP_TRANSPORT, JSON_FACTORY,
clientSecrets, SCOPES)
.setDataStoreFactory(new FileDataStoreFactory(new java.io.File(TOKENS_DIRECTORY_PATH)))
.setAccessType("offline").build();
LocalServerReceiver receiver = new LocalServerReceiver.Builder().setPort(8888).build();
return new AuthorizationCodeInstalledApp(flow, receiver).authorize("user");
}
view raw getCredentials.java hosted with ❤ by GitHub

6. Create createSpreadsheet method to create a new spreadsheet on Google Sheets and set the name of the default sheet. This method returns a Spreadsheet object.

private static Spreadsheet createSpreadsheet(Sheets _service, String spreadsheetName, String _defaultSheetName)
throws IOException {
// Create a new spreadsheet
Spreadsheet spreadsheet = new Spreadsheet()
.setProperties(new SpreadsheetProperties().setTitle(spreadsheetName));
// Create a new sheet
Sheet sheet = new Sheet();
sheet.setProperties(new SheetProperties());
sheet.getProperties().setTitle(_defaultSheetName);
// Add sheet to list
List<Sheet> sheetList = new ArrayList<Sheet>();
sheetList.add(sheet);
spreadsheet.setSheets(sheetList);
// Execute request
spreadsheet = _service.spreadsheets().create(spreadsheet).setFields("spreadsheetId").execute();
System.out.println("Spreadsheet ID: " + spreadsheet.getSpreadsheetId());
return spreadsheet;
}
view raw createSpreadsheet.java hosted with ❤ by GitHub

7. Create an addSheet method to add a new sheet in the Google spreadsheet.

private static void addSheet(Sheets _service, String _spreadSheetID, String _sheetName) {
try {
// Add new Sheet
AddSheetRequest addSheetRequest = new AddSheetRequest();
addSheetRequest.setProperties(new SheetProperties());
addSheetRequest.getProperties().setTitle(_sheetName);
// Create update request
BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
Request req = new Request();
req.setAddSheet(addSheetRequest);
batchUpdateSpreadsheetRequest.setRequests(new ArrayList<Request>());
batchUpdateSpreadsheetRequest.getRequests().add(req);
// Execute request
_service.spreadsheets().batchUpdate(_spreadSheetID, batchUpdateSpreadsheetRequest).execute();
} catch (Exception e) {
System.out.println("Error in creating sheet: " + e.getMessage());
}
}
view raw addSheet.java hosted with ❤ by GitHub

8. Now, create the exportExcelToGoogle method, and in this method, initialize the Sheets service.

// Build a new authorized API client service.
final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
Sheets service = new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, getCredentials(HTTP_TRANSPORT))
.setApplicationName(APPLICATION_NAME).build();
view raw startService.java hosted with ❤ by GitHub

9. Then, load the Excel XLS/XLSX file using Aspose.Cells for Java. Also, get the name of the first worksheet in the workbook.

// Load an Excel workbook
Workbook wb = new Workbook(_excelFileName);
// Get name of the first worksheet
String defaultWorksheetName = wb.getWorksheets().get(0).getName().trim();
// Get all worksheets
WorksheetCollection collection = wb.getWorksheets();
view raw loadExcelFile.java hosted with ❤ by GitHub

10. Call the createSpreadsheet method to create a new spreadsheet on Google Sheets.

// Create a new Google spreadsheet with default worksheet
Spreadsheet spreadsheet = createSpreadsheet(service, wb.getFileName(), defaultWorksheetName);
view raw createNewSpreadsheet.java hosted with ❤ by GitHub

11. Read data from each worksheet and save it into a list.

String range;
// Loop through worksheets
for (int worksheetIndex = 0; worksheetIndex < collection.getCount(); worksheetIndex++) {
// Get reference of the worksheet
Worksheet ws = collection.get(worksheetIndex);
if (worksheetIndex == 0) {
// First sheet is created by default, so only set range
range = defaultWorksheetName + "!A:Y";
} else {
// Add a new sheet
addSheet(service, spreadsheet.getSpreadsheetId(), ws.getName().trim());
range = ws.getName().trim() + "!A:Y";
}
// Get number of rows and columns
int rows = ws.getCells().getMaxDataRow();
int cols = ws.getCells().getMaxDataColumn();
List<List<Object>> worksheetData = new ArrayList<List<Object>>();
// Loop through rows
for (int i = 0; i <= rows; i++) {
List<Object> rowData = new ArrayList<Object>();
// Loop through each column in selected row
for (int j = 0; j <= cols; j++) {
if (ws.getCells().get(i, j).getValue() == null)
rowData.add("");
else
rowData.add(ws.getCells().get(i, j).getValue());
}
// Add to worksheet data
worksheetData.add(rowData);
}
// TO-DO: Execute request to post data to Google Sheets
}
view raw readExcelFile.java hosted with ❤ by GitHub

12. For each worksheet in the Excel file, create a request to write data to the spreadsheet in the Google Sheets.

// Set range
ValueRange body = new ValueRange();
body.setRange(range);
// Set values
body.setValues(worksheetData);
// Export values to Google Sheets
UpdateValuesResponse result = service.spreadsheets().values()
.update(spreadsheet.getSpreadsheetId(), range, body).setValueInputOption("USER_ENTERED")
.execute();
// Print output
System.out.printf("%d cells updated.", result.getUpdatedCells());
view raw writeToGoogleSheets.java hosted with ❤ by GitHub

The complete method to read and export data from an Excel file to a spreadsheet in Google Sheets is given below.

private static void exportExcelToGoogle(String _excelFileName) {
try {
// Build a new authorized API client service.
final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
Sheets service = new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, getCredentials(HTTP_TRANSPORT))
.setApplicationName(APPLICATION_NAME).build();
// Load an Excel workbook
Workbook wb = new Workbook(_excelFileName);
// Get name of the first worksheet
String defaultWorksheetName = wb.getWorksheets().get(0).getName().trim();
// Get all worksheets
WorksheetCollection collection = wb.getWorksheets();
// Create a new Google spreadsheet with default worksheet
Spreadsheet spreadsheet = createSpreadsheet(service, wb.getFileName(), defaultWorksheetName);
System.out.println("Spreadsheet URL: " + spreadsheet.getSpreadsheetUrl());
System.out.println("ID: " + spreadsheet.getSpreadsheetId());
String range;
// Loop through worksheets
for (int worksheetIndex = 0; worksheetIndex < collection.getCount(); worksheetIndex++) {
// Get reference of the worksheet
Worksheet ws = collection.get(worksheetIndex);
if (worksheetIndex == 0) {
// First sheet is created by default, so only set range
range = defaultWorksheetName + "!A:Y";
} else {
// Add a new sheet
addSheet(service, spreadsheet.getSpreadsheetId(), ws.getName().trim());
range = ws.getName().trim() + "!A:Y";
}
// Get number of rows and columns
int rows = ws.getCells().getMaxDataRow();
int cols = ws.getCells().getMaxDataColumn();
List<List<Object>> worksheetData = new ArrayList<List<Object>>();
// Loop through rows
for (int i = 0; i <= rows; i++) {
List<Object> rowData = new ArrayList<Object>();
// Loop through each column in selected row
for (int j = 0; j <= cols; j++) {
if (ws.getCells().get(i, j).getValue() == null)
rowData.add("");
else
rowData.add(ws.getCells().get(i, j).getValue());
}
// Add to worksheet data
worksheetData.add(rowData);
}
// Set range
ValueRange body = new ValueRange();
body.setRange(range);
// Set values
body.setValues(worksheetData);
// Export values to Google Sheets
UpdateValuesResponse result = service.spreadsheets().values()
.update(spreadsheet.getSpreadsheetId(), range, body).setValueInputOption("USER_ENTERED")
.execute();
// Print output
System.out.printf("%d cells updated.", result.getUpdatedCells());
}
// Open Google spreadsheet in browser
Desktop desk = Desktop.getDesktop();
desk.browse(new URI("https://docs.google.com/spreadsheets/d/" + spreadsheet.getSpreadsheetId()));
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
view raw exportExcelToGoogle.java hosted with ❤ by GitHub

Complete Source Code

The following is the complete source code to convert an Excel XLSX file to Google Sheets in Java.

package excel_to_google_sheets;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;
import com.aspose.cells.WorksheetCollection;
import com.google.api.client.auth.oauth2.Credential;
import com.google.api.client.extensions.java6.auth.oauth2.AuthorizationCodeInstalledApp;
import com.google.api.client.extensions.jetty.auth.oauth2.LocalServerReceiver;
import com.google.api.client.googleapis.auth.oauth2.GoogleAuthorizationCodeFlow;
import com.google.api.client.googleapis.auth.oauth2.GoogleClientSecrets;
import com.google.api.client.googleapis.javanet.GoogleNetHttpTransport;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.JsonFactory;
import com.google.api.client.json.gson.GsonFactory;
import com.google.api.client.util.store.FileDataStoreFactory;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.AddSheetRequest;
import com.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetRequest;
import com.google.api.services.sheets.v4.model.Request;
import com.google.api.services.sheets.v4.model.Sheet;
import com.google.api.services.sheets.v4.model.SheetProperties;
import com.google.api.services.sheets.v4.model.Spreadsheet;
import com.google.api.services.sheets.v4.model.SpreadsheetProperties;
import com.google.api.services.sheets.v4.model.UpdateValuesResponse;
import com.google.api.services.sheets.v4.model.ValueRange;
import java.awt.Desktop;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.URI;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
public class ExportExcelToGoogle {
/**
* Global instance of the scopes required. If modifying these scopes, delete
* your previously saved tokens/ folder.
*/
private static final String APPLICATION_NAME = "Google Sheets API Java Quickstart";
private static final JsonFactory JSON_FACTORY = GsonFactory.getDefaultInstance();
private static final String TOKENS_DIRECTORY_PATH = "tokens";
private static final List<String> SCOPES = Collections.singletonList(SheetsScopes.SPREADSHEETS);
public static void main(String[] args) throws Exception {
// Export Excel data to Google Sheets
exportExcelToGoogle("workbook.xlsx");
}
private static void exportExcelToGoogle(String _excelFileName) {
try {
// Build a new authorized API client service.
final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
Sheets service = new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, getCredentials(HTTP_TRANSPORT))
.setApplicationName(APPLICATION_NAME).build();
// Load an Excel workbook
Workbook wb = new Workbook(_excelFileName);
// Get name of the first worksheet
String defaultWorksheetName = wb.getWorksheets().get(0).getName().trim();
// Get all worksheets
WorksheetCollection collection = wb.getWorksheets();
// Create a new Google spreadsheet with default worksheet
Spreadsheet spreadsheet = createSpreadsheet(service, wb.getFileName(), defaultWorksheetName);
String range;
// Loop through worksheets
for (int worksheetIndex = 0; worksheetIndex < collection.getCount(); worksheetIndex++) {
// Get reference of the worksheet
Worksheet ws = collection.get(worksheetIndex);
if (worksheetIndex == 0) {
// First sheet is created by default, so only set range
range = defaultWorksheetName + "!A:Y";
} else {
// Add a new sheet
addSheet(service, spreadsheet.getSpreadsheetId(), ws.getName().trim());
range = ws.getName().trim() + "!A:Y";
}
// Get number of rows and columns
int rows = ws.getCells().getMaxDataRow();
int cols = ws.getCells().getMaxDataColumn();
List<List<Object>> worksheetData = new ArrayList<List<Object>>();
// Loop through rows
for (int i = 0; i <= rows; i++) {
List<Object> rowData = new ArrayList<Object>();
// Loop through each column in selected row
for (int j = 0; j <= cols; j++) {
if (ws.getCells().get(i, j).getValue() == null)
rowData.add("");
else
rowData.add(ws.getCells().get(i, j).getValue());
}
// Add to worksheet data
worksheetData.add(rowData);
}
// Set range
ValueRange body = new ValueRange();
body.setRange(range);
// Set values
body.setValues(worksheetData);
// Export values to Google Sheets
UpdateValuesResponse result = service.spreadsheets().values()
.update(spreadsheet.getSpreadsheetId(), range, body).setValueInputOption("USER_ENTERED")
.execute();
// Print output
System.out.printf("%d cells updated.", result.getUpdatedCells());
}
// Open Google spreadsheet in browser
Desktop desk = Desktop.getDesktop();
desk.browse(new URI("https://docs.google.com/spreadsheets/d/" + spreadsheet.getSpreadsheetId()));
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
private static Spreadsheet createSpreadsheet(Sheets _service, String spreadsheetName, String _defaultSheetName)
throws IOException {
// Create a new spreadsheet
Spreadsheet spreadsheet = new Spreadsheet()
.setProperties(new SpreadsheetProperties().setTitle(spreadsheetName));
// Create a new sheet
Sheet sheet = new Sheet();
sheet.setProperties(new SheetProperties());
sheet.getProperties().setTitle(_defaultSheetName);
// Add sheet to list
List<Sheet> sheetList = new ArrayList<Sheet>();
sheetList.add(sheet);
spreadsheet.setSheets(sheetList);
// Execute request
spreadsheet = _service.spreadsheets().create(spreadsheet).setFields("spreadsheetId").execute();
System.out.println("Spreadsheet ID: " + spreadsheet.getSpreadsheetId());
return spreadsheet;
}
private static void addSheet(Sheets _service, String _spreadSheetID, String _sheetName) {
try {
// Add new Sheet
AddSheetRequest addSheetRequest = new AddSheetRequest();
addSheetRequest.setProperties(new SheetProperties());
addSheetRequest.getProperties().setTitle(_sheetName);
// Create update request
BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
Request req = new Request();
req.setAddSheet(addSheetRequest);
batchUpdateSpreadsheetRequest.setRequests(new ArrayList<Request>());
batchUpdateSpreadsheetRequest.getRequests().add(req);
// Execute request
_service.spreadsheets().batchUpdate(_spreadSheetID, batchUpdateSpreadsheetRequest).execute();
} catch (Exception e) {
System.out.println("Error in creating sheet: " + e.getMessage());
}
}
private static Credential getCredentials(final NetHttpTransport HTTP_TRANSPORT) throws IOException {
// Load client secrets
InputStream in = new FileInputStream(new File("credentials.json"));
GoogleClientSecrets clientSecrets = GoogleClientSecrets.load(JSON_FACTORY, new InputStreamReader(in));
// Build flow and trigger user authorization request
GoogleAuthorizationCodeFlow flow = new GoogleAuthorizationCodeFlow.Builder(HTTP_TRANSPORT, JSON_FACTORY,
clientSecrets, SCOPES)
.setDataStoreFactory(new FileDataStoreFactory(new java.io.File(TOKENS_DIRECTORY_PATH)))
.setAccessType("offline").build();
LocalServerReceiver receiver = new LocalServerReceiver.Builder().setPort(8888).build();
return new AuthorizationCodeInstalledApp(flow, receiver).authorize("user");
}
}
view raw ConvertExcelToGoogleSheets.java hosted with ❤ by GitHub

Demo – Convert Excel Files to Google Sheets in Java

Get a Free Aspose.Cells License

You can get a free temporary license and use Aspose.Cells for Java without evaluation limitations.

Conclusion

In this article, you have learned how to convert Excel XLS or XLSX files to Google Sheets programmatically in Java. We have provided the complete guidelines on how to create a Google Cloud project, enable Google Sheets API, read Excel files, and export data from Excel files to Google Sheets. You can also visit the documentation to read more about Aspose.Cells for Java. In case you would have any questions or queries, let us know via our forum.

See Also

This entry was posted in Aspose.Cells Product Family and tagged , , , . .