importjava.io.*;
importjava.util.UUID;
importjxl.Cell;
importjxl.Sheet;
importjxl.Workbook;
importjxl.read.biff.BiffException;
publicclass ExcelUtil {
privateString path;
privateString tableName;
privateString[] tableCols;
privateWorkbook workbook;
publicExcelUtil(String path, String tableName, String[] tableCols)
throwsBiffException, IOException {
this.tableName = tableName;
this.tableCols = tableCols;
this.setPath(path);
this.setWorkbook(Workbook.getWorkbook(newjava.io.File(path)));
}
/**
* 获取工作薄数量
*
* @return 工作薄数量
*/
publicint getNumberOfSheets(Workbook book) {
returnbook == null? 0: book.getNumberOfSheets();
}
/**
* 获取工作薄总行数
*
* @param sheet
* 工作薄
* @return 工作薄总行数
*/
publicint getRows(Sheet sheet) {
returnsheet == null? 0: sheet.getRows();
}
/**
* 获取最大列数
*
* @param sheet
* 工作薄
* @return 总行数最大列数
*/
publicint getColumns(Sheet sheet) {
returnsheet == null? 0: sheet.getColumns();
}
/**
* 获取每行单元格数组
*
* @param sheet
* 工作薄
* @param row
* 行数
* @return 每行单元格数组
*/
publicCell[] getRows(Sheet sheet, introw) {
returnsheet == null|| sheet.getRows() < row ? null: sheet
.getRow(row);
}
/**
* 获取每行单元格数组
*
* @param sheet
* 工作薄
* @param endrow
* 结束行
* @param endCol
* 结束列
* @return 每行单元格数组
*/
publicCell[][] getCells(Sheet sheet, intendrow, intendcol) {
returngetCells(sheet, 0, endrow, 0, endcol);
}
/**
* 获取每行单元格数组
*
* @param sheet
* 工作薄
* @param startrow
* 行数
* @param endrow
* 结束行
* @param startcol
* 开始列
* @param endCol
* 结束列
* @return 每行单元格数组
*/
publicCell[][] getCells(Sheet sheet, intstartrow, intendrow,
intstartcol, intendcol) {
Cell[][] cellArray = newCell[endrow - startrow][endcol - startcol];
intmaxRow = this.getRows(sheet);
intmaxCos = this.getColumns(sheet);
for(inti = startrow; i < endrow && i < maxRow; i++) {
for(intj = startcol; j < endcol && j < maxCos; j++) {
cellArray[i - startrow][j - startcol] = sheet.getCell(j, i);
}
}
returncellArray;
}
/**
* 得到行的值
*
* @param sheet
* @param col
* @param startrow
* @param endrow
* @return
*/
publicCell[] getColCells(Sheet sheet, intcol, intstartrow, intendrow) {
Cell[] cellArray = newCell[endrow - startrow];
intmaxRow = this.getRows(sheet);
intmaxCos = this.getColumns(sheet);
if(col <= 0|| col > maxCos || startrow > maxRow || endrow < startrow) {
returnnull;
}
if(startrow < 0) {
startrow = 0;
}
for(inti = startrow; i < endrow && i < maxRow; i++) {
cellArray[i - startrow] = sheet.getCell(col, i);
}
returncellArray;
}
/**
* 得到列的值
*
* @param sheet
* @param row
* @param startcol
* @param endcol
* @return
*/
publicCell[] getRowCells(Sheet sheet, introw, intstartcol, intendcol) {
Cell[] cellArray = newCell[endcol - startcol];
intmaxRow = this.getRows(sheet);
intmaxCos = this.getColumns(sheet);
if(row <= 0|| row > maxRow || startcol > maxCos || endcol < startcol) {
returnnull;
}
if(startcol < 0) {
startcol = 0;
}
for(inti = startcol; i < startcol && i < maxCos; i++) {
cellArray[i - startcol] = sheet.getCell(i, row);
}
returncellArray;
}
/**
* 生成随机ID
*
* @return
*/
publicstatic String getStrRandomId() {
String uuid = UUID.randomUUID().toString().replace("-","");
returnuuid;
}
/**
* 组装SQL语句(扩展导入数据库额外增加字段的情况)
*
* @param sheet
* 工作薄
* @param startrow
* 开始行
* @param endrow
* 结束行
* @param startcol
* 开始列
* @param endcol
* 结束列
* @return SQL语句数组
*/
publicObject[] constrctCellsSql(Sheet sheet, intstartrow, intendrow,
intstartcol, intendcol, String payTime) {
Cell[][] cellArray = getCells(sheet, startrow, endrow, startcol, endcol);
java.util.ArrayList<String> list = newjava.util.ArrayList<String>();
StringBuffer bf = newStringBuffer("INSERT INTO " + tableName + "(");
for(inti = 0; tableCols != null&& i < tableCols.length; i++)
{
if(i != tableCols.length - 1)
{
bf.append(tableCols[i]).append(",");
}
else
{
bf.append(tableCols[i]).append("");
}
}
bf.append(") VALUES ");
for(inti = 0; i < cellArray.length; i++)
{
StringBuffer sqlBuffer = newStringBuffer();
sqlBuffer.append(bf.toString() + "(");
Cell[] cell = cellArray[i];
if(tableCols != null&& cell != null
&& tableCols.length != cell.length)
{
continue;
}
for(intj = 0; j < cell.length; j++)
{
String tmp = "";
if(cell[j] != null&& cell[j].getContents() != null)
{
tmp = (String) cell[j].getContents();
}
if(j != cell.length - 1)
{
sqlBuffer.append("'").append(tmp).append("',");
}
else
{
sqlBuffer.append("'").append(tmp).append("'");
}
}
sqlBuffer.append(")");
list.add(sqlBuffer.toString());
System.out.println(sqlBuffer.toString());
}
System.out.println(list);
returnlist.toArray();
}
/**
* 获取Excel文件路径
*
* @return Excel文件路径
*/
publicString getPath() {
returnthis.path;
}
/**
* 设置Excel文件路径
*
* @param path
* Excel文件路径
*/
publicvoid setPath(String path) {
this.path = path;
}
/**
* 获取工作薄集合
*/
publicWorkbook getWorkbook() {
returnthis.workbook;
}
/**
* 设置工作薄集合
*
* @param workbook
* 工作薄集合
*/
publicvoid setWorkbook(Workbook workbook) {
this.workbook = workbook;
}
/**
*
* @param args
*/
publicstatic void main(String[] args) {
}
