Jxl导入Excel数据

package com.pjb.sys.util;

import java.io.File;
import java.io.InputStream;
import java.util.ArrayList;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * 提供对XLS格式类型的文件进行处理
 *
 * @author Administrator
 *
 */
public class XlsTools {
    private Workbook readBook;

    private int currIndex;

    private ArrayList<String> alLineContent;

    private Cell cell;

    private int firstDataCount;

    private boolean isFirst;

    private Sheet sheet;

    private WritableWorkbook writeBook;

    private WritableSheet writeSheet;

    private Label label;

    /**
     * 初始化xls文件的读取参数
     *
     * @param filePath
     *            xls文件路径
     *
     */
    public void read(String filePath) throws Exception {
        try {
            // 创建xls工作表对象
            readBook = Workbook.getWorkbook(new File(filePath));
            // 只读取第一个工作表中的内容
            sheet = readBook.getSheet(0);
            currIndex = 0;
            isFirst = true;
        } catch (Exception e) {
            throw new Exception(e);
        }
    }

    /**
     *
     * @param is
     *            初始化xls文件的读取参数
     * @throws Exception
     */

    public void read(InputStream is) throws Exception {
        try {
            // 创建xls工作表对象
            readBook = Workbook.getWorkbook(is);
            // 只读取第一个工作表中的内容
            sheet = readBook.getSheet(0);
            currIndex = 0;
            isFirst = true;
        } catch (Exception e) {
            throw new Exception(e);
        }
    }

    /**
     * 读取一行xls文件中的数据
     *
     * @return 包含数据的String列表
     */
    public ArrayList<String> readLine() {
        alLineContent = new ArrayList<String>();
        int i = 0;
        String content = null;
        while (true) {
            if (!isFirst && i >= firstDataCount)
                break;
            try {
                // 读取一个单元格的数据
                cell = sheet.getCell(i, currIndex);
                i++;
            } catch (Exception e) {
                // 没有数据可读取
                if (i == 0)
                    return null;
                // 读取首行
                if (isFirst) {
                    firstDataCount = i;
                    isFirst = false;
                    break;
                } else
                    content = "";
            }
            content = cell.getContents();
            // 首行存在空值时认为提取数据完毕
            if (isFirst && "".equals(content)) {
                firstDataCount = i - 1;
                isFirst = false;
                break;
            }
            alLineContent.add(content);
        }
        currIndex++;
        return alLineContent;
    }

    /**
     * 读取xls文件中的所有可读取数据
     */
    public ArrayList<ArrayList<String>> readAll() {
        ArrayList<ArrayList<String>> alAllData = new ArrayList<ArrayList<String>>();
        ArrayList<String> data = null;
        while (true) {
            data = this.readLine();
            if (data == null)
                break;
            alAllData.add(data);
        }
        return alAllData;
    }

    public void closeRead() {
        readBook.close();
    }

    /**
     * 创建一个xls文件并初始化写入参数
     *
     * @param filePath
     *            xls文件路径
     */
    public void write(String filePath) throws Exception {
        try {
            // 打开.xls文件
            writeBook = Workbook.createWorkbook(new File(filePath));
            // 创建一个工作表
            writeSheet = writeBook.createSheet("Sheet1", 0);
            currIndex = 0;
        } catch (Exception e) {
            throw new Exception(e);
        }
    }

    /**
     * 将一条数据写入xls文件中
     *
     * @param dataLine
     *            需要写入的数据集合
     */
    public void writeLine(ArrayList<String> dataLine) throws Exception {
        try {
            for (int i = 0; i < dataLine.size(); i++) {
                label = new Label(i, currIndex, dataLine.get(i));
                writeSheet.addCell(label);
            }
            currIndex++;
        } catch (Exception e) {
            throw new Exception(e);
        }
    }

    /**
     * 将所有数据写入xls文件
     *
     * @param data需要写入的数据
     */
    public void writeAll(ArrayList<ArrayList<String>> data) throws Exception {
        for (int i = 0; i < data.size(); i++) {
            this.writeLine(data.get(i));
        }
    }

    public void closeWrite() throws Exception {
        try {
            // 将值写到文件中
            writeBook.write();
            writeBook.close();
        } catch (Exception e) {
            throw new Exception(e);
        }
    }
}

package com.pjb.sys.util;

import java.io.File;
import java.io.InputStream;
import java.util.ArrayList;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * 提供对XLS格式类型的文件进行处理
 * 
 * @author Administrator
 * 
 */
