Most accurate and complete text representation of XLS

Aspose.Cells Product Family
1

Hi, i am trying to use Cells to generate the most accurate string representation of a spreadsheet. I need the string to be roughly formatted like the spreadsheet (for multiline cell contents, text boxes), and contain all text from the spreadsheet. Currently im using this naive code to join up cell contents:

var body = new StringBuilder();
foreach (Worksheet worksheet in wb.Worksheets)
{
body.AppendLine(worksheet.Name);

for (int i = worksheet.Cells.MinRow; i <= worksheet.Cells.MaxRow; i++)
{
for (int j = worksheet.Cells.MinColumn; j <= worksheet.Cells.MaxColumn; j++)
{
var cell = worksheet.Cells[i, j];
if (cell != default(Cell) && cell.Value != default(object))
{
body.Append(cell.StringValue);
}
body.Append(CELLSPACER);
}
body.AppendLine();
}
}

Is there a better / inbuilt way to achieve my goal? Thanks!

2

Hi,

Well, I think you may also try to create txt, csv or tab delimited file to make text representation of XLS files,

e.g..,

Workbook wb = new Workbook();
wb.Open(@"f:\test\test461.xls");
wb.Save("f:\\test\\outtst1.txt", ';');


Workbook wb = new Workbook();
wb.Open(@"f:\test\test461.xls");
wb.Save("f:\\test\\outtst2.csv", FileFormatType.CSV);

Workbook wb = new Workbook();
wb.Open(@"f:\test\test461.xls");
wb.Save("f:\\test\\outtst3.txt", FileFormatType.TabDelimited);
And if you don't want to export a txt / csv file, you may enhance your code a bit as follows.
Please try to check a cell existance first before getting the value from it otherwise we will create all the cells in the worksheets. And, you should try to loop through all the cells in a worksheet using Cells.Count i.e.., for(int i = 0 ; i < cells.Count; i++)
Sample code:
var body = new StringBuilder();
foreach (Worksheet worksheet in wb.Worksheets)
{
body.AppendLine(worksheet.Name);
var cells = worksheet.Cells;

for (int i = 0; i < cells.Count; i++)
{
if (cells.CheckExistence(cells[i].Row, cells[i].Column) != -1)
{
var cell = worksheet.Cells[i];
if (cell != default(Cell) && cell.Value != default(object))
{
body.Append(cell.StringValue);
}
body.Append(CELLSPACER);
}
body.AppendLine();
}
}
Thank you.
3
Hi,

There is a minor error in my sample code, when you utilizing for (int i = 0; i < cells.Count; i++) loop, you do not need to actually check the existance of the cells because cells.count will iterated through all the initialized cells only. There are two ways to achieve this.

i)

var body = new StringBuilder();
foreach (Worksheet worksheet in wb.Worksheets)
{
body.AppendLine(worksheet.Name);

for (int i = worksheet.Cells.MinRow; i <= worksheet.Cells.MaxRow; i++)
{
for (int j = worksheet.Cells.MinColumn; j <= worksheet.Cells.MaxColumn; j++)
{

var index = worksheet.Cell.CheckExistence(i,j);
if(index != -1)
{
var cell = worksheet.Cells[index];
if (cell != default(Cell) && cell.Value != default(object))
{
body.Append(cell.StringValue);
}
}
body.Append(CELLSPACER);
}
body.AppendLine();
}
}

ii).

var body = new StringBuilder();
foreach (Worksheet worksheet in wb.Worksheets)
{
body.AppendLine(worksheet.Name);
var cells = worksheet.Cells;
for (int i = 0; i < cells.Count; i++)

{

var cell = worksheet.Cells[i];
if (cell != default(Cell) && cell.Value != default(object))
{
body.Append(cell.StringValue);
}
body.Append(CELLSPACER);
}

body.AppendLine();

}

Thank you.


4

Thanks for the quick reply! I like the brevity of the first suggestions, but unfortunately they do not include all the content i need in the output file. I have modified my custom method to take your suggestions. Cheers.