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>
