int rownumber = rs.getRows();//多少行 int cellnumber = rs.getColumns();//多少列 for (int k = 0; k < cellnumber; k++) { jxl.write.Label labelC = new jxl.write.Label(k,rownumber,"这是第'"+rownumber+"'行,第'"+k+"'列"); labelC.setString("fdsadf"); //k表示哪一列,rownumber表示哪一行,从而定向到某个单元格,第三个参数是单元格内的内容 rs.addCell(labelC);//把此单元格放入表中 }
wr.write();//写入 wr.close(); } public String GoujianTable(String s ) throws Exception{ int rownumber=s.split(";").length; int cellnumber=s.split(";")[0].split(",").length; String [] slist=s.split(";"); String talbeString="<table >"; for(int i=0; i<rownumber;i++){ String [] sllist=slist[i].split(","); talbeString=talbeString+"<tr>"; for(int k=0;k<cellnumber-1;k++){ talbeString=talbeString+"<td>"; talbeString=talbeString+sllist[k]; talbeString=talbeString+"</td>";
} talbeString=talbeString+"</tr>"; } talbeString=talbeString+"</table>"; return talbeString; } public void Inserted(String s ) throws Exception{ int rownumber=s.split(";").length; int cellnumber=s.split(";")[0].split(",").length; WritableSheet rs = wr.getSheet(0);//查找表一 String [] slist=s.split(";"); for(int i=0; i<rownumber;i++){ String [] sllist=slist[i].split(","); for(int k=0;k<cellnumber;k++){ jxl.write.Label labelC = new jxl.write.Label(k,i,sllist[k]); rs.addCell(labelC); System.out.println("22"); }
createExcel.crExcel(gateWayId,cpId,new String(cpName.getBytes("iso8859-1"),"gb2312"),tim,path);
java.io.BufferedInputStream bis=null;
java.io.BufferedOutputStream bos=null;
try{
String filename = mon+"月"+cpName+".xls";
filename=new String(filename.getBytes("iso8859-1"),"gb2312");
response.setContentType("application/x-msdownload");
response.setHeader("Content-disposition","attachment; filename="+new String(filename.getBytes("gb2312"),"iso8859-1"));
bis =new java.io.BufferedInputStream(new java.io.FileInputStream(config.getServletContext().getRealPath("file/" + filename)));
bos=new java.io.BufferedOutputStream(response.getOutputStream());
byte[] buff = new byte[2048];
int bytesRead;
while(-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff,0,bytesRead);
}
}
catch(Exception e){
e.printStackTrace();
}
finally {
if (bis != null)bis.close();
if (bos != null)bos.close();
}红色的字 表示:在服务器端新建excel,然后以流得方式 读出来,写入到输出流就可以了
Struts2上传和下载CSV与Excel文件
如果是struts2 上传下载更简单
{ public InputStream getInputStream()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet1"); HSSFRow row = sheet.createRow(6);
HSSFCell cell=null;
HSSFCellStyle style = wb.createCellStyle();
String[] str={"序号","姓","名","年龄"};
for(int i=0;i<str.length;i++){
cell = row.createCell((short)i);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(str[i]);
}
/* HSSFCell cell = row.createCell((short) 0);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("序号"); cell = row.createCell((short) 1);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("姓"); cell = row.createCell((short) 2);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("名"); cell = row.createCell((short) 3);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("年龄");
*/ List<User> list = this.findAll();
System.out.println("# "+list); for (int i = 0; i < list.size(); ++i)
{
User user = list.get(i);
row = sheet.createRow(i + 7); cell = row.createCell((short) 0);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(i + 1); cell = row.createCell((short) 1);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(user.getFirstname()); cell = row.createCell((short) 2);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(user.getLastname()); cell = row.createCell((short) 3);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(user.getAge());
}
//利用流体得到内存中数据的方法一
/* String fileName = MakeRandomString.RandomString1(10);
fileName = new StringBuffer(fileName).append(".xls").toString();
final File file = new File(fileName); try
{
OutputStream os = new FileOutputStream(file);
wb.write(os);
os.close();
}
catch (Exception e)
{
e.printStackTrace();
} InputStream is = null;
try
{
is = new FileInputStream(file);
}
catch (FileNotFoundException e)
{
e.printStackTrace();
} new Thread(new Runnable() {
public void run() {
try {
Thread.sleep(15000);
file.delete();
} catch (InterruptedException e) {
e.printStackTrace();
}
}
}
).start();
return is;
*/
//利用流体得到内存中数据的方法二
ByteArrayOutputStream os=new ByteArrayOutputStream(1024);
try {
wb.write(os);
} catch (IOException e) {
e.printStackTrace();
}
/* byte[] b=wb.getBytes();//此方法仅仅只获得HSSFWorkbook中部分的字符
System.out.println(b);
InputStream is=new ByteArrayInputStream(b);
*/
byte[] b=os.toByteArray();
System.out.println(b);
InputStream is=new ByteArrayInputStream(b);
return is;
}
}Action层:
public class GenerateExcelAction extends ActionSupport
{
//注意红色文字,javabean规范,一会他和struts.xml中的文件相匹对
public InputStream getDownloadFile()
{
return this.service.getInputStream();
}
@Override
public String execute() throws Exception
{
return SUCCESS;
}
}struts.xml:
<action name="generateExcel" class="generateExcelAction">
<result name="success" type="stream">
<param name="contentType">application/vnd.ms-excel</param>
<param name="contentDisposition">attachment;filename="AllUsers.xls"</param>
<param name="inputName">downloadFile</param>
</result>
</action>
然后就可以直接下载了 很简单
request.getRequestDispatcher("/excel_template/excel_details.jsp").forward(request,response);
然后你自己搞一个excel_details.jsp中间放着你要得模板
就可以简单实现这个功能
/*
*导出Excel
*/
public ActionForward exportExcel(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response) {
List list = this.userService.getAll();
try {
//设置导出类型
response.setContentType("application/msexcel");
String fileName = URLEncoder.encode("员工表.xls", "UTF-8");
//设置头文件
response.setHeader("Content-disposition", "attachment;filename="+fileName);
OutputStream os = response.getOutputStream();
WritableWorkbook workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet("员工信息表", 0);
for(int i=0;i<list.size();i++){
TUser user = (TUser) list.get(i);
int j=0;
sheet.addCell(new Label(j++,i,user.getUId().toString()));
sheet.addCell(new Label(j++,i,user.getUName()));
sheet.addCell(new Label(j++,i,user.getUSex()));
}
workbook.write();
workbook.close();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;import java.util.*;
/**
* 2011-4-27
* Caozuo.java
* author:zhouquan
* tel:15058149753
* email:[email protected]
*/
public class Caozuo {
InputStream is = null; //定义一个输入流
jxl.Workbook rwb = null;//定义一个只读的工作薄
WritableWorkbook wr=null; //定义一个可写的工作薄
FileOutputStream out=null;
WritableSheet sheet=null;
String worksheet = "List";//输出的excel文件工作表名
public Caozuo() {
try {
out=new FileOutputStream("E:\\a.xls");
wr=Workbook.createWorkbook(out);
sheet = wr.createSheet(worksheet, 0);
// WritableWorkbook wr = Workbook.createWorkbook(new File("E:\\a.xls"));
// //out=new FileOutputStream("E:\\a.xls");
// is = new FileInputStream("E:\\a.xls");//创建一个输入流
// rwb = Workbook.getWorkbook(is);//创建只读工作薄
// //wr=Workbook.createWorkbook(out);
// wr=Workbook.createWorkbook(new File("E:\\a.xls"), rwb);//创建可写的工作薄 } catch (Exception e) {
e.printStackTrace();
}
} public ArrayList Select() {
ArrayList arr = new ArrayList();
Sheet rs = rwb.getSheet(0);//读取第一个表
for (int i = 0; i < rs.getRows(); i++) {
ArrayList arr1 = new ArrayList();
for (int k = 0; k < rs.getColumns(); k++) {
Cell c00 = rs.getCell(k, i);//k表示列,i表示行,注列和行从0开始
String strc00 = c00.getContents();//getContents()方法是将Cell转换为字符串形式
arr1.add(strc00);
}
arr.add(arr1);
}
rwb.close();
return arr;
} public void Insert() throws RowsExceededException, WriteException, IOException {
WritableSheet rs = wr.getSheet(0);//查找表一
//WritableSheet rs = (WritableSheet)rwb.getSheet(0);
int rownumber = rs.getRows();//多少行
int cellnumber = rs.getColumns();//多少列
for (int k = 0; k < cellnumber; k++) {
jxl.write.Label labelC = new jxl.write.Label(k,rownumber,"这是第'"+rownumber+"'行,第'"+k+"'列");
labelC.setString("fdsadf");
//k表示哪一列,rownumber表示哪一行,从而定向到某个单元格,第三个参数是单元格内的内容
rs.addCell(labelC);//把此单元格放入表中
}
wr.write();//写入
wr.close();
}
public String GoujianTable(String s ) throws Exception{
int rownumber=s.split(";").length;
int cellnumber=s.split(";")[0].split(",").length;
String [] slist=s.split(";");
String talbeString="<table >";
for(int i=0; i<rownumber;i++){
String [] sllist=slist[i].split(",");
talbeString=talbeString+"<tr>";
for(int k=0;k<cellnumber-1;k++){
talbeString=talbeString+"<td>";
talbeString=talbeString+sllist[k];
talbeString=talbeString+"</td>";
}
talbeString=talbeString+"</tr>";
}
talbeString=talbeString+"</table>";
return talbeString;
}
public void Inserted(String s ) throws Exception{
int rownumber=s.split(";").length;
int cellnumber=s.split(";")[0].split(",").length;
WritableSheet rs = wr.getSheet(0);//查找表一
String [] slist=s.split(";");
for(int i=0; i<rownumber;i++){
String [] sllist=slist[i].split(",");
for(int k=0;k<cellnumber;k++){
jxl.write.Label labelC = new jxl.write.Label(k,i,sllist[k]);
rs.addCell(labelC);
System.out.println("22");
}
}
wr.write();//写入
System.out.println("**********************");
wr.close();
}
public void Delete(int id) throws IOException, WriteException{//id表示行号
WritableSheet rs=wr.getSheet(0);//查找表一
rs.removeRow(id-1);//删除行
wr.write();//从内存中写入
wr.close();
}
public void Update(int rowid,int columid,String cansu) throws RowsExceededException, WriteException, IOException{
WritableSheet rs=wr.getSheet(0);
//因为行和列都是从0开始,所以要减一
jxl.write.Label labelC = new jxl.write.Label(columid-1,rowid-1,cansu);
//WritableCell cell=(WritableCell) rs.getCell(columid-1,rowid-1);
rs.addCell(labelC);
wr.write();//写入
wr.close();
}
}