private static void ReadP4Break(Excel.IXLWorksheet shSheet)
{
int nRow = 1;
int nCol = 1;
lstP4Break.Clear();
//第一行是产物，行号从1开始
for (nCol = 1; shSheet.Cell(1, nCol).GetString() != String.Empty; nCol++)
{
nRow = 1;
Objects.T2Product item = new Objects.T2Product();
item.Name = shSheet.Cell(nRow, nCol).GetString();
item.Volume = 1;
nRow++;
//产出材料
while (!string.IsNullOrEmpty(shSheet.Cell(nRow, nCol).GetString()))
{
string[] strValue = shSheet.Cell(nRow, nCol).GetString().Split(new string[] { " (" }, StringSplitOptions.RemoveEmptyEntries);
double dCount = ReadDouble(strValue[1].Substring(0, strValue[1].IndexOf("个")));
dCount = Math.Floor(dCount * 0.55);
item.Items.Add(strValue[0], (int)dCount);
nRow++;
}
lstP4Break.Add(item);
}
}
private static void ReadT2Work(Excel.IXLWorksheet shSheet)
{
int nRow = 1;
int nCol = 1;
lstT2Product.Clear();
//第一行是产物，行号从1开始
for (nCol = 1; shSheet.Cell(1, nCol).GetString() != String.Empty; nCol++)
{
nRow = 1;
Objects.T2Product item = new Objects.T2Product();
string[] strValue = shSheet.Cell(nRow, nCol).GetString().Split(new string[] { " x " }, StringSplitOptions.RemoveEmptyEntries);
item.Name = strValue[1];
item.Volume = int.Parse(strValue[0]);
nRow++;
//材料需求
while (!string.IsNullOrEmpty(shSheet.Cell(nRow, nCol).GetString()))
{
strValue = shSheet.Cell(nRow, nCol).GetString().Split(new string[] { " x " }, StringSplitOptions.RemoveEmptyEntries);
item.Items.Add(strValue[1], ReadInt(strValue[0]));
nRow++;
}
lstT2Product.Add(item);
}
}
private static void ReadT1Item(Excel.IXLWorksheet shSheet)
{
int nRow = 1;
lstT1Item.Clear();
//第一行是表头，行号从1开始
for (nRow = 2; shSheet.Cell(nRow, 1).GetString() != String.Empty; nRow++)
{
Objects.T1Product item = new Objects.T1Product();
item.Name = shSheet.Cell(nRow, 1).GetString();
item.Tri = ReadDouble(shSheet.Cell(nRow, 2).GetString());
item.Pye = ReadDouble(shSheet.Cell(nRow, 3).GetString());
item.Mex = ReadDouble(shSheet.Cell(nRow, 4).GetString());
item.Iso = ReadDouble(shSheet.Cell(nRow, 5).GetString());
item.Noc = ReadDouble(shSheet.Cell(nRow, 6).GetString());
item.Zyd = ReadDouble(shSheet.Cell(nRow, 7).GetString());
item.Meg = ReadDouble(shSheet.Cell(nRow, 8).GetString());
item.Volume = int.Parse(shSheet.Cell(nRow, 9).GetString());
lstT1Item.Add(item);
}
}
private static void ReadItem(Excel.IXLWorksheet shSheet)
{
int nRow = 1;
lstItem.Clear();
//第一行是表头，行号从1开始
for (nRow = 2; shSheet.Cell(nRow, 1).GetString() != String.Empty; nRow++)
{
Objects.Item item = new Objects.Item();
item.TypeID = shSheet.Cell(nRow, 1).GetString();
item.Name = shSheet.Cell(nRow, 2).GetString();
item.Descript = shSheet.Cell(nRow, 3).GetString();
item.Category1 = shSheet.Cell(nRow, 4).GetString();
item.Category2 = shSheet.Cell(nRow, 5).GetString();
item.Category3 = shSheet.Cell(nRow, 6).GetString();
item.Category4 = shSheet.Cell(nRow, 7).GetString();
item.Category5 = shSheet.Cell(nRow, 8).GetString();
item.Category6 = shSheet.Cell(nRow, 9).GetString();
lstItem.Add(item);
}
}
private static void ReadSolarSystem(Excel.IXLWorksheet shSheet)
{
int nRow = 1;
List <JOSolarSystem> lstSystem = new List <JOSolarSystem>();
//第一行是表头，行号从1开始
for (nRow = 2; shSheet.Cell(nRow, 1).GetString() != String.Empty; nRow++)
{
JOSolarSystem item = new JOSolarSystem();
item.system_id = (long)shSheet.Cell(nRow, 1).GetDouble();
item.system_name = shSheet.Cell(nRow, 2).GetString();
item.constellation_id = (long)shSheet.Cell(nRow, 3).GetDouble();
item.constellation_name = shSheet.Cell(nRow, 4).GetString();
item.region_id = (long)shSheet.Cell(nRow, 5).GetDouble();
item.region_name = shSheet.Cell(nRow, 6).GetString();
lstSystem.Add(item);
}
FilesHelper.OutputJsonFile("Sovereignty\\UniverseSystem", JsonConvert.SerializeObject(lstSystem, Formatting.Indented));
}
//-----------------------------------------------------------//
// SubRoutine
//-----------------------------------------------------------//
private void editReviewData(string[,] pubDat)
{
int limCol = Convert.ToInt32(pubDat[11, 5]);
int sCNo = 4;
for (int i = 0; i < Columns; i++)
{
for (int j = 0; j < rowNo.Length; j++)
{
oWSheet.Cell(rowNo[j], sCNo + i).Value = pubDat[j, i] ?? null;
}
if (i >= limCol)
{
oWSheet.Cell(5, sCNo + i).Value = null;
oWSheet.Cell(11, sCNo + i).Value = null;
}
}
oWSheet.Cell(1, 7).Value = pubDat[11, 0] + "年度 " + pubDat[11, 1] + pubDat[11, 2] + " 総括表";
oWSheet.Cell(2, 4).Value = pubDat[11, 3];
oWSheet.Cell(2, 6).Value = pubDat[11, 4];
}
//---------------------------------------------------------/
// Method
//---------------------------------------------------------/
public void ExcelFile(string proc)
{
string tempFile = Folder.DefaultLocation() + @"\.~temp.xlsx";
Cursor.Current = Cursors.WaitCursor; // マウスカーソルを砂時計(Wait)
// Wakamatsu 20170313
try
{
using (oWBook = new XLWorkbook(fileName))
{
// 編集
switch (procList.IndexOf(proc))
{
case 0:
case 1:
if (pod == null || pod.Length == 0)
{
DMessage.DataNotExistence("中断します！");
return;
}
//MessageBox.Show("Excel書込み開始");
if (proc == payoff)
{
oWSheet = oWBook.Worksheet(1); // シートを開く
editOsPayOff(pod); // 起案書発行
}
else
{
// Wakamatsu 20170313
//decimal sum = 0M;
//int page = pod.Length / posRow;
//if (pod.Length % posRow > 0) page++;
//for (int i = 0; i < page; i++)
//{
// oWSheet = oWBook.Worksheet(i + 1); // シートを開く
// oWSheet.Cell(3, 5).Value = "'" + (i + 1).ToString() + "/" + page.ToString();
// sum = editOsPayOffS(pod, posRow * i, sum); // 起案書発行
//}
oWSheet = oWBook.Worksheet(1); // シートを開く
oWSheet.Cell(3, 5).Value = "'1/1";
editOsPayOffS(pod, posRow, 0); // 起案書発行
// Wakamatsu 20170313
}
break;
case 2:
if (pmd == null || pmd.Length == 0)
{
DMessage.DataNotExistence("中断します！");
return;
}
//MessageBox.Show("Excel書込み開始");
oWSheet = oWBook.Worksheet(1); // シートを開く
editOsPayment(pmd); // 調書発行
break;
default:
break;
}
// 保存
oWBook.SaveAs(tempFile); // Excel保存
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
return;
}
// Wakamatsu 20170313
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
//System.Diagnostics.Process.Start("Excel.exe", tempFile); // 表示用Excel
// pdf file 出力
DateTime now = DateTime.Now;
outputFile = System.IO.Path.GetDirectoryName(tempFile) + @"\" + pNameList[procList.IndexOf(proc)] + "_" + itemCode + "_" + now.ToString("yyMMddHHmmss");
PublishExcelToPdf etp = new PublishExcelToPdf();
// Wakamatsu 20170313
//etp.ExcelToPDF(tempFile, outputFile);
if (etp.ExcelToPDF(tempFile, outputFile) == true)
{
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
}
}
//----------------------------------------------------------------------
// SubRoutine
//----------------------------------------------------------------------
private void editOsPayment(OsPaymentData[] pmd)
{
readyExcelRows(pmd.Length, 5);
int sNo = 6;
for (int i = 0; i < pmd.Length; i++)
{
if (i == 0)
{
//oWSheet.Cell(3, 1).Value = pmd[i].ReportDate.ToString("Y");
//oWSheet.Cell(3, 3).Value = "部署：";
//oWSheet.Cell(3, 4).Value = Conv.bList[Conv.OfficeCodeIndex(pmd[i].OfficeCode)] + "／" + Conv.dNmList[Conv.DepNoIndex(pmd[i].Department)];
oWSheet.Cell(1, 4).Value = Convert.ToString(pmd[i].ReportDate.Month) + "月分 外注出来高調書（" + Conv.dNmList[Conv.DepNoIndex(pmd[i].Department)] + "部）";
oWSheet.Cell(1, 7).Value = DateTime.Today;
itemCode = pmd[i].ItemCode;
}
oWSheet.Cell(sNo + i, 1).Value = pmd[i].Item;
oWSheet.Cell(sNo + i, 2).Value = pmd[i].OrderNo;
oWSheet.Cell(sNo + i, 3).Value = pmd[i].TaskCode;
oWSheet.Cell(sNo + i, 4).Value = pmd[i].TaskName;
oWSheet.Cell(sNo + i, 5).Value = pmd[i].OrderAmount;
oWSheet.Cell(sNo + i, 6).Value = pmd[i].SAmount;
oWSheet.Cell(sNo + i, 7).Value = pmd[i].Amount;
oWSheet.Cell(sNo + i, 8).Value = pmd[i].OrderAmount - (pmd[i].SAmount + pmd[i].Amount);
}
}
private static void OutputBluePrintResult(Excel.IXLWorksheet xLSheet, string strKeyWord, ref int nRow, ref int nCol)
{
BluePrint bluePrint = lstBluePrint.Find(Item => Item.BPName == strKeyWord || Item.ProductName == strKeyWord);
if (bluePrint == null)
{
return;
}
//价格查询
List <string> lstSearch = new List <string>();
lstSearch.Add(bluePrint.ProductID.ToString());
foreach (BluePrintMtls Mtls in bluePrint.Materials)
{
lstSearch.Add(Mtls.TypeID.ToString());
}
Dictionary <string, Price> dicResult = CEVEMarketAPI.SearchPriceJson(lstSearch);
xLSheet.Cell(nRow, nCol).Value = "产物";
xLSheet.Cell(nRow, nCol + 1).Value = "流程产量";
xLSheet.Cell(nRow, nCol + 2).Value = "流程数";
xLSheet.Cell(nRow, nCol + 3).Value = "物品ID";
xLSheet.Cell(nRow, nCol + 4).Value = "卖单价";
xLSheet.Cell(nRow, nCol + 5).Value = "收单价";
nRow++;
xLSheet.Cell(nRow, nCol).Value = bluePrint.ProductName;
xLSheet.Cell(nRow, nCol + 1).Value = bluePrint.ProductQty;
xLSheet.Cell(nRow, nCol + 2).Value = 1;
xLSheet.Cell(nRow, nCol + 3).Value = bluePrint.ProductID;
//double dMtlsSell = dicResult[Mtls.TypeID.ToString()].sell.min * Math.Ceiling(dQty);
xLSheet.Cell(nRow, nCol + 4).Value = dicResult[bluePrint.ProductID.ToString()].sell.min;
xLSheet.Cell(nRow, nCol + 5).Value = dicResult[bluePrint.ProductID.ToString()].buy.max;
nRow++;
xLSheet.Cell(nRow, nCol).Value = "材料";
xLSheet.Cell(nRow, nCol + 1).Value = "需求量";
xLSheet.Cell(nRow, nCol + 2).Value = "总需求量";
xLSheet.Cell(nRow, nCol + 3).Value = "物品ID";
xLSheet.Cell(nRow, nCol + 4).Value = "卖单价";
xLSheet.Cell(nRow, nCol + 5).Value = "收单价";
nRow++;
List <string> lstNext = new List <string>();
foreach (BluePrintMtls Mtls in bluePrint.Materials)
{
lstNext.Add(Mtls.Name);
xLSheet.Cell(nRow, nCol).Value = Mtls.Name;
xLSheet.Cell(nRow, nCol + 1).Value = Mtls.Qty;
xLSheet.Cell(nRow, nCol + 2).Value = Mtls.Qty;
xLSheet.Cell(nRow, nCol + 3).Value = Mtls.TypeID;
xLSheet.Cell(nRow, nCol + 4).Value = dicResult[Mtls.TypeID.ToString()].sell.min;
xLSheet.Cell(nRow, nCol + 5).Value = dicResult[Mtls.TypeID.ToString()].buy.max;
nRow++;
}
nRow++;
foreach (string strNextName in lstNext)
{
OutputBluePrintResult(xLSheet, strNextName, ref nRow, ref nCol);
}
return;
}
private void editTaskDataPart(TaskData td)
{
if (td.IssueMark == 1)
{
oWSheet.Cell(1, 1).Value = "仮登録 承認待ち";
oWSheet.Cell(1, 1).Style.Font.FontSize = 14;
oWSheet.Cell(1, 1).Style.Font.FontColor = XLColor.Red;
}
if (td.AdmLevel == 0) // 管理レベル
{
oWSheet.Cell(1, 12).Value = "■ 一般管理";
oWSheet.Cell(1, 10).Value = "□ 重要管理";
}
else
{
oWSheet.Cell(1, 12).Value = "□ 一般管理";
oWSheet.Cell(1, 10).Value = "■ 重要管理";
}
if (td.VersionNo == 0) // 発行回数
{
oWSheet.Cell(2, 1).Value = "■ 仮着工";
oWSheet.Cell(2, 3).Value = "□ 第 0 回";
}
else
{
oWSheet.Cell(2, 1).Value = "□ 仮着工";
oWSheet.Cell(2, 3).Value = "■ 第 " + td.VersionNo.ToString() + " 回";
//oWSheet.Cell( 2, 5 ).Value = td.IssueDate;
}
oWSheet.Cell(2, 5).Value = td.IssueDate;
oWSheet.Cell(3, 3).Value = td.TaskName; // 業務名
//oWSheet.Cell("C3").Value = td.TaskName; // 業務名
oWSheet.Cell(4, 3).Value = td.TaskPlace; // 施工箇所
oWSheet.Cell(5, 10).Value = td.StartDate.ToString("yyyy年MM月dd日") + " ～ " + td.EndDate.ToString("yyyy年MM月dd日"); // 工期
oWSheet.Cell(7, 3).Value = td.PayNote; // 支払条件
oWSheet.Cell(8, 3).Value = td.TaskOffice; // 事務所
oWSheet.Cell(8, 11).Value = td.TaskLeader; // 担当者
oWSheet.Cell(9, 3).Value = td.TelNo; // TEL
oWSheet.Cell(9, 6).Value = td.FaxNo; // FAX
oWSheet.Cell(9, 11).Value = td.EMail; // e-Mail
// 添付書類編集
string[] itemArray = new string[] { "□打合せ協議簿 ", "□見積内訳書 ", "□設計図書 ", "□契約書 ", "□注文書 ", "□着工依頼書 ", "□その他（ ）" };
if (td.AttProceed == 1)
{
itemArray[0] = "■打合せ協議簿 ";
}
if (td.AttEstimate == 1)
{
itemArray[1] = "■見積内訳書 ";
}
if (td.AttDesign == 1)
{
itemArray[2] = "■設計図書 ";
}
if (td.AttContract == 1)
{
itemArray[3] = "■契約書 ";
}
if (td.AttOrder == 1)
{
itemArray[4] = "■注文書 ";
}
if (td.AttStart == 1)
{
itemArray[5] = "■着工依頼書 ";
}
if (td.AttOther == 1)
{
itemArray[6] = "■その他（" + td.AttOtherCont + "）";
}
oWSheet.Cell(20, 3).Value = ""; // 添付書類
for (int i = 0; i < itemArray.Length; i++)
{
oWSheet.Cell(20, 3).Value += itemArray[i];
}
oWSheet.Cell(21, 5).Value = td.OrderNote; // 発注形態
// 仕様書関係編集
itemArray = new string[] { "□共通仕様書 ", "□特記仕様書 ", "□その他（ ）" };
if (td.CommonSpec == 1)
{
itemArray[0] = "■共通仕様書 ";
}
if (td.ExclusiveSpec == 1)
{
itemArray[1] = "■特記仕様書 ";
}
if (td.OtherSpec == 1)
{
itemArray[2] = "■その他（" + td.SpecCont + "）";
}
oWSheet.Cell(22, 5).Value = ""; // 仕様書関係
for (int i = 0; i < itemArray.Length; i++)
{
oWSheet.Cell(22, 5).Value += itemArray[i];
}
// 指示
itemArray = new string[] { "□実行予算書", "□業務計画書" };
if (td.OrderBudget == 1)
{
itemArray[0] = "■実行予算書";
}
if (td.OrderPlanning == 1)
{
itemArray[1] = "■業務計画書";
}
oWSheet.Cell(43, 5).Value = itemArray[0] + Environment.NewLine + itemArray[1];
}
//---------------------------------------------------------/
// Method
//---------------------------------------------------------/
public void ExcelFile()
{
string tempFile = Folder.DefaultLocation() + @"\.~temp.xlsx";
Cursor.Current = Cursors.WaitCursor; // マウスカーソルを砂時計(Wait)
// Wakamatsu 20170315
try
{
using (oWBook = new XLWorkbook(fileName))
{
if (tla == null || tla.Length == 0)
{
DMessage.DataNotExistence("中断します！");
return;
}
// 編集
oWSheet = oWBook.Worksheet(1); // シートを開く
// Wakamatsu 20170315
//readyExcelRows(tla.Length, 5);
readyExcelRows(tla.Length, 4);
int sNo = 5;
for (int i = 0; i < tla.Length; i++)
{
// Wakamatsu 20170315
using (IXLRange SetRange = oWSheet.Range("A5:I5"))
// テンプレートデータ行コピー/ペースト
SetRange.CopyTo(oWSheet.Cell(sNo + i, 1));
// Wakamatsu 20170315
if (i == 0)
{
officeName = tla[i].OfficeName;
departName = tla[i].DepartName;
// Wakamatsu 20170315
//oWSheet.Cell(1, 7).Value = DateTime.Today;
oWSheet.Cell(2, 2).Value = DateTime.Today;
oWSheet.Cell(3, 2).Value = officeName + " " + departName;
// Wakamatsu 20170315
}
oWSheet.Cell(sNo + i, 1).Value = tla[i].TaskCode;
oWSheet.Cell(sNo + i, 2).Value = tla[i].TaskName;
oWSheet.Cell(sNo + i, 3).Value = tla[i].PartnerName;
oWSheet.Cell(sNo + i, 4).Value = tla[i].Contract;
oWSheet.Cell(sNo + i, 5).Value = tla[i].StartDate;
oWSheet.Cell(sNo + i, 6).Value = tla[i].EndDate;
oWSheet.Cell(sNo + i, 7).Value = tla[i].SalesM;
oWSheet.Cell(sNo + i, 8).Value = tla[i].LeaderM;
oWSheet.Cell(sNo + i, 9).Value = tla[i].IssueDate;
// Wakamatsu 20170315
if (i != 0)
{
oWSheet.Range(sNo + i, 1, sNo + i, 9).Style.Border.TopBorder = XLBorderStyleValues.Hair;
}
if (i == tla.Length - 1)
{
oWSheet.Range(sNo + i, 1, sNo + i, 9).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
}
else
{
oWSheet.Range(sNo + i, 1, sNo + i, 9).Style.Border.BottomBorder = XLBorderStyleValues.Hair;
}
// Wakamatsu 20170315
}
}
// 保存
oWBook.SaveAs(tempFile); // Excel保存
}
// Wakamatsu 20170315
catch (Exception ex)
{
MessageBox.Show(ex.Message);
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
return;
}
// Wakamatsu 20170315
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
//System.Diagnostics.Process.Start("Excel.exe", tempFile); // 表示用Excel
// pdf file 出力
DateTime now = DateTime.Now;
outputFile = System.IO.Path.GetDirectoryName(tempFile) + @"\業務一覧表_" + officeName + "_" + departName + "_" + now.ToString("yyMMddHHmmss");
PublishExcelToPdf etp = new PublishExcelToPdf();
etp.ExcelToPDF(tempFile, outputFile);
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
}
private static void ReadT2High(Excel.IXLWorksheet shSheet)
{
int nRow = 1;
lstT2High.Clear();
//第一行是表头，行号从1开始
for (nRow = 2; shSheet.Cell(nRow, 1).GetString() != String.Empty; nRow++)
{
Objects.T2Base item = new Objects.T2Base();
item.Name = shSheet.Cell(nRow, 1).GetString();
item.Volume = int.Parse(shSheet.Cell(nRow, 2).GetString());
if (!string.IsNullOrEmpty(shSheet.Cell(nRow, 3).GetString()))
{
item.Items.Add(shSheet.Cell(nRow, 3).GetString(), int.Parse(shSheet.Cell(nRow, 4).GetString()));
}
if (!string.IsNullOrEmpty(shSheet.Cell(nRow, 5).GetString()))
{
item.Items.Add(shSheet.Cell(nRow, 5).GetString(), int.Parse(shSheet.Cell(nRow, 6).GetString()));
}
if (!string.IsNullOrEmpty(shSheet.Cell(nRow, 7).GetString()))
{
item.Items.Add(shSheet.Cell(nRow, 7).GetString(), int.Parse(shSheet.Cell(nRow, 8).GetString()));
}
if (!string.IsNullOrEmpty(shSheet.Cell(nRow, 9).GetString()))
{
item.Items.Add(shSheet.Cell(nRow, 9).GetString(), int.Parse(shSheet.Cell(nRow, 10).GetString()));
}
if (!string.IsNullOrEmpty(shSheet.Cell(nRow, 11).GetString()))
{
item.Items.Add(shSheet.Cell(nRow, 11).GetString(), int.Parse(shSheet.Cell(nRow, 12).GetString()));
}
lstT2High.Add(item);
}
}
private void Export_Novy()
{
if (!InvokeRequired)
{
form.timer_ClearInfo.Stop();
}
else
{
this.BeginInvoke(new Action(() => form.timer_ClearInfo.Stop()));
}
form.infoBox.Text = form.jazyk.Message_Exportuji;
form.Update();
DateTime start;
DateTime konec;
over = false;
List <ExportRow> exportRadky = new List <ExportRow>();
int odec = 0;
if (DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).DayOfWeek == DayOfWeek.Sunday && (int)DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).DayOfWeek == 0)
{
odec = 1;
}
if (radioButton3.Checked)
{
start = dateTimePicker1.Value;
konec = dateTimePicker2.Value;
}
else if (radioButton1.Checked)
{
konec = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
//tohle zrevidovat, jestli by nestačilo jen to Add day v catch bez try
try
{
start = new DateTime(DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Year, DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Month, DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Day + odec);
}
catch
{
start = new DateTime(DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Year, DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Month, DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Day);
start = start.AddDays(odec);
}
}
else
{
start = DateTime.Now.AddDays(-7);
start = start.AddDays(-(int)(start.DayOfWeek - 1));
start = new DateTime(start.Year, start.Month, start.Day);
konec = new DateTime(start.Year, start.Month, start.Day).AddDays(6);
}
//Projít tickety splňující podmínku a zařadit je správně do exportu
for (DateTime d = start; d <= konec; d = d.AddDays(1))
{
if (form.poDnech.ContainsKey(d))
{
foreach (string s in form.poDnech[d].Keys)
{
foreach (Ticket t in form.poDnech[d][s])
{
DateTime pauzaDohromady = new DateTime();
DateTime cas = new DateTime();
DateTime hrubyCas = new DateTime();
for (int i = 0; i < t.PauzyDo.Count; i++)
{
if (t.PauzyDo[i].ToString("H:mm") != "0:00")
{
pauzaDohromady = pauzaDohromady.AddHours(t.PauzyDo[i].Hour - t.PauzyOd[i].Hour).AddMinutes(t.PauzyDo[i].Minute - t.PauzyOd[i].Minute);
}
}
try
{
cas = cas.AddHours((t.Do.Hour - t.Od.Hour) - pauzaDohromady.Hour).AddMinutes((t.Do.Minute - t.Od.Minute) - pauzaDohromady.Minute);
hrubyCas = form.RoundUp(cas, TimeSpan.FromMinutes(30));
//dny[d][t.TerpT].Add(t, hrubyCas);
string den = "";
switch (d.DayOfWeek)
{
case DayOfWeek.Monday: den = "Pondělí";
break;
case DayOfWeek.Tuesday: den = "Úterý";
break;
case DayOfWeek.Wednesday: den = "Středa";
break;
case DayOfWeek.Thursday: den = "Čtvrtek";
break;
case DayOfWeek.Friday: den = "Pátek";
break;
case DayOfWeek.Saturday: den = "Sobota";
break;
case DayOfWeek.Sunday: den = "Neděle";
break;
}
ExportTyp et;
if (t.TypPrace == 0 || t.TypPrace == 2 || t.TypPrace == 8 || t.TypPrace == 12 || t.TypPrace == 16 || t.TypPrace == 20 || t.TypPrace == 24)
{
et = ExportTyp.Normal;
}
else if (t.TypPrace == 1 || t.TypPrace == 6 || t.TypPrace == 10 || t.TypPrace == 15 || t.TypPrace == 18 || t.TypPrace == 23 || t.TypPrace == 27)
{
et = ExportTyp.Holiday;
}
else if (t.TypPrace == 3 || t.TypPrace == 5 || t.TypPrace == 9 || t.TypPrace == 13 || t.TypPrace == 17 || t.TypPrace == 21 || t.TypPrace == 25)
{
et = ExportTyp.Prescas;
}
else if (t.TypPrace == 4 || t.TypPrace == 7 || t.TypPrace == 11 || t.TypPrace == 14 || t.TypPrace == 19 || t.TypPrace == 22 || t.TypPrace == 26)
{
et = ExportTyp.Compens;
}
else
{
et = ExportTyp.Normal;
}
if (exportRadky.Count == 0)
{
exportRadky.Add(new ExportRow());
}
List <int> toSkip = new List <int>();
if (t.CustomTerp == "")
{
t.CustomTerp = Zakaznici.GetTerp(t.Zakaznik);
}
//když není task, tak defaultně incident 1.2.1 - nová verze by neměla umět uložit bez tasku
if (t.CustomTask == "")
{
t.CustomTask = Zakaznici.Terpy.Get <NbtCompound>("Task").Get <NbtString>("Incident").Value;
}
//přiřazení ticketu ke správnému řádku a dni
for (int i = 0; i < exportRadky.Count; i++)
{
if (exportRadky[i].Terp == null)
{
exportRadky[i].Terp = t.CustomTerp;
exportRadky[i].Task = t.CustomTask;
exportRadky[i].Typ = et;
exportRadky[i].Radek[den].Koment = t.ID + " " + t.Zakaznik + " " + t.Popis + "\r\n";
decimal tCas = hrubyCas.Hour;
if (hrubyCas.Minute == 30)
{
tCas += 0.5m;
}
exportRadky[i].Radek[den].Cas = tCas;
break;
}
else if (exportRadky[i].Terp == t.CustomTerp && exportRadky[i].Task == t.CustomTask && exportRadky[i].Typ == et)
{
if ((exportRadky[i].Radek[den].Koment.Length + (t.ID + " " + t.Zakaznik + " " + t.Popis + "\r\n").Length < 240))
{
exportRadky[i].Radek[den].Koment += t.ID + " " + t.Zakaznik + " " + t.Popis + "\r\n";
decimal tCas = hrubyCas.Hour;
if (hrubyCas.Minute == 30)
{
tCas += 0.5m;
}
exportRadky[i].Radek[den].Cas += tCas;
break;
}
else
{
exportRadky.Add(new ExportRow());
continue;
}
}
else if (i < exportRadky.Count - 1)
{
continue;
}
exportRadky.Add(new ExportRow());
}
}
catch
{
MessageBox.Show(form.jazyk.Windows_Export_Ticket + " " + t.ID + " - " + t.Zakaznik + ", " + form.jazyk.Windows_Export_NaKteremJsiPracoval + " " + t.Datum.ToString("d.MM.yyyy") + ", " + form.jazyk.Windows_Export_Neukoncen);
}
}
}
}
}
//přepočet času na 8h
//celkový čas normálních ticketů (statní se neupravují)
Dictionary <string, decimal> casy = new Dictionary <string, decimal> {
{ "Pondělí", 0 }, { "Úterý", 0 }, { "Středa", 0 }, { "Čtvrtek", 0 }, { "Pátek", 0 }, { "Sobota", 0 }, { "Neděle", 0 }
};
foreach (ExportRow s in exportRadky)
{
if (s.Typ == ExportTyp.Normal)
{
casy["Pondělí"] += s.Radek["Pondělí"].Cas;
casy["Úterý"] += s.Radek["Úterý"].Cas;
casy["Středa"] += s.Radek["Středa"].Cas;
casy["Čtvrtek"] += s.Radek["Čtvrtek"].Cas;
casy["Pátek"] += s.Radek["Pátek"].Cas;
casy["Sobota"] += s.Radek["Sobota"].Cas;
casy["Neděle"] += s.Radek["Neděle"].Cas;
}
}
//výběr řádků, co se upraví čas
foreach (string cs in casy.Keys)
{
if (casy[cs] == 8 || casy[cs] == 0)
{
continue;
}
else if (casy[cs] < 8)
{
Dictionary <int, decimal> pridat = new Dictionary <int, decimal>();
for (int i = 0; i < exportRadky.Count; i++)
{
if (exportRadky[i].Typ == ExportTyp.Normal && exportRadky[i].Radek[cs].Cas > 0)
{
pridat.Add(i, exportRadky[i].Radek[cs].Cas);
}
}
pridat = pridat.OrderBy(x => x.Value).ToDictionary(x => x.Key, x => x.Value);
decimal zbyva = 8 - casy[cs];
decimal prumerNaRadek = Math.Ceiling((zbyva / pridat.Count) / 0.5m) * 0.5m;
//úprava času
int index = 0;
for (decimal d = zbyva; d > 0; d -= prumerNaRadek)
{
if (d - prumerNaRadek < 0)
{
prumerNaRadek = d;
}
pridat[pridat.Keys.ElementAt(index)] += prumerNaRadek;
index++;
zbyva -= prumerNaRadek;
}
foreach (int newI in pridat.Keys)
{
exportRadky[newI].Radek[cs].Cas = pridat[newI];
}
}
else if (casy[cs] > 8)
{
Dictionary <int, decimal> ubrat = new Dictionary <int, decimal>();
for (int i = 0; i < exportRadky.Count; i++)
{
if (exportRadky[i].Typ == ExportTyp.Normal && exportRadky[i].Radek[cs].Cas > 0.5m)
{
ubrat.Add(i, exportRadky[i].Radek[cs].Cas);
}
}
ubrat = ubrat.OrderByDescending(x => x.Value).ToDictionary(x => x.Key, x => x.Value);
decimal zbyva = casy[cs] - 8;
decimal prumerNaRadek = Math.Ceiling((zbyva / ubrat.Count) / 0.5m) * 0.5m;
//úprava času
int index = 0;
for (decimal d = zbyva; d > 0; d -= prumerNaRadek)
{
if (d - prumerNaRadek < 0)
{
prumerNaRadek = d;
}
ubrat[ubrat.Keys.ElementAt(index)] -= prumerNaRadek;
index++;
zbyva -= prumerNaRadek;
}
foreach (int newI in ubrat.Keys)
{
exportRadky[newI].Radek[cs].Cas = ubrat[newI];
}
}
}
//export do souboru
//pro excel
File.WriteAllBytes(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\Ticketnik\\tmp_export.xlsx", Properties.Resources.mytime_template);
Excel.XLWorkbook export = new Excel.XLWorkbook(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\Ticketnik\\tmp_export.xlsx");
Excel.IXLWorksheet exportSheet = export.Worksheet(1);
NumberFormatInfo nfi = new NumberFormatInfo();
nfi.NumberDecimalSeparator = ".";
int row = 2;
foreach (ExportRow s in exportRadky)
{
if (s.Terp != null)
{
//project
exportSheet.Cell(row, 1).Value = s.Terp;
//project name
exportSheet.Cell(row, 2).Value = "najdiSiSam";
//task
exportSheet.Cell(row, 3).SetValue(s.Task);
//task name
exportSheet.Cell(row, 4).Value = "TyVisCo";
//type
exportSheet.Cell(row, 5).Value = s.GetTyp();
//pondělí (čas, comment)
exportSheet.Cell(row, 6).Value = s.Radek["Pondělí"].Cas.ToString() == "0" ? "" : s.Radek["Pondělí"].Cas.ToString(nfi);
exportSheet.Cell(row, 7).Value = s.Radek["Pondělí"].Koment.Replace("\t", " ").Replace("\"", "");
//úterý
exportSheet.Cell(row, 10).Value = s.Radek["Úterý"].Cas.ToString() == "0" ? "" : s.Radek["Úterý"].Cas.ToString(nfi);
exportSheet.Cell(row, 11).Value = s.Radek["Úterý"].Koment.Replace("\t", " ").Replace("\"", "");
//středa
exportSheet.Cell(row, 14).Value = s.Radek["Středa"].Cas.ToString() == "0" ? "" : s.Radek["Středa"].Cas.ToString(nfi);
exportSheet.Cell(row, 15).Value = s.Radek["Středa"].Koment.Replace("\t", " ").Replace("\"", "");
//čtvrtek
exportSheet.Cell(row, 18).Value = s.Radek["Čtvrtek"].Cas.ToString() == "0" ? "" : s.Radek["Čtvrtek"].Cas.ToString(nfi);
exportSheet.Cell(row, 19).Value = s.Radek["Čtvrtek"].Koment.Replace("\t", " ").Replace("\"", "");
//pátek
exportSheet.Cell(row, 22).Value = s.Radek["Pátek"].Cas.ToString() == "0" ? "" : s.Radek["Pátek"].Cas.ToString(nfi);
exportSheet.Cell(row, 23).Value = s.Radek["Pátek"].Koment.Replace("\t", " ").Replace("\"", "");
//sobota
exportSheet.Cell(row, 26).Value = s.Radek["Sobota"].Cas.ToString() == "0" ? "" : s.Radek["Sobota"].Cas.ToString(nfi);
exportSheet.Cell(row, 27).Value = s.Radek["Sobota"].Koment.Replace("\t", " ").Replace("\"", "");
//neděle
exportSheet.Cell(row, 30).Value = s.Radek["Neděle"].Cas.ToString() == "0" ? "" : s.Radek["Neděle"].Cas.ToString(nfi);
exportSheet.Cell(row, 31).Value = s.Radek["Neděle"].Koment.Replace("\t", " ").Replace("\"", "");
row++;
}
}
export.Save();
export.Dispose();
form.infoBox.Text = "";
saveFileDialog1.AddExtension = true;
saveFileDialog1.DefaultExt = "xlsx";
saveFileDialog1.Filter = "Excel|*.xlsx";
saveFileDialog1.FileName = "MyTime Info.xlsx";
saveFileDialog1.ShowDialog();
}
private void btnExport_Click(object sender, EventArgs e)
{
#if ENG
string templateFile = "ROIN_RT_TESTER_REPORT_Eng.xlsx";
#elif CHS
string templateFile = "ROIN_RT_TESTER_REPORT_Chs.xlsx";
#else
string templateFile = "ROIN_RT_TESTER_REPORT_Cht.xlsx";
#endif
Util.WriteExcel(templateFile, this.txtProductName.Text + ".xlsx", (workbook) => {
ClosedXML.Excel.IXLWorksheet workSheet = null;
try {
workSheet = workbook.Worksheet(2);
workSheet.Cell(2, 2).Value = this.txtProductName.Text;
workSheet.Cell(3, 2).Value = _form.lblFileName.Text;
workSheet.Cell(4, 2).Value = _data.Rows[0]["TestTime"];
workSheet.Cell(5, 2).Value = _data.Rows[_data.Rows.Count - 1]["TestTime"];
workSheet.Cell(6, 2).Value = Util.GetProperty("TestMachine");
workSheet.Cell(9, 2).Value = Util.GetProperty("LabelTitle");
workSheet.Cell(10, 2).Value = Util.GetProperty("LabelProduct");
workSheet.Cell(11, 2).Value = Util.GetProperty("LabelRevision");
workSheet.Cell(12, 2).Value = Util.GetProperty("LabelPart");
workSheet.Cell(13, 2).Value = Util.GetProperty("LabelPlantVendor");
workSheet.Cell(14, 2).Value = Util.GetProperty("LabelTestedBy");
workSheet.Cell(2, 5).Value = _form.lblCondData.Text;
workSheet.Cell(2, 7).Value = _form.lblACHipotData.Text;
workSheet.Cell(2, 9).Value = _form.lblDCHipotData.Text;
workSheet.Cell(3, 9).Value = _form.lblDCHipotTimeData.Text;
workSheet.Cell(3, 5).Value = _form.lblInterTime2Data.Text;
workSheet.Cell(3, 7).Value = _form.lblACHipotTimeData.Text;
workSheet.Cell(4, 9).Value = _form.lblInstData.Text;
workSheet.Cell(4, 7).Value = _form.lblDCILeakageData.Text;
workSheet = workbook.Worksheet(3);
// 加入所有 DataTables 資料到 worksheet
workSheet.Cell("A18").InsertTable(_table);
//var header = workSheet.Range("A18:" + Convert.ToChar(_table.Columns.Count - 1 + Convert.ToInt32('A')) + "18");
//header.Style.Alignment.Horizontal = ClosedXML.Excel.XLAlignmentHorizontalValues.Center;
workSheet.Tables.First().ShowAutoFilter = false;
IEnumerable <DataRow> dataRow = _data.AsEnumerable();
for (int y = 0; y < dataRow.Count(); y++)
{
if (workSheet.Cell(19 + y, 5).Value.ToString() != "PASS")
{
workSheet.Cell(19 + y, 5).Style.Font.FontColor = XLColor.Red;
}
for (int x = 0; x < _netlistPins.Count(); x++)
{
string sVal = workSheet.Cell(19 + y, 9 + x).Value.ToString();
double iVal = 0;
// 僅驗證導通值是否大於 10, 規避元件判斷
if (_netlistPins[x].IndexOf(':') == -1 && double.TryParse(sVal, out iVal) && iVal > 10)
{
workSheet.Cell(19 + y, 9 + x).Style.Font.FontColor = XLColor.Red;
}
}
}
workSheet.Row(3).Cell(1).Value = _form.lblCustomerName.Text; //_data.Rows[0]["ProductName"];
workSheet.Row(3).Cell(2).Value = dataRow.Count();
workSheet.Row(3).Cell(3).Value = dataRow.Count(row => row.Field <String>("TestResult") == "PASS");
workSheet.Row(3).Cell(7).Value = dataRow.Count(row => row.Field <String>("TestResult") == "Open" && row.Field <String>("ResultDesc").IndexOf("Component Fail") == -1);
workSheet.Row(3).Cell(8).Value = dataRow.Count(row => row.Field <String>("TestResult") == "Short" && row.Field <String>("ResultDesc").IndexOf("Component Fail") == -1);
workSheet.Row(3).Cell(9).Value = dataRow.Count(row => row.Field <String>("TestResult") == "Cond");
workSheet.Row(3).Cell(10).Value = dataRow.Count(row => row.Field <String>("TestResult") == "Leak" || row.Field <String>("TestResult") == "Ovac");
workSheet.Row(3).Cell(11).Value = dataRow.Count(row => row.Field <String>("TestResult") == "Ins" || row.Field <String>("TestResult") == "Ovv" || row.Field <String>("TestResult") == "Ovc" || row.Field <String>("TestResult") == "Arc" || row.Field <String>("TestResult") == "DcOvv" || row.Field <String>("TestResult") == "DcOvc" || row.Field <String>("TestResult") == "DcArc" || row.Field <String>("TestResult") == "Dci");
workSheet.Row(3).Cell(12).Value = dataRow.Count(row => row.Field <String>("TestResult") == "Inter");
workSheet.Row(3).Cell(13).Value = dataRow.Count(row => row.Field <String>("ResultDesc").IndexOf("Component Fail") > -1);
} catch (Exception ee) {
Util.TraceInfo("Report Error: " + ee.Message);
}
});
this.DialogResult = DialogResult.None;
}
//---------------------------------------------------------/
// Method
//---------------------------------------------------------/
public void ExcelFile()
{
string tempFile = Folder.DefaultLocation() + @"\.~temp.xlsx";
Cursor.Current = Cursors.WaitCursor; // マウスカーソルを砂時計(Wait)
try
{
using (oWBook = new XLWorkbook(fileName))
{
if (tncA == null || tncA.Length == 0)
{
DMessage.DataNotExistence("中断します！");
return;
}
// 編集
oWSheet = oWBook.Worksheet(1); // シートを開く
readyExcelRows(tncA.Length, 4);
int sNo = 5;
for (int i = 0; i < tncA.Length; i++)
{
using (IXLRange SetRange = oWSheet.Range("A5:M5"))
// テンプレートデータ行コピー/ペースト
SetRange.CopyTo(oWSheet.Cell(sNo + i, 1));
if (i == 0)
{
officeName = tncA[i].OfficeName;
oWSheet.Cell(2, 3).Value = DateTime.Today;
oWSheet.Cell(3, 3).Value = officeName;
}
oWSheet.Cell(sNo + i, 1).Value = i + 1;
oWSheet.Cell(sNo + i, 2).Value = tncA[i].TaskCode;
oWSheet.Cell(sNo + i, 3).Value = tncA[i].TaskName;
oWSheet.Cell(sNo + i, 4).Value = tncA[i].VersionNo;
oWSheet.Cell(sNo + i, 5).Value = tncA[i].IssueDate;
oWSheet.Cell(sNo + i, 6).Value = tncA[i].SalesMName;
oWSheet.Cell(sNo + i, 7).Value = tncA[i].SalesMInputDate;
oWSheet.Cell(sNo + i, 8).Value = tncA[i].Approval;
oWSheet.Cell(sNo + i, 9).Value = tncA[i].ApprovalDate;
oWSheet.Cell(sNo + i, 10).Value = tncA[i].MakeOrder;
oWSheet.Cell(sNo + i, 11).Value = tncA[i].MakeOrderDate;
oWSheet.Cell(sNo + i, 12).Value = tncA[i].ConfirmAdm;
oWSheet.Cell(sNo + i, 13).Value = tncA[i].ConfirmDate;
if (i != 0)
{
oWSheet.Range(sNo + i, 1, sNo + i, 13).Style.Border.TopBorder = XLBorderStyleValues.Hair;
}
if (i == tncA.Length - 1)
{
oWSheet.Range(sNo + i, 1, sNo + i, 13).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
}
else
{
oWSheet.Range(sNo + i, 1, sNo + i, 13).Style.Border.BottomBorder = XLBorderStyleValues.Hair;
}
}
}
// 保存
oWBook.SaveAs(tempFile); // Excel保存
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
return;
}
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
System.Diagnostics.Process.Start("Excel.exe", tempFile); // 表示用Excel
// pdf出力にする場合は、上記 System.DiafnosticsのLineをコメントアウトし、下記DateTime以下のコメントを外す。
// pdf file 出力
//DateTime now = DateTime.Now;
//outputFile = System.IO.Path.GetDirectoryName( tempFile ) + @"\業務引継書承認未完了一覧表_" + officeName + "_" + "_" + now.ToString( "yyMMddHHmmss" );
//PublishExcelToPdf etp = new PublishExcelToPdf();
//etp.ExcelToPDF( tempFile, outputFile );
//if( File.Exists( tempFile ) ) File.Delete( tempFile );
}
private void editContractWorks(PublishData pd, DataGridView dgv)
{
//MessageBox.Show( "Excel書込み開始" );
oWSheet.Cell(3, 1).Value = pd.vYear + "年度";
oWSheet.Cell(3, 3).Value = "部署 ：" + pd.OfficeName + pd.DepartName;
oWSheet.Cell(5, 1).Value = "業務番号：" + pd.TaskCode;
oWSheet.Cell(5, 3).Value = "業務名 ：" + pd.TaskName;
oWSheet.Cell(6, 3).Value = "取引先名：" + pd.PartnerName;
oWSheet.Row(8).Height = 24;
readyExcelRows(dgv, 1, 18, 8);
const int SR = 9; // Excel Sheet Start Row No.
for (int i = 0; i < dgv.Rows.Count; i++)
{
oWSheet.Cell(SR + i, 1).Value = Convert.ToString(dgv.Rows[i].Cells["MName"].Value);
oWSheet.Cell(SR + i, 2).Value = Convert.ToString(dgv.Rows[i].Cells["MCode"].Value);
oWSheet.Cell(SR + i, 3).Value = Convert.ToString(dgv.Rows[i].Cells["Unit"].Value);
oWSheet.Cell(SR + i, 4).Value = Convert.ToString(dgv.Rows[i].Cells["Price"].Value);
oWSheet.Cell(SR + i, 5).Value = Convert.ToString(dgv.Rows[i].Cells["WorkSum"].Value);
oWSheet.Cell(SR + i, 6).Value = Convert.ToString(dgv.Rows[i].Cells["CostSum"].Value);
int k = 7;
for (int j = 0; j < 12; j++)
{
oWSheet.Cell(SR + i, k).Value =
DHandling.ToRegDecimal(Convert.ToString(dgv.Rows[i].Cells["Work" + j.ToString("00")].Value));
oWSheet.Cell(SR + i, k + 1).Value =
DHandling.ToRegDecimal(Convert.ToString(dgv.Rows[i].Cells["Cost" + j.ToString("00")].Value));
k += 2;
}
oWSheet.Row(SR + i).Height = 24;
}
}
// Wakamatsu
private void editEstimateTop(PublishData pd)
{
//MessageBox.Show("Excel書込み開始");
editEstimateCommon(pd);
oWSheet.Cell(11, 5).Value = pd.Note;
}