62,614
社区成员
发帖
与我相关
我的任务
分享
package ttt;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* 分批读取提交
*
*/
public class TT4 {
XSSFSheet xssfSheet =null;//Sheet 对象
ArrayList<ArrayList<String>> ans = new ArrayList<ArrayList<String>>();
ArrayList<ArrayList<String>> arr = null;
/**
* 初始化Sheet
*/
public TT4(){
XSSFWorkbook xssfWorkbook = null;
File excelFile = new File("D:\\work\\workWord\\资料\\案例\\","template.xlsx");
InputStream is;
try {
is = new FileInputStream(excelFile);
xssfWorkbook = new XSSFWorkbook(is);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
xssfSheet = xssfWorkbook.getSheetAt(0);
}
public static void main(String[] args) throws IOException {
new TT4().test();
}
/**
* 分批测试
* @throws IOException
*/
public void test() throws IOException{
int lastRowNum = xssfSheet.getLastRowNum();
int excecuteNum = this.testRowNumber(lastRowNum);
for(int i = 0 ;i < excecuteNum; i++){
switch (i){
case 1:
this.printOut(3,1000);
break;
case 2:
this.printOut(1001,2000);
break;
case 3:
this.printOut(2001,3000);
break;
case 4:
this.printOut(3001,4000);
break;
case 5:
this.printOut(4001,5000);
break;
case 6:
this.printOut(5001,6000);
break;
}
}
}
/**
* 打印输出
* @param beginRow
* @param endRow
* @throws IOException
*/
public void printOut(int beginRow,int endRow) throws IOException{
arr=this.xlsx_reader(0,beginRow,endRow,0,8);
ArrayList<String> row = null;
for(int i=0;i<arr.size();i++){
row=arr.get(i);
for(int j=0;j<row.size();j++){
System.out.print(row.get(j)+" ");
}
System.out.println("");
}
ans.clear();
arr.clear();
}
/**xlsx文件读取函数
* @param sheetNumArg 第几个Sheet
* @param rowNumBegin 开始行
* @param rowNumEnd 结束行
* @param columnBegin 开始列
* @param columnEnd 结束列
* @return
* @throws IOException
*/
public ArrayList<ArrayList<String>> xlsx_reader(int sheetNumArg,
int rowNumBegin, int rowNumEnd, int columnBegin, int columnEnd)
throws IOException {
if (xssfSheet == null) {
return null;
}
// 对于每个sheet,读取其中的每一行
for (int rowNum = rowNumBegin; rowNum <= rowNumEnd; rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow == null) {
continue;
}
ArrayList<String> curarr = new ArrayList<String>();
for (int columnNum = columnBegin; columnNum < columnEnd; columnNum++) {
XSSFCell cell = xssfRow.getCell(columnNum);
curarr.add(Trim_str(getValue(cell)));
}
ans.add(curarr);
curarr = null;
}
return ans;
}
//判断后缀为xlsx的excel文件的数据类
@SuppressWarnings({"static-access" })
private static String getValue(XSSFCell xssfRow) {
if(xssfRow==null){
return "";
}
if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfRow.getBooleanCellValue());
} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
double cur=xssfRow.getNumericCellValue();
long longVal = Math.round(cur);
Object inputValue = null;
if(Double.parseDouble(longVal + ".0") == cur)
inputValue = longVal;
else
inputValue = cur;
return String.valueOf(inputValue);
} else if(xssfRow.getCellType() == xssfRow.CELL_TYPE_BLANK || xssfRow.getCellType() == xssfRow.CELL_TYPE_ERROR){
return "";
}
else {
return String.valueOf(xssfRow.getStringCellValue());
}
}
//字符串修剪 去除所有空白符号 , 问号 , 中文空格
static private String Trim_str(String str) {
if (str == null)
return null;
return str.replaceAll("[\\s\\?]", "").replace(" ", "");
}
/**
* 根据总行数,判断读几次
* 一千行一次
*/
public int testRowNumber(int i){
if(0 == i){
return 0;
}
if(i<1000){
return 1;
}else{
return i / 1000 +1;
}
}
}