Java to read Excel file

sponsored links
Recently used to import data via Excel to the database more, order a simple and practical to read Excel file through jxl.jar category:

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;

import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;

import org.apache.log4j.Logger;

public class ExcelTool {

    private static final String ENCODE_WHEN_READING = "GBK";
    
    public static final Logger log4gm = LoggerManager.getLog();

    public ExcelTool() {
        
    }
    
    public Workbook getExcel(InputStream is) {
        Workbook wb = null;
        try {
            wb = Workbook.getWorkbook(is);
        } catch (Exception e) {
            log4gm.error(e.toString());
        }
        
        return wb;
    }
    
    public Workbook getExcel(String path) {
        Workbook wb = null;
        try {
            InputStream is = new FileInputStream(new File(path));
            wb = Workbook.getWorkbook(is);
        } catch (Exception e) {
            log4gm.error(e.toString());
        }
        return wb;
    }
    
    public Sheet[] getEachSheet(Workbook wb) {
        if(null!=wb) {
            return wb.getSheets();
        }else {
            return null;
        }
    }
    
    public int findColIndex(Sheet st,String strHeadColContents) {
        int intIndex = 0;

        for (int i = 0; i < st.getColumns(); i++) {
            String strCon = st.getCell(i,0).getContents().toString().trim();
            if(strHeadColContents.equals(strCon)) {
                intIndex = i;
            }
        }
        
        return intIndex;
    }
    
    public String getValueAt(Sheet st, int rowIndex, int colIndex) {
        String strValueAt = "";
        if(null != st.getCell(colIndex,rowIndex)) {
            Cell cellUnit = st.getCell(colIndex,rowIndex);
            if(null != cellUnit.getContents().trim()
                    && !"".equals(cellUnit.getContents().toString().trim())) {
                
                if(cellUnit.getType()==CellType.NUMBER ||
                        cellUnit.getType()==CellType.NUMBER_FORMULA) {
                    NumberCell nc=(NumberCell)cellUnit;
                    strValueAt = ""+nc.getValue();
                }else if(cellUnit.getType()==CellType.DATE ||
                        cellUnit.getType()==CellType.DATE_FORMULA){
                    DateCell dt =(DateCell)cellUnit;
                    strValueAt = new SimpleDateFormat("yyyy-MM-dd").format(dt);
                }else {
                    try {
                        String temp = cellUnit.getContents().trim();
                        strValueAt = new String(temp.getBytes(),ENCODE_WHEN_READING).trim();

                    } catch (Exception e) {
                        log4gm.error(e.toString());
                    }
                }
            }
        }
        
        return strValueAt;
    }
    
    public String[][] getTableInSheet(Sheet st,int rowStart,int columnStart,int rowMinus) {
        String[][] strContents = null;
    
        int intActualRows = st.getRows()-rowStart-rowMinus;
        int intActualCols = st.getColumns()-columnStart;
        
        strContents = new String[intActualRows][intActualCols];
        for (int i = 0; i < intActualRows; i++) {
            for (int j = 0; j < intActualCols; j++) {
                strContents[i][j] = getValueAt(st,(i+rowStart),(j+columnStart));
            }
        }
        return strContents;
    }
    
}
  • del.icio.us
  • StumbleUpon
  • Digg
  • TwitThis
  • Mixx
  • Technorati
  • Facebook
  • NewsVine
  • Reddit
  • Google
  • LinkedIn
  • YahooMyWeb

Related Posts of Java to read Excel file

  • In the servlet use Bean

    According to Sun's definition, JavaBean is a reusable software components. In fact JavaBean is a Java class, through the package into a property and methods of treatment of a function or a business object, referred to as bean. Because JavaBean is ...

  • hibernate generic generic DAO

    package org.lzpeng.dao; import java.io.Serializable; import java.util.List; import org.hibernate.Criteria; import org.hibernate.Query; import org.hibernate.criterion.Criterion; import org.springside.modules.orm.hibernate.Page; /** * * @version 2009-1-10 *

  • Servlet brief introduction

    Servlet brief introduction: Servlet is a small application server Are used to complete the B / S architecture, the client requests the response to treatment Platform independence, performance, able to run thread Servlet API for Servlet provides the s ...

  • can not be represented as java.sql.Timestamp

    Development of procedures for the use of hibernate when, some time there is no need to fill in the fields, but after the hibernate query time reported "Java.sql.SQLException: Value'0000-00-00 'can not be represented as java.sql.Timestamp ...

  • First Hibernate Example

    Curd a simple example. Source does not contain the dependent libraries, or playing too much of the package. PO object Note: One must have the default constructor 2 non-final modified. Otherwise useless lazy loading. UserDAOImpl category code, and other co

  • Struts2 + hibernate + spring problem user log in

    dao layer services layer action jsp <tr> <td align="center"> <b> user name: </ b> </ td> <td> <s: textfield name = "czyNumber" cssClass = "textstyle" theme = "simple" size = &q

blog comments powered by Disqus
Recent
Recent Entries
Tag Cloud
Random Entries