CODEWITHSUNDEEP
c#excelopenxml
Nitin Purohit
Nitin Purohit

Reputation: 18580

Writing Japanese string to excel using OpenXml

I am trying to create Excel file by reading data from the database. One of the columns contains Japanese text. While writing that column to excel cell and saving workbook gives following error (which makes sense as the characters are not valid xml chars ) :'', hexadecimal value 0x0B, is an invalid character.

I am writing the string as following to the excel cell using DocumentFormat.OpenXml package.

var excelCell = new Cell();
var cellValue = dtRow[col.Name].ToString(); 
var inlineStr = new InlineString(new Text(cellValue));
excelCell.DataType = CellValues.InlineString;
excelCell.InlineString = inlineStr;

What needs to be done to write Japanese characters to the excel using OpenXml in C#

Upvotes: 0

Views: 494

Answers (1)

Nitin Purohit
Nitin Purohit

Reputation: 18580

Ok. Found the right way. Putting it as answer so that it can be helpful. To add text to excel which is not allowed as valid xml, add the text as SharedString to the SharedStringTable

var index = InsertSharedStringItem(text, shareStringPart);
excelCell.CellValue = new CellValue(index.ToString());
excelCell.DataType = new EnumValue<CellValues>(CellValues.SharedString);



        private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
        {
            // If the part does not contain a SharedStringTable, create one.
            if (shareStringPart.SharedStringTable == null)
            {
                shareStringPart.SharedStringTable = new SharedStringTable();
            }

            int i = 0;

            // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
            foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == text)
                {
                    return i;
                }

                i++;
            }

            // The text does not exist in the part. Create the SharedStringItem and return its index.
            shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
            shareStringPart.SharedStringTable.Save();

            return i;
        }

Full documentation for adding text as shared string to excel using OpenXml https://msdn.microsoft.com/en-us/library/office/cc861607.aspx

Upvotes: 1

Related Questions