public bool openTemplate() { if (File.Exists(templateFilePath)) { _wbk = wbks.Add(templateFilePath); shs = _wbk.Sheets; return(true); } return(false); }
/// <summary> /// 格式化未供完表格 /// </summary> /// <param name="file"></param> public static void FormatWGWExcel(string file) { Application app = new Application(); app.DisplayAlerts = false; app.Visible = false; app.UserControl = true; Workbooks workbooks = app.Workbooks; _Workbook workbook = workbooks.Add(file); Sheets sheets = workbook.Sheets; Worksheet worksheet = (Worksheet)sheets.get_Item(1); int recordCount = 2;//从第二行开始判断 //查找数据行数 while (true) { if (Method.IsNumber(worksheet.Cells[recordCount, 1].TEXT)) { recordCount++; } else { break; } } MergeWGWCell(ref worksheet, 2, recordCount, 2); //MergePHCell(ref worksheet, 2, recordCount, 6); workbook.SaveAs(file); app.Quit(); }
/// <summary> /// 创建Book、sheet /// </summary> public void CreateExceFile() { UserControl(false); m_objBooks = (Workbooks)m_objExcel.Workbooks; m_objBook = (Workbook)(m_objBooks.Add(miss)); sheet = (Worksheet)m_objBook.ActiveSheet; }
protected override void HandleWriteStarted() { // There are problems with exporting to excel on non-English machines // See: http://www.made4dotnet.com/Default.aspx?tabid=141&aid=15 iOriginalCulture = System.Threading.Thread.CurrentThread.CurrentCulture; ChangeToEnglishCulture(); iExcelApp = new Microsoft.Office.Interop.Excel.Application(); iExcelApp.DisplayAlerts = false; Workbooks workbooks = iExcelApp.Workbooks; workbooks.Add(XlWBATemplate.xlWBATWorksheet); iWorkbook = workbooks.get_Item(workbooks.Count); // Create summary sheet Sheets sheets = iWorkbook.Worksheets; iSummary = (Worksheet)sheets.get_Item(1); CreateSummary(iSummary); // Create pages iPages.Add(new Pages.PageUnchanged(iEngine.ResultsUnchanged, iWorkbook)); iPages.Add(new Pages.PageUnique(iEngine.Reconstructor2, iEngine.ResultsUniqueInHeap2, iWorkbook, "Unique in Heap 2", 2, 1)); iPages.Add(new Pages.PageUnique(iEngine.Reconstructor1, iEngine.ResultsUniqueInHeap1, iWorkbook, "Unique in Heap 1", 1, 2)); iPages.Add(new Pages.PageSimpleListing(iEngine.ResultsUnchangedLengthButDifferentContents, iWorkbook, "Same length, different contents")); iPages.Add(new Pages.PageSimpleListing(iEngine.ResultsChanged, iWorkbook, "Changed", false)); }
public static bool SaveExcel(string savePath, System.Data.DataTable tripTable, System.Data.DataTable testTable) { try { Application excelApp = new Application(); Workbooks excelBooks = excelApp.Workbooks; Workbook excelBook = excelBooks.Add(Missing.Value); Sheets sheets = excelBook.Sheets; Worksheet tripSheet = ((Worksheet)sheets.get_Item(1)); Worksheet testSheet = ((Worksheet)sheets.get_Item(2)); fillData(ref tripSheet, "Trip", tripTable); fillData(ref testSheet, "Test", testTable); excelBook.SaveAs(savePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); excelBook.Close(Missing.Value, Missing.Value, Missing.Value); excelBooks.Close(); excelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); excelApp = null; return(true); } catch (Exception exp) { MsgBox.Show(exp.Message); return(false); } }
/// <summary> /// 默认构造函数 /// </summary> public Excel() { books = app.Workbooks; book = books.Add(); sheets = book.Worksheets; sheet = (_Worksheet)sheets.get_Item(1); }
/// <summary> /// 格式化表格 /// </summary> /// <param name="file"></param> public static void FormateExcel(string file) { Application app = new Application(); app.DisplayAlerts = false; app.Visible = false; app.UserControl = true; Workbooks workbooks = app.Workbooks; _Workbook workbook = workbooks.Add(file); Sheets sheets = workbook.Sheets; for (int i = 1; i <= 1; i++) //读取2009-2017表格,首张为汇总表 { Worksheet worksheet = (Worksheet)sheets.get_Item(i); int recordCount = 2;//从第二行开始判断 //查找数据行数 while (true) { if (Method.IsNumber(worksheet.Cells[recordCount, 1].TEXT)) { recordCount++; } else { break; } } MergeCell(ref worksheet, 2, recordCount, 3); MergeCell(ref worksheet, 2, recordCount, 7); } workbook.SaveAs(file); app.Quit(); }
public void openExcel(OperationParaVO opVO) { filePatch = opVO.filePath; workBooks = new Application().Workbooks; workBook = workBooks.Add(filePatch); sheet = workBook.Sheets.get_Item(opVO.sheetIndex); }
public static int CreateFromRealmsCSV(string source, string destination) { var app = CreateExcelApp(destination); app.ScreenUpdating = false; Workbooks workbooks = app.Workbooks; Workbook workbook = workbooks.Add(); Worksheet worksheet = (Worksheet)workbook.Worksheets.Item[1]; CreateWorksheetTemplate(worksheet); var csv = ConfigureCSV(source); // Reading records the old way var departmentRoot = ReadRealmsRecords(csv); // Writing records the old way PopulateTemplate(worksheet, departmentRoot); workbook.SaveAs(destination); // Cleanup app.ScreenUpdating = true; Cleanup(app, workbooks, workbook, worksheet); return(0); }
protected override bool OpenInternal(string fileName, bool write) { if (_obj != null) { return(false); } _app = new Application(); _app.Visible = false; _app.DisplayAlerts = false; _app.ScreenUpdating = false; _workbooks = _app.Workbooks; if (!_write && File.Exists(fileName)) { _obj = _workbooks.Open(fileName); } else { _obj = _workbooks.Add(); } _worksheets = _obj.Worksheets; return(true); }
public void WriteExcel(Object userData) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Workbooks workbooks = excel.Workbooks; Workbook workBook = workbooks.Add(true); try { excel.DisplayAlerts = false; excel.AlertBeforeOverwriting = false; WriteExcelEventArgs args = new WriteExcelEventArgs(workBook, userData, excel); if (WriteExcelEvent != null) { WriteExcelEvent(this, args); } //((Worksheet)workBook.Worksheets[workBook.Worksheets]).Delete(); workBook.SaveAs(fileName, XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } finally { workBook.Close(); workbooks.Close(); excel.Quit(); GC.Collect(); } }
static public void ReadExcel(String filepath) { //打开已有excel文档,或者创建一个新的excel文档 Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); if (app == null) { MessageBox.Show("可能您的机子未安装excel,无法创建excel对象!", "系统提示 ", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } Workbooks wbks = app.Workbooks; Workbook wbk = wbks.Add(filepath); //注:若新建一个excel文档,“filepath”替换成true即可;不过这里新建的excel文档默认只有一个sheet。 //Workbook wbk = wbks.Open(filepath, missing, true, missing, missing, missing, // missing, missing, missing, true, missing, missing, missing, missing, missing); Sheets shs = wbk.Sheets;//定义一个新的工作表 //i是要取得的sheet的index _Worksheet wsh = (_Worksheet)shs.get_Item(1); wsh.Activate();//激活工作表 #region 取得总记录行数(包括标题列) ///UsedRange:返回一个Microsoft.Office.Interop.Excel.Range对象,该对象表示指定工作表上使用的范围。 只读。 ///Cells:返回一个Range对象,它表示指定范围内的单元格。 ///Rows返回表示指定范围内的行的Microsoft.Office.Interop.Excel.Range对象。 ///Count:返回集合中的对象数量。 #endregion int rowsint = wsh.UsedRange.Cells.Rows.Count; //得到非空行数 int columnsint = wsh.UsedRange.Cells.Columns.Count; //得到非空列数 //System.Diagnostics.Debug.Assert(false, "rowsint" + rowsint); //System.Diagnostics.Debug.Assert(false, "columnsint" + columnsint); Form1.PersonList = new List <List <String> >(); //Range range = (Range)wsh.Cells[2, 3]; //System.Diagnostics.Debug.Assert(false, range.Text.ToString()); for (int i = 1; i <= rowsint; i++) { List <String> array = new List <String>(); for (int j = 1; j <= columnsint; j++) { Range range = (Range)wsh.Cells[i, j]; array.Add(range.Text.ToString()); } Form1.PersonList.Add(array); } app.Quit(); return; }
public void Automation_QueryTable(string fileAndPath, string connStr, string sqlQuery) { if (File.Exists(fileAndPath)) { File.Delete(fileAndPath); } connStr = connStr.Replace("User Id", "Uid").Replace("Password", "Pwd"); m_objExcel = new Application(); m_objBooks = m_objExcel.Workbooks; m_objBook = m_objBooks.Add(m_objOpt); m_objSheets = m_objBook.Worksheets; m_objSheet = (_Worksheet)m_objSheets.Item[1]; m_objRange = m_objSheet.get_Range("A1", m_objOpt); m_objQryTables = m_objSheet.QueryTables; m_objQryTable = m_objQryTables.Add(@"ODBC;DRIVER=SQL Server;" + connStr + "APP=Microsoft Office 2013;WSID=KEDS-13136;", m_objRange, sqlQuery); m_objQryTable.RefreshStyle = XlCellInsertionMode.xlInsertEntireRows; m_objQryTable.Refresh(false); m_objQryTable.Delete(); m_objBook.SaveAs(fileAndPath, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); }
/// <summary> /// 打开excel /// </summary> protected void Create() { m_objExcel = new Application(); m_objExcel.Application.DisplayAlerts = false; m_objBooks = (Workbooks)m_objExcel.Workbooks; m_objBook = (_Workbook)(m_objBooks.Add(m_objOpt)); }
public void Automation_CellByCell() { //startimi i nje workbooki ne excel m_objExcel = new Application(); m_objBooks = (Workbooks)m_objExcel.Workbooks; m_objBook = (Workbook)(m_objBooks.Add(m_objOpt)); //vendosja e te dhenave ne sheetin e pare m_objSheets = (Sheets)m_objBook.Worksheets; m_objSheet = (_Worksheet)(m_objSheets.Item[1]); m_objRange = m_objSheet.Range["A1", m_objOpt]; m_objRange.set_Value(m_objOpt, "Value A1"); m_objRange = m_objSheet.Range["B1", m_objOpt]; m_objRange.set_Value(m_objOpt, "Value B1"); m_objRange = m_objSheet.Range["A2", m_objOpt]; m_objRange.set_Value(m_objOpt, "Value A2"); m_objRange = m_objSheet.Range["B2", m_objOpt]; m_objRange.set_Value(m_objOpt, "Value B2"); //Bold i cellsave m_objRange = m_objSheet.get_Range("A1", "B1"); m_objFont = m_objRange.Font; m_objFont.Bold = true; //save and quite excel m_objBook.SaveAs(m_strSampleFolder + "Book1.xls", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); }
public void InformeGeneralObras() { oExcel = new Application(); oBooks = oExcel.Workbooks; oBook = oBooks.Add(1); oSheets = (Sheets)oBook.Worksheets; oSheet = oSheets.get_Item(1); this.oSheet.Cells[1,1] = "Consecutivo"; this.oSheet.Cells[1,2] = "Título"; this.oSheet.Cells[1,3] = "Núm. de Material"; this.oSheet.Cells[1,4] = "Año"; this.oSheet.Cells[1,5] = "Tiraje"; int ind = 2; for (int j = 0; j < obrasImprimir.Count; j++) { oSheet.Cells[1][ind] = obrasImprimir[j].Consecutivo; oSheet.Cells[2][ind] = obrasImprimir[j].Titulo; oSheet.Cells[3][ind] = obrasImprimir[j].NumMaterial; oSheet.Cells[4][ind] = obrasImprimir[j].AnioPublicacion; oSheet.Cells[5][ind] = obrasImprimir[j].Tiraje; ind++; } this.oExcel.ActiveWorkbook.Save(); this.oExcel.Quit(); }
//获取模板第一个工作表 /// <summary> /// 获取模板第一个工作表 /// </summary> private void GetWorkSheet() { try { app = new Microsoft.Office.Interop.Excel.Application(); } catch (Exception ex) { //提示:服务器上缺少Excel组件,需要安装Office软件 errorMsg = "服务器上缺少Excel组件:" + ex.Message; return; } app.Visible = false; app.UserControl = true; Workbooks workbooks = app.Workbooks; workbook = workbooks.Add(page.Server.MapPath(string.Format(@"{0}{1}", path, fileName))); //加载模板 Sheets sheets = workbook.Sheets; //第一个工作薄。 worksheet = (_Worksheet)sheets.get_Item(1); if (worksheet == null) { //提示:工作薄中没有工作表 errorMsg = "工作薄中没有工作表"; } }
/// <summary> /// 打开一个Excel文件 /// </summary> /// <param name="FileName"></param> public void Open(string FileName) { app = new Application(); wbs = app.Workbooks; wb = wbs.Add(FileName); mFilename = FileName; }
public void StartUp() { _ExcelApp = new Excel.Application(); _books = (Workbooks)_ExcelApp.Workbooks; _book = (_Workbook)(_books.Add(_optionalValue)); _sheets = (Sheets)_book.Worksheets; }
/// <summary> /// Setup workbooks/worksheets and populate them /// </summary> public void Initialize() { //_application.Workbooks.add(...) is not possible. //2 dots must be avoided to release wrappers: http://support.microsoft.com/kb/317109 Workbooks workbooks = _application.Workbooks; _workbook = workbooks.Add(Missing.Value); Sheets worksheets = _workbook.Worksheets; _worksheet = (Worksheet)_workbook.ActiveSheet; _worksheetHidden = (Worksheet)worksheets.Add(); // Release wrapper objects Marshal.FinalReleaseComObject(worksheets); Marshal.FinalReleaseComObject(workbooks); _worksheetHidden.Name = "Mapping Information"; _worksheetHidden.Visible = XlSheetVisibility.xlSheetVeryHidden; //Event handlers _worksheet.Change += Event_ChangeEvent; Populate(); _application.Visible = true; //Show the application after it's populated }
public void Fill(string xlsxName, string pdfName = null, string docTitle = null, string forthLine = null, string[] pictures = null, string[] titles = null, bool[] markAsRed = null, string endText = null, bool markEndTextRed = false, string itemNo = null, string inspectionTime = null) { string exitCode = "0"; try { //wbk = wbks.Add(true); wbk = wbks.Add(System.Environment.CurrentDirectory + "\\Template.xlsx"); filename = xlsxName; sheets = wbk.Sheets; sheet1 = (Worksheet)sheets.Item[1]; sheet1.Name = "WorkSheet"; SetStandardRowHeight(pictures.Count()); //sheet1.Cells[2, 2] = "test"; alertColor = GetAlertColor(); ProgressUpdated(5, "文件创建完成"); AddPictures(pictures, titles, markAsRed); ProgressUpdated(110, "图片填充完成"); // add end box if (pictures.Count() == 6) { currentRow = pageHeaderHeight + 6 / groupColumnCount * (picBoxRowCount + titleBoxRowCount) + 1; } else if (pictures.Count() % 6 == 0) { currentRow = (pageHeight * (pictures.Count() / 6 - 1) + (groupPicCount / groupColumnCount) * (picBoxRowCount + titleBoxRowCount)) + pageVerticalOffset + 1; } AddEndBox(endText, 1, currentRow, markEndTextRed); ProgressUpdated(115, "结束语添加完成"); // add INSPECTION TIME and ITEM NO AddInspectionTime(inspectionTime); AddItemNo(itemNo); AddDocTitle(docTitle); AddForthLine(forthLine); ProgressUpdated(120, "文件信息添加完成"); // save and export pdf SaveWorkbook(); if (!string.IsNullOrEmpty(pdfName)) { ExportPDF(pdfName); } } finally { Exit(exitCode); QuitExcel(); } }
public ExcelUtils(string fileName) { this.fileName = fileName; app = new Application(); DefaultSetting(); workbooks = app.Workbooks; workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet); }
/// <summary> /// Create Excel application parameters instances /// </summary> private void CreateExcelRef() { myExcelApp = new Microsoft.Office.Interop.Excel.Application(); myBooks = (Workbooks)myExcelApp.Workbooks; myBook = (Workbook)(myBooks.Add(myOptionalValue)); mySheets = (Sheets)myBook.Worksheets; mySheet = (Worksheet)(mySheets.get_Item(1)); }
public void AddWorkbook() { mWorkbook = mWorkbooks.Add(true); mWorksheet = (Worksheet)mWorkbook.ActiveSheet; ColCount = 0; RowCount = 0; }
public void AddWorkbook() { _workbook = _workbooks.Add(true); _worksheet = (Worksheet)_workbook.ActiveSheet; ColCount = 0; RowCount = 0; }
/// <summary> /// 基于excel.dll打开excel文件 /// </summary> /// <param name="FileName">文件名</param> public void OpenExcel(string FileName) { //打开excel app = new Excel.Application(); wbs = app.Workbooks; wb = wbs.Add(FileName); app.Visible = true; }
private void CreateNewBook() { //_ExcelApp = new Excel.Application(); //_books = (Excel.Workbooks)_ExcelApp.Workbooks; _book = (_Workbook)(_books.Add(_optionalValue)); _sheets = (Sheets)_book.Worksheets; _sheet = (_Worksheet)(_sheets.get_Item(1)); }
/// <summary> /// Metodo que crea una aplicacion de Excel /// </summary> private void CrearExcel() { excelApp = new Application(); libros = excelApp.Workbooks; libro = libros.Add(valorOpcional); hojas = libro.Worksheets; hoja = (Worksheet)(hojas.Item[1]); }
private static void CreateExcelComponents(out Application app, out Workbooks workBooks, out Workbook workbook, out Sheets worksheets, out Worksheet worksheet) { app = new Application(); workBooks = app.Workbooks; workbook = workBooks.Add(Type.Missing); worksheets = workbook.Sheets; worksheet = (Worksheet)worksheets[1]; }
/// Create Excel application parameters instances private void CreateExcelRef() { excelApp = new Microsoft.Office.Interop.Excel.Application(); books = (Workbooks)excelApp.Workbooks; book = (Workbook)(books.Add(optionalValue)); sheets = (Sheets)book.Worksheets; sheet = (Worksheet)(sheets.get_Item(1)); }
static void Main(string[] args) { SQLiteConnection conn = new SQLiteConnection("Data Source = weather.db");; conn.Open(); SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter("select * from nasaweather", conn); DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet, "nasaweather"); System.Data.DataTable dataTable = dataSet.Tables["nasaweather"]; string path = Environment.CurrentDirectory + "\\"; Application excel = new Application(); Workbooks wbks = excel.Workbooks; Workbook wb = wbks.Add(path + "mb.xlsx"); Worksheet wsh = wb.Sheets[1]; int row = 2; foreach (DataRow dataRow in dataTable.Rows) { if (getName(dataRow["type"].ToString()) == "不要") { continue; } int col = 1; wsh.Cells[row, col++] = dataRow["lon"]; wsh.Cells[row, col++] = dataRow["lat"]; wsh.Cells[row, col++] = getName(dataRow["type"].ToString()); string[] data = getArray(dataRow["data"].ToString()); foreach (string str in data) { wsh.Cells[row, col++] = str; } //Console.WriteLine(dataRow["lat"] + " " + dataRow["lon"] + " " + dataRow["type"] + " " + dataRow["data"]); Console.WriteLine((row - 1.0) * 100 / 13104 + "%"); if ((row - 1) % 4 == 0) { Range lonRange = wsh.Range[wsh.Cells[row - 3, 1], wsh.Cells[row, 1]]; lonRange.ClearContents(); lonRange.Merge(); wsh.Cells[row - 3, 1] = dataRow["lon"]; Range latRange = wsh.Range[wsh.Cells[row - 3, 2], wsh.Cells[row, 2]]; latRange.ClearContents(); latRange.Merge(); wsh.Cells[row - 3, 2] = dataRow["lat"]; Range range = wsh.Range[wsh.Cells[row - 3, 1], wsh.Cells[row, col - 1]]; range.BorderAround(); } ++row; //if (row > 100) // break; } excel.DisplayAlerts = false; excel.AlertBeforeOverwriting = false; wb.SaveAs(path + "气象表.xlsx"); wb.Close(); wbks.Close(); }
public DataGridExcelTools() { CursorPosition = 1; excel = Microsoft.VisualBasic.Interaction.CreateObject("Excel.Application", string.Empty); //excel.ScreenUpdating = false; workbook = excel.workbooks; workbook.Add(); worksheet = excel.ActiveSheet; }
public void Open(string filePath) { excel = new Application {DisplayAlerts = false}; workbooks = excel.Workbooks; if (File.Exists(filePath)) { workbook = workbooks.Open(filePath); } else { workbook = workbooks.Add(); workbook.SaveAs(filePath); } worksheet = workbook.Worksheets[1] as Worksheet; if (worksheet == null) throw new NullReferenceException("worksheet"); }
private void openExcel(String filename) { app.Visible = false; this.wbks = app.Workbooks; r_wbk= wbks.Add(filename); }
protected override bool OpenInternal(string fileName, bool write) { if (_obj != null) return false; _app = new Application(); _app.Visible = false; _app.DisplayAlerts = false; _app.ScreenUpdating = false; _workbooks = _app.Workbooks; if (!_write && File.Exists(fileName)) { _obj = _workbooks.Open(fileName); } else { _obj = _workbooks.Add(); } _worksheets = _obj.Worksheets; return true; }
private void initExcel() { try { excelApp = new ApplicationClass(); workbooks = excelApp.Workbooks; workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet); worksheet =(Worksheet) workbook.Worksheets[1]; worksheet.Cells[1, 1] = "No"; worksheet.Cells[1, 2] = "Picture"; worksheet.Cells[1, 3] = "Time"; worksheet.Cells[1, 4] = "CorrectRate"; worksheet.Cells[1, 5] = "MaxGrasp"; worksheet.Cells[1, 6] = "MaxSpeed"; worksheet.Cells[1, 7] = "DetailData"; } catch(Exception exp){ throw(exp); } }
//创建一个Excel对象 public void Create() { app = new Application(); wbs = app.Workbooks; wb = wbs.Add(true); }
//打开一个Excel文件 public void Open(string FileName) { app = new Application(); wbs = app.Workbooks; wb = wbs.Add(FileName); //wb = wbs.Open(FileName, 0, true, 5,"", "", true, XlPlatform.xlWindows, "t", false, false, 0, true,Type.Missing,Type.Missing); //wb = wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing); mFilename = FileName; }