使用Apache POI和DWR
poi用于在后台生成Excel,用DWR在前台显示后台生成Excel的进度
基本的生成Excel类:
ExcelHelper.java
public class ExcelHelper extends Thread{
private int rowIndex=0;
private int count;
private String fileName;
private String sheetName;
private List<String[]> dataList;
public ExcelHelper(String fileName, String sheetName, List<String[]> dataList) {
this.fileName = fileName;
this.sheetName = sheetName;
this.dataList = dataList;
this.count=dataList.size();
}
@Override
public void run() {
try {
if (rowIndex==0) {
generateWorkbook();
}
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 生成Excel文件
*
* @param fileName
* 要生成的Excel文件名(可用绝对或相对路径)
* @param sheetName
* 生成的Excel文件中的sheet名
* @param dataList
* 要放入Excel文件的内容
* @throws IOException
*/
public void generateWorkbook() throws IOException {
Workbook wb = null;
if (fileName.endsWith(".xlsx")) {
wb = new XSSFWorkbook();
} else if (fileName.endsWith(".xls")) {
wb = new HSSFWorkbook();
} else {
fileName = fileName.concat(".xls");
wb = new HSSFWorkbook();
}
CellStyle cellStyle = wb.createCellStyle();
Font font = wb.createFont();
font.setColor(HSSFColor.RED.index);
cellStyle.setFont(font);
Sheet sheet = wb.createSheet(sheetName);
rowIndex = 0;
for (String[] rowData : dataList) {
Row row = sheet.createRow(rowIndex);
rowIndex++;
int cellIndex = 0;
for (String cellData : rowData) {
Cell cell = row.createCell(cellIndex);
cell.setCellValue(cellData);
cell.setCellStyle(cellStyle);
cellIndex++;
}
}
FileOutputStream fileOut = new FileOutputStream(fileName);
wb.write(fileOut);
fileOut.close();
}
public int getRowIndex() {
return rowIndex;
}
public int getCount() {
return count;
}
}
DWR操作类:
ExcelHelperDWR.java
public class ExcelHelperDWR {
private static Map<Long, ExcelHelper> excelHelperMap;
public ExcelHelperDWR() {
if (excelHelperMap == null) {
excelHelperMap = new HashMap<Long, ExcelHelper>();
}
}
public int getRowIndex(long threadId) {
ExcelHelper excelHelper = excelHelperMap.get(threadId);
if (excelHelper.getRowIndex()<excelHelper.getCount()) {
return excelHelper.getRowIndex();
}else {
return -1;
}
}
public long beginProcess(String fileName, String sheetName) {
//test case
List<String[]> dataList = new ArrayList<String[]>();
for (int i = 0; i < 1000; i++) {
dataList.add(new String[] { "1", "2612601001", "学生1", "123", "3", null, "", "13712345678" });
dataList.add(new String[] { "1", "2612601001", "学生2", "123", "3", "", "", "13712345678" });
dataList.add(new String[] { "1", "2612601001", "学生3", "123", "3", "", "", "13712345678" });
dataList.add(new String[] { "1", "2612601001", "学生4", "123", "3", "", "", "13712345678" });
}
//end test case
ExcelHelper excelHelper = new ExcelHelper(fileName, sheetName, dataList);
excelHelper.start();
long threadId = excelHelper.getId();
excelHelperMap.put(threadId, excelHelper);
return threadId;
}
}
前台index.jsp:
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>My JSP 'index.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
<script type='text/javascript' src='dwr/interface/ExcelHelperDWR.js'></script>
<script type='text/javascript' src='dwr/engine.js'></script>
<script type='text/javascript' src='dwr/util.js'></script>
<script type="text/javascript">
var generateProgress;
function beginProcess()
{
ExcelHelperDWR.beginProcess("D://我的Excel.xlsx", "sheet1",invoke);
}
function invoke(threadId)
{
generateProgress=window.setInterval("ExcelHelperDWR.getRowIndex("+threadId+", invokeCB)", 500);
}
function invokeCB(data)
{
if (data==-1) {
window.clearInterval(generateProgress);
document.getElementById("data").innerHTML = "生成Excel ok!";
} else {
document.getElementById("data").innerHTML = data;
}
}
</script>
</head>
<body>
<input type="button" οnclick="beginProcess()" value="generateExcel" />
<div id="data"></div>
</body>
</html>
OK了,很简易~