Jxl Excel source code

/**
* Export personal information to an Excel worksheet
*/
Public void grxxExportXLS (HttpServletResponse response, Grxx grxx,
PageBean p ){
List list = queryGrxxList (grxx, p );
Try {
// String targetfile = getServlet (). getServletContext (). getRealPath (
// "/Companymanage ")
// + "/Grxxlistxls.xls ";
// Method 1: Create an Excel worksheet that can be written
// Jxl. write. WritableWorkbook wwb = Workbook. createWorkbook (new
// File (
// Targetfile ));
// Method 2: Write WritableWorkbook directly to the output stream
OutputStream OS = response. getOutputStream ();
String fs = "personal information table ";
Fs = new String (fs. getBytes ("gb2312"), "iso8859-1 ");
Response. reset (); // clear the output stream
Response. setContentType ("application/msexcel"); // defines the output type.
Response. setHeader ("Content-disposition", "attachment; filename ="
+ Fs + ". xls"); // sets the output file header.
Jxl. write. WritableWorkbook wwb = Workbook. createWorkbook (OS );

// Create an Excel worksheet
Jxl. write. WritableSheet ws = wwb. createSheet ("personal information table 1", 0 );
/**
* 19 columns with two titles
*/
Int charTitle = 10; // Title font size
Int charNormal = 10; // Title font size
// Add an object with a font Formatting

// Used for title
Jxl. write. WritableFont titleFont = new jxl. write. WritableFont (
WritableFont. createFont (""), charTitle, WritableFont. BOLD );

Jxl. write. WritableCellFormat titleFormat = new jxl. write. WritableCellFormat (
TitleFont );
TitleFormat. setBorder (Border. ALL, BorderLineStyle. THIN); // line
TitleFormat. setVerticalAlignment (VerticalAlignment. CENTRE); // Vertical Alignment
TitleFormat. setAlignment (Alignment. CENTRE); // Horizontal Alignment
TitleFormat. setWrap (true); // whether to wrap
TitleFormat. setBackground (Colour. GRAY_25); // the background color is dark-25%
// Used for text
WritableFont normalFont = new WritableFont (WritableFont
. CreateFont (""), charNormal );
Jxl. write. WritableCellFormat normalFormat = new jxl. write. WritableCellFormat (
NormalFont );
NormalFormat. setBorder (Border. ALL, BorderLineStyle. THIN); // line
NormalFormat. setVerticalAlignment (VerticalAlignment. CENTRE); // Vertical Alignment
NormalFormat. setAlignment (Alignment. CENTRE); // Horizontal Alignment
NormalFormat. setWrap (true); // whether to wrap

// Number object with formatting
WritableFont nf = new WritableFont (WritableFont
. CreateFont ("Times New Roman"), charNormal );
// Jxl. write. NumberFormat nf = new jxl. write. NumberFormat ("#.##");
Jxl. write. WritableCellFormat wcfN = new jxl. write. WritableCellFormat (
Nf );
WcfN. setBorder (Border. ALL, BorderLineStyle. THIN); // line
WcfN. setVerticalAlignment (VerticalAlignment. CENTRE); // Vertical Alignment
WcfN. setAlignment (Alignment. CENTRE); // Horizontal Alignment
WcfN. setWrap (true); // whether to wrap
/**
* Add a title
*/
// The first line
*****************/
Ws. mergeCells (14, 0, 18, 0); // merge cells,
Ws. mergeCells (10, 0, 13, 0); // merge cells,

Ws. mergeCells (0, 0, 0, 1); // merge cells, sequence number
Ws. setColumnView (0, 6); // set the column width to 1st columns.
Ws. mergeCells (1, 0, 1, 1); // merge cells, Citizenship ID card number
Ws. setColumnView (1, 9); // set the column width to 2nd columns.
Ws. mergeCells (2, 0, 2, 1); // merge cells, computer serial number
Ws. setColumnView (2, 6); // set the column width to 3rd columns.
Ws. mergeCells (3, 0, 3, 1); // merge cells, four insurance names
Ws. setColumnView (3, 6); // set the column width to 4th columns.
Ws. mergeCells (4, 0, 4, 1); // merge cells, medical name
Ws. setColumnView (4, 6); // set the column width to 5th columns.
Ws. mergeCells (5, 0, 5, 1); // merge cells, Medical Insurance Manual No.
Ws. setColumnView (5, 9); // sets the column width to 6th columns.
Ws. mergeCells (6, 0, 6, 1); // merge cells, four insurance payers
Ws. setColumnView (6, 9); // sets the column width to 7th columns.
Ws. mergeCells (7, 0, 7, 1); // merge cells and medical payers
Ws. setColumnView (7, 9); // sets the column width to 8th columns.
Ws. mergeCells (8, 0, 8, 1); // merge cells, medical insurance personnel category
Ws. setColumnView (8, 9); // set the column width to 9th columns.
Ws. mergeCells (9, 0, 9, 1); // merge cells, medical insurance status
Ws. setColumnView (9, 7); // set the column width to 10th columns.
Ws. setColumnView (10, 6); // set the column width to 11th columns.
Ws. setColumnView (11, 6); // set the column width to 12th columns.
Ws. setColumnView (12, 6); // set the column width to 13th columns.
Ws. setColumnView (13, 6); // set the column width to 14th columns.
Ws. setColumnView (14, 6); // set the column width to 15th columns.
Ws. setColumnView (15, 6); // set the column width to 16th columns.
Ws. setColumnView (16, 6); // set the column width to 17th columns.
Ws. setColumnView (17, 6); // set the column width to 18th columns.
Ws. setColumnView (18, 6); // set the column width to 19th columns.
Jxl. write. Label tit00 = new jxl. write. Label (0, 0, "Serial Number", titleFormat );
Ws. addCell (tit00 );
Jxl. write. Label tit10 = new jxl. write. Label (1, 0, "citizen ID card number ",
TitleFormat );
Ws. addCell (tit10 );
Jxl. write. Label tit20 = new jxl. write. Label (2, 0, "Computer no ",
TitleFormat );
Ws. addCell (tit20 );
Jxl. write. Label tit30 = new jxl. write. Label (3, 0, "four insurance name ",
TitleFormat );
Ws. addCell (tit30 );
Jxl. write. Label tit40 = new jxl. write. Label (4, 0, "medical name ",
TitleFormat );
Ws. addCell (tit40 );
Jxl. write. Label tit50 = new jxl. write. Label (5, 0, "Medical Insurance Manual No ",
TitleFormat );
Ws. addCell (tit50 );
Jxl. write. Label tit60 = new jxl. write. Label (6, 0, "four insurance payers ",
TitleFormat );
Ws. addCell (tit60 );
Jxl. write. Label tit70 = new jxl. write. Label (7, 0, "medical payers ",
TitleFormat );
Ws. addCell (tit70 );
Jxl. write. Label tit80 = new jxl. write. Label (8, 0, "medical insurance personnel category ",
TitleFormat );
Ws. addCell (tit80 );
Jxl. write. Label tit90 = new jxl. write. Label (9, 0, "medical insurance status ",
TitleFormat );
Ws. addCell (tit90 );
Jxl. write. Label tit100 = new jxl. write. Label (10, 0, "four insurance payment status ",
TitleFormat );
Ws. addCell (tit100 );
Jxl. write. Label tit140 = new jxl. write. Label (14, 0, "payment base ",
TitleFormat );
Ws. addCell (tit140 );
// The second line
Jxl. write. Label tit101 = new jxl. write. Label (10, 1, "elder care ",
TitleFormat );
Ws. addCell (tit101 );
Jxl. write. Label tit111 = new jxl. write. Label (11, 1, "unemployed ",
TitleFormat );
Ws. addCell (tit111 );
Jxl. write. Label tit121 = new jxl. write. Label (12, 1, "Work Injury ",
TitleFormat );
Ws. addCell (tit121 );
Jxl. write. Label tit131 = new jxl. write. Label (13, 1, "fertility ",
TitleFormat );
Ws. addCell (tit131 );
Jxl. write. Label tit141 = new jxl. write. Label (14, 1, "elder care ",
TitleFormat );
Ws. addCell (tit141 );
Jxl. write. Label tit151 = new jxl. write. Label (15, 1, "unemployed ",
TitleFormat );
Ws. addCell (tit151 );
Jxl. write. Label tit161 = new jxl. write. Label (16, 1, "Work Injury ",
TitleFormat );
Ws. addCell (tit161 );
Jxl. write. Label tit171 = new jxl. write. Label (17, 1, "fertility ",
TitleFormat );
Ws. addCell (tit171 );
Jxl. write. Label tit181 = new jxl. write. Label (18, 1, "medical ",
TitleFormat );
Ws. addCell (tit181 );

Double yljs = 0; // total medical base
Double yxjs = 0; // total pension base
Double syjs = 0; // total unemployment Base
Double sxjs = 0; // total fertility Base
Double gsjs = 0; // total worker injury Base
For (int I = 0; I <list. size (); I ++ ){
Grxx xx = (Grxx) list. get (I );
Jxl. write. Label lab1 = new jxl. write. Label (0, I + 2, String
. ValueOf (I + 1), wcfN );
Ws. addCell (lab1); // column 1st: serial number
Jxl. write. Label lab2 = new jxl. write. Label (1, I + 2, xx
. GetBzhm (), wcfN );
Ws. addCell (lab2); // Column 2nd: citizen ID card number
Jxl. write. Label lab3 = new jxl. write. Label (2, I + 2, "the field does not exist ",
WcfN );
Ws. addCell (lab3); // column 3rd: computer serial number
Jxl. write. Label lab4 = new jxl. write. Label (3, I + 2,
Xx. getXm (), normalFormat );
Ws. addCell (lab4); // column 4th: four insurance names
Jxl. write. Label lab5 = new jxl. write. Label (4, I + 2,
Xx. getXm (), normalFormat );
Ws. addCell (lab5); // column 5th: medical name
Jxl. write. Label lab6 = new jxl. write. Label (5, I + 2, xx
. GetBxh (), wcfN );
Ws. addCell (lab6); // column 6th: Medical Insurance Manual No.
Jxl. write. Label lab7 = new jxl. write. Label (6, I + 2, xx
. GetBxh (), normalFormat );
Ws. addCell (lab7); // column 7th: four insurance payers
Jxl. write. Label lab8 = new jxl. write. Label (7, I + 2, "the field does not exist ",
NormalFormat );
Ws. addCell (lab8); // column 8th: Medical payers
Jxl. write. Label lab9 = new jxl. write. Label (8, I + 2, "the field does not exist ",
NormalFormat );
Ws. addCell (lab9); // column 9th: medical insurance personnel category
Jxl. write. Label lab10 = new jxl. write. Label (9, I + 2, "the field does not exist ",
NormalFormat );
Ws. addCell (lab10); // column 10th: Medical Insurance Status
Jxl. write. Label lab11 = new jxl. write. Label (10, I + 2, "the field does not exist ",
NormalFormat );
Ws. addCell (lab11); // column 11th: pension payment status
Jxl. write. Label lab12 = new jxl. write. Label (11, I + 2, "the field does not exist ",
NormalFormat );
Ws. addCell (lab12); // column 12th: unemployment payment status
Jxl. write. Label lab13 = new jxl. write. Label (12, I + 2, "the field does not exist ",
NormalFormat );
Ws. addCell (lab13); // column 13th: Work Injury payment status
Jxl. write. Label lab14 = new jxl. write. Label (13, I + 2, "the field does not exist ",
NormalFormat );
Ws. addCell (lab14); // column 14th: maternity payment status
Jxl. write. Number lab15 = new jxl. write. Number (14, I + 2, xx
. GetGrjs (). getYxjfjs (). doubleValue (), wcfN );
Ws. addCell (lab15); // column 15th: pension payment base
Yxjs + = xx. getGrjs (). getYxjfjs (). doubleValue ();
Jxl. write. Number lab16 = new jxl. write. Number (15, I + 2, xx
. GetGrjs (). getSyjfjs (). doubleValue (), wcfN );
Ws. addCell (lab16); // column 16th: unemployment payment base
Syjs + = xx. getGrjs (). getSyjfjs (). doubleValue ();
Jxl. write. Number lab17 = new jxl. write. Number (16, I + 2, xx
. GetGrjs (). getGsjfjs (). doubleValue (), wcfN );
Ws. addCell (lab17); // column 17th: worker injury payment base
Gsjs + = xx. getGrjs (). getGsjfjs (). doubleValue ();
Jxl. write. Number lab18 = new jxl. write. Number (17, I + 2, xx
. GetGrjs (). getSxjfjs (). doubleValue (), wcfN );
Ws. addCell (lab18); // column 18th: Reproductive payment base
Sxjs + = xx. getGrjs (). getSxjfjs (). doubleValue ();
Jxl. write. Number lab19 = new jxl. write. Number (18, I + 2, xx
. GetGrjs (). getYljfjs (). doubleValue (), wcfN );
Ws. addCell (lab19); // column 19th: medical payment base
Yljs + = xx. getGrjs (). getYljfjs (). doubleValue ();
}
/**
* Total of the last row
*/
Int end = list. size () + 2;
Jxl. write. Label end0 = new jxl. write. Label (0, end, "Total ",
NormalFormat );
Ws. addCell (end0 );
Jxl. write. Label end1 = new jxl. write. Label (1, end ,"--",
NormalFormat );
Ws. addCell (end1 );
Jxl. write. Label end2 = new jxl. write. Label (2, end ,"--",
NormalFormat );
Ws. addCell (end2 );
Jxl. write. Label end3 = new jxl. write. Label (3, end ,"--",
NormalFormat );
Ws. addCell (end3 );
Jxl. write. Label end4 = new jxl. write. Label (4, end ,"--",
NormalFormat );
Ws. addCell (end4 );
Jxl. write. Label end5 = new jxl. write. Label (5, end ,"--",
NormalFormat );
Ws. addCell (end5 );
Jxl. write. Label end6 = new jxl. write. Label (6, end ,"--",
NormalFormat );
Ws. addCell (end6 );
Jxl. write. Label end7 = new jxl. write. Label (7, end ,"--",
NormalFormat );
Ws. addCell (end7 );
Jxl. write. Label end8 = new jxl. write. Label (8, end ,"--",
NormalFormat );
Ws. addCell (end8 );
Jxl. write. Label end9 = new jxl. write. Label (9, end ,"--",
NormalFormat );
Ws. addCell (end9 );
Jxl. write. Label end10 = new jxl. write. Label (10, end ,"--",
NormalFormat );
Ws. addCell (end10 );
Jxl. write. Label end11 = new jxl. write. Label (11, end ,"--",
NormalFormat );
Ws. addCell (end11 );
Jxl. write. Label end12 = new jxl. write. Label (12, end ,"--",
NormalFormat );
Ws. addCell (end12 );
Jxl. write. Label end13 = new jxl. write. Label (13, end ,"--",
NormalFormat );
Ws. addCell (end13 );
// Elder care
Jxl. write. Number end14 = new jxl. write. Number (14, end, yxjs, wcfN );
Ws. addCell (end14 );
// Unemployment
Jxl. write. Number end15 = new jxl. write. Number (15, end, syjs, wcfN );
Ws. addCell (end15 );
// Work Injury
Jxl. write. Number end16 = new jxl. write. Number (16, end, gsjs, wcfN );
Ws. addCell (end16 );
// Fertility
Jxl. write. Number end17 = new jxl. write. Number (17, end, sxjs, wcfN );
Ws. addCell (end17 );
// Medical care
Jxl. write. Number end18 = new jxl. write. Number (18, end, yljs, wcfN );
Ws. addCell (end18 );
// Write the Exel Worksheet
Wwb. write ();
// Close the Excel worksheet object
Wwb. close ();
OS. close ();
} Catch (Exception e ){
E. printStackTrace ();
}
}