前两天项目需用到excel2007/2010，发现以前的POI包已经不能支持得到了，赶紧的到网上找了个最新的POI3.6包，可这个解析方式给我们来了个大变样，以前的在excel2003中用的那一套已经不能适用，网上能找到的又只是一些很简单的例子，没办法，自己来搞吧，好了，开工吧，本文件分别把2007的读文件和写文件的关键代码贴了出来供大家参考，如有更简洁的方式，欢迎指正：
1、读文件，其实从下面的代码中就可以看出其实读excel2007是可以基本上2003通用的，只是Workbook的对像不同而已，当然对于HSSFXXX形式的具体类都需要改成用新的接口：
public static String getHeaders(String filePath){
String ret = null;
String suffix = filePath.substring(filePath.lastIndexOf(".")); // 文件后辍.
Workbook wb = null;
FileInputStream is = new FileInputStream(new File(filePath));
if (TYPE_EXCEL2003.equals(suffix.toLowerCase())) {
try {
wb = new HSSFWorkbook(is);
ret = getHeaders(wb);
} catch (Exception e) {
throw new FatalBizException(ERROR_CODE_1);
} finally {
try {
is.close();
} //关闭流,finally里可以嵌套trycatch结构
catch (Exception e) {
}
}
} else if (TYPE_EXCEL2007.equals(suffix.toLowerCase())) {
try {
wb = new XSSFWorkbook(is);
ret = getHeaders(wb);
} catch (Exception e) {
throw new FatalBizException(ERROR_CODE_2);
}finally {
try {
is.close();
} //关闭流,finally里可以嵌套trycatch结构
catch (Exception e) {
}
}
} else {
throw new FatalBizException("不支持的文件类型！");
}
return ret;
}
private static String getHeaders(Workbook wb) throws Exception {
StringBuffer sb = new StringBuffer("[");
int sheetNum = wb.getNumberOfSheets();
if (sheetNum > 0) {
Sheet childSheet = wb.getSheetAt(0);
int rowNum = childSheet.getLastRowNum();
if (rowNum > 0) {
Row headerRow = childSheet.getRow(0); // 目前默认第0行为header
int cellNum = headerRow.getLastCellNum();
for (int k = 0; k < cellNum; k++) {
if (k != 0) sb.append(",");
sb.append("\"").append(headerRow.getCell(k).toString()).append("\"");
}
}
}
sb.append("]");
return sb.toString();
}
2、写文件，如果说读文件还差别不大的话，那么写文件就差得比较多了，特别是对单元格颜色的处理改得更加合理了，2003中用模板的形式真的很恶心
，至于合并单元格，虽然类名不同了，但逻辑还是一致的。
public static Workbook exp(String xml,String pathToSaveFolder,int type) throws IOException, DocumentException {
XmlToObj t = new XmlToObj();
ObjForExpImp[] objForExpImps = t.readFromXml(xml);
Workbook wb = new XSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("第一页");
int i;
for (ObjForExpImp objForExpImp : objForExpImps) {
i = topToRow((int) objForExpImp.getTop());
// create the table head
Row row = sheet.createRow(i);
int cellstart = leftToCell((int) objForExpImp.getLeft());
Cell hcell = row.createCell(cellstart);
RichTextString richTextString = createHelper.createRichTextString(
objForExpImp.getTitle());
richTextString.applyFont(createFont(wb, objForExpImp.getFont(),
objForExpImp.getFontsize(), true, objForExpImp
.getFontstyle(), objForExpImp.getFontweight()));
// hcell.setEncoding(HSSFCell.ENCODING_UTF_16);
hcell.setCellStyle(getStyle(wb, 1, objForExpImp.getPosition(), null));
hcell.setCellValue(richTextString);
merged(sheet, i, cellstart, i, cellstart
+ objForExpImp.getCellSlength(), getStyle(wb, 1,
objForExpImp.getPosition(), null));
// create the table body
RowForExp[] rowForExps = objForExpImp.getRowForExps();
i++;
Font bodyFont = createFont(wb, objForExpImp.getFont(),
objForExpImp.getFontsize(), false, objForExpImp
.getFontstyle(), objForExpImp.getFontweight());
// LastRowSpan lastRowSpan = new LastRowSpan();
for (RowForExp rowForExp : rowForExps) {
CellForExp[] cellForExps = rowForExp.getCellForExps();
row = sheet.createRow(i);
int j = leftToCell((int) objForExpImp.getLeft());
CellStyle cs = wb.createCellStyle();
for (CellForExp cellForExp : cellForExps) {
if(0==cellForExp.getColspan()){ //colspan为0的不生成cell.
j++;
continue;
}
Cell cell = row.createCell(j);
CellStyle cellStyle = getStyle(wb, cellForExp
.getRowspan(), cellForExp.getAlign(),cellForExp.getColor());
if (cellForExp.getColspan() > 1
|| cellForExp.getRowspan() > 1) {
int endcolspan = j + cellForExp.getColspan() - 1;
merged(sheet, i, j, i + cellForExp.getRowspan() - 1,
endcolspan, cellStyle);
}
j++;
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
RichTextString richString = createHelper.createRichTextString(
cellForExp.getData());
richString.applyFont(bodyFont);
cell.setCellValue(richString);
cell.setCellStyle(cellStyle);
}
i++;
}
ImageForExp[] imageForExps = objForExpImp.getImageForExps();
for (ImageForExp imageForExp : imageForExps) {
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
BufferedImage bufferImg = ImageIO.read(new File(
pathToSaveFolder + File.separator
+ imageForExp.getName()));
ImageIO.write(bufferImg, "jpg", byteArrayOut);
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = new XSSFClientAnchor(
0,
0,
1023,
255,
(short) leftToCell(imageForExp.getLeft()),
topToRow(imageForExp.getTop()),
(short) (lengthToCell(imageForExp.getLength()) + leftToCell(imageForExp
.getLeft())), topToRow(imageForExp.getTop())
+ (short) height2Row(imageForExp.getHeight()));
drawing.createPicture(anchor, wb.addPicture(byteArrayOut
.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
}
}
return wb;
}
private static CellStyle getStyle(Workbook wb, int rowspan,
int align, String color) {
XSSFCellStyle cs = (XSSFCellStyle)wb.createCellStyle();
if (rowspan > 1)
cs.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
cs.setAlignment(getAlignment(align));
cs.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cs.setBottomBorderColor(HSSFColor.BLACK.index);
cs.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cs.setLeftBorderColor(HSSFColor.BLACK.index);
cs.setBorderRight(HSSFCellStyle.BORDER_THIN);
cs.setRightBorderColor(HSSFColor.BLACK.index);
cs.setBorderTop(HSSFCellStyle.BORDER_THIN);
cs.setTopBorderColor(HSSFColor.BLACK.index);
int[] rbg = Utils.hex2rbg(StringUtils.isBlank(color)?"#ffffff":color);
cs.setFillForegroundColor(new XSSFColor(new Color(rbg[0],rbg[1],rbg[2])));
cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
return cs;
}
private static void merged(Sheet sheet, int startRow, int startCell,
int endRow, int endCell, CellStyle cs) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow,endRow, (short) startCell,
(short) endCell);
sheet.addMergedRegion(cellRangeAddress);
setRegionStyle(sheet, cellRangeAddress, cs);
}
private static void setRegionStyle(Sheet sheet, CellRangeAddress cellRangeAddress,
CellStyle cs) {
for (int i = cellRangeAddress.getFirstRow(); i <= cellRangeAddress.getLastRow(); i++) {
Row row = CellUtil.getRow(i, sheet);
for (int j = cellRangeAddress.getFirstColumn(); j <= cellRangeAddress.getLastColumn(); j++) {
Cell cell = CellUtil.getCell(row, (short) j);
cell.setCellStyle(cs);
}
}
}
好了，代码就贴这么多了，在excel2003是如何渲染背景色、合并单元格、添加图表在这里就不多做说明了，相信网上资料也不少了。
分享到：
相关推荐
收集了poi3.6跟poi3.7解析excel2007用到的包
利用poi3.6 解析excel2003文件的程序代码.
poi3.6 兼容office 2003,2007
poi3.6官方帮助文档 poi3.6官方帮助文档 poi3.6官方帮助文档
excel poi3.6 excel poi3.6 excel poi3.6
poi-3.6-20091214.jar
poi 3.6 zuixinde API chm格式
网上找了很久 poi3.6 excel2007操作 使用event api的例子
POI-3.6 poi解析Excel(2003\2010)的jar集合,支持office2003\2010
poi读写excelpoi读写excelpoi读写excelpoi读写excelpoi读写excelpoi读写excelpoi读写excelpoi读写excelpoi读写excel
poi读写excel，poi总结； poi读写excel，poi总结 poi读写excel，poi总结
poi-3.6-20091214.jar poi-contrib-3.6-20091214.jar poi-examples-3.6-20091214.jar poi-ooxml-3.6-20091214.jar poi-ooxml-schemas-3.6-20091214.jar poi-scratchpad-3.6-20091214.jar xmlbeans-2.3.0.jar
poi excel poi excel poi excel poi excel poi excel poi excel poi excel poi excel
poi 支持Excel 2007 poi支持Excel 2007 poi支持Excel 2007
ApachePOI项目是一个对微软文档格式进行读写的开源JAVA库软件。现在已更新到了3.6版本。新的版本提供了更少的内存消耗以及在改善对电子表格格式的处理性能。根据开发者，相比先前版本，POI3.6在处理电子表格数据时...
Java 操作Excel poi读写excel，所需要的jar包 https://blog.csdn.net/u014646662/article/details/83217382
poi api 3.6.chm,poi最新版本chm格式api文档
poi3.9读写EXCEL 支持2007
ExcelDemo_Excel导出_下载_POI读excel_JXL读写excel_java读写excel_列宽_读取合并或拆分单元格内容
用于java导出生成excel，excel导出jsp的图片