81,092
社区成员
发帖
与我相关
我的任务
分享请发表友善的回复…
发表回复
The_end90 2014-09-30
- 打赏
- 举报
可以使用JXL，挺简单的
wmpcf 2014-09-30
- 打赏
- 举报
你用jacob试试，里面有方法，我操作word用到的，基本上是几个方法组合调用的！
wyp954311 2014-09-30
- 打赏
- 举报
我这有源码和jre文件，回去可以发给你，十点左右吧，要的话回复我
不止鱼 2014-09-30
- 打赏
- 举报
补充一句，希望对后来者有用：
当时我也在网上找了一下，找到了@jevon2714 的方法，
里面逻辑大都没错。
但是你要知道，EXCEL在超过多少行之后，会写不进去数据，需要新建一个sheet,
也就是这句：
if (rownum == 60000) {
sheet = workbook.createSheet();// 创建个空白的sheet
rownum = 0;
}不止鱼 2014-09-30
- 打赏
- 举报
两年以前写过，是用POI封装的。
专门翻开以前的项目，工具类这么写的：
package com.zyd.common;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Iterator;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
* 导出文件公用类
*
* @author li_zq
*
*/
public class Export {
/**
* 导出文件
*
* @param fileValue
* 文件内容
* @param file
* 文件
* @throws IOException
*/
public static void ExportFile(String fileValue, File file)
throws IOException {
FileWriter fw = null;
BufferedWriter bw = null;
fw = new FileWriter(file);
bw = new BufferedWriter(fw);
bw.write(fileValue);
bw.flush();
bw.close();
}
/**
* 导出文件
*
* @param fileValue
* 文件内容
* @param filePath
* 文件路径
* @throws IOException
*/
public static void ExportFile(String fileValue, String filePath)
throws IOException {
FileWriter fw = null;
BufferedWriter bw = null;
fw = new FileWriter(filePath);
bw = new BufferedWriter(fw);
bw.write(fileValue);
bw.flush();
bw.close();
}
/**
* 统计导出EXCEL文件
*
* @param list
* @param request
*/
public static void ExportEXCEL(List<?> list, HttpServletRequest request) {
File path = new File(request.getSession().getServletContext()
.getRealPath("\\")
+ "ftpfiles\\backfile\\"
+ request.getSession().getId()
+ "exportEXCEL.xls");
HSSFWorkbook workbook = new HSSFWorkbook();// 创建个空白的workbook
HSSFSheet sheet = workbook.createSheet();// 创建个空白的sheet
int rownum = 0;
for (Iterator<?> iterator = list.iterator(); iterator.hasNext();) {
Object[] objects = (Object[]) iterator.next();
if (rownum == 60000) {
sheet = workbook.createSheet();// 创建个空白的sheet
rownum = 0;
}
HSSFRow row = sheet.createRow(rownum++);// 创建行
for (int i = 0; i < objects.length; i++) {
HSSFCell cell = row.createCell(i);// 创建上面行的第一个单元格
if (objects[i] != null) {
cell.setCellValue(objects[i].toString());
} else {
cell.setCellValue("");
}
}
}
FileOutputStream out = null;
try {
out = new FileOutputStream(path.getAbsolutePath());
workbook.write(out);// 调用HSSFWorkbook类的write方法写入到输出流
} catch (IOException e) {
System.out.println(e.toString());
} finally {
try {
out.close();
} catch (IOException e) {
System.out.println(e.toString());
}
}
}
/**
* 统计导出EXCEL文件
*
* @param list
* @param request
*/
public static void ExportEXCEL(List<?> list, HttpServletRequest request,String[]titles) {
File path = new File(request.getSession().getServletContext()
.getRealPath("\\")
+ "ftpfiles\\backfile\\"
+ request.getSession().getId()
+ "exportEXCEL.xls");
HSSFWorkbook workbook = new HSSFWorkbook();// 创建个空白的workbook
HSSFSheet sheet = workbook.createSheet();// 创建个空白的sheet
int rownum = 0;
HSSFRow row = sheet.createRow(rownum);// 创建行
for(int j=0;j<titles.length;j++){
row.createCell(j).setCellValue(titles[j]);
}
for (Iterator<?> iterator = list.iterator(); iterator.hasNext();) {
Object[] objects = (Object[]) iterator.next();
if (rownum == 60000) {
sheet = workbook.createSheet();// 创建个空白的sheet
rownum = 0;
}
row = sheet.createRow(++rownum);// 创建行
for (int i = 0; i < objects.length; i++) {
HSSFCell cell = row.createCell(i);// 创建上面行的第一个单元格
if (objects[i] != null) {
cell.setCellValue(objects[i].toString());
} else {
cell.setCellValue("");
}
}
}
FileOutputStream out = null;
try {
out = new FileOutputStream(path.getAbsolutePath());
workbook.write(out);// 调用HSSFWorkbook类的write方法写入到输出流
} catch (IOException e) {
System.out.println(e.toString());
} finally {
try {
out.close();
} catch (IOException e) {
System.out.println(e.toString());
}
}
}
/**
* 下载文件
*
* @param response
* @param file
* @throws IOException
*/
public static void downFile(HttpServletResponse response, File file)
throws IOException {
BufferedInputStream br = new BufferedInputStream(new FileInputStream(
file));
byte[] buf = new byte[1024];
int len = 0;
response.reset();
response.setContentType("text/plain");
response.setHeader("Content-Disposition", "attachment; filename="
+ file.getName());
OutputStream out = response.getOutputStream();
while ((len = br.read(buf)) > 0)
out.write(buf, 0, len);
file.delete();
br.close();
out.close();
}
/**
* 下载文件
*
* @param response
* @param path
* @throws IOException
*/
public static HttpServletResponse downFile(HttpServletResponse response,
String path) throws IOException {
try {
// path下载的文件的路径。
File file = new File(path);
// 取得文件名。
String filename = file.getName();
// 取得文件的后缀名。
/*String ext = filename.substring(filename.lastIndexOf(".") + 1)
.toUpperCase();*/
// 以流的形式下载文件。
InputStream fis = new BufferedInputStream(new FileInputStream(path));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
// 清空response
response.reset();
// 设置response的Header
response.addHeader("Content-Disposition", "attachment;filename="
+ new String(filename.getBytes()));
response.addHeader("Content-Length", "" + file.length());
OutputStream toClient = new BufferedOutputStream(
response.getOutputStream());
response.setContentType("application/octet-stream");
file.delete();
toClient.write(buffer);
toClient.flush();
toClient.close();
} catch (IOException ex) {
ex.printStackTrace();
}
return response;
}
}
jevon2714 2014-09-30
- 打赏
- 举报
PS:这里主要用于将数据传递到后台之后，再将值插入到EXCEl表格中，若有不明白请留言！！
package eclipseToExcel;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.sql.Date;
import java.text.SimpleDateFormat;
import javax.swing.JOptionPane;
/**
* 一个Excel文件的层次：Excel文件->工作表->行->单元格 对应到POI中，为：workbook->sheet->row->cell
*/
public class EclipseToExcel {
SimpleDateFormat formatter = new SimpleDateFormat("yyyy年MM月dd日HHmmss");
Date curDate = new Date(System.currentTimeMillis());// 获取当前时间
String str = formatter.format(curDate).trim();
public String outputFile = "e:\\" + str + ".xls";
public String fileToBeRead = "e:\\test.xls";
/**
* 生成Excel表格
*/
public void createExcel() {
try {
// 创建新的Excel 工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 在Excel工作簿中建一工作表，其名为缺省值, 如要新建一名为"效益指标"的工作表，其语句为：
HSSFSheet sheet = workbook.createSheet("效益指标");
// 创建行对象
HSSFRow row = null;
// 创建列对象
HSSFCell cell = null;
row = sheet.createRow((short) 0);
// 在索引0的位置创建单元格（列）
cell = row.createCell((short) 0);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
String rowstr [] = {"id","name","Addr"};
for(int i = 0;i<rowstr.length;i++) {
cell = row.createCell((short) 0);
cell.setCellValue(rowstr[i]);
}
row = sheet.createRow((short) 1);
cell = row.createCell((short) 0);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
String[] str = {"1", "Jevon", "高新区"};
for (int i = 0; i < str.length; i++) {
cell = row.createCell((short) i);
cell.setCellValue(str[i]);
}
// 新建一输出文件流
FileOutputStream fOut = new FileOutputStream(outputFile);
// 把相应的Excel 工作簿存盘
workbook.write(fOut);
fOut.flush();
// 操作结束，关闭文件
fOut.close();
System.out.println("文件生成...");
} catch (Exception e) {
e.printStackTrace();
System.out.println("已运行 xlCreate() : " + e);
}
}
public static void main(String[] args) {
EclipseToExcel poi = new EclipseToExcel();
poi.createExcel();
JOptionPane.showMessageDialog(null, "文件写入成功", "POI", 1);
// poi.readExcel();
// JOptionPane.showMessageDialog(null, "文件读取成功", "POI", 1);
}Morning_peter 2014-09-30
- 打赏
- 举报
主要是可以默认Excel保存的路径，不需要弹出框让用户手动选择路径
Golden_Dog 2014-09-30
- 打赏
- 举报
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="EXCEL" method="post">
<input type="text" name="filename" />
<input type="submit" value="保存" />
</form>
</body>
</html>package action;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class EXCEL extends HttpServlet{
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
this.doPost(request, response);
}
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
String filename=request.getParameter("filename");
文件头
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition" ,"inline; filename="+filename);
ByteArrayOutputStream out = new ByteArrayOutputStream();
InputStream in=null;
输出流
ServletOutputStream outexcel = null;
HSSFWorkbook wb = new HSSFWorkbook();
Complete comp=new Complete();
测试数据
DutyTable dutytable=new DutyTable();
List <DutyTable> DTlist=new ArrayList<DutyTable>();
dutytable.setDate("9月16日");
dutytable.setRemarks("");
dutytable.setSign(" ");
dutytable.setWatcher("往哪");
dutytable.setWeekDay("星期一");
DTlist.add(dutytable);
int rownum = 0;
由测试数据创建表的函数，自己写
wb=comp.creat(rownum,wb,DTlist);
wb.write(out);
out.flush();
byte[] aa = out.toByteArray();
in = new ByteArrayInputStream(aa, 0, aa.length);
outexcel=response.getOutputStream();
outexcel.flush();
int aRead = 0;
从数组读取到对应的文件
while ((aRead = in.read()) != -1 & in != null) {
outexcel.write(aRead);
}
in.close();
out.close();
outexcel.close();
}
}
