6

I am using OPENXML SDK 2.0 to stream the spread sheet file. The source data is come from data table and writing this to Spreadsheet using openxml. If there is a one of the column data of a data table has " Treshold%" (this text has tab space on it preceding) and the same being written excel but which is writing it to "Treshold%" in excel cell and removing the tab space.

I am using the code as below. Using workSheetWriter.PasteText and workSheetWriter.PasteValue methods.

WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);

int intValue = 0;
if (strValue.Contains("$"))
{
    strValue = strValue.Replace("$", "");
    strValue = strValue.Replace(",", "");

    workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
}
else if (int.TryParse(strValue, out intValue))
{
    workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
}
else if (string.IsNullOrEmpty(strValue))
{
    workSheetWriter.PasteText(cellLocation, strValue);
}
else
{
    workSheetWriter.PasteText(cellLocation, strValue);
}

Please help on this. How can write the value which a tab space in the begining ( Treshold%) into excel cell as same format ?

Chris
  • 8,527
  • 10
  • 34
  • 51
user2390252
  • 61
  • 1
  • 2

1 Answers1

6

I am using OPENXML SDK 2.0

Since there is no workSheetWriter class in OpenXML SDK 2.0, I guess you are using this library: Simple OOXML

I don't use this library but,

I think you can't by using these methods, since the .PastText and .PasteValue methods are storing text by using CellValues.String and CellValue, and this causes that the space is ignored:

{
    cell.CellValue = new CellValue(value);
    cell.DataType = new EnumValue<CellValues>(type);
}

By using OPENXML SDK 2.0 only, I am able to accomplish what you want (preserve space) by using CellValues.InlineString type, InlineString and Text class:

Text text1 = new Text
{
    Text = " Text with space at beginning",
    Space = SpaceProcessingModeValues.Preserve
};

cell.InlineString = new InlineString(text1);
cell.DataType = new EnumValue<CellValues>(CellValues.InlineString);
huysentruitw
  • 27,376
  • 9
  • 90
  • 133
Chris
  • 8,527
  • 10
  • 34
  • 51
  • If anyone desires another example with more code: https://blogs.msdn.microsoft.com/wriju/2016/03/13/open-xml-create-excel-from-scratch. NOTE: this example does not have the "Preserve" setting set, which is required to keep spacing. You would need to modify the line "Text t = new Text();" to "Text t = new Text() { Space = SpaceProcessingModeValues.Preserve };" – joeshmoe301 Sep 25 '18 at 12:44