java读取excel文件

package com.murong.ecp.readfile;

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;

import com.murong.ecp.tool.ExcelPoiTools;

public class INDUSTRY_TYPtestPeiXLSX {
public static void main(String[] args) throws FileNotFoundException, IOException {
/*
* 读取平台管理员xlsx文件
* */
try {
PrintStream ps = new PrintStream("C:\Users\123、\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\123、\Desktop\88.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=8;i<rows;i++){
XSSFRow row=st.getRow(i);//读取某一行数据
if(row!=null){
//获取行中所有列数据总列数
cols=row.getLastCellNum();
for(int j=0;j<5;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.murong.ecp.readfile;

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

import com.murong.ecp.tool.ExcelPoiTools;

public class peiReadXlsx {

public static void main(String[] args) throws FileNotFoundException, IOException {
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;

}
}

package com.murong.ecp.readfile;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.TreeMap;

public class XlsxToXlsxTransformUtil {

/*
*
*
* 读取xlsx文件的内容输入到xls文件中
*
* */

public static void main(String[] args) throws FileNotFoundException, IOException {

//输入xlse格式
POIFSFileSystem fs = null;//文件输入流
XSSFWorkbook xwb = null;//设置工作簿
XSSFSheet xsheet = null;//设置表单
XSSFRow xrow = null;//获得行
XSSFCell xcell = null;//行列单元格的值
//输出xls格式
XSSFWorkbook hwb = new XSSFWorkbook();
XSSFSheet hsheet =null;// wb.createSheet("sheet1");
XSSFRow hrow = null;//sheet.createRow(0);
XSSFCell hcell=null;//row0.createCell(0);
//cell0.setCellValue("0000");

SimpleDateFormat df = new SimpleDateFormat("yyyy_MM_dd_HH_mm");//设置日期格式
System.out.println(df.format(new Date()));// new Date()为获取当前系统时间

File inf=new File("C:\Users\123、\Desktop\39.xlsx");
File ouf=new File("C:\Users\123、\Desktop\HYD test 1_0_2 tm="+df.format(new Date())+".xlsx");

boolean sheetflag=true;
int sheetid=0;

boolean rowflag=true;
int rowid =0;
try {
FileInputStream fis = new FileInputStream(inf);//文件输入流
xwb=new XSSFWorkbook(fis);//设置工作簿
System.out.println("表单数="+xwb.getNumberOfSheets());//获取工作表单数目
while(sheetflag){
if(sheetid==xwb.getNumberOfSheets()){break;}
xsheet=xwb.getSheetAt(sheetid);//设置从第几个表单开始读取
if(xsheet==null){
sheetflag=false;
}else{
hsheet=hwb.createSheet(xsheet.getSheetName());//新的工作簿创建工作表单数
sheetid++;
rowid=0;
rowflag=true;
while(rowflag){
hrow=hsheet.createRow(rowid);//新的工作簿表单创建行数
xrow=xsheet.getRow(rowid);//xlsx获得行
if(xrow==null){
rowflag=false;
}else{
rowid++;
for(int c=0;c<30;c++){

xcell=xrow.getCell(c);//xlsx获得行列的内容
if(xcell!=null&&xcell.toString().length()>0){
String a1=xcell.toString().replaceAll(".00", "");//.00替换为空字符串
String a2=a1.replaceAll(".0", "");//.0替换为空字符串
String data=a2.replaceAll("O", "0");//o替换为0
hrow.createCell(c).setCellValue(data);//hrow.createCell(c)创建行列单元格，.setCellValue(data)调用xls的方法将内容写入单元格
}
}
}
}
}
}

//FileOutputStream output=new FileOutputStream(ouf);
//xwb.write(output);
//System.out.println("导出结束");
//output.close();
} catch (Exception e) {
e.printStackTrace();
}
finally{
System.out.println("处理结束。。。。等待导出完成");
FileOutputStream output=new FileOutputStream(ouf);
hwb.write(output);
System.out.println("导出结束");
output.close();
}

}

}

//===========================

1.新建一个maven工程在pom文件中导入

<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>


<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.14</version>
</dependency>
</dependencies>

//java代码

package excel;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintStream;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcel {
public static void main(String[] args) {
poiExcel("C:\Users\Administrator\Desktop\列表方案20190724_V4.xlsx");
}
@SuppressWarnings("resource")
public static void poiExcel(String fileName){
boolean is2007 = true; //判断是否是excel2007格式
if(fileName.endsWith("xlsx")){
is2007 = false;
}
try {
//设置读取的excel内容输出到指定的txt文件中
PrintStream ps=new PrintStream("C:\Users\Administrator\Desktop\one.txt");
System.setOut(ps);
InputStream input = new FileInputStream(fileName); //建立输入流
Workbook wb = null;
//根据文件格式(2003或者2007)来初始化
if(is2007){
wb = new HSSFWorkbook(input);
}
else{
wb = new XSSFWorkbook(input);
}
Sheet sheet = wb.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
for (int i = 0; i <= lastRowNum; i++) {
//具体到某一行
Row row = sheet.getRow(i);
//单元格的数量
int lastCellNum = row.getLastCellNum();
for (int j = 0; j < lastCellNum; j++) {
Cell cell = row.getCell(j);
//根据cell中的类型来输出数据
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
Object result = cell.getNumericCellValue();
// // 判断单元格是否属于日期格式
// if(HSSFDateUtil.isCellDateFormatted(cell)){
// //java.util.Date类型
// result = cell.getDateCellValue();
// }
short format = cell.getCellStyle().getDataFormat();
SimpleDateFormat sdf = null;
if(format != 0){
// format == 176时是当指定单元格格式为日期是都是176
if(format == 14 || format == 31 || format == 57 || format == 58||format == 176){
//日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
}else if (format == 20 || format == 32) {
//时间
sdf = new SimpleDateFormat("HH:mm");
}
double value = cell.getNumericCellValue();
Date date = DateUtil.getJavaDate(value);
result = sdf.format(date);
}
System.out.print(result+" ");
break;
case HSSFCell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue()+" ");
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue()+" ");
break;
case HSSFCell.CELL_TYPE_FORMULA:
System.out.print(cell.getCellFormula()+" ");
break;
case HSSFCell.CELL_TYPE_BLANK:
//System.out.println("为空");
break;
default:
System.out.println("unsuported sell type");
break;
}
}
System.out.println();
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}