Export DataTable to Excel?

Question

I need code that shows how to export a DataTable out to an Excel spreadsheet.

 string data = null;
            int i = 0;
            int j = 0;
            string sFileName=null;

            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            for (i = 0; i <= dt.Rows.Count - 1; i++)
            {
                for (j = 0; j <= dt.Columns.Count - 1; j++)
                {
                    data = dt.Rows[i].ItemArray[j].ToString();
                    xlWorkSheet.Cells[i + 1, j + 1] = data;
                }
            }

            SaveFileDialog oDialog = new SaveFileDialog();
            oDialog.Filter = "Excel files | *.xls";
            if (oDialog.ShowDialog() == DialogResult.OK)
            {
                sFileName = oDialog.FileName;
            }

            if (sFileName != null)
            {
                xlWorkBook.SaveAs(sFileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();
                MessageBox.Show("Report saved with file: " + sFileName, "To Excel", MessageBoxButtons.OK, MessageBoxIcon.Information);


            }
            else
            {
                //oEXLApp.ActiveWorkbook.Close(false, Missing.Value, Missing.Value);
                MessageBox.Show("Cannot export to excel...", "Can't export", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

This code can export datatable to excel, but problem is that I don't get the column name. I want dt(DataTable)column name show than the value comes.

Answer 1

Here is an extension method I wrote for this very task:

    public static Range LoadFromDataTable(this Worksheet ws, int startRow, int startCol, System.Data.DataTable dt)
    {
        var columnHeaders = new List<string>();
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            columnHeaders.Add(dt.Columns[i].ColumnName);
        }
        ws.Range[ws.Cells[startRow, startCol], ws.Cells[startRow, startCol + dt.Columns.Count - 1]].Value2 = columnHeaders.ToArray();

        //Transfer dt data to 2d array
        var data = new object[dt.Rows.Count, dt.Columns.Count];
        for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
        {
            for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
            {
                data[rowIndex, colIndex] = dt.Rows[rowIndex][colIndex];
            }
        }
        ws.Range[ws.Cells[startRow + 1, startCol], ws.Cells[startRow + dt.Rows.Count, startCol + dt.Columns.Count - 1]].Value2 =
            data;

        var rng = ws.Range[ws.Cells[startRow, startCol], ws.Cells[startRow + dt.Rows.Count, startCol + dt.Columns.Count - 1]];
        return rng;
    }

The reason why I transfer to a 2d array is for performance reasons.

Answer 2

require 'spreadsheet'

def export_from_db_to_excel(model)
  @format = Spreadsheet::Format.new(:weight => :bold)
  @template_folder = File.join(Rails.root, 'vendor','gems','omni','db','import','templates') 
  @template_file = File.join(@template_folder, "my_file_to_write_to")
  @book = Spreadsheet::Workbook.new  
  write_sheet = Spreadsheet::Workbook.new :name => model.table_name
  write_sheet.row(0).set_format(0, @format)
  write_sheet.row(0).push 'display'
  # Create header row
  model.columns.each_with_index do |col, i| 
    puts 'processing attribute: ' + col.name
    column = ""
    column << "*" unless col.null  #to indicate required fields
    column << "#{col.name}"
    write_sheet.row(0).set_format(i+1, @format)
    write_sheet.row(0).push column
  end
  # Insert data
  model.all.each do |row|
    row.keys.reject {|k| !row[k] or row[k] == ' '}.each {|attribute_name| write_sheet.row(0).push row[attribute_name]
  end
  @book.write @template_file = File.join(@template_folder, @template_file)
end

Answer 3

the simplest solution would be if you just do a small loop

 xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//new code here
for(int i=0; i<dt.Columns.Count; i++)
{
  xlWorkSheet.Cells[0,i] = dt.Columns[i].ColumnName;
}

for (i = 0; i <= dt.Rows.Count - 1; i++)
{
    for (j = 0; j <= dt.Columns.Count - 1; j++)
    {
         data = dt.Rows[i].ItemArray[j].ToString();
         xlWorkSheet.Cells[i + 2, j + 1] = data;
    }
}