程序博客网 > 2016制造业行业数据

EXCEL通过配置导入规则，实现任意格式EXCEL文件解析

来源：互联网 发布：2016制造业行业数据 编辑：程序博客网 时间：2024/05/16 06:53

EXCEL文件导入到数据库，是一种常见的文件导入格式。

本文实现的EXCEL文件解析方法，优点是适合任意格式的EXCEL文件，缺点是每一张表单都需要配置对应的导入规则，在字段数量多时，配置导入规则就尴尬了，好在也只需要配置一遍。

先看效果，以下两张图是我测试的EXCEL文件，其中第二张图中，有5个工作表。这两个表单字段也会不定期变动，这就需要重新修改导入规则。（对于字段变动的问题，用到的是表单引擎技术，这里就不进行说明了）




先看导入规则表字段：

字段名称中文名称说明ID主键 BIAODANYUID表单域ID导入表的字段名称；name，sexBIAODANYUMINGCHEN表单域名称导入表的字段中文名；姓名，性别EXCELLIEHAOEXCEL列号姓名所在的列，BEXCELHANGHAOEXCEL行号姓名所在的行，2SHIFUBITIANJIAOYAN是否必填字段必填校验SHIFUCHONGFUJIAOYAN是否唯一字段唯一性校验SHUJULEIXING数据类型日期类型，数值类型，字典类型，图片类型SHUJUGESHI数据格式日期的格式yyyy-MM-dd，数值的格式#.0000

下面开始导入EXCEL文件了

1 JSP页面上传文件

