Page 1 of 1

Excel size decreases on resaving.

PostPosted: Thu Apr 23, 2020 11:48 pm
by ashanaik
Hi Jason ,

When i generate excel using below code the file size is 79kb. (STEP 1)

If i open the excel, do nothing and save it. The file size decreases to 33kb. (STEP 2)

What could be reason for it and how can i avoid this?

After extracting the excels i obeserved that theres size variation for drawing.xml's. Step 2 Xml is formatted and has leading and trailing spaces. Where as Step 1 xmls are TRIMMED .

Code :

public static void main(String[] args) throws IOException, Exception {

String outputfilepath =System.getProperty("user.dir") + "/sample-docs/outline5.xlsx";
String imagefilePath = "D:\\Checkout\\Images\\image.jpg" ;

SpreadsheetMLPackage pkg = SpreadsheetMLPackage.createPackage();
WorksheetPart worksheet = pkg.createWorksheetPart(new PartName("/xl/worksheets/sheet1.xml"), "Sheet1", 1);

// Create Drawing part and add to sheet
Drawing drawingPart = new Drawing();
Relationship drawingRel = worksheet.addTargetPart(drawingPart);

// Add anchor XML to worksheet
org.xlsx4j.sml.CTDrawing drawing = org.xlsx4j.jaxb.Context.getsmlObjectFactory().createCTDrawing();
worksheet.getJaxbElement().setDrawing(drawing);
drawing.setId( drawingRel.getId() );

// Create image part and add to Drawing part
BinaryPartAbstractImage imagePart
= BinaryPartAbstractImage.createImagePart(pkg, drawingPart,
FileUtils.readFileToByteArray(new File(imagefilePath) ));
String imageRelID = imagePart.getSourceRelationship().getId();

drawingPart.setJaxbElement(
buildDrawingPartContentFromXmlString(imageRelID));

// Save the xlsx
SaveToZipFile saver = new SaveToZipFile(pkg);
saver.save(outputfilepath);
System.out.println("\n\n done .. " + outputfilepath);
}

public static org.docx4j.dml.spreadsheetdrawing.CTDrawing buildDrawingPartContentFromXmlString(String imageRelID) throws JAXBException {

String openXML="<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?> <xdr:wsDr xmlns:xdr=\"http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing\" xmlns:a=\"http://schemas.openxmlformats.org/drawingml/2006/main\"> <xdr:twoCellAnchor editAs=\"oneCell\"> <xdr:from> <xdr:col>3</xdr:col> <xdr:colOff>0</xdr:colOff> <xdr:row>3</xdr:row> <xdr:rowOff>0</xdr:rowOff> </xdr:from> <xdr:to> <xdr:col>5</xdr:col> <xdr:colOff>104775</xdr:colOff> <xdr:row>7</xdr:row> <xdr:rowOff>142875</xdr:rowOff> </xdr:to> <xdr:pic> <xdr:nvPicPr> <xdr:cNvPr id=\"2\" name=\"Picture 1\"> <a:extLst> <a:ext uri=\"{FF2B5EF4-FFF2-40B4-BE49-F238E27FC236}\"> <a16:creationId xmlns:a16=\"http://schemas.microsoft.com/office/drawing/2014/main\" id=\"{00000000-0008-0000-0000-000002000000}\"/> </a:ext> </a:extLst> </xdr:cNvPr> <xdr:cNvPicPr> <a:picLocks noChangeAspect=\"1\"/> </xdr:cNvPicPr> </xdr:nvPicPr> <xdr:blipFill> <a:blip xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\" r:embed=\"rId1\" cstate=\"print\"> <a:extLst> <a:ext uri=\"{28A0092B-C50C-407E-A947-70E740481C1C}\"> <a14:useLocalDpi xmlns:a14=\"http://schemas.microsoft.com/office/drawing/2010/main\" val=\"0\"/> </a:ext> </a:extLst> </a:blip> <a:stretch> <a:fillRect/> </a:stretch> </xdr:blipFill> <xdr:spPr> <a:xfrm rot=\"18839999\"> <a:off x=\"0\" y=\"0\"/> <a:ext cx=\"714375\" cy=\"714375\"/> </a:xfrm> <a:prstGeom prst=\"rect\"> <a:avLst/> </a:prstGeom> </xdr:spPr> </xdr:pic> <xdr:clientData/> </xdr:twoCellAnchor> </xdr:wsDr>";
return (org.docx4j.dml.spreadsheetdrawing.CTDrawing)XmlUtils.unwrap(
XmlUtils.unmarshalString(openXML));
}