import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
/**
* Excel2003公用接口
*
*/
public class XlsUtil {
public File xlsFile;
public Workbook wb = null;
public Sheet xlsSheet = null;
public String summarySheetName = "summary";
public boolean flag = true;
public boolean flagAll = true;
public int R_Index = 0;
public XlsUtil() {
//
}
public XlsUtil(File file) {
this.xlsFile = file;
initXls();
}
/**
* 初始化EXCEL文件 调用XLS时需检查WORKBOOK是否为空
*/
public void initXls() {
try {
WorkbookSettings wbs = new WorkbookSettings();
wbs.setGCDisabled(true);
wb = Workbook.getWorkbook(xlsFile, wbs);
// xlsSheet = wb.getSheet(0);
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void close()
{
if(wb!=null)
wb.close();
}
public String[] getSheetsName() {
if (wb == null)
return null;
return wb.getSheetNames();
}
public String[] getSheetHeads(Sheet sheet)
{
String[] values = getStartRowValues(sheet,0,CsvConst.Csv_Sheet_Col_Start);
return values;
}
public Sheet getSheet(String sheetname) {
Sheet sht = null;
if (wb == null)
return null;
try {
sht = wb.getSheet(sheetname);
} catch (ArrayIndexOutOfBoundsException e) {
// TODO: handle exception
System.out.println(e.getLocalizedMessage());
int index = getSheetIndexFrmSheetName(sheetname);
sht = wb.getSheet(index);
}
return sht;
}
public Sheet getSheet(int index) {
Sheet sht = null;
if (wb == null)
return null;
try {
sht = wb.getSheet(index);
} catch (ArrayIndexOutOfBoundsException e) {
// TODO: handle exception
System.out.println(e.getLocalizedMessage());
}
return sht;
}
/** 根据列头名称获取列头索引号 */
public int getHeadIndex(Sheet sheet, String headname) {
int colcount = sheet.getColumns();
for (int i = 0; i < colcount; i++) {
String tmphead = sheet.getCell(i, 0).getContents();
if (tmphead.trim().equalsIgnoreCase(headname))
return i;
else
continue;
}
return -1;
}
public int getRowNo(Sheet sheet, String rowname, int col) {
int maxlength = sheet.getRows();
for (int i = 0; i < maxlength; i++) {
String cellvalue = getCellValue(sheet, i, col);
if(cellvalue==null)
return -1;
if (cellvalue.equalsIgnoreCase(rowname)
|| formatHeadStr(cellvalue).equalsIgnoreCase(rowname))
return i;
else
continue;
}
return -1;
}
private String formatHeadStr(String head) {
head = head.replaceAll(" ", "");
head = head.replaceAll("\n", "");
head = head.replaceAll("\t", "");
return head;
}
public String getCellValue(Sheet sheet, int row, int col) {
Cell[] cells = sheet.getRow(row);
if(cells==null||cells.length<col+1)
return null;
return cells[col].getContents();
}
/** 获取指定行数据 */
public String[] getRowValues(Sheet sheet, int row) {
Cell[] cells = sheet.getRow(row);
int length = cells.length;
String[] values = new String[length];
for (int i = 0; i < length; i++) {
values[i] = cells[i].getContents();
}
return values;
}
public String[] getStartRowValues(Sheet sheet, int row,int startCol) {
Cell[] cells = sheet.getRow(row);
int length = cells.length;
String[] values = new String[length-startCol];
for (int i = 0; i < length-startCol; i++) {
values[i] = cells[i+startCol].getContents();
}
return values;
}
/**
* 获取XLS中单元格数据
*
* @param sheetindex
* @param row
* @param col
* @return String
*/
public String getXlsData(int sheetindex, int row, int col) {
Sheet tmpSheet = wb.getSheet(sheetindex);
return tmpSheet.getCell(col, row).getContents();
}
/**
* 获取指定XLS中单元格数据
*
* @param workbook
* @param sheetindex
* @param row
* @param col
* @return String
*/
public String getXlsData(Workbook workbook, int sheetindex, int row, int col) {
Sheet tmpSheet = workbook.getSheet(sheetindex);
return tmpSheet.getCell(col, row).getContents();
}
/**
* 根据SHEET名称获取SHEET的索引
*
* @param sheetname
* @return
*/
public int getSheetIndexFrmSheetName(String sheetname) {
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
if (wb.getSheet(i).getName().trim().equalsIgnoreCase(sheetname))
return i;
else
continue;
}
return -1;
}
/**
* 获取指定SHEET页中结束行
*
* @param tmpSheet
* @param startRow
* @return int 结束行号
*/
public int getSheetEndRow(Sheet tmpSheet, int startRow) {
int row = 0, col = 0;
for (row = startRow; row < tmpSheet.getRows(); row++) {
boolean rowflag = false;
for (col = 0; col < 3; col++) {
String cell = tmpSheet.getCell(col, row).getContents();
if (!cell.equalsIgnoreCase("")) {
rowflag = true;
} else {
continue;
}
}
if (rowflag)
continue;
else
return row;
}
return tmpSheet.getRows();
}
/**
* 获取指定SHEET,指定列,某两行之间单元格的值数组
*
* @param tmpSheet
* @param col
* @param rowStart
* @param rowEnd
* @return String[]
*/
public String[] getXlsRowsData(Sheet tmpSheet, int col, int rowStart,
int rowEnd) {
int length = rowEnd - rowStart + 1;
String[] colRst = new String[length];
for (int i = 0; i <= rowEnd - rowStart; i++) {
colRst[i] = tmpSheet.getCell(col, rowStart - 1 + i).getContents()
.trim();
}
return colRst;
}
/**
* 根据第0列单元格内容获取当前行号
*
* @param tmpSheet
* 操作的SHEET
* @param contents
* 单元格内容
* @return int 行号
*/
public int getRowByContents(Sheet tmpSheet, String contents) {
for (int row = 0; row < tmpSheet.getRows(); row++) {
String tmpContents = tmpSheet.getCell(0, row).getContents();
if (tmpContents.equalsIgnoreCase(contents))
return row;
else
continue;
}
return -1;
}
public int getSheetCount() {
return wb.getNumberOfSheets();
}
/**
* 根据SHEET名称获取SHEET
*
* @param sheetname
* @return sheet
*/
public Sheet getSheetFrmSheetName(String sheetname) {
int sheetindex = getSheetIndexFrmSheetName(sheetname);
if (sheetindex >= 0)
return wb.getSheet(sheetindex);
else
return null;
}
/**
* 向指定SHEET,指定行列写入值
*
* @param WritableSheet
* ws
* @param row
* @param col
* @param Cell
* value
*/
public void writeCell(WritableSheet ws, int row, int col, String value) {
try {
ws.addCell(new Label(col, row, value));
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public String getCurrentTimeString() {
return new SimpleDateFormat("yyyyMMdd_HHmmss").format(new Date());
}
public String[] getAllSheetsName(String startSheetName, int step) {
int startindex = getSheetIndexFrmSheetName(startSheetName);
int sheetlength = wb.getNumberOfSheets() - step - startindex;
String[] rst = new String[sheetlength];
for (int i = 0; i < sheetlength; i++) {
rst[i] = wb.getSheet(i + startindex + step).getName().trim();
}
return rst;
}
}
Excel2003操作接口
最新推荐文章于 2024-08-31 07:11:56 发布