Hi Friends,
I am using Visual studio 2019 and .netcore 3.1.
how to generate single excel sheet in an excel file from multiple datasets,
I am using DocumentFormat.OpenXml nuget package.
I want to first display the contents from table1 from dataset1.
Then I want to display the contents from table2 from dataset1.
Next I want to display the contents from table1 from dataset2.
next I want to display the contents from table2 from dataset2. and so on
This should be done in single excel sheet in single excel file.
Thanks in advance
George
What I have tried:
I am using DocumentFormat.OpenXml nuget package.
I have used the following code
private const string noRecordsToDisplay = "No records to display";
public static byte[] ExportToExcelDownload(DataSet dataSet)
{
byte[] byteResult = null;
if (dataSet == null) { return byteResult; }
if (dataSet.Tables.Count > 0)
{
using (MemoryStream stream = new MemoryStream())
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = AddWorkbookPart(spreadsheetDocument);
AddSheet(spreadsheetDocument, out Sheets sheets, out uint currentSheetID);
AddNewPartStyle(workbookpart);
int rowIndexCount = 1;
foreach (DataTable dt in dataSet.Tables)
{
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
Columns columns = SetDefaultColumnWidth();
worksheetPart.Worksheet.Append(columns);
SheetData sheetData = new SheetData();
worksheetPart.Worksheet.AppendChild(sheetData);
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = currentSheetID,
Name = string.IsNullOrWhiteSpace(dt.TableName) ? "Sheet" + currentSheetID : dt.TableName
};
if (dt.Rows.Count == 0)
{
CreateDefaultWithMessage(rowIndexCount, sheetData);
}
else
{
int numberOfColumns = dt.Columns.Count;
string[] excelColumnNames = new string[numberOfColumns];
Row SheetrowHeader = CreateHeader(rowIndexCount, dt, numberOfColumns, excelColumnNames);
sheetData.Append(SheetrowHeader);
++rowIndexCount;
rowIndexCount = CreateBody(rowIndexCount, dt, sheetData, excelColumnNames);
}
sheets.Append(sheet);
++currentSheetID;
rowIndexCount = 1;
}
workbookpart.Workbook.Save();
}
stream.Flush();
stream.Position = 0;
byteResult = new byte[stream.Length];
stream.Read(byteResult, 0, byteResult.Length);
}
}
return byteResult;
}