excel导入数据代码,JS代码库,德仔网

代码语言
代码分类
【JS】 excel导入数据
作者:土豆-地瓜 / 发布于2015/1/22/ 723

	package cn.xfk.util;
	
	import java.io.File;
	import java.io.FileInputStream;
	import java.io.IOException;
	import java.io.InputStream;
	import java.lang.reflect.Field;
	import java.util.ArrayList;
	import java.util.Date;
	import java.util.List;
	
	import org.apache.poi.hssf.usermodel.HSSFWorkbook;
	import org.apache.poi.ss.usermodel.Cell;
	import org.apache.poi.ss.usermodel.CellValue;
	import org.apache.poi.ss.usermodel.DateUtil;
	import org.apache.poi.ss.usermodel.FormulaEvaluator;
	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.junit.Test;
	
	import cn.xfk.model.CardCenter;
	
	/**
	 * 类描述：导入exel工具类
	 * 未经本公司许可，不得以任何方式复制或使用本程序任何部分，
	 * 侵权者将受到法律追究。
	 * @author 安普尚
	 * @date 2013-3-25 下午10:44:40
	 * @comment
	 * @version 1.0
	 */
	public class ExelUtil {
	
	 //第一列开始
	 int start = 0;
	 //最后一列序号
	 int end =0;
	
	
	 @Test
	 public void test() {
	 File file = new File("D:\\cardNum.xlsx");
	 String fileName = file.getName();
	 String prefix = fileName.substring(fileName.lastIndexOf(".")+1);
	 System.out.println(prefix);
	 CardCenter cardCenter = new CardCenter();
	 try {
	 List<CardCenter> exportListFromExcel = (List<CardCenter>) exportListFromExcel(file,prefix,cardCenter);
	 for(CardCenter ca : exportListFromExcel){
	 //System.out.println(ca.getCardNum());
	 System.err.println(ca.getCardPassword());
	 }
	 } catch (IOException e) {
	 // TODO Auto-generated catch block
	 e.printStackTrace();
	 }
	 }
	
	
	 /**
	 * 方法描述：由Excel文件的Sheet导出至List
	 * @param file
	 * @param sheetNum
	 * @return
	 * @throws IOException
	 * @author 安普尚
	 * @date 2013-3-25 下午10:44:26
	 * @comment
	 */
	 public List<?> exportListFromExcel(File file, String fileFormat,Object dtoobj)
	 throws IOException {
	 return exportListFromExcel(new FileInputStream(file), fileFormat,dtoobj);
	 }
	
	 /**
	 * 方法描述：由Excel流的Sheet导出至List
	 * @param is
	 * @param extensionName
	 * @param sheetNum
	 * @return
	 * @throws IOException
	 * @author 安普尚
	 * @date 2013-3-25 下午10:44:03
	 * @comment
	 */
	 public List<Object> exportListFromExcel(InputStream is,String fileFormat,Object dtoobj) throws IOException {
	
	 Workbook workbook = null;
	
	 if (fileFormat.equals(BizConstant.XLS)) {
	 workbook = new HSSFWorkbook(is);
	 } else if (fileFormat.equals(BizConstant.XLSX)) {
	 workbook = new XSSFWorkbook(is);
	 }
	
	 return exportListFromExcel(workbook,dtoobj);
	 }
	
	
	 /**
	 * 方法描述：由指定的Sheet导出至List
	 * @param workbook
	 * @param sheetNum
	 * @return
	 * @author 安普尚
	 * @date 2013-3-25 下午10:43:46
	 * @comment
	 */
	 private List<Object> exportListFromExcel(Workbook workbook ,Object dtoobj) {
	 List<Object> list = new ArrayList<Object>();
	 String[] model = null;
	 Sheet sheet = workbook.getSheetAt(0);
	
	 // 解析公式结果
	 FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
	 int minRowIx = sheet.getFirstRowNum();
	 int maxRowIx = sheet.getLastRowNum();
	 for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) {
	 Object obj = null;
	 if(rowIx==minRowIx){
	 start = sheet.getRow(rowIx).getFirstCellNum();
	 end = sheet.getRow(rowIx).getLastCellNum();
	 }
	 Row row = sheet.getRow(rowIx);
	 StringBuilder sb = new StringBuilder();
	 for (int i = start; i < end; i++) {
	
	 Cell cell = row.getCell(new Integer(i));
	 CellValue cellValue = evaluator.evaluate(cell);
	 if (cellValue == null) {
	 sb.append(BizConstant.SEPARATOR+null);
	 continue;
	 }
	 // 经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了
	 // 其余数据类型，根据官方文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.html
	 switch (cellValue.getCellType()) {
	 case Cell.CELL_TYPE_BOOLEAN:
	 sb.append(BizConstant.SEPARATOR + cellValue.getBooleanValue());
	 break;
	 case Cell.CELL_TYPE_NUMERIC:
	 // 这里的日期类型会被转换为数字类型，需要判别后区分处理
	 if (DateUtil.isCellDateFormatted(cell)) {
	 sb.append(BizConstant.SEPARATOR + cell.getDateCellValue());
	 } else {
	 sb.append(BizConstant.SEPARATOR + cellValue.getNumberValue());
	 }
	 break;
	 case Cell.CELL_TYPE_STRING:
	 sb.append(BizConstant.SEPARATOR + cellValue.getStringValue());
	 break;
	 case Cell.CELL_TYPE_FORMULA:
	 break;
	 case Cell.CELL_TYPE_BLANK:
	 break;
	 case Cell.CELL_TYPE_ERROR:
	 break;
	 default:
	 break;
	 }
	 }
	 if(rowIx==minRowIx){
	 String index = String.valueOf(sb);
	 String realmodel =index.substring(1, index.length());
	 model =realmodel.split(",");
	 }else{
	 String index = String.valueOf(sb);
	 String realvalue =index.substring(1, index.length());
	 String[] value =realvalue.split(",");
	 //字段映射
	 try {
	 dtoobj =dtoobj.getClass().newInstance();
	 } catch (InstantiationException e) {
	 e.printStackTrace();
	 } catch (IllegalAccessException e) {
	 e.printStackTrace();
	 }
	 obj = reflectUtil(dtoobj,model,value);
	 list.add(obj);
	
	 }
	
	 }
	 return list;
	 }
	
	 /**
	 * 方法描述：字段映射赋值
	 * @param objOne
	 * @param listName
	 * @param listVales
	 * @return
	 * @author 安普尚
	 * @date 2013-3-25 下午10:53:43
	 * @comment
	 */
	 @SuppressWarnings("deprecation")
	 private Object reflectUtil(Object objOne, String[] listName,
	 String[] listVales) {
	 Field[] fields = objOne.getClass().getDeclaredFields();
	 for (int i = 0; i < fields.length; i++) {
	 fields[i].setAccessible(true);
	 for (int j = 0; j < listName.length; j++) {
	 if (listName[j].equals(fields[i].getName())) {
	 try {
	 if (fields[i].getType().getName().equals(java.lang.String.class.getName())) {
	 // String type
	 if(listVales[j]!=null){
	 fields[i].set(objOne, listVales[j]);
	 }else{
	 fields[i].set(objOne, "");
	 }
	
	 } else if (fields[i].getType().getName().equals(java.lang.Integer.class.getName())
	 || fields[i].getType().getName().equals("int")) {
	 // Integer type
	 if(listVales[j]!=null){
	 fields[i].set(objOne, (int)Double.parseDouble(listVales[j]));
	 }else{
	 fields[i].set(objOne, -1);
	 }
	
	 }else if(fields[i].getType().getName().equals("Date")){
	 //date type
	 if(listVales[j]!=null){
	 fields[i].set(objOne, Date.parse(listVales[j]));
	 }
	 }else if(fields[i].getType().getName().equals("Double")
	 ||fields[i].getType().getName().equals("float")){
	 //double
	 if(listVales[j]!=null){
	 fields[i].set(objOne, Double.parseDouble(listVales[j]));
	 }else{
	 fields[i].set(objOne, 0.0);
	 }
	
	 }
	 } catch (IllegalArgumentException e) {
	 e.printStackTrace();
	 } catch (IllegalAccessException e) {
	 e.printStackTrace();
	 }
	 break;
	 }
	 }
	 }
	
	 return objOne;
	
	 }
	
	
	
	}
试试其它关键字
　excel　　导入数据　
同语言下
可能有用的
土豆-地瓜贡献的其它代码(4)