对Excel表格的读写操作

由于经常要涉及到对Excel的操作,而且数据量都比较大,所以开发一些代码来代替人工操作就显得非常必要了.
在晚上找了好久,发现支持对Excel操作的第三方jar包还是不少的,当时由于时间紧就选择了个jexcelapi_2_6_6.tar.gz,后来在使用的过程中发现问题还是不少.
1,对Excel表格好像对文本(就是单元格格式是文本)支持比较好,其它很容易出问题
2,只支持Excel2007版本之前的版本,暂不支持2007,有待改进.
3,数据量过大很容易造成内存溢出(有解决的办法,稍候)

package com.infothunder.drm.oma1.excel;

import java.io.BufferedWriter;

import java.io.File;

import java.io.FileWriter;

import java.io.IOException;

import jxl.Sheet;

import jxl.Workbook;

import jxl.read.biff.BiffException;

import jxl.write.Label;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import jxl.write.WriteException;

import jxl.write.biff.RowsExceededException;

import com.infothunder.drm.oma1.makeDcf.biz.ToPinyin;

import com.infothunder.drm.util.ByteArrayUtil;

public class ExcelToSongExcel{

/**

* @param args

public static void main(String[] args){

String srcFile = args[0];

String decFile = args[1];

String sqlFile = args[2];

toSongExcelFile(srcFile, decFile, sqlFile);

}

public static void toSongExcelFile(String srcFile, String decFile, String sqlFile){

String f1 = srcFile;

String f2 = decFile;

String f3 = sqlFile;

try{

Workbook workbook = Workbook.getWorkbook(new File(f1));

WritableWorkbook hw = Workbook.createWorkbook(new File(f2));

Sheet[] sheets = workbook.getSheets();

//StringBuffer sb = new StringBuffer();

FileWriter fw = new FileWriter(f3, true);

BufferedWriter bw = new BufferedWriter(fw);

System.out.println("sheet工作簿的数量是："+sheets.length);

for(int i=0;i<sheets.length;i++){

Sheet sheet = sheets[i];

String she = "sheet"+i;

WritableSheet ws = hw.createSheet(she, i);

//int columns = sheet.getColumns(); //检查表的列数

int rows = sheet.getRows(); //行数

System.out.println(rows);

for(int j=1;j<rows;j++){

String id = sheet.getCell(14, j).getContents();

ChangString.checkId(id);

Label l0 = new Label(0,j-1,id); //0

ws.addCell(l0);

Label l1 = new Label(1,j-1,id); //1

ws.addCell(l1);

String musicName = sheet.getCell(2, j).getContents();

String fileName = ToPinyin.getFormattedLetterTitle(musicName);

Label l4 = new Label(4,j-1,fileName); //4

ws.addCell(l4);

String filename = sheet.getCell(1, j).getContents();

Label l5 = new Label(5,j-1,filename); //5

ws.addCell(l5);

String artist = sheet.getCell(4, j).getContents();

String art = ToPinyin.getFormattedLetterTitle(artist);

Label l6 = new Label(6,j-1,art); //6

ws.addCell(l6);

Label l7 = new Label(7,j-1,artist); //7

ws.addCell(l7);

String singerType = sheet.getCell(5, j).getContents();

String st = ChangString.changSingerType(singerType);

//NumberCell l8 = new NumberCell(8,j,st);

Label l8 = new Label(8,j-1,st); //8

ws.addCell(l8);

String district = sheet.getCell(6, j).getContents();

String dist = ChangString.changDistrict(district);

Label l9 = new Label(9,j-1,dist);

ws.addCell(l9);

String genre = sheet.getCell(9, j).getContents();

String style = ChangString.changGenre(genre);

Label l12 = new Label(12,j-1,style); //12

ws.addCell(l12);

String time = sheet.getCell(12, j).getContents();

int length = Integer.parseInt(time);

Label l14 = new Label(14,j-1,time); //14

ws.addCell(l14);

String language = sheet.getCell(11, j).getContents();

String lang = ChangString.changLanguage(language);

Label l15 = new Label(15,j-1,lang); //15

ws.addCell(l15);

byte[] b = String.valueOf(Math.random()).getBytes();

b = ByteArrayUtil.MD5Jdk(b);

String ms = ByteArrayUtil.byteArray2HexString(b);

Label l16 = new Label(16,j-1,ms);

ws.addCell(l16);

String CPID = sheet.getCell(20, j).getContents();

Label l17 = new Label(17,j-1,CPID);

ws.addCell(l17); //17 CPID

String contentId = sheet.getCell(21, j).getContents();

Label l18 = new Label(18,j-1,contentId); //18 ContenId

ws.addCell(l18);

String ringId = sheet.getCell(22, j).getContents();

Label l19 = new Label(19,j-1,ringId);

ws.addCell(l19);

String price = sheet.getCell(23,j).getContents();

Label l20 = new Label(20,j-1,price);

String amt = "200";

price = ChangString.getPrice(price);

if(price=="0"||price==null||price.equals("")){

amt = "200";

}else{

amt = price;

}

System.out.println(price);

ws.addCell(l20); //

String startDate = sheet.getCell(24,j).getContents();

startDate = ChangString.getSimpleDate(startDate);

String endDate = sheet.getCell(25,j).getContents();

endDate = ChangString.getSimpleDate(endDate);

Label l21 = new Label(21,j-1,startDate);

ws.addCell(l21);

Label l22 = new Label(22,j-1,endDate);

ws.addCell(l22);

String DL = sheet.getCell(16,j).getContents(); //以下都是渠道ID及其关联

Label l23 = new Label(23,j-1,DL);

ws.addCell(l23);

String CH = sheet.getCell(17,j).getContents();

Label l24 = new Label(24,j-1,CH);

ws.addCell(l24);

String CCH = sheet.getCell(18,j).getContents();

Label l25 = new Label(25,j-1,CCH);

ws.addCell(l25);

String BID = sheet.getCell(19,j).getContents();

Label l26 = new Label(26,j-1,BID);

ws.addCell(l26);

String publishDate = sheet.getCell(10, j).getContents(); //publishDate

String pd = ChangString.changPublicDate(publishDate, BID);

Label l13 = new Label(13,j-1,pd);//13

ws.addCell(l13);

String dir = sheet.getCell(0, j).getContents();

dir = ChangString.checkDir(dir);

Label l27 = new Label(27,j-1,dir);

ws.addCell(l27);

String contentDescription = (musicName + time).toString();

String mediaPath = "resource2/dcf"+BID+"/"+CPID+"/";

String sql = "insert into tsongs"

+ "(id,contentName,tcp_id,singerName,genre,publishDate,district,mediaPath,"

+ "mediaFile,mediaType,aesKey,startDate,endDate,sid,regTime,modTime,"

+ "duration,language,state,keyword,contentId,ringId,amt,contentDescription)"

+ " values ('"

+ id + "','" + musicName + "','" + CPID + "','" + artist + "'," + style + ",'"

+ pd+ "'," + dist + ",'" + mediaPath + "','" + id + "',0," + "'" + ms + "','"

+ startDate + "','" + endDate + "','" + id + "',now()," + "now()," + length + "," + lang + ","

+ "1,'" + fileName + "','" + contentId + "','" + ringId + "'," + amt + ",'" + contentDescription + "');";

System.out.println(sql);

bw.write(sql);

bw.newLine();

bw.flush();

}

hw.write();

hw.close();

}catch(BiffException ex){

ex.printStackTrace();

}catch(IOException ex){

ex.printStackTrace();

}catch(RowsExceededException ex){

ex.printStackTrace();

}catch(WriteException ex){

ex.printStackTrace();

}catch(Exception ex){

ex.printStackTrace();

}

对于上面的代码如果操作表格量太大的话可以在命令前加上
java -Xmn128M -Xms512M -Xmx1024M -XX:PermSize=64M -XX:MaxPermSize=128m -classpath......
意思上指定内存最大128M
希望有支持Excel2007jar的朋友跟我说下,我好对代码进行改造,怎样更好支持非文本的单元格,谢谢大家的交流.

posted on 2008-06-09 16:33 虫子阅读(578) 评论(0) 编辑收藏

新用户注册刷新评论列表


只有注册用户登录后才能发表评论。




网站导航: 博客园 IT新闻知识库 C++博客博问管理

一起学java

对Excel表格的读写操作

导航

统计

常用链接

留言簿(2)

随笔档案

搜索

最新评论

阅读排行榜

评论排行榜