Recording cells by cells is very slow, but I ran into a situation in the recording area

Question

I have a very large array, the size of which depends on the external device, it can be 65536 , or it can be twice as large as 131072 and its ushort data type .

I need to write all the data to a file of 65,536 rows * 1 column .xlsx . I used to write cell by cell, but it only takes 4 minutes.

So, I thought about using a range approach (as indicated here ), my incomplete code looks like this:

{ \\ VoltageFluctuations is of ushort type with 65536 values xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); // Voltage Fluctuations xlWorkSheet1 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet1.Name = "Voltage Fluctuations"; xlWorkSheet1.Cells[1, 1] = ("Minutes"); xlWorkSheet1.Cells[1, 2] = ("Voltage"); var startCell = (Excel.Range)xlWorkSheet1.Cells[2, 2]; var endCell = new object(); endCell = (Excel.Range)xlWorkSheet1.Cells[65536, 2]; var writeRange = xlWorkSheet1.get_Range(startCell, endCell); writeRange.set_Value(Type.Missing, VoltageFluctuations); // As soon as the above line is executed my program crashes // Finally saving this as a .xlsx file type } 

An exception I get:

Answer 1

Try this problem using the OpenXML SDK :

For the interface:

  private static Worksheet GetWorksheet(SpreadsheetDocument document, string worksheetName = "Sheet1") { var sheets = document.WorkbookPart.Workbook .Descendants<Sheet>().Where(s => s.Name == worksheetName); var worksheetPart = (WorksheetPart)document.WorkbookPart .GetPartById(sheets.First().Id); return worksheetPart.Worksheet; } private static string GetColumnName(int columnIndex) { var dividend = columnIndex; var columnName = String.Empty; while (dividend > 0) { var modifier = (dividend - 1) % 26; columnName = Convert.ToChar(65 + modifier).AsString() + columnName; dividend = (dividend - modifier) / 26; } return columnName; } private static Cell CreateTextCell(int columnIndex, int rowIndex, object cellValue) { var cell = new Cell(); var inlineString = new InlineString(); var txt = new Text(); cell.DataType = CellValues.InlineString; cell.CellReference = GetColumnName(columnIndex) + rowIndex; txt.Text = cellValue.AsString(); inlineString.AppendChild(txt); cell.AppendChild(inlineString); return cell; } private static Row CreateContentRow(int rowIndex, object[] cellValues) { var row = new Row { RowIndex = (UInt32)rowIndex }; for (var i = 0; i < cellValues.Length; i++) { var dataCell = CreateTextCell(i + 1, rowIndex, cellValues[i]); row.AppendChild(dataCell); } return row; } 

The data specified in the table:

  var sourcePath = "Report_Template.xlsx"; var data = File.ReadAllBytes(sourcePath); var memoryStream = new MemoryStream(); memoryStream.Write(data, 0, data.Length); var processSettings = new MarkupCompatibilityProcessSettings ( MarkupCompatibilityProcessMode.ProcessAllParts, FileFormatVersions.Office2007 ); var openSettings = new OpenSettings() { MarkupCompatibilityProcessSettings = processSettings, AutoSave = true }; using (var spreadSheet = SpreadsheetDocument.Open(memoryStream, true, openSettings)) { var worksheet = GetWorksheet(spreadSheet); var worksheetPart = worksheet.WorksheetPart; const int lastRowIndex = 1; var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>(); var lastRow = sheetData.Elements<Row>().LastOrDefault(l => l.RowIndex == lastRowIndex); var reportItems=null;//Get your reoirting data... if (lastRow != null) { foreach (var newRow in (from reportItem in reportItems let newRowIndex = lastRow.RowIndex + 1 select CreateContentRow((int)newRowIndex, new object[] { newRowIndex-lastRow.RowIndex, reportItem.Column1, reportItem.Column2 { sheetData.InsertAfter(newRow, lastRow); } worksheet.Save(); } } 

After and before, reference line and cell methods for inserting and adding data ...

Answer 2

You need to declare VoltageFluctuations as follows:

 object[,] VoltageFluctuations = new object[65536, 1]; 

(Think using something like const int size = 65536 )

Admittedly, this is not very intuitive, at least not for me. However, arrays of the ushort type did not work at all, and arrays with one dimension did not give the expected result, but wrote the first value in all cells.