public async Task <IActionResult> PutSheet1(int id, Sheet1 sheet1)
{
if (id != sheet1.Id)
{
return(BadRequest());
}
_context.Entry(sheet1).State = EntityState.Modified;
try
{
await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!Sheet1Exists(id))
{
return(NotFound());
}
else
{
throw;
}
}
return(NoContent());
}
static void OnPostprocessAllAssets(string[] importedAssets, string[] deletedAssets, string[] movedAssets, string[] movedFromAssetPaths)
{
foreach (string asset in importedAssets)
{
if (!filePath.Equals(asset))
{
continue;
}
Sheet1 data = (Sheet1)AssetDatabase.LoadAssetAtPath(assetFilePath, typeof(Sheet1));
if (data == null)
{
data = ScriptableObject.CreateInstance <Sheet1> ();
data.SheetName = filePath;
data.WorksheetName = sheetName;
AssetDatabase.CreateAsset((ScriptableObject)data, assetFilePath);
//data.hideFlags = HideFlags.NotEditable;
}
//data.dataArray = new ExcelQuery(filePath, sheetName).Deserialize<Sheet1Data>().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 <Sheet1Data>().ToArray();
ScriptableObject obj = AssetDatabase.LoadAssetAtPath(assetFilePath, typeof(ScriptableObject)) as ScriptableObject;
EditorUtility.SetDirty(obj);
}
}
}
/// <summary>
/// 加载结束回调
/// </summary>
private void onCompleted()
{
//UIManager.Instance.ShowPanel<UITest>("TestMain","TestMainPanel");
GetComponent <SpriteRenderer>().sprite = AssetManager.GetAsset <Sprite>("123");
data = ExcelDataManager.GetData <Sheet1>("Sheet1");
Debug.Log(data.dataArray[2].Name);
}
private void Awake()
{
Instance = this;
sheet1Datas = Resources.Load <Sheet1>("Sheet1").dataArray.ToList();
//이렇게 하면 공유 데이터 활용
sheet1DataObject = Resources.Load <Sheet1>("Sheet1");
}
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 static Dictionary <string, IConfigData> LoadSheet1()
{
var tabel = JsonDataManager.ReadTabelData("Sheet1");
var config = new Dictionary <string, IConfigData>();
foreach (var kv in tabel.RowDic)
{
var row = kv.Value;
var buff = new Sheet1
{
Id = row.GetString("Id"),
Chinese = row.GetString("Chinese"),
English = row.GetString("English")
};
config.Add(buff.Id, buff);
}
return(config);
}
public static bool OtchProtokolType2(Protokols_class.SGroup_class.Protokol_class Protokol, bool CreateNew = true, bool Open = true)
{
{
var MsgErr = "";
for (int i = 0; i < Protokol.MarkCount; i++)
{
var PAMIndex = -1;
for (int j = 0; j < Protokol.SampleCount; j++)
{
if (Protokol[j][i].LocalAlow && Protokol[j][i].Method.Length > 0)
{
if (PAMIndex < 0)
{
PAMIndex = j;
}
else if (Protokol[j][i].Method != Protokol[PAMIndex][i].Method)
{
MsgErr += '\n' + Protokol[j][i].Mark + " имеет различные методы у нормативов " + T.Object.Rows.Get <string>(Protokol[j].ObjectID, C.Object.Norm, C.Norm.Name) + " и " + T.Object.Rows.Get <string>(Protokol[PAMIndex].ObjectID, C.Object.Norm, C.Norm.Name);
}
}
}
}
if (MsgErr.Length > 0)
{
MessageBox.Show(MsgErr, "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(false);
}
}
string NewFileName;
{
int Month, Year;
ATMisc.GetYearMonthFromYM(Employe_Form.SPoints.YM, out Year, out Month);
{
NewFileName = Application.StartupPath + "\\Отчеты\\";
if (!Directory.Exists(NewFileName))
{
Directory.CreateDirectory(NewFileName);
}
NewFileName += T.Podr.Rows.Get <string>(Protokol.PodrID, C.Podr.ShrName) + "\\";
if (!Directory.Exists(NewFileName))
{
Directory.CreateDirectory(NewFileName);
}
NewFileName += ATMisc.GetMonthName1(Month) + "\\";
if (!Directory.Exists(NewFileName))
{
Directory.CreateDirectory(NewFileName);
}
}
NewFileName += ProtokolFileName(Protokol);
}
if (CreateNew || !File.Exists(NewFileName))
{
var WorkBook = ATMisc.GetGenericExcel("Протокол испытаний тип 2.xls");
if (WorkBook == null)
{
return(false);
}
var TitleSheet = WorkBook.GetSheet("Заголовок");
NPOI.SS.UserModel.ISheet Sheet1;
if (TitleSheet == null)
{
MessageBox.Show("В шаблоне не найден лист \"Заголовок\", вывод невозможен.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(false);
}
var DT = Protokol.Date;
GetProtokolsExchanges(TitleSheet
, ATMisc.GetDateTimeFromYM(Employe_Form.SPoints.YM).Year
, Protokol.Number.ToString() + "-АВ-" + DT.Month.ToString() + "/" + DT.Year.ToString()
, Protokol.Objects + ' ' + T.Podr.Rows.Get <string>(Protokol.PodrID, C.Podr.ShrName)
, Protokol.Objects
, Protokol.ProtoType
, Protokol.ObjectsLocations
, Protokol.DateOstr
, Protokol.DateP
, Protokol.StrTime
, Protokol.Peoples
, Protokol.Causes
, Protokol.Numbers
, Protokol.Day.ToString()
, ATMisc.GetMonthName2(DT.Month)
, DT.Month.ToString()
, RCache.PSG.GetMethodName(Protokol.PodrID)
, T.PaPoS.Rows.Get <string>(Protokol.PaPoSID, C.PaPoS.Name)
, T.Podr.Rows.Get <string>(Protokol.PodrID, C.Podr.FllName)
, T.Podr.Rows.Get <string>(Protokol.PodrID, C.Podr.ShrName)
, T.Podr.Rows.Get <string>(Protokol.PodrID, C.Podr.Contact)
, 0).Exchange();
{
var Exchanges = new CellExchange_Class(TitleSheet);
Exchanges.ClearExchanges();
Exchanges.AddColumn("{имя свойства}");
Exchanges.AddColumn("{ед. свойства}");
Exchanges.AddColumn("{значение свойства}");
if (Exchanges.CheckTableColumns())
{
for (int i = 0; i < Protokol[0].TCSCount; i++)
{
var Values = new object[3];
Values[0] = Protokol[0].TCSName(i);
Values[1] = Protokol[0].TCSEdType(i);
Values[2] = Protokol[0].TCSValue(i);
Exchanges.SetRow(Values);
}
}
}
if (Protokol.MarkCount == 0)
{
MessageBox.Show("Заполненые показатели не найдены.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(false);
}
string UnDeleteSheetName;
switch (Protokol.SGroup)
{
case data.SGroup.Group2:
UnDeleteSheetName = "Изначальный";
{
int Index = WorkBook.GetSheetIndex(UnDeleteSheetName);
Sheet1 = WorkBook.GetSheetAt(Index);
WorkBook.SetSheetName(Index, "Концентрации");
}
if (Sheet1 == null)
{
MessageBox.Show("В шаблоне не найден лист \"" + UnDeleteSheetName + "\", вывод невозможен.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(false);
}
var SPointIndex = new SColumn_struct(-1, null);
var OEdTypeIndex = new SColumn_struct(-1, null);
var OMethodIndex = new SColumn_struct(-1, null);
var OMarkNameIndex = new SColumn_struct(-1, null);
var NumberIndex = new SColumn_struct(-1, null);
var ResultIndex = new SColumn_struct(-1, null);
var ProbeIndex = new SColumn_struct(-1, null);
int RowIndex = -1;
var SPStyle = new NPOI.SS.UserModel.ICellStyle[3]; //верх, середина, низ
var PStyle = new NPOI.SS.UserModel.ICellStyle[3]; //верх, середина, низ
{
var OEdTypeRowIndex = -1;
var OMethodRowIndex = -1;
var OMarkNameRowIndex = -1;
var NumberRowIndex = -1;
var SPointRowIndex = -1;
var ResultRowIndex = -1;
var ProbeRowIndex = -1;
var ExistColumn = new CellExchange_Class(Sheet1);
ExistColumn.AddExchange("{номер п/п}", (Cell) =>
{
NumberRowIndex = Cell.RowIndex;
NumberIndex = new SColumn_struct(Cell);
}, 5);
ExistColumn.AddExchange("{место отбора}", (Cell) =>
{
SPointRowIndex = Cell.RowIndex;
SPointIndex = new SColumn_struct(Cell);
SPStyle[0] = Sheet1.Workbook.CreateCellStyle(); //верх
CopyStyleFromCell(SPStyle[0], Cell); SPStyle[0].BorderBottom = NPOI.SS.UserModel.BorderStyle.None;
SPStyle[1] = Sheet1.Workbook.CreateCellStyle(); //середина
CopyStyleFromCell(SPStyle[1], Cell); SPStyle[1].BorderBottom = SPStyle[1].BorderTop = NPOI.SS.UserModel.BorderStyle.None;
SPStyle[2] = Sheet1.Workbook.CreateCellStyle(); //низ
CopyStyleFromCell(SPStyle[2], Cell); SPStyle[2].BorderTop = NPOI.SS.UserModel.BorderStyle.None;
}, 5);
ExistColumn.AddExchange("{показатель}", (Cell) =>
{
OMarkNameRowIndex = Cell.RowIndex;
OMarkNameIndex = new SColumn_struct(Cell);
}, 5);
ExistColumn.AddExchange("{проба}", (Cell) =>
{
ProbeRowIndex = Cell.RowIndex;
ProbeIndex = new SColumn_struct(Cell);
PStyle[0] = Sheet1.Workbook.CreateCellStyle(); //верх
CopyStyleFromCell(PStyle[0], Cell); PStyle[0].BorderBottom = NPOI.SS.UserModel.BorderStyle.None;
PStyle[1] = Sheet1.Workbook.CreateCellStyle(); //середина
CopyStyleFromCell(PStyle[1], Cell); PStyle[1].BorderBottom = PStyle[1].BorderTop = NPOI.SS.UserModel.BorderStyle.None;
PStyle[2] = Sheet1.Workbook.CreateCellStyle(); //низ
CopyStyleFromCell(PStyle[2], Cell); PStyle[2].BorderTop = NPOI.SS.UserModel.BorderStyle.None;
}, 5);
ExistColumn.AddExchange("{ед.изм.}", (Cell) =>
{
OEdTypeRowIndex = Cell.RowIndex;
OEdTypeIndex = new SColumn_struct(Cell);
}, 5);
ExistColumn.AddExchange("{методика}", (Cell) =>
{
OMethodRowIndex = Cell.RowIndex;
OMethodIndex = new SColumn_struct(Cell);
}, 5);
ExistColumn.AddExchange("{результат}", (Cell) =>
{
ResultRowIndex = Cell.RowIndex;
ResultIndex = new SColumn_struct(Cell);
}, 5);
ExistColumn.AddExchange("{Дата}", DT.ToShortDateString(), 5);
ExistColumn.AddExchange("{Номер протокола}", Protokol.Number.ToString() + "-АВ-" + DT.Month.ToString() + "/" + DT.Year.ToString(), 5);
SetResp(ExistColumn, Protokol.PodrID, data.TResp.LaboratoryProtokol);
if (SPointRowIndex == -1 || OMarkNameRowIndex == -1 || OEdTypeRowIndex == -1 || OMethodRowIndex == -1 || ResultRowIndex == -1)
{
MessageBox.Show("Не все табличные метки найдены.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(false);
}
if (ProbeRowIndex > -1 && ProbeRowIndex != OMarkNameRowIndex || NumberRowIndex > -1 && NumberRowIndex != OMarkNameRowIndex || SPointRowIndex != OMarkNameRowIndex || OMarkNameRowIndex != OEdTypeRowIndex || OEdTypeRowIndex != OMethodRowIndex || OMethodRowIndex != ResultRowIndex)
{
MessageBox.Show("Все табличные метки должны распологаться в одной строке.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(false);
}
RowIndex = SPointRowIndex;
}
Sheet1.ShiftRows(RowIndex, Sheet1.LastRowNum, Protokol.TableCount * Protokol.MarkCount - 1);
int ONumber = 0;
for (int i = 0; i < Protokol.TableCount; i++)
{
NPOI.SS.UserModel.IRow Row;
for (int j = 0; j < Protokol.MarkCount; j++)
{
Row = Sheet1.CreateRow(RowIndex++);
if (NumberIndex.Index > -1)
{
ATMisc.SetValue(Row, ++ONumber, NumberIndex.Index, NumberIndex.Style);
}
if (ProbeIndex.Index > -1)
{
ATMisc.SetValue(Row, Protokol[i].Number, ProbeIndex.Index, ProbeIndex.Style);
}
ATMisc.SetValue(Row, Protokol.GetMethod(j), OMethodIndex.Index, OMethodIndex.Style);
ATMisc.SetValue(Row, Protokol.GetMarkName(j), OMarkNameIndex.Index, OMarkNameIndex.Style);
ATMisc.SetValue(Row, Protokol.GetMarkEdType(j), OEdTypeIndex.Index, OEdTypeIndex.Style);
if (Protokol.IsSpetialOut(j, i))
{
ATMisc.SetValue(Row, Protokol.GetSpetialOut(j, i), ResultIndex.Index, ResultIndex.Style);
}
else
{
ATMisc.SetValue(Row, Protokol.GetMarkAmount(j, i), ResultIndex.Index, ResultIndex.Style);
}
}
Row = Sheet1.GetRow(RowIndex - Protokol.MarkCount);
switch (Protokol.MarkCount)
{
case 1:
ATMisc.SetValue(Row, Protokol.GetTableName(i), SPointIndex.Index, SPointIndex.Style);
break;
case 2:
Sheet1.AddMergedRegion(new CellRangeAddress(RowIndex - Protokol.MarkCount, RowIndex - 1, SPointIndex.Index, SPointIndex.Index));
ATMisc.SetValue(Row, Protokol.GetTableName(i), SPointIndex.Index, SPStyle[0]);
Sheet1.GetRow(RowIndex - 1).CreateCell(SPointIndex.Index).CellStyle = SPStyle[2];
if (ProbeIndex.Index > -1)
{
Sheet1.GetRow(RowIndex - 1).CreateCell(ProbeIndex.Index).CellStyle = PStyle[2];
Sheet1.AddMergedRegion(new CellRangeAddress(RowIndex - Protokol.MarkCount, RowIndex - 1, ProbeIndex.Index, ProbeIndex.Index));
}
break;
default:
Sheet1.AddMergedRegion(new CellRangeAddress(RowIndex - Protokol.MarkCount, RowIndex - 1, SPointIndex.Index, SPointIndex.Index));
ATMisc.SetValue(Row, Protokol.GetTableName(i), SPointIndex.Index, SPStyle[0]);
if (ProbeIndex.Index > -1)
{
Sheet1.GetRow(RowIndex - 1).CreateCell(ProbeIndex.Index).CellStyle = PStyle[2];
Sheet1.GetRow(RowIndex - 1).CreateCell(SPointIndex.Index).CellStyle = PStyle[2];
Sheet1.AddMergedRegion(new CellRangeAddress(RowIndex - Protokol.MarkCount, RowIndex - 1, ProbeIndex.Index, ProbeIndex.Index));
for (int s = Protokol.MarkCount - 2; s > 1; s--)
{
Sheet1.GetRow(RowIndex - 1 - s).GetCell(ProbeIndex.Index).CellStyle = PStyle[1];
//Sheet1.GetRow(RowIndex - 1 - s).GetCell(SPointIndex.Index).CellStyle = SPStyle[1]; //это тут лишнее
}
Sheet1.GetRow(RowIndex - 1).CreateCell(ProbeIndex.Index).CellStyle = PStyle[2];
Sheet1.GetRow(RowIndex - 1).GetCell(SPointIndex.Index).CellStyle = SPStyle[2];
}
else
{
for (int s = Protokol.MarkCount - 2; s > 1; s--)
{
Sheet1.GetRow(RowIndex - 1 - s).GetCell(SPointIndex.Index).CellStyle = SPStyle[1];
}
Sheet1.GetRow(RowIndex - 1).GetCell(SPointIndex.Index).CellStyle = SPStyle[2];
}
break;
}
}
break;
default: throw new Exception("Не верный тип протокола");
}
for (int i = 0; i < WorkBook.NumberOfSheets; i++)
{
if (WorkBook.GetSheetAt(i).SheetName.ToLower() != "заголовок" && WorkBook.GetSheetAt(i).SheetName.ToLower() != "концентрации")
{
WorkBook.RemoveSheetAt(i);
i--;
}
}
return(SaveExcel(WorkBook, NewFileName, Open));
}
else
{
if (Open)
{
System.Diagnostics.Process.Start(NewFileName);
}
return(true);
}
}
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;
}
private Match GetPageScaleMatch()
{
return(Regex.Match(Sheet1.GetAttribute("style"), @"\w*\s*scale\((([0-9]*[.])?[0-9]+)(?:,\s(?:([0-9]*[.])?[0-9]+))?\).*"));
}
private void LoadChapter()
{
curChapterData = chapterDatas[chapterIndex];
}