如何用Java實(shí)現(xiàn)把excel表中的數(shù)據(jù)導(dǎo)入到mysql數(shù)據(jù)庫已有的表中

1、如何用Java實(shí)現(xiàn)把excel表中的數(shù)據(jù)導(dǎo)入到mysql數(shù)據(jù)庫已有的表中？懸賞分：15|解決時(shí)間：2010-12-2313:53|提問者：quce227如何用Java實(shí)現(xiàn)把excel表中的數(shù)據(jù)導(dǎo)入到mysql數(shù)據(jù)庫已有的表中？數(shù)據(jù)庫中表的字段已定好問題補(bǔ)充：主要是excel中的字段和數(shù)據(jù)庫表中的字段匹配然后批量導(dǎo)入（插入），能否給一個(gè)實(shí)例商最佳答案java讀excel還是比較方便簡單的，原理就是，先用java讀取excel,然后,一行行的寫入數(shù)據(jù)庫，字段的話，你自己程序里面寫就行了，給你個(gè)例子：從Excel讀取數(shù)據(jù)，生成新的Excel,以及修改Excelpackagecommon.util;i

2、mportjxl.*;importjxl.format.UnderlineStyle;importjxl.write.*;importjxl.write.Number;importjxl.write.Boolean;importjava.io.*;/* CreatedbyIntelliJIDEA.* User:xl* Date:2005-7-17* Time:9:33:22* TochangethistemplateuseFile|Settings|FileTemplates.*/publicclassExcelHandlepublicExcelHandle()/*讀取Excel* param

3、filePath* /publicstaticvoidreadExcel(StringfilePath)tryInputstreamis=newFilelnputStream(filePath);Workbookrwb=Workbook.getWorkbook(is);/Sheetst=rwb.getSheet("0")這里有兩種方法獲取sheet表,1為名字，而為下標(biāo)，從0開始Sheetst=rwb.getSheet("original");Cellc00=st.getCell(0,0);/通用的獲取cell值的方式，返回字符串Stringstrc00

4、=c00.getContents();/獲得cell具體類型值的方式if(c00.getType()=CellType.LABEL)LabelCelllabelc00=(LabelCell)c00;strc00=labelc00.getString();)/輸出System.out.println(strc00);/關(guān)閉rwb.close();)catch(Exceptione)e.printStackTrace();)/*輸出Excel* paramos* /publicstaticvoidwriteExcel(OutputStreamos)/* 只能通過API提供的工廠方法來創(chuàng)建Workb

5、ook,而不能使用WritableWorkbook的構(gòu)造函數(shù)，* 因?yàn)轭怶ritableWorkbook的構(gòu)造函數(shù)為protected類型* method(1)直接從目標(biāo)文件中讀取WritableWorkbookwwb=Workbook.createWorkbook(newFile(targetfile);* method(2)如下實(shí)例所示將WritableWorkbook直接寫入到輸出流*/WritableWorkbookwwb=Workbook.createWorkbook(os);/創(chuàng)建Excel工作表指定名稱和位置WritableSheetws=wwb.createSheet(&quo

6、t;TestSheet1",0);/*往工作表中添力口數(shù)據(jù)*/1.添加Label對(duì)象Labellabel=newLabel(0,0,"thisisalabeltest");ws.addCell(label);/添加帶有字型Formatting對(duì)象WritableFontwf=newWritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);WritableCellFormatwcf=newWritableCellFormat(wf);Labellabelcf=newLabel(1,0,"thisi

7、salabeltest",wcf);ws.addCell(labelcf);/添加帶有字體顏色的Formatting對(duì)象WritableFontwfc=newWritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED);WritableCellFormatwcfFC=newWritableCellFormat(wfc);LabellabelCF=newLabel(1,0,"ThisisaLabelCell&quo

8、t;,wcfFC);ws.addCell(labelCF);/2.添加Number對(duì)象NumberlabelN=newNumber(0,1,3.1415926);ws.addCell(labelN);/添加帶有formatting的Number對(duì)象NumberFormatnf=newNumberFormat("#.#");WritableCellFormatwcfN=newWritableCellFormat(nf);NumberlabelNF=newjxl.write.Number(1,1,3.1415926,wcfN);ws.addCell(labelNF);/3.添加

9、Boolean對(duì)象BooleanlabelB=newjxl.write.Boolean(0,2,false);ws.addCell(labelB);/4.添加DateTime對(duì)象jxl.write.DateTimelabelDT=newjxl.write.DateTime(0,3,newjava.util.Date();ws.addCell(labelDT);添加帶有formatting的DateFormat對(duì)象DateFormatdf=newDateFormat("ddMMyyyyhh:mm:ss");WritableCellFormatwcfDF=newWritable

10、CellFormat(df);DateTimelabelDTF=newDateTime(1,3,newjava.util.Date(),wcfDF);ws.addCell(labelDTF);/添加圖片對(duì)象，jxl只支持png格式圖片F(xiàn)ileimage=newFile("f:2.png");Writablelmagewimage=newWritableImage(0,1,2,2,image);ws.addlmage(wimage);/寫入工作表wwb.write();wwb.close();)catch(Exceptione)(e.printStackTrace();)/*

11、 拷貝后，進(jìn)行彳改，其中file1為被copy對(duì)象，file2為修改后創(chuàng)建的對(duì)象* 盡單元格原有的格式化修飾是不能去掉的，我們還是可以將新的單元格修飾加上去，* 以使單元格的內(nèi)容以不同的形式表現(xiàn)* paramfile1* paramfile2* /publicstaticvoidmodifyExcel(Filefile1,Filefile2)(Workbookrwb=Workbook.getWorkbook(filel);WritableWorkbookwwb=Workbook.createWorkbook(file2,rwb);/copyWritableSheetws=wwb.getShee

12、t(0);WritableCellwc=ws.getWritableCell(0,0);/判斷單元格的類型，做出相應(yīng)的轉(zhuǎn)換if(wc.getType=CellType.LABEL)(Labellabel=(Label)wc;label.setString("Thevaluehasbeenmodified");wwb.write();wwb.close();rwb.close();catch(Exceptione)(e.printStackTrace();/測(cè)試publicstaticvoidmain(Stringargs)(try(/讀ExcelExcelHandle.re

13、adExcel("f:/testRead.xls");輸出ExcelFilefileWrite=newFile("f:/testWrite.xls");fileWrite.createNewFile();OutputStreamos=newFileOutputStream(fileWrite);ExcelHandle.writeExcel(os);/修改ExcelExcelHandle.modifyExcel(newfile(""),newFile("");catch(Exceptione)(e.printStackTrace();2.在jsp中做相關(guān)測(cè)試，創(chuàng)建一個(gè)writeExcel.jsp<%response.reset();/清除Bufferresponse.setContentType("application/vnd.ms-excel");FilefileWrite=newFile("f:/testWrite.xls");fileWrite.createNewFile();newFileOutputStream(fileWrite);ExcelHandle.writeExcel(newFileOutputSt