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