static void OnPostprocessAllAssets(string[] importedAssets, string[] deletedAssets, string[] movedAssets, string[] movedFromAssetPaths)
{
foreach (string asset in importedAssets)
{
if (!filePath.Equals(asset))
{
continue;
}
Sheet2 data = (Sheet2)AssetDatabase.LoadAssetAtPath(assetFilePath, typeof(Sheet2));
if (data == null)
{
data = ScriptableObject.CreateInstance <Sheet2> ();
data.SheetName = filePath;
data.WorksheetName = sheetName;
AssetDatabase.CreateAsset((ScriptableObject)data, assetFilePath);
//data.hideFlags = HideFlags.NotEditable;
}
//data.dataArray = new ExcelQuery(filePath, sheetName).Deserialize<Sheet2Data>().ToArray();
//ScriptableObject obj = AssetDatabase.LoadAssetAtPath (assetFilePath, typeof(ScriptableObject)) as ScriptableObject;
//EditorUtility.SetDirty (obj);
ExcelQuery query = new ExcelQuery(filePath, sheetName);
if (query != null && query.IsValid())
{
data.dataArray = query.Deserialize <Sheet2Data>().ToArray();
ScriptableObject obj = AssetDatabase.LoadAssetAtPath(assetFilePath, typeof(ScriptableObject)) as ScriptableObject;
EditorUtility.SetDirty(obj);
}
}
}
public async Task <IActionResult> PutSheet2(int id, Sheet2 sheet2)
{
if (id != sheet2.Id)
{
return(BadRequest());
}
_context.Entry(sheet2).State = EntityState.Modified;
try
{
await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!Sheet2Exists(id))
{
return(NotFound());
}
else
{
throw;
}
}
return(NoContent());
}
private async Task UploadFile()
{
string res = "";
using (FileStream stream = new FileStream("Книга1.xlsx", FileMode.Open))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
int j = -1;
do
{
j++;
int i = 0;
string[] colName = new string[reader.FieldCount];
while (reader.Read())
{
for (int column = 0; column < reader.FieldCount; column++)
{
Console.WriteLine(reader.GetValue(column));
if (i == 0)
{
colName[column] = reader.GetValue(column).ToString();
}
else
{
Sheet sheet;
if (j == 0)
{
sheet = new Sheet1()
{
ColumnName = colName[column],
Value = reader.GetValue(column).ToString(),
DateTime = DateTime.Now
};
await _context.Sheet1s.AddAsync((Sheet1)sheet);
}
else
{
sheet = new Sheet2()
{
ColumnName = colName[column],
Value = reader.GetValue(column).ToString(),
DateTime = DateTime.Now
};
await _context.Sheet2s.AddAsync((Sheet2)sheet);
}
}
}
await _context.SaveChangesAsync();
i++;
}
j++;
} while (reader.NextResult());
}
}
}
private void FrmItemAttr_Load(object sender, EventArgs e)
{
lvItems.Columns.Add("Item #", 100);
lvItems.Columns.Add("Name", 100);
lvItems.Columns.Add("Batch #", 60);
lvItems.Columns.Add("Serial #", 60);
lvItems.Columns.Add("Expired", 100);
Sheet1 ws = Globals.Sheet1;
Sheet2 wsItemAttr = Globals.Sheet2 as Sheet2;
bool oldItemAttrLocked = wsItemAttr.Locked;
wsItemAttr.Locked = false;
Excel.Range range = ws.UsedRange as Excel.Range;
int rowCount = range.Rows.Count;
for (int i = 1; i <= rowCount; ++i)
{
Excel.Range row_range = ws.Cells[i + 1, 1] as Excel.Range;
object val = row_range.Value2;
if (val == null)
{
continue;
}
string item_number = val.ToString();
row_range = ws.Cells[i + 1, 2] as Excel.Range;
val = row_range.Value2;
if (val == null)
{
continue;
}
string item_name = val.ToString();
lvItems.Items.Add(GetItem(item_number, item_name));
}
cboGender.DataSource = EnumToList <LightItem.ItemGender>();
cboSize.DataSource = EnumToList <LightItem.ItemSize>();
cboGender.SelectedIndex = 0;
cboSize.SelectedIndex = 0;
wsItemAttr.Locked = oldItemAttrLocked;
}
private void PopulateData(Excel.Worksheet ws)
{
Sheet1 sh1 = Globals.Sheet1;
Sheet2 sh2 = Globals.Sheet2;
int year_from = int.Parse(txtFromYear.Text);
int year_to = int.Parse(txtToYear.Text);
int SaleAmountColIndex = 0;
int CostOfSalesAmountColIndex = 0;
int GrossProfitColIndex = 0;
int ProfitMarginColIndex = 0;
double TotalSaleAmount = 0;
double TotalCostOfSalesAmount = 0;
double TotalGrossProfit = 0;
double TotalProfitMargin = 0;
bool oldSh2Locked = sh2.Locked;
sh2.Locked = false;
Excel.Range cell = null;
Accountant _obj = AccountantPool.Instance.CurrentAccountant;
IList <Item> items = _obj.ItemMgr.List();
int columnIndex = 1;
int rowOffset = 7;
if (chkItemNumber.Checked)
{
cell = ws.Cells[rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = "Item #";
cell.Font.Bold = true;
}
if (chkItemName.Checked)
{
cell = ws.Cells[rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = "Name";
cell.Font.Bold = true;
}
if (chkBatchNumber.Checked)
{
cell = ws.Cells[rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = "Batch #";
cell.Font.Bold = true;
}
if (chkSerialNumber.Checked)
{
cell = ws.Cells[rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = "Serial #";
cell.Font.Bold = true;
}
if (chkExpiryDate.Checked)
{
cell = ws.Cells[rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = "Expiry Date";
cell.Font.Bold = true;
}
if (chkBrand.Checked)
{
cell = ws.Cells[rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = "Brand";
cell.Font.Bold = true;
}
if (chkColor.Checked)
{
cell = ws.Cells[rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = "Color";
cell.Font.Bold = true;
}
if (chkGender.Checked)
{
cell = ws.Cells[rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = "Gender";
cell.Font.Bold = true;
}
if (chkSize.Checked)
{
cell = ws.Cells[rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = "Size";
cell.Font.Bold = true;
}
if (chkSaleAmount.Checked)
{
SaleAmountColIndex = columnIndex;
cell = ws.Cells[rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = "Sales";
cell.Font.Bold = true;
}
if (chkCostOfSalesAmount.Checked)
{
CostOfSalesAmountColIndex = columnIndex;
cell = ws.Cells[rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = "Cost of Sales";
cell.Font.Bold = true;
}
if (chkGrossProfit.Checked)
{
GrossProfitColIndex = columnIndex;
cell = ws.Cells[rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = "Gross Profit";
cell.Font.Bold = true;
}
if (chkProfitMargin.Checked)
{
ProfitMarginColIndex = columnIndex;
cell = ws.Cells[rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = "%Margin";
cell.Font.Bold = true;
}
if (chkUnitsSold.Checked)
{
cell = ws.Cells[rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = "Units Sold";
cell.Font.Bold = true;
}
if (chkAverageCost.Checked)
{
cell = ws.Cells[rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = "Average Cost";
cell.Font.Bold = true;
}
int totalColCount = columnIndex - 1;
ws.get_Range(ws.Cells[1, 1], ws.Cells[1, totalColCount]).Merge(missing);
cell = ws.Cells[1, 1] as Excel.Range;
cell.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
cell.Font.Bold = true;
cell.Value2 = _obj.DataFileInformationMgr.Company.CompanyName;
ws.get_Range(ws.Cells[2, 1], ws.Cells[2, totalColCount]).Merge(missing);
cell = ws.Cells[2, 1] as Excel.Range;
cell.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
cell.Font.Italic = true;
cell.RowHeight = 48;
cell.Value2 = _obj.DataFileInformationMgr.Company.Address;
ws.get_Range(ws.Cells[3, 1], ws.Cells[3, totalColCount]).Merge(missing);
cell = ws.Cells[3, 1] as Excel.Range;
cell.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
cell.Font.Bold = true;
cell.Value2 = "Analyse Sales [Item]";
ws.get_Range(ws.Cells[4, 1], ws.Cells[4, totalColCount]).Merge(missing);
cell = ws.Cells[4, 1] as Excel.Range;
cell.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
cell.Font.Bold = true;
if (year_from == year_to)
{
cell.Value2 = string.Format("{0}", year_from);
}
else
{
cell.Value2 = string.Format("{0} - {1}", year_from, year_to);
}
ws.get_Range(ws.Cells[5, 1], ws.Cells[5, totalColCount]).Merge(missing);
cell = ws.Cells[5, 1] as Excel.Range;
cell.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
cell.Font.Bold = true;
cell.Value2 = DateTime.Now.ToString("yyyy-MMM-dd");
ws.get_Range(ws.Cells[6, 1], ws.Cells[6, totalColCount]).Merge(missing);
cell = ws.Cells[6, 1] as Excel.Range;
cell.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
cell.Font.Bold = true;
cell.Value2 = DateTime.Now.ToString("HH:mm:ss");
rowOffset++;
int itemCount = items.Count;
for (int i = 0; i < itemCount; ++i)
{
columnIndex = 1;
LightItem _lght = sh2.GetItem(items[i].ItemNumber);
if (chkItemNumber.Checked)
{
cell = ws.Cells[i + rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = items[i].ItemNumber;
}
if (chkItemName.Checked)
{
cell = ws.Cells[i + rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = items[i].ItemName;
}
if (chkBatchNumber.Checked)
{
cell = ws.Cells[i + rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = _lght.BatchNumber;
}
if (chkSerialNumber.Checked)
{
cell = ws.Cells[i + rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = _lght.SerialNumber;
}
if (chkExpiryDate.Checked)
{
cell = ws.Cells[i + rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = _lght.ExpiryDate.ToString("yyyy-MMM-dd");
}
if (chkBrand.Checked)
{
cell = ws.Cells[i + rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = _lght.Brand;
}
if (chkColor.Checked)
{
cell = ws.Cells[i + rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = _lght.Color;
}
if (chkGender.Checked)
{
cell = ws.Cells[i + rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = _lght.Gender.ToString();
}
if (chkSize.Checked)
{
cell = ws.Cells[i + rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = _lght.Size.ToString();
}
List <ItemSalesHistory> itemSalesHistories = _obj.ItemSalesHistoryMgr.List(items[i].ItemID, year_from, year_to);
double SaleAmount = 0;
if (chkSaleAmount.Checked || chkGrossProfit.Checked)
{
foreach (ItemSalesHistory history in itemSalesHistories)
{
SaleAmount += history.SaleAmount;
}
cell = ws.Cells[i + rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = _obj.CurrencyMgr.Format(SaleAmount);
}
double CostOfSalesAmount = 0;
if (chkCostOfSalesAmount.Checked || chkGrossProfit.Checked)
{
foreach (ItemSalesHistory history in itemSalesHistories)
{
CostOfSalesAmount += history.CostOfSalesAmount;
}
cell = ws.Cells[i + rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = _obj.CurrencyMgr.Format(CostOfSalesAmount);
}
double GrossProfit = SaleAmount - CostOfSalesAmount;
if (chkGrossProfit.Checked)
{
cell = ws.Cells[i + rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = _obj.CurrencyMgr.Format(GrossProfit);
}
double margin = 0;
if (SaleAmount != 0)
{
margin = GrossProfit * 100 / SaleAmount;
}
if (chkProfitMargin.Checked)
{
cell = ws.Cells[i + rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = _obj.CurrencyMgr.FormatPercent(margin);
}
if (chkUnitsSold.Checked)
{
double UnitsSold = 0;
foreach (ItemSalesHistory history in itemSalesHistories)
{
UnitsSold += history.UnitsSold;
}
cell = ws.Cells[i + rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = UnitsSold.ToString();
}
if (chkAverageCost.Checked)
{
cell = ws.Cells[i + rowOffset, columnIndex++] as Excel.Range;
cell.Value2 = _obj.CurrencyMgr.Format(items[i].PositiveAverageCost);
}
TotalSaleAmount += SaleAmount;
TotalProfitMargin += margin;
TotalGrossProfit += GrossProfit;
TotalCostOfSalesAmount += CostOfSalesAmount;
}
if (chkSaleAmount.Checked)
{
cell = ws.Cells[itemCount + rowOffset, SaleAmountColIndex] as Excel.Range;
cell.Value2 = _obj.CurrencyMgr.Format(TotalSaleAmount / itemCount);
}
if (chkCostOfSalesAmount.Checked)
{
cell = ws.Cells[itemCount + rowOffset, CostOfSalesAmountColIndex] as Excel.Range;
cell.Value2 = _obj.CurrencyMgr.Format(TotalCostOfSalesAmount / itemCount);
}
if (chkProfitMargin.Checked)
{
cell = ws.Cells[itemCount + rowOffset, ProfitMarginColIndex] as Excel.Range;
cell.Value2 = _obj.CurrencyMgr.FormatPercent(TotalProfitMargin / itemCount);
}
if (chkGrossProfit.Checked)
{
cell = ws.Cells[itemCount + rowOffset, GrossProfitColIndex] as Excel.Range;
cell.Value2 = _obj.CurrencyMgr.Format(TotalGrossProfit / itemCount);
}
ws.UsedRange.Columns.AutoFit();
sh2.Locked = oldSh2Locked;
}
