Java to read Excel file

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;
    }

}
分类:Java 时间:2010-05-11 人气:371
分享到:
blog comments powered by Disqus

相关文章

  • java xml parsing and import database (dom4j) 2010-07-11

    java xml parsing and import database (dom4j) import java.io.File; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.Iterator; import java.util.List; import org.dom4j.Document; import org.dom4j.

  • JAVA interview questions doubts Series (II) - in the end to create a few String objects? 2010-12-26

    We first look at a piece of code: 1. String str=new String("abc"); 2. String str=new String("abc"); 3. Immediately after this code is often the problem, and that is what this line of code to create a few String objects? This question i

  • java.util.Date is converted to String format java.text.SimpleDateFormat 2010-12-29

    SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); String today = df.format(new Date()); The Doc in java.text.SimpleDateFormat format specification in detail

  • Java Log Management: Logger.getLogger () and LogFactory.getLog () difference (Detailed Log4j) 2011-07-29

    First, Logger.getLogger () and LogFactory.getLog () difference 1.Logger.getLogger () is a way to use log4j logging; 2.LogFactory.getLog () is from the apache common-logging package. common-logging components: Jakarta Commons Logging (JCL) provides a

  • Get the current path in Java 2010-06-25

    Java files are also moved the function source code (the directory mobile) mentioned in the write batch is often used as a function of: the implementation of documents to obtain the current path. This is actually very simple, with the following code f

  • Get file path in java 2010-07-26

    On the absolute path and relative path: Absolute path is your home page file or directory on your hard disk real path, (URL and the physical path) example: C: xyz est.txt represents the absolute path of the file test.txt. http://www.sun.com/index.htm

  • C#中把字符串String转换为整型Int的小例子 2014-06-02

    这篇文章主要介绍了C#中把字符串String转换为整型Int的小例子,本文使用TryParse方法实现转换,需要的朋友可以参考下 本文介绍如何在使用C#开发程序时,将一个字符串String变量的值转换为一个整型Int变量. 比如,我们在C#中定义一个字符串变量,用它来获取一个xml中的值.小编这里并不是故意要用一个字符串去获取xml节点的值,而是使用InnerText的方式获取的值必须是字符串String类型的. string tmpValue = ""; tmpValue = xml

  • Python's import xx and from xx import * 2010-09-10

    Both look the same. However, there is a difference. Difference is that the former use of all things imported with the module name be limited, while the latter do not. For example today, a simple question that bothers me, import User usermapper = mapp

  • java telnet to connect the host implementation of the shell to return a string garbled solution 2008-07-02

    String newString = new String(oldString.getBytes("iso8859-1"), "GBK");

  • About org.apache.jasper.JasperException: The absolute uri: http://java.sun.com/jsp/j 2009-05-24

    This problem usually occurs because the jar (jstl.jar, and standard.jar) caused problems: The first is: Eclipse project is to import JSTL.jar and standard.jar Second: Modify the web.xml, add the following statement: <jsp-config> <taglib> <t

iOS 开发

Android 开发

Python 开发

JAVA 开发

开发语言

PHP 开发

Ruby 开发

搜索

前端开发

数据库

开发工具

开放平台

Javascript 开发

.NET 开发

云计算

服务器

Copyright (C) codeweblog.com, All Rights Reserved.

CodeWeblog.com 版权所有 黔ICP备15002463号-1

processed in 0.393 (s). 12 q(s)