Question

JAVA编程中用Apache POI 怎么用SXSSFWorkbook对已存在的excel（.xlsx）操作进行写数据操作

想用POI来读取和写入excel2007文件，但是网上找了半天只找到对已经存在的excel2003格式文件的内容追加的方式，但是我要写的数据很多，只能用SXSSFWorkbook格式而不是HSSFWorkbook，此外，我是想对本地已存在的excel进行增加数据，而不是像网上那样创建一个新的excel文件，请问这个该怎么办？

网上找了半天只找到对已经存在的excel2003格式文件的内容追加的方式，如下：
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.hssf.model.Workbook;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class Test11 {

public static void main(String[] args) throws Exception {
FileInputStream fs=new FileInputStream("d://test.xls");
POIFSFileSystem ps=new POIFSFileSystem(fs);
HSSFWorkbook wb=new HSSFWorkbook(ps);
HSSFSheet sheet=wb.getSheetAt(0);
HSSFRow row=sheet.getRow(0);
System.out.println(sheet.getLastRowNum()+" "+row.getLastCellNum());
FileOutputStream out=new FileOutputStream("d://workbook.xls");
row=sheet.createRow((short)(sheet.getLastRowNum()+1));
row.createCell(17).setCellValue(9.2);
row.createCell(20).setCellValue(9);

out.flush();
wb.write(out);
out.close();
System.out.println(row.getPhysicalNumberOfCells()+" "+row.getLastCellNum());
}
}

但是对于excel2007的操作需要用XSSF，我照葫芦画瓢，发现不行呢。
像 HSSFWorkbook wb=new HSSFWorkbook(ps); 直接换成是 XSSFWorkbook wb=new XSSFWorkbook(ps); 提示是错误的。

Answer 1

XSSFWorkbook wb=new XSSFWorkbook(参数);中的参数是InputStream ，你直接XSSFWorkbook wb=new XSSFWorkbook(fs);就可以了。

第一步查询数据--这一步读者自行实现自己的数据查询 List<PointInfo> points = null;

points = this.dao.getAllCollect(userId);

final Map<String, List<PointInfo>> pointMap = new HashMap<>();

