Exception during file writing

[JuniorRaja]

Hi,

I'm trying to write a xlsx file from a dataset. I get the following exception when I'm running in production. Then same code works in my environment.

System.Memory; The type initializer for 'PerTypeValues`1' threw an exception.

[RomanPavelko]

Hi @JuniorRaja,

Your code looks good. Could you please share what line of your code throws this exception and a full stacktrace of it? This is highly likely not related to the library but to the project / environment itself. It's hard to say more without additional details.

[JuniorRaja]

Hi @RomanPavelko

Trying to write the xlsx file through an Windows Service installed in the server. As mentioned earlier, the package works in dev environment.

The stack trace is as below, `Stack Trace

4/1/2024 5:40:10 PM: System.Memory; The type initializer for 'PerTypeValues 1' threw an exception. 4/1/2024 5:40:10 PM: at EODService.DailyReports.writeXLSXSwift(DataTable dsOutput, String ReportName, String Report_Fk) at EODService.DailyReports.generateReports() at EODService.EODService.EODExecution() at EODService.EODService.timer1_Tick(Object sender, ElapsedEventArgs e) at System.Timers.Timer.MyTimerCallback(Object state) at System.Threading.TimerQueueTimer.CallCallbackInContext(Object state) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.TimerQueueTimer.CallCallback() at System.Threading.TimerQueueTimer.Fire() at System.Threading.TimerQueue.FireNextTimers() at System.Threading.TimerQueue.AppDomainTimerCallback(Int32 id) `

[RomanPavelko]

@JuniorRaja unfortunately I still cannot relate this error to the library. In your dev environment, are you also using a Windows Service? Are you also using the same amount of data / dataset? If this is a memory issue within a Windows Service I would suspect an issue with the data source which is DataTable in your case. Is it possible you are holding all of it in memory and it overflows your memory? I would recommend to keep narrowing down the issue and try to rule out SwiftExcel library by commenting out its code and just keep the logic that iterates your data source in prod. Something like this:

int rowNum = 4;
int colNum = 1;
foreach (DataColumn col in dsOutput.Columns)
  {
    var name = col.ColumnName;
    colNum += 1;
}

for (var row = 0; row <= dsOutput.Rows.Count - 1;  row++)
{
  rowNum++;
  colNum = 1;
  for (var col = 0; col < dsOutput.Columns.Count - 1; col++)
  {
    var value = dsOutput.Rows[row][col].ToString();
    colNum += 1;
  }
}

[JuniorRaja]

Hi @RomanPavelko

Thanks to you. I was able to resolve the earlier said exception by splitting the data and writing in chunks. Now, I'm able to work with string and number columns. I'm unable write "date" in the excel. It is being written as string only.

How can I write the dates as Date Format Thanks in advance.

[RomanPavelko]

Congratulations on resolving the previous issue.

Unfortunately SwiftExcel doesn't support exporting dates. You will need to look into the prepaid .Pro version . Feel free to use the trial version for however you need.

Here is a code example how to output a date type with .Pro version:

var style = new Style
{ 
    NumberFormat = NumberFormat.Custom("dd/mmm/yyyy") 
};

var dateToExport = new DateTime(2022, 11, 13); 
using (var ew = new ExcelWriter("C:\\temp\\test.xlsx", style)) 
{ 
    ew.Write($"{dateToExport.ToOADate()}", 1, 2, 1, 1, DataType.Number); 
}