81,090
社区成员
发帖
与我相关
我的任务
分享
package com.jagie.test;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class dfd{
public Connection getConnection(){
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@10.0.3.6:1521:MSQJGL", "msqjgl", "msqjgl");
} catch (Exception e) {
System.out.println("数据库链接异常!");
e.printStackTrace();
}
return conn;
}
@SuppressWarnings({ "unchecked", "rawtypes" })
public List getData(){
ResultSet rs = null;
List list = new ArrayList();
try {
String sql = "select * from jl_verifi_dept";
Statement st =this.getConnection().createStatement();
rs = st.executeQuery(sql);
Datathplyyvch data = null;
while(rs.next()){
data = new Datathplyyvch();
data.setVfdept_code(rs.getString("vfdept_code"));
data.setVfdept_name(rs.getString("vfdept_name"));
list.add(data);
System.out.println(rs.getString("vfdept_NAME"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
@SuppressWarnings({ "unused", "rawtypes" })
public static void writeExcel(OutputStream os) throws Exception {
try{
jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os);
jxl.write.WritableSheet ws = wwb.createSheet("TestSheet1", 0);
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
SimpleDateFormat date2Format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
java.util.Date date = new java.util.Date();
String dateStr = dateFormat.format(date);
WritableFont wf = new WritableFont(WritableFont.TIMES,16, WritableFont.BOLD,false);
WritableCellFormat wcf = new WritableCellFormat(wf);//实例化文字格式化
wcf.setAlignment(jxl.format.Alignment.CENTRE);//左右居中
wcf.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//上下居中
//打印日期行格式化
WritableFont wf2 = new WritableFont(WritableFont.TIMES,10, WritableFont.NO_BOLD,false);
WritableCellFormat wcf2 = new WritableCellFormat(wf2);//实例化文字格式化
wcf2.setAlignment(jxl.format.Alignment.LEFT);//左右居中
//wcf2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//上下居中
//列名格式化
WritableFont wf3 = new WritableFont(WritableFont.TIMES,13, WritableFont.BOLD,false);
WritableCellFormat wcf3 = new WritableCellFormat(wf3);//实例化文字格式化
wcf2.setAlignment(jxl.format.Alignment.CENTRE);//左右居中
//wcf2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//上下居中
ws.insertRow(0);
ws.mergeCells(0, 0, 1, 0);//合并单元格
ws.addCell(new Label(0, 0, "部门表",wcf));
ws.setRowView(0, 800);//设置高度
ws.insertRow(1);
ws.mergeCells(0, 1, 1, 1);
ws.addCell(new Label(0, 1, "导出日期:"+date2Format.format(date),wcf2));
ws.insertRow(2);
ws.addCell(new Label(0,2,"编码",wcf3));
ws.setColumnView(0, 10);//设置列宽度
ws.addCell(new Label(1,2,"名称",wcf3));
ws.setColumnView(1, 20);//设置列宽度
List list = new ArrayList();
//System.out.println(list.size());
for(int i=3;i<list.size()+3;i++){
Datathplyyvch data = (Datathplyyvch) list.get(i-3);
ws.insertRow(i);
ws.addCell(new Label(0, i, data.getVfdept_code()));
ws.addCell(new Label(1, i, data.getVfdept_name()));
}
//写入Excel工作表
wwb.write();
//关闭Excel工作薄对象
wwb.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
//System.out.println(getVfdept_code());
}
}
//最好写一个这样的main方法来测试一下你的这个class是否写好了。
public static void main(String[] args)throws Exception{
File f=new File("kk.xls");
f.createNewFile();
//OutputStream os = new FileOutputStream(fileWrite);;
writeExcel(new FileOutputStream(f));
}
}
package com.jagie.test;
public class Datathplyyvch {
private String vfdept_code;
private String vfdept_name;
public String getVfdept_code() {
return vfdept_code;
}
public void setVfdept_code(String vfdept_code) {
// TODO 自动生成方法存根
this.vfdept_code=vfdept_code;
}
public String getVfdept_name() {
return vfdept_name;
}
public void setVfdept_name(String vfdept_name) {
// TODO 自动生成方法存根
this.vfdept_name=vfdept_name;
}
}
<%@page import="com.jagie.test.dfd" %>
<%
response.reset();
response.setContentType("application/vnd.ms-excel");
dfd.writeExcel(response.getOutputStream());
%>
<%@page import="com.jagie.test.dfd" %>
<%@ page import="java.util.List"%>
<%@ page import="java.text.SimpleDateFormat"%>
<%@ page import="java.sql.*"%>
<%@ page import="java.io.OutputStream"%>
<%@ page import="jxl.*"%>
<%@ page import="java.io.File"%>
<%
response.reset();
response.setContentType("application/vnd.ms-excel");
//dfd.writeExcel(response.getOutputStream(), null);
dfd.writeExcel(response.getOutputStream(),list);
%>
一样的提示list cannot be resolved to a variabledfd.writeExcel(response.getOutputStream(),list);我用你的方法,就得在JSP里面加上list,但是就会提示list cannot be resolved to a variable,又该怎么解决呢,不好意思,问题可能有点笨,海涵。
<%@ page import="java.util.List" %>
其它没有的类依次也导入下//在调用这个方法之前先获得list(通过getData方法),然后传到下面的方法里dfd dObject=new dfd(); List list = dObject.getData(); File f=new File("kk.xls"); f.createNewFile(); writeExcel(new FileOutputStream(f),list);
public static void writeExcel(OutputStream os,List list) throws Exception { try{ jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os); jxl.write.WritableSheet ws = wwb.createSheet("TestSheet1", 0); SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss"); SimpleDateFormat date2Format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); java.util.Date date = new java.util.Date(); String dateStr = dateFormat.format(date); WritableFont wf = new WritableFont(WritableFont.TIMES,16, WritableFont.BOLD,false); WritableCellFormat wcf = new WritableCellFormat(wf);//实例化文字格式化 wcf.setAlignment(jxl.format.Alignment.CENTRE);//左右居中 wcf.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//上下居中 //打印日期行格式化 WritableFont wf2 = new WritableFont(WritableFont.TIMES,10, WritableFont.NO_BOLD,false); WritableCellFormat wcf2 = new WritableCellFormat(wf2);//实例化文字格式化 wcf2.setAlignment(jxl.format.Alignment.LEFT);//左右居中 //wcf2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//上下居中 //列名格式化 WritableFont wf3 = new WritableFont(WritableFont.TIMES,13, WritableFont.BOLD,false); WritableCellFormat wcf3 = new WritableCellFormat(wf3);//实例化文字格式化 wcf2.setAlignment(jxl.format.Alignment.CENTRE);//左右居中 //wcf2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//上下居中 ws.insertRow(0); ws.mergeCells(0, 0, 1, 0);//合并单元格 ws.addCell(new Label(0, 0, "部门表",wcf)); ws.setRowView(0, 800);//设置高度 ws.insertRow(1); ws.mergeCells(0, 1, 1, 1); ws.addCell(new Label(0, 1, "导出日期:"+date2Format.format(date),wcf2)); ws.insertRow(2); ws.addCell(new Label(0,2,"编码",wcf3)); ws.setColumnView(0, 10);//设置列宽度 ws.addCell(new Label(1,2,"名称",wcf3)); ws.setColumnView(1, 20);//设置列宽度 //System.out.println(list.size()); for(int i=3;i<list.size()+3;i++){ Datathplyyvch data = (Datathplyyvch) list.get(i-3); ws.insertRow(i); ws.addCell(new Label(0, i, data.getVfdept_code())); ws.addCell(new Label(1, i, data.getVfdept_name())); } //写入Excel工作表 wwb.write(); //关闭Excel工作薄对象 wwb.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); //System.out.println(getVfdept_code()); } }
dfd dObject=new dfd();
List list = dObject.getData();
File f=new File("kk.xls");
f.createNewFile();
writeExcel(new FileOutputStream(f),list);
public static void writeExcel(OutputStream os,List list) throws Exception {
try{
jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os);
jxl.write.WritableSheet ws = wwb.createSheet("TestSheet1", 0);
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
SimpleDateFormat date2Format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
java.util.Date date = new java.util.Date();
String dateStr = dateFormat.format(date);
WritableFont wf = new WritableFont(WritableFont.TIMES,16, WritableFont.BOLD,false);
WritableCellFormat wcf = new WritableCellFormat(wf);//实例化文字格式化
wcf.setAlignment(jxl.format.Alignment.CENTRE);//左右居中
wcf.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//上下居中
//打印日期行格式化
WritableFont wf2 = new WritableFont(WritableFont.TIMES,10, WritableFont.NO_BOLD,false);
WritableCellFormat wcf2 = new WritableCellFormat(wf2);//实例化文字格式化
wcf2.setAlignment(jxl.format.Alignment.LEFT);//左右居中
//wcf2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//上下居中
//列名格式化
WritableFont wf3 = new WritableFont(WritableFont.TIMES,13, WritableFont.BOLD,false);
WritableCellFormat wcf3 = new WritableCellFormat(wf3);//实例化文字格式化
wcf2.setAlignment(jxl.format.Alignment.CENTRE);//左右居中
//wcf2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//上下居中
ws.insertRow(0);
ws.mergeCells(0, 0, 1, 0);//合并单元格
ws.addCell(new Label(0, 0, "部门表",wcf));
ws.setRowView(0, 800);//设置高度
ws.insertRow(1);
ws.mergeCells(0, 1, 1, 1);
ws.addCell(new Label(0, 1, "导出日期:"+date2Format.format(date),wcf2));
ws.insertRow(2);
ws.addCell(new Label(0,2,"编码",wcf3));
ws.setColumnView(0, 10);//设置列宽度
ws.addCell(new Label(1,2,"名称",wcf3));
ws.setColumnView(1, 20);//设置列宽度
//System.out.println(list.size());
for(int i=3;i<list.size()+3;i++){
Datathplyyvch data = (Datathplyyvch) list.get(i-3);
ws.insertRow(i);
ws.addCell(new Label(0, i, data.getVfdept_code()));
ws.addCell(new Label(1, i, data.getVfdept_name()));
}
//写入Excel工作表
wwb.write();
//关闭Excel工作薄对象
wwb.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
//System.out.println(getVfdept_code());
}
}
不一定要把所有都改为静态的,可以把list作为参数传到writeExcel方法中 这样你在main方法中先获得这个list然后把list传到writeExcel中就行了
List list = new ArrayList();
//System.out.println(list.size());
for(int i=3;i<list.size()+3;i++){
Datathplyyvch data = (Datathplyyvch) list.get(i-3);
ws.insertRow(i);
ws.addCell(new Label(0, i, data.getVfdept_code()));
ws.addCell(new Label(1, i, data.getVfdept_name()));
}
package com.foolfish.jxl;
import java.io.File;
import java.io.IOException;
import jxl.Workbook;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class JxlWrite {
public static void main(String[] args){
WritableWorkbook workbook;
try {
//创建工作簿
workbook = Workbook.createWorkbook(new File("E:/eclipse project/Jxl/bin/test.xls"));
//创建sheet
WritableSheet sheet1 = workbook.createSheet("测试用excel", 0);
WritableSheet sheet2 = workbook.createSheet("测试excel", 1);
//开始创建cell
//WritableCell cell
for(int i = 0 ; i < 10 ;i++){
//向sheet中写入数据
sheet1.addCell(new jxl.write.Label(0, i, "书目ID"));
}
workbook.write();
workbook.close();
//sheet.addCell(new jxl.write.Label(0, 1, "书目ID"));
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
下面是个读取excel的代码
package com.foolfish.jxl;
import java.io.File;
import java.io.IOException;
import jxl.Workbook;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class JxlWrite {
public static void main(String[] args){
WritableWorkbook workbook;
try {
//创建工作簿
workbook = Workbook.createWorkbook(new File("E:/eclipse project/Jxl/bin/test.xls"));
//创建sheet
WritableSheet sheet1 = workbook.createSheet("测试用excel", 0);
WritableSheet sheet2 = workbook.createSheet("测试excel", 1);
//开始创建cell
//WritableCell cell
for(int i = 0 ; i < 10 ;i++){
//向sheet中写入数据
sheet1.addCell(new jxl.write.Label(0, i, "书目ID"));
}
workbook.write();
workbook.close();
//sheet.addCell(new jxl.write.Label(0, 1, "书目ID"));
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
List list = new ArrayList();改为 List list=getData();试试