读取平台管理员xlsx文件

package com.cn.peitest.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintStream;
import java.text.DecimalFormat;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class INDUSTRY_TYPtestPeiXLSX {
	public static void main(String[] args) throws FileNotFoundException, IOException {
		/*
		 * 读取平台管理员xlsx文件
		 * */
		try {
			PrintStream ps = new PrintStream("C:\Users\pei\Desktop\1111.txt");
			//System.setOut(ps);
			System.out.println("INSERT INTO t_urm_menu(menu_no,menu_nm,menu_typ,menu_lev,module_typ,pmenu_no,sts,btn_flg) VALUES " );
		} catch (FileNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		try{
			String realPath = "C:\Users\Pei\Desktop\开发周期(2)(1)(1).xlsx";
            File fileDes = new File(realPath);
            InputStream str = new FileInputStream(fileDes);
            XSSFWorkbook xwb = new XSSFWorkbook(str);  //利用poi读取excel文件流
            XSSFSheet st = xwb.getSheetAt(0);  //读取sheet的第一个工作表
            int rows=st.getLastRowNum();//总行数
            int cols;//总列数
            int l=0;
            StringBuffer date=new StringBuffer("");
            String bbb="";
            for(int i=0;i<rows;i++){
                XSSFRow row=st.getRow(i);//读取某一行数据
                if(row!=null){
                    //获取行中所有列数据
                    cols=row.getLastCellNum();
                for(int j=0;j<7;j++){
                    XSSFCell cell=row.getCell(j);
                    if(cell!=null){
                    	date=date.append(getStringCellValue(cell));
                    	//bbb=date.substring(0, date.length()-2);
                    }
                }
                l=(date.length())/2;
                System.out.print("('" + date+"',");
                date.setLength(0);
                
                for (int y = 5; y <6; y++) {
                	XSSFCell cell=row.getCell(y);		
					if(cell!=null){
					date =date.append( getStringCellValue(cell));
					}					
				}
				System.out.println("'" + date+"','2','"+l+"','HEALTH','"+bbb+"','1','1'),");
				date.setLength(0);
				bbb="";
                }
            }
        }catch(IOException e){
            e.printStackTrace();  
        }
         
	}
	
	public static String getStringCellValue(XSSFCell cell){
		String aaa="";
		switch (cell.getCellType()) { 
        case XSSFCell.CELL_TYPE_NUMERIC: // 数字 
        	DecimalFormat df = new DecimalFormat("0.00");
            aaa = df.format(cell.getNumericCellValue()).substring(0,2);
            break; 
        case XSSFCell.CELL_TYPE_STRING: // 字符串 
            aaa=cell.getStringCellValue().trim();
            break; 
        case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean 
            aaa=String.valueOf(cell.getBooleanCellValue()); 
            break; 
        case XSSFCell.CELL_TYPE_FORMULA: // 公式 
            aaa=cell.getCellFormula(); 
            break; 
        case XSSFCell.CELL_TYPE_BLANK: // 空值 
        	aaa=""; 
            break; 
        case XSSFCell.CELL_TYPE_ERROR: // 故障 
        	aaa="故障"; 
            break; 
        default: 
        	aaa="未知类型 "; 
            break; 
        }
		return aaa;
		
	}
}

　　

package com.cn.peitest.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.PrintStream;
import java.text.DecimalFormat;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class peiReadXlsx {

	public static void main(String[] args) {
		try {
			/*
			 * 平台项目人员逻辑
			 * */
			//PrintStream ps = new PrintStream("C:\Users\123、\Desktop\66.txt");
			//System.setOut(ps);//设置输出路径/输出到指定的文件中
			System.out.println("INSERT INTO t_urm_menu(menu_no,menu_nm,menu_typ,menu_lev,module_typ,pmenu_no,sts,btn_flg,URL) VALUES ");
		} catch (Exception e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			XSSFWorkbook rwb = null;//定义工作簿
			XSSFSheet incomeSheet = null;//定义工作表
			POIFSFileSystem fs = null;//文件输入流
			XSSFRow row = null;//获得行
			int l = 0;
			String tableFile = "C:\Users\123、\Desktop\88.xlsx";
			FileInputStream fxs = new FileInputStream(new File(tableFile));
			rwb = new XSSFWorkbook(fxs);
			// 获取到Excel中的Sheet
			incomeSheet = rwb.getSheetAt(1);//设置读取文件的第几个模块

			StringBuffer date=new StringBuffer("");
			
			String kk="";
			for (int x = 7; x <= 317; x++) {//循环行
				System.out.print("(" );
				
				/*System.out.print("('99'," );*/
				
				row = incomeSheet.getRow(x);//获得行
				// 读取格 编号
				for (int y = 0; y <= 4; y++) {//循环列
					l++;
					XSSFCell cell = row.getCell(y);	//获取列内容	
					if(cell!=null){		
						date =date.append( getStringCellValue( cell));//对获得的值进行处理
					    kk=date.substring(0,date.length()-2);
					}					
				}
				l=(date.length())/2;//获取等级
				
				System.out.print("'" + date+"',");
				date.setLength(0);//清空
				
				// 读取格 内容
				for (int y = 5; y <= 5; y++) {
					XSSFCell cell = row.getCell(y);	//获取行列单元格的内容	
					if(cell!=null){
					 date =date.append( getStringCellValue(cell));
					}					
				}
				System.out.println("'" + date+"','2','"+l+"','HEALTH','"+kk+"','1','1','future.jsp'),");
				date.setLength(0);
				kk="";
				
			
			}

		} catch (Exception e) {
			System.out.println(e);
		}
	}
	
	
	public static String getStringCellValue(XSSFCell cell){
		String aaa="";
		switch (cell.getCellType()) { 
        case XSSFCell.CELL_TYPE_NUMERIC: // 数字 
        	DecimalFormat df = new DecimalFormat("0.00");
            aaa = df.format(cell.getNumericCellValue()).substring(0,2);
            break; 
        case XSSFCell.CELL_TYPE_STRING: // 字符串 
            aaa=cell.getStringCellValue().trim();
            break; 
        case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean 
            aaa=String.valueOf(cell.getBooleanCellValue()); 
            break; 
        case XSSFCell.CELL_TYPE_FORMULA: // 公式 
            aaa=cell.getCellFormula(); 
            break; 
        case XSSFCell.CELL_TYPE_BLANK: // 空值 
        	aaa=""; 
            break; 
        case XSSFCell.CELL_TYPE_ERROR: // 故障 
        	aaa="故障"; 
            break; 
        default: 
        	aaa="未知类型 "; 
            break; 
        }
		return aaa;
		
	}
}

　　

//========pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>cn.com</groupId>
  <artifactId>excelReadAndWrite</artifactId>
  <version>0.0.1-SNAPSHOT</version>
   <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
	 <dependency>
	    <groupId>org.apache.directory.studio</groupId>
	    <artifactId>org.apache.commons.codec</artifactId>
	    <version>1.8</version>
	</dependency>
	 <dependency>
		<groupId>net.sourceforge.jexcelapi</groupId>
		<artifactId>jxl</artifactId>
		<version>2.6.12</version>
	</dependency>
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml</artifactId>
		<version>3.9</version>
	</dependency>
  </dependencies>
</project>