In J2EE application development, for various reasons, it is often necessary to process some old Excel format spreadsheet data or generate workbooks. There are many ways to process Excel workbooks. For example, JDBC can be used to read the contents of a workbook like reading data in a database. Here we use the open-source spreadsheet processing tool jxl, that is, Java Excel API for processing. For jxl and related usage, you can find many materials on the Internet. Here is a simple demonstration of how to use easyjweb. This is an application in a company project some time ago. You need to generate a specific Excel table for the client to download. The code in action in Web MVC is as follows: public class personchangeaction extends basecrudaction {private excelreportservice = excelreportservice. getinstance (); Public page dodownloadmenu (webform, module) throws exception {
String town = commutil. null2string (Form. Get ("querytown "));
String village = commutil. null2string (Form. Get ("queryvillage "));
String team = commutil. null2string (Form. Get ("queryteam "));
String belongdept = "";
Activeuser user = (activeuser) This. getcurrentuser (form );
If (! Userservice. checkadmin (User ))
{
Belongdept = user. getdept ();
}
Actioncontext. getcontext (). getresponse (). setcontenttype ("application/X-msdownload ");
String filename = "XXXXX personnel list ";
If (! "". Equals (town) filename + = "-" + town;
If (! "". Equals (village) filename + = "-" + village;
If (! "". Equals (Team) filename + = "-" + team;
Filename + = ". xls ";
Actioncontext. getcontext (). getresponse (). setheader ("content-disposition", "attachment; filename =/" "+ new string (filename. getbytes ("GBK"), "iso8859-1") + "/"");
Java. Io. outputstream out = com. easyjf. Web. actioncontext. getcontext (). getresponse (). getoutputstream ();
Excelreportservice. personchangeexcellist (Out, belongdept, town, village, team );
Return NULL;
} ..} The dodownloadmenu in the action corresponds to the downloadmenu command. This Code note implements the function of returning binary stream files to the client. Pay attention to some details. The content in the stream, that is, the content of the Excel table, is processed through the personchangeexcellist method of excelreportservice. This method belongs to the business logic layer, and the main function is to set the objects that meet the conditions, add to an Excel table. The content of this method is roughly as follows: public synchronized void personchangeexcellist (Java. io. outputstream out, string belongdept, string town, string village, string Team)
{
String scope = "1 = 1 ";
String scope1 = "1 = 1 ";
Collection paras = new arraylist ();
// Omit Part of the query code if (! "1 = 1". Equals (scope) scope1 + = "and personid in (select CID as personid from person where" + scope + ")";
Dbpagelist plist = new dbpagelist (personchange. Class, scope1, paras );
Plist. dolist (1,500 );
Try {
Jxl. Write. writableworkbook book = jxl. Workbook. createworkbook (out );
Jxl. Write. writablesheet sheet = book. createsheet ("XXXX personnel list", 0 );
Int pages = plist. getpages ();
String [] lables = {"name", "town (Township, street)", "village (neighborhood committee)", "group", "householder", "Employment mode ", "Employment transfer time", "Employment transfer location", "Employment Organization", "Employment wage", "employment type", "contact number "};
For (int l = 0; L <lables. length; l ++)
Sheet. addcell (New jxl. Write. Label (L, 0, lables [l]);
Int ROW = 1;
For (INT p = 1; P <= pages; P ++)
{
Plist = new dbpagelist (personchange. Class, scope1, paras );
Plist. dolist (p, 500 );
List list = plist. getresult ();
For (INT I = 0; I <list. Size (); I ++)
{
Personchange change = (personchange) list. Get (I );
Person = (person) This. Dao. Get (person. Class, change. getpersonid ());
Sheet. addcell (New jxl. Write. Label (0, row, person. gettruename ()));
Sheet. addcell (New jxl. Write. Label (1, row, person. gettown ()));
Sheet. addcell (New jxl. Write. Label (2, row, person. getvillage ()));
Sheet. addcell (New jxl. Write. Label (3, row, person. getteam ()));
Sheet. addcell (New jxl. Write. Label (4, row, person. gethouseholdername ()));
Sheet. addcell (New jxl. Write. Label (5, row, logic. showtitle ("getworktype", change. getworktype ())));
Sheet. addcell (New jxl. Write. Label (6, row, commutil. Format (change. gettransfertime ())));
String workplace = logic. showtitle ("Workplace", change. getworkplace ());
If ("". Equals (workplace) Workplace = change. getworkplace ();
Sheet. addcell (New jxl. Write. Label (7, row, workplace ));
Sheet. addcell (New jxl. Write. Label (8, row, change. getworkdept ()));
Sheet. addcell (New jxl. Write. Label (9, row, logic. showtitle ("worksalary", change. getworksalary ())));
Sheet. addcell (New jxl. Write. Label (10, row, logic. showtitle ("skills", change. getworksubject ())));
Sheet. addcell (New jxl. Write. Label (11, row, change. gettel ()));
Row ++;
}
}
Book. Write ();
Book. Close ();
}
Catch (exception E)
{
E. printstacktrace ();
}
}
