java导出excel表格篇(一):java导出Excel通用方法
Java导出Excel通用方法，只需要一个list 集合。通用方法改进之处踊跃提出
[java] view plain copy
package oa.common.utils;
import java.io.OutputStream;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts2.ServletActionContext;
import java.lang.reflect.Field;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
/***
* @author lsf
*/
public class ExportExcel {
/***************************************************************************
* @param fileName EXCEL文件名称
* @param listTitle EXCEL文件第一行列标题集合
* @param listContent EXCEL文件正文数据集合
* @return
*/
public final static String exportExcel(String fileName,String[] Title, List<Object> listContent) {
String result="系统提示：Excel文件导出成功！";
// 以下开始输出到EXCEL
try {
//定义输出流，以便打开保存对话框______________________begin
HttpServletResponse response=ServletActionContext.getResponse();
OutputStream os = response.getOutputStream();// 取得输出流
response.reset();// 清空输出流
response.setHeader("Content-disposition", "attachment; filename="+ new String(fileName.getBytes("GB2312"),"ISO8859-1"));
// 设定输出文件头
response.setContentType("application/msexcel");// 定义输出类型
//定义输出流，以便打开保存对话框_______________________end
/** **********创建工作簿************ */
WritableWorkbook workbook = Workbook.createWorkbook(os);
/** **********创建工作表************ */
WritableSheet sheet = workbook.createSheet("Sheet1", 0);
/** **********设置纵横打印（默认为纵打）、打印纸***************** */
jxl.SheetSettings sheetset = sheet.getSettings();
sheetset.setProtected(false);
/** ************设置单元格字体************** */
WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD);
/** ************以下设置三种单元格样式，灵活备用************ */
// 用于标题居中
WritableCellFormat wcf_center = new WritableCellFormat(BoldFont);
wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcf_center.setAlignment(Alignment.CENTRE); // 文字水平对齐
wcf_center.setWrap(false); // 文字是否换行
// 用于正文居左
WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);
wcf_left.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条
wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcf_left.setAlignment(Alignment.LEFT); // 文字水平对齐
wcf_left.setWrap(false); // 文字是否换行
/** ***************以下是EXCEL开头大标题，暂时省略********************* */
//sheet.mergeCells(0, 0, colWidth, 0);
//sheet.addCell(new Label(0, 0, "XX报表", wcf_center));
/** ***************以下是EXCEL第一行列标题********************* */
for (int i = 0; i < Title.length; i++) {
sheet.addCell(new Label(i, 0,Title[i],wcf_center));
}
/** ***************以下是EXCEL正文数据********************* */
Field[] fields=null;
int i=1;
for(Object obj:listContent){
fields=obj.getClass().getDeclaredFields();
int j=0;
for(Field v:fields){
v.setAccessible(true);
Object va=v.get(obj);
if(va==null){
va="";
}
sheet.addCell(new Label(j, i,va.toString(),wcf_left));
j++;
}
i++;
}
/** **********将以上缓存中的内容写到EXCEL文件中******** */
workbook.write();
/** *********关闭文件************* */
workbook.close();
} catch (Exception e) {
result="系统提示：Excel文件导出失败，原因："+ e.toString();
System.out.println(result);
e.printStackTrace();
}
return result;
}
}
测试：
[java] view plain copy
/**
* 导出excel
* @return
*/
public String excelPage(){
ExportExcel excel=new ExportExcel();
String str="";
try {
str = new String(getHTTP.getRequest().getParameter("wineOrg.orgName").getBytes("iso8859-1"),"UTF-8");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
wineOrg.setOrgName(str);
List<Object> li=service.exportExcel(wineOrg);
String[] Title={"机构ID","会员编号","类别","名称","省ID","省名称","城市ID","城市名称","详细地址","联系人","性别","联系手机","联系电话","传真","邮箱","QQ","生日","积分","客户等级","现金账户余额","结算方式","客户类型","购买次数","购买支数","创建人ID","创建人姓名","create_time","del","STS","备注","负责人ID","负责人姓名","审核标识","审核人ID ","审核人姓名","审核日期","分配人ID","分配人姓名","分配日期","修改人ID","修改人姓名 ","修改时间"};
excel.exportExcel("客户资料信息.xls",Title, li);
return SUCCESS;
}
java导出excel表格篇(二):java POI导出Excel报表
package com.venustech.custom.utils;
import java.io.BufferedInputStream;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.OutputStream;import java.lang.reflect.Field;import java.lang.reflect.Method;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Collection;import java.util.Date;import java.util.Iterator;import java.util.List;import java.util.regex.Matcher;import java.util.regex.Pattern;
import javax.swing.JOptionPane;
import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFClientAnchor;import org.apache.poi.hssf.usermodel.HSSFComment;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFPatriarch;import org.apache.poi.hssf.usermodel.HSSFRichTextString;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;
import com.venustech.custom.bean.Book;import com.venustech.custom.bean.Student;
public class ExportExcel<T> { public void exportExcel(Collection<T> dataset,OutputStream out){ exportExcel("测试POI导出excel文档",null,dataset,out,"yyyy-MM-dd"); }
public void exportExcel(String[] headers,Collection<T> dataset, OutputStream out) { exportExcel("测试POI导出excel文档",headers,dataset,out,"yyyy-MM-dd"); } public void exportExcel(String[] headers,Collection<T> dataset,OutputStream out,String pattern){ exportExcel("测试POI导出excel文档",headers,dataset,out,"yyyy-MM-dd"); } @SuppressWarnings("unchecked") public void exportExcel(String title,String[] headers,Collection<T> dataset,OutputStream out,String pattern){ //声明一个工作薄 HSSFWorkbook workbook=new HSSFWorkbook(); //生成一个表格 HSSFSheet sheet=workbook.createSheet(title); //设置表格默认宽度为15个字节 sheet.setDefaultColumnWidth(15); //生成一个样式 HSSFCellStyle style=workbook.createCellStyle(); //设置这些样式 style.setFillForegroundColor(HSSFColor.SKY_BLUE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //生成一个字体 HSSFFont font =workbook.createFont(); font.setColor(HSSFColor.VIOLET.index); font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //把字体应用到当前的样式 style.setFont(font); //生成并设置另一个样式 HSSFCellStyle style_ =workbook.createCellStyle(); style_.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); style_.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style_.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_.setBorderLeft(HSSFCellStyle.BORDER_THIN); style_.setBorderRight(HSSFCellStyle.BORDER_THIN); style_.setBorderTop(HSSFCellStyle.BORDER_THIN); style_.setAlignment(HSSFCellStyle.ALIGN_CENTER); style_.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //生成另一个字体 HSSFFont font_=workbook.createFont(); //font.setColor(color); //font.setFontHeightInPoints(height); font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); style_.setFont(font_); //生成一个画图的顶级管理器 HSSFPatriarch patriarch=sheet.createDrawingPatriarch(); //定义注释的大小和位置 HSSFComment comment=patriarch.createComment(new HSSFClientAnchor(0,0,0,0,(short)4,2,(short)6,5)); //设置注释内容 comment.setString(new HSSFRichTextString("可以在POI中添加注释！")); //设置注释作者，当鼠标移到单元格上可以在状态栏中看到该内容 comment.setAuthor("leno"); //产生表格标题行 HSSFRow row=sheet.createRow(0); for(int i=0;i<headers.length;i++){ HSSFCell cell=row.createCell(i); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString(headers[i])); } //遍历集合数据，产生数据行 Iterator<T> it=dataset.iterator(); int index=0; while(it.hasNext()){ index++; row=sheet.createRow(index); T t=(T)it.next(); //利用反射，根据javabean属性的先后顺序，动态调用getXxx（）方法得到属性值 Field[] fields=t.getClass().getDeclaredFields(); for(int i=0;i<fields.length;i++){ HSSFCell cell=row.createCell(i); cell.setCellStyle(style_); Field field=fields[i]; String fieldName=field.getName(); String getMethodName="get"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1); try{ Class tCls=t.getClass(); Method getMethod=tCls.getMethod(getMethodName,new Class[]{}); Object value=getMethod.invoke(t,new Object[]{}); //判断值的类型后进行强制类型转换 String textValue=null; if(value instanceof Boolean){ boolean bValue=(Boolean) value; textValue="男"; if(!bValue){ textValue="女"; } }else if (value instanceof Date) { Date date=(Date)value; SimpleDateFormat sdf=new SimpleDateFormat(pattern); textValue=sdf.format(date); }else if (value instanceof byte[]) { //有图片时，设置行高为60px row.setHeightInPoints(60); //设置图片所在列宽度为80px，注意这里单位的一个 sheet.setColumnWidth(i,(short)(35.7*80)); byte[] bsValue=(byte[])value; HSSFClientAnchor anchor=new HSSFClientAnchor(0,0,1023,255,(short)6, index,(short)6,index); anchor.setAnchorType(2); patriarch.createPicture(anchor,workbook.addPicture(bsValue,HSSFWorkbook.PICTURE_TYPE_JPEG)); }else{ //其他数据类型都当作字符串简单处理 textValue=value.toString(); } //如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成 if(textValue!=null){ Pattern p=Pattern.compile("^\\d+(\\.\\d+)?$"); Matcher matcher = p.matcher(textValue); if(matcher.matches()){ //是数字当作double处理 cell.setCellValue(Double.parseDouble(textValue)); }else{ HSSFRichTextString richString=new HSSFRichTextString(textValue); HSSFFont _font=workbook.createFont(); _font.setColor(HSSFColor.BLUE.index); richString.applyFont(_font); cell.setCellValue(richString); } } }catch(Exception e){ e.printStackTrace(); } } } try{ workbook.write(out); }catch(Exception e){ e.printStackTrace(); } } /** * @param args */ public static void main(String[] args) { ExportExcel<Student> ex=new ExportExcel<Student>(); String[] headers={"学号","姓名","年龄","性别","出生日期"}; List<Student> dataset=new ArrayList<Student>(); dataset.add(new Student(10000001L,"张三",20, true,new Date())); dataset.add(new Student(10000002L,"李四",21, false,new Date())); dataset.add(new Student(10000003L,"王五",23, true,new Date())); ExportExcel<Book> ex_book=new ExportExcel<Book>(); String[] headers_book={"图书编号","图书名称","图书作者","图书价格","图书ISBN","图书出版社","封面图片"}; List<Book> dataset_book=new ArrayList<Book>(); try{ BufferedInputStream bis=new BufferedInputStream(new FileInputStream("book.jpg")); byte[] buf=new byte[bis.available()]; while(bis.read(buf)!=-1){ } dataset_book.add(new Book(1,"jsp","leno",300.33f,"1234567","清华出版社",buf)); dataset_book.add(new Book(2,"java 编程思想","leno",300.33f,"1234567","清华出版社",buf)); dataset_book.add(new Book(3,"C++","leno",300.33f,"1234567","清华出版社",buf)); dataset_book.add(new Book(4,"C#入门 编程思想","leno",300.33f,"1234567","清华出版社",buf)); dataset_book.add(new Book(5,"Flex3.0入门","leno",300.33f,"1234567","清华出版社",buf)); dataset_book.add(new Book(6,"java与模式","leno",300.33f,"1234567","清华出版社",buf)); OutputStream out=new FileOutputStream("c:\\a.xls"); OutputStream out2=new FileOutputStream("c:\\b.xls"); ex.exportExcel(headers,dataset, out); ex_book.exportExcel(headers_book, dataset_book, out2); out.close(); JOptionPane.showMessageDialog(null, "导出成功！"); System.out.println("xls导出成功！"); }catch(Exception e){ e.printStackTrace(); } }
}
package com.venustech.custom.servlet;
import java.io.BufferedInputStream;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.OutputStream;import java.util.ArrayList;import java.util.List;
import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;
import com.venustech.custom.bean.Book;import com.venustech.custom.utils.ExportExcel;
public class ExportExcelServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doPost(request, response); }
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { File file=new File(getServletContext().getRealPath(("WEB-INF/book.jpg"))); response.setContentType("octets/stream"); response.addHeader("Content-Disposition", "attachment;filename=test.xls"); ExportExcel<Book> export_book=new ExportExcel<Book>(); String[] headers_book={"图书编号","图书名称","图书作者","图书价格","图书ISBN","图书出版社","封面图片"}; List<Book> dataset_book=new ArrayList<Book>(); try{ BufferedInputStream bis=new BufferedInputStream(new FileInputStream(file)); byte[] buf=new byte[bis.available()]; while(bis.read(buf)!=-1){ //将图片数据存放到缓冲数组中 } dataset_book.add(new Book(1,"jsp","leno",300.33f,"1234567","清华出版社",buf)); dataset_book.add(new Book(2,"java 编程思想","leno",300.33f,"1234567","清华出版社",buf)); dataset_book.add(new Book(3,"C++","leno",300.33f,"1234567","清华出版社",buf)); dataset_book.add(new Book(4,"C#入门 编程思想","leno",300.33f,"1234567","清华出版社",buf)); dataset_book.add(new Book(5,"Flex3.0入门","leno",300.33f,"1234567","清华出版社",buf)); dataset_book.add(new Book(6,"java与模式","leno",300.33f,"1234567","清华出版社",buf)); OutputStream out=response.getOutputStream(); export_book.exportExcel(headers_book, dataset_book, out); out.close(); System.out.println("excel导出成功！"); }catch(Exception e){ e.printStackTrace(); } }
}
java导出excel表格篇(三):Java实现Excel的导入和导出
一、下载jxl.jar包导入到工程中
http://download.csdn.net/detail/lrici/9758600
二、新建Book.java
[java] view plain copy print?
package com.cc.reflection;
public class Book {
private int id;
private String name;
private String type;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
}
三、新建ExcelBook
[java] view plain copy print?
package com.cc.reflection;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
public class ExcelBook {
//将数据导出到Excel
public void excelOut(ArrayList<Book> arrayList) {
WritableWorkbook bWorkbook = null;
try {
// 创建Excel对象
bWorkbook = Workbook.createWorkbook(new File("D:/book.xls"));
// 通过Excel对象创建一个选项卡对象
WritableSheet sheet = bWorkbook.createSheet("sheet1", 0);
//使用循环将数据读出
for (int i = 0; i < arrayList.size(); i++) {
Book book=arrayList.get(i);
Label label=new Label(0,i,String.valueOf(book.getId()));
Label label1=new Label(1,i,String.valueOf(book.getName()));
Label label2=new Label(2,i,String.valueOf(book.getType()));
sheet.addCell(label);
sheet.addCell(label1);
sheet.addCell(label2);
}
// 创建一个单元格对象，第一个为列，第二个为行，第三个为值
Label label = new Label(0, 2, "test");
// 将创建好的单元格放入选项卡中
//sheet.addCell(label);
// 写如目标路径
bWorkbook.write();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
bWorkbook.close();
} catch (WriteException | IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//将Excel中的数据导入
public ArrayList<Book> ExcelIn(){
ArrayList<Book>arrayList=new ArrayList<Book>();
Workbook bWorkbook=null;
try {
bWorkbook=Workbook.getWorkbook(new File("D:/book.xls"));
Sheet sheet=bWorkbook.getSheet(0);
for (int i = 0; i < sheet.getRows(); i++) {
Book book=new Book();
//获取单元格对象
Cell cell =sheet.getCell(0,i);
//获取单元格的值
book.setId(Integer.valueOf(cell.getContents()));
book.setName(sheet.getCell(1,i).getContents());
book.setType(sheet.getCell(2, i).getContents() );
arrayList.add(book);
}
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
bWorkbook.close();
}
return arrayList;
}
public static void main(String[] args) {
//将数据导出到Excel中
ExcelBook book = new ExcelBook();
// ArrayList<Book> arrayList = new ArrayList<Book>();
// Book bo = new Book();
// bo.setId(1);
// bo.setName("酒馆");
// bo.setType("生活");
//
// Book bo1 = new Book();
// bo1.setId(2);
// bo1.setName("酒馆1");
// bo1.setType("生活1");
//
// arrayList.add(bo);
// arrayList.add(bo1);
// book.excelOut(arrayList);
//将数据从Excel中导入
ArrayList<Book> arrayList1 = book.ExcelIn();
for(Book bo2:arrayList1){
System.out.println(bo2.getName()+bo2.getType());
}
}
}
点击运行
https://m.shanpow.com/dl/496138/
推荐访问:javaweb导出excel表格
