Pages

Tuesday, December 31, 2013

Write DataTable to Excel

public void WriteToExcel(Excel.Worksheet worksheet, DataTable dataTable)
{
    var columns = dataTable.Columns.Count;
    var rows = dataTable.Rows.Count;
    var startCell = (Excel.Range)worksheet.Cells[1, 1];
    var endCell = (Excel.Range)worksheet.Cells[rows + 1, columns];
    var writeRange = (Excel.Range)worksheet.get_Range(startCell, endCell);
    var data = new object[rows + 1, columns];
    //Header                   
    for (var column = 0; column < columns; column++)
    {
        data[0, column] = dataTable.Columns[column].ColumnName;
    }
    for (var row = 1; row <= rows; row++)
    {
        for (var column = 0; column < columns; column++)
        {
            data[row, column] = dataTable.Rows[row - 1][column].ToString();
        }
    }
    worksheet.Rows.Clear();
    writeRange.Value = data;
    worksheet.Name = "test";   
}

No comments:

Post a Comment