Java读取Excle创建Oracle语句
遇到的问题及解决方案:
异常
jxl. read. biff. BiffException: Unable to recognize OLE stream
at jxl. read. biff. CompoundFile. < init> ( CompoundFile. java: 116 )
at jxl. read. biff. File. < init> ( File. java: 127 )
at jxl. Workbook. getWorkbook ( Workbook. java: 268 )
at jxl. Workbook. getWorkbook ( Workbook. java: 253 )
解决方案
excle 模版:
依赖:
< dependency>
< groupId> net. sourceforge. jexcelapi< / groupId>
< artifactId> jxl< / artifactId>
< version> 2.6 .10 < / version>
< / dependency>
方法:buildCreateOracleTableSQL();
源码
package demo. js. pms. ips. common;
import demo. js. pms. ips. pojo. po. erp. mft. TErpApPaymentDist;
import demo. js. pms. ips. pojo. po. erp. mft. TErpApPaymentHead;
import demo. js. pms. ips. pojo. po. erp. mft. TErpApPaymentLine;
import jxl. Sheet;
import jxl. Workbook;
import java. io. File;
import java. io. FileInputStream;
import java. io. InputStream;
import java. lang. reflect. Field;
import java. lang. reflect. Method;
import java. lang. reflect. ParameterizedType;
import java. lang. reflect. Type;
import java. math. BigDecimal;
import java. util. *;
public class TableExcelParser
{
private static final int PARSE_START_ROW = 1 ;
private static final int PARSE_START_COL = 1 ;
private static final int MAX_TABLE_NAME_LENGTH = 25 ;
private static final String DEFAULT_TABLE_SPACE = "system" ;
public static void main ( String[ ] args) throws Exception
{
buildCreateOracleTableSQL ( ) ;
}
private static void buildOAUserSelectIn ( ) throws Exception
{
InputStream is = new FileInputStream ( new File ( "D:\\tableExcel\\OAUser.xls" ) ) ;
Workbook wb = Workbook. getWorkbook ( is) ;
Sheet sheet = wb. getSheet ( 0 ) ;
int startCol = 2 ;
String format = "select * from oa_user where OAU_EMPLOYEENUMBER in( %s )" ;
String itemFormat = "'%s'" ;
String split = ", " ;
StringBuilder selectSB = new StringBuilder ( ) ;
for ( int row = 1 ; row < sheet. getRows ( ) ; row++ )
{
String colName = sheet. getCell ( startCol, row) . getContents ( ) . trim ( ) ;
if ( colName. isEmpty ( ) )
{
System. out. println ( "读取到空行,停止解析:" + ( row + 1 ) ) ;
break ;
}
colName = colName. replaceAll ( "\\n" , "" ) . replaceAll ( "\\r" , "" ) ;
selectSB. append ( String. format ( itemFormat, colName) ) . append ( split) ;
}
selectSB. delete ( selectSB. length ( ) - split. length ( ) , selectSB. length ( ) - 1 ) ;
System. out. println ( String. format ( format, selectSB. toString ( ) ) ) ;
}
public static void MakeClassStatementForDotNet ( Class clazz )
{
if ( clazz == null)
{
return ;
}
String classStatementFormat = "public class %s{\n%s}" ;
String propertyStatement = "public %s %s { get;set; }\n" ;
StringBuilder propertyStringBuilder = new StringBuilder ( ) ;
Field[ ] fields = clazz. getDeclaredFields ( ) ;
for ( Field field : fields)
{
propertyStringBuilder.