ExcelToSql

package com.cj.ExcelSql;


import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Stack;


import javax.print.attribute.standard.SheetCollate;


import org.apache.poi.hssf.record.formula.functions.Rows;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


import sun.text.normalizer.IntTrie;


import com.sun.org.apache.bcel.internal.generic.NEW;




/**
 * 将excel表格中结构话的数据 转换为sql语句
 * @author Administrator
 *
 */
public class ExcelToSql {
public static Stack<CellRecord> cellsStack=new Stack<CellRecord>();
public static void main(String[] args) {
String path="D:\\物种分类.xlsx";
ExcelToSql.readExcel(path);
}
/*
 * 读取excle内容
 */
public static void readExcel(String path){
File file=new java.io.File(path);
BufferedInputStream bis;
XSSFWorkbook xss=null;
POIFSFileSystem fs=null;
try {
bis = new BufferedInputStream(new FileInputStream(file));
//fs = new POIFSFileSystem(bis);
xss = new XSSFWorkbook(bis);
} catch (FileNotFoundException e1) {
e1.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
 
//获取某一页
XSSFSheet sheet=xss.getSheetAt(0);
//记录行和列
// 得到总行数
    int rowNum = sheet.getLastRowNum();
for(int i=0;i<rowNum;i++){
Row row=sheet.getRow(i);
//列数现在最多为4
for(int j=0;j<4;j++){
Cell cell=row.getCell(j);
//是否有值
Boolean flag=(cell!=null&&cell.getStringCellValue()!=null&&!"".equals(cell.getStringCellValue()));
//判断是否是合并的单元格 并且有值
if(judgeMargedCell(sheet,i,j)){
//如果栈为空
if(cellsStack.isEmpty()){
CellRecord cr=new CellRecord("01","0",i);
cellsStack.push(cr);
System.out.println("01"+"  0  "+cell.getStringCellValue());
continue;
    }
//如果栈顶有元素
CellRecord cRecord= cellsStack.pop();;
CellRecord cellnew=new CellRecord();
if(cRecord.getCol()==j){
//将该单元格bean推入栈总
//获取新的id
cellnew.setId(idAdd(cRecord.getId()));
//跟栈顶元素在同一级，所以superid是一样 id增加1
cellnew.setSuperid(cRecord.getSuperid());
cellnew.setCol(j);
cellnew.setValue(cell.getStringCellValue());
cellsStack.push(cellnew);
System.out.println(cellnew.getId()+"  "+cellnew.getSuperid()+"  "+cell.getStringCellValue());
                      
}else if(cRecord.getCol()<j){
//说明是栈顶元素的下级菜单
cellnew.setCol(j);
cellnew.setSuperid(cRecord.getId());
//通过栈顶元素的id得到新的id
cellnew.setId( cRecord.getId()+"01");
 
System.out.println(cellnew.getId()+"    "+cellnew.getSuperid()+"    "+cell.getStringCellValue());
cellsStack.push(cellnew);
}else if(cRecord.getCol()>j){
 while(cRecord.getCol()!=j){
cRecord=cellsStack.pop();
 }
 //直到两个菜单是平级菜单
 cRecord=cellsStack.pop();
 //将该单元格bean推入栈总
 //获取新的id
 cellnew.setId(idAdd(cRecord.getId()));
 //跟栈顶元素在同一级，所以superid是一样 id增加1
 cellnew.setSuperid(cRecord.getSuperid());
     cellnew.setCol(j);
     cellsStack.push(cellnew);
 System.out.println(cellnew.getId()+"  "+cellnew.getSuperid()+"  "+cell.getStringCellValue());
                          
  }
    }else if(flag){
   //非合并单元格
   CellRecord cellTemp=cellsStack.peek();
   if(cellTemp.getCol()<j){
    //构造当前非单元格的值
    CellRecord cellNow=new CellRecord();
    cellNow.setSuperid(cellTemp.getId());
    if(cellTemp.getChildIndex()==1){
      cellNow.setId(cellTemp.getId()+"01");
      cellNow.setSuperid(cellTemp.getId());
      cellNow.setChildIndex(1);
    }else{
    int nowIndex=cellTemp.getChildIndex()+1;
    cellNow.setId("0"+cellTemp.getChildIndex()+1);
    cellNow.setSuperid(cellTemp.getId());
    cellTemp.setChildIndex(cellTemp.getChildIndex()+1);
    //把当前单元格从栈中推出 然后再push进去
    cellsStack.pop();
    cellsStack.push(cellTemp);
    }
   
    System.out.println(cellNow.getId()+"    "+cellNow.getSuperid()+"    "+cell.getStringCellValue());
    }
    }
}
System.out.println();
}
 
}
     
/** 
* 获取合并单元格的值 
* @param sheet 
* @param row 
* @param column 
* @return 
*/ 
public String getMergedRegionValue(Sheet sheet, int row, int column) { 
//获取合并单元格的数量
     int sheetMergeCount = sheet.getNumMergedRegions(); 


for (int i = 0; i < sheetMergeCount; i++) { 
CellRangeAddress ca = sheet.getMergedRegion(i); 
int firstColumn = ca.getFirstColumn(); 
int lastColumn = ca.getLastColumn(); 
int firstRow = ca.getFirstRow(); 
int lastRow = ca.getLastRow(); 

if (row >= firstRow && row <= lastRow) { 
if (column >= firstColumn && column <= lastColumn) { 
Row fRow = sheet.getRow(firstRow); 
Cell fCell = fRow.getCell(firstColumn); 

return fCell.getStringCellValue(); 
    } 
} 
} 


return null; 
}
/**
 * 判断是否是合并单元格
 */
public static Boolean judgeMargedCell(Sheet sheet, int row, int column){
//获取合并单元格的数量
    int sheetMergeCount = sheet.getNumMergedRegions(); 


for (int i = 0; i < sheetMergeCount; i++) { 
CellRangeAddress ca = sheet.getMergedRegion(i); 
int firstColumn = ca.getFirstColumn(); 
//int lastColumn = ca.getLastColumn(); 
int firstRow = ca.getFirstRow(); 
// int lastRow = ca.getLastRow(); 
    //判断是否有值
if (row == firstRow&&column == firstColumn) { 
Row row1=sheet.getRow(firstRow);
Cell cell1=row1.getCell(column);
if(cell1.getStringCellValue()!=null&&!"".equals(cell1.getStringCellValue()))
   return true; 
}
}
return false;
     }
/**
 * id加1  比如0205变为0206
 *        03变为04
 */
public static String idAdd(String id){
String temp=id.substring(id.length()-2, id.length());
int newid=Integer.parseInt(temp)+1;
if(id.length()==2){
return "0"+newid;
}
//如果id大于2的
return id.substring(0,id.length()-2);
}
}