1.首先引入POI的相关jar包:
可以在这里下载(http://download.csdn.net/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文件的具体方法就完成了