Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Linux \ Apache POI Excel Document Processing     - CentOS 6.5 set under Oracle 12c at startup (Database)

- How do I use Linux development environment (Linux)

- namespace mechanism Linux kernel analysis (Linux)

- Scope of variables in C # (Programming)

- Articles do not resolve after opening under Ubuntu WordPress setting a fixed link (Server)

- To install the Git and Github under Ubuntu (Linux)

- ls command: 15 Level Linux interview question (Linux)

- Linux kernel VLAN study notes (Programming)

- KVM add virtual disks (Linux)

- CentOS 6.5 makes the LAN http source (Linux)

- Binder began to talk about inter-process communication mechanism from Android AIDL (Programming)

- Android and OpenCV2.4.4 build an image processing framework (2013 edition) (Linux)

- Ubuntu 14.04 to install Blender 2.71 (Linux)

- Oracle 11g forget approach SYS and SYSTEM password (Database)

- How to build a custom exclusive Ubuntu Live CD (Linux)

- To compile install and test Swift under Linux (Linux)

- JavaScript Advanced Programming notes event capture and event bubbling (Programming)

- Oracle 11g upgrade PSU detailed steps (Database)

- Security of data to create a safe .mdb database (Linux)

- ThinkPad X220 Ubuntu 14.10 installed on fingerprint recognition (Linux)

 
         
  Apache POI Excel Document Processing
     
  Add Date : 2018-11-21      
         
         
         
  Currently popular treatment excel document, there are two general ways, namely POI and JXL. Heavyweight POI advantages and disadvantages: suitable for excel document details a relatively professional requirements, such as using formulas, macros and other advanced features; drawback is that the operation is relatively cumbersome, non-pure java prepared rack package, cross-platform needs to be strengthened. Lightweight JXL disadvantages: Jxl pure javaAPI, excellent cross-platform, the operation is relatively simple; drawback is that does not support some of the advanced features excel document, but to meet the daily needs. Here we introduce the basic use of the POI.

1. First, import the relevant frame package

Also note that this version of the JDK you what development projects is to download different versions according to the corresponding POI JDK version.

2. Operation ExcelReader helper class can handle xls and xlsx files, readExcelTitle (InputStream is) Reads the file header, which is the first line of the file, readExcelContent (InputStream is) method to read the contents of the file:

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.Map;
  
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.ss.usermodel.WorkbookFactory;
  
/ **
 * Operation Excel spreadsheet functionality class
 * /
public class ExcelReader {
    private static DecimalFormat df = new DecimalFormat ( "0");
    private final static Logger log = Logger.getLogger (ExcelReader.class);
    private Workbook wb = null;
    private Sheet sheet = null;
    private Row row = null;
  
    / **
    * Read the contents of an Excel spreadsheet header
    * @param InputStream
    * @return String array header content
    * @throws IOException
    * /
    public String [] readExcelTitle (InputStream is) throws Exception {
        try {
            wb = WorkbookFactory.create (is);
        } Catch (IOException e) {
            log.error ( "read the contents of Excel spreadsheet header abnormal", e);
            throw e;
        }
        sheet = wb.getSheetAt (0);
        row = sheet.getRow (0);
        The total number of columns // heading
        int colNum = row.getPhysicalNumberOfCells ();
        String [] title = new String [colNum];
        for (int i = 0; i             title [i] = getCellFormatValue (row.getCell (i));
        }
        log.info ( "read the contents of the complete Excel table header");
        return title;
    }
  
    / **
    * Read Excel data content
    * @param InputStream
    * @return Map Map object that contains the cell data content
    * @throws IOException
    * /
    public Map readExcelContent (InputStream is) throws Exception {
        Map content = new LinkedHashMap ();
        String str = "";
        try {
            wb = WorkbookFactory.create (is);
        } Catch (IOException e) {
            log.error ( "read Excel data content", e);
            throw e;
        }
        sheet = wb.getSheetAt (0);
        // Get the total number of rows
        int rowNum = sheet.getLastRowNum ();
        row = sheet.getRow (0);
        int colNum = row.getPhysicalNumberOfCells ();
        // Body content should start from the second row, first row header title
        for (int i = 1; i <= rowNum; i ++) {
            row = sheet.getRow (i);
            int j = 0;
            while (j                 // The data contents of each cell with a "-" separated, String class replace when needed for future use () method to restore data
                // You can also set the data to each cell in a javabean attribute, then you need to create a javabean
                // Str + = getStringCellValue (row.getCell ((short) j)). Trim () +
                // "-";
                if (row! = null) {
                    . Str + = getCellFormatValue (row.getCell (j)) trim () + ",";
                } Else {
                    str + = "" + ",";
                }
                j ++;
            }
            content.put (i, str.substring (0, str.length () - 1));
            str = "";
        }
        log.info ( "read Excel data content complete");
        return content;
    }
  
