Question

jsp如何导出excel

详细代码！

Answer 1

类文件，导入poi包。package com.grs.exportexcel;import java.io.*;
import java.util.List;
import org.apache.poi.hssf.usermodel.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;public final class ExcelUtils
{public ExcelUtils() {
}/**
* 导出excel对外接口
*
* @param title 标题 如：同业对标分段统计报表
* @param tableData 数据表数据 如：new String[][]{{"表头1","表头2"},{"data1","data2"}}
* @param exportFileName 导出后的文件名 如：data.xls
* @param request
* @param response
*/
public static void exportExcelData(
String title,
String[][] tableData,
String exportFileName,
HttpServletRequest request,
HttpServletResponse response) {
response.reset();
String fileName = "attachment;filename=\"" + exportFileName + "\";";
response.setHeader("Content-disposition", fileName);
response.setContentType("application/vnd.ms-excel");
try {
exportExcel(title,tableData,response.getOutputStream(),createWorkbook());
response.getOutputStream().flush();
response.getOutputStream().close();
} catch (Exception e) {
e.printStackTrace();
// log.error(e.getMessage(), e);
}
}
protected static void exportExcel(
String title,
String[][] tableData,
OutputStream output,
HSSFWorkbook workbook)
throws Exception {
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setFillBackgroundColor((short) 55);
HSSFFont hsFont = workbook.createFont();
hsFont.setBoldweight((short) 700);
String titles[] = tableData[0];
titleStyle.setFont(hsFont);
titleStyle.setAlignment((short) 2);
HSSFRow row = sheet.createRow(1); row.setHeight((short) 300);
HSSFCell cell = createCell(row, (short) 0, title, titleStyle); row = sheet.createRow(3);
row.setHeight((short) 250);
short i = 0;
for (int forI = 0; forI < titles.length; forI++) {
createCell(row, i, titles[forI], titleStyle);
i++;
} int rowCount = 4; for (int rowPos = 1; rowPos < tableData.length; rowPos++) {
row = sheet.createRow(rowCount++);
i = 0;
for (int colPos = 0; colPos < tableData[rowPos].length; colPos++) {
createCell(row, i, tableData[rowPos][colPos], null);
i++;
}
}
try {
workbook.write(output);
} catch (Exception e) {
//throw new Exception(e);
}
} protected static HSSFCell createCell(
HSSFRow row,
short cellCount,
String value,
HSSFCellStyle titleStyle) {
HSSFCell cell = row.createCell(cellCount);
cell.setEncoding((short) 1);
if (titleStyle != null)
cell.setCellStyle(titleStyle);
cell.setCellValue(value); return cell;
}public static HSSFWorkbook createWorkbook() throws Exception {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
return wb;
}public static HSSFWorkbook createWorkbook(int sheetCount)
throws Exception {
HSSFWorkbook wb = new HSSFWorkbook();
for (int i = 0; i < sheetCount; i++) {
HSSFSheet si = wb.createSheet();
} return wb;
}public static HSSFWorkbook createWorkbook(int sheetCount, List sblxList)
throws Exception {
HSSFWorkbook wb = new HSSFWorkbook();
for (int i = 0; i < sheetCount; i++) {
HSSFSheet si = wb.createSheet((String) sblxList.get(i));
wb.setSheetName(i, (String) sblxList.get(i), (short) 1);
} return wb;
}}页面代码！<%@ page contentType="text/html; charset=gb2312"%>
<%@ page import="java.util.*"%>
<%@ page import="com.grs.exportexcel.*"%><%
//导出在数据
String queryData[][] = new String[3][2]; //[行][列]
//列名
/***
***在此处获得请求在参数，根据参数或者调用方法得到结果
***将结果存入queryDate[][]中
***/ String exportFileName = "data.xls";//导出Excel在名称可以根据参数命名。
//导出EXCEL
ExcelUtils.exportExcelData("san", queryData, exportFileName,
request, response);
if (out != null && out.getBufferSize() != 0) {
out.clearBuffer();
out = pageContext.pushBody();
}
%>

