jxl处理excel文件 -

yaoyao19851023

浏览: 31512 次
性别:
来自: 大连

最近访客更多访客>>

zhzozh

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

jxl处理excel文件

博客分类：

Excel Xcode XSL J#SQL

haha先留下以后几不用搜索了。

之前都没有用java去操作过excel，项目需要，所以专门去了解了下，看到有用到jxl这个插件，感觉很实用。特地建个备忘录，以便以后自己查询:
public static void main(String[] args) {
try {
 Workbook wb=Workbook.getWorkbook(new File("e:\\test.xls"));
 Sheet sheet=wb.getSheet(0);
 Cell[] cellcol=sheet.getColumn(0);
 for (int j=0;j<cellcol.length;j++) { ①
 if(cellcol[j].getContents().equals("测试酒店1")){
Cell[] cell=sheet.getRow(j);
for(int i=0;i<cell.length;i++){
 System.out.println(i+":"+cell[i].getContents());
}
 }
 }
 wb.close();
} catch (BiffException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 } catch (IOException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 }
}
① 因为头回接触，不知道怎么获取特定行里头的数据，所以用了个很原始的方法，先获取某一特定列的值，然后匹配你所特定的，然后获取那行数据

/**
* Title: 
* Description: 
* Copyright: Copyright (c) 2002
* Company: 
* @author not attributable
* @version 1.0
*/

import java.io.*;
import java.net.URL;
import java.io.File;
import java.util.Date;
import jxl.*;
import jxl.read.biff.*;
import tel.db.DBConnect;
import tel.util.*;
import java.sql.*;
import javax.servlet.http.*;

public class xsl {
public xsl() {

}
public void test(){
 System.out.println("test okccccccccccccc");
}
//readWrite 写入count 表 2004-11-22 update by renchanglin 上传的data.xsl 只有3列
 public void readWrite(String xlsFile ,String date) throws Exception{
 String myxslFile=xlsFile;
 FileInputStream fileOut=null;
 URL url=this.getClass().getResource("../../../upload/data.xls");

 try {
 Workbook workbook = Workbook.getWorkbook(new File(url.getFile()));
 Sheet sheet = workbook.getSheet(0);

int cNum=sheet.getColumns();
int fcode=22;

String user="信息";
String danwei ="信息";
DBConnect dbc = new DBConnect();
dbc.prepareStatement("delete from account");
dbc.executeUpdate();
int rNum=sheet.getRows();
for(int i=1;i<rNum;i++)
 {

 Cell a0=sheet.getCell(0,i);
 // NumberCell b0=(NumberCell)a0;
 Double b0=new Double(a0.getContents());
 fcode=b0.intValue();

 Cell a1=sheet.getCell(1,i);
 user= a1.getContents();
 Cell a2=sheet.getCell(2,i);
 danwei = a2.getContents();
 danwei=danwei.trim();

 dbc.prepareStatement("INSERT INTO account (fcode,user,danwei) VALUES (?,?,?)");
 dbc.setInt(1, fcode);

 dbc.setBytes(2, user.getBytes("GBK"));
 dbc.setBytes(3, danwei.getBytes("GBK"));
 dbc.executeUpdate();
 }

workbook.close();
 }
 catch (BiffException ex2) {
 System.out.println("iiiiiiiiiiiiiiiiii");
 }
 catch (IOException ex2) {
 }

 }

