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 bool DeleteExcelSheet(string pExcelPath, string pSheetName, out string pDeleteSheet) { try { object objOpt = Missing.Value; //打开一个Excel应用 Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); if (app == null) { throw new Exception("打开Excel应用时发生错误!"); } app.Visible = false; Microsoft.Office.Interop.Excel.Workbooks wbs = app.Workbooks; Microsoft.Office.Interop.Excel._Workbook wb = wbs.Open(pExcelPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); wb.EnableAutoRecover = false; Microsoft.Office.Interop.Excel.Sheets shs = wb.Sheets; Microsoft.Office.Interop.Excel._Worksheet sh = (Microsoft.Office.Interop.Excel._Worksheet)shs.get_Item(pSheetName); app.DisplayAlerts = false; sh.Delete(); wb.Save(); pDeleteSheet = string.Empty; return(true); } catch (Exception vErr) { pDeleteSheet = vErr.Message; return(false); } finally { KillProcess(); } }
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 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); } }
public void exportDataExcel(DataSet dsTemp, string reportName) { ArrayList paramArraylist = new ArrayList(); string Excelpath = ConfigurationSettings.AppSettings["ExcelFile"] + "CarrierReports.xlsx"; string dateTime = "CarrierReports " + System.DateTime.Now.Year.ToString() + System.DateTime.Now.Month.ToString() + System.DateTime.Now.Day.ToString() + System.DateTime.Now.Hour.ToString() + System.DateTime.Now.Minute.ToString() + System.DateTime.Now.Second.ToString() + ".xlsx"; string Destpath = ConfigurationSettings.AppSettings["ExcelFile"] + dateTime; DataSet DsExportedData = (DataSet)dsTemp; File.Copy(Excelpath, Destpath); Microsoft.Office.Interop.Excel.ApplicationClass XcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel._Workbook workbook = XcelApp.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Workbook workbookDummy = XcelApp.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet worksheet = null; Microsoft.Office.Interop.Excel.Range chartrange; #region Excel //workbook = XcelApp.Workbooks.Open(Destpath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true); SVN try { if (DsExportedData != null) { if (DsExportedData.Tables[0] != null) { #region TemplateData worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets["TemplateData"]; //worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet; DataTable dtTemplateData = (DataTable)DsExportedData.Tables[0]; for (int j = 0; j < dtTemplateData.Rows.Count; j++) { for (int k = 0; k < dtTemplateData.Columns.Count; k++) { worksheet.Cells[j + 2, k + 1] = dtTemplateData.Rows[j].ItemArray[k].ToString(); } } chartrange = worksheet.get_Range("A1", "D1"); chartrange.Font.Bold = true; chartrange.Cells.Interior.Color = System.Drawing.Color.Gainsboro.ToArgb(); chartrange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; chartrange.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black); XcelApp.Columns.AutoFit(); workbook.Save(); #endregion } if (DsExportedData.Tables[1] != null) { #region TemplateBillingData worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets["TemplateBillingData"]; //worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet; DataTable dtTemplateBillingData = (DataTable)DsExportedData.Tables[1]; for (int j = 0; j < dtTemplateBillingData.Rows.Count; j++) { for (int k = 0; k < dtTemplateBillingData.Columns.Count; k++) { worksheet.Cells[j + 2, k + 1] = dtTemplateBillingData.Rows[j].ItemArray[k].ToString(); } } chartrange = worksheet.get_Range("A1", "E1"); chartrange.Font.Bold = true; chartrange.Cells.Interior.Color = System.Drawing.Color.Gainsboro.ToArgb(); chartrange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; chartrange.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black); XcelApp.Columns.AutoFit(); workbook.RefreshAll(); workbook.Save(); #endregion } else { } } else { } // workbook.Close(); SVN } catch (Exception ex) { // workbook.Close(); SVN ErrorHandler.WriteError(ex.Message); } #endregion }