Excel 和 SQL Server数据库

Question

怎样把Excel中的数据导入到SQL Server数据库中

Answer 1

企业管理器里面有导入导出功能，可以将Table导出为Excel文件。

Answer 2

sun有一套API读写eXCEL数据的，上网搜索一下

Answer 3

java中间有POI/JXL 这两个开源类库 可以自己写程序实现，
直接导入的话可以参考sql loader自己写脚本

Answer 4

给你一段我项目中用到的类,用作参考:
提示要用到的包poipackage com.training.trClass.service.impl;import java.io.FileInputStream;
import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;
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.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;import com.jdp.core.comm.sequence.dao.SequInfoDao;
import com.jdp.core.comm.sequence.domain.SequInfo;
import com.training.trClass.service.TcAttendanceParse;
import com.training.trClass.dao.TrClassInfoDao;
import com.training.trClass.domain.TcAttendanceInfo;
import com.training.trClassEmp.dao.TrClassEmpInfoDao;
import com.training.trClassEmp.domain.TrClassEmpInfo;public class TcAttendanceImport implements TcAttendanceParse {
@Autowired
private SequInfoDao sequInfoDao;
@Autowired
private TrClassInfoDao trClassInfoDao;
@Autowired
private TrClassEmpInfoDao trClassEmpInfoDao;
/**
 * 培训班考勤文件导入
 * @param fileName
 * @param extName
 * @param tcAttendanceInfo
 * @return
 * @throws Exception
 */
@Transactional
public String checkParse(String fileName, String extName, TcAttendanceInfo tcAttendanceInfo) throws Exception {
     
int failNum = 0 , successNum = 0;

Workbook book = null;
if("xlsx".equals(extName)){
book = new XSSFWorkbook(fileName);
}else{
book = new HSSFWorkbook(new FileInputStream(fileName));
}
Row row;

//清空班级考勤表
trClassInfoDao.delTcAttendance(tcAttendanceInfo);

TrClassEmpInfo trClassEmpInfo = new TrClassEmpInfo();
trClassEmpInfo.setTc_id(tcAttendanceInfo.getTc_id());

List empLst = trClassEmpInfoDao.getTrClassEmpList(trClassEmpInfo);//查询培训班下的人

Sheet recordSheet=book.getSheetAt(0); //考勤sheet
int rows = recordSheet.getLastRowNum();     //总行数

TcAttendanceInfo temp = new TcAttendanceInfo();

int index = 0;
for(int i=1;i<=rows;i++){

row = recordSheet.getRow(i);

if( row != null){

String tca_code = getCellValue(row.getCell(index+2));

temp.setTca_state("0"); //导入失败
for(int j = 0; j < empLst.size(); j++){

trClassEmpInfo = (TrClassEmpInfo)empLst.get(j);
String u_code = trClassEmpInfo.getU_code();

if(u_code != null && u_code.trim().equals(tca_code)){

temp.setTca_state("1"); //默认导入成功
break;
}
}

temp.setTc_id(tcAttendanceInfo.getTc_id());
temp.setTca_code(getCellValue(row.getCell(index)));          //登记号码
temp.setTca_userName(getCellValue(row.getCell(index+1)));    //姓名
temp.setTca_userCode(getCellValue(row.getCell(index+2)));    //人员序号
temp.setTca_yingDao(getCellValue(row.getCell(index+3)));     //应到 
temp.setTca_shiDao(getCellValue(row.getCell(index+4)));      //实到
temp.setTca_chiDao(getCellValue(row.getCell(index+5)));      //迟到
temp.setTca_zaoTui(getCellValue(row.getCell(index+6)));      //早退
temp.setTca_yingQian(getCellValue(row.getCell(index+7)));    //应签次数
temp.setTca_qianDao(getCellValue(row.getCell(index+8)));     //签到
temp.setTca_qianTui(getCellValue(row.getCell(index+9)));     //签退
temp.setTca_weiQianDao(getCellValue(row.getCell(index+10))); //未签到
temp.setTca_weiQianTui(getCellValue(row.getCell(index+11))); //未签退
temp.setTca_qingJia(getCellValue(row.getCell(index+12)));    //请假
temp.setTca_gongChu(getCellValue(row.getCell(index+13)));    //公出
temp.setTca_binJia(getCellValue(row.getCell(index+14)));     //病假
temp.setTca_shiJia(getCellValue(row.getCell(index+15)));     //事假        

if(temp.getTca_state().equals("1")){
successNum = successNum + 1;  //成功人数
}
else{
failNum    = failNum + 1;     //失败人数
}
trClassInfoDao.insertTcAttendance(temp);
}
}
return successNum+"_"+failNum;
}

/**
 * 获得Excel单元格的数据
 * @param cell
 * @return
 */
public String getCellValue(Cell cell){
String value = "";

if( cell != null){

switch(cell.getCellType()){
case HSSFCell.CELL_TYPE_STRING: // 字符串型 
            value = cell.getRichStringCellValue().toString();
            break;
case HSSFCell.CELL_TYPE_NUMERIC: // 数字型 
value = String.valueOf((int)cell.getNumericCellValue());
            break;
case HSSFCell.CELL_TYPE_BLANK: // 空值 
            value = ""; 
            break;
}
}
return value;
}



/**
 * 获得sequenceId
 * @param tableName
 * @return
 */
public int getSequenceId(String tableName){
SequInfo seq = new SequInfo();
seq.setTbName(tableName);
int sequence = sequInfoDao.getID(seq);
seq.setSequence(sequence);
if(sequence > 1){
sequInfoDao.updateSequ(seq);
}else{
sequInfoDao.insertSequ(seq);
}
return sequence;
}

}

