导入excel
JAVA 通常有两种方法来操作Excel，分别是开源的POI和JExcelAPI。
POI是Apace公司，对中文支持较弱；
JExcelAPI是韩国公司，中文支持好，纯JAVA编写，可跨平台。不依赖Windows系统，在Linux下同样能够正确的处理Excel文件。 对图形和图表的支持很有限，仅识别PNG格式。
本例使用POI
实现的功能：插入
JSP页面：
<script type="text/javascript" src="js/plugins/swfupload/swfupload.js"></script> <script type="text/javascript" src="js/plugins/swfupload/js/handlers.js"></script> <script type="text/javascript" src="js/plugins/swfupload/js/swfupload.queue.js"></script> <script type="text/javascript" src="js/plugins/swfupload/js/fileprogress.js"></script> <script type="text/javascript" src="js/plugins/swfupload/js/swfupload.swfobject.js"></script> <script type="text/javascript"> var swfu; function wload() { var settings = { flash_url : "js/plugins/swfupload/swfupload.swf", upload_url : "taoke/uploadExcel", post_params : { //"user" : "1" }, file_post_name : "file", file_size_limit : "2048 MB", file_types : "*.*", file_types_description : "All Files", file_upload_limit : 100, file_queue_limit : 0, custom_settings : { progressTarget : "fsUploadProgress", cancelButtonId : "btnCancel" }, debug : false, // Button settings button_image_url : "../images/daoru.png", button_width : "56", button_height : "35", button_placeholder_id : "spanButtonPlaceHolder", //button_text: '<span class="theFont" >上传文件</span>', button_text_style : ".theFont { font-size: 14; }", button_text_left_padding : 12, button_text_top_padding : 3, // The event handler functions are defined in handlers.js file_queued_handler : fileQueued, file_queue_error_handler : fileQueueError, file_dialog_complete_handler : fileDialogComplete, upload_start_handler : uploadStart, upload_progress_handler : uploadProgress, upload_error_handler : uploadError, upload_success_handler : uploadExcelSuccess, upload_complete_handler : uploadComplete, queue_complete_handler : queueComplete // Queue plugin event }; swfu = new SWFUpload(settings); }; var wlds = window.onload; window.onload = function() { if (wlds != undefined) { wlds(); } wload(); }; </script> <div style="display: none;"> <div class="fieldset flash" id="fsUploadProgress"> <span class="legend">Upload Queue</span> </div> <input id="btnCancel" type="button" value="Cancel All Uploads" onclick="swfu.cancelQueue();" disabled="disabled" style="margin-left: 2px; font-size: 8pt; height: 29px;" /> </div>
JS页面：
var wld=window.onload; window.onload=function(){ if(wld!=undefined){ wld(); } appLoad.init(); }; function uploadExcelSuccess(file, response) { try { var dt=JSON.parse(response); alert('导入成功'); } catch (ex) { alert(ex) } }
java代码：
package com.entrym.web.action; import java.io.File; import java.io.FileInputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.OfficeXmlFileException; 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.json.JSONUtil; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.context.annotation.Scope; import org.springframework.stereotype.Controller; import com.admin.commons.BaseAction; import com.admin.commons.Page; import com.entrym.domain.TaokeDetail; import com.entrym.service.TaokeDetailService; @Scope("prototype") @Controller("taokeDetailAction") public class TaokeDetailAction extends BaseAction { Logger log = Logger.getLogger(""); private static final long serialVersionUID = 1L; public String taokeDetail() { return "taokeDetail"; } @Autowired private TaokeDetailService taokeDetailService; @SuppressWarnings("unused") public void uploadExcel() { if (file != null) { try { Workbook workbook = load(file); TaokeDetail taokeDetail = new TaokeDetail(); for (Sheet sheet : workbook) {// 拿到每个sheet Row firstRow = sheet.getRow(0);// 获取第一行 List<String> firstLine = new ArrayList<String>(); for (Cell cell : firstRow) {// 遍历第一行的每一个单元格 if (cell != null) { firstLine.add(cell.toString()); } } SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); for (int s = 1; s <= sheet.getLastRowNum(); s++) {// 从第二行开始遍历，存入数据库 HashMap<String, Object> lhhead = new HashMap<>(); for (int i = 0; i < firstRow.getLastCellNum(); i++) {// row.getLastCellNum()获取行的列数 try { Cell cell = sheet.getRow(s).getCell(i);// 获取每一行某列单元格 String value = null; String header = firstLine.get(i); String cre = "创建时间"; if (cell != null) { cell.setCellType(Cell.CELL_TYPE_STRING); switch (header) { case "创建时间": taokeDetail.setCreatdate(sdf.parse(cell.getStringCellValue())); break; case "点击时间": taokeDetail.setHittime(sdf.parse(cell.getStringCellValue())); break; case "结算时间": taokeDetail.setSettlementtime(sdf.parse(cell.getStringCellValue())); break; case "商品信息": taokeDetail.setGoodsinfo(cell.getStringCellValue()); break; case "掌柜旺旺": taokeDetail.setAlitrademanager(cell.getStringCellValue()); break; case "所属店铺": taokeDetail.setShop(cell.getStringCellValue()); break; case "订单状态": taokeDetail.setOrderstate(cell.getStringCellValue()); break; case "订单类型": taokeDetail.setOrdertype(cell.getStringCellValue()); break; case "补贴类型": taokeDetail.setSubsidytype(cell.getStringCellValue()); break; case "成交平台": taokeDetail.setTransactionplatform(cell.getStringCellValue()); break; case "第三方服务来源": taokeDetail.setSourceservise(cell.getStringCellValue()); break; case "类目名称": taokeDetail.setTypename(cell.getStringCellValue()); break; case "来源媒体名称": taokeDetail.setSourcemedianame(cell.getStringCellValue()); break; case "广告位名称": taokeDetail.setAdvertisingspacename(cell.getStringCellValue()); break; case "商品ID": taokeDetail.setGoodsid(Long.parseLong(cell.getStringCellValue())); break; case "订单编号": taokeDetail.setOrdernumber(Long.parseLong(cell.getStringCellValue())); break; case "来源媒体ID": taokeDetail.setSourcemediaid(Long.parseLong(cell.getStringCellValue())); break; case "广告位ID": taokeDetail.setAdvertisingspaceid(Long.parseLong(cell.getStringCellValue())); break; case "商品数": taokeDetail.setGoodscount(Integer.parseInt(cell.getStringCellValue())); break; case "商品单价": taokeDetail.setUnitprice(Double.parseDouble(cell.getStringCellValue())); break; case "收入比率": value = cell.getStringCellValue(); taokeDetail.setEarningratio(Double.parseDouble(value.substring(0, value.length() - 1))); break; case "分成比率": value = cell.getStringCellValue(); taokeDetail.setProrataratio(Double.parseDouble(value.substring(0, value.length() - 1))); break; case "付款金额": taokeDetail.setPaymoney(Double.parseDouble(cell.getStringCellValue())); break; case "效果预估": taokeDetail.setEffectestimates(Double.parseDouble(cell.getStringCellValue())); break; case "结算金额": taokeDetail.setSettlementamount(Double.parseDouble(cell.getStringCellValue())); break; case "预估收入": taokeDetail.setEstimaterevenue(Double.parseDouble(cell.getStringCellValue())); break; case "佣金比率": value = cell.getStringCellValue(); taokeDetail.setCommissionrate(Double.parseDouble(value.substring(0, value.length() - 1))); break; case "佣金金额": taokeDetail.setCommission(Double.parseDouble(cell.getStringCellValue())); break; case "补贴比率": value = cell.getStringCellValue(); taokeDetail.setSubsidyratio(Double.parseDouble(value.substring(0, value.length() - 1))); break; case "补贴金额": taokeDetail.setSubsidycalculation(Double.parseDouble(cell.getStringCellValue())); break; default: break; } } } catch (Exception e) { e.printStackTrace(); } } taokeDetailService.insertSelective(taokeDetail); } } write("{\"success\":true}"); } catch (Exception e) { e.printStackTrace(); } } } private static Workbook load(File file) throws Exception {// 读取 Workbook workbook = null; try { workbook = new HSSFWorkbook(new FileInputStream(file)); } catch (Exception e) { if (e instanceof OfficeXmlFileException) { try { workbook = new XSSFWorkbook(new FileInputStream(file)); } catch (Exception ex) { workbook = null; throw ex; } } else { throw e; } } return workbook; } private File file; public File getFile() { return file; } public void setFile(File file) { this.file = file; } }
解决办法：先设置Cell的类型，然后就可以把纯数字作为String类型读进来了：
cell.setCellType(Cell.CELL_TYPE_STRING);
其他：
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
cell.setCellType(Cell.CELL_TYPE_FORMULA);
cell.setCellType(Cell.CELL_TYPE_BLANK);
cell.setCellType(Cell.CELL_TYPE_ERROR);
