package src;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.record.*;
import org.apache.poi.hssf.model.*;
import org.apache.poi.hssf.util.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
import java.io.FileInputStream;
import java.sql.SQLException;
public class xls2table
{
public static String fileToBeRead="e://lawon//新疆.xls";
public static void main(String argv[])
{
DBConn db=new DBConn();
try
{
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead));
HSSFSheet sheet = workbook.getSheet("Sheet1");
int rows = sheet.getPhysicalNumberOfRows();//行数
db.getConn();
for(int r = 2; r < 232; r++)
{
HSSFRow row = sheet.getRow(r);
if(row != null)
{
int cells = row.getPhysicalNumberOfCells(); //每行单元格数
String[] value = new String[cells];
//System.out.println("单元格数:"+cells);
String num = "";
for(short c = 0; c < cells; c++)
{
HSSFCell cell = row.getCell(c);//单元格
if(cell != null)
{
switch(cell.getCellType())
{
case HSSFCell.CELL_TYPE_FORMULA :
//
break;
case HSSFCell.CELL_TYPE_NUMERIC:
value[c] += (long)cell.getNumericCellValue()+"/t";
break;
case HSSFCell.CELL_TYPE_STRING:
value[c] += cell.getStringCellValue()+"/t";
break;
default:
value[c] +="/t";
}
/*if(value[c]!=null){
value[c] = value[c].substring(4).trim();
if(value[c].indexOf(" ")>0)
value[c] = value[c].substring(0, value[c].indexOf(" ")-1);
if("null".equals(value[c]))
value[c]="";
} */
//System.out.println(c+"value:"+value[c]);
}
}
//下面可以将查找到的行内容用SQL语句INSERT到sqlserver数据库
String sql="insert into lspm_lawhome(num,name,tel,userSum,tax,email,star,infor,city) values('"
+value[0]+"','"+value[1]+"','"+value[2]+"','"+value[3]+"','"+value[4]+"','"+value[5]+"','"
+value[6]+"','"+value[7]+"','新疆')";
System.out.println("sql:"+sql);
db.exeUpdate(sql);
}
}
}
catch(Exception e){
System.out.println("Exception:"+e.getMessage());
}finally{
try {
db.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}