Data exchange between JSP and Excel-my project practices

Some time ago, the customer asked our system to exchange data with MS Excel and import and export the data in the system to excel. our system is a B/S structure MIS system made of Java. to solve this problem, I used the open-source Excel project jxl.
Based on Customer requirements, jxl is used to implement the following functions:
1. read data from Excel files on the client;
2: Export System data to an Excel template file.

So I thought of uploading an Excel file to the server and then parsing the data using jxl. I won't talk about how to upload files. Anyone who has done JSP should know it. this section describes how to resolve the issue. because the uploaded data is both
There is a fixed format, so we made an Excel template, the customer must fill in the data according to the template format, the program can be normal resolution.
Resolution is actually very simple, but in the process of doing it, we found that the time format data in Excel requires special attention: If you read the time format data according to the text format, the read time is incorrect (you can try it ).
To solve the problem of incorrect reading time, I carefully studied the jxl API and found a good stuff: datecell class, the getdate method can be used to obtain the date object of a cell. however, because
For internationalization, the date object obtains the Greenwich Mean Time. You need to handle it.
As for export, it is almost the same as import. It is nothing more than accessing the database first, writing the obtained information into an Excel file, and then downloading it.
The following is the Java class I wrote for importing and exporting the weekly production plan. The first few lines are commented out as examples of downloading and uploading.
(In fact, the template is also very important. The following program is written based on the template. However, I believe you can understand the template after reading the following program. It should be noted that, in the template, the time field must be set
Format ----- in Excel, right-click the menu to easily solve the problem)

/*
* Create Date:
* Create by: Li chunlei
* Purpose: import and export operations related to weekly plans
*/

/********************* Weekly production download example ************* ****
Excelzjhxm myzxm = new excelzjhxm ();
Myzjh. Download ("mydatazjh", "20041018003 ");
**************************************** ************/

/********************* Weekly scheduled upload example ************* ****
Excelzjhxm test = new excelzjhxm ();
Test. Upload ("zscjhtest ");
If (test. getflag ()){
System. Out. println ("begin test --------------");
Arraylist A = test. getdata ();
For (INT I = 0; I <A. Size (); I ++ ){
Excelzjhxm. datazjhxm mydata = (excelzjhxm. datazjhxm) A. Get (I );
System. out. println (mydata. XH + "" + mydata. xmmc + "" + mydata. gznr + "" + mydata. sftd + "" + mydata. tdfw + "" + mydata. jhlrflbh + "" + mydata. jhlrbh + "" + mydata. zxdwmc + "" + mydata. phdwmc + "" + mydata. dbrmc + "" + mydata. jhkssj + "" + mydata. jhwcsj + "" + mydata. BZ );

}
} Else {
System. Out. println ("template error ");
}
**************************************** *************/

Package com. infoearth. Excel;

Import com. infoearth. Common. dataaccess; // Our project's own database access stuff
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.exe cutequery ();
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 ("zscloud ");
Jxl. Write. writablecellformat wcffc = new jxl. Write. writablecellformat ();
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 number
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 the power is down
Sheet. addcell (new label (6, I, Rs. getstring ("tdfw"), wcffc); // power failure 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); // Execution Unit
Sheet. addcell (new label (10, I, Rs. getstring ("phdwmc"), wcffc); // Cooperation 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 ("jhkssj"), wcfdf ));
If (Rs. getstring ("jhwcsj ")! = NULL) sheet. addcell (New jxl. Write. datetime (13, I, dateformat. getdatetimeinstance (). parse (Rs. getstring ("jhwcsj"), wcfdf ));
Sheet. addcell (new label (14, I, Rs. getstring ("BZ"), wcffc); // remarks
I ++;
}
Book. Write ();
Book. Close ();
Rs. Close ();
Pstatement. Close ();
Return true;
}

Catch (exception E)
{
E. printstacktrace ();
Return false;
}
Finally {
Try {

If (RS! = NULL ){
Rs. Close ();
}

If (pstatement! = NULL ){
Pstatement. Close ();
}
If (Conn! = NULL ){
Conn. Close ();
}
}
Catch (sqlexception sqle ){
Conn = NULL;
}
}
}

Public void upload (string filename ){
String flagstr;
Try {
Jxl. Workbook RWB = Workbook. getworkbook (new file ("pengyue // webmis // upload //" + filename + ". xls "));
Sheet ST = RWB. getsheet ("zscloud ");
Int I = 4; // its first column-1
Flagstr = ST. getcell (0, 0). getcontents ();
If (flagstr. Equals ("zscjhbegin") Flag = true;
While (St. getcell (2, I). getcontents (). Length ()! = 0 & flag = true ){
Datazjhxm mydata = new datazjhxm ();
For (Int J = 2; j <st. getcolumns (); j ++ ){
If (St. getcell (J, 0). getcontents (). Equals ("XH") mydata. XH = ST. getcell (J, I). getcontents ();
If (St. getcell (J, 0). getcontents (). Equals ("xmmc") mydata. xmmc = ST. getcell (J, I). getcontents ();
If (St. getcell (J, 0). getcontents (). Equals ("gznr") mydata. gznr = ST. getcell (J, I). getcontents ();
If (St. getcell (J, 0). getcontents (). Equals ("sftd") mydata. sftd = ST. getcell (J, I). getcontents ();
If (St. getcell (J, 0). getcontents (). Equals ("tdfw") mydata. tdfw = ST. getcell (J, I). getcontents ();
If (St. getcell (J, 0). getcontents (). Equals ("jhlrflbh") mydata. jhlrflbh = ST. getcell (J, I). getcontents ();
If (St. getcell (J, 0). getcontents (). Equals ("jhlrbh") mydata. jhlrbh = ST. getcell (J, I). getcontents ();
If (St. getcell (J, 0). getcontents (). Equals ("zxdwmc") mydata. zxdwmc = ST. getcell (J, I). getcontents ();
If (St. getcell (J, 0). getcontents (). Equals ("phdwmc") mydata. phdwmc = ST. getcell (J, I). getcontents ();
If (St. getcell (J, 0). getcontents (). Equals ("dbrmc") mydata. dbrmc = ST. getcell (J, I). getcontents ();
// Time Processing
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;
}
}

// Process date format data
Public String formatedata (cell formatecell ){
Try {
Java. util. Date mydate = NULL;
Datecell datecll = (datecell) formatecell;
Mydate = 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 String XH;
Public String xmmc;
Public String gznr;
Public String sftd;
Public String tdfw;
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 = "";
}

}
}

Appendix:

Reference: http://www-900.cn.ibm.com/developerWorks/cn/java/l-javaExcel/#2