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开发包可以到http:/www.apache.org/dyn/closer.cgi/jakarta/poi/最新的POI工具包2EXCEL结构HSSFWorkbookexcel文档对象介绍HSSFSheetexcel的表单HSSFRowexcel的行HSSFCellexcel的格子单元HSSFFontexcel字体HSSFName名称HSSFDataFormat日期格式HSSFHeadersheet头HSSFFootersheet尾和这个样式HSSFCellStylecell样式辅助操作包括HSSFDateUtil日期HSSFPrintSetup打印HSSFErrorConstants错误信息表4. 可参考文档POI主页：http:/jakarta.apache.org/poi/,初学者如何快速上手使用POIHSSFhttp:/jakarta.apache.org/poi/hssf/quick-guide.html。代码例子http:/blog.java-里面有很多例子代码，可以很方便上手。四.使用心得POIHSSF的usermodel包把Excel文件映射成我们熟悉的结构，诸如Workboo中，便于理解，操作方便，基本上能够满足我们的要求，所以说这个是一个不错的选择。1.创建工作簿(WORKBOOK)HSSFWorkbookwb=newHSSFWorkbook();FileOutputStreamfileOut=newFileOutputStreamCworkbook.xls);wb.write(fileOut);fileOut.closeQ;kSheetRowCell等，它把整个结构以一组对象的形式保存在内存之2创建工作表(SHEETHSSFWorkbookwb=newHSSFWorkbook();HSSFSheetsheetl=wb.createSheetfnewsheet);HSSFSheetsheet2=wb.createSheet(secondsheet);FileOutputStreamfileOut=newFileOutputStreamCworkbook.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(l);row.createCell(short)1).setCellValue(1.2);row.createCell(short)2).setCellValue(Thisisastring);row.createCell(short)3).setCellValue(true);FileOutputStreamfileOut=newFileOutputStreamCworkbook.xls);wb.write(fileOut);fileOut.closeQ;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=newFileOutputStreamCworkbook.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(newDateQ);row.createCell(short)2).setCellValue(astring);row.createCell(short)3).setCellValue(true);row.createCell(short)4).setCellType(HSSFCell.CELL_TYPE_ERROR);FileOutputStreamfileOut=newFileOutputStreamCworkbook.xls);wb.write(fileOut);fileOut.closeQ;6. 单元格的不通对齐方式publicstaticvoidmain(Stringargs)throwslOExceptionHSSFWorkbookwb=newHSSFWorkbook();HSSFSheetsheet=wb.createSheetfnewsheet);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.Al_IGN_RIGHT);FileOutputStreamfileOut=newFileOutputStreamCworkbook.xls);wb.write(fileOut);fileOut.closeQ;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=newFileOutputStreamCworkbook.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=newFileOutputStreamCworkbook.xls);wb.write(fileOut);fileOut.closeQ;9. 合并单元格操作HSSFWorkbookwb=newHSSFWorkbook();HSSFSheetsheet=wb.createSheet(newsheet);HSSFRowrow=sheet.createRow(short)1);HSSFCellcell=row.createCell(short)1);cell.setCellValue(Thisisatestofmerging);sheet.addMergedRegion(newRegion(1,(short)1,1,(short)2);FileOutputStreamfileOut=newFileOutputStreamCworkbook.xls);wb.write(fileOut);fileOut.close();9.1合并单元格边框的解决办法privatevoidsetRegionStyle(HSSFSheetsheet,Regionregion,HSSFCellStylecs)inttoprowNum=region,getRowFrom();for(inti=region.getRowFrom();i=region,getRowTo();i+)HSSFRowrow=HSSFCellUtil.getRow(i,sheet);for(intj=region.getColumnFrom();j=region.getColumnTo();j+)HSSFCellcell=HSSFCellUtil.getCell(row,(short)j);cell.setCellStyle(cs);10. 字体设置HSSFWorkbookwb=newHSSFWorkbook();HSSFSheetsheet=wb.createSheet(newsheet);HSSFRowrow=sheet.createRow(short)1);HSSFFontfont=wb.createFont();font.setFontHeightlnPoints(short)24);font.setFontName(CourierNew);font.setltalic(true);font.setStrikeout(true);HSSFCellStylestyle=wb.createCellStyle();style.setFont(font);HSSFCellcell=row.createCell(short)1);cell.setCellValue(Thisisatestoffonts);cell.setCellStyle(style);FileOutputStreamfileOut=newFileOutputStreamCworkbook.xls);wb.write(fileOut);fileOut.closeQ;11. 自定义颜色Java代码HSSFWorkbookwb=newHSSFWorkbook();HSSFSheetsheet=wb.createSheet();HSSFRowrow=sheet.createRow(short)0);HSSFCellcell=row.createCell(short)0);cell.setCellValue(DefaultPalette);HSSFCellStylestyle=wb.createCellStyle();style.setFillForegroundColor(HSSFColor.LIME.index);style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);HSSFFontfont=wb.createFont();font.setColor(HSSFColor.RED.index);style.setFont(font);cell.setCellStyle(style);FileOutputStreamout=newFileOutputStreamCdefault_palette.xls);wb.write(out);out.close();cell.setCellValue(ModifiedPalette);HSSFPalettepalette=wb.getCustomPalette();palette.setColorAtlndex(HSSFColor.RED.index,(byte)153,/RGBred(0-255)(byte)0,/RGBgreen(byte)0/RGBblue)；palette.setColorAtlndex(HSSFColor.LIME.index,(byte)255,(byte)204,(byte)102);out=newFileOutputStream(modified_palette.xls);wb.write(out);out.closeQ;12读和重写EXCELS件POIFSFileSystemfs=newPOIFSFileSystem(newFilelnputStreamCworkbook.xls);HSSFWorkbookwb=newHSSFWorkbook(fs);HSSFSheetsheet=wb.getSheetAt(O);HSSFRowrow=sheet.getRow(2);HSSFCellcell=row.getCell(short)3);if(cell=null)cell=row.createCell(short)3);cell.setCellType(HSSFCell.CELL_TYPE_STRING);cell.setCellValue(atest);FileOutputStreamfileOut=newFileOutputStreamCworkbook.xls);wb.write(fileOut);fileOut.close();13在EXCEL单元格中使用自动换行HSSFWorkbookwb=newHSSFWorkbook();HSSFSheets=wb.createSheet();HSSFRowr=null;HSSFCellc=null;HSSFCellStylecs=wb.createCellStyle();HSSFFontf=wb.createFont();HSSFFontf2=wb.createFont();cs=wb.createCellStyle();cs.setFont(f2);cs.setWrapText(true);r=s.createRow(short)2);r.setHeight(short)0x349);c=r.createCell(short)2);c.setCellType(HSSFCell.CELL_TYPE_STRING);c.setCellValue(Usenwithwordwrapontocreateanewline);c.setCellStyle(cs);s.setColumnWidth(short)2,(short)(50*8)/(double)1/20);FileOutputStreamfileOut=newFileOutputStream(workbook.xls);wb.write(fileOut);fileOut.closeQ;14数字格式自定义HSSFWorkbookwb=newHSSFWorkbook();HSSFSheetsheet=wb.createSheet(formatsheet);HSSFCellStylestyle;HSSFDataFormatformat=wb.createDataFormat();HSSFRowrow;HSSFCellcell;shortrowNum=0;shortcolNum=0;row=sheet.createRow(rowNum+);cell=row.createCell(colNum);cell.setCellValue(11111.25);style=wb.createCellStyle();style.setDataFormat(format.getFormat(0.0);cell.setCellStyle(style);row=sheet.createRow(rowNum+);cell=row.createCell(colNum);cell.setCellValue(11111.25);style=wb.createCellStyle();style.setDataFormat(format.getFormat(#,#0.0000);cell.setCellStyle(style);FileOutputStreamfileOut=newFileOutputStreamCworkbook.xls);wb.write(fileOut);fileOut.closeQ;HSSFWorkbookwb=newHSSFWorkbook();HSSFSheetsheet=wb.createSheet(formatsheet);HSSFCellStylestyle;HSSFDataFormatformat=wb.createDataFormat();HSSFRowrow;HSSFCellcell;shortrowNum=0;shortcolNum=0;row=sheet.createRow(rowNum+);cell=row.createCell(colNum);cell.setCellValue(11111.25);style=wb.createCellStyle();style.setDataFormat(format.getFormat(0.0);cell.setCellStyle(style);row=sheet.createRow(rowNum+);cell=row.createCell(colNum);cell.setCellValue(11111.25);style=wb.createCellStyle();style.setDataFormat(format.getFormat(#,#0.0000);cell.setCellStyle(style);FileOutputStreamfileOut=newFileOutputStreamCworkbook.xls);wb.write(fileOut);fileOut.closeQ;15.调整工作单位置HSSFWorkbookwb=newHSSFWorkbook();HSSFSheetsheet=wb.createSheet(formatsheet);HSSFPrintSetupps=sheet.getPrintSetup();sheet.setAutobreaks(true);ps.setFitHeight(short)1);ps.setFitWidth(short)1);/Createvariouscellsandrowsforspreadsheet.FileOutputStreamfileOut=newFileOutputStreamCworkbook.xls);wb.write(fileOut);fileOut.close();16设置打印区域HSSFWorkbookwb=newHSSFWorkbook();HSSFSheetsheet=wb.createSheet(Sheet1);wb.setPrintArea(O,$A$1:$C$2);FileOutputStreamfileOut=newFileOutputStreamCworkbook.xls);wb.write(fileOut);fileOut.closeQ;17标注脚注HSSFWorkbookwb=newHSSFWorkbook();HSSFSheetsheet=wb.createSheet(formatsheet);HSSFFooterfooter=sheet.getFooter()footer.setRight(Page+HSSFFooter.page()+of+HSSFFooter.numPages();FileOutputStreamfileOut=newFileOutputStreamCworkbook.xls);wb.write(fileOut);fileOut.close();18. 使用方便的内部提供的函数HSSFWorkbookwb=newHSSFWorkbook();HSSFSheetsheetl=wb.createSheet(newsheet);HSSFRowrow=sheetl.createRow(short)1);HSSFRowrow2=sheetl.createRow(short)2);HSSFCellcell=row.createCell(short)1);cell.setCellValue(Thisisatestofmerging);Regionregion=newRegion(1,(short)1,4,(short)4);sheetl.addMergedRegion(region);finalshortborderMediumDashed=HSSFCellStyle.BORDER_MEDIUM_DASHED;HSSFRegionUtil.setBorderBottom(borderMediumDashed,region,sheetl,wb);HSSFRegionUtil.setBorderTop(borderMediumDashed,region,sheetl,wb);HSSFRegionUtil.setBorderLeft(borderMediumDashed,region,sheetl,wb);HSSFRegionUtil.setBorderRight(borderMediumDashed,region,sheetl,wb);HSSFRegionUtil.setBottomBorderColor(HSSFColor.AQUA.index,region,sheetl,wb);HSSFRegionUtil.setTopBorderColor(HSSFColor.AQUA.index,region,sheetl,wb);HSSFRegionUtil.setLeftBorderColor(HSSFColor.AQUA.index,region,sheetl,wb);HSSFRegionUtil.setRightBorderColor(HSSFColor.AQUA.index,region,sheetl,wb);HSSFCellStylestyle=wb.createCellStyle();style.setlndention(short)4);HSSFCellUtil.createCell(row,8,Thisisthevalueofthecell,style);HSSFCellcell2=HSSFCellUtil.createCell(row2,8,Thisisthevalueofthecell);HSSFCellUtil.setAlignment(cell2,wb,HSSFCellStyle.ALIGN_CENTER);FileOutputStreamfileOut=newFileOutputStream(workbook.xls);wb.write(fileOut);fileOut.close();19. 在工作单中移动行，调整行的上下位置HSSFWorkbookwb=newHSSFWorkbook();HSSFSheetsheet=wb.createSheet(rowsheet);sheet.shiftRows(5,10,-5);FileOutputStreamfileOut=newFileOutputStreamCworkbook.xls);wb.write(fileOut);fileOut.closeQ;20. 选种指定的工作单HSSFWorkbookwb=newHSSFWorkbook();HSSFSheetsheet=wb.createSheet(rowsheet);sheet.setSelected(true);FileOutputStreamfileOut=newFileOutputStreamCworkbook.xls);wb.write(fileOut);fileOut.close();21工作单的放大缩小HSSFWorkbookwb=newHSSFWorkbook();HSSFSheetsheetl=wb.createSheetfnewsheet);sheetl.setZoom(3,4);FileOutputStreamfileOut=newFileOutputStreamCworkbook.xls);wb.write(fileOut);fileOut.closeQ;22.头注和脚注HSSFWorkbookwb=newHSSFWorkbook();HSSFSheetsheet=wb.createSheet(newsheet);HSSFHeaderheader=sheet.getHeader();header.setCenter(CenterHeader);header.setLeft(LeftHeader);header.setRight(HSSFHeader.font(Stencil-Normal,Italic)+HSSFHeader.fontSize(short)16)+Rightw/Stencil-NormalItalicfontandsize16);FileOutputStreamfileOut=newFileOutputStream(workbook.xls);wb.write(fileOut);fileOut.close();23.图片的使用/先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArrayByteArrayOutputStreambyteArrayOut=newByteArrayOutputStream();BufferedImagebufferImg=ImageIO.read(newFile(ok.jpg);ImageIO.write(bufferImg,jpg,byteArrayOut);/读进一个excel模版FileInputStreamfos=newFileInputStream(filePathName+/stencil.xlt);fs=newPOIFSFileSystem(fos);/创建一个工作薄HSSFWorkbookwb=newHSSFWorkbook(fs);HSSFSheetsheet=wb.getSheetAt(0);HSSFPatriarchpatriarch=sheet.createDrawingPatriarch();HSSFClientAnchoranchor=newHSSFClientAnchor(0,0,1023,255,(short)0,0,(short)10,10);patriarch.createPicture(anchor,wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG);/先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArrayByteArrayOutputStreambyteArrayOut=newByteArrayOutputStream();BufferedImagebufferImg=ImageIO.read(newFile(ok.jpg);ImageIO.write(bufferImg,jpg,byteArrayOut);/读进一个excel模版FileInputStreamfos=newFileInputStream(filePathName+/stencil.xlt);fs=newPOIFSFileSystem(fos);/创建一个工作薄HSSFWorkbookwb=newHSSFWorkbook(fs);HSSFSheetsheet=wb.getSheetAt(0);HSSFPatriarchpatriarch=sheet.createDrawingPatriarch();HSSFClientAnchoranchor=newHSSFClientAnchor(0,0,1023,255,(short)0,0,(short)10,10);patriarch.createPicture(anchor,wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG);/以上实例代码均来自官方网站/POI中使用的颜色是用颜色索引来实现，如下：/*颜色对照表数字代表颜色索引8：BLACK60：BROWN59：OLIVE_GREEN58：DARK_GREEN56：DARK_TEAL18：DARK_BLUE32：DARK_BLUE62635319仃213812395423105250574948203655143351133411INDIGOGREY_80_PERCENTORANGEDARK_YELLOWGREENTEALTEALBLUEBLUEBLUE_GREYGREY_50_PERCENTREDLIGHT_ORANGELIMESEA_GREENAQUALIGHT_BLUEVIOLETVIOLETGREY_40_PERCENTPINKPINKGOLDYELLOWYELLOWBRIGHTGREEN351535163740612522454342412744469:24262528293031BRIGHT_GREENTURQUOISETURQUOISEDARK_REDDARK_REDSKY_BLUEPLUMPLUMGREY_25_PERCENTROSELIGHT_YELLOWLIGHT_GREENLIGHT_TURQUOISELIGHT_TURQUOISEPALE_BLUELAVENDERWHITECORNFLOWER_BLUELEMON_CHIFFONMAROONORCHIDCORALROYAL_BLUELIGHTCORNFLOWERBLUE五、应用实例：计算机设备管理数据提交    重新输入    导出到ExcelonClick=clickForOutToExcel();设备编号inputname=btnDotype=submitvalue=inputname=btnResettype=resetvalue=inputname=btnOutToExceltype=buttonvalue=functionclickForOutToExcel()document.form2.GetOrPut.value=PutMaintain;alert(维修设备清单成功导出为EXCELS件！rr请查看C盘根目录下的:document.fonYi2.submit();维修清单.Xis);六、处理有EXCEL导入导出要求的JSP文件(JSPExcel.jsp)七、Javabean中专门处理EXCELS导入导出的类：GetOrWriteExceI.javapackagecomputer;importjava.io.*;importjava.sql.Connection;importjava.sql.ResultSet;importjava.sql.ResultSetMetaData;importjava.sql.SQLException;importjava.sql.Statement;importjava.util.ArrayList;importorg.apache.poi.hssf.usermodel.*;importorg.apache.poi.hssf.util.HSSFColor;importorg.apache.poi.poifs.filesystem.POIFSFileSystem;publicclassGetOrWriteExcelPoolpl;Connectioncn;publicGetOrWriteExcel()trypl=newPool();/在办公室通过WE臊作数据库cn=pl.getConnection();/在办公室用Test测试数据库操作cn=pl.getConnectForTest();/在家测试数据库cn=pl.getConnectAtHome();if(cn=null)System.out.println(数据库连接为空catch(Exceptionex)System.out.println(ex);publicvoidAIIEquipToExcel()StringsqlStr=select*fromEquip_View;设备清单.xls;tryDataToExcel(sqlStr,fname);Excel成功!);System.out.println(所有设备导入到catch(lOExceptione)/TODOAuto-generatedcatchblocke.printStackTrace();/*从Excel中导入设备列表，参数gf表示要导入的excel文件名*/publicvoidGetEquipFromExcel(Stringgf)throwsIOExceptiontryPOIFSFileSystemfs=newPOIFSFileSystem(newFileInputStream(gf);HSSFWorkbookwb=newHSSFWorkbook(fs);HSSFSheetsheet=wb.getSheetAt(0);HSSFRowrow;intcount=0;count=sheet.getPhysicalNumberOfRows();/数据库操作trycn.setAutoCommit(false);Statementst=cn.createStatement();StringsqlStr=;for(inti=1;icount;i+)row=sheet.getRow(i);sqlStr=INSERTINTOEquip(EquipID,Price,BuyDate,ETypeID,DealerID,CPU,HD,MEMORY,DISPLAY,MAINBOARD,EState,Other,Remark,Spec)VALUES(;sqIStr+=”；sqIStr+=row.getCell(short)0).toString().trim();sqIStr+=7;sqIStr+=Float.parseFloat(row.getCell(short)1).toString();sqIStr+=7;sqIStr+=row.getCell(short)2).toString().substring,10);sqIStr+=7;sqIStr+=row.getCell(short)3).toString().trim();sqIStr+=7;sqIStr+=row.getCell(short)4).toString().trim();sqIStr+=7;sqIStr+