public FileResult ExportMarkups(int companyId, string sCurrDate)
{
byte[] b;
DateTime curr;
sCurrDate = sCurrDate.Replace("'", "");
if (!DateTime.TryParse(sCurrDate, out curr))
{
curr = DateTime.Now.ToLocalTime();
}
string currDate = $"{curr.ToShortDateString()} {curr.ToShortTimeString()}";
Company company = db.FindCompany(companyId);
MarkupModel mm = new MarkupModel();
if (company.markup != null)
{
mm.markups = JsonConvert.DeserializeObject <List <Markup> >(company.markup);
}
DCTSOpenXML oxl = new DCTSOpenXML();
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;
string loc;
int rr;
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Markups"
};
sheets.Append(sheet);
Worksheet worksheet = new Worksheet();
SheetData sd = new SheetData();
// Build sheet
// Title
row = new Row();
cell = oxl.SetCellVal("A1", $"Export - Markups {currDate}");
row.Append(cell);
sd.Append(row);
row = new Row();
cell = oxl.SetCellVal("A2", "");
row.Append(cell);
sd.Append(row);
// Headers
row = new Row();
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 1, Max = 1, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("A3", "Title"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 2, Max = 2, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("B3", "Multiplier"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 3, Max = 3, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("C3", "Markup"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 3, Max = 3, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("D3", "Margin"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 4, Max = 4, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("E3", "Addend"); row.Append(cell);
worksheet.Append(oxl.columns);
sd.Append(row);
// Content
for (int i = 0; i < mm.markups.Count(); i++)
{
Markup m = mm.markups[i];
row = new Row();
rr = 4 + i;
loc = "A" + rr; cell = oxl.SetCellVal(loc, m.title); row.Append(cell);
loc = "B" + rr; cell = oxl.SetCellVal(loc, m.multiplier); row.Append(cell);
loc = "C" + rr; cell = oxl.SetCellVal(loc, m.ratio); row.Append(cell);
loc = "D" + rr; cell = oxl.SetCellVal(loc, m.margin); row.Append(cell);
loc = "E" + rr; cell = oxl.SetCellVal(loc, m.Addend); row.Append(cell);
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",
$"Markups as of {currDate}.xlsx"));
}
}
}
public FileResult ExportStoneShapesReport(int companyId, string sCurrDate)
{
byte[] b;
DateTime curr;
sCurrDate = sCurrDate.Replace("'", "");
if (!DateTime.TryParse(sCurrDate, out curr))
{
curr = DateTime.Now.ToLocalTime();
}
string currDate = $"{curr.ToShortDateString()} {curr.ToShortTimeString()}";
DCTSOpenXML oxl = new DCTSOpenXML();
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;
string loc;
int rr;
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Stone Shapes"
};
sheets.Append(sheet);
Worksheet worksheet = new Worksheet();
SheetData sd = new SheetData();
// Build sheet
// Title
row = new Row();
cell = oxl.SetCellVal("A1", $"Export - Stone Shapes {currDate}");
row.Append(cell);
sd.Append(row);
row = new Row();
cell = oxl.SetCellVal("A2", "");
row.Append(cell);
sd.Append(row);
// Headers
row = new Row();
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 1, Max = 1, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("A3", "Name"); row.Append(cell);
worksheet.Append(oxl.columns);
sd.Append(row);
List <Shape> Shapes = db.Shapes.Where(v => v.CompanyId == companyId).ToList();
// Content
for (int i = 0; i < Shapes.Count(); i++)
{
row = new Row();
rr = 4 + i;
loc = "A" + rr; cell = oxl.SetCellVal(loc, Shapes[i].Name); row.Append(cell);
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",
"Stone Shapes as of " + currDate + ".xlsx"));
}
}
}
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"));
}
}
}
public async Task <FileResult> ExportCollectionReport(int companyId, string sCurrDate)
{
byte[] b;
DateTime curr;
sCurrDate = sCurrDate.Replace("'", "");
if (!DateTime.TryParse(sCurrDate, out curr))
{
curr = DateTime.Now.ToLocalTime();
}
string currDate = $"{curr.ToShortDateString()} {curr.ToShortTimeString()}";
DCTSOpenXML oxl = new DCTSOpenXML();
List <Memo> memos = db.Memos.ToList();
using (MemoryStream memStream = new MemoryStream())
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(memStream, SpreadsheetDocumentType.Workbook))
{
// Build Excel File
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
// Stylesheet
Stylesheet stylesheet = oxl.CreateStyleSheet();
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>();
Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
workbookStylesPart.Stylesheet = stylesheet;
workbookStylesPart.Stylesheet.Save();
// declare locals
Row row;
Cell cell;
string loc;
int rr;
uint shNo = 1;
//oxl.columns = columns;
//for each collection
foreach (Collection collection in db.Collections.Where(c => c.CompanyId == companyId).OrderBy(c => c.Name).Include("Styles"))
{
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = shNo++,
Name = collection.Name // collection name"
};
sheets.Append(sheet);
// Clear out the drawing object form each page
drawing = null;
//columns = new Columns();
Columns columns = new Columns();
Worksheet worksheet = new Worksheet();
SheetData sd = new SheetData();
List <Bitmap> images = new List <Bitmap>();
string imageName = "";
// Build sheet
// Title
row = new Row();
cell = oxl.SetCellVal("A1", $"Export - Collection {collection.Name} {currDate}", false);
row.Append(cell);
sd.Append(row);
row = new Row();
cell = oxl.SetCellVal("A2", "");
row.Append(cell);
sd.Append(row);
// Headers
row = new Row();
cell = oxl.SetCellVal("A3", "", false, 3); row.Append(cell); columns.Append(new Column()
{
Width = 15.3D, Min = 1, Max = 1, BestFit = false, CustomWidth = false
});
cell = oxl.SetCellVal("B3", "Name", false, 3); row.Append(cell); columns.Append(new Column()
{
Width = 20.9D, Min = 2, Max = 2, BestFit = false, CustomWidth = false
});
cell = oxl.SetCellVal("C3", "Desc", false, 3); row.Append(cell); columns.Append(new Column()
{
Width = 20.9D, Min = 3, Max = 3, BestFit = false, CustomWidth = false
});
//cell = oxl.SetCellVal("D3", "Style No.", false, 3); row.Append(cell); columns.Append(new Column() { Width = 17.2D, Min = 4, Max = 4, BestFit = false, CustomWidth = false });
cell = oxl.SetCellVal("D3", "Inv", false, 3); row.Append(cell); columns.Append(new Column()
{
Width = 7.67, Min = 4, Max = 4, BestFit = false, CustomWidth = false
});
sd.Append(row);
worksheet.Append(columns);
oxl.columns = columns;
List <Style> Styles = collection.Styles.OrderBy(s => s.StyleName).ThenBy(s => s.Desc).ToList();
// Content
for (int i = 0; i < Styles.Count(); i++)
{
row = new Row();
rr = 4 + i;
Bitmap image;
if (Styles[i].Image == null)
{
image = new Bitmap(Server.MapPath("/Images") + "/logo.png");
}
else
{
// get the file off storage
image = new Bitmap(await Singletons.azureBlobStorage.Download(Styles[i].Image));
//image = Image.FromFile(Server.MapPath("/Images") + "/logo.png");
}
images.Add(image);
row.Height = oxl.ComputeExcelCellHeight(pixelRowHeight);
row.CustomHeight = true;
loc = "A" + rr; cell = oxl.SetCellVal(loc, image, pixelRowHeight, false, 3); row.Append(cell);
loc = "B" + rr; cell = oxl.SetCellVal(loc, Styles[i].StyleName, false, 3); row.Append(cell);
loc = "C" + rr; cell = oxl.SetCellVal(loc, Styles[i].Desc, false, 3); row.Append(cell);
//loc = "D" + rr; cell = oxl.SetCellVal(loc, Styles[i].StyleNum, false, 3); row.Append(cell);
//List<Memo> memos = db.Memos.Where(m => m.StyleID.Value == Styles[i].Id).ToList();
int styleId = Styles[i].Id;
double quantity = memos.Where(m => m.StyleID == styleId).Sum(m => m.Quantity);
loc = "D" + rr; cell = oxl.SetCellVal(loc, quantity, false, 3); row.Append(cell);
sd.Append(row);
}
worksheet.Append(sd);
worksheetPart.Worksheet = worksheet;
// Images
Column col0 = columns.ElementAt(0) as Column;
double col0Width = col0.Width;
for (int i = 0; i < Styles.Count(); i++)
{
if (Styles[i].Image == null)
{
imageName = Server.MapPath("/Images") + "/logo.png";
}
else
{
// get the file off storage
imageName = Styles[i].Image;
//imageName = Server.MapPath("/Images") + "/logo.png";
}
rr = 4 + i;
// place images in column A
string contentType = MimeMapping.GetMimeMapping(imageName);
PlaceImageOnCell(worksheet, images[i], 0, rr - 1, col0Width, pixelRowHeight, contentType, Styles[i].StyleName, Styles[i].Desc);
}
}
workbookPart.Workbook.Save();
document.Close();
b = memStream.ToArray();
string compName = db.FindCompany(companyId).Name;
return(File(b, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
compName + " Collection as of " + $"{currDate}" + ".xlsx"));
}
}
}
public FileResult ExportFindingsReport(int companyId, string sCurrDate)
{
byte[] b;
DateTime curr;
sCurrDate = sCurrDate.Replace("'", "");
if (!DateTime.TryParse(sCurrDate, out curr))
{
curr = DateTime.Now.ToLocalTime();
}
string currDate = $"{curr.ToShortDateString()} {curr.ToShortTimeString()}";
DCTSOpenXML oxl = new DCTSOpenXML();
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;
string loc;
int rr;
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Findings"
};
sheets.Append(sheet);
Worksheet worksheet = new Worksheet();
SheetData sd = new SheetData();
// Build sheet
// Title
row = new Row();
cell = oxl.SetCellVal("A1", $"Export - Findings {currDate}");
row.Append(cell);
sd.Append(row);
row = new Row();
cell = oxl.SetCellVal("A2", "");
row.Append(cell);
sd.Append(row);
// Headers
row = new Row();
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 1, Max = 1, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("A3", "Id"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 2, Max = 2, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("B3", "Name"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 3, Max = 3, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("C3", "Weight (dwt)"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 4, Max = 4, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("D3", "Price"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 5, Max = 5, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("E3", "Vendor"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 6, Max = 6, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("F3", "Inventory"); row.Append(cell);
worksheet.Append(oxl.columns);
sd.Append(row);
List <Finding> Findings = db.Findings.Where(v => v.CompanyId == companyId).OrderBy(f => f.Name).Include("Vendor").ToList();
// Content
for (int i = 0; i < Findings.Count(); i++)
{
row = new Row();
rr = 4 + i;
loc = "A" + rr; cell = oxl.SetCellVal(loc, Findings[i].Id); row.Append(cell);
loc = "B" + rr; cell = oxl.SetCellVal(loc, Findings[i].Name); row.Append(cell);
loc = "C" + rr;
if (Findings[i].Weight == null)
{
cell = oxl.SetCellVal(loc, "");
}
else
{
cell = oxl.SetCellVal(loc, Findings[i].Weight.Value);
}
row.Append(cell);
loc = "D" + rr; cell = oxl.SetCellVal(loc, Findings[i].Price); row.Append(cell);
loc = "E" + rr; cell = oxl.SetCellVal(loc, Findings[i].Vendor.Name); row.Append(cell);
loc = "F" + rr; cell = oxl.SetCellVal(loc, Findings[i].Qty); row.Append(cell);
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",
$"Findings as of {currDate}.xlsx"));
}
}
}
public FileResult ExportVendorReport(int companyId, string sCurrDate)
{
byte[] b;
DateTime curr;
sCurrDate = sCurrDate.Replace("'", "");
if (!DateTime.TryParse(sCurrDate, out curr))
{
curr = DateTime.Now.ToLocalTime();
}
string currDate = $"{curr.ToShortDateString()} {curr.ToShortTimeString()}";
DCTSOpenXML oxl = new DCTSOpenXML();
using (MemoryStream memStream = new MemoryStream())
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(memStream, SpreadsheetDocumentType.Workbook))
{
// Build Excel File
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
workbookPart.AddNewPart <WorkbookStylesPart>();
workbookPart.WorkbookStylesPart.Stylesheet = oxl.CreateStyleSheet();
workbookPart.WorkbookStylesPart.Stylesheet.Save();
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;
string loc;
int rr;
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Vendors"
};
sheets.Append(sheet);
Worksheet worksheet = new Worksheet();
SheetData sd = new SheetData();
// Build sheet
// Title
row = new Row();
cell = oxl.SetCellVal("A1", $"Export - Vendors {currDate}");
row.Append(cell);
sd.Append(row);
row = new Row();
cell = oxl.SetCellVal("A2", "");
row.Append(cell);
sd.Append(row);
// Headers
row = new Row();
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 1, Max = 1, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("A3", "Name"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 2, Max = 2, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("B3", "Phone"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 3, Max = 3, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("C3", "Email"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 4, Max = 4, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("D3", "Sells"); row.Append(cell);
worksheet.Append(oxl.columns);
sd.Append(row);
List <Vendor> vendors = db.Vendors.Where(v => v.CompanyId == companyId).OrderBy(vv => vv.Name).ToList();
// Content
int offset = 4;
for (int i = 0; i < vendors.Count(); i++)
{
if (vendors[i].Name == "") // don't print default vendor
{
offset--;
continue;
}
row = new Row();
rr = offset + i;
loc = "A" + rr; cell = oxl.SetCellVal(loc, vendors[i].Name); row.Append(cell);
loc = "B" + rr; cell = oxl.SetCellVal(loc, vendors[i].Phone); row.Append(cell);
loc = "C" + rr; cell = oxl.SetCellVal(loc, vendors[i].Email); row.Append(cell);
loc = "D" + rr; cell = oxl.SetCellVal(loc, vendors[i].Type.Type.ToString()); row.Append(cell);
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",
"Vendors as of " + $"{currDate}" + ".xlsx"));
}
}
}
public FileResult ExportLaborTable(int companyId, string sCurrDate)
{
byte[] b;
DateTime curr;
sCurrDate = sCurrDate.Replace("'", "");
if (!DateTime.TryParse(sCurrDate, out curr))
{
curr = DateTime.Now.ToLocalTime();
}
string currDate = $"{curr.ToShortDateString()} {curr.ToShortTimeString()}";
DCTSOpenXML oxl = new DCTSOpenXML();
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;
string loc;
int rr;
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Labor Table"
};
sheets.Append(sheet);
Worksheet worksheet = new Worksheet();
SheetData sd = new SheetData();
// Build sheet
// Title
row = new Row();
cell = oxl.SetCellVal("A1", $"Export - Labor Table {currDate}");
row.Append(cell);
sd.Append(row);
row = new Row();
cell = oxl.SetCellVal("A2", "");
row.Append(cell);
sd.Append(row);
// Headers
row = new Row();
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 1, Max = 1, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("A3", "Name"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 2, Max = 2, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("B3", "$/Hour"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 3, Max = 3, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("C3", "$/Piece"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 4, Max = 4, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("D3", "Vendor"); row.Append(cell);
worksheet.Append(oxl.columns);
sd.Append(row);
List <LaborItem> LaborTableItems = db.LaborTable.Where(lt => lt.CompanyId == companyId).OrderBy(lt => lt.Name).ToList();
// Content
for (int i = 0; i < LaborTableItems.Count(); i++)
{
row = new Row();
rr = 4 + i;
loc = "A" + rr; cell = oxl.SetCellVal(loc, LaborTableItems[i].Name); row.Append(cell);
loc = "B" + rr; cell = oxl.SetCellVal(loc, LaborTableItems[i].pph.GetValueOrDefault()); row.Append(cell);
loc = "C" + rr; cell = oxl.SetCellVal(loc, LaborTableItems[i].ppp.GetValueOrDefault()); row.Append(cell);
loc = "D" + rr; cell = oxl.SetCellVal(loc, LaborTableItems[i].Vendor.Name); row.Append(cell);
sd.Append(row);
}
worksheet.Append(sd);
worksheetPart.Worksheet = worksheet;
workbookPart.Workbook.Save();
document.Close();
b = memStream.ToArray();
return(File(b, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
"Labor Table as of " + $"{currDate}" + ".xlsx"));
}
}
}
