Action RecordListToExcel force column type as text in excel

Action RecordListToExcel force column type as text in excel

  

Hi all,

is there any way to force a column to be set as text type when using the RecordListToExcel action?


Let's say i have a structure and the attribute is defined as text in the structure, but when i export to excel using the action RecordListToExcel the column type is general. 

The problem is that i need to use negative values, and in some cases the value start with a minus and the excel return an error. 

#VALUE!


Thanks

Best regards



Hello Severiano,

The RecordListToExcel does not give you the possibility to change the excel column type. Everything is "automatic" (at least when I see in google docs).

But probably, with one of the components in the forge that enable you to create more complex excel files, you will be able to do this.

If this is important, you should try them.

Cheers,
Eduardo Jauch

Hi Severino,


Have you tried to add an ' (apostrophe ) to the beggining of the cell value? Like: "'" + Value.


"The apostrophe ' is a special character for Excel when it appears as the first character in a cell. It tells Excel to treat the rest of the string as text."


I hope that do the trick.


Best regards,

João Nobre



João Nobre wrote:

"The apostrophe ' is a special character for Excel when it appears as the first character in a cell. It tells Excel to treat the rest of the string as text."


João Nobre

Hello João.

I tested in a Google Sheets and it won't worked.
It works only when you put the ' direct in the cell, from the google sheets itself (like the 123).

Maybe it works in Excel (I don't have one to test).
But if it works it will require to process all the rows/columns that can cause problems...

Maybe, using a more advanced plugin, would be possible to set the column type.

Cheers,
Eduardo Jauch

Hi guys,


yes indeed my first option was to use the apostrophe, but as Eduardo said, this dont change the column/cell content type, it only gives the possibility to use the minus char at the start. 

I needed a better option cause in this case, the excel is to be used by final users to import data, and dont want to force them to use the apostrophe.

I finaly found a way to do it, i had to create a .Net extension using spreedsheetgear component, i created a method to format a list of columns using the NumberFormat property of the class.

In this case i call this function by passing the parameters: ssColumnLetter="C", ssFormatName="@"

that in the excel means type text.

bellow is the code i used to create the extension in integration studio.


public void MssFormatColumn(RLFormatSettingRecordList ssFormatSettings, byte[] ssBinary, out byte[] ssBinaryOut, out string ssErrorMessage) {
ssBinaryOut = new byte[] {};

            try
            {
                ssErrorMessage = "";
                System.Globalization.CultureInfo culture = new System.Globalization.CultureInfo("pt-PT");
                SpreadsheetGear.IWorkbookSet workbookSet = SpreadsheetGear.Factory.GetWorkbookSet(culture);

                SpreadsheetGear.IWorkbook workbook = workbookSet.Workbooks.OpenFromMemory(ssBinary);
                SpreadsheetGear.IWorksheet worksheet = workbook.ActiveWorksheet;
                var cells = worksheet.Cells;

                ssFormatSettings.Data.ToList().ForEach(set => {

                    var col = string.Format("{0}:{0}", set.ssSTFormatSetting.ssColumnLetter);
                    cells[col].NumberFormat = set.ssSTFormatSetting.ssFormatName;

                });

                ssBinaryOut = workbook.SaveToMemory(SpreadsheetGear.FileFormat.OpenXMLWorkbook);
            }
            catch (Exception ex)
            {
                ssErrorMessage =  ex.Message;
            }

            } // MssFormatColumn


Thanks
Best regards