一起学java

 

对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 虫子 阅读(587) 评论(0)  编辑  收藏


只有注册用户登录后才能发表评论。
网站导航:
博客园   IT新闻   Chat2DB   C++博客   博问  
 

导航

统计

常用链接

留言簿(2)

随笔档案

搜索

最新评论

阅读排行榜

评论排行榜