POI是 血统高贵的Apache下的开源项目，仰视一下。它可以轻松处理一般的excel读取、导出、和一般的报表操作，但是涉及到一些excel的不常用的特性时又有点复杂。这不怪POI，只能说excel身就是个非常复杂的东西，何况POI还要处理微软Office和开放标准的兼容问题。折腾了一个多星期，写点东西吧。进入正题。POI版本为poi-3.5-FINAL-20090928.jar
public HSSFWorkbook readWorkbook(File file) throws IOException, FileNotFoundException {
ByteArrayOutputStream byteOS = new ByteArrayOutputStream();
FileInputStream fis = new FileInputStream(file);
byte[] by = new byte[512];
int t = fis.read(by, 0, by.length);
while (t > 0) {
byteOS.write(by, 0, 512); // read 512
t = fis.read(by, 0, by.length);
}
byteOS.close();
InputStream byteIS = new ByteArrayInputStream(byteOS.toByteArray());
HSSFWorkbook wbDest = new HSSFWorkbook(byteIS);
return wbDest;
}
将HSSFWorkbook 写入文件
public void saveWorkbook(File file, HSSFWorkbook targetWb) throws FileNotFoundException, IOException {
// 目标填充文件
FileOutputStream fos = new FileOutputStream(file);
// 写文件
targetWb.write(fos);
fos.flush();
fos.close();
}
对sheet中所有的单元格(包括合并单元格)根据字数设置合适的行高
在报表开发中，有时需要对合并的单元格调整行高，使内容全部展示出来。在单个普通单元格中，可以设置cell.getCellStyle.setWrapText(true)实现自动换行，且自动调整行高，但是这对合并的单元格似乎没有效果。附上自己写的一个方法，貌似效率不高。
/**
*对sheet中所有的单元格(包括合并单元格)根据字数设置合适的行高
*/
public void adjustRowHeight(HSSFSheet stTpt) {
// 默认行间距，pixel为单位
float defaultRowGapInPoint = 4f;
// 得到所有的合并区域
List<Region> regions = new ArrayList<Region>();
for (int i = 0; i < stTpt.getNumMergedRegions(); i++) {
regions.add(stTpt.getMergedRegionAt(i));
}
int rows = stTpt.getPhysicalNumberOfRows();
for (int r = 0; r < rows; r++) {
HSSFRow row = stTpt.getRow(r);
int cells = row.getPhysicalNumberOfCells();
for (short c = 0; c < cells; c++) {
HSSFCell cell = row.getCell(c);
// 只对String类型单元格调整高度
if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
boolean isBelongToRegion = false;
for (Region region : regions) {
if (region.contains(r, c)) {
isBelongToRegion = true;
int rowFrom = region.getRowFrom();
int rowTo = region.getRowTo();
int colFrom = region.getColumnFrom();
int colTo = region.getColumnTo();
int regionWidths = 0;
for (int ii = colFrom; ii <= colTo; ii++) {
regionWidths += stTpt.getColumnWidth(ii);
}
long stringWidths = cell.getRichStringCellValue().toString().getBytes().length * 256;
// 计算所需高度为默认高度的多少倍
long aRows = stringWidths / regionWidths + 1；
//得到每行应该有的高度
HSSFFont font = cell.getCellStyle().getFont(stTpt.getWorkbook());
float rowHeightInPoint = font.getFontHeightInPoints() + defaultRowGapInPoint;
float height = rowHeightInPoint * aRows / (rowTo - rowFrom + 1);
for (int jj = rowFrom; jj <= rowTo; jj++) {
HSSFRow RegionRow = stTpt.getRow(jj);
if (RegionRow.getHeightInPoints() < height) {
RegionRow.setHeightInPoints(height);
}
}
break;
}
}
if (!isBelongToRegion) {
long stringWidths = cell.getRichStringCellValue().toString().getBytes().length * 256;
//得到列宽为一个字符的1/256
long colWidth = stTpt.getColumnWidth(c);
long aRows = stringWidths / colWidth + 1;
//得到每行应该有的高度
HSSFFont font = cell.getCellStyle().getFont(stTpt.getWorkbook());
float rowHeightInPoint = font.getFontHeightInPoints() + defaultRowGapInPoint;
if (row.getHeightInPoints() < aRows * rowHeightInPoint) {
row.setHeightInPoints(aRows * rowHeightInPoint);
}
}
}
}
}
}
如果单元格的类型为“文本"，对应的cellStyle中的dataFormat为“@”或这“Text”，那么当内容很多时，excel会用“####”来代替，而不管你是否已经为其调整出了显示区域。对应的解决方案为设置单元格为“常规”类型，对应的cellStyle中的dataFormat为“General”，程序实现类似cell.getCellStyle.setDataFormat(targetWorkbook.createDataFormat().getFormat("General"))。这样就不会出现“###########“了。
调整合并单元格的样式，基本上是边框
public void addMergedRegionStyle(HSSFSheet targetSheet, Region region, HSSFCellStyle style) {
int rowFrom = region.getRowFrom();
int rowTo = region.getRowTo();
int colFrom = region.getColumnFrom();
int colTo = region.getColumnTo();
for (int r = rowFrom; r <= rowTo; r++) {
HSSFRow row = targetSheet.getRow(r);
if (row != null) {
for (int c = colFrom; c <= colTo; c++) {
HSSFCell cell = row.getCell(c);
if (cell == null) {
cell = row.createCell(c);
}
cell.getCellStyle().setBorderLeft(style.getBorderLeft());
cell.getCellStyle().setBorderRight(style.getBorderRight());
cell.getCellStyle().setBorderTop(style.getBorderTop());
cell.getCellStyle().setBorderBottom(style.getBorderBottom());
}
}
}
}
为单元格或者合并单元格添加合法性限制的下拉列表
摘点关键代码
String[] list = { "是", "否", "随便" };
// 只对（0，0）单元格有效
CellRangeAddressList regions = new CellRangeAddressList(0,0,0,0);
// // 生成下拉框内容
DVConstraint constraint = DVConstraint.createExplicitListConstraint(list);
// // 绑定下拉框和作用区域
HSSFDataValidation dataValidation = new HSSFDataValidation(regions,constraint);
// 对sheet页生效
targetSt.addValidationData(dataValidation );
这种方法在创建新的excel的时候没有问题，可以创建下来列表，但是当sheet是从创建好的MS Office excel 2003 中读取的话，就会出问题啦。/跑出一个傻乐吧唧的异常：java.lang.IllegalStateException: Unexpected (org.apache.poi.hssf.record.UnknownRecord)好像是POI对微软格式的excel支持不好导致的。
如果仍然想实现下拉列表的功能，可能就要换个思路了，什么思路呢，我的做法是重新创建了一个sheet，然后在把原来sheet中的内容拷贝过来，这时再添加列表就没有问题了。
下面奉上一个拷贝sheet的方法。
copy已有workbook中的第一个sheet到新的workbook中的第一个sheet
public HSSFWorkbook createNewWorkBook(HSSFWorkbook sourceWb) {
HSSFSheet sourceSheet = sourceWb.getSheetAt(0);
HSSFWorkbook targetWb = new HSSFWorkbook();
HSSFSheet targetSheet = targetWb.createSheet();
HSSFRow sourceRow = null;
HSSFRow targetRow = null;
HSSFCell sourceCell = null;
HSSFCell targetCell = null;
Region region = null;
int pEndRow = sourceSheet.getLastRowNum();
// 拷贝合并的单元格
for (int i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
region = sourceSheet.getMergedRegionAt(i);
if ((region.getRowFrom() >= 0) && (region.getRowTo() <= pEndRow)) {
targetSheet.addMergedRegion(Region.convertToCellRangeAddress(region));
}
}
// 拷贝行并填充数据
for (int i = 0; i <= pEndRow; i++) {
sourceRow = sourceSheet.getRow(i);
if (sourceRow == null) {
continue;
}
int targetRowIndex = i;
targetRow = targetSheet.createRow(targetRowIndex);
targetRow.setHeight(sourceRow.getHeight());
for (int j = sourceRow.getFirstCellNum(); j <= sourceRow.getPhysicalNumberOfCells(); j++) {
sourceCell = sourceRow.getCell(j);
if (sourceCell == null) {
continue;
}
targetSheet.setColumnWidth((int) j, sourceSheet.getColumnWidth((int) j));
targetSheet.setActive(sourceSheet.isActive());
targetSheet.setColumnHidden(j, sourceSheet.isColumnHidden(j));
targetCell = targetRow.createCell(j);
int cType = sourceCell.getCellType();
targetCell.setCellType(cType);
if (sourceCell.getHyperlink() != null)
targetCell.setHyperlink(sourceCell.getHyperlink());
if (sourceCell.getCellComment() != null)
targetCell.setCellComment(sourceCell.getCellComment());
HSSFFont srcFont = sourceCell.getCellStyle().getFont(sourceWb);
//targetCell.setCellStyle(this.copyCellStyle(targetWb, sourceCell.getCellStyle(), srcFont));
targetCell.getCellStyle().cloneStyleFrom(sourceCell.getCellStyle());
switch (cType) {
case HSSFCell.CELL_TYPE_BOOLEAN:
targetCell.setCellValue(sourceCell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
targetCell.setCellErrorValue(sourceCell.getErrorCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
String s = sourceCell.getCellFormula();
s = s.replaceAll(String.valueOf(i + 1), String.valueOf(targetRowIndex + 1));
targetCell.setCellFormula(s);
break;
case HSSFCell.CELL_TYPE_NUMERIC:
targetCell.setCellValue(sourceCell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
targetCell.setCellValue(sourceCell.getRichStringCellValue());
break;
}
}
}
return targetWb;
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
