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

  • 基于 apache 的 commons-net-3.3.jar 的 ftp java代码上传下载文件 2014-12-15

    package com.genomics.ib.item.control; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.InputStream; import java.io.OutputStream; import org.apache.commons.net.ftp.FTPClient; import org.apache.common

  • Java日志服务入门系列教程--(2)Apache log4j入门 2012-03-20

    1. 概述 目前log4j有三个发展分支,一个是1.2的稳定版,一个是已经停止继续发展的1.3版本,另一个就是实验阶段的2.0版本.Apache log4j官网上称,在一台载有运行在800MHz的AMD Duron CPU并使用JDK 1.3.1 上的计算机中,log4j 1.2 只需要5纳秒就可以完成一个日志语句是应该作为日志输出还是不输出的决定. 2. 下载log4j 1.2 API library 从人人网的实验室下载: http://labs.renren.com/apache-mirr

  • 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

  • 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

  • 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

iOS 开发

Android 开发

Python 开发

JAVA 开发

开发语言

PHP 开发

Ruby 开发

搜索

前端开发

数据库

开发工具

开放平台

Javascript 开发

.NET 开发

云计算

服务器

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

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

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