Answer 2

jsp页面上的表数据进行导出。
导出的是一个list对象。还是需要后台进行操作。
以下代码没有把excel进行附件下载操作。
Java代码如下：

package util;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import entity.Car;
import jxl.Sheet;
import jxl.Workbook;

public class Excelutil {
private File upload;
/**
* 数据输出到excel
* @throws IOException
*时间2015年8月18日
*
* */
public void Excelutilout(List<Car> list, Class<Car> cal) throws IOException{

HSSFWorkbook wb= new HSSFWorkbook();// 第一步，创建一个webbook，对应一个Excel文件
HSSFSheet sheet=wb.createSheet("车辆表"); // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFRow row=sheet.createRow((int)0);// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFCellStyle style=wb.createCellStyle(); // 第四步，创建单元格，并设置值表头 设置表头居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 创建一个居中格式

/**
* 对于xecel到底要创建多少个表头由配置文件决定。每个实体类对应一个map。循环创建表头
* */
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue("编号");
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellValue("车型");
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue("类型");
cell.setCellStyle(style);
cell = row.createCell((short) 3);
cell.setCellValue("接收日期");
cell.setCellStyle(style);
cell = row.createCell((short) 4);
cell.setCellValue("部品数");
cell.setCellStyle(style);
cell = row.createCell((short) 5);
cell.setCellValue("设变数");
cell.setCellStyle(style);

for (int i = 0; i < list.size(); i++)
{
row = sheet.createRow((int) i + 1);
Car car = (Car) list.get(i);
// 第四步，创建单元格，并设置值
row.createCell((short) 0).setCellValue(car.getId());
row.createCell((short) 1).setCellValue(car.getTypes());
row.createCell((short) 2).setCellValue(car.getKinds());
cell = row.createCell((short) 3);
cell.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(car.getRec_date()));
row.createCell((short) 4).setCellValue(car.getCom_num());
row.createCell((short) 5).setCellValue(car.getEc_num());
}
// 第六步，将文件存到指定位置
FileOutputStream fout=null;
try
{
fout = new FileOutputStream("E:/123.xls");
wb.write(fout);
fout.close();
}
catch (Exception e)
{
e.printStackTrace();
}finally {
fout.close();
}
}

/**
* 导入。读取文件
* @throws Exception
* */
public List<Car> Excelutildaoru(String path) throws Exception{
File f = new File(path);
FileReader fr = new FileReader(f);
BufferedReader br = new BufferedReader(fr);
SimpleDateFormat sim=new SimpleDateFormat("yyyy-MM-dd");
List<Car> list=new ArrayList<Car>();

try{

Workbook rwb=Workbook.getWorkbook(f);
Sheet rs=rwb.getSheet(0);
int clos=rs.getColumns();//得到所有的列
int rows=rs.getRows();//得到所有的行

for (int i = 1; i < rows; i++) {
for (int j = 0; j < clos; j++) {
//第一个是列数，第二个是行数
int id=Integer.parseInt(rs.getCell(j++, i).getContents());//默认最左边编号也算一列 所以这里得j++
String types=rs.getCell(j++, i).getContents();
String kinds=rs.getCell(j++, i).getContents();
Date rec_date=sim.parse(rs.getCell(j++, i).getContents());
int com_num=Integer.parseInt(rs.getCell(j++, i).getContents());
int ec_num=Integer.parseInt(rs.getCell(j++, i).getContents());
Car car = new Car();
car.setId(id);
car.setTypes(types);
car.setKinds(kinds);
car.setCom_num(com_num);
car.setEc_num(ec_num);
car.setRec_date(rec_date);
list.add(car);
}

}return list;
}catch(Exception e){

}
return null;

}

}

Answer 3

先生成excel，然后下载文件的形式导出。