public GenerarExcel(M_Eno m, string titulo) { this.m = m; this.titulo = titulo; excel = new Excel.Application(); libro = (Excel._Workbook)excel.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); hoja = (Excel._Worksheet)libro.Worksheets.Add(); hoja.Name = "Resultado Del ENO"; ((Excel.Worksheet)excel.ActiveWorkbook.Sheets["Hoja1"]).Delete(); generarDocumento(ref hoja); if (System.IO.File.Exists(Environment.CurrentDirectory + @"Result.xlsx")) { libro.Save(); } else { libro.SaveAs(Environment.CurrentDirectory + @"\Result.xlsx"); } libro.Close(); releaseObject(libro); excel.UserControl = false; excel.Quit(); releaseObject(excel); }
//-> Export to Excel private void StatisticExportToExcel() { // creating Excel Application Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); // creating new WorkBook within Excel application Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); // creating new Excelsheet in workbook Microsoft.Office.Interop.Excel._Worksheet worksheet = null; // see the excel sheet behind the program app.Visible = true; // get the reference of first sheet. By default its name is Sheet1. // store its reference to worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[0]; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet; // changing the name of active sheet worksheet.Name = "Exported from gridview"; // storing header part in Excel for (int i = 1; i < dgv_Statistic.Columns.Count + 1; i++) { worksheet.Cells[1, i] = dgv_Statistic.Columns[i - 1].HeaderText; } // storing Each row and column value to excel sheet for (int i = 0; i < dgv_Statistic.Rows.Count - 1; i++) { for (int j = 0; j < dgv_Statistic.Columns.Count; j++) { worksheet.Cells[i + 2, j + 1] = dgv_Statistic.Rows[i].Cells[j].Value.ToString(); } } string path = Directory.GetParent(System.IO.Directory.GetCurrentDirectory()).Parent.Parent.Parent.FullName + "\\Statistic & Analyze\\Excel\\output.xls"; // save the application workbook.SaveAs(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // Exit from the application app.Quit(); }
private void PrintFileImage_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet worksheet = null; app.Visible = true; worksheet = workbook.Sheets["Sheet1"]; worksheet = workbook.ActiveSheet; worksheet.Name = "Exported from gridview"; for (int i = 1; i < dataGridView_Course.Columns.Count + 1; i++) { worksheet.Cells[1, i] = dataGridView_Course.Columns[i - 1].HeaderText; } for (int i = 0; i < dataGridView_Course.Rows.Count - 1; i++) { for (int j = 0; j < dataGridView_Course.Columns.Count; j++) { worksheet.Cells[i + 2, j + 1] = dataGridView_Course.Rows[i].Cells[j].Value.ToString(); } } workbook.SaveAs("D:\\list4.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing); MessageBox.Show("Save file successful!", "Save File", MessageBoxButtons.OK, MessageBoxIcon.Information); app.Quit(); }
private void bwExportExcel_DoWork(object sender, DoWorkEventArgs e) { EXCEL._Application excel = new Microsoft.Office.Interop.Excel.Application(); EXCEL._Workbook workbook = excel.Workbooks.Add(Type.Missing); EXCEL._Worksheet worksheet = null; try { worksheet = workbook.ActiveSheet; worksheet.Cells.HorizontalAlignment = EXCEL.XlHAlign.xlHAlignCenter; worksheet.Cells.Font.Name = "Arial"; worksheet.Cells.Font.Size = 10; // Title, Header. int rowTitle = 1; int rowHeader = 2; int columnNumber = 0; Dispatcher.Invoke(new Action(() => { columnNumber = dgWHDeliveryDetail.Columns.Count(); // Title worksheet.Range[worksheet.Cells[rowTitle, 1], worksheet.Cells[rowTitle, columnNumber]].Merge(); worksheet.Cells.Rows[rowTitle].Font.Size = 15; worksheet.Cells.Rows[rowTitle].Font.FontStyle = "Bold"; // Header worksheet.Cells[rowTitle, 1] = txtTitle.Text; for (int i = 0; i < columnNumber; i++) { var column = dgWHDeliveryDetail.Columns[i] as DataGridTemplateColumn; worksheet.Cells[rowHeader, i + 1] = column.Header; } worksheet.Cells.Rows[rowHeader].Font.Size = 10; worksheet.Cells.Rows[rowHeader].Font.FontStyle = "Bold"; progressBar.Maximum = (double)excelExportList.Count(); })); int rowContent = 3; for (int i = 0; i < excelExportList.Count(); i++) { Dispatcher.Invoke(new Action(() => { progressBar.Value = rowContent - 2; lblStatus.Text = string.Format("Writing {0} rows / {1}", rowContent - 2, excelExportList.Count()); })); var export = excelExportList[i]; worksheet.Cells[rowContent, 1] = export.ProductNo; worksheet.Cells[rowContent, 2] = export.ArticleNo; worksheet.Cells[rowContent, 3] = export.OrderEFD; worksheet.Cells[rowContent, 4] = export.DeliveryEFD; worksheet.Cells[rowContent, 5] = export.SewingStartDate; worksheet.Cells[rowContent, 6] = export.QuantityOrder; worksheet.Cells[rowContent, 7] = export.Release; worksheet.Cells[rowContent, 8] = export.Delivery; worksheet.Cells[rowContent, 9] = export.Reject; for (int j = 0; j < export.ContentStatusList.Count(); j++) { worksheet.Cells[rowContent, j + 10].ColumnWidth = export.ContentStatusList[j].WidthDefaultCell; worksheet.Cells[rowContent, j + 10].Font.Color = System.Drawing.ColorTranslator.ToOle(export.ContentStatusList[j].ForegroundCell); worksheet.Cells[rowContent, j + 10].Interior.Color = System.Drawing.ColorTranslator.ToOle(export.ContentStatusList[j].BackgroundCell); worksheet.Cells[rowContent, j + 10].Borders.Color = System.Drawing.ColorTranslator.ToOle(export.ContentStatusList[j].BorderColorCell); if (export.ContentStatusList[j].Quantity > 0) { worksheet.Cells[rowContent, j + 10] = export.ContentStatusList[j].Quantity; } if (export.ContentStatusList[j].ForegroundCell != null) { worksheet.Cells[rowContent, j + 10].Font.Color = System.Drawing.ColorTranslator.ToOle(export.ContentStatusList[j].ForegroundCell); } if (export.ContentStatusList[j].BackgroundCell != null) { worksheet.Cells[rowContent, j + 10].Interior.Color = System.Drawing.ColorTranslator.ToOle(export.ContentStatusList[j].BackgroundCell); } } worksheet.Cells[rowContent, columnNumber - 1] = export.TotalBalance; worksheet.Cells[rowContent, columnNumber] = export.TotalReleasePainting; rowContent++; } Dispatcher.Invoke(new Action(() => { if (workbook != null) { var sfd = new System.Windows.Forms.SaveFileDialog(); sfd.Title = "Master Schedule - Export Excel File"; sfd.Filter = "Excel Documents (*.xls)|*.xls|Excel Documents (*.xlsx)|*.xlsx"; sfd.FilterIndex = 2; sfd.FileName = String.Format("Outsole WH Delivery Report"); if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK) { workbook.SaveAs(sfd.FileName); MessageBox.Show("Export Successful !", "Master Schedule - Export Excel File", MessageBoxButton.OK, MessageBoxImage.Information); } } })); } catch (System.Exception ex) { Dispatcher.Invoke(new Action(() => { MessageBox.Show(ex.Message, "Master Schedule - Export Excel File", MessageBoxButton.OK, MessageBoxImage.Error); })); } finally { excel.Quit(); workbook = null; excel = null; } }
private void buttonExport_Click(object sender, EventArgs e) { // Creating a Excel object. Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet worksheet = null; try { excel.SheetsInNewWorkbook = 6; workbook = excel.Workbooks.Add(); worksheet = workbook.Worksheets[1]; worksheet.Name = "Gironi"; int cellRowIndex = 1; int cellColumnIndex = 1; #region Gironi //Loop through gironi for (int j = 0; j < dataGridViewGironi.Columns.Count; j++) { // Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check. worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridViewGironi.Columns[j].HeaderText; cellColumnIndex++; } cellRowIndex++; cellColumnIndex = 1; for (int i = 0; i < dataGridViewGironi.Rows.Count; i++) { for (int j = 0; j < dataGridViewGironi.Columns.Count; j++) { worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridViewGironi.Rows[i].Cells[j].Value.ToString(); cellColumnIndex++; } cellColumnIndex = 1; cellRowIndex++; } #endregion #region sedicesimi worksheet = workbook.Sheets[2]; worksheet.Name = "Sedicesimi"; cellRowIndex = 1; cellColumnIndex = 1; //Loop through Sedicesimi for (int j = 0; j < dataGridViewSedicesimi.Columns.Count; j++) { // Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check. worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridViewSedicesimi.Columns[j].HeaderText; cellColumnIndex++; } cellRowIndex++; cellColumnIndex = 1; for (int i = 0; i < dataGridViewSedicesimi.Rows.Count; i++) { for (int j = 0; j < dataGridViewSedicesimi.Columns.Count; j++) { worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridViewSedicesimi.Rows[i].Cells[j].Value.ToString(); cellColumnIndex++; } cellColumnIndex = 1; cellRowIndex++; } #endregion #region ottavi worksheet = workbook.Sheets[3]; worksheet.Name = "Ottavi"; cellRowIndex = 1; cellColumnIndex = 1; //Loop through Ottavi for (int j = 0; j < dataGridViewOttavi.Columns.Count; j++) { // Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check. worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridViewOttavi.Columns[j].HeaderText; cellColumnIndex++; } cellRowIndex++; cellColumnIndex = 1; for (int i = 0; i < dataGridViewOttavi.Rows.Count; i++) { for (int j = 0; j < dataGridViewOttavi.Columns.Count; j++) { worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridViewOttavi.Rows[i].Cells[j].Value.ToString(); cellColumnIndex++; } cellColumnIndex = 1; cellRowIndex++; } #endregion #region Quarti worksheet = workbook.Sheets[4]; worksheet.Name = "Quarti"; cellRowIndex = 1; cellColumnIndex = 1; //Loop through Ottavi for (int j = 0; j < dataGridViewQuarti.Columns.Count; j++) { // Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check. worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridViewQuarti.Columns[j].HeaderText; cellColumnIndex++; } cellRowIndex++; cellColumnIndex = 1; for (int i = 0; i < dataGridViewQuarti.Rows.Count; i++) { for (int j = 0; j < dataGridViewQuarti.Columns.Count; j++) { worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridViewQuarti.Rows[i].Cells[j].Value.ToString(); cellColumnIndex++; } cellColumnIndex = 1; cellRowIndex++; } #endregion #region Semifinali worksheet = workbook.Sheets[5]; worksheet.Name = "Semifinali"; cellRowIndex = 1; cellColumnIndex = 1; //Loop through Semifinali for (int j = 0; j < dataGridViewSemifinali.Columns.Count; j++) { // Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check. worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridViewSemifinali.Columns[j].HeaderText; cellColumnIndex++; } cellRowIndex++; cellColumnIndex = 1; for (int i = 0; i < dataGridViewSemifinali.Rows.Count; i++) { for (int j = 0; j < dataGridViewSemifinali.Columns.Count; j++) { worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridViewSemifinali.Rows[i].Cells[j].Value.ToString(); cellColumnIndex++; } cellColumnIndex = 1; cellRowIndex++; } #endregion #region Finali worksheet = workbook.Sheets[6]; worksheet.Name = "Finali"; cellRowIndex = 1; cellColumnIndex = 1; //Loop through Finali for (int j = 0; j < dataGridViewFinali.Columns.Count; j++) { // Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check. worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridViewFinali.Columns[j].HeaderText; cellColumnIndex++; } cellRowIndex++; cellColumnIndex = 1; for (int i = 0; i < dataGridViewFinali.Rows.Count; i++) { for (int j = 0; j < dataGridViewFinali.Columns.Count; j++) { worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridViewFinali.Rows[i].Cells[j].Value.ToString(); cellColumnIndex++; } cellColumnIndex = 1; cellRowIndex++; } #endregion //Getting the location and file name of the excel to save from user. SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx"; //|All files (*.*)|*.*"; saveDialog.FilterIndex = 1; if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK) { workbook.SaveAs(saveDialog.FileName); MessageBox.Show("Export completato con successo", "Salvataggio", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } } catch (System.Exception ex) { MessageBox.Show(ex.Message); } finally { excel.Quit(); workbook = null; excel = null; } }
public void Exportieren(string turniername, DataGrid Starterliste_DataGrid) { string filename = ""; // Configure save file dialog box SaveFileDialog dlg = new SaveFileDialog(); dlg.FileName = turniername; // Default file name dlg.DefaultExt = ".xlsx"; // Default file extension dlg.Filter = "Excel documents (.xlsx)|*.xlsx"; // Filter files by extension // Show save file dialog box Nullable <bool> result = dlg.ShowDialog(); // Process save file dialog box results if (result == true) { // Save document filename = dlg.FileName; } //instantiate excel objects (application, workbook, worksheets) excel.Application XlObj = new excel.Application(); XlObj.Visible = false; excel._Workbook WbObj = (excel.Workbook)(XlObj.Workbooks.Add("")); excel._Worksheet WsObj = (excel.Worksheet)WbObj.ActiveSheet; DataTable dt = DataGridtoDataTable(Starterliste_DataGrid); //run through datatable and assign cells to values of datatable try { int row = 1; int col = 1; foreach (DataColumn column in dt.Columns) { //adding columns WsObj.Cells[row, col] = column.ColumnName; col++; } //reset column and row variables col = 1; row++; for (int i = 0; i < dt.Rows.Count; i++) { //adding data foreach (var cell in dt.Rows[i].ItemArray) { WsObj.Cells[row, col] = cell; col++; } col = 1; row++; } WbObj.SaveAs(filename); } catch (COMException x) { Console.WriteLine(x); } catch (Exception ex) { Console.WriteLine(ex); } finally { WbObj.Close(); } }
private void Export_Click(object sender, System.EventArgs e) { // Creating a Excel object. Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet worksheet = null; try { worksheet = workbook.ActiveSheet; worksheet.Name = "ExportedFromDatGrid"; int cellRowIndex = 1; int cellColumnIndex = 1; //Loop through each row and read value from each column. for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) { for (int j = 0; j < dataGridView1.Columns.Count; j++) { // Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check. if (cellRowIndex == 1) { worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1.Columns[j].HeaderText; } else { worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1.Rows[i].Cells[j].Value.ToString(); } cellColumnIndex++; } cellColumnIndex = 1; cellRowIndex++; } //Getting the location and file name of the excel to save from user. SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"; saveDialog.FilterIndex = 2; if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK) { workbook.SaveAs(saveDialog.FileName); MessageBox.Show("Export Successful"); } } catch (System.Exception ex) { MessageBox.Show(ex.Message); } finally { excel.Quit(); workbook = null; excel = null; } /////////////////////////////////////incercarea 2///////////// //Microsoft.Office.Interop.Excel._Application app = // new Microsoft.Office.Interop.Excel.Application(); //Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); //Microsoft.Office.Interop.Excel._Worksheet worksheet = null; //worksheet = workbook.Sheets["Sheet1"]; //worksheet = workbook.ActiveSheet; //worksheet.Name = "Toti Angajatii"; //for (int i = 1; i < dataGridView1.Columns.Count + 1; i++) //{ // worksheet.Cells[1,i]=dataGridView1.Columns[i-1].HeaderText; //} //for (int i = 0; i < dataGridView1.Rows.Count + 1; i++) //{ // for (int j = 0; j < dataGridView1.Columns.Count ; j++) // { // worksheet.Cells[i + 2, j + 1] = // dataGridView1.Rows[i].Cells[j].Value.ToString(); // } //} //var saveFileDialoge = new SaveFileDialog(); //saveFileDialoge.FileName = "Excel_exportContacte"; //saveFileDialoge.DefaultExt = ".xlsx"; //if(saveFileDialoge.ShowDialog()==DialogResult.OK) //{ // workbook.SaveAs(saveFileDialoge.FileName, // Type.Missing, Type.Missing, Type.Missing, Type.Missing, // Type.Missing,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, // Type.Missing, Type.Missing, Type.Missing // , Type.Missing, Type.Missing); //} //app.Quit(); ////////////////////////////////////prima incercare/////// //saveFileDialog1.InitialDirectory = "C:"; //saveFileDialog1.Title = "Salveaza ca fila Excel"; //saveFileDialog1.FileName = "Excel_exportContacte"; //saveFileDialog1.Filter = "ExcelFiles(2016)|*.xlsx"; //if (saveFileDialog1.ShowDialog() != DialogResult.Cancel) { // Microsoft.Office.Interop.Excel.Application ExcelApp = // new Microsoft.Office.Interop.Excel.Application(); // ExcelApp.Application.Workbooks.Add( ); // //schimb propietati Workbook // ExcelApp.Columns.ColumnWidth = 20; // //Storing // for (int i = 1; i < dataGridView1.Columns.Count + 1; i++) // { // ExcelApp.Cells[1, i] = // dataGridView1.Columns[i - 1].HeaderText; // } // for (int i = 1; i < dataGridView1.Columns.Count + 1; i++) // { // for (int j = 0; j < dataGridView1.Columns.Count; j++) // { // ExcelApp.Cells[i + 2, j + 1] = // dataGridView1.Rows[i].Cells[j].Value.ToString(); // } // } // ExcelApp.ActiveWorkbook.SaveCopyAs(saveFileDialog1.FileName.ToString()); // ExcelApp.ActiveWorkbook.Saved = true; // ExcelApp.Quit(); }
static void MainTask(string[] args) { // display usage if no arguments if (args.Length == 0) { Console.WriteLine(Usage()); return; } var xlApp = new Microsoft.Office.Interop.Excel.Application(); try { Microsoft.Office.Interop.Excel._Workbook wbk = null; Microsoft.Office.Interop.Excel.Range rng = null; xlApp.DisplayAlerts = false; xlApp.Visible = true; xlApp.Interactive = true; // no-well established parsearg method for (int i = 0; i < args.Length; i++) { var cmd = args[i]; while (cmd[0] == '-' || cmd[0] == '/') { cmd = cmd.Substring(1); } switch (cmd) { case "xlFileOpen": case "xlFilePath": var openPath = MakeFullPath(args[++i]); Console.WriteLine($"> Open Workbook {openPath}"); if (wbk != null) { wbk.Close(SaveChanges: false); } wbk = xlApp.Workbooks.Open(openPath); break; case "xlFileNew": Console.WriteLine($"> New Workbook"); if (wbk != null) { wbk.Close(SaveChanges: false); } wbk = xlApp.Workbooks.Add(); break; case "xlFileSave": Console.WriteLine($"> Save Workbook"); wbk.Save(); break; case "xlFileSaveAs": var savePath = MakeFullPath(args[++i]); Console.WriteLine($"> Save Workbook as {savePath}"); wbk.SaveAs(savePath); break; case "xlEvalMacro": var macro = args[++i]; Console.WriteLine($"> Evaluate macro {macro}"); xlApp.Evaluate(macro); break; case "xlRefreshLeftToRight": Console.WriteLine($"> Refresh sheets left to right"); foreach (Microsoft.Office.Interop.Excel._Worksheet wsh in wbk.Worksheets) { if (wsh.Visible == XlSheetVisibility.xlSheetVisible) { wsh.Calculate(); } } break; case "xlRngGet": var rngGetAddr = args[++i]; Console.WriteLine($"> Get Range {rngGetAddr}"); rng = xlApp.Range[rngGetAddr]; Console.WriteLine(rng.Value); break; case "xlRngSet": var rngSetAddr = args[++i]; var rngSetValue = args[++i]; Console.WriteLine($"> Set Range {rngSetAddr} {rngSetValue}"); rng = xlApp.Range[rngSetAddr]; rng.Value = rngSetValue; break; case "timeout": i++; break; default: throw new Exception($"> Unexpected command {cmd}"); } } } catch (Exception e) { Console.WriteLine(e.Message); Environment.Exit(1); } finally { foreach (Microsoft.Office.Interop.Excel._Workbook wbk in xlApp.Workbooks) { wbk.Close(SaveChanges: false); } xlApp.Quit(); Console.WriteLine("Done."); } }
public Result Execute( ExternalCommandData commandData, ref string message, ElementSet elements) { UIApplication uiapp = commandData.Application; UIDocument uidoc = uiapp.ActiveUIDocument; Application app = uiapp.Application; Document doc = uidoc.Document; // creating bfs document instance BfsDocument bfsDocument = new BfsDocument(doc); // collecting schedules data from Revit List <List <List <string> > > schedulesData = getSchedulesData(doc); List <List <List <string> > > revisionData = getRevisionData(doc); if (schedulesData == null || revisionData == null) { return(Result.Cancelled); } // asking file name to save System.Windows.Forms.SaveFileDialog saveFileDialog = new System.Windows.Forms.SaveFileDialog(); saveFileDialog.InitialDirectory = doc.PathName; saveFileDialog.Title = "Salvar"; saveFileDialog.DefaultExt = ".xlsx"; saveFileDialog.Filter = "Excel file|*.xlsx"; saveFileDialog.FileName += string.Format("{0}-QTD-Rev.{1}", bfsDocument.Codigo, bfsDocument.Revisao); saveFileDialog.OverwritePrompt = false; if (saveFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK) { // creating export to excel instance ExportToExcel exportToExcel = new ExportToExcel( saveFileDialog.FileName, "Planilha de quantidades", schedulesData, revisionData); // solving export exportToExcel.Export(ExportToExcel.TemplateType.QUANTIDADES, bfsDocument); // running export() and getting the application created Excel._Application oXL = exportToExcel.Application; Excel._Workbook oWB = exportToExcel.Workbook; oXL.UserControl = false; try { // saving and closing oWB.SaveAs(saveFileDialog.FileName, Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } catch (Exception) { System.Windows.Forms.MessageBox.Show("O arquivo a ser substituído está aberto.", "Erro"); return(Result.Failed); } oWB.Close(); oXL.Quit(); // launching the file System.Diagnostics.Process.Start(saveFileDialog.FileName); return(Result.Succeeded); } return(Result.Cancelled); }
private void reportQuiz() { Excel.Workbooks objBooks; Excel.Sheets objSheets; Excel._Worksheet objSheet; StreamWriter streamWritertxt; string MeanTime = MeanResponseTime().ToString(); string MeanTrueTime = MeanTrueAnswersResponseTime().ToString(); string MeanFalseTime = MeanFalseAnswersResponseTime().ToString(); if (dataTableQuizResults.Rows.Count != 0) { dataTableQuizResults.Rows[0]["Time"] = DBNull.Value; dataTableQuizResults.Rows[0]["Accepted"] = DBNull.Value; dataGridView1[3, 0].Style.BackColor = Color.White; } dataGridView1.EndEdit(); btnStartStop.Select(); // I did this to change control to s.th other than datagridview to have my changes accepted tbxCorrectAnswers.Text = dataTableQuizResults.Compute("Count(Accepted)", "Accepted = TRUE").ToString(); // Set saveFileDialog1 Properties saveFileDialog1.Filter = "Both Excel and Text Files (*.xls;*.txt)|*.*|Excel Files (*.xls)|*.*|Text Files (*.txt)|*.*"; saveFileDialog1.FilterIndex = 1; try { if (saveFileDialog1.ShowDialog() == DialogResult.OK) { if (saveFileDialog1.FilterIndex != 3) { // Instantiate Excel and start a new workbook. System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); objApp = new Excel.Application(); objBooks = objApp.Workbooks; objBook = objBooks.Add(Missing.Value); objSheets = objBook.Worksheets; objSheet = (Excel._Worksheet)objSheets.get_Item(1); //Quiz Properties objSheet.Cells[1, 6] = "Quiz Type"; if (radioBtnBoth.Checked) { objSheet.Cells[1, 7] = "Both"; } else if (radioBtnPASAT.Checked) { objSheet.Cells[1, 7] = "PASAT"; } else { objSheet.Cells[1, 7] = "PVSAT"; } objSheet.Cells[2, 6] = "Response Time Limit"; objSheet.Cells[2, 7] = numUpDnResponseTime.Value.ToString(); objSheet.Cells[3, 6] = "Automatic Query"; objSheet.Cells[3, 7] = ChkBxAutomatic.Checked.ToString(); // Patient's identification objSheet.Cells[5, 6] = "Name"; objSheet.Cells[5, 7] = tbxName.Text; objSheet.Cells[6, 6] = "Age"; objSheet.Cells[6, 7] = tbxAge.Text; //Quiz Summary objSheet.Cells[8, 6] = "Number of Correct Answers"; objSheet.Cells[8, 7] = tbxCorrectAnswers.Text; objSheet.Cells[9, 6] = "Total Mean Time"; objSheet.Cells[9, 7] = MeanTime; objSheet.Cells[10, 6] = "Correct Answers Mean Time"; objSheet.Cells[10, 7] = MeanTrueTime; objSheet.Cells[11, 6] = "Incorrect Answers Mean Time"; objSheet.Cells[11, 7] = MeanFalseTime; // Fill Column Headers objSheet.Cells[1, 1] = dataTableQuizResults.Columns[0].ColumnName; objSheet.Cells[1, 2] = dataTableQuizResults.Columns[1].ColumnName; objSheet.Cells[1, 3] = dataTableQuizResults.Columns[2].ColumnName; objSheet.Cells[1, 4] = dataTableQuizResults.Columns[3].ColumnName; // Fill other Fields for (int iRow = 0; iRow < dataTableQuizResults.Rows.Count; iRow++) { for (int iCol = 0; iCol < dataTableQuizResults.Columns.Count; iCol++) { objSheet.Cells[iRow + 2, iCol + 1] = dataTableQuizResults.Rows[iRow][iCol].ToString(); } } objSheet.Columns.AutoFit(); // Save Excel WorkBook objBook.SaveAs(@saveFileDialog1.FileName + ".xls", Excel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); objApp.Quit(); System.Threading.Thread.CurrentThread.CurrentCulture = oldCI; } if (saveFileDialog1.FilterIndex != 2) { // Instantiate File Stream streamWritertxt = File.CreateText(@saveFileDialog1.FileName + ".txt"); // Write Quiz Properties streamWritertxt.WriteLine("Quiz Properties:"); if (radioBtnBoth.Checked) { streamWritertxt.WriteLine("Quiz Type\t" + "Both"); } else if (radioBtnPASAT.Checked) { streamWritertxt.WriteLine("Quiz Type\t" + "PASAT"); } else { streamWritertxt.WriteLine("Quiz Type\t" + "PVSAT"); } streamWritertxt.WriteLine("Response Time Limit\t" + numUpDnResponseTime.Value.ToString()); streamWritertxt.WriteLine("Automatic Query\t" + ChkBxAutomatic.Checked.ToString()); streamWritertxt.WriteLine("---------------------------------------------------------"); streamWritertxt.WriteLine(); // Patient's identification streamWritertxt.WriteLine("Name\t" + tbxName.Text); streamWritertxt.WriteLine("Age\t" + tbxAge.Text); streamWritertxt.WriteLine(); // //Quiz Summary streamWritertxt.WriteLine("Number of Correct Answers\t" + tbxCorrectAnswers.Text); streamWritertxt.WriteLine("Total Mean Time\t" + MeanTime); streamWritertxt.WriteLine("Correct Answers Mean Time\t" + MeanTrueTime); streamWritertxt.WriteLine("Incorrect Answers Mean Time\t" + MeanFalseTime); streamWritertxt.WriteLine(); // Write Column Headers streamWritertxt.WriteLine(dataTableQuizResults.Columns[0].ColumnName + "\t" + dataTableQuizResults.Columns[1].ColumnName + "\t" + dataTableQuizResults.Columns[2].ColumnName + "\t" + dataTableQuizResults.Columns[3].ColumnName); // Fill other Fields for (int iRow = 0; iRow < dataTableQuizResults.Rows.Count; iRow++) { for (int iCol = 0; iCol < dataTableQuizResults.Columns.Count; iCol++) { streamWritertxt.Write(dataTableQuizResults.Rows[iRow][iCol].ToString() + "\t"); } streamWritertxt.WriteLine(); } // Save Text File streamWritertxt.Close(); } } } catch (Exception theException) { String errorMessage; errorMessage = "Error: "; errorMessage = String.Concat(errorMessage, theException.Message); errorMessage = String.Concat(errorMessage, " Line: "); errorMessage = String.Concat(errorMessage, theException.Source); MessageBox.Show(errorMessage, "Error"); } }
public int CreateDataBase(string fileName, ProductParameter prdParm, ProductBatInfo prdBatInfo) { try { fName = fileName; // Check if the file is existing xls_exp = new Microsoft.Office.Interop.Excel.ApplicationClass(); if (System.IO.File.Exists(fileName)) { xls_book = xls_exp.Workbooks.Open(fileName, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); if (xls_book != null) { // find the last index xls_sheet = (Microsoft.Office.Interop.Excel._Worksheet)xls_book.Worksheets.get_Item(1); int i = xls_sheet.UsedRange.Rows.Count; if (i >= 9) { startIndex = 9; } else { startIndex = i + 1; } index = i + 1; Close(); return(0); } } // Create a new file //xls_exp = new Microsoft.Office.Interop.Excel.ApplicationClass(); xls_book = xls_exp.Workbooks.Add(true); xls_sheet = (Microsoft.Office.Interop.Excel._Worksheet)xls_book.Worksheets.get_Item(1); object misValue = System.Reflection.Missing.Value; //xls_sheet.Cells[1, 1] = "客户编号"; //xls_sheet.Cells[1, 2] = prdBatInfo.customCode; //xls_sheet.Cells[2, 1] = "随工单号"; //xls_sheet.Cells[2, 2] = prdBatInfo.suigongCode; //xls_sheet.Cells[3, 1] = "操作员"; //xls_sheet.Cells[3, 2] = prdBatInfo.operatorCode; //xls_sheet.Cells[4, 1] = "入纤功率"; //xls_sheet.Cells[4, 2] = prdBatInfo.ruQianPower.ToString() + "dBm"; //xls_sheet.Cells[5, 1] = "机器号"; //xls_sheet.Cells[5, 2] = prdParm.MachineCode; //xls_sheet.Cells[6, 1] = "测试序号"; //xls_sheet.Cells[6, 2] = "VPP(MV)"; //xls_sheet.Cells[6, 3] = "耦合时间"; xls_sheet.Cells[6, 1] = "目标值(mV/uA)"; xls_sheet.Cells[6, 2] = prdParm.MinimumVpp.ToString(); xls_sheet.Cells[7, 1] = "生产信息"; xls_sheet.Cells[8, 1] = "Index"; xls_sheet.Cells[8, 2] = "产品编号"; xls_sheet.Cells[8, 3] = "Vpp(mV)/Iop(uA)"; xls_sheet.Cells[8, 4] = "Vbr(V)"; xls_sheet.Cells[8, 5] = "LX1(um)"; xls_sheet.Cells[8, 6] = "LY1(um)"; xls_sheet.Cells[8, 7] = "LZ(um)"; xls_sheet.Cells[8, 8] = "LX2(um)"; xls_sheet.Cells[8, 9] = "LY2(um)"; xls_sheet.Cells[8, 10] = "Coupling Time(S)"; xls_sheet.Cells[8, 11] = "Total Time(S)"; xls_sheet.Cells[8, 12] = "Date in producted"; xls_sheet.Cells[8, 13] = "Vpp/Iop_10V(mV/uA)"; xls_sheet.Cells[8, 14] = "Vpp/Iop_Check(mV/uA)"; xls_book.Saved = true; xls_book.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); //xls_book.Save(); startIndex = 9; index = 9; Close(); } catch (Exception ee) { Close(); error = ee.Message; return(-1); } return(0); }
/// <summary> /// 导出数据到excel文件 /// </summary> /// <param name="dt">要导出的数据集</param> /// <returns>生成的文件名</returns> static public string ExportToExcel(DataTable dt) { Excel.Application excelApp = null; Excel.Workbooks wbks = null; Excel._Workbook wbk = null; try { excelApp = new Excel.Application(); excelApp.Visible = false;//是打开不可见 wbks = excelApp.Workbooks; wbk = wbks.Add(true); System.Windows.Forms.SaveFileDialog saveFileDialog = new System.Windows.Forms.SaveFileDialog(); saveFileDialog.Title = "保存为"; saveFileDialog.Filter = "xls工作薄|*.xls|xlsx工作薄|*.xlsx"; String version = excelApp.Version; //获取你使用的excel 的版本号 int FormatNum; //保存excel文件的格式 if (Convert.ToDouble(version) < 12) //You use Excel 97-2003 { { FormatNum = -4143; } } else//you use excel 2007 or later { { FormatNum = 56; } } object Nothing = Missing.Value; Excel._Worksheet whs; whs = (Excel._Worksheet)wbk.Sheets[1];//获取第一张工作表 whs.Activate(); //写入标题行 int rowIndex = 1; for (int col = 0; col < dt.Columns.Count; col++) { whs.Cells[rowIndex, col + 1] = dt.Columns[col].Caption.ToString(); } rowIndex++; //写入数据内容 foreach (DataRow row in dt.Rows) { for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++) { whs.Cells[rowIndex, colIndex + 1] = row[colIndex].ToString(); } rowIndex++; } excelApp.DisplayAlerts = false; //保存excel文件 //wbk.SaveCopyAs(@"D:\test.xls"); string newFileName = string.Empty; if (saveFileDialog.ShowDialog() == DialogResult.OK && saveFileDialog.FileName.Trim() != "") { newFileName = saveFileDialog.FileName; wbk.SaveAs(newFileName, FormatNum); System.Windows.Forms.MessageBox.Show("数据已经成功导入EXCEL文件" + newFileName, "数据导出", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); } //关闭文件 wbk.Close(false, Nothing, Nothing); return(newFileName); } catch (Exception e) { System.Windows.Forms.MessageBox.Show(e.ToString()); return("EXPORT ERROR"); } finally { //wbks.Close();//关闭工作簿 excelApp.Quit(); //关闭excel应用程序 System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); //释放excel进程 excelApp = null; } }
public ActionResult DownloadMonthlyActiveBeneficiariesSummaryReport([Bind(Include = "SequenceID")] TrimesterIntervalViewModel trimesterIntervalDTO) { var fileName = @"~\Templates\SummaryReports\monthly-active-beneficiaries-summary.xlsx"; Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); Excel._Workbook workbook = excelApp.Workbooks.Open(Server.MapPath(fileName)); //Excel._Worksheet worksheet = excelApp.ActiveSheet; DateTime CurrentTrimesterInitialDate = trimesterQueryService.getTrimesterStartOrEndDateByID(trimesterIntervalDTO.SequenceID, "start"); DateTime CurrentTrimesterLastDate = trimesterQueryService.getTrimesterStartOrEndDateByID(trimesterIntervalDTO.SequenceID, "end"); string evaluationType = "ReportData"; // Worksheet 2, grouped by partner Excel._Worksheet worksheet = excelApp.Worksheets[2]; worksheet.Range["AG2:AN2"].Value = "Período de " + CurrentTrimesterInitialDate.ToShortDateString() + " à " + CurrentTrimesterLastDate.ToShortDateString(); var row = 6; var column = 0; List <EFDataAccess.DTO.MonthlyActiveBeneficiariesSummaryReportDTO> dataPartner = summaryReports.getMonthlyActiveBeneficiariesSummaryPartner(CurrentTrimesterInitialDate, CurrentTrimesterLastDate); foreach (var refType in dataPartner) { row++; column = 1; foreach (var item in refType.PopulatedValues()) { column++; worksheet.Cells[row, column] = item; } worksheet.Rows[row + 1].Insert(); } // Worksheet 1, grouped by head partner worksheet = excelApp.Worksheets[1]; worksheet.Range["AG2:AN2"].Value = "Período de " + CurrentTrimesterInitialDate.ToShortDateString() + " à " + CurrentTrimesterLastDate.ToShortDateString(); row = 6; column = 0; List <EFDataAccess.DTO.MonthlyActiveBeneficiariesSummaryReportDTO> dataChiefPartner = summaryReports.getMonthlyActiveBeneficiariesSummaryChiefPartner(CurrentTrimesterInitialDate, CurrentTrimesterLastDate); foreach (var refType in dataChiefPartner) { row++; column = 1; foreach (var item in refType.PopulatedValues()) { column++; worksheet.Cells[row, column] = item; } worksheet.Rows[row + 1].Insert(); } var temporaryFilepath = Path.ChangeExtension(Path.GetTempFileName(), ".xlsx"); workbook.SaveAs(temporaryFilepath); workbook.Close(0); excelApp.Quit(); DownloadXlsxFile(temporaryFilepath); return(RedirectToAction("MonthlyActiveBeneficiariesSummaryReport")); }
private void button1_Click(object sender, EventArgs e) { //int columnWidth = 0; // Daten speichern try { // creating Excel Application Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); // creating new WorkBook within Excel application Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); // creating new Excelsheet in workbook Excel._Worksheet worksheet = null; // see the excel sheet behind the program app.Visible = false; // get the reference of first sheet. By default its name is Sheet1. // store its reference to worksheet worksheet = workbook.Worksheets.get_Item(1); worksheet = workbook.ActiveSheet; // Set Orientation to Landscape worksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; // changing the name of active sheet worksheet.Name = "Jahresrangliste"; // storing title in Excel worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, dataGridView1.Columns.Count]].Merge(false); worksheet.Cells[1, 1] = label4.Text; worksheet.Cells[1, 1].Font.Bold = true; worksheet.Cells[1, 1].Font.Size = 20; worksheet.Cells[1, 1].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; worksheet.Cells[1, 1].Interior.Color = ColorTranslator.ToOle(Color.Gray); worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, dataGridView1.Columns.Count]].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic); // storing header part in Excel for (int i = 1; i < dataGridView1.Columns.Count + 1; i++) { if (i > 3 && i < dataGridView1.Columns.Count) { worksheet.Cells[3, i] = "Lauf " + (i - 3).ToString(); } worksheet.Cells[2, i].Font.Bold = true; worksheet.Cells[2, i].Font.Size = 12; worksheet.Cells[2, i].Interior.Color = ColorTranslator.ToOle(Color.LightGray); worksheet.Cells[2, i].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic); if (i > 3 && i < dataGridView1.Columns.Count) { worksheet.Cells[2, i] = dataGridView1.Columns[i - 1].HeaderText; } else { worksheet.Cells[3, i] = dataGridView1.Columns[i - 1].HeaderText; } worksheet.Cells[3, i].Font.Bold = true; worksheet.Cells[3, i].Interior.Color = ColorTranslator.ToOle(Color.LightGray); worksheet.Cells[3, i].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic); } // storing Each row and column value to excel sheet for (int i = 0; i < dataGridView1.Rows.Count; i++) { for (int j = 0; j < dataGridView1.Columns.Count; j++) { try { worksheet.Cells[i + 4, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString(); } catch //Fängt Ausnahmen ab wenn Zellen leer sind { } worksheet.Cells[i + 4, j + 1].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic); } } worksheet.Range[worksheet.Cells[dataGridView1.Rows.Count + 4, 1], worksheet.Cells[dataGridView1.Rows.Count + 4, dataGridView1.Columns.Count]].Merge(false); worksheet.Cells[dataGridView1.Rows.Count + 4, 1] = DateTime.Today.ToString("d"); worksheet.Cells[dataGridView1.Rows.Count + 4, 1].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; // Set column width worksheet.UsedRange.Columns.AutoFit(); // for(int j = 1; j < dataGridView1.Columns.Count; j++) // { // if(columnWidth < worksheet.Cells[3,j].width) columnWidth = (int)worksheet.Cells[3, j].width; // } //worksheet.UsedRange.ColumnWidth = 15; // save the application workbook.SaveAs(path + "\\" + jahr + "_" + klasse + "_0_Jahresrangliste" + ".xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // export PDF worksheet.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, path + "\\" + jahr + "_" + klasse + "_0_Jahresrangliste" + ".pdf"); // Exit from the application app.Quit(); } catch { } }
protected byte[] getPhoneCalls() { string date = ""; if (RequestArr != null && RequestArr.Length > 0) { foreach (string req_str in RequestArr) { string[] p = req_str.Split("=".ToCharArray()); if (p[0] == "date") { date = p[1].Replace("%20", " "); } } } if (date == "") { date = DateTime.Today.ToString(); } //DateTime dt = Convert.ToDateTime(date); DB db = new DB(); List <GetPhoneCallsData> phoneCalls = db.getPhoneCalls(date); if (data_type == "xml") { string answer = ""; Log.add("Формируем xml файл GetPhoneCalls"); ContentType = "xml"; // Create XML answer += "<phone_calls>"; foreach (GetPhoneCallsData phoneCall in phoneCalls) { answer += "<phone_call>"; if (!String.IsNullOrEmpty(phoneCall.CompanyId.ToString())) { answer += "<CompanyId>" + phoneCall.CompanyId.ToString() + "</CompanyId>"; } if (!String.IsNullOrEmpty(phoneCall.CompanyName)) { answer += "<CompanyName>" + phoneCall.CompanyName + "</CompanyName>"; } if (!String.IsNullOrEmpty(phoneCall.PhoneCallId.ToString())) { answer += "<PhoneCallId>" + phoneCall.PhoneCallId.ToString() + "</PhoneCallId>"; } if (!String.IsNullOrEmpty(phoneCall.PhoneCallNr)) { answer += "<PhoneCallNr>" + phoneCall.PhoneCallNr + "</PhoneCallNr>"; } if (!String.IsNullOrEmpty(phoneCall.Pin)) { answer += "<Pin>" + phoneCall.Pin + "</Pin>"; } if (!String.IsNullOrEmpty(phoneCall.CustomerName)) { answer += "<CustomerName>" + phoneCall.CustomerName + "</CustomerName>"; } if (!String.IsNullOrEmpty(phoneCall.Inn)) { answer += "<Inn>" + phoneCall.Inn + "</Inn>"; } if (!String.IsNullOrEmpty(phoneCall.Kpp)) { answer += "<Kpp>" + phoneCall.Kpp + "</Kpp>"; } if (!String.IsNullOrEmpty(phoneCall.Email)) { answer += "<Email>" + phoneCall.Email + "</Email>"; } if (!String.IsNullOrEmpty(phoneCall.BitrixId)) { answer += "<BitrixId>" + phoneCall.BitrixId + "</BitrixId>"; } if (!String.IsNullOrEmpty(phoneCall.ScheduledStart.ToString())) { answer += "<ScheduledStart>" + phoneCall.ScheduledStart.ToString() + "</ScheduledStart>"; } if (!String.IsNullOrEmpty(phoneCall.Overview)) { answer += "<Overview>" + phoneCall.Overview + "</Overview>"; } if (!String.IsNullOrEmpty(phoneCall.Priority.ToString())) { answer += "<Priority>" + phoneCall.Priority.ToString() + "</Priority>"; } if (!String.IsNullOrEmpty(phoneCall.EventNr.ToString())) { answer += "<EventNr>" + phoneCall.EventNr.ToString() + "</EventNr>"; } if (!String.IsNullOrEmpty(phoneCall.DateEvent.ToString())) { answer += "<DateEvent>" + phoneCall.DateEvent.ToString() + "</DateEvent>"; } if (!String.IsNullOrEmpty(phoneCall.ProductEvent)) { answer += "<ProductEvent>" + phoneCall.ProductEvent + "</ProductEvent>"; } if (!String.IsNullOrEmpty(phoneCall.Phone)) { answer += "<Phone>" + phoneCall.Phone + "</Phone>"; } if (!String.IsNullOrEmpty(phoneCall.EventName)) { answer += "<EventName>" + phoneCall.EventName + "</EventName>"; } if (!String.IsNullOrEmpty(phoneCall.TimeDifference.ToString())) { answer += "<TimeDifference>" + phoneCall.TimeDifference.ToString() + "</TimeDifference>"; } answer += "</phone_call>"; } answer += "</phone_calls>"; return(Encoding.UTF8.GetBytes(answer)); } if (data_type == "xls") { var excelApp = new Excel.Application(); excelApp.Visible = false; Excel._Workbook workBook = excelApp.Workbooks.Add(); Excel._Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(1); byte[] answer; Log.add("Формируем xls файл GetPhoneCalls"); ContentType = "xls"; //delete old xls file if (File.Exists("phone_calls.xls")) { File.Delete("phone_calls.xls"); } workSheet.Cells[1, "A"] = "ID Компании"; workSheet.Cells[1, "B"] = "Наименование кампании"; workSheet.Cells[1, "C"] = "ИД звонка"; workSheet.Cells[1, "D"] = "№ звонка"; workSheet.Cells[1, "E"] = "Пин клиента"; workSheet.Cells[1, "F"] = "Наименование клиента"; workSheet.Cells[1, "G"] = "ИНН"; workSheet.Cells[1, "H"] = "КПП"; workSheet.Cells[1, "I"] = "E-mail"; workSheet.Cells[1, "J"] = "BitrixId"; workSheet.Cells[1, "K"] = "Назначенная дата звонка"; workSheet.Cells[1, "L"] = "Презентация"; workSheet.Cells[1, "M"] = "Приоритет"; workSheet.Cells[1, "N"] = "№ события"; workSheet.Cells[1, "O"] = "Дата события"; workSheet.Cells[1, "P"] = "Продукт по событию"; workSheet.Cells[1, "Q"] = "Телефон"; workSheet.Cells[1, "R"] = "Событие"; workSheet.Cells[1, "S"] = "Часовая разница"; // Start from 1 col int i = 2; // Write data to xls foreach (GetPhoneCallsData phoneCall in phoneCalls) { if (!String.IsNullOrEmpty(phoneCall.CompanyId.ToString())) { workSheet.Cells[i, "A"] = phoneCall.CompanyId.ToString(); } if (!String.IsNullOrEmpty(phoneCall.CompanyName)) { workSheet.Cells[i, "B"] = phoneCall.CompanyName; } if (!String.IsNullOrEmpty(phoneCall.PhoneCallId.ToString())) { workSheet.Cells[i, "C"] = phoneCall.PhoneCallId.ToString(); } if (!String.IsNullOrEmpty(phoneCall.PhoneCallNr)) { workSheet.Cells[i, "D"] = phoneCall.PhoneCallNr; } if (!String.IsNullOrEmpty(phoneCall.Pin)) { workSheet.Cells[i, "E"] = phoneCall.Pin; } if (!String.IsNullOrEmpty(phoneCall.CustomerName)) { workSheet.Cells[i, "F"] = phoneCall.CustomerName; } if (!String.IsNullOrEmpty(phoneCall.Inn)) { workSheet.Cells[i, "G"] = phoneCall.Inn; } if (!String.IsNullOrEmpty(phoneCall.Kpp)) { workSheet.Cells[i, "H"] = phoneCall.Kpp; } if (!String.IsNullOrEmpty(phoneCall.Email)) { workSheet.Cells[i, "I"] = phoneCall.Email; } if (!String.IsNullOrEmpty(phoneCall.BitrixId)) { workSheet.Cells[i, "J"] = phoneCall.BitrixId; } if (!String.IsNullOrEmpty(phoneCall.ScheduledStart.ToString())) { workSheet.Cells[i, "K"] = phoneCall.ScheduledStart.ToString(); } if (!String.IsNullOrEmpty(phoneCall.Overview)) { workSheet.Cells[i, "L"] = phoneCall.Overview; } if (!String.IsNullOrEmpty(phoneCall.Priority.ToString())) { workSheet.Cells[i, "M"] = phoneCall.Priority.ToString(); } if (!String.IsNullOrEmpty(phoneCall.EventNr.ToString())) { workSheet.Cells[i, "N"] = phoneCall.EventNr.ToString(); } if (!String.IsNullOrEmpty(phoneCall.DateEvent.ToString())) { workSheet.Cells[i, "O"] = phoneCall.DateEvent.ToString(); } if (!String.IsNullOrEmpty(phoneCall.ProductEvent)) { workSheet.Cells[i, "P"] = phoneCall.ProductEvent; } if (!String.IsNullOrEmpty(phoneCall.Phone)) { workSheet.Cells[i, "Q"] = phoneCall.Phone; } if (!String.IsNullOrEmpty(phoneCall.EventName)) { workSheet.Cells[i, "R"] = phoneCall.EventName; } if (!String.IsNullOrEmpty(phoneCall.TimeDifference.ToString())) { workSheet.Cells[i, "S"] = phoneCall.TimeDifference.ToString(); } i++; } // Write xls Random rnd = new Random(); string fname = Application.StartupPath + @"\phone_calls_" + rnd.Next(1000, 9000).ToString() + ".xlsx"; workBook.SaveAs(fname, Excel.XlFileFormat.xlOpenXMLWorkbook); workBook.Close(Type.Missing, Type.Missing, Type.Missing); excelApp.Quit(); Marshal.FinalReleaseComObject(workSheet); Marshal.FinalReleaseComObject(workBook); Marshal.FinalReleaseComObject(excelApp); //workbook.SaveToFile("phone_calls.xls"); // Read xls answer = File.ReadAllBytes(fname); // Delete xls if (File.Exists(fname)) { File.Delete(fname); } //send xls to client return(answer); } return(Encoding.UTF8.GetBytes("")); }
public static void Open_xls_Files() { // creating Excel Application Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); // creating new WorkBook within Excel application Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Open(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + @"\SPA特价.xlsx"); //app.Workbooks.Add(Type.Missing); // creating new Excelsheet in workbook Microsoft.Office.Interop.Excel._Worksheet worksheet = workbook.Sheets[1]; // see the excel sheet behind the program app.Visible = false; // get the reference of first sheet. By default its name is Sheet1. // store its reference to worksheet worksheet = workbook.Sheets["SPA台湾"]; worksheet = workbook.ActiveSheet; // changing the name of active sheet worksheet.Copy(Type.Missing, worksheet); Microsoft.Office.Interop.Excel.Worksheet wcSheet = workbook.Sheets[2]; wcSheet.Name = "ExSPA台湾"; wcSheet.UsedRange.UnMerge(); Console.WriteLine(wcSheet.UsedRange.Rows.Count); Range Ro1 = null; Range Rd1 = null; Range Ro2 = null; Range Rd2 = null; Range tempR = null; for (int i = 1; i <= wcSheet.UsedRange.Rows.Count; i++) { try { string s = wcSheet.UsedRange.Cells[i, 1].Value2.ToString(); Console.WriteLine(s); if (wcSheet.Cells[i, 1].Value2 != null)//DBNull) { if (s == "SPA: ") { Ro1 = wcSheet.Range[wcSheet.Cells[i, 1], wcSheet.Cells[i, 9]]; // Console.WriteLine("=============="+wcSheet.Cells[i, 1].Value2.ToString()); Rd1 = wcSheet.Range[wcSheet.Cells[i + 2, 10], wcSheet.Cells[i + 2, 19]]; Ro1.Cut(Rd1); ///////////////////////////////////// Ro2 = wcSheet.Range[wcSheet.Cells[i + 1, 1], wcSheet.Cells[i + 1, 9]]; // Console.WriteLine(Ro.Cells.Count); Rd2 = wcSheet.Range[wcSheet.Cells[i + 4, 10], wcSheet.Cells[i + 4, 19]]; Ro2.Cut(Rd2); // Console.WriteLine(wcSheet.UsedRange.Cells[i,1]); i = i + 4; } //Rd2.Copy(wcSheet.Range[wcSheet.Cells[i + 4, 10], wcSheet.Cells[i + 4, 19]]); tempR = wcSheet.Range[wcSheet.Cells[i, 10], wcSheet.Cells[i, 19]]; //= //Rd2.Copy(wcSheet.Range[wcSheet.Cells[i + 3, 10], wcSheet.Cells[i + 3, 19]]); Ro2.Copy(tempR); // tempR.Clear(); // Ro2.Clear(); } else if (wcSheet.Cells[i, 1].Value == "") { } } catch (Exception ex) { Console.WriteLine(ex.Message); } } //for (int i = 1; i <= wcSheet.UsedRange.Rows.Count; i++) //{ // try // { // string s = wcSheet.UsedRange.Cells[i, 1].Value2.ToString(); // Console.WriteLine(s); // if (wcSheet.Cells[i, 1].Value2 != null)//DBNull) // { // if (s == "SPA: ") // { // ///////////////////////////////////// // // Ro2 = wcSheet.Range[wcSheet.Cells[i + 1, 1], wcSheet.Cells[i + 1, 9]]; // // Console.WriteLine(Ro.Cells.Count); // // Rd2 = wcSheet.Range[wcSheet.Cells[i + 4, 10], wcSheet.Cells[i + 4, 19]]; // // Ro2.Cut(Rd2); // // Console.WriteLine(wcSheet.UsedRange.Cells[i,1]); // Ro1 = wcSheet.Range[wcSheet.Cells[i, 1], wcSheet.Cells[i, 9]]; // // Console.WriteLine(Ro.Cells.Count); // Rd1 = wcSheet.Range[wcSheet.Cells[i + 2, 10], wcSheet.Cells[i + 2, 19]]; // Ro1.Cut (Rd1); // } // //Rd2.Copy(wcSheet.Range[wcSheet.Cells[i + 4, 10], wcSheet.Cells[i + 4, 19]]); // // tempR = wcSheet.Range[wcSheet.Cells[i, 10], wcSheet.Cells[i, 19]]; // //= //Rd2.Copy(wcSheet.Range[wcSheet.Cells[i + 3, 10], wcSheet.Cells[i + 3, 19]]); // // Ro2.Copy(tempR); // } // } // catch (Exception ex) // { // Console.WriteLine(ex.Message); // } //} //// storing header part in Excel ////for (int i = 1; i < this.DGV1.Columns.Count + 1; i++) ////{ //// worksheet.Cells[1, i] = DGV1.Columns[i - 1].Header;//.HeaderText; // // worksheet.Columns.ColumnWidth //} //// storing Each row and column value to excel sheet //for (int i = 0; i < DGV1.Items.Count - 1; i++) //{ // for (int j = 0; j < DGV1.Columns.Count; j++) // { // // var drview= DGV1.Items[i]; // // worksheet.Cells[i + 2, j + 1] = .Cells[j].Value.ToString(); // // System.Data.DataRowView dataRow = drview; // DataRowView dataRow = (DataRowView)DGV1.Items[i];//.Row.//.Row; // worksheet.Cells[i + 2, j + 1] = dataRow[j].ToString();//dataRow.Row..ToString(); // // worksheet.Cells[i + 2, j + 1] // //MessageBox.Show(DGV1.Items[i].ToString()); // } //} // save the application // Microsoft.Office.Interop.Excel.Worksheet ws = app.ActiveWorkbook.Worksheets[1]; // Microsoft.Office.Interop.Excel.Range range = ws.UsedRange; // ws.Columns.AutoFit(); // ws.Rows.AutoFit(); try { Fname = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + @"\temp" + DateTime.Now.ToString().Trim().Replace(':', '_').Replace('/', '_').Trim() + ".xlsx"; workbook.SaveAs(Fname, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // Exit from the application // workbook.SaveCopyAs(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + @"\列宽output.xls"); } catch (Exception es) { // MessageBox.Show(es.Message); Console.WriteLine(es.Message); } // workbook.Saved = true; app.Quit(); Marshal.ReleaseComObject(app); Console.WriteLine("excel文件导出到当前app运行目录下,命名为output.xls,Excel将关闭退出~"); }
public void SaveResult(string path = @"D:\GoogleDrive\Roslesinforg\Дела\2020.07.14 - Ц\ОСВ 205.31 - result2.xlsx") { workbook.SaveAs(path); workbook.Close(); CloseApp(); }
private void exportDGV() { // Creating a Excel object. Excel._Application excel = new Excel.Application(); Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing); Excel._Worksheet worksheet = null; Excel.Range chartRange; int wrkRow = 0; try { worksheet = workbook.ActiveSheet; worksheet.Name = "ExportedFromDatGrid"; //Add or subtract from the count to determine which portion of the datagrid view is exported for (int i = 0; i < dgvDailyReport.Columns.Count - 1; i++) { worksheet.Cells[1, i + 1] = dgvDailyReport.Columns[i].HeaderText; } chartRange = worksheet.get_Range("a1", "l1"); chartRange.Font.Bold = true; chartRange.Font.Size = 15; chartRange.Columns.AutoFit(); chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); //Loop through each row and read value from each column. for (int worksheetrow = 2, ddgrow = 0; ddgrow < dgvDailyReport.Rows.Count; worksheetrow++, ddgrow++) { //Add or subtract from the count to determine which portion of the datagrid view is exported, this should most likely match to the above one for (int dgvcolumn = 0; dgvcolumn < dgvDailyReport.Columns.Count - 1; dgvcolumn++) { worksheet.Cells[worksheetrow, dgvcolumn + 1] = dgvDailyReport.Rows[ddgrow].Cells[dgvcolumn].Value.ToString(); if (ddgrow == dgvDailyReport.Rows.Count - 1) { if (dgvcolumn < 6) { worksheet.Cells[worksheetrow, dgvcolumn + 1] = dgvDailyReport.Rows[ddgrow].Cells[dgvcolumn].Value.ToString(); } else { break; } } } wrkRow = worksheetrow; } //gets range of cells and autosizes the data columns string cell2 = "l" + wrkRow.ToString(); chartRange = worksheet.get_Range("a2", cell2); chartRange.Font.Size = 11; chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.PeachPuff); //Puts a border around the actual data chartRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic); //Totals Row Changes cell2 = "l" + wrkRow.ToString(); string cell1 = "a" + wrkRow.ToString(); chartRange = worksheet.get_Range(cell1, cell2); //Borders to separate and organize the totals row chartRange = worksheet.get_Range("b" + wrkRow.ToString(), "c" + wrkRow.ToString()); chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Peru); chartRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic); chartRange = worksheet.get_Range("d" + wrkRow.ToString(), "e" + wrkRow.ToString()); chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Salmon); chartRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic); chartRange = worksheet.get_Range("f" + wrkRow.ToString(), "g" + wrkRow.ToString()); chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSalmon); chartRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic); //Getting the location and file name of the excel to save from user. SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"; saveDialog.FilterIndex = 2; saveDialog.FileName = DateTime.Now.ToString("yyyy-MM-dd hh_mm") + " " + "Daily Report.xlsx"; if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK) { workbook.SaveAs(saveDialog.FileName); MessageBox.Show("Export Successful"); } } catch (System.Exception ex) { MessageBox.Show(ex.Message); } finally { excel.Quit(); workbook = null; excel = null; } }
private void button_Click(object sender, RoutedEventArgs e) { for (int j = 0; j < ExcelData.ExcelDataValuesCumulative.Count(); j++) { try { //Start Excel and get Application object. oXL = new Microsoft.Office.Interop.Excel.Application(); oXL.Visible = true; //Get a new workbook. oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add("")); oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet; //set the middle column width as it acts a delimiter column only oSheet.Columns["C:C"].ColumnWidth = 2; oSheet.Columns["A:A"].ColumnWidth = 23; oSheet.Columns["B:B"].ColumnWidth = 23; oSheet.Columns["D:D"].ColumnWidth = 23; oSheet.Columns["E:E"].ColumnWidth = 23; oSheet.Rows.RowHeight = 22.5; oSheet.Rows.WrapText = true; #region RowWidth For Special ROws //Included rows - DOB, Email, Immigration History Table Headers oSheet.Rows[5].RowHeight = 30; oSheet.Rows[7].RowHeight = 30; oSheet.Rows[38].RowHeight = 30; oSheet.Rows[44].RowHeight = 30; #endregion #region Labels Only Include text bold code //Header Code oSheet.Cells[1, 1] = "General Information Sheet"; oSheet.Range[oSheet.Cells[1, 1], oSheet.Cells[1, 5]].Merge(); oSheet.Range[oSheet.Cells[1, 1], oSheet.Cells[1, 5]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //Last Name and First Name Code - Row 2 oSheet.Cells[2, 1] = "Last Name"; oSheet.Cells[2, 4] = "First Name"; //Middle Name and CWID Code - Row 3 oSheet.Cells[3, 1] = "Middle Name"; oSheet.Cells[3, 4] = "Banner ID (Student ID)"; //Visa Type and SEVIS ID Code - Row 4 oSheet.Cells[4, 1] = "Visa Type"; oSheet.Cells[4, 4] = "SEVIS ID"; //DOB and COB Code - Row 5 oSheet.Cells[5, 1] = "Date Of Birth (DD-MMM-YYYY)"; oSheet.Cells[5, 4] = "Country Of Birth"; //Gender and COC Code - Row 6 oSheet.Cells[6, 1] = "Gender"; oSheet.Cells[6, 4] = "Country Of Citizenship"; //OSU Email Address and Alternate Email Address Code - Row 7 oSheet.Cells[7, 1] = "OSU Email Address"; oSheet.Cells[7, 4] = "Alternate Email Address"; //Row 8 will be empty //Home Country Address Heading Only - Row 9 oSheet.Cells[9, 1] = "Home Country Address"; oSheet.Range[oSheet.Cells[9, 1], oSheet.Cells[9, 5]].Merge(); //Street Address 1 - Row 10 oSheet.Cells[10, 1] = "Street Address 1"; oSheet.Range[oSheet.Cells[10, 2], oSheet.Cells[10, 5]].Merge(); //Street Address 2 - Row 11 oSheet.Cells[11, 1] = "Street Address 2"; oSheet.Range[oSheet.Cells[11, 2], oSheet.Cells[11, 5]].Merge(); //City and State - Row 12 oSheet.Cells[12, 1] = "City"; oSheet.Cells[12, 4] = "State"; //Country and Zip Code - Row 13 oSheet.Cells[13, 1] = "Country"; oSheet.Cells[13, 4] = "Zip Code"; //Row 14 will be empty //Local/Temporary Address Heading Only Row 15 oSheet.Cells[15, 1] = "Local/Temporary Address"; oSheet.Range[oSheet.Cells[15, 1], oSheet.Cells[15, 5]].Merge(); //Street Address 1 - Row 16 oSheet.Cells[16, 1] = "Street Address 1"; oSheet.Range[oSheet.Cells[16, 2], oSheet.Cells[16, 5]].Merge(); //Street Address 2 - Row 17 oSheet.Cells[17, 1] = "Street Address 2"; oSheet.Range[oSheet.Cells[17, 2], oSheet.Cells[17, 5]].Merge(); //City and State - Row 18 oSheet.Cells[18, 1] = "City"; oSheet.Cells[18, 4] = "State"; //Country and Zip Code - Row 19 oSheet.Cells[19, 1] = "Country"; oSheet.Cells[19, 4] = "Zip Code"; //Local Phone - Row 20 oSheet.Cells[20, 1] = "Local Phone\n(Leave blank if you don't have a local phone now but update ISS once you do)"; oSheet.Range[oSheet.Cells[20, 1], oSheet.Cells[20, 4]].Merge(); oSheet.Range[oSheet.Cells[20, 1], oSheet.Cells[20, 4]].RowHeight = 27.75; //Row 21 will be empty //Emergency Contact Information Heading Only Row 22 oSheet.Cells[22, 1] = "Emergency Contact Information"; oSheet.Range[oSheet.Cells[22, 1], oSheet.Cells[22, 5]].Merge(); oSheet.Range[oSheet.Cells[22, 1], oSheet.Cells[22, 5]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //Explanation Text Row 23 oSheet.Cells[23, 1] = "Family Member, Friend or relative in the U.S. the ISS office should contact on your behalf in case of medical emergency (such as illness or accident). Please fill as much information as you can and update the ISS office with any changes"; oSheet.Range[oSheet.Cells[23, 1], oSheet.Cells[23, 5]].Merge(); oSheet.Range[oSheet.Cells[23, 1], oSheet.Cells[23, 5]].RowHeight = 45; //Row 24 will be empty //Name and Relationship Row 25 oSheet.Cells[25, 1] = "Name"; oSheet.Cells[25, 4] = "Relationship"; //Street Address 1 - Row 26 oSheet.Cells[26, 1] = "Street Address 1"; //Street Address 2 - Row 27 oSheet.Cells[27, 1] = "Street Address 2"; //City and State - Row 28 oSheet.Cells[28, 1] = "City"; oSheet.Cells[28, 4] = "State"; //Country and Zip Code - Row 29 oSheet.Cells[29, 1] = "Zip Code"; //Phone and Email Address - Row 30 oSheet.Cells[30, 1] = "Phone"; oSheet.Cells[30, 4] = "Email Address"; //Row 31 is empty //Row 32 is empty //Row 33 is empty //Previous semester field Row 34 oSheet.Cells[34, 1] = "If you previously attended OSU enter the last semester you attended(Eg. Fall 2016)"; oSheet.Range[oSheet.Cells[34, 1], oSheet.Cells[34, 4]].Merge(); //Row 35 is empty //Previously entered US heading Row 36 oSheet.Cells[36, 1] = "If you previously entered the U.S. provide information below:"; oSheet.Range[oSheet.Cells[36, 1], oSheet.Cells[36, 5]].Merge(); //Row 37 is empty //Approximate Date of Entry, Approximate Date of Exit, Immigration Status E.g. F1, F2 etc, Primary Activity E.g. High School, Exchange etc - Row 38 oSheet.Cells[38, 1] = "Approximate Date of Entry"; oSheet.Cells[38, 2] = "Approximate Date of Exit"; oSheet.Cells[38, 4] = "Immigration Status E.g. F1, F2 etc"; oSheet.Cells[38, 5] = "Primary Activity E.g. High School, Exchange etc"; //Row 39, 40, 41, 42, 43 is empty //Attestation Statement Row 44 oSheet.Cells[44, 1] = "I attest the information provided here is accurate"; oSheet.Range[oSheet.Cells[44, 1], oSheet.Cells[44, 5]].Merge(); //Signature and Date Row 45 oSheet.Cells[45, 1] = "Signature"; oSheet.Cells[45, 4] = "Date"; //Row 46, 47, 48, 49, 50 will be empty //For ISS use only heading Row 51 oSheet.Cells[51, 1] = "For ISS use only"; //Row 52 will be empty //Reviewed By and Date - Row 53 oSheet.Cells[53, 1] = "Reviewed By"; oSheet.Cells[53, 4] = "Date"; #endregion #region Bold Text for Labels //Make the labels bold for (int i = 1; i <= 53; i++) { oSheet.Cells[i, 1].Font.Bold = true; oSheet.Cells[i, 4].Font.Bold = true; } //left out cells that are not part of for loop oSheet.Cells[38, 2].Font.Bold = true; oSheet.Cells[38, 5].Font.Bold = true; //underline the cells oSheet.Cells[1, 1].Font.Underline = true; oSheet.Cells[22, 1].Font.Underline = true; #endregion #region Underline and Borders For Values for (int i = 2; i <= 45; i++) { //draw underlines for Street Address Fields if (i == 10 || i == 11 || i == 16 || i == 17 || i == 26 || i == 27) { oSheet.Cells[i, 2].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; oSheet.Cells[i, 3].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; oSheet.Cells[i, 4].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; oSheet.Cells[i, 5].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; } else if (i == 34 || i == 20) { oSheet.Cells[i, 5].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; continue; } //Blank Fields - Don't draw underlines else if (i == 8 || i == 9 || i == 10 || i == 14 || i == 15 || i == 20 || i == 21 || i == 22 || i == 23 || i == 24 || i == 31 || i == 32 || i == 33 || i == 34 || i == 35 || i == 36 || i == 37 || i == 38 || i == 39 || i == 40 || i == 41 || i == 42 || i == 43 || i == 44) { continue; } //Label Value Fields need single cell underlines oSheet.Cells[i, 2].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; oSheet.Cells[i, 5].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; } //Border for Immigration History oRng = oSheet.get_Range("A36", "E43"); oRng.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; oRng.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; oRng.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; oRng.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; oRng = oSheet.get_Range("A36", "E43"); oRng.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; oRng.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; oRng.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; oRng.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //Outside only Borders for ISS Signature oRng = oSheet.get_Range("A51", "E53"); oRng.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlDouble; oRng.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlDouble; oRng.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlDouble; oRng.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlDouble; #endregion #region Values From Excel Data ArrayList al = ExcelData.ExcelDataValuesCumulative.ElementAt(j); oSheet.Cells[2, 2] = al[0]; oSheet.Cells[2, 5] = al[1]; oSheet.Cells[3, 2] = al[2]; oSheet.Cells[3, 5] = al[3]; oSheet.Cells[4, 2] = al[4]; oSheet.Cells[4, 5] = al[5]; oRng = oSheet.get_Range("B5"); oRng.NumberFormat = "DD-MMM-YYYY"; oSheet.Cells[5, 2] = al[6]; oSheet.Cells[5, 5] = al[7]; oSheet.Cells[6, 2] = al[8]; oSheet.Cells[6, 5] = al[9]; oSheet.Cells[7, 2] = al[10]; oSheet.Cells[7, 5] = al[11]; oSheet.Cells[10, 2] = al[12]; oSheet.Cells[11, 2] = al[13]; oSheet.Cells[12, 2] = al[14]; oSheet.Cells[12, 5] = al[15]; oSheet.Cells[13, 2] = al[16]; oSheet.Cells[13, 5] = al[17]; var admitTerm = al[18]; //Extract year and semester from it var year = admitTerm.ToString().Substring(0, 4); var term = admitTerm.ToString().Substring(4); string termText = ""; if (term == "20") { termText = "Spring"; } else if (term == "40") { termText = "Summer"; } else if (term == "60") { termText = "Fall"; } oSheet.Cells[1, 1] = "General Information Sheet (" + termText + " " + year + ")"; //Adding Barcode Image to sheet Microsoft.Office.Interop.Excel.Range oRange = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[55, 2]; b.IncludeLabel = true; System.Drawing.Image img = b.Encode(BarcodeLib.TYPE.CODE39, al[3].ToString()); img.Save("C:\\images\\" + al[3] + ".jpg"); float Left = (float)((double)oRange.Left); float Top = (float)((double)oRange.Top); const float imageWidth = 327; const float imageHeight = 139; oSheet.Shapes.AddPicture("C:\\images\\" + al[3] + ".jpg", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, imageWidth, imageHeight); #endregion string fileName = al[0] + ", " + al[1]; // oSheet.Cells[56, 4] = al[3]; // oRange = oSheet.Cells[56, 4]; //oRange.Cells.Font.Size = 7; oSheet.PageSetup.Zoom = 92; oSheet.PageSetup.FitToPagesWide = 1; oWB.SaveAs(textBox1.Text + "\\" + fileName + ".xlsx"); oWB.Close(); oXL.Quit(); } catch (Exception ex) { } } MessageBox.Show("Process Completed Successfully"); }
public void ExcelCreate(String path, int day, Dictionary <int, String> em) { if (File.Exists(path)) { excelapp = new Excel.Application(); excelapp.Visible = true; excelapp.DisplayAlerts = false; wBook = excelapp.Workbooks.Open(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); DateTimeFormatInfo dfi = DateTimeFormatInfo.CurrentInfo; DateTime date = DateTime.Now; Calendar cal = dfi.Calendar; bool found = false; foreach (Excel.Worksheet sheet in wBook.Sheets) { if (sheet.Name == ("week" + cal.GetWeekOfYear(date, dfi.CalendarWeekRule, dfi.FirstDayOfWeek))) { found = true; } } if (found == true) { wSheet = wBook.Sheets[("week" + cal.GetWeekOfYear(date, dfi.CalendarWeekRule, dfi.FirstDayOfWeek))]; wSheet.Activate(); } else { Excel.Worksheet newWorksheet; newWorksheet = (Excel.Worksheet)wBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); newWorksheet.Name = ("week" + cal.GetWeekOfYear(date, dfi.CalendarWeekRule, dfi.FirstDayOfWeek)); wSheet = newWorksheet; wSheet.Activate(); NameTagInit(excelapp); } Excel.Range rng = excelapp.Range[excelapp.Cells[1, day], excelapp.Cells[em.Count + 2, day]]; rng.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Empty); excelapp.get_Range("A1", "J99").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; excelapp.Cells[1, day] = DateTime.Now.ToString("ddd"); //1,2 = 週一,1,3 = 週二 ... for (int i = 2; i < em.Count + 2; i++) { if (em.ElementAt(i - 2).Value.Equals("出席")) { excelapp.Cells[em.ElementAt(i - 2).Key + 1, day] = em.ElementAt(i - 2).Value; } else if (em.ElementAt(i - 2).Value.Equals("遲到")) { Excel.Range rang = excelapp.Range[excelapp.Cells[em.ElementAt(i - 2).Key + 1, day], excelapp.Cells[em.ElementAt(i - 2).Key + 1, day]]; rang.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue); excelapp.Cells[em.ElementAt(i - 2).Key + 1, day] = "出席"; } else { Excel.Range rang = excelapp.Range[excelapp.Cells[em.ElementAt(i - 2).Key + 1, day], excelapp.Cells[em.ElementAt(i - 2).Key + 1, day]]; rang.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); excelapp.Cells[em.ElementAt(i - 2).Key + 1, day] = em.ElementAt(i - 2).Value; } } try { wBook.SaveAs(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Console.WriteLine("儲存文件於 " + Environment.NewLine + path); } catch { MessageBox.Show("儲存檔案出錯,檔案可能正在使用", "錯誤:檔案無法儲存", MessageBoxButtons.OK, MessageBoxIcon.Error); } wBook.Close(false, Type.Missing, Type.Missing); excelapp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelapp); wBook = null; wSheet = null; excelapp = null; GC.Collect(); Console.Read(); } else { MessageBox.Show("檔案不存在,請確認路徑", "錯誤:路徑", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void ExportBtn_Click(object sender, EventArgs e) { if (Table.Rows.Count > 0) { SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = @"Excel (.xlsx)| *.xlsx"; sfd.FileName = "Output.xlsx"; bool fileError = false; if (sfd.ShowDialog() == DialogResult.OK) { if (File.Exists(sfd.FileName)) { try { File.Delete(sfd.FileName); } catch (IOException ex) { fileError = true; MessageBox.Show(Messages.ImpossibleToWriteData + ex.Message); } } if (!fileError) { try { Excel.Application xcelApp = new Excel.Application(); Excel._Workbook workbook = xcelApp.Workbooks.Add(Type.Missing); //worksheet = (Excel._Worksheet) workbook.Sheets["Sheet1"]; var worksheet = (Excel._Worksheet)workbook.ActiveSheet; worksheet.Name = "Output"; worksheet.Application.ActiveWindow.SplitRow = 1; worksheet.Application.ActiveWindow.FreezePanes = true; for (int i = 0; i < Table.Rows.Count; i++) { for (int j = 0; j < Table.Columns.Count; j++) { if (Table.Rows[i].Cells[j].Value != null) { worksheet.Cells[i + 1, j + 1] = Table.Rows[i].Cells[j].Value.ToString(); } } } worksheet.Columns.AutoFit(); workbook.SaveAs(sfd.FileName); xcelApp.Quit(); ReleaseObject(worksheet); ReleaseObject(workbook); ReleaseObject(xcelApp); MessageBox.Show(Messages.SuccesfulExport, @"Info"); } catch (Exception ex) { MessageBox.Show(@"Error :" + ex.Message); } } } } else { MessageBox.Show(Messages.NoRecord, @"Info"); } }
public static void CreateWorkbook(string suf, int MaxColumnLength, string dir, int MinG, int MaxG, int MinR, int MaxR) { var dirs = FileReader.DigForFiles(dir, suf); // creating Excel Application Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); // creating new WorkBook within Excel application Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); // creating new Excelsheet in workbook Microsoft.Office.Interop.Excel._Worksheet worksheet = null; Sheets xlSheets = null; xlSheets = workbook.Sheets as Sheets; // see the excel sheet behind the program app.Visible = false; //Select the sheet worksheet = workbook.Worksheets[1]; //Rename the sheet worksheet.Name = "Summary"; string[] names = GetNames(dirs); Console.WriteLine("Processing Files:"); for (int i = 0; i < names.Length; i++) { Console.WriteLine(names[i]); Microsoft.Office.Interop.Excel._Workbook csvWorkbook = app.Workbooks.Open(dirs[i]); Microsoft.Office.Interop.Excel._Worksheet worksheetCSV = ((Microsoft.Office.Interop.Excel._Worksheet)csvWorkbook.Worksheets[1]); worksheetCSV.Copy(xlSheets[1]); xlSheets[1].Name = names[i]; ((_Worksheet)xlSheets[1]).Cells[1, 24] = "Mean_Cell - Mean_Noise"; for (int row = 2; row < MaxColumnLength; row++) { ((_Worksheet)xlSheets[1]).Cells[row, 24] = "=E" + row + "-U" + row; } ((_Worksheet)xlSheets[1]).Cells[1, 25] = "Max_Cell"; ((_Worksheet)xlSheets[1]).Cells[2, 25] = "=MAX(X2:X" + MaxColumnLength + ")"; // Exit from the application csvWorkbook.Close(); } worksheet.Move(Before: workbook.Sheets[1]); Console.WriteLine("Preparing the summary..."); //worksheet.Cells[row, column] = "=cell57_Q2_Ch0_Green_Results!E2"; int currentColumn = 1; int currentRow = 1; int increment = names.Length; for (currentRow = 1; currentRow < MaxColumnLength; currentRow++) { worksheet.Cells[currentRow, currentColumn] = "=" + names[0] + "!C" + currentRow; } currentRow = 1; for (int i = 0; i < names.Length; i++) { //Area cell currentColumn = i + 2; worksheet.Cells[currentRow, currentColumn] = "Area_Cell_" + names[i]; //Mean cell currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "Mean_Cell_" + names[i]; //Area noise currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "Area_Noise_" + names[i]; //Mean noise currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "Mean_Noise_" + names[i]; //Area spots currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "Area_Spot_" + names[i]; //Mean spots currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "Mean_Spot_" + names[i]; //Mean-Noise cell currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "Mean-Noise_Cell_" + names[i]; //Max cell currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "Max_Cell_" + names[i]; worksheet.Cells[currentRow + 1, currentColumn] = "=" + names[i] + "!Y2"; //Max-Results currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "Max-Results_" + names[i]; //Results currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "Results_" + names[i]; //ResultsTo0 currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "To0_Results_" + names[i]; //ResultsTo1 currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "nTo0_Results_" + names[i]; } //Mean result worksheet.Cells[currentRow, currentColumn + 1] = "nAvgMob"; worksheet.Cells[currentRow, currentColumn + 2] = "nnAvgMob"; worksheet.Cells[currentRow, currentColumn + 3] = "nStDevMob"; worksheet.Cells[currentRow, currentColumn + 4] = "AvgMob"; worksheet.Cells[currentRow, currentColumn + 5] = "StDevMob"; int spotSignal, MaxCell, CellMinusNoise; for (currentRow = 2; currentRow < MaxColumnLength; currentRow++) { for (int i = 0; i < names.Length; i++) { //Area cell currentColumn = i + 2; worksheet.Cells[currentRow, currentColumn] = "=" + names[i] + "!D" + currentRow; //Mean cell currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "=" + names[i] + "!E" + currentRow; //Area noise currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "=" + names[i] + "!T" + currentRow; //Mean noise currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "=" + names[i] + "!U" + currentRow; //Area spots currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "=AVERAGE(" + names[i] + "!H" + currentRow + "," + names[i] + "!L" + currentRow + "," + names[i] + "!P" + currentRow + ")"; //Mean spots currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "=AVERAGE(" + names[i] + "!I" + currentRow + "," + names[i] + "!M" + currentRow + "," + names[i] + "!Q" + currentRow + ")"; spotSignal = currentColumn; //Cell-Noise mean currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "=" + names[i] + "!X" + currentRow; CellMinusNoise = currentColumn; //Max Cell currentColumn += increment; MaxCell = currentColumn; //Min-Results currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "=" + ColumnLabel(MaxCell) + "2-(" + ColumnLabel(MaxCell) + "2/" + ColumnLabel(CellMinusNoise) + currentRow + ")*(" + ColumnLabel(spotSignal) + currentRow + "-" + names[i] + "!U" + currentRow + ")"; //Results currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "=(" + ColumnLabel(MaxCell) + "2/" + ColumnLabel(CellMinusNoise) + currentRow + ")*(" + ColumnLabel(spotSignal) + currentRow + "-" + names[i] + "!U" + currentRow + ")"; } } //calculations currentColumn++; int MaxMinResults = 2 + 8 * increment; int ResultsTo0 = currentColumn; for (int i = 0; i < names.Length; i++) { for (currentRow = 2; currentRow < MaxColumnLength; currentRow++) { if (suf == "Green") { worksheet.Cells[currentRow, currentColumn] = "=(" + ColumnLabel(MaxMinResults) + currentRow + "-AVERAGE(" + ColumnLabel(MaxMinResults) + MinG + ":" + ColumnLabel(MaxMinResults) + MaxG + ")" + ")"; } else { worksheet.Cells[currentRow, currentColumn] = "=(" + ColumnLabel(MaxMinResults) + currentRow + "-AVERAGE(" + ColumnLabel(MaxMinResults) + MinR + ":" + ColumnLabel(MaxMinResults) + MaxR + ")" + ")"; } } currentColumn++; MaxMinResults++; } //NormTo1 MaxMinResults = currentColumn - increment; for (int i = 0; i < names.Length; i++) { for (currentRow = 2; currentRow < MaxColumnLength; currentRow++) { worksheet.Cells[currentRow, currentColumn] = "=(" + ColumnLabel(MaxMinResults) + currentRow + "/MAX(" + ColumnLabel(MaxMinResults) + "2" + ":" + ColumnLabel(MaxMinResults) + MaxColumnLength + ")" + ")"; } currentColumn++; MaxMinResults++; } { int MinInd = currentColumn - increment; int MaxInd = currentColumn - 1; int Mean = currentColumn; int nMean = currentColumn + 1; int StDev = currentColumn + 2; for (currentRow = 2; currentRow < MaxColumnLength; currentRow++) { worksheet.Cells[currentRow, Mean] = "=" + "AVERAGE(" + ColumnLabel(MinInd) + currentRow + ":" + ColumnLabel(MaxInd) + currentRow + ")"; worksheet.Cells[currentRow, StDev] = "=" + "STDEV.S(" + ColumnLabel(MinInd) + currentRow + ":" + ColumnLabel(MaxInd) + currentRow + ")"; } for (currentRow = 2; currentRow < MaxColumnLength; currentRow++) { worksheet.Cells[currentRow, nMean] = "=(" + ColumnLabel(Mean) + currentRow + "/MAX(" + ColumnLabel(Mean) + "2:" + ColumnLabel(Mean) + MaxColumnLength + ")" + ")"; } MinInd -= increment; MaxInd -= increment; Mean = currentColumn + 3; StDev = currentColumn + 4; for (currentRow = 2; currentRow < MaxColumnLength; currentRow++) { worksheet.Cells[currentRow, Mean] = "=" + "AVERAGE(" + ColumnLabel(MinInd) + currentRow + ":" + ColumnLabel(MaxInd) + currentRow + ")"; worksheet.Cells[currentRow, StDev] = "=" + "STDEV.S(" + ColumnLabel(MinInd) + currentRow + ":" + ColumnLabel(MaxInd) + currentRow + ")"; } } string name = dir.Substring(dir.LastIndexOf("\\") + 1, dir.Length - dir.LastIndexOf("\\") - 1); name = dir + "\\" + "Res_" + suf + ".xlsx"; app.Visible = true; workbook.SaveAs(name, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Console.WriteLine("Results saved to :\n" + name); app.Quit();//Check is working Console.WriteLine("Done!"); }
public void generatExcelNoSubmit(string f) { string file_Name = f; m_oExcelApp = new Excel.Application(); m_oExcelApp.Visible = false; m_oExcelApp.UserControl = false; m_oSheet = null; excelRange = null; try { m_oBook = m_oExcelApp.Workbooks.Add(missing); Excel.Sheets sheets = m_oBook.Worksheets; //Add new 4 Sheet //sheets.Add(System.Type.Missing, m_oBook.Sheets[3], 1, Excel.XlSheetType.xlWorksheet); //Product Sheet [Sheet1] m_oSheet = (Excel._Worksheet)m_oBook.Sheets[1]; m_oSheet.Activate(); m_oSheet.Name = "รายการเสนอซื้อ"; SetData_to_SheetNoSubmit(); string template = Application.StartupPath; string strRunReport = file_Name; //string strPass = "******"; password m_oBook.SaveAs(strRunReport, Excel.XlFileFormat.xlWorkbookNormal, null, null, null, null, Excel.XlSaveAsAccessMode.xlShared, null, null, null, null, null); m_oExcelApp.Visible = true; } catch (interop.COMException ex) { MessageBox.Show("Error accessing Excel: " + ex.ToString()); } catch (Exception ex) { MessageBox.Show("Error: " + ex.ToString()); } finally { if (m_oExcelApp == null) { m_oExcelApp.Quit(); m_oExcelApp = null; GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } } }
private void button4_Click(object sender, EventArgs e) { if (dataGridView1.DataSource != null) { // Krijimi i Excel Application _excel._Application app = new _excel.Application(); // krijimi i new WorkBook Ne Excel application _excel._Workbook workbook = app.Workbooks.Add(Type.Missing); // krijimi i new Excelsheet in workbook _excel._Worksheet worksheet = null; // Shfaqja e excel sheet Sapo te ekzekutohet programi //app.Visible = true; // get the reference of first sheet. By default its name is Sheet1. // store its reference to worksheet worksheet = workbook.Sheets["Sheet1"]; worksheet = workbook.ActiveSheet; // Ndryshimi i emrit te active sheet worksheet.Name = "Exported from gridview"; // Ruajtja e header part in Excel for (int i = 1; i < dataGridView1.Columns.Count + 1; i++) { worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText; } // ruajtja e cdo rreshti dhe kolone (value) ne excel sheet for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) { for (int j = 0; j < dataGridView1.Columns.Count; j++) { worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString(); } } // save the application if (File.Exists(@"C:\Users\albana\Desktop\output.xlsx")) { DialogResult result = MessageBox.Show("File exist! Do you want to owerite it?", "Please Confirm", MessageBoxButtons.YesNoCancel); if (result == DialogResult.Yes) {// if (app.ActiveWorkbook = true) { } // xlApp.IgnoreRemoteRequests = true; bool isopen = IsOpened("output.xlsx"); if (isopen) { MessageBox.Show("Please close Excel file in order to replace it with the file generated!"); app.DisplayAlerts = false; } else { File.Delete(@"C:\Users\albana\Desktop\output.xlsx"); workbook.SaveAs(@"C:\Users\albana\Desktop\output.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, _excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing); app.DisplayAlerts = false; dataGridView1.DataSource = null; dataGridView1.Refresh(); } } else if (result == DialogResult.No) { excelcopy += 1; while ((File.Exists(@"C:\Users\albana\Desktop\output" + excelcopy + ".xlsx") == true)) { excelcopy += 1; } //nese dumam te dime emrin e filit qe ka te njejtin emer dhe qe duam ti bejme kopje //perjashtojme rastin kur eshte output1 dhe ne rastet tjera bejme 'show ["output"+"excelcopy-1+]' workbook.SaveAs(@"C:\Users\albana\Desktop\output" + excelcopy + ".xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, _excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing); MessageBox.Show("File is saved in the same directori with existing file name and the name of file created is output" + excelcopy + ".xlsx"); app.DisplayAlerts = false; dataGridView1.DataSource = null; dataGridView1.Refresh(); } else if (result == DialogResult.Cancel) { app.DisplayAlerts = false; dataGridView1.Refresh(); } } else { workbook.SaveAs(@"C:\Users\albana\Desktop\output.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, _excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing); app.DisplayAlerts = false; dataGridView1.DataSource = null; dataGridView1.Refresh(); } // Exit from the application app.Quit(); } else { MessageBox.Show("Datagridview is empty! Nothing to save"); } }
public static void export2Excel(string heading1, string heading2, string heading3, DataGridView dgv, ListView lvcolumns) { Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet worksheet = null; //excel.Windows.Application.ActiveWindow.DisplayGridlines = false; //excel.ScreenUpdating = false; try { worksheet = workbook.ActiveSheet; worksheet.Columns.ColumnWidth = 20; ////worksheet.Cells.HorizontalAlignment = ContentAlignment.MiddleCenter; worksheet.Columns.WrapText = true; worksheet.Cells[1, 1] = heading1; worksheet.Cells[2, 1] = heading2; worksheet.Cells.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ////worksheet.get_Range("A1", "B2").HorizontalAlignment = ContentAlignment.MiddleLeft; ////worksheet.get_Range("A1", "B2").Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; worksheet.get_Range("A1", "B2").WrapText = false; worksheet.get_Range("A1", "B2").Font.Bold = true; worksheet.get_Range("A1", "B2").Font.Size = 15; worksheet.get_Range("A1", "B2").Font.Color = Color.SeaGreen; string[] detail = { }; if (heading3.Length != 0) { detail = heading3.Split(Main.delimiter2); } int k = 3; for (int i = 0; i < detail.Length; i++) { try { DateTime temp; string str1 = detail[i].Substring(0, detail[i].IndexOf(Main.delimiter1)); string str2 = detail[i].Substring(detail[i].IndexOf(Main.delimiter1) + 1); worksheet.Cells[k, 1] = str1; if (DateTime.TryParse(str2, out temp)) { worksheet.Cells[k, 2] = (Convert.ToDateTime(str2)).Date; } else { worksheet.Cells[k, 2] = str2; } } catch (Exception ex) { MessageBox.Show("export2Excel() : ERROR in header String format"); } k++; } worksheet.Name = heading1; int col = 0; DateTime dt; //---------16/10/2017 int[] tArray = new Int32[dgv.ColumnCount]; int colCount = 0; int colNumber = 0; foreach (ListViewItem itemRow in lvcolumns.Items) { if (itemRow.Checked) { int cNumber = dgv.Columns.IndexOf(dgv.Columns[itemRow.SubItems[3].Text]); tArray[colNumber] = cNumber; colNumber++; } colCount++; } for (int i = colNumber; i < tArray.Length; i++) { tArray[i] = -1; } //int row = 3; int row = k; //print heading //Boolean flip = true; for (int i = 0; i < colNumber; i++) { int cNumber = tArray[i]; worksheet.Cells[row, i + 1] = dgv.Columns[cNumber].HeaderText; worksheet.Cells[row, i + 1].Interior.Color = Color.Orange; } row++; //Using Range int a = 0; object[,] data = new object[dgv.Rows.Count, dgv.Columns.Count]; foreach (DataGridViewRow rw in dgv.Rows) { int b = 0; foreach (DataGridViewCell c in rw.Cells) { if (tArray.Contains(c.ColumnIndex)) { data[a, b] = c.Value; b++; } } a++; } worksheet.Range[worksheet.Cells[row, 1], worksheet.Cells[dgv.Rows.Count + k, dgv.Columns.Count]].value = data; int lastrow = worksheet.UsedRange.Rows.Count; int lastcolumn = worksheet.UsedRange.Columns.Count; Microsoft.Office.Interop.Excel.Range rng = worksheet.Range[worksheet.Cells[row, 1], worksheet.Cells[lastrow, lastcolumn]]; Microsoft.Office.Interop.Excel.FormatCondition format = rng.Rows.FormatConditions. Add(Microsoft.Office.Interop.Excel.XlFormatConditionType.xlExpression, Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlEqual, "=MOD(ROW(),2)=0"); format.Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbLightGreen; Microsoft.Office.Interop.Excel.Range rangeData = excel.Range[worksheet.Cells[row, 1], worksheet.Cells[lastrow, lastcolumn]]; rangeData.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; Microsoft.Office.Interop.Excel.Range rangeHead = excel.Range[worksheet.Cells[row - 1, 1], worksheet.Cells[row - 1, lastcolumn]]; rangeHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //----------- //excel.ScreenUpdating = false; SaveFileDialog saveDialog = new SaveFileDialog(); ////saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"; saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx"; saveDialog.FilterIndex = 2; saveDialog.OverwritePrompt = false; if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK) { workbook.SaveAs(saveDialog.FileName); MessageBox.Show("Export Successful"); } workbook.Close(false, Type.Missing, Type.Missing); } catch (Exception ex) { MessageBox.Show("Export Failed"); } }
private void ExportToExcelToolStripMenuItem_Click(object sender, EventArgs e) { //Set region = US System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); Excel._Workbook wb = app.Workbooks.Add(Type.Missing); Excel._Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets[1];; try { SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.Title = "Export to Excel"; saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx;*.xls"; //saveDialog.FilterIndex = 2; saveDialog.FileName = "Book" + number.ToString(); if (saveDialog.ShowDialog() == DialogResult.OK) { ws.Name = "Ket Qua Noi Suy"; Excel.Range r = ws.Range["A1:L1"]; r.Merge(); r.Value = "Bảng tính nội suy"; r.BorderAround2(); r.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //r.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; // r.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; // r.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; // r.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; int i = 0, j = 0; while (i < dgvKQ.ColumnCount) { //tieu de cot ws.Cells[2, i + 1] = dgvKQ.Columns[i].HeaderText; for (j = 0; j < dgvKQ.RowCount; j++) { ws.Cells[j + 3, i + 1] = dgvKQ[i, j].Value; ws.Range[ws.Cells[j + 2, i + 1], ws.Cells[j + 2, i + 1]].BorderAround2(); } i++; } wb.SaveAs(saveDialog.FileName); app.Quit(); MessageBox.Show("Export Successful"); } //Open Folder Process folderProcess = new Process(); folderProcess.StartInfo.UseShellExecute = true; folderProcess.StartInfo.FileName = Path.GetDirectoryName(saveDialog.FileName); // folderProcess.StartInfo.Arguments = "/select"; folderProcess.Start(); // Process.Start("explorer", "/select," + saveDialog.FileName); saveDialog = null; folderProcess = null; } catch (Exception ex) { MessageBox.Show(ex.Message + "\n" + "Không thể mở được Folder do không thấy đường dẫn"); } app = null; wb = null; ws = null; }
public static void export2Excel20102017(string heading1, string heading2, string heading3, DataGridView dgv, ListView lvcolumns) { Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet worksheet = null; excel.Windows.Application.ActiveWindow.DisplayGridlines = false; try { worksheet = workbook.ActiveSheet; worksheet.Columns.ColumnWidth = 20; ////worksheet.Cells.HorizontalAlignment = ContentAlignment.MiddleCenter; worksheet.Columns.WrapText = true; worksheet.Cells[1, 1] = heading1; worksheet.Cells[2, 1] = heading2; worksheet.Cells.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ////worksheet.get_Range("A1", "B2").HorizontalAlignment = ContentAlignment.MiddleLeft; ////worksheet.get_Range("A1", "B2").Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; worksheet.get_Range("A1", "B2").WrapText = false; worksheet.get_Range("A1", "B2").Font.Bold = true; worksheet.get_Range("A1", "B2").Font.Size = 15; worksheet.get_Range("A1", "B2").Font.Color = Color.SeaGreen; string[] detail = { }; if (heading3.Length != 0) { detail = heading3.Split(Main.delimiter2); } int k = 3; for (int i = 0; i < detail.Length; i++) { try { DateTime temp; string str1 = detail[i].Substring(0, detail[i].IndexOf(Main.delimiter1)); string str2 = detail[i].Substring(detail[i].IndexOf(Main.delimiter1) + 1); worksheet.Cells[k, 1] = str1; if (DateTime.TryParse(str2, out temp)) { worksheet.Cells[k, 2] = (Convert.ToDateTime(str2)).Date; } else { worksheet.Cells[k, 2] = str2; } } catch (Exception ex) { MessageBox.Show("export2Excel() : ERROR in header String format"); } k++; } worksheet.Name = heading1; int col = 0; DateTime dt; //---------16/10/2017 int[] tArray = new Int32[dgv.ColumnCount]; int colCount = 0; int colNumber = 0; foreach (ListViewItem itemRow in lvcolumns.Items) { if (itemRow.Checked) { int cNumber = dgv.Columns.IndexOf(dgv.Columns[itemRow.SubItems[3].Text]); tArray[colNumber] = cNumber; colNumber++; } colCount++; } for (int i = colNumber; i < tArray.Length; i++) { tArray[i] = -1; } int row = 3; //print heading Boolean flip = true; for (int i = 0; i < colNumber; i++) { int cNumber = tArray[i]; worksheet.Cells[row, i + 1] = dgv.Columns[cNumber].HeaderText; worksheet.Cells[row, i + 1].Interior.Color = Color.Orange; } row++; //print details DateTime stime = DateTime.Now; for (int j = 0; j < dgv.Rows.Count; j++) { for (int i = 0; i < colNumber; i++) { int cNumber = tArray[i]; if (dgv.Rows[j].Cells[cNumber].Value == null || dgv.Rows[j].Cells[cNumber].Value.ToString() == "") { worksheet.Cells[row, i + 1] = ""; } else { string str1 = dgv.Rows[j].Cells[cNumber].Value.ToString(); worksheet.Cells[row, i + 1] = str1; } if (flip) { worksheet.Cells[row, i + 1].Interior.Color = Color.LightGreen; } else { worksheet.Cells[row, i + 1].Interior.Color = Color.LightSkyBlue; } } if (flip) { flip = false; } else { flip = true; } row++; } DateTime etime = DateTime.Now; //----------- worksheet.get_Range("A1", "A30").Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; SaveFileDialog saveDialog = new SaveFileDialog(); ////saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"; saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx"; saveDialog.FilterIndex = 2; saveDialog.OverwritePrompt = false; if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK) { workbook.SaveAs(saveDialog.FileName); MessageBox.Show("Export Successful"); } workbook.Close(false, Type.Missing, Type.Missing); } catch (Exception ex) { MessageBox.Show("Export Failed"); } }
private void ExportData() { //Start Excel and get Application object. oXL = new Microsoft.Office.Interop.Excel.Application(); //Get a new workbook. //oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add("")); oWB = oXL.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); oXL.Visible = true; //oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet; //oSheet.Activate(); oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets.Add(); oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet; oSheet.Activate(); oSheet.Name = DateTime.Now.ToString("yyyyMMdd-HHmm"); //Add table headers going cell by cell. oSheet.Cells[1, 1] = "First Name"; oSheet.Cells[1, 2] = "Last Name"; oSheet.Cells[1, 3] = "Full Name"; oSheet.Cells[1, 4] = "Salary"; //Format A1:D1 as bold, vertical alignment = center. oSheet.get_Range("A1", "D1").Font.Bold = true; oSheet.get_Range("A1", "D1").VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; // Create an array to multiple values at once. string[,] saNames = new string[5, 2]; saNames[0, 0] = "John"; saNames[0, 1] = "Smith"; saNames[1, 0] = "Tom"; saNames[4, 1] = "Johnson"; //Fill A2:B6 with an array of values (First and Last Names). oSheet.get_Range("A2", "B6").Value2 = saNames; //Fill C2:C6 with a relative formula (=A2 & " " & B2). oRng = oSheet.get_Range("C2", "C6"); oRng.Formula = "=A2 & \" \" & B2"; //Fill D2:D6 with a formula(=RAND()*100000) and apply format. oRng = oSheet.get_Range("D2", "D6"); //AutoFit columns A:D. oRng.Formula = "=RAND()*100000"; oRng.NumberFormat = "$0.00"; oRng = oSheet.get_Range("A1", "D1"); oRng.EntireColumn.AutoFit(); oXL.Visible = false; oXL.UserControl = false; //Save file //oWB.Save(); //Save as oWB.SaveAs("C:\\file.xls", XlFileFormat.xlWorkbookNormal, null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, false, false, false); oWB.Close(); }
public static void export2ExcelOld(string heading1, string heading2, string heading3, DataGridView dgv, ListView lvcolumns) { Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet worksheet = null; excel.Windows.Application.ActiveWindow.DisplayGridlines = false; try { worksheet = workbook.ActiveSheet; worksheet.Columns.ColumnWidth = 20; ////worksheet.Cells.HorizontalAlignment = ContentAlignment.MiddleCenter; worksheet.Columns.WrapText = true; worksheet.Cells[1, 1] = heading1; worksheet.Cells[2, 1] = heading2; worksheet.Cells.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ////worksheet.get_Range("A1", "B2").HorizontalAlignment = ContentAlignment.MiddleLeft; ////worksheet.get_Range("A1", "B2").Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; worksheet.get_Range("A1", "B2").WrapText = false; worksheet.get_Range("A1", "B2").Font.Bold = true; worksheet.get_Range("A1", "B2").Font.Size = 15; worksheet.get_Range("A1", "B2").Font.Color = Color.SeaGreen; string[] detail = { }; if (heading3.Length != 0) { detail = heading3.Split(Main.delimiter2); } int k = 3; for (int i = 0; i < detail.Length; i++) { try { DateTime temp; string str1 = detail[i].Substring(0, detail[i].IndexOf(Main.delimiter1)); string str2 = detail[i].Substring(detail[i].IndexOf(Main.delimiter1) + 1); worksheet.Cells[k, 1] = str1; if (DateTime.TryParse(str2, out temp)) { worksheet.Cells[k, 2] = (Convert.ToDateTime(str2)).Date; } else { worksheet.Cells[k, 2] = str2; } } catch (Exception ex) { MessageBox.Show("ERROR in HeaderDetail String format"); } k++; } worksheet.Name = heading1; int col = 0; DateTime dt; foreach (ListViewItem itemRow in lvcolumns.Items) { if (itemRow.Checked) { int row = k + 1; col = col + 1; string str = itemRow.SubItems[1].Text; string[] format = { "dd-MM-yyyy", "dd/MM/yyyy", "dd/MM/yyyy hh:mm:ss", "dd-MM-yyyy hh:mm:ss", "dd/MM/yyyy hh:mm:ss tt", "dd-MM-yyyy hh:mm:ss tt", "dd-MM-yy", "dd/MM/yy", "dd/MM/yy hh:mm:ss", "dd-MM-yy hh:mm:ss", "dd/MM/yy hh:mm:ss tt", "dd-MM-yy hh:mm:ss tt" }; for (int i = 0; i < dgv.Columns.Count; i++) { if (dgv.Columns[i].HeaderText.Equals(itemRow.SubItems[1].Text)) // if column is selected for export { ////worksheet.Columns[col].ColumnWidth = itemRow.SubItems[2].Text; worksheet.Cells[row, col] = dgv.Columns[i].HeaderText; worksheet.Cells[row, col].Interior.Color = Color.Orange; Boolean flip = true; for (int j = 0; j <= dgv.Rows.Count - 1; j++) { row = row + 1; if (dgv.Rows[j].Cells[i].Value == null || dgv.Rows[j].Cells[i].Value.ToString() == "") { worksheet.Cells[row, col] = ""; } else { string str1 = dgv.Rows[j].Cells[i].Value.ToString(); worksheet.Cells[row, col] = str1; //string format = ""; ////if (DateTime.TryParseExact(dgv.Rows[j].Cells[i].Value.ToString(), format, CultureInfo.InvariantCulture, DateTimeStyles.None, out dt)) //// worksheet.Cells[row + 1, col] = Convert.ToDateTime(dgv.Rows[j].Cells[i].Value.ToString()); ////else //// worksheet.Cells[row + 1, col] = dgv.Rows[j].Cells[i].Value.ToString(); } if (flip) { worksheet.Cells[row, col].Interior.Color = Color.LightGreen; flip = false; } else { worksheet.Cells[row, col].Interior.Color = Color.LightSkyBlue; flip = true; } worksheet.Cells[row, col].Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); } } } } } worksheet.get_Range("A1", "A30").Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; SaveFileDialog saveDialog = new SaveFileDialog(); ////saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"; saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx"; saveDialog.FilterIndex = 2; saveDialog.OverwritePrompt = false; if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK) { workbook.SaveAs(saveDialog.FileName); MessageBox.Show("Export Successful"); } workbook.Close(false, Type.Missing, Type.Missing); } catch (Exception ex) { MessageBox.Show("Export Failed"); } }
//Execute Method takes the file selected and formats it //to the rockland template specification. The new excel //workbook is then saved in the same directory as the file //that was selected. public void Execute(String path) { filename = path; //templatePath will be where template.xls will be stored //source path get from browse button //appPath is directory the .exe is located, which the template should be in same directory string appPath = Application.StartupPath; string templatePath, sourcePath; //root of exe plus template name appPath += "\\template.xls"; templatePath = @appPath; sourcePath = @filename; //If the template file is found, run program if (File.Exists(templatePath) && File.Exists(sourcePath)) { //Template setup tmpApp = new Excel.Application(); tmpApp.Visible = false; tmpBook = (Excel._Workbook)(tmpApp.Workbooks.Add(templatePath)); tmpSheet = (Excel._Worksheet)tmpBook.Sheets[1]; //source setup srcApp = new Excel.Application(); srcApp.Visible = false; srcBook = (Excel._Workbook)(srcApp.Workbooks.Add(sourcePath)); srcSheet = (Excel._Worksheet)srcBook.Sheets[1]; //Merge Both files here for (int i = 1; i < 33; i++) { string str = srcSheet.Cells[2, i].Value2; tmpSheet.Cells[3, i] = str; } //Results page tmpSheet = (Excel._Worksheet)tmpBook.Sheets[2]; srcSheet = (Excel._Worksheet)srcBook.Sheets[2]; int mod = 1; for (int i = 2; i <= srcSheet.UsedRange.Rows.Count; i++) { //skip FLDTMP Row if (srcSheet.Cells[i, 1].Value2 != "FLDTEMP") { for (int j = 1; j < 16; j++) { string str = srcSheet.Cells[i, j].Value2; tmpSheet.Cells[i + mod, j] = str; } //re size row height tmpSheet.Cells[i + 1, 1].RowHeight = 12.75; } else { mod = 0; } } object m_objOpt = System.Reflection.Missing.Value; //remove extension of filename string[] remove = { ".xls", ".xlsx" }; foreach (string item in remove) { if (filename.EndsWith(item)) { filename = filename.Substring(0, filename.LastIndexOf(item)); break; //only allow one match at most } } //save workbook tmpBook.SaveAs(@filename + "_Formatted.xls", m_objOpt, m_objOpt, m_objOpt, true, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); tmpBook.Close(true); tmpApp.Workbooks.Close(); tmpApp.Quit(); srcBook.Close(true); srcApp.Workbooks.Close(); srcApp.Quit(); //Open file location after saved if (File.Exists(@filename + "_Formatted.xls")) { Process.Start("explorer.exe", "/select, " + @filename + "_Formatted.xls"); } } else { this.label1.Text = "Error: File not found. Please Try again."; } }
public void XLS(DirectoryInfo RutaExcel, DataTable Datos) { //BackgroundWorker bw = sender as BackgroundWorker; Excel.Application excel = new Excel.Application(); Excel._Workbook libro = null; Excel._Worksheet hoja = null; Excel.Range rango = null; try { //creamos un libro nuevo y la hoja con la que vamos a trabajar libro = (Excel._Workbook)excel.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); hoja = (Excel._Worksheet)libro.Worksheets.Add(); hoja.Name = "EJEMPLO"; ((Excel.Worksheet)excel.ActiveWorkbook.Sheets["Hoja1"]).Delete(); //Borramos la hoja que crea en el libro por defecto //Montamos las cabeceras montaCabeceras(1, ref hoja, Datos); //Rellenamos las celdas int inColumn = 0, inRow = 0; for (int m = 0; m < Datos.Rows.Count; m++) { for (int n = 0; n < Datos.Columns.Count; n++) { inColumn = n + 1; inRow = 2 + m;//1 + 2 + m; hoja.Cells[inRow, inColumn] = Datos.Rows[m].ItemArray[n].ToString(); if (m % 2 == 0) { hoja.get_Range("A" + inRow.ToString(), "W" + inRow.ToString()).Interior.Color = System.Drawing.ColorTranslator.FromHtml("#DAA520"); } } } libro.Saved = true; libro.SaveAs(Environment.CurrentDirectory + @"\Ejemplo.xlsx"); // Si es un libro nuevo libro.Close(); releaseObject(libro); excel.UserControl = false; excel.Quit(); releaseObject(excel); } catch (Exception ex) { Console.WriteLine(ex.Message, "Error en creación/actualización del Ejemplo"); libro.Saved = true; libro.SaveAs(Environment.CurrentDirectory + @"\Ejemplo.xlsx"); // libro.Save(); libro.Close(); releaseObject(libro); excel.UserControl = false; excel.Quit(); releaseObject(excel); System.Threading.Thread.Sleep(2000); } }