poi excel文件的导入

使用poi来实现excel文件的导入导出。使用struts2来做处理。

首先看jsp页面：

index.jsp:

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@ taglib uri="/struts-tags" prefix="s"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
    <head>

        <title>导入数据</title>
        <meta http-equiv="pragma" content="no-cache">
        <meta http-equiv="cache-control" content="no-cache">
        <meta http-equiv="expires" content="0">
        <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
        <meta http-equiv="description" content="This is my page">
        <!--
    <link rel="stylesheet" type="text/css" href="styles.css">
    -->
    </head>
    <body>
        <h1>
            导入Excel数据
        </h1>

        <s:form action="import!importExcel.action" enctype="multipart/form-data" method="post">
        导入Excel文件：<s:file name="excelFile"></s:file>
            <br />
        <s:submit value="导入"></s:submit>
        </s:form>
    </body>
</html>

action中的主要代码：原来依然是通过文件上传的原理来实现，只不过导入是将文件里的数据进行解析。

package com.chinasoft.action;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFWorkbook;
import com.chinasoft.dao.StudentDAO;
import com.chinasoft.entity.ExcelWorkSheet;
import com.chinasoft.entity.Student;
import com.opensymphony.xwork2.ActionSupport;

public class ImportExcelAction extends ActionSupport
{

    private StudentDAO dao = new StudentDAO();
    private File excelFile;
    private String excelFileFileName;
    private ExcelWorkSheet<Student> excelWorkSheet;

    public File getExcelFile()
    {
        return excelFile;
    }

    public void setExcelFile(File excelFile)
    {
        this.excelFile = excelFile;
    }

    public String getExcelFileFileName()
    {
        return excelFileFileName;
    }

    public void setExcelFileFileName(String excelFileFileName)
    {
        this.excelFileFileName = excelFileFileName;
    }

    public ExcelWorkSheet<Student> getExcelWorkSheet()
    {
        return excelWorkSheet;
    }

    public void setExcelWorkSheet(ExcelWorkSheet<Student> excelWorkSheet)
    {
        this.excelWorkSheet = excelWorkSheet;
    }

    private Workbook createWorkBook(InputStream is) throws IOException
    {
        if (excelFileFileName.toLowerCase().endsWith("xls"))
        {
            return new HSSFWorkbook(is);
        }

        if (excelFileFileName.toLowerCase().endsWith("xlsx"))
        {
            return new XSSFWorkbook(is);
        }

        return null;

    }

    public String importExcel() throws Exception
    {
        Workbook workbook = createWorkBook(new FileInputStream(excelFile));
        Sheet sheet = workbook.getSheetAt(0);
        excelWorkSheet = new ExcelWorkSheet<Student>();
        excelWorkSheet.setSheetName(sheet.getSheetName());
        Row firstRow = sheet.getRow(0);
        Iterator<Cell> iterator = firstRow.iterator();
        List<String> cellNames = new ArrayList<String>();
        while (iterator.hasNext())
        {
            cellNames.add(iterator.next().getStringCellValue());
        }

        for (int i = 1; i <= sheet.getLastRowNum(); i++)
        {
            Row row = sheet.getRow(i);
            Student student = new Student();
            System.out.println(row.getCell(0).getNumericCellValue());
            //student.setId((int) row.getCell(1).getNumericCellValue());
            student.setName(row.getCell(1).getStringCellValue());
            //student.setSex(row.getCell(2).getStringCellValue());
            //student.setBirthday(row.getCell(3).getDateCellValue());
            excelWorkSheet.getData().add(student);
            //this.dao.addStudent(student);
        }
        return SUCCESS;
    }

}

struts的配置也没有多大的变化，如下：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE struts PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 2.1//EN" 
    "http://struts.apache.org/dtds/struts-2.1.dtd">
<struts>
    <package name="chinasoft" extends="struts-default">
        <action name="import" class="com.chinasoft.action.ImportExcelAction">
            <result>/index.jsp</result>
        </action>
    </package>
</struts>

posted on 2015-01-07 01:15 缘非缘阅读(374) 评论(0) 编辑收藏举报