注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

和申的个人主页

专注于java开发,1985wanggang

 
 
 

日志

 
 

Jxl的EXCEL报表导出Util  

2008-10-09 18:29:29|  分类: Java |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 /**工具类

*/

import java.io.IOException;
import java.io.OutputStream;
import java.util.Arrays;
import java.util.Collection;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
/**
 * @author wanggang
 * @version Oct 9, 2008
 */
public class ExportExlUtil {
 private WritableWorkbook workbook;
 private int sheetNum =1;
 
 public ExportExlUtil(HttpServletResponse response,String fileName){
  response.reset();
    response.setContentType("application/OCTET-STREAM;charset=gb2312");
    response.setHeader("pragma", "no-cache");
    response.addHeader("Content-Disposition", "attachment;filename=\""+fileName+".xls\"");//点击导出excle按钮时候页面显示的默认名称
  
  
  try {
   workbook = Workbook.createWorkbook(response.getOutputStream());
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
 public ExportExlUtil(OutputStream os){
  
  try {
   workbook = Workbook.createWorkbook(os);
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
 
 /**<p>
  * head Excel 上方标题
  * title 标题数组
  * dataList 与标题对应的数据</p>
  * */
 public void createExcelSheet(String head,String []title,List dataList){
  this.createExcelSheet(head, title, dataList, null);
 }
 
 /**<p>
  * title 标题数组
  * dataList 与标题对应的数据</p>
  * */
 public void createExcelSheet(String []title,List dataList){
  this.createExcelSheet(null, title, dataList, null);
 }
 /**<p>
  * head Excel 上方标题
  * title 标题数组
  * dataList 与标题对应的数据,其中个元素为每行的数据类型是Object[],或集合类型,如List
  * worksheet 输出的excel文件工作表名 * 不能重名</p>
  * */
 public void createExcelSheet(String head,String[] title,List  dataList, String worksheet){
  if(worksheet == null || worksheet.trim().equals("")){
   worksheet = "Sheet"+sheetNum;
  }
  WritableSheet sheet = workbook.createSheet(worksheet, sheetNum); // 添加一个工作表
  sheetNum++;
  int posRow =0;
  
  try {
   
   if(head != null && !head.trim().equals("")){//写Excel内的标题
    // sheet.mergeCells(int col1,int row1,int col2,int row2);//左上角到右下角 合并单元格
    sheet.mergeCells(0, 0, 10, 1);
    WritableFont wfc = new jxl.write.WritableFont(WritableFont.TIMES, 15,
      WritableFont.BOLD, false,
      jxl.format.UnderlineStyle.NO_UNDERLINE,
      jxl.format.Colour.BLACK);
    jxl.write.WritableCellFormat wchB = new jxl.write.WritableCellFormat(wfc);
    wchB.setAlignment(jxl.format.Alignment.CENTRE);
    Label labelCFC = new jxl.write.Label(0, 0, head, wchB);
    sheet.addCell(labelCFC); //
    posRow=3;
   }
   
   //添加标题行
   int i = 0;
   if(title !=null){
    for (; i < title.length; i++) {
     // 添加带有字体颜色,带背景颜色 Formatting的对象
     jxl.write.WritableFont wfc = new jxl.write.WritableFont(
       WritableFont.ARIAL, 10, WritableFont.BOLD, false,
       jxl.format.UnderlineStyle.NO_UNDERLINE,
       jxl.format.Colour.BLACK);
     jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
     wcfFC.setBackground(jxl.format.Colour.GRAY_25);
     jxl.write.Label labelCFC = new jxl.write.Label(i, posRow, title[i], wcfFC);
     sheet.addCell(labelCFC);
    }
    posRow +=1;
   }
   
   
   
   if(dataList !=null && dataList.size()!= 0){
    for(i = 0;i<dataList.size();i++){
     Object dataObj = dataList.get(i);
     if(dataObj.getClass().isArray()){
      Object[] dataArray = (Object[]) dataObj;
      for(int j=0;j < dataArray.length;j++){
       Object dataValue = dataArray[j];
       writeCell(sheet, dataValue, j, i+posRow);
      }
     }else if(dataObj instanceof Collection){
      Collection c = (Collection) dataObj;
      Object[] dataArray =c.toArray();
      for(int j=0;j < dataArray.length;j++){
       Object dataValue = dataArray[j];
       writeCell(sheet, dataValue, j, i+posRow);
      }
     }
     
    }
   }
   
   
   
   
  } catch (RowsExceededException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (WriteException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
 
 public void close(){
  try {
   workbook.write();
   workbook.close();
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (WriteException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
 public void writeCell(WritableSheet sheet, Object cellValue,int column,int row) throws RowsExceededException, WriteException{
  if(cellValue instanceof String){
   jxl.write.Label label;
   label = new jxl.write.Label(column, row , (String) cellValue); // put the title
   sheet.addCell(label);
  }else if(cellValue instanceof Number){//如果是数字
   
   if(cellValue instanceof Integer ){
    // 添加带有formatting的Number对象
    jxl.write.Number labelNF = new jxl.write.Number(column, row,  (Integer) cellValue);
    sheet.addCell(labelNF);
   }else if(cellValue instanceof Short){
    jxl.write.Number labelNF = new jxl.write.Number(column, row,  (Short) cellValue);
    sheet.addCell(labelNF);
   }else if(cellValue instanceof Double){
    // 添加带有formatting的Number对象
    jxl.write.NumberFormat nf =  new jxl.write.NumberFormat("0.00");;
    jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
    jxl.write.Number labelNF = new jxl.write.Number(column, row,(Double) cellValue, wcfN);
    sheet.addCell(labelNF);
   }else if(cellValue instanceof Long){
    jxl.write.Number labelNF = new jxl.write.Number(column, row,  (Long) cellValue);
    sheet.addCell(labelNF);
   }
   
   
  }else if(cellValue instanceof Date){
   // 添加带有formatting的DateFormat对象
   jxl.write.DateFormat df = new jxl.write.DateFormat( "yyyy-MM-dd hh:mm:ss");
   jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df);
   jxl.write.DateTime labelDTF = new jxl.write.DateTime(column, row, (Date) cellValue, wcfDF);
   
   sheet.addCell(labelDTF);
   
  }else if(cellValue instanceof Boolean){
   jxl.write.Boolean labelB = new jxl.write.Boolean(column, row, false);
   sheet.addCell(labelB);
  }
 }
}

 

 

 

 

 

/**
 * 测试类
 */
package jxltest.java;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

/**
 * @author wanggang
 * @version Oct 9, 2008
 */
public class ExportTest {

 /**
  * @param args
  * @throws FileNotFoundException
  */
 public static void main(String[] args) throws FileNotFoundException {
  // TODO Auto-generated method stub
  OutputStream os = new FileOutputStream("c:/export.xls");
  ExportExlUtil eeu = new ExportExlUtil(os);
  String[]title={"abc","bck","efd"};
  List<Object[]>dataList =new ArrayList();
  Object[] o={new Date(),"nihao",300,400,600};
  dataList.add(o);
//  eeu.createExcelSheet("您好! ", title, dataList, "abcsheet1");
//  eeu.createExcelSheet("您好! ", title, dataList, null);
//  eeu.createExcelSheet("您好!  ", title, null, "abcsheet3");
//  eeu.createExcelSheet("您好!  ", null, dataList, "abcsheet31");
  eeu.createExcelSheet(title, dataList);
  eeu.createExcelSheet(title, dataList);
  String[]title2 ={"abc","title2"};
  Object[] o2={"abcd","nihao",300,400,600};
  eeu.createExcelSheet(title2, dataList);
  eeu.createExcelSheet(title, dataList);
  eeu.close();
  System.out.println("abc");
 }

}

修改版2008.11.17=======================================================================

=====================================================================================

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * @author wanggang
 * @version Oct 9, 2008
 *
 * <pre>
 *  ExportExlUtil eeu = new ExportExlUtil(response,&quot;AdvertiserTotalAccount&quot;+new Date().getTime());
 *  eeu.createExcelSheet(title1, dataList1);
 *  //eeu.createExcelSheet(title2, dataList2);//可创建多个sheet
 *  eeu.close();
 * 
 * 
 * 
 *  *******************
  OutputStream os = new FileOutputStream("c:/export.xls");
  ExportExlUtil eeu = new ExportExlUtil(os);
  String[]title={"abc","bck","efd"};
  List<Object[]>dataList =new ArrayList();
  Object[] o={new Date(),"nihao",300,400,600};
  dataList.add(o);
//  eeu.createExcelSheet("您好! ", title, dataList, "abcsheet1");
//  eeu.createExcelSheet("您好! ", title, dataList, null);
//  eeu.createExcelSheet("您好!  ", title, null, "abcsheet3");
//  eeu.createExcelSheet("您好!  ", null, dataList, "abcsheet31");
  eeu.createExcelSheet(title, dataList);
  eeu.createExcelSheet(title, dataList);
  String[]title2 ={"title1","title2"};
  Object[] o2={"stringData1","stringData2",300,400,600};
  eeu.createExcelSheet(title2, dataList);
  eeu.createExcelSheet(title, dataList);
  eeu.close();
  
 *  </pre>
 * 
 *
 */
public class ExportExlUtil {
 private Log log = LogFactory.getLog(ExportExlUtil.class);

 private WritableWorkbook workbook;

 private int sheetNum = 1;

 private jxl.format.Colour titleBackGroundColor = jxl.format.Colour.GRAY_25;

 private jxl.write.WritableCellFormat titleFormat;

 private Map<Integer, jxl.write.WritableCellFormat> formatMap = new HashMap<Integer, jxl.write.WritableCellFormat>();

 public ExportExlUtil(HttpServletResponse response, String fileName)
   throws IOException {
  response.reset();
  response.setContentType("application/OCTET-STREAM;charset=gb2312");
  response.setHeader("pragma", "no-cache");
  response.addHeader("Content-Disposition", "attachment;filename=\""
    + fileName + ".xls\"");// 点击导出excle按钮时候页面显示的默认名称
  workbook = Workbook.createWorkbook(response.getOutputStream());
 }

 public ExportExlUtil(OutputStream os) {

  try {
   workbook = Workbook.createWorkbook(os);
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }

 /**
  * <p>
  * head Excel 上方标题 title 标题数组 dataList 与标题对应的数据
  * </p>
  */
 public void createExcelSheet(String head, String[] title, List dataList)
   throws IOException, WriteException {
  this.createExcelSheet(head, title, dataList, null);
 }

 /**
  * <p>
  * title 标题数组 dataList 与标题对应的数据
  * </p>
  */
 public void createExcelSheet(String[] title, List dataList)
   throws IOException, WriteException {
  this.createExcelSheet(null, title, dataList, null);
 }

 /**
  * <p>
  * head Excel 上方标题 title 标题数组 dataList
  * 与标题对应的数据,其中个元素为每行的数据类型是Object[],或集合类型,如List worksheet 输出的excel文件工作表名 *
  * 不能重名
  * </p>
  */
 public void createExcelSheet(String head, String[] title, List dataList,
   String worksheet) throws IOException, WriteException {
  if (worksheet == null || worksheet.trim().equals("")) {
   worksheet = "Sheet" + sheetNum;
  }
  WritableSheet sheet = workbook.createSheet(worksheet, sheetNum); // 添加一个工作表
  sheetNum++;
  int posRow = 0;
  try {
   if (head != null && !head.trim().equals("")) {// 写Excel内的标题
    // sheet.mergeCells(int col1,int row1,int col2,int
    // row2);//左上角到右下角 合并单元格
    sheet.mergeCells(0, 0, 10, 1);
    WritableFont wfc = new jxl.write.WritableFont(
      WritableFont.TIMES, 15, WritableFont.BOLD, false,
      jxl.format.UnderlineStyle.NO_UNDERLINE,
      jxl.format.Colour.BLACK);
    jxl.write.WritableCellFormat wchB = new jxl.write.WritableCellFormat(
      wfc);
    wchB.setAlignment(jxl.format.Alignment.CENTRE);
    Label labelCFC = new jxl.write.Label(0, 0, head, wchB);
    sheet.addCell(labelCFC); //
    posRow = 3;
   }
   // 添加标题行
   int i = 0;
   if (title != null) {
    if (titleFormat == null) {// 默认样式
     // 添加带有字体颜色,带背景颜色 Formatting的对象
     jxl.write.WritableFont wfc = new jxl.write.WritableFont(
       WritableFont.ARIAL, 10, WritableFont.BOLD, false,
       jxl.format.UnderlineStyle.NO_UNDERLINE,
       jxl.format.Colour.BLACK);
     titleFormat = new jxl.write.WritableCellFormat(wfc);
     titleFormat.setBackground(titleBackGroundColor);
    }
    for (; i < title.length; i++) {
     jxl.write.Label labelCFC = new jxl.write.Label(i, posRow,
       title[i], titleFormat);
     sheet.addCell(labelCFC);
    }
    posRow += 1;
   }
   if (dataList != null && dataList.size() != 0) {
    for (i = 0; i < dataList.size(); i++) {
     Object dataObj = dataList.get(i);
     if (dataObj instanceof List) {
      List rowList = (List) dataObj;
      Iterator it = rowList.iterator();
      for(int j=0;it.hasNext();j++){
       Object dataValue= it.next();
       writeCell(sheet, dataValue, j, i + posRow);
      }
      /*for (int j = 0; j < rowList.size(); j++) {
       Object dataValue = rowList.get(j);
       writeCell(sheet, dataValue, j, i + posRow);
      }*/
     } else if (dataObj.getClass().isArray()) {
      Object[] dataArray = (Object[]) dataObj;
      for (int j = 0; j < dataArray.length; j++) {
       Object dataValue = dataArray[j];
       writeCell(sheet, dataValue, j, i + posRow);
      }
     }
    }
   }
  } catch (ClassCastException cce) {
   log.error("createExcelSheet ExportExlUtil faild:", cce);
  } catch (IndexOutOfBoundsException ioobe) {
   log.error("createExcelSheet ExportExlUtil faild:", ioobe);
  } catch (NullPointerException npe) {
   log.error("createExcelSheet ExportExlUtil faild:", npe);
  } catch (RowsExceededException ree) {
   log.error("createExcelSheet ExportExlUtil faild:", ree);
  } catch (Exception e) {
   log.error("createExcelSheet ExportExlUtil faild:", e);
  }
  workbook.write();
  workbook.close();
 }

 /**
  * 关闭流,写完数据这后必须关闭
  */
 public void close() {
  try {
   workbook.write();
   workbook.close();
  } catch (IOException e) {
   log.error("close ExportExlUtil faild:"+e);
  } catch (WriteException e) {
   log.error("close ExportExlUtil faild:"+e);
  }
 }

 /**
  * 设置对应列的单元格显示样式
  */
 public void setColumnFormat(int column, jxl.write.WritableCellFormat format) {
  formatMap.put(column, format);
 }

 /**
  * 设置对应列的单元格显示样式
  */
 public void setColumnFormat(int column, jxl.biff.DisplayFormat displayFormat) {
  jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(
    displayFormat);
  formatMap.put(column, wcfN);
 }

 /**
  * 设置标题的格式
  */
 public void setTitleFormate(jxl.write.WritableCellFormat titleFormat) {
  this.titleFormat = titleFormat;
 }

 /**
  * 设置标题背景
  */
 public void setTitleBackGround(jxl.format.Colour colour) {
  this.titleBackGroundColor = colour;
 }

 /**
  * 返回给定的样式串的对应的NumberFormate用于数字的格式化样式
  */
 public static jxl.write.WritableCellFormat getNumberFormate(
   String formatString) {
  // 添加带有formatting的Number对象
  jxl.write.NumberFormat nf = new jxl.write.NumberFormat(formatString);
  jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
  return wcfN;
 }

 /**
  * 返回给定的样式的对应的NumberFormate用于数字的格式化样式 用法:可以使用jxl.write.NumberFormats的预制的值初始化
  */
 public static jxl.write.WritableCellFormat getNumberFormate(
   jxl.biff.DisplayFormat format) {
  jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(
    format);
  return wcfN;
 }

 /**
  * 返回给定字符串对应的DateFormate的日期格式化样式
  */
 public static jxl.write.WritableCellFormat getDateFormate(String format) {
  jxl.write.DateFormat df = new jxl.write.DateFormat(format);
  jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(
    df);
  return wcfDF;
 }

 private void writeCell(WritableSheet sheet, Object cellValue, int column,
   int row) throws RowsExceededException, WriteException {
  if (cellValue instanceof String) {
   jxl.write.Label label;
   if (formatMap.containsKey(column)) {
    label = new jxl.write.Label(column, row, (String) cellValue,
      formatMap.get(column)); // put the title
   } else {
    label = new jxl.write.Label(column, row, (String) cellValue);
   }

   sheet.addCell(label);
  } else if (cellValue instanceof Number) {// 如果是数字

   if (cellValue instanceof Integer) {
    // 添加带有formatting的Number对象
    jxl.write.Number labelNF;
    if (formatMap.containsKey(column)) {
     labelNF = new jxl.write.Number(column, row,
       (Integer) cellValue, formatMap.get(column));
    } else {
     labelNF = new jxl.write.Number(column, row,
       (Integer) cellValue);
    }

    sheet.addCell(labelNF);
   } else if (cellValue instanceof Short) {
    jxl.write.Number labelNF;
    if (formatMap.containsKey(column)) {
     labelNF = new jxl.write.Number(column, row,
       (Short) cellValue, formatMap.get(column));
    } else {
     labelNF = new jxl.write.Number(column, row,
       (Short) cellValue);
    }

    sheet.addCell(labelNF);
   } else if (cellValue instanceof Double) {
    jxl.write.Number labelNF;
    if (formatMap.containsKey(column)) {
     labelNF = new jxl.write.Number(column, row,
       (Double) cellValue, formatMap.get(column));
    } else {
     labelNF = new jxl.write.Number(column, row,
       (Double) cellValue);
    }

    sheet.addCell(labelNF);
   } else if (cellValue instanceof Long) {
    jxl.write.Number labelNF;
    if (formatMap.containsKey(column)) {
     labelNF = new jxl.write.Number(column, row,
       (Long) cellValue, formatMap.get(column));
    } else {
     labelNF = new jxl.write.Number(column, row,
       (Long) cellValue);
    }

    sheet.addCell(labelNF);
   } else if (cellValue instanceof Float) {
    jxl.write.Number labelNF;
    if (formatMap.containsKey(column)) {
     labelNF = new jxl.write.Number(column, row,
       (Float) cellValue, formatMap.get(column));
    } else {
     labelNF = new jxl.write.Number(column, row,
       (Float) cellValue);
    }

    sheet.addCell(labelNF);
   }

  } else if (cellValue instanceof Date) {
   // 添加带有formatting的DateFormat对象
   jxl.write.DateFormat df = new jxl.write.DateFormat(
     "yyyy-MM-dd hh:mm:ss");
   jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(
     df);
   jxl.write.DateTime labelDTF;
   if (formatMap.containsKey(column)) {
    labelDTF = new jxl.write.DateTime(column, row,
      (Date) cellValue, formatMap.get(column));
   } else {
    labelDTF = new jxl.write.DateTime(column, row,
      (Date) cellValue, wcfDF);
   }

   sheet.addCell(labelDTF);

  } else if (cellValue instanceof Boolean) {
   jxl.write.Boolean labelB;
   if (formatMap.containsKey(column)) {
    labelB = new jxl.write.Boolean(column, row,
      (Boolean) cellValue, formatMap.get(column));
   } else {
    labelB = new jxl.write.Boolean(column, row, (Boolean) cellValue);
   }

   sheet.addCell(labelB);
  }
 }
}
/****************************************

测试文件

*************************************************/

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import jxl.write.NumberFormats;
import jxl.write.WriteException;

 

/**
 * @author wanggang
 * @version Oct 9, 2008
 */
public class ExportTest {

 /**
  * @param args
  * @throws FileNotFoundException
  */
 public static void main(String[] args) throws FileNotFoundException {
 
  
  String[] title = { "title1", "title2", "title3", "title4", "title5", "title6"};
  List<List> dataList = new ArrayList<List>();
 
  
  List<Object> row = new ArrayList<Object>();
   
   row.add(data1);
   row.add(data2);
   row.add(data3);
   row.add(data4);
   
   dataList.add(row);
  
  
  
  Object [] row2 = {null,null,null,null,null,value1,value2};
  
  dataList.add(row2);
  try {
   ExportExlUtil exportExlUtil = new ExportExlUtil(response, "fileName");
   exportExlUtil.setColumnFormat(0, ExportExlUtil.getDateFormate("yyyy-MM-dd"));//设置样式
   exportExlUtil.setColumnFormat(4, NumberFormats.ACCOUNTING_RED_FLOAT);
   exportExlUtil.setColumnFormat(5, NumberFormats.ACCOUNTING_RED_FLOAT);
   exportExlUtil.setColumnFormat(6, NumberFormats.ACCOUNTING_RED_FLOAT);
   exportExlUtil.setColumnFormat(7, NumberFormats.ACCOUNTING_RED_FLOAT);
   exportExlUtil.setColumnFormat(8, NumberFormats.ACCOUNTING_RED_FLOAT);
   exportExlUtil.createExcelSheet(title, dataList);
  }catch(IOException ioe){
   
  } catch (WriteException we) {
   
  }

 }

}
参见:jxl写Excel文件

  评论这张
 
阅读(974)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016