While coverting XLS to csv one of the column values

[gauti0710]

I have already give the file PFA screenshot in the column "U" as you can see it is going to other line, there is text after indrapu but its not visible.

That is creating problem in formatting

[gauti0710]

I have to replace the column with a replace but for that i have made a code but it is not taking more than one value

for (int iRow = 12; iRow < 200; iRow++)
{
var StringValue = workbook.Worksheets[0].Cells[iRow, 2].StringValue;
StringValue = StringValue.Replace('\n', ' ');

workbook.Worksheets[0].Cells[iRow, 2].Value = StringValue;
}

can you please give me the code to replace \n with " "

That will be helpful

[gauti0710]

I have already provided the file i have created code as well but that code is not working in spire

i want to replace \n with space for particular column PFB code.



PFA screenshot of the column after indrapur it is not visible

for (int iRow = 12; iRow < 200; iRow++)
{
var StringValue = workbook.Worksheets[0].Cells[iRow, 20].StringValue;
//StringValue = Regex.Replace(StringValue, @"\s+", " ");
StringValue = StringValue.Replace('\n', ' ');

workbook.Worksheets[0].Cells[iRow, 20].Value = StringValue;
}

[gauti0710]

Hello ,

In this case there is problem of carriage return do you know how to replace that in whole column with space?

[gauti0710]

Do you have any update on this?

[lynn.zhang]

Hi

Thanks for your detailed information.
Please refer to the following code to replace the "\n" with space. If the problem persists, please porvide your Excel file for further investigation. Thanks in advance.

Code: Select all

Workbook workbook = new Workbook();
workbook.LoadFromFile("test.xlsx");
var StringValue = workbook.Worksheets[0].Range[1,1].Text;
StringValue = StringValue.Replace('\n', ' ');
workbook.Worksheets[0].Range[1, 1].Value = StringValue;
workbook.SaveToFile("result.xlsx");
System.Diagnostics.Process.Start("result.xlsx");

Sincerely,
Lynn
E-iceblue support team

[gauti0710]

I am getting error string value was null. After using the above code

[gauti0710]

I have already shared the xls in my posts please check.

[gauti0710]

Did you check xls?

[lynn.zhang]

Hi,

Thanks for your feedback.
In previous code I posted, the Range[1,1] represents the first cell ("A1") in Excel sheet, you have to modify the corresponding cells according to your own document. I used the Excel you sent me earlier to replace ‘\n’ of the Address column with space, no error happened. Below is my code and attached my generated file for your better reference.

Code: Select all

  Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"XLStocsv.XLS");
            for (int i = 13; i < 69; i++)
            {
                var StringValue = workbook.Worksheets[0].Range[i,16 ].Text;
                StringValue = StringValue.Replace('\n', ' ');
                workbook.Worksheets[0].Range[i, 16].Value = StringValue;   
            }
            workbook.SaveToFile("result.xlsx");
            System.Diagnostics.Process.Start("result.xlsx");

Sincerely,
Lynn
E-iceblue support team

[gauti0710]

It is working fine thanks a lot.

[lynn.zhang]

Hi,

Thanks for your feedback.
Glad to hear that your issue has been resolved. If you need further assistance, please feel free to write back.
Wish you all the best!

Sincerely,
Lynn
E-iceblue support team