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