JSP中导入导出Excel文件一．POI简介Jakarta POI 是apache的子项目，目标是处理ole2对象。
它提供了一组操纵Windows文档的Java API目前比较成熟的是HSSF接口，处理MS Excel（97-2002）对象。
它不象我们仅仅是用csv生成的没有格式的可以由Excel转换的东西，而是真正的Excel 对象，你可以控制一些属性如sheet,cell等等。
二．HSSF概况HSSF 是sHorrible SpreadSheet Format的缩写，也即“讨厌的电子表格格式”。
也许HSSF的名字有点滑稽，就本质而言它是一个非常严肃、正规的API。
通过HSSF，你可以用纯Java代码来读取、写入、修改Excel文件。
HSSF 为读取操作提供了两类API：usermodel和eventusermodel，即“用户模型”和“事件-用户模型”。
前者很好理解，后者比较抽象，但操作效率要高得多。
三．开始编码1 ．准备工作要求:JDK 1.4+POI开发包可以到 /dyn/closer.cgi/jakarta/poi/ 最新的POI工具包2 ． EXCEL 结构HSSFWorkbook excel 文档对象介绍HSSFSheet excel的表单HSSFRow excel的行HSSFCell excel的格子单元HSSFFont excel字体HSSFName 名称HSSFDataFormat 日期格式HSSFHeader sheet头HSSFFooter sheet尾和这个样式HSSFCellStyle cell样式辅助操作包括HSSFDateUtil 日期HSSFPrintSetup 打印HSSFErrorConstants 错误信息表4 ．可参考文档POI 主页：/poi/，初学者如何快速上手使用POI HSSF/poi/hssf/quick-guide.html 。
代码例子/user1/6749/archives/2005/18347.html里面有很多例子代码，可以很方便上手。
四．使用心得POI HSSF 的usermodel包把Excel文件映射成我们熟悉的结构，诸如Workbook、Sheet、Row、Cell等，它把整个结构以一组对象的形式保存在内存之中，便于理解，操作方便，基本上能够满足我们的要求，所以说这个是一个不错的选择。
1.创建工作簿 (WORKBOOK)HSSFWorkbook wb = new HSSFWorkbook();FileOutputStream fileOut = new FileOutputStream("workbook.xls");wb.write(fileOut);fileOut.close();2.创建工作表（SHEET）HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet1 = wb.createSheet("new sheet");HSSFSheet sheet2 = wb.createSheet("second sheet"); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut);fileOut.close();3.创建单元格(CELL)HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet("new sheet");HSSFRow row = sheet.createRow((short)0);HSSFCell cell = row.createCell((short)0);cell.setCellValue(1);row.createCell((short)1).setCellValue(1.2);row.createCell((short)2).setCellValue("This is a string");row.createCell((short)3).setCellValue(true);FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut);fileOut.close();4.创建指定单元格式的单元格HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet("new sheet");HSSFRow row = sheet.createRow((short)0);HSSFCell cell = row.createCell((short)0);cell.setCellValue(new Date());HSSFCellStyle cellStyle = wb.createCellStyle();cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); cell = row.createCell((short)1);cell.setCellValue(new Date());cell.setCellStyle(cellStyle);FileOutputStream fileOut = new FileOutputStream("workbook.xls");wb.write(fileOut);fileOut.close();5. 单元格的不同格式HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet("new sheet");HSSFRow row = sheet.createRow((short)2);row.createCell((short) 0).setCellValue(1.1);row.createCell((short) 1).setCellValue(new Date());row.createCell((short) 2).setCellValue("a string");row.createCell((short) 3).setCellValue(true);row.createCell((short) 4).setCellType(HSSFCell.CELL_TYPE_ERROR);FileOutputStream fileOut = new FileOutputStream("workbook.xls");wb.write(fileOut);fileOut.close();6.单元格的不通对齐方式public static void main(String[] args) throws IOException{HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet("new sheet");HSSFRow row = 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);FileOutputStream fileOut = new FileOutputStream("workbook.xls");wb.write(fileOut);fileOut.close();}private static void createCell(HSSFWorkbook wb, HSSFRow row, short column, short align) {HSSFCell cell = row.createCell(column);cell.setCellValue("Align It");HSSFCellStyle cellStyle = wb.createCellStyle();cellStyle.setAlignment(align);cell.setCellStyle(cellStyle);}7.单元格的边框设置HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet("new sheet");HSSFRow row = sheet.createRow((short) 1);HSSFCell cell = row.createCell((short) 1);cell.setCellValue(4);HSSFCellStyle style = 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);FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut);fileOut.close();8.填充和颜色设置HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet("new sheet");HSSFRow row = sheet.createRow((short) 1);HSSFCellStyle style = wb.createCellStyle();style.setFillBackgroundColor(HSSFColor.AQUA.index);style.setFillPattern(HSSFCellStyle.BIG_SPOTS);HSSFCell cell = 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);FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut);fileOut.close();9.合并单元格操作HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet("new sheet");HSSFRow row = sheet.createRow((short) 1);HSSFCell cell = row.createCell((short) 1);cell.setCellValue("This is a test of merging");sheet.addMergedRegion(new Region(1,(short)1,1,(short)2));FileOutputStream fileOut = new FileOutputStream("workbook.xls");wb.write(fileOut);fileOut.close();9.1合并单元格边框的解决办法private void setRegionStyle(HSSFSheet sheet, Region region , HSSFCellStyle cs) { int toprowNum = region.getRowFrom();for (int i = region.getRowFrom(); i <= region.getRowTo(); i ++) {HSSFRow row = HSSFCellUtil.getRow(i, sheet);for (int j = region.getColumnFrom(); j <= region.getColumnTo(); j++) { HSSFCell cell = HSSFCellUtil.getCell(row, (short)j);cell.setCellStyle(cs);}}}10.字体设置HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet("new sheet");HSSFRow row = sheet.createRow((short) 1);HSSFFont font = wb.createFont();font.setFontHeightInPoints((short)24);font.setFontName("Courier New");font.setItalic(true);font.setStrikeout(true);HSSFCellStyle style = wb.createCellStyle();style.setFont(font);HSSFCell cell = row.createCell((short) 1);cell.setCellValue("This is a test of fonts");cell.setCellStyle(style);FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut);fileOut.close();11.自定义颜色Java代码HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet();HSSFRow row = sheet.createRow((short) 0);HSSFCell cell = row.createCell((short) 0);cell.setCellValue("Default Palette");HSSFCellStyle style = wb.createCellStyle();style.setFillForegroundColor(HSSFColor.LIME.index);style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);HSSFFont font = wb.createFont();font.setColor(HSSFColor.RED.index);style.setFont(font);cell.setCellStyle(style);FileOutputStream out = new FileOutputStream("default_palette.xls");wb.write(out);out.close();cell.setCellValue("Modified Palette");HSSFPalette palette = wb.getCustomPalette();palette.setColorAtIndex(HSSFColor.RED.index,(byte) 153, //RGB red (0-255)(byte) 0, //RGB green(byte) 0 //RGB blue);palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 255, (byte) 204, (byte) 102);out = new FileOutputStream("modified_palette.xls");wb.write(out);out.close();12.读和重写EXCEL文件POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("workbook.xls")); HSSFWorkbook wb = new HSSFWorkbook(fs);HSSFSheet sheet = wb.getSheetAt(0);HSSFRow row = sheet.getRow(2);HSSFCell cell = row.getCell((short)3);if (cell == null)cell = row.createCell((short)3);cell.setCellType(HSSFCell.CELL_TYPE_STRING);cell.setCellValue("a test");FileOutputStream fileOut = new FileOutputStream("workbook.xls");wb.write(fileOut);fileOut.close();13.在EXCEL单元格中使用自动换行HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet s = wb.createSheet();HSSFRow r = null;HSSFCell c = null;HSSFCellStyle cs = wb.createCellStyle();HSSFFont f = wb.createFont();HSSFFont f2 = 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( "Use \n with word wrap on to create a new line" );c.setCellStyle( cs );s.setColumnWidth( (short) 2, (short) ( ( 50 * 8 ) / ( (double) 1 / 20 ) ) ); FileOutputStream fileOut = new FileOutputStream( "workbook.xls" );wb.write( fileOut );fileOut.close();14.数字格式自定义HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet("format sheet");HSSFCellStyle style;HSSFDataFormat format = wb.createDataFormat();HSSFRow row;HSSFCell cell;short rowNum = 0;short colNum = 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);FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut);fileOut.close();HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet("format sheet");HSSFCellStyle style;HSSFDataFormat format = wb.createDataFormat();HSSFRow row;HSSFCell cell;short rowNum = 0;short colNum = 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);FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut);fileOut.close();15.调整工作单位置HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet("format sheet");HSSFPrintSetup ps = sheet.getPrintSetup();sheet.setAutobreaks(true);ps.setFitHeight((short)1);ps.setFitWidth((short)1); // Create various cells and rows for spreadsheet. FileOutputStream fileOut = new FileOutputStream("workbook.xls");wb.write(fileOut);fileOut.close();16.设置打印区域HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet("Sheet1");wb.setPrintArea(0, "$A$1:$C$2");FileOutputStream fileOut = new FileOutputStream("workbook.xls");wb.write(fileOut);fileOut.close();17.标注脚注HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet("format sheet");HSSFFooter footer = sheet.getFooter()footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() ); FileOutputStream fileOut = new FileOutputStream("workbook.xls");wb.write(fileOut);fileOut.close();18.使用方便的内部提供的函数HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet1 = wb.createSheet( "new sheet" );HSSFRow row = sheet1.createRow( (short) 1 );HSSFRow row2 = sheet1.createRow( (short) 2 );HSSFCell cell = row.createCell( (short) 1 );cell.setCellValue( "This is a test of merging" );Region region = new Region( 1, (short) 1, 4, (short) 4 );sheet1.addMergedRegion( region );final short borderMediumDashed = HSSFCellStyle.BORDER_MEDIUM_DASHED; HSSFRegionUtil.setBorderBottom( borderMediumDashed,region, sheet1, wb );HSSFRegionUtil.setBorderTop( borderMediumDashed,region, sheet1, wb );HSSFRegionUtil.setBorderLeft( borderMediumDashed,region, sheet1, wb );HSSFRegionUtil.setBorderRight( borderMediumDashed,region, sheet1, wb );HSSFRegionUtil.setBottomBorderColor(HSSFColor.AQUA.index, region, sheet1, wb); HSSFRegionUtil.setTopBorderColor(HSSFColor.AQUA.index, region, sheet1, wb); HSSFRegionUtil.setLeftBorderColor(HSSFColor.AQUA.index, region, sheet1, wb); HSSFRegionUtil.setRightBorderColor(HSSFColor.AQUA.index, region, sheet1, wb); HSSFCellStyle style = wb.createCellStyle();style.setIndention((short)4);HSSFCellUtil.createCell(row, 8, "This is the value of the cell", style);HSSFCell cell2 = HSSFCellUtil.createCell( row2, 8, "This is the value of the cell"); HSSFCellUtil.setAlignment(cell2, wb, HSSFCellStyle.ALIGN_CENTER);FileOutputStream fileOut = new FileOutputStream( "workbook.xls" );wb.write( fileOut );fileOut.close();19.在工作单中移动行，调整行的上下位置HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet("row sheet");sheet.shiftRows(5, 10, -5);FileOutputStream fileOut = new FileOutputStream("workbook.xls");wb.write(fileOut);fileOut.close();20.选种指定的工作单HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet("row sheet");sheet.setSelected(true);FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut);fileOut.close();21.工作单的放大缩小HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet1 = wb.createSheet("new sheet");sheet1.setZoom(3,4);FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut);fileOut.close();22.头注和脚注HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet("new sheet");HSSFHeader header = sheet.getHeader();header.setCenter("Center Header");header.setLeft("Left Header");header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16"); FileOutputStream fileOut = new FileOutputStream("workbook.xls");wb.write(fileOut);fileOut.close();23.图片的使用//先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();BufferedImage bufferImg = ImageIO.read(new File("ok.jpg"));ImageIO.write(bufferImg,"jpg",byteArrayOut);//读进一个excel模版FileInputStream fos = new FileInputStream(filePathName+"/stencil.xlt");fs = new POIFSFileSystem(fos);//创建一个工作薄HSSFWorkbook wb = new HSSFWorkbook(fs);HSSFSheet sheet = wb.getSheetAt(0);HSSFPatriarch patriarch = sheet.createDrawingPatriarch();HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10);patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG)); //先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArrayByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();BufferedImage bufferImg = ImageIO.read(new File("ok.jpg"));ImageIO.write(bufferImg,"jpg",byteArrayOut);//读进一个excel模版FileInputStream fos = new FileInputStream(filePathName+"/stencil.xlt");fs = new POIFSFileSystem(fos);//创建一个工作薄HSSFWorkbook wb = new HSSFWorkbook(fs);HSSFSheet sheet = wb.getSheetAt(0);HSSFPatriarch patriarch = sheet.createDrawingPatriarch();HSSFClientAnchor anchor = new HSSFClientAnchor(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_BLUE62： INDIGO63： GREY_80_PERCENT 53： ORANGE19： DARK_YELLOW 17： GREEN21： TEAL38： TEAL12： BLUE39： BLUE54： BLUE_GREY23： GREY_50_PERCENT 10： RED52： LIGHT_ORANGE 50： LIME57： SEA_GREEN49： AQUA48： LIGHT_BLUE20： VIOLET36： VIOLET55： GREY_40_PERCENT 14： PINK33： PINK51： GOLD13： YELLOW34： YELLOW11： BRIGHT_GREEN35： BRIGHT_GREEN15： TURQUOISE35： TURQUOISE16： DARK_RED37： DARK_RED40： SKY_BLUE61： PLUM25： PLUM22： GREY_25_PERCENT45： ROSE43： LIGHT_YELLOW42： LIGHT_GREEN41： LIGHT_TURQUOISE27：LIGHT_TURQUOISE44： PALE_BLUE46： LAVENDER9： WHITE24： CORNFLOWER_BLUE26： LEMON_CHIFFON25： MAROON28： ORCHID29： CORAL30： ROYAL_BLUE31： LIGHT_CORNFLOWER_BLUE五、应用实例：<%@ page contentType="text/html; charset=gb2312" language="java" import="java.sql.*" errorPage="" %> <%@ page import="ng.*,computer.*"%><html><head><title>计算机设备管理</title><%GetData gd=new GetData();%></head><form name="form1" action="SaveData.jsp"><table border="0" cellspacing="1" cellpadding="2" width="97%" align="center" bgcolor="#F6F6F6"> <tr><td colspan="5"><div align="center"><img src="images/text01.jpg" align="center"></div></td></tr> <tr><td colspan="5"><hr></td></tr><tr><td width="15%"><div align="right">设备编号</div></td></tr><tr><td colspan="5"><div align="center"><input name="btnDo" type="submit" value="数据提交"> <input name="btnReset" type="reset" value="重新输入"> <input name="btnOutToExcel" type="button" value="导出到Excel" onClick="clickForOutToExcel();"></div></td></tr><tr><td colspan="5"><hr></td></tr></table><input name="Page_Mark" type="hidden" value="Maintain"></form><form name="form2" action="JSPExcel.jsp"><input name="GetOrPut" type="hidden" value=""></form><script language=javascript>function clickForOutToExcel(){document.form2.GetOrPut.value="PutMaintain";alert("维修设备清单成功导出为EXCEL文件！\r\r请查看C盘根目录下的：维修清单.xls"); document.form2.submit();}</script></html>六、处理有EXCEL导入导出要求的JSP文件（JSPExcel.jsp）<%@ page contentType="text/html;charset=gbk"%><%@ page import="ng.*,computer.*"%><html><head></head><body><%String gp=request.getParameter("GetOrPut");String gf=Pool.toChinese(request.getParameter("GFF")); GetOrWriteExcel gwe=new GetOrWriteExcel();if (pareTo("Put")==0) {try{gwe.AllEquipToExcel();%><jsp:forward page="Equip.jsp"><jsp:param name="" value=""/></jsp:forward><%}catch(Exception ep){System.out.println(ep);}}else if (pareTo("Get")==0) {try{gwe.GetEquipFromExcel(gf);%><jsp:forward page="Equip.jsp"><jsp:param name="" value=""/></jsp:forward><%}catch(Exception ep){System.out.println(ep);}}else if (pareTo("PutDis")==0) {try{gwe.DisToExcel();%><jsp:forward page="Distribute.jsp"><jsp:param name="" value=""/></jsp:forward><%}catch(Exception ep){System.out.println(ep);}}else if (pareTo("PutMaintain")==0) {try{gwe.MaintainToExcel();%><jsp:forward page="Maintain.jsp"><jsp:param name="" value=""/></jsp:forward><%}catch(Exception ep){System.out.println(ep);}}else if (pareTo("PutTrash")==0) { try{gwe.TrashToExcel();%><jsp:forward page="Trash.jsp"><jsp:param name="" value=""/></jsp:forward><%}catch(Exception ep){System.out.println(ep);}}else {System.out.println("GETORPUT的值是空的！");}%></body></html>七、Javabean中专门处理EXCEL的导入导出的类：GetOrWriteExcel.java package computer;import java.io.*;import java.sql.Connection;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import ermodel.*;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.poifs.filesystem.POIFSFileSystem;public class GetOrWriteExcel {Pool pl;Connection cn;public GetOrWriteExcel(){try{pl=new Pool();//在办公室通过WEB操作数据库cn=pl.getConnection();//在办公室用Test测试数据库操作//cn=pl.getConnectForTest();//在家测试数据库//cn=pl.getConnectAtHome();if (cn==null){System.out.println("数据库连接为空");}} catch(Exception ex){System.out.println(ex);}}public void AllEquipToExcel(){String sqlStr="select * from Equip_View";String fname="c:\\设备清单.xls";try {DataToExcel(sqlStr,fname);System.out.println("所有设备导入到Excel成功!"); } catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}/** 从Excel中导入设备列表，参数gf表示要导入的excel文件名*/public void GetEquipFromExcel(String gf)throws IOException{try{POIFSFileSystem fs=new POIFSFileSystem(new FileInputStream(gf));HSSFWorkbook wb=new HSSFWorkbook(fs);HSSFSheet sheet=wb.getSheetAt(0);HSSFRow row;int count=0;count=sheet.getPhysicalNumberOfRows();//数据库操作try{cn.setAutoCommit(false);Statement st=cn.createStatement();String sqlStr="";for (int i=1;i<count;i++){row=sheet.getRow(i);sqlStr="INSERT INTO Equip(EquipID,Price,BuyDate,ETypeID,DealerID,CPU,HD,MEMORY,DISPLAY,MAINBOARD,EState,Other,Remark,Spec)VALUES(";sqlStr += "'";sqlStr += row.getCell((short)0).toString().trim();sqlStr += "','";sqlStr += Float.parseFloat(row.getCell((short)1).toString()); sqlStr += "','";sqlStr += row.getCell((short)2).toString().substring(0, 10); sqlStr += "','";sqlStr += row.getCell((short)3).toString().trim();sqlStr += "','";sqlStr += row.getCell((short)4).toString().trim();sqlStr += "','";sqlStr += row.getCell((short)5).toString().trim();sqlStr += "','";sqlStr += row.getCell((short)6).toString().trim();sqlStr += "','";sqlStr += row.getCell((short)7).toString().trim();sqlStr += "','";sqlStr += row.getCell((short)8).toString().trim();sqlStr += "','";sqlStr += row.getCell((short)9).toString().trim();sqlStr += "','";sqlStr += row.getCell((short)10).toString().trim();sqlStr += "','";sqlStr += row.getCell((short)11).toString().trim();sqlStr += "','";sqlStr += row.getCell((short)12).toString().trim();sqlStr += "','";sqlStr += row.getCell((short)13).toString().trim();sqlStr += "')";//System.out.println(sqlStr);st.addBatch(sqlStr);//st.execute(sqlStr);//System.out.println(sqlStr);}st.executeBatch();mit();cn.setAutoCommit(true);st.close();System.out.println("从Excel中成功批量导入设备!");}catch(Exception se){System.out.println("向Equip表中批量存入数据时出错，错误代码：" + se); }}catch (FileNotFoundException e) {e.printStackTrace();}}public void DisToExcel(){String sqlStr="select * from Distribute_View";String fname="c:\\领用清单.xls";try {DataToExcel(sqlStr,fname);System.out.println("所有领用设备导入到Excel成功!"); } catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}public void MaintainToExcel(){String sqlStr="select * from EquipMaintain_View";String fname="c:\\维修清单.xls";try {DataToExcel(sqlStr,fname);System.out.println("所有维修记录导入到Excel成功!"); } catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}public void TrashToExcel(){String sqlStr="select * from Trash_View";String fname="c:\\报废清单.xls";try {DataToExcel(sqlStr,fname);System.out.println("所有维修记录导入到Excel成功!");} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}/** 从数据库将数据导出到EXCEL文件的通用函数，参数sqlStr是传来的sql语句，filename是要保存的文件名与路径 */public void DataToExcel(String sqlStr,String filename) throws IOException{ResultSet rs = null;ResultSetMetaData rsmeta=null;Statement st = null;try{String[] title;HSSFWorkbook wb=new HSSFWorkbook();HSSFSheet sheet=wb.createSheet("数据清单");HSSFRow row=sheet.createRow((short)0);//第一行显示字段名称st=cn.createStatement();rs = st.executeQuery(sqlStr);rsmeta=rs.getMetaData();int numofCols=rsmeta.getColumnCount();title=new String[numofCols];for (int i=0;i<numofCols;i++){title[i]=rsmeta.getColumnName(i+1);row.createCell((short)i).setCellValue(title[i]);}//以下显示数据int k=1;while(rs.next()){row=sheet.createRow((short)k);for (int j=1;j<=numofCols;j++){row.createCell((short)(j-1)).setCellValue(rs.getString(j)); }k++;}FileOutputStream fileOut=new FileOutputStream(filename);wb.write(fileOut);fileOut.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace();} finally {try {rs.close();st.close();} catch (Exception e) {e.printStackTrace();}}}}。
