jsp输出excel文件

最新推荐文章于 2024-04-17 15:18:39 发布

Bruce54

最新推荐文章于 2024-04-17 15:18:39 发布

阅读量2.4k

点赞数

CC 4.0 BY-SA版权

文章标签： excel jsp import string null exception

本文链接：https://blog.youkuaiyun.com/epng54/article/details/2208597

本文介绍使用Java进行Excel文件操作的方法，包括使用Jakarta POI库创建、读取和格式化Excel文件，以及通过JSP页面直接输出Excel内容。

jsp输出excel文件（转贴备用）

2008-02-03 10:03

介绍:

Jakarta_POI 使用Java读写Excel(97-2002)文件，可以满足大部分的需要。

因为刚好有一个项目使用到了这个工具，花了点时间顺便翻译了一下POI本身

带的一个Guide.有一些节减和修改，希望给使用这个项目的人一些入门帮助。

POI 下面有几个自项目:HSSF用来实现Excel 的读写.以下是HSSF的主页

http://jakarta.apache.org/poi/hssf/index.html

下面的介绍是基于以下地址的翻译：

http://jakarta.apache.org/poi/hssf/quick-guide.html

目前的版本为1.51应该是很长时间之内的一个稳定版，但HSSF提供的Sample不是基于

1.51所写，所以使用的时候需要适当的注意.

其实POI下面的几个子项目侧重不同读写 Word 的HDF正在开发当中.

