=Hyperlink function not rendering as Hyperlink text it is rendered as normal text when import data table

Aspose.Cells Product Family
1

Hi

We are generating report from sql server to excel. When importing data table to excel the =Hyperlink formula not rendered as a link it rendered as a normal text. Can you please give me suggestion how to import this.

Example Data (DataTable)
SNo HypText
1 =Hyperlink("http://test.com/", "linkTest")
2 =Hyperlink("http://Abc.com/", "linkABC")

Thanks
Murugan M

2

@mk_mur,

When you import data from datatable or datasource, the formulas are inserted as string/text. You have to use/set specified options to accomplish the task. For example, if you are importing data using Aspose.Cells data importing options (ImportData method), then you may easily do that using ImportTableOptions. See the sample code for your complete reference:
e.g.
Sample code:

DataTable dt = new DataTable("MyTable");
            dt.Columns.Add("Col_ID", typeof(Int32));
            dt.Columns.Add("Col_no", typeof(Int32));
            dt.Columns.Add("Col_Formula1", typeof(String));
            dt.Columns.Add("Col_Formula2", typeof(String));


            DataRow row = dt.NewRow();
            row[0] = 1242;
            row[1] = 3502;
            row[2] = "=SUM(A2,B2)";
            row[3] = "=HYPERLINK(\"https://www.aspose.com\",\"Aspose Website\")";
            dt.Rows.Add(row);
            

            //Define 2nd data row
            row = dt.NewRow();
            row[0] = 1013;
            row[1] = 3503;
            row[2] = "=SUM(A3,B3)";
            row[3] = "=HYPERLINK(\"https://www.aspose.com\",\"Aspose Website\")";
            dt.Rows.Add(row);

            //Define 3rd data row
            row = dt.NewRow();
            row[0] = 1106;
            row[1] = 3504;
            row[2] = "=SUM(A4,B4)";
            row[3] = "=HYPERLINK(\"https://www.aspose.com\",\"Aspose Website\")";
            dt.Rows.Add(row);

            //Define 4th data row
            row = dt.NewRow();
            row[0] = 1015;
            row[1] = 3505;
            row[2]= "=SUM(A5,B5)";
            row[3] = "=HYPERLINK(\"https://www.aspose.com\",\"Aspose Website\")";
            dt.Rows.Add(row);

            //Create workbook object
            Workbook wb = new Workbook();

            //Access first worksheet
            Worksheet ws = wb.Worksheets[0];

            //Specify import table options
            ImportTableOptions opts = new ImportTableOptions();
            //opts.ColumnIndexes = new int[] { 0, 1 }; //first two  column would be imported only. 

            //Specify which field is formula field, here the last two fields are formula fields
            opts.IsFormulas = new bool[] { false, false, true, true };
            opts.NumberFormats = new string[] { "#.00", "#.0", null, null };//only set first two columns number formattings

            opts.InsertRows = false;

            //Import custom objects
            ws.Cells.ImportData(dt, 0, 0, opts);

            //Calculate formula
            wb.CalculateFormula();

            //Hyperlink formulas (inserted into D column)would be treated as formulas but since the links are not popped in blue color/style, so you may apply relevant formatting to those cells.
            Style style = wb.CreateStyle();
            style.Font.Color = Color.Blue;
            style.Font.Underline = FontUnderlineType.Single;
            int maxRow = ws.Cells.MaxDataRow;
            for (int i = 1; i <= maxRow; i++)
            {
                ws.Cells[i, 3].SetStyle(style);

            }

            
            //Autofit columns
            ws.AutoFitColumns();

            //Save the output Excel file
            wb.Save("e:\\test2\\out1.xlsx");

Hope, this helps a bit.

3

Hi

Thank you for your reply my version not supporting ImportTableOptions().

//Specify import table options
        ImportTableOptions opts = new ImportTableOptions();

Thanks
mk_mur

4

@mk_mur,

Please upgrade to newer version to use the APIs.

5

Thank you very much.

Thanks
mk_mur

6

@mk_mur,

You are welcome.