// Wakamatsu 20170313
/// <summary>
/// フォーマット等設定
/// </summary>
/// <param name="exLine">基準列</param>
private void FromatCopy(int exLine)
{
// 不足行追加
int sheetRowsCount = oWSheet.LastRowUsed().RowNumber(); // Excel最終行位置
sheetRowsCount -= exLine; // タイトル行と合計行を行数から除く
using (IXLRange SetRange = oWSheet.Range("A6:O6"))
{
for (int i = 0; i < sheetRowsCount - 1; i++)
{
// テンプレートデータ行コピー/ペースト
SetRange.CopyTo(oWSheet.Cell(exLine + 1 + i, 1));
// 連番再設定
oWSheet.Cell(exLine + 1 + i, 1).Value = i + 2;
oWSheet.Range(exLine + 1 + i, 1, exLine + 1 + i, 15).Style.Border.TopBorder = XLBorderStyleValues.Hair;
oWSheet.Range(exLine + 1 + i, 1, exLine + 1 + i, 15).Style.Border.BottomBorder = XLBorderStyleValues.Hair;
}
}
}
//---------------------------------------------------------/
// 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 );
}
//---------------------------------------------------------/
// 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);
}
}
// 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);
}