Convert a gear array to a 2D array directly without repeating each element?

Question

I am trying to save a DataTable to an Excel worksheet ... my code is as follows ..

Excel.Range range = xlWorkSheet.get_Range("A2"); range = range.get_Resize(dtExcel.Rows.Count, dtExcel.Columns.Count); object[,] rng1 = new object[dtExcel.Rows.Count, dtExcel.Columns.Count]; 

An Excel range requires a range value in the form of an array [,], but I have a DataTable in the form of a gear array [] [].

 object[][] rng2 = dtExcel.AsEnumerable().Select(x => x.ItemArray).ToArray(); 

Is there a built-in function for directly converting a gear array [] [] into a two-dimensional array [] []? Excel iterations, DataTable and assignment seem slower with bulk data.

Also I do not want to configure queries with DSN for Excel. I chose the Excel repository to avoid setting up any databases ..: P I found a detailed explanation of how to write data to succeed here .. http://support.microsoft.com/kb/306023

Answer 1

Finally, I used the NPOI library for this. It is quite simple and free.

The code for converting a DataTable to Excel is as follows.

 HSSFWorkbook hssfworkbook = new HSSFWorkbook(); foreach (DataTable dt in DataSource.Tables) { ISheet sheet1 = hssfworkbook.CreateSheet(dt.TableName); //Set column titles IRow headRow = sheet1.CreateRow(0); for (int colNum = 0; colNum < dt.Columns.Count; colNum++) { ICell cell = headRow.CreateCell(colNum); cell.SetCellValue(dt.Columns[colNum].ColumnName); } //Set values in cells for (int rowNum = 1; rowNum <= dt.Rows.Count; rowNum++) { IRow row = sheet1.CreateRow(rowNum); for (int colNum = 0; colNum < dt.Columns.Count; colNum++) { ICell cell = row.CreateCell(colNum); cell.SetCellValue(dt.Rows[rowNum - 1][colNum].ToString()); } } // Resize column width to show all data for (int colNum = 0; colNum < dt.Columns.Count; colNum++) { sheet1.AutoSizeColumn(colNum); } }