public class XlsTools {
	private Workbook readBook;

	private int currIndex;

	private ArrayList<String> alLineContent;

	private Cell cell;

	private int firstDataCount;

	private boolean isFirst;

	private Sheet sheet;

	private WritableWorkbook writeBook;

	private WritableSheet writeSheet;

	private Label label;

	/**
	 * 初始化xls文件的读取参数
	 * 
	 * @param filePath
	 *            xls文件路径
	 * 
	 */
	public void read(String filePath) throws Exception {
		try {
			// 创建xls工作表对象
			readBook = Workbook.getWorkbook(new File(filePath));
			// 只读取第一个工作表中的内容
			sheet = readBook.getSheet(0);
			currIndex = 0;
			isFirst = true;
		} catch (Exception e) {
			throw new Exception(e);
		}
	}

	/**
	 * 
	 * @param is
	 *            初始化xls文件的读取参数
	 * @throws Exception
	 */

	public void read(InputStream is) throws Exception {
		try {
			// 创建xls工作表对象
			readBook = Workbook.getWorkbook(is);
			// 只读取第一个工作表中的内容
			sheet = readBook.getSheet(0);
			currIndex = 0;
			isFirst = true;
		} catch (Exception e) {
			throw new Exception(e);
		}
	}

	/**
	 * 读取一行xls文件中的数据
	 * 
	 * @return 包含数据的String列表
	 */
	public ArrayList<String> readLine() {
		alLineContent = new ArrayList<String>();
		int i = 0;
		String content = null;
		while (true) {
			if (!isFirst && i >= firstDataCount)
				break;
			try {
				// 读取一个单元格的数据
				cell = sheet.getCell(i, currIndex);
				i++;
			} catch (Exception e) {
				// 没有数据可读取
				if (i == 0)
					return null;
				// 读取首行
				if (isFirst) {
					firstDataCount = i;
					isFirst = false;
					break;
				} else
					content = "";
			}
			content = cell.getContents();
			// 首行存在空值时认为提取数据完毕
			if (isFirst && "".equals(content)) {
				firstDataCount = i - 1;
				isFirst = false;
				break;
			}
			alLineContent.add(content);
		}
		currIndex++;
		return alLineContent;
	}

	/**
	 * 读取xls文件中的所有可读取数据
	 */
	public ArrayList<ArrayList<String>> readAll() {
		ArrayList<ArrayList<String>> alAllData = new ArrayList<ArrayList<String>>();
		ArrayList<String> data = null;
		while (true) {
			data = this.readLine();
			if (data == null)
				break;
			alAllData.add(data);
		}
		return alAllData;
	}

	public void closeRead() {
		readBook.close();
	}

	/**
	 * 创建一个xls文件并初始化写入参数
	 * 
	 * @param filePath
	 *            xls文件路径
	 */
	public void write(String filePath) throws Exception {
		try {
			// 打开.xls文件
			writeBook = Workbook.createWorkbook(new File(filePath));
			// 创建一个工作表
			writeSheet = writeBook.createSheet("Sheet1", 0);
			currIndex = 0;
		} catch (Exception e) {
			throw new Exception(e);
		}
	}

	/**
	 * 将一条数据写入xls文件中
	 * 
	 * @param dataLine
	 *            需要写入的数据集合
	 */
	public void writeLine(ArrayList<String> dataLine) throws Exception {
		try {
			for (int i = 0; i < dataLine.size(); i++) {
				label = new Label(i, currIndex, dataLine.get(i));
				writeSheet.addCell(label);
			}
			currIndex++;
		} catch (Exception e) {
			throw new Exception(e);
		}
	}

	/**
	 * 将所有数据写入xls文件
	 * 
	 * @param data需要写入的数据
	 */
	public void writeAll(ArrayList<ArrayList<String>> data) throws Exception {
		for (int i = 0; i < data.size(); i++) {
			this.writeLine(data.get(i));
		}
	}

	public void closeWrite() throws Exception {
		try {
			// 将值写到文件中
			writeBook.write();
			writeBook.close();
		} catch (Exception e) {
			throw new Exception(e);
		}
	}
}

 

接下来，我们以Struts1.2的FormFile做文件上传。编写如下Form

 

package com.pjb.struts.form;

import org.apache.struts.action.ActionForm;
import org.apache.struts.upload.FormFile;

