April 23, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Using Java to Import and Manipulate Microsoft Excel Documents, Page 2

  • February 6, 2009
  • By Aleksey Shevchenko
  • Send Email »
  • More Articles »

3. Struts Action Code

Struts action will have code to get the file from the StrutsUploadForm action form, check content type, and pass File's input stream to the Workbook class. Workbook is a JExcelApi class that represents a Workbook. The class contains the various factory methods and provides a variety of accessors that provide access to the work sheets (see Listing 3).

Listing 3: Excerpt from ExcelUploadAction.java struts action class

...
StrutsUploadForm uploadForm = (StrutsUploadForm)form;;
FormFile myFile = uploadForm.getExcelFile();
Workbook workbook =
   Workbook.getWorkbook(myFile.getInputStream());
...

Now that you have the spreadsheet read into the Workbook class, you can start iterating through its rows. The following code will get the first sheet from the workbook, get the number of rows and columns, and iterate thought each row, displaying the content of each cell (see Listing 4).

Listing 4: Excerpt from ExcelUploadAction.java struts action class

...
Sheet sheet = workbook.getSheet(0);
int numberOfRows = sheet.getRows();
int numberOfColumns = sheet.getColumns();
for (int row = 0; row < numberOfRows; row ++ ) {
   for (int column = 0; column < numberOfColumns; column ++ ) {
            Cell cell = sheet.getCell(column,row);
            System.out.print(cell.getContents() + " | ");
   }
   System.out.println();
}
...

In the following code snippet, you will fetch all values under the column name "Age" and calculate an average age.

Listing 5: Calculating average Age in Java

...
LabelCell labelCell = sheet.findLabelCell("Age");
int ageColumnNumber = labelCell.getColumn();
double ageSum = 0;
for (int row = 1; row < numberOfRows; row ++ ) {
Cell cell = sheet.getCell(ageColumnNumber,row);
   if (CellType.NUMBER.equals(cell.getType())){
      ageSum = ageSum + Integer.parseInt(cell.getContents());
   }
}

double averageAge =  ageSum / (numberOfRows - 1);
System.out.println("Sum Age : " + ageSum);
System.out.println("Average Age : " + averageAge);
...

Example 2: Output an Excel File from a Servlet

In this example, you will create an Excel spreadsheet and return it back to the browser.

As you will see from the example below, creating an Excel spreadsheet using JExcelApi is a fairly simple process. In your doPost() method, set HTTP Response content type to "ms-excel" and set Content-Disposition to "attachment" and provide the attachment's file name.

Listing 6: Output an Excel file from a Servlet

...
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition",
   "attachment; filename=sampleName.xls");
WritableWorkbook writableWorkbook =
   Workbook.createWorkbook(response.getOutputStream());
WritableSheet writableSheet =
   writableWorkbook.createSheet("Demo", 0);
writableSheet.addCell(new Label(0, 0, "Hello World"));
writableWorkbook.write();
writableWorkbook.close();
...

The output of this example creates a spreadsheet "sampleName.xls" that contains one worksheet that contains "Hello World" text in cell A1.

Download the Code

The Source Code for this article can be downloaded here.

Reference

About the Author

Aleksey Shevchenko has been working with object-oriented languages for eight years. For the past four years, he has served as a technical lead and a project manager. Aleksey has been implementing Enterprise IT solutions for Wall Street and the manufacturing and publishing industries.





Page 2 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel