Aspose.Cells - How to do TextToColumn process

Aspose.Cells Product Family
1

Dear All,

I need a solution for the Issue:

Issue:

I have one Excel Sheet with comma sepertated value in a first column. I need to split this comma value and put this value to subsequent column. How to do this in Aspose.Cell.

Kindly give me a Suggession.

Regards,

Prabu

2

Hi Prabu,

Well, the feature (Text2Columns) is not supported currently but I think you may try some custom programming for your requirements. Take a look at the following sample code and run it with the attached template excel file for your reference:

Sample code:

Workbook workbook = new Workbook();
workbook.Open("f:\\test\\text2cols.xls");
Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;
for (int col = 0; col <= cells.MaxDataColumn; col++)
{
for (int row = 0; row <= cells.MaxDataRow; row++)
{
string val = sheet.Cells[row, col].StringValue;
int afterComma = 0;
int firstVal = 0;
if (val != "" && val.Contains(","))
{
afterComma = (val.Length - 1) - val.IndexOf(',');
firstVal = val.Length - 1 - afterComma;

cells[row, col].PutValue(val.Substring(0, firstVal));
cells[row, col +1].PutValue(val.Substring(val.IndexOf(',') + 1, afterComma));
}

}
}
workbook.Save("f:\\test\\text2cols_out1.xls", FileFormatType.Default);

I have also attached the output file for your reference too.

Thank you.

3

Dear Amjad Sahi,

Thank you for your timely reply.

Regards,

Prabu