<!DOCTYPE html><html><head><meta charset="UTF-8"><title>Excel导入</title><style type="text/css">.table td, .table th {border: 1px solid #cad9ea;padding: 0 1em 0;}.table tr {height: 18px;} #loading{ position:fixed; _position:absolute; top:40%; left:50%; width:120px; height:120px; overflow:hidden; background:url(${root!}/static/img/importExcel/1.gif) no-repeat; z-index:10; display:none; }</style></head><body><form id="importForm" method="post" action=""enctype="multipart/form-data"><table width="90%" class="table" style="margin-top: 5px;"><tr><td style="align: center;"><input id="files" name="files" type="file" multiple="multiple" class="file "></td></tr><tr><td style="text-align: center;"><div style="height: 20px;">文件只限于xls,xlsx格式</div><div style="height: 20px;">上传文件最大数量:${excelNumber!}</div><div style="height: 20px;">上传文件总大小:${excelSize!}M</div></td></tr></table></form><div id="loading"></div> <script type='text/javascript'> $(document).ready(function () {        $('#loading').hide();//隐藏loading    });function submit() {var excelNumber = "${excelNumber!}";var excelSize = "${excelSize!}";var filepath = $("input[name='files']").val();var extStart = filepath.lastIndexOf(".");var ext = filepath.substring(extStart, filepath.length).toUpperCase();var fileCount = document.getElementById("importForm")["files"].files.length;if(fileCount == 0 ){// window.alert('请选择上传文件！');layer.msg("请选择上传文件！", {icon: 2,time:1500});return false;}if (ext != ".XLS" && ext != ".XLSX") {// alert("文件只限于xls,xlsx格式");layer.msg("文件只限于xls,xlsx格式", {icon: 2,time:1500});return false;}if (fileCount > excelNumber) {// window.alert('文件数不能超过'+excelNumber+'个，你选择了' + fileCount + '个');layer.msg("文件数不能超过"+excelNumber+"个，您选择了" + fileCount + "个", {icon: 2,time:1500});return false;}var fileSize = 0;var i = 0;for (i; i < fileCount; i++) {fileSize = fileSize + $("#files")[0].files[i].size;}var size = fileSize / (1024 * 1024);if (size > excelSize) {// alert("上传的文件大小不能超过"+excelSize+"M！");layer.msg("上传的文件大小不能超过"+excelSize+"M！", {icon: 2,time:1500});return false;}$( '#importForm').attr("action",Power.rootPath + "${url!}"+"?buttonName=确认导入&flag=end" );$("#loading").show();//显示loading$( '#importForm').submit();}</script></body></html>



2 控制层接收文件
@RequestMapping(value = "/uploadFile",  method = RequestMethod.POST)public String uploadFile(HttpServletRequest request,HttpServletResponse response, ModelMap map,@RequestParam(value = "files", required = true) MultipartFile[] files) {String appCode = approveConstant.getProperty("app.code");// 过滤用户所能看到的机构String userId = "";try {userId = getCurrentUser().get("ID");} catch (SessionException e1) {e1.printStackTrace();}PubUserOrganizatiion userOrg = userOrganizatiionService.getEntityByUserId(userId);try {String realPath = request.getRealPath("/");Map<String, String> msg = importExcelService.importExcels(files, appCode, userOrg, "10", realPath, true, true, false);net.sf.json.JSONObject jsonObject = net.sf.json.JSONObject.fromObject(msg);setAttr("importMsg",jsonObject);jjgbglImportExcelService.insertImportMsg(jsonObject,getCurrentUser().getStr("ID"),"10");//干部信息10，干部事项报告20JSONObject table = formConnectionService.getModelByTask("10"); // 开启的表代号String formId = table.getString("DaoRuBiaoDanID");this.setAttr("formId", formId);setAttr("year",table.getString("NianDuXuanZe"));setAttr("task","10");} catch (Exception e) {e.printStackTrace();}return this.getIndexView("inspection/cadreInformation/information_index", map);}

3 service层解析文件
代码量较大
package com.inspur.inspection.system.service;import java.sql.Connection;import java.sql.SQLException;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Properties;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import org.springframework.web.multipart.MultipartFile;import com.alibaba.fastjson.JSONObject;import com.inspur.approval.base.dao.FormDao;import com.inspur.approval.base.service.DictService;import com.inspur.approval.base.service.FormConnectionService;import com.inspur.inspection.system.userOrganization.po.PubUserOrganizatiion;import com.inspur.mng.core.db.Record;import com.inspur.punish.business.attachment.servise.EnforceServise;import com.inspur.service.FormService;import com.inspur.utils.excel.ImportExcel;@Service("com.inspur.inspection.system.service.ImportExcelService")public class ImportExcelService {@Autowiredprivate FormConnectionService formConnectionService;@Autowiredprivate EnforceServise enforceservise;@Autowiredprivate FormService formService;@Autowiredprivate FormDao formDao;@Autowiredprivate DictService dictService;@Autowiredprivate Properties approveConstant;/** * 导入EXCEL * @param files 文件 * @param appCode  * @param userOrg * @param task 开启的表单编号 * @param realPath requset的真实路径 * @param savePic 是否保存照片 * @param ShiFuDiaoChu   是否给ShiFuDiaoChu隐藏域设置默认值 * @param NianFen 是否给NianFen隐藏域设置默认值 * @return * @throws Exception */public Map<String, String> importExcels( MultipartFile[] files, String appCode, PubUserOrganizatiion userOrg , String task ,String realPath, boolean savePic,boolean ShiFuDiaoChu , boolean NianFen)throws Exception {Map<String, String> importFeedback = new HashMap<String, String>();// 导入结果反馈信息int total = files.length; // excel文件总数int success = 0;// 导入成功数int fail = files.length;// 导入失败数String msg = "";//导入失败信息msg = fileCheck(files); //检查文件数量，大小if (msg != null && (!msg.equals(""))) {getImportFeedbackMap(importFeedback, total, success, fail, msg);return importFeedback;}JSONObject table = formConnectionService.getModelByTask(task); // 开启的表代号String formId = "";String year = ""; //年份String formDataId ="";if (table != null) {formId = table.getString("DaoRuBiaoDanID"); // 导入模板IDyear = table.getString("NianDuXuanZe"); // 年份formDataId = table.getString("formDataId");// 表单数据ID}else{if(task.equals("20")){msg = "[{'result':'模板对照配置异常','fileName':'模板对照配置异常'}]";}else{msg = "[{'result':'模板对照配置异常','fileName':'模板对照配置异常'}]";}getImportFeedbackMap(importFeedback, total, success, fail, msg);return importFeedback;}fail = 0;List<Map<String, String>> failList = new ArrayList<Map<String, String>>();// 失败原因之多个文件for (MultipartFile file : files) {boolean flag = true; // 数据校验标志Map<String, String> fileFailMsg = new HashMap<String, String>();// 失败原因之文件Map<String, String> resultMsg = new HashMap<String, String>();// 失败原因之表单域String excelName = file.getOriginalFilename();ImportExcel excel = new ImportExcel(excelName,file.getInputStream(), 0, 0);Workbook wb = excel.getWb();Sheet sheet = null;Map<String, String> map = new HashMap<String, String>();// 数据mapList<String> numList = queryRuleSheetNum(formDataId);//工作表sheet编号List<Record> list = new ArrayList<Record>();String picName ="";if(savePic){try{picName = savePic(realPath, wb);// 保存照片}catch (Exception e){flag = false;resultMsg.put("个人一寸照片", "导入异常");}}for (String num : numList) {list = queryRule(formDataId,num);// 导入规则try{sheet = wb.getSheetAt(Integer.valueOf(num)-1);}catch(IllegalArgumentException e){resultMsg.put("文件读取异常", "工作表数量不匹配");flag = false;}for (Record record : list) {String biaoDanYuId = record.getStr("BIAODANYUID"); // 表单域IDString biaoDanYuMingChen = record.getStr("BIAODANYUMINGCHEN");// 表单域名称String columnString = record.getStr("EXCELLIEHAO");// EXCEL列号String rowString = record.getStr("EXCELHANGHAO");// EXCEL行号String isRequired = record.getStr("SHIFUBITIANJIAOYAN");// 是否必填String isUnique = record.getStr("SHIFUCHONGFUJIAOYAN");// 是否唯一String shuJuGeShi = record.getStr("SHUJUGESHI");// 数据格式String shuJuLeiXing = record.getStr("SHUJULEIXING");// 数据类型if ((!biaoDanYuId.equals("ShiFuDiaoChu"))&& (!biaoDanYuId.equals("NianFen"))) {//过滤隐藏域int column = ImportExcel.columnFormat(columnString);int row = ImportExcel.rowFormat(rowString);String cellValue = ImportExcel.getCellValue(sheet, row, column,shuJuLeiXing,shuJuGeShi);String value = getValueFromDic(formId, biaoDanYuId, cellValue,appCode);if(biaoDanYuId.equals("SuoShuJiGou")){//所属机构，导入权限设置boolean power = false ;if (userOrg != null && userOrg.getOrganization() != null&& !"".equals(userOrg.getOrganization())) {String[] params = userOrg.getOrganization().split(",");for (String s : params) {if(s.equals(value)){power = true;}}} if(!power){resultMsg.put(biaoDanYuMingChen, "未拥有该机构数据导入权限");flag = false;}}if ("1".equals(isRequired)) {// 必填校验if (!biaoDanYuId.equals("GeRenYiCunZhaoPian")) {if (value.trim().equals("") || value == null) {resultMsg.put(biaoDanYuMingChen, "必填项缺失");flag = false;}}}if ("1".equals(isUnique)) {// 唯一校验boolean b = isUniqueMethod(formId, biaoDanYuId, value , task , year);if (b) {resultMsg.put(biaoDanYuMingChen, "记录重复");flag = false;}}if (biaoDanYuId.equals("GeRenYiCunZhaoPian")) {if ("1".equals(isRequired)) {// 必填校验if(picName==null||picName.trim().equals("")){resultMsg.put(biaoDanYuMingChen, "必填项缺失");flag = false;}else{map.put(biaoDanYuId, picName);}}else{map.put(biaoDanYuId, picName);}} else{map.put(biaoDanYuId, value);}}}}if(ShiFuDiaoChu){map.put("ShiFuDiaoChu", "20");// 是否调出隐藏域赋值}if(NianFen){map.put("NianFen",  year );// 年份}net.sf.json.JSONObject formData = net.sf.json.JSONObject.fromObject(map);JSONObject formData1 = JSONObject.parseObject(formData.toString());if (flag) {JSONObject json = formService.saveFormData(appCode, null,formId, formData1);String state = json.getString("state");if ("200".equals(state)) {success++;} else {flag = false;fail++;resultMsg.put("系统异常", "写入数据库失败");}} else {fail++;}if (!flag) {fileFailMsg.put("fileName", excelName);net.sf.json.JSONObject jsonObject = net.sf.json.JSONObject.fromObject(resultMsg);fileFailMsg.put("result",jsonObject.toString().replaceAll("\"", "").replaceAll("\\{", "").replaceAll("\\}", "") );failList.add(fileFailMsg);}}net.sf.json.JSONArray jsonObject = net.sf.json.JSONArray.fromObject(failList);msg = jsonObject.toString().replaceAll("\"", "'");getImportFeedbackMap(importFeedback, total, success, fail, msg);return importFeedback;}/** * 封装反馈信息Map * @param importFeedback * @param total * @param success * @param fail * @param msg */public void getImportFeedbackMap(Map<String, String> importFeedback , int total , int success , int fail , String msg){importFeedback.put("total", String.valueOf(total));importFeedback.put("success", String.valueOf(success));importFeedback.put("fail", String.valueOf(fail));importFeedback.put("msg", msg);}/** * 校验文件数量和大小 *  * @param files * @return */public String fileCheck(MultipartFile[] files) {String msg = "" ; //失败信息String excelNumber = approveConstant.getProperty("excel.number");String excelSize = approveConstant.getProperty("excel.size");// 文件数量校验if (files.length == 0) {msg = "文件数量为空";return msg;}if (files.length > Integer.valueOf(excelNumber)) {msg ="文件数量超过" + excelNumber + "个";return msg;}// 文件大小校验long size = Long.valueOf(excelSize) * 1024 * 1024;long fileSize = 0L;for (MultipartFile multipartFile : files) {fileSize = fileSize + multipartFile.getSize();}if (fileSize == 0) {msg ="文件大小为0";return msg;}if (fileSize > size) {msg ="文件大小为超过了" + excelSize + "M";return msg;}return msg;}/** * 获取导入规则 工作表sheet的编号种类 *  * @param formDataId * @return * @throws SQLException */public List<String> queryRuleSheetNum(String formDataId) throws SQLException {StringBuffer sql = new StringBuffer();sql.append(" SELECT　DISTINCT ( GONGZUOBIAOBIANHAO ) ");sql.append(" FROM  DAORUGUIZE ");sql.append(" WHERE ");sql.append(" MOBANDUIZHAOID =  " + formDataId);sql.append(" ORDER BY GONGZUOBIAOBIANHAO ASC ");Object[] paramArrayOfObject = {};Connection connection = null;List<String> numList = new ArrayList<String>();try {connection = formConnectionService.getDataSource().getConnection();List<Record> list = formDao.getRecordList(connection,sql.toString(), paramArrayOfObject);for (Record record : list) {String num = record.getStr("GONGZUOBIAOBIANHAO");numList.add(num);}} catch (Exception e) {e.printStackTrace();} finally {if (connection != null) {connection.close();}}return numList;}/** * 获取导入规则列表 *  * @param formDataId * @param num  工作表 sheet 编号 * @return * @throws SQLException */public List<Record> queryRule(String formDataId , String num) throws SQLException {StringBuffer sql = new StringBuffer();sql.append(" SELECT　*　");sql.append(" FROM  DAORUGUIZE ");sql.append(" where ");sql.append(" MOBANDUIZHAOID =  " + formDataId);sql.append(" AND GongZuoBiaoBianHao =  " + num );sql.append(" ORDER BY CAST( PAIXUHAO AS  INTEGER ) ASC");Object[] paramArrayOfObject = {};Connection connection = null;try {connection = formConnectionService.getDataSource().getConnection();List<Record> list = formDao.getRecordList(connection,sql.toString(), paramArrayOfObject);return list;} catch (Exception e) {e.printStackTrace();} finally {if (connection != null) {connection.close();}}return null;}/** * 根据字典获取值 *  * @param formId *            表单ID * @param biaoDanYuId *            表单域ID * @param cellValue *            EXCEL单元格值 * @param appCode * @return */public String getValueFromDic(String formId, String biaoDanYuId,String cellValue, String appCode) {String value = "";Map<String, String> dictMap = new HashMap<String, String>();try {dictMap = formConnectionService.getDictCodes(formId);// 表单字典} catch (SQLException e) {e.printStackTrace();}if (dictMap != null && dictMap.size() != 0) {String code = dictMap.get(biaoDanYuId);List<Map<String, Object>> dicList = dictService.getListByDictCode(code, appCode);if (dicList.size() > 0) {for (Map<String, Object> map2 : dicList) {String name = (String) map2.get("NAME");if (name.equals(cellValue.trim())) {value = (String) map2.get("CODE");break;}}} else {value = cellValue;}}return value;}/** * 上传照片至网盘 *  * @param realPath * @param wb * @return * @throws Exception */public String savePic(String realPath, Workbook wb) throws Exception{String filePath = ImportExcel.saveTempPic(realPath, wb); // 保存图片至临时目录String ext = "";String docid = "";if (filePath != null && !"".equals(filePath.trim())) {// filePath不为空，图片存在,将图片上传至网盘ext = filePath.substring(filePath.lastIndexOf(".") + 1);net.sf.json.JSONObject obj = enforceservise.fileUpload(filePath); // 上传至网盘ImportExcel.delTempPic(filePath);// 删除临时图片docid = obj.get("docid").toString();// 保存成功时返回的文件Id}String picName = docid + "." + ext;return picName;}/** * 唯一性校验 *  * @param formId *            表单Id * @param biaoDanYuId *            表单域Id * @param value *            需要校验的值 * @param task *            表单代号 * @return * @throws SQLException */public boolean isUniqueMethod(String formId, String biaoDanYuId,String value , String task , String year) throws SQLException {StringBuffer sql = new StringBuffer("select  " + biaoDanYuId+ "  from  " + formId + "  where  " + biaoDanYuId + "  =  '"+ value + "'");if(task.equals("20")){sql.append("  and NIANFEN = '" + year + "'");}Object[] paramArrayOfObject = {};Connection connection = null;try {connection = formConnectionService.getDataSource().getConnection();List<Record> list = formDao.getRecordList(connection,sql.toString(), paramArrayOfObject);if (list != null && list.size() > 0) {return true;}} catch (Exception e) {e.printStackTrace();} finally {if (connection != null) {connection.close();}}return false;}}

4 EXCEL解析通用工具类
package com.inspur.utils.excel;import java.io.BufferedOutputStream;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.Date;import java.util.List;import java.util.UUID;import org.apache.commons.lang3.StringUtils;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.DateUtil;import org.apache.poi.ss.usermodel.PictureData;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.ss.util.CellRangeAddress;import org.apache.poi.xssf.usermodel.XSSFWorkbook;/** * 导入Excel文件（支持“XLS”和“XLSX”格式） *  * @author JiangP * @version 2017-03-20 */public class ImportExcel {/** * 工作薄对象 */private Workbook wb;/** * 工作表对象 */private Sheet sheet;/** * 标题行号 */private int headerNum;/** * 构造函数 *  * @param path *            导入文件对象 * @param headerNum *            标题行号，数据行号=标题行号+1 * @param sheetIndex *            工作表编号 * @throws InvalidFormatException * @throws IOException */public ImportExcel(String fileName, InputStream is, int headerNum,int sheetIndex) throws InvalidFormatException, IOException {if (StringUtils.isBlank(fileName)) {throw new RuntimeException("导入文档为空!");} else if (fileName.toLowerCase().endsWith("xls")) {this.wb = new HSSFWorkbook(is);} else if (fileName.toLowerCase().endsWith("xlsx")) {this.wb = new XSSFWorkbook(is);} else {throw new RuntimeException("文档格式不正确!");}if (this.wb.getNumberOfSheets() < sheetIndex) {throw new RuntimeException("文档中没有工作表!");}this.sheet = this.wb.getSheetAt(sheetIndex);this.headerNum = headerNum;}/** * 获取行对象 *  * @param rownum * @return */public Row getRow(int rownum) {return this.sheet.getRow(rownum);}/** * 获取数据行号 *  * @return */public int getDataRowNum() {return headerNum + 1;}public Workbook getWb() {return wb;}public void setWb(Workbook wb) {this.wb = wb;}/** * 获取最后一个数据行号 *  * @return */public int getLastDataRowNum() {if (headerNum == 0) {return this.sheet.getLastRowNum() + headerNum + 1;}return this.sheet.getLastRowNum() + headerNum;}/** * 获取最后一个列号 *  * @return */public int getLastCellNum() {return this.getRow(headerNum).getLastCellNum();}/** * 判断指定的单元格是否是合并单元格 *  * @param sheet * @param row *            行下标 * @param column *            列下标 * @return */public static boolean isMergedRegion(Sheet sheet, int row, int column) {int sheetMergeCount = sheet.getNumMergedRegions();for (int i = 0; i < sheetMergeCount; i++) {CellRangeAddress range = sheet.getMergedRegion(i);int firstColumn = range.getFirstColumn();int lastColumn = range.getLastColumn();int firstRow = range.getFirstRow();int lastRow = range.getLastRow();if (row >= firstRow && row <= lastRow) {if (column >= firstColumn && column <= lastColumn) {return true;}}}return false;}/** * 获取合并单元格的值 *  * @param sheet * @param row * @param column * @return */public static String getMergedRegionValue(Sheet sheet, int row, int column,String shuJuLeiXing,String shuJuGeShi) {int sheetMergeCount = sheet.getNumMergedRegions();for (int i = 0; i < sheetMergeCount; i++) {CellRangeAddress ca = sheet.getMergedRegion(i);int firstColumn = ca.getFirstColumn();int lastColumn = ca.getLastColumn();int firstRow = ca.getFirstRow();int lastRow = ca.getLastRow();if (row >= firstRow && row <= lastRow) {if (column >= firstColumn && column <= lastColumn) {Row fRow = sheet.getRow(firstRow);Cell fCell = fRow.getCell(firstColumn);return getCellValue(fCell,shuJuLeiXing, shuJuGeShi);}}}return null;}/** * 获取非合并单元格的值 *  * @param sheet * @param row * @param column * @return */public static String getRichStringCellValue(Sheet sheet, int row,int column,String shuJuLeiXing , String shuJuGeShi) {Row sheetRow = sheet.getRow(row);String cellValue = "";if(sheetRow!=null){cellValue = getCellValue(sheetRow.getCell(column),shuJuLeiXing, shuJuGeShi);}return cellValue;}/** * 获取单元格的值 *  * @param sheet * @param row * @param column * @return */public static String getCellValue(Sheet sheet, int row, int column,String shuJuLeiXing,String shuJuGeShi) {String cellValue = "";boolean b = isMergedRegion(sheet, row, column);if (b) {cellValue = getMergedRegionValue(sheet, row, column,shuJuLeiXing, shuJuGeShi);} else {cellValue = getRichStringCellValue(sheet, row, column,shuJuLeiXing, shuJuGeShi);}return cellValue;}/** * 获取单元格的值 *  * @param cell * @return */public static String getCellValue(Cell cell,String shuJuLeiXing, String shuJuGeShi) {if (cell == null)return "";if (cell.getCellType() == Cell.CELL_TYPE_STRING) {return cell.getStringCellValue();} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {return String.valueOf(cell.getBooleanCellValue());} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {return cell.getCellFormula();} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {try{if (shuJuLeiXing!=null && shuJuLeiXing.trim().equals("30")) {DecimalFormat df=new DecimalFormat(shuJuGeShi); //数值类型String cellValue = df.format(cell.getNumericCellValue());return cellValue;} else if(shuJuLeiXing!=null && shuJuLeiXing.trim().equals("20")) {SimpleDateFormat dateformat = new SimpleDateFormat(shuJuGeShi);Date dt = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());// 获取成DATE类型String cellValue = dateformat.format(dt);return cellValue;}}catch(Exception e){e.printStackTrace();}}return "";}public static boolean isCellDateFormatted(Cell cell) {if (cell == null)return false;boolean bDate = false;double d = cell.getNumericCellValue();if (DateUtil.isValidExcelDate(d)) {CellStyle style = cell.getCellStyle();if (style == null)return false;int i = style.getDataFormat();String f = style.getDataFormatString();f = f.replaceAll("[\"|\']","").replaceAll("[年|月|日|时|分|秒|毫秒|微秒]", "");  bDate = DateUtil.isADateFormat(i, f);}return bDate;}/** * 行号转换 *  * @param rowString * @return */public static int rowFormat(String rowString) throws Exception {try {int row = Integer.valueOf(rowString.trim());row = row - 1;return row;} catch (NumberFormatException exception) {throw new Exception("行号转换异常!");}}/** * 列号转换 *  * @param columString * @return */public static int columnFormat(String columString) throws Exception {try {char[] c = columString.trim().toUpperCase().toCharArray();int colum = 0;int length = c.length;for (int i = 0; i < c.length; i++) {length = length - i;int charNum = (int) c[i];colum = colum + (charNum - 64)* ((int) Math.pow(26, length - 1));}colum = colum - 1;return colum;} catch (Exception e) {throw new Exception("列号转换异常!");}}public static String saveTempPic(String realPath,Workbook workbook) throws Exception {@SuppressWarnings("unchecked")List<PictureData> picList = (List<PictureData>) workbook.getAllPictures();// 获取工作表中所有图片if (picList.size() == 0) {return null;}if (picList.size() > 1) {throw new Exception("工作表中的照片超过一张!");}PictureData pic = picList.get(0); // 一张图片的情况下// 将此图片保存到临时目录byte[] buf = pic.getData();String ext = pic.suggestFileExtension();String fileName = UUID.randomUUID() + "." + ext;String dir = realPath + "assets\\data\\"; // 临时文件路径String filePath = dir + File.separator + fileName; // 临时存放路径BufferedOutputStream bos = null;FileOutputStream fos = null;File file = null;try {File directory = new File(dir);if (!directory.exists()) {// 创建目录boolean result = directory.mkdirs();if (!result) {throw new Exception("创建临时目录失败");}}file = new File(filePath);fos = new FileOutputStream(file);bos = new BufferedOutputStream(fos);bos.write(buf);} catch (Exception e) {e.printStackTrace();} finally {if (bos != null) {try {bos.close();} catch (IOException e) {e.printStackTrace();}}if (fos != null) {try {fos.close();} catch (IOException e) {e.printStackTrace();}}}return filePath;}public static void delTempPic(String filePath) throws Exception {File tempFile = new File(filePath);if (tempFile.exists()) {boolean result = tempFile.delete();if (!result) {throw new Exception("删除临时图片失败！");}}}}



                                             
        0        0           
	
				
		









 2016制造业行业数据
    		2016制造业行业数据



原创粉丝点击



		

热门IT博客


淘宝客服组长工作计划淘宝客服组长工作计划




苹果cms自带采集插件苹果cms自带采集插件




js设置margintop




淘宝店铺如何靠前




阿尔法家家具软件




球鞋淘宝哪里物美价廉




粉笔做题数据怎么清零




网络精灵中文版




淘宝未发货退款流程




行知学院校歌浙师大




曾国藩 知其雄 守其雌




python timedelta




手机淘宝5.6.0版本




java map 是否存在key




网络热歌




周边手办数据




华为网络认证考试




神奇宝贝坂木老大知乎




特惠淘宝网站




三维电气设计软件



		

热门问题
    老师的惩罚
    人脸识别
    我在镇武司摸鱼那些年
    重生之率土为王
    我在大康的咸鱼生活
    盘龙之生命进化
    天生仙种
    凡人之先天五行
    春回大明朝
    姑娘不必设防，我是瞎子
    卧室竖梁压顶的7种破解方法
    围墙压顶
    气体顶压消防给水设备
    油压千斤顶价格
    泰山压顶而面不改色
    洪荒之黑云压顶 我是圣人
    横梁压顶的正确图片
    压顶石
    千斤顶压不起来为什么
    窗台压顶做法图片
    客厅横梁压顶的7种破解方法
    吸顶喇叭定压定阻
    压龙大仙
    九尾老狐敢压龙
    把皇后压在龙椅上
    龙郓煤业冲击地压事故
    龙拳小子压腿哭的图片
    压分龙机
    皇上被臣子压在龙椅上肉雾
    厌奶
    厌世
    厌奶期
    厌笔萧生作品
    百听不厌经典歌曲50首
    百听不厌经典歌曲
    天牧厌笔萧生
    妻不厌诈娄爷我错
    悲观厌世
    二个月宝宝厌奶期绝招
    江厌离图片
    厌组词
    宝宝厌奶期
    厌囊肿
    百听不厌经典老歌
    相看两不厌只有敬亭山
    婴儿厌奶
    厌世脸
    不厌其烦意思
    相看两不厌
    百听不厌fuiween
    百听不厌fuiwen


	










程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里