本文介绍了一种利用Java POI库实现从Web应用程序中导出Excel文件的方法。该方法适用于JDK1.8和POI-3.14版本，并通过一个具体的工具类示例展示了如何将数据导出到Excel表格中。文中还提供了前端JS部分的代码用于触发导出功能。
项目中要实现导出excel功能，下面是一个公共方法：
环境：JDK1.8，poi-3.14
项目中js请求是ajax请求。
因为response原因，一般请求浏览器是会处理服务器输出的response，例如生成png、文件下载等，然而ajax请求只是个“字符型”的请求，即请求的内容是以文本类型存放的。文件的下载是以二进制形式进行的，虽然可以读取到返回的response，但只是读取而已，是无法执行的，说白点就是js无法调用到浏览器的下载处理机制和程序。
本例中我们先将excel文件下载到服务端，然后前台点击导出excel按钮，从服务端下载相应的文件。
下面代码片是DataExportUtil工具类，控制层传入3个参数就可以实现excel导出：
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.context.ContextLoader;
/**
* 导出excel工具类
* 导出的excel是存在于项目的src/main/webapp/export目录下，如修改，请在84行设置
* @author
*
*/
public class DataExportUtil
{
/**
* 导出excle
* @param sheetName 表名
* @param titleColumns 表头
* @param list 要导出的数据
* @throws IOException
*/
public static String exportDataToExcel(String sheetName, String[] titleColumns, List
list) throws IOException
{
return writeToFile(sheetName,titleColumns,list);
}
/**
* 导出excle
* @param sheetName 表名
* @param titleColumns 表头
* @param list 要导出的数据
*/
private static String writeToFile(String sheetName, String[] titleColumns, List
list) throws IOException
{
String name = "";
HSSFWorkbook workbook = new HSSFWorkbook(); //工作薄
List
dataList = new ArrayList
();
//数据大于10000条，分多张sheet
if (list != null && list.size() > 10000)
{
int c = list.size()%10000==0?list.size()/10000:list.size()/10000+1;
int allTotal = 0;
for(int i = 0; i < c; i++)
{
if (i == c-1)
{
dataList = list.subList(allTotal+1, list.size()-1);
}else
{
if (allTotal == 0)
{
dataList = list.subList(1, i*10001);
}else
{
dataList = list.subList(allTotal+1, i*10000+1);
}
}
allTotal = allTotal + 10000;
HSSFSheet sheet = workbook.createSheet(sheetName+i); //工作表
setHeaderRow(workbook, sheet, sheetName+i, titleColumns); //设置表头
setSignData(dataList, workbook, sheet); //插入数据
}
}else
{
HSSFSheet sheet = workbook.createSheet(sheetName); //工作表
setHeaderRow(workbook, sheet, sheetName, titleColumns); //设置表头
setSignData(list, workbook, sheet); //插入数据
}
try
{
FileOutputStream outputStream;
name = name + sheetName + DateUtil.getNowDateTimeStr() + ".xls"; //导出excel的表名称
outputStream = new FileOutputStream(ContextLoader.getCurrentWebApplicationContext().getServletContext().getRealPath("/") + "/export/" + name); //存储在服务器上路径
workbook.write(outputStream);//输出
outputStream.flush();
outputStream.close();
} catch (Exception e)
{
e.printStackTrace();
throw e;
}
return name;
}
/***
* 设置表头
* @param workbook
* @param sheet
* @param sheetName
* @param titleColumns
*/
private static void setHeaderRow(HSSFWorkbook workbook, HSSFSheet sheet, String sheetName, String[] titleColumns)
{
HSSFRow row = null; //行
HSSFCell cell = null; //单元格
row = sheet.createRow(1); //表头行
for(int i = 0; i < titleColumns.length; i++)
{
cell = row.createCell(i);
cell.setCellStyle(getHeadCellStyle(workbook));
cell.setCellValue(titleColumns[i]);
}
row.setHeight((short) (30 * 40)); //设置行高
row = sheet.createRow(0); //标题
cell = row.createCell(0);
cell.setCellStyle(getHeadCellStyle(workbook));
cell.setCellValue(sheetName + "");
row.setHeight((short) (30 * 40));
setHeaderRowStyle(workbook, row, titleColumns.length);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titleColumns.length - 1)); //合并标题栏
}
/***
* 向表格里写入数据
* @param list
* @param workbook
* @param sheet
*/
private static void setSignData(List
list, HSSFWorkbook workbook, HSSFSheet sheet) { HSSFRow row = null; //行 HSSFCell cell = null; //单元格 HSSFCellStyle style = getContentCellStyle(workbook); int START_ROW = 2; if (list.size() > 0) { //多列 if (list.get(0) instanceof Object[]) { for(Object[] obj : list) { row = sheet.createRow(START_ROW); for(int i = 0; i < obj.length; i++) { cell = row.createCell(i); cell.setCellStyle(style); cell.setCellValue((String)obj[i]); } START_ROW++; } }else { for(Object obj : list) { row = sheet.createRow(START_ROW); cell = row.createCell(0); cell.setCellStyle(style); cell.setCellValue((String)obj); START_ROW++; } } } } /*** * 取得表头样式 * @param workbook 工作薄 * @return */ private static HSSFCellStyle getHeadCellStyle(HSSFWorkbook workbook) { //生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); //设置这些样式 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //生成一个字体 HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.BLACK.index); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //把字体应用到当前的样式 style.setFont(font); style.setWrapText(true); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBottomBorderColor(HSSFColor.BLACK.index); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setLeftBorderColor(HSSFColor.BLACK.index); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setRightBorderColor(HSSFColor.BLACK.index); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setTopBorderColor(HSSFColor.BLACK.index); return style; } /*** * 为了合并单元格，要把其他的单元格带上边框线 * @param workbook * @param row * @param length */ private static void setHeaderRowStyle(HSSFWorkbook workbook,HSSFRow row,int length) { HSSFCell cell = null; //单元格 for (int i = 1; i < length; i++) { cell = row.createCell(i); cell.setCellStyle(null); } } /*** * 取得每行数据样式 * @param workbook * @return */ private static HSSFCellStyle getContentCellStyle(HSSFWorkbook workbook) { HSSFCellStyle style = workbook.createCellStyle(); //样式对象 HSSFFont font = workbook.createFont(); font.setFontName("宋体"); font.setFontHeightInPoints((short) 9); style.setFont(font); style.setWrapText(true); style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBottomBorderColor(HSSFColor.BLACK.index); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setLeftBorderColor(HSSFColor.BLACK.index); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setRightBorderColor(HSSFColor.BLACK.index); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setTopBorderColor(HSSFColor.BLACK.index); return style; } }
下面是js请求的代码，ajax请求我们是封装了下的：
$('#export-table-excle').on('click',function(){
var tableHeader = "项目名称,项目编号,客户名称,客户联系人,活动名称,活动日期,活动地点,我方参与人员,活动目的,会议纪要,关键问题及困难说明,评分";
var params = {
"other": {
"isManager": isManager,
"shortNo": shortNo,
"tableHeader": tableHeader,
"sheetName": "客户公关活动汇总"
}
}
demo.ajax.postUrl("/summary/getExcel.serv", params,
function (ret) {
if (ret != null && ret.code != null && ret.code.code != null && ret.code.code == '0000') {
var name = ret.bo; //name是excel的文件名
var url = "127.0.0.1:8080/demo/export/" + name; //在项目webapp下的export文件夹下载文件
window.open("http://" + url);
}
else {
//服务器操作失败,不关闭对话框
}
return;
}
);
});
@RequestMapping(value = "/getExcel.serv", method = RequestMethod.POST, consumes = MediaType.APPLICATION_JSON_VALUE, produces = MediaType.APPLICATION_JSON_VALUE)
@ResponseBody
public ServiceData getExcel(HttpServletRequest request, @RequestBody FormData<SummaryListModel> form) throws Exception
{
//返回统一的服务端数据
ServiceData ret = new ServiceData();
Map<String, Object> map = new HashMap<String, Object>();
//传入url参数
if (form.getOther().containsKey("shortNo") && form.getOther().containsKey("isManager"))
{
String shortNo = (String)form.getOther().get("shortNo");
String isManager = (String)form.getOther().get("isManager");
//设置查询条件
map.put("shortNo", shortNo);
map.put("isManager", isManager);
}
String tableHeader = (String)form.getOther().get("tableHeader");
String[] titleColumns = tableHeader.trim().split(","); //获取表头
String sheetName = (String)form.getOther().get("sheetName"); //获取表名
List<Object[]> dataList = new ArrayList<Object[]>();
dataList = summaryService.getList(map); //查询数据库中数据,获取数据
String name = DataExportUtil.exportDataToExcel(sheetName, titleColumns, dataList); //调用导出excel工具类
ret.setCode(request, ServiceData.RetCode.Success);
ret.setBo(name);
return ret;
}
如果js使用的不是基于ajax的请求，如果是form表单提交，那么控制层可以写为：
@RequestMapping(value = "/getExcel.serv", method = RequestMethod.POST, consumes = MediaType.APPLICATION_JSON_VALUE, produces = MediaType.APPLICATION_JSON_VALUE)
@ResponseBody
public ServiceData getExcel(HttpServletRequest request, HttpServletResponse response, @RequestBody FormData<SummaryListModel> form) throws Exception
{
//返回统一的服务端数据
ServiceData ret = new ServiceData();
Map<String, Object> map = new HashMap<String, Object>();
//传入url参数
if (form.getOther().containsKey("shortNo") && form.getOther().containsKey("isManager"))
{
String shortNo = (String)form.getOther().get("shortNo");
String isManager = (String)form.getOther().get("isManager");
//设置查询条件
map.put("shortNo", shortNo);
map.put("isManager", isManager);
}
String tableHeader = (String)form.getOther().get("tableHeader");
String[] titleColumns = tableHeader.trim().split(","); //获取表头
String sheetName = (String)form.getOther().get("sheetName"); //获取表名
try{
//设置header
response.setHeader("Content-Disposition","attachment; filename=\""+new String((sheetName).getBytes("gb2312"),"ISO-8859-1")+".xls\"");
OutputStream out = response.getOutputStream();
List<Object[]> dataList = new ArrayList<Object[]>();
dataList = summaryService.getList(map); //查询数据库中数据,获取数据
//此时将工具类中的参数改为4个，exportDataToExcel、writeToFile方法上加上OutputStream out，工具类中的writeToFile方法try中修改为
//workbook.write(out); 这一条语句就ok了，浏览器会自动弹出选择文件夹的框
String name = DataExportUtil.exportDataToExcel(out,sheetName, titleColumns, dataList); //调用导出excel工具类
ret.setCode(request, ServiceData.RetCode.Success);
ret.setBo(name);
out.close();
}catch (IOException e) {
e.printStackTrace();
}
return ret;
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
