java实现Excle导出功能，主要是通过poi,或者jxl包来实现，所谓的万能导出，无非是通过反射机制，首先创建了list，然后向list里面传递对象，而对象就可以通过反射机制来进行解析，通过解析对象里面的字段，获取所需要的数据。这里是单表实现，如果是多表实现，即有多个sheet,则可以通过创建Map,Map的key可以储存sheet名称，Map的value就可以储存List。
以下代码分别实现了，单表导出，多表导出，以及导出到本地，或者通过I/o流在页面中输出。
import java.io.File;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.Row;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class ExcleUtil {
public static void excleOut(ArrayList ar,String str){
WritableWorkbook book = null;
try {
book = Workbook.createWorkbook(new File(str));
WritableSheet sheet = book.createSheet("sheet", 0);
for(int i = 0;i<ar.size();i++){
Object ob = ar.get(i);
Class cl = ob.getClass();
Field[] fi = cl.getDeclaredFields();
for(int j = 0;j<fi.length;j++){
fi[j].setAccessible(true);
Label la = new Label(j, i, String.valueOf(fi[j].get(ob)));
sheet.addCell(la);
}
}
book.write();
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
book.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* 当需要将多个list，导入同一个文件中，不同的sheet中时候，需要修改导出方法
*/
public static void excleOut2(Map<String, ArrayList<T>> map,String str){
WritableWorkbook book = null;
try {
book = Workbook.createWorkbook(new File(str));
Set<Entry<String, ArrayList<T>>> entrys = map.entrySet();
int k = 0;
for(Entry<String, ArrayList<T>> entry : entrys){ //遍历map中的元素
WritableSheet sheet = book.createSheet(entry.getKey(), k);
k++;
for(int i = 0;i<entry.getValue().size();i++){
Object ob = entry.getValue().get(i);
Class cl = ob.getClass();
Field[] fi = cl.getDeclaredFields();
for(int j = 0;j<fi.length;j++){
fi[j].setAccessible(true);
Label la = new Label(j, i, String.valueOf(fi[j].get(ob)));
sheet.addCell(la);
}
}
}
book.write();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally{
try {
book.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* 通过输出流在网页上输出
* 需要HttpServletResponse对象，然后可以传递一个localFileName作为文件名，如test.xls。这里导出的格式都是xls格式
* map中map的key存储sheet名称，value中存储要导出的list
*/
public static void excleOut3(Map<String, ArrayList<T>> map,HttpServletResponse response,String localFileName){
HSSFWorkbook book = null;
ServletOutputStream servletOS = null;
try {
servletOS = response.getOutputStream();
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} //获取输出流
try {
book = new HSSFWorkbook();
response.setContentType("application/x-msdownload");
response.setHeader("Content-disposition", "attachment; filename=" + new String(localFileName.getBytes("gb2312"), "ISO-8859-1"));
Set<Entry<String, ArrayList<T>>> entrys = map.entrySet();
int k = 0;
for(Entry<String, ArrayList<T>> entry : entrys){ //遍历map中的元素
HSSFSheet sheet = book.createSheet(entry.getKey());
k++;
for(int i = 0;i<entry.getValue().size();i++){
Object ob = entry.getValue().get(i);
Class cl = ob.getClass();
Field[] fi = cl.getDeclaredFields();
Row row = sheet.createRow(i);
for(int j = 0;j<fi.length;j++){
fi[j].setAccessible(true);
Label la = new Label(j, i, String.valueOf(fi[j].get(ob)));
row.createCell(j).setCellValue(la.getContents());
}
}
}
book.write(servletOS);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally{
if(null!=servletOS){
try {
servletOS.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
/**
*Excle导入方法，只是获取了第一个sheet，多个sheet，这里就不扩展了
*/
public static ArrayList excleIn(Class cl,String str){
ArrayList ar = new ArrayList();
Workbook book = null;
try {
book = Workbook.getWorkbook(new File(str));
Sheet sheet = book.getSheet(0);
Field[] fi = cl.getDeclaredFields();
for(int i = 0;i<sheet.getRows();i++){
Object ob = cl.newInstance();//创建实例化对象
for(int j = 0;j<fi.length;j++){
fi[j].setAccessible(true);
String con = sheet.getCell(j,i).getContents();
if(fi[j].getType().toString().equals("class java.lang.String")){
fi[j].set(ob, con);
}else if(fi[j].getType().toString().equals("int")){
fi[j].setInt(ob, Integer.valueOf(con));
}else if(fi[j].getType().toString().equals("class java.lang.Integer")){
fi[j].setInt(ob, Integer.valueOf(con));
}
}
ar.add(ob);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
book.close();
}
return ar;
}
class Otest{
String usernameString;
String loginNameString;
public Otest(String usernameString, String loginNameString) {
super();
this.usernameString = usernameString;
this.loginNameString = loginNameString;
}
public String getUsernameString() {
return usernameString;
}
public void setUsernameString(String usernameString) {
this.usernameString = usernameString;
}
public String getLoginNameString() {
return loginNameString;
}
public void setLoginNameString(String loginNameString) {
this.loginNameString = loginNameString;
}
}
/* public static void main(String[] args) {
Map<String, ArrayList<T>> map = new HashMap<String, ArrayList<T>>();
ArrayList list = new ArrayList();
ExcleUtil eu = new ExcleUtil();
ExcleUtil.Otest tOtest = eu.new Otest("张三","zhangsan");
ExcleUtil.Otest tOtest2 = eu.new Otest("李四", "lisi");
list.add(tOtest);
list.add(tOtest2);
ArrayList list2 = new ArrayList();
ExcleUtil.Otest tOtest3 = eu.new Otest("王五","wangwu");
ExcleUtil.Otest tOtest4 = eu.new Otest("马六", "maliu");
list2.add(tOtest3);
list2.add(tOtest4);
map.put("一组", list);
map.put("二组", list2);
excleOut2(map, "d:/ysy.xls");
}*/
}
2315
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
