JAVA编程中用Apache POI 怎么用SXSSFWorkbook对已存在的excel（.xlsx）操作进行写数据操作
网上找了半天只找到对已经存在的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); 提示是错误的。 展开
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;//文件保存成功
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.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.
All three use cases can work in a
combination. What is not supported:
API文档里面有，反正就是说，使用SXSSFWorkbook附加数据到已经存在的Excel中的话就是不行的；SXSSFSheet.getRow(int) 和 SXSSFRow.getCell(int) 会返回空。
SXSSFWorkbook只能用在新创建Excel中才行。
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创建一个新的表去写
