java poi 实现Excel的批量导出

第一步：客户端的脚本

<li>

<input type="button" onclick="expCustomerFile()" value="Excel导出"

class="btn"></input>

</li>

function expCustomerFile(){

   window.location.href="expConsumerFileInfo.action"; 

}

第二步：struts。xml配置文件

 <!-- 批量导出客户档案信息 -->

  //此处配置跟前面提到excel的模板下载配置文件类似

  <action name="expConsumerFileInfo"

class="com.daja.paymp.presentation.action.consumer.ConsumerAction" method="expConsumerFile">

  <result name="success" type="stream"> 

  <param name="contentType">application/vnd.ms-excel</param> 

 <param name="contentDisposition">attachment;filename="${excelName}"</param> 

 <param name="inputName">excelFile</param>

 </result>

</action>

第三步:业务逻辑类的编写

//生成对应的set，get方法

private InputStream inputStream;

public String expConsumerFile()throws Exception{

String customerFile = "客户档案信息.xls";

excelName = new String(customerFile.getBytes(), "iso8859-1");//解决中文 文件名问题 

return SUCCESS;

}

 public InputStream getExcelFile() throws Exception{

 

String[] headers = { "客户编号", "客户名称", "地址", "证件号", "电话", "开户日期","邮箱" };

String title ="客户资料信息";

consumerList = new ArrayList<CustomerFile>();

//该方法是查询出所有的客户信息列表集合

consumerList = consumerService.selectByCustomerFile();

                //CurrentExcelTemplete为封装静态类

       inputStream = CurrentExcelTemplete.expExcel(title, headers, consumerList); 

       return inputStream;

 }

//批量导出的封装类 CurrentExcelTemplete

 1.工作薄的创建以及样式的封装

public  static Map<String,Object> currentExcel(String title) throws Exception{

// 声明一个工作薄

workbook = new HSSFWorkbook();

// 生成一个表格

sheet = workbook.createSheet(title);

// 设置表格默认列宽度为15个字节

sheet.setDefaultColumnWidth(15);

// 生成一个样式

      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);

// 生成一个字体

font = workbook.createFont();

font.setColor(HSSFColor.VIOLET.index);

font.setFontHeightInPoints((short) 12);

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

// 把字体应用到当前的样式

style.setFont(font);

Map<String,Object> map  = new HashMap<String,Object>();

map.put("workbook",workbook);

map.put("sheet", sheet);

map.put("style", style);

map.put("font",font);

return map;

}

2.批量导出方法的封装

public static InputStream expExcel(String title, String[] headers,

List<CustomerFile> dataset) throws Exception {

  Map<String,Object> impMap = new HashMap<String,Object>();

  impMap = CurrentExcelTemplete.currentExcel(title);

  workbook =(HSSFWorkbook)impMap.get("workbook");

  sheet = (HSSFSheet)impMap.get("sheet");

  style = (HSSFCellStyle)impMap.get("style");

  font =(HSSFFont) impMap.get("font");

//产生表格标题行

HSSFRow row = sheet.createRow(0);

for (short i = 0; i < headers.length; i++) {

HSSFCell cell = row.createCell(i);

cell.setCellStyle(style);

HSSFRichTextString text = new HSSFRichTextString(headers[i]);

cell.setCellValue(text);

}

//遍历集合数据，产生数据行

int index = 0;

HSSFCell cell = null;

for (int i = 0; i < dataset.size(); i++) {

index++;

row = sheet.createRow((short) index);

cell = row.createCell(0);

cell.setCellValue(dataset.get(i).getFileNo());

cell = row.createCell(1);

cell.setCellValue(dataset.get(i).getName());

cell = row.createCell(2);

cell.setCellValue(dataset.get(i).getAddress());

cell = row.createCell(3);

cell.setCellValue(dataset.get(i).getIdCard());

cell = row.createCell(4);

cell.setCellValue(dataset.get(i).getPhone());

cell = row.createCell(5);

String createdata = null;

Date data = (Date) dataset.get(i).getCreatedDate();

SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

createdata = format.format(data);

cell.setCellValue(createdata);

cell = row.createCell(6);

cell.setCellValue(dataset.get(i).getEmail());

}

 

 ByteArrayOutputStream baos = new ByteArrayOutputStream();  

         workbook.write(baos);  

         byte[] ba = baos.toByteArray();  

        ByteArrayInputStream bais = new ByteArrayInputStream(ba);  

      return bais;  

}

//导入如果限定小数点位数需加入如下代码

 

  //生成样式

  HSSFCellStyle cs = workbook.createCellStyle();

  HSSFDataFormat format = workbook.createDataFormat();

  cs.setDataFormat(format.getFormat("0.00"));

 //针对字段的处理

 cell = row.createCell(3);

 cell.setCellStyle(cs);

cell.setCellValue(dataset.get(i).getConsNum());