Jxl exports Excel files and jxl exports excel files

Jxl exports Excel files and jxl exports excel files
I. java projects read and export Excel files

Code for reading and exporting Excel files:

Package servlet; import java. io. fileInputStream; import java. io. fileOutputStream; import java. io. IOException; import java. io. inputStream; import java. io. outputStream; import java. text. simpleDateFormat; import java. util. arrayList; import java. util. date; import java. util. list; import jxl. cell; import jxl. sheet; import jxl. workbook; import jxl. format. alignment; import jxl. format. border; import jxl. format. borderLineStyle; import jxl. format. cellFormat; import jxl. format. colour; import jxl. read. biff. biffException; import jxl. write. label; import jxl. write. writableCellFormat; import jxl. write. writableFont; import jxl. write. writableSheet; import jxl. write. writableWorkbook; import jxl. write. writeException; public class Excel {/*** @ param args * Excel reader * @ throws IOException * @ throws BiffException */public static void main (String [] args) throws BiffException, IOException {// export the Excel file writeExcel (); // read the Excel file // readExcel ();} // read the Excel file public static void readExcel () throws BiffException, IOException {// create a list to store the read content List = new ArrayList (); Workbook rwb = null; Cell cell = null; // create the input stream InputStream stream = new FileInputStream ("d :\\ testJXL.xls"); // obtain the Excel file object rwb = Workbook. getWorkbook (stream); // get the first Sheet sheet = rwb by default for the specified worksheet of the file. getSheet (0); // number of rows (the table header directory is not required, starting from 1) for (int I = 0; I <sheet. getRows (); I ++) {// create an array to store the value of each column String [] str = new String [sheet. getColumns ()]; // Number of columns for (int j = 0; j <sheet. getColumns (); j ++) {// obtain the value of row I and column j, cell = sheet. getCell (j, I); str [j] = cell. getContents ();} // Save the obtained column to the list. add (str) ;}for (int I = 0; I <list. size (); I ++) {String [] str = (String []) list. get (I); for (int j = 0; j <str. length; j ++) {System. out. println (str [j]) ;}}/ *** write an Excel file */public static void writeExcel () {String [] title = {"no ", "Product Name", "product price", "product quantity", "production date", "Origin", "Export "}; try {// obtain the start time long start = System. currentTimeMillis (); // output excel path String filePath = "e: \ testJXL.xls"; // create an Excel worksheet WritableWorkbook wwb; // create a new jxlfile, that is, testjxl.xls OutputStream OS = new FileOutputStream (filePath); wwb = Workbook. createWorkbook (OS); // Add the first worksheet and set the name of the first Sheet to WritableSheet = wwb. createSheet ("product list", 0); Label label; for (int I = 0; I <title. length; I ++) {// Label (x, y, z) indicates column x + 1 of the cell, row y + 1 of the cell, content z // specify the position and content of the cell in the sub-object of the Label object // label = new Label (I, 0, title [I]); label = new Label (I, 0, title [I], getHeader (); // Add the defined cells to the sheet. addCell (label);} // fill in the data below/** Save the number to the cell, you need to use jxl. write. number must use its full path; otherwise, an error may occur * // fill in the product Number jxl. write. number number = new jxl. write. number (0, 1, 20071001); sheet. addCell (number); // enter the product name label = new Label (1, 1, "golden pigeon melon seeds"); sheet. addCell (label);/** defines the common format of the displayed amount. jxl is automatically rounded. For example, 2.456 is formatted as 2.46, and 2.454 is formatted as 2.45 */jxl. write. numberFormat nf = new jxl. write. numberFormat ("#,###. 00 "); jxl. write. writableCellFormat wcf = new jxl. write. writableCellFormat (nf); // filling product price jxl. write. number nb = new jxl. write. number (2, 1, 200000.45, wcf); sheet. addCell (nb); // fill in the number of products jxl. write. number numb = new jxl. write. number (3, 1,200); sheet. addCell (numb);/** defines the public format of the display date, for example, yyyy-MM-dd hh: mm */SimpleDateFormat sdf = new SimpleDateFormat ("yyyy-MM-dd"); String newdate = sdf. format (new Date (); // fill in the production Date label = new Label (4, 1, newdate); sheet. addCell (label); // filling place label = new Label (5, 1, "Xi'an, Shaanxi"); sheet. addCell (label);/** display Boolean value */jxl. write. boolean bool = new jxl. write. boolean (6, 1, true); sheet. addCell (bool);/** merge cells by using writablesheet. mergeCells (int x, int y, int m, int n); * indicates that the x + 1 column, y + 1 row, and m + 1 column, merge n + 1 rows */sheet. mergeCells (0, 3, 2, 3); label = new Label (0, 3, "three cells merged"); sheet. addCell (label);/*** defines the public font format. by obtaining a font style, the template first uses the web. getSheet (0) obtains the first sheet * And then obtains the second column of the first sheet. The first row is the font "Product Name" */CellFormat cf = wwb. getSheet (0 ). getCell (1, 0 ). getCellFormat (); WritableCellFormat wc = new WritableCellFormat (); // set the center wc. setAlignment (Alignment. CENTRE); // set the border line wc. setBorder (Border. ALL, BorderLineStyle. THIN); // set the background color of the cell wc. setBackground (jxl. format. colour. RED); label = new Label (1, 5, "font", wc); sheet. addCell (label); // set the font jxl. write. writableFont wfont = new jxl. write. writableFont (WritableFont. createFont (""), 20); WritableCellFormat font = new WritableCellFormat (wfont); label = new Label (2, 6, "", font); sheet. addCell (label); // write data to wwb. write (); // close the wwb file. close (); long end = System. currentTimeMillis (); System. out. println ("---- the time for completing this operation is:" + (end-start)/1000);} catch (Exception e) {System. out. println ("--- exception ---"); e. printStackTrace () ;}}/*** set the Header style * @ return */public static WritableCellFormat getHeader () {WritableCellFormat format = null; try {WritableFont font = new WritableFont (WritableFont. TIMES, 10, WritableFont. BOLD); // defines the font. setColour (Colour. BLUE); // The BLUE font format = new WritableCellFormat (font); format. setAlignment (jxl. format. alignment. CENTRE); // format in the left-right corner. setVerticalAlignment (jxl. format. verticalAlignment. CENTRE); // The Upper and Lower center format. setBorder (Border. ALL, BorderLineStyle. THIN, Colour. BLACK); // BLACK border format. setBackground (Colour. YELLOW); // YELLOW background} catch (WriteException e) {e. printStackTrace ();} return format ;}}

Export result:

2. Export an Excel file 2.1 in a web project and create a dynamic web project

After you create a project, add the jxl-2.6.jar and servlet-api.jar files to the project.

2.2 create a data entity object Member. java

package entity;import java.util.Date;public class Member implements java.io.Serializable {// Fieldsprivate String id;private String checkOrg;private String sn;private String memberName;private String sex;private String cardId;private String duty;private String title;private String academic;private String special;private String workTime;private String memo;private String role;private Date lastModify;    private Date regTime;// Constructors/** default constructor */public Member() {}/** full constructor */public Member(String checkOrg, String sn, String memberName, String sex,String cardId, String duty, String title, String academic,String special, String workTime, String memo, String role, Date lastModify, Date regTime) {this.checkOrg = checkOrg;this.sn = sn;this.memberName = memberName;this.sex = sex;this.cardId = cardId;this.duty = duty;this.title = title;this.academic = academic;this.special = special;this.workTime = workTime;this.memo = memo;this.role = role;this.lastModify = lastModify;this.regTime = regTime;}// Property accessorspublic String getId() {return this.id;}public void setId(String id) {this.id = id;}public String getCheckOrg() {return this.checkOrg;}public void setCheckOrg(String checkOrg) {this.checkOrg = checkOrg;}public String getSn() {return this.sn;}public void setSn(String sn) {this.sn = sn;}public String getMemberName() {return this.memberName;}public void setMemberName(String memberName) {this.memberName = memberName;}public String getSex() {return this.sex;}public void setSex(String sex) {this.sex = sex;}public String getCardId() {return this.cardId;}public void setCardId(String cardId) {this.cardId = cardId;}public String getDuty() {return this.duty;}public void setDuty(String duty) {this.duty = duty;}public String getTitle() {return this.title;}public void setTitle(String title) {this.title = title;}public String getAcademic() {return this.academic;}public void setAcademic(String academic) {this.academic = academic;}public String getSpecial() {return this.special;}public void setSpecial(String special) {this.special = special;}public String getWorkTime() {return this.workTime;}public void setWorkTime(String workTime) {this.workTime = workTime;}public String getMemo() {return this.memo;}public void setMemo(String memo) {this.memo = memo;}public String getRole() {return this.role;}public void setRole(String role) {this.role = role;}public Date getLastModify() {return lastModify;}public void setLastModify(Date lastModify) {this.lastModify = lastModify;}public Date getRegTime() {return regTime;}public void setRegTime(Date regTime) {this.regTime = regTime;}}

2.3 Add the servlet File: ExportExlServlet. java

Package servlet; import java. io. IOException; import java. io. outputStream; import java. util. arrayList; import java. util. date; import java. util. list; import javax. servlet. servletException; import javax. servlet. http. httpServlet; import javax. servlet. http. httpServletRequest; import javax. servlet. http. httpServletResponse; import jxl. workbook; import jxl. format. border; import jxl. format. borderLineStyle; import jxl. format. colour; import jxl. format. verticalAlignment; import jxl. read. biff. biffException; import jxl. write. label; import jxl. write. writableCellFormat; import jxl. write. writableFont; import jxl. write. writableSheet; import jxl. write. writableWorkbook; import jxl. write. writeException; import jxl. write. biff. rowsExceededException; import entity. member; public class ExportExlServlet extends HttpServlet {private WritableWorkbook wwb = null; private WritableSheet sheet = null; private WritableSheet sheetk = null; private WritableSheet sheeth = null; @ paivoid service (HttpServletRequest request, httpServletResponse response) throws ServletException, IOException {String checkOrgId = null; String orgName = "XX unit"; try {exportCheckOrgMember (checkOrgId, orgName, response);} catch (release e) {e. printStackTrace ();} catch (WriteException e) {e. printStackTrace ();} exportExcel (response, wwb, orgName + "Personnel Schedule ");} /*** export data processing ** @ param checkOrgId * @ param orgName * @ param response * @ throws IOException * @ throws writable * @ throws WriteException */private void exportCheckOrgMember (String checkOrgId, string orgName, HttpServletResponse response) throws IOException, RowsExceededException, WriteException {// here listMember needs to be set to List <Member> listMember = new ArrayList <Member> (); member member1 = new Member ("str", "str ", "str", "str", "", "str", new Date (), new Date (); listMember. add (member1); listMember. add (member1); listMember. add (member1); System. out. println (listMember. size () + "***"); response. setContentType ("application/ms-excel"); String sheetName _ = orgName + "personnel list"; // file name = XX personnel list String sheetName = new String (sheetName _. getBytes (), "iso8859-1"); response. addHeader ("Content-Disposition", "attachment; filename =" + sheetName + ". xls "); OutputStream OS = response. getOutputStream (); wwb = Workbook. createWorkbook (OS); wwb. setProtected (false); // A sheetsheetk in EXECL = wwb. createSheet ("personnel details", 0 ); // ================ set some attributes of the execl table ============ WritableFont wf = new WritableFont (WritableFont. ARIAL, 13, WritableFont. BOLD, false); WritableCellFormat wcf = new WritableCellFormat (wf); WritableFont wf1 = new WritableFont (WritableFont. ARIAL, 13, WritableFont. NO_BOLD, false); WritableCellFormat wcf1 = new WritableCellFormat (wf1); wcf. setBorder (Border. ALL, BorderLineStyle. THIN, Colour. BLACK); wcf. setVerticalAlignment (verticalignment. CENTRE); wcf1.setBorder (Border. ALL, BorderLineStyle. THIN, Colour. BLACK); wcf1.setVerticalAlignment (VerticalAlignment. CENTRE); // ================ set some attributes of the execl table ====== END =======/* sheetk. mergeCells (0, 0, 9, 0); sheetk. addCell (new Label (0, 0, "This is a merged cell", wcf); // Add a new Label (column location, row location, cell content, writableCellFormat object) // here, the second parameter 1 represents the second row. The cells merged above are the first row of sheetk. addCell (new Label (0, 1, "Serial Number", wcf); sheetk. addCell (new Label (1, 1, "name", wcf); sheetk. addCell (new Label (2, 1, "gender", wcf); sheetk. addCell (new Label (3, 1, "ID card number", wcf); sheetk. addCell (new Label (4, 1, "education", wcf); sheetk. addCell (new Label (5, 1, "professional", wcf); sheetk. addCell (new Label (6, 1, "title", wcf); sheetk. addCell (new Label (7, 1, "title", wcf); sheetk. addCell (new Label (8, 1, "role", wcf); sheetk. addCell (new Label (9, 1, "Remarks", wcf); */String [] title = {"no.", "name", "gender ", "ID card number", "education", "Major", "title", "title", "role", "Remarks"}; Label label; for (int I = 0; I <title. length; I ++) {// Label (x, y, z) indicates column x + 1 of the cell, row y + 1 of the cell, content z // specify the position and content of the cell in the sub-object of the Label object // label = new Label (I, 0, title [I]); label = new Label (I, 0, title [I], Excel. getHeader (); // Add the defined cells to the sheet. addCell (label);} // set the font jxl. write. writableFont wfont = new jxl. write. writableFont (WritableFont. createFont (""), 20); WritableCellFormat font = new WritableCellFormat (wfont); // Loop Data, fill the data in the cell int t = 1; for (Member member: listMember) {sheetk. addCell (new Label (0, t, t + "", wcf1); sheetk. addCell (new Label (1, t, member. getMemberName (), wcf1); sheetk. addCell (new Label (2, t, member. getSex (), wcf1); sheetk. addCell (new Label (3, t, member. getCardId (), wcf1); sheetk. addCell (new Label (4, t, member. getAcademic (), wcf1); sheetk. addCell (new Label (5, t, member. getSpecial (), wcf1); sheetk. addCell (new Label (6, t, member. getTitle (), wcf1); sheetk. addCell (new Label (7, t, member. getDuty (), wcf1); sheetk. addCell (new Label (8, t, member. getRole (), wcf1); sheetk. addCell (new Label (9, t, member. getMemo (), font); t ++ ;}/ ** execute the export operation */private void exportExcel (HttpServletResponse response, WritableWorkbook retValue, String filename) {response. setContentType ("application/ms-excel");/* response. addHeader ("Content-Disposition", "attachment; filename =" + filename + ". xls "); */try {retValue. write (); retValue. close ();} catch (IOException e) {e. printStackTrace ();} catch (WriteException e) {e. printStackTrace ();}}}

2.4 Add the servlet configuration in the web. xml file:

<?xml version="1.0" encoding="UTF-8"?><web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">  <display-name></display-name>  <welcome-file-list>    <welcome-file>index.jsp</welcome-file>  </welcome-file-list>  <servlet>  <servlet-name>ExportExlServlet</servlet-name>  <servlet-class>servlet.ExportExlServlet</servlet-class>  </servlet>  <servlet-mapping>  <servlet-name>ExportExlServlet</servlet-name>  <url-pattern>/ExportExlServlet</url-pattern>  </servlet-mapping></web-app>

2.5 add index. jsp request servlet

<Body> This is my JSP page. <br> <a href = "ExportExlServlet"> export data </a> </body>

Export result:

Source code: https://github.com/ablejava/Jxl-Excel