jsp表格导出到EXCEL

JSP中导入导出Excel文件一．POI简介Jakarta POI 是apache的子项目，目标是处理ole2对象。

它提供了一组操纵Windows文档的Java API目前比较成熟的是HSSF接口，处理MS Excel（97-2002）对象。

它不象我们仅仅是用csv生成的没有格式的可以由Excel转换的东西，而是真正的Excel 对象，你可以控制一些属性如sheet,cell等等。

二．HSSF概况HSSF 是sHorrible SpreadSheet Format的缩写，也即“讨厌的电子表格格式”。

也许HSSF的名字有点滑稽，就本质而言它是一个非常严肃、正规的API。

通过HSSF，你可以用纯Java代码来读取、写入、修改Excel文件。

HSSF 为读取操作提供了两类API：usermodel和eventusermodel，即“用户模型”和“事件-用户模型”。

前者很好理解，后者比较抽象，但操作效率要高得多。

三．开始编码1 ．准备工作要求:JDK 1.4+POI开发包可以到 /dyn/closer.cgi/jakarta/poi/ 最新的POI工具包2 ． EXCEL 结构HSSFWorkbook excel 文档对象介绍HSSFSheet excel的表单HSSFRow excel的行HSSFCell excel的格子单元HSSFFont excel字体HSSFName 名称HSSFDataFormat 日期格式HSSFHeader sheet头HSSFFooter sheet尾和这个样式HSSFCellStyle cell样式辅助操作包括HSSFDateUtil 日期HSSFPrintSetup 打印HSSFErrorConstants 错误信息表4 ．可参考文档POI 主页：/poi/，初学者如何快速上手使用POI HSSF/poi/hssf/quick-guide.html 。

代码例子/user1/6749/archives/2005/18347.html里面有很多例子代码，可以很方便上手。

纯JS将table表格导出到excel的⽅法html<div ><button type="button" onclick="getXlsFromTbl('tableExcel','myDiv')">IE导出Excel⽅法</button><button type="button" onclick="method5('tableExcel')">Chrome导出Excel</button></div><div id="myDiv"><table id="tableExcel" width="100%" border="1" cellspacing="0" cellpadding="0"><tr><td colspan="5" align="center">html 表格导出到Excel</td></tr><tr><td>列标题1</td><td>列标题2</td><td>类标题3</td><td>列标题4</td><td>列标题5</td></tr><tr><td>aaa</td><td>bbb</td><td>ccc</td><td>ddd</td><td>eee</td></tr><tr><td>AAA</td><td>BBB</td><td>CCC</td><td>DDD</td><td>EEE</td></tr><tr><td>FFF</td><td>GGG</td><td>HHH</td><td>III</td><td>JJJ</td></tr></table></div>js<script language="JavaScript" type="text/javascript">//IE⽅法function getXlsFromTbl(inTblId, inWindow){try {var allStr = "";var curStr = "";if (inTblId != null && inTblId != "" && inTblId != "null") {curStr = getTblData(inTblId, inWindow);}if (curStr != null) {allStr += curStr;}else {alert("你要导出的表不存在");return;}var fileName = getExcelFileName();doFileExport(fileName, allStr);}catch(e) {alert("导出发⽣异常:" + + "->" + e.description + "!");}}function getTblData(inTbl, inWindow) {var rows = 0;var tblDocument = document;if (!!inWindow && inWindow != "") {if (!document.all(inWindow)) {return null;}else {tblDocument = eval(inWindow).document;}}var curTbl = tblDocument.getElementById(inTbl);var outStr = "";if (curTbl != null) {for (var j = 0; j < curTbl.rows.length; j++) {for (var i = 0; i < curTbl.rows[j].cells.length; i++) {if (i == 0 && rows > 0) {outStr += " t";rows -= 1;}outStr += curTbl.rows[j].cells[i].innerText + "t";if (curTbl.rows[j].cells[i].colSpan > 1) {for (var k = 0; k < curTbl.rows[j].cells[i].colSpan - 1; k++) { outStr += " t";}}if (i == 0) {if (rows == 0 && curTbl.rows[j].cells[i].rowSpan > 1) {rows = curTbl.rows[j].cells[i].rowSpan - 1;}}}outStr += "rn";}}else {outStr = null;alert(inTbl + "不存在 !");}return outStr;}function getExcelFileName() {var d = new Date();var curYear = d.getYear();var curMonth = "" + (d.getMonth() + 1);var curDate = "" + d.getDate();var curHour = "" + d.getHours();var curMinute = "" + d.getMinutes();var curSecond = "" + d.getSeconds();if (curMonth.length == 1) {curMonth = "0" + curMonth;}if (curDate.length == 1) {curDate = "0" + curDate;}if (curHour.length == 1) {curHour = "0" + curHour;}if (curMinute.length == 1) {curMinute = "0" + curMinute;}if (curSecond.length == 1) {curSecond = "0" + curSecond;}var fileName = "table" + "_" + curYear + curMonth + curDate + "_"+ curHour + curMinute + curSecond + ".csv";return fileName;}function doFileExport(inName, inStr) {var xlsWin = null;if (!!document.all("glbHideFrm")) {xlsWin = glbHideFrm;}else {var width = 6;var height = 4;var openPara = "left=" + (window.screen.width / 2 - width / 2)+ ",top=" + (window.screen.height / 2 - height / 2)+ ",scrollbars=no,width=" + width + ",height=" + height;xlsWin = window.open("", "_blank", openPara);}xlsWin.document.write(inStr);xlsWin.document.close();xlsWin.document.execCommand('Saveas', true, inName);xlsWin.close();}//Chrome⽅法var idTmr;function getExplorer() {var explorer = erAgent ;//ieif (explorer.indexOf("MSIE") >= 0) {return 'ie';}//firefoxelse if (explorer.indexOf("Firefox") >= 0) {return 'Firefox';}//Chromeelse if(explorer.indexOf("Chrome") >= 0){return 'Chrome';}//Operaelse if(explorer.indexOf("Opera") >= 0){return 'Opera';}//Safarielse if(explorer.indexOf("Safari") >= 0){return 'Safari';}}function method5(tableid) {if(getExplorer()=='ie'){var curTbl = document.getElementById(tableid);var oXL = new ActiveXObject("Excel.Application");var oWB = oXL.Workbooks.Add();var xlsheet = oWB.Worksheets(1);var sel = document.body.createTextRange();sel.moveToElementText(curTbl);sel.select();sel.execCommand("Copy");xlsheet.Paste();oXL.Visible = true;try {var fname = oXL.Application.GetSaveAsFilename("Excel.xls", "Excel Spreadsheets (*.xls), *.xls"); } catch (e) {print("Nested catch caught " + e);} finally {oWB.SaveAs(fname);oWB.Close(savechanges = false);oXL.Quit();oXL = null;idTmr = window.setInterval("Cleanup();", 1);}}else{tableToExcel(tableid)}}function Cleanup() {window.clearInterval(idTmr);CollectGarbage();}var tableToExcel = (function() {var uri = 'data:application/vnd.ms-excel;base64,',template = '<html><head><meta charset="UTF-8"></head><body><table>{table}</table></body></html>',base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) },format = function(s, c) {return s.replace(/{(\w+)}/g,function(m, p) { return c[p]; }) }return function(table, name) {if (!table.nodeType) table = document.getElementById(table)var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML}window.location.href = uri + base64(format(template, ctx))}})()</script>到此这篇关于纯JS将table表格导出到excel的⽅法的⽂章就介绍到这了,更多相关js table表格导出excel内容请搜索以前的⽂章或继续浏览下⾯的相关⽂章希望⼤家以后多多⽀持！。

