Using OpenXML to insert a datatable into excel

I have a datatable that – depending on the user selection – will generate a dynamic datatable with any number of rows and columns. I’m currently using OpenXml to manipulate said spreadsheet. How would I go about inserting a datatable?

Thanks

Stu

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

I found some code which I was able to modify to suit my needs. Hope someone finds this useful.

        public void ExportDataTable(System.Data.DataTable exportData, SheetData sheetData)
    {
            //add column names to the first row  
            Row header = new Row();
            header.RowIndex = (UInt32)42;
            SheetData sheetData2 = new SheetData();

            foreach (DataColumn column in exportData.Columns)
            {
                Cell headerCell = createTextCell(exportData.Columns.IndexOf(column) + 1, Convert.ToInt32(header.RowIndex.Value), column.ColumnName);
                header.AppendChild(headerCell); 
            }

            sheetData.AppendChild(header);

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

        }                    


    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;
    }

    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;
    }


    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;
    }

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0