最近在在一个web的项目开发时,需要用到excel表格导入数据库,做完之后发现需要注意的点有很多,所以就写下来分享一下自己的新的体会
package com.oracle.match.action;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.io.FileUtils;
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 org.apache.struts2.ServletActionContext;
import com.opensymphony.xwork2.ActionSupport;
import com.oracle.match.po.Introduce;
import com.oracle.match.po.Match1;
import com.oracle.match.service.IntroduceService;
import com.oracle.match.service.MatchService;
public class FilematchAction extends ActionSupport{
/**
*
*/
private static final long serialVersionUID = -2807461111381509765L;
private File excel;
private String excelFileName;
private String result;
public String getResult() {
return result;
}
public void setResult(String result) {
this.result = result;
}
public File getExcel() {
return excel;
}
public void setExcel(File excel) {
this.excel = excel;
}
public String getExcelFileName() {
return excelFileName;
}
public void setExcelFileName(String excelFileName) {
this.excelFileName = excelFileName;
}
private MatchService matchService;
private IntroduceService introduceService;
public IntroduceService getIntroduceService() {
return introduceService;
}
public void setIntroduceService(IntroduceService introduceService) {
this.introduceService = introduceService;
}
private Introduce introduce;
public Introduce getIntroduce() {
return introduce;
}
public void setIntroduce(Introduce introduce) {
this.introduce = introduce;
}
public MatchService getMatchService() {
return matchService;
}
public void setMatchService(MatchService matchService) {
this.matchService = matchService;
}
public String Fileupload(){
String realPath = ServletActionContext.getServletContext().getRealPath("/files");
System.out.println(excelFileName);
File savefile = new File(new File(realPath),excelFileName);
if(!savefile.getParentFile().exists()) savefile.getParentFile().mkdirs();
try {
FileUtils.copyFile(excel, savefile);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
getDataFromExcel(realPath+"\\"+excelFileName);
if(excel.exists())
excel.delete();
return "SUCCESS";
}
/**
*
* @param cell 一个单元格的对象
* @return 返回该单元格相应的类型的值
*/
public static Object getRightTypeCell(Cell cell){
Object object = null;
switch(cell.getCellType())
{
case Cell.CELL_TYPE_STRING :
{
object=cell.getStringCellValue();
break;
}
case Cell.CELL_TYPE_NUMERIC :
{
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
object=cell.getNumericCellValue();
break;
}
case Cell.CELL_TYPE_FORMULA :
{
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
object=cell.getNumericCellValue();
break;
}
case Cell.CELL_TYPE_BLANK :
{
cell.setCellType(Cell.CELL_TYPE_BLANK);
object=cell.getStringCellValue();
break;
}
}
return object;
}
/**
* 读取出filePath中的所有数据信息
* @param filePath excel文件的绝对路径
*
*/
public void getDataFromExcel(String filePath)
{
List<Map<String,Integer>> list = new ArrayList<Map<String, Integer>>();
FileInputStream fis =null;
Workbook workbook = null;
int flag = 0;
try
{
//获取一个绝对地址的流
fis = new FileInputStream(filePath);
}
catch(Exception e)
{
e.printStackTrace();
}
try
{
//2003版本的excel,用.xls结尾
workbook = new HSSFWorkbook(fis);//得到工作簿
}
catch (Exception ex)
{
try
{
//2007版本的excel,用.xlsx结尾
fis = new FileInputStream(filePath);
workbook = new XSSFWorkbook(fis);//得到工作簿
} catch (IOException e)
{
e.printStackTrace();
}
}
//得到一个工作表
Sheet sheet = workbook.getSheetAt(0);
//获得表头
Row rowHead = sheet.getRow(0);
//根据不同的data放置不同的表头
Map<Object,Integer> headMap = new HashMap<Object, Integer>();
try
{
//----------------这里根据你的表格有多少列
while (flag < 6)
{
Cell cell = rowHead.getCell(flag);
if (getRightTypeCell(cell).toString().equals("赛事名"))
headMap.put("mname", flag);
if (getRightTypeCell(cell).toString().equals("类别编号"))
headMap.put("cid", flag);
if (getRightTypeCell(cell).toString().equals("赛事介绍"))
headMap.put("intro", flag);
if (getRightTypeCell(cell).toString().equals("人数"))
headMap.put("num", flag);
if (getRightTypeCell(cell).toString().equals("开赛时间"))
headMap.put("btime", flag);
if (getRightTypeCell(cell).toString().equals("比赛场地"))
headMap.put("matchcd", flag);
flag++;
}
} catch (Exception e)
{
e.printStackTrace();
System.out.println("表头不合规范,请修改后重新导入");
}
//获得数据的总行数
int totalRowNum = sheet.getLastRowNum();
System.out.println(totalRowNum);
String mname ="";//这堆其实没什么用,我忘删了
String cid ="";
Double cid1=0.0;
int Cid=0;
String intro="";
String num="";
Double num1 =0.0;
int Num=0;
String btime ="";
String matchcd ="";
// String pass1 ="";
Double temp=0.0;
if(0 == totalRowNum)
{
System.out.println("Excel内没有数据!");
}
Cell cell_1 = null,cell_2 = null,cell_3 = null,cell_4 = null,cell_5 = null,cell_6 = null;
//获得所有数据
for(int i = 1 ; i <= totalRowNum ; i++)
{
//获得第i行对象
Row row = sheet.getRow(i);
try
{
cell_1 = row.getCell(headMap.get("mname"));
cell_2 =row.getCell(headMap.get("cid"));
cell_3 = row.getCell(headMap.get("intro"));
cell_4 = row.getCell(headMap.get("num"));
cell_5 = row.getCell(headMap.get("btime"));
cell_6 = row.getCell(headMap.get("matchcd"));
} catch (Exception e)
{
e.printStackTrace();
System.out.println("获取单元格错误");
}
try
{
mname = cell_1.toString();
cid = cell_2.toString();
if(cid!=null&&cid.length()>0)
{
cid1=Double.valueOf(cid);
for(;cid1!=0;cid1--){
Cid++;
}
}
intro = cell_3.toString();
num = cell_4.toString();
if(num!=null&&num.length()>0)
{
num1=Double.valueOf(num);
for(;num1!=0;num1--){
Num++;
}
}
btime = cell_5.toString();
matchcd = cell_6.toString();
} catch (ClassCastException e)
{
e.printStackTrace();
System.out.println("数据不全是数字或全部是文字!");
}
Match1 match = new Match1();
if(mname!=null&&mname.length()>0)
{
match.setMname(mname);
match.setCid(Cid);
getMatchService().save(match);
Introduce introduce=new Introduce();
List<Match1> Mid=matchService.queryByMname(mname);
int ID = 0;
for(Match1 id:Mid)
{
ID=Integer.valueOf(id.getMid()).intValue();
}
introduce.setMid(ID);
introduce.setBtime(btime);
introduce.setIntro(intro);
introduce.setMatchcd(matchcd);
introduce.setNum(Num);
introduceService.save(introduce);
}
}
result="导入完毕!";
}
}
上面就是导入的代码,不过要注意,表格里所有的数字在经过程序堆出来的时候都会变成double型,比如4会变成4.0所以数据在读出来的时候需要自己转化一下,字存到数据库里防止存入数据出错,例如我上面的代码就是先转化成String类型的在转化成double在转化成int型,这种方法比较笨,不过实用 num = cell_4.toString();
if(num!=null&&num.length()>0)
{
num1=Double.valueOf(num);
for(;num1!=0;num1--){
Num++;
}代码
上面的代码写完之后,这个action类还要在struct.xml里配置才可以调用实用,代码如下:
<action name="filematchAction_*" method="{1}" class="com.oracle.match.action.FilematchAction">
<result name="SUCCESS">admin/match/query.jsp</result>
<interceptor-ref name="token">
<param name="excludeMethods">Fileupload</param>
</interceptor-ref>
<!-- 文件上传 -->
<interceptor-ref name="fileUpload">
<param name="maximumSize">10M</param>
<param name="allowedExtensions">xls,xlsx</param>
</interceptor-ref>
<interceptor-ref name="defaultStack"></interceptor-ref>
</action>
这样配置完之后,就可以在前台编写jsp代码调用了,代码如下:
<form action="filematchAction_Fileupload.action" method="post"
enctype="multipart/form-data">
<s:file label="文件" id="fileName" name="excel"></s:file>
<input type="submit" value="导入数据" id="go" οnclick="goto1()">${result}
</form>
这样就可以excel表格导入数据了,希望可以帮到你们