/// <summary>
/// Generate Excel Document.
/// </summary>
/// <param name="queryResult">QueryResult</param>
/// <returns>MemoryStream</returns>
public static MemoryStream CreateExcelDocument(QueryResult queryResult)
{
var ms = new MemoryStream( );
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook, true))
{
// Create the Workbook
WorkbookPart workbookPart = spreadSheet.AddWorkbookPart( );
spreadSheet.WorkbookPart.Workbook = new Workbook( );
// A Workbook must only have exactly one <Sheets> section
spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets( ));
var newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart <WorksheetPart>( );
newWorksheetPart.Worksheet = new Worksheet( );
// Create a new Excel worksheet
SheetData sheetData = newWorksheetPart.Worksheet.AppendChild(new SheetData( ));
var tdp = newWorksheetPart.AddNewPart <TableDefinitionPart>( );
string rId = newWorksheetPart.GetIdOfPart(tdp);
var T = new Table
{
Id = 1U,
Name = "MyTable",
DisplayName = "MyTable",
Reference = "A1:B10",
TotalsRowShown = false
};
var columns = new TableColumns
{
Count = 2U
};
var column1 = new TableColumn
{
Id = 1U,
Name = "Column1"
};
var column2 = new TableColumn
{
Id = 2U,
Name = "Column2"
};
var styleInfo = new TableStyleInfo
{
Name = "TableStyleMedium2",
ShowFirstColumn = false,
ShowLastColumn = false,
ShowRowStripes = true,
ShowColumnStripes = false
};
var autoFilter = new AutoFilter
{
Reference = "A1:B10"
};
columns.Append(column1);
columns.Append(column2);
T.Append(autoFilter);
T.Append(columns);
T.Append(styleInfo);
tdp.Table = T;
T.Save( );
var tableParts = new TableParts
{
Count = 1U
};
var tablePart = new TablePart
{
Id = rId
};
tableParts.Append(tablePart);
newWorksheetPart.Worksheet.Append(tableParts);
newWorksheetPart.Worksheet.Save( );
// Create Styles and Insert into Workbook
var stylesPart = workbookPart.AddNewPart <WorkbookStylesPart>( );
Stylesheet styles = new ExportDataStylesheet( );
styles.Save(stylesPart);
// Insert Datatable data into the worksheet.
InsertTableData(queryResult, sheetData, stylesPart);
// Save the worksheet.
newWorksheetPart.Worksheet.Save( );
// Link this worksheet to our workbook
spreadSheet.WorkbookPart.Workbook.GetFirstChild <Sheets>( ).AppendChild(new Sheet
{
Id = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart),
SheetId = 1,
Name = "Table"
});
// Save the workbook.
spreadSheet.WorkbookPart.Workbook.Save( );
}
return(ms);
}
/// <summary>
/// Generate Excel Document.
/// </summary>
/// <param name="reportResult">QueryResult</param>
/// <returns>MemoryStream</returns>
public static MemoryStream CreateExcelDocument(ReportResult reportResult, List <DataRow> rows)
{
sharedStringIndex = 0;
sharedStrings = new ConcurrentDictionary <int, string>();
var ms = new MemoryStream();
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook, true))
{
// Create the Workbook
WorkbookPart workbookPart = spreadSheet.AddWorkbookPart();
spreadSheet.WorkbookPart.Workbook = new Workbook();
// A Workbook must only have exactly one <Sheets> section
spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets());
WorksheetPart newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart <WorksheetPart>("rId1");
newWorksheetPart.Worksheet = new Worksheet();
//Add columns of constant width
int columnIndx = 1;
Columns cols = new Columns();
foreach (ReportColumn column in reportResult.Metadata.ReportColumns.Values)
{
if (!column.IsHidden && column.Type != "Image")
{
cols.Append(CreateColumnData(columnIndx));
columnIndx++;
}
}
newWorksheetPart.Worksheet.Append(cols);
newWorksheetPart.Worksheet.Save();
// Create a new Excel worksheet
SheetData sheetData = newWorksheetPart.Worksheet.AppendChild(new SheetData());
// Create Styles and Insert into Workbook
WorkbookStylesPart stylesPart = workbookPart.AddNewPart <WorkbookStylesPart>("rId3");
Stylesheet styles = new ExportDataStylesheet();
styles.Save(stylesPart);
// Insert Datatable data into the worksheet.
InsertTableData(reportResult, sheetData, rows);
//Create table part.
TableDefinitionPart tableDefinitionPart = newWorksheetPart.AddNewPart <TableDefinitionPart>("rId1");
GenerateTablePartContent(tableDefinitionPart, reportResult, rows, columnIndx - 2);
TableParts tableParts1 = new TableParts()
{
Count = (UInt32Value)1U
};
TablePart tablePart1 = new TablePart()
{
Id = "rId1"
};
tableParts1.Append(tablePart1);
newWorksheetPart.Worksheet.Append(tableParts1);
newWorksheetPart.Worksheet.Save();
//add shared stringd
SharedStringTablePart sharedStringTablePart = spreadSheet.WorkbookPart.AddNewPart <SharedStringTablePart>("rId2");
SharedStringTable sharedStringTable = new SharedStringTable();
foreach (string sharedString in sharedStrings.Values)
{
CreateSharedStringItem(sharedStringTable, sharedString);
}
sharedStringTablePart.SharedStringTable = sharedStringTable;
// Save the worksheet.
newWorksheetPart.Worksheet.Save();
// Link this worksheet to our workbook
spreadSheet.WorkbookPart.Workbook.GetFirstChild <Sheets>().AppendChild(new Sheet()
{
Id = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart),
SheetId = 1,
Name = "Table"
});
// Save the workbook.
spreadSheet.WorkbookPart.Workbook.Save();
}
return(ms);
}
