static void Main(string[] args)
{
Excel.Application app = new Excel.Application();
app.Visible = true;
app.Workbooks.Add("");
app.Workbooks.Add(@"c:\MyWork\WorkBook1.xls");
app.Workbooks.Add(@"c:\MyWork\WorkBook2.xls");
for (int i = 2; i <= app.Workbooks.Count; i++)
{
int count = app.Workbooks[i].Worksheets.Count;
app.Workbooks[i].Activate();
for (int j = 1; j <= count; j++)
{
Excel._Worksheet ws = (Excel._Worksheet)app.Workbooks[i].Worksheets[j];
ws.Select(Type.Missing);
ws.Cells.Select();
Excel.Range sel = (Excel.Range)app.Selection;
sel.Copy(Type.Missing);
Excel._Worksheet sheet = (Excel._Worksheet)app.Workbooks[1].Worksheets.Add(
Type.Missing, Type.Missing, Type.Missing, Type.Missing
);
sheet.Paste(Type.Missing, Type.Missing);
}
}
}
/// <param name="шаблон">например: "работыПоСекциям.xls"</param>
/// <param name="filename">имя файла отчета. например: "Исполнители.xls"</param>
public bool Init(string шаблон, string filename, bool visible = false, string otchetDir = @"C:\_provodnikFTP")
{
try
{
this.otchetDir = otchetDir;
path = filename;
//string path = string.Format(@"{2} {1} {0} - Исполнители.xls", r.remType, r.poezd, r.endDate.Value.ToString("yyyy.MM.dd"));
try
{
myExcel = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
foreach (Excel._Workbook b in (myBooks = (Excel.Workbooks)myExcel.Workbooks))
{
if (b.Name == path)
{
b.Close(false); break;
}
}
}
catch { }
if (myExcel == null || myExcel != null && myBooks.Count > 0)
{
myExcel = new Excel.Application();
}
myExcel.Visible = false;
myBooks = (Excel.Workbooks)myExcel.Workbooks;
myExcel.Visible = visible; //myExcel.Visible = true;
myExcel.DisplayAlerts = false;
// var pp = Environment.CurrentDirectory;
// var pp2 = System.Reflection.Assembly.GetExecutingAssembly().Location;
myBook = (Excel._Workbook)(myBooks.Open(string.Format("{0}\\_шаблоны\\" + шаблон, AppDomain.CurrentDomain.BaseDirectory)));
mySheets = (Excel.Sheets)myBook.Worksheets;
mySheet = (Excel._Worksheet)(mySheets.get_Item(1)); mySheet.Select();
cell = mySheet.Cells;
return(true);
}
catch (Exception e)
{
System.Windows.MessageBox.Show("При создании отчета возникла ошибка:\n" + e.Message, "Ошибка Excel", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
return(false);
}
}
private void generateLabelSheet(_Workbook workbook)
{
Microsoft.Office.Interop.Excel._Worksheet labelSheet = null;
Microsoft.Office.Interop.Excel._Worksheet calcSheet = null;
System.Data.DataTable labelDt = getLabelDataFromView();
if (labelDt.Rows.Count > 0)
{
labelDt.Columns.Add("OTH_REF");
labelDt.Columns.Add("OLD_ITEM");
labelDt.Columns.Add("BOXCODE");
labelDt.Columns.Add("MOGNBR1");
labelDt.Columns.Add("ENGDES");
labelDt.Columns.Add("SPNDES");
labelDt.Columns.Add("GERDES");
labelDt.Columns.Add("LBLCODE");
labelDt.Columns.Add("LBLTYPE");
labelDt.Columns.Add("LBLSTYE");
labelDt.Columns.Add("OENBR1");
labelDt.Columns.Add("OENBR2");
labelDt.Columns.Add("OENBR3");
labelDt.Columns.Add("OENBR4");
labelDt.Columns.Add("OENBR5");
labelDt.Columns.Add("OENBR6");
labelDt.Columns.Add("OENBR7");
labelDt.Columns.Add("OENBR8");
labelDt.Columns.Add("OENBR9");
labelDt.Columns.Add("OENBR10");
labelDt.Columns.Add("MOGNBR2");
labelDt.Columns.Add("TPFNBR1");
labelDt.Columns.Add("TPFNBR2");
labelDt.Columns.Add("TRWNBR1");
labelDt.Columns.Add("TRWNBR2");
labelDt.Columns.Add("DANNBR1");
labelDt.Columns.Add("DANNBR2");
labelDt.Columns.Add("MCQNBR1");
labelDt.Columns.Add("MCQNBR2");
labelDt.Columns.Add("DESC1");
labelDt.Columns.Add("DESC2");
labelDt.Columns.Add("DESC3");
labelDt.Columns.Add("DESC4");
labelDt.Columns.Add("DESC5");
labelDt.Columns.Add("DESC6");
labelDt.Columns.Add("DESC7");
labelDt.Columns.Add("DESC8");
labelDt.Columns.Add("DESC9");
labelDt.Columns.Add("DESC10");
labelDt.Columns.Add("QRCODE");
labelDt.Columns.Add("HBSQRCODE");
var rfcGetPackingFM = rfcRepo.CreateFunction(getLabelData);
for (int i = 0; i <= labelDt.Rows.Count - 1; i++)
{
var nbr = labelDt.Rows[i]["NBR"];
var posnr = labelDt.Rows[i]["POSNR"].ToString();
rfcGetPackingFM.SetValue("P_VBELN", nbr); //訂單號碼
rfcGetPackingFM.SetValue("P_POSNR", posnr); //訂單項次
rfcGetPackingFM.Invoke(rfcDest);
IRfcTable ITAB = rfcGetPackingFM.GetTable("ITAB");
if (ITAB.CurrentIndex == 0)
{
//只取第一列
ITAB.CurrentIndex = 0;
labelDt.Rows[i]["OTH_REF"] = ITAB.GetString("KDMAT");
labelDt.Rows[i]["OLD_ITEM"] = ITAB.GetString("BISMT");
labelDt.Rows[i]["OENBR1"] = ITAB.GetString("OENBR1");
labelDt.Rows[i]["OENBR2"] = ITAB.GetString("OENBR2");
labelDt.Rows[i]["OENBR3"] = ITAB.GetString("OENBR3");
labelDt.Rows[i]["OENBR4"] = ITAB.GetString("OENBR4");
labelDt.Rows[i]["OENBR5"] = ITAB.GetString("OENBR5");
labelDt.Rows[i]["OENBR6"] = ITAB.GetString("OENBR6");
labelDt.Rows[i]["OENBR7"] = ITAB.GetString("OENBR7");
labelDt.Rows[i]["OENBR8"] = ITAB.GetString("OENBR8");
labelDt.Rows[i]["OENBR9"] = ITAB.GetString("OENBR9");
labelDt.Rows[i]["OENBR10"] = ITAB.GetString("OENBR10");
labelDt.Rows[i]["MOGNBR1"] = ITAB.GetString("MOGNBR1");
labelDt.Rows[i]["MOGNBR2"] = ITAB.GetString("MOGNBR2");
labelDt.Rows[i]["TPFNBR1"] = ITAB.GetString("TPFNBR1");
labelDt.Rows[i]["TPFNBR2"] = ITAB.GetString("TPFNBR2");
labelDt.Rows[i]["TRWNBR1"] = ITAB.GetString("TRWNBR1");
labelDt.Rows[i]["TRWNBR2"] = ITAB.GetString("TRWNBR2");
labelDt.Rows[i]["DANNBR1"] = ITAB.GetString("DANNBR1");
labelDt.Rows[i]["DANNBR2"] = ITAB.GetString("DANNBR2");
labelDt.Rows[i]["MCQNBR1"] = ITAB.GetString("MCQNBR1");
labelDt.Rows[i]["MCQNBR2"] = ITAB.GetString("MCQNBR2");
labelDt.Rows[i]["ENGDES"] = ITAB.GetString("ENGDES");
labelDt.Rows[i]["SPNDES"] = ITAB.GetString("SPNDES");
labelDt.Rows[i]["GERDES"] = ITAB.GetString("GERDES");
labelDt.Rows[i]["DESC1"] = ITAB.GetString("DESC1");
labelDt.Rows[i]["DESC2"] = ITAB.GetString("DESC2");
labelDt.Rows[i]["DESC3"] = ITAB.GetString("DESC3");
labelDt.Rows[i]["DESC4"] = ITAB.GetString("DESC4");
labelDt.Rows[i]["DESC5"] = ITAB.GetString("DESC5");
labelDt.Rows[i]["DESC6"] = ITAB.GetString("DESC6");
labelDt.Rows[i]["DESC7"] = ITAB.GetString("DESC7");
labelDt.Rows[i]["DESC8"] = ITAB.GetString("DESC8");
labelDt.Rows[i]["DESC9"] = ITAB.GetString("DESC9");
labelDt.Rows[i]["DESC10"] = ITAB.GetString("DESC10");
labelDt.Rows[i]["BOXCODE"] = ITAB.GetString("BOXCODE");
labelDt.Rows[i]["LBLCODE"] = ITAB.GetString("LBLCODE");
labelDt.Rows[i]["LBLTYPE"] = ITAB.GetString("LBLTYPE");
labelDt.Rows[i]["LBLSTYE"] = ITAB.GetString("LBLSTYE");
if ((ITAB.GetString("QRCODE") == null) || (ITAB.GetString("QRCODE") == "") || (ITAB.GetString("QRCODE") == " "))
{
labelDt.Rows[i]["QRCODE"] = "";
labelDt.Rows[i]["HBSQRCODE"] = "";
}
else
{
labelDt.Rows[i]["QRCODE"] = ITAB.GetString("QRCODE");
labelDt.Rows[i]["HBSQRCODE"] = labelDt.Rows[i]["QRCODE"].ToString() + labelDt.Rows[i]["CUS_ITEM"].ToString();
}
}
}
BindingSource bs = new BindingSource();
bs.DataSource = labelDt.DefaultView;
dgvPacking.DataSource = null;
dgvPacking.DataSource = bs;
foreach (DataGridViewColumn column in dgvPacking.Columns)
{
//表頭選擇
column.HeaderCell = new DataGridViewAutoFilterColumnHeaderCell(column.HeaderCell);
//禁止排序
column.SortMode = DataGridViewColumnSortMode.NotSortable;
}
labelSheet = workbook.Sheets["工作表2"];
labelSheet.Name = "標籤明細";
labelSheet.Select();
//計算 dataGrid 的欄列數
string maxCols = GetStandardExcelColumnName(dgvPacking.Columns.Count);
string maxRows = dgvPacking.Rows.Count.ToString();
//將 datagrid 填入 excel 中
//填上標題列
for (int i = 1; i < dgvPacking.Columns.Count + 1; i++)
{
labelSheet.Cells[1, i] = dgvPacking.Columns[i - 1].HeaderText;
}
//宣告 datagrid 沒有標題列
dgvPacking.RowHeadersVisible = false;
//將資料從 datagrid 貼到 object
dgvPacking.SelectAll();
DataObject dataObj = dgvPacking.GetClipboardContent();
if (dataObj != null)
{
Clipboard.SetDataObject(dataObj);
}
//將 object 傳給 workbook
labelSheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(1);
//將資料貼入第二列 (第一列是標題列)
labelSheet = workbook.ActiveSheet;
//資料格式化
labelSheet.get_Range("A1", "BZ" + maxRows).NumberFormat = "@";
labelSheet.Application.Goto(labelSheet.Range["A2"], true);
//從剪貼薄貼上
labelSheet.PasteSpecial(Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
//將 worksheet 複製一份到 calcsheet
labelSheet.Copy(Type.Missing, workbook.Sheets[workbook.Sheets.Count]);
// 複製的 sheet 更名
workbook.Sheets[workbook.Sheets.Count].Name = "計算張數";
//選取 calcSheet
calcSheet = workbook.Sheets[workbook.Sheets.Count];
//刪掉不要的部份
calcSheet.Range["A1:I" + maxRows].Delete();
calcSheet.Range["B1:" + maxCols + maxRows].Delete();
//去除重覆料號
calcSheet.Range["A1:A" + maxRows].RemoveDuplicates(1);
calcSheet.UsedRange.NumberFormat = "General";
calcSheet.Range["A1"].Value = "料號";
calcSheet.Range["B1"].Value = "張數";
//求有值的最大列數
int calcsheetMaxRows = calcSheet.Range["A1"].Offset[calcSheet.Rows.Count - 1, 0].End[Microsoft.Office.Interop.Excel.XlDirection.xlUp].Row;
calcSheet.Range["B2"].Value = "=COUNTIF(標籤明細!J:J,A2)";
//將公式往下複製
calcSheet.Range["B2"].Copy(calcSheet.Range["B3:B" + calcsheetMaxRows]);
//刪掉多的工作表
//workbook.Sheets["工作表2"].Delete();
}
else
{
MessageBox.Show("標籤明細沒有資料", "錯誤");
}
}
public void AddCharts(string reportFile)
{
var excel = new Excel.Application()
{
DisplayAlerts = false
};
excel.Workbooks.Open(reportFile);
Excel._Worksheet workSheet = (Excel.Worksheet)excel.ActiveSheet;
var sheetNumber = 1;
var chartPositionNumber = 2;
foreach (Excel.Worksheet sheet in excel.Worksheets)
{
if (sheet.Name != "Charts")
{
// Set the datasheet for the source of the data
Excel.Worksheet dataSheet = excel.Worksheets[sheetNumber];
// Open the chart sheet to save the charts
workSheet = excel.ActiveWorkbook.Sheets["Charts"];
workSheet.Select();
// Get column & row length
var colomn = dataSheet.UsedRange.Columns.Count;
var rows = dataSheet.UsedRange.Rows.Count - 1;
// Chart settings and stuff
Excel.ChartObjects xlCharts = (Excel.ChartObjects)workSheet.ChartObjects(Type.Missing);
Excel.ChartObject runChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart runChartPage = runChart.Chart;
runChartPage.ChartType = Excel.XlChartType.xlLine;
// set ChartStyle based on Office version
var chartStyle = 301;
if (Type.GetTypeFromProgID("Excel.Application.14") != null)
{
chartStyle = 2;
}
runChartPage.HasTitle = true;
runChartPage.HasLegend = true;
runChartPage.ChartTitle.Text = sheet.Name;
runChartPage.ChartStyle = chartStyle;
// Position of chart
var runChartPosition = "B" + chartPositionNumber;
Excel.Range runChartPlacementRange = workSheet.get_Range(runChartPosition, runChartPosition);
runChart.Top = runChartPlacementRange.Top;
runChart.Left = runChartPlacementRange.Left;
chartPositionNumber = chartPositionNumber + 21;
// Size of Chart
runChart.Width = 500;
runChart.Height = 250;
Excel.SeriesCollection runSeriesCollection = runChartPage.SeriesCollection();
// Create run line chart
for (int i = 2; i <= (colomn); i++)
{
Excel.Series runSeries = runSeriesCollection.NewSeries();
runSeries.Name = dataSheet.Cells[1, i].Value;
// set correct range for chart data
var ia = i;
// Time range
var xValuesBegin = ParseColumnName(1) + "2";
var xValuesEnd = ParseColumnName(1) + (rows.ToString());
var valuesBegin = ParseColumnName(ia) + (2).ToString();
var valuesEnd = ParseColumnName(ia) + (rows + 1).ToString();
runSeries.XValues = dataSheet.get_Range(xValuesBegin, xValuesEnd);
runSeries.Values = dataSheet.get_Range(valuesBegin, valuesEnd);
}
}
sheetNumber++;
}
var error = new ErrorHandler();
try
{
// Save excel sheet
workSheet.SaveAs(reportFile);
}
catch (Exception)
{
excel.Quit();
error.Exit(93);
}
// Close sheet
excel.Quit();
}
