public static bool writeSelfCheckData(carinfor.lljSelfDetectInf cgjdata) { Microsoft.Office.Interop.Excel.Application appexcel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks appworkbook = (Microsoft.Office.Interop.Excel.Workbooks)appexcel.Workbooks; if (File.Exists(Application.StartupPath + "\\bk\\自检日志.xls")) { string filename = Application.StartupPath + "\\bk\\检测信息" + DateTime.Now.ToString("yyMMdd") + ".xls"; if (!File.Exists(filename)) { File.Copy(Application.StartupPath + "\\bk\\自检日志.xls", filename, true); } appworkbook.Open(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Microsoft.Office.Interop.Excel.Workbook objbook = appworkbook.get_Item(1); Microsoft.Office.Interop.Excel.Sheets msheets = (Microsoft.Office.Interop.Excel.Sheets)objbook.Worksheets; Microsoft.Office.Interop.Excel._Worksheet objsheet = (Microsoft.Office.Interop.Excel._Worksheet)msheets.get_Item(1); Microsoft.Office.Interop.Excel.Range m_objRange = (Microsoft.Office.Interop.Excel.Range)objsheet.get_Range("A1", "F30"); objsheet.Cells[27, 3] = "√正常"; objsheet.Cells[28, 3] = "√正常"; System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange); m_objRange = null; objbook.Save(); appworkbook.Close(); //调用方法关闭excel进程 appexcel.Visible = true; return(true); } else { return(false); } }
private void btneExcelReport_Click(object sender, EventArgs e) { try { Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Sheets xlSheets = null; Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null; //Create Excel Sheets xlSheets = ExcelApp.Sheets; xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); DataTable dtAccount = new DataTable(); foreach (DataGridViewColumn col in dgvCompany.Columns) { dtAccount.Columns.Add(col.HeaderText); } foreach (DataGridViewRow row in dgvCompany.Rows) { DataRow dRow = dtAccount.NewRow(); foreach (DataGridViewCell cell in row.Cells) { dRow[cell.ColumnIndex] = cell.Value; } dtAccount.Rows.Add(dRow); } dtAccount.Columns.Remove("dealerId"); dtAccount.Columns.Remove("जन्मदिनांक"); dtAccount.Columns.Remove("लिंग"); dtAccount.Columns.Remove("व्यापाऱ्याचा पत्ता"); dtAccount.Columns.Remove("ई-मेल"); dtAccount.TableName = "Dealer Report"; xlWorksheet.Name = dtAccount.TableName; for (int j = 1; j < dtAccount.Columns.Count + 1; j++) { ExcelApp.Cells[1, j] = dtAccount.Columns[j - 1].ColumnName; } // Storing Each row and column value to excel sheet for (int k = 0; k < dtAccount.Rows.Count; k++) { for (int l = 0; l < dtAccount.Columns.Count; l++) { ExcelApp.Cells[k + 2, l + 1] = dtAccount.Rows[k].ItemArray[l].ToString(); } } ExcelApp.Columns.AutoFit(); ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete(); ExcelApp.Visible = true; } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public void create_file() { System.Diagnostics.Process p = new System.Diagnostics.Process(); bool createdFile = false; try { excelApp1 = new Microsoft.Office.Interop.Excel.Application(); string filename = foldername + "First Article Sheet Template.xlsx"; // string filename = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\MIM_Menlo Demand TrackerTemplate.xlsx"; //filename = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\Demand Tracker1_"+DateTime.Now.Month.ToString("00") + "_" + DateTime.Now.Day.ToString("00") + "_" + DateTime.Now.Year.ToString("00") + DateTime.Now.Hour.ToString("00") + DateTime.Now.Minute.ToString("00") + DateTime.Now.Second.ToString("00") +".xlsx"; string workbookPath = filename.Replace("file:", "\\"); filename1 = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\First Article Sheet Template_" + DateTime.Now.Month.ToString("00") + "_" + DateTime.Now.Day.ToString("00") + "_" + DateTime.Now.Year.ToString("00") + DateTime.Now.Hour.ToString("00") + DateTime.Now.Minute.ToString("00") + DateTime.Now.Second.ToString("00") + ".xlsx"; excelWorkbooks = excelApp1.Workbooks; excelWorkbook = excelWorkbooks.Open(workbookPath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); // excelWorkbook = excelApp1.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); excelWorkbook.SaveAs(filename1, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, false, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value); excelSheets = excelWorkbook.Worksheets; sheet = excelSheets[1] as Microsoft.Office.Interop.Excel.Worksheet; String strQuery = " SELECT GDOC_FirstArticleSchedule.RefreshDate,GDOC_FirstArticleSchedule.ProdSite, GDOC_FirstArticleSchedule.SFSRouting, GDOC_FirstArticleSchedule.PartMap, GDOC_FirstArticleSchedule.WorkInstr, GDOC_FirstArticleSchedule.QADoc, GDOC_FirstArticleSchedule.Pack, GDOC_FirstArticleSchedule.Tooling, GDOC_FirstArticleSchedule.OSPPO, GDOC_FirstArticleSchedule.Barcode, GDOC_FirstArticleSchedule.Test, GDOC_FirstArticleSchedule.WorkOrdNum, GDOC_FirstArticleSchedule.AsmGPN, GDOC_FirstArticleSchedule.description, GDOC_FirstArticleSchedule.Qty, GDOC_FirstArticleSchedule.SchedAsmDate, GDOC_FirstArticleSchedule.SchedWeek, GDOC_FirstArticleSchedule.DemandSource, GDOC_FirstArticleSchedule.CommCode, GDOC_FirstArticleSchedule.RefGPN, GDOC_FirstArticleSchedule.PlanGroup, GDOC_FirstArticleSchedule.FormFactor, GDOC_FirstArticleSchedule.Platform, GDOC_FirstArticleSchedule.BP_BuildID, GDOC_FirstArticleSchedule.BP_TLAGPN, GDOC_FirstArticleSchedule.BP_POPDate, GDOC_FirstArticleSchedule.BP_FinalDestination, GDOC_FirstArticleSchedule.BP_Cluster, GDOC_FirstArticleSchedule.BP_ProjCode, GDOC_FirstArticleSchedule.BP_Owner FROM MIMDIST.dbo.GDOC_FirstArticleSchedule GDOC_FirstArticleSchedule ORDER BY GDOC_FirstArticleSchedule.SchedAsmDate "; DataSet dsFirstArticle = getdataSet(strQuery); int col = 0; for (int i = 0; i < dsFirstArticle.Tables[0].Rows.Count; i++) { //copyPasteRangeExcel(("C" + j).ToString(), ("AL" + j).ToString()); for (int j = 0; j < dsFirstArticle.Tables[0].Columns.Count; j++) { sheet.Cells[i + 3, j + 1] = dsFirstArticle.Tables[0].Rows[i][j].ToString(); } } excelWorkbook.Save(); // excelWorkbook.Close(true, false, Type.Missing); createdFile = true; p.StartInfo.FileName = filename1; //p.StartInfo.WindowStyle = ProcessWindowStyle.Minimized p.Start(); // System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp1); } catch (Exception e) { Console.Write(e.Message.ToString()); } finally { //Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal /* excelWorkbook.Close(true, false, Type.Missing); * System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheets); * System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook); * System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbooks); * System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp1);*/ } }
public void exportToExcel(DataTable dt, string filename) { try { Microsoft.Office.Interop.Excel.Application excelApp1 = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp1.Workbooks.Add(); Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets; Microsoft.Office.Interop.Excel.Worksheet sheet = excelSheets[1] as Microsoft.Office.Interop.Excel.Worksheet; int col = 0; for (int i = 0; i < dt.Rows.Count; i++) { //copyPasteRangeExcel(("C" + j).ToString(), ("AL" + j).ToString()); for (int j = 0; j < dt.Columns.Count; j++) { sheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString(); } } excelWorkbook.Save(); excelWorkbook.SaveAs(filename); } catch (Exception e) { } }
private void exportToExcel1(string filename) { System.Diagnostics.Process p = new System.Diagnostics.Process(); Microsoft.Office.Interop.Excel.Application excelApp1 = new Microsoft.Office.Interop.Excel.Application(); try { Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp1.Workbooks.Add(); Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets; Microsoft.Office.Interop.Excel.Worksheet sheet = excelSheets[1] as Microsoft.Office.Interop.Excel.Worksheet; string cnt = "data source=mvint;initial catalog=proddist;user id=sa;password=mimi~100;pooling=true;max pool size=100;min pool size=1;"; Microsoft.Office.Interop.Excel.QueryTable oQryTable = sheet.QueryTables.Add("OLEDB;Provider=sqloledb;" + cnt, sheet.Range["A1"], sql); oQryTable.RefreshStyle = Microsoft.Office.Interop.Excel.XlCellInsertionMode.xlInsertEntireRows; // 2; //' xlInsertEntireRows = 2 oQryTable.Refresh(false); excelApp1.DisplayAlerts = false; excelWorkbook.Save(); excelWorkbook.SaveAs(filename); p.StartInfo.FileName = filename; p.Start(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp1); } catch (Exception e) { string Message = e.Message; MessageBox.Show(Message); if (excelApp1 != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp1); } } finally { GC.Collect(); p.Close(); } }
private void buttonExcel_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); excelApp.Visible = true; BringToFront(); string workbookPath = Application.StartupPath + "\\templates/zhurnal.xls"; Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets; string currentSheet = "Бланк"; Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(currentSheet); int i = 1; int i2 = 4; foreach (ListViewItem lvi in listViewZhurnal.Items) { i = 1; foreach (ListViewItem.ListViewSubItem lvs in lvi.SubItems) { excelWorksheet.Cells[i2, i] = lvs.Text; i++; } i2++; } }
public ExcelWriter(string _fileName) { fileName = _fileName; if(File.Exists(FilePath)) throw new ApplicationException("File already exists: " + FilePath); File.Create(FilePath); app = new Microsoft.Office.Interop.Excel.Application(); Console.Error.WriteLine("Connected to Excel"); wbs = app.Workbooks; wb = wbs.Add(1); wb.Activate(); wss = wb.Sheets; ws = (Microsoft.Office.Interop.Excel.Worksheet)wss.get_Item(1); Console.Error.WriteLine("Excel Worksheet Initialized"); }
public void getallSheet(string FileName) { Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); try { string workbookPath = FileName.Replace("file:\\", ""); excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); sheetnum = 0; excelSheets = excelWorkbook.Worksheets; foreach (Microsoft.Office.Interop.Excel.Worksheet worksheet in excelWorkbook.Worksheets) { if (worksheet.Name.Contains("Rack Delivery Plan")) { ListItem1 lst = new ListItem1(worksheet.Name, worksheet.Name); lstWorksheet.Items.Add(lst); } } } catch (Exception e) { MessageBox.Show(e.Message.ToString()); } finally { //Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal excelWorkbook.Close(true, Type.Missing, Type.Missing); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); } }
//get a list of Row objects for a sheet private List <Row> GetRows(Sheet sheet) { List <Row> rows = new List <Row>(); Book book = _books.Where(b => b.Id == sheet.BookId).FirstOrDefault(); Microsoft.Office.Interop.Excel._Workbook xlBook = _xlBooks.Open(book.Path); Microsoft.Office.Interop.Excel.Sheets xlSheets = xlBook.Worksheets; Microsoft.Office.Interop.Excel._Worksheet xlSheet = (Microsoft.Office.Interop.Excel._Worksheet)xlSheets[sheet.Index]; int id = _rows.Count(); int row = 2; while (xlSheet.get_Range(CellName(row, 1)).Value2 != null) { Row r = new Row() { Id = id++, SheetId = sheet.Id, Index = row, Data = GetRowData(GetRowVals(xlSheet, 1), GetRowVals(xlSheet, row++)) }; rows.Add(r); } _xlBooks.Close(); return(rows); }
private void button1_Click(object sender, EventArgs e) { openFileDialog1.ShowDialog(); if (openFileDialog1.FileName.Length > 0) { string sFileName = openFileDialog1.FileName; Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); app.Visible = true; Microsoft.Office.Interop.Excel.Workbooks wbks = app.Workbooks; Microsoft.Office.Interop.Excel._Workbook _wbk = wbks.Open(openFileDialog1.FileName); Microsoft.Office.Interop.Excel.Sheets shs = _wbk.Sheets; Microsoft.Office.Interop.Excel._Worksheet _wsh = (Microsoft.Office.Interop.Excel._Worksheet)shs[1]; foreach (DataRowView row in m_viewTagValue) { if (row["DataSourcesNo"].ToString() == "1") { _wsh.Cells[int.Parse(row["id"].ToString()), 6] = row["TagValue"].ToString(); } } string sExt = sFileName.Substring(sFileName.LastIndexOf('.')); sFileName = sFileName.Substring(0, sFileName.LastIndexOf('.')); sFileName = sFileName + "_2" + sExt; _wbk.SaveAs(sFileName); MessageBox.Show("完成!"); } }
private void browse_Click(object sender, EventArgs e) { using (OpenFileDialog openFileDialog = new OpenFileDialog() { Filter = "Excel Workbook|*.xlsx|Excel 97-2003 Workbook|*.xls" }) { if (openFileDialog.ShowDialog() == DialogResult.OK) { Cursor.Current = Cursors.WaitCursor; this._open.Enabled = true; fileName.Text = openFileDialog.FileName; theWorkbook = ExcelObj.Workbooks.Open(fileName.Text); Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets; cmbSheet.Items.Clear(); var numSheet = sheets.Count; for (int i = 1; i <= numSheet; i++) { Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(i); cmbSheet.Items.Add(worksheet.Name); cmbSheet.SelectedIndex = 0; } } } }
private void simpleButton5_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Application oExcel_12 = null; Microsoft.Office.Interop.Excel.Workbook oBook = null; Microsoft.Office.Interop.Excel.Sheets oSheetsColl = null; Microsoft.Office.Interop.Excel.Worksheet oSheet = null; Microsoft.Office.Interop.Excel.Range oRange = null; Object oMissing = System.Reflection.Missing.Value; oExcel_12 = new Microsoft.Office.Interop.Excel.Application(); oExcel_12.Visible = true; oExcel_12.UserControl = true; oBook = oExcel_12.Workbooks.Add(oMissing); oSheetsColl = oExcel_12.Worksheets; oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheetsColl.get_Item(1); for (int j = 0; j < dataGridView1.Columns.Count; j++) { oRange = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, j + 1]; oRange.Value2 = dataGridView1.Columns[j].HeaderText; } for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) { for (int j = 0; j < dataGridView1.Columns.Count; j++) { oRange = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i + 2, j + 1]; oRange.Value2 = dataGridView1[j, i].Value; } } oBook = null; oExcel_12 = null; GC.Collect(); }
private void btnExcelConvert_Click(object sender, EventArgs e) { try { Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Sheets xlSheets = null; Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null; //Create Excel Sheets xlSheets = ExcelApp.Sheets; xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); // System.Data.DataTable dat = (System.Data.DataTable)(dgvSaleBook.DataSource); DataTable dat = new DataTable(); foreach (DataGridViewColumn col in dgvSaleBook.Columns) { dat.Columns.Add(col.HeaderText); } foreach (DataGridViewRow row in dgvSaleBook.Rows) { DataRow dRow = dat.NewRow(); foreach (DataGridViewCell cell in row.Cells) { dRow[cell.ColumnIndex] = cell.Value; } dat.Rows.Add(dRow); } dat.TableName = "Sale Book 2"; xlWorksheet.Name = dat.TableName; for (int j = 1; j < dat.Columns.Count + 1; j++) { ExcelApp.Cells[1, j] = dat.Columns[j - 1].ColumnName; } // Storing Each row and column value to excel sheet for (int k = 0; k < dat.Rows.Count; k++) { for (int l = 0; l < dat.Columns.Count; l++) { ExcelApp.Cells[k + 2, l + 1] = dat.Rows[k].ItemArray[l].ToString(); } } ExcelApp.Columns.AutoFit(); ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete(); ExcelApp.Visible = true; } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private Microsoft.Office.Interop.Excel.Worksheet GetWorksheet(string WorksheetName) { Microsoft.Office.Interop.Excel.Sheets sheets = xlWB.Worksheets; Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets[WorksheetName]; ReleaseObject(sheets); return(sheet); }
public static bool writeDatatableToElt(DataTable dt) { Microsoft.Office.Interop.Excel.Application appexcel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks appworkbook = (Microsoft.Office.Interop.Excel.Workbooks)appexcel.Workbooks; if (File.Exists(Application.StartupPath + "\\bk\\检测信息.xls")) { File.Copy(Application.StartupPath + "\\bk\\检测信息.xls", Application.StartupPath + "\\检测信息.xls", true); appworkbook.Open(Application.StartupPath + "\\检测信息.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Microsoft.Office.Interop.Excel.Workbook objbook = appworkbook.get_Item(1); Microsoft.Office.Interop.Excel.Sheets msheets = (Microsoft.Office.Interop.Excel.Sheets)objbook.Worksheets; Microsoft.Office.Interop.Excel._Worksheet objsheet = (Microsoft.Office.Interop.Excel._Worksheet)msheets.get_Item(1); Microsoft.Office.Interop.Excel.Range m_objRange = (Microsoft.Office.Interop.Excel.Range)objsheet.get_Range("A1", "D5"); for (int i = 0; i < dt.Rows.Count; i++) { objsheet.Cells[i + 4, 1] = (i + 1).ToString(); objsheet.Cells[i + 4, 2] = ""; objsheet.Cells[i + 4, 3] = mainPanel.stationinfmodel.STATIONNAME; objsheet.Cells[i + 4, 4] = dt.Rows[i]["LINEID"].ToString(); objsheet.Cells[i + 4, 5] = DateTime.Parse(dt.Rows[i]["JCSJ"].ToString()).ToString("yyyy-MM-dd HH:mm"); objsheet.Cells[i + 4, 6] = dt.Rows[i]["CLHP"].ToString(); objsheet.Cells[i + 4, 7] = dt.Rows[i]["PP"].ToString() + dt.Rows[i]["XH"].ToString(); objsheet.Cells[i + 4, 8] = dt.Rows[i]["SYXZ"].ToString(); objsheet.Cells[i + 4, 9] = dt.Rows[i]["CLLX"].ToString(); objsheet.Cells[i + 4, 10] = "'" + dt.Rows[i]["CLSBM"].ToString(); objsheet.Cells[i + 4, 11] = DateTime.Parse(dt.Rows[i]["ZCRQ"].ToString()).ToString("yyyy/MM/dd"); objsheet.Cells[i + 4, 12] = dt.Rows[i]["RLZL"].ToString(); objsheet.Cells[i + 4, 13] = "'" + dt.Rows[i]["LSH"].ToString(); switch (dt.Rows[i]["JCFF"].ToString()) { case "ASM": objsheet.Cells[i + 4, 14] = "稳态工况法"; break; case "SDS": objsheet.Cells[i + 4, 14] = "双怠速法"; break; case "JZJS": objsheet.Cells[i + 4, 14] = "加载减速法"; break; case "ZYJS": objsheet.Cells[i + 4, 14] = "自由加速法"; break; case "VMAS": objsheet.Cells[i + 4, 14] = "简易瞬态法"; break; case "LZ": objsheet.Cells[i + 4, 14] = "滤纸法"; break; default: break; } } //保存工作表 System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange); m_objRange = null; //调用方法关闭excel进程 appexcel.Visible = true; return(true); } else { return(false); } }
/// <summary> /// activate the excel application /// </summary> /// protected virtual void ActivateExcel() { _excelApplication = new Microsoft.Office.Interop.Excel.Application(); _workBooks = (Microsoft.Office.Interop.Excel.Workbooks)_excelApplication.Workbooks; _workBook = (Microsoft.Office.Interop.Excel._Workbook)(_workBooks.Add(_value)); _excelSheets = (Microsoft.Office.Interop.Excel.Sheets)_workBook.Worksheets; _excelSheet = (Microsoft.Office.Interop.Excel._Worksheet)(_excelSheets.get_Item(1)); }
/// <summary> /// Create Excel application parameters instances /// </summary> private void CreateExcelRef() { _excelApp = new Microsoft.Office.Interop.Excel.Application(); _books = (Microsoft.Office.Interop.Excel.Workbooks)_excelApp.Workbooks; _book = (Microsoft.Office.Interop.Excel._Workbook)(_books.Add(_optionalValue)); _sheets = (Microsoft.Office.Interop.Excel.Sheets)_book.Worksheets; _sheet = (Microsoft.Office.Interop.Excel._Worksheet)(_sheets.get_Item(1)); }
private void btnExport_Click(object sender, EventArgs e) { try { System.Data.DataTable ds = new System.Data.DataTable(); Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Sheets xlSheets = null; Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null; //Create Excel Sheets xlSheets = ExcelApp.Sheets; xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); foreach (DataGridViewColumn col in dgvStockRegister.Columns) { ds.Columns.Add(col.HeaderText); } foreach (DataGridViewRow row in dgvStockRegister.Rows) { DataRow dRow = ds.NewRow(); foreach (DataGridViewCell cell in row.Cells) { dRow[cell.ColumnIndex] = cell.Value; } ds.Rows.Add(dRow); } ds.Columns.Remove("प्रकार"); ds.Columns.Remove("कंपनी"); ds.Columns.Remove("बॅच नंबर उत्पदन अंतिम मुदत दिनांक"); ds.TableName = "Item Ledger Report"; xlWorksheet.Name = ds.TableName; for (int j = 1; j < ds.Columns.Count + 1; j++) { ExcelApp.Cells[1, j] = ds.Columns[j - 1].ColumnName; } // Storing Each row and column value to excel sheet for (int k = 0; k < ds.Rows.Count; k++) { for (int l = 0; l < ds.Columns.Count; l++) { ExcelApp.Cells[k + 2, l + 1] = ds.Rows[k].ItemArray[l].ToString(); } } ExcelApp.Columns.AutoFit(); ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete(); ExcelApp.Visible = true; } catch (Exception ac) { MessageBox.Show(ac.Message); } }
public override void CreateWorksheet(string worksheetname) { Microsoft.Office.Interop.Excel.Sheets sheets = xlWB.Worksheets; Microsoft.Office.Interop.Excel.Worksheet newWS = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); newWS.Name = worksheetname; ReleaseObject(sheets); ReleaseObject(newWS); }
public bool TableToExcel_AE(String strfilename) { try { dataGridView1.DataSource = null; dataGridView1.DataSource = Global.db.NangSuatDeSo_AE(firstDateTime, lastDateTime); Microsoft.Office.Interop.Excel.Application App = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook book = App.Workbooks.Open(strfilename, 0, true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); Microsoft.Office.Interop.Excel.Sheets _sheet = (Microsoft.Office.Interop.Excel.Sheets)book.Sheets; Microsoft.Office.Interop.Excel.Worksheet wrksheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet; int h = 1; foreach (DataGridViewRow dr in dataGridView1.Rows) { wrksheet.Cells[h + 2, 1] = h; wrksheet.Cells[h + 2, 2] = dr.Cells[0].Value != null ? dr.Cells[0].Value.ToString() : ""; wrksheet.Cells[h + 2, 3] = dr.Cells[1].Value != null ? dr.Cells[1].Value.ToString() : ""; wrksheet.Cells[h + 2, 4] = dr.Cells[2].Value != null ? dr.Cells[2].Value.ToString() : ""; wrksheet.Cells[h + 2, 5] = dr.Cells[3].Value != null ? dr.Cells[3].Value.ToString() : ""; wrksheet.Cells[h + 2, 6] = dr.Cells[4].Value != null ? dr.Cells[4].Value.ToString() : ""; wrksheet.Cells[h + 2, 7] = dr.Cells[5].Value != null ? dr.Cells[5].Value.ToString() : ""; wrksheet.Cells[h + 2, 8] = dr.Cells[6].Value != null ? dr.Cells[6].Value.ToString() : ""; h++; } string savePath = ""; SaveFileDialog saveFileDialog1 = new SaveFileDialog(); saveFileDialog1.Title = "Save Excel Files"; saveFileDialog1.Filter = "Excel files (*.xls)|*.xls"; saveFileDialog1.FileName = "NangSuat_AE_" + dtp_FirstDay.Value.Day + "-" + dtp_EndDay.Value.Day; saveFileDialog1.RestoreDirectory = true; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { book.SaveCopyAs(saveFileDialog1.FileName); book.Saved = true; savePath = Path.GetDirectoryName(saveFileDialog1.FileName); App.Quit(); } else { MessageBox.Show("Lỗi khi xuất excel!"); return(false); } Process.Start(savePath); return(true); } catch (Exception ex) { MessageBox.Show(ex.Message); return(false); } }
public void ReadExistingExcel(DataTable dt, string templatePath, string Exportpath, string late1, string late2, string late3) { try { //string path = @"C:\Users\zkritsawan.p\Desktop\Project ใช้จริง\testreportMo\TestReport\report\encotemplate.xls"; string path = templatePath; oXL = new Microsoft.Office.Interop.Excel.Application(); oXL.Visible = true; oXL.DisplayAlerts = false; Microsoft.Office.Interop.Excel.Workbooks workbooks = oXL.Workbooks; mWorkBook = workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); //Get all the sheets in the workbook mWorkSheets = oXL.Worksheets; //Get the allready exists sheet mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item(1); Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange; int colCount = range.Columns.Count; //int rowCount = range.Rows.Count; int rowCount = 5; mWSheet1.Cells[rowCount, 7] = late1; mWSheet1.Cells[rowCount, 8] = late2; mWSheet1.Cells[rowCount, 9] = late3; for (int r = 0; r < dt.Rows.Count; r++) { mWSheet1.Cells[rowCount + r + 1, 2] = dt.Rows[r]["NAME"].ToString(); mWSheet1.Cells[rowCount + r + 1, 7] = dt.Rows[r]["Case1"].ToString(); mWSheet1.Cells[rowCount + r + 1, 8] = dt.Rows[r]["Case2"].ToString(); mWSheet1.Cells[rowCount + r + 1, 9] = dt.Rows[r]["Case3"].ToString(); mWSheet1.Cells[rowCount + r + 1, 6] = string.Format("=E{0}/30", rowCount + r + 1); mWSheet1.Cells[rowCount + r + 1, 11] = string.Format("=E{0}-J{0}", rowCount + r + 1); mWSheet1.Cells[rowCount + r + 1, 10] = string.Format("=((G{0}*F{0})+(H{0}*F{0})+(I{0}*F{0}))", rowCount + r + 1); } // string pathSave = @"C:\Users\zkritsawan.p\Desktop\Project ใช้จริง\testreportMo\TestReport\report\EncoReport(1).xls"; string pathSave = Exportpath; mWorkBook.SaveAs(pathSave, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value); mWSheet1 = null; mWorkBook = null; oXL.Quit(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); } catch (Exception e) { } }
private void ExportSSummarizeList(object[] parameter) { string exepath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase; string file = exepath + "MyData\\导出表.xlsx"; Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks wbks = app.Workbooks; Microsoft.Office.Interop.Excel.Workbook wbk = wbks.Add(file); Microsoft.Office.Interop.Excel.Sheets sheets = wbk.Sheets; Microsoft.Office.Interop.Excel.Worksheet wsh = sheets["Sheet1"]; int i = 0; if (KPISSummarizelist.Count > 0) { foreach (KPIIndicators item in KPISSummarizelist) { wsh.Cells[i + 2, 1].value = item.Name; wsh.Cells[i + 2, 2].value = item.Period; for (int j = 0; j < 10; j++) { wsh.Cells[i + 2, j * 2 + 3].value = item.IndicatorNames[j]; wsh.Cells[i + 2, j * 2 + 4].value = item.Indicators[j]; } wsh.Cells[i + 2, 23].value = item.Score; wsh.Cells[i + 2, 24].value = item.Grade; i++; } if (!Directory.Exists(FilePathS + "\\汇总")) { Directory.CreateDirectory(FilePathS + "\\汇总"); } string fileSummarize = FilePathS + "\\汇总\\干部汇总表.xlsx"; if (File.Exists(fileSummarize)) { try { File.Delete(fileSummarize); } catch (Exception ex) { MessageBox.Show(ex.Message); throw; } } wsh.SaveAs(fileSummarize); wbks.Close(); MessageBox.Show("生成完毕!", "", MessageBoxButton.OK, MessageBoxImage.Information); Process.Start("explorer.exe ", FilePathS + "\\汇总"); } else { MessageBox.Show("没有数据", "出错了", MessageBoxButton.OK, MessageBoxImage.Error); } }
private void ReadPlanFile(object[] parameter) { if (Directory.Exists(FilePath)) { FileInfo fileInfo = new FileInfo(FilePath); DirectoryInfo folder = new DirectoryInfo(FilePath); KPISummarizelist = new ObservableCollection <KPIIndicators>(); ErrorInfos = new ObservableCollection <ErrorInfo>(); //try //{ Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); foreach (FileInfo file in folder.GetFiles("*.xls")) { if (!file.Name.Contains("~$")) { Microsoft.Office.Interop.Excel.Workbooks wbks = app.Workbooks; Microsoft.Office.Interop.Excel.Workbook wbk = wbks.Add(file.FullName); Microsoft.Office.Interop.Excel.Sheets sheets = wbk.Sheets; for (int i = 1; i <= sheets.Count; i++) { Microsoft.Office.Interop.Excel.Worksheet wsh = sheets.Item[i]; string sheetname = wsh.Name; if (wsh.Cells[17, 2].value == "被考核者姓名" && wsh.Cells[16, 2].value == "绩效考核指标总得分" && wsh.Cells[5, 12].value == "考核得分") { KPISummarizelist.Add(GetKPIIndicators(file, wsh)); } else { ErrorInfo error = new ErrorInfo(); error.FileName = file.Name; error.SheetName = sheetname; error.Information = "未使用标准表格"; ErrorInfos.Add(error); } } wbks.Close(); } } //} //catch (Exception ex) //{ // MessageBox.Show(ex.Message); // throw; //} } else { MessageBox.Show("当前选择的路径不存在", "出错了", MessageBoxButton.OK, MessageBoxImage.Error); } }
public bool create_file() { System.Diagnostics.Process p = new System.Diagnostics.Process(); bool createdFile = false; try { excelApp1 = new Microsoft.Office.Interop.Excel.Application(); string filename = foldername + "BOLReportData.xlsx"; // string filename = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\MIM_Menlo Demand TrackerTemplate.xlsx"; //filename = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\Demand Tracker1_"+DateTime.Now.Month.ToString("00") + "_" + DateTime.Now.Day.ToString("00") + "_" + DateTime.Now.Year.ToString("00") + DateTime.Now.Hour.ToString("00") + DateTime.Now.Minute.ToString("00") + DateTime.Now.Second.ToString("00") +".xlsx"; string workbookPath = filename.Replace("file:", "\\"); filename1 = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\BOL Data _" + DateTime.Now.Month.ToString("00") + "_" + DateTime.Now.Day.ToString("00") + "_" + DateTime.Now.Year.ToString("00") + DateTime.Now.Hour.ToString("00") + DateTime.Now.Minute.ToString("00") + DateTime.Now.Second.ToString("00") + ".xlsx"; excelWorkbooks = excelApp1.Workbooks; excelWorkbook = excelWorkbooks.Open(workbookPath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); // excelWorkbook = excelApp1.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); excelWorkbook.SaveAs(filename1, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, false, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value); excelSheets = excelWorkbook.Worksheets; sheet = excelSheets[1] as Microsoft.Office.Interop.Excel.Worksheet; String strQuery = " SELECT [ShipDate] ,[BOL#] ,[TruckId] ,[Seal#] ,[PalletId] ,[Container#] ,[Order#] ,[GPN] ,[Qty] ,[OwnerCode] ,[ProjectCode] FROM [PRODDIST].[dbo].[XPO_BOL] where [ShipDate] >getdate()-2 order by [ShipDate] "; DataSet dsFirstArticle = getdataSet(strQuery); int col = 0; for (int i = 0; i < dsFirstArticle.Tables[0].Rows.Count; i++) { //copyPasteRangeExcel(("C" + j).ToString(), ("AL" + j).ToString()); for (int j = 0; j < dsFirstArticle.Tables[0].Columns.Count; j++) { sheet.Cells[i + 2, j + 1] = dsFirstArticle.Tables[0].Rows[i][j].ToString(); } } excelWorkbook.Save(); excelWorkbook.Close(true, false, Type.Missing); } catch (Exception e) { Console.Write(e.Message.ToString()); } finally { //Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal /* excelWorkbook.Close(true, false, Type.Missing); * System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheets); * System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook); * System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbooks); * System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp1);*/ } return(true); }
private void button1_Click(object sender, EventArgs e) { string path = @"D:\NYT_Lasttry\MyConfigScapy.xls"; oXL = new Microsoft.Office.Interop.Excel.Application(); oXL.Visible = true; oXL.DisplayAlerts = false; mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); //Get all the sheets in the workbook mWorkSheets = mWorkBook.Worksheets; //Get the allready exists sheet mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("Sheet1"); Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange; mWSheet1.Cells[3, 2] = this.textBox1.Text; mWSheet1.Cells[4, 2] = this.textBox2.Text; mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value); mWorkBook.Close(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value); mWSheet1 = null; mWorkBook = null; oXL.Quit(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); this.mainForm.label1.Text = "IP = " + this.textBox1.Text;; this.mainForm.label2.Text = "Payload = " + this.textBox2.Text;; int ExitCode; Process p = new Process(); p.StartInfo.UseShellExecute = true; p.StartInfo.FileName = "D:\\NYT_Lasttry\\batch.bat"; p.Start(); p.WaitForExit(); ExitCode = p.ExitCode; p.Close(); this.Close(); }
public void ExcelOut(string id) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook wb = null; Microsoft.Office.Interop.Excel.Sheets sh = null; excel.Application.Workbooks.Add(true); excel.Cells[1, 1] = "箱码"; excel.Cells[1, 2] = "物流码"; excel.Cells[1, 3] = "积分码";; string content = " 箱码 物流码 积分码\r\n"; BLL.BarCode bllBarCode = new BLL.BarCode(); System.Data.DataTable dt = bllBarCode.GetBarCodeCreateDetailByBarCodeCreateRecordID(id); BLL.BarCodeCreateRecord bllBarCodeCreateRecord = new BLL.BarCodeCreateRecord(); int num = 2; string FileName; FileName = Guid.NewGuid().ToString(); object OsPath, FilePathxls, FilePathtxt; //表格文件的保存地址 OsPath = Server.MapPath("/DownLoad/"); //这里我定义了桌面的地址 FilePathxls = string.Format("{0}{1}", (string)OsPath, FileName + ".xls"); //为了自定义地址,加了一个地址拼接 FilePathtxt = string.Format("{0}{1}", (string)OsPath, FileName + ".txt"); //为了自定义地址,加了一个地址拼接 FileStream fs = File.Create(FilePathtxt.ToString()); foreach (DataRow dr in dt.Rows) { excel.Cells[num, 1] = dr["BoxCode"]; excel.Cells[num, 2] = dr["LogisticsCode"]; excel.Cells[num, 3] = dr["IntegralCode"]; content += dr["BoxCode"].ToString() + " " + dr["LogisticsCode"] + " " + dr["IntegralCode"] + "\r\n"; num++; } byte[] buffer = Encoding.UTF8.GetBytes(content); fs.Write(buffer, 0, buffer.Length); //bllBarCodeCreateRecord.Update(id, "//DownLoad//" + FileName); bllBarCodeCreateRecord.Update(id, FileName); excel.Visible = false; excel.DisplayAlerts = false; excel.AlertBeforeOverwriting = false; excel.ActiveWorkbook.SaveAs(FilePathxls); excel.Quit(); //Response.Write("/DownLoad/" + FileName + ".txt"); }
/// <summary> /// Save the string To the excel File. /// </summary> /// <param name="ToFile">String Of TOLEDO Format</param> /// <param name="SavedFlag">Boolean Flag that Shows this string Saved or not</param> public void ReadExistingExcel(String ToFile, Boolean SavedFlag) { try { string path = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\Resources\\iTOLEDO_Loggs.xls"; oXL = new Microsoft.Office.Interop.Excel.Application(); // oXL.Visible = true; oXL.DisplayAlerts = false; try { mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); } catch (Exception) { // mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); } //Get all the sheets in the workbook mWorkSheets = mWorkBook.Worksheets; //Get the allready exists sheet mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("Sheet1"); Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange; int colCount = range.Columns.Count; int rowCount = range.Rows.Count; ///Add Columns To the Excel file. for (int index = 1; index < 2; index++) { mWSheet1.Cells[rowCount + index, 1] = DateTime.Now.ToString("MMM dd, yyyy hh:mm:ss tt"); mWSheet1.Cells[rowCount + index, 2] = ToFile; mWSheet1.Cells[rowCount + index, 3] = SavedFlag.ToString(); } mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value); mWSheet1 = null; mWorkBook = null; oXL.Quit(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); } catch (Exception) { } }
//取得sheet by name public static Microsoft.Office.Interop.Excel.Worksheet GetSheetByName(Microsoft.Office.Interop.Excel.Application myExcel, string name) { Microsoft.Office.Interop.Excel.Worksheet sheet = null; Microsoft.Office.Interop.Excel.Sheets sheets = myExcel.Sheets; foreach (Microsoft.Office.Interop.Excel.Worksheet s in sheets) { if (s.Name == name) { sheet = s; break; } } return(sheet); }
private void ExportDoneTable(object[] parameter) { string exepath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase; string file = exepath + "MyData\\绩效汇总表-模板.xls"; Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks wbks = app.Workbooks; Microsoft.Office.Interop.Excel.Workbook wbk = wbks.Add(file); Microsoft.Office.Interop.Excel.Sheets sheets = wbk.Sheets; Microsoft.Office.Interop.Excel.Worksheet wsh = sheets["sheet1"]; int i = 0; foreach (KpiInfoViewModel kpi in SelectedKpiInfos) { wsh.Cells[i + 2, 1].value = kpi.KPIInfo.Period; wsh.Cells[i + 2, 2].value = kpi.KPIInfo.Zone; wsh.Cells[i + 2, 3].value = kpi.KPIInfo.Name; wsh.Cells[i + 2, 4].value = kpi.KPIInfo.DonePoint_Target; wsh.Cells[i + 2, 5].value = kpi.KPIInfo.FirstVirsionPoint_Target; wsh.Cells[i + 2, 6].value = kpi.KPIInfo.PatentDegree_Target; wsh.Cells[i + 2, 7].value = kpi.KPIInfo.InTimePortion_Target; wsh.Cells[i + 2, 8].value = kpi.KPIInfo.Score_Cowork; wsh.Cells[i + 2, 9].value = kpi.KPIInfo.Score_Passion; wsh.Cells[i + 2, 10].value = kpi.KPIInfo.Score_Selfdrive; wsh.Cells[i + 2, 11].value = kpi.KPIInfo.DonePoint; wsh.Cells[i + 2, 12].value = kpi.KPIInfo.PatentDegree; wsh.Cells[i + 2, 13].value = kpi.KPIInfo.InTimePortion; wsh.Cells[i + 2, 14].value = kpi.KPIInfo.FirstVirsionPoint; wsh.Cells[i + 2, 15].value = kpi.KPIInfo.Position; wsh.Cells[i + 2, 16].value = kpi.KPIInfo.Examiner; wsh.Cells[i + 2, 17].value = kpi.KPIInfo.Examiner_Positon; wsh.Cells[i + 2, 18].value = kpi.KPIInfo.Score; wsh.Cells[i + 2, 20].value = kpi.KPIInfo.Comment; i++; } string fileSummarize = "C:\\WORK\\绩效考核\\绩效考核汇总表.xlsx"; if (File.Exists(fileSummarize)) { File.Delete(fileSummarize); } wsh.SaveAs(fileSummarize); wbks.Close(); MessageBox.Show("生成完毕!", "", MessageBoxButton.OKCancel, MessageBoxImage.Information); }
//populate a list of Sheet objects for a workbook private void LoadSheets(Book b, Microsoft.Office.Interop.Excel._Workbook xlBook) { Microsoft.Office.Interop.Excel.Sheets xlSheets = xlBook.Worksheets; foreach (Microsoft.Office.Interop.Excel._Worksheet w in xlSheets) { Sheet s = new Sheet() { Id = _sheets.Count(), BookId = b.Id, Name = w.Name }; LoadRows(s, w); _sheets.Add(s); } }
public void create_file() { System.Diagnostics.Process p = new System.Diagnostics.Process(); try { String strQuery = " select Top 1 [SharedDocFolderName],TLA_PartNum,TLADesc FROM [MIMDIST].[dbo].[BaconBOM_for_SharedDoc] where [SharedDocFolderName] is not null SELECT [AssemblyType] ,[AssyGPN],[CommodityCode] ,[SupplyType],[CompGPN] ,[LeadTime] ,[BOMQty] ,[UnitCost],[ExtCost],[CompDesc] FROM [MIMDIST].[dbo].[BaconBOM_for_SharedDoc] order by sortorder,COMPGPN "; DataSet ds = getDataSet(strQuery); DataTable dt = dtPopulate.Clone(); dt = ds.Tables[1];// ultraGridBOM.DataSource as DataTable; Collection = ds.Tables[0].Rows[0][0].ToString(); TLA_PartNum = ds.Tables[0].Rows[0][1].ToString(); TLA_PartDesc = ds.Tables[0].Rows[0][2].ToString(); excelApp1 = new Microsoft.Office.Interop.Excel.Application(); // string workbookPath = "E:\\Ruchi\\MaintainGoogleDoc\\MaintainGoogleDoc\\DemandTrackerSheet.xls";// Microsoft.Office.Interop.Excel.XlPlatform.xlWindows filename = "\\\\mvfile\\MV REPORTS\\Master Data & Forms\\PlanningExecutionForms\\Templates\\shared Doc Costed BOM Template.xlsx"; excelApp1 = new Microsoft.Office.Interop.Excel.Application(); filename1 = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\" + TLA_PartNum + ".xlsx";//Menlo_Metrics_Kit_SnapshotTemplate.xlsx"; string workbookPath = filename.Replace("file:\\", ""); excelWorkbook = excelApp1.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); excelWorkbook.SaveAs(filename1, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, false, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value); excelSheets = excelWorkbook.Worksheets; sheet = excelSheets[1] as Microsoft.Office.Interop.Excel.Worksheet; sheet.Cells[1, 2] = TLA_PartNum; sheet.Cells[2, 2] = TLA_PartDesc; updateSheet(dt); excelWorkbook.Save(); } catch (Exception e) { MessageBox.Show(e.Message.ToString()); } finally { //Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal excelWorkbook.Close(true, false, Type.Missing); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp1); } }
private void CleanUp() { lock(sync) { if(wb != null) { wb.Close(Type.Missing, Type.Missing, Type.Missing); Release(wb); wb = null; } if(wbs != null) { Release(wbs); wbs = null; } if(ws != null) { Release(ws); ws = null; } if(wss != null) { Release(wss); wss = null; } if(app != null) { app.Quit(); Release(app); app = null; } } }