We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Saving file with delimiter adds extra rows to end of file

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.

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.

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!

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);


<o:p></o:p>

Thank you.

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);