/// <summary>
/// Inserts the cell in worksheet.
/// </summary>
/// <param name="sheetData">The sheet data.</param>
/// <param name="columnName">Name of the column.</param>
/// <param name="rowIndex">Index of the row.</param>
/// <returns></returns>
protected static SpreadSheet.Cell InsertCellInWorksheet(SpreadSheet.SheetData sheetData, string columnName, uint rowIndex)
{
string cellReference = columnName + rowIndex;
SpreadSheet.Row row;
if (sheetData.Elements <SpreadSheet.Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements <SpreadSheet.Row>().Where(r => r.RowIndex == rowIndex).First();
}
else
{
row = new SpreadSheet.Row()
{
RowIndex = rowIndex
};
sheetData.Append(row);
}
// If column doesn't exist, insert one.
if (row.Elements <SpreadSheet.Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
{
return(row.Elements <SpreadSheet.Cell>().Where(c => c.CellReference.Value == cellReference).First());
}
else
{
SpreadSheet.Cell refCell = null;
foreach (SpreadSheet.Cell cell in row.Elements <SpreadSheet.Cell>())
{
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
{
refCell = cell;
break;
}
}
SpreadSheet.Cell newCell = new SpreadSheet.Cell()
{
CellReference = cellReference
};
row.InsertBefore(newCell, refCell);
return(newCell);
}
}
static void SaveRow(Excel.SheetData exportedSheetData, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Row row)
{
Excel.Row exportedRow = new Excel.Row()
{
RowIndex = RowIndex(row), Hidden = row._hidden
};
if (row._hidden)
{
exportedRow.Hidden = true;
}
exportedSheetData.Append(exportedRow);
foreach (var cell in row._cells.OrderBy(r => r.Column._index))
{
SaveCell(exportedRow, styleSheet, cellFormatList, cell);
}
}
public ActionResult DownloadStudentSubmittedProposalsSpreadsheet()
{
int courseId = 1;
uint i = 2;
string cellReference = "A2";
string fileName = "StudentProposalsSpreadsheet_" + DateTime.Now.ToString("G") + ".xlsx";
List <string> headerRow = new List <string>();
MemoryStream memoryStream = new MemoryStream();
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
List <DocumentFormat.OpenXml.Spreadsheet.Font> fonts = new List <DocumentFormat.OpenXml.Spreadsheet.Font>
{
new DocumentFormat.OpenXml.Spreadsheet.Font(new Bold()),
new DocumentFormat.OpenXml.Spreadsheet.Font()
};
List <ForegroundColor> foregroundColors = new List <ForegroundColor>();
foregroundColors.Add(new ForegroundColor {
Rgb = HexBinaryValue.FromString("FFD633")
});
foregroundColors.Add(new ForegroundColor {
Rgb = HexBinaryValue.FromString("FFFFFF")
});
List <BackgroundColor> backgroundColors = new List <BackgroundColor>();
backgroundColors.Add(new BackgroundColor {
Indexed = 64
});
backgroundColors.Add(new BackgroundColor {
Indexed = 64
});
List <DocumentFormat.OpenXml.Spreadsheet.Alignment> alignments = new List <DocumentFormat.OpenXml.Spreadsheet.Alignment>();
alignments.Add(new DocumentFormat.OpenXml.Spreadsheet.Alignment());
alignments.Add(new DocumentFormat.OpenXml.Spreadsheet.Alignment {
WrapText = true, Vertical = VerticalAlignmentValues.Top
});
SpreadsheetDocument spreadsheetDocument;
Stylesheet stylesheet;
stylesheet = ExcelFormatter.CreateStyles(fonts, foregroundColors, backgroundColors, alignments);
spreadsheetDocument = ExcelFormatter.CreateExcelSpreadsheet(ref memoryStream);
ExcelFormatter.InitializeSpreadsheet(ref spreadsheetDocument, stylesheet);
List <List <string> > spreadsheetData = _projectctx.GetSelfInitiatedProjectsSpreadsheetData(courseId);
headerRow.AddRange(new string[] { "S/N", "Project Background", "Project Title", "Project Brief", "Technology Specification" });
sheetData.Append(ExcelFormatter.CreateRow("A1", 1, headerRow, 1));
foreach (List <string> l in spreadsheetData)
{
sheetData.Append(ExcelFormatter.CreateRow(cellReference, i, l, 2));
++i;
cellReference = "A" + i;
}
ExcelFormatter.FinalizeSpreadsheetWriting(ref spreadsheetDocument, ref sheetData);
memoryStream.Seek(0, SeekOrigin.Begin);
Response.Headers.Add("Content-Disposition", "attachement; filename=" + fileName);
Response.Cookies.Append("completedDownloadToken", "downloaded", new CookieOptions()
{
Expires = DateTime.Now.AddSeconds(8)
});
return(new FileStreamResult(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
}
public ActionResult DownloadProjectSelectionSpreadsheet()
{
int courseId = 1;
uint i = 2;
string cellReference = "A2";
string fileName = "ProjectSelectionSpreadsheet_" + DateTime.Now.ToString("G") + ".xlsx";
List <string> headerRow = new List <string>();
IEnumerable <Project> allAvailableProjects = new List <Project>();
MemoryStream memoryStream = new MemoryStream();
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
List <Font> fonts = new List <Font>();
fonts.Add(new Font(new Bold()));
fonts.Add(new Font());
List <ForegroundColor> foregroundColors = new List <ForegroundColor>();
foregroundColors.Add(new ForegroundColor {
Rgb = HexBinaryValue.FromString("FFD633")
});
foregroundColors.Add(new ForegroundColor {
Rgb = HexBinaryValue.FromString("FFFFFF")
});
List <BackgroundColor> backgroundColors = new List <BackgroundColor>();
backgroundColors.Add(new BackgroundColor {
Indexed = 64
});
backgroundColors.Add(new BackgroundColor {
Indexed = 64
});
List <Alignment> alignments = new List <Alignment>();
alignments.Add(new Alignment());
alignments.Add(new Alignment());
SpreadsheetDocument spreadsheetDocument;
Stylesheet stylesheet;
stylesheet = ExcelFormatter.CreateStyles(fonts, foregroundColors, backgroundColors, alignments);
spreadsheetDocument = ExcelFormatter.CreateExcelSpreadsheet(ref memoryStream);
ExcelFormatter.InitializeSpreadsheet(ref spreadsheetDocument, stylesheet);
List <List <string> > spreadsheetData = groupRepository.GetGroupProjectSelectionSpreadsheetData(courseId);
allAvailableProjects = projectRepository.GetAvailableProjects(courseId);
headerRow.AddRange(new string[] { "Group#", "Student ID", "Student Name", "Mobile", "Personal Email" });
foreach (Project p in allAvailableProjects)
{
headerRow.Add(p.project_title);
}
sheetData.Append(ExcelFormatter.CreateRow("A1", 1, headerRow, 1));
foreach (List <string> l in spreadsheetData)
{
sheetData.Append(ExcelFormatter.CreateRow(cellReference, i, l, 2));
++i;
cellReference = "A" + i;
}
ExcelFormatter.FinalizeSpreadsheetWriting(ref spreadsheetDocument, ref sheetData);
memoryStream.Seek(0, SeekOrigin.Begin);
Response.Headers.Add("Content-Disposition", "attachment; filename=" + fileName);
CookieOptions options = new CookieOptions();
options.Expires = DateTime.Now.AddMilliseconds(8000);
Response.Cookies.Append("completedDownloadToken", "downloaded", options);
return(new FileStreamResult(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
}
private SheetData WriteDataTableToExcelWorksheet(List <InventoryValuationModel> data)
{
SheetData sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
//
// Create the Header row in our Excel Worksheet
//
uint rowIndex = 1;
string[] excelColumnNames = new string[12];
excelColumnNames[0] = "Item";
excelColumnNames[1] = "Name";
excelColumnNames[2] = "Brand";
excelColumnNames[3] = "Category";
excelColumnNames[4] = "ContractCategory";
excelColumnNames[5] = "Pack";
excelColumnNames[6] = "Size";
excelColumnNames[7] = "Label";
excelColumnNames[8] = "Each";
excelColumnNames[9] = "Quantity";
excelColumnNames[10] = "Price";
excelColumnNames[11] = "ExtPrice";
rowIndex = OpenXmlSpreadsheetUtilities.AddTitleRow
(rowIndex, "InventoryValuationModel", excelColumnNames, "Inventory Valuation Report", sheetData);
rowIndex = OpenXmlSpreadsheetUtilities.AddCustomerRow
(rowIndex, "InventoryValuationModel", excelColumnNames, _customerRepo.GetCustomerByCustomerNumber(_context.CustomerId, _context.BranchId), sheetData);
var headerRow = new Row {
RowIndex = rowIndex
}; // add a row at the top of spreadsheet
sheetData.Append(headerRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[0] + rowIndex.ToString(), "Item", headerRow, CellValues.String, OpenXmlSpreadsheetUtilities.TEXT_WRAP_BOLD_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[1] + rowIndex.ToString(), "Name", headerRow, CellValues.String, OpenXmlSpreadsheetUtilities.TEXT_WRAP_BOLD_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[2] + rowIndex.ToString(), "Brand", headerRow, CellValues.String, OpenXmlSpreadsheetUtilities.TEXT_WRAP_BOLD_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[3] + rowIndex.ToString(), "Category", headerRow, CellValues.String, OpenXmlSpreadsheetUtilities.TEXT_WRAP_BOLD_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[4] + rowIndex.ToString(), "Contract Category", headerRow, CellValues.String, OpenXmlSpreadsheetUtilities.TEXT_WRAP_BOLD_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[5] + rowIndex.ToString(), "Pack", headerRow, CellValues.String, OpenXmlSpreadsheetUtilities.RIGHT_ALIGNED_TEXT_WRAP_BOLD_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[6] + rowIndex.ToString(), "Size", headerRow, CellValues.String, OpenXmlSpreadsheetUtilities.TEXT_WRAP_BOLD_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[7] + rowIndex.ToString(), "Label", headerRow, CellValues.String, OpenXmlSpreadsheetUtilities.TEXT_WRAP_BOLD_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[8] + rowIndex.ToString(), "Each", headerRow, CellValues.String, OpenXmlSpreadsheetUtilities.TEXT_WRAP_BOLD_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[9] + rowIndex.ToString(), "Quantity", headerRow, CellValues.String, OpenXmlSpreadsheetUtilities.RIGHT_ALIGNED_TEXT_WRAP_BOLD_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[10] + rowIndex.ToString(), "Price", headerRow, CellValues.String, OpenXmlSpreadsheetUtilities.RIGHT_ALIGNED_TEXT_WRAP_BOLD_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[11] + rowIndex.ToString(), "Ext. Price", headerRow, CellValues.String, OpenXmlSpreadsheetUtilities.RIGHT_ALIGNED_TEXT_WRAP_BOLD_CELL);
//
// Now, step through each row of data in our DataTable...
foreach (var item in data)
{
rowIndex++;
var newExcelRow = new Row {
RowIndex = rowIndex
}; // add a row at the top of spreadsheet
sheetData.Append(newExcelRow);
if (item != null)
{
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[0] + rowIndex.ToString(), item.ItemId, newExcelRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[1] + rowIndex.ToString(), item.Name, newExcelRow, CellValues.String, OpenXmlSpreadsheetUtilities.TEXT_WRAP_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[2] + rowIndex.ToString(), item.Brand, newExcelRow, CellValues.String, OpenXmlSpreadsheetUtilities.TEXT_WRAP_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[3] + rowIndex.ToString(), item.Category, newExcelRow, CellValues.String, OpenXmlSpreadsheetUtilities.TEXT_WRAP_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[4] + rowIndex.ToString(), item.ContractCategory, newExcelRow, CellValues.String, OpenXmlSpreadsheetUtilities.TEXT_WRAP_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[5] + rowIndex.ToString(), item.Pack, newExcelRow, CellValues.String, OpenXmlSpreadsheetUtilities.RIGHT_ALIGNED_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[6] + rowIndex.ToString(), item.Size, newExcelRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[7] + rowIndex.ToString(), item.Label, newExcelRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[8] + rowIndex.ToString(), item.Each ? "Y" : "N", newExcelRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[9] + rowIndex.ToString(), item.Quantity.ToString(), newExcelRow, CellValues.String, OpenXmlSpreadsheetUtilities.RIGHT_ALIGNED_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[10] + rowIndex.ToString(), Math.Round(item.Price, 2).ToString("F2"), newExcelRow, CellValues.String, OpenXmlSpreadsheetUtilities.RIGHT_ALIGNED_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[11] + rowIndex.ToString(), Math.Round(item.ExtPrice, 2).ToString("F2"), newExcelRow, CellValues.String, OpenXmlSpreadsheetUtilities.RIGHT_ALIGNED_CELL);
}
}
rowIndex++;
var newRow = new Row {
RowIndex = rowIndex
}; // add a row at the top of spreadsheet
sheetData.Append(newRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[0] + rowIndex.ToString(), "", newRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[1] + rowIndex.ToString(), "", newRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[2] + rowIndex.ToString(), "", newRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[3] + rowIndex.ToString(), "", newRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[4] + rowIndex.ToString(), "", newRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[5] + rowIndex.ToString(), "", newRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[6] + rowIndex.ToString(), "", newRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[7] + rowIndex.ToString(), "", newRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[8] + rowIndex.ToString(), "", newRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[9] + rowIndex.ToString(), "", newRow);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[10] + rowIndex.ToString(), "Total", newRow, CellValues.String, OpenXmlSpreadsheetUtilities.RIGHT_ALIGNED_TEXT_WRAP_BOLD_CELL);
OpenXmlSpreadsheetUtilities.AppendTextCell(excelColumnNames[11] + rowIndex.ToString(), Math.Round(data.Sum(s => s.ExtPrice), 2).ToString("F2"), newRow, CellValues.String, OpenXmlSpreadsheetUtilities.RIGHT_ALIGNED_CELL);
return(sheetData);
}
