We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Excel compression - how much

I am looking for information about how much compression can be done by Aspose for Excel sheets for example 50MB file compressed to 1MB etc.

@sanjay.agrawal,

Aspose.Cells is a spreadsheet or MS Excel file format manipulation API rather than compression tool. Aspose.Cells might render Excel files with size a fraction less (little less) than what MS Excel produces but not to that extent (50MB file compressed to 1MB etc.).

Thanks Amjad. Could you suggest me an excel compression library for Java.

@sanjay.agrawal,

How could you do this in MS Excel manually? You can provide details with sample Excel file(s). If MS Excel can do this, Aspose.Cells can do this as well.

For general documents compression and archive manipulation, you may try our other Aspose.ZIP for .NET API for standard ZIP format.

@sanjay.agrawal
a) xls (Excel97-2003) is a structured binary file. You can only compress files into archives.

b) xlsx, xlsb, xlsm (since 2007) are compressed files containing files, you can specify the compression ratio and reduce unnecessary data to compress files.

        OoxmlSaveOptions saveOptions = new OoxmlSaveOptions();
        saveOptions.ExportCellName = false;
        saveOptions.CompressionType = OoxmlCompressionType.Level9;
        workbook.Save(dir + "dest.xlsx");

c) Compress the images in the file with a graphics compression algorithm.

Thanks Simon/Amjad

Could you help me with a code sample to compress all images in an excel file using Aspose cells.

I am trying to get similar behavior as provided by MS-Excel under Picture Tools , Format tab using Compress Pictures to compress all pictures.

@sanjay.agrawal
Please try the following codes to compress images:

static void Main(string[] args)
{
    var workbook = new Workbook(dir +"book2.xlsx");
    //Compress scale
    int flag = 50;

    foreach (Worksheet sheet in workbook.Worksheets)
    {
        foreach (Picture p in sheet.Pictures)
        {
            if (p.ImageType == ImageType.Emf || p.ImageType == ImageType.Wmf)
            {
                continue;
            }

            GetPicThumbnail(p, flag);
        }
    }
    workbook.Save(dir + "dest.xlsx");
}

private static void GetPicThumbnail(Picture pic, int flag)
{
    var data = pic.Data;
    if (data == null)
    {
        return;
    }

    MemoryStream ms = new MemoryStream(data);
    var iSource = Image.FromStream(ms);
    var tFormat = iSource.RawFormat;
    int sW, sH;
    // we can not simply use Picture.Width and Picture.Height as the size of image for rotation in cells.
    var r = flag / 100d;
    var dHeight = Convert.ToInt32(Math.Ceiling(iSource.Height * r)); // > pic.Height ? pic.Height : iSource.Height;
    var dWidth = Convert.ToInt32(Math.Ceiling(iSource.Width * r)); //> pic.Width ? pic.Width : iSource.Width;
    // var dHeight = iSource.Height / 2;
    // var dWidth = iSource.Width / 2;
    // Scale proportionally 
    var temSize = new Size(iSource.Width, iSource.Height);

    if (temSize.Width > dHeight || temSize.Width > dWidth)
    {
        if (temSize.Width * dHeight > temSize.Width * dWidth)
        {
            sW = dWidth;
            sH = dWidth * temSize.Height / temSize.Width;
        }
        else
        {
            sH = dHeight;
            sW = temSize.Width * dHeight / temSize.Height;
        }
    }
    else
    {
        sW = temSize.Width;
        sH = temSize.Height;
    }

    var ob = new Bitmap(dWidth, dHeight);
    var g = Graphics.FromImage(ob);

    g.Clear(Color.WhiteSmoke);
    g.CompositingQuality = CompositingQuality.HighQuality;
    g.SmoothingMode = SmoothingMode.HighQuality;
    g.InterpolationMode = InterpolationMode.HighQualityBicubic;

    g.DrawImage(iSource, new Rectangle((dWidth - sW) / 2, (dHeight - sH) / 2, sW, sH), 0, 0, iSource.Width, iSource.Height, GraphicsUnit.Pixel);

    g.Dispose();
    // The following code sets the compression quality when saving pictures
    var ep = new EncoderParameters();
    var qy = new long[1];
    qy[0] = flag; // Set the compression ratio 1-100
    var eParam = new EncoderParameter(Encoder.Quality, qy);
    ep.Param[0] = eParam;
    try
    {
        var imageCodecInfos = ImageCodecInfo.GetImageEncoders();
        var imageCodecInfo = imageCodecInfos.FirstOrDefault(t => t.FormatDescription.Equals("JPEG"));

        using (MemoryStream outStream = new MemoryStream())
        {
            if (imageCodecInfo != null)
            {
                ob.Save(outStream, imageCodecInfo, ep);
            }
            else
            {
                ob.Save(outStream, tFormat);
            }

            outStream.Flush();
            pic.Data = outStream.ToArray();
        }

    }
    catch
    {
        // return false;
    }
    finally
    {
        iSource.Dispose();
        ob.Dispose();
    }
}

