POI中HSSF和XSSF操作Excel
2024-08-24 20:51:37
POI中HSSF和XSSF操作Excel
在公司实习快一个月了,这段时间公司业务要用JAVA操作复杂的Excel报表.刚开始的Excel还好,没有涉及到复杂的图表,所以使用JXL操作Excel,但是到后来涉及到复杂的图表和单元格公式后JXL就显得无力了.
公司业务需要在原有的Excel模板上填写从数据库读出来的数据,然后保存为新文件让客户下载.最后在业务的每个流程环节上添加签名图片,而且模板复杂所以只有使用Apache的POI来操作Excel.
在刚接触使用POI时,因为Excel模板格式是用的97-2003的板式(后缀名是.xls),所以使用HSSF来操作Excel,我很傻很天真的认为是Apache的幽默把微软的老版Excel称为Horrible SpreadSheet Format,即"讨厌的电子表格格式",后来我才慢慢发现了HSSF操作有多张图表的Excel也很让人郁闷.
首先是HSSF读取Excel
public class ExcelEditor {
private HSSFWorkbook book;
private String excelRealPath;
public ExcelEditor(String excelRealPath)throws IOException{
this.excelRealPath = excelRealPath;
FileInputStream fis = new FileInputStream(excelRealPath);
book = new HSSFWorkbook(bis);
fis.close();
}
}
但是有时候在读取的时候会抛出Unable to read entire block；0 bytes read; expected 512 bytes 异常,而且到现在没有发现一个好的解决方案,最后拜托谷老师,找到了一个解决方案:
public class ExcelEditor {
private HSSFWorkbook book;
private String excelRealPath;
public ExcelEditor(String excelRealPath) throws IOException {
this.excelRealPath = excelRealPath;
FileInputStream fis = new FileInputStream(excelRealPath);
ByteArrayInputStream bis = this.converter(fis);
book = new HSSFWorkbook(bis);
bis.close();
fis.close();
}
/**
* @param fis 将文件流转换成字节流可以解决问题
* @return
* @throws IOException
*/
private ByteArrayInputStream converter(FileInputStream fis)
throws IOException {
ByteArrayInputStream byteArrayInputStream = null;
byte buf[] = org.apache.commons.io.IOUtils.toByteArray(fis);
byteArrayInputStream = new ByteArrayInputStream(buf);
return byteArrayInputStream;
}
}
像这样转一道流解决了抛异常的问题,没有经过长时间测试,我的操作类为:
public class ExcelEditor {
private HSSFWorkbook book;
private String excelRealPath;
private Map<Integer,ExcelSheet> sheets = new HashMap<Integer,ExcelSheet>();
public ExcelEditor(String excelRealPath)throws IOException{
this.excelRealPath = excelRealPath;
FileInputStream fis = new FileInputStream(excelRealPath);
ByteArrayInputStream bis = this.converter(fis);
book = new HSSFWorkbook(bis);
bis.close();
fis.close();
}
/** * @param fis 将文件流转换成字节流可以解决问题 * @return * @throws IOException */
private ByteArrayInputStream converter(FileInputStream fis) throws IOException {
ByteArrayInputStream byteArrayInputStream = null;
byte buf[] = org.apache.commons.io.IOUtils.toByteArray(fis);
byteArrayInputStream = new ByteArrayInputStream(buf);
return byteArrayInputStream;
}
public HSSFWorkbook getHSSFWorkbook(){
return book;
}
/**
* @param index 將Sheet的操作和Workbook分离,在ExcelEditor中用一个Map保存Sheet的引用
* @return
*/
public ExcelSheet getSheet(int index){
validateSheetIndex(index);
ExcelSheet sheet;
if(sheets.containsKey(index)){
sheet = sheets.get(index);
}else{
sheet = new ExcelSheet(book.getSheetAt(index));
sheets.put(index, sheet);
}
return sheet;
}
private void validateSheetIndex(int index) {
int lastSheetIx = book.getNumberOfSheets() - 1;
if (index < 0 || index > lastSheetIx) {
throw new IllegalArgumentException("Sheet index ("+ index +") is out of range (0.." + lastSheetIx + ")");
}
}
public void writeExcel(String path) throws IOException{
FileOutputStream out = new FileOutputStream(path);
book.write(out);
out.close();
}
public void save() throws IOException{
this.writeExcel(excelRealPath);
}
}
ExcelEditer提供了基本的读取,保存等操作,而下面的ExcelSheet则提供了对Sheet的操作:
public class ExcelSheet {
private HSSFSheet sheet;
public ExcelSheet(HSSFSheet sheet) {
this.sheet = sheet;
}
private HSSFCell getCell(int rowNum, int column) {
HSSFRow row = sheet.getRow(rowNum);
if (row == null) {
row = sheet.createRow(rowNum);
}
HSSFCell c = row.getCell(column);
if (c == null) {
c = row.createCell(column);
}
return c;
}
public void setGraphic(BufferedImage bi, HSSFClientAnchor anchor,boolean resize) throws IOException {
// anchor.setAnchorType(HSSFClientAnchor.DONT_MOVE_AND_RESIZE);
ByteArrayOutputStream baos = new ByteArrayOutputStream();
ImageIO.write(bi, "jpg", baos);
HSSFPatriarch printer = this.sheet.createDrawingPatriarch();
HSSFPicture picture = printer.createPicture(anchor, sheet.getWorkbook().addPicture(baos.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
if (resize) {
picture.resize();
}
baos.close();
}
public HSSFCell setString(int rownum, int column, String value) {
HSSFCell xcell = this.getCell(rownum, column);
xcell.setCellValue(value);
return xcell;
}
public HSSFCell setInt(int rownum, int column, int value) {
HSSFCell xcell = this.getCell(rownum, column);
xcell.setCellValue(value);
return xcell;
}
public HSSFCell setDouble(int rownum, int column, double value) {
HSSFCell xcell = this.getCell(rownum, column);
xcell.setCellValue(value);
return xcell;
}
public HSSFCell setBoolean(int rownum, int column, boolean value) {
HSSFCell xcell = this.getCell(rownum, column);
xcell.setCellValue(value);
return xcell;
}
public HSSFCell setText(int rownum, int column, RichTextString value) {
HSSFCell xcell = this.getCell(rownum, column);
xcell.setCellValue(value);
return xcell;
}
public HSSFCell setDate(int rownum, int column, Date value) {
HSSFCell xcell = this.getCell(rownum, column);
xcell.setCellValue(value);
return xcell;
}
public HSSFCell setCalendar(int rownum, int column, Calendar value) {
HSSFCell xcell = this.getCell(rownum, column);
xcell.setCellValue(value);
return xcell;
}
}
这两个类对付一般的有单元格公式的Sheet没有问题,但是遇到图表麻烦就出现了.项目中有一个模板是这样的:
在向这个模板插入新图片时把原来图表的内容给弄不见了,而且把格式搞得一塌糊涂. 之后发现是createDrawingPatriarch()这个方法的问题,在API中是这样解释的:
Creates the top-level drawing patriarch. This will have the effect of removing any existing drawings on this sheet. This may then be used to add graphics or charts
没办法,调用这个方法会把Chart整个Remove掉,换一种方法调用getDrawingPatriarch(),结果发现一样的效果.
测试了一下午,依然没有发现解决的问题,到这个时候我总算体会到了这SeparateSheetFormat是如此的可怕,Apache的良苦用心啊..
最后只有把目光转向XSSF,XSSF是第二代的Excel格式,也是微软公司支持Open Document Format(开放式文档格式)的一个改版.其后缀名是.xlsx,最后那个X的含义貌似是代表XML吧.有同学不懂Open Document Format的请问谷老师.懂的同学把后缀名改为.zip然后解压出来你就豁然开朗了.
不扯那么远了,最终的两个类为:
public class ExcelEditor {
private XSSFWorkbook book;
private OPCPackage opc;
private String excelRealPath;
private Map<Integer,ExcelSheet> sheets = new HashMap<Integer,ExcelSheet>();
/**
* 新版的采用OPCPackage作为文件操作类包括.xlsx和.docx都可以用这个类去读取文件
* @param excelRealPath
* @throws IOException
* @throws InvalidFormatException
*/
public ExcelEditor(String excelRealPath)throws IOException, InvalidFormatException {
this.opc = OPCPackage.open(excelRealPath);
this.book = new XSSFWorkbook(opc);
this.excelRealPath = excelRealPath;
}
public XSSFWorkbook getXSSFWorkbook(){
return book;
}
public ExcelSheet getSheet(int index){
validateSheetIndex(index);
ExcelSheet sheet;
if(sheets.containsKey(index)){
sheet = sheets.get(index);
}else{
sheet = new ExcelSheet(book.getSheetAt(index));
sheets.put(index, sheet);
}
return sheet;
}
private void validateSheetIndex(int index) {
int lastSheetIx = book.getNumberOfSheets() - 1;
if (index < 0 || index > lastSheetIx) {
throw new IllegalArgumentException("Sheet index ("+ index +") is out of range (0.." + lastSheetIx + ")");
}
}
public void writeExcel(String path) throws FileNotFoundException,IOException {
File excelFile = new File(path);
if(excelFile.exists()){
String extension;
String fileName;
if(path.lastIndexOf(".")!=-1 && ".xlsx".equalsIgnoreCase(path.substring(path.lastIndexOf(".")))){
extension = path.substring(path.lastIndexOf("."));
fileName = path.substring(0,path.lastIndexOf("."));
}else{
throw new IOException("Xlsx file output only,check your path!!!");
}
File tempFile = new File(fileName+"T"+extension);
FileOutputStream fos = new FileOutputStream(tempFile);
book.write(fos);
fos.close();
opc.revert();
excelFile.delete();
tempFile.renameTo(new File(path));
}else{
FileOutputStream fos = new FileOutputStream(excelFile);
book.write(fos);
fos.close();
}
}
public void saveExcel() throws IOException{
this.writeExcel(this.excelRealPath);
}
}
我在XSSFWorkbook中没有找到保存Excel的方法,之后在发现OPCPackage中发现了revert()和close()方法.revert()个是将打开的Excel还原,不保存任何的修改,close()则是保存已经修改的操作.
close()在第二天运行的时候无法正确保存文件,导致close()之后再打开抛出异常,问了谷老师也不知道是什么原因,最后只有把源文件还原revert()然后保存为新文件之后在删掉.
操作XSSFSheet的类为:
public class ExcelSheet{
private XSSFSheet sheet;
public ExcelSheet(XSSFSheet sheet){
this.sheet = sheet;
}
private XSSFCell getCell(int rowNum, int column) {
XSSFRow row = sheet.getRow(rowNum);
if (row == null) {
row = sheet.createRow(rowNum);
}
XSSFCell c = row.getCell(column);
if (c == null) {
c = row.createCell(column);
}
return c;
}
public void setGraphicByAnchor(BufferedImage bi,XSSFClientAnchor anchor)throws IOException {
// anchor.setAnchorType(XSSFClientAnchor.DONT_MOVE_AND_RESIZE);
ByteArrayOutputStream baos = new ByteArrayOutputStream();
ImageIO.write(bi,"jpg", baos);
XSSFDrawing printer = this.sheet.createDrawingPatriarch();
XSSFPicture picture = printer.createPicture(anchor, sheet.getWorkbook().addPicture(baos.toByteArray(),XSSFWorkbook.PICTURE_TYPE_JPEG));
picture.resize();
baos.close();
}
public XSSFCell setString(int rownum, int column, String value) {
XSSFCell xcell = this.getCell(rownum, column);
xcell.setCellValue(value);
return xcell;
}
public XSSFCell setInt(int rownum, int column, int value) {
XSSFCell xcell = this.getCell(rownum, column);
xcell.setCellValue(value);
return xcell;
}
public XSSFCell setDouble(int rownum, int column, double value) {
XSSFCell xcell = this.getCell(rownum, column);
xcell.setCellValue(value);
return xcell;
}
public XSSFCell setBoolean(int rownum, int column, boolean value) {
XSSFCell xcell = this.getCell(rownum, column);
xcell.setCellValue(value);
return xcell;
}
public XSSFCell setText(int rownum, int column, RichTextString value) {
XSSFCell xcell = this.getCell(rownum, column);
xcell.setCellValue(value);
return xcell;
}
public XSSFCell setDate(int rownum, int column, Date value) {
XSSFCell xcell = this.getCell(rownum, column);
xcell.setCellValue(value);
return xcell;
}
public XSSFCell setCalendar(int rownum, int column, Calendar value) {
XSSFCell xcell = this.getCell(rownum, column);
xcell.setCellValue(value);
return xcell;
}
}
@原文引入：http://www.cnblogs.com/rockcookies/archive/2012/05/15/2502169.html
最新文章
- 分布式服务协调员zookeeper - 应用场景和监控
- Hue
- gulp实用插件总结
- dll显式加载与隐式加载
- ios 加载本地html css文件 ps:css和html必须在同一文件夹下面
- 比较好的自学IT的网站
- 怎么给qt程序添加版本信息
- Memcached安装，操作，用C#操作
- Unqualified name lookup
- NYOJ115 市叛乱 【SPFA】
- JMeter处理Cookie与Session
- 51nod_1119:机器人走方格 V2
- 糖果大战 hdu1204
- Python多进程应用
- 【ASP.NET Core快速入门】（八）Middleware管道介绍、自己动手构建RequestDelegate管道
- Js返回上一页，刷新页面，定时刷新，改变地址栏 等常用实用技巧
- Nmap的详细使用
- 如何使用JDBC查询所有记录
- 【linux】常见问题&常用命令笔记
- sublime常用插件及配置