c# - Create a xls or csv file with certain header of rows and columns

Question

I have a list of ID in a matrix 'UserID'. I want create a xls or csv file that this UserID is its header lines. number of rows is:2200000 and number of columns is 11. Label of columns is years of 1996 - 2006 . I read this page :

https://www.mathworks.com/matlabcentral/answers/101309-how-do-i-use-xlswrite-to-add-row-and-column-labels-to-my-matlab-matrix-when-i-write-it-to-excel-in-m

but this code give me error. Although sometimes less is true for the number of rows and sometimes does not answer.Can anyone introduce a program that will do this? (with matlab or even c# code)

I write this code:

data=zeros(2200000,11);
data_cells=num2cell(data);
col_header={'1996','1997','1998','1999','2000','2001','2002','2003','2004','2005','2006'};
row_header(1:2200000,1)=UserID;
output_matrix=[{' '} col_header; row_header data_cells];
xlswrite('My_file.xls',output_matrix);

and I get this error:

The specified data range is invalid or too large to write to the specified file format. Try writing to an XLSX file and use Excel A1 notation for the range argument, for example, ‘A1:D4’.

Answer 1

When you use xlswrite you are limited to the number of rows that your Excel version permits:

The maximum size of array A depends on the associated Excel version.

In Excel 2013 the maximum is 1048576, which is 1151424 fewer rows than your 2200000x11 matrix.

You better use csvwrite to export your data, and refer also to the tip therein:

csvwrite does not accept cell arrays for the input matrix M. To export a cell array that contains only numeric data, use cell2mat to convert the cell array to a numeric matrix before calling csvwrite. To export cell arrays with mixed alphabetic and numeric data... you must use low-level export functions to write your data.

EDIT:

In your case, you should at least change this parts of the code:

col_header = 1996:2006;
output_matrix=[0, col_header; row_header data];

and you don't need to define output_matrix as a cell array (and don't need data_dells). However, you may also have to convert UserID to a numeric variable.