DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Securing Your Software Supply Chain with JFrog and Azure
Register Today

Trending

  • Web Development Checklist
  • Tactics and Strategies on Software Development: How To Reach Successful Software [Video]
  • A React Frontend With Go/Gin/Gorm Backend in One Project
  • Front-End: Cache Strategies You Should Know

Trending

  • Web Development Checklist
  • Tactics and Strategies on Software Development: How To Reach Successful Software [Video]
  • A React Frontend With Go/Gin/Gorm Backend in One Project
  • Front-End: Cache Strategies You Should Know

Using Apache POI to Read Excel (Part 2)

Now that your CSV data is in Excel, let's work on formatting it and adding cell styles to create better, more readable spreadsheets.

Jay Sridhar user avatar by
Jay Sridhar
CORE ·
Apr. 07, 17 · Tutorial
Like (9)
Save
Tweet
Share
14.61K Views

Join the DZone community and get the full member experience.

Join For Free

In part 1, we examined how to create an Excel spreadsheet and add data to it. We also looked at properly storing data into cells to avoid “Number Stored as Text” errors.

In this chapter, let us look at some more options for formatting data within an Excel spreadsheet.

Convert CSV to Excel

Let's see how we can convert CSV to an Excel spreadsheet. Excel offers this functionality with the Data Import Wizard when you attempt to import a CSV or text file. Here, we show you how to use Apache POI to parse CSVs and output an Excel Spreadsheet.

Workbook wb = new XSSFWorkbook();
CreationHelper ch = wb.getCreationHelper();
String safeName = WorkbookUtil.createSafeSheetName("Population");
Sheet sheet = wb.createSheet(safeName);
 
try (InputStream in = new FileInputStream(csvFile);) {
    CSV csv = new CSV(true, ',', in);
    List<String> colNames = null;
    if ( csv.hasNext() ) {
    colNames = new ArrayList<String>(csv.next());
    Row row = sheet.createRow((short)0);
    for (int i = 0 ; i < colNames.size() ; i++) {
        String name = colNames.get(i);
        row.createCell(i).setCellValue(name);
    }
    }
 
    int rowNum = 0;
    while (csv.hasNext()) {
    List<String> fields = csv.next();
    rowNum++;
    Row row = sheet.createRow((short)rowNum);
 
    /* First 5 fields are text. The next 2 are floating
     * point. */
    for (int i = 0 ; i < 5 ; i++) {
        String value = fields.get(i);
        row.createCell(i).setCellValue(value);
    }
 
    for (int i = 5 ; i < fields.size() ; i++) {
        /* Attempt to set as double. If that fails, set as
         * text. */
        try {
        double value = Double.parseDouble(fields.get(i));
        row.createCell(i).setCellValue(value);
        } catch(NumberFormatException ex) {
        String value = fields.get(i);
        row.createCell(i).setCellValue(value);
        }
    }
    }
}
 
for (short i = sheet.getRow(0).getFirstCellNum(),
     end = sheet.getRow(0).getLastCellNum() ; i < end ; i++) {
    sheet.autoSizeColumn(i);
}
 
FileOutputStream fileOut = new FileOutputStream(xlsFile);
wb.write(fileOut);
fileOut.close();


Here is what the spreadsheet looks like:

Image title

Adding Filter Headers

To automatically turn on filtering on some or all the columns of data, use the method setAutoFilter(). The code below turns on auto filtering for all the columns.

for (short i = sheet.getRow(0).getFirstCellNum(),
     end = sheet.getRow(0).getLastCellNum() ; i < end ; i++) {
    CellRangeAddress ca =
    new CellRangeAddress(0, rowNum,
                 sheet.getRow(0).getFirstCellNum(),
                 sheet.getRow(0).getLastCellNum());
    sheet.setAutoFilter(ca);
}


As you can see in the pic below, all columns have the Excel Filter drop-down enabled.

Image title

Formatting Date Cells

To format a cell as a date, you have to set the cell value as a Date and associate a date format with the cell.

Create a cell style with a suitable date format as follows:

Workbook wb = new XSSFWorkbook();
CreationHelper ch = wb.getCreationHelper();
CellStyle dateStyle = wb.createCellStyle();
dateStyle.setDataFormat(ch.createDataFormat().getFormat("d-mmm-yy"));


Parse the text to a Date . and associate the cell style as shown below. (Creating and using the cell style is expensive. Create, store and re-use it to avoid re-creation.)

SimpleDateFormat datefmt = new SimpleDateFormat("yyyy-MM-dd");
...
String value = ...;
Date date = datefmt.parse(value);
Cell cell = row.createCell(i);
cell.setCellValue(date);
cell.setCellStyle(bdateStyle);


Cell Alignment Options

You can align cell data with a variety of options. For horizontal alignment, this involves setting the cell style to HorizontalAlignment.CENTER or HorizontalAlignment.RIGHT.

Workbook wb = new XSSFWorkbook();
CellStyle hcenter = wb.createCellStyle();
hcenter.setAlignment(HorizontalAlignment.CENTER);
 
CellStyle hright = wb.createCellStyle();
hright.setAlignment(HorizontalAlignment.RIGHT);


Set the cell style for each cell.

Cell cell = row.createCell(i);
cell.setCellStyle(hright); // as appropriate
cell.setCellValue(value);


Image title

Text Wrapping

There is an option to enable text wrap using the cell style. It is enabled as follows:

CellStyle wrap = wb.createCellStyle();
wrap.setWrapText(true);


With this style set on a cell, Excel may not actually display cell data on multiple lines. This could be because the row height is set to a specific height preventing the display of multiple lines.

To set the row height to display multiple lines, do the following in Excel. (From here.)

On the Home tab, select Format in the Cells group.

Under Cell Size, select AutoFit Row Height. Now you can see the text has been wrapped and is visible.

Summary

In this article, we looked at some more options for formatting an Excel spreadsheet using Apache POI. We covered converting CSV data to Excel, Adding sort headers to columns and date formatting. In addition, cell styles can be used to set up alignment and text wrap options.

Apache POI

Published at DZone with permission of Jay Sridhar, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Trending

  • Web Development Checklist
  • Tactics and Strategies on Software Development: How To Reach Successful Software [Video]
  • A React Frontend With Go/Gin/Gorm Backend in One Project
  • Front-End: Cache Strategies You Should Know

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: