import java.io.*;
import org.apache.poi.hssf.usermodel.*;

/**
 * 틀고정, 나누기
 */
public class SplitAndFreezePanes
{
    public static void main(String[] args)
    {
        FileOutputStream out = null;
        HSSFWorkbook workbook;
        HSSFSheet sheet1;
        HSSFSheet sheet2;
        HSSFSheet sheet3;
        HSSFSheet sheet4;

        try
        {
            // workbook 생성
            workbook = new HSSFWorkbook();

            // sheet1 생성
            sheet1 = workbook.createSheet("sheet1");
            // sheet2 생성
            sheet2 = workbook.createSheet("sheet2");
            // sheet3 생성
            sheet3 = workbook.createSheet("sheet3");
            // sheet4 생성
            sheet4 = workbook.createSheet("sheet4");

            // 첫번째 row 틀고정
            // 6번째 row 부터 보임
            // (int colSplit, int rowSplit, int leftmostColumn, int topRow)
            // colSplit - Horizonatal position of split.
            // rowSplit - Vertical position of split.
            // leftmostColumn - Left column visible in right pane.
            // topRow - Top row visible in bottom pane
            sheet1.createFreezePane(0, 1, 0, 5);

            // 첫번째 column 틀고정
            // F column 부터 보임
            sheet2.createFreezePane(1, 0, 5, 0);

            // C3 에서 틀고정
            // (int colSplit, int rowSplit)
            // colSplit - Horizonatal position of split.
            // rowSplit - Vertical position of split.
            sheet3.createFreezePane(2, 2);

            // 나누기
            // (int xSplitPos, int ySplitPos, int leftmostColumn, int topRow, int activePane)
            // xSplitPos - Horizonatal position of split (in 1/20th of a point).
            // ySplitPos - Vertical position of split (in 1/20th of a point).
            // leftmostColumn - Left column visible in right pane.
            // topRow - Top row visible in bottom pane
            // activePane - Active pane. One of: PANE_LOWER_RIGHT,
            // PANE_UPPER_RIGHT, PANE_LOWER_LEFT, PANE_UPPER_LEFT
            sheet4.createSplitPane(5000, 5000, 0, 0, HSSFSheet.PANE_LOWER_LEFT);

            out = new FileOutputStream("SplitAndFreezePanes.xls");
            workbook.write(out);
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
        finally
        {
            closeOutputStream(out);
        }
    }

    /**
     * close OutputStream.
     *
     * @param   con
     */
    public static void closeOutputStream(OutputStream out)
    {
        try
        {
            if (out != null)
            {
                out.close();
            }
        }
        catch (IOException e)
        {
            // ignore
        }
    }
}

출처 - http://blog.naver.com/PostView.nhn?blogId=ssari93&logNo=120007182716

Reading/writing excel files in java : POI tutorial

If you are building a software for HR or finance domain, there is usually requirement for generating excel reports which are usually across management levels. Apart from reports, you can expect input data for application coming in form of excel sheets and application is expected to support it. These are many open source APIs to handle such scenarios.

Apache POI is one of them and is well trusted over time. In short, you can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java.

In this post, I am discussing some common activities required to do in real life application.

Sections in this post:

Apache POI runtime dependencies
Some useful common classes
Writing an excel file
Reading an excel file
Using formulas in excel sheet
Formatting the cells
Sourcecode download

Apache POI runtime dependencies

If you are working on a maven project, you can include the POI dependency in pom.xml file using this:

If you are not using maven, then you can download maven jar files from POI download page. Include following jar files minimum to run the sample code:

• dom4j-1.6.1.jar
• poi-3.9-20121203.jar
• poi-ooxml-3.9-20121203.jar
• poi-ooxml-schemas-3.9-20121203.jar
• xmlbeans-2.3.0.jar

Some useful POI classes

Apache POI main classes usually start with either HSSF, XSSF or SXSSF.

• HSSF is the POI Project’s pure Java implementation of the Excel ’97(-2007) file format. e.g. HSSFWorkbook, HSSFSheet.
• XSSF is the POI Project’s pure Java implementation of the Excel 2007 OOXML (.xlsx) file format. e.g. XSSFWorkbook, XSSFSheet.
• SXSSF (since 3.8-beta3) is an API-compatible streaming extension of XSSF to be used when very large spreadsheets have to be produced, and heap space is limited. e.g. SXSSFWorkbook, SXSSFSheet. SXSSF achieves its low memory footprint by limiting access to the rows that are within a sliding window, while XSSF gives access to all rows in the document.

Apart from above classes, Row and Cell are used to interact with a particular row and a particular cell in excel sheet.

Another useful class FormulaEvaluator is used to evaluate the formula cells in excel sheet.

A wide range of classes like CellStyle, BuiltinFormats, ComparisonOperator, ConditionalFormattingRule, FontFormatting,IndexedColors, PatternFormatting, SheetConditionalFormatting etc. are used when you have to add formatting in a sheet, mostly based on some rules.

We will see the usage of above classes in coming examples.

Writing an excel file

I am taking this example first so that we can reuse the excel sheet created by this code to read back in next example.

Writing a file using POI is very simple and involve following steps:

1. Create a workbook
2. Create a sheet in workbook
3. Create a row in sheet
4. Add cells in sheet
5. Repeat step 3 and 4 to write more data

It seems very simple, right? Lets have a look at the code doing these steps:

Reading an excel file

Reading an excel file is also very simple if we divide this in steps.

1. Create workbook instance from excel sheet
2. Get to the desired sheet
3. Increment row number
4. iterate over all cells in a row
5. repeat step 3 and 4 until all data is read

Lets see all above steps in code. I am writing the code to read the excel file created in above example.

Using formulas in excel sheet

When working on complex excel sheets, we encounter many cells which have formula to calculate their values. These are formula cells. Apache POI has excellent support for adding formula cells and evaluating already present formula cells also.

Les see one example of how to set formula cells in excel?

In this code, there are four cells in a row and fourth one in multiplication of all previous 3 rows. So the formula will be : A2*B2*C2 (in second row)

Similarly, I you want to read a file which have formula cells in it, use following logic to evaluate the formula cells.

Formatting the cells

So for we have seen the examples of reading/ writing and excel file using apache POI. But, when we are creating a report in excel file and it becomes utmost important to add formatting on cells which fit into any per-determined criteria. This formatting can be a different coloring based on certain value range, based on expiry date limit etc.

In below examples, I am taking couple of such formatting examples for various purposes.

1) Cell value is in between a certain range

This piece of code will color any cell in range whose value is between a configured range. [e.g. between 50 and 70]

2) Highlight duplicate values

Highlight all cells which have duplicate values in observed cells

3) Color alternate rows in different colors

A simple code to color each alternate row in a different color

4) Color amounts which are going to expire in next 30 days

A very useful code for financial projects which keep track of dead lines.

I am ending this post here for keeping the post in limit. I will post some useful code samples in coming posts.

Sourcecode download

Click on below given link to download the source code of above examples.

Download Sourcecode

Happy Learning !!

References

http://poi.apache.org/spreadsheet/quick-guide.html

출처 - http://howtodoinjava.com/2013/06/19/readingwriting-excel-files-in-java-poi-tutorial/

Spring 3 - Apache POI - Hibernate: Creating an Excel Report Tutorial

In this tutorial we will build a simple Spring MVC 3 application with reporting capabilities. We will useApache POI to generate dynamic Excel reports and Hibernate for the ORM framework. We will use a simple Java List as our data source. The data will be retrieved from a MySQL database. 

This tutorial is part of the following reporting tutorial series that uses Jasper, DynamicJasper, and Apache POI:

Spring 3 - Apache POI - Hibernate: Creating an Excel Report Tutorial
Spring 3 - DynamicJasper - Hibernate Tutorial: Concatenating a DynamicReport
Spring 3 - DynamicJasper - Hibernate Tutorial: Concatenating a Subreport
Spring 3 - DynamicJasper - Hibernate Tutorial: Using Plain List
Spring 3 - DynamicJasper - Hibernate Tutorial: Using JRDataSource
Spring 3 - DynamicJasper - Hibernate Tutorial: Using HQL Query

All of these tutorials produce the same document, and all of them demonstrate different ways of creating the same report.

What is Apache POI?

The Apache POI Project's mission is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft's OLE 2 Compound Document format (OLE2). In short, you can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java. Apache POI is your Java Excel solution (for Excel 97-2008). 

Source: http://poi.apache.org/

Background

Before we start our application, let's preview first the final print document:

Our document is a simple Excel document. It's a Sales Report for a list of power supplies. The data is retrieved from a MySQL database. 

Domain

Notice that for each Power Supply entry there's a common set of properties:

id
brand
model
maximum power
price
efficiency

Development

Domain

We'll start our application by declaring the domain object PowerSupply

PowerSupply.java

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91

package org.krams.tutorial.domain;   import java.io.Serializable;   import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.Id; import javax.persistence.Table;   /**  * A simple POJO containing the common properties of a Power Supply  * This is an annotated Hibernate entity.  *  * @author Krams at {@link http://krams915@blogspot.com}  */ @Entity @Table(name = "POWER_SUPPLY") public class PowerSupply implements Serializable {    private static final long serialVersionUID = 8634209606034270882L;    @Id  @Column(name = "ID")  @GeneratedValue  private Long id;     @Column(name = "BRAND")  private String brand;     @Column(name = "MODEL")  private String model;     @Column(name = "MAXIMUM_POWER")  private String maximumPower;     @Column(name = "PRICE")  private Double price;     @Column(name = "EFFICIENCY")  private Double efficiency;    public Long getId() {   return id;  }    public void setId(Long id) {   this.id = id;  }    public String getBrand() {   return brand;  }    public void setBrand(String brand) {   this.brand = brand;  }    public String getModel() {   return model;  }    public void setModel(String model) {   this.model = model;  }    public String getMaximumPower() {   return maximumPower;  }    public void setMaximumPower(String maximumPower) {   this.maximumPower = maximumPower;  }    public Double getPrice() {   return price;  }    public void setPrice(Double price) {   this.price = price;  }    public Double getEfficiency() {   return efficiency;  }    public void setEfficiency(Double efficiency) {   this.efficiency = efficiency;  }    }

PowerSupply is a simple POJO containing six private fields. Each of these fields have been annotated with @Column and assigned with corresponding database column names.

ID
BRAND
MODEL
MAXIMUM_POWER
PRICE
EFFICIENCY

Service

We'll be declaring a single service named DownloadService. This service is the heart of the application that will process and retrieve the report document.

The service will run the following steps:

1. Create new workbook
2. Create new worksheet
3. Define starting indices for rows and columns
4. Build layout 
5. Fill report
6. Set the HttpServletResponse properties
7. Write to the output stream

DownloadService.java 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95

package org.krams.tutorial.service;   import java.util.List; import javax.servlet.http.HttpServletResponse;   import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.hibernate.Query; import org.hibernate.Session; import org.hibernate.SessionFactory; import org.krams.tutorial.domain.PowerSupply; import org.krams.tutorial.report.FillManager; import org.krams.tutorial.report.Layouter; import org.krams.tutorial.report.Writer; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional;   import javax.annotation.Resource;   /**  * Service for processing Apache POI-based reports  *  * @author Krams at {@link http://krams915@blogspot.com}  */ @Service("downloadService") @Transactional public class DownloadService {    private static Logger logger = Logger.getLogger("service");     @Resource(name="sessionFactory")  private SessionFactory sessionFactory;     /**   * Processes the download for Excel format.   * It does the following steps:   * <pre>1. Create new workbook   * 2. Create new worksheet   * 3. Define starting indices for rows and columns   * 4. Build layout   * 5. Fill report   * 6. Set the HttpServletResponse properties   * 7. Write to the output stream   * </pre>   */  @SuppressWarnings("unchecked")  public void downloadXLS(HttpServletResponse response) throws ClassNotFoundException {   logger.debug("Downloading Excel report");       // 1. Create new workbook   HSSFWorkbook workbook = new HSSFWorkbook();       // 2. Create new worksheet   HSSFSheet worksheet = workbook.createSheet("POI Worksheet");       // 3. Define starting indices for rows and columns   int startRowIndex = 0;   int startColIndex = 0;       // 4. Build layout   // Build title, date, and column headers   Layouter.buildReport(worksheet, startRowIndex, startColIndex);     // 5. Fill report   FillManager.fillReport(worksheet, startRowIndex, startColIndex, getDatasource());       // 6. Set the response properties   String fileName = "SalesReport.xls";   response.setHeader("Content-Disposition", "inline; filename=" + fileName);   // Make sure to set the correct content type   response.setContentType("application/vnd.ms-excel");       //7. Write to the output stream   Writer.write(response, worksheet);  }     /**   * Retrieves the datasource as as simple Java List.   * The datasource is retrieved from a Hibernate HQL query.   */  @SuppressWarnings("unchecked")  private List<PowerSupply> getDatasource() {           // Retrieve session   Session session = sessionFactory.getCurrentSession();   // Create query for retrieving products   Query query = session.createQuery("FROM PowerSupply");   // Execute query   List<PowerSupply> result = query.list();       // Return the datasource   return result;  } }

This service is our download service for generating the report document. It should be clear what each line of code is doing. 

The service has been divided into separate classes to encapsulate specific jobs. 

The Layouter

The purpose of the Layouter is to layout the design of the report. Here's where we declare the dynamic columns and special properties of the document.

Layouter.java

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128

package org.krams.tutorial.report;   import java.util.Date; import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.util.CellRangeAddress; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font;   /**  * Builds the report layout, the template, the design, the pattern or whatever synonym you may want to call it.  *  * @author Krams at {@link http://krams915@blogspot.com}  */ public class Layouter {    private static Logger logger = Logger.getLogger("service");     /**   * Builds the report layout.   * <p>   * This doesn't have any data yet. This is your template.   */  public static void buildReport(HSSFSheet worksheet, int startRowIndex, int startColIndex) {   // Set column widths   worksheet.setColumnWidth(0, 5000);   worksheet.setColumnWidth(1, 5000);   worksheet.setColumnWidth(2, 5000);   worksheet.setColumnWidth(3, 5000);   worksheet.setColumnWidth(4, 5000);   worksheet.setColumnWidth(5, 5000);       // Build the title and date headers   buildTitle(worksheet, startRowIndex, startColIndex);   // Build the column headers   buildHeaders(worksheet, startRowIndex, startColIndex);  }     /**   * Builds the report title and the date header   *   * @param worksheet   * @param startRowIndex starting row offset   * @param startColIndex starting column offset   */  public static void buildTitle(HSSFSheet worksheet, int startRowIndex, int startColIndex) {   // Create font style for the report title   Font fontTitle = worksheet.getWorkbook().createFont();   fontTitle.setBoldweight(Font.BOLDWEIGHT_BOLD);   fontTitle.setFontHeight((short) 280);             // Create cell style for the report title         HSSFCellStyle cellStyleTitle = worksheet.getWorkbook().createCellStyle();         cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER);         cellStyleTitle.setWrapText(true);         cellStyleTitle.setFont(fontTitle);             // Create report title   HSSFRow rowTitle = worksheet.createRow((short) startRowIndex);   rowTitle.setHeight((short) 500);   HSSFCell cellTitle = rowTitle.createCell(startColIndex);   cellTitle.setCellValue("Sales Report");   cellTitle.setCellStyle(cellStyleTitle);       // Create merged region for the report title   worksheet.addMergedRegion(new CellRangeAddress(0,0,0,5));       // Create date header   HSSFRow dateTitle = worksheet.createRow((short) startRowIndex +1);   HSSFCell cellDate = dateTitle.createCell(startColIndex);   cellDate.setCellValue("This report was generated at " + new Date());  }     /**   * Builds the column headers   *   * @param worksheet   * @param startRowIndex starting row offset   * @param startColIndex starting column offset   */  public static void buildHeaders(HSSFSheet worksheet, int startRowIndex, int startColIndex) {   // Create font style for the headers   Font font = worksheet.getWorkbook().createFont();         font.setBoldweight(Font.BOLDWEIGHT_BOLD);           // Create cell style for the headers   HSSFCellStyle headerCellStyle = worksheet.getWorkbook().createCellStyle();   headerCellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);   headerCellStyle.setFillPattern(CellStyle.FINE_DOTS);   headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);   headerCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);   headerCellStyle.setWrapText(true);   headerCellStyle.setFont(font);   headerCellStyle.setBorderBottom(CellStyle.BORDER_THIN);       // Create the column headers   HSSFRow rowHeader = worksheet.createRow((short) startRowIndex +2);   rowHeader.setHeight((short) 500);       HSSFCell cell1 = rowHeader.createCell(startColIndex+0);   cell1.setCellValue("Id");   cell1.setCellStyle(headerCellStyle);     HSSFCell cell2 = rowHeader.createCell(startColIndex+1);   cell2.setCellValue("Brand");   cell2.setCellStyle(headerCellStyle);     HSSFCell cell3 = rowHeader.createCell(startColIndex+2);   cell3.setCellValue("Model");   cell3.setCellStyle(headerCellStyle);     HSSFCell cell4 = rowHeader.createCell(startColIndex+3);   cell4.setCellValue("Max Power");   cell4.setCellStyle(headerCellStyle);     HSSFCell cell5 = rowHeader.createCell(startColIndex+4);   cell5.setCellValue("Price");   cell5.setCellStyle(headerCellStyle);      HSSFCell cell6 = rowHeader.createCell(startColIndex+5);   cell6.setCellValue("Efficiency");   cell6.setCellStyle(headerCellStyle);  } }

The FillManager

The purpose of the FillManager is to fill the Excel report with data from the data source.

FillManager.java

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67

package org.krams.tutorial.report;   import java.util.List;   import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.ss.usermodel.CellStyle; import org.krams.tutorial.domain.PowerSupply;   public class FillManager {    /**   * Fills the report with content   *   * @param worksheet   * @param startRowIndex starting row offset   * @param startColIndex starting column offset   * @param datasource the data source   */  public static void fillReport(HSSFSheet worksheet, int startRowIndex, int startColIndex, List<PowerSupply> datasource) {   // Row offset   startRowIndex += 2;       // Create cell style for the body   HSSFCellStyle bodyCellStyle = worksheet.getWorkbook().createCellStyle();   bodyCellStyle.setAlignment(CellStyle.ALIGN_CENTER);   bodyCellStyle.setWrapText(true);       // Create body   for (int i=startRowIndex; i+startRowIndex-2< datasource.size()+2; i++) {    // Create a new row    HSSFRow row = worksheet.createRow((short) i+1);      // Retrieve the id value    HSSFCell cell1 = row.createCell(startColIndex+0);    cell1.setCellValue(datasource.get(i-2).getId());    cell1.setCellStyle(bodyCellStyle);      // Retrieve the brand value    HSSFCell cell2 = row.createCell(startColIndex+1);    cell2.setCellValue(datasource.get(i-2).getBrand());    cell2.setCellStyle(bodyCellStyle);      // Retrieve the model value    HSSFCell cell3 = row.createCell(startColIndex+2);    cell3.setCellValue(datasource.get(i-2).getModel());    cell3.setCellStyle(bodyCellStyle);      // Retrieve the maximum power value    HSSFCell cell4 = row.createCell(startColIndex+3);    cell4.setCellValue(datasource.get(i-2).getMaximumPower());    cell4.setCellStyle(bodyCellStyle);      // Retrieve the price value    HSSFCell cell5 = row.createCell(startColIndex+4);    cell5.setCellValue(datasource.get(i-2).getPrice());    cell5.setCellStyle(bodyCellStyle);        // Retrieve the efficiency value    HSSFCell cell6 = row.createCell(startColIndex+5);    cell6.setCellValue(datasource.get(i-2).getEfficiency());    cell6.setCellStyle(bodyCellStyle);   }  } }

The Writer

The purpose of the Writer is to write the "exported" worksheet to the output stream. Once the document has been written to the stream, the user will receive the document ready to be downloaded.

Writer.java

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

package org.krams.tutorial.report;   import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse;   import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.HSSFSheet;   /**  * Writes the report to the output stream  *  * @author Krams at {@link http://krams915@blogspot.com}  */ public class Writer {    private static Logger logger = Logger.getLogger("service");  /**   * Writes the report to the output stream   */  public static void write(HttpServletResponse response, HSSFSheet worksheet) {       logger.debug("Writing report to the stream");   try {    // Retrieve the output stream    ServletOutputStream outputStream = response.getOutputStream();    // Write to the output stream    worksheet.getWorkbook().write(outputStream);    // Flush the stream    outputStream.flush();     } catch (Exception e) {    logger.error("Unable to write report to the output stream");   }  } }

Controller

We've completed the domain and service layer of the application. Since we're developing a Spring MVC web application, we're required to declare a controller that will handle the user's request.

DownloadController.java

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

package org.krams.tutorial.controller;   import javax.annotation.Resource; import javax.servlet.http.HttpServletResponse; import org.apache.log4j.Logger; import org.krams.tutorial.service.DownloadService; import org.springframework.stereotype.Controller; import org.springframework.ui.Model; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod;   /**  * Handles download requests  *  * @author Krams at {@link http://krams915@blogspot.com}  */ @Controller @RequestMapping("/download") public class DownloadController {    private static Logger logger = Logger.getLogger("controller");     @Resource(name="downloadService")  private DownloadService downloadService;    /**   * Downloads the report as an Excel format.   * <p>   * Make sure this method doesn't return any model. Otherwise, you'll get   * an "IllegalStateException: getOutputStream() has already been called for this response"   */     @RequestMapping(value = "/xls", method = RequestMethod.GET)     public void getXLS(HttpServletResponse response, Model model) throws ClassNotFoundException {      logger.debug("Received request to download report as an XLS");             // Delegate to downloadService. Make sure to pass an instance of HttpServletResponse      downloadService.downloadXLS(response);  } }

DownloadController is a simple controller that handles download requests. It delegates report generation to the DownloadService. Notice we're required to pass the HttpServletResponse to the service.

Database Configuration

We've completed the MVC module of the application. However we haven't created yet the Hibernate configuration and the MySQL database.

Our first task is to create an empty MySQL database. 

Here are the steps:
1. Run MySQL
2. Open MySQL admin
3. Create a new database mydb

In this tutorial I've setup a local MySQL database and used phpmyadmin to administer it. 

Next, we'll be declaring a hibernate-context.xml configuration file. Its purpose is to contain all of Spring-related configuration for Hibernate.

hibernate-context.xml

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

<?xml version="1.0" encoding="UTF-8"?> <beans  xmlns="http://www.springframework.org/schema/beans"         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"         xmlns:p="http://www.springframework.org/schema/p"         xmlns:tx="http://www.springframework.org/schema/tx"         xmlns:context="http://www.springframework.org/schema/context"         xsi:schemaLocation="    http://www.springframework.org/schema/beans    http://www.springframework.org/schema/beans/spring-beans-3.0.xsd    http://www.springframework.org/schema/tx    http://www.springframework.org/schema/tx/spring-tx-3.0.xsd    http://www.springframework.org/schema/context    http://www.springframework.org/schema/context/spring-context-3.0.xsd       ">    <context:property-placeholder location="/WEB-INF/spring.properties" />        <!-- Declare a pooled datasource -->   <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"     destroy-method="close"     p:driverClass="${app.jdbc.driverClassName}"     p:jdbcUrl="${app.jdbc.url}"     p:user="${app.jdbc.username}"     p:password="${app.jdbc.password}"     p:acquireIncrement="5"     p:idleConnectionTestPeriod="60"     p:maxPoolSize="100"     p:maxStatements="50"     p:minPoolSize="10" />           <!-- Declare the Hibernate SessionFactory for retrieving Hibernate sessions -->     <!-- See http://static.springsource.org/spring/docs/3.0.x/javadoc-api/org/springframework/orm/hibernate3/annotation/AnnotationSessionFactoryBean.html -->         <!-- See http://docs.jboss.org/hibernate/stable/core/api/index.html?org/hibernate/SessionFactory.html -->  <!-- See http://docs.jboss.org/hibernate/stable/core/api/index.html?org/hibernate/Session.html -->  <bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"      p:dataSource-ref="dataSource"      p:configLocation="${hibernate.config}"      p:packagesToScan="org.krams.tutorial"/>     <!-- Declare a transaction manager-->  <bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager"           p:sessionFactory-ref="sessionFactory" />         <!-- Enable annotation style of managing transactions -->  <tx:annotation-driven transaction-manager="transactionManager" /> </beans>

This configuration requires two external configurations further:

spring.properties

?

1 2 3 4 5 6 7 8

# database properties app.jdbc.driverClassName=com.mysql.jdbc.Driver app.jdbc.url=jdbc:mysql://localhost/mydb app.jdbc.username=root app.jdbc.password=   #hibernate properties hibernate.config=/WEB-INF/hibernate.cfg.xml

hibernate.cfg.xml

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE hibernate-configuration PUBLIC   "-//Hibernate/Hibernate Configuration DTD 3.0//EN"   "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">     <hibernate-configuration>  <session-factory>   <!-- We're using a MySQL database so the dialect needs to be MySQL as well -->   <!-- Also we want to use MySQL's InnoDB engine -->   <property name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>       <!-- Enable this to see the Hibernate generated SQL statements in the logs -->   <property name="show_sql">false</property>       <!-- Setting this to 'create' will drop our existing database and re-create a new one.     This is only good for testing. In production, this is a bad idea! -->   <property name="hbm2ddl.auto">create</property>  </session-factory> </hibernate-configuration>

The Import.SQL

After declaring all the Hibernate-related configuration, let's now declare a SQL script that will populate our database with a sample data automatically.

import.sql

?

1 2 3 4 5 6

insert into POWER_SUPPLY (ID, BRAND, MODEL, MAXIMUM_POWER, PRICE, EFFICIENCY) values (null, 'Corsair', 'CMPSU-750TX', '750W', '109.99', '0.80') insert into POWER_SUPPLY (ID, BRAND, MODEL, MAXIMUM_POWER, PRICE, EFFICIENCY) values (null, 'Antec', 'NEO ECO 620C', '620W', '69.99', '0.80') insert into POWER_SUPPLY (ID, BRAND, MODEL, MAXIMUM_POWER, PRICE, EFFICIENCY) values (null, 'OCZ', 'OCZ700MXSP', '700W', '89.99', '0.86') insert into POWER_SUPPLY (ID, BRAND, MODEL, MAXIMUM_POWER, PRICE, EFFICIENCY) values (null, 'Thermaltake', 'W0070RUC', '430W', '43.99', '0.65') insert into POWER_SUPPLY (ID, BRAND, MODEL, MAXIMUM_POWER, PRICE, EFFICIENCY) values (null, 'COOLER MASTER', 'RS-460-PSAR-J3', '460W', '29.99', '0.70') insert into POWER_SUPPLY (ID, BRAND, MODEL, MAXIMUM_POWER, PRICE, EFFICIENCY) values (null, 'Rosewill', 'RG530-S12', '530W', '54.99', '0.80')

Make sure to place this document under the classpath. Hibernate will automatically import the contents of this document everytime your start the application. This is dictated by the hbm2ddl.auto setting we declared in the hibernate.cfg.xml earlier.

We're not required to create this import.sql file. We could of course create a MySQL SQL script and import it directly to the database, or add the data manually in the database. I just believe this is convenient for development purposes.

Spring MVC Configuration

We've declared all the necessary classes and Hibernate-related configuration of the application. However, we haven't created yet the required Spring MVC configuration. 

Let's begin with the web.xml
web.xml

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

<servlet>   <servlet-name>spring</servlet-name>   <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>   <load-on-startup>1</load-on-startup>  </servlet>     <servlet-mapping>   <servlet-name>spring</servlet-name>   <url-pattern>/krams/*</url-pattern>  </servlet-mapping>    <listener>   <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>  </listener>

Take note of the URL pattern. When accessing any pages in our MVC application, the host name must be appended with

/krams

In the web.xml we declared a servlet-name spring. By convention, we must declare a spring-servlet.xml.

spring-servlet.xml

?

1 2 3

<!-- Declare a view resolver for resolving JSPs -->  <bean id="viewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver"       p:prefix="/WEB-INF/jsp/" p:suffix=".jsp" />

By convention, we must declare an applicationContext.xml as well.

applicationContext.xml

?

1 2 3 4 5 6 7 8 9 10 11 12 13

<!-- Activates various annotations to be detected in bean classes -->  <context:annotation-config />     <!-- Scans the classpath for annotated components that will be auto-registered as Spring beans.   For example @Controller and @Service. Make sure to set the correct base-package-->  <context:component-scan base-package="org.krams.tutorial" />     <!-- Configures the annotation-driven Spring MVC Controller programming model.  Note that, with Spring 3.0, this tag works in Servlet MVC only!  -->  <mvc:annotation-driven />     <!-- Loads Hibernate related configuration -->  <import resource="hibernate-context.xml" />

Run the Application

We've completed the application. Our last task is to run the application and download the report.

To run the application, open your browser and enter the following URL:

http://localhost:8080/spring-poi-hibernate/krams/download/xls

This will automatically download the report document. Again, here's the final screenshot of the document:

Conclusion

That's it. We've managed to build a simple Spring MVC 3 application with reporting capabilities. We usedApache POI to generate the dynamic Excel reports and Hibernate for the ORM framework. Lastly, we used a plain Java List as the data source where the data is retrieved from a MySQL database. 

Download the project
You can access the project site at Google's Project Hosting at http://code.google.com/p/spring-poi-integration-tutorial/

You can download the project as a Maven build. Look for the spring-poi-hibernate.zip in the Download sections.

You can run the project directly using an embedded server via Maven.
For Tomcat: mvn tomcat:run
For Jetty: mvn jetty:run

If you want to learn more about Spring MVC and integration with other technologies, feel free to read my other tutorials in the Tutorials section.

출처 - http://krams915.blogspot.kr/2011/02/spring-3-apache-poi-hibernate-creating.html

/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
==================================================================== */

package org.apache.poi.ss.examples;

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.util.Map;
import java.util.HashMap;
import java.io.FileOutputStream;

/**
 * A weekly timesheet created using Apache POI.
 * Usage:
 *  TimesheetDemo -xls|xlsx
 *
 * @author Yegor Kozlov
 */
public class TimesheetDemo {
    private static final String[] titles = {
            "Person",	"ID", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun",
            "Total\nHrs", "Overtime\nHrs", "Regular\nHrs"
    };

    private static Object[][] sample_data = {
            {"Yegor Kozlov", "YK", 5.0, 8.0, 10.0, 5.0, 5.0, 7.0, 6.0},
            {"Gisella Bronzetti", "GB", 4.0, 3.0, 1.0, 3.5, null, null, 4.0},
    };

    public static void main(String[] args) throws Exception {
        Workbook wb;

        if(args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook();
        else wb = new XSSFWorkbook();

        Map<String, CellStyle> styles = createStyles(wb);

        Sheet sheet = wb.createSheet("Timesheet");
        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);

        //title row
        Row titleRow = sheet.createRow(0);
        titleRow.setHeightInPoints(45);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("Weekly Timesheet");
        titleCell.setCellStyle(styles.get("title"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));

        //header row
        Row headerRow = sheet.createRow(1);
        headerRow.setHeightInPoints(40);
        Cell headerCell;
        for (int i = 0; i < titles.length; i++) {
            headerCell = headerRow.createCell(i);
            headerCell.setCellValue(titles[i]);
            headerCell.setCellStyle(styles.get("header"));
        }

        int rownum = 2;
        for (int i = 0; i < 10; i++) {
            Row row = sheet.createRow(rownum++);
            for (int j = 0; j < titles.length; j++) {
                Cell cell = row.createCell(j);
                if(j == 9){
                    //the 10th cell contains sum over week days, e.g. SUM(C3:I3)
                    String ref = "C" +rownum+ ":I" + rownum;
                    cell.setCellFormula("SUM("+ref+")");
                    cell.setCellStyle(styles.get("formula"));
                } else if (j == 11){
                    cell.setCellFormula("J" +rownum+ "-K" + rownum);
                    cell.setCellStyle(styles.get("formula"));
                } else {
                    cell.setCellStyle(styles.get("cell"));
                }
            }
        }

        //row with totals below
        Row sumRow = sheet.createRow(rownum++);
        sumRow.setHeightInPoints(35);
        Cell cell;
        cell = sumRow.createCell(0);
        cell.setCellStyle(styles.get("formula"));
        cell = sumRow.createCell(1);
        cell.setCellValue("Total Hrs:");
        cell.setCellStyle(styles.get("formula"));

        for (int j = 2; j < 12; j++) {
            cell = sumRow.createCell(j);
            String ref = (char)('A' + j) + "3:" + (char)('A' + j) + "12";
            cell.setCellFormula("SUM(" + ref + ")");
            if(j >= 9) cell.setCellStyle(styles.get("formula_2"));
            else cell.setCellStyle(styles.get("formula"));
        }
        rownum++;
        sumRow = sheet.createRow(rownum++);
        sumRow.setHeightInPoints(25);
        cell = sumRow.createCell(0);
        cell.setCellValue("Total Regular Hours");
        cell.setCellStyle(styles.get("formula"));
        cell = sumRow.createCell(1);
        cell.setCellFormula("L13");
        cell.setCellStyle(styles.get("formula_2"));
        sumRow = sheet.createRow(rownum++);
        sumRow.setHeightInPoints(25);
        cell = sumRow.createCell(0);
        cell.setCellValue("Total Overtime Hours");
        cell.setCellStyle(styles.get("formula"));
        cell = sumRow.createCell(1);
        cell.setCellFormula("K13");
        cell.setCellStyle(styles.get("formula_2"));

        //set sample data
        for (int i = 0; i < sample_data.length; i++) {
            Row row = sheet.getRow(2 + i);
            for (int j = 0; j < sample_data[i].length; j++) {
                if(sample_data[i][j] == null) continue;

                if(sample_data[i][j] instanceof String) {
                    row.getCell(j).setCellValue((String)sample_data[i][j]);
                } else {
                    row.getCell(j).setCellValue((Double)sample_data[i][j]);
                }
            }
        }

        //finally set column widths, the width is measured in units of 1/256th of a character width
        sheet.setColumnWidth(0, 30*256); //30 characters wide
        for (int i = 2; i < 9; i++) {
            sheet.setColumnWidth(i, 6*256);  //6 characters wide
        }
        sheet.setColumnWidth(10, 10*256); //10 characters wide

        // Write the output to a file
        String file = "timesheet.xls";
        if(wb instanceof XSSFWorkbook) file += "x";
        FileOutputStream out = new FileOutputStream(file);
        wb.write(out);
        out.close();
    }

    /**
     * Create a library of cell styles
     */
    private static Map<String, CellStyle> createStyles(Workbook wb){
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style;
        Font titleFont = wb.createFont();
        titleFont.setFontHeightInPoints((short)18);
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(titleFont);
        styles.put("title", style);

        Font monthFont = wb.createFont();
        monthFont.setFontHeightInPoints((short)11);
        monthFont.setColor(IndexedColors.WHITE.getIndex());
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(monthFont);
        style.setWrapText(true);
        styles.put("header", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setWrapText(true);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        styles.put("cell", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
        styles.put("formula", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
        styles.put("formula_2", style);

        return styles;
    }
}

출처 - http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/TimesheetDemo.java

Spring MVC And Excel File Via AbstractExcelView

Posted on August 17, 2010 ,     Last modified : July 28, 2011 By mkyong

Spring MVC comes with AbstractExcelView class to export data to Excel file via Apache POI library. In this tutorial, it show the use of AbstractExcelView class in Spring MVC application to export data to Excel file for download.

1. Apache POI

Get the Apache POI library to create the excel file.

   <!-- Excel library --> 
   <dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.6</version>
   </dependency>

2. Controller

A controller class, generate dummy data for demonstration, and get the request parameter to determine which view to return. If the request parameter is equal to “EXCEL”, then return an Excel view (AbstractExcelView).

File : RevenueReportController.java

package com.mkyong.common.controller;
 
import java.util.HashMap;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.web.bind.ServletRequestUtils;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.AbstractController;
 
public class RevenueReportController extends AbstractController{
 
	@Override
	protected ModelAndView handleRequestInternal(HttpServletRequest request,
		HttpServletResponse response) throws Exception {
 
		String output =
			ServletRequestUtils.getStringParameter(request, "output");
 
		//dummy data
		Map<String,String> revenueData = new HashMap<String,String>();
		revenueData.put("Jan-2010", "$100,000,000");
		revenueData.put("Feb-2010", "$110,000,000");
		revenueData.put("Mar-2010", "$130,000,000");
		revenueData.put("Apr-2010", "$140,000,000");
		revenueData.put("May-2010", "$200,000,000");
 
		if(output ==null || "".equals(output)){
			//return normal view
			return new ModelAndView("RevenueSummary","revenueData",revenueData);
 
		}else if("EXCEL".equals(output.toUpperCase())){
			//return excel view
			return new ModelAndView("ExcelRevenueSummary","revenueData",revenueData);
 
		}else{
			//return normal view
			return new ModelAndView("RevenueSummary","revenueData",revenueData);
 
		}	
	}
}

3. AbstractExcelView

Create an Excel view by extends the AbstractExcelView class, and override the buildExcelDocument() method to populate the data to Excel file. The AbstractExcelView is using the Apache POI API to create the Excel file detail.

File : ExcelRevenueReportView.java

package com.mkyong.common.view;
 
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;
 
public class ExcelRevenueReportView extends AbstractExcelView{
 
	@Override
	protected void buildExcelDocument(Map model, HSSFWorkbook workbook,
		HttpServletRequest request, HttpServletResponse response)
		throws Exception {
 
		Map<String,String> revenueData = (Map<String,String>) model.get("revenueData");
		//create a wordsheet
		HSSFSheet sheet = workbook.createSheet("Revenue Report");
 
		HSSFRow header = sheet.createRow(0);
		header.createCell(0).setCellValue("Month");
		header.createCell(1).setCellValue("Revenue");
 
		int rowNum = 1;
		for (Map.Entry<String, String> entry : revenueData.entrySet()) {
			//create the row data
			HSSFRow row = sheet.createRow(rowNum++);
			row.createCell(0).setCellValue(entry.getKey());
			row.createCell(1).setCellValue(entry.getValue());
                }
	}
}

4. Spring Configuration

Create a XmlViewResolver for the Excel view.

<beans ...>
 
  <bean
  class="org.springframework.web.servlet.mvc.support.ControllerClassNameHandlerMapping" />
 
	<bean class="com.mkyong.common.controller.RevenueReportController" />
 
	<bean class="org.springframework.web.servlet.view.XmlViewResolver">
		<property name="location">
			<value>/WEB-INF/spring-excel-views.xml</value>
		</property>
	</bean>
 
</beans>

File : spring-excel-views.xml

   <bean id="ExcelRevenueSummary"
   	class="com.mkyong.common.view.ExcelRevenueReportView">
   </bean>

5. Demo

URL : http://localhost:8080/SpringMVC/revenuereport.htm?output=excel

It generates an Excel file for user to download.

Download Source Code

References

1. Apache POI
2. AbstractExcelView Javadoc
3. Spring MVC export data to Excel file via AbstractJExcelView
4. Spring MVC XmlViewResolver example

출처 - http://www.mkyong.com/spring-mvc/spring-mvc-export-data-to-excel-file-via-abstractexcelview/

Jakarta POI

IX. Cell을 좀더 유연하게!

1. Date타입 셀 만들기

① 소스

② 결과

③ HSSFDateFormat이 지원하는 날짜 포맷

cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); 에서 다음과 같이 포맷을 정할 수 있다 (현재시간은 2005년 3월 14일 0시 52분 17초.. 헛 화이트데이 --;)

2. Cell의 Align속성

① 소스

② 결과

3. Cell의 Border 속성

① 소스

② 결과

③ HSSFCellStyle

HSSFCellStyle에는 다음과 같은 static 멤버변수가 존재합니다

4. Cell의 색갈 채우기

① 소스

② 결과

③ HSSFColor 정리!

5. Cell 병합

① 소스

② 결과

③ Region

특정셀을 합칠 때는 HSSFSheet의 addMergedRegion(Region region)와

합칠 셀의 영역을 나타내는 Region을 사용한다.

Region region = new (int 시작ROW, short 시작COL, int 종료ROW, short 종료COL);

6. Cell에 폰트 설정하기

① 소스

② 결과

=============================================

본문서는 자유롭게 배포/복사 할수 있지만

이문서의 저자에 대한 언급을 삭제하시면 안됩니다

저자 : GoodBug (unicorn@jakartaproject.com)

최초 : http://www.jakartaproject.com 

=============================================

출처 - http://sinope.tistory.com/28

package com.java.connect.poi;

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class POIFillAndColorExample {
	public static void main(String[] args) throws IOException {
		// Create a workbook object
		Workbook workbook = new XSSFWorkbook();
		// Create sheet
		Sheet sheet = workbook.createSheet();

		// Create a row and put some cells in it.
		Row row = sheet.createRow((short) 1);

		// Aqua background
		CellStyle style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		Cell cell = row.createCell((short) 1);
		cell.setCellValue("X1");
		cell.setCellStyle(style);

		// Orange "foreground", foreground being the fill foreground not the
		// font color.
		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.AUTOMATIC.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row.createCell((short) 2);
		cell.setCellValue("X2");
		cell.setCellStyle(style);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row.createCell((short) 3);
		cell.setCellValue("X3");
		cell.setCellStyle(style);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row.createCell((short) 4);
		cell.setCellValue("X4");
		cell.setCellStyle(style);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row.createCell((short) 5);
		cell.setCellValue("X5");
		cell.setCellStyle(style);

		// Create a row and put some cells in it.
		Row row2 = sheet.createRow((short) 2);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.BROWN.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row2.createCell((short) 1);
		cell.setCellValue("X6");
		cell.setCellStyle(style);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.CORAL.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row2.createCell((short) 2);
		cell.setCellValue("X7");
		cell.setCellStyle(style);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.CORNFLOWER_BLUE.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row2.createCell((short) 3);
		cell.setCellValue("X8");
		cell.setCellStyle(style);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row2.createCell((short) 4);
		cell.setCellValue("X9");
		cell.setCellStyle(style);
		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.DARK_GREEN.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row2.createCell((short) 5);
		cell.setCellValue("X10");
		cell.setCellStyle(style);

		// Create a row and put some cells in it.
		Row row3 = sheet.createRow((short) 3);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.DARK_RED.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row3.createCell((short) 1);
		cell.setCellValue("X11");
		cell.setCellStyle(style);
		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.DARK_TEAL.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row3.createCell((short) 2);
		cell.setCellValue("X12");
		cell.setCellStyle(style);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.DARK_YELLOW.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row3.createCell((short) 3);
		cell.setCellValue("X13");
		cell.setCellStyle(style);
		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.GOLD.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row3.createCell((short) 4);
		cell.setCellValue("X14");
		cell.setCellStyle(style);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.GREEN.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row3.createCell((short) 5);
		cell.setCellValue("X15");
		cell.setCellStyle(style);

		// Create a row and put some cells in it.
		Row row4 = sheet.createRow((short) 4);
		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row4.createCell((short) 1);
		cell.setCellValue("X16");
		cell.setCellStyle(style);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row4.createCell((short) 2);
		cell.setCellValue("X17");
		cell.setCellStyle(style);
		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row4.createCell((short) 3);
		cell.setCellValue("X18");
		cell.setCellStyle(style);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row4.createCell((short) 4);
		cell.setCellValue("X19");
		cell.setCellStyle(style);
		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.INDIGO.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row4.createCell((short) 5);
		cell.setCellValue("X20");
		cell.setCellStyle(style);

		// Create a row and put some cells in it.
		Row row5 = sheet.createRow((short) 5);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.LAVENDER.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row5.createCell((short) 1);
		cell.setCellValue("X21");
		cell.setCellStyle(style);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row5.createCell((short) 2);
		cell.setCellValue("X22");
		cell.setCellStyle(style);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row5.createCell((short) 3);
		cell.setCellValue("X23");
		cell.setCellStyle(style);
		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row5.createCell((short) 4);
		cell.setCellValue("X24");
		cell.setCellStyle(style);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row5.createCell((short) 5);
		cell.setCellValue("X25");
		cell.setCellStyle(style);

		// Create a row and put some cells in it.
		Row row6 = sheet.createRow((short) 6);
		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE
				.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row6.createCell((short) 1);
		cell.setCellValue("X26");
		cell.setCellStyle(style);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row6.createCell((short) 2);
		cell.setCellValue("X27");
		cell.setCellStyle(style);
		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row6.createCell((short) 3);
		cell.setCellValue("X28");
		cell.setCellStyle(style);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row6.createCell((short) 4);
		cell.setCellValue("X29");
		cell.setCellStyle(style);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row6.createCell((short) 5);
		cell.setCellValue("X30");
		cell.setCellStyle(style);

		// Create a row and put some cells in it.
		Row row7 = sheet.createRow((short) 7);
		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.LIME.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row7.createCell((short) 1);
		cell.setCellValue("X31");
		cell.setCellStyle(style);
		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.MAROON.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row7.createCell((short) 2);
		cell.setCellValue("X32");
		cell.setCellStyle(style);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.OLIVE_GREEN.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row7.createCell((short) 3);
		cell.setCellValue("X33");
		cell.setCellStyle(style);
		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row7.createCell((short) 4);
		cell.setCellValue("X34");
		cell.setCellStyle(style);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.ORCHID.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row7.createCell((short) 5);
		cell.setCellValue("X35");
		cell.setCellStyle(style);

		// Create a row and put some cells in it.
		Row row8 = sheet.createRow((short) 8);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row8.createCell((short) 1);
		cell.setCellValue("X36");
		cell.setCellStyle(style);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.PINK.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row8.createCell((short) 2);
		cell.setCellValue("X37");
		cell.setCellStyle(style);
		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.PLUM.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row8.createCell((short) 3);
		cell.setCellValue("X38");
		cell.setCellStyle(style);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.RED.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row8.createCell((short) 4);
		cell.setCellValue("X39");
		cell.setCellStyle(style);
		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.ROSE.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row8.createCell((short) 5);
		cell.setCellValue("X40");
		cell.setCellStyle(style);

		// Create a row and put some cells in it.
		Row row9 = sheet.createRow((short) 9);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row9.createCell((short) 1);
		cell.setCellValue("X41");
		cell.setCellStyle(style);
		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row9.createCell((short) 2);
		cell.setCellValue("X42");
		cell.setCellStyle(style);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row9.createCell((short) 3);
		cell.setCellValue("X43");
		cell.setCellStyle(style);
		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.TAN.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row9.createCell((short) 4);
		cell.setCellValue("X44");
		cell.setCellStyle(style);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.TEAL.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row9.createCell((short) 5);
		cell.setCellValue("X45");
		cell.setCellStyle(style);

		// Create a row and put some cells in it.
		Row row10 = sheet.createRow((short) 10);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.TURQUOISE.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row10.createCell((short) 1);
		cell.setCellValue("X46");
		cell.setCellStyle(style);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.VIOLET.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row10.createCell((short) 2);
		cell.setCellValue("X47");
		cell.setCellStyle(style);
		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row10.createCell((short) 3);
		cell.setCellValue("X48");
		cell.setCellStyle(style);

		style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cell = row10.createCell((short) 3);
		cell.setCellValue("X49");
		cell.setCellStyle(style);

		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(
				"POIFillAndColorExample.xlsx");
		workbook.write(fileOut);
		fileOut.close();

	}
}

출처 - http://xiaohewoai.iteye.com/blog/1300817

Apache POI는 아파치 소프트웨어 재단에서 만든 라이브러리로서 마이크로소프트 오피스 파일 포맷을 순수 자바 언어로서 읽고 쓰는 기능을 제공한다. 주로워드, 엑셀, 파워포인트와 파일을 지원하며 최근의 오피스 포맷인 Office Open XML File Formats ^[1] (OOXML, 즉 xml 기반의 *.docx, *.xlsx, *.pptx 등) 이나 아웃룩, 비지오, 퍼블리셔 등으로 지원 파일 포맷을 늘려가고 있다.

역사[편집]

POI 라는 이름은 "Poor Obfuscation Implementation"의 줄임말 ^[2] 로서 기존의 마이크로소프트 오피스의 파일 포맷(OLE 2 Compund Document Format : OLE2)이 일부러 해독하기 힘들게 만들어 놓은것 같음에도 불구하고 실제로 리버스 엔지니어링되어 사용할 수 있게 되었음을 의미한다. POI 프로젝트 내부에서 사용하는 각 모듈의 이름들 또한 이와 비슷하게 유머섞인 이름들로 되어있다.

Office Open XML 지원[편집]

POI 는 3.5 버전부터 ISO/IEC 29500:2008 오피스 오픈 XML 파일 포맷을 지원한다. SourceSense라는 오픈소스 업체로부터 많은 지원을 받았는데 이 업체는 마이크로소프트와 협력하여 개발을 한 것으로 알려져 있다. ^[3]

아키텍처[편집]

Apache POI는 다음과 같은 하위 컴포넌트로 구성되어 있다.

• POIFS(Poor Obfuscation Implementation File System) : 마이크로소프트 오피스의 OLE 2 Compound document 파일 포맷을 읽고 쓰는 컴포넌트. 모든 오피스 파일 포맷은 OLE2 방식이므로 하위 모든 컴포넌트의 기반이 된다.
• HSSF(Horrible SpreadSheet Format) : 마이크로소프트 엑셀 파일포맷을 읽고 쓰는 컴포넌트로서 엑셀 97버전부터 현재까지 지원한다.
• XSSF(XML SpreadSheet Format) : 마이크로소프트 엑셀 2007부터 지원하는 오피스 오픈 XML 파일 포맷인 *.xlsx 파일을 읽고 쓰는 컴포넌트이다.
• HPSF(Horrible Property Set Format) : 오피스 파일의 문서요약 정보를 읽는데 사용되는 컴포넌트이다.
• HWPF(Horrible Word Processor Format) : 마이크로소프트 워드 97(*.doc) 파일을 읽고 쓰는데 사용되는 컴포넌트이다. 아직까지는 개발 초기단계이다.
• HSLF(Horrible Slid Layout Format) : 마이크로소프트 파워포인트 파일을 읽고 쓰는데 사용되는 컴포넌트이다.
• HDGF(Horrible DiaGram Format) : 마이크로소프트 비지오 파일을 읽는데 사용하는 컴포넌트이다.
• HPBF(Horrible PuBlisher Format) : 마이크로소프트 퍼블리셔 파일을 다루는데 사용되는 컴포넌트이다.
• HSMF(Horrible PuBlisher Format) : 마이크로소프트 아웃룩에서 사용되는 *.msg 파일을 다루는데 사용되는 컴포넌트이다.
• DDF(Dreadful Drawing Format) : 마이크로소프트 오피스에서 사용되는 이미지 파일을 읽어오는데 사용하는 컴포넌트이다.

HSSF 컴포넌트가 가장 안정적이고 많은 기능을 지원하며 다른 컴포넌트들은 사용은 가능하나 아직까지는 개발 단계이다.

같이 보기[편집]

• 오피스 오픈 XML

참고 문헌[편집]

1. 이동↑ Standard ECMA-376, Office Open XML File Formats
2. 이동↑ Sundaram, Elango (2004년 3월 22일). Excelling in Excel with Java. JavaWorld.com. 2013년 1월 22일에 확인.
3. 이동↑ Paul McDougall (2008년 3월 26일). Microsoft Eyes Open Source Components For Office 2007. InformationWeek. 2013년 1월 22일에 확인.

Apache POI (아파치 포이 또는 삐오아이)는 Apache 소프트웨어 재단 의 프로젝트에서 Word 및 Excel 과 같은 Microsoft Office 형식의 파일 을 읽고 쓸 수있는 100 % Java 라이브러리 로 제공되고있다.

명칭의 유래 [ 편집 ]

POI라는 명칭은 Microsoft Office 파일 형식을 리버스 엔지니어링 했을 때, 그 형식이 의도적으로, 게다가 어중간하게 이해하기 어렵게되어 있었기 때문에, 풍자를 담아 " Poor Obfuscation Implementation "(저질품 읽을 구현) 라고 불렀지 만 약어 로 발단있다. 이처럼 유머러스 한 정식 명칭을 적용 방법은 일단 여러가지 하위 프로젝트로 볼 수 있었지만, 유머를 부적절하다고 파악 비즈니스계 진출을 의식 해 공식 웹 페이지에서 삭제되어있다. 또 다른 유래는, 하와이의 진미 Poi 에서왔다. 하와이 사람들이 이것을 계속 먹으면 거인이된다고 말해지고있다.

Office Open XML 지원 [ 편집 ]

버전 3.5에서 ISO / IEC 29500 Office Open XML 형식의 파일을 지원하고있다.

하위 구성 요소 [ 편집 ]

Apache POI 프로젝트는 다음과 같은 하위 구성 요소로 구성된다.

• POIFS ( Poor Obfuscation Implementation File System ) - Microsoft OLE 2 복합 문서 포맷을 읽고 쓰는 구성 요소. 모든 Microsoft Office 파일은 OLE 2 파일이기 때문에 POIFS 다른 POI 구성 요소의 기초가되고있다. 따라서 POIFS은 명시 적으로 POI로 작성된 모듈 이외에도 다양한 종류의 파일을 읽는 사용되고있다.
• HSSF ( Horrible SpreadSheet Format ) - Microsoft Excel (XLS) 형식의 파일을 취급한다. Excel 97 이상 파일을 읽고 쓸 수있다. 이 파일 형식은 BIFF 8 포맷으로 알려져있다. 필터 및보기를 포함 시트를 열 수 없습니다.
• XSSF ( XML SpreadSheet Format ) - Office Open XML Workbook 형식의 파일을 취급한다. Excel 2007에서 채택 된 OOXML 형식의 파일을 읽고 쓸 수있다.
• HWPF ( Horrible Word Processor Format ) - Microsoft Word (DOC) 형식의 파일을 취급한다. Word 97 이상 파일을 읽고 쓸 수있다. Word 95 이전 형식도 제한적으로 읽을 수있다.
• XWPF ( XML Word Processor Format ) - Office Open XML Document 형식의 파일을 취급한다. Word 2007에서 채택 된 OOXML 형식의 파일을 읽고 쓸 수있다.
• HSLF ( Horrible Slide Layout Format ) - Microsoft PowerPoint (PPT) 형식의 파일을 취급한다. PowerPoint 97 이상 파일을 읽고 쓸 수있다.
• XSLF ( XML Slide Layout Format ) - Office Open XML Presentation 형식의 파일을 취급한다. PowerPoint 2007에서 채택 된 OOXML 형식의 파일을 읽고 쓸 수있다.
• HPSF ( Horrible Property Set Format ) - Microsoft Office의 문서 요약보기 구성 요소. 문서 요약은 주로 Office 응용 프로그램의 메뉴 바에서 "파일"→ "속성"에 보이는 정보 것.
• HDGF ( Horrible DiaGram Format ) - Microsoft Visio 형식의 파일을 취급한다. 현재는 읽기 전용 가능.
• HPBF ( Horrible PuBlisher Format ) - Microsoft Publisher 형식의 파일을 취급한다. 현재는 개발 초기 단계에 있고, 파일의 일부를 읽을 제한됩니다.
• HSMF ( Horrible Stupid Mail Format ) - Microsoft Outlook (MSG) 형식의 파일을 취급한다. 현재는 파일을 읽고 쓸 수 있습니다.

POI 라이브러리는 Ruby 의 확장으로도 볼 수있다.

관련 항목 [ 편집 ]

• Apache Jakarta Project
• Microsoft Excel
• Microsoft Word
• Microsoft PowerPoint
• Microsoft Outlook
• Microsoft Visio
• Microsoft Publisher
• Office Open XML

출처 - http://ko.wikipedia.org/wiki/Apache_POI#cite_note-1

CSS는 웹사이트 디자인에 사용하는 도구다. 개념도 복잡하지 않고 문법도 간단하기 때문에 Box 모델이나 플로팅(floating) 같은 몇 가지 기본 개념만 익히고 나면 약간의 코딩 만으로 웹사이트에 멋진 비주얼 요소를 집어넣을 수가 있다. 그렇지만 매번 웹사이트를 만들 때마다 일일이 처음부터 CSS 작업을 하는 일은 경우에 따라서는 제법 성가시고 품이 많이 가는 일이기도 하다. 특히 IE의 비정상적인 작동을 바로잡기 위한 핵(Hack) 까지 고려한다면 더욱 그렇다. CSS 개발의 생산성을 높여주는 몇 가지 도구들에 대해 알아보자.

1. CSS 프레임워크

CSS 프레임워크(framework)는 미리 만들어 놓은 일종의 CSS 모듈이다. CSS 프레임워크를 사용하면 페이지의 기본 레이아웃에서부터 타이포그라피, 폼 디자인, 버튼 등 많은 부분의 디자인을 "날로 먹을 수"가 있다. 물론 모든 "프레임워크"가 그러하듯, CSS 프레임워크도 언제나 "최선"의 선택인 것은 아니며, 어떤 디자이너나 개발자들은 CSS 프레임워크 자체에 거부감을 가지기도 한다. 그렇지만 CSS 프레임워크가 개발 생산성을 높여줄 수 있다는 점에서는 이론의 여지가 없다. 그만큼 그 종류도 다양하기 때문에 오히려 어떤 것을 선택하느냐가 더 고민이 되는 경우가 많지만, 몇 가지 대표적인 것들만 소개하면 다음과 같다.

• 960 Grid System
• Blueprint
• Elastic CSS Framework
• YUI Grid CSS
• Less Framework

이들 중 대부분의 CSS 프레임워크는 소위 "그리드(grid) 디자인"에 기반하지만, 자바스크립트와 함께 사용하여 UI 효과를 내는 것들도 있고, 또 최근에는 HTML5/CSS3를 이용한 CSS 프레임워크들이 많이 등장하고 있다.

2. SASS와 LESS

SASS와 LESS는 일종의 CSS 전처리기(pre-processor)다. 조금 더 간단하고 구조화된 문법으로 코드를 작성하면 자동으로 CSS 파일을 생성해 주기 때문에 CSS 개발의 생산성을 폰이는 데에 꼭 필요한 도구이기도 하다. 특히 믹스인(Mixin)이라는 개념은 일종의 '모듈'을 만드는 것으로서, CSS 코드 재활용을 높이고 생산성을 높여주는 좋은 개념이다. SASS는 루비(ruby) 언어로 작성되었고, 그래서 주로 루비/레일스 프로젝트에서 많이 사용되지만 반드시 그래야 하는 건 아니다.

LESS(앞서 소개한 Less Framework과는 별개)도 SASS와 개념은 유사하지만, CSS파일의 생성이 자바스크립트 파서(parser)를 통해 클라이언트 측에서 이루어진다는 점에서, 서버측에서 컴파일이 이루어지는 SASS와 구별된다. 물론 LESS는 Node.js 환경에서도 작동한다. 이들 도구의 홈페이지는 각각 다음과 같다.

• SASS http://sass-lang.com/
• LESS http://lesscss.org/

3. Compass

마지막으로 소개할 도구는 일종의 CSS 개발 자동화 도구인 Compass다. 이 도구를 이용하면 앞서 소개한 CSS Framework를 명령행에서 바로 설치할 수도 있고, SASS 컴파일을 자동화 시킬 수도 있으며, 무엇보다도 SASS와 결합하여 CSS의 구성요소를 모듈단위로 쪼개서 SASS 모듈의 재활용성을 높이는 역할을 하는, 일종의 메타(meta) CSS 프레임워크라 할 수 있다. Compass의 사용법은 Compass 사이트에 자세히 소개되어 있으니 참조하면 된다.

출처 - http://usefulparadigm.com/2011/03/18/productive-css-development-tools/