Hi Team,
We are facing the below problem in Ver. 17.8 aspose.cells
“We found a problem with some content in ‘fileName.xlsx’. Do you want us to try t recover as we can? If you trust the source of this woorkbook, click yes.”
We are creating worksheet from DataTable and applying below functionalities
Previously we were using 8.3 version and we are planning to upgrade the version to 17.8.
@GeP,
Thanks for providing us some details.
Please try our latest version/fix: Aspose.Cells for .NET v17.8.6:
If you still find the issue with v17.8.6, kindly create a simple console demo application (runnable) to reproduce the issue on our end, zip the project with the resource files (you may upload to some file sharing service, e.g dropbox or Google drive, etc. and share the Download link here), we will check it soon.
Hi Amjad,
Problem still persist.
I have created sample console app to replicate the issue, kindly find the dropbox link below.
https://www.dropbox.com/s/5ytohuoitzgups9/SampleCellsApp.zip?dl=0
@GeP,
Thanks for the sample project.
I have tested your scenario/case using your sample project with v17.8.6 and got the issue with the output file which prompts MS Excel to show the error message(s) as you mentioned. I also tried with v8.3.0 and v8.3.2 and got the same issue. Could you give us details which Aspose.Cells
previous version worked fine using your sample project, so we could evaluate it thoroughly and log a ticket for it into our database.
Hi Amjad,
Thanks for the response.
We are using Aspose.Cells 8.3.0 Version.
I have change the sample code slightly, please find the dropbox link below.
I have tested the sample with Version 8.3.0, it is working fine and I am able to replicate the problem with Version 17.8.6 and 17.8.
Thanks,
@GeP,
Thanks for the sample project.
After an initial test, I observed the issue as you mentioned by running your project with latest version/fix: Aspose.Cells for .NET v17.8.x. I found MS Excel prompts an error message “We found a problem with some content in ‘fileName.xlsx’. Do you want us to try to recover as we can…” when opening the output file into it after filling a worksheet from DataTable. It works fine though and the output file is fine tuned if I use v8.3.0.
I have logged a ticket with an id “CELLSNET-45656” for your issue. We will look into it soon.
Once we have an update on it, we will let you know here.
Please save the file to new stream. Please see the following code:
C#
MemoryStream ms0 = new MemoryStream();
workbook.Save(ms0, SaveFormat.Xlsx);
ms0.Seek(0, SeekOrigin.Begin);
//// Obtaining the reference of the worksheet
//int worksheetId = AddWorksheet("Sales", ms);
//Workbook workbook1 = new Workbook(ms);
//Worksheet worksheet1 = workbook1.Worksheets[worksheetId];
//worksheet1.Name = "Sales";
//DataTable dataTable1 = GetDataTable("Sales");
//worksheet1.Cells.ImportDataTable(dataTable1, false, 1, 0, true);
//workbook1.Save(ms, SaveFormat.Xlsx);
//ms.Seek(0, SeekOrigin.Begin);
byte[] bData = new byte[ms0.Length];
ms0.Read(bData, 0, bData.Length);
File.WriteAllBytes("D:\\filetemp\\ABC.xlsx", bData);Hi Shakeel,
Thanks for the response.
I have tested the code, its working fine.
But we can’t change the approach as it has major impact on our product and our excel sheet contains multiple sheets. From our DataSet we are picking up DataTable one by one to generate the worksheet and its being stored as a Stream, due to which we won’t be able to upgrade to new version.
It will be helpful if you provide the solution which will work for 8.3 and newer versions as well.
Thanks,
As a workaround, save your workbook in new memory stream object and then write that memory stream object to your original stream object
C#
MemoryStream o = new MemoryStream();
workbook.Save(o, SaveFormat.Xlsx);
ms.SetLength(0);
ms.Write(o.GetBuffer(), 0, (int)o.Length);
ms.Seek(0, SeekOrigin.Begin);
Here is the full code for your reference.
C#
using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace SampleCellsApp
{
class Program
{
static void Main(string[] args)
{
using (Stream ms = new MemoryStream())
{
int worksheetId1 = AddWorksheet("Product", ms);
DataTable dataTable = GetDataTable("Products");
Workbook workbook = new Workbook(ms);
workbook.FileFormat = FileFormatType.Xlsx;
// Obtaining the reference of the worksheet
Worksheet worksheet = workbook.Worksheets[worksheetId1];
worksheet.Name = "Product";
worksheet.Cells.ImportDataTable(dataTable, false, 1, 0, true);
MemoryStream o = new MemoryStream();
workbook.Save(o, SaveFormat.Xlsx);
//workbook.Save(ms, opts);
ms.SetLength(0);
ms.Write(o.GetBuffer(), 0, (int)o.Length);
ms.Seek(0, SeekOrigin.Begin);
//// Obtaining the reference of the worksheet
//int worksheetId = AddWorksheet("Sales", ms);
//Workbook workbook1 = new Workbook(ms);
//Worksheet worksheet1 = workbook1.Worksheets[worksheetId];
//worksheet1.Name = "Sales";
//DataTable dataTable1 = GetDataTable("Sales");
//worksheet1.Cells.ImportDataTable(dataTable1, false, 1, 0, true);
//workbook1.Save(ms, SaveFormat.Xlsx);
//ms.Seek(0, SeekOrigin.Begin);
byte[] bData = new byte[ms.Length];
ms.Read(bData, 0, bData.Length);
File.WriteAllBytes("F:\\Download\\ABC1.xlsx", bData);
}
}
public static int AddWorksheet(string name, Stream _fileStream)
{
if (string.IsNullOrEmpty(name) || string.IsNullOrWhiteSpace(name))
throw new ArgumentNullException("Name");
//Instantiating a Workbook object
Workbook workbook = new Workbook(_fileStream);
//removed the Extra Sheet 1 from WorkBook
workbook.Worksheets.RemoveAt("Sheet1");
SaveFormat saveFormat = SaveFormat.Xlsx; //Default Format
//TODO : Format Mapper should be separate class and Each component we use should have mapping with standard GEP File Format
workbook.FileFormat = FileFormatType.Xlsx;
//saveFormat = SaveFormat.Xlsx;
int wkId = workbook.Worksheets.Add();
workbook.Worksheets[wkId].Name = name;
workbook.Save(_fileStream, saveFormat);
return wkId;
}
public static DataTable GetDataTable(string tableName)
{
// Instantiating a "Products" DataTable object
DataTable dataTable = new DataTable(tableName);
// Adding columns to the DataTable object
dataTable.Columns.Add("Action", typeof(string));
dataTable.Columns.Add("Product ID", typeof(Int32));
dataTable.Columns.Add("Product Name", typeof(string));
dataTable.Columns.Add("Units In Stock", typeof(Int32));
dataTable.Columns.Add("Change In Price", typeof(Int32));
dataTable.Columns.Add("FORMULA", typeof(string));
dataTable.Columns.Add("Change", typeof(string));
// Creating an empty row in the DataTable object
DataRow dr = dataTable.NewRow();
// Adding data to the row
dr[0] = "Modified From";
dr[1] = 1;
dr[2] = "Aniseed Syrup";
dr[3] = 15;
dr[4] = DBNull.Value;
dr[5] = "";
dr[6] = "";
// Adding filled row to the DataTable object
dataTable.Rows.Add(dr);
// Creating another empty row in the DataTable object
dr = dataTable.NewRow();
// Adding data to the row
dr[0] = "Modified To";
dr[1] = 1;
dr[2] = "Boston Crab Meat";
dr[3] = 15;
dr[4] = 100;
dr[5] = "";
dr[6] = "=COUNTIF(F7:F12,\"*'\"&$B$6&\"'*\")";
// Adding filled row to the DataTable object
dataTable.Rows.Add(dr);
// Creating another empty row in the DataTable object
dr = dataTable.NewRow();
// Adding data to the row
dr[0] = "Added";
dr[1] = 2;
dr[2] = "Boston Crab";
dr[3] = 15;
dr[4] = 100;
dr[5] = "";
dr[6] = "=COUNTIF(F7:F12,\"*'\"&$C$6&\"'*\")";
// Adding filled row to the DataTable object
dataTable.Rows.Add(dr);
// Creating another empty row in the DataTable object
dr = dataTable.NewRow();
// Adding data to the row
dr[0] = "Deleted";
dr[1] = 3;
dr[2] = "Boston";
dr[3] = 15;
dr[4] = 100;
dr[5] = "";
dr[6] = "=COUNTIF(F7:F12,\"*'\"&$D$6&\"'*\")";
// Adding filled row to the DataTable object
dataTable.Rows.Add(dr);
// Creating another empty row in the DataTable object
dr = dataTable.NewRow();
// Adding data to the row
dr[0] = "Modified From";
dr[1] = 4;
dr[2] = "Boston";
dr[3] = 15;
dr[4] = 100;
dr[5] = "";
dr[6] = "=COUNTIF(F7:F12,\"*'\"&$E$6&\"'*\")";
// Adding filled row to the DataTable object
dataTable.Rows.Add(dr);
// Creating another empty row in the DataTable object
dr = dataTable.NewRow();
// Adding data to the row
dr[0] = "Modified To";
dr[1] = 4;
dr[2] = "Boston S";
dr[3] = 20;
dr[4] = 50;
dr[5] = "";
dr[6] = "";
// Adding filled row to the DataTable object
dataTable.Rows.Add(dr);
return dataTable;
}
}
}Hi Shakeel,
Thanks for the response.
As I said earlier, we can’t change the approach as you have suggested, it has a major impact on our product.
Is it possible for you guys to fix the problem and provide the new dll version for it.
Probably, you have not checked or tested the solution suggested by me. It does not affect your original stream or your original code. It just uses a temporary memory stream object and then write it to your original stream.
We do not fix issues if they are found in older versions. We only fix the issues if they are found in most recent version. Even if fix issues in most recent version or older version, it will be released as a new version like 17.9.1 etc. and you will have to purchase a new license unless your older license allows you to use the most recent version.