//---------------------------------------------------------------------- // 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); }