void frmdrill_FormClosed(object sender, FormClosedEventArgs e) { if (frmdrill.DialogResult == System.Windows.Forms.DialogResult.OK) { System.Data.DataTable dt = frmdrill._dataTable; Excel.DataTable dtEx; Excel.Workbook _wbook = (Excel.Workbook)ExcelApp.ActiveWorkbook; _wbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); Excel.Worksheet _wsheet = (Excel.Worksheet)ExcelApp.ActiveSheet; for (int i = 0; i < dt.Columns.Count; i++) { Excel.Range _range = (Excel.Range)_wsheet.Cells[1, i + 1]; _range.Font.Bold = true; _range.Value = dt.Columns[i].ColumnName; } for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { Excel.Range _range = (Excel.Range)_wsheet.Cells[i + 2, j + 1]; _range.Value = dt.Rows[i][j]; } } string add = _wsheet.Name + "!R1C1:R" + (dt.Rows.Count + 1) + "C" + dt.Columns.Count; _wbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); Excel.Worksheet _wpivotsheet = (Excel.Worksheet)ExcelApp.ActiveSheet; string des = _wpivotsheet.Name + "!R3C1"; _wbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, add).CreatePivotTable(des, "PivotTable1", Type.Missing);//, Excel. Excel.XlPivotTableVersionList.xlPivotTableVersion10); } }
public static void ConvertExcel(string openPath, string savePath, ref string errText) { errText = string.Empty; try { //将xml文件转换为标准的Excel格式 Object Nothing = Missing.Value; //由于yongCOM组件很多值需要用Missing.Value代替 Excel.Application ExclApp = new Excel.ApplicationClass(); // 初始化 Excel.Workbook ExclDoc = ExclApp.Workbooks.Open(openPath, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing); //打开Excl工作薄 try { Object format = Excel.XlFileFormat.xlWorkbookNormal; //获取Excl 2007文件格式 ExclApp.DisplayAlerts = false; ExclDoc.SaveAs(savePath, format, Nothing, Nothing, Nothing, Nothing, Excel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing); //保存为Excl 2007格式 } catch (Exception ex) { errText = ex.Message; } finally { ExclDoc.Close(Nothing, Nothing, Nothing); ExclApp.Quit(); } } catch (Exception ex) { errText = ex.Message; } }
private void ExcelButton_Click(object sender, EventArgs e) { excelApp.Visible = true; Excel.Workbook wb = excelApp.Workbooks.Add(); Excel.Worksheet sheet = wb.ActiveSheet; for (int i = 1; i <= 10; i++) { for (int j = 1; j <= 10; j++) { sheet.Cells[i, j].Value = i * j; if (i == j) { sheet.Cells[i, j].Font.Bold = true; } } } sheet.Range["A1:J1"].Font.Bold = true; sheet.Range["A1:A10"].Font.Bold = true; sheet.Range["A1:J1"].Interior.Color = Color.LightGray; sheet.Range["A1:A10"].Interior.Color = Color.LightGray; float left = (float)sheet.Range["A15"].Left; float top = (float)sheet.Range["A15"].Top; sheet.Shapes.AddPicture(Application.StartupPath + @"/../../logo.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, left, top, 100, 100); }
private void button1_Click(object sender, System.EventArgs e) { string str_Sql, errorstring; useExcel = true; int i = 0, j = 0; excelApp = new Excel.ApplicationClass(); Excel.Workbook excelBook = excelApp.Workbooks.Add(1); Excel.Worksheet excelSheet = (Excel.Worksheet)excelBook.Worksheets[1]; if (conn.ds.Tables[0].Rows.Count == 0) { MessageBox.Show("无可打印内容!", "提醒!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); return; } try { //设置表头 excelSheet.Cells[1, 1] = comboBox_QuXian.Text + " 共有学生 " + label_Count.Text + " 个"; excelSheet.Cells[2, 1] = "学校类型"; excelSheet.Cells[2, 2] = "区县代码"; excelSheet.Cells[2, 3] = "区县"; excelSheet.Cells[2, 4] = "人数"; //设置表头格式 excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 2]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 2]).Font.Bold = true; str_Sql = "select * FROM View_QuXian_Student_Statistics WHERE 区县代码='" + comboBox_QuXian.SelectedValue.ToString() + "'"; errorstring = conn.Fill(str_Sql); DataGrid1.SetDataBinding(conn.ds, "TableIn"); //填充表中各单元格 for (i = 1; i <= conn.ds.Tables[0].Rows.Count; i++) { for (j = 1; j <= conn.ds.Tables[0].Columns.Count; j++) { excelSheet.Cells[i + 2, j] = "'" + conn.ds.Tables[0].Rows[i - 1][j - 1].ToString(); } } //设置报表表格为最适应宽度 excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Select(); excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Columns.AutoFit(); excelSheet.Cells[i + 3, 1] = "完中初中部"; excelSheet.Cells[i + 3, 2] = label_Class_Type_ID_chu.Text; excelSheet.Cells[i + 3, 3] = "人"; excelSheet.Cells[i + 4, 1] = "完中高中部"; excelSheet.Cells[i + 4, 2] = label_Class_Type_ID_gao.Text; excelSheet.Cells[i + 4, 3] = "人"; excelApp.Visible = true; //excelApp.Quit(); } catch { throw new Exception("Excel error"); } }
public static DataTable DataTableFromXlsx(string Path, string password = "") { Excel.Application objXL = null; Excel.Workbook objWB = null; objXL = new Excel.Application(); objWB = objXL.Workbooks.Open(Path); Excel.Worksheet objSHT = objWB.Worksheets[1]; int rows = objSHT.UsedRange.Rows.Count; int cols = objSHT.UsedRange.Columns.Count; DataTable dt = new DataTable(); int noofrow = 1; for (int c = 1; c <= cols; c++) { string colname = objSHT.Cells[1, c].Text; dt.Columns.Add(colname); noofrow = 2; } for (int r = noofrow; r <= rows; r++) { DataRow dr = dt.NewRow(); for (int c = 1; c <= cols; c++) { dr[c - 1] = objSHT.Cells[r, c].Text; } dt.Rows.Add(dr); } objWB.Close(); objXL.Quit(); return(dt); }
/// <summary> /// Export data table to Excel like DataGridTableStyle /// </summary> /// <param name="view"></param> /// <param name="headers"></param> /// <param name="indexes"></param> /// <param name="startRow"></param> /// <param name="startCol"></param> /// <remarks> /// Author: PhatLT. FPTSS. /// Created date: 14/02/2011 /// </remarks> public void ExportToExcel(DataView view, string[] headers, int[] indexes, int startRow, int startCol) { Excel.Application excelApp = null; Excel.Workbook excelBook = null; Excel.Worksheet sheet = null; try { excelApp = new Excel.Application(); excelBook = excelApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); sheet = (Excel.Worksheet)excelBook.Worksheets[1]; ExportToExcel(view, headers, indexes, startRow, startCol, sheet); excelApp.Visible = true; } catch (Exception ex) { log.Error(ex.Message, ex); if (excelApp != null) { excelApp.Visible = true; } } }
public void GenerateReport(IEnumerable <StudentDto> students) { Excel.Application app = new Excel.Application(); app.DisplayAlerts = false; Excel.Workbook book_template = app.Workbooks.Open(@"шаблон_отчета.xlsx"); Excel._Worksheet sheet_template = book_template.Sheets["отчет"]; foreach (var ob in students) { //1. Создаем объкт LearnerReport из БД LearnerReport report = new LearnerReport { SNS = $"{ob.surname} {ob.name} {ob.SecondName}", SchoolName = ob.SchoolName, ClassName = ob.ClassName, TestResult5 = ob.TestResult5 }; //2. Экспорт объкта LearnerReport в шаблон xlsx sheet_template.Range["C4"].Value2 = report.SNS; sheet_template.Range["C5"].Value2 = report.SchoolName; sheet_template.Range["C6"].Value2 = report.ClassName; sheet_template.Range["C9"].Value2 = report.TestResult5; //3. Сохраняем полученный файл в .pdf на рабочем столе string file_name = $@"{Environment.GetFolderPath(Environment.SpecialFolder.Desktop)}\{report.SNS}.pdf"; sheet_template.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, file_name); } book_template.Close(0); book_template = null; app.Quit(); app = null; }
/// <summary> /// 清理所有正在使用的资源。 /// </summary> protected override void Dispose(bool disposing) { if (useExcel == true) { try { excelApp.Application.Workbooks.Close(); excelApp.Application.Quit(); excelApp.Quit(); excelBook = null; excelSheet = null; excelApp = null; GC.Collect(); } catch { throw new Exception("Excel 关闭错误!"); } } if (disposing) { if (components != null) { components.Dispose(); } } base.Dispose(disposing); }
private void GeData() { string connection = @"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Server=192.168.0.1\SQL2005;DataBase=Test;UID=sa;PWD=pass@123"; string command = "SELECT Column1,Column2,Column3,Column4,cast((Column5*1.00)/Column4 AS DECIMAL(16,2)) as Column5 FROM PivotData"; Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Excel.Workbook workbook = (Microsoft.Office.Interop.Excel.Workbook)app.Workbooks.Add(Type.Missing); Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet; Excel.PivotCache pivotCache = app.ActiveWorkbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, (Excel.Range)sheet.get_Range("A1", "E10")); pivotCache.Connection = connection; pivotCache.MaintainConnection = true; pivotCache.CommandText = command; pivotCache.CommandType = Excel.XlCmdType.xlCmdSql; Excel.PivotTables pivotTables = (Excel.PivotTables)sheet.PivotTables(Type.Missing); Excel.PivotTable pivotTable = pivotTables.Add(pivotCache, app.ActiveCell, "PivotTable1", Type.Missing, Type.Missing); pivotTable.SmallGrid = false; pivotTable.ShowTableStyleRowStripes = true; pivotTable.TableStyle2 = "PivotStyleLight1"; Excel.PivotFields rowField = (Excel.PivotFields)pivotTable.PivotFields(Type.Missing); int fieldCount = rowField.Count; for (int i = 1; i <= fieldCount; i++) { if ("Colunm" + i != "Colunm2" && "Colunm" + i != "Colunm5") { Excel.PivotField field = (Excel.PivotField)pivotTable.PivotFields("Column" + i); field.Orientation = Excel.XlPivotFieldOrientation.xlRowField; } } pivotTable.AddDataField(pivotTable.PivotFields("Column4"), "Sum of Column4", Excel.XlConsolidationFunction.xlSum); }
public void checkin(Excel.Workbook activeWorkbook) { try { if (MessageBox.Show(resources.GetString("sure_check_in"), resources.GetString("checkin"), MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation) == DialogResult.OK) { object saveChanges = true; object missing = Type.Missing; String localFileName = activeWorkbook.FullName; activeWorkbook.Close(saveChanges, missing, missing); // Always we save document docXML.refresh(); // Refresh document list if (docXML.isOpenKMDocument(localFileName)) { OKMDocument oKMDocument = docXML.getOpenKMDocument(localFileName); docXML.remove(oKMDocument); DocumentLogic.checkin(oKMDocument, configXML.getHost(), configXML.getUser(), configXML.getPassword()); if (File.Exists(localFileName)) { File.Delete(localFileName); } } } } catch (Exception e) { String errorMsg = "OpenKMExcelAddIn - (checkinButton_Click)\n" + e.Message + "\n\n" + e.StackTrace; MessageBox.Show(errorMsg, "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } }
//把Excel文档转换为Pdf文档 #region private static void ExcelToPdf(string ExcelFileName, string PdfFileName) { //Object missing = System.Reflection.Missing.Value; //Object ReadOnly = (object)true; //ET.Application app = new ET.Application(); //app.Visible = false; //ET.workbook doc = (ET.workbook)app.Workbooks.Open(ExcelFileName, missing, ReadOnly, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); //doc.ExportPdf(PdfFileName, "", ""); //doc.Close(missing, missing, missing); //app.Quit(); //System.Runtime.InteropServices.Marshal.ReleaseComObject(app); //app = null; Object missing = System.Reflection.Missing.Value; Object ReadOnly = (object)true; Excel.Application app = new Excel.Application(); app.Visible = false; Excel.Workbook doc = (Excel.Workbook)app.Workbooks.Open(ExcelFileName, missing, ReadOnly, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); doc.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, PdfFileName); doc.Close(missing, missing, missing); app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); app = null; }
public void ReadSample() { Excel.Application excelApp = new Excel.Application(); if (excelApp != null) { Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(@"C:\test.xls", 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelWorkbook.Sheets[1]; Excel.Range excelRange = excelWorksheet.UsedRange; int rowCount = excelRange.Rows.Count; int colCount = excelRange.Columns.Count; for (int i = 1; i <= rowCount; i++) { for (int j = 1; j <= colCount; j++) { Excel.Range range = (excelWorksheet.Cells[i, 1] as Excel.Range); string cellValue = range.Value.ToString(); //do anything } } excelWorkbook.Close(); excelApp.Quit(); } }
/// <summary> /// WORKBOOK EXTENSION METHOD /// This method return the name of the class that we /// are embedded inside of. /// If we are not embedded it return null. /// If there is any exception it return null. /// If the container cannot be accessed it returns UNKNOWN. /// </summary> /// <param name="PobjWb"></param> /// <returns></returns> public static string EmbedClassName(this Excel.Workbook PobjWb) { try { IOleObject LobjOleObject = ((object)PobjWb) as IOleObject; IOleClientSite LobjPpClientSite; // get the client site LobjOleObject.GetClientSite(out LobjPpClientSite); if (LobjPpClientSite != null) { IOleContainer LobjPpContainer; LobjPpClientSite.GetContainer(out LobjPpContainer); if (LobjPpContainer != null) { return(LobjPpContainer.GetType().Name); } else { // something wrong - container is not valid return("UNKNOWN"); } } else { // not embedded return(null); } } catch (Exception ex) { Debug.Print(ex.ToString()); return(null); // failed } }
public static void PrintPreview(string fileName, PageSetup p) { Excel.Application excelApp = null; Excel.Workbook workBook = null; try { object objOpt = System.Reflection.Missing.Value; excelApp = ExcelInit(); workBook = excelApp.Workbooks.Open(fileName, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt); if (p != null) { //if (p.TopMargin != 0 || p.LeftMargin != 0 || p.RightMargin != 0 || p.BottomMargin != 0) { int sheetCount = workBook.Sheets.Count; Excel.Worksheet workSheet = null; int i = 0; while (i <= sheetCount) { try { workSheet = workBook.Sheets[i]; break; } catch { i++; } } workSheet.PageSetup.LeftMargin = p.GetLeftMargin(); workSheet.PageSetup.RightMargin = p.GetRightMargin(); workSheet.PageSetup.TopMargin = p.GetTopMargin(); workSheet.PageSetup.BottomMargin = p.GetBottomMargin(); //workSheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; } } excelApp.Visible = true; workBook.PrintPreview(objOpt); excelApp.Visible = false; } catch (Exception ex) { throw ex; } finally { if (workBook != null) { workBook.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook); workBook = null; } ExcelTerminal(excelApp); } }
private void BtnOpen_Click(object sender, EventArgs e) { Excel.Application excel = new Excel.Application(); string strPath = this.txtExcel.Text; Excel.Workbook wBook = excel.Application.Workbooks.Add(strPath); excel.Visible = true; }
public void CreateExcelFile() { UserControl(false); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel.Workbook)(m_objBooks.Add(miss)); //m_objSheets = (Excel.Worksheets)m_objBook.Sheets; m_objSheet = (Excel.Worksheet)m_objBook.ActiveSheet; }
private void button1_Click(object sender, System.EventArgs e) { string str_Sql; useExcel = true; int i = 0, j = 0; excelApp = new Excel.ApplicationClass(); Excel.Workbook excelBook = excelApp.Workbooks.Add(1); Excel.Worksheet excelSheet = (Excel.Worksheet)excelBook.Worksheets[1]; if (conn.ds.Tables[0].Rows.Count == 0) { MessageBox.Show("无可打印内容!", "提醒!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); return; } try { //设置表头 String str_SchoolName = conn.School_IDtoWhat(comboBox_School.SelectedValue.ToString(), "School_Name"); str_Sql = "Select * from Teacher WHERE School_ID='" + comboBox_School.SelectedValue.ToString() + "'"; string errorstring = conn.Fill(str_Sql); excelSheet.Cells[1, 1] = str_SchoolName + " 共有教师 " + conn.ds.Tables[0].Rows.Count.ToString() + " 个"; excelSheet.Cells[2, 1] = "学校代码"; excelSheet.Cells[2, 2] = "学校名称"; excelSheet.Cells[2, 3] = "学历"; excelSheet.Cells[2, 4] = "人数"; //设置表头格式 excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 4]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 4]).Font.Bold = true; str_Sql = "Select * from View_Teacher_Statistics WHERE View_Teacher_Statistics.学校代码='" + comboBox_School.SelectedValue.ToString() + "'"; errorstring = conn.Fill(str_Sql); DataGrid1.SetDataBinding(conn.ds, "TableIn"); //填充表中各单元格 for (i = 1; i <= conn.ds.Tables[0].Rows.Count; i++) { for (j = 1; j <= conn.ds.Tables[0].Columns.Count; j++) { excelSheet.Cells[i + 2, j] = "'" + conn.ds.Tables[0].Rows[i - 1][j - 1].ToString(); } } //设置报表表格为最适应宽度 excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Select(); excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Columns.AutoFit(); excelApp.Visible = true; //excelApp.Quit(); } catch { throw new Exception("Excel error"); } }
private void button3_Click(object sender, System.EventArgs e) { useExcel = true; excelApp = new Excel.ApplicationClass(); //Excel.Workbook excelBook = excelApp.Workbooks.Add(1); //Excel.Worksheet excelSheet = (Excel.Worksheet)excelBook.Worksheets[1]; if (conn.ds.Tables[0].Rows.Count == 0) { MessageBox.Show("无可打印内容!", "提醒!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); return; } try { //创建应用程序,工作表,工作簿 int i, j = 0; excelApp.Visible = true; //设置表头 excelSheet.Cells[2, 1] = "身份证号码"; excelSheet.Cells[2, 2] = "姓名"; excelSheet.Cells[2, 3] = "性别"; excelSheet.Cells[2, 4] = "出生日期"; excelSheet.Cells[2, 5] = "参加工作时间"; excelSheet.Cells[2, 6] = "学历"; excelSheet.Cells[2, 7] = "职称"; excelSheet.Cells[2, 8] = "职务"; excelSheet.Cells[2, 9] = "毕业院校"; excelSheet.Cells[2, 10] = "毕业日期"; excelSheet.Cells[2, 11] = "所学专业"; excelSheet.Cells[2, 12] = "是否在编"; excelSheet.Cells[2, 13] = "是否专任教师"; excelSheet.Cells[2, 14] = "担任课程"; //设置表头格式 excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 14]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 14]).Font.Bold = true; //填充表中各单元格 for (i = 1; i <= conn.ds.Tables[0].Rows.Count; i++) { for (j = 1; j <= conn.ds.Tables[0].Columns.Count; j++) { excelSheet.Cells[i + 2, j] = "'" + conn.ds.Tables[0].Rows[i - 1][j - 1].ToString(); } } //设置报表表格为最适应宽度 excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Select(); excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Columns.AutoFit(); } catch { throw new Exception("Excel error"); } }
protected override void Execute(CodeActivityContext context) { Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Excel.Workbook Nuovo = xlApp.Workbooks.Add(); Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(PathWorkbookInput.Get(context)); var nomeFile = NomeFile.Get(context); var nomeSheet = NomeSheet.Get(context); }
protected override void Execute(CodeActivityContext context) { Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Excel.Workbook Nuovo = xlApp.Workbooks.Add(); Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(PathWorkbookInput.Get(context)); var nomeFile = NomeFile.Get(context); var nomeSheet = NomeSheet.Get(context); Nuovo.SaveAs(System.IO.Directory.GetCurrentDirectory() + "\\" + NomeFile + NomeSheet, Excel.XlFileFormat.xlOpenXMLWorkbook);
public void DoSomething() { xl = new Excel.Application(); xl.Visible = true; book = xl.Workbooks.Add(Type.Missing); // These variables are locally scoped, so we need not worry about them. // Notice I don't care about using two dots. Excel.Range rng = book.Worksheets[1].UsedRange; }
private void creat_Click(object sender, EventArgs e) { // textBox2.Text = UInt96.Parse(textBox1.Text).Inc().ToString(); saveFileDialog1.Title = "MAC地址生成工具----百思威科技"; saveFileDialog1.Filter = "Excel(*.xls)|*.xls"; saveFileDialog1.FileName = string.Format("MAC地址_{0}", DateTime.Now.ToString("yyyyMMdd")); DialogResult result = saveFileDialog1.ShowDialog(); Excel._Application xlapp = new Excel.Application(); Excel.Workbook xlbook = xlapp.Workbooks.Add(true); Excel.Worksheet xlsheet = (Excel.Worksheet)xlbook.Worksheets[1]; // MessageBox.Show(textBox2.Text); int RowCount = Convert.ToInt32(textBox2.Text); int RowIndex = 0; string val = textBox1.Text; val = val.Replace(":", ""); for (int i = 0; i < RowCount; i++) { RowIndex++; if (i != 0) { if (radioButton1.Checked) { val = UInt96.Parse(val).Inc().ToString(":"); } else { val = UInt96.Parse(val).Inc().ToString(""); } xlsheet.Cells[RowIndex, 1] = val; } else { if (radioButton1.Checked) { string[] sArray = Regex.Split(val, @"(\w{2})"); List <string> listTemp = new List <string>(); foreach (string s in sArray) { if (string.IsNullOrEmpty(s)) { continue; } listTemp.Add(s); } string[] newlist = listTemp.ToArray(); val = String.Join(":", newlist); } xlsheet.Cells[RowIndex, 1] = val; } } xlbook.Saved = true; xlbook.SaveCopyAs(saveFileDialog1.FileName); xlapp.Quit(); MessageBox.Show("导出成功!", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); }
public static void getExcelFile() { //Create COM Objects. Create a COM object for everything that is referenced Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\E56626\Desktop\Teddy\VS2012\Sandbox\sandbox_test - Copy - Copy.xlsx"); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; int rowCount = xlRange.Rows.Count; //IMPORTANT SECTION var dictionary = new Dictionary <string, List <string> >(); //iterate over the rows and columns as it appears in the file //excel is not zero based!! for (int i = 1; i <= rowCount; i++) { //it would be nice if we add some null checking to this variables. Check the article again var col1 = xlRange.Cells[i, 1].Value2.ToString(); var col2 = xlRange.Cells[i, 2].Value2.ToString(); if (dictionary.ContainsKey(col1)) { var existingList = dictionary[col1]; existingList.Add(col2); } else { var newList = new List <string>(); newList.Add(col2); dictionary.Add(col1, newList); } } //Do whatever you'd like with the dictionary //END OF IMPORTANT SECTION //cleanup GC.Collect(); GC.WaitForPendingFinalizers(); //rule of thumb for releasing com objects: // never use two dots, all COM objects must be referenced and released individually // ex: [somthing].[something].[something] is bad //release com objects to fully kill excel process from running in the background Marshal.ReleaseComObject(xlRange); Marshal.ReleaseComObject(xlWorksheet); //close and release xlWorkbook.Close(); Marshal.ReleaseComObject(xlWorkbook); //quit and release xlApp.Quit(); Marshal.ReleaseComObject(xlApp); }
public void CleanUp() { book = null; xl.Quit(); xl = null; GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); }
void Application_WorkbookBeforeClose(Excel.Workbook Wb, ref bool Cancel) { if (Application.Workbooks.Count > 1 || window.Handle == IntPtr.Zero) { return; } Cancel = true; window.ReleaseHandle(); Dispatcher.CurrentDispatcher.BeginInvoke(new MethodInvoker(Application.Quit), null); }
public static Excel.Workbook OpenBook(Excel.Application excelInstance, string fileName, bool readOnly, bool editable, bool updateLinks) { Excel.Workbook book = excelInstance.Workbooks.Open( fileName, updateLinks, readOnly, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, editable, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); return(book); }
/// <summary> /// /// </summary> /// <param name="sender"></param> /// <param name="e"></param> /// new code private void Window_Loaded(object sender, RoutedEventArgs e) { List <Food> listFoods = new List <Food>(); using Excel = Microsoft.Office.Interop.Excel; Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"sandbox_test.xlsx"); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; }
/// <summary> /// /// </summary> private void InputExcel() { // if(m_dt.Rows.Count==0) // else // { // m_exl=new Excel.ApplicationClass(); // m_exl.Workbooks.Add ( true ); // for(int j=0;j<m_dt.Columns.Count;j++) // { // for(int z=0;z<m_Title.Length;z++) // { // if(m_dt.Columns[j].ColumnName==m_Title[z].name) // { // m_exl.Cells[2,j+1]=m_Title[z].title; // for(int i=0;i<m_dt.Rows.Count;i++) // { // m_exl.Cells[i+3,j+1]=m_dt.Rows[i][j]; // } // break; // } // // } // continue; // } // m_exl.Visible=true; // } // else // { string str = Path.GetDirectoryName(Application.ExecutablePath) + "\\report\\GRDB.xls"; m_exl = new Excel.ApplicationClass(); // m_exl.Workbooks.Add(true); Excel.Workbook eWork = m_exl.Workbooks.Add(str); //true) eWork.SaveCopyAs("ll"); m_exl.Cells[2, 1] = m_time; for (int i = 1; i < m_Title.Length; i++) { for (int j = 0; j < m_dt.Columns.Count; j++) { if (m_dt.Columns[j].ColumnName == m_Title[i].name) { // m_exl.Cells[2,i]=m_Title[i].title; for (int z = 0; z < m_dt.Rows.Count; z++) { m_exl.Cells[z + 5, i] = m_dt.Rows[z][j]; } } } } m_exl.Visible = true; // } }
static void CreateInternalCOA(CSV.Common common, Excel.FinishedGoods finishedGoods, Excel.Workbook.CustomerName customerName) { string input; int daysBackToInclude; do { System.Console.SetCursorPosition(0, 30); System.Console.Write(new string(' ', System.Console.WindowWidth)); System.Console.SetCursorPosition(0, 30); System.Console.Write("Days before " + DateTime.Now.ToShortDateString() + ": "); input = System.Console.ReadLine(); } while (int.TryParse(input, out daysBackToInclude) == false); // A string is used as the hashset can't easily differentiate arrays with matching contents HashSet <string> set = new HashSet <string>(); foreach (List <string> line in common.DelimitedMicroResults) { for (int i = 0; i < daysBackToInclude; i++) { if (line[5] == DateTime.Now.AddDays(i * -1).ToString("M/d/yy") || line[5] == DateTime.Now.AddDays(i * -1).ToShortDateString()) { if (line[16] == "K1") { if (line[10].Contains('/') || line[10].Contains('\\')) { char[] delimit = { '/', '\\' }; string reformattedEntry = line[10].Split(delimit)[0]; reformattedEntry += " & "; reformattedEntry += line[10].Split(delimit)[1]; set.Add(Convert.ToDateTime(line[9]).ToString("M-d-yy") + "," + reformattedEntry); } else { set.Add(Convert.ToDateTime(line[9]).ToString("M-d-yy") + "," + line[10]); } } } } } foreach (string productAndDateCombo in set) { Excel.Workbook workbook = new Excel.Workbook(common.DelimitedTitrationResults, common.DelimitedMicroResults, customerName, finishedGoods.Contents, common.Recipes, Excel.Workbook.CustomerType.Internal); workbook.InternalCOAData = productAndDateCombo.Split(new char[] { ',' }); Thread thread = new Thread(workbook.Generate); thread.Start(); } }
/// <summary> /// 打开Excel文件 /// </summary> /// <param name="filename">路径</param> public void OpenExcelFile(string filename) { UserControl(false); m_objExcel.Workbooks.Open(filename, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel.Workbook)m_objExcel.ActiveWorkbook; //m_objSheets = (Excel.Worksheets)m_objBook.Sheets; m_objSheet = (Excel.Worksheet)m_objBook.ActiveSheet; }
/// <summary> /// エクセルデータの操作を行う /// </summary> /// <param name="path">xlsファイルパス</param> public ExcelController(string path) { exPath = path; oXls = new Excel.Application(); //Excel画面を表示しない oXls.Visible = false; //Excelファイルをオープンする oWBook = (Excel.Workbook)(oXls.Workbooks.Open(exPath)); }
//********************************************************************** /// <summary> /// New excel file from the giving path /// </summary> public void create(string strPath) { if(File.Exists(strPath)) { File.Delete(strPath); } object Nothing = Missing.Value; object format = Excel.XlFileFormat.xlWorkbookNormal; xBook = excelMain.Workbooks.Add(Nothing); xSheet = (Excel.Worksheet)xBook.Sheets[1]; //MessageBox.Show(strPath); xBook.SaveAs(strPath,Nothing,Nothing,Nothing,Nothing,Nothing, Excel.XlSaveAsAccessMode.xlExclusive,Nothing,Nothing,Nothing,Nothing); }
// 打开文件 public bool Open(string file_name) { if (!file_name.Contains(".xlsx") && !file_name.Contains(".xls")) return false; if (ExcelApp == null) return false; try { Book = ExcelApp.Workbooks.Open(file_name); return true; } catch (System.Exception) { return false; } }
// 创建空白工作薄 public bool Creat(string file_name) { if (!file_name.Contains(".xlsx") && !file_name.Contains(".xls")) return false; if (ExcelApp == null) return false; try { Book = ExcelApp.Workbooks.Add(Type.Missing); Excel.XlFileFormat file_format = file_name.Contains(".xlsx") ? Excel.XlFileFormat.xlOpenXMLWorkbook : Excel.XlFileFormat.xlXMLSpreadsheet; Book.SaveAs(file_name, FileFormat: file_format, AccessMode: Excel.XlSaveAsAccessMode.xlNoChange); return true; } catch (System.Exception) { return false; } }
/// <summary> /// 构造函数,将一个已有Excel工作簿作为模板,并指定输出路径 /// </summary> /// <param name="templetFilePath">Excel模板文件路径</param> /// <param name="outputFilePath">输出Excel文件路径</param> public ExcelHelper(string templetFilePath, string outputFilePath) { if (templetFilePath == null) throw new Exception("Excel模板文件路径不能为空!"); if (outputFilePath == null) throw new Exception("输出Excel文件路径不能为空!"); if (!File.Exists(templetFilePath)) throw new Exception("指定路径的Excel模板文件不存在!"); this.templetFile = templetFilePath; this.outputFile = outputFilePath; //创建一个Application对象并使其可见 beforeTime = DateTime.Now; app = new Excel.ApplicationClass(); app.Visible = true; afterTime = DateTime.Now; //打开模板文件,得到WorkBook对象 workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); //得到WorkSheet对象 workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1); }
private void Dispose() { workBook.Close(null, null, null); app.Workbooks.Close(); app.Quit(); if (range != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(range); range = null; } if (range1 != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(range1); range1 = null; } if (range2 != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(range2); range2 = null; } if (textBox != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(textBox); textBox = null; } if (workSheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet); workSheet = null; } if (workBook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook); workBook = null; } if (app != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(app); app = null; } GC.Collect(); this.KillExcelProcess(); }//end Dispose
/// <summary> /// 构造函数,新建一个工作簿 /// </summary> public ExcelHelper() { //创建一个Application对象并使其可见 beforeTime = DateTime.Now; app = new Excel.ApplicationClass(); //app.Visible = true; 不自动打开 afterTime = DateTime.Now; //新建一个WorkBook workBook = app.Workbooks.Add(Type.Missing); //得到WorkSheet对象 workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1); }
/// <summary> /// 构造函数,打开一个已有的工作簿 /// </summary> /// <param name="fileName">Excel文件名</param> public ExcelHelper(string fileName) { if (!File.Exists(fileName)) throw new Exception("指定路径的Excel文件不存在!"); //创建一个Application对象并使其可见 beforeTime = DateTime.Now; app = new Excel.ApplicationClass(); app.Visible = true; afterTime = DateTime.Now; //打开一个WorkBook workBook = app.Workbooks.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //得到WorkSheet对象 workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1); }
private void WebBrowser_DocumentCompleted(object sender, System.Windows.Forms.WebBrowserDocumentCompletedEventArgs e) { object[] args = new object[4]; args[0] = SHDocVw.OLECMDID.OLECMDID_HIDETOOLBARS; args[1] = SHDocVw.OLECMDEXECOPT.OLECMDEXECOPT_DONTPROMPTUSER; args[2] = missing; args[3] = missing; object axWebBrowser = this.WebBrowser.ActiveXInstance; axWebBrowser.GetType().InvokeMember("ExecWB", System.Reflection.BindingFlags.InvokeMethod, null, axWebBrowser, args); object oApplication = axWebBrowser.GetType().InvokeMember("Document", System.Reflection.BindingFlags.GetProperty, null, axWebBrowser, null); wbb = (Excel.Workbook)oApplication; }
// 关闭当前工作薄 public bool Close() { Sheet = null; if (Book != null) { Book.Close(); Book = null; } return true; }
public void ExcelOpen(string EXPath, bool ReadOnly) { CurrentWorkBook = ExcelApp.Workbooks.Open(@EXPath, Type.Missing, ReadOnly, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing //,Type.Missing, Type.Missing//for officeXP ); CurrentWorkSheet = (Excel.Worksheet)CurrentWorkBook.Worksheets.get_Item(1); }
public void Dispose() { if (mUseTemporaryInstance) { CurrentWorkBook = null; CurrentWorkSheet = null; mCurrentCells = null; ExcelApp.Quit(); GC.Collect(); } if (oldCI != null) Thread.CurrentThread.CurrentCulture = oldCI; }
public void SelectWorkBook(int workBookIndex) { CurrentWorkBook = ExcelApp.Workbooks[workBookIndex]; CurrentWorkSheet = (Excel.Worksheet)CurrentWorkBook.Worksheets.get_Item(1); }
//=========================================== // WORKBOOKS WORK //=========================================== /// <summary> /// Adds needed new WorkBook with several sheets, /// and selected this book as Active. /// </summary> /// <param name="sheetsCount"></param> public void AddWorkBook(int sheetsCount) { if (sheetsCount < 1) sheetsCount = 1; ExcelApp.SheetsInNewWorkbook = sheetsCount; ExcelApp.Workbooks.Add(Type.Missing); CurrentWorkBook = ExcelApp.ActiveWorkbook; CurrentWorkSheet = (Excel.Worksheet)CurrentWorkBook.Worksheets.get_Item(1); }
//********************************************************************** /// <summary> /// Open excel file from the giving path /// </summary> public void open(string strPath) { xBook = excelMain.Workbooks.Open(strPath,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value); xSheet = (Excel.Worksheet)xBook.Sheets[1]; }