Click here to Skip to main content
16,016,925 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I have a spreadsheet with 3 sheets named as T1,T2 and T3.

I have three datatables in dataset named as T1,T2 and T3.

My requirement is, Insert T1 Datatable data into T1 Sheet.:Insert T2 Datatable data into T2 Sheet : Insert T3 Datatable data into T3 Sheet.

Here i am creted spreadsheet using Open XMl SDK.

If anyone knows please help me guys.....I'm struck with that work......
Posted

1 solution

check below code, it is convert all the DataTables to Excel sheets in workbook. I have tested and working fine. credit goes to This User[^]

C#
private void ExportDataSet(DataSet ds, string destination)
{
    using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
    {
        var workbookPart = workbook.AddWorkbookPart();

        workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

        workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();

        foreach (System.Data.DataTable table in ds.Tables)
        {
            var sheetPart = workbook.WorkbookPart.AddNewPart<worksheetpart>();
            var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
            sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);

            DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<documentformat.openxml.spreadsheet.sheets>();
            string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

            uint sheetId = 1;
            if (sheets.Elements<documentformat.openxml.spreadsheet.sheet>().Count() > 0)
            {
                sheetId =
                    sheets.Elements<documentformat.openxml.spreadsheet.sheet>().Select(s => s.SheetId.Value).Max() + 1;
            }

            DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
            sheets.Append(sheet);

            DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

            List<string> columns = new List<string>();
            foreach (System.Data.DataColumn column in table.Columns)
            {
                columns.Add(column.ColumnName);

                DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
                headerRow.AppendChild(cell);
            }


            sheetData.AppendChild(headerRow);

            foreach (System.Data.DataRow dsrow in table.Rows)
            {
                DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                foreach (String col in columns)
                {
                    DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                    cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                    cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); 
                    newRow.AppendChild(cell);
                }

                sheetData.AppendChild(newRow);
            }

        }
    }
}</string></string></documentformat.openxml.spreadsheet.sheet></documentformat.openxml.spreadsheet.sheet></documentformat.openxml.spreadsheet.sheets></worksheetpart>
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900