XML下的FOP(http://xml.apache.org/fop/index.html)

可以输出pdf文件，也是比较好的一个工具

目录:

创建一个workbook

创建一个sheet

创建cells

创建日期cells

设定单元格格式

说明：

以下可能需要使用到如下的类

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;

import org.apache.poi.hssf.usermodel.HSSFFont;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.hssf.util.HSSFColor;

创建workbook

HSSFWorkbook wb = new HSSFWorkbook();

//使用默认的构造方法创建workbook

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

//指定文件名

wb.write(fileOut);

//输出到文件

fileOut.close();

创建一个sheet

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet1 = wb.createSheet("new sheet");

//workbook创建sheet

HSSFSheet sheet2 = wb.createSheet("second sheet");

//workbook创建另外的sheet

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

创建cells

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("new sheet");

//注意以下的代码很多方法的参数是short 而不是int 所以需要做一次类型转换

HSSFRow row = sheet.createRow((short)0);

//sheet 创建一行

HSSFCell cell = row.createCell((short)0);

//行创建一个单元格

cell.setCellValue(1);

//设定单元格的值

//值的类型参数有多中double ,String ,boolean,

row.createCell((short)1).setCellValue(1.2);

row.createCell((short)2).setCellValue("This is a string");

row.createCell((short)3).setCellValue(true);

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

创建日期cells

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("new sheet");

HSSFRow row = sheet.createRow((short)0);

HSSFCell cell = row.createCell((short)0);

//设定值为日期

cell.setCellValue(new Date());

HSSFCellStyle cellStyle = wb.createCellStyle();

//指定日期显示格式

cellStyle.setDataFormat(HSSFDataFormat.getFormat("m/d/yy h:mm"));

cell = row.createCell((short)1);

cell.setCellValue(new Date());

//设定单元格日期显示格式

cell.setCellStyle(cellStyle);

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

设定单元格格式

单元格格式的设定有很多形式包括单元格的对齐方式，内容的字体设置，

单元格的背景色等，因为形式比较多，只举一些例子.以下的例子在

POI1.5中可能会有所改变具体查看API.

..........

// Aqua background

HSSFCellStyle style = wb.createCellStyle();

//创建一个样式

style.setFillBackgroundColor(HSSFCellStyle.AQUA);

//设定此样式的的背景颜色填充

style.setFillPattern(HSSFCellStyle.BIG_SPOTS);

//样式的填充类型。

//有多种式样如:

//HSSFCellStyle.BIG_SPOTS

//HSSFCellStyle.FINE_DOTS

//HSSFCellStyle.SPARSE_DOTS等

style.setAlignment(HSSFCellStyle.ALIGN_CENTER );

//居中对齐

style.setFillBackgroundColor(HSSFColor.GREEN.index);

//设定单元个背景颜色

style.setFillForegroundColor(HSSFColor.RED.index);

//设置单元格显示颜色

HSSFCell cell = row.createCell((short) 1);

cell.setCellValue("X");

cell.setCellStyle(style);

前段时间,客户要求我们的系统可以实现与ms excel的数据交换,可以把系统中的数据导入导出到excel.我们的系统是一个用java做的b/s结构的MIS系统.为了解决这个
问题,我找到了一个专门操作excel的开源项目jxl,并找到了一篇介绍如何使用jxl开发包的好文章:
--http://www-900.cn.ibm.com/developerWorks/cn/java/l-javaExcel/#2
根据客户的要求,用jxl实现了如下功能:
1:在客户端读取excel文件的数据;
2:把系统数据导出到excel模板文件中.
对于第一个问题,我开始考虑的是写一个客户端运行的applet小程序.后来才明白,java为了安全,applet是不能随便访问客户端的文件系统的(:(低级错误!不要鄙视我).
于是就想到了先把excel文件上传到服务器,然后用jxl来解析数据.怎么上传文件我就不说了,只要做过jsp的人应该都知道吧.主要讲一下如何解析.由于上传的数据都是
有固定格式的,所以我们做了一个excel模板,客户必须按照模板的格式填写数据,程序才可以正常的解析.
解析其实也是很简单的,但在做的过程中发现,excel中的时间格式数据需要特别注意:如果按照文本格式读取时间格式数据,读取的时间是错误的(大家可以试验一下).
为了解决读取时间错误的问题,我仔细研究了一下jxl的api,呵呵,居然发现了一个好东东:DateCell类,用它的getDate方法可以得到一个cell的date对象.不过由于
国际化的需要,这个date对象得到的是格林尼治时间,你需要处理一下.
至于导出,其实和导入差不多,无非就是先访问一下数据库,把得到的信息写入excel文件然后下载.
下面是我写的导入导出周生产计划的java类,其中开始的几行注释掉的部分分别是下载和上传的试例.
(其实模板也很关键,下面的程序是基于模板而写的,不过相信大家看了下面的程序,也可以知道模板的样子了,需要重点说明的是,在模板中,填写时间的字段必须设置成时
间格式-----在excel中点击右键菜单轻松搞定)

/*
* Create Date: 2004-11-9 10:20
* Create By: 李春雷
* purpose:和周计划相关的导入导出操作
*/

/**********************周生产下载示例*****************
ExcelZJHXM myZJH = new ExcelZJHXM();
myZJH.download("mydataZjh","20041018003");
****************************************************/

/**********************周计划上传示例*****************
ExcelZJHXM test = new ExcelZJHXM();
test.upload("zscjhtest");
if(test.getFlag()){
System.out.println("begin test--------------");
ArrayList a = test.getData();
for(int i = 0;i<a.size();i++){
ExcelZJHXM.dataZJHXM mydata =(ExcelZJHXM.dataZJHXM)a.get(i);
System.out.println(mydata.XH+" "+mydata.XMMC+" "+mydata.GZNR +" "+mydata.SFTD +" "+mydata.TDFW +" "+mydata.JHLRFLBH +" "+mydata.JHLRBH +" "+mydata.ZXDWMC +" "+mydata.PHDWMC +" "+mydata.DBRMC +" "+mydata.JHKSSJ+" "+mydata.JHWCSJ +" "+mydata.BZ);

}
}else{
System.out.println("模板错误");
}
*****************************************************/

package com.infoearth.excel;

import com.infoearth.common.DataAccess; //我们项目自己的访问数据库的东东
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.SQLException;
import java.sql.*;
import java.io.*;
import java.io.Serializable;
import java.util.*;
import javax.sql.RowSet;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.*;
import java.text.DateFormat;
import jxl.DateCell;
import java.text.*;

public class ExcelZJHXM
{
private boolean flag=false;

private ArrayList subdata = new ArrayList();

public boolean download(String filename,String jhbh){
Connection conn = null;
PreparedStatement pStatement = null;
ResultSet rs = null;
DataAccess dBean = new DataAccess();
String mysql;
mysql = "select XH,XMMC,GZNR,SFTD,TDFW,JHLRFLBH,JHLRBH,ZXDWMC,PHDWMC,DBRMC,JHKSSJ,JHWCSJ,BZ from JH_ZJHXM where JHBH='"+jhbh+"' order by jhxmbh asc";
try {
conn = dBean.getConnection();
pStatement = conn.prepareStatement(mysql);
rs = pStatement.executeQuery();
Workbook wb=Workbook.getWorkbook(new File("pengyue//webmis//template//zscjh.xls"));
WritableWorkbook book= Workbook.createWorkbook(new File("pengyue//webmis//download//"+filename+".xls"),wb);
WritableSheet sheet = book.getSheet("zscjh");
jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat();
wcfFC.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
int i=4;
jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-mm-dd hh:mm");
jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df);
wcfDF.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
while(rs.next()) {
sheet.addCell(new Label(2,i,rs.getString("XH"),wcfFC)); //序号
sheet.addCell(new Label(3,i,rs.getString("XMMC"),wcfFC)); //项目名称
sheet.addCell(new Label(4,i,rs.getString("GZNR"),wcfFC)); //工作内容
sheet.addCell(new Label(5,i,rs.getString("SFTD"),wcfFC)); //是否停电
sheet.addCell(new Label(6,i,rs.getString("TDFW"),wcfFC)); //停电范围
sheet.addCell(new Label(7,i,rs.getString("JHLRFLBH"),wcfFC)); //任务来源
sheet.addCell(new Label(8,i,rs.getString("JHLRBH"),wcfFC)); //来源编号
sheet.addCell(new Label(9,i,rs.getString("ZXDWMC"),wcfFC)); //执行单位
sheet.addCell(new Label(10,i,rs.getString("PHDWMC"),wcfFC)); //配合单位
sheet.addCell(new Label(11,i,rs.getString("DBRMC"),wcfFC)); //督办人
// sheet.addCell(new Label(12,i,rs.getString("JHKSSJ"),wcfFC)); //开始时间
// sheet.addCell(new Label(13,i,rs.getString("JHWCSJ"),wcfFC)); //结束时间
if(rs.getString("JHKSSJ")!=null)sheet.addCell(new jxl.write.DateTime(12, i,DateFormat.getDateTimeInstance().parse(rs.getString("JHKSSJ")), wcfDF));
if(rs.getString("JHWCSJ")!=null)sheet.addCell(new jxl.write.DateTime(13, i,DateFormat.getDateTimeInstance().parse(rs.getString("JHWCSJ")), wcfDF));
sheet.addCell(new Label(14,i,rs.getString("BZ"),wcfFC)); //备注
i++;
}
book.write();
book.close();
rs.close();
pStatement.close();
return true;
}

catch(Exception e)
{
e.printStackTrace();
return false;
}
finally{
try{

if (rs!=null){
rs.close();
}

if (pStatement!=null){
pStatement.close();
}
if (conn!=null){
conn.close();
}
}
catch(SQLException sqle){
conn = null;
}
}
}

public void upload(String filename){
String flagstr;
try{
jxl.Workbook rwb = Workbook.getWorkbook(new File("pengyue//webmis//upload//"+filename+".xls"));
Sheet st = rwb.getSheet("zscjh");
int i = 4; //其始列-1
flagstr = st.getCell(0,0).getContents();
if(flagstr.equals("zscjhbegin"))flag=true;
while(st.getCell(2,i).getContents().length()!=0&&flag==true){
dataZJHXM mydata = new dataZJHXM();
for(int j=2;j<st.getColumns();j++){
if(st.getCell(j,0).getContents().equals("XH"))mydata.XH = st.getCell(j,i).getContents();
if(st.getCell(j,0).getContents().equals("XMMC"))mydata.XMMC = st.getCell(j,i).getContents();
if(st.getCell(j,0).getContents().equals("GZNR"))mydata.GZNR = st.getCell(j,i).getContents();
if(st.getCell(j,0).getContents().equals("SFTD"))mydata.SFTD = st.getCell(j,i).getContents();
if(st.getCell(j,0).getContents().equals("TDFW"))mydata.TDFW = st.getCell(j,i).getContents();
if(st.getCell(j,0).getContents().equals("JHLRFLBH"))mydata.JHLRFLBH = st.getCell(j,i).getContents();
if(st.getCell(j,0).getContents().equals("JHLRBH"))mydata.JHLRBH = st.getCell(j,i).getContents();
if(st.getCell(j,0).getContents().equals("ZXDWMC"))mydata.ZXDWMC = st.getCell(j,i).getContents();
if(st.getCell(j,0).getContents().equals("PHDWMC"))mydata.PHDWMC = st.getCell(j,i).getContents();
if(st.getCell(j,0).getContents().equals("DBRMC"))mydata.DBRMC = st.getCell(j,i).getContents();
//时间处理
if(st.getCell(j,0).getContents().equals("JHKSSJ"))mydata.JHKSSJ = FormateData(st.getCell(j,i));
if(st.getCell(j,0).getContents().equals("JHWCSJ"))mydata.JHWCSJ = FormateData(st.getCell(j,i));
if(st.getCell(j,0).getContents().equals("BZ"))mydata.BZ = st.getCell(j,i).getContents();
}
subdata.add(mydata);
i++;
}
rwb.close();
}catch(Exception e){
e.printStackTrace();
flag = false;
}
}

//处理日期格式数据
public String FormateData(Cell formatecell){
try{
java.util.Date mydate=null;
DateCell datecll = (DateCell)formatecell;
mydate =datecll.getDate();
long time=(mydate.getTime()/1000)-60*60*8;
mydate.setTime(time*1000);
Calendar cal = Calendar.getInstance();
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm");
return formatter.format(mydate);
}
catch(Exception e){
e.printStackTrace();
return null;
}
}

public boolean getFlag(){
return flag;
}

public ArrayList getData(){
return subdata;
}

public static class dataZJHXM implements Serializable
{
public String XH;
public String XMMC;
public String GZNR;
public String SFTD;
public String TDFW;
public String JHLRFLBH;
public String JHLRBH;
public String ZXDWMC;
public String PHDWMC;
public String DBRMC;
public String JHKSSJ;
public String JHWCSJ;
public String BZ;

public dataZJHXM(){
XH = "";
XMMC = "";
GZNR = "";
SFTD = "";
TDFW = "";
JHLRFLBH = "";
JHLRBH = "";
ZXDWMC = "";
PHDWMC = "";
DBRMC = "";
JHKSSJ = "";
JHWCSJ = "";
BZ = "";
}

}
}

JSP转EXCEL

<%@ page contentType="application/msexcel;charset=utf-8" %>

<%
   response.setHeader("Content-disposition","inline; filename=test1.xls");

   //以上这行设定传送到前端浏览器时的档名为test1.xls
   //就是靠这一行，让前端浏览器以为接收到一个excel档
%>
<html>
<head>
<meta http-equiv="Content-Type" content="application/msexcel; charset=utf-8">
<title>Excel档案呈现方式</title>
</head>
<body>
<table border="1" width="100%">
    <tr>
      <td>姓名</td>
      <td>身份证字号</td>
      <td>生日안녕</td>
    </tr>
    <tr>
      <td>李玟</td>
      <td>N111111111</td>
      <td>1900/11/12</td>
    </tr>
    <tr>
      <td>梁静如</td>
      <td>N222222222</td>
      <td>1923/10/1</td>
    </tr>
    <tr>
      <td>张惠妹</td>
      <td>N333333333</td>
      <td>1934/12/18</td>
    </tr>
</table>
</body>
</html>

<HTML>
<BODY>
<SCRIPT LANGUAGE="VBScript">
Function CreateNamesArray()
' Create an array to set multiple values at once.
Dim saNames(5, 2)
saNames(0, 0) = "John"
saNames(0, 1) = "Smith"
saNames(1, 0) = "Tom"
saNames(1, 1) = "Brown"
saNames(2, 0) = "Sue"
saNames(2, 1) = "Thomas"
saNames(3, 0) = "Jane"
saNames(3, 1) = "Jones"
saNames(4, 0) = "Adam"
saNames(4, 1) = "Johnson"
CreateNamesArray = saNames
End Function
</SCRIPT>

<SCRIPT LANGUAGE="JScript">
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;
oXL.UserControl = true;
}