@SuppressWarnings("serial")
public class UploadForm extends ActionForm {

    private FormFile formFile;

    public FormFile getFormFile() {
        return formFile;
    }

    public void setFormFile(FormFile formFile) {
        this.formFile = formFile;
    }
}

package com.pjb.struts.form;

import org.apache.struts.action.ActionForm;
import org.apache.struts.upload.FormFile;

@SuppressWarnings("serial")
public class UploadForm extends ActionForm {

	private FormFile formFile;

	public FormFile getFormFile() {
		return formFile;
	}

	public void setFormFile(FormFile formFile) {
		this.formFile = formFile;
	}
}

 

 

 JSP页面

<%@ page language="java" contentType="text/html; charset=utf-8"%>
<%@ include file="/page/importTag.jsp"%>
<html>
    <head>
        <%
            String str = "<script type='text/javascript'>" + "\n alert('操作成功');" + "\n</script>";
            String reuslt = (String) request.getAttribute("result");
            if ("1".equals(reuslt))
                out.println(str);
        %>
    </head>
    <body>
        <div align="center">
            <html:form action="/ExcelImport.do?method=upload" method="post" enctype="multipart/form-data">
                选择导入文件<html:file property="formFile" onkeydown="javascrpit:return false;" />
                <input type="submit" value="导入数据" />
            </html:form>
        </div>
    </body>
</html>

<%@ page language="java" contentType="text/html; charset=utf-8"%>
<%@ include file="/page/importTag.jsp"%>
<html>
	<head>
		<%
			String str = "<script type='text/javascript'>" + "\n alert('操作成功');" + "\n</script>";
			String reuslt = (String) request.getAttribute("result");
			if ("1".equals(reuslt))
				out.println(str);
		%>
	</head>
	<body>
		<div align="center">
			<html:form action="/ExcelImport.do?method=upload" method="post" enctype="multipart/form-data">
				选择导入文件<html:file property="formFile" onkeydown="javascrpit:return false;" />
				<input type="submit" value="导入数据" />
			</html:form>
		</div>
	</body>
</html>

 

 

Struts Action代码如下。

 

/**
 * 读XLS数据
 *
 * @param mapping
 * @param form
 * @param request
 * @param response
 * @return ActionForward
 * @throws Exception
 */
public ActionForward readXls(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response)
        throws Exception {
    try {
        UploadForm uploadForm = (UploadForm) form;
        FormFile file = uploadForm.getFormFile();
        InputStream is = file.getInputStream();
        XlsTools xlsTools = new XlsTools();
        xlsTools.read(is);
        //读Xls行所有数据并封装
        ArrayList<ArrayList<String>> listAll = xlsTools.readAll();
        xlsTools.closeRead();
        List<UserBean> userList = new ArrayList<UserBean>();
        for (int i = 1; i < listAll.size(); i++) {
            ArrayList<String> aList = listAll.get(i);
            UserBean user = new UserBean();
            user.setUserName(aList.get(0));
            userList.add(user);
        }
        logger.info(userList);
        //调用Service写入数据库......
        request.setAttribute("result", "1");
        return mapping.getInputForward();
    } catch (Exception e) {
        e.printStackTrace();
        logger.error(e.getMessage());
        request.setAttribute("errMsg", e.getMessage());
    }
    return mapping.findForward("error");

}

	/**
	 * 读XLS数据
	 * 
	 * @param mapping
	 * @param form
	 * @param request
	 * @param response
	 * @return ActionForward
	 * @throws Exception
	 */
	public ActionForward readXls(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		try {
			UploadForm uploadForm = (UploadForm) form;
			FormFile file = uploadForm.getFormFile();
			InputStream is = file.getInputStream();
			XlsTools xlsTools = new XlsTools();
			xlsTools.read(is);
			//读Xls行所有数据并封装
			ArrayList<ArrayList<String>> listAll = xlsTools.readAll();
			xlsTools.closeRead();
			List<UserBean> userList = new ArrayList<UserBean>();
			for (int i = 1; i < listAll.size(); i++) {
				ArrayList<String> aList = listAll.get(i);
				UserBean user = new UserBean();
				user.setUserName(aList.get(0));
				userList.add(user);
			}
			logger.info(userList);
			//调用Service写入数据库......
			request.setAttribute("result", "1");
			return mapping.getInputForward();
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
			request.setAttribute("errMsg", e.getMessage());
		}
		return mapping.findForward("error");

	}