package poiMain;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Scanner;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class PoiGH3215 {
private static String fileName="";
public static String getCell(int x,int y) throws IOException{
DecimalFormat df = new DecimalFormat("#");
InputStream myxls=new FileInputStream(fileName);
@SuppressWarnings("resource")
XSSFWorkbook wb =new XSSFWorkbook(myxls);
XSSFSheet sheet=wb.getSheetAt(0);//第一个工作表
XSSFRow row =sheet.getRow(x-1);//第x行
XSSFCell cell =row.getCell((short)y-1);//第x行第y个元素
if(cell==null){
return null;
}
String cellValue = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
cellValue = cell.getRichStringCellValue().getString().trim();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else {// 日期
sdf = new SimpleDateFormat("yyyy/MM/dd");
}
Date date = cell.getDateCellValue();
cellValue = sdf.format(date);
} else {
cellValue = df.format(cell.getNumericCellValue()).toString();
}
break;
default:
cellValue = "";
}
return cellValue;
}
public static void ModelGH3215() throws IOException{
@SuppressWarnings("resource")
XSSFWorkbook wb =new XSSFWorkbook();
Date d = new Date();
DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
String fileName = df.format(d);
FileOutputStream fileOut = new FileOutputStream("D:/输出/工行3215/" + fileName + "_main" + ".xls");
XSSFSheet sheet=wb.createSheet();
Font font = wb.createFont();
XSSFCellStyle style = wb.createCellStyle(); //新建单元格样式
font.setFontName("宋体");
font.setFontHeightInPoints((short) 12);//设置字体大小
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//粗体显示
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); //下边框
style.setFont(font);
//设置单元格列宽
sheet.setColumnWidth(0, 100 * 100);
sheet.setColumnWidth(1, 100 * 30);
sheet.setColumnWidth(3, 100 * 30);
sheet.setColumnWidth(4, 100 * 30);
sheet.setColumnWidth(9, 100 * 130);
sheet.setColumnWidth(20, 100 * 40);
XSSFRow row =sheet.createRow((short)0);
XSSFCell cell=row.createCell((short)0);
row.setHeight((short) 700);//设置行高
cell.setCellValue("银行科目");
row.getCell((short)0).setCellStyle(style);
row.createCell((short)1).setCellValue("币别");
row.getCell((short)1).setCellStyle(style);
row.createCell((short)2).setCellValue("序列号");
row.getCell((short)2).setCellStyle(style);
row.createCell((short)3).setCellValue("日期");
row.getCell((short)3).setCellStyle(style);
row.createCell((short)4).setCellValue("业务日期");
row.getCell((short)4).setCellStyle(style);
row.createCell((short)5).setCellValue("凭证字");
row.getCell((short)5).setCellStyle(style);
row.createCell((short)6).setCellValue("凭证号");
row.getCell((short)6).setCellStyle(style);
row.createCell((short)7).setCellValue("凭证年");
row.getCell((short)7).setCellStyle(style);
row.createCell((short)8).setCellValue("凭证期间");
row.getCell((short)8).setCellStyle(style);
row.createCell((short)9).setCellValue("摘要");
row.getCell((short)9).setCellStyle(style);
row.createCell((short)10).setCellValue("对方科目");
row.getCell((short)10).setCellStyle(style);
row.createCell((short)11).setCellValue("结算方式");
row.getCell((short)11).setCellStyle(style);
row.createCell((short)12).setCellValue("结算号");
row.getCell((short)12).setCellStyle(style);
row.createCell((short)13).setCellValue("");
row.getCell((short)13).setCellStyle(style);
row.createCell((short)14).setCellValue("");
row.getCell((short)14).setCellStyle(style);
row.createCell((short)15).setCellValue("汇率");
row.getCell((short)15).setCellStyle(style);
row.createCell((short)16).setCellValue("附件数");
row.getCell((short)16).setCellStyle(style);
row.createCell((short)17).setCellValue("汇率类型");
row.getCell((short)17).setCellStyle(style);
row.createCell((short)18).setCellValue("备注");
row.getCell((short)18).setCellStyle(style);
row.createCell((short)19).setCellValue("对应外币");
row.getCell((short)19).setCellStyle(style);
row.createCell((short)20).setCellValue("对应外币金额");
row.getCell((short)20).setCellStyle(style);
int j = 1;int sum=0;int a=0;int s=getNum();
ArrayList<String> date1 = new ArrayList();
ArrayList<Integer> value1 = new ArrayList();
ArrayList<String> date2 = new ArrayList();
ArrayList<Integer> value2 = new ArrayList();
for(int i=5;i<s+2;i++){
if(PoiGH3215.getCell(i,4).equals("转款")&&(PoiGH3215.getCell(i,8).equals("31001547840050012398"))){
System.out.println("get");
XSSFRow row2 =sheet.createRow((short)j++);
row2.createCell((short)14).setCellValue(getCell(i,5));
row2.createCell((short)13).setCellValue(getCell(i,7));
row2.createCell((short)3).setCellValue(getCell(i,2));//日期
row2.createCell((short)4).setCellValue(getCell(i,2));//日期
row2.createCell((short)0).setCellValue("1021.01.10|工行广州白云路支行（8980）-备汇缴");//第一列
row2.createCell((short)9).setCellValue("收转款，建行怒江2398转入工行侨香");//摘要
}else if(PoiGH3215.getCell(i,4).equals("收付")&&(!PoiGH3215.getCell(i,5).equals(PoiGH3215.getCell(i,6)))){
XSSFRow row3 =sheet.createRow((short)j);
row3.createCell((short)9).setCellValue("付移动手机充值款");
row3.createCell((short)13).setCellValue(getCell(i,7));//添加值
row3.createCell((short)14).setCellValue(getCell(i,5));//添加值
row3.createCell((short)3).setCellValue(getCell(j,2));
row3.createCell((short)4).setCellValue(getCell(j,2));
row3.createCell((short)0).setCellValue("1021.01.10|工行广州白云路支行（8980）-备汇缴");
row3.createCell((short)14).setCellValue(getCell(i,5));
row3.createCell((short)9).setCellValue("收还款退款");//摘要
} else if(PoiGH3215.getCell(i,4).equals("收付")&&(PoiGH3215.getCell(i,5).equals(PoiGH3215.getCell(i,6)))){
XSSFRow row3 =sheet.createRow((short)j);
row3.createCell((short)9).setCellValue("付移动手机充值款");
row3.createCell((short)13).setCellValue(getCell(i,7));
row3.createCell((short)14).setCellValue(getCell(i,5));//添加值
row3.createCell((short)3).setCellValue(getCell(j,2));
row3.createCell((short)4).setCellValue(getCell(j,2));
row3.createCell((short)0).setCellValue("1021.01.10|工行广州白云路支行（8980）-备汇缴");
row3.createCell((short)14).setCellValue(getCell(i,5));
row3.createCell((short)9).setCellValue("收收付");//摘要
} else if(PoiGH3215.getCell(i,4).equals("拉卡拉代付款")&&(!PoiGH3215.getCell(i,9).equals("拉卡拉支付有限公司客户备付金"))&&(!PoiGH3215.getCell(i,9).equals("拉卡拉支付有限公司（客户备付金）"))&&(!PoiGH3215.getCell(i,9).equals("拉卡拉网络技术有限公司北京分公司的数（合计数）"))){
//System.out.println(i);
date1.add(PoiGH3215.getCell(i,2));
value1.add(Integer.parseInt(PoiGH3215.getCell(i,5)));
}
}
for(int c=0;c<date1.size();c++){
try {
if(date1.get(c).equals(date1.get(c+1))){
a=value1.get(c);
sum=a+sum;
}else {
a= value1.get(c);
sum=a+sum;
date2.add(date1.get(c));
value2.add(sum);
sum=0;
}
} catch (Exception e) {
a=value1.get(c);
sum=a+sum;
date2.add(date1.get(c));
value2.add(sum);
}
}
for(int k=0;k<date2.size();k++){
XSSFRow row3 =sheet.createRow((short)j+k);
row3.createCell((short)14).setCellValue(value2.get(k));
row3.createCell((short)9).setCellValue("付代付款");
row3.createCell((short)3).setCellValue(date2.get(k));
row3.createCell((short)4).setCellValue(date2.get(k));
row3.createCell((short)0).setCellValue("1021.01.10|工行广州白云路支行（8980）-备汇缴");
}
for(int m=1;m<j+date2.size();m++){
XSSFRow row2 =sheet.getRow((short)m);
row2.createCell((short)1).setCellValue("RMB|人民币");
row2.createCell((short)15).setCellValue("1");
row2.createCell((short)16).setCellValue("0");
row2.createCell((short)17).setCellValue("01|公司汇率");
row2.createCell((short)20).setCellValue("0");
}
wb.write(fileOut);
fileOut.close();
System.out.println("输入完毕");
}
public static int getNum() throws IOException{
synchronized (fileName) {
while("".equals(fileName)){
System.out.println("请输入您的文件路径：");
Scanner sc = new Scanner(System.in);
fileName = sc.nextLine();
sc.close();
}
}
InputStream myxls=new FileInputStream(fileName);
@SuppressWarnings("resource")
XSSFWorkbook wb =new XSSFWorkbook(myxls);
XSSFSheet sheet=wb.getSheetAt(0);//第一个工作表
return sheet.getLastRowNum();
}
}
我要处理的表有6万多行，我这个处理一个200行的都要1分钟
