//----------------------------------------------------------------------
// SubRoutine
//----------------------------------------------------------------------
/// <summary>
/// データ件数と現在のエクセルの行数を比較し、エクセルの行数が少ない場合は
/// エクセルに行を追加する。
/// 比較の差異は列ヘッダーの行数、合計行の行数は除外する。
/// </summary>
/// <param name="lineCount"></param>
/// <param name="exLine"></param>
private void readyExcelRows(int lineCount, int exLine)
{
// 不足行追加
if (1 < lineCount)
{
var rowCount = oWSheet.Row(6).InsertRowsBelow(lineCount - 1);
oWSheet.Rows("6:" + (6 + lineCount - 1 - 1)).Height = oWSheet.Row(5).Height;
}
oWSheet.Row(6 + lineCount - 1).Delete();
}
private void ImpostaValoreRiga(
int riga, int colonna, object valore)
{
if (_wb.Range("Righe").LastRow().RowNumber() - _wb.Range("Righe").FirstRow().RowNumber() == riga)
{
IXLRow row1 = _ws.Row(riga + _wb.Range("Righe").FirstRow().RowNumber());
row1.InsertRowsAbove(1);
}
_wb.Range("Righe").Range(riga, colonna, riga, colonna).Value = valore;
}
//----------------------------------------------------------------------
// SubRoutine
//----------------------------------------------------------------------
/// <summary>
/// データ件数と現在のエクセルの行数を比較し、エクセルの行数が少ない場合は
/// エクセルに行を追加する。
/// 比較の差異は列ヘッダーの行数、合計行の行数は除外する。
/// </summary>
/// <param name="lineCount"></param>
/// <param name="exLine"></param>
private void readyExcelRows(int lineCount, int exLine)
{
// 不足行追加
// Wakamatsu 20170315
//int sheetRowsCount = oWSheet.LastRowUsed().RowNumber(); // Excel最終行位置
//sheetRowsCount -= exLine; // タイトル行と合計行を行数から除く
//if (sheetRowsCount < lineCount)
if (1 < lineCount)
// Wakamatsu 20170315
{
// Wakamatsu 20170315
//var rowCount = oWSheet.Row(6).InsertRowsBelow(lineCount - sheetRowsCount);
var rowCount = oWSheet.Row(6).InsertRowsBelow(lineCount - 1);
oWSheet.Rows("6:" + (6 + lineCount - 1 - 1)).Height = oWSheet.Row(5).Height;
// Wakamatsu 20170315
}
// Wakamatsu 20170315
oWSheet.Row(6 + lineCount - 1).Delete();
}
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;
}
}
/// <summary>
/// データ件数と現在のエクセルの行数を比較し、エクセルの行数が少ない場合は
/// エクセルに行を追加する。
/// 比較の差異は列ヘッダーの行数、合計行の行数は除外する。
/// </summary>
/// <param name="lineCount"></param>
/// <param name="exLine"></param>
private void readyExcelRows(int lineCount, int exLine)
{
// 不足行追加
int sheetRowsCount = oWSheet.LastRowUsed().RowNumber(); // Excel最終行位置
sheetRowsCount -= exLine; // タイトル行と合計行を行数から除く
if (sheetRowsCount < lineCount)
{
// Wakamatsu 20170313
//var rowCount = oWSheet.Row(6).InsertRowsBelow(lineCount - sheetRowsCount);
var rowCount = oWSheet.Row(exLine + 1).InsertRowsBelow(lineCount - sheetRowsCount);
oWSheet.Rows((exLine + 1) + ":" + (exLine + 1 + lineCount - sheetRowsCount)).Height = oWSheet.Row(exLine + 1).Height;
// Wakamatsu 20170313
}
}
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;
}
// Wakamatsu 20170302
/// <summary>
/// 業務元帳出力
/// </summary>
/// <param name="pd">出力データ</param>
/// <param name="dgv">出力対象データグリッドビュー</param>
private void editTaskSummary(PublishData pd, DataGridView dgv)
{
oWSheet.Cell(3, 2).Value = pd.OfficeName + " " + pd.Department; // 部門 部署
oWSheet.Cell(3, 5).Value = pd.OrderStartDate.ToString("yyyy/MM/dd") +
"～" + pd.OrderEndDate.ToString("yyyy/MM/dd"); // 表示期間
oWSheet.Cell(5, 2).Value = pd.TaskCode; // 業務番号
oWSheet.Cell(5, 5).Value = pd.TaskName; // 業務名
oWSheet.Cell(7, 5).Value = pd.PartnerName; // 取引先名
oWSheet.Cell(7, 7).Value = pd.Note; // 工期
int WriteOffset = 12; // 書出し位置オフセット
bool EmptyFlag = true; // 空データグリッドビューフラグ
for (int i = 0; i < 8; i++)
{
// Wakamatu 20170307
//if( Convert.ToString( dgv.Rows[i].Cells[i].Value ) != "" )
if (Convert.ToString(dgv.Rows[0].Cells[i].Value) != "")
{
EmptyFlag = false;
}
}
if (EmptyFlag == false)
{
for (int i = 0; i < dgv.Rows.Count; i++)
{
using (IXLRange SetRange = oWSheet.Range("A11:I11"))
// テンプレートデータ行コピー/ペースト
SetRange.CopyTo(oWSheet.Cell(i + WriteOffset, 1));
if (Convert.ToString(dgv.Rows[i].Cells["ReportDate"].Value) != "") // 月日
{
oWSheet.Cell(i + WriteOffset, 1).Value = Convert.ToString(dgv.Rows[i].Cells["ReportDate"].Value);
oWSheet.Range(i + WriteOffset, 1, i + WriteOffset, 9).Style.Border.TopBorder = XLBorderStyleValues.Hair;
}
if (Convert.ToString(dgv.Rows[i].Cells["SlipNo"].Value) != "") // 伝票No.
{
oWSheet.Cell(i + WriteOffset, 2).Value = Convert.ToString(dgv.Rows[i].Cells["SlipNo"].Value);
oWSheet.Cell(i + WriteOffset, 2).Style.Border.TopBorder = XLBorderStyleValues.Hair;
oWSheet.Cell(i + WriteOffset, 9).Style.Border.TopBorder = XLBorderStyleValues.Hair;
}
if (Convert.ToString(dgv.Rows[i].Cells["ItemCode"].Value) != "") // コード
{
oWSheet.Cell(i + WriteOffset, 3).Value = Convert.ToString(dgv.Rows[i].Cells["ItemCode"].Value);
}
else
{
oWSheet.Cell(i + WriteOffset, 2).Style.Border.RightBorder = XLBorderStyleValues.None;
oWSheet.Cell(i + WriteOffset, 3).Style.Border.LeftBorder = XLBorderStyleValues.None;
oWSheet.Cell(i + WriteOffset, 3).Style.Border.RightBorder = XLBorderStyleValues.None;
oWSheet.Cell(i + WriteOffset, 4).Style.Border.LeftBorder = XLBorderStyleValues.None;
oWSheet.Cell(i + WriteOffset, 5).Style.Border.RightBorder = XLBorderStyleValues.None;
oWSheet.Cell(i + WriteOffset, 6).Style.Border.LeftBorder = XLBorderStyleValues.None;
oWSheet.Cell(i + WriteOffset, 7).Style.Border.RightBorder = XLBorderStyleValues.None;
oWSheet.Cell(i + WriteOffset, 8).Style.Border.LeftBorder = XLBorderStyleValues.None;
}
oWSheet.Range(i + WriteOffset, 3, i + WriteOffset, 8).Style.Border.TopBorder = XLBorderStyleValues.Hair;
if (Convert.ToString(dgv.Rows[i].Cells["Item"].Value) != "") // 名称
{
oWSheet.Cell(i + WriteOffset, 4).Value = Convert.ToString(dgv.Rows[i].Cells["Item"].Value);
}
if (Convert.ToString(dgv.Rows[i].Cells["Quantity"].Value) != "") // 数量
{
oWSheet.Cell(i + WriteOffset, 6).Value = Convert.ToString(dgv.Rows[i].Cells["Quantity"].Value);
}
if (Convert.ToString(dgv.Rows[i].Cells["UnitPrice"].Value) != "") // 単価
{
oWSheet.Cell(i + WriteOffset, 7).Value = Convert.ToString(dgv.Rows[i].Cells["UnitPrice"].Value);
}
if (Convert.ToString(dgv.Rows[i].Cells["Cost"].Value) != "") // 金額
{
oWSheet.Cell(i + WriteOffset, 8).Value = Convert.ToString(dgv.Rows[i].Cells["Cost"].Value);
}
if (Convert.ToString(dgv.Rows[i].Cells["Balance"].Value) != "") // 累計
{
oWSheet.Cell(i + WriteOffset, 9).Value = Convert.ToString(dgv.Rows[i].Cells["Balance"].Value);
}
// 行の高さ設定
oWSheet.Row(i + WriteOffset).Height = 21.00;
if (i == dgv.Rows.Count - 1)
{
// 最終データのみ罫線の設定
oWSheet.Range(i + WriteOffset, 1, i + WriteOffset, 9).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
}
}
}
// 先頭データのみ罫線の設定
oWSheet.Range(WriteOffset, 1, WriteOffset, 9).Style.Border.TopBorder = XLBorderStyleValues.Thin;
// テンプレート行削除
oWSheet.Row(11).Delete();
oWSheet.PageSetup.FitToPages(1, 0);
}