 public void addData(String xlsFile ,String date) throws Exception{
 String myxslFile=xlsFile;
 FileInputStream fileOut=null;
 URL url=this.getClass().getResource("../../../upload/data.xls");
 System.out.println("222222222222222");
 try {
 Workbook workbook = Workbook.getWorkbook(new File(url.getFile()));
 Sheet sheet = workbook.getSheet(0);
// Cell a1 = sheet.getCell(2,4);

// Cell b2 = sheet.getCell(1,1);
// Cell c2 = sheet.getCell(2,1);

int ddh=3402;
float shyzf=35.0f;//月租费
float shthf=0.0f;//市话费
float bdwhf=0.0f;//本地网话费
float qzdchf=0.0f; //长话费
float sjf=0.0f;//数据费
float yhje=0.0f;//优惠费
float qthf=0.0f;//其它话费
float total=0.0f;//化费合计
String fdate=date;//日期

DBConnect dbc = new DBConnect();
int rNum=sheet.getRows();
int cNum=sheet.getColumns();
for(int i=1;i<rNum-1;i++)
 {
 Cell a0=sheet.getCell(0,i);
 // NumberCell b0=(NumberCell)a0;
 Double b0=new Double(a0.getContents());
 ddh=b0.intValue();
 Cell a1=sheet.getCell(2,i);
 Double b1=new Double(a1.getContents());
 shyzf=b1.floatValue();
 Cell a3=sheet.getCell(3,i);
 Double b3=new Double(a3.getContents());
 shthf=b3.floatValue();
 Cell a4=sheet.getCell(4,i);
 Double b4=new Double(a4.getContents());
 bdwhf=b4.floatValue();
 Cell a6=sheet.getCell(6,i);
 Double b6=new Double(a6.getContents());
 qzdchf=b6.floatValue();
 Cell a18=sheet.getCell(18,i);
 Double b18=new Double(a18.getContents());
 sjf=b18.floatValue();

 Cell a26=sheet.getCell(26,i);
 Double b26=new Double(a26.getContents());
 yhje=b26.floatValue();
 float tempdata=0.0f;

 for(int j=7;j<cNum-1;j++)
 {
 Cell a7=sheet.getCell(j,i);
 Double b7=new Double(a7.getContents());
 float f7=b7.floatValue();
 tempdata=tempdata+f7;
 }
 qthf=tempdata-yhje-sjf;
 total=qthf+sjf+shyzf+shthf+bdwhf+qzdchf-yhje;
 // total=total+yzcharg; rcl temp update
 dbc.prepareStatement("INSERT INTO alldata (ddh,shyzf,shthf,bdwhf,qzdchf,sjf,yhje,qthf,total,fdate) VALUES (?,?,?,?,?,?,?,?,?,?)");
 dbc.setInt(1, ddh);
 dbc.setFloat(2, shyzf);
 dbc.setFloat(3, shthf);
 dbc.setFloat(4, bdwhf);
 dbc.setFloat(5,qzdchf);
 dbc.setFloat(6, sjf);
 dbc.setFloat(7, yhje);
 dbc.setFloat(8, qthf);
 dbc.setFloat(9, total);
 dbc.setBytes(10, fdate.getBytes("GBK"));
 dbc.executeUpdate();
 }

workbook.close();
 }
 catch (BiffException ex2) {
 System.out.println("iiiiiiiiiiiiiiiiii");
 }
 catch (IOException ex2) {
 }

 }

