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

Free Support Forum - aspose.com

Two Problems using SheetRender

sheetToImage used to work, but now that I'm using SheetRender, I'm having two problems. The first is that the toImage method is creating an image for the entire sheet, not just the range of cells. The second is that I'm getting a Unknown image format:Parameter is not valid. message using the AddPictureInChart method when using a stream object that was created using the ToImage method. See code towards the end for the issues (bulk of code is creating sample data).

            Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense("Aspose.Total.lic");

Workbook workbook = new Workbook();
Worksheet dataSheet = workbook.Worksheets[0];
dataSheet.Name = "data";
DataTable dt1 = new DataTable();
dt1.Columns.Add("period", Type.GetType("System.String"));
dt1.Columns.Add("S1R", Type.GetType("System.Decimal"));
dt1.Columns.Add("S1E", Type.GetType("System.Decimal"));
dt1.Columns.Add("S1C", Type.GetType("System.Decimal"));

DataRow dr = dt1.NewRow();
dr[0] = "JULY";
dr[1] = 0.123;
dr[2] = 0.388;
dr[3] = 0.7877;
dt1.Rows.Add(dr);
dr = dt1.NewRow();
dr[0] = "AUGUST";
dr[1] = 0.423;
dr[2] = 0.288;
dr[3] = 0.1877;
dt1.Rows.Add(dr);
dr = dt1.NewRow();
dr[0] = "SEPTEMBER";
dr[1] = 0.723;
dr[2] = 0.988;
dr[3] = 0.5877;
dt1.Rows.Add(dr);
dataSheet.Cells.ImportDataTable(dt1, true, "A1");
int i = workbook.Worksheets.Add(SheetType.Chart);
Worksheet chartSheet = workbook.Worksheets[i];
chartSheet.Name = "Chart";
int chartIndex = chartSheet.Charts.Add(ChartType.ScatterConnectedByLinesWithDataMarker, 5, 0, 40, 20);
Chart chart = chartSheet.Charts[chartIndex];
dataSheet.Move(1);
chart.NSeries.Add("'data'!B2:b4", true); // Rate
chart.NSeries[0].XValues = "'data'!A2:A4"; // Periods
chart.NSeries[0].Name = "ProductA";


// ADD TABLE as IMAGE
Aspose.Cells.Range range1 = dataSheet.Cells.CreateRange("A1", "D4");
range1.Worksheet.AutoFitRows();
dataSheet.PageSetup.PrintArea = "A1:D4";

//Changes default margin to 0
dataSheet.PageSetup.TopMargin = 0;
dataSheet.PageSetup.BottomMargin = 0;
dataSheet.PageSetup.LeftMargin = 0;
dataSheet.PageSetup.RightMargin = 0;
dataSheet.IsGridlinesVisible = true;

MemoryStream stream = new MemoryStream();

chart.PlotArea.X = 10;
chart.PlotArea.Width = chart.PlotArea.Width + 25;

int l = chart.PlotArea.X;
int t = chart.PlotArea.Y;
int w = chart.PlotArea.Width;
int h = chart.PlotArea.Height;
chart.PlotArea.Height = (int)(h * .8);
chart.PlotArea.X = 50;
t = (int)((t + h) * .8);
h = (int)(h * .2);

Aspose.Cells.Rendering.ImageOrPrintOptions options = new ImageOrPrintOptions();
options.ImageFormat = System.Drawing.Imaging.ImageFormat.Bmp;
options.IsImageFitToPage = true; // only output cells with data

SheetRender sr = new SheetRender(dataSheet, options);
Bitmap bitmap = sr.ToImage(0);
bitmap.Save(@"C:\test.bmp"); // this file is the size of the entire page instead of just the range
sr.ToImage(1, stream);
chart.Shapes.AddPictureInChart(0, 0, stream, 77, 100);// ERROR: Unknown image format:Parameter is not valid.
chart.Shapes[0].TopInShape = 4000 - chart.Shapes[0].HeightInShape - 400;
chart.Shapes[0].LeftInShape = 50;
chart.Calculate();

workbook.Save(@"C:\test.xls", SaveFormat.Excel97To2003);

Hi,

1) Please change your line of code i.e…,

options.IsImageFitToPage = true; 
to:
options.OnePagePerSheet = true;

2) Since there is only one page rendered for the data sheet, so your code i.e…,
sr.ToImage(1, stream); will give your an error.

Please change your line of code to:

sr.ToImage(0, stream);

Thank you.

Thanks. That worked! The documentation appears to be a little misleading then. It appears IsImageFitToPage is the only property that discusses issues about which cells to include in the renderering.

IsImageFitToPage: When set the value to true, the page only include the cells that have data. The default value is false.
OnePagePerSheet: If OnePagePerSheet is true , all content of one sheet will output to only one page in tiff. The paper size of pagesetup will be invalid, and the other settings of pagesetup will still taking effect.