Recently contact with Excel frequently contact, mainly copying data in the N Excel tables to another Excel table to be used as two-dimensional structure to enter the library. However, some tables have the case of consolidated cells, which is very annoyed. What should I do? Fortunately, this N Excel table structure is still consistent, and writes to read in the specified location, then write into the new Excel table, so OK. This mainly uses a component JXL.
JXL installation: Mainly to put JXL's CLASSES below WEB-INF (if downloaded is .jar file, put it under lib). Don't forget to put the Common folder in the JXL package is also placed on the web -Inf below.
JXL use: The main function is to read the Excel file and write an Excel file.
Read: Reading is such a idea, first get an Excel file with an input stream, then get a workbeat with Workbook in JXL, and use Sheet to get a worksheet from the worksheet. Get a worksheet with Cell. INPUTSTREAM-> WORKBOOK-> Sheet-> Cell, get cells in the Excel file
Code: <% @ page contenttype = "text / html; charSet = GB2312"%> <% @ page import = "java.io. *, Jxl. *, Jxl.write. *, Jxl.write. *, Jxl. Format. * "%> string path =" c: //excel.xls "; // Excel file urlinputstream is = new fileinputstream (path); // Write to FileInputStreamjxl.Workbook WB = Workbook.getWorkbook (IS); / / Get working thin jxl.sheet st = wb.getsheet (0); // Get the first worksheet in the worksheet Cell Cell = St.getcell (0, 0); // Get the first unit of the worksheet , Ie, a1string content = cell.getContents (); // getContents () converts characters in Cell to string wb.close (); // turn off workbook is.close (); // Close input stream
We can get the coordinates of any cell, X, Y, and Excel by Sheet's getcell (x, y) method. For example, A1 corresponds to (0, 0), A2 corresponds to (0, 1), D3 corresponds to (3, 2). EXCEL Coordinate from A, 1, all from 0 starts from 0. You can also get the number of row number columns by the getRows (), getColumns () method and used for loop control, output all content in a sheet. .
Write: The write content in Excel is mainly in the jxl.write package. The idea is like this: OutputStream <-writableworkbook <-writablesheet <-Label This Label represents Label is written for Write Sheet CELL location and content. Code:
<% @ Page ContentType = "Text / HTML; Charset = GB2312"%> <% @ page import = "java.io. *, jxl. *, jxl.write. *, jxl.format. * "%> OutputStream OS = New FileoutputStream (" c: //test.xls "); // Output Excel file URLWRITABLEWORKBOOK WWB = Workbook.createWorkbook (OS); // Create Wranty WrTablesheet WS = WWB.CREATESHEET ("Sheet1", 0); // Creating a Watch Writing Watch Label Labelcf = New Label (0, 0, "Hello"); // Create Write Location and Content Ws.Addcell (Labelcf); // Write Label Entering the constructor of the Sheet Label (int X, int y, string astring) XY, astring is the content written.
WritableFont wf = new WritableFont (WritableFont.TIMES, 12, WritableFont.BOLD, false); // set the write font WritableCellFormat wcfF = new WritableCellFormat (wf); // set CellFormatLabel labelCF = new Label (0, 0, "hello" ); // Create a write location, content, and format
Another constructor of Label Label (INT C, INT R, STRING CONT, CELLFORMAT ST) can format, set fonts, and other properties to write content.
You can now write WWB.WRITE (); turn off wwb.close () after writing; Os.close;
OK, just combine reading and writing, you can read the data written in N Excel to write your desirable Excel new table, or more convenient.
