Java Data import in Excel

First, a JXL package is required: http://download.csdn.net/source/292830
1. Generating Excel requires a manual write query that queries the data in the Oracle database and writes it to the Excel file.
2, through Excel to read the data to Oracle, also need to read the contents of the Excel workbook, and then through the INSERT statement into the database operation.

Example:
Includes reading data from Excel, generating new Excel, and modifying Excel

Java code:

 PackageCommon.util;ImportJxl.*; ImportJxl.format.UnderlineStyle;ImportJxl.write.*; ImportJxl.write.Number;ImportJxl.write.Boolean;ImportJava.io.*; /*** Created by IntelliJ idea. * USER:XL * DATE:2005-7-17 * time:9:33:22 * To change this template use File | Settings | File Templates. */  Public classExcelhandle { PublicExcelhandle () {}/*** Read Excel * *@paramFilePath*/  Public Static voidReadexcel (String filePath) {Try{InputStream is=NewFileInputStream (FilePath); Workbook RWB=Workbook.getworkbook (IS);//Sheet st = Rwb.getsheet ("0") here are two ways to get the Sheet table, 1 for the name, and the subscript, starting from 0Sheet st = Rwb.getsheet ("original"); Cell c00= St.getcell (0,0); //Common way to get a cell value, return a stringString strc00 =c00.getcontents ();//How to get the cell-specific type valueif(C00.gettype () = =Celltype.label) {Labelcell labelc00=(Labelcell) c00; strc00=labelc00.getstring ();} //OutputSystem.out.println (STRC00);//Closerwb.close ();} Catch(Exception e) {e.printstacktrace ();}} /*** Output Excel * *@paramOS*/  Public Static voidwriteexcel (outputstream os) {Try { /*** Workbook can only be created using the factory method provided by the API, not the Writableworkbook constructor, * Because the constructor for class Writableworkbook is protected type * Method (1) Read Writableworkbook WWB = workbook.createworkbook (new file (TargetFile)) directly from the target file; * Method (2) writes Writableworkbook directly to the output stream as shown in the example below*/Writableworkbook WWB=workbook.createworkbook (OS);//Create an Excel worksheet specifying a name and locationWritablesheet ws = Wwb.createsheet ("Test Sheet 1", 0); //************** Add data to the worksheet *****************//1. Add a Label objectLabel label =NewLabel (0,0, "This is a label test"); Ws.addcell (label); //add an object with a font formattingWritablefont WF =NewWritablefont (Writablefont.times,18,writablefont.bold,true); Writablecellformat WCF=NewWritablecellformat (WF); Label LABELCF=NewLabel (1,0, "This is a label test", WCF); Ws.addcell (LABELCF); //add a Formatting object with font colorWritablefont WFC =NewWritablefont (Writablefont.arial,10,writablefont.no_bold,false, underlinestyle.no_underline,jxl.format.colour.red); Writablecellformat WCFFC=NewWritablecellformat (WFC); Label LABELCF=NewLabel (1,0, "This is a Label Cell", WCFFC); Ws.addcell (LABELCF); //2. Add a Number objectNumber Labeln =NewNumber (0,1,3.1415926); Ws.addcell (Labeln); //add a Number object with formattingNumberFormat NF =NewNumberFormat ("#.##"); Writablecellformat WCFN=NewWritablecellformat (NF); Number LABELNF=NewJxl.write.Number (1,1,3.1415926, WCFN); Ws.addcell (LABELNF); //3. Adding a Boolean objectBoolean Labelb =NewJxl.write.Boolean (0,2,false); Ws.addcell (LABELB); //4. Adding a DateTime objectJxl.write.DateTime Labeldt =NewJxl.write.DateTime (0,3,Newjava.util.Date ()); Ws.addcell (Labeldt); //Add a DateFormat object with formattingDateFormat DF =NewDateFormat ("dd MM yyyy hh:mm:ss"); Writablecellformat WCFDF=NewWritablecellformat (DF); DateTime LABELDTF=NewDateTime (1, 3,Newjava.util.Date (), WCFDF); Ws.addcell (LABELDTF); //Add Picture object, JXL only support PNG format pictureFile image =NewFile ("F:\\2.png"); Writableimage Wimage=NewWritableimage (0,1,2,2, image); Ws.addimage (Wimage); //Writing Worksheetswwb.write (); Wwb.close ();} Catch(Exception e) {e.printstacktrace ();}} /*** After copying, make changes, where File1 is the Copy object, File2 is the object created after the modification. * The original formatting of the cell is not removed, we can still add the new cell decoration, * to make the contents of the cell behave differently *@paramFile1 *@paramfile2*/  Public Static voidmodifyexcel (File file1,file file2) {Try{Workbook RWB=Workbook.getworkbook (FILE1); Writableworkbook WWB= Workbook.createworkbook (FILE2,RWB);//CopyWritablesheet ws = Wwb.getsheet (0); Writablecell WC= Ws.getwritablecell (0,0); //determine the type of cell and make a corresponding conversionif(Wc.gettype = =Celltype.label) {label label=(Label) WC; label.setstring ("The value has been modified"); } wwb.write (); Wwb.close (); Rwb.close (); } Catch(Exception e) {e.printstacktrace ();}} //Test Public Static voidMain (string[] args) {Try { //Read ExcelExcelhandle.readexcel ("F:/testread.xls"); //Output ExcelFile FileWrite =NewFile ("F:/testwrite.xls"); Filewrite.createnewfile (); OutputStream OS=NewFileOutputStream (FileWrite); Excelhandle.writeexcel (OS); //Modify ExcelExcelhandle.modifyexcel (NewFile (""),NewFile ("")); } Catch(Exception e) {e.printstacktrace ();}} } 

2. Do the relevant tests in the JSP and create a writeexcel.jsp

<% Response.reset (); //  response.setcontenttype ("application/vnd.ms-excel"new File ("F:/testwrite.xls" )  New  FileOutputStream (fileWrite); Excelhandle.writeexcel (New

Java Data import in Excel