Open-XML-SDK

Open-XML-SDK copied to clipboard

Hi Folks,

I face issue cell value rendering HTML tag.

Cell value contain html tags but its not rendering while export to excel.

Please help me any of my friends.

@MohammedMubeen This may be a "how to" question for stackoverflow. However, can you give an example of the code you're using to create the cell?

Hi @tomjebo ,

Thanks for the response.

FYR:

private static void AppendHeaderTextCell(string cellReference, string cellStringValue, OpenXmlWriter writer) { // Add a new "text" Cell to the first row in our Excel worksheet // We set these cells to use "Style # 3", so they have a gray background color & white text. writer.WriteElement(new Cell { CellValue = new CellValue(cellStringValue), CellReference = cellReference, DataType = CellValues.String, StyleIndex = 3 }); } private static void AppendTextCell(string cellReference, string cellStringValue, OpenXmlWriter writer) { // Add a new Excel Cell to our Row if (!string.IsNullOrEmpty(cellStringValue)) { writer.WriteElement(new Cell { CellValue = new CellValue(cellStringValue), CellReference = cellReference, DataType = CellValues.String }); } else { writer.WriteElement(new Cell { }); } } private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart, DocumentFormat.OpenXml.Office2010.Excel.DefinedNames definedNamesCol) { OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.ASCII); writer.WriteStartElement(new Worksheet());

        //  To demonstrate how to set column-widths in Excel, here's how to set the width of all columns to our default of "25":
        UInt32 inx = 1;
        writer.WriteStartElement(new Columns());
        foreach (DataColumn dc in dt.Columns)
        {
            writer.WriteElement(new Column { Min = inx, Max = inx, CustomWidth = true, Width = DEFAULT_COLUMN_WIDTH });
            inx++;
        }
        writer.WriteEndElement();

        writer.WriteStartElement(new SheetData());

        string cellValue = "";
        string cellReference = "";
        //  Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
        //
        //  We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
        //  cells of data, we'll know if to write Text values or Numeric cell values.
        int numberOfColumns = dt.Columns.Count;
        bool[] IsIntegerColumn = new bool[numberOfColumns];
        bool[] IsFloatColumn = new bool[numberOfColumns];
        bool[] IsDateColumn = new bool[numberOfColumns];

        string[] excelColumnNames = new string[numberOfColumns];
        for (int n = 0; n < numberOfColumns; n++)
            excelColumnNames[n] = GetExcelColumnName(n);

        //
        //  Create the Header row in our Excel Worksheet
        //  We'll set the row-height to 20px, and (using the "AppendHeaderTextCell" function) apply some formatting to the cells.
        //
        uint rowIndex = 1;

        writer.WriteStartElement(new Row { RowIndex = rowIndex, Height = 18, CustomHeight = true });
        for (int colInx = 0; colInx < numberOfColumns; colInx++)
        {
            DataColumn col = dt.Columns[colInx];
            AppendHeaderTextCell(excelColumnNames[colInx] + "1", col.ColumnName, writer);
            IsIntegerColumn[colInx] = (col.DataType.FullName.StartsWith("System.Int"));
            IsFloatColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single");
            IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime");
        }
        writer.WriteEndElement();   //  End of header "Row"
        //
        //  Now, step through each row of data in our DataTable...
        //
        double cellFloatValue = 0;
        CultureInfo ci = new CultureInfo("en-US");
        foreach (DataRow dr in dt.Rows)
        {
            // ...create a new row, and append a set of this row's data to it.
            ++rowIndex;

            writer.WriteStartElement(new Row { RowIndex = rowIndex, Height = 18, CustomHeight = true });

            for (int colInx = 0; colInx < numberOfColumns; colInx++)
            {
                cellValue = dr.ItemArray[colInx].ToString();
                cellValue = ReplaceHexadecimalSymbols(cellValue);
                cellReference = excelColumnNames[colInx] + rowIndex.ToString();

                // Create cell with data
                if (IsIntegerColumn[colInx] || IsFloatColumn[colInx])
                {
                    //  For numeric cells without any decimal places.
                    //  If this numeric value is NULL, then don't write anything to the Excel file.
                    cellFloatValue = 0;
                    bool bIncludeDecimalPlaces = IsFloatColumn[colInx];
                    if (double.TryParse(cellValue, out cellFloatValue))
                    {
                        cellValue = cellFloatValue.ToString(ci);
                        AppendNumericCell(cellReference, cellValue, bIncludeDecimalPlaces, writer);
                    }
                }
                else if (IsDateColumn[colInx])
                {
                    //  For date values, we save the value to Excel as a number, but need to set the cell's style to format
                    //  it as either a date or a date-time.
                    DateTime dateValue;
                    if (DateTime.TryParse(cellValue, out dateValue))
                    {
                        AppendDateCell(cellReference, dateValue, writer);
                    }
                    else
                    {
                        //  This should only happen if we have a DataColumn of type "DateTime", but this particular value is null/blank.
                        AppendTextCell(cellReference, cellValue, writer);
                    }
                }
                else
                {
                    //  For text cells, just write the input data straight out to the Excel file.
                    AppendTextCell(cellReference, cellValue, writer);
                }
            }
            writer.WriteEndElement(); //  End of Row
        }
        writer.WriteEndElement(); //  End of SheetData
        writer.WriteEndElement(); //  End of worksheet

        writer.Close();
    }