function DispalyQuarterlySales(oWS)
{
var iNumQtrs, sMsg, iRet;

// Number of quarters to display data for.
iNumQtrs = 4;

// Starting at E1, fill headers for the number of columns selected.
var oResizeRange = oWS.Range("E1", "E1").Resize(1,iNumQtrs);
oResizeRange.Formula = "=/"Q/" & COLUMN()-4 & CHAR(10) & /"Sales/"";

// Change the Orientation and WrapText properties for the headers.
oResizeRange.Orientation = 38;
oResizeRange.WrapText = true;

// Fill the interior color of the headers.
oResizeRange.Interior.ColorIndex = 36;

// Fill the columns with a formula and apply a number format.
oResizeRange = oWS.Range("E2", "E6").Resize(5,iNumQtrs);
oResizeRange.Formula = "=RAND()*100";
oResizeRange.NumberFormat = "$0.00";

// Apply borders to the Sales data and headers.
oResizeRange = oWS.Range("E1", "E6").Resize(6,iNumQtrs);
oResizeRange.Borders.Weight = 2; // xlThin

// Add a Totals formula for the sales data and apply a border.
oResizeRange = oWS.Range("E8", "E8").Resize(1,iNumQtrs);
oResizeRange.Formula = "=SUM(E2:E6)";
// 9 = xlEdgeBottom
oResizeRange.Borders(9).LineStyle = -4119; //xlDouble
oResizeRange.Borders(9).Weight = 4; //xlThick

// Add a Chart for the selected data.
oResizeRange = oWS.Range("E2:E6").Resize(5,iNumQtrs);
var oChart = oWS.Parent.Charts.Add();
oChart.ChartWizard(oResizeRange, -4100, null, 2); // -4100 = xl3dColumn
oChart.SeriesCollection(1).XValues = oWS.Range("A2", "A6");
for (iRet = 1; iRet <= iNumQtrs; iRet++) {
oChart.SeriesCollection(iRet).Name = "=/"Q" + iRet + "/"";
}
oChart.Location(2, oWS.Name); // 2 = xlLocationAsObject

// Move the chart so as not to cover your data.
oWS.Shapes("Chart 1").Top = oWS.Rows(10).Top;
oWS.Shapes("Chart 1").Left = oWS.Columns(2).Left;
}
</SCRIPT>
<P><INPUT id=button1 type=button value="創建Excel表格和柱状図" onclick="AutomateExcel"></P>
</BODY>
</HTML>
---------------------------------------------------------------------------