这是我写的一个读取excel方法,返回的是一个list,你先必须去下载一个jxl包才可以使用, 如果你想传个文件名测试,你必须这样测试,例如你的excel在d盘(d:\\\test.xls) /** * * @param FileName * 上传的文件名 */ public List jxlImportExcelToDate(String FileName) { try { //读取excel数据返回到此List List result = new ArrayList();
File uploadFileName = new File(FileName); InputStream is = new FileInputStream(uploadFileName); // 创建一个excel文件对象 jxl.Workbook excel = Workbook.getWorkbook(is); // 获得第一个excel文件的sheet Sheet sheet = excel.getSheet(0); // 获取sheet单元的总行数 int rows = sheet.getRows(); // 获得sheet单元的总列数 int columns = sheet.getColumns();
// 读取数据 for (int r = 0; r < rows; r++) {
//每行数据 String[] rowDates = new String[columns]; for (int c = 0; c < columns; c++) { Cell cell = sheet.getCell(c, r); String cellValue = cell.getContents(); rowDates[c] = cellValue; }
如果你想传个文件名测试,你必须这样测试,例如你的excel在d盘(d:\\\test.xls)
/**
*
* @param FileName
* 上传的文件名
*/
public List jxlImportExcelToDate(String FileName) { try {
//读取excel数据返回到此List
List result = new ArrayList();
File uploadFileName = new File(FileName);
InputStream is = new FileInputStream(uploadFileName); // 创建一个excel文件对象
jxl.Workbook excel = Workbook.getWorkbook(is);
// 获得第一个excel文件的sheet
Sheet sheet = excel.getSheet(0);
// 获取sheet单元的总行数
int rows = sheet.getRows();
// 获得sheet单元的总列数
int columns = sheet.getColumns();
// 读取数据
for (int r = 0; r < rows; r++) {
//每行数据
String[] rowDates = new String[columns];
for (int c = 0; c < columns; c++) {
Cell cell = sheet.getCell(c, r);
String cellValue = cell.getContents();
rowDates[c] = cellValue;
}
//将值加入到List中返回
result.add(rowDates);
} // 关闭excel对象
excel.close();
System.out.println("Read Excel file sucess!");
return result;
} catch (Exception e) {
System.out.println(e.getMessage());
return null;
} }
import org.apache.poi.hssf.usermodel.*;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.io.File;
import java.sql.*;
import org.apache.poi.hssf.util.*;
import java.io.FileInputStream;
import org.apache.poi.poifs.filesystem.*;
import org.apache.poi.hssf.model.Workbook;
public void ColorExcel(OutputStream os)throws Exception
{
try {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet1 = wb.createSheet("Color");
HSSFRow row00 = sheet1.createRow(0);
for(int i=0;i<100;i++)
{
HSSFCellStyle stylebt = wb.createCellStyle();
stylebt.setFillPattern((short) 1);
stylebt.setFillForegroundColor((short)i); //綠色green表頭
stylebt.setAlignment(HSSFCellStyle.ALIGN_CENTER);
stylebt.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
stylebt.setBorderBottom(HSSFCellStyle.BORDER_THIN);
stylebt.setBorderLeft(HSSFCellStyle.BORDER_THIN);
stylebt.setBorderRight(HSSFCellStyle.BORDER_THIN);
stylebt.setBorderTop(HSSFCellStyle.BORDER_THIN);
stylebt.setWrapText(true); HSSFCell cell0000 = row00.createCell((short)i);
cell0000.setCellStyle(stylebt);
cell0000.setEncoding(HSSFCell.ENCODING_UTF_16);
cell0000.setCellValue(i); } wb.write(os);
os.close();
}
catch(Exception ex)
{
ex.printStackTrace();
}
}這個沒有數據連接,是我測試顔色的,呵呵。樓主只要把cell0000.setCellValue(i);中i改爲你讀到的數據就是了。一個格子一個格子一行一行的循環需要下載包才能支持。網上很容易找到
你的代码没有注释,我还不是很清楚,能不能写的详细一些?编号 姓名 日期 时间 bc 工号 部门名称
1 王 2006-10-13 13:12:00 5 01 aa数据从数据库取出的,我怎么把这样一个表通过点击一个按钮,把他们保存到一个excel表中,让用户自己选择保存路径
{
try {
//声明一个workbook
HSSFWorkbook wb = new HSSFWorkbook();
//声明一个worksheet,Color是定义的sheet名称,如sheet1等
HSSFSheet sheet1 = wb.createSheet("Color");
//声明一行
HSSFRow row00 = sheet1.createRow(0);
for(int i=0;i<100;i++)
{
//声明一个样式
HSSFCellStyle stylebt = wb.createCellStyle();
//设置样式的属性
stylebt.setFillPattern((short) 1);
stylebt.setFillForegroundColor((short)i); //綠色green表頭
stylebt.setAlignment(HSSFCellStyle.ALIGN_CENTER);
stylebt.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
stylebt.setBorderBottom(HSSFCellStyle.BORDER_THIN);
stylebt.setBorderLeft(HSSFCellStyle.BORDER_THIN);
stylebt.setBorderRight(HSSFCellStyle.BORDER_THIN);
stylebt.setBorderTop(HSSFCellStyle.BORDER_THIN);
stylebt.setWrapText(true);
//声明一个格子
HSSFCell cell0000 = row00.createCell((short)i);
//设置格子的样式
cell0000.setCellStyle(stylebt);
//设置格子的编码
cell0000.setEncoding(HSSFCell.ENCODING_UTF_16);
//为格子赋值,即格子的内容
cell0000.setCellValue(i); } wb.write(os);//输出
os.close();//关掉OutputStream
}
catch(Exception ex)
{
ex.printStackTrace();
}
}
//测试
public static void main(String[] args) throws Exception {
ReportOutput report = new ReportOutput ();
File f=new File("d:\\testExcel\\test.xls");//设定输出的位置,此文件夹需存在
report. ColorExcel (new FileOutputStream(f));
}//输出的servlet
public void doGet(HttpServletRequest request, HttpServletResponse response) throws
ServletException, IOException {
response.setContentType(CONTENT_TYPE);
try{
response.reset();
String fname = "Resume";
response.setHeader("Content-disposition", "attachment; filename=" + fname + ".xls");
response.setContentType("application/vnd.ms-excel");
ReportOutput report = new ReportOutput ();
report. ColorExcel (response.getOutputStream()); }
catch(Exception ex)
{
ex.printStackTrace();
} }
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40"><head>
<meta http-equiv=Content-Type content="text/html; charset=GB2312">
<script>
function out(){try{
var elTable = document.getElementById("out");
var oRangeRef = document.body.createTextRange();
oRangeRef.moveToElementText( elTable );
oRangeRef.execCommand( "Copy" );var oXL = new ActiveXObject("Excel.Application")
var oWB = oXL.Workbooks.Add ;
var oSheet = oWB.ActiveSheet ;
oSheet.Paste();
oSheet.Cells.NumberFormatLocal = "@";oSheet.Columns("D:D").Select
oXL.Selection.ColumnWidth = 20//oSheet.Columns("A:A").Select
//oSheet.Columns("A").Width = 1000;oXL.Visible = true;
oSheet = null;
oWB = null;
appExcel = null;}catch(e){alert(e.description)}}</script>
</head><body><table id ='out'>
<tr>
<td x:str bgcolor="#FF0000">00001</td>
<td x:str>0002</td>
</tr>
<tr>
<td height=18 align=right style='height:13.5pt' x:str>00001</td>
<td align=right x:str>000002</td>
</tr>
<tr>
<td x:str>0003</td>
<td x:str>00003</td>
</tr>
</table>
<INPUT type="button" value="Button" id=button1 name=button1 onclick="out()">
</body>
</html>
至于数据,你写个方法从数据库中取出来,装到诸如list,hashtable,vector之类的东西里,然后再在生成Excel的方法里取出来放到格子里去就是了。
你可以先测试poi,下载个包出来,直接放到你的classpath
这是官方网站上的下载地址:
http://jakarta.apache.org/site/downloads/downloads_poi.cgi
<%@ page contentType="text/html; charset=GBK" %>
换成
<%@page contentType="application/vnd.ms-excel;charset=gb2312" %>
就行了
<%@page contentType="application/vnd.ms-excel;charset=gb2312" %>
<%@ page import="java.util.*" %>
<%@ page import="com.inspire.product.common.db.*" %>
<%@ page import="com.inspire.product.common.util.*" %>
<%@ page import="com.inspire.product.common.util.DividePage"%>
<%@ page import="com.inspire.product.javabeans.martcenter.navigate.NavigateOperate" %>
<%
String path = request.getContextPath(); DBAccess db = new DBAccess();
ToolsUtil tools = new ToolsUtil(db);
NavigateOperate navigate = new NavigateOperate(); String yearsel = tools.nvl(request.getParameter("year"));
String monthsel = tools.nvl(request.getParameter("month"));
String[] mapmaker = tools.Str_to_Arr(tools.nvl(request.getParameter("mapmaker")),tools.SPLIT_CHAR);
HashMap hmParam = new HashMap();
hmParam.put("year",yearsel);
hmParam.put("month",monthsel);
hmParam.put("mapmaker",mapmaker);
HashMap[] hmNavigate = navigate.getNavigateInfo(hmParam);
%>
<html>
<head><link rel="stylesheet" href="<%=path%>/css/style.css" type="text/css">
<title></title>
</head>
<body text="#000000" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<form name="form1" method="post" action="">
<input type="hidden" name="flag" value="">
<center> <font size="3" color="blue">导航仪销售累计量情况</font>
<br>
<table width="800" border="1" align="center" cellspacing="0" bordercolor="#5EA0F5">
<tr bordercolor="#5EA0F5" bgcolor="#AFCFFC">
<td align="center">编号</td>
<td align="center">汽车制造商</td>
<td align="center">品牌</td>
<td align="center">DVD语音导航系统</td>
<td align="center">导航仪</td>
<td align="center">formt</td>
<td align="center">Navi Disk</td>
<td align="center">地图DB</td>
<td align="center">上市时间</td>
<td align="center">销售数量(万台)</td>
</tr> <%
if(hmNavigate!=null){
for (int j = 0; j < hmNavigate.length; j++) {
%>
<tr>
<td align="center"> <%=j+1%></td>
<td align="center"> <%=tools.getValue("CarMakerinfo","Carmakername","Carmakerid",tools.nvl((String)hmNavigate[j].get("CARMAKERID")))%></td>
<td align="center"> <%=tools.getValue("CarBrandInfo","CarBrandname","CarBrandid",tools.nvl((String)hmNavigate[j].get("CARBRANDID")))%></td>
<td align="center"> <%=tools.getValue("yesorno",tools.nvl((String)hmNavigate[j].get("DVD")))%></td>
<td align="center"> <%=tools.getValue("pndpda_maker_info","P_MAKER_NAME","P_MAKER_ID",(String)hmNavigate[j].get("MAKERID"))%></td>
<td align="center"> <%=tools.nvl((String)hmNavigate[j].get("FORMAT"))%></td>
<td align="center"> <%=tools.nvl((String)hmNavigate[j].get("NAVI_DISK"))%></td>
<td align="center"> <%=tools.getValue("mapmaker",tools.nvl((String)hmNavigate[j].get("MAPMAKER")))%></td>
<td align="center"> <%=tools.nvl((String)hmNavigate[j].get("S_DATE"))%></td>
<td align="center"> <%=tools.nvl((String)hmNavigate[j].get("CARSALENUM"))%></td>
</tr>
<%
}
}
%>
</table> <br>
<table width="90%" border="0" align="center" cellspacing="0" bordercolor="#5EA0F5">
<tr>
<td align="center" colspan=2>
<img id="telnumimg" src="<%=path%>/jsp/martcenter/navigate/navigateStatNumimg3.jsp?yearsel=<%=yearsel%>&monthsel=<%=monthsel%>&mapmaker=<%=tools.Arr_to_Str(mapmaker,tools.SPLIT_CHAR)%>" alt="">
</td>
</tr> <tr>
<td align="center" colspan=2>
<img id="telnumimg" src="<%=path%>/jsp/martcenter/navigate/navigateStatNumimg2.jsp?yearsel=<%=yearsel%>&monthsel=<%=monthsel%>&mapmaker=<%=tools.Arr_to_Str(mapmaker,tools.SPLIT_CHAR)%>" alt="">
</td>
</tr> </table></center>
</form>
</body>
</html>
<%@ page contentType="text/html; charset=GBK" %>
换成
<%@page contentType="application/vnd.ms-excel;charset=gb2312" %>
就行了你改的这个是什么意思?这样做就可以生成excel文件?
{
if (typeof(EXPORT_OBJECT)!="object")
{
document.body.insertAdjacentHTML("afterBegin","<OBJECT style='display:none' classid=clsid:0002E510-0000-0000-C000-000000000046 id=EXPORT_OBJECT></Object>");
}
with (EXPORT_OBJECT)
{
DataType = "HTMLData";
HTMLData =tableid.outerHTML;
try
{
ActiveSheet.Export("d:\\表格.xls", 0);
alert('成功导出EXCEL表格!');
}catch (e){
}
}
}