for (final PointInfo pointInfo : points) {

final String pt = pointInfo.getPointType(); if (pointMap.containsKey(pt)) {final List<PointInfo> subList = pointMap.get(pt);

subList.add(pointInfo);

} else {final List<PointInfo> subList = new ArrayList<>();subList.add(pointInfo);

pointMap.put(pt, subList

第二步：生成工作簿

final SXSSFWorkbook wb = new SXSSFWorkbook();

// 对每一种类型生成一个sheet

for (final Map.Entry<String, List<PointInfo>> entry : pointMap.entrySet()) {

final List<PointInfo> pts = entry.getValue();

// 获取每种类型的名字--作为sheet显示名称--如果不需要分sheet可忽略

String typeName = "";

if (this.dao.getTypeByTypeCode(pts.get(0).getPointType()) != null) {

typeName = this.dao.getTypeByTypeCode(pts.get(0).getPointType()).getPointTypeName();

}

final Sheet sheet = wb.createSheet(typeName);

//生成用于插入图片的容器--这个方法返回的类型在老api中不同

final Drawing patriarch = sheet.createDrawingPatriarch();

// 为sheet1生成第一行，用于放表头信息

final Row row = sheet.createRow(0);

// 第一行的第一个单元格的值

Cell cell = row.createCell((short) 0);

cell.setCellValue("详细地址");

cell = row.createCell((short) 1);

cell.setCellValue("经度");

cell = row.createCell((short) 2);

cell.setCellValue("纬度");

cell = row.createCell((short) 3);

for (int i = 0; i < pts.size(); i++) {

final Row each = sheet.createRow(i + 1);

Cell infoCell = each.createCell((short) 0);

infoCell.setCellValue(pts.get(i).getAddrDetail());

infoCell = each.createCell((short) 1);

infoCell.setCellValue(pts.get(i).getX());

infoCell = each.createCell((short) 2);

infoCell.setCellValue(pts.get(i).getY());

infoCell = each.createCell((short) 3);

//查询获取图片路径信息--该步读者自定义

PointPic pic = this.dao.getPicInfoByPointId(pts.get(i).getId());

try {

if (pic != null) {

for (int k = 0; k < 6; k++) {//因为有六张图片，所以循环6次

final short colNum = (short) (4+k);

infoCell = each.createCell(colNum);

BufferedImage img = null;

switch (k) {

case 0:

if (!StringUtils.isEmpty(pic.getPicOneAddr())) {

File imgFile = new File(pic.getPicOneAddr());

img = ImageIO.read(imgFile);

imgFile = null;

}

break;

case 1:

if (!StringUtils.isEmpty(pic.getPicTwoAddr())) {

File imgFile = new File(pic.getPicTwoAddr());

img = ImageIO.read(imgFile);

imgFile = null;

}

break;

case 2:

if (!StringUtils.isEmpty(pic.getPicThreeAddr())) {

File imgFile = new File(pic.getPicThreeAddr());

img = ImageIO.read(imgFile);

imgFile = null;

}

break;

case 3:

if (!StringUtils.isEmpty(pic.getPicFourAddr())) {

File imgFile = new File(pic.getPicFourAddr());

img = ImageIO.read(imgFile);

imgFile = null;

}

break;

case 4:

if (!StringUtils.isEmpty(pic.getPicFiveAddr())) {

File imgFile = new File(pic.getPicFiveAddr());

img = ImageIO.read(imgFile);

imgFile = null;

}

break;

case 5:

if (!StringUtils.isEmpty(pic.getPicSixAddr())) {

File imgFile = new File(pic.getPicSixAddr());

img = ImageIO.read(imgFile);

imgFile = null;

}

break;

}

ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();

ImageIO.write(img, "jpg", byteArrayOut);

img = null;

//设置每张图片插入位置

final XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, colNum,

i + 1, (short) (colNum + 1), i + 2);//参数为图片插入在表格的坐标，可以自行查看api研究参数

anchor.setAnchorType(0);

// 插入图片

patriarch.createPicture(anchor, wb.addPicture(

byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));

byteArrayOut.close();

byteArrayOut = null;

}

pic = null;

}

} catch (final Exception e) {

e.printStackTrace();

}

}

}

final ByteArrayOutputStream os = new ByteArrayOutputStream();

try {

wb.write(os);

} catch (final IOException e) {

e.printStackTrace();

}

final byte[] content = os.toByteArray();

final String url = Var.BASE_URL+ File.separator + "output.xls";//读者自定义路径

final File file = new File(url);// Excel文件生成后存储的位置。

OutputStream fos = null;

try {

fos = new FileOutputStream(file);

fos.write(content);

os.close();

fos.close();

} catch (final Exception e) {

e.printStackTrace();

}

return url;//文件保存成功

Answer 2

public SXSSFWorkbook(XSSFWorkbook workbook)

• Construct a workbook from a template.

  There are three use-cases to use
  SXSSFWorkbook(XSSFWorkbook) :

• Append new sheets to existing workbooks. You can open existing workbook from
  a file or create on the fly with XSSF.

• Append rows to existing sheets. The row number MUST be greater than
  max(rownum) in the template sheet.

• Use existing workbook as a template and re-use global objects such as cell
  styles, formats, images, etc.

All three use cases can work in a
combination. What is not supported:

• Access initial cells and rows in the template. After constructing SXSSFWorkbook(XSSFWorkbook) all internal windows are empty and SXSSFSheet.getRow(int) and SXSSFRow.getCell(int) return null.

• Override existing cells and rows. The API silently allows that but the
  output file is invalid and Excel cannot read it.

API文档里面有，反正就是说，使用SXSSFWorkbook附加数据到已经存在的Excel中的话就是不行的；SXSSFSheet.getRow(int) 和 SXSSFRow.getCell(int) 会返回空。

SXSSFWorkbook只能用在新创建Excel中才行。

Answer 3

XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(fileName));
SXSSFWorkbook wb = new SXSSFWorkbook(xwb,100);
SXSSFSheet sxssfSheet = wb.getSheetAt(1);
SXSSFRow sxssfRow = sxssfSheet.getRow(1);
SXSSFCell sxssfCell = sxssfRow.getCell(1);
sxssfCell.getStringCellValue();
XSSFSheet xSheet = xwb.getSheetAt(1); // 获取excel表的sheet
for (Map.Entry cEntry : cMap.entrySet()) {
String[] aString = cEntry.getKey().toString().split("_");
String ip = "10.240." + aString[3] + "." + aString[4];
System.out.println(ip+"---vale---");
int line = Integer.parseInt(cEntry.getKey().toString().substring(0, cEntry.getKey().toString().indexOf("$")));
XSSFCell l2 = xSheet.getRow(line).getCell(3);
XSSFRow row222 = xSheet.getRow(line);
if(ipMap.get(ip)!=null){
l2.setCellValue(cEntry.getValue() + ipMap.get(ip));
}else{代码如上：SXSSFCell sxssfCell = sxssfRow.getCell(1);NullPointerException，也就是说只能用XSSFWorkbook去读用SXSSFWorkbook创建一个新的表去写

Answer 4

XSSFWorkbook wb=new XSSFWorkbook(参数);中的参数是InputStream ，你直接XSSFWorkbook wb=new XSSFWorkbook(fs);就可以了。

Answer 5

XSSFWorkbookwb=newXSSFWorkbook(参数);中的参数是InputStream，你直接XSSFWorkbookwb=newXSSFWorkbook(fs);就可以了。