// GET: Stones
public ActionResult Index(int?companyId)
{
if (companyId == null)
{
return(RedirectToAction("Index", "Home"));
}
StoneSorter stoneSorter = new StoneSorter();
companyId = companyId.Value;
var stones = db.Stones.Where(st => st.CompanyId == companyId).Include(s => s.Company).Include(s => s.Shape).Include(s => s.Vendor);
ViewBag.CompanyId = companyId;
ViewBag.CompanyName = db._Companies.Find(companyId)?.Name;
return(View(stones.AsEnumerable().OrderBy(g => g.Name).ThenBy(h => h.Shape.Name).ThenBy(i => i.StoneSize, stoneSorter).ToList()));
}
public FileResult ExportStonesReport(int companyId, string sCurrDate)
{
byte[] b;
sCurrDate = sCurrDate.Replace("'", "");
if (!DateTime.TryParse(sCurrDate, out DateTime curr))
{
curr = DateTime.Now.ToLocalTime();
}
string currDate = $"{curr.ToShortDateString()} {curr.ToShortTimeString()}";
DCTSOpenXML oxl = new DCTSOpenXML();
StoneSorter stoneSorter = new StoneSorter();
using (MemoryStream memStream = new MemoryStream())
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(memStream, SpreadsheetDocumentType.Workbook))
{
// Build Excel File
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
// declare locals
Row row;
Cell cell;
int rr;
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Stones"
};
sheets.Append(sheet);
Worksheet worksheet = new Worksheet();
SheetData sd = new SheetData();
// Build sheet
// Title
row = new Row();
cell = oxl.SetCellVal("A1", $"Export - Stones {currDate}");
row.Append(cell);
sd.Append(row);
row = new Row();
cell = oxl.SetCellVal("A2", "");
row.Append(cell);
sd.Append(row);
// Headers
row = new Row();
UInt32 cn = 1;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Id"); row.Append(cell); cn++;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Name"); row.Append(cell); cn++;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Title"); row.Append(cell); cn++;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Stone"); row.Append(cell); cn++;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Shape"); row.Append(cell); cn++;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Vendor"); row.Append(cell); cn++;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Carat"); row.Append(cell); cn++;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Size"); row.Append(cell); cn++;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Price"); row.Append(cell); cn++;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Setting Cost"); row.Append(cell); cn++;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Qty"); row.Append(cell); cn++;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Parent Handle"); row.Append(cell); cn++;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Tags"); row.Append(cell); cn++;
worksheet.Append(oxl.columns);
sd.Append(row);
var stones = db.Stones.Where(v => v.CompanyId == companyId).Include("Vendor").Include("Shape");
List <Stone> Stones = stones.AsEnumerable()
.OrderBy(s => s.Name).ThenBy(s => s.Shape.Name).ThenBy(s => s.StoneSize, stoneSorter).ToList();
// Content
for (int i = 0; i < Stones.Count(); i++)
{
cn = 1;
row = new Row();
rr = 4 + i;
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Id); row.Append(cell); cn++;
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Label); row.Append(cell); cn++;
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Title); row.Append(cell); cn++;
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Name); row.Append(cell); cn++;
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Shape.Name); row.Append(cell); cn++;
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Vendor?.Name); row.Append(cell); cn++;
if (Stones[i].CtWt == null)
{
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), "");
}
else
{
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].CtWt.Value);
}
row.Append(cell); cn++;
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].StoneSize); row.Append(cell); cn++;
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Price); row.Append(cell); cn++;
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].SettingCost); row.Append(cell); cn++;
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Qty); row.Append(cell); cn++;
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].ParentHandle); row.Append(cell); cn++;
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Tags); row.Append(cell); cn++;
sd.Append(row);
}
worksheet.Append(sd);
// Autofit columns - ss:AutoFitWidth="1"
worksheetPart.Worksheet = worksheet;
workbookPart.Workbook.Save();
document.Close();
b = memStream.ToArray();
return(File(b, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
"Stones as of " + $"{currDate}" + ".xlsx"));
}
}
}
