package export.excel; import java.io.BufferedInputStream; import java.io.BufferedOutputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.OutputStream; import java.util.ArrayList; import java.util.List; import java.util.Random; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import jxl.format.Alignment; import jxl.format.VerticalAlignment; import jxl.write.Label; import jxl.write.WritableCellFormat; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import export.vo.PersonVo; /**导出excel文件  *  * @author 路人甲  *  */ public class ExportExcel {    public List<PersonVo> personVoList;  public ExportExcel()  {   initdata();  }  /**   * 初始化数据   */  public void initdata()  {   // 随机函数   Random random = new Random();   personVoList = new ArrayList<PersonVo>();   // 初始化100个人进集合   PersonVo personVo = null;   for (int i = 0; i < 100; i++)   {    personVo = new PersonVo();    personVo.setId("" + i);    personVo.setName("路人" + i);    if (i%2==0)    {     personVo.setSex("男");    }    else    {     personVo.setSex("女");    }    // 取100岁以内的随机年龄    personVo.setAge("" + random.nextInt(100));    personVoList.add(personVo);   }  }  /**导出数据以Excel格式导出   * exportName 导出文件名称   * @param exportName   */  public void exportPerson(String exportName)  {   try{    File excelFile = new File(exportName + "Temp.xls");    // 文件格式    WritableCellFormat format = new WritableCellFormat();    // x  靠左    format.setAlignment(Alignment.RIGHT);    // y 靠顶    format.setVerticalAlignment(VerticalAlignment.TOP);    // 创建一个工作文件    WritableWorkbook writableWorkbook = Workbook.createWorkbook(excelFile);    // 创建一个工作簿    WritableSheet sheet1 = writableWorkbook.createSheet("个人信息列表", 0);    //创建行数 设置行的宽度    sheet1.setColumnView(0, 10);    sheet1.setColumnView(1, 10);    sheet1.setColumnView(2, 10);    sheet1.setColumnView(3, 10);    // 设置行的值    sheet1.addCell(new Label(0, 0, "ID", format));    sheet1.addCell(new Label(1, 0, "姓名", format));    sheet1.addCell(new Label(2, 0, "性别", format));    sheet1.addCell(new Label(3, 0, "年龄", format));    // 把集合写入到excel中    int rowNum = 1;    for (PersonVo bean : personVoList)    {     int colspanNum = 0;     sheet1.addCell(new Label(colspanNum, rowNum, bean.getId(), format));     sheet1.addCell(new Label(colspanNum++, rowNum, bean.getName(), format));     sheet1.addCell(new Label(colspanNum++, rowNum, bean.getSex(), format));     sheet1.addCell(new Label(colspanNum++, rowNum, bean.getAge(), format));     rowNum++;    }    writableWorkbook.write();    writableWorkbook.close(); //   如果是在网络下载的，那么就写这些 //            super.getHttpServletResponse().setContentType("application/x-msdownload"); //            String encodetittle = new String(excelName.getBytes("GBK"), "ISO-8859-1"); //            super.getHttpServletResponse().addHeader("Content-Disposition","attachment;filename="+encodetittle+".xls");             FileInputStream finput = new FileInputStream(excelFile); //            OutputStream output = super.getHttpServletResponse().getOutputStream();             File fout = new File(exportName + ".xls");             OutputStream output = new FileOutputStream(fout);             BufferedInputStream buffin = new BufferedInputStream(finput);             BufferedOutputStream buffout = new BufferedOutputStream(output);             byte[] buffer = new byte[4096];             int count = 0;             while ((count = buffin.read(buffer, 0, buffer.length)) > 0) {                 buffout.write(buffer, 0, count);             }             buffin.close();             buffout.close();             finput.close();             output.close();             excelFile.delete();   }   catch (Exception e) {    e.printStackTrace();   }   finally   {    System.out.println("完成导出操作");   }  }  /**导入数据以Excel格式导出   *   * @param importExcel 导入Excel文件名称   */  public void importPerson(String importExcel)  {   try{    File excelFile = new File(importExcel+".xls");    // 创建一个工作文件    Workbook workbook = Workbook.getWorkbook(excelFile);    // 获得第一个工作簿 这里有两种方法获取sheet表,1为名字，而为下标，从0开始    Sheet sheet = workbook.getSheet(0);    // 总记录数    int allRow = sheet.getRows();    int allColspan = sheet.getColumns();    System.out.println(allRow);    System.out.println(allColspan);    // 取数据    for (int i=0; i<allRow; i++)    {     // 取出每一列的值     for (int j=0; j<allColspan; j++)     { //     sheet.getCell(列数, 行数);      Cell cell = sheet.getCell(j, i);      // 打印出该列的值      System.out.print(cell.getContents() + "\t");     }     System.out.println();    }    //关闭    workbook.close();   }catch (Exception e) {    e.printStackTrace();   }   finally   {    System.out.println("完成导入操作");   }  }  /**   * @param args   */  public static void main(String[] args) {   ExportExcel exportExcel = new ExportExcel();   // 设置路径   String srcPath = "C:/Quarantine/PersonVo"; //  导出 //  exportExcel.exportPerson(srcPath);   // 读取   exportExcel.importPerson(srcPath);  } }