Package service

Source Code of service.ExcelService

package service;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Map;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import bean.HisTrans;
import bean.Point;

public class ExcelService {

  /*************************************** �������_ʼ ********************************************/
  private static int settle_date = 4;/* �������� */
  private static int pan = 3;/* ���� */
  private static int acc_type = -1;
  private static int txn_name = -1;
  private static int txn_amt = 6;/* ���׽�� */
  private static int fee_amt = 7;/* �̻������� */
  private static int cname = -1;/* �̻� */
  private static int tid = 0;/* �ն˺� */
  private static int sp_mid = -1;
  private static int sp_tid = 0;/* ֧�������ն˺� */
  private static int trace1 = -1;
  private static int rrn = 13;/* ���ײο��� */
  private static int txn_date = 5;/* �������� */

  /*************************************** �������Y�� ********************************************/

  /**
   * ��ȡexcel���������ݣ����ض�ȡ������
   *
   * @param rs
   *            ������
   * @param rowBegin
   *            ��ʼ���кţ�0�_ʼ
   * @param rowEnd
   *            �������кţ�������ԓ��
   * @param colBegin
   *            ��ʼ���кţ�0�_ʼ
   * @param colEnd
   *            �������кţ�������ԓ��
   * @return Point��ʾ��Ԫ�����ˣ�String��ԓ��Ԫ�����
   */
  public Map<Point, String> readExcel(Sheet rs, int rowBegin, int rowEnd, int colBegin, int colEnd) {
    if (rowBegin > rowEnd || rowEnd > rs.getRows() || rowBegin < 0) {
      throw new RuntimeException("���������Ϸ���");
    }
    if (colBegin > colEnd || colEnd > rs.getColumns() || colBegin < 0) {
      throw new RuntimeException("���������Ϸ���");
    }
    Map<Point, String> result = new HashMap<Point, String>();
    int i, j;
    for (i = rowBegin; i < rowEnd; i++) {
      for (j = colBegin; j < colEnd; j++) {
        Cell c = rs.getCell(j, i);
        String s = c.getContents();
        result.put(new Point(j, i), s);
      }
    }
    return result;
  }

  public Map<Point, String> readExcel(String filename) throws BiffException, IOException {
    Sheet rs = getSheet(filename, 0);
    return readExcel(rs, 0, rs.getRows(), 0, rs.getColumns());
  }

  public Sheet getSheet(String filename, int index) throws BiffException, IOException {
    InputStream is = null;
    Workbook rwb = null;
    Sheet rs = null;
    try {
      is = new FileInputStream(filename);
      rwb = Workbook.getWorkbook(is);
      rs = rwb.getSheet(0);
    } finally {
      if (is != null)
        is.close();
    }
    return rs;
  }

  /**
   * ��ȡexcel����������
   *
   * @param filename
   * @param index
   * @param rowBegin
   * @param rowEnd
   * @param colBegin
   * @param colEnd
   * @return
   * @throws BiffException
   * @throws IOException
   */
  public Map<Point, String> readExcel(String filename, int index, int rowBegin, int rowEnd, int colBegin, int colEnd)
      throws BiffException, IOException {
    Sheet rs = getSheet(filename, 0);
    return readExcel(rs, rowBegin, rowEnd, colBegin, colEnd);
  }

  /**
   * ��ȡһ��
   *
   * @param result
   * @param row
   * @return
   */
  public HisTrans getRow(Map<Point, String> result, int row) {
    HisTrans trans = new HisTrans();
    trans.setSettle_date(result.get(new Point(settle_date, row)));
    trans.setPan(result.get(new Point(pan, row)).replaceAll("-", ""));
    trans.setAcc_type(0f);
    trans.setTxn_name("����");
    trans.setTxn_amt(Float.valueOf(result.get(new Point(txn_amt, row)).replaceAll(",", "")));
    trans.setFee_amt(Float.valueOf(result.get(new Point(fee_amt, row)).replaceAll(",", "")));
    trans.setCname("˳�����ˣ����ݣ����޹�˾");
    trans.setTid(result.get(new Point(tid, row)));
    trans.setSp_mid("104481442150002");
    trans.setSp_tid(result.get(new Point(sp_tid, row)));
    trans.setTrace1("0");
    trans.setRrn(result.get(new Point(rrn, row)));
    trans.setTxn_date(result.get(new Point(txn_date, row)).replaceAll("/", ""));
    trans.setArea("����");
    return trans;
  }

  public void writeExcel(Map<Point, String> toWrite, String filename, int rowBegin, int rowEnd, int colBegin,
      int colEnd) throws IOException, RowsExceededException, WriteException {
    OutputStream os = new FileOutputStream(filename);
    WritableWorkbook wwb = Workbook.createWorkbook(os);
    WritableSheet ws = wwb.createSheet("fuck sf", 0);
    int x, y;// д����У�������
    int i, j;
    for (i = rowBegin, x = 0; i < rowEnd; i++, x++) {
      for (j = colBegin, y = 0; j < colEnd; j++, y++) {
        String content = toWrite.get(new Point(j, i));
        if (!isInteger(content) && j == 0) {
          x--;
          break;
        }
        jxl.write.Label labelC = new jxl.write.Label(y, x, content);
        ws.addCell(labelC);
      }
    }
    wwb.write();
    wwb.close();
    os.close();
  }

  public boolean isInteger(String str) {
    boolean result = true;
    try {
      Integer.valueOf(str);
    } catch (Exception e) {
      result = false;
    }
    return result;
  }
}
TOP

Related Classes of service.ExcelService

TOP
Copyright © 2018 www.massapi.com. All rights reserved.
All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.