error while exporting to excel data : ' ', hexadecimal value 0x07, is an invalid character using c#

Question

I have got strange problem when I try to export data in kendo ui grid to excel .....

Error :

  An exception of type 'System.ArgumentException' occurred in System.Xml.dll but was not handled in user code

Additional information: ' ', hexadecimal value 0x07, is an invalid character.

and the below method I was used for export to excel

  private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart)
  {
        OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart);
        writer.WriteStartElement(new Worksheet());
        writer.WriteStartElement(new SheetData());

        string cellValue = "";

        int numberOfColumns = dt.Columns.Count;
        bool[] IsNumericColumn = new bool[numberOfColumns];

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

        uint rowIndex = 1;

        writer.WriteStartElement(new Row { RowIndex = rowIndex });
        for (int colInx = 0; colInx < numberOfColumns; colInx++)
        {
            DataColumn col = dt.Columns[colInx];
            AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, ref writer);
            IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single");
        }
        writer.WriteEndElement();   

        double cellNumericValue = 0;
        foreach (DataRow dr in dt.Rows)
        {

            ++rowIndex;

            writer.WriteStartElement(new Row { RowIndex = rowIndex });

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

                if (IsNumericColumn[colInx])
                {

                    cellNumericValue = 0;
                    if (double.TryParse(cellValue, out cellNumericValue))
                    {
                        cellValue = cellNumericValue.ToString();
                        AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
                    }
                }
                else
                {

                    AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
                }
            }
            writer.WriteEndElement(); 
        }
        writer.WriteEndElement(); 
        writer.WriteEndElement(); 

        writer.Close();
   }

   private static void AppendTextCell(string cellReference, string cellStringValue, ref OpenXmlWriter writer)
   {

        writer.WriteElement(new Cell { CellValue = new CellValue(cellStringValue), CellReference = cellReference, DataType = CellValues.String });
   }

in the below mentioned method I am getting above exception error..

  private static void AppendTextCell(string cellReference, string cellStringValue, ref OpenXmlWriter writer)
  {           
       writer.WriteElement(new Cell { CellValue = new CellValue(cellStringValue), CellReference = cellReference, DataType = CellValues.String });
  }

would any one please help on how to overcome this error while exporting to excel data ....

Many thanks in advance..

You should use `>` to format errors and get your code left justified. — crthompson, Jun 16 '14 at 05:18
Maybe you need to use CellValues.InlineString as described in http://stackoverflow.com/questions/6468783/what-is-the-difference-between-cellvalues-inlinestring-and-cellvalues-string-in — Mike Hixson, Jun 16 '14 at 05:43
@MikeHixson sorry inline string also I am getting same problem.... — Glory Raj, Jun 16 '14 at 06:06

score 6 · Accepted Answer · answered Jun 16 '14 at 06:17

6

XML can handle just about any character, but there are ranges, control codes and such, that it won't.

Your best bet, if you can't get them to fix their output, is to sanitize the raw data you're receiving. You need replace illegal characters with the character reference format you noted.

There are a lot of symbols which can't be in xml code. For replace them we can use Reqex.Replace

static string ReplaceHexadecimalSymbols(string txt)
{
   string r = "[\x00-\x08\x0B\x0C\x0E-\x1F\x26]";
   return Regex.Replace(txt, r,"",RegexOptions.Compiled);
}

answered Jun 16 '14 at 06:17

hmm thanks .. where I need to use this replacement in my code – Glory Raj Jun 16 '14 at 06:20
invoke this function where you write data to excel to replace string that you want to insert in excel. – Jun 16 '14 at 06:23
like this........ cellValue = ReplaceHexadecimalSymbols (dr.ItemArray[colInx].ToString()) – Jun 16 '14 at 06:25
2

Just a suggestion - compile regex once, an then just call Replace method. This will increase performance. static Regex regexReplaceHex = new Regex("[\x00-\x08\x0B\x0C\x0E-\x1F\x26]", RegexOptions.Compiled); static string ReplaceHexadecimalSymbols(string txt) { if (!string.IsNullOrEmpty(txt)) return regexReplaceHex.Replace(txt, string.Empty); } – Master Jun 06 '18 at 09:01

score 1 · Answer 2 · answered Jun 16 '14 at 08:45

1

x26 is the ampersand '&' character - this can be encoded with '&' so that it appears in your excel sheet.

answered Jun 16 '14 at 08:45

JimFR

89
11

error while exporting to excel data : ' ', hexadecimal value 0x07, is an invalid character using c#

2 Answers2