Adding 10-char-long string with Cells.PutValue and return only a 9-char-long one

Aspose.Cells Product Family
1

Hi all,

I’m new to this forum, I created this topic because I have an interesting issue here. I’m binary serializing c# objects and store them in excel as a string (don’t ask why it’s not important). The process is really simple: I write a value into the cell; save the file; Open it later; Read the cell’s value. During this process, I found that sometimes the value is not stored as it is, it is chunked. It is reproducible, so I created a simple app which tries to store a 10-character-long string, but reads it back as a 9-character-long one. The code is here:

var filePath = @“d:\Temp\TestAsposeStringLength\test.xlsx”;
var template = new Workbook(FileFormatType.Xlsx);
var sheet = template.Worksheets[0];

var tenCharLongString = “\t\u0013\0\0\0\u0001ě˙˙˙”;
if (tenCharLongString.Length != 10)
throw new Exception(“It never throws: This is not a 10-character-long string”);

sheet.Cells[0, 0].PutValue(tenCharLongString);
template.Save(filePath, SaveFormat.Xlsx);

var readTemplate = new Workbook(filePath);
var readValue1 = readTemplate.Worksheets[0].Cells[0, 0].StringValue;
var readValue2 = readTemplate.Worksheets[0].Cells[0, 0].StringValueWithoutFormat;
var readValue3 = (string)readTemplate.Worksheets[0].Cells[0, 0].Value;

if (readValue1.Length == 9 && readValue2.Length == 9 && readValue3.Length == 9)
throw new Exception(“It always throws. The read values are only 9 characters long”);

I’m using the last stable version 18.2.
Does anyone know how it can happen? Any solution would be appreciated.

Thanks in advance

2

@JobCTRL

Thanks for using Aspose APIs.

The only way possible to binary serialize Workbook object is to save it to byte[] and then re-create it from byte[].

Please see the following sample code, its input and output Excel files for a reference.

Download Link:
Input and Output Excel Files.zip (13.8 KB)

C#

public static void TestSerialization()
{
    Workbook book = new Workbook("sample.xlsx");

    byte[] bytesBook = SerializeWorkbook(book);

    Workbook newBook = DeSerializeWorkbook(bytesBook);

    newBook.Save("output.xlsx");
}
public static byte[] SerializeWorkbook(Workbook workbook)
{
    //Create a memory stream 
    MemoryStream ms = new MemoryStream();
        
    //Save the workbook which contains all excel objects 
    //into memory stream
    workbook.Save(ms, SaveFormat.Xlsx);
        
    //Read bytes from memory stream
    ms.Position = 0;
    byte[] bytesWorkbook = new byte[ms.Length];
    ms.Read(bytesWorkbook, 0, bytesWorkbook.Length);

    return bytesWorkbook;
}
public static Workbook DeSerializeWorkbook(byte[] bytesWorkbook)
{
    MemoryStream ms = new MemoryStream();
    ms.Write(bytesWorkbook, 0, bytesWorkbook.Length);

    Workbook workbook = new Workbook(ms);
    ms.Dispose();

    return workbook;
}
3

Thanks for your quick answer. Probably I was not clear enough. My goal is not to serialize the workbook itself, I simply want to store strings in the excel cell but the stored data gets truncated when I save and reopen the file.
I only mentioned that I’m serializing, because otherwise, the string in the example would seem quite weird.
Do you have any idea what truncates my string value in the example? (actually, the first char is cut off)

Thanks for your answer in advance

4

@JobCTRL

Thanks for your posting and using Aspose APIs.

We have found the issue but we are not sure if it relates to your string or Aspose.Cells. Please provide us correct and expected Excel file. Please create expected Excel file manually using Microsoft Excel and save it and provide it to us. We will load your expected Excel file and look into this issue further.

5