    / **
    * Set the type of data based on Cell
    * @param Cell
    * @return
    * /
    private String getCellFormatValue (Cell cell) {
        String cellvalue = "";
        if (cell! = null) {
            Analyzing the current // Type Cell
            switch (cell.getCellType ()) {
            // If the current Type Cell is NUMERIC
            case Cell.CELL_TYPE_NUMERIC:
            case Cell.CELL_TYPE_FORMULA: {
                // Determine whether the current cell to Date
                if (DateUtil.isCellDateFormatted (cell)) {
                    // If the Date type is converted to a Data Format
  
                    // Method 1: The data format is like this with every minute of the time: 2015-12-18 0:00:00
                    // Cellvalue = cell.getDateCellValue () toLocaleString ().;
  
                    // Method 2: data format is like this when every minute without tape: 2011-10-12
                    Date date = cell.getDateCellValue ();
                    SimpleDateFormat sdf = new SimpleDateFormat ( "yyyy-MM-dd");
                    cellvalue = sdf.format (date);
  
                }
                // If it is a pure digital
                else {
                    // Get the current value of the Cell
                    cellvalue = String.valueOf (df.format (cell.getNumericCellValue ()));
                }
                break;
            }
            // If the current Type Cell is STRIN
            case Cell.CELL_TYPE_STRING:
                // Get the current Cell strings
                cellvalue = cell.getRichStringCellValue () getString ().;
                break;
            // Default value of Cell
            default:
                cellvalue = "";
            }
        } Else {
            cellvalue = "";
        }
        return cellvalue;
  
    }
}

Simple example code, the actual use of exception handling mechanism should be added:
12345678910111213 FileInputStream is = new FileInputStream (file);
    ExcelReader excelReader = new ExcelReader ();
    String [] title = excelReader.readExcelTitle (is); // read the file title (non-file name, but the first line of the file)
    for (String str: title) {
    System.out.println (str);
    }
    is.close ();
    is = new FileInputStream (file);
    Map map = excelReader.readExcelContent (is); // read the contents of the file
    for (int i = 1; i <= map.size (); i ++) {
        System.out.println (map.get (i));
    }
    is.close ();
     
         
         
         
  More:      
 
- Linux, modify / retrieve the root password (Linux)
- To install Python-Pip and Fabric under CentOS / Ubuntu (Linux)
- Linux System Getting Started Tutorial: Installing Brother printer in Linux (Linux)
- MySQL Study of --Percona Server 5.5 Upgrade 5.6 (Database)
- Oracle multi-user concurrency and transaction processing (Database)
- HTML5 Fundamentals study notes (Programming)
- Postfix mail service system principle and configuration (Linux)
- The maximum subsequence algorithm and optimization problems (Programming)
- Configuring Allatori code confusion when developing general Java applications in NetBeans (Programming)
- 11 you Linux Terminal Command (Linux)
- Oracle 12c detailing the new features (Database)
- CentOS modify yum update source (Linux)
- To install the mail client terminal Evolution 3.13.2 under Ubuntu 14.04 (Linux)
- 5 steps to help you become a good Docker contributors (Linux)
- CentOS 6.5_x64 install Oracle 11g R2 (Database)
- Ubuntu 14.04 / Linux Mint 17 How to install the MintMenu 5.5.2 menu (Linux)
- Check with Hello World Docker installation (Server)
- How to manage the time and date at systemd Linux systems (Linux)
- Different between Linux file path and the windows (Linux)
- Linux use glibc2 library and crypt () function to generate the user password (Programming)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.