Excel 2007

package com.excel.test;

 

import java.io.*;

import java.util.List;

 

import javax.servlet.http.*;

 

import org.apache.poi.hssf.usermodel.*;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

 

import com.wp.domain.Member;

import com.wp.service.MemberService;

/**

 * 导出导入报表

 * @author Administrator

 *

 */

public class ExcelTest {

private static MemberService mbService;

//**********************************测试*******************************

/**

* 导出所有用户

* 

* @param mapping

* @param form

* @param request

* @param response

* @return

*/

public String getExportOperator(HttpServletRequest request,

HttpServletResponse response) {

response.setHeader("Charset", "UTF-8");

response.setContentType("text/html; charset=utf-8");

List<Member> operList = mbService.getMemberList();

if (null != operList) {

Workbook wb = (Workbook) new XSSFWorkbook();

Sheet sheet = wb.createSheet("用户表");

sheet.setColumnWidth(0, 4000);

sheet.setColumnWidth(1, 4000);

sheet.setColumnWidth(2, 7000);

sheet.setColumnWidth(3, 4000);

sheet.setColumnWidth(4, 4000);

sheet.setColumnWidth(5, 4000);

sheet.setColumnWidth(6, 4000);

sheet.setColumnWidth(7, 4000);

sheet.setColumnWidth(8, 4000);

sheet.setColumnWidth(9, 4000);

sheet.setColumnWidth(10, 4000);

sheet.setColumnWidth(11, 4000);

sheet.setColumnWidth(12, 4000);

Row row = sheet.createRow((int) 0);

CellStyle style = wb.createCellStyle();

style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

//style.setAlignment(CellStyle.ALIGN_CENTER); 

Cell cell = row.createCell((short) 0);

cell.setCellValue("用户名");

cell.setCellStyle(style);

cell = row.createCell((short) 1);

cell.setCellValue("真实姓名");

cell.setCellStyle(style);

cell = row.createCell((short) 2);

cell.setCellValue("证件号码");

cell.setCellStyle(style);

cell = row.createCell((short) 3);

cell.setCellValue("联系电话");

cell.setCellStyle(style);

cell = row.createCell((short) 4);

cell.setCellValue("备注");

cell.setCellStyle(style);

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

row = sheet.createRow((int) i + 1);

// 第四步，创建单元格，并设置值

Member oper = operList.get(i);

row.createCell((short) 0).setCellValue(oper.getMbUserName());

row.createCell((short) 1).setCellValue(oper.getMbRealName());

row.createCell((short) 2).setCellValue(oper.getMbIDCards());

row.createCell((short) 3).setCellValue(oper.getMbMobile());

row.createCell((short) 4).setCellValue("");

}

try {

File file = new File(request.getSession().getServletContext().getRealPath("/DeviceImport"));

if(!file.exists()){

try {

file.mkdir();

} catch (Exception e) {

e.printStackTrace();

}

}

FileOutputStream fout = new FileOutputStream(file

+ "\\用户存根表.xlsx");

wb.write(fout);

fout.close();

GetDownloadExecl(file + "\\用户存根表.xlsx", request, response);

} catch (Exception e) {

e.printStackTrace();

}

}

return null;

}

 

public void GetDownloadExecl(String path, HttpServletRequest request,

HttpServletResponse response) {

try {

File file = new File(path);

// 取得文件名。

String filename = file.getName();

// 取得文件的后缀名。

String ext = filename.substring(filename.lastIndexOf(".") + 1)

.toUpperCase();

 

// 以流的形式下载文件。

InputStream fis = new BufferedInputStream(new FileInputStream(path));

byte[] buffer = new byte[fis.available()];

fis.read(buffer);

fis.close();

// 清空response

response.reset();

// 设置response的Header

response.addHeader("Content-Disposition", "attachment;filename="

+ new String(filename.getBytes("gb2312"), "ISO8859-1"));

response.addHeader("Content-Length", "" + file.length());

OutputStream toClient = new BufferedOutputStream(response

.getOutputStream());

response.setContentType("application/octet-stream");

toClient.write(buffer);

toClient.flush();

toClient.close();

} catch (IOException ex) {

ex.printStackTrace();

}

}

//**********************************测试*******************************

//********************导出   2007*************************************************

public void getWordBook(HttpServletRequest request) throws IOException{

// 构造 XSSFWorkbook 对象，strPath 传入文件路径   

Workbook xwb = new XSSFWorkbook(request.getSession().getServletContext().getRealPath("/DeviceImport")+"/ss.xlsx");   

// 读取第一章表格内容   

Sheet sheet = xwb.getSheetAt(0);   

// 定义 row、cell   

Row row;   

String cell;   

// 循环输出表格中的内容   

for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {   

   row = sheet.getRow(i);   

   for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {   

       // 通过 row.getCell(j).toString() 获取单元格内容，   

       cell = row.getCell(j).toString();   

       System.out.print(cell + "\t");   

   }   

   System.out.println("");   

} 

}

//********************导出 2007*******************************************************

}