JSP 后台导出Excel

主要的思想就是：先设置路径以及导出excel表格名字，要有一个模板excel（导出的excel表格是以这个为模板的，就是模板上基本的名字合并单元格都已经弄好了，我们只要进行插值就好了），然后通过一个类方法在模板上插值。




需要三个包：poi-3.0.2-FINAL-20080204.jar
    poi-contrib-3.0.2-FINAL-20080204.jar
    poi-scratchpad-3.0.2-FINAL-20080204.jar


excel类（最主要的功能类）

public void exportExcel(String s , int x , int y , OutputStream out) throws FileNotFoundException, IOException{POIFSFileSystemfs = new POIFSFileSystem(new FileInputStream("model/xcbg.xls"));//这里是设置需要参考的模板excel，特别重要！一切的填写都是按照这个模板填写的，最后生成的excel也是按照这个模板生成的//只不过比起模板，我们生成的新excel会加上我们自己的信息HSSFWorkbook workbook = new HSSFWorkbook(fs);HSSFSheet sheet = workbook.getSheetAt(0);HSSFRow row = sheet.getRow(x-1);//HSSFCell cell = row.getCell((short)0);HSSFCell cell = row.createCell((short)(y-1));cell.setCellValue(s);HSSFCellStyle cellStyle = cell.getCellStyle();try{workbook.write(out);//客户端下载该excel}catch (IOException e) {// TODO: handle exceptione.printStackTrace();}}//这个方法是单独对一块没有规律的excel填写用的，s是填写的信息 ，例如 ：你好；x，y分别表示第几行第几列（不是从0开始）；out表示写入哪个文件里去。//public void exportExcelMain (int xStart , Collection<T> dataset ,OutputStream out) throws FileNotFoundException, IOException{POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("model/xcbg.xls"));HSSFWorkbook workbook = new HSSFWorkbook(fs);HSSFSheet sheet = workbook.getSheetAt(0);HSSFCellStyle cellStyle = workbook.createCellStyle();HSSFDataFormat format = workbook.createDataFormat();cellStyle.setDataFormat(format.getFormat("@"));cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);cellStyle.setWrapText(true);System.out.println(xStart);Iterator<T> it = dataset.iterator();int index = 1;while(it.hasNext()) {index++;HSSFRow row = sheet.getRow(xStart-1);T t = (T) it.next();Field[] fields = t.getClass().getDeclaredFields();for(short i=0; i<fields.length; i++){HSSFCell cell = row.createCell(i);cell.setCellStyle(cellStyle);Field field = fields[i];String filedName = field.getName();String getMethodName = "get" + filedName.substring(0,1).toUpperCase()+ filedName.substring(1);try{Class tCls = t.getClass();Method getMethod = tCls.getMethod(getMethodName, new Class[]{});Object value = getMethod.invoke(t , new Object[]{});String textValue = null;textValue = value.toString();if (textValue != null) {                    Pattern p = Pattern.compile("^//d+(//.//d+)?{1}quot;");                    Matcher matcher = p.matcher(textValue);                    if (matcher.matches()) {                        // 锟斤拷锟斤拷锟街碉拷锟斤拷double锟斤拷锟斤拷                        cell.setCellValue(Double.parseDouble(textValue));                    } else {                        HSSFRichTextString richString = new HSSFRichTextString(                                textValue);                        HSSFFont font3 = workbook.createFont();                        font3.setColor(HSSFColor.BLUE.index);                        richString.applyFont(font3);                        cell.setCellValue(richString);                    }                }}catch (SecurityException e) {// TODO: handle exceptione.printStackTrace();}catch (NoSuchMethodException e) {// TODO: handle exceptione.printStackTrace();}catch (IllegalArgumentException e) {// TODO: handle exceptione.printStackTrace();}catch (IllegalAccessException e) {// TODO: handle exceptione.printStackTrace();}catch (InvocationTargetException e) {// TODO: handle exceptione.printStackTrace();}}xStart++;}try{workbook.write(out);}catch (IOException e) {// TODO: handle exceptione.printStackTrace();}}

如何调用上面的功能类？

如下：

public class FillXCBG{public static void main(String[] args) {excel<Xcbg> ex = new excel<Xcbg>();//这里需要特别注意！用到excel里的第二个方法时，dataset里装的哪个类，这里也是哪个类int xStart = 8;ArrayList<Xcbg> dataset = new ArrayList<Xcbg>();dataset.add(new Xcbg("地fang",0,0,1,0,1,0,"处理方法","使用药物","建议"));dataset.add(new Xcbg("网卡阿卡",0,0,1,0,1,0,"处理方法","使用药物","建议"));try{OutputStream out = new FileOutputStream("excel\\test.xls");//这里是确定你要生成的excel是在哪个目录下的，并且想要命名为什么//这里表示的意思是：在new File().getAbsolutedPath()获取到的路径下的excel文件夹里的test.xls文件ex.exportExcelMain(xStart, dataset , out);//xStart是excel从第一行开始填写，然后根据dataset里面装的实体有几个属性填写该行里的几列（其中有一个是填充位，我也不知道干什么的）//然后根据dataset有几个元素（实体）这里是add了几个Xcbg类对象 就填写几行}catch(FileNotFoundException e){e.printStackTrace();}catch (IOException e) {// TODO: handle exceptione.printStackTrace();}this.doPost(request, response);}

下面再贴出一个综合上面两个大功能类的方法：

public void exportExcelMain (int xStart , Collection<T> dataset ,ArrayList<XcbgTitle> list,OutputStream out) throws FileNotFoundException, IOException{POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("../webapps/Insecticide/WEB-INF/model/xcbg.xls"));//POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("model/xcbg.xls"));HSSFWorkbook workbook = new HSSFWorkbook(fs);HSSFSheet sheet = workbook.getSheetAt(0);for(int j = 0 ; j < list.size(); j ++){//这里面的就是之前excel的第一个填充不规则地区的方法，通过x，y坐标HSSFRow row = sheet.getRow(list.get(j).getX()-1);//excel也是从0行0列开始的，我们给出来的都是从第一行第一列开始的HSSFCell cell = row.createCell((short)(list.get(j).getY()-1));cell.setCellValue(list.get(j).getString());HSSFCellStyle cellStyle = cell.getCellStyle();}HSSFCellStyle cellStyle = workbook.createCellStyle();HSSFDataFormat format = workbook.createDataFormat();cellStyle.setDataFormat(format.getFormat("@"));cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);cellStyle.setWrapText(true);Iterator<T> it = dataset.iterator();int index = 1;while(it.hasNext()) {index++;HSSFRow row = sheet.getRow(xStart-1);T t = (T) it.next();Field[] fields = t.getClass().getDeclaredFields();for(short i=0; i<fields.length; i++){HSSFCell cell = row.createCell(i);cell.setCellStyle(cellStyle);Field field = fields[i];String filedName = field.getName();String getMethodName = "get" + filedName.substring(0,1).toUpperCase()+ filedName.substring(1);try{Class tCls = t.getClass();Method getMethod = tCls.getMethod(getMethodName, new Class[]{});Object value = getMethod.invoke(t , new Object[]{});String textValue = null;textValue = value.toString();if (textValue != null) {                    Pattern p = Pattern.compile("^//d+(//.//d+)?{1}quot;");                    Matcher matcher = p.matcher(textValue);                    if (matcher.matches()) {                        // �����ֵ���double����                        cell.setCellValue(Double.parseDouble(textValue));                    } else {                        HSSFRichTextString richString = new HSSFRichTextString(                                textValue);                        HSSFFont font3 = workbook.createFont();                        font3.setColor(HSSFColor.BLUE.index);                        richString.applyFont(font3);                        cell.setCellValue(richString);                    }                }}catch (SecurityException e) {// TODO: handle exceptione.printStackTrace();}catch (NoSuchMethodException e) {// TODO: handle exceptione.printStackTrace();}catch (IllegalArgumentException e) {// TODO: handle exceptione.printStackTrace();}catch (IllegalAccessException e) {// TODO: handle exceptione.printStackTrace();}catch (InvocationTargetException e) {// TODO: handle exceptione.printStackTrace();}}xStart++;}try{workbook.write(out);}catch (IOException e) {// TODO: handle exceptione.printStackTrace();}}public void createExcel(OutputStream out) throws IOException{POIFSFileSystemfs = new POIFSFileSystem(new FileInputStream("model/xcbg.xls"));HSSFWorkbook workbook = new HSSFWorkbook(fs);try{workbook.write(out);}catch (IOException e) {// TODO: handle exceptione.printStackTrace();}}