- 设置宽度,1个汉字的宽度
- 导入excel用,返回行数
- sheetName是sheet,显示名
- 导出excel
- 导出excel
- 获得excel数据
- 写输出,最后用
- 重新单元格指定位置
- 移到下一行,列开头(读取)
- 移到下i行,列开头(读取)
- 创建一新行(写入用)
- 跳一个单元格
- 时间类型与文本不能同时存在
- 设置输出文本格式,与日期不能同时存在
- 添加数字的字符文本
- 添加指定类型的,非日期
- 在指定的位置建造下拉,–>下拉名(Id)
- Workbook读取excel工厂,可以判断扩展名选择2007或者2003的excelapi
- 获取第一行所有字符串集合.表头验证
- 当前行列的位置
- 获取当前行指定列日期值
- 读取指定单元格数据
- 自动调整显示宽度
- 设置head–虽然可以在加载excel的时候获取到head，但是有的没有head设置，要单独设置head
private Sheet sheet;
private Workbook workbook;
private CreationHelper createHelper;
private int nowrow = 0;
private int nowcolumn = 0;
private Row row;
private List<String> head;
private CellStyle style = null;
设置宽度,1个汉字的宽度
/**
* 设置宽度,1个汉字的宽度
* @param array
*/
public void setWidth(int[] array) {
for (int i = 0; i < array.length; i++) {
sheet.setColumnWidth(i, array[i] * 512);
}
}
导入excel用,返回行数
/**
* 导入excel用,返回行数
* @param fileName
* @
*/
public int importExcel(String fileName) {
workbook = this.openFile(fileName);
sheet = workbook.getSheetAt(0);
createHelper = workbook.getCreationHelper();
row = sheet.getRow(0);
head = this.getRowHead();
return sheet.getLastRowNum();
}
sheetName是sheet,显示名
/**
* sheetName是sheet,显示名
* @param fileName
* @param sheetName
* @return
* @
*/
public int importExcel(String fileName, String sheetName) {
workbook = this.openFile(fileName);
sheet = workbook.getSheet(sheetName);
createHelper = workbook.getCreationHelper();
row = sheet.getRow(0);
head = this.getRowHead();
return sheet.getLastRowNum();
}
导出excel
/**
* 导出excel
* @
*/
public Row exportExcel(String sheetName) {
workbook = new HSSFWorkbook();
sheet = workbook.createSheet(sheetName);
createHelper = workbook.getCreationHelper();
row = sheet.createRow(0);
style = workbook.createCellStyle();
initBorder(style);
return row;
}
导出excel
/**
* 导出excel
* @
*/
public Row exportExcel(List<String> sheetNames, Map<String, List<List<String>>> data) {
workbook = new HSSFWorkbook();
for (String sheetName : sheetNames) {
Sheet sheet = workbook.createSheet(sheetName);
List<List<String>> sheetData = data.get(sheetName);
int rowNum = 0;
if (sheetData == null)
continue;
for (List<String> rowData : sheetData) {
Row row = sheet.createRow(rowNum);
int cellNum = 0;
for (String cellData : rowData) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellData);
cellNum++;
}
rowNum++;
}
}
return row;
}
获得excel数据
public List<List<String>> getExcelData(String fileName, int rowNum) {
int totalNum = this.importExcel(fileName) + 1;
if (rowNum == 0 || totalNum < rowNum) {
rowNum = totalNum;
}
List<List<String>> resultList = new ArrayList<List<String>>();
for (int i = 0; i < rowNum; i++) {
List<String> list = new ArrayList<String>();
row = sheet.getRow(i);
for (int j = 0; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
SimpleDateFormat from = new SimpleDateFormat("yyyy-MM-dd");
list.add(from.format(date));
} else {
list.add(cell.toString());
}
}
resultList.add(list);
}
return resultList;
}
public List<List<String>> listAll() {
List<List<String>> resultList = new ArrayList<List<String>>();
for (Row myrow : sheet) {
List<String> list = new ArrayList<String>();
for (int i = 0; i < myrow.getLastCellNum() + 1; i++) {
Cell mycell = myrow.getCell(i);
if (mycell == null) {
list.add("");
} else if (mycell.getCellType() == Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(mycell)) {
Date date = mycell.getDateCellValue();
SimpleDateFormat from = new SimpleDateFormat("yyyy-MM-dd");
list.add(from.format(date));
} else {
list.add(mycell.toString());
}
}
resultList.add(list);
}
return resultList;
}
public List<List<ExcelCellBean>> listCellBean() {
List<List<ExcelCellBean>> resultList = new ArrayList<List<ExcelCellBean>>();
int rowNum = sheet.getLastRowNum();
for (int rowId = 0; rowId <= rowNum; rowId++) {
Row myrow = sheet.getRow(rowId);
List<ExcelCellBean> list = new ArrayList<ExcelCellBean>();
for (int columnId = 0; columnId < myrow.getLastCellNum() + 1; columnId++) {
ExcelCellBean ecb = new ExcelCellBean(rowId, columnId);
Cell mycell = myrow.getCell(columnId);
if (mycell == null) {
ecb.setCellValue("");
continue;
}
ecb.setWidth(sheet.getColumnWidth(columnId));
ecb.setHeight(myrow.getHeight());
ecb.setAlign(convertAlignToHtml(mycell.getCellStyle().getAlignment()));
ecb.setValign(convertValignToHtml(mycell.getCellStyle().getVerticalAlignment()));
if (mycell.getCellType() == Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(mycell)) {
Date date = mycell.getDateCellValue();
SimpleDateFormat from = new SimpleDateFormat("yyyy-MM-dd");
ecb.setCellValue(from.format(date));
} else {
ecb.setCellValue(mycell.toString());
}
list.add(ecb);
}
resultList.add(list);
}
String delList = new String(";");
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
try {
CellRangeAddress region = sheet.getMergedRegion(i);
int firstRow = region.getFirstRow();
int firstColumn = region.getFirstColumn();
int lastRow = region.getLastRow();
int lastColumn = region.getLastColumn();
resultList.get(firstRow).get(firstColumn).setRightMerged(lastColumn - firstColumn + 1);
resultList.get(firstRow).get(firstColumn).setDownMerged(lastRow - firstRow + 1);
for (int delRow = lastRow; delRow >= firstRow; delRow--) {
for (int delColumn = lastColumn; delColumn >= firstColumn; delColumn--) {
if (delList.indexOf(delRow + "," + delColumn + ";") < 0
&& ((delRow != firstRow || delColumn != firstColumn))) {
delList += delRow + "," + delColumn + ";";
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
for (int delRow = resultList.size() - 1; delRow >= 0; delRow--) {
for (int delColumn = resultList.get(delRow).size() - 1; delColumn >= 0; delColumn--) {
if (delList.indexOf(";" + delRow + "," + delColumn + ";") >= 0)
resultList.get(delRow).remove(delColumn);
}
}
return resultList;
}
写输出,最后用
/**
* 写输出,最后用
* @return
* @
*/
public InputStream write() {
ByteArrayOutputStream byteStream = null;
InputStream result = null;
try {
byteStream = new ByteArrayOutputStream();
try {
workbook.write(byteStream);
} catch (IOException e) {
e.printStackTrace();
}
result = new ByteArrayInputStream(byteStream.toByteArray());
return result;
} finally {
}
}
重新单元格指定位置
/**
* 重新单元格指定位置
* @param nowrow
* @param nowcolumn
*/
public void setPosition(int nowrow, int nowcolumn) {
this.nowcolumn = nowcolumn;
this.nowrow = nowrow;
}
移到下一行,列开头(读取)
/**
* 移到下一行,列开头(读取)
*/
public Row nextRow() {
row = sheet.getRow(++nowrow);
this.nowcolumn = 0;
return row;
}
移到下i行,列开头(读取)
/**
* 移到下i行,列开头(读取)
*/
public Row nextRow(int i) {
nowrow = nowrow + i;
row = sheet.getRow(nowrow);
this.nowcolumn = 0;
return row;
}
创建一新行(写入用)
/**
*
* 创建一新行(写入用)
* @return
*/
public Row createRow() {
row = sheet.createRow(++nowrow);
this.nowcolumn = 0;
return row;
}
跳一个单元格
/**
* 跳一个单元格
*/
public void skipCell() {
this.nowcolumn = nowcolumn + 1;
}
时间类型与文本不能同时存在
/**
* 时间类型与文本不能同时存在
* @return 时间类型的style(yyyy-MM-dd)
* @
*/
public short getDateStyle() {
return createHelper.createDataFormat().getFormat("yyyy-MM-dd");
}
设置输出文本格式,与日期不能同时存在
/**
* 设置输出文本格式,与日期不能同时存在
* @return
* @
*/
public short getTextStyle() {
return createHelper.createDataFormat().getFormat("@");
}
添加数字的字符文本
/**
* 添加数字的字符文本
* @param value
* @
*/
public Cell addValue(String value) {
CellStyle mystyle = workbook.createCellStyle();
mystyle.cloneStyleFrom(style);
mystyle.setDataFormat(createHelper.createDataFormat().getFormat("@"));
Cell cell = this.addValue(value, mystyle);
return cell;
}
添加指定类型的,非日期
/**
* 添加指定类型的,非日期
* @param value
* @
*/
public Cell addValue(Object value, CellStyle style) {
Cell cell = row.createCell(nowcolumn++);
cell.setCellStyle(style);
if (value instanceof String) {
cell.setCellValue((String) value);
}
if (value instanceof Double) {
cell.setCellValue((Double) value);
}
if (value instanceof Date) {
if (value != null)
cell.setCellValue((Date) value);
}
return cell;
}
在指定的位置建造下拉,–>下拉名(Id)
/**
* 在指定的位置建造下拉,-->下拉名(Id)
* @param x 行
* @param lx 最后行
* @param y 列
* @param ly 最后列
* @param dropLs 要添加的下拉,下拉有数量限制,上百个的下拉只能用名称空间
* @
*/
public void addDrop(List<DropBean> dropLs) {
addDropNoMove(dropLs);
this.addValue("");
}
public void addDropNoMove(List<DropBean> dropLs) {
addDropAllColumn(dropLs, nowrow, nowrow, nowrow);
}
public void addDropAllColumn(List<DropBean> dropLs, int startrow, int endrow, int columnnum) {
CellRangeAddressList conDrop = new CellRangeAddressList(startrow, endrow, columnnum, columnnum);
String[] strArr = new String[dropLs.size()];
for (int j = 0; j < dropLs.size(); j++) {
strArr[j] = dropLs.get(j).getLabel() + "(" + dropLs.get(j).getValue() + ")";
}
DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(strArr);
DataValidation dataValidation = new HSSFDataValidation(conDrop, dvConstraint);
dataValidation.setSuppressDropDownArrow(false);
sheet.addValidationData(dataValidation);
}
Workbook读取excel工厂,可以判断扩展名选择2007或者2003的excelapi
/**
* Workbook读取excel工厂,可以判断扩展名选择2007或者2003的excelapi
*
* @param file
* @return Sheet
* @
*/
public Workbook openFile(String fileName) {
InputStream inp = null;
try {
File file = new File(fileName);
if (!file.exists() || !file.isFile()) {
throw new BaseConfirmException("文件不存在!");
}
if (!"xls".equalsIgnoreCase(GjCommonUtil.getExtName(file.getName()))
&& !"xlsx".equalsIgnoreCase(GjCommonUtil.getExtName(file.getName()))) {
throw new BaseConfirmException("文件格式不正确，请重新选择Excel文件！");
}
try {
inp = new FileInputStream(file);
return WorkbookFactory.create(inp);
} catch (Exception e) {
e.printStackTrace();
}
} finally {
if (inp != null) {
try {
inp.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return null;
}
获取第一行所有字符串集合.表头验证
/**
* 获取第一行所有字符串集合.表头验证
* @param headRow
* @return
* @
*/
public List<String> getRowHead() {
Row headRow = sheet.getRow(0);
if (headRow == null) {
return null;
}
Iterator<Cell> cellIter = headRow.cellIterator();
List<String> hiddenhead = new ArrayList<String>();
while (cellIter.hasNext()) {
hiddenhead.add(cellIter.next().toString());
}
return hiddenhead;
}
当前行列的位置
/**
* 当前行列的位置
* 读取的单元格本身必须是文本类型的数字或者是字符
* @param index
* @return
* @
*/
public Long getLong(String strindex) {
int numIndex = head.indexOf(strindex);
String strLong = row.getCell(numIndex).toString();
if (strLong == null || "".equals(strLong.trim())) {
return null;
}
return Long.valueOf(strLong);
}
public Timestamp getDate(String strindex) {
int numIndex = head.indexOf(strindex);
Cell mycell = row.getCell(numIndex);
Date utilDate = null;
if (mycell.getCellType() == Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(mycell)) {
utilDate = row.getCell(numIndex).getDateCellValue();
if (utilDate == null) {
return null;
}
} else {
try {
String datestr = row.getCell(numIndex).getStringCellValue();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
utilDate = dateFormat.parse(datestr);
} catch (Exception e) {
return null;
}
}
return new java.sql.Timestamp(utilDate.getTime());
}
public String getStr(String strindex) {
int numIndex = head.indexOf(strindex);
return getStrByNumIndex(numIndex);
}
public String getStrByNumIndex(int numIndex) {
String result = null;
if (row != null) {
Cell cell = row.getCell(numIndex);
if (cell == null) {
return "";
}
if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
result = cell.getNumericCellValue() + "";
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
result = row.getCell(numIndex) == null ? null : row.getCell(numIndex).getStringCellValue();
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
result = row.getCell(numIndex) == null ? null : row.getCell(numIndex).getNumericCellValue() + "";
if (result.indexOf(".0") == result.length() - 2) {
result = result.substring(0, result.length() - 2);
}
} else {
result = row.getCell(numIndex) == null ? null : row.getCell(numIndex).toString();
}
}
return result;
}
获取当前行指定列日期值
/**
* 获取当前行指定列日期值
* @param strindex
* @return
* @throws Exception
*/
public Timestamp getDate(int numIndex) throws Exception {
Cell mycell = row.getCell(numIndex);
Date utilDate = null;
if (mycell.getCellType() == Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(mycell)) {
utilDate = row.getCell(numIndex).getDateCellValue();
if (utilDate == null) {
return null;
}
} else {
try {
String datestr = row.getCell(numIndex).getStringCellValue();
if (datestr.length() == 7 && datestr.indexOf("-") > 0)
datestr = datestr + "-01";
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
utilDate = dateFormat.parse(datestr);
} catch (Exception e) {
return null;
}
}
return new java.sql.Timestamp(utilDate.getTime());
}
读取指定单元格数据
/**
* 读取指定单元格数据
* @param curRow 当前行对象
* @param dataRow 需要读取的行对象
* @param numIndex 需要读取的列
* @return
*/
public String getStrByRow(int curRow, int dataRow, int numIndex) {
row = sheet.getRow(dataRow);
String result = this.getStrByNumIndex(numIndex);
row = sheet.getRow(curRow);
return result;
}
public List<String> getStrLs(String strindex) {
List<String> result = new ArrayList<String>();
for (int i = 0; i < head.size(); i++) {
if (strindex.equals(head.get(i))) {
result.add(getStrByNumIndex(i));
}
}
return result;
}
自动调整显示宽度
/**
* 自动调整显示宽度
* @param row
* @param sheet
* @
*/
public void autoSize() {
if (sheet == null || sheet.getRow(0) == null || sheet.getRow(0).getLastCellNum() == 0) {
return;
}
for (int i = 0; i < sheet.getRow(0).getLastCellNum(); i++) {
sheet.autoSizeColumn(i);
}
}
public void setFrozen(int rowNum) {
sheet.createFreezePane(0, rowNum, 0, rowNum);
}
设置head–虽然可以在加载excel的时候获取到head，但是有的没有head设置，要单独设置head
/**
* 设置head--虽然可以在加载excel的时候获取到head，但是有的没有head设置，要单独设置head
*/
public void setHead(List<String> head) {
this.head = head;
}
private String convertAlignToHtml(short alignment) {
String align = "left";
switch (alignment) {
case HSSFCellStyle.ALIGN_LEFT:
align = "left";
break;
case HSSFCellStyle.ALIGN_CENTER:
align = "center";
break;
case HSSFCellStyle.ALIGN_RIGHT:
align = "right";
break;
default:
break;
}
return align;
}
private String convertValignToHtml(short valignment) {
String align = "left";
switch (valignment) {
case HSSFCellStyle.VERTICAL_TOP:
align = "top";
break;
case HSSFCellStyle.VERTICAL_CENTER:
align = "middle";
break;
case HSSFCellStyle.VERTICAL_BOTTOM:
align = "bootom";
break;
default:
break;
}
return align;
}