private void WriteExcel(Dictionary <string, string> dict, string filename, int col = 2) { try { oXL = new Microsoft.Office.Interop.Excel.Application(); oXL.Visible = true; oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add("")); oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet; int row = 1; foreach (var key in dict.Keys) { oSheet.Cells[row, 1] = key; string keyVal; if (dict.TryGetValue(key, out keyVal)) { oSheet.Cells[row, col] = keyVal; } row++; } oWB.SaveAs("C:\\Users\\mahmad\\Desktop\\" + filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); oWB.Close(); oXL.Quit(); } catch { MessageBox.Show("Something went wrong"); } }
private void UpdateExcel(decimal row, string partno, string model, string pono, string year, string seqno, string sn) { Microsoft.Office.Interop.Excel.Application oXL = null; Microsoft.Office.Interop.Excel._Workbook oWB = null; Microsoft.Office.Interop.Excel._Worksheet oSheet = null; try { oXL = new Microsoft.Office.Interop.Excel.Application(); oWB = oXL.Workbooks.Open(AppContext.BaseDirectory + @"\2021.xlsx"); oSheet = String.IsNullOrEmpty("Sheet1") ? (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet : (Microsoft.Office.Interop.Excel._Worksheet)oWB.Worksheets["Sheet1"]; oSheet.Cells[row, 1] = partno; oSheet.Cells[row, 2] = partno; oSheet.Cells[row, 3] = partno; oSheet.Cells[row, 4] = partno; oSheet.Cells[row, 5] = partno; oSheet.Cells[row, 6] = partno; oWB.Save(); MessageBox.Show("Done!"); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { if (oWB != null) { oWB.Close(); } } }
public void createDDetailsReport(List <NodeRecord> nodeRecordsList) { try { //Start Excel and get Application object. oXL1 = new Microsoft.Office.Interop.Excel.Application(); //oXL1.Visible = true; //Get a new workbook. oWB1 = (Microsoft.Office.Interop.Excel._Workbook)(oXL1.Workbooks.Add("")); oSheet1 = (Microsoft.Office.Interop.Excel._Worksheet)oWB1.ActiveSheet; oSheet1.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait; int rows = 1; for (int i = 0; i < nodeRecordsList.Count(); i++) { oSheet1.get_Range("A" + rows, "B" + rows).Merge(); oSheet1.Cells[rows, 1].Value2 = nodeRecordsList[i].name; oSheet1.Cells[rows, 1].Font.Bold = true; oSheet1.Cells[rows++, 1].Font.Size = 15; for (int j = 0; j < nodeRecordsList[i].record.Count(); j++) { oSheet1.Cells[rows, 1].Value2 = nodeRecordsList[i].record[j].status; if (nodeRecordsList[i].record[j].status.Equals("Online")) { oSheet1.Cells[rows, 1].Font.Color = 0x22EE22; } else { oSheet1.Cells[rows, 1].Font.Color = 0x000000FF; } oSheet1.Cells[rows, 1].Font.Bold = true; oSheet1.Cells[rows, 2].Font.Bold = true; oSheet1.Cells[rows++, 2].Value2 = nodeRecordsList[i].record[j].timeDate; } } //oRng1 = oSheet.get_Range("A1", "E1"); //oRng1.EntireColumn.AutoFit(); //oSheet.Columns["A:F"].ColumnWidth = 18; // oXL.Visible = false; oXL1.UserControl = false; oXL1.DisplayAlerts = false; oWB1.SaveAs(resPath + "Details" + ".xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); oWB1.Close(); reportLabel.Invoke((MethodInvoker) delegate { reportLabel.Text = "Details Report is saved successfully"; }); } catch (Exception e) { MessageBoxButtons buttons = MessageBoxButtons.OK; DialogResult result; // Displays the MessageBox. result = MessageBox.Show(e.Message, "Details Report", buttons); } }
private void exportExl3(DataGridView dv) { try { object misValue = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel._Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = xlApp.Workbooks.Add(misValue); Microsoft.Office.Interop.Excel._Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item(1); int num = 0; int num2 = 0; for (num = 0; num < 1; num++) { num2 = 0; while (num2 <= (dv.ColumnCount - 1)) { worksheet.Cells[num + 1, num2 + 1] = dv.Columns[num2].HeaderText; num2++; } } for (num = 0; num <= (dv.RowCount - 1); num++) { for (num2 = 0; num2 <= (dv.ColumnCount - 1); num2++) { worksheet.Cells[num + 2, num2 + 1] = dv.Rows[num].Cells[num2].Value; } } string filename = @"W:\Software\AutoMailMachineBreakdown\Total Incidents Closed (" + TxtTo.Text + ")" + ".xls"; //string filename = string.Empty; //SaveFileDialog dialog = new SaveFileDialog //{ // Filter = "Excel files |*.xls|All files (*.*)|*.*", // FilterIndex = 2, // RestoreDirectory = true //}; //if (dialog.ShowDialog() == DialogResult.OK) //{ //filename = dialog.FileName; xlApp.DisplayAlerts = false; workbook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); workbook.Close(true, misValue, misValue); xlApp.Quit(); //} //else //{ // return; //} this.releaseObject(worksheet); this.releaseObject(workbook); this.releaseObject(xlApp); //MessageBox.Show("Exl exported successfully..."); } catch (Exception exception) { MessageBox.Show(exception.ToString()); } }
private void btnExcel_Click(object sender, EventArgs e) { try { Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet worksheet = null; app.Visible = true; worksheet = workbook.Sheets["Sheet1"]; worksheet = workbook.ActiveSheet; //worksheet.PageSetup.CenterHeader = lblTraining.Text; //worksheet.PageSetup.LeftHeader = label1.Text + " " + lblS_date.Text; //worksheet.PageSetup.RightHeader = label2.Text + " " + lblE_date.Text; workbook.WebOptions.Encoding = Microsoft.Office.Core.MsoEncoding.msoEncodingUTF8; app.StandardFont = "Myanmar3"; app.StandardFontSize = 10; for (int i = 1; i < dgvOfficer.Columns.Count + 1; i++) { //worksheet.Range ["A1"].Style worksheet.Cells[1, i] = dgvOfficer.Columns[i - 1].HeaderText.ToString(); } for (int i = 0; i <= dgvOfficer.Rows.Count - 1; i++) { for (int j = 0; j < dgvOfficer.Columns.Count; j++) { if (dgvOfficer.Rows[i].Cells[j].Value != null) { worksheet.Cells[i + 2, j + 1] = dgvOfficer.Rows[i].Cells[j].Value.ToString(); } else { worksheet.Cells[i + 2, j + 1] = ""; } } } object misValue = System.Reflection.Missing.Value; string caption = ""; if (cboRank_Search.Text.Trim() != "") { caption = "အရာရှိစာရင်း(" + cboRank_Search.Text.Trim() + ").xls"; } else { caption = "အရာရှိစာရင်း.xls"; } workbook.SaveAs(caption, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); workbook.Close(true, misValue, misValue); app.Quit(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void exportExl(DataGridView dv) { try { object misValue = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel._Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = xlApp.Workbooks.Add(misValue); Microsoft.Office.Interop.Excel._Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item(1); int num = 0; int num2 = 0; for (num = 0; num < 1; num++) { num2 = 0; while (num2 <= (dv.ColumnCount - 1)) { worksheet.Cells[num + 1, num2 + 1] = dv.Columns[num2].HeaderText; num2++; } } for (num = 0; num <= (dv.RowCount - 1); num++) { for (num2 = 0; num2 <= (dv.ColumnCount - 1); num2++) { worksheet.Cells[num + 2, num2 + 1] = dv.Rows[num].Cells[num2].Value; } } string filename = string.Empty; SaveFileDialog dialog = new SaveFileDialog { Filter = "Excel files |*.xls|All files (*.*)|*.*", FilterIndex = 2, RestoreDirectory = true }; if (dialog.ShowDialog() == DialogResult.OK) { filename = dialog.FileName; workbook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); workbook.Close(true, misValue, misValue); xlApp.Quit(); } else { return; } this.releaseObject(worksheet); this.releaseObject(workbook); this.releaseObject(xlApp); MessageBox.Show("Exl exported successfully..."); } catch (Exception exception) { MessageBox.Show(exception.ToString()); } }
private void closeExcel() { oXL.DisplayAlerts = false; oXL.Visible = false; oXL.UserControl = true; //oWB.Save(); oWB.SaveAs(file, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); oWB.Close(); oXL.Quit(); }
public void DataReport(string filename) { Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workBook = app.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet workSheet = null; workSheet = workBook.Sheets["Sheet1"]; workSheet = workBook.ActiveSheet; workSheet.Name = "Sale Report"; app.Columns.ColumnWidth = 30; app.DisplayAlerts = false; System.Globalization.CultureInfo _cultureTHInfo = new System.Globalization.CultureInfo("th-TH"); DateTime dt; for (int i = 1; i < dataGridView1.Columns.Count + 1; i++) { workSheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText; } for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) { for (int j = 0; j < dataGridView1.Columns.Count; j++) { if (j == 0) // Datetime { dt = Convert.ToDateTime(dataGridView1.Rows[i].Cells[j].Value.ToString()); workSheet.Cells[i + 2, j + 1] = dt.ToString("dd MMM yyyy", _cultureTHInfo); } else { workSheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString(); } } } string lastRow = "E" + Convert.ToString(dataGridView1.Rows.Count); workSheet.get_Range("A1", lastRow).Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; workSheet.get_Range("A1", "E1").Cells.Font.Bold = true; // Header workBook.SaveAs(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); workBook.Close(); app.Quit(); }
private void button1_Click(object sender, System.EventArgs e) { Microsoft.Office.Interop.Excel.Application xlexcel = null; Microsoft.Office.Interop.Excel._Workbook xlWorkbook = null; Microsoft.Office.Interop.Excel._Worksheet xlWorkSheet = null; Excel.Range oRng; try { //open existing workbook. xlexcel.Workbooks.Add("C:\\vehicledet.xlsx"); xlWorkSheet = (Excel._Worksheet) xlWorkbook.ActiveSheet; //Get a new workbook. xlWorkbook = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value)); xlWorkSheet = (Excel._Worksheet) xlWorkbook.ActiveSheet; //Add table headers going cell by cell. xlWorkSheet.Cells[1, 1] = "Plate Number"; xlWorkSheet.Cells[1, 2] = "Car Model"; xlWorkSheet.Cells[1, 3] = "Car Brand"; xlWorkSheet.Cells[1, 4] = "Mileage"; //Format A1:D1 as bold, vertical alignment = center. xlWorkSheet.get_Range("A1", "D1").Font.Bold = true; xlWorkSheet.get_Range("A1", "D1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; // insert text at every last row int _lastRow = xlWorkSheet.Range["A" + xlWorkSheet.Rows.Count].End[Excel.XlDirection.xlUp].Row + 1; xlWorkSheet.Cells[_lastRow, 1] = textBox1.Text; xlWorkSheet.Cells[_lastRow, 2] = textBox2.Text; xlWorkSheet.Cells[_lastRow, 3] = textBox3.Text; xlWorkSheet.Cells[_lastRow, 4] = textBox4.Text; //AutoFit columns A:D. oRng = xlWorkSheet.get_Range("A1", "D1"); oRng.EntireColumn.AutoFit(); //Make sure Excel is visible and give the user control of Microsoft Excel's lifetime. xlexcel.Visible = true; xlexcel.UserControl = true; xlWorkbook.Save(); xlWorkbook.Close(); } catch (Exception theException) { String errorMessage; errorMessage = "Error: "; errorMessage = String.Concat(errorMessage, theException.Message); errorMessage = String.Concat(errorMessage, " Line: "); errorMessage = String.Concat(errorMessage, theException.Source); MessageBox.Show(errorMessage, "Error"); } }
private string DownLoadExcel_Crosscheckfile(List <clsFAinfo> Result) { try { ProcessLogger.Fatal("8948 Down Initial" + DateTime.Now.ToString()); #region 获取模板路径 string fullPath = AppDomain.CurrentDomain.BaseDirectory + "System\\confing.xls"; SaveFileDialog sfdDownFile = new SaveFileDialog(); sfdDownFile.OverwritePrompt = false; string DesktopPath = Convert.ToString(System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop)); sfdDownFile.Filter = "Excel files (*.xls,*.xlsx)|*.xls;*.xlsx"; string fileinfo = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Resources\\"); sfdDownFile.FileName = Path.Combine(fileinfo, "print" + "_" + DateTime.Now.ToString("yyyyMMdd_mmss") + ".xls"); string strExcelFileName = string.Empty; string ResaveName = AppDomain.CurrentDomain.BaseDirectory + "Resources\\" + "print" + "_" + DateTime.Now.ToString("yyyyMMdd_mmss") + ".xls"; #endregion #region 导出前校验模板信息 if (string.IsNullOrEmpty(sfdDownFile.FileName)) { MessageBox.Show("File name can't be empty, please Check, thanks!"); return(""); } if (!File.Exists(fullPath)) { MessageBox.Show("Template file does not exist, please Check, thanks!"); return(""); } else { strExcelFileName = sfdDownFile.FileName; } #endregion #region Excel 初始化 System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Microsoft.Office.Interop.Excel.Range rng; Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); System.Reflection.Missing missingValue = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel._Workbook ExcelBook = ExcelApp.Workbooks.Open(fullPath, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue); #endregion ProcessLogger.Fatal("8949 Input Initial" + DateTime.Now.ToString()); #region 导入 try { #region Sheet 初始化 Microsoft.Office.Interop.Excel._Worksheet ExcelSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[1]; #region 填充数据 int RowIndex = 6; string fapiaoleixingin = ""; string guidangren = ""; string danghao = ""; string guidangshijian = ""; foreach (clsFAinfo item in Result) { fapiaoleixingin = item.fapiaoleixing; guidangren = item.guidangrenzhanghao; danghao = item.danganhao; if (item.Input_Date != null && item.Input_Date.Length > 8) { guidangshijian = item.Input_Date.Substring(0, 8); } RowIndex++; ExcelSheet.Cells[RowIndex, 1] = "'" + item.fapiaohao; ExcelSheet.Cells[RowIndex, 2] = "'" + item.bianhao; //ExcelSheet.Cells[RowIndex, 3] = item.fapiaohao; } //ExcelApp.Visible = true; //ExcelApp.ScreenUpdating = true; ExcelSheet.Cells[1, 1] = "总件数:" + Result.Count.ToString(); ExcelSheet.Cells[2, 1] = "发票类型:" + fapiaoleixingin; ExcelSheet.Cells[3, 1] = "归档人:" + guidangren; ExcelSheet.Cells[4, 1] = "档号:" + danghao; ExcelSheet.Cells[5, 1] = "归档时间:" + guidangshijian; //直接打印 //ExcelApp.ActiveWindow.View = Excel.XlWindowView.xlPageBreakPreview; //ExcelSheet.PageSetup.PaperSize =Excel.XlPaperSize.xlPaperA4; //ExcelBook.PrintOut(); #region 写入文件 ProcessLogger.Fatal("8950 Output Initial" + DateTime.Now.ToString()); ExcelApp.DisplayAlerts = false; ExcelApp.ScreenUpdating = true; ExcelBook.SaveAs(ResaveName, missingValue, missingValue, missingValue, missingValue, missingValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missingValue, missingValue, missingValue, missingValue, missingValue); ExcelApp.DisplayAlerts = false; ExcelBook.Close(false, missingValue, missingValue); ExcelBook = null; #endregion //转换成PDF 文件 //if (XLSConvertToPDF(ResaveName, ResaveName.Replace("xlsx", "pdf"))) //{ // // FilePath.Add(ResaveName.Replace("xlsx", "pdf")); //} return(ResaveName); #endregion #endregion } #endregion #region 异常处理 catch (Exception ex) { ExcelApp.DisplayAlerts = false; ExcelApp.Quit(); ExcelBook = null; ExcelApp = null; GC.Collect(); GC.WaitForPendingFinalizers(); throw ex; } #endregion #region Finally垃圾回收 finally { //ExcelBook.Close(false, missingValue, missingValue); //ExcelBook = null; ExcelApp.DisplayAlerts = true; ExcelApp.Quit(); clsKeyMyExcelProcess.Kill(ExcelApp); GC.Collect(); GC.WaitForPendingFinalizers(); } #endregion } catch (Exception ex) { throw ex; } }
public static bool crateFinalControl(string save, Clamp clamp) { bool open = false; Microsoft.Office.Interop.Excel.Application oXL = null; Microsoft.Office.Interop.Excel._Workbook oWB = null; Microsoft.Office.Interop.Excel._Worksheet oSheet = null; //Microsoft.Office.Interop.Excel.Range oRng; object misvalue = System.Reflection.Missing.Value; try { //Start Excel and get Application object. oXL = new Microsoft.Office.Interop.Excel.Application(); //oXL.Visible = true; // oWB = (Excel.Workbook)(oXL.Workbooks._Open(@"C:\Users\AleksandraTos\Documents\Visual Studio 2008\Projects\Control Generator\bin\Debug\final control.xlsx", Missing.Value, //Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, //Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)); oWB = (Excel.Workbook)(oXL.Workbooks._Open(save, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)); //Get a new workbook. //oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add("")); oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet; if (!clamp.Clamptype.Contains("PC")) { RegularClamp(oSheet, clamp); } else { PinchClamp(oSheet, clamp); } oXL.Visible = false; oXL.UserControl = false; oWB.SaveAs(save, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //oWB.Close(true, open, Missing.Value); open = true; oWB.Close(true, Missing.Value, Missing.Value); oXL.Quit(); Marshal.ReleaseComObject(oSheet); Marshal.ReleaseComObject(oWB); Marshal.ReleaseComObject(oXL); } catch (Exception ex) { string newfolder = direktorijum + "\\Logs"; if (!Directory.Exists(newfolder)) { Directory.CreateDirectory(newfolder); } newfolder += "\\log21" + DateTime.Now.ToString("dd_MM_yyyy hh_mm_ss") + ".txt"; StreamWriter sw = new StreamWriter(newfolder); sw.Write(ex.ToString()); sw.Close(); //MessageBox.Show("Something went wrong during the creation of the control file", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); //if (!Directory.Exists(direktorijum + @"\Log Files")) Directory.CreateDirectory(direktorijum + @"\Log Files"); //StreamWriter sw = new StreamWriter(direktorijum + "\\Log Files\\Log" + DateTime.Now.ToString("dd-MM-yyyy -- HH-mm-ss") + ".txt"); //sw.Write(ex); //sw.Close(); open = false; } finally { } return(open); }
private void btnPrint_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Application oXL = null; Microsoft.Office.Interop.Excel._Workbook oWB = null; Microsoft.Office.Interop.Excel._Worksheet oSheet = null; Excel.Range range; oXL = new Microsoft.Office.Interop.Excel.Application(); oWB = oXL.Workbooks.Open(AppContext.BaseDirectory + @"\2021.xlsx"); oSheet = String.IsNullOrEmpty("Sheet1") ? (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet : (Microsoft.Office.Interop.Excel._Worksheet)oWB.Worksheets["Sheet1"]; int rw = 0; int cl = 0; range = oSheet.UsedRange; if (txtSeqNo.Text == "0") { rw = range.Rows.Count + 1; } else { rw = range.Rows.Count + 2; } cl = range.Columns.Count; try { //export UG to text file using (TextWriter tw = new StreamWriter(AppContext.BaseDirectory + @"\SN.txt")) { for (int x = 0; x < DG.Rows.Count - 1; x++) { tw.Write($"{DG.Rows[x].Cells[0].Value.ToString()}"); tw.WriteLine(); //append text fields to excell //UpdateExcel(decimal.Parse(txtSeqNo.Text), txtPartNo.Text, txtBrand.Text, txtPONo.Text, txtYear.Text, DG.Rows[x].Cells[0].Value.ToString().Substring(DG.Rows[x].Cells[0].Value.ToString().Length - 8, 8), DG.Rows[x].Cells[0].Value.ToString()); oSheet.Cells[rw + x, 1] = txtPartNo.Text; oSheet.Cells[rw + x, 2] = txtBrand.Text; oSheet.Cells[rw + x, 3] = txtPONo.Text; oSheet.Cells[rw + x, 4] = txtYear.Text; oSheet.Cells[rw + x, 5] = DG.Rows[x].Cells[0].Value.ToString().Substring(DG.Rows[x].Cells[0].Value.ToString().Length - 8, 8); oSheet.Cells[rw + x, 6] = DG.Rows[x].Cells[0].Value.ToString(); } } oWB.Save(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { if (oWB != null) { oWB.Close(); } oXL.Quit(); Marshal.ReleaseComObject(oSheet); Marshal.ReleaseComObject(oWB); Marshal.ReleaseComObject(oXL); } }
private bool XLSConvertToPDF(string sourcePath, string targetPath) { bool result = false; Excel.XlFixedFormatType targetType = Excel.XlFixedFormatType.xlTypePDF; object missing = Type.Missing; Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = null; Microsoft.Office.Interop.Excel._Workbook ExcelBook = null; try { object target = targetPath; object type = targetType; //workBook = application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing, // missing, missing, missing, missing, missing, missing, missing, missing, missing); // sourcePath = "C:\\Users\\IBM_ADMIN\\Desktop\\newadd.xlsx"; System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); System.Reflection.Missing missingValue = System.Reflection.Missing.Value; ExcelBook = ExcelApp.Workbooks.Open(sourcePath, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue); //ExcelApp.Visible = true; //ExcelApp.ScreenUpdating = true; ////ActiveWindow.SmallScroll Down:=6; //ExcelApp.ActiveWindow.View = Excel.XlWindowView.xlPageBreakPreview; ////ExcelApp.ActiveWindow.SmallScroll = Excel.; //ExcelApp.ActiveWindow.Zoom = 80; ////ActiveWindow.SmallScroll Down:=-3 //// excelRange.WrapText = true; ////ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1 //ActiveWindow.SmallScroll Down:=30 //Set ActiveSheet.HPageBreaks(1).Location = Range("A67") //ActiveWindow.SmallScroll Down:=-75 //Microsoft.Office.Interop.Excel.Worksheet WS2 = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[2]; ////上边距 //double top = 0; ////左边距 //double left = 0; ////右边距 //double right = 0; ////下边距 //double footer = 0; //WS2.DisplayAutomaticPageBreaks = false;//显示分页线 //WS2.PageSetup.CenterFooter = "第 &P 页,共 &N 页"; //WS2.PageSetup.TopMargin = ExcelApp.InchesToPoints(top / 2.54);//上 //WS2.PageSetup.BottomMargin = ExcelApp.InchesToPoints(footer / 15.54);//下 //WS2.PageSetup.LeftMargin = ExcelApp.InchesToPoints(left / 2.54);//左 //WS2.PageSetup.RightMargin = ExcelApp.InchesToPoints(right / 2.54);//右 //WS2.PageSetup.CenterHorizontally = true;//水平居中 xlSheet.PageSetup.PrintTitleRows = "$1:$3";//顶端标题行 //WS2.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA3;//A3纸张大小 xlSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;//纸张方向.横向 //Excel.Range excelRange = WS2.get_Range(WS2.Cells[1, 1], WS2.Cells[64, 24]); //自动调整列宽 //// excelRange.EntireColumn.AutoFit(); //// excelRange.WrapText = false; //文本自动换行 //excelRange.ShrinkToFit = false; ////设置字体在单元格内的对其方式 //excelRange.HorizontalAlignment = XlHAlign.xlHAlignCenter; //// 文本水平居中方式 //excelRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //////设置为横向打印 ////WS2.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; ////ExcelApp.ActiveWindow.FreezePanes = true; ////excelRange.EntireColumn.AutoFit(); ////WS2.PageSetup.Orientation = 2; //WS2.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; //WS2.PageSetup.LeftMargin = ExcelApp.InchesToPoints(0.0); //WS2.PageSetup.RightMargin = ExcelApp.InchesToPoints(0.0); //WS2.PageSetup.TopMargin = ExcelApp.InchesToPoints(0.0); //WS2.PageSetup.BottomMargin = ExcelApp.InchesToPoints(0.0); //WS2.PageSetup.HeaderMargin = ExcelApp.InchesToPoints(0.0); //WS2.PageSetup.FooterMargin = ExcelApp.InchesToPoints(0.0); //WS2.PageSetup.CenterHorizontally = true; //// WS2.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; //WS2.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; //excelRange = WS2.get_Range(WS2.Cells[1, 1], WS2.Cells[2, 20]); //WS2.PageSetup.PrintTitleRows = excelRange.get_Address(excelRange.Row, excelRange.Column, Excel.XlReferenceStyle.xlA1, 1, 1); ExcelBook.ExportAsFixedFormat(targetType, target, Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing); result = true; } catch { result = false; } finally { if (ExcelBook != null) { ExcelBook.Close(true, missing, missing); ExcelBook = null; } if (ExcelApp != null) { ExcelApp.Quit(); ExcelApp = null; } GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } return(result); }
public static void SaveFile(DataGridView dataGridView, string filePathAndName) { // creating Excel Application Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); // creating new WorkBook within Excel application Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); // creating new Excelsheet in workbook Microsoft.Office.Interop.Excel._Worksheet worksheet = null; //Do not show alerts app.DisplayAlerts = false; // see the excel sheet behind the program app.Visible = false; // get the reference of first sheet. By default its name is Sheet1. // store its reference to worksheet worksheet = workbook.Sheets["Sheet1"]; worksheet = workbook.ActiveSheet; // changing the name of active sheet worksheet.Name = "Exported from gridview"; //// storing header part in Excel //int excelColNo = 1; //for (int i = 0; i < dataGridView.Columns.Count; i++) //{ // if (dataGridView.Columns[i].Visible == true) // { // worksheet.Cells[1, excelColNo] = dataGridView.Columns[i].HeaderText; // excelColNo++; // } //} // storing Each row and column value to excel sheet int excelColNo = 1; for (int i = 0; i < dataGridView.Rows.Count; i++) { excelColNo = 1; for (int j = 0; j < dataGridView.Columns.Count; j++) { if (dataGridView.Columns[j].Visible == true) { worksheet.Cells[i + 2, excelColNo] = "'" + dataGridView.Rows[i].Cells[j].Value.ToString(); excelColNo++; } } } // save the application string timeStamp = DateTime.Now.ToString("ddMMyy"); NewFileName = "DK12_Cold_TM_" + timeStamp + "_UTF8_TM-DK.txt"; try { workbook.SaveAs(FileHelper.FilePath + "\\" + FileHelper.NewFileName, Excel.XlFileFormat.xlCurrentPlatformText, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing); MessageBox.Show("File saved succesfully!"); } catch (Exception ex) { MessageBox.Show("Error during save: " + ex.Message); workbook.Close(); app.Quit(); } workbook.Close(); // Exit from the application app.Quit(); }
private void ExportToExcel() { bool IsExport = false; // Creating a Excel object. Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet worksheet = null; //DataGridView에 불러온 Data가 아무것도 없을 경우 if (studentScoreTable.Rows.Count == 0) { MessageBox.Show("데이터가 없습니다.", "Inform", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } try { worksheet = workbook.ActiveSheet; int cellRowIndex = 1; int cellColumnIndex = 1; for (int col = 0; col < studentScoreTable.Columns.Count; col++) { if (cellRowIndex == 1) { worksheet.Cells[cellRowIndex, cellColumnIndex] = studentScoreTable.Columns[col].HeaderText; } cellColumnIndex++; } cellColumnIndex = 1; cellRowIndex++; for (int row = 0; row < studentScoreTable.Rows.Count - 1; row++) { for (int col = 0; col < studentScoreTable.Columns.Count; col++) { worksheet.Cells[cellRowIndex, cellColumnIndex] = studentScoreTable.Rows[row].Cells[col].Value.ToString(); cellColumnIndex++; } cellColumnIndex = 1; cellRowIndex++; } SaveFileDialog saveFileDialog = GetExcelSave(); if (saveFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK) { workbook.SaveAs(saveFileDialog.FileName); MessageBox.Show("저장되었습니다."); IsExport = true; } //Export 성공 했으면 객체들 해제 if (IsExport) { workbook.Close(); excel.Quit(); workbook = null; excel = null; } } catch (System.Exception ex) { MessageBox.Show(ex.Message); } }
public ActionResult DownLoadFile() { try { #region Exal byte[] bytes; string fileName = "", contentType = "", PaperPath = ""; DataSet dswordchargeinrow = new DataSet(); KeystoneProject.Buisness_Logic.Master.Bl_Services Bl_obj = new Buisness_Logic.Master.Bl_Services(); Services AddServiceMod = new Services(); AddServiceMod.StoreAllServices = Bl_obj.GetAllServicesExcelData(); AddServiceMod.dsServicesCharges = Bl_obj.GetAllWardName(); Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet worksheet = null; Excel.Range chartRange; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"]; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet; app.get_Range("A2", "S2").Locked = true; string WardNAme; //worksheet.Name = "Exported from Keystone"; int count = 0; for (int i = 1; i < AddServiceMod.StoreAllServices.Tables[0].Columns.Count + 1; i++) { worksheet.Cells[1, i] = AddServiceMod.StoreAllServices.Tables[0].Columns[i - 1].ColumnName; count = i; } int m = 0; for (int word = 0; word < AddServiceMod.dsServicesCharges.Tables[0].Rows.Count; word++) { worksheet.Cells[1, count + word] = AddServiceMod.dsServicesCharges.Tables[0].Rows[m]["WardName"]; // worksheet.get_Range(worksheet.Cells[1, word], worksheet.Cells[4, 3]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Orange); WardNAme = AddServiceMod.dsServicesCharges.Tables[0].Rows[m]["WardName"].ToString() + ","; m++; } for (int i = 0; i < AddServiceMod.StoreAllServices.Tables[0].Rows.Count; i++) { for (int j = 0; j < AddServiceMod.StoreAllServices.Tables[0].Columns.Count; j++) { // if (j < 2) // { worksheet.Cells[i + 2, j + 1] = AddServiceMod.StoreAllServices.Tables[0].Rows[i][j].ToString(); // } if (AddServiceMod.StoreAllServices.Tables[0].Rows[i]["ServiceID"].ToString() != "") { dswordchargeinrow = Bl_obj.SelectServiceChargesbyID(Convert.ToInt16(AddServiceMod.StoreAllServices.Tables[0].Rows[i]["ServiceID"])); if (dswordchargeinrow.Tables[0].Rows.Count > 0) { foreach (DataRow drcharge in AddServiceMod.dsServicesCharges.Tables[0].Rows) { int charge = 0; foreach (DataRow dr in dswordchargeinrow.Tables[0].Rows) { string WardName = drcharge["WardName"].ToString(); string WardName1 = dr["WardName"].ToString(); if (drcharge["WardName"].ToString() == dr["WardName"].ToString()) { worksheet.Cells[i + 2, charge + 7] = dr["GeneralCharges"]; } charge++; } } } } } } #endregion Random obj = new Random(); int a = obj.Next(1, 300); string DateCode = "Service_Excel_Data" + Bl_obj.ReffrenceExcel(); fileName = Server.MapPath("~/") + "Service_Excel/" + DateCode; workbook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); workbook.Close(true, Type.Missing, Type.Missing); string Path = fileName + ".xlsx"; string filename = System.IO.Path.GetFileName(DateCode + ".xlsx"); // string fullpath = System.IO.Path.Combine(Path, fileName); contentType = MimeMapping.GetMimeMapping(Path); File(Path, contentType, filename); // File(Path, contentType, filename); return(new JsonResult { Data = "Done", JsonRequestBehavior = JsonRequestBehavior.AllowGet }); //fileName = "Service_Excel_Data" + Bl_obj.ReffrenceCode()+".xlsx"; } catch (Exception ex) { throw; } // return File(FileById.FileContent, "/MRDFiles/pdf", FileById.FileName); }
//public static void addPictureExcel(string value, int cellname, int cellnum) //{ // foreach (Excel.Shape sh in myExcelWorkSheet.Shapes) // { // if (sh.Name != "Picture 1") // sh.Delete(]=1; // } // myExcelWorkSheet.Shapes.AddPicture(value, Microsoft.Office.Core.MsoTriState.msoTrue, Microsoft.Office.Core.MsoTriState.msoTrue, cellname, cellnum, 131, 40]=1; //} public static bool createFinalControlReport(string save, List <Clamp> clamps, string date, string RN) { bool result = false; int counter = 5; object misValue = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application oXL = null; Microsoft.Office.Interop.Excel._Workbook oWB = null; Microsoft.Office.Interop.Excel._Worksheet oSheet = null; try { oXL = new Microsoft.Office.Interop.Excel.Application(); //oXL.Visible = true; // oWB = (Excel.Workbook)(oXL.Workbooks._Open(@"C:\Users\AleksandraTos\Documents\Visual Studio 2008\Projects\Control Generator\bin\Debug\final control.xlsx", Missing.Value, //Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, //Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)); oWB = (Excel.Workbook)(oXL.Workbooks._Open(save, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)); //Get a new workbook. //oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add("")); oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet; if (oXL == null) { //MessageBox.Show("Excel is not properly installed!!"); //return; } else { oSheet.Cells[5, 1] = date; oSheet.Cells[15, 1] = RN; foreach (Clamp c in clamps) { oSheet.Cells[counter, 2] = c.Serialnum; oSheet.Cells[counter, 3] = c.Rado; oSheet.Cells[12, 1] = c.Clamptype; if (c.Clamptype.Contains("WH1 - 15V") || c.Clamptype.Contains("WH2 - 15V")) { oSheet.Cells[9, 1] = "15V"; oSheet.Cells[counter, 8] = c.Smallresistance; } else { oSheet.Cells[9, 1] = "24V"; oSheet.Cells[counter, 8] = c.Bigresistance; } oSheet.Cells[counter, 6] = "OK"; oSheet.Cells[counter, 5] = "MM, NS, MM"; oSheet.Cells[counter, 4] = "1"; counter++; } oXL.Visible = false; oXL.UserControl = false; oWB.SaveAs(save, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //oWB.Close(true, open, Missing.Value); result = true; oWB.Close(true, Missing.Value, Missing.Value); oXL.Quit(); Marshal.ReleaseComObject(oSheet); Marshal.ReleaseComObject(oWB); Marshal.ReleaseComObject(oXL); } } catch (Exception ex) { string newfolder = direktorijum + "\\Logs"; if (!Directory.Exists(newfolder)) { Directory.CreateDirectory(newfolder); } newfolder += "\\log22" + DateTime.Now.ToString("dd_MM_yyyy hh_mm_ss") + ".txt"; StreamWriter sw = new StreamWriter(newfolder); sw.Write(ex.ToString()); sw.Close(); //MessageBox.Show("Something went wrong during the creation of the control file", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); //if (!Directory.Exists(direktorijum + @"\Log Files")) Directory.CreateDirectory(direktorijum + @"\Log Files"); //StreamWriter sw = new StreamWriter(direktorijum + "\\Log Files\\Log" + DateTime.Now.ToString("dd-MM-yyyy -- HH-mm-ss") + ".txt"); //sw.Write(ex); //sw.Close(); result = false; } return(result); }
public void createColorScaleExcel() { try { //Start Excel and get Application object. oXL = new Microsoft.Office.Interop.Excel.Application(); //oXL.Visible = true; //Get a new workbook. oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add("")); oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet; oSheet.Columns["A:F"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; oSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; oSheet.get_Range("A1:A2", "B1:B2").Merge(); oSheet.Cells[1, 1].Value2 = "From: " + reportDate[0] + " at " + reportTimePeriod[0] + "\nTo: " + reportDate[1] + " at " + reportTimePeriod[1]; int m = 3; for (int i = 0; i < nodePathInfoList.Count(); i++) { oSheet.Cells[m, 1].Value2 = nodePathInfoList[i].portName + ":"; oSheet.Cells[m, 1].Font.Bold = true; oSheet.Cells[m, 1].Font.Size = 20; oSheet.Cells[m, 1].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; oSheet.get_Range("A" + m.ToString(), "B" + m.ToString()).Merge(); // Down to top counter int z = nodePathInfoList[i].nodePathData.Count() - 1; for (int j = 0; j < nodePathInfoList[i].nodePathData.Count(); j++) { if (j != 0 && j % 6 == 0) { m += 4; } oSheet.Cells[m + 1, (j % 6) + 1].Value2 = nodePathInfoList[i].nodePathData[z].ElementAt(1); oSheet.Cells[m + 2, (j % 6) + 1].Value2 = nodePathInfoList[i].nodePathData[z].ElementAt(2); oSheet.Cells[m + 3, (j % 6) + 1].Value2 = nodePathInfoList[i].nodePathData[z].ElementAt(3); z--; // Set all borders for table oSheet.Cells[m + 1, (j % 6) + 1].Borders.Color = System.Drawing.Color.Black.ToArgb(); oSheet.Cells[m + 2, (j % 6) + 1].Borders.Color = System.Drawing.Color.Black.ToArgb(); oSheet.Cells[m + 3, (j % 6) + 1].Borders.Color = System.Drawing.Color.Black.ToArgb(); // Create a color scale for third row cfColorScale = (ColorScale)(oSheet.get_Range("A" + (m + 3).ToString(), "F" + (m + 3).ToString()).FormatConditions.AddColorScale(2)); // Min and Max color cfColorScale.ColorScaleCriteria[1].FormatColor.Color = 0x000000FF; // Red cfColorScale.ColorScaleCriteria[2].FormatColor.Color = 0x0000FF00; // Green // Set table font size and bold oSheet.get_Range("A" + (m + 1).ToString(), "F" + (m + 1).ToString()).Font.Size = 11; oSheet.get_Range("A" + (m + 2).ToString(), "F" + (m + 2).ToString()).Font.Size = 11; oSheet.get_Range("A" + (m + 3).ToString(), "F" + (m + 3).ToString()).Font.Size = 11; oSheet.get_Range("A" + (m + 1).ToString(), "F" + (m + 1).ToString()).Font.Bold = true; oSheet.get_Range("A" + (m + 2).ToString(), "F" + (m + 2).ToString()).Font.Bold = true; oSheet.get_Range("A" + (m + 3).ToString(), "F" + (m + 3).ToString()).Font.Bold = true; } m += 6; } oRng = oSheet.get_Range("A1", "E1"); oRng.EntireColumn.AutoFit(); oSheet.Columns["A:F"].ColumnWidth = 18; // oXL.Visible = false; oXL.UserControl = false; oXL.DisplayAlerts = false; oWB.SaveAs(resPath + ".xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); oWB.Close(); reportLabel.Invoke((MethodInvoker) delegate { reportLabel.Text = "Report is saved successfully"; }); } catch (Exception e) { MessageBoxButtons buttons = MessageBoxButtons.OK; DialogResult result; // Displays the MessageBox. result = MessageBox.Show(e.Message, "Reports", buttons); } }
public static bool createPackingList(string save, List <Clamp> clamps, List <string> onetimeparams, string RN) { bool open = false; int counter = 16; object misValue = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application oXL = null; Microsoft.Office.Interop.Excel._Workbook oWB = null; Microsoft.Office.Interop.Excel._Worksheet oSheet = null; //Microsoft.Office.Interop.Excel.Range oRng; try { //if (xlApp == null) //{ // //MessageBox.Show("Excel is not properly installed!!"); // //return; //} //else //{ oXL = new Microsoft.Office.Interop.Excel.Application(); //oXL.Visible = true; // oWB = (Excel.Workbook)(oXL.Workbooks._Open(@"C:\Users\AleksandraTos\Documents\Visual Studio 2008\Projects\Control Generator\bin\Debug\final control.xlsx", Missing.Value, //Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, //Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)); oWB = (Excel.Workbook)(oXL.Workbooks._Open(save, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)); //Get a new workbook. //oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add("")); oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet; oSheet.Cells[4, 8] = onetimeparams[0]; oSheet.Cells[13, 1] = onetimeparams[1]; oSheet.Cells[10, 1] = RN; oSheet.Cells[13, 4] = onetimeparams[2]; foreach (Clamp c in clamps) { oSheet.Cells[counter, 7] = c.Serialnum; oSheet.Cells[counter, 8] = c.Rado; oSheet.Cells[counter, 9] = "1"; oSheet.Cells[16, 6] = c.Clamptype; counter++; } oXL.Visible = false; oXL.UserControl = false; oWB.SaveAs(save, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //oWB.Close(true, open, Missing.Value); open = true; oWB.Close(true, Missing.Value, Missing.Value); oXL.Quit(); Marshal.ReleaseComObject(oSheet); Marshal.ReleaseComObject(oWB); Marshal.ReleaseComObject(oXL); //} } catch (Exception ex) { string newfolder = direktorijum + "\\Logs"; if (!Directory.Exists(newfolder)) { Directory.CreateDirectory(newfolder); } newfolder += "\\log23" + DateTime.Now.ToString("dd_MM_yyyy hh_mm_ss") + ".txt"; StreamWriter sw = new StreamWriter(newfolder); sw.Write(ex.ToString()); sw.Close(); //MessageBox.Show("Something went wrong during the creation of the control file", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); //if (!Directory.Exists(direktorijum + @"\Log Files")) Directory.CreateDirectory(direktorijum + @"\Log Files"); //StreamWriter sw = new StreamWriter(direktorijum + "\\Log Files\\Log" + DateTime.Now.ToString("dd-MM-yyyy -- HH-mm-ss") + ".txt"); //sw.Write(ex); //sw.Close(); open = false; } return(open); }
public int print(bool isShow) { // 打开一个Excel进程 Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application(); // 利用这个进程打开一个Excel文件 Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\cleancut\5 SOP-MFG-302-R05A 清洁分切工序清场记录.xlsx"); // 选择一个Sheet,注意Sheet的序号是从1开始的 Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[1]; // 修改Sheet中某行某列的值 fill_excel(my); //"生产指令-步骤序号- 表序号 /&P" int sheetnum; OleDbDataAdapter da = new OleDbDataAdapter("select ID from 清场记录" + " where 生产指令ID=" + instrid.ToString(), mySystem.Parameter.connOle); DataTable dt = new DataTable("temp"); da.Fill(dt); List <Int32> sheetList = new List <Int32>(); for (int i = 0; i < dt.Rows.Count; i++) { sheetList.Add(Convert.ToInt32(dt.Rows[i]["ID"].ToString())); } sheetnum = sheetList.IndexOf(Convert.ToInt32(dt_prodinstr.Rows[0]["ID"])) + 1; my.PageSetup.RightFooter = instrcode + "-05-" + sheetnum.ToString("D3") + " &P/" + wb.ActiveSheet.PageSetup.Pages.Count; // &P 是页码 if (isShow) { //true->预览 // 设置该进程是否可见 oXL.Visible = true; // 让这个Sheet为被选中状态 my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能 return(0); } else { int pageCount = wb.ActiveSheet.PageSetup.Pages.Count; bool isPrint = true; //false->打印 try { // 设置该进程是否可见 //oXL.Visible = false; // oXL.Visible=false 就会直接打印该Sheet // 直接用默认打印机打印该Sheet my.PrintOut(); } catch { isPrint = false; } finally { if (isPrint) { //写日志 string log = "=====================================\n"; log += DateTime.Now.ToString("yyyy年MM月dd日 hh时mm分ss秒") + "\n" + label角色.Text + ":" + mySystem.Parameter.userName + " 打印文档\n"; dt_prodinstr.Rows[0]["日志"] = dt_prodinstr.Rows[0]["日志"].ToString() + log; bs_prodinstr.EndEdit(); if (!mySystem.Parameter.isSqlOk) { da_prodinstr.Update((DataTable)bs_prodinstr.DataSource); } else { da_prodinstrsql.Update((DataTable)bs_prodinstr.DataSource); } } // 关闭文件,false表示不保存 wb.Close(false); // 关闭Excel进程 oXL.Quit(); // 释放COM资源 Marshal.ReleaseComObject(wb); Marshal.ReleaseComObject(oXL); wb = null; oXL = null; my = null; } return(pageCount); } }
public int print(bool b) { int label_打印成功 = 1; // 打开一个Excel进程 Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application(); // 利用这个进程打开一个Excel文件 string dir = System.IO.Directory.GetCurrentDirectory(); dir += "./../../xls/cleancut/SOP-MFG-302-R01A 清洁分切生产指令.xlsx"; Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(dir); // 选择一个Sheet,注意Sheet的序号是从1开始的 Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[2]; // 修改Sheet中某行某列的值 fill_excel(my); //"生产指令-步骤序号- 表序号 /&P" my.PageSetup.RightFooter = instrcode + "-01-" + find_indexofprint().ToString("D3") + " &P/" + wb.ActiveSheet.PageSetup.Pages.Count; // &P 是页码 if (b) { // 设置该进程是否可见 oXL.Visible = true; // 让这个Sheet为被选中状态 my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能 return(0); } else { int pageCount = wb.ActiveSheet.PageSetup.Pages.Count; // 直接用默认打印机打印该Sheet try { my.PrintOut(); // oXL.Visible=false 就会直接打印该Sheet } catch { label_打印成功 = 0; } finally { if (1 == label_打印成功) { string log = "\n=====================================\n"; log += DateTime.Now.ToString("yyyy年MM月dd日 hh时mm分ss秒") + "\n" + label角色.Text + ":" + mySystem.Parameter.userName + " 完成打印\n"; dt_prodinstr.Rows[0]["日志"] = dt_prodinstr.Rows[0]["日志"].ToString() + log; bs_prodinstr.EndEdit(); if (!mySystem.Parameter.isSqlOk) { da_prodinstr.Update((DataTable)bs_prodinstr.DataSource); } else { da_prodinstrsql.Update((DataTable)bs_prodinstr.DataSource); } } // 关闭文件,false表示不保存 wb.Close(false); // 关闭Excel进程 oXL.Quit(); // 释放COM资源 Marshal.ReleaseComObject(wb); Marshal.ReleaseComObject(oXL); wb = null; oXL = null; my = null; } return(pageCount); } }
private void printToolStripMenuItem_Click(object sender, EventArgs e) { try { //string file = ""; //if (folderBrowserDialog1.ShowDialog() == DialogResult.OK) //{ // file = folderBrowserDialog1.SelectedPath + @"\MES.xlsx"; //} //else //{ // return; //} //FileStream outStream = new FileStream(file, FileMode.Create, FileAccess.Write, FileShare.Read); //outStream.Dispose(); //string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;" + // "Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=0'", file); ////FileStream inStream = new FileStream(Application.StartupPath + @"\temp.xlsx", FileMode.Open, FileAccess.Read, FileShare.None); //using (OleDbConnection cn = new OleDbConnection(connectionString)) //{ // cn.Open(); // for (int i = 0; i <= dgvTrainner .Rows .Count ; i++) // { // OleDbCommand cmd1 = new OleDbCommand("INSERT INTO [Sheet1$] " + // "([Column1],[Column2],[Column3],[Column4],[Column5],[Column6]) " + // "VALUES(@value1, @value2, @value3, @value4, @value5, @value6)", cn); // cmd1.Parameters.AddWithValue("@value1", dgvTrainner.Rows[i].Cells[0].Value.ToString () ); // cmd1.Parameters.AddWithValue("@value2", dgvTrainner.Rows[i].Cells[1].Value.ToString()); // cmd1.Parameters.AddWithValue("@value3", dgvTrainner.Rows[i].Cells[2].Value.ToString()); // cmd1.Parameters.AddWithValue("@value4", dgvTrainner.Rows[i].Cells[3].Value.ToString()); // cmd1.Parameters.AddWithValue("@value5", dgvTrainner.Rows[i].Cells[4].Value.ToString()); // cmd1.Parameters.AddWithValue("@value6", dgvTrainner.Rows[i].Cells[5].Value.ToString()); // cmd1.ExecuteNonQuery(); // } //} //using (inStream) //{ // while (true) // { // int b = inStream.ReadByte(); // if (b == -1) // break; // outStream.WriteByte((byte)b); // } // MessageBox.Show("Your output file is ready in " + file + " as Marker_M_Data.xlsx!", "Creating output file Completed!", MessageBoxButtons.OK, MessageBoxIcon.Information); //} //inStream.Dispose(); //outStream.Dispose(); /* * Function -2 */ Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet worksheet = null; app.Visible = true; worksheet = workbook.Sheets["Sheet1"]; worksheet = workbook.ActiveSheet; worksheet.PageSetup.CenterHeader = lblTraining.Text; worksheet.PageSetup.LeftHeader = label1.Text + " " + lblS_date.Text; worksheet.PageSetup.RightHeader = label2.Text + " " + lblE_date.Text; workbook.WebOptions.Encoding = Microsoft.Office.Core.MsoEncoding.msoEncodingUTF8; app.StandardFont = "Myanmar3"; app.StandardFontSize = 10; for (int i = 1; i < dgvTrainner.Columns.Count + 1; i++) { //worksheet.Range ["A1"].Style worksheet.Cells[1, i] = dgvTrainner.Columns[i - 1].HeaderText.ToString(); } for (int i = 0; i <= dgvTrainner.Rows.Count - 1; i++) { for (int j = 0; j < dgvTrainner.Columns.Count; j++) { if (dgvTrainner.Rows[i].Cells[j].Value != null) { worksheet.Cells[i + 2, j + 1] = dgvTrainner.Rows[i].Cells[j].Value.ToString(); } else { worksheet.Cells[i + 2, j + 1] = ""; } } } object misValue = System.Reflection.Missing.Value; workbook.SaveAs(lblTraining.Text + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); workbook.Close(true, misValue, misValue); app.Quit(); /* * Function -1 */ //Excel.Application xlApp; //Excel.Workbook xlWorkBook; //Excel.Worksheet xlWorkSheet; //object misValue = System.Reflection.Missing.Value; //xlApp = new Excel.Application(); //xlWorkBook = xlApp.Workbooks.Add(misValue); //xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); ////int i = 0; ////int j = 0; //for (int i = 0; i < dgvTrainner .RowCount ; i++) //{ // for (int j = 0; j < dgvTrainner .ColumnCount; j++) // { // DataGridViewCell cell = dgvTrainner[j, i]; // xlWorkSheet.Cells[i + 1, j + 1] = cell.Value; // } //} //xlWorkBook.SaveAs("MES.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); //xlWorkBook.Close(true, misValue, misValue); //xlApp.Quit(); //release_Object(xlWorkSheet); //release_Object(xlWorkBook); //release_Object(xlApp); //MessageBox.Show("Excel file တည်ဆောက်ပြီးပါပြီ။","သင်တန်းဆိုင်ရာ သတင်းပေးချက်", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show(ex.Message); } }