Answer 5

上面的类是实现方法,下面的类是action中调用checkImportAction()方法.package com.training.trClass.action;import javax.servlet.http.HttpServletRequest;import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.struts2.ServletActionContext;
import org.springframework.beans.factory.annotation.Autowired;import com.jdp.core.comm.constant.Constant;
import com.jdp.core.ibatis.PagedList;
import com.jdp.core.struts.BaseAction;
import com.training.trClass.domain.TcAttendanceInfo;
import com.training.trClass.service.TcAttendanceInfoService;
import com.training.trClass.service.TrClassInfoService;public class TcAttendanceAction extends BaseAction { private static final long serialVersionUID = 1L;
private final Log logger = LogFactory.getLog(getClass());

@Autowired
private TrClassInfoService trClassInfoService;
@Autowired
private TcAttendanceInfoService tcAttendanceInfoService;
private TcAttendanceInfo tcAttendanceInfo;


private String file;
private String result;
private int ajaxReturnCode = 1; //1:成功 0:失败

/* ---------------------- turn page begin ---------------------- */
private String firstQuery;
private boolean searchOption;
private int page;
private static final String QUERY_NAME = "lstTcAttendance";
private PagedList searchList;

public TcAttendanceAction(){
tcAttendanceInfo = new TcAttendanceInfo();
}



public TcAttendanceInfo getTcAttendanceInfo() {
return tcAttendanceInfo;
}
public void setTcAttendanceInfo(TcAttendanceInfo tcAttendanceInfo) {
this.tcAttendanceInfo = tcAttendanceInfo;
}

public void setFirstQuery(String firstQuery) {
this.firstQuery = firstQuery;
}

public boolean getSearchOption() {
return searchOption;
}
public void setPage(int page) {
this.page = page;
}

public PagedList getSearchList() {
return searchList;
}
public void setSearchList(PagedList searchList) {
this.searchList = searchList;
}
public String getFile() {
return file;
}
public void setFile(String file) {
this.file = file;
}
public String getResult() {
return result;
}
public void setResult(String result) {
this.result = result;
}
public int getAjaxReturnCode() {
return ajaxReturnCode;
}
public void setAjaxReturnCode(int ajaxReturnCode) {
this.ajaxReturnCode = ajaxReturnCode;
}

/* ---------------------- turn page end ---------------------- */






/**
 * 考勤信息导入
 * @return
 * @throws Exception
 */
public String checkImportAction() throws Exception {
try{
HttpServletRequest request = ServletActionContext.getRequest();
String extName = request.getParameter("extName");

String  returnValue = trClassInfoService.checkParserExcel(file,extName,tcAttendanceInfo);

if(returnValue != null && !returnValue.trim().equals("")){
String[] arg = returnValue.split("_");
int empCount = Integer.parseInt(arg[0])+ Integer.parseInt(arg[1]);
result = "共导入考勤数据"+empCount+"人 "+arg[0]+"人成功 "+arg[1]+"人失败";
}


}catch(Exception e){
e.printStackTrace();
result = "数据导入失败,请重新进行尝试!";
}
return SUCCESS;
}


}

Answer 6

//解析入口
checkParserExcel(file,extName,tcAttendanceInfo)