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