Writableworkbook book = null;
Response.reset ();
PageContext.
Response.setcharacterencoding ("UTF-8");//Set character sets
Set object caption font with glyph formatting
Writablefont.bold, True);
Writablefont wf14 = new Writablefont (Writablefont.createfont ("Microsoft Ya Black"), 14,
Writablefont.bold, false);
Writablecellformat wcfF14 = new Writablecellformat (WF14);
Wcff14.setalignment (alignment.centre);//Horizontal alignment
Wcff14.setverticalalignment (verticalalignment.centre);//Vertical alignment
Label Font Properties
Writablefont wf12 = new Writablefont (Writablefont.createfont ("Microsoft Ya Black"), 11,
Writablefont.bold, false);
Writablecellformat wcfF12 = new Writablecellformat (WF12);
Wcff12.setalignment (Alignment.centre);
Wcff12.setverticalalignment (Verticalalignment.centre);
Content Font Properties
Writablefont wf11 = new Writablefont (Writablefont.createfont ("Microsoft Ya Black"), 11,
Writablefont.no_bold, false);
Writablecellformat wcfF11 = new Writablecellformat (WF11);
Wcff11.setalignment (Alignment.centre);
Create Person reviewer font properties
Writablefont wf13 = new Writablefont (Writablefont.createfont ("Microsoft Ya Black"), 8,
Writablefont.no_bold, false);
Writablecellformat wcfF13 = new Writablecellformat (WF11);
Set borders to cells and borders
Wcff12.setborder (Jxl.format.Border.ALL, Jxl.format.BorderLineStyle.THIN);
Create a workflow
OutputStream OS = null;
try {
Set Popup dialog box
Response.setcontenttype ("Application/dowload");
Response.setcharacterencoding ("UTF-8");
Set the title of a worksheet
Response.setheader ("Content-disposition",
"Attachment; Filename=norecord_social_credit_code.xls ");
Set the generated file name
Response.AddHeader ("content-disposition", "inline; Filename= "
+ New String (Name.getbytes ("GB2312"), "iso8859_1") + ". xls");
OS = Response.getoutputstream ();
Initializing a worksheet
Book = Workbook.createworkbook (OS);
} catch (IOException E1) {
Logger.error ("Export Excel occurs IO exception", E1);
throw new Serviceexception ("Export failed", E1);
}
try {
Set Date format
SimpleDateFormat SF = new SimpleDateFormat ("Yyyy-mm-dd");//Date format 1
SimpleDateFormat sf2 = new SimpleDateFormat ("Yyyy-mm-dd HH:mm:ss");//Date format 1
Timestamp appdate = Order_scrk.getb_createtime ();
String appdatestr = Sf.format (appdate);//Build Table Date
String Product_date=sf2.format (Order_cgtl_dts.getproduct_date ());
The following is the Excel table content
int ncount = List.size ();
Writablesheet sheet = book.createsheet (name, 0);//Set sheet name
Sheet.getsettings (). Setautomaticformulacalculation (True);
Set the default column width
Sheet.getsettings (). Setdefaultcolumnwidth (14);
Set Default row height
Sheet.getsettings (). Setdefaultrowheight (300);
Build name Sheet, parameter 0 indicates this is the first page
int NI = 1;
table field Name
Sheet.mergecells (0, 0, one, 0);//1-k Merge
Sheet.setrowview (0, 800); Set line 1th Row height
Sheet.addcell (New Jxl.write.Label (0, 0, name, wcfF14));
Merge notes there cells
Sheet.mergecells (1, 27, 11, 27);//Notes there
Sheet.setrowview (27, 1000); Set line 27th Row height
Sheet.setc
Sheet.mergecells (0, 1, 11, 1);//second line merge
Sheet.addcell (New Jxl.write.Label (0, 1, "Document number:"
+ order_scrk.getnumber () + "\ t Date:" + appdatestr + "\ t Document Status:"
+ Order_status.getorder_status_name (), wcfF12));
Sheet.mergecells (1, 2, 11, 2);//Line 3rd Merge
Sheet.addcell (New Jxl.write.Label (0, 2, "Quality Inspector:", wcfF12));
Sheet.addcell (New Jxl.write.Label (1, 2, User1.getuser_name (), wcfF11));
Sheet.mergecells (1, 3, 11, 3);//Line 4th merge
Sheet.addcell (New Jxl.write.Label (0, 3, "Project:", wcfF12));
Sheet.addcell (New Jxl.write.Label (1, 3, Project.getproject_name (), wcfF11));//project name
Sheet.addcell (New Jxl.write.Label (1, 2, "raw material silo", wcfF12));
Sheet.addcell (New Jxl.write.Label (0, 4, "Material Code", wcfF12));
Sheet.addcell (New Jxl.write.Label (1, 4, "Material name", wcfF12));
Sheet.addcell (New Jxl.write.Label (2, 4, "spec", wcfF12));
Sheet.addcell (New Jxl.write.Label (3, 4, "model", wcfF12));
Sheet.addcell (New Jxl.write.Label (4, 4, "unit", wcfF12));
Sheet.addcell (New Jxl.write.Label (5, 4, "Amount receivable", wcfF12));
Sheet.addcell (New Jxl.write.Label (6, 4, "serial number", wcfF12));
Sheet.addcell (New Jxl.write.Label (7, 4, "Amount paid", wcfF12));
Sheet.addcell (New Jxl.write.Label (8, 4, "lot number", wcfF12));
Sheet.addcell (New Jxl.write.Label (9, 4, "warehouse", wcfF12));
Sheet.addcell (New Jxl.write.Label (10, 4, "position", wcfF12));
Sheet.addcell (New Jxl.write.Label (11, 4, "Stock Status", wcfF12));
for (int i = 0; i < list.size (); i++) {
Order_scrk_dts order_scrk_dts= List.get (i). Getorder_scrk_dts ();
String sql= "Select F.ser_number from Finished_p_sernum F"
+ "left JOIN order_zj_dts o f.order_cskc_dts_id=o.order_zj_dts_id"
+ "left JOIN Order_scrk_dts s on o.order_zj_dts_id=s.order_scrk_dts_id"
+ "where s.order_scrk_id=?1";
Map<integer, object> maps = new Hashmap<integer, object> ();
Maps.put (1, order_scrk_dts.getorder_zj_dts_id ());
list<map<string,object>> names = basedao.findbysql (sql, maps);
String hege= "";
for (int j = 0; J < Names.size (); j + +)
{
hege+= (String) Names.get (j). Get ("Ser_number") + ",";
}
Material Material=list.get (i). Getmaterialdto (). getmaterial ();//Get Material
Get the Unit ID
Long Unit_id=list.get (i). Getmaterialdto (). GetMaterial (). Getcom_unit ();
Unit unit= (unit) Basedao.get (Unit.class, (Serializable) unit_id);
Get Warehouse
Long store_id=order_scrk_dts.getstore_house_id ();
Store_house store_house= (store_house) Basedao.get (Store_house.class, (Serializable) store_id);
Get position
Long position=order_scrk_dts.getstore_house_position_id ();
Store_house_position house_position= (store_house_position) Basedao.get (Store_house_position.class, (Serializable) position);
Get inventory Status
Long Stock=order_scrk_dts.getstock_status ();
Stock_status status= (stock_status) Basedao.get (Stock_status.class, (Serializable) Stock);
Get Batch
String batch= "";
if (Order_scrk_dts.getbatch_num ()!=null)
{
Batch=order_scrk_dts.getbatch_num (). toString ();
}
Sheet.addcell (New Jxl.write.Label (0, 5+i, Material.getmaterial_number (), wcfF11));//Encoding
Sheet.addcell (New Jxl.write.Label (1, 5+i, Material.getmaterial_name (), wcfF11));//Item Name
Sheet.addcell (New Jxl.write.Label (2, 5+i, Material.getcom_stantard (), wcfF11));//Specifications
Sheet.addcell (New Jxl.write.Label (3, 5+i, Material.getcom_model (), wcfF11));//model
Sheet.addcell (New Jxl.write.Label (4, 5+i, Unit.getunit_name (), wcfF11));//Unit
Sheet.addcell (New Jxl.write.Label (5, 5+i, Order_scrk_dts.getyinshou_num (). toString (), wcfF11));//Receivable quantity
Sheet.addcell (New Jxl.write.Label (6, 5+i, hege,wcff11));//Unit
Sheet.addcell (New Jxl.write.Label (7, 5+i, Order_scrk_dts.getshishou_num (). toString (), wcfF11));//Receivable quantity
Sheet.addcell (New Jxl.write.Label (8, 5+i, Batch, wcfF11));//Receivable quantity
Sheet.addcell (New Jxl.write.Label (9, 5+i, Store_house.getstore_house_name (), wcfF11));//Receivable quantity
Sheet.addcell (New Jxl.write.Label (Ten, 5+i, House_position.getposition_name (), wcfF11));//Receivable quantity
Sheet.addcell (New Jxl.write.Label (one, 5+i, Status.getstock_status_name (), wcfF11));//Receivable quantity
}
28 Lines plus Notes
Sheet.addcell (New Jxl.write.Label (0, 27, "Remarks:", wcfF12));
Create person
Sheet.addcell (New Jxl.write.Label (7, 28, "creator:"
+ User3.getuser_name (), wcfF13));
Auditor.
Sheet.addcell (New Jxl.write.Label (8, 28, "Auditor:", wcfF13));
Sheet.addcell (New Jxl.write.Label (1, Order_scrk.getremark (), wcfF11));//Remarks
Book.write ();
Book.close ();
} catch (Exception e) {
throw new Serviceexception ("Export failed", e);
} finally {
if (null! = OS) {
try {
Os.flush ();
Os.close ();
} catch (IOException e) {
Logger.error ("Off-stream exception", e);
E.printstacktrace ();
}
}
}
Using JXL to implement tabular export in Java