Using the library cXXXXXXXXXXXXXXXXXXXXXXXXXXML

Question

I have a C # application that is exported to Excel using ClosedXML . It works fine, but just ran into a problem when I hit:

var ms = new MemoryStream(); workbook.SaveAs(ms); 

I get an exception:

 ' ', hexadecimal value 0x0B, is an invalid character 

Certain data is related to the fact that I look at certain data, which it works fine, but other data leads to this problem.

how can i figure out which character is causing the problem? Also, as soon as I find out, what is the best way to find where this character is in my data?

Answer 1

Since you have invalid characters in the data lines / lines that you put in the ClosedXML sheet, you have to find them and get them.

The simplest solution is to add

 .Replace((0x0B).ToString(), " ") 

for all your lines to get rid of vertical tabs and replace them with spaces.

Answer 2

Since ClosedXML is an open source project, the easiest way to track the error would be to build it from source ^* and then run your code in the library in debug mode.

Once you see the full stack trace, you can determine where the error is coming from. A good chance is a bug in how the ClosedXML project uses Microsoft XML libraries because the error you specified is reported by the library outside of the ClosedXML project.

< / "> ^* I downloaded the project and tried to build it. Everything in the closedxml-79843.zip package closedxml-79843.zip built correctly.

Answer 3

Since ClosedXML does not stop you from using the 0x0B character in values, you will either have to clean your data yourself (as suggested by @Raidri), or you can force and exclude, or replace the string when the value is set. I created an example program that uses Dynamic Proxy Lock to wrap the IXLWorksheet and IXLCell . First, we proxy IXLWorksheet values ​​(which were returned from adding a new sheet, as in the example below, or by indexing an existing sheet). This must be done manually by calling the method; everything else has been set up since then. When accessing cells (via the Cell methods or the ActiveCell property), the IXLCell value is IXLCell , which checks the data specified using the Value property and the SetValue method. Validation is done in ValidateMethodInterceptor according to the comments. All this mechanism can be left in your code base and turned on / off using the switch in the Program.Proxy method, if you wish.

As an additional alternative, the EPPlus package (which has similar features to ClosedXML) does not crash when it encounters a VT symbol, instead it replaces it with the value _x00B_ . Perhaps the switch will be more useful?

 internal class Program { private static void Main(string[] args) { var stream = new MemoryStream(); using (stream) { using (var workbook = new XLWorkbook()) { using (var worksheet = Proxy(workbook.Worksheets.Add("Sheet 1"))) { worksheet.Cell("A1").Value = "This is a test"; worksheet.Cell("A2").Value = "This \v is a test"; workbook.SaveAs(stream); } } } } public static IXLWorksheet Proxy(IXLWorksheet target) { var generator = new ProxyGenerator(); var options = new ProxyGenerationOptions { Selector = new WorksheetInterceptorSelector() }; return generator.CreateInterfaceProxyWithTarget<IXLWorksheet>(target, options); } } public class WorksheetInterceptorSelector : IInterceptorSelector { private static readonly MethodInfo[] methodsToAdjust; private readonly ProxyCellInterceptor proxyCellInterceptor = new ProxyCellInterceptor(); static WorksheetInterceptorSelector() { methodsToAdjust = typeof(IXLWorksheet).GetMethods() .Where(x => x.Name == "Cell") .Union(new[] { typeof(IXLWorksheet).GetProperty("ActiveCell").GetGetMethod() }) .ToArray(); } #region IInterceptorSelector Members public IInterceptor[] SelectInterceptors(System.Type type, System.Reflection.MethodInfo method, IInterceptor[] interceptors) { if (!methodsToAdjust.Contains(method)) return interceptors; return new IInterceptor[] { proxyCellInterceptor }.Union(interceptors).ToArray(); } #endregion } public class CellInterceptorSelector : IInterceptorSelector { private static readonly MethodInfo[] methodsToAdjust = new[] { typeof(IXLCell).GetMethod("SetValue"), typeof(IXLCell).GetProperty("Value").GetSetMethod() }; private ValidateMethodInterceptor proxyCellInterceptor = new ValidateMethodInterceptor(); #region IInterceptorSelector Members public IInterceptor[] SelectInterceptors(System.Type type, MethodInfo method, IInterceptor[] interceptors) { if (method.IsGenericMethod && method.Name == "SetValue" || methodsToAdjust.Contains(method)) return new IInterceptor[] { proxyCellInterceptor }.Union(interceptors).ToArray(); return interceptors; } #endregion } public class ProxyCellInterceptor : IInterceptor { #region IInterceptor Members public void Intercept(IInvocation invocation) { invocation.Proceed(); //Wrap the return value invocation.ReturnValue = Proxy((IXLCell)invocation.ReturnValue); } #endregion public IXLCell Proxy(IXLCell target) { var generator = new ProxyGenerator(); var options = new ProxyGenerationOptions { Selector = new CellInterceptorSelector() }; return generator.CreateInterfaceProxyWithTarget<IXLCell>(target, options); } } public class ValidateMethodInterceptor : IInterceptor { #region IInterceptor Members public void Intercept(IInvocation invocation) { var value = invocation.Arguments[0]; //Validate the data as it is being set if (value != null && value.ToString().Contains('\v')) { throw new ArgumentException("Value cannot contain vertical tabs!"); } //Alternatively, you could do a string replace: //if (value != null && value.ToString().Contains('\v')) //{ // invocation.Arguments[0] = value.ToString().Replace("\v", Environment.NewLine); //} invocation.Proceed(); } #endregion }