Some time ago, customers requested our system to implement data exchange with MS Excel, which can be imported into Excel in the system. Our system is a MIS system for B / S structure made with Java. To solve this problem, I used the open source project JXL for Excel to implement the following features according to the customer's requirements: 1: Read the data of the Excel file in the client; 2: Export system data into the Excel template file. So I thought First upload the Excel file to the server, then use JXL to parse the data. How to upload a file, I don't say it. As long as people who have done JSP should know. It mainly tells how to analyze. Since the uploaded data is fixed format So we made an Excel template. Customers must fill in data according to the format of the template. The program can be analyzed normally. The analysis is actually very simple, but in the process of doing, the time format data in Excel needs special attention. : If you read the time format data according to the text format, read the time is wrong (everyone can test it). In order to solve the problem of reading time error, I carefully study the JXL API, huh, I actually discovered a good Dongdong: Datecell class, uses its getDate method to get a Cell's DATE object. However, due to internationalization needs, this DATE object is Greenwich Time, you need to handle it. As for the export, it is similar to the import, nothing more than First access the database, write the obtained information to the Excel file and download it. Below is the Java class I have written to export the weekly production plan, where some of the few rows of comments are the tests of downloading and uploading. (In fact The template is also very critical. The following program is written based on the template, but I believe that everyone will look at the following program, or you can know the template, you need to focus, in the template, the field of filling time must be set to time Format ----- Right-click on Excel to easily get it)
/ * * Create Date: 2004-11-9 10:20 * Create By: Li Chunlei * Purpose: and Zhou Plan Related Import Operation * / / *************************** ***** Weekly Production Decisions *************************** EXCELZJHXM Myzjh = New ExcelzjhXM (); Myzjh.Download ("MyDatazjh", "20041018003"); *** ********************************************************* / / ***************************************************** Excelzjhxm test = new Excelzjhxm ( Test.upload ("zscjhtest"); if (Test.getflag ()) {system.out.println ("Begin Test ------------"); ArrayList A = TEST .getdata (); for (int i = 0; i Import com.infoearth.common.DataAccess; // We project your own access database of Dongdong Import Java.io.fileInputStream; Import Java.io.InputStream; Import Java.sql.sqlexception; Import Java.sql. *; Import Java .io. *; Import java.io.Serializable; import java.util. *; import javax.sql.rowset; import jxl.cell; import jxl.sheet; import jxl.workbook; import jxl.write. *; import java .text.dateformat; import jxl.datecell; import java.text. *; public class ExcelZJHXM {private boolean flag = false; private ArrayList subdata = new ArrayList (); public boolean download (String filename, String jhbh) {Connection conn = null; PreparedStatement pStatement = null; ResultSet rs = null; DataAccess dBean = new DataAccess (); String mysql; mysql = "select XH, XMMC, GZNR, SFTD, TDFW, JHLRFLBH, JHLRBH, ZXDWMC, PHDWMC, DBRMC, JHKSSJ, JHWCSJ, BZ from JH_ZJHXM where JHBH = '" jhbh "' order by jhxmbh asc "; try {conn = dBean.getConnection (); pStatement = conn.prepareStatement (mysql); rs = pStatement.executeQuery (); Workbook wb = Workbook.getWorkbook (new File (" pengyue // webmis // template // zscjh .xls ")); Writableworkbook book = workbook.createworkbook (new file (" pengyue // webmis // Download // " FileName ". xls "), WB); Writablesheet Sheet = BOOK.GETSHEET (" zscjh "); JXL.WRITE.WRITABECECELLFORMAT WCFFC = New JXL.WRITE.WRITABECELLFOR Mat (); wcffc.setborder (jxl.format.border.all, jxl.format.BorderLineStyle.thin); int i = 4; jxl.write.dateformat DF = new jxl.write.dateFormat ("YYYY-MM-DD HH: mm "); jxl.write.writablecellformat wcfdf = new jxl.write.writablecellformat (df); wcfdf.SetBorder (jxl.format.Border.all, jxl.format.BorderLineStyle.thin); while (rs.next )) {Sheet.Addcell (New Label (2, I, RS.GetString ("XH"), WCFFC)); // Serial SHEET.ADDCELL (New Label (3, I, Rs.getstring " XMMC "), WCFFC); // Project Name Sheet.Addcell (New Label (4, I, Rs.getstring (" gznr "), WCFFC)); // Work content Sheet.Addcell (New Label (5, i rs.getstring ("sftd"), WCFFC); // Whether it is power outage Sheet.Addcell (New Label (6, I, RS.GETSTRING ("TDFW"), WCFFC); // Power-off range Sheet.Addcell New Label (7, I, Rs.getstring ("Jhlrflbh"), WCFFC)); // Task Source Sheet.Addcell (New Label (8, I, RS.GetString ("Jhlrbh"), WCFFC); // Source number Sheet.Addcell (New Label (9, I, RS.GetString ("ZXDWMC"), WCFFC)); // Execute unit Sheet.Addcell (New Label (10, I, RS.GetString ("phDWMC"), WCFFC)); // Teamwork unit Sheet.Addcell (New Label (11, I, Rs.getstring ("DBRMC"), WCFFC); // Supervisor // Sheet.Addcell (New Label (12, I, RS .getstring ("jhkssj"), WCFFC)); // Start time // Sheet.Addcell (New Label (13, I, Rs.getstring ("Jhwcsj"), WCFFC)); // End Time IF (RS. GetString ("jhkssj")! = null) Sheet.Addcell (New JXL.WRITE.DATETIME (12, I, DateFormat.getdatetimeInstance (). Parse (Rs.getstring ("J HKSSJ "), WCFDF); if (Rs.getstring (" Jhwcsj ")! = Null) Sheet.Addcell (New JXL.WRITE.DATETIME (13, I, Dateformat.getdateTimeImeinstance (). Parse (RS.GetString "JHWCSJ")), WCFDF); Sheet.Addcell (New Label (14, I, RS.GetString ("BZ"), WCFFC); // Remark i ;} book.write (); book.close ( ); Rs.close (); pStatement.close (); return true;} catch (exception e) {E.PrintStackTrace (); return false; } Finally {try {if (rs! = Null) {rclose ();} if (pstate! = Null) {pStatement.close ();} if (conn! = Null) {conn.close (); } Catch (sqlexception sqle) {conn = null;}}} Public void upload (string filename) {string flagstr; trybook.getbook rwb = workbook.getworkbook (new file ("pengyue // webmis // upload //" filename ". xls"); Sheet ST = RWB. GetSheet ("zscjh"); int i = 4; // 其 -1 flagstr = st.getcell (0, 0) .getContents (); if (FlagStr.Equals ("zscjhbegin")) Flag = true; (St.getCell (2, i) .GetContents (). Length ()! = 0 && flag == true) {datazjhxm mydata = new datazjhxm (); for (int J = 2; j PHDATA.PHDWMC = St.getCell (J, I) .GetContents (); if (St.getcell (J, 0) .getContents (). Equals ("dbrmc")) MyData.dbrmc = St.Getcell (j, i) .getContents (); // Time handle IF (St.getcell (J, 0) .GetContents (). Equals ("jhkssj")) mydata.jhkssj = formatedata (st.getcell (j, i) ); If (st.getcell (j, 0) .GetContents (). Equals ("jhwcsj")) mydata.jhwcsj = formatedata (St.getcell (J, I)); if (St.getcell (J, 0) .getContents (). Equals ("bz")) MyData.bz = st.getCell (J, I) .GetContents ();} subdata.add (mydata); i ;} rwb.close ();} catch (Exception e) {E.PrintStackTrace (); flag = false;}} // Handling date format data public string formatedata (cell {java.util.date mydate = null; datecell datecl = () = datecll.getdate (); long time = (MyDate.gettime () / 1000) -60 * 60 * 8; mydate.settime (Time * 1000); Calendar Cal = Calendar.GetInstance (); SimpleDateFormat Formatter = new SimpleDateFormat ("YYYY-MM-DD HH: mm"); return formatter.Format (mydate);} catch (Exception e) {e.printStackTrace (); return null;}} public boolean getFlag () {return flag;} public ArrayList getData () {return subdata;} public static class dataZJHXM implements Serializable {public PUBLIC STRING GZNR; Public String SFTD; Public String Jhlrflbh; Public String JHLRBH; public String ZXDWMC; public String PHDWMC; public String DBRMC; public String JHKSSJ; public String JHWCSJ; public String BZ; public dataZJHXM () {XH = ""; XMMC = ""; GZNR = ""; SFTD = "" TDFW = ""; jhlrflbh = "; jhlrbh =" "; zxdwmc =" "; pHDWMC =" "; dbrmc =" "; jhkssj ="; jhwcsj = "; bz =" "}}} Attachment:
