Saving file with delimiter adds extra rows to end of file

Aspose.Cells Product Family
1

Hi,

Currently I have a method that opens an excel file (.xls/.xlsx), applies different styles to different rows/columns and then saves the file with each row of data being separated by a delimiter ("~").

The excel file will have the first row consist of headings, then the second row and beyond be data rows. Such as (first row): "Name Data1 Data2 Data3", then (second row): "Row1 2.345 3.145 6.422"

When the file saves, the first row and all subsequent data rows are correctly delimited with a "~". So the first two rows look like this: (first row): "Name~Data1~Data2~Data3" and (second row): "Row1~2.345~3.145~6.422". However, there are always a total of around 10,000+ rows in the file that are just "~ ~ ~"...which I dont know how to get rid of.

My code just looks like this:

LoadFormat lf = LoadFormat.Excel97To2003;

//Creating a Workbook object

LoadOptions loadOptions = new LoadOptions(lf);

Workbook workbook = new Workbook(sFileName, loadOptions);

//Obtaining the reference of the newly added worksheet by passing its sheet index

Worksheet worksheet = workbook.Worksheets[0];

sSheetName = worksheet.Name;

do {

//Here we have a code block that sets style/display formats for various cells

} while (worksheet.Cells[iRow, iCol].Value != null);

TxtSaveOptions options = new TxtSaveOptions();

options.Separator = Convert.ToChar("~");

workbook.Save(sFileName, options);

Thanks.

2

Hi,



Well, your so called blank rows after 2nd row may not be null, or you are applying formatting/styles to those rows, so you are getting extra rows with “~” delimiter. Could you try to use the line:
e.g

worksheet.Cells.DeleteBlankRows();
before rendering the text file if it works for you.

If you still find any issue, kindly provide us your Excel file and complete runnable code or preferably a console application. zip it and post it here to show the issue. I also suspect your code/logic to apply formatting to those extra rows might be doing this.

Thank you.
3

Hi,

I did add the methods to delete any extra rows (and columns) but that didnt work either. I can't send my whole solution - its against company policy but I am attaching the part of the code that is altering the source file and the file I'm testing with, along with the file that is being generated.

Its supposed to be a fairly simple and straight forward routine, nothing complex - take the source file, apply date and number formatting to cells, then convert to a CSV file with "~" as a delimiter, thats it.

Thanks!

4

Hi,

Thanks for the sample code and files.

I have tested your case with our latest fix/version: Aspose.Cells for .NET v7.5.0.1, it works fine. Please try our latest fix.

I have used the following sample code, I have to update your code a bit accordingly to test/run your case.

Sample code:

Int32 iRow;

Int32 iCol;

Int32 iMaxRow;

Int32 iMaxCol;

Boolean bResult;

bResult = false;

Workbook workbook = new Workbook(@“e:\test2\FEGLOBH_TEST_SOURCE.xlsx”);

//Obtaining the reference of the newly added worksheet by passing its sheet index

Worksheet worksheet = workbook.Worksheets[0];

string sSheetName = worksheet.Name;

if ((worksheet.Cells[0, 0].Value + “”).ToString().ToUpper() == “SERIES”)

{

//Setting the display format of the dates

iRow = 0;

iCol = 3;

do

{

Aspose.Cells.Style style = worksheet.Cells[iRow, iCol].GetStyle();

style.Number = 15; //: d-mmm-yyyy

worksheet.Cells[iRow, iCol].SetStyle(style);

iCol++;

} while (worksheet.Cells[iRow, iCol].Value != null);

iMaxCol = iCol - 1;

//find the max number of rows

iRow = 0;

iCol = 0;

do

{

iRow++;

} while (worksheet.Cells[iRow, iCol].Value != null);

iMaxRow = iRow - 1;

//setting the display format of the value to decimal without comma separators

Range rng;

rng = worksheet.Cells.CreateRange(1, 3, iMaxRow, iMaxCol);

Aspose.Cells.Style styl;

styl = worksheet.Cells[2, 3].GetStyle();

styl.Custom = “0.00000000”; //set format to show 8 decimal places

rng.SetStyle(styl); //decimal

worksheet.Cells.DeleteBlankRows();

worksheet.Cells.DeleteBlankColumns();

//Save the workbook with the specified delimiter

TxtSaveOptions options = new TxtSaveOptions();

options.Separator = Convert.ToChar(“~”);

workbook.Save(@“e:\test2\outFEGLOBH_TEST_SOURCE.txt”, options);

Thank you.

5

Hi,


Also, since you are saving to text file, so see my last few lines of code, you should use .txt extension for your output file.

//Save the workbook with the specified delimiter
TxtSaveOptions options = new TxtSaveOptions();
options.Separator = Convert.ToChar("~");

workbook.Save(@“e:\test2\outFEGLOBH_TEST_SOURCE.txt”, options);