此例子是一个导入导入excel的程序,前题是必须要有poi包(poi,poi-contrib,poi-scratchpad),明确excel文件包含sheet,sheet包括row和cell
1.将包中需要用到的功能做一个程序封装起来,以方便自己使用
ConvertXLS .java
package utils.xls;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.io.FileOutputStream;
import java.io.*;
public class ConvertXLS {
private String xlsfilename="";
private HSSFSheet sheet=null;
private HSSFWorkbook wb = null;
private ConvertXLS(String xlsfilename) {
POIFSFileSystem fs = null;
try {
fs = new POIFSFileSystem(new FileInputStream(xlsfilename));
wb = new HSSFWorkbook(fs);
} catch (IOException ex) {
System.out.println("ERR >> an error in constructor of ConvertXLS !");
ex.printStackTrace();
}
}
//
private ConvertXLS(InputStream is) {
POIFSFileSystem fs = null;
try {
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
} catch (IOException ex) {
System.out.println("ERR >> an error in constructor of ConvertXLS !");
ex.printStackTrace();
}
}
//
private ConvertXLS(String xlsfilename,int flag) {
this.xlsfilename=xlsfilename;
wb=new HSSFWorkbook();
}
//根据sheet号得到sheet
private void getSheet(int sheetunit){
sheet = wb.getSheetAt(sheetunit);
}
//read from xls file
public static ConvertXLS getReadConvertXLS(String xlsfilename){
ConvertXLS cxls=new ConvertXLS(xlsfilename);
return cxls;
}
//
public static ConvertXLS getReadConvertXLS(InputStream is){
ConvertXLS cxls=new ConvertXLS(is);
return cxls;
}
//write into xls file
public static ConvertXLS getWriteConvertXLS(String xlsfilename){
return new ConvertXLS(xlsfilename,0);
}
//以上各方法是为了得到excel文件对象,着情使用
//数据二维表格式的数据集导入到命名的sheet中()
public void setData(String sheetname,String[][] data){
try {
FileOutputStream fileOut = new FileOutputStream(xlsfilename);
HSSFSheet sheet=wb.createSheet(sheetname);
for(short i=0;i<data.length;i++){
HSSFRow row=sheet.createRow(i);
for(short j=0;j<data[0].length;j++){
HSSFCell cell=row.createCell(j);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(data[i][j]);
}
}
wb.write(fileOut);
} catch (FileNotFoundException ex) {
System.out.println("ERR >> an error in method setData of class ConvertXLS !");
ex.printStackTrace();
} catch (IOException ex) {
System.out.println("ERR >> an error in method setData of class ConvertXLS !");
ex.printStackTrace();
}
}
//数据二维表格式的数据集导入到相应索引的sheet中
public String[][] getData(int sheetunit,int columncount){
int firstrow,lastrow,firstcol;
getSheet(sheetunit);
firstrow=sheet.getFirstRowNum();
lastrow=sheet.getLastRowNum();
HSSFRow row=null;
if(lastrow-firstrow > 0)
row = sheet.getRow(firstrow);
if (row == null)
row = sheet.createRow(firstrow);
firstcol=row.getFirstCellNum();
return getData(firstrow,lastrow,firstcol,firstcol+columncount-1);
}
//从相应索引sheet中将表格数据导出并在于二维数组中
public String[][] getData(int sheetunit){
int firstrow,lastrow,firstcol,lastcol;
getSheet(sheetunit);
firstrow=sheet.getFirstRowNum();
lastrow=sheet.getLastRowNum();
HSSFRow row=null;
if(lastrow-firstrow >= 0)
row = sheet.getRow(firstrow);
else
return null;
if (row == null)
row = sheet.createRow(firstrow);
firstcol=row.getFirstCellNum();
lastcol=row.getLastCellNum()-1;
return getData(firstrow,lastrow,firstcol,lastcol);
}
//根据指定的行列将相应索引sheet中的表格数据导出到二维数组中
public String[][] getData(int sheetunit,int startrow,int endrow,int startcolumn,int endcolumn){
getSheet(sheetunit);
return getData(startrow,endrow,startcolumn,endcolumn);
}
//
private String[][] getData(int startrow,int endrow,int startcolumn,int endcolumn){
HSSFRow row=null;
HSSFCell cell=null;
String data[][]=new String[endrow-startrow+1][endcolumn-startcolumn+1];
for(int i=0;i<data.length;i++){
row=sheet.getRow(i);
for(short j=0;j<data[0].length;j++){
cell = row.getCell(j);
data[i][j]=new String();
switch(cell.getCellType()){
case 0 :{
if(cell.getCellStyle().getIndex()==(short)22){
DateFormat d=new SimpleDateFormat("yyyy-MM-dd");
// HSSFDataFormat.getBuiltinFormat(cell.getCellStyle().getDataFormat()));
if(cell.getDateCellValue()!=null)
data[i][j] = d.format(cell.getDateCellValue());
}
else
data[i][j] = Double.toString(cell.getNumericCellValue());
}
break;
default:data[i][j]=cell.getStringCellValue();
break;
}
}
}
return data;
}
}
2.应用例子
ConvertXLS cl=ConvertXLS.getReadConvertXLS("1234.xls");//得到excel文件对象,用于读数据
String s[][]=cl.getData(0);//取得0sheet中的数据
cl=ConvertXLS.getWriteConvertXLS("4321.xls");//得到新excel文件对象,用于写数据
cl.setData("sh",s);//将1234.xls中得到的数据导入到4321.xls中
1.将包中需要用到的功能做一个程序封装起来,以方便自己使用
ConvertXLS .java
package utils.xls;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.io.FileOutputStream;
import java.io.*;
public class ConvertXLS {
private String xlsfilename="";
private HSSFSheet sheet=null;
private HSSFWorkbook wb = null;
private ConvertXLS(String xlsfilename) {
POIFSFileSystem fs = null;
try {
fs = new POIFSFileSystem(new FileInputStream(xlsfilename));
wb = new HSSFWorkbook(fs);
} catch (IOException ex) {
System.out.println("ERR >> an error in constructor of ConvertXLS !");
ex.printStackTrace();
}
}
//
private ConvertXLS(InputStream is) {
POIFSFileSystem fs = null;
try {
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
} catch (IOException ex) {
System.out.println("ERR >> an error in constructor of ConvertXLS !");
ex.printStackTrace();
}
}
//
private ConvertXLS(String xlsfilename,int flag) {
this.xlsfilename=xlsfilename;
wb=new HSSFWorkbook();
}
//根据sheet号得到sheet
private void getSheet(int sheetunit){
sheet = wb.getSheetAt(sheetunit);
}
//read from xls file
public static ConvertXLS getReadConvertXLS(String xlsfilename){
ConvertXLS cxls=new ConvertXLS(xlsfilename);
return cxls;
}
//
public static ConvertXLS getReadConvertXLS(InputStream is){
ConvertXLS cxls=new ConvertXLS(is);
return cxls;
}
//write into xls file
public static ConvertXLS getWriteConvertXLS(String xlsfilename){
return new ConvertXLS(xlsfilename,0);
}
//以上各方法是为了得到excel文件对象,着情使用
//数据二维表格式的数据集导入到命名的sheet中()
public void setData(String sheetname,String[][] data){
try {
FileOutputStream fileOut = new FileOutputStream(xlsfilename);
HSSFSheet sheet=wb.createSheet(sheetname);
for(short i=0;i<data.length;i++){
HSSFRow row=sheet.createRow(i);
for(short j=0;j<data[0].length;j++){
HSSFCell cell=row.createCell(j);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(data[i][j]);
}
}
wb.write(fileOut);
} catch (FileNotFoundException ex) {
System.out.println("ERR >> an error in method setData of class ConvertXLS !");
ex.printStackTrace();
} catch (IOException ex) {
System.out.println("ERR >> an error in method setData of class ConvertXLS !");
ex.printStackTrace();
}
}
//数据二维表格式的数据集导入到相应索引的sheet中
public String[][] getData(int sheetunit,int columncount){
int firstrow,lastrow,firstcol;
getSheet(sheetunit);
firstrow=sheet.getFirstRowNum();
lastrow=sheet.getLastRowNum();
HSSFRow row=null;
if(lastrow-firstrow > 0)
row = sheet.getRow(firstrow);
if (row == null)
row = sheet.createRow(firstrow);
firstcol=row.getFirstCellNum();
return getData(firstrow,lastrow,firstcol,firstcol+columncount-1);
}
//从相应索引sheet中将表格数据导出并在于二维数组中
public String[][] getData(int sheetunit){
int firstrow,lastrow,firstcol,lastcol;
getSheet(sheetunit);
firstrow=sheet.getFirstRowNum();
lastrow=sheet.getLastRowNum();
HSSFRow row=null;
if(lastrow-firstrow >= 0)
row = sheet.getRow(firstrow);
else
return null;
if (row == null)
row = sheet.createRow(firstrow);
firstcol=row.getFirstCellNum();
lastcol=row.getLastCellNum()-1;
return getData(firstrow,lastrow,firstcol,lastcol);
}
//根据指定的行列将相应索引sheet中的表格数据导出到二维数组中
public String[][] getData(int sheetunit,int startrow,int endrow,int startcolumn,int endcolumn){
getSheet(sheetunit);
return getData(startrow,endrow,startcolumn,endcolumn);
}
//
private String[][] getData(int startrow,int endrow,int startcolumn,int endcolumn){
HSSFRow row=null;
HSSFCell cell=null;
String data[][]=new String[endrow-startrow+1][endcolumn-startcolumn+1];
for(int i=0;i<data.length;i++){
row=sheet.getRow(i);
for(short j=0;j<data[0].length;j++){
cell = row.getCell(j);
data[i][j]=new String();
switch(cell.getCellType()){
case 0 :{
if(cell.getCellStyle().getIndex()==(short)22){
DateFormat d=new SimpleDateFormat("yyyy-MM-dd");
// HSSFDataFormat.getBuiltinFormat(cell.getCellStyle().getDataFormat()));
if(cell.getDateCellValue()!=null)
data[i][j] = d.format(cell.getDateCellValue());
}
else
data[i][j] = Double.toString(cell.getNumericCellValue());
}
break;
default:data[i][j]=cell.getStringCellValue();
break;
}
}
}
return data;
}
}
2.应用例子
ConvertXLS cl=ConvertXLS.getReadConvertXLS("1234.xls");//得到excel文件对象,用于读数据
String s[][]=cl.getData(0);//取得0sheet中的数据
cl=ConvertXLS.getWriteConvertXLS("4321.xls");//得到新excel文件对象,用于写数据
cl.setData("sh",s);//将1234.xls中得到的数据导入到4321.xls中