Sorry for the late response. It’s a shame but I simply couldn’t manually create an excel file with the expected result, because no matter how I tried to save the result in excel when I read it back and compared the value with the one from the source code, they were not the same.
I made a test to write my string into a text file too and reading it back, and this test was successful, the strings were equal. But when I do the same with excel it changes my string somehow. When I use Aspose Cells PutValue() then it changes to a 9-char-long string (truncates the first (tab) character), when I try to manually add to the cell then it will result in a 10-char-long string, but is not programmatically equal with the one I added from the source code.
Do you have any idea what happens in the background and what the problem is with my strings?
I made a cleaner code to reproduce this issue:

        var tenCharLongString = "\t\u0013\0\0\0\u0001ě˙˙˙";
        if (tenCharLongString.Length != 10)
            throw new Exception("Never throws");

        // write string into excel
        var filePath = @"d:\Temp\TestAsposeStringLength\test.xlsx";
        var template = new Workbook(FileFormatType.Xlsx);
        template.Worksheets[0].Cells[0, 0].PutValue(tenCharLongString);
        template.Save(filePath, SaveFormat.Xlsx);

        // write string into a text file
        var txtFilePath = @"d:\Temp\TestAsposeStringLength\test.txt";
        File.WriteAllText(txtFilePath, tenCharLongString);

        // read back the string from text file is okay
        var readText = File.ReadAllText(txtFilePath);
        if (readText != tenCharLongString)
            throw new Exception("Never throws");    // reading from text file is okay

        // read back the string from excel fails
        var readTemplate = new Workbook(filePath);
        var readValue1 = readTemplate.Worksheets[0].Cells[0, 0].StringValue;    // the result always will be "\u0013\0\0\0\u0001ě˙˙˙"
        var readValue2 = readTemplate.Worksheets[0].Cells[0, 0].StringValueWithoutFormat;
        var readValue3 = (string)readTemplate.Worksheets[0].Cells[0, 0].Value;

        if (readValue1.Length == 9 && readValue2.Length == 9 && readValue3.Length == 9)
            throw new Exception("Always throws");

I’ve uploaded the correct text file (if you need it: test.zip (128 Bytes))
. Any ideas would be welcome. Thanks in advance.

6

@JobCTRL

I have tested this issue with the following code and found that the initial ‘\t’ character is lost. Please see the screenshot for comparison. The input string has 14 characters but Aspose.Cells inserts 13 characters and the first tab character is lost. Tab might have special meaning in the Microsoft Excel.

C#

byte[] b = File.ReadAllBytes("test.txt");

string s = "";

for (int i=0; i<b.Length; i++)
{
    s = s + (char)b[i];
}

Workbook wb = new Workbook();

Worksheet ws = wb.Worksheets[0];

Cell cell = ws.Cells["B5"];
cell.PutValue(s);

wb.Save("output.xlsx");

wb = new Workbook("output.xlsx");

ws = wb.Worksheets[0];
cell = ws.Cells["B5"];

string s1 = cell.StringValue;

Screenshot:

7

Thanks for your answer, I ended with the same result. Excel somehow corrupts the data I put into the cell, Aspose library does what it has to.
I’ll continue the investigation alone, thanks for your help again.

Ps: My current idea is that I am going to store my serialized information with base64 encoding, which surely won’t be touched even by excel.

Have a nice day to all.

8

@JobCTRL

Thanks for using Aspose APIs.

We are not sure that Base64 will yield good results always. However, Hex String will work always fine.

Hex string contains only 16 characters and all of them are printable characters. These characters are as follows

0 1 2 3 4 5 6 7 8 9 A B C D E F

You can convert your string or byte[] to hex string using the following code.

C#

public static string ToHexString(byte[] b)
{
    StringBuilder sb = new StringBuilder();

    for (int i = 0; i < b.Length; i++)
    {
        sb.Append(b[i].ToString("X2"));
    }

    return sb.ToString(); 
}

This is the hex string of your string/data which you are trying to input in Excel file.

091300000001C49BCB99CB99CB99
9

Thanks Shakeel, good idea, I’ll try both.