Java 代码实现读取Excel文件
1.下载下方五个jar包(支持1.7和1.8jdk版本实测)
xmlbeans-2.5.0
poi-scratchpad-3.9
poi-ooxml-schemas-3.9
poi-ooxml-3.9
poi-3.9
2.代码如下:
public static void main(String[] args) throws Exception {
//1、获取工作簿
XSSFWorkbook workbook = new XSSFWorkbook("E:\\txcode.xlsx");
//2、获取工作表
XSSFSheet sheet = workbook.getSheetAt(0);
//3、获取行
int lastRowNum = sheet.getLastRowNum(); //得到有效行
int num=0;
for (int i = 0; i <= lastRowNum; i++) {
XSSFRow row = sheet.getRow(i);
if (row != null) {
short cellNum = row.getLastCellNum(); //获取有效列
String acctNo="";
String acctnoOther="";
String acctName="";
String txAmt="";
String txDirection="";
String sts="";
String prjno="";
String prjname="";
String tradeid="";
String tradeType="";
for (int j = 0; j <= cellNum; j++) {
XSSFCell cell = row.getCell(j);
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING); //设置格式为string
String stringCellValue = cell.getStringCellValue();
// if(stringCellValue.equals("acctno") ||stringCellValue.equals("acctnoOther")||stringCellValue.equals("acctName")||stringCellValue.equals("txAmt")
// ||stringCellValue.equals("txDirection")||stringCellValue.equals("sts")||stringCellValue.equals("prjno")||stringCellValue.equals("prjname")||stringCellValue.equals("tradeid")
// ||stringCellValue.equals("tradeType"));
if(j==1 && !stringCellValue.equals("acctno")){
BigDecimal bigDecimal = new BigDecimal(stringCellValue);
String out = bigDecimal.toPlainString();
acctNo=out;
}
//此处代码为获取指定列的数据(1,3.5.。。。。。。。)
if(j==3 && !stringCellValue.equals("acctnoOther")&& !stringCellValue.equals("")){
BigDecimal bigDecimal = new BigDecimal(stringCellValue);
String out = bigDecimal.toPlainString();
acctnoOther=out;
}
if(j==5 && !stringCellValue.equals("acctName")){
acctName=stringCellValue;
}
if(j==6 && !stringCellValue.equals("txAmt")){
BigDecimal bigDecimal = new BigDecimal(stringCellValue);
String out = bigDecimal.toPlainString();
txAmt=out;
}
if(j==8 && !stringCellValue.equals("txDirection")){
txDirection=stringCellValue;
}
if(j==11 && !stringCellValue.equals("sts")){
sts=stringCellValue;
}
if(j==13 && !stringCellValue.equals("prjno")){
prjno=stringCellValue;
}
if(j==15 && !stringCellValue.equals("prjname")){
prjname=stringCellValue;
}
if(j==18 && !stringCellValue.equals("tradeid")){
BigDecimal bigDecimal = new BigDecimal(stringCellValue);
String out = bigDecimal.toPlainString();
tradeid=out;
}
if(j==20 && !stringCellValue.equals("tradeType")){
tradeType=stringCellValue;
}
}
}
//因为工作上的原因,需要将数据全部动态拼接到SQL中,
String sql="UPDATE BANK_STATEMENT SET STS ='"+sts+"',TRADE_TYPE='"+tradeType+"', TRADE_ID='"+tradeid+"' WHERE ACCT_NO='"+acctNo+"' AND ACCT_NAME_OTHER='"+
acctName+"' AND ACCT_NO_OTHER='"+acctnoOther+"' AND TX_AMT='"+txAmt+"' AND TX_DIRECTION='"+txDirection+"' AND PRJ_NAME='"+prjname+
"' AND PRJ_NO='"+prjno+"' AND TX_DATE>='20220413' AND TX_DATE<'20220606';";
num++;
System.out.println(sql);
}
}
System.out.println(num);
}