CSV output removes the last delimiter if last column does not have data

Aspose.Cells Product Family
1

I am using Aspose.Cells v2.0.50727 [7.3.4.0]

Filling excel template using smart markers and then saving it as CSV:

TxtSaveOptions opts = new TxtSaveOptions();
opts.Separator = ‘;’;
workbookDesigner.Workbook.Save(outputFile, opts);

Now if the last column does not have value the last delimiter is absent:
Notice data row 1st and last:

Head1;Head2;Head3;Head4
01;;ABC
01;1233;ABF;D
01;4566;OJKY;B
01;8799;TYFX

I am expecting following: because if I open the excel and save as CSV below is what I get:
Head1;Head2;Head3;Head4
01;;ABC;
01;1233;ABF;D
01;4566;OJKY;B
01;8799;TYFX;

1. How to retain the last separator if the last column does not have data?

2. Also I see some extra lines at the end in the CSV output, how to get rid of those?
;;;;
;;;;
;;;;

3. Seperate question but is there a way to always end the line with the separator ??

Help is much appreciated.

Thanks- Rohit.

2

Hi,


Thanks for your posting and using Aspose.Cells.

We were unable to observe this issue with the latest version: Aspose.Cells for .NET v16.11.6. Please download and try the latest version and see if it makes any difference in sorting out this issue.

If your issue still occurs with the latest version, then please provide us some simple console application project which we could run at our end and replicate the issue. We will look into it and update you asap.

3

Hello Shakeel, Many thanks for responding.

Is there a posibility that you can support me on Aspose.Cells v2.0.50727 [7.3.4.0] ??

Upgrading to the latest version will not be possible for me in a short span of time.

Help is much appreciated.

Thanks - Rohit
4

Hi,


Thanks for your posting and using Aspose.Cells.

Please provide us your simple console application with your older version. We will look into it. If there is any error in your code or if there is some workaround for you, we will update you asap.

5

Thank You! Attached is a sample.

Let me know if any questions.

Thanks - Rohit
6

Hi,


Thanks for your posting and using Aspose.Cells.

Please check the following workaround and see if it works good for you. The idea is to find the last maximum cell using the max display range and then check if that cell is empty. If that cell is empty then add empty string inside it so that cell gets instantiated inside the worksheet. I have attached the output csv generated with this workaround and also displayed it in this post for your reference. Besides. I have also provided the full sample code and the smart marker excel file used inside it.

Workaround:
#region CELLSTEAM
//Find your last maximum cell and check if it is null, then add empty string inside it
Worksheet sh = workbookDesigner.Workbook.Worksheets[0];
Range r = sh.Cells.MaxDisplayRange;
Cell c = sh.Cells[r.RowCount-1, r.ColumnCount-1];

if (c.StringValue == “”)
c.PutValue("");
#endregion


Here is the full sample code which you can execute at your end.

Full Code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Aspose.Cells;
using System.Data;

namespace TestCSV
{
class Program
{
static void Main(string[] args)
{
String templatePath = “…\…\Test.xlsx”;
WorkbookDesigner workbookDesigner = new WorkbookDesigner();
//Open a designer spreadsheet containing smart markers

        workbookDesigner.Workbook = new Workbook(templatePath);

        workbookDesigner.SetDataSource(GetTestData());
        workbookDesigner.Process();

        </font><font color="#0000ff">#region CELLSTEAM</font><font color="#800080">

//Find your last maximum cell and check if it is null, then add empty string inside it
Worksheet sh = workbookDesigner.Workbook.Worksheets[0];
Range r = sh.Cells.MaxDisplayRange;
Cell c = sh.Cells[r.RowCount-1, r.ColumnCount-1];

        if (c.StringValue == "")
            c.PutValue("");

#endregion

        TxtSaveOptions opts = new TxtSaveOptions();
        opts.Separator = ',';
        workbookDesigner.Workbook.Save("cellsteam.csv", opts);
    }

    private static DataSet GetTestData()
    {
        DataSet ds = new DataSet();
        DataTable ordersTable = ds.Tables.Add("TestData");

        ordersTable.Columns.Add("Head1", typeof(string));
        ordersTable.Columns.Add("Head2", typeof(string));
        ordersTable.Columns.Add("Head3", typeof(string));
        ordersTable.Columns.Add("Head4", typeof(string));

        DataRow dr1 = ds.Tables["TestData"].NewRow();
        dr1["Head1"] = "1";
        //dr1["Head2"] = "";
        dr1["Head3"] = "ABC";
        //dr1["Head4"] = "";
        ds.Tables["TestData"].Rows.Add(dr1);

        DataRow dr2 = ds.Tables["TestData"].NewRow();
        dr2["Head1"] = "1";
        dr2["Head2"] = "1233";
        dr2["Head3"] = "ABF";
        dr2["Head4"] = "D";       
        ds.Tables["TestData"].Rows.Add(dr2);  
        
        DataRow dr3 = ds.Tables["TestData"].NewRow();
        dr3["Head1"] = "1";
        dr3["Head2"] = "4566";
        dr3["Head3"] = "OJKY";
        dr3["Head4"] = "B";
        ds.Tables["TestData"].Rows.Add(dr3);

        DataRow dr4 = ds.Tables["TestData"].NewRow();
        dr4["Head1"] = "1";
        dr4["Head2"] = "8799";
        dr4["Head3"] = "TYFX";
        //dr4["Head4"] = "";
        ds.Tables["TestData"].Rows.Add(dr4);

        return(ds);
    }
}

}


CSV Output
Head1,Head2,Head3,Head4
1,ABC
1,1233,ABF,D
1,4566,OJKY,B
1,8799,TYFX,