how to write Excel from datatable without using row by row access

I am trying to populate Excel with datatable data by looping row by row of datatable.

In this process it is taking more time for 20-30 thousand records(15min)

Is there any other way we can write to Excel without looping through row by row and faster?

I am not using sqlserver,just populating xml with 20K records to dataset and populating excel.

I found the following code in google , but this doesnot work in .net.(says there is no ExcelFile define开发者_如何学God)

    Dim ef2 As New ExcelFile

    ' Imports all the tables from DataSet to new file.
    For Each table In ds.Tables
        ' Add new worksheet to the file.
        Dim ws As ExcelWorksheet = ef2.Worksheets.Add(table.TableName)

        ' Change the value of the first cell in the DataTable.
        table.Rows(0)(0) = "This is new file!"

        ' Insert the data from DataTable to the worksheet starting at cell "A1".
        ws.InsertDataTable(table, "A1", True)
    Next

    ' Save the file to XLS format.
    ef2.SaveXls("DataSet.xls")

---

Take a look at this article, which explains how to export a DataTable to Excel using the OpenXML SDK.

http://lateral8.com/articles/2010/3/5/openxml-sdk-20-export-a-datatable-to-excel.aspx

Here is the main content from the article:

Create the ExcelExport Class

Now create a new class file called ExcelExport.cs in your project. Add the following references to the beginning of the file:

using System; using System.Data; using System.Linq; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet;

Next, add the following method to the file:

public void ExportDataTable(DataTable table, string exportFile)
{
    //create the empty spreadsheet template and save the file 
    //using the class generated by the Productivity tool  ExcelDocument excelDocument = new ExcelDocument();
    excelDocument.CreatePackage(exportFile);

    //populate the data into the spreadsheet  using (SpreadsheetDocument spreadsheet =
    SpreadsheetDocument.Open(exportFile, true))
    {
        WorkbookPart workbook = spreadsheet.WorkbookPart;
        //create a reference to Sheet1  WorksheetPart worksheet = workbook.WorksheetParts.Last();
        SheetData data = worksheet.Worksheet.GetFirstChild<SheetData>();

        //add column names to the first row  Row header = new Row();
        header.RowIndex = (UInt32)1;

        foreach (DataColumn column in table.Columns)
        {
            Cell headerCell = createTextCell(table.Columns.IndexOf(column) + 1, 1, column.ColumnName);
            header.AppendChild(headerCell); 
        }

        data.AppendChild(header);

        //loop through each data row  DataRow contentRow;
        for (int i = 0;i < table.Rows.Count; i++)
        {
            contentRow = table.Rows[i];
            data.AppendChild(createContentRow(contentRow, i + 2));
        }
    }            
}

The method above starts by using the ExcelDocument class created earlier to create a new file, which is saved to the location specified in the exportFile argument. Once the file is created, there are two main loops that occur. The first loops iterates through the DataTable object's columns and creates a Cell object for each column name using the createTextCell method:

private Cell createTextCell(int columnIndex, int rowIndex, object cellValue)
{
    Cell cell = new Cell();

    cell.DataType = CellValues.InlineString;
    cell.CellReference = getColumnName(columnIndex) + rowIndex;

    InlineString inlineString = new InlineString();
    Text t = new Text();

    t.Text = cellValue.ToString();
    inlineString.AppendChild(t);
    cell.AppendChild(inlineString);

    return cell;

}

Next, each row is appended to the worksheet using the createContentRow method:

private Row createContentRow(DataRow dataRow, int rowIndex)
{
    Row row = new Row  {RowIndex = (UInt32)rowIndex }; 

    for (int i = 0; i < dataRow.Table.Columns.Count; i++)
    {
        Cell dataCell = createTextCell(i + 1, rowIndex, dataRow[i]);
        row.AppendChild(dataCell);
    }
    return row;
}

The createTextCell method uses one additional supporting method to do the job of mapping the rows and columns to the correct cell reference, the method getColumnName:

private string getColumnName(int columnIndex)
{
    int dividend = columnIndex;
    string columnName = String.Empty;
    int modifier;

    while (dividend > 0)
    {
        modifier = (dividend - 1) % 26;
        columnName = 
            Convert.ToChar(65 + modifier).ToString() + columnName;
        dividend = (int)((dividend - modifier) / 26);
    }

    return columnName;
}

This method provides a quick an easy way to map a column index number to an Excel column name (A-Z). The Cell object in the OpenXML SDK requires a valid Excel cell reference (ex. A1, C2) to be specified, so this method is combined with a row index reference to create the cell reference. It is important to note that the indexes here are not zero-based.

Finally, to implement the class above, use the following code:

//create DataTable from sample data DataSet sampleDataSet = new DataSet();
sampleDataSet.ReadXml(context.Server.MapPath("~/sampleData.xml"));
DataTable productsTable = sampleDataSet.Tables[0];
string exportFile = context.Server.MapPath("~/excelExport.xslx");

ExcelExport export = new ExcelExport();
export.ExportDataTable(productsTable, exportFile);

---

I had found one solution on MSDN with just 5 lines of codes through which you can export data from Data Table to Excel within few seconds.

       //using ClosedXML.Excel;
        XLWorkbook wb = new XLWorkbook();
        DataTable dt = GetTable();
        wb.Worksheets.Add(dt, "WorksheetName");
        wb.SaveAs(@"D:\HelloWorld.xlsx");

ClosedXML

---

To either get or set a range in one operation you have to work out the size, and then get/set it with one large 2 dimensional object array.

//get values
object[,] objectArray = shtName.get_Range("A1:Z100").Value2;
iNumber = Convert.ToInt32(objectArray[1,1]);

//set values
object[,] objectArray = new object[3,1] {{"A"}{"B"}{"C"}};
rngName.Value2 = objectArray;

To set a DataTable to the Excel spreadsheet "in one go" you will need to convert it into an Object Array:

//set DataTable 
var targetRange = "Sheet1!A1:" + GetExcelColumnName(dt.Columns.Count) + rows.Count.ToString();

rng = Application.Range(targetRange);
rng.Value2 = ConvertTo2DArray(dataTable1);

public static object[,] ConvertTo2DArray(DataTable dt)
{
    var rows = dt.Rows;
    int rowCount = rows.Count;
    int colCount = dt.Columns.Count;
    var result = new object[rowCount, colCount];

    for (int i = 0; i < rowCount; i++)
    {
        var row = rows[i];
        for (int j = 0; j < colCount; j++)
        {
            result[i, j] = row[j];
        }
    }    
    return result;
}

private string GetExcelColumnName(int columnNumber)
{
    string columnName = "";    
    while (columnNumber > 0)
    {
        int modulo = (columnNumber - 1) % 26;
        columnName = Convert.ToChar('A' + modulo) + columnName;
        columnNumber = (columnNumber - modulo) / 26;
    }     
    return columnName;
}

Note its important you know what datatype Excel is storing (text or numbers) as it won't automatically do this for you when you are converting the type back from the object array.

ps If you want to keep styles when Exporting a DataGridView to Excel with all the cells format.

Ref: https://stackoverflow.com/a/2294087/495455, https://stackoverflow.com/a/2294087/495455

---

Why don't you start by creating multiple threads. Each thread would read 1000 records (one by one) simultaneously and write them to their respective temp file or Stream Writer. In the end, merge the result.

Hope that helps.