JSP中导入导出Excel文件

JSP中导入导出Excel文件一．POI简介JakartaPOI是apache的子项目，目标是处理ole2对象。它提供了一组操纵Windows文档的JavaAPI目前比较成熟的是HSSF接口，处理MSExcel（97-2002）对象。它不象我们仅仅是用csv生成的没有格式的可以由Excel转换的东西，而是真正的Excel对象，你可以控制一些属性如sheet,cell等等。二．HSSF概况HSSF是sHorribleSpreadSheetFormat的缩写，也即“讨厌的电子表格格式”。也许HSSF的名字有点滑稽，就本质而言它是一个非常严肃、正规的API。通过HSSF，你可以用纯Java代码来读取、写入、修改Excel文件。HSSF为读取操作提供了两类API：usermodel和eventusermodel，即“用户模型”和“事件-用户模型”。前者很好理解，后者比较抽象，但操作效率要高得多。三．开始编码1．准备工作要求:JDK1.4+POI开发包可以到最新的POI工具包2．EXCEL结构HSSFWorkbookexcel文档对象介绍HSSFSheetexcel的表单HSSFRowexcel的行HSSFCellexcel的格子单元HSSFFontexcel字体HSSFName名称HSSFDataFormat日期格式HSSFHeadersheet头HSSFFootersheet尾和这个样式HSSFCellStylecell样式辅助操作包括HSSFDateUtil日期HSSFPrintSetup打印HSSFErrorConstants错误信息表4．可参考文档POI主页：，初学者如何快速上手使用POIHSSF。代码例子里面有很多例子代码，可以很方便上手。四．使用心得POIHSSF的usermodel包把Excel文件映射成我们熟悉的结构，诸如Workbook、Sheet、Row、Cell等，它把整个结构以一组对象的形式保存在内存之中，便于理解，操作方便，基本上能够满足我们的要求，所以说这个是一个不错的选择。1.创建工作簿(WORKBOOK)HSSFWorkbookwb=newHSSFWorkbook();FileOutputStreamfileOut=newFileOutputStream(workbook.xls);wb.write(fileOut);fileOut.close();2.创建工作表（SHEET）HSSFWorkbookwb=newHSSFWorkbook();HSSFSheetsheet1=wb.createSheet(newsheet);HSSFSheetsheet2=wb.createSheet(secondsheet);FileOutputStreamfileOut=newFileOutputStream(workbook.xls);wb.write(fileOut);fileOut.close();3.创建单元格(CELL)HSSFWorkbookwb=newHSSFWorkbook();HSSFSheetsheet=wb.createSheet(newsheet);HSSFRowrow=sheet.createRow((short)0);HSSFCellcell=row.createCell((short)0);cell.setCellValue(1);row.createCell((short)1).setCellValue(1.2);row.createCell((short)2).setCellValue(Thisisastring);row.createCell((short)3).setCellValue(true);FileOutputStreamfileOut=newFileOutputStream(workbook.xls);wb.write(fileOut);fileOut.close();4.创建指定单元格式的单元格HSSFWorkbookwb=newHSSFWorkbook();HSSFSheetsheet=wb.createSheet(newsheet);HSSFRowrow=sheet.createRow((short)0);HSSFCellcell=row.createCell((short)0);cell.setCellValue(newDate());HSSFCellStylecellStyle=wb.createCellStyle();cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(m/d/yyh:mm));cell=row.createCell((short)1);cell.setCellValue(newDate());cell.setCellStyle(cellStyle);FileOutputStreamfileOut=newFileOutputStream(workbook.xls);wb.write(fileOut);fileOut.close();5.单元格的不同格式HSSFWorkbookwb=newHSSFWorkbook();HSSFSheetsheet=wb.createSheet(newsheet);HSSFRowrow=sheet.createRow((short)2);row.createCell((short)0).setCellValue(1.1);row.createCell((short)1).setCellValue(newDate());row.createCell((short)2).setCellValue(astring);row.createCell((short)3).setCellValue(true);row.createCell((short)4).setCellType(HSSFCell.CELL_TYPE_ERROR);FileOutputStreamfileOut=newFileOutputStream(workbook.xls);wb.write(fileOut);fileOut.close();6.单元格的不通对齐方式publicstaticvoidmain(String[]args)throwsIOException{HSSFWorkbookwb=newHSSFWorkbook();HSSFSheetsheet=wb.createSheet(newsheet);HSSFRowrow=sheet.createRow((short)2);createCell(wb,row,(short)0,HSSFCellStyle.ALIGN_CENTER);createCell(wb,row,(short)1,HSSFCellStyle.ALIGN_CENTER_SELECTION);createCell(wb,row,(short)2,HSSFCellStyle.ALIGN_FILL);createCell(wb,row,(short)3,HSSFCellStyle.ALIGN_GENERAL);createCell(wb,row,(short)4,HSSFCellStyle.ALIGN_JUSTIFY);createCell(wb,row,(short)5,HSSFCellStyle.ALIGN_LEFT);createCell(wb,row,(short)6,HSSFCellStyle.ALIGN_RIGHT);FileOutputStreamfileOut=newFileOutputStream(workbook.xls);wb.write(fileOut);fileOut.close();}privatestaticvoidcreateCell(HSSFWorkbookwb,HSSFRowrow,shortcolumn,shortalign){HSSFCellcell=row.createCell(column);cell.setCellValue(AlignIt);HSSFCellStylecellStyle=wb.createCellStyle();cellStyle.setAlignment(align);cell.setCellStyle(cellStyle);}7.单元格的边框设置HSSFWorkbookwb=newHSSFWorkbook();HSSFSheetsheet=wb.createSheet(newsheet);HSSFRowrow=sheet.createRow((short)1);HSSFCellcell=row.createCell((short)1);cell.setCellValue(4);HSSFCellStylestyle=wb.createCellStyle();style.setBorderBottom(HSSFCellStyle.BORDER_THIN);style.setBottomBorderColor(HSSFColor.BLACK.index);style.setBorderLeft(HSSFCellStyle.BORDER_THIN);style.setLeftBorderColor(HSSFColor.GREEN.index);style.setBorderRight(HSSFCellStyle.BORDER_THIN);style.setRightBorderColor(HSSFColor.BLUE.index);style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM_DASHED);style.setTopBorderColor(HSSFColor.BLACK.index);cell.setCellStyle(style);FileOutputStreamfileOut=newFileOutputStream(workbook.xls);wb.write(fileOut);fileOut.close();8.填充和颜色设置HSSFWorkbookwb=newHSSFWorkbook();HSSFSheetsheet=wb.createSheet(newsheet);HSSFRowrow=sheet.createRow((short)1);HSSFCellStylestyle=wb.createCellStyle();style.setFillBackgroundColor(HSSFColor.AQUA.index);style.setFillPattern(HSSFCellStyle.BIG_SPOTS);HSSFCellcell=row.createCell((short)1);cell.setCellValue(X);cell.setCellStyle(style);style=wb.createCellStyle();style.setFillForegroundColor(HSSFColor.ORANGE.index);style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);cell=row.createCell((short)2);cell.setCellValue(X);cell.setCellStyle(style);FileOutputStreamfileOut=newFileOutputStream(wo