之前用过jxl动态到处数据到excel表格的，最近需使用POI3.6动态导出，方法都类似。
首先下载poi包，我这使用的是3.6版本的。
以下是测试类：
@SuppressWarnings({ "deprecation", "unchecked" })
public static void main(String[] args) throws Exception
{
//创建一个webbook，对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
//在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("统计分析");
//设置单元格宽度SetColumnWidth
sheet.setColumnWidth(0, 15 * 256); //15为字符数，乘以256，因为参数单位是1/256个字符宽度（0为A列，1为B列，以此类推）
sheet.setColumnWidth(1, 15 * 256);
sheet.setColumnWidth(4, 40 * 256);
//在sheet中添加表头第0，1,3。。。行
HSSFRow row1 = sheet.createRow((int) 0);
HSSFRow row2 = sheet.createRow((int) 1);
HSSFRow row3 = sheet.createRow((int) 2);
HSSFRow row4 = sheet.createRow((int) 3);
HSSFRow row5 = sheet.createRow((int) (row4.getRowNum()+3));
HSSFRow row6 = sheet.createRow((int) (row5.getRowNum()+1));
HSSFRow row7 = sheet.createRow((int) (row6.getRowNum()+1));
//创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
//合并单元格
sheet.addMergedRegion(new Region(0,(short)0,0,(short)1)); //四个数字分别依次为第一行，第一个单元格，第一行，第二个单元格，合并
sheet.addMergedRegion(new Region(1,(short)0,1,(short)1));
sheet.addMergedRegion(new Region(2,(short)0,2,(short)1));
sheet.addMergedRegion(new Region(3,(short)0,3,(short)1));
sheet.addMergedRegion(new Region(0,(short)3,0,(short)4));
sheet.addMergedRegion(new Region(1,(short)3,1,(short)4));
sheet.addMergedRegion(new Region(2,(short)3,2,(short)4));
sheet.addMergedRegion(new Region(3,(short)3,3,(short)4));
//创建单元格列
HSSFCell cell1 = row1.createCell((short) 0);
HSSFCell cell2 = row2.createCell((short) 0);
HSSFCell cell3 = row3.createCell((short) 0);
HSSFCell cell4 = row4.createCell((short) 0);
HSSFCell cell5 = row5.createCell((short) 0);
HSSFCell cell6 = row6.createCell((short) 0);
HSSFCell cell7 = row7.createCell((short) 0);
cell1.setCellValue("学校");
cell1.setCellStyle(style);
cell1 = row1.createCell((short) 2);
cell1.setCellValue("人数");
cell1.setCellStyle(style);
cell1 = row1.createCell((short) 3);
cell1.setCellValue("备注");
cell1.setCellStyle(style);
cell2.setCellValue("一中");
cell2.setCellStyle(style);
cell2 = row2.createCell((short) 2);
cell2.setCellValue(6165);
cell2.setCellStyle(style);
cell3.setCellValue("二中");
cell3.setCellStyle(style);
cell3 = row3.createCell((short) 2);
cell3.setCellValue(4153);
cell3.setCellStyle(style);
cell3 = row3.createCell((short) 3);
cell3.setCellValue("备注信息");
cell3.setCellStyle(style);
cell4.setCellValue("三中");
cell4.setCellStyle(style);
cell4 = row4.createCell((short) 2);
cell4.setCellValue(2534);
cell4.setCellStyle(style);
cell5.setCellValue("年级");
cell5.setCellStyle(style);
cell5 = row5.createCell((short) 1);
cell5.setCellValue("各班级主任");
cell5.setCellStyle(style);
cell5 = row5.createCell((short) 2);
cell5.setCellValue("学生数目");
cell5.setCellStyle(style);
cell5 = row5.createCell((short) 3);
cell5.setCellValue("比例");
cell5.setCellStyle(style);
cell5 = row5.createCell((short) 4);
cell5.setCellValue("备注");
cell5.setCellStyle(style);
cell6.setCellValue("一年级");
cell6.setCellStyle(style);
cell6 = row6.createCell((short) 2);
cell6.setCellValue(213);
cell6.setCellStyle(style);
List listAAA1=new ArrayList();
listAAA1.add("王敏");
listAAA1.add("王丽");
listAAA1.add("李娟");
List listAAA2=new ArrayList();
listAAA2.add(15);
listAAA2.add(36);
listAAA2.add(86);
cell7.setCellValue("二年级");
cell7.setCellStyle(style);
for(int i=0;i<listAAA1.size();i++){
cell7= row7.createCell((short) 1);
cell7.setCellValue((String) listAAA1.get(i));
cell7= row7.createCell((short) 2);
cell7.setCellValue((Integer) listAAA2.get(i));
row7=sheet.createRow((int)(row7.getRowNum()+1));
}
HSSFRow row8 = sheet.createRow((int) (row7.getRowNum()));
HSSFCell cell8 = row8.createCell((short) 0);
List listAN1=new ArrayList();
listAN1.add("李艳");
listAN1.add("刘芳");
List listAN2=new ArrayList();
listAN2.add(65);
listAN2.add(55);
cell8.setCellValue("三年级");
cell8.setCellStyle(style);
for(int i=0;i<listAN1.size();i++){
cell8= row8.createCell((short) 1);
cell8.setCellValue((String) listAN1.get(i));
cell8= row8.createCell((short) 2);
cell8.setCellValue((Integer) listAN2.get(i));
row8=sheet.createRow((int)(row8.getRowNum()+1));
}
HSSFRow row9 = sheet.createRow((int) (row8.getRowNum()));
HSSFCell cell9 = row9.createCell((short) 0);
List listDUANXIN1=new ArrayList();
listDUANXIN1.add("陈莹");
listDUANXIN1.add("李雪");
listDUANXIN1.add("王瑶");
List listDUANXIN2=new ArrayList();
listDUANXIN2.add(47);
listDUANXIN2.add(94);
listDUANXIN2.add(68);
cell9.setCellValue("四年级");
cell9.setCellStyle(style);
for(int i=0;i<listDUANXIN1.size();i++){
cell9= row9.createCell((short) 1);
cell9.setCellValue((String) listDUANXIN1.get(i));
cell9= row9.createCell((short) 2);
cell9.setCellValue((Integer) listDUANXIN2.get(i));
row9=sheet.createRow((int)(row9.getRowNum()+1));
}
HSSFRow row10 = sheet.createRow((int) (row9.getRowNum()));
HSSFCell cell10 = row10.createCell((short) 0);
List listCRBT1=new ArrayList();
listCRBT1.add("赵梅");
listCRBT1.add("孙彤");
List listCRBT2=new ArrayList();
listCRBT2.add(76);
listCRBT2.add(68);
cell10.setCellValue("五年级");
cell10.setCellStyle(style);
for(int i=0;i<listCRBT1.size();i++){
cell10= row10.createCell((short) 1);
cell10.setCellValue((String) listCRBT1.get(i));
cell10= row10.createCell((short) 2);
cell10.setCellValue((Integer) listCRBT2.get(i));
row10=sheet.createRow((int)(row10.getRowNum()+1));
}
HSSFRow row11 = sheet.createRow((int) (row10.getRowNum()));
HSSFCell cell11 = row11.createCell((short) 0);
List listISCP1=new ArrayList();
listISCP1.add("汪灵");
listISCP1.add("李沫");
listISCP1.add("周晗");
List listISCP2=new ArrayList();
listISCP2.add(85);
listISCP2.add(61);
listISCP2.add(72);
cell11.setCellValue("六年级");
cell11.setCellStyle(style);
for(int i=0;i<listISCP1.size();i++){
cell11= row11.createCell((short) 1);
cell11.setCellValue((String) listISCP1.get(i));
cell11= row11.createCell((short) 2);
cell11.setCellValue((Integer) listISCP2.get(i));
row11=sheet.createRow((int)(row11.getRowNum()+1));
}
HSSFRow row12 = sheet.createRow((int) (row11.getRowNum()));
HSSFCell cell12 = row12.createCell((short) 0);
cell12.setCellValue("七年级");
cell12.setCellStyle(style);
cell12 = row12.createCell((short) 2);
cell12.setCellValue(97);
cell12.setCellStyle(style);
sheet.addMergedRegion(new Region((row12.getRowNum()+1),(short)0,(row12.getRowNum()+1),(short)1));
HSSFRow row13 = sheet.createRow((int) (row12.getRowNum()+1));
HSSFCell cell13 = row13.createCell((short) 0);
cell13.setCellValue("其他");
cell13.setCellStyle(style);
cell13 = row13.createCell((short) 2);
cell13.setCellValue(45);
cell13.setCellStyle(style);
//将文件生成存入硬盘
try
{
FileOutputStream fout = new FileOutputStream("E:/school.xls");
wb.write(fout);
fout.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}导出excel表格如下：
在struts2J2EE应用中，在action使用，只贴出方法：
/**
* 导出excel表格数据
*/
@SuppressWarnings({ "deprecation", "unchecked" })
public void exportExcel(){
student=studentService.statisticQuery(this);
peopleList=studentService.getPeopleList();
//创建一个webbook，对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
//在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("统计分析");
//设置单元格宽度SetColumnWidth
sheet.setColumnWidth(0, 15 * 256); //15为字符数，乘以256，因为参数单位是1/256个字符宽度（0为A列，1为B列，以此类推）
sheet.setColumnWidth(1, 15 * 256);
sheet.setColumnWidth(4, 40 * 256);
在sheet中添加表头第0，1,3。。。行。注意老版本poi对Excel的行数列数有限制short
HSSFRow row1 = sheet.createRow((int) 0);
HSSFRow row2 = sheet.createRow((int) 1);
HSSFRow row3 = sheet.createRow((int) 2);
HSSFRow row4 = sheet.createRow((int) 3);
HSSFRow row14 = sheet.createRow((int) 4);
HSSFRow row15 = sheet.createRow((int) 5);
HSSFRow row5 = sheet.createRow((int) (row15.getRowNum()+3));
//创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
//第一列第二列合并
sheet.addMergedRegion(new Region(0,(short)0,0,(short)1)); //四个数字分别依次为第一行，第一个单元格，第一行，第二个单元格，合并
sheet.addMergedRegion(new Region(1,(short)0,1,(short)1));
sheet.addMergedRegion(new Region(2,(short)0,2,(short)1));
sheet.addMergedRegion(new Region(3,(short)0,3,(short)1));
sheet.addMergedRegion(new Region(4,(short)0,4,(short)1));
sheet.addMergedRegion(new Region(5,(short)0,5,(short)1));
//第四列第五列合并
sheet.addMergedRegion(new Region(0,(short)3,0,(short)4));
sheet.addMergedRegion(new Region(1,(short)3,1,(short)4));
sheet.addMergedRegion(new Region(2,(short)3,2,(short)4));
sheet.addMergedRegion(new Region(3,(short)3,3,(short)4));
sheet.addMergedRegion(new Region(4,(short)3,4,(short)4));
sheet.addMergedRegion(new Region(5,(short)3,5,(short)4));
//创建单元格列
HSSFCell cell1 = row1.createCell((short) 0);
HSSFCell cell2 = row2.createCell((short) 0);
HSSFCell cell3 = row3.createCell((short) 0);
HSSFCell cell4 = row4.createCell((short) 0);
HSSFCell cell14 = row14.createCell((short) 0);
HSSFCell cell15 = row15.createCell((short) 0);
HSSFCell cell5 = row5.createCell((short) 0);
cell1.setCellValue("学校"); //单元格表头名称
cell1.setCellStyle(style);
cell1 = row1.createCell((short) 2);
cell1.setCellValue("人数"); //单元格表头名称
cell1.setCellStyle(style);
cell1 = row1.createCell((short) 3);
cell1.setCellValue("备注"); //单元格表头名称
cell1.setCellStyle(style);
cell2.setCellValue("一中");
cell2.setCellStyle(style);
cell2 = row2.createCell((short) 2);
cell2.setCellValue(student.getSuccess());
cell2.setCellStyle(style);
cell3.setCellValue("二中");
cell3.setCellStyle(style);
cell3 = row3.createCell((short) 2);
cell3.setCellValue(student.getFaile());
cell3.setCellStyle(style);
cell3 = row3.createCell((short) 3);
cell3.setCellValue("备注信息");
cell3.setCellStyle(style);
cell4.setCellValue("三中");
cell4.setCellStyle(style);
cell4 = row4.createCell((short) 2);
cell4.setCellValue(student.getBackoff());
cell4.setCellStyle(style);
cell14.setCellValue("总数量");
cell14.setCellStyle(style);
cell14 = row14.createCell((short) 2);
cell14.setCellValue(student.getAll());
cell14.setCellStyle(style);
cell15.setCellValue("比率");
cell15.setCellStyle(style);
cell15 = row15.createCell((short) 2);
cell15.setCellValue(student.getFailScale());
cell15.setCellStyle(style);
cell5.setCellValue("年级");
cell5.setCellStyle(style);
cell5 = row5.createCell((short) 1);
cell5.setCellValue("各班级主任");
cell5.setCellStyle(style);
cell5 = row5.createCell((short) 2);
cell5.setCellValue("学生数目");
cell5.setCellStyle(style);
cell5 = row5.createCell((short) 3);
cell5.setCellValue("比例");
cell5.setCellStyle(style);
cell5 = row5.createCell((short) 4);
cell5.setCellValue("备注");
cell5.setCellStyle(style);
HSSFRow row6 = sheet.createRow((int) (row5.getRowNum()+1));
HSSFCell cell6 = row6.createCell((short) 0);
cell6.setCellValue("一年级");
cell6.setCellStyle(style);
//写入动态数据
for(Hlr hlr : peopleList.getHlrList()){
cell6= row6.createCell((short) 1);
cell6.setCellValue((String) hlr.getFailure_name());
cell6.setCellStyle(style);
cell6= row6.createCell((short) 2);
cell6.setCellValue(Integer.valueOf(hlr.getCount()));
cell6.setCellStyle(style);
cell6= row6.createCell((short) 3);
cell6.setCellValue(hlr.getScale());
cell6.setCellStyle(style);
row6=sheet.createRow((int)(row6.getRowNum()+1)); //创建下一行
}
//数据是否为空 如果为空 创建下一行
if(peopleList.getHlrList().size()==0){
row6=sheet.createRow((int)(row6.getRowNum()+1));
}
HSSFRow row7 = sheet.createRow((int) (row6.getRowNum()));
HSSFCell cell7 = row7.createCell((short) 0);
cell7.setCellValue("二年级");
cell7.setCellStyle(style);
for(Aaa aaa : peopleList.getAaaList()){
cell7= row7.createCell((short) 1);
cell7.setCellValue((String) aaa.getFailure_name());
cell7.setCellStyle(style);
cell7= row7.createCell((short) 2);
cell7.setCellValue(Integer.valueOf(aaa.getCount()));
cell7.setCellStyle(style);
cell7= row7.createCell((short) 3);
cell7.setCellValue(aaa.getScale());
cell7.setCellStyle(style);
row7=sheet.createRow((int)(row7.getRowNum()+1));
}
if(peopleList.getAaaList().size()==0){
row7=sheet.createRow((int)(row7.getRowNum()+1));
}
HSSFRow row8 = sheet.createRow((int) (row7.getRowNum()));
HSSFCell cell8 = row8.createCell((short) 0);
cell8.setCellValue("三年级");
cell8.setCellStyle(style);
for(AnAaa anaaa : peopleList.getAnaaaList()){
cell8= row8.createCell((short) 1);
cell8.setCellValue((String) anaaa.getFailure_name());
cell8.setCellStyle(style);
cell8= row8.createCell((short) 2);
cell8.setCellValue(Integer.valueOf(anaaa.getCount()));
cell8.setCellStyle(style);
cell8= row8.createCell((short) 3);
cell8.setCellValue(anaaa.getScale());
cell8.setCellStyle(style);
row8=sheet.createRow((int)(row8.getRowNum()+1));
}
if(peopleList.getAnaaaList().size()==0){
row8=sheet.createRow((int)(row8.getRowNum()+1));
}
HSSFRow row9 = sheet.createRow((int) (row8.getRowNum()));
HSSFCell cell9 = row9.createCell((short) 0);
cell9.setCellValue("四年级");
cell9.setCellStyle(style);
for(Duanxin duanxin : peopleList.getDuanxinList()){
cell9= row9.createCell((short) 1);
cell9.setCellValue((String) duanxin.getFailure_name());
cell9.setCellStyle(style);
cell9= row9.createCell((short) 2);
cell9.setCellValue(Integer.valueOf(duanxin.getCount()));
cell9.setCellStyle(style);
cell9= row9.createCell((short) 3);
cell9.setCellValue(duanxin.getScale());
cell9.setCellStyle(style);
row9=sheet.createRow((int)(row9.getRowNum()+1));
}
if(peopleList.getDuanxinList().size()==0){
row9=sheet.createRow((int)(row9.getRowNum()+1));
}
HSSFRow row10 = sheet.createRow((int) (row9.getRowNum()));
HSSFCell cell10 = row10.createCell((short) 0);
cell10.setCellValue("五年级");
cell10.setCellStyle(style);
for(Crbt crbt : peopleList.getCrbtList()){
cell10= row10.createCell((short) 1);
cell10.setCellValue((String) crbt.getFailure_name());
cell10.setCellStyle(style);
cell10= row10.createCell((short) 2);
cell10.setCellValue(Integer.valueOf(crbt.getCount()));
cell10.setCellStyle(style);
cell10= row10.createCell((short) 3);
cell10.setCellValue((String)crbt.getScale());
cell10.setCellStyle(style);
row10=sheet.createRow((int)(row10.getRowNum()+1));
}
if(peopleList.getCrbtList().size()==0){
row10=sheet.createRow((int)(row10.getRowNum()+1));
}
HSSFRow row11 = sheet.createRow((int) (row10.getRowNum()));
HSSFCell cell11 = row11.createCell((short) 0);
cell11.setCellValue("六年级");
cell11.setCellStyle(style);
for(Iscp iscp : peopleList.getIscpList()){
cell11= row11.createCell((short) 1);
cell11.setCellValue((String) iscp.getFailure_name());
cell11.setCellStyle(style);
cell11= row11.createCell((short) 2);
cell11.setCellValue(Integer.valueOf(iscp.getCount()));
cell11.setCellStyle(style);
cell11= row11.createCell((short) 3);
cell11.setCellValue((String)iscp.getScale());
cell11.setCellStyle(style);
row11=sheet.createRow((int)(row11.getRowNum()+1));
}
if(peopleList.getIscpList().size()==0){
row11=sheet.createRow((int)(row11.getRowNum()+1));
}
HSSFRow row12 = sheet.createRow((int) (row11.getRowNum()));
HSSFCell cell12 = row12.createCell((short) 0);
cell12.setCellValue("七年级");
cell12.setCellStyle(style);
for(Scp scp : peopleList.getScpList()){
cell12= row12.createCell((short) 1);
cell12.setCellValue((String) scp.getFailure_name());
cell12.setCellStyle(style);
cell12= row12.createCell((short) 2);
cell12.setCellValue(Integer.valueOf(scp.getCount()));
cell12.setCellStyle(style);
cell12= row12.createCell((short) 3);
cell12.setCellValue((String)scp.getScale());
cell12.setCellStyle(style);
row12=sheet.createRow((int)(row12.getRowNum()+1));
}
if(peopleList.getScpList().size()==0){
row12=sheet.createRow((int)(row12.getRowNum()+1));
}
//合并单元格，上一行行数加1
sheet.addMergedRegion(new Region((row12.getRowNum()+1),(short)0,(row12.getRowNum()+1),(short)1));
HSSFRow row13 = sheet.createRow((int) (row12.getRowNum()));
HSSFCell cell13 = row13.createCell((short) 0);
cell13.setCellValue("其他");
cell13.setCellStyle(style);
cell13 = row13.createCell((short) 2);
cell13.setCellValue(8);
cell13.setCellStyle(style);
try
{
FileOutputStream fout = new FileOutputStream("E:/export.xls");
wb.write(fout);
fout.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
以上部分的实现都是生成excel文件后保存到本地硬盘，在实际开发中，很多时候这样实现不可行，需要直接弹出下载文件框供用户选择下载，此时就可以这样实现：
使用ByteArrayOutputStream和ByteArrayInputStream类，思路为将HSSFWorkbook 写入ByteArrayOutputStream.然后用ByteArrayOutputStream来转换为字节流..然后再将字节流转换为ByteArrayInputStream ..这样，我们就可以把excel转换为输入流然后再输出下载。
String fileName="";(fileName为空时，会默认为action方法名称)
response.reset();
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel"); //保证不乱码
response.setHeader("Content-Disposition","attachment;" + " filename=" + new String(fileName.getBytes(), "ISO-8859-1"));
OutputStream os = response.getOutputStream();
try
{
ByteArrayOutputStream oss =new ByteArrayOutputStream();
wb.write(oss);
byte temp[] = oss.toByteArray();
ByteArrayInputStream in = new ByteArrayInputStream(temp);
int n = 0;
while ((n = in.read(temp)) >0) {
os.write(temp, 0, n);
}
os.flush();
os.close();
}web.xml文件配置：
<mime-mapping>
<extension>xls</extension>
<mime-type>application/vnd.ms-excel</mime-type>
</mime-mapping>
6万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
