public void AddExportColumn(ReportColumn rptCol)
{
rptCol.Index = _columns.Count;
_columns.Add(rptCol);
_columnMap.Add(rptCol.Column, rptCol);
rptCol.Column.ExtendedProperties["export"] = true;
}
public ReportColumn AddExportColumn(DataColumn column, string alias)
{
ReportColumn rptCol = new ReportColumn(column);
rptCol.Name = alias;
AddExportColumn(rptCol);
return(rptCol);
}
protected override void AfterContentWritten()
{
ReportSourceProfile profile = this.SheetAdapters[0].ReportProfile;
//設定顯示格式
this.SheetAdapters[0].SetFormat(_columnHdrRow, profile.IndexOf("工作單號") + 1, "0");
if (_form.InspeceMode == InspeceMode.ByQcNo)
{
this.SheetAdapters[0].SetFormat(_columnHdrRow, profile.IndexOf("QCN") + 1, "@");
}
Range range;
range = this.SheetAdapters[0].GetUsedRange(_columnHdrRow);
this.SheetAdapters[0].SetBorder(range, true, true, true, true);
range = this.SheetAdapters[0].GetRange(_columnHdrRow + 1, 1);
range.Select();
this.Application.ActiveWindow.FreezePanes = true;
//抬頭
range = this.SheetAdapters[0].GetRange(1, 1, 1, this.SheetAdapters[0].UsedColumnsCount);
this.SheetAdapters[0].SetBorder(range, false, true, false, false);
range = this.SheetAdapters[0].GetRange(1, 1);
range.Font.Size = 18;
range.Font.Bold = true;
//欄位抬頭
if (_form.InspeceMode == InspeceMode.ByPn)
{
for (int i = 0; i < profile.Columns.Count; i++)
{
ReportColumn rptCol = profile.Columns[i];
if (rptCol.MergeSpan == 0)
{
range = this.SheetAdapters[0].GetRange(_columnHdrRow - 1, rptCol.Index + 1, _columnHdrRow, rptCol.Index + 1);
range.Merge(Missing);
}
else
{
range = this.SheetAdapters[0].GetRange(_columnHdrRow - 1, rptCol.Index + 1, _columnHdrRow - 1, rptCol.Index + rptCol.MergeSpan);
range.Merge(Missing);
range = this.SheetAdapters[0].GetRange(_columnHdrRow - 1, rptCol.Index + 1, _columnHdrRow - 1, rptCol.Index + 1);
range.Value2 = rptCol.MergeTitle;
range.Font.Bold = true;
i += rptCol.MergeSpan - 1;
}
}
range = this.SheetAdapters[0].GetRange(_columnHdrRow - 1, 1, _columnHdrRow, this.SheetAdapters[0].UsedColumnsCount);
range.Interior.Color = 13434828;
//range.AutoFilter(1, Missing, XlAutoFilterOperator.xlAnd, Missing, true);
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
this.SheetAdapters[0].SetBorder(range, true, true, true, true);
this.SheetAdapters[0].SetBorder(range, XlBordersIndex.xlInsideHorizontal);
this.SheetAdapters[0].SetBorder(range, XlBordersIndex.xlInsideVertical);
}
else
{
range = this.SheetAdapters[0].GetRange(_columnHdrRow, 1, _columnHdrRow, this.SheetAdapters[0].UsedColumnsCount);
range.Interior.Color = 13434828;
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
}
//產線欄
range = this.SheetAdapters[0].GetRange(_columnHdrRow + 1, profile.IndexOf("產線") + 1, this.SheetAdapters[0].UsedRowsCount, profile.IndexOf("產線") + 1);
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
//隱藏格線
this.Application.ActiveWindow.DisplayGridlines = false;
//顯示總筆數
range = this.SheetAdapters[0].GetRange(this.SheetAdapters[0].UsedRowsCount + 2, 1);
int count = _form.InspeceMode == InspeceMode.ByPn ? _groupTable.Rows.Count : _table.Rows.Count;
range.Value2 = "共 " + count + " 筆";
range.Font.Bold = true;
this.Sheets[0].UsedRange.Font.Name = "Arial";
//品質履歷欄
if (_table.Rows.Count > 0)
{
range = this.SheetAdapters[0].GetRange(_columnHdrRow + 1, profile.IndexOf("品質履歷") + 1, this.SheetAdapters[0].UsedRowsCount, profile.IndexOf("品質履歷") + 1);
range.Font.Color = 255;
//range.Font.Bold = true;
}
// Inser方法會在Excel2003失敗 所以採取迂迴方法達成(??)
//this.SheetAdapters[0].Worksheet.UsedRange.Cut(Missing);
//this.SheetAdapters[0].GetRange(1, this.SheetAdapters[0].Worksheet.UsedRange.Columns.Count + 2).Select();
//this.SheetAdapters[0].Worksheet.Paste(Missing, Missing);
//((Range)this.Application.Selection).Cut(Missing);
//this.SheetAdapters[0].GetRange(1, 2).Select();
//this.SheetAdapters[0].Worksheet.Paste(Missing, Missing);
//調整欄寬
range = this.SheetAdapters[0].GetRange(_form.InspeceMode == InspeceMode.ByPn ? _columnHdrRow - 1 : _columnHdrRow, 1, this.SheetAdapters[0].UsedRowsCount, this.SheetAdapters[0].UsedColumnsCount);
range.Columns.AutoFit();
if (_form.InspeceMode == InspeceMode.ByPn)
{
for (int i = 0; i < 5; i++)
{
this.SheetAdapters[0].GetRange(1, profile.IndexOf("Total") + 1 + i).ColumnWidth = 8.5;
}
range = this.SheetAdapters[0].GetRange(1, profile.IndexOf("品質履歷") + 1);
if ((double)range.ColumnWidth < 10)
{
range.ColumnWidth = 10;
}
}
//欄位自動篩選
//range = this.SheetAdapters[0].GetRange(_columnHdrRow, 2, _columnHdrRow, this.SheetAdapters[0].UsedColumnsCount).EntireRow;
range = this.SheetAdapters[0].GetRange(_columnHdrRow, 1, this.SheetAdapters[0].UsedRowsCount - 2, this.SheetAdapters[0].UsedColumnsCount);
range.AutoFilter(1, Missing, XlAutoFilterOperator.xlAnd, Missing, true);
//插入空白行
this.SheetAdapters[0].GetRange(1, 1).EntireColumn.Insert(XlInsertShiftDirection.xlShiftToRight, XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
////調整欄寬
//range = this.SheetAdapters[0].GetRange(_form.InspeceMode == InspeceMode.ByPn ? _columnHdrRow - 1 : _columnHdrRow, 1, this.SheetAdapters[0].UsedRowsCount, this.SheetAdapters[0].UsedColumnsCount);
//range.Columns.AutoFit();
//if (_form.InspeceMode == InspeceMode.ByPn)
//{
// for (int i = 0; i < 5; i++)
// this.SheetAdapters[0].GetRange(1, profile.IndexOf("Total") + 2 + i).ColumnWidth = 8.5;
// range = this.SheetAdapters[0].GetRange(1, profile.IndexOf("品質履歷") + 2);
// if ((double)range.ColumnWidth < 10)
// range.ColumnWidth = 10;
//}
////欄位自動篩選
////range = this.SheetAdapters[0].GetRange(_columnHdrRow, 2, _columnHdrRow, this.SheetAdapters[0].UsedColumnsCount).EntireRow;
//range = this.SheetAdapters[0].GetRange(_columnHdrRow, 1, this.SheetAdapters[0].UsedRowsCount - 2, this.SheetAdapters[0].UsedColumnsCount);
//range.AutoFilter(1, Missing, XlAutoFilterOperator.xlAnd, Missing, true);
this.SheetAdapters[0].GetRange(1, 1).EntireColumn.ColumnWidth = 4;
this.SheetAdapters[0].GetRange(_columnHdrRow, 1).EntireRow.RowHeight = 40;
if (_form.OutputStatistic)
{
profile = this.SheetAdapters[1].ReportProfile;
//抬頭
range = this.SheetAdapters[1].GetRange(1, 1).EntireRow;
range.Font.Size = 18;
range.Font.Bold = true;
//欄位抬頭
range = this.SheetAdapters[1].GetRange(_columnHdrRow, 1, _columnHdrRow, this.SheetAdapters[1].UsedColumnsCount);
range.Interior.Color = 16777164;
//range.AutoFilter(1, Missing, XlAutoFilterOperator.xlAnd, Missing, true);
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
//產線欄
range = this.SheetAdapters[1].GetRange(_columnHdrRow + 1, profile.IndexOf("產線") + 1, this.SheetAdapters[1].UsedRowsCount, profile.IndexOf("產線") + 1);
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
//字體顏色
range = this.SheetAdapters[1].GetRange(_columnHdrRow, 1, this.SheetAdapters[1].UsedRowsCount - 1, this.SheetAdapters[1].UsedColumnsCount);
range.Font.Color = 16711680;
//總和行
if (_table.Rows.Count > 0)
{
range = this.SheetAdapters[1].GetRange(this.SheetAdapters[1].UsedRowsCount, 1, this.SheetAdapters[1].UsedRowsCount, this.SheetAdapters[1].UsedColumnsCount);
range.Font.Bold = true;
range.Font.Color = 255;
range.Interior.Color = 65535;
}
//資料格式
this.SheetAdapters[1].SetFormat(_columnHdrRow, profile.IndexOf("合格率") + 1, "0.00%");
this.Sheets[1].UsedRange.Font.Name = "Arial";
// Inser方法會在Excel2003失敗 所以採取迂迴方法達成
//this.SheetAdapters[1].Worksheet.Select(Missing);
//this.SheetAdapters[1].Worksheet.UsedRange.Cut(Missing);
//this.SheetAdapters[1].GetRange(1, this.SheetAdapters[1].Worksheet.UsedRange.Columns.Count + 2).Select();
//this.SheetAdapters[1].Worksheet.Paste(Missing, Missing);
//((Range)this.Application.Selection).Cut(Missing);
//this.SheetAdapters[1].GetRange(1, 2).Select();
//this.SheetAdapters[1].Worksheet.Paste(Missing, Missing);
//欄位自動篩選
range = this.SheetAdapters[1].GetRange(_columnHdrRow, 1, this.SheetAdapters[1].UsedRowsCount - 1, this.SheetAdapters[1].UsedColumnsCount);
//range = this.SheetAdapters[1].GetRange(_columnHdrRow, 2, _columnHdrRow, this.SheetAdapters[1].UsedColumnsCount);
range.AutoFilter(1, Missing, XlAutoFilterOperator.xlAnd, Missing, true);
//調整欄寬
range = this.SheetAdapters[1].GetRange(_columnHdrRow, 1, this.SheetAdapters[1].UsedRowsCount, this.SheetAdapters[1].UsedColumnsCount);
range.Columns.AutoFit();
this.SheetAdapters[1].GetRange(1, 1).EntireColumn.Insert(XlInsertShiftDirection.xlShiftToRight, XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
this.SheetAdapters[1].GetRange(1, 1).EntireColumn.ColumnWidth = 4;
this.SheetAdapters[1].Worksheet.Select(Missing);
this.SheetAdapters[1].Worksheet.Name = "Statistic";
this.Application.ActiveWindow.DisplayGridlines = false;
}
this.SheetAdapters[0].Worksheet.Select(Missing);
this.SheetAdapters[0].Worksheet.Name = "Report";
base.AfterContentWritten();
}
public void AddExportColumn(ReportColumn rptCol)
{
rptCol.Index = _columns.Count;
_columns.Add(rptCol);
_columnMap.Add(rptCol.Column, rptCol);
rptCol.Column.ExtendedProperties["export"] = true;
}
public ReportColumn AddExportColumn(DataColumn column, string alias)
{
ReportColumn rptCol = new ReportColumn(column);
rptCol.Name = alias;
AddExportColumn(rptCol);
return rptCol;
}