Many thanks Simon. I will try this and revert.

@simon.zhao Thanks for sharing the code snippet, Could you help me with imports of the same as i am unable to find the imports for some classes like MemoryStream.

@prateek.chouhan25,

MemoryStream belongs to System.IO namespace. Which other classes for which you could not evaluate?

Thanks Amjad. We are using Aspose for Java platform. Could you suggest where in Aspose Java MemoryStream belongs to?

@sanjay.agrawal @prateek.chouhan25
There are two ways to compress image with Java.
1, Graphics

/**
 * compressImage
 * 
 * @param imageByte
 *            Image source array
 * @param ppi
 * @return
 */
public static byte[] compressImage(byte[] imageByte, int ppi) {
	byte[] smallImage = null;
	int width = 0, height = 0;

	if (imageByte == null)
		return null;

	ByteArrayInputStream byteInput = new ByteArrayInputStream(imageByte);
	try {
		Image image = ImageIO.read(byteInput);
		int w = image.getWidth(null);
		int h = image.getHeight(null);
		// adjust weight and height to avoid image distortion
		double scale = 0;
		scale = Math.min((float) ppi / w, (float) ppi / h);
		width = (int) (w * scale);
		width -= width % 4;
		height = (int) (h * scale);

		if (scale >= (double) 1)
			return imageByte;

		BufferedImage buffImg = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);
		buffImg.getGraphics().drawImage(image.getScaledInstance(width, height, Image.SCALE_SMOOTH), 0, 0, null);
		ByteArrayOutputStream out = new ByteArrayOutputStream();
		ImageIO.write(buffImg, "png", out);
		smallImage = out.toByteArray();
		return smallImage;

	} catch (IOException e) {
		log.error(e.getMessage());
		throw new RSServerInternalException("");
	}
}

2,Thumbnailator.
Please use google thumbnailator jar.

 /**
 * compressImage
 * 
 * @param path
 * @param ppi
 * @return
 */
 public static byte[] compressImage(String path, int ppi) {
      byte[] smallImage = null;

      try {
           ByteArrayOutputStream out = new ByteArrayOutputStream();
           Thumbnails.of(path).size(ppi, ppi).outputFormat("png").toOutputStream(out);
           smallImage = out.toByteArray();
           return smallImage;
      } catch (IOException e) {
           log.error(e.getMessage());
           throw new RSServerInternalException("");
      }
 }

Thanks Simon. We will try and this and update likewise.

@simon.zhao Thanks for the code snippet, however we are more interested to compress image inside a existing excel file. Do we have any such functionality implemented in Aspose java tool.

@prateek.chouhan25,

I am afraid, there is no such feature available in Aspose.Cells for Java API, so you have to compress images using suggested approaches and algorithms by yourselves.