Poi实现Excel导出工具类封装.docx
《Poi实现Excel导出工具类封装.docx》由会员分享,可在线阅读,更多相关《Poi实现Excel导出工具类封装.docx(13页珍藏版)》请在冰豆网上搜索。
Poi实现Excel导出工具类封装
Poi实现Excel导出工具类封装
工具类代码PoiExcelExport如下:
packagecom.myssm.util.poi;
importjava.io.File;
importjava.io.FileOutputStream;
importjava.io.IOException;
importjava.io.OutputStream;
importjava.lang.reflect.Method;
import.URLEncoder;
importjava.text.DecimalFormat;
importjava.util.List;
importjavax.servlet.http.HttpServletResponse;
importorg.apache.poi.hssf.usermodel.HSSFCellStyle;
importorg.apache.poi.hssf.usermodel.HSSFFont;
importorg.apache.poi.hssf.usermodel.HSSFPalette;
importorg.apache.poi.hssf.usermodel.HSSFWorkbook;
importorg.apache.poi.ss.usermodel.Cell;
importorg.apache.poi.ss.usermodel.CellStyle;
importorg.apache.poi.ss.usermodel.Row;
importorg.apache.poi.ss.usermodel.Sheet;
importorg.apache.poi.ss.util.CellRangeAddress;
publicclassPoiExcelExport{
HttpServletResponseresponse;
//文件名
privateStringfileName;
//文件保存路径
privateStringfileDir;
//sheet名
privateStringsheetName;
//表头字体
privateStringtitleFontType="ArialUnicodeMS";
//表头背景色
privateStringtitleBackColor="C1FBEE";
//表头字号
privateshorttitleFontSize=12;
//添加自动筛选的列如A:
M
privateStringaddress="";
//正文字体
privateStringcontentFontType="ArialUnicodeMS";
//正文字号
privateshortcontentFontSize=12;
//Float类型数据小数位
privateStringfloatDecimal=".00";
//Double类型数据小数位
privateStringdoubleDecimal=".00";
//设置列的公式
privateStringcolFormula[]=null;
DecimalFormatfloatDecimalFormat=newDecimalFormat(floatDecimal);
DecimalFormatdoubleDecimalFormat=newDecimalFormat(doubleDecimal);
privateHSSFWorkbookworkbook=null;
publicPoiExcelExport(StringfileDir,StringsheetName){
this.fileDir=fileDir;
this.sheetName=sheetName;
workbook=newHSSFWorkbook();
}
publicPoiExcelExport(HttpServletResponseresponse,StringfileName,StringsheetName){
this.response=response;
this.sheetName=sheetName;
workbook=newHSSFWorkbook();
}
/**
*设置表头字体.
*@paramtitleFontType
*/
publicvoidsetTitleFontType(StringtitleFontType){
this.titleFontType=titleFontType;
}
/**
*设置表头背景色.
*@paramtitleBackColor十六进制
*/
publicvoidsetTitleBackColor(StringtitleBackColor){
this.titleBackColor=titleBackColor;
}
/**
*设置表头字体大小.
*@paramtitleFontSize
*/
publicvoidsetTitleFontSize(shorttitleFontSize){
this.titleFontSize=titleFontSize;
}
/**
*设置表头自动筛选栏位,如A:
AC.
*@paramaddress
*/
publicvoidsetAddress(Stringaddress){
this.address=address;
}
/**
*设置正文字体.
*@paramcontentFontType
*/
publicvoidsetContentFontType(StringcontentFontType){
this.contentFontType=contentFontType;
}
/**
*设置正文字号.
*@paramcontentFontSize
*/
publicvoidsetContentFontSize(shortcontentFontSize){
this.contentFontSize=contentFontSize;
}
/**
*设置float类型数据小数位默认.00
*@paramdoubleDecimal如".00"
*/
publicvoidsetDoubleDecimal(StringdoubleDecimal){
this.doubleDecimal=doubleDecimal;
}
/**
*设置doubel类型数据小数位默认.00
*@paramfloatDecimalFormat如".00
*/
publicvoidsetFloatDecimalFormat(DecimalFormatfloatDecimalFormat){
this.floatDecimalFormat=floatDecimalFormat;
}
/**
*设置列的公式
*@paramcolFormula存储i-1列的公式涉及到的行号使用@替换如A@+B@
*/
publicvoidsetColFormula(String[]colFormula){
this.colFormula=colFormula;
}
/**
*写excel.
*@paramtitleColumn对应bean的属性名
*@paramtitleNameexcel要导出的表名
*@paramtitleSize列宽
*@paramdataList数据
*/
publicvoidwirteExcel(StringtitleColumn[],StringtitleName[],inttitleSize[],List
>dataList){
//添加Worksheet(不添加sheet时生成的xls文件打开时会报错)
Sheetsheet=workbook.createSheet(this.sheetName);
//新建文件
OutputStreamout=null;
try{
if(fileDir!
=null){
//有文件路径
out=newFileOutputStream(fileDir);
}else{
//否则,直接写到输出流中
out=response.getOutputStream();
fileName=fileName+".xls";
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition","attachment;filename="
+URLEncoder.encode(fileName,"UTF-8"));
}
//写入excel的表头
RowtitleNameRow=workbook.getSheet(sheetName).createRow(0);
//设置样式
HSSFCellStyletitleStyle=workbook.createCellStyle();
titleStyle=(HSSFCellStyle)setFontAndBorder(titleStyle,titleFontType,(short)titleFontSize);
titleStyle=(HSSFCellStyle)setColor(titleStyle,titleBackColor,(short)10);
for(inti=0;isheet.setColumnWidth(i,titleSize[i]*256);//设置宽度
Cellcell=titleNameRow.createCell(i);
cell.setCellStyle(titleStyle);
cell.setCellValue(titleName[i].toString());
}
//为表头添加自动筛选
if(!
"".equals(address)){
CellRangeAddressc=(CellRangeAddress)CellRangeAddress.valueOf(address);
sheet.setAutoFilter(c);
}
//通过反射获取数据并写入到excel中
if(dataList!
=null&&dataList.size()>0){
//设置样式
HSSFCellStyledataStyle=workbook.createCellStyle();
titleStyle=(HSSFCellStyle)setFontAndBorder(titleStyle,contentFontType,(short)contentFontSize);
if(titleColumn.length>0){
for(introwIndex=1;rowIndex<=dataList.size();rowIndex++){
Objectobj=dataList.get(rowIndex-1);//获得该对象
Classclsss=obj.getClass();//获得该对对象的class实例
RowdataRow=workbook.getSheet(sheetName).createRow(rowIndex);
for(intcolumnIndex=0;columnIndexStringtitle=titleColumn[columnIndex].toString().trim();
if(!
"".equals(title)){//字段不为空
//使首字母大写
StringUTitle=Character.toUpperCase(title.charAt(0))+title.substring(1,title.length());//使其首字母大写;
StringmethodName="get"+UTitle;
//设置要执行的方法
Methodmethod=clsss.getDeclaredMethod(methodName);
//获取返回类型
StringreturnType=method.getReturnType().getName();
Stringdata=method.invoke(obj)==null?
"":
method.invoke(obj).toString();
Cellcell=dataRow.createCell(columnIndex);
if(data!
=null&&!
"".equals(data)){
if("int".equals(returnType)){
cell.setCellValue(Integer.parseInt(data));
}elseif("long".equals(returnType)){
cell.setCellValue(Long.parseLong(data));
}elseif("float".equals(returnType)){
cell.setCellValue(floatDecimalFormat.format(Float.parseFloat(data)));
}elseif("double".equals(returnType)){
cell.setCellValue(doubleDecimalFormat.format(Double.parseDouble(data)));
}else{
cell.setCellValue(data);
}
}
}else{//字段为空检查该列是否是公式
if(colFormula!
=null){
StringsixBuf=colFormula[columnIndex].replace("@",(rowIndex+1)+"");
Cellcell=dataRow.createCell(columnIndex);
cell.setCellFormula(sixBuf.toString());
}
}
}
}
}
}
workbook.write(out);
}catch(Exceptione){
e.printStackTrace();
}finally{
try{
out.close();
}catch(IOExceptione){
e.printStackTrace();
}
}
}
/**
*将16进制的颜色代码写入样式中来设置颜色
*@paramstyle保证style统一
*@paramcolor颜色:
66FFDD
*@paramindex索引8-64使用时不可重复
*@return
*/
publicCellStylesetColor(CellStylestyle,Stringcolor,shortindex){
if(color!
=""&&color!
=null){
//转为RGB码
intr=Integer.parseInt((color.substring(0,2)),16);//转为16进制
intg=Integer.parseInt((color.substring(2,4)),16);
intb=Integer.parseInt((color.substring(4,6)),16);
//自定义cell颜色
HSSFPalettepalette=workbook.getCustomPalette();
palette.setColorAtIndex((short)index,(byte)r,(byte)g,(byte)b);
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(index);
}
returnstyle;
}
/**
*设置字体并加外边框
*@paramstyle样式
*@paramstyle字体名
*@paramstyle大小
*@return
*/
publicCellStylesetFontAndBorder(CellStylestyle,StringfontName,shortsize){
HSSFFontfont=workbook.createFont();
font.setFontHeightInPoints(size);
font.setFontName(fontName);
font.setBold(true);
style.setFont(font);
style.setBorderBottom(CellStyle.BORDER_THIN);//下边框
style.setBorderLeft(CellStyle.BORDER_THIN);//左边框
style.setBorderTop(CellStyle.BORDER_THIN);//上边框
style.setBorderRight(CellStyle.BORDER_THIN);//右边框
returnstyle;
}
/**
*删除文件
*@paramfileDir
*@return
*/
publicbooleandeleteExcel(){
booleanflag=false;
Filefile=newFile(this.fileDir);
//判断目录或文件是否存在
if(!
file.exists()){//不存在返回false
returnflag;
}else{
//判断是否为文件
if(file.isFile()){//为文件时调用删除文件方法
file.delete();
flag=true;
}
}
returnflag;
}
/**
*删除文件
*@paramfileDir
*@return
*/
publicbooleandeleteExcel(Stringpath){
booleanflag=false;
Filefile=newFile(path);
//判断目录或文件是否存在
if(!
file.exists()){//不存在返回false
returnflag;
}else{
//判断是否为文件
if(file.isFile()){//为文件时调用删除文件方法
file.delete();
flag=true;
}
}
returnflag;
}
}
测试如下:
实体bean:
packagecom.myssm.util.poi;
publicclassMan{
privateStringname;
privateintsex;
privateStringidCard;
privatefloatsalary;
publicMan(Stringname,intsex,StringidCard,floatsalary){
super();
this.name=name;
this.sex=sex;
this.idCard=idCard;
this.salary=salary;
}
publicMan(){
super();
}
publicStringgetName(){
returnname;
}
publicvoidsetName(Stringname){
this.name=name;
}
publicintgetSex(){
returnsex;
}
publicvoidsetSex(intsex){
this.sex=sex;
}
publicStringgetIdCard(){
returnidCard;
}
publicvoidsetIdCard(StringidCard){
this.idCard=idCard;
}
publicfloatgetSalary(){
returnsalary;
}
publicvoidsetSalary(floatsalary){
this.salary=salary;
}
}测试类:
packagecom.myssm.util.poi;
importjava.util.ArrayList;
importjava.util.List;
publicclassTest{
publicstaticvoidmain(String[]args){
PoiExcelExportpee=newPoiExcelExport("E:
/test.xls","sheet1");
//数据
ListdataList=newArrayList();
Manman1=newMan("张三",20,"男",(float)10000.8);
Manman2=newMan("李四",21,"男",(float)11000.8);
Manman3=newMan("王五",22,"女",(float)1200.8);
Manman4=newMan("赵六",23,"男",(float)13000.8);
Manman5=newMan("田七",24,"男",(float)14000.8);
Manman6=newMan();
man6.setName("老八");
data