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);
 }

}