excel|js| Data | Before the project time, the customer request our system can realize the data exchange with MS Excel, can import the data of the system into Excel. Our system is a Java b/s structure of the MIS system. To solve this
Problem, I found an open source project JXL that specializes in Excel, and found a good article on how to use the JXL Development Kit:
--http://www-900.cn.ibm.com/developerworks/cn/java/l-javaexcel/#2
According to the requirements of customers, the following functions have been implemented with JXL:
1: Read the Excel file data on the client;
2: Export the system data to the Excel template file.
For the first question, I started thinking about writing a client-run applets applet. Later realized that Java for security, applets are not random access to the client's file system (:(low-level error! Don't despise me).
So the idea is to upload the Excel file to the server, and then use JXL to parse the data. How to upload the file I will not say, as long as the people who have done JSP should know. Mainly talk about how to parse. Because the data uploaded is
There is a fixed format, so we do an Excel template, the customer must follow the format of the template to fill in the data, the program can be normal parsing.
Parsing is also very simple, but in the process of doing it, it is found that the time format data in Excel needs special attention: If you read the time format data in text format, the reading time is wrong (you can experiment).
In order to solve the problem of reading time error, I carefully studied the JXL API, hehe, actually found a good dongdong: Datecell class, with its GetDate method can get a cell date object. But because
The need for internationalization, this date object to get is GMT, you need to deal with.
As for the export, in fact, and import almost, nothing more than the first access to the database, the information received to write Excel files and then download.
The following is a Java class that I wrote to import the export week production plan, where the first few lines of comments were downloaded and uploaded in the sample.
(In fact, the template is also very critical, the following program is based on the template to write, but I believe you see the following program, you can know the appearance of the template, it is important to note that in the template, fill in the time of the field must be set to when
Room format-----Click the right button in Excel to easily handle the menu.
/*
* Create date:2004-11-9 10:20
* Create by: Li Chunlei
* Purpose: Import and export operations related to week plans
*/
/********************** Weekly production Download sample *****************
EXCELZJHXM myzjh = new Excelzjhxm ();
Myzjh.download ("Mydatazjh", "20041018003");
****************************************************/
/********************** Week Plan 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; We project our own access to the database 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.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)); Is there a power outage
Sheet.addcell (New Label (6,i,rs.getstring ("TDFW"), WCFFC)); Blackout 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)); Cooperating Unit
Sheet.addcell (New Label (11,i,rs.getstring ("DBRMC"), WCFFC)); Supervising person
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 (), I,dateformat.getdatetimeinstance (). Parse (rs.getstring ("JHKSSJ")), WCFDF);
if (rs.getstring ("JHWCSJ")!=null) Sheet.addcell (New Jxl.write.DateTime (). Parse (rs.getstring ("JHWCSJ")), WCFDF);
Sheet.addcell (New Label (14,i,rs.getstring ("BZ"), WCFFC)); Note
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 ("Zscjh");
int i = 4; Its starting list-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;
}
}
Working with 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 = "";
}
}
}
