文件的导入导出整理

一、pdf导出：

OutputStream os = response.getOutputStream();// 取得输出流
 
  String s = namingMethod(opt1,startTime1,startTime,endTime1,endTime,depart1);
  s = s.concat(".pdf");
  response.reset();                // 清空输出流
  response.setHeader("Content-disposition", s); //设定输出文件头,该方法有两个参数，分别表示应答头的名字和值。
  response.setContentType("application/msexcel");
  /**********生成PDF文档********/
  Document document = new Document();// 建立一个Document对象  
  document.setPageSize(PageSize.A2);// 设置页面大小

    try {

     PdfWriter.getInstance(document, os);// 建立一个PdfWriter对象
     document.open();
     BaseFont bfChinese = BaseFont.createFont("STSong-Light",
       "UniGB-UCS2-H", BaseFont.NOT_EMBEDDED);// 设置中文字体
     Font titleFont = new Font(bfChinese, 15, Font.BOLD);// 设置字体大小
     Font headFont = new Font(bfChinese, 10, Font.BOLD);// 设置字体大小
     Font headFont1 = new Font(bfChinese, 10, Font.BOLD);// 设置字体大小
     Font headFont2 = new Font(bfChinese, 10, Font.NORMAL);// 设置字体大小
     document.add(new Paragraph("会议室使用情况导出",headFont));
    
     float[] widths = { 350f,500f,350f,350f,350f,450f,350f,500f,500f };// 设置表格的列宽
    
     PdfPTable table = new PdfPTable(widths);// 建立一个pdf表格

     table.setSpacingBefore(20f);// 设置表格上面空白宽度
     table.setTotalWidth(1000);// 设置表格的宽度
     table.setLockedWidth(true);// 设置表格的宽度固定
     table.getDefaultCell().setBorder(1);//设置表格默认为边框1

     PdfPCell cell = new PdfPCell(new Paragraph("预定部门",headFont));// 建立一个单元格
     cell.setFixedHeight(20);
     cell.setColspan(1);// 设置合并单元格的列数
     cell.setHorizontalAlignment(Element.ALIGN_CENTER);// 设置内容水平居中显示
     cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
     table.addCell(cell);// 增加单元格
    

     cell = new PdfPCell( new Paragraph("会议耗材", headFont));
     cell.setFixedHeight(20);
     cell.setColspan(1);// 设置合并单元格的列数
     cell.setHorizontalAlignment(Element.ALIGN_CENTER);// 设置内容水平居中显示
     cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
     table.addCell(cell);

     cell = new PdfPCell(new Paragraph("办公楼名称", headFont));
     cell.setFixedHeight(20);
     cell.setHorizontalAlignment(Element.ALIGN_CENTER);// 设置内容水平居中显示
     cell.setVerticalAlignment(Element.ALIGN_MIDDLE);    
     table.addCell(cell);

     cell = new PdfPCell(new Paragraph("会议室名称", headFont));
     cell.setFixedHeight(20);
     cell.setHorizontalAlignment(Element.ALIGN_CENTER);// 设置内容水平居中显示
     cell.setVerticalAlignment(Element.ALIGN_MIDDLE);    
     table.addCell(cell);
    
    
     cell = new PdfPCell(new Paragraph("预定人姓名", headFont));
     cell.setFixedHeight(20);  
     cell.setHorizontalAlignment(Element.ALIGN_CENTER);// 设置内容水平居中显示
     cell.setVerticalAlignment(Element.ALIGN_MIDDLE);    
     table.addCell(cell);
    
     cell = new PdfPCell(new Paragraph("预定人邮箱", headFont));
     cell.setFixedHeight(20);  
     cell.setHorizontalAlignment(Element.ALIGN_CENTER);// 设置内容水平居中显示
     cell.setVerticalAlignment(Element.ALIGN_MIDDLE);    
     table.addCell(cell);
    
     cell = new PdfPCell(new Paragraph("预定人电话", headFont));
     cell.setFixedHeight(20);  
     cell.setHorizontalAlignment(Element.ALIGN_CENTER);// 设置内容水平居中显示
     cell.setVerticalAlignment(Element.ALIGN_MIDDLE);    
     table.addCell(cell);
    
    
     cell = new PdfPCell(new Paragraph("预定起始时间", headFont));
     cell.setFixedHeight(20);  
     cell.setHorizontalAlignment(Element.ALIGN_CENTER);// 设置内容水平居中显示
     cell.setVerticalAlignment(Element.ALIGN_MIDDLE);    
     table.addCell(cell);
    
    
     cell = new PdfPCell(new Paragraph("预定结束时间", headFont));
     cell.setFixedHeight(20);  
     cell.setHorizontalAlignment(Element.ALIGN_CENTER);// 设置内容水平居中显示
     cell.setVerticalAlignment(Element.ALIGN_MIDDLE);    
     table.addCell(cell);
    /********创建表格数据******/
     for (int i = 0; i < bev.size(); i++) {
      cell = new PdfPCell(new Paragraph(bev.get(i).getCRBDepartment(),headFont1));// 建立一个单元格
      cell.setFixedHeight(20);
      cell.setColspan(1);// 设置合并单元格的列数
      cell.setHorizontalAlignment(Element.ALIGN_CENTER);// 设置内容水平居中显示
      cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
      table.addCell(cell);// 增加单元格
     
     
      cell = new PdfPCell( new Paragraph(bev.get(i).getCRBConsume(), headFont1));
      cell.setFixedHeight(20);
      cell.setColspan(1);// 设置合并单元格的列数
      cell.setHorizontalAlignment(Element.ALIGN_LEFT);// 设置内容水平居中显示
      cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
      table.addCell(cell);
     
     
      cell = new PdfPCell( new Paragraph(bev.get(i).getBDName(), headFont1));
      cell.setFixedHeight(20);
      cell.setColspan(1);// 设置合并单元格的列数
      cell.setHorizontalAlignment(Element.ALIGN_CENTER);// 设置内容水平居中显示
      cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
      table.addCell(cell);
     
     
      cell = new PdfPCell( new Paragraph(bev.get(i).getCRBName(), headFont1));
      cell.setFixedHeight(20);
      cell.setColspan(1);// 设置合并单元格的列数
      cell.setHorizontalAlignment(Element.ALIGN_CENTER);// 设置内容水平居中显示
      cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
      table.addCell(cell);
     
      cell = new PdfPCell( new Paragraph(bev.get(i).getCRBName(), headFont1));
      cell.setFixedHeight(20);
      cell.setColspan(1);// 设置合并单元格的列数
      cell.setHorizontalAlignment(Element.ALIGN_CENTER);// 设置内容水平居中显示
      cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
      table.addCell(cell);
     
     
      cell = new PdfPCell( new Paragraph(bev.get(i).getCRBAddress(), headFont1));
      cell.setFixedHeight(20);
      cell.setColspan(1);// 设置合并单元格的列数
      cell.setHorizontalAlignment(Element.ALIGN_CENTER);// 设置内容水平居中显示
      cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
      table.addCell(cell);
     
      cell = new PdfPCell( new Paragraph(bev.get(i).getCRBPhone(), headFont1));
      cell.setFixedHeight(20);
      cell.setColspan(1);// 设置合并单元格的列数
      cell.setHorizontalAlignment(Element.ALIGN_CENTER);// 设置内容水平居中显示
      cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
      table.addCell(cell);
  
    
    
      cell = new PdfPCell( new Paragraph(bev.get(i).getCRBDateStart(), headFont1));
      cell.setFixedHeight(20);
      cell.setColspan(1);// 设置合并单元格的列数
      cell.setHorizontalAlignment(Element.ALIGN_CENTER);// 设置内容水平居中显示
      cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
      table.addCell(cell);
     
    
      cell = new PdfPCell( new Paragraph(bev.get(i).getCRBDateEnd(), headFont1));
      cell.setFixedHeight(20);
      cell.setColspan(1);// 设置合并单元格的列数
      cell.setHorizontalAlignment(Element.ALIGN_CENTER);// 设置内容水平居中显示
      cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
      table.addCell(cell);     
   
   }    

     document.add(table);

    } catch (DocumentException de) {

     System.err.println(de.getMessage());  

    }
    catch (IOException ioe) {
     System.err.println(ioe.getMessage());  

    }

    document.close();