 public void initUser(String xlsFile ,String date) throws Exception{
 String myxslFile=xlsFile;
 FileInputStream fileOut=null;
 URL url=this.getClass().getResource("../../../upload/data.xls");
 System.out.println("222222222222222");
 try {
 Workbook workbook = Workbook.getWorkbook(new File(url.getFile()));
 Sheet sheet = workbook.getSheet(0);
// Cell a1 = sheet.getCell(2,4);

// Cell b2 = sheet.getCell(1,1);
// Cell c2 = sheet.getCell(2,1);
int cNum=sheet.getColumns();
int fcode=22;
int xcode=0;
 String user="renchanglin";
 String user1="renchanglin";
 String pcode="888888";
 int fclass=1;
 int cnum=0;
 float ccharg=0.0f;
 float cser=0.0f;
 int jcnum=0;
 float jccharg=0.0f;
 int mcnum=0;
 float mccharg=0.0f;
 int jnum=0;
 float jcharg=0.0f;
 float jser=0.0f;
 int dnum=0;
 float dcharg=0.0f;
 float dser=0.0f;
 int inum=0;
 float icharg=0.0f;
 float iser=0.0f;
 float yzcharg=0.0f;
 float btcharg=0.0f;
 float qtcharg=0.0f;
 float total=0.0f;
 String fdate=date;
DBConnect dbc = new DBConnect();
int rNum=sheet.getRows();
for(int i=1;i<rNum-1;i++)
 {

 Cell a0=sheet.getCell(0,i);
 // NumberCell b0=(NumberCell)a0;
 Double b0=new Double(a0.getContents());
 fcode=b0.intValue();
 Cell a1=sheet.getCell(1,i);
 Double b1=new Double(a1.getContents());
 xcode=b1.intValue();
 Cell a2=sheet.getCell(2,i);
 user= a2.getContents();
 // Cell a3=sheet.getCell(3,i);
 // user1= a3.getContents();
 pcode="888888";
 Cell a5=sheet.getCell(5,i);
 Double b5=new Double(a5.getContents());
 fclass=b5.intValue();
 Cell a6=sheet.getCell(6,i);
 Double b6=new Double(a5.getContents());
 cnum=b6.intValue();
 Cell a7=sheet.getCell(7,i);
 Double b7=new Double(a7.getContents());
 ccharg=b7.floatValue();
 Cell a8=sheet.getCell(8,i);
 Double b8=new Double(a8.getContents());
 cser=b8.floatValue();
 Cell a9=sheet.getCell(9,i);
 Double b9=new Double(a9.getContents());
 jcnum=b9.intValue();
 Cell a10=sheet.getCell(10,i);
 Double b10=new Double(a10.getContents());
 jcharg=b10.floatValue();
 Cell a11=sheet.getCell(11,i);
 Double b11=new Double(a11.getContents());
 mcnum=b11.intValue();
 Cell a12=sheet.getCell(12,i);
 Double b12=new Double(a12.getContents());
 mccharg=b12.floatValue();
 Cell a13=sheet.getCell(13,i);
 Double b13=new Double(a13.getContents());
 jnum=b13.intValue();
 Cell a14=sheet.getCell(14,i);
 Double b14=new Double(a14.getContents());
 jcharg=b14.floatValue();
 Cell a15=sheet.getCell(15,i);
 Double b15=new Double(a15.getContents());
 jser=b15.floatValue();
 Cell a16=sheet.getCell(16,i);
Double b16=new Double(a16.getContents());
dnum=b16.intValue();
Cell a17=sheet.getCell(17,i);
Double b17=new Double(a17.getContents());
dcharg=b17.floatValue();
Cell a18=sheet.getCell(18,i);
Double b18=new Double(a18.getContents());
dser=b18.floatValue();
 Cell a19=sheet.getCell(19,i);
 Double b19=new Double(a19.getContents());
 inum=b19.intValue();
 Cell a20=sheet.getCell(20,i);
 Double b20=new Double(a20.getContents());
 icharg=b20.floatValue();
 Cell a21=sheet.getCell(21,i);
 Double b21=new Double(a21.getContents());
 iser=b21.floatValue();

 Cell a22=sheet.getCell(22,i);
 Double b22=new Double(a22.getContents());
 yzcharg=b22.floatValue();
 Cell a23=sheet.getCell(23,i);
 Double b23=new Double(a23.getContents());
 btcharg=b23.floatValue();
 Cell a24=sheet.getCell(24,i);
 Double b24=new Double(a24.getContents());
 qtcharg=b24.floatValue();
 Cell a25=sheet.getCell(25,i);
 Double b25=new Double(a25.getContents());
 total=b25.floatValue();
 dbc.prepareStatement("INSERT INTO user (fcode,user,pcode) VALUES (?,?,?)");
 dbc.setInt(1, fcode);
 dbc.setBytes(2, user.getBytes("GBK"));
 dbc.setBytes(3, pcode.getBytes("GBK"));
 dbc.executeUpdate();
 }

workbook.close();
 }
 catch (BiffException ex2) {
 System.out.println("iiiiiiiiiiiiiiiiii");
 }
 catch (IOException ex2) {
 }
 }

 public void readWriteqk(String xlsFile ,String date) throws Exception{
String myxslFile=xlsFile;
 FileInputStream fileOut=null;
 URL url=this.getClass().getResource("../../../upload/data.xls");

 try {
 Workbook workbook = Workbook.getWorkbook(new File(url.getFile()));
 Sheet sheet = workbook.getSheet(0);
 int cNum=sheet.getColumns();
 String fdate=date;
DBConnect dbc = new DBConnect();
int rNum=sheet.getRows();
 String text[]=new String[11];
for(int i=1;i<rNum;i++)
 {
 for (int j=0;j<10;j++)
 {
 Cell a0 = sheet.getCell(j, i);
 text[j]=a0.getContents();
 System.out.println("zzzzzzzzz"+text[j]);
 }
 // NumberCell b0=(NumberCell)a0;
 System.out.println("zzzzzzzzz"+text[1].getBytes("GBK"));
 dbc.prepareStatement("INSERT INTO book (idcode,title,author,author_affiliation,classify,serial_title,year,issue,keyword,abstract_C) VALUES (?,?,?,?,?,?,?,?,?,?)");
 for (int j=0;j<10;j++)
 {
 dbc.setString(j+1, text[j]);
 }
 dbc.executeUpdate();
 }
workbook.close();
 }
 catch (BiffException ex2) {
 System.out.println("iiiiiiiiiiiiiiiiii");
 }
 catch (IOException ex2) {
 }
}
}

分享到：