ws.AllocatedRange.AutoFitRows() throws an error

[frostholm]

Specified argument was out of the range of valid values.
Parameter name: RightColumnOffset

!!!! EDITED : I think the real problem is this statement where i try to set autofilters on (without using a table)

ws.AutoFilters.Range = ws.Range[headerY, headerX, headerY, correctX - 2];

After calling the above method on the worksheet, it suddenly starts to give an exeption. It has worked before.

Is the "AllocatedRange" not set automatically ?

if i comment out the autofitrows line, i will instead get the same error when i try to save it (this also have worked) :

MemoryStream memoryStream = new MemoryStream();
workbook.SaveToStream(memoryStream, Spire.Xls.FileFormat.Version2010);

Any idear on the problem ?

Best regards
Michael

[sweety1]

Hello,

Thanks for your inquiry.
Could you please offer us your sample document and your sample code?
It would be helpful to replicate the issue and work out the solution for you ASAP.
If the information is confidential, you can send it to us ( Support@e-iceblue.com ) via email.

Best Regards,
Sweety
E-iceblue support team

[frostholm]

public void addDataGrids(Workbook woorkbook)
{
var ws = woorkbook.Worksheets.Add(this.sheetName);

int lastRow = 0;
int firstDataRow = 0;

if (sheetHeader != null)
lastRow = sheetHeader.createSheetHeader(ws);


int correctX = 1;
int correctY = 1;
int headerX = correctX;
int headerY = correctY;

lastRow += 2;
firstDataRow = lastRow;
int gridNo = 0;

foreach (var grid in dataGrids)
{

#region setting correct x and y values for datagrid

if (grid.cellX == null)
correctX = 1;
else
correctX = grid.cellX.Value;

if (grid.cellY == null)
correctY = lastRow - 1;
else
correctY = grid.cellY.Value;

headerX = correctX;
headerY = correctY;

#endregion

#region initialize DataTable

DataTable localTable = null;

if (grid.iQueryable != null)
{
localTable = grid.iQueryable.CopyToDataTable2();
}
else
if (grid.dataTable != null)
{
localTable = grid.dataTable;
}

#endregion

#region Insert table data

if (localTable != null)
{
foreach (var prop in localTable.Columns)
{
ws.SetCellValue(correctY, correctX, TextGetter.GetText(((DataColumn)prop).ColumnName, "dbattribs"));
correctX++;
}

correctY++;
ws.InsertDataTable(localTable, false, correctY, headerX);
correctY += localTable.Rows.Count - 1;

if (grid.showAsTable)
{
ws.ListObjects.Create("Table", ws.Range[headerY, headerX, correctY, correctX - 1]);
ws.Range[headerY, headerX, headerY, correctX - 1].Style.Color = System.Drawing.Color.FromArgb(0x002550);
ws.Range[headerY, headerX, headerY, correctX - 1].Style.Font.Color = Color.White;
ws.Range[headerY, headerX, headerY, correctX - 1].Style.Font.IsBold = true;

ws.ListObjects[gridNo].BuiltInTableStyle = TableBuiltInStyles.TableStyleLight9;

if (grid.showTotalsRow)
ws.ListObjects[gridNo].DisplayTotalRow = grid.showTotalsRow;
}
else
{
if (grid.includeHeaderFormatting)
{
// ws.AutoFilters.Range = ws.Range[headerY, headerX, headerY, correctX - 2];
// ws.AutoFilters.Range = ws.Range[headerY, headerX, correctY, correctX - 1];
ws.Range[headerY, headerX, headerY, correctX - 1].Style.Color = System.Drawing.Color.FromArgb(0x002550);
//ws.Range[headerY, headerX, headerY, correctX - 1].Style.Color = System.Drawing.Color.FromArgb(0x000033);
ws.Range[headerY, headerX, headerY, correctX - 1].Style.Font.Color = Color.White;
ws.Range[headerY, headerX, headerY, correctX - 1].Style.Font.IsBold = true;

}
else
{

}
}
}





#endregion


#region show data as a table


#endregion


#region format rows

if (grid.colorRows)
{

int endFormatRow = ws.LastRow;
int startFormatRow = headerY + 1;
int color = 0;

for (int ix = startFormatRow; ix < endFormatRow + 1; ix++)
{
if (color % 2 != 0)
{
ws.Range[ix, headerX, ix, correctX - 1].Style.Color = System.Drawing.Color.FromArgb(0xdbdbdb);
}

color++;
}

}

#endregion



lastRow = ws.LastRow + 2;
gridNo++;
}

int finalRow = lastRow;

if (sheetFooter != null)
finalRow = sheetFooter.createSheetFooter(ws);

ws.AllocatedRange.AutoFitColumns();
//ws.AllocatedRange.AutoFitRows();

#region Setup pages ettings

ws.PageSetup.RightFooter = TextGetter.GetText("Antal sider: ", "Excel");


//ws.PageSetup.Footer.Right.AddText(TextGetter.GetText("Antal sider: ", "Excel"), XLHFOccurrence.AllPages);
//ws.PageSetup.Footer.Right.AddText(XLHFPredefinedText.PageNumber, XLHFOccurrence.AllPages);
//ws.PageSetup.Footer.Right.AddText("/", XLHFOccurrence.AllPages);
//ws.PageSetup.Footer.Right.AddText(XLHFPredefinedText.NumberOfPages, XLHFOccurrence.AllPages);

ws.PageSetup.Orientation = PageOrientationType.Landscape;
//ws.PageSetup.PagesWide = 1;
ws.PageSetup.PaperSize = PaperSizeType.PaperA4;

ws.PageSetup.TopMargin = 0.5;
ws.PageSetup.BottomMargin = 0.5;
ws.PageSetup.LeftMargin = 0.2;
ws.PageSetup.RightMargin = 0.2;
ws.PageSetup.FooterMarginInch = 0.15;
ws.PageSetup.HeaderMarginInch = 0.15;

#endregion

}
}

[sweety1]

Hello,

Thanks for your code.
Could you please offer us your sample file?

Best Regards,
Sweety
E-iceblue support team

[frostholm]

What sample file ?

Have you read the updated first message in this thread ?

[Betsy]

Dear frostholm,

If you load a document and then operate it, the document is sample file, please offer us it. If you create a new document, please use following code to set document version when you create a new file:

Code: Select all

Wookbook.Version = ExcelVersion.Version2010;

Thanks,
Betsy
E-iceblue support team

[frostholm]

Here is a sample code for free version which crashes is this because of the free version or due to an error ?:

//Create Workbook

Workbook workbook = new Workbook();
workbook.Version = ExcelVersion.Version2010;

workbook.Worksheets.Clear();

Worksheet sheet = workbook.Worksheets.Add("test mig");



//Insert Image
sheet.Pictures.Add(1, 1, @"D:\Udvikling\Subversion\SmartGarment\trunk\SmartGarment.Web.Mvc\Images\SmartgarmentBerendsen.png");


sheet.SetCellValue(5, 1, "OVerskrift er den ikke flot");
sheet.Range[5, 1].Style.Font.FontName = "Arial";
sheet.Range[5, 1].Style.Font.IsBold = true;
sheet.Range[5, 1].Style.Font.Size = 14;
sheet.Range[5, 1].Style.HorizontalAlignment = HorizontalAlignType.Left;


for (int i = 7; i < 70007; i++)
{
sheet.SetCellValue(i, 1, "Rækken her er meget bred så det kan være der skal rykkes lidt på colonne bredden ");
sheet.SetCellValue(i, 3, "#" + (i - 6).ToString());
}

//sheet.ListObjects.Create("Table", sheet.Range[7, 1, sheet.LastRow, 3]);
//sheet.ListObjects[0].BuiltInTableStyle = TableBuiltInStyles.TableStyleLight9;
sheet.AutoFilters.Range = sheet.Range[7, 1, sheet.LastRow, 3];

sheet.AllocatedRange.AutoFitColumns();
sheet.AllocatedRange.AutoFitRows();


//Save and Launch

MemoryStream memoryStream = new MemoryStream();

workbook.SaveToStream(memoryStream);

memoryStream.Flush();
memoryStream.Position = 0;


workbook.SaveToFile("c:\\FreeSpire.xlsx", ExcelVersion.Version2010);

System.Diagnostics.Process.Start("c:\\FreeSpire.xlsx");

[Betsy]

Dear frostholm,

Yes, this is an issue with Free Spire.XLS, as using Spire.XLS Pack Hotfix version:7.8.26 to test your sample code doesn't encounter any issue.

Sincerely,
Besty
E-iceblue support team