package ut;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import javax.swing.JOptionPane;
import org.apache.poi.ss.usermodel.Row;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.PageOrientation;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
/**不支持Excel2007格式(也就是xlsx格式文件)*/
public class ExcelUtil {
public static ArrayList<String[]> readRows(String f,int sheetIndex,int startRow) {
Workbook wb = null;
try {
wb = Workbook.getWorkbook(new File(f));
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
Sheet sheet = wb.getSheets()[sheetIndex];
int rows = sheet.getRows();
ArrayList<String[]> list=new ArrayList<String[]>();
for(int i = startRow ;i < rows ; i++){
Cell[] cells = sheet.getRow(i);
String[] row=new String[cells.length];
for(int j=0;j<cells.length;j++){
row[j]=cells[j].getContents();
}
list.add(row);
}
return list;
}
public static ArrayList<String[]> readRows(String f,int sheetIndex,int startRow,int endRow) {
Workbook wb = null;
try {
wb = Workbook.getWorkbook(new File(f));
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
Sheet sheet = wb.getSheets()[sheetIndex];
int rows = sheet.getRows();
ArrayList<String[]> list=new ArrayList<String[]>();
for(int i = startRow ;i <=endRow ; i++){
Cell[] cells = sheet.getRow(i);
String[] row=new String[cells.length];
for(int j=0;j<cells.length;j++){
row[j]=cells[j].getContents();
}
list.add(row);
}
return list;
}
/**获取多个sheets的行数据.sheetIndex<0||>length则获取所有Sheets,>0则获取index及之前的*/
public static ArrayList<String[]> readRowsOfSheets(String f,int sheetIndex,int startRow) {
Workbook wb = null;
try {
wb = Workbook.getWorkbook(new File(f));
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
//get sheets
Sheet[] sheets=wb.getSheets();
int end=sheetIndex;
if(sheetIndex<0||sheetIndex>sheets.length)
end=sheets.length;
ArrayList<String[]> list=new ArrayList<String[]>();
for(int k=0;k<end+1;k++){
Sheet sheet = sheets[k];
int rows = sheet.getRows();
for(int i = startRow ;i < rows ; i++){
Cell[] cells = sheet.getRow(i);
String[] row=new String[cells.length];
for(int j=0;j<cells.length;j++){
row[j]=cells[j].getContents();
}
list.add(row);
}
}
return list;
}
/**获取某个目录下的,所有表格文件的,指定sheets的,所有行的数据.endSheetIndex<0||>length则获取所有Sheets,>0则获取index及之前的*/
public static ArrayList<String[]> readRowsOfSheetsOfFiles(String path,int endSheetIndex,int startRow) {
String[] farr=FileUtil.getFilesFullPath(path);
if(farr==null)//是个文件
farr=new String[]{path};
ArrayList<String[]> list=new ArrayList<String[]>();
for(String f:farr){
System.out.println(f);
Workbook wb = null;
try {
wb = Workbook.getWorkbook(new File(f));
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
//get sheets
Sheet[] sheets=wb.getSheets();
int end=endSheetIndex;
if(endSheetIndex<0||endSheetIndex>sheets.length)
end=sheets.length;
for(int k=0;k<end+1;k++){
Sheet sheet = sheets[k];
int rows = sheet.getRows();
for(int i = startRow ;i < rows ; i++){
Cell[] cells = sheet.getRow(i);
String[] row=new String[cells.length];
for(int j=0;j<cells.length;j++){
row[j]=cells[j].getContents();
}
list.add(row);
}
}
}
return list;
}
public static ArrayList<String[]> readColumns(String f,int sheetIndex) {
Workbook wb = null;
try {
wb = Workbook.getWorkbook(new File(f));
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
Sheet sheet = wb.getSheets()[sheetIndex];
int cols = sheet.getColumns();
ArrayList<String[]> list=new ArrayList<String[]>();
for(int i = 0 ;i < cols ; i++){
Cell[] cells = sheet.getColumn(i);
String[] col=new String[cells.length];
for(int j=0;j<cells.length;j++){
col[j]=cells[j].getContents();
}
list.add(col);
}
return list;
}
/**读取每一行里的指定列元素,按列的先后顺序放入一个数组里*/
public static ArrayList<String[]> readColumns(String f,int[] indexs,int sheetIndex) {
Workbook wb = null;
try {
wb = Workbook.getWorkbook(new File(f));
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
Sheet sheet = wb.getSheets()[sheetIndex];
int rows = sheet.getRows