前面寫過了幾次操作Excel的,第一和第二次的都寫的是main原生.當然,你要套用到WEB當中完全可以,其實網上的實現方法很多,只要思路清晰,做一兩個實驗,就會很快的運用此JXL為自己服務了.
其實很多文字上的都是寫好了的.這裡只是貼出來,然後貼出做好的代碼.
包:jxl.jar
/*<br />前面介紹了main的匯入匯出,都相當簡單,沒有經過一些特別的修飾,只是可以操作,這幾天又閑的沒事,就每天寫一小點吧,今天肯定寫不完,因為還想寫點然後搞搞其他的東西,所以就寫一部分吧.<br />JXL針對匯出Excel其實相當簡單.流程大致相當清晰:<br />顯示頁面開始--->匯出按鈕(可以做成全部匯出,或者匯出你所想要的部分)--->選擇你所要儲存的目錄--->開始匯出(讀資料-->寫入Excel檔案)--->完成</p><p>然後就是來看具體的程式操作了,<br />要下載首先還是要資料,</p><p>顯示資料,全部都寫在了JSP裡面,其實就是簡單的SQL語句而已,然後就是一個按鈕,相當簡單的操作,其實匯出就跟main函數一個樣,就是直接的指定匯出就可以了.<br />這裡唯一多的就是對於多了對Excel的處理,簡單的看看Excel的樣式,大家就會知道.有什麼標題,頁碼(就是左下角的翻頁),還有就是一些匯入格式(內容標題,內容台頭,還有就是字型,列的寬高等等).</p><p>*/
下面開始代碼:
第一:jsp部分
<%@ page language="java" contentType="text/html; charset=utf-8"<br /> pageEncoding="utf-8"%><br /><%@ page import="java.util.*,java.sql.*"%><br /><% String webapp = request.getContextPath();%><br /><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><br /><html><br /><head><br /><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><br /><title>簡單的匯出操作</title><br /><mce:script type="text/javascript"><!--</p><p>//點擊按鈕就進行匯出操作,簡單吧,既不需要其他的操作,就是直接的簡單的點擊一下,其他就交給前台處理,當然只要有可能,你可以自己寫不同的匯出操作.<br />function download(){<br />rul='<%=webapp%>/servlet/DownloadServlet?action=download';<br />window.location.href=rul;<br />}</p><p>// --></mce:script><br /></head></p><p><body><br /><%<br />try{<br />Class.forName("com.mysql.jdbc.Driver").newInstance();<br />Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:5885/excel","root","5885");</p><p>String sql = "select * from upload";<br />String sql1 = "select count(*) from upload";</p><p>Statement stat = conn.createStatement();<br />ResultSet resl = stat.executeQuery(sql1);</p><p>resl.next();<br />int row = resl.getInt(1);//取得記錄總數,可以不要,願意想用其他的列表之類的列出來,後來用到了它自身的取得資料,<br />//所以這個就是沒有用的,但是後來沒有刪除掉,所以就保留著.</p><p>resl = stat.executeQuery(sql);<br />%></p><p><p align="center">所有結果顯示如下:</p><br /><p align="center"><input type="button" value="匯出" onclick="download()"></input></p><br /><% if(row>0){ %><br /><table border="1" align="center" width="100%"><br /><tr><td width="30%" align="center"> ---編號--- </td><td width="40%" align="center"> ---姓名--- </td><td width="30%" align="center"> ---地址--- </td></tr><br /><%<br />//for(int i = 0;i<=row;i++){<br />while(resl.next()){<br />%><br /><tr><br /><td><%=String.valueOf(resl.getInt(1)) %></td><br /><td><%=String.valueOf(resl.getString(2)) %></td><br /><td><%=String.valueOf(resl.getString(3)) %></td><br /></tr><br /><%} %><br /></table><br /><%}%><br /><%<br />resl.close();<br />stat.close();<br />conn.close();<br />}catch(Exception e){<br />//<br />}<br /> %><br /></body><br /></html>
第二:Java Servlet部分
package download;</p><p>import java.io.IOException;<br />import java.io.OutputStream;<br />import java.io.PrintWriter;</p><p>import javax.servlet.ServletException;<br />import javax.servlet.http.HttpServlet;<br />import javax.servlet.http.HttpServletRequest;<br />import javax.servlet.http.HttpServletResponse;</p><p>import java.sql.*;<br />import java.util.ArrayList;<br />import java.util.HashMap;<br />import java.util.List;<br />import java.util.Map;<br />import java.util.Vector;</p><p>import jxl.Workbook;<br />import jxl.write.Label;<br />import jxl.write.WritableCellFormat;<br />import jxl.write.WritableFont;<br />import jxl.write.WritableWorkbook;<br />import jxl.write.WriteException;</p><p>@SuppressWarnings("serial")<br />public class DownloadServlet extends HttpServlet {</p><p>public DownloadServlet() {<br />super();<br />}</p><p>public void destroy() {<br />super.destroy(); // Just puts "destroy" string in log<br />// Put your code here<br />}</p><p>public void doGet(HttpServletRequest request, HttpServletResponse response)<br />throws ServletException, IOException {<br />this.doPost(request, response);<br />}<br />public void doPost(HttpServletRequest request, HttpServletResponse response)<br />throws ServletException, IOException {</p><p>//準備工作,<br />response.setContentType("text/html");<br />response.setCharacterEncoding("UTF-8");<br />//PrintWriter out = response.getWriter();<br />String flag = request.getParameter("action");</p><p>if(flag.equals("download")){<br />try{<br />Class.forName("com.mysql.jdbc.Driver").newInstance();<br />Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:5885/excel","root","5885");</p><p>String sql = "select * from upload";<br />Statement stat = conn.createStatement();<br />ResultSet resl = stat.executeQuery(sql);</p><p>List list = toList(resl);</p><p>String fileName = "downloadTest";<br />String sheetName = "第一頁";<br />String[] titles = {"ID編號","姓名","住址"};<br />Vector vector = new Vector();<br />for(int i=0;i<list.size();i++){<br />Map map = (Map)list.get(i);<br />String[] excelData = {String.valueOf(map.get("Id")),String.valueOf(map.get("name")),String.valueOf(map.get("address"))};<br />vector.add(excelData);<br />}<br />exportToExcel(response,fileName,sheetName,titles,vector);</p><p>}catch(Exception e){<br />//<br />}<br />}<br />//out.flush();<br />//out.close();<br />}</p><p>public void init() throws ServletException {<br />// Put your code here<br />}</p><p>//將result轉換為list<br />public List toList(ResultSet rs)<br /> {<br /> try<br /> {<br /> List list = new ArrayList();<br /> ResultSetMetaData md = rs.getMetaData();<br /> int columnCount = md.getColumnCount();<br /> //Map rowData;<br /> while (rs.next()) {<br /> //rowData = new HashMap(columnCount);<br /> Map rowData = new HashMap();<br /> for (int i = 1; i <= columnCount; i++) {<br /> rowData.put(md.getColumnName(i), rs.getObject(i));<br /> }<br /> list.add(rowData);<br /> }<br /> return list;<br /> }<br /> catch(Exception ex)<br /> {<br /> ex.printStackTrace();<br /> return null;<br /> } </p><p> }</p><p>//這裡定義一個公用的方法,匯出Excel檔案.<br />public void exportToExcel(HttpServletResponse res, String fileName,<br />String sheetname, String[] titles, Vector vect) throws Exception {<br />OutputStream os = null;<br />try {</p><p>os = res.getOutputStream(); //取得輸出資料流<br />res.reset(); // 清空輸出資料流<br />res.setHeader("Content-disposition", "attachment; filename="<br />+ fileName + ".xls"); //設定輸出檔案頭<br />res.setContentType("application/msexcel"); //定義輸出類型<br />} catch (IOException ex) {<br />System.out.println("流操作錯誤:" + ex.getMessage());<br />}<br />WritableWorkbook workbook = null;<br />try {<br />// 建立新的Excel 活頁簿這個是哪裡都必須有的,就是這些步驟,其實其他的相關操作,比如定義表頭,定義標題,定義字型等等,都可以不要,因為只要有了個主架,能夠寫入資料,就夠了.<br />workbook = Workbook.createWorkbook(os);<br />// 在Excel活頁簿中建一工作表，其名為:第一頁<br />jxl.write.WritableSheet wsheet = workbook.createSheet(sheetname, 0); // sheet();<br />//基本的就是建立一個 活頁簿Excel.---> 然後就是這個Excel的頁.--->最後寫入資料.<br />//中間可能有些修飾.具體的就按照自己的需求定義.查詢API,很明顯就能夠得到,搜尋網路,一大堆都是.<br />//資料才是最主要的,一個標題,一個列名描述就夠了,其他的設定其實沒有必要,</p><p>//設定每一列的寬度.第一個參數是列,第二個是多寬按厘米記,<br />wsheet.setColumnView(0, 30);//第一列<br />wsheet.setColumnView(1, 30);//第二列<br />wsheet.setColumnView(2, 30);//第三列</p><p>WritableFont font = new WritableFont(WritableFont.ARIAL, 12,<br />WritableFont.BOLD, false,<br />jxl.format.UnderlineStyle.NO_UNDERLINE,<br />jxl.format.Colour.BLACK);<br />WritableCellFormat format = new WritableCellFormat(font);<br />for (int i = 0; i < titles.length; i++) {<br />Label wlabel1 = new Label(i, 0, String.valueOf(titles[i]),<br />format); // 行、列、儲存格中的文本、文字格式設定<br />wsheet.addCell(wlabel1);<br />}<br />font = new jxl.write.WritableFont(WritableFont.createFont("宋體"),<br />12, WritableFont.NO_BOLD, false,<br />jxl.format.UnderlineStyle.NO_UNDERLINE,<br />jxl.format.Colour.BLACK);<br />format = new jxl.write.WritableCellFormat(font);<br />for (int i = 1; i <= vect.size(); i++) { // 在索引0的位置建立行（最頂端的行）<br />String[] sdata = (String[]) vect.elementAt(i - 1);<br />for (int j = 0; j < sdata.length; j++) { // 在索引0的位置建立儲存格（左上端）<br />Label wlabel1 = new Label(j, i, sdata[j], format); // 行、列、儲存格中的文本、文字格式設定<br />wsheet.addCell(wlabel1);<br />}<br />}<br />workbook.write(); // 寫入檔案<br />workbook.close();<br />os.close();<br />} catch (WriteException ex1) {<br />System.out.println("WriteException:" + ex1.getMessage());<br />} catch (IOException ex2) {<br />System.out.println("IOException:" + ex2.getMessage());<br />}<br />// res.flushBuffer();<br />}</p><p>}<br />
第三:WEB配置
</servlet><br /> <servlet><br /> <servlet-name>DownloadServlet</servlet-name><br /> <servlet-class>download.DownloadServlet</servlet-class><br /> </servlet><br /> <servlet-mapping><br /> <servlet-name>DownloadServlet</servlet-name><br /> <url-pattern>/servlet/DownloadServlet</url-pattern><br /> </servlet-mapping><br />
第四:資料庫檔案.(為了避免上傳.所以給出來了)
# Host: localhost:5885 Database: excel<br /># ------------------------------------------------------<br /># Server version 5.0.51b-community-nt</p><p>DROP DATABASE IF EXISTS `excel`;<br />CREATE DATABASE `excel` /*!40100 DEFAULT CHARACTER SET utf8 */;<br />USE `excel`;</p><p>#<br /># Table structure for table test<br />#</p><p>CREATE TABLE `test` (<br /> `Id` int(11) NOT NULL auto_increment,<br /> `name` varchar(20) default NULL,<br /> PRIMARY KEY (`Id`)<br />) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;</p><p>CREATE TABLE `upload` (<br /> `Id` int(8) NOT NULL default '0',<br /> `name` varchar(30) NOT NULL default '',<br /> `address` varchar(30) NOT NULL default '',<br /> PRIMARY KEY (`Id`)<br />) ENGINE=InnoDB DEFAULT CHARSET=utf8;</p><p>#<br /># Dumping data for table upload<br />#<br />LOCK TABLES `upload` WRITE;<br />/*!40000 ALTER TABLE `upload` DISABLE KEYS */;</p><p>INSERT INTO `upload` VALUES (1,'1','1');<br />INSERT INTO `upload` VALUES (2,'2','2');<br />INSERT INTO `upload` VALUES (3,'3','3');<br />INSERT INTO `upload` VALUES (4,'4','4');<br />INSERT INTO `upload` VALUES (5,'5','5');<br />INSERT INTO `upload` VALUES (6,'6','6');<br />INSERT INTO `upload` VALUES (7,'7','7');<br />INSERT INTO `upload` VALUES (12,'11232','1231312');<br />INSERT INTO `upload` VALUES (13,'12313','123313');<br />INSERT INTO `upload` VALUES (14,'3214','321214');<br />INSERT INTO `upload` VALUES (15,'sdf15','132s15');<br />INSERT INTO `upload` VALUES (16,'1616','161616');<br />INSERT INTO `upload` VALUES (17,'fsd','fsdf');<br />INSERT INTO `upload` VALUES (18,'sd','fsdf');<br />INSERT INTO `upload` VALUES (19,'sdf','sdfsd');<br />INSERT INTO `upload` VALUES (20,'sdf','sdf');<br />INSERT INTO `upload` VALUES (21,'sdf','sdf');<br />INSERT INTO `upload` VALUES (22,'sdf','sdf');<br />INSERT INTO `upload` VALUES (23,'sdf','sdf');<br />INSERT INTO `upload` VALUES (24,'sdf','sdf');<br />INSERT INTO `upload` VALUES (25,'sdf','sdf');<br />INSERT INTO `upload` VALUES (26,'sdf','ssdf');<br />INSERT INTO `upload` VALUES (27,'sdf','sdf');<br />INSERT INTO `upload` VALUES (28,'sdf','sdf');<br />/*!40000 ALTER TABLE `upload` ENABLE KEYS */;<br />UNLOCK TABLES;
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
