POI导出Excel文件

1.首先引入POI的相关jar包：

   可以在这里下载（http://download.youkuaiyun.com/detail/chengjiang008/5767991）

2.再建立一个导出Excel文件的工具类：

package cn.ljj.utils;
import java.util.List;


import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
public class EXCELUtils<T> {


// 序号 登录名 姓名 性别 登录许可 部门 E-mail 角色，具体根据要导出的表而定
public static final String[] TITLES = new String[] { "序号", "登录名", "姓名",
"性别", "登录许可", "部门", "E-mail", "角色" };


private void createTitleRow(Sheet sheet) {
Row row = sheet.createRow(0);
for (int i = 0; i < TITLES.length; i++) {
row.createCell(i).setCellValue(TITLES[i]);
}
}
private void createContentRows(Sheet sheet,List<T> list) {

for (int i = 0; i < list.size(); i++) {
T template = list.get(i);
Row row = sheet.createRow(i + 1);


Cell cell = row.createCell(0);
cell.setCellValue("" + (i + 1));
            //这个是你具体需要导出的表的信息，根据你的实体类中的方法而定
/*cell = row.createCell(1);
cell.setCellValue(template.getLoginName());


cell = row.createCell(2);
cell.setCellValue(template.gettemplateloyeeName());


cell = row.createCell(3);
cell.setCellValue(template.getGender());


cell = row.createCell(4);
cell.setCellValue(template.getEnabled());


cell = row.createCell(5);
cell.setCellValue(template.getDepartment().getDepartmentName());


cell = row.createCell(6);
cell.setCellValue(template.getEmail());


cell = row.createCell(7);
cell.setCellValue(template.getRoleNames());*/
}
}
public void setCellStyle(Workbook wb) {
Sheet sheet = wb.getSheet("templateloyees");


for (int i = 0; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
row.setHeightInPoints(25);


for (int j = 0; j < TITLES.length; j++) {
Cell cell = row.getCell(j);
cell.setCellStyle(getCellStyle(wb));
}
}


for (int j = 0; j < TITLES.length; j++) {
sheet.autoSizeColumn(j);
sheet.setColumnWidth(j, (int) (sheet.getColumnWidth(j) * 1.3));
}
}
private CellStyle getCellStyle(Workbook wb) {
CellStyle style = wb.createCellStyle();
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());


style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());


style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());


style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());


return style;
}

}

3.在service层调用该工具类（这里是service里面的一个方法）：

 public void downLoad(String excelFileName) throws IOException {

                EXCELUtils excel  = new EXCELUtils();

Workbook wb = new XSSFWorkbook(); // or new HSSFWorkbook();

              //0.创建Sheet名称
Sheet sheet = wb.createSheet("employees");

// 1. 填充标题行.
excel.createTitleRow(sheet);

                //--------------------------------------------------------------------------------------------------
// 2. 填充实体表信息,在这里调用你要查询的数据信息，将它传到该方法中，例如：T为要查询的雇员实体类
excel.createContentRows(sheet,List<T> list);
                //--------------------------------------------------------------------------------------------------

// 3. 设置样式: 包括行高, 列宽, 边框.
excel.setCellStyle(wb);


// 4. 把 Excel 保存到磁盘上.
FileOutputStream fileOut = new FileOutputStream(excelFileName);
wb.write(fileOut);
fileOut.close();
}

4.在action层，编写调用的downToExcel方法

注意：在struts.xml中的result节点内，type属性指定为stream（具体细节，请了解struts2的文件下载）

例如：<result name="excel-result" type="stream"></result>

特别要注意的是文件下载，需要在action设置如下几个属性，并编写其get方法：

//文件下载相关的属性
private String contentType;
private long contentLength;
private String fileName;
private InputStream inputStream;

public String getContentType() {
return contentType;
}

public long getContentLength() {
return contentLength;
}

public String getContentDisposition() {
return "attachment;filename=" + fileName;
}

public InputStream getInputStream() {
return inputStream;
}

---------------------------------------------------------------------------------------------------------

现在开始编写具体方法

public String downToExcel() throws IOException{
//初始化文件下载的相关属性.
contentType = "application/vnd.ms-excel";

String excelFileName = ServletActionContext.getServletContext().getRealPath("/files/" + System.currentTimeMillis() + ".xls");
//System.out.println(excelFileName);

                //以employee为例子，具体调用自行编写
employeeService.downLoad(excelFileName);
inputStream = new FileInputStream(excelFileName);
contentLength = inputStream.available();
fileName = "employees.xls";
//记住下载完成后将在服务器上下载的临时文件删掉

                File file = new File(excelFileName);

                file.delete();
return "excel-result";
}

-----------------------------------------------------------------------------------------------------------

这样从数据库中查出的数据导入到excel文件的具体方法就完成了