APACHE POI读取Excel 2007

#1. 使用apache poi 3.11 读取Excel 2007内容

　　##1.使用到的jar

　　##2.出现的问题:org.apache.poi.POIXMLException: java.lang.reflect.InvocationTargetException

　　　　原因:         与xbeans-2.2.0冲突.

　　　　解决方案 : 删除xbeans-2.2.0

#2.使用jxl.jar 读取Excel 2003 的内容  . (PS:jxl.jar 不能读取Excel2007的内容)

package com.sgai.web.servlet;

import java.io.File;
import java.io.IOException;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import org.junit.Test;



public class InsertData {
	/**
	 * 
	 * @param path
	 *            要解析的excel文件路径
	  * @param dataTable
	 *            要写入到数据库中的表名          
	 * @throws BiffException
	 * @throws IOException
	 */
	public void insert(String path,String dataTable) throws BiffException, IOException {
		
		File file = new File(path); 		
		// 创建新的Excel 工作簿
		Workbook rwb = null;
		rwb = Workbook.getWorkbook(file);
		
		// 得到工作簿中的第一个表索引即为excel下的sheet1,sheet2,sheet3...
		Sheet sheet = rwb.getSheets()[0];
		int rsColumns = sheet.getColumns();// 列数
		int rsRows = sheet.getRows();// 行数
		String simNumber = "" ;//每个单元格中的数据
		
		String sql = "INSERT INTO ";
		String pltColumn = "NEXT_PRODUCT_LINE,MAIN_CATEGORY,POSITION,COILS_ID,STORAGE_AREA,ROW_S,COIUMN_S,FLOOR_S,REAL_SITUATION,REMARK,SAMPING_RULE,SF_QUALITY,P_IN_CTRL,FINAL_TEMP,CURL_TEMP,QF_INTENSITY,IN_CODE,C_CONTENT,THICKNESS,WIDTH,WEIGHT,LENGTH,Z_EXIT_THICK,Z_EXIT_WIDTH,SCREWDOWN,NOMINAL_THICK,PUBLIC_WIDTH,F5_LENGTH,MIN_TARGET_WIDTH,MAX_TARGET_WIDTH,WIDTH_CD,TRIM_DEMAND,MIN_WEIGHT,MAX_WEIGHT,PACK_CODE,EX_CODE,USAGE,CUSTOM,FINAL_USER,SHIPING_TYPE,CX_PLAN,BILLDATE,PROD_ORDER_NO,PLAN_TIMES,GD_COIL_TYPE,CREATE_DATE";
		String calColumn = "CX_PLAN,SEQ_POSITION,PROD_ORDER_NO,SELL_ORDER_NO,ROW_ITEM,PCOILID,OUT_LINE,HEAT_TEMP,IN_CODE,EX_CODE,DEFECT_DESC,ELONGATION,ST,MAT_THICK,MAT_WIDTH,MAT_WEIGHT,LENGTH,C,ORDER_THICK,ORDER_WIDTH,ST_MODE,ST_FORMULA,MIN_WIDTH_CTRL,MAX_WIDTH_CTRL,MIN_SEPARATE,MAX_SEPARATE,SURFACE_DEMAND,SURFACE_ORDER,DEVIATION,OIL_CATEGORY,OIL_TYPE,OIL_UP,OIL_DOWN,ROUGH_MAX,ROUGH_MIN,QF_STRONG_MAX,QF_STRONG_MIN,INTERNAL_SIZE,PACK_CODE,CUSTOM,FINAL_USER,USAGE,ORDER_TYPE,USER_SP_DEMAND,REMARK,XTZHKWZ_Q,XTZHKWZ_H,XTZHKWZ_L,XTZHKWZ_C,REAL_SITUATION,SHIP,ARRIVE,USE_TIME,GD_COIL_TYPE,CREATE_DATE";
		String cglColumn = "SEQ_POSITION,CX_PLAN,PROD_ORDER_NO,SELL_ORDER_NO,ROW_ITEM,PCOILID,OUT_LINE,MAT_TYPE,ORDRE_MAT_TYPE,MAT_DEFECT,PATCM_DEVIATE,MAT_THICK,MAT_WIDTH,MAT_WEIGHT,LENGTH,C,ORDER_THICK,ORDER_WIDTH,ST_MODE,ST_FORMULA,MIN_WIDTH_CTRL,MAX_WIDTH_CTRL,MIN_SEPARATE,MAX_SEPARATE,SURFACE_DEMAND,INNER_CTRL,ZN_THICK,HEAT_TEMP,SUG_GZ_GJ,GZ_ELONGATION,LJ_ELONGATION,SUF_HANDLE,OIL_TYPE,OIL_UP,OIL_DOWN,ROUGH_MAX,ROUGH_MIN,QF_STRONG_MAX,QF_STRONG_MIN,INTERNAL_SIZE,PACK_CODE,CUSTOM,FINAL_USER,USAGE,ORDER_TYPE,USER_SP_DEMAND,USE_TIME,XTZHKWZ_Q,XTZHKWZ_H,XTZHKWZ_L,XTZHKWZ_C,REAL_SITUATION,BILLDATE,SHIP,GD_COIL_TYPE,CREATE_DATE";
		
		DBUtils jdbc=new DBUtils();
		
		if(dataTable == "" || dataTable == null ){
			jdbc.closeStmt();
			jdbc.closeConnection();
			return;
		}else if (dataTable == "PLTCM"){
			sql += "PRODUCTION_PLAN_PLTCM ("+pltColumn+")";
			rsColumns = 46; //读Excel 46列 酸轧有46个字段 , 固定列数为了防止读空列
		}else if (dataTable == "CAL"){
			sql += "PRODUCTION_PLAN_CAL ("+ calColumn+")";
			rsColumns = 55;
		}else if (dataTable == "CGL1"){
			sql +="PRODUCTION_PLAN_CGL ("+ cglColumn+")";
			rsColumns = 57;
		}else if (dataTable == "CGL2"){
			sql +="PRODUCTION_PLAN_CGL ("+ cglColumn+")";
			rsColumns = 57;
		}else if (dataTable == "TEST"){
			sql += "PRODUCTION_CAL_TEST (" + calColumn + ")";
			rsColumns = 55;
		}else if (dataTable == "PLTCM_TEST" ){
			sql += "PRODUCTION_PLTCM_TEST ("+pltColumn+")";
			rsColumns = 47;
		}
		for (int i = 1; i < rsRows; i++) {
			String value = "";
			String _sql = "";
			for (int j = 1; j < rsColumns; j++) {
				
				if(j == rsColumns-1){
					value += "TO_DATE('"+sheet.getCell(j, i).getContents()+"','YYYYMMDD'";
				}else{
				value += "\'"+sheet.getCell(j, i).getContents()+"\',";
				}
				}
			
			if(dataTable == "PLTCM_TEST" ){
				_sql =sql + "values (" + value + ")";
			}
			System.out.println(_sql);
			
		}
//		String str="";//拼接要插入的列
//			for (int j = 0; j <rsColumns; j++) {
//				Cell cell = sheet.getCell(j, 0);
//				simNumber = cell.getContents();
//				if(j==rsColumns-1){
//					str +=  simNumber  ;
//				}else{
//					str +=  simNumber+",";
//				}
//				
//			}
//		for (int i = 1; i < rsRows; i++) {
//			
//			String sql = "insert into "+dataTable+"("+str+") values(";//拼接sql
//			System.out.println(str);
//			for (int j = 0; j < rsColumns; j++) {
//				Cell cell = sheet.getCell(j, i);
//				simNumber = cell.getContents();
//				if(j==rsColumns-1){
//					sql += "'"+ simNumber+"'" ;
//				}else{
//					sql +="'"+ simNumber+"',";
//				}
//				
//			}
//			sql += " )";
//			jdbc.executeUpdate(sql);//执行sql
//			
//		}
		jdbc.closeStmt();
		jdbc.closeConnection();
	}

	
	@Test
	public void testInsert(){
		try {
			insert("E:/work/生产计划报表/数据文件/酸轧-26号.xls","PLTCM_TEST");
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

 

转载于:https://www.cnblogs.com/xxx-xxx/p/4366264.html