js导⼊导出excel（实例代码）复制代码代码如下:<html xmlns="/1999/xhtml" ><head><title>Untitled Page</title></head><script language="javascript" type="text/javascript">function importXLS(fileName){objCon = new ActiveXObject("ADODB.Connection");objCon.Provider = "Microsoft.Jet.OLEDB.4.0";objCon.ConnectionString = "Data Source=" + fileName + ";Extended Properties=Excel 8.0;";objCon.CursorLocation = 1;objCon.Open;var strQuery;//Get the SheetNamevar strSheetName = "Sheet1$";var rsTemp = new ActiveXObject("ADODB.Recordset");rsTemp = objCon.OpenSchema(20);if(!rsTemp.EOF)strSheetName = rsTemp.Fields("Table_Name").Value;rsTemp = null;rsExcel = new ActiveXObject("ADODB.Recordset");strQuery = "SELECT * FROM [" + strSheetName + "]";rsExcel.ActiveConnection = objCon;rsExcel.Open(strQuery);while(!rsExcel.EOF){for(i = 0;i<rsExcel.Fields.Count;++i){alert(rsExcel.Fields(i).value);}rsExcel.MoveNext;}// Close the connection and dispose the fileobjCon.Close;objCon =null;rsExcel = null;}</script></head><body><input type="file" id="f" /><input type="button" id="b" value="import" onclick="if(f.value=='')alert('请选择xls⽂件');else importXLS(f.value)" /> </body></html>复制代码代码如下:function AutomateExcel(){// Start Excel and get Application object.var oXL = new ActiveXObject("Excel.Application");oXL.Visible = true;// Get a new workbook.var oWB = oXL.Workbooks.Add();var oSheet = oWB.ActiveSheet;// Add table headers going cell by cell.oSheet.Cells(1, 1).Value = "First Name";oSheet.Cells(1, 2).Value = "Last Name";oSheet.Cells(1, 3).Value = "Full Name";oSheet.Cells(1, 4).Value = "Salary";// Format A1:D1 as bold, vertical alignment = center.oSheet.Range("A1", "D1").Font.Bold = true;oSheet.Range("A1", "D1").VerticalAlignment = -4108; //xlVAlignCenter// Create an array to set multiple values at once.// Fill A2:B6 with an array of values (from VBScript).oSheet.Range("A2", "B6").Value = CreateNamesArray();// Fill C2:C6 with a relative formula (=A2 & " " & B2).var oRng = oSheet.Range("C2", "C6");oRng.Formula = "=A2 & " " & B2";// Fill D2:D6 with a formula(=RAND()*100000) and apply format.oRng = oSheet.Range("D2", "D6");oRng.Formula = "=RAND()*100000";oRng.NumberFormat = "$0.00";// AutoFit columns A:D.oRng = oSheet.Range("A1", "D1");oRng.EntireColumn.AutoFit();// Manipulate a variable number of columns for Quarterly Sales Data. DispalyQuarterlySales(oSheet);// Make sure Excel is visible and give the user control// of Excel's lifetime.oXL.Visible = true;erControl = true;}<HTML><HEAD><TITLE>将页⾯中指定表格的数据导⼊到Excel中</TITLE><SCRIPT LANGUAGE="javascript"><!--function AutomateExcel(){var oXL = new ActiveXObject("Excel.Application"); //创建应该对象var oWB = oXL.Workbooks.Add();//新建⼀个Excel⼯作簿var oSheet = oWB.ActiveSheet;//指定要写⼊内容的⼯作表为活动⼯作表var table = document.all.data;//指定要写⼊的数据源的idvar hang = table.rows.length;//取数据源⾏数var lie = table.rows(0).cells.length;//取数据源列数// Add table headers going cell by cell.for (i=0;i<hang;i++){//在Excel中写⾏for (j=0;j<lie;j++){//在Excel中写列//定义格式oSheet.Cells(i+1,j+1).NumberFormatLocal = "@";//上⾯这⼀句是将单元格的格式定义为⽂本oSheet.Cells(i+1,j+1).Font.Bold = true;//加粗oSheet.Cells(i+1,j+1).Font.Size = 10;//字体⼤⼩oSheet.Cells(i+1,j+1).value = table.rows(i).cells(j).innerText;//向单元格写⼊值}}oXL.Visible = true;erControl = true;}//--></SCRIPT></HEAD><BODY><table border="0" width="300" id="data" bgcolor="black" cellspacing="1"><tr bgcolor="white"><td>编号</td><td>姓名</td><td>年龄</td><td>性别</td></tr><tr bgcolor="white"><td>0001</td><td>张三</td><td>22</td><td>⼥</td></tr><tr bgcolor="white"><td>0002</td><td>李四</td><td>23</td><td>男</td></tr></table><input type="button" name="out_excel" onclick="AutomateExcel();" value="导出到excel"></BODY></HTML>复制代码代码如下:<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""/TR/xhtml1/DTD/xhtml1-transitional.dtd"><!-- 导出excle的三个⽅法要把ie浏览器的"对没有标记为安全的 ActiveX 控件进⾏初始化和脚本运⾏设置为提⽰或启⽤" --> <html xmlns="/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=gb2312"><title>WEB页⾯导出为EXCEL⽂档的⽅法</title></head><body><table id="tableExcel" width="100%" border="1" cellspacing="0" cellpadding="0"><tr><td colspan="5" align="center">WEB页⾯导出为EXCEL⽂档的⽅法</td></tr><tr><td>列标题1</td><td>列标题2</td><td>列标题3</td><td>列标题4</td><td>列标题5</td></tr><tr><td>aaa</td><td></td><td>ccc</td><td>ddd</td><td>eee</td></tr><tr><td>AAA</td><td></td><td>CCC</td><td>DDD</td><td>EEE</td></tr><tr><td>FFF</td><td>GGG</td><td>HHH</td><td>III</td><td>JJJ</td></tr></table><input type="button" onclick="javascript:method1('tableExcel');" value="第⼀种⽅法导⼊到EXCEL"><input type="button" onclick="javascript:method2('tableExcel');" value="第⼆种⽅法导⼊到EXCEL"><input type="button" onclick="javascript:getXlsFromTbl('tableExcel',null);" value="第三种⽅法导⼊到EXCEL"> <script language="javascript">function method1(tableid) {//整个表格拷贝到EXCEL中var curTbl = document.getElementById(tableid);var oXL = new ActiveXObject("Excel.Application"); //创建AX对象excelvar oWB = oXL.Workbooks.Add(); //获取workbook对象var oSheet = oWB.ActiveSheet; //激活当前sheetvar sel = document.body.createTextRange();sel.moveToElementText(curTbl); //把表格中的内容移到TextRange中sel.select(); //全选TextRange中内容sel.execCommand("Copy"); //复制TextRange中内容oSheet.Paste(); //粘贴到活动的EXCEL中oXL.Visible = true; //设置excel可见属性}function method2(tableid) //读取表格中每个单元到EXCEL中{var curTbl = document.getElementById(tableid);var oXL = new ActiveXObject("Excel.Application"); //创建AX对象excelvar oWB = oXL.Workbooks.Add(); //获取workbook对象var oSheet = oWB.ActiveSheet; //激活当前sheetvar Lenr = curTbl.rows.length; //取得表格⾏数for (i = 0; i < Lenr; i++) {var Lenc = curTbl.rows(i).cells.length; //取得每⾏的列数for (j = 0; j < Lenc; j++) {oSheet.Cells(i + 1, j + 1).value = curTbl.rows(i).cells(j).innerText; //赋值 }}oXL.Visible = true; //设置excel可见属性}function getXlsFromTbl(inTblId, inWindow) {try {var allStr = "";var curStr = "";if (inTblId != null && inTblId != "" && inTblId != "null") {curStr = getTblData(inTblId, inWindow);}if (curStr != null) {allStr += curStr;}else {alert("你要导出的表不存在！");return;}var fileName = getExcelFileName();doFileExport(fileName, allStr);}catch (e) {alert("导出发⽣异常:" + + "->" + e.description + "!");}}function getTblData(inTbl, inWindow) {var rows = 0;var tblDocument = document;if (!!inWindow && inWindow != "") {if (!document.all(inWindow)) {return null;}else {tblDocument = eval(inWindow).document;}}var curTbl = tblDocument.getElementById(inTbl);var outStr = "";if (curTbl != null) {for (var j = 0; j < curTbl.rows.length; j++) {for (var i = 0; i < curTbl.rows[j].cells.length; i++) {if (i == 0 && rows > 0) {outStr += " /t";rows -= 1;}outStr += curTbl.rows[j].cells[i].innerText + "/t";if (curTbl.rows[j].cells[i].colSpan > 1) {for (var k = 0; k < curTbl.rows[j].cells[i].colSpan - 1; k++) { outStr += " /t";}}if (i == 0) {if (rows == 0 && curTbl.rows[j].cells[i].rowSpan > 1) { rows = curTbl.rows[j].cells[i].rowSpan - 1;}}}outStr += "/r/n";}}else {outStr = null;alert(inTbl + "不存在!");}return outStr;}function getExcelFileName() {var d = new Date();var curYear = d.getYear();var curMonth = "" + (d.getMonth() + 1);var curDate = "" + d.getDate();var curHour = "" + d.getHours();var curMinute = "" + d.getMinutes();var curSecond = "" + d.getSeconds();if (curMonth.length == 1) {curMonth = "0" + curMonth;}if (curDate.length == 1) {curDate = "0" + curDate;}if (curHour.length == 1) {curHour = "0" + curHour;}if (curMinute.length == 1) {curMinute = "0" + curMinute;}if (curSecond.length == 1) {curSecond = "0" + curSecond;}var fileName = "leo_zhang" + "_" + curYear + curMonth + curDate + "_" + curHour + curMinute + curSecond + ".csv";return fileName;}function doFileExport(inName, inStr) {var xlsWin = null;if (!!document.all("glbHideFrm")) {xlsWin = glbHideFrm;}else {var width = 6;var height = 4;var openPara = "left=" + (window.screen.width / 2 - width / 2)+ ",top=" + (window.screen.height / 2 - height / 2)+ ",scrollbars=no,width=" + width + ",height=" + height;xlsWin = window.open("", "_blank", openPara);}xlsWin.document.write(inStr);xlsWin.document.close();xlsWin.document.execCommand('Saveas', true, inName);xlsWin.close();}</script></body></html>。

1.excel导入数据导入时候注意数字类型和字符类型的区别，数字类型要在excel中设置数字类型，对应数据库中也应该建立对应类型<%@ page import="com.guanri.database.*,java.util.*,com.guanri.util.*,com.jspsmart.upload.*" contentType="text/html; charset=GBK" %><%@ pageimport="java.util.*,java.io.*,org.apache.poi.poifs.filesystem.*,ermodel.*" %><jsp:include page="/admin/checkLogin.jsp"/><%String result = "";String contracId = "";String projectname = "";double projectscale = 0;String projecttype="";String projectconunit="";double designtime = 0;String designphase = "";double completedtime = 0;String participants = "";String positionofpro = "";String dutiesas = "";int inum = 0;int unum = 0;int rnum = 0;int snum = 0;String rmstr = "";Table tb = new Table();Vector vc = null;Hashtable rows = new Hashtable();String sql = "";if(request.getMethod().equalsIgnoreCase("POST")) {String sdir = "/download/xlsdir/"; //文件保存目录String filesep=System.getProperty("file.separator");String ssdir = filesep+"download"+filesep+"xlsdir"+filesep;Calendar c = Calendar.getInstance();String fname = c.getTimeInMillis()+".xls";String sfname = sdir + fname;String ss = "xls"; //支持的文件后缀名try{SmartUpload su = new SmartUpload();su.initialize(pageContext);su.setAllowedFilesList(ss);su.upload();com.jspsmart.upload.File file = su.getFiles().getFile(0);if(!file.isMissing()){file.saveAs(sfname);// System.out.println(application.getRealPath("/")+ssdir+fname );FileInputStream finput = new FileInputStream(application.getRealPath("/")+ssdir+fname );//设定FileINputStream读取Excel档POIFSFileSystem fs = new POIFSFileSystem( finput );HSSFWorkbook wb = new HSSFWorkbook(fs);HSSFSheet sheet = wb.getSheetAt(0);//读取第一个工作表，宣告其为sheetfinput.close();HSSFRow row=null;//宣告一列HSSFCell cell=null;//宣告一个储存格short i=0;short y=0;//以巢状迴圈读取所有储存格资料System.out.println("sheet.getLastRowNum()="+sheet.getLastRowNum());for (i=1;i<=sheet.getLastRowNum();i++){row=sheet.getRow(i);for (y=1;y<=row.getLastCellNum();y++){cell=row.getCell(y);switch(y){case 1: //合同号if(cell!=null){contracId=(cell.getStringCellValue()).trim();}break;case 2: //项目名称if(cell!=null){projectname=(cell.getStringCellValue()).trim();}break;case 3: //项目规模if(cell!=null){projectscale=cell.getNumericCellValue();}break;case 4: //项目类型if(cell!=null){projecttype=(cell.getStringCellValue()).trim();}break;case 5: //项目建设单位if(cell!=null){projectconunit=(cell.getStringCellValue()).trim();}break;case 6: //设计时间if(cell!=null){designtime=cell.getNumericCellValue();}break;case 7: //设计阶段if(cell!=null){designphase=(cell.getStringCellValue()).trim();}break;case 8: //竣工时间if(cell!=null){completedtime=cell.getNumericCellValue();}break;case 9: //参与人姓名if(cell!=null){participants=(cell.getStringCellValue()).trim();}break;case 10: //担任项目职务if(cell!=null){positionofpro=(cell.getStringCellValue()).trim();}break;case 11: //担任职责if(cell!=null){dutiesas=(cell.getStringCellValue()).trim();}break;}}sql = "insert into hy_pro_briefinfo(contracId,projectname,projectscale,projecttype,projectconunit,designtime,designphase,completedtime, participants,positionofpro,dutiesas) ";sql+="values('"+contracId+"','"+projectname+"','"+projectscale+"','"+projecttype+"','"+projectconunit+"','"+(int)designti me+"','"+designphase+"','"+(int)completedtime+"','"+participants+"','"+positionofpro+"','"+dutiesas+"')"; //System.out.println(sql);snum = tb.update(sql);if(snum<=0){++rnum;rmstr = rmstr + ",";}else{++inum;}mit();tb.setCommit(true);// System.out.println("result="+result);//System.out.println("snum="+snum +"inum="+inum);}result = "共成功上传"+(inum+unum)+" 条记录，其中新增"+inum+" 条,更新"+unum+" 条；失败"+rnum+" 条（"+rmstr+" ）";// System.out.println(result);java.io.File f = new java.io.File(application.getRealPath("/")+ssdir+fname);f.delete();}else{result = "上传文件失败";}}catch(Exception ex){ex.printStackTrace();java.io.File f = new java.io.File(application.getRealPath("/")+ssdir+fname);//f.delete();// result = ex.getMessage();if(result.indexOf("1010")>0){result = "文件格式不对！只支持"+ss+"格式文件";}if("You cannot get a numeric value from a String based cell".equals(result)){result = "格式错误：在数字单元格中存在非数字！第"+(unum+inum+1)+"条记录出错";}}//finally{// response.sendRedirect("pro_per_info.jsp");// }}%><jsp:include page="/admin/BodyStart" />Ext.MessageBox.alert("提示",'<%=result %>');setTimeout("window.location.href='pro_per_info.jsp'", 1000);<jsp:include page="/admin/BodyEnd" />2.excel导出数据导出的时候注意中文编码的问题response.reset();response.setContentType("application/vnd.ms-excel");必须按照先后顺序书写<%@ page language="java"import="com.guanri.database.*,java.util.*,com.guanri.util.*,com.jspsmart.upload.*" contentType="text/html; charset=gb2312" pageEncoding="gb2312"%><%@ page import="java.util.*,java.io.*,ermodel.*" %><%//response.setContentType("application/msexcel;charset=utf-8" );//response.setContentType("text/html;charset=utf-8");response.reset();response.setContentType("application/vnd.ms-excel");request.setCharacterEncoding("gb2312");String workSheetName = "pro_info.xls";response.setHeader("Content-disposition", "attachment; filename="+workSheetName);//WriteExcel we=new WriteExcel();//System.out.println("文件生成");//we.getExcel("steet1",response.getOutputStream());HSSFWorkbook workbook = new HSSFWorkbook(); //产生工作簿对象HSSFSheet sheet = workbook.createSheet(); //产生工作表对象sheet.setDefaultRowHeight((short) 10);sheet.setDefaultColumnWidth((short) 20);sheet.setColumnWidth((short) 4, (short) 10000);// 单位workbook.setSheetName(0,"firstSheet");// 设置字体HSSFFont font = workbook.createFont();// font.setFontHeightInPoints(( short ) 20 ); // 字体高度// font.setColor(HSSFFont.COLOR_RED); // 字体颜色font.setFontName( " 黑体" ); // 字体font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 宽度// font.setItalic( true ); // 是否使用斜体// font.setStrikeout(true); // 是否使用划线// 设置单元格类型HSSFCellStyle cellStyle = workbook.createCellStyle();cellStyle.setFont(font);cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 水平布局：居左边cellStyle.setWrapText( true );HSSFRow row = sheet.createRow((short)0);//产生第一行HSSFCell cell = row.createCell((short) 0);//产生第一个单元格 cell.setCellType(HSSFCell.CELL_TYPE_STRING);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue("本公司项目统计信息");row = sheet.createRow((short) 1);//产生第二行cell = row.createCell((short) 0);//产生第一个单元格cell.setCellType(HSSFCell.CELL_TYPE_STRING);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue("序号");cell = row.createCell((short) 1);//产生第二个单元格cell.setCellType(HSSFCell.CELL_TYPE_STRING);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue("姓名");cell = row.createCell((short) 2);//产生第二个单元格cell.setCellType(HSSFCell.CELL_TYPE_STRING);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue("项目名称");cell = row.createCell((short) 3);//产生第二个单元格cell.setCellType(HSSFCell.CELL_TYPE_STRING);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue("项目类型");cell = row.createCell((short) 4);//产生第二个单元格cell.setCellType(HSSFCell.CELL_TYPE_STRING);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue("项目规模");cell = row.createCell((short) 5);//产生第二个单元格cell.setCellType(HSSFCell.CELL_TYPE_STRING);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue("担任项目职务");cell = row.createCell((short) 6);//产生第二个单元格cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue("设计时间");cell = row.createCell((short) 7);//产生第二个单元格 cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue("设计阶段");cell = row.createCell((short) 8);//产生第二个单元格 cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue("项目建设单位");cell = row.createCell((short) 9);//产生第二个单元格 cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue("竣工时间");cell = row.createCell((short) 10);//产生第二个单元格 cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue("专项设计");cell = row.createCell((short) 11);//产生第二个单元格 cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue("直属专业领导");Table tb = new Table();Vector vc = null;Hashtable rows=new Hashtable();String sql = "";sql = "select * from hy_pro_briefinfo where 1=1"; sql = sql + " order by participants";vc = tb.getContent(sql);// System.out.println(sql);// String result = "";int order=0;String contracId = "";String projectname = "";String projectscale = "";String projecttype="";String projectconunit="";double designtime = 0;String designphase = "";double completedtime = 0;String participants = "";String positionofpro = "";String dutiesas = "";String leader="";int iRow=2;String info="";String pername="";int num=1;for(int i=0;i<vc.size();i++){rows = (Hashtable)vc.get(i);order=i+1;participants=(String)rows.get("participants");if(!pername.equals(participants)){pername=participants;num=1;}else{num++;}// System.out.println("participants="+participants);projectname=(String)rows.get("projectname");projecttype=(String)rows.get("projecttype");// System.out.println("projectname="+projectname);positionofpro=(String)rows.get("positionofpro");// System.out.println("positionofpro="+positionofpro);designtime=((Integer)rows.get("designtime")).intValue();projectscale=(Double)rows.get("projectscale")+"";// System.out.println("designtime="+designtime);designphase=(String)rows.get("designphase");// System.out.println("designphase="+designphase);projectconunit=(String)rows.get("projectconunit");// System.out.println("projectconunit="+projectconunit);completedtime=((Integer)rows.get("completedtime")).intValue(); // System.out.println("completedtime="+completedtime);dutiesas=(String)rows.get("dutiesas");// System.out.println("dutiesas="+dutiesas);row= sheet.createRow((short)iRow);;for(int j=1;j<=11;j++){cell = row.createCell((short)(j-1));cell.setCellStyle(cellStyle); // 设置单元格样式cell.setCellType(HSSFCell.CELL_TYPE_STRING);cell.setEncoding(HSSFCell.ENCODING_UTF_16);switch(j-1){case 0: //编号info=num+"";break;case 1: //姓名info=participants+"";break;case 2: //项目名称info=projectname+"";break;case 3: //项目类型info=projecttype+"";break;case 4: //面积info="建筑面积："+projectscale+"平方米";break;case 5: //担任项目职务info=positionofpro+"";break;case 6: //设计时间info=designtime+"";info=info.substring(0,info.indexOf("."));break;case 7: //设计阶段info=designphase+"";break;case 8: //施工单位info=projectconunit+"";break;case 9: //竣工时间info=completedtime+"";info=info.substring(0,info.indexOf("."));if("0".equals(info)){info="";}break;case 10: //专项设计info=dutiesas+"";break; //直属专业领导case 11:info=leader+"";break;}//System.out.println("info="+info);cell.setCellValue(info);}iRow++;}OutputStream output=response.getOutputStream();output.flush();workbook.write(response.getOutputStream());output.close();%>。

JSP页面‎中导出Ex‎c el文件‎及其中文乱‎码的解决办‎法Web‎应用导出E‎x cel报‎表的简单实‎现（HTM‎L）‎在Web应‎用中，很多‎数据经常要‎导出成Ex‎c el文档‎。

用专门的‎生成真正的‎E xcel‎文档的方式‎比较复杂，‎不太好用。

‎所以经常用‎一种简单的‎方式来实现‎，即将报表‎保存为HT‎M L格式，‎然后用Ex‎c el打开‎。

实现方‎式：第一‎步,用JS‎P实现HT‎M L版本的‎报表第二‎步,在该J‎S P页面头‎部设置re‎s pons‎e的Con‎t entT‎y pe为E‎x cel格‎式<%‎resp‎o nse.‎s etCo‎n tent‎T ype(‎"appl‎i cati‎o n/vn‎d.ms-‎e xcel‎;char‎s et=G‎B K");‎%>‎中文问题‎：查看源‎代码时发现‎J SP文件‎中写死的中‎文为乱码，‎则在JSP‎文件头部添‎加一行<‎%@ pa‎g e co‎n tent‎T ype=‎"text‎/html‎; cha‎r set=‎g b231‎2" %>‎查看源代‎码时发现文‎字为中文，‎但是用Ex‎c el打开‎为乱码则在‎<html‎>与<he‎a d>中加‎入<me‎t a ht‎t p-eq‎u iv="‎C onte‎n t-Ty‎p e" c‎o nten‎t="te‎x t/ht‎m l; c‎h arse‎t=GBK‎">用‎S ervl‎e t实现也‎是类似的处‎理方法。

‎实现样例‎：Test‎.jsp‎<%@ ‎p age ‎c onte‎n tTyp‎e="te‎x t/ht‎m l; c‎h arse‎t=GBK‎" %>‎<% re‎s pons‎e.set‎C onte‎n tTyp‎e("ap‎p lica‎t ion/‎v nd.m‎s-exc‎e l;ch‎a rset‎=GBK"‎); %>‎<HTM‎L><m‎e ta h‎t tp-e‎q uiv=‎"Cont‎e nt-T‎y pe" ‎c onte‎n t="t‎e xt/h‎t ml; ‎c hars‎e t=GB‎K"><‎h ead>‎<titl‎e>Tes‎t</ti‎t le><‎/head‎><bo‎d y><‎T ABLE‎bord‎e rCol‎o r=#1‎11111‎cell‎S paci‎n g=0 ‎c ellP‎a ddin‎g=2 w‎i dth=‎1200 ‎a lign‎=cent‎e r bo‎r der=‎1><T‎R><T‎D ali‎g n=mi‎d dle ‎w idth‎="10%‎" hei‎g ht=2‎0 row‎S pan=‎2>项目‎</TD>‎<TD ‎a lign‎=midd‎l e wi‎d th="‎2%" h‎e ight‎=20 r‎o wSpa‎n=2>计‎量单位 <‎/TD>‎<TD a‎l ign=‎m iddl‎e wid‎t h="4‎%" he‎i ght=‎20>满期‎赔付率 <‎/TD>‎</TR>‎</TA‎B LE>‎</bod‎y></‎H TML>‎Web应‎用导出Ex‎c el报表‎的简单实现‎（HTML‎）在We‎b应用中，‎很多数据经‎常要导出成‎E xcel‎文档。

JS导出Table为excel的三种可行方法[html] view plain copy1.<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"2."/TR/xhtml1/DTD/xhtml1-transitional.dtd">3.<html xmlns="/1999/xhtml">4.<head>5.<meta http-equiv="Content-Type"content="text/html; charset=gb2312"/>6.<title>WEB页面导出为EXCEL文档的方法</title>7.</head>8.<body>9.<table id="tableExcel"width="100%"border="1"cellspa cing="0"cellpadding="0">10.<tr>11.<td colspan="5"align="center">WEB页面导出为EXCEL文档的方法</td>12.</tr>13.<tr>14.<td>列标题1</td>15.<td>列标题2</td>16.<td>列标题3</td>17.<td>列标题4</td>18.<td>列标题5</td>19.</tr>20.<tr>21.<td>aaa</td>22.<td>bbb</td>23.<td>ccc</td>24.<td>ddd</td>25.<td>eee</td>26.</tr>27.<tr>28.<td>AAA</td>29.<td>BBB</td>30.<td>CCC</td>31.<td>DDD</td>32.<td>EEE</td>33.</tr>34.<tr>35.<td>FFF</td>36.<td>GGG</td>37.<td>HHH</td>38.<td>III</td>39.<td>JJJ</td>40.</tr>41.</table>42.<input type="button"onclick="javascript:method1('ta bleExcel');"value="第一种方法导入到EXCEL">43.<input type="button"onclick="javascript:method2('ta bleExcel');"value="第二种方法导入到EXCEL">44.<input type="button"onclick="javascript:getXlsFrom Tbl('tableExcel',null);"value="第三种方法导入到EXCEL">45.<SCRIPT LANGUAGE="javascript">46.function method1(tableid) {//整个表格拷贝到EXCEL 中47.var curTbl = document.getElementById(tableid);48.var oXL = new ActiveXObject("Excel.Application");49.//创建AX对象excel50.var oWB = oXL.Workbooks.Add();51.//获取workbook对象52.var oSheet = oWB.ActiveSheet;53.//激活当前sheet54.var sel = document.body.createTextRange();55.sel.moveToElementText(curTbl);56.//把表格中的内容移到TextRange中57.sel.select();58.//全选TextRange中内容59.sel.execCommand("Copy");60.//复制TextRange中内容61.oSheet.Paste();62.//粘贴到活动的EXCEL中63.oXL.Visible = true;64.//设置excel可见属性65.}66.function method2(tableid) //读取表格中每个单元到EXCEL中67.{68.var curTbl = document.getElementById(tableid);69.var oXL = new ActiveXObject("Excel.Application");70.//创建AX对象excel71.var oWB = oXL.Workbooks.Add();72.//获取workbook对象73.var oSheet = oWB.ActiveSheet;74.//激活当前sheet75.var Lenr = curTbl.rows.length;76.//取得表格行数77.for (i = 0; i <Lenr; i++)78.{79.var Lenc = curTbl.rows(i).cells.length;80.//取得每行的列数81.for (j = 0; j <Lenc; j++)82.{83.oSheet.Cells(i + 1, j + 1).value = curTbl.rows(i).cells(j).i nnerText;84.//赋值85.}86.}87.oXL.Visible = true;88.//设置excel可见属性89.}90.function getXlsFromTbl(inTblId, inWindow) {91.try {92.var allStr = "";93.var curStr = "";94.//alert("getXlsFromTbl");95.if (inTblId != null && inTblId != "" && inTblId != "null" ) {96.curStr = getTblData(inTblId, inWindow);97.}98.if (curStr != null) {99.allStr += curStr;100.}101.else {102.alert("你要导出的表不存在！");103.return;104.}105.var fileName = getExcelFileName();106.doFileExport(fileName, allStr);107.}108.catch(e) {109.alert("导出发生异常:" + + "->" + e.description + "!");110.}111.}112.function getTblData(inTbl, inWindow) {113.var rows = 0;114.//alert("getTblData is " + inWindow);115.var tblDocument = document;116.if (!!inWindow && inWindow != "") {117.if (!document.all(inWindow)) {118.return null;119.}120.else {121.tblDocument = eval(inWindow).document;122.}123.}124.var curTbl = tblDocument.getElementById(inTbl);125.var outStr = "";126.if (curTbl != null) {127.for (var j = 0; j <curTbl.rows.length; j++) {128.//alert("j is " + j);129.for (var i = 0; i <curTbl.rows[j].cells.length; i++) {130.//alert("i is " + i);131.if (i == 0 && rows > 0) {132.outStr += " \t";133.rows -= 1;134.}135.outStr += curTbl.rows[j].cells[i].innerText + "\t";136.if (curTbl.rows[j].cells[i].colSpan > 1) {137.for (var k = 0; k <curTbl.rows[j].cells[i].colSpan - 1; k ++) {138.outStr += " \t";139.}140.}141.if (i == 0) {142.if (rows == 0 && curTbl.rows[j].cells[i].rowSpan > 1) {143.rows = curTbl.rows[j].cells[i].rowSpan - 1;144.}145.}146.}147.outStr += "\r\n";148.}149.}150.else {151.outStr = null;152.alert(inTbl + "不存在!");153.}154.return outStr;155.}156.function getExcelFileName() {157.var d = new Date();158.var curYear = d.getYear();159.var curMonth = "" + (d.getMonth() + 1);160.var curDate = "" + d.getDate();161.var curHour = "" + d.getHours();162.var curMinute = "" + d.getMinutes();163.var curSecond = "" + d.getSeconds();164.if (curMonth.length == 1) {165.curMonth = "0" + curMonth;166.}167.if (curDate.length == 1) {168.curDate = "0" + curDate;169.}170.if (curHour.length == 1) {171.curHour = "0" + curHour;172.}173.if (curMinute.length == 1) {174.curMinute = "0" + curMinute;175.}176.if (curSecond.length == 1) {177.curSecond = "0" + curSecond;178.}179.var fileName = "leo_zhang" + "_" + curYear + curMont h + curDate + "_"180.+ curHour + curMinute + curSecond + ".csv";181.//alert(fileName);182.return fileName;183.}184.function doFileExport(inName, inStr) {185.var xlsWin = null;186.if (!!document.all("glbHideFrm")) {187.xlsWin = glbHideFrm;188.}189.else {190.var width = 6;191.var height = 4;192.var openPara = "left=" + (window.screen.width / 2 - width / 2)193.+ ",top=" + (window.screen.height / 2 - height / 2) 194.+ ",scrollbars=no,width=" + width + ",height=" + heig ht;195.xlsWin = window.open("", "_blank", openPara);196.}197.xlsWin.document.write(inStr);198.xlsWin.document.close();199.xlsWin.document.execCommand('Saveas', true, inNam e);200.xlsWin.close();201.}202.</SCRIPT>203.</body>204.</html>。

jsp页面显示数据导出到excel表中发表时间:2007-01-26内容来源:CSDN作者:佚名Excel报表的方法，一个过於简单，一个只能用於window平台(因为使用jdbc-odbc bridge)，且无法使用到Excel内部的各种公式或是方法，因此，今天介绍一个apache出的元件叫POI，它可以在UNIX或window平台处理word或Excel档案，而不需要依靠window的com，并且可设定储存格格式、列印格式等等；今天我来介绍其中有关资料读取、新增、修改及删除的功能，若各位网友研究好其他的功能，麻烦Email给我(ljj@.tw)，分享给大家！一、需要用的档案：jakarta-poi-1.8.0-dev-20020917.jar几乎每天都有1.8.0的最新版(但非正式版)，正式的版本是1.5.0/builds/jakarta-poi/nightly/将档案复制到classpath所指到的地方二、有兴趣的朋友可以参考/poi/三、先建立一个叫做book1.xls的Excel档，内容如下----------------------------------项目单价数量合计CPU7000535000硬碟250025000记忆体160034800----------------------------------其中合计的栏位是设定公式，单价*数量四、资料读取範例<%@ page contentType="text/html;charset=MS950" import="java.util.*,java.io.*" %><%@ page import="org.apache.poi.poifs.filesystem.*,ermodel.*" %><html><head><meta http-equiv="Content-Type" content="text/html; charset=MS950"><title>读取Excel档案</title></head><body><table border="1" width="100%"><%FileInputStream finput = new FileInputStream(application.getRealPath("/")+"book1.xls" );//设定FileINputStream读取Excel档POIFSFileSystem fs = new POIFSFileSystem( finput );HSSFWorkbook wb = new HSSFWorkbook(fs);HSSFSheet sheet = wb.getSheetAt(0);//读取第一个工作表，宣告其为sheetfinput.close();HSSFRow row=null;//宣告一列HSSFCell cell=null;//宣告一个储存格short i=0;short y=0;//以巢状迴圈读取所有储存格资料for (i=0;i<=sheet.getLastRowNum();i++){out.println("<tr>");row=sheet.getRow(i);for (y=0;y<row.getLastCellNum();y++){cell=row.getCell(y);out.print("<td>");//判断储存格的格式switch ( cell.getCellType() ){case HSSFCell.CELL_TYPE_NUMERIC:out.print(cell.getNumericCellValue());//getNumericCellValue()会回传double值，若不希望出现小数点，请自行转型为intbreak;case HSSFCell.CELL_TYPE_STRING:out.print( cell.getStringCellValue());break;case HSSFCell.CELL_TYPE_FORMULA:out.print(cell.getNumericCellValue());//读出公式储存格计算後的值//若要读出公式内容，可用cell.getCellFormula()break;default:out.print( "不明的格式");break;}out.println("</td>");}out.println("</tr>");}%></table></body></html>五、资料新增範例<%@ page contentType="text/html;charset=MS950" import="java.util.*,java.io.*" %><%@ page import="org.apache.poi.poifs.filesystem.*,ermodel.*" %><html><meta http-equiv="Content-Type" content="text/html; charset=MS950"><title>插入资料至Excel档案</title></head><body><%FileInputStream finput = new FileInputStream(application.getRealPath("/")+"book1.xls" ); //设定FileINputStream读取Excel档POIFSFileSystem fs = new POIFSFileSystem( finput );HSSFWorkbook wb = new HSSFWorkbook(fs);HSSFSheet sheet = wb.getSheetAt(0);//读取第一个工作表，宣告其为sheetfinput.close();HSSFRow row=null;//宣告一列HSSFCell cell=null;//宣告一个储存格short i=4;row=sheet.createRow(i);//建立一个新的列，注意是第五列(列及储存格都是从0起算)cell=row.createCell((short)0);cell.setEncoding(HSSFCell.ENCODING_UTF_16);//设定这个储存格的字串要储存双位元cell.setCellValue("显示卡");cell=row.createCell((short)1);cell.setCellValue(1700);cell=row.createCell((short)2);cell.setCellValue(8);cell=row.createCell((short)3);//设定这个储存格为公式储存格，并输入公式cell.setCellFormula("B"+(i+1)+"*C"+(i+1));try{FileOutputStream fout=new FileOutputStream(application.getRealPath("/")+"book1.xls");wb.write(fout);//储存fout.close();out.println("储存成功<a href='book1.xls'>book1.xls</a>");}catch(IOException e){out.println("产生错误，错误讯息："+e.toString());}%></html>六、资料删除、修改範例<%@ page contentType="text/html;charset=MS950" import="java.util.*,java.io.*" %><%@ page import="org.apache.poi.poifs.filesystem.*,ermodel.*" %><html><head><meta http-equiv="Content-Type" content="text/html; charset=MS950"><title>删除、修改资料至Excel档案</title></head><body><%FileInputStream finput = new FileInputStream(application.getRealPath("/")+"book1.xls" ); //设定FileINputStream读取Excel档POIFSFileSystem fs = new POIFSFileSystem( finput );HSSFWorkbook wb = new HSSFWorkbook(fs);HSSFSheet sheet = wb.getSheetAt(0);//读取第一个工作表，宣告其为sheetfinput.close();HSSFRow row=null;//宣告一列HSSFCell cell=null;//宣告一个储存格row=sheet.getRow((short)4);//取出第五列if (row!=null)sheet.removeRow(row);//先侦测第五列存不存在，若在的话将第五列删除row=sheet.getRow((short)3);//取出第四列cell=row.getCell((short)2);//取出第三个储存格cell.setCellValue(7);//设定该储存格值为7cell=row.getCell((short)3);cell.setCellFormula(cell.getCellFormula());//上两行为取出公式储存格，并重新计算(因为刚才更新过计算公式的值)//如果不做，公式计算後的值不会更新try{FileOutputStream fout=new FileOutputStream(application.getRealPath("/")+"book1.xls");wb.write(fout);//储存fout.close();out.println("储存成功<a href='book1.xls'>book1.xls</a>");}catch(IOException e){out.println("产生错误，错误讯息："+e.toString());}%></body></html>/gqm1982/archive/2007/01/25/1493229.aspx本篇文章来源于站长资讯网原文链接：/0701/jsp-116977784110490.html。