二、excel导出：

String s = namingMethod(opt1,startTime1,startTime,endTime1,endTime,depart1);
  s = s.concat(".xls");
  OutputStream os = response.getOutputStream();         // 取得输出流
  response.reset();                // 清空输出流
  response.setHeader("Content-disposition",s ); //设定输出文件头,该方法有两个参数，分别表示应答头的名字和值。
  response.setContentType("application/msexcel");
 
 

  HSSFWorkbook workbook = new HSSFWorkbook();
  // 在Excel 工作簿中建一工作表
  HSSFSheet sheet = workbook.createSheet("会议室预定信息");
  // 在索引0的位置创建行（第一行）
  HSSFFont font = workbook.createFont(); // 创建一个字体对象
  font.setBoldweight((short) 12);
  font.setFontHeightInPoints((short) 12);
  font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
  HSSFCellStyle cellstyle = workbook.createCellStyle(); // 创建一个格式
  cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
  cellstyle.setFont(font);
  cellstyle.setFillForegroundColor((short) HSSFColor.SKY_BLUE.index);// 设置背景色
  cellstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
  // 设置第一列的值
  HSSFRow row = sheet.createRow((short) 0);
  HSSFCell cell0 = createCell(workbook, row, (short) 0, "预定部门");
  cell0.setCellStyle(cellstyle);
  HSSFCell cell1 = createCell(workbook, row, (short) 1, "会议耗材");
  cell1.setCellStyle(cellstyle);
  HSSFCell cell2 = createCell(workbook, row, (short) 2, "办公楼名称");
  cell2.setCellStyle(cellstyle);
  HSSFCell cell3 = createCell(workbook, row, (short) 3, "会议室名称");
  cell3.setCellStyle(cellstyle);
  HSSFCell cell4 = createCell(workbook, row, (short) 4, "预订人姓名");
  cell4.setCellStyle(cellstyle);
  HSSFCell cell5 = createCell(workbook, row, (short) 5, "预订人邮箱");
  cell5.setCellStyle(cellstyle);
  HSSFCell cell6 = createCell(workbook, row, (short) 6, "预订人电话");
  cell6.setCellStyle(cellstyle);
  HSSFCell cell7 = createCell(workbook, row, (short) 7, "预定起始时间");
  cell7.setCellStyle(cellstyle);
  HSSFCell cell8 = createCell(workbook, row, (short) 8, "预定结束时间");
  cell8.setCellStyle(cellstyle);

  // 设置列宽
  sheet.setColumnWidth((short) 0, (short) 8000);
  sheet.setColumnWidth((short) 1, (short) 8000);
  sheet.setColumnWidth((short) 2, (short) 8000);
  sheet.setColumnWidth((short) 3, (short) 8000);
  sheet.setColumnWidth((short) 4, (short) 8000);
  sheet.setColumnWidth((short) 5, (short) 8000);
  sheet.setColumnWidth((short) 6, (short) 8000);
  sheet.setColumnWidth((short) 7, (short) 8000);
  sheet.setColumnWidth((short) 8, (short) 8000);

  // 创建文件
  SimpleDateFormat sp=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

  // 学生信息分布
  for (int i = 0; i < bev.size(); i++) {
   HSSFRow nextrow = sheet.createRow((short) i + 1);

   createCell(workbook, nextrow, (short) 0, bev.get(i)
     .getCRBDepartment());
   createCell(workbook, nextrow, (short) 1, bev.get(i).getCRBConsume());
   createCell(workbook, nextrow, (short) 2, bev.get(i).getBDName());
   createCell(workbook, nextrow, (short) 3, bev.get(i).getCRName());
   createCell(workbook, nextrow, (short) 4, bev.get(i).getCRBName());
   createCell(workbook, nextrow, (short) 5, bev.get(i).getCRBAddress());
   createCell(workbook, nextrow, (short) 6, bev.get(i).getCRBPhone());  
    createCell(workbook, nextrow, (short)7, bev.get(i).getCRBDateStart());  
    createCell(workbook, nextrow, (short)8, bev.get(i).getCRBDateEnd());
  }
  //FileOutputStream os;
  try {
   //os = new FileOutputStream(filePath);
   workbook.write(os);
   os.flush();
   os.close();
  } catch (FileNotFoundException e1) {
   // TODO Auto-generated catch block
   e1.printStackTrace();
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }

/***此是生成单元格的方法****/

public HSSFCell createCell(HSSFWorkbook workbook, HSSFRow row, int index,
   String value) {
  HSSFCell cell = row.createCell(index);
  cell.setCellValue(value);
  HSSFCellStyle cellstyle = workbook.createCellStyle();
  cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  cell.setCellStyle(cellstyle);
  return cell;
 }

三、导入excel文件：

public ActionForward readFile(ActionMapping mapping, ActionForm form, HttpServletRequest request,
   HttpServletResponse response) throws URISyntaxException, UnsupportedEncodingException {
   FormFile file;
 
   /*************读取文件*******************/
  CRFileForm crfileForm = (CRFileForm) form; 
  file = crfileForm.getCrfile(); //获得文件
  String fileName1 = file.getFileName();
  String fileName = new String(fileName1.getBytes("ISO-8859-1"),"GBK");//获得文件名，防止中文乱码
  InputStream stream;
  Workbook wookBook =null;//定义工作薄
  try {
   stream = file.getInputStream();
  
   if(fileName.toUpperCase().endsWith(".XLSX"))
   {
    wookBook = new XSSFWorkbook(stream);
   }
   else
   {
    wookBook = new HSSFWorkbook(stream);
   }
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }//获得输入流
 
 
  int allSheets = wookBook.getNumberOfSheets();//工作表总数 
  request.setAttribute("crNumberOfSheet", allSheets);
 
  List<String> sheetNameList = new ArrayList<String>();
  for(int i = 0;i < allSheets;i++)
  {
   sheetNameList.add(wookBook.getSheetName(i));//取得工作表的名称列表
   Sheet sheet = wookBook.getSheetAt(i);
   int allRows = sheet.getLastRowNum()+1;
   List<String[]> datas = new ArrayList<String[]>();
   for (int j = 0; j < allRows; j++){   
    Row row = sheet.getRow(j);
    String[] data = cellToString(row);
    datas.add(data);
   }
   request.getSession().setAttribute("sheetList"+i, datas);//把每个表的list放在session
  } 
  request.getSession().setAttribute("crallSheetNames1", sheetNameList);
  request.setAttribute("crallSheetNames", sheetNameList);
  return mapping.findForward("success");
 
 }
 /**
  * 把每行的数据转化为字符串数组
  * @param row
  * @return
  */
 public String[] cellToString(Row row) {
  if (null != row)
  {
   int size = row.getLastCellNum();
   if(size == -1){
    String[] strings = new String[]{" "," "," "," "," "," "," "," "," "," "};
    return strings;
   }
   String[] strings = new String[size];
   int flag = 0;
   for (int i = 0; i < size; i++)
   {  
    flag++;
    if(flag == 10)
    {
     break;
    }
    Cell cell = row.getCell(i);
    if (null != cell)
    {
     switch (cell.getCellType())
     {
      case Cell.CELL_TYPE_NUMERIC:
       strings[i] = NumberFormat.getNumberInstance().format(cell.getNumericCellValue());
       while(strings[i].indexOf(",")>-1){
        strings[i] = strings[i].substring(0,strings[i].indexOf(","))+strings[i].substring(strings[i].indexOf(",")+1);
          }
       break;  
      case Cell.CELL_TYPE_STRING:
       strings[i] = cell.getStringCellValue();
       break;  
      case Cell.CELL_TYPE_BOOLEAN:
       strings[i] = String.valueOf(cell.getBooleanCellValue());
       break;  
      case Cell.CELL_TYPE_FORMULA:
       //strings[i] = String.valueOf(cell.getCellFormula());
       try{
        strings[i] = String.valueOf(cell.getNumericCellValue());
       }catch(IllegalStateException e){
        try{
         strings[i] = String.valueOf(cell.getRichStringCellValue());
        }catch(IllegalStateException ie){
         strings[i] = String.valueOf(cell.getErrorCellValue());
        }
       }
       break;  
      case Cell.CELL_TYPE_BLANK:
       strings[i] = " ";
       break;  
      case Cell.CELL_TYPE_ERROR:
       strings[i] = " ";
       break;  
      default:
       strings[i] = " ";
       break;  
     }  
    }
   }
   return strings;
  }else{
   String[] strings = new String[]{" "," "," "," "," "," "," "," "," "," "};
   return strings;
  }
 }