/// <summary> /// Metod so koj se vrsi polnenje na cel red vo tabelata so pole od objekti /// i soodvetni vrednosti vo rangot od pocetna do krajna kolona /// </summary> /// <param name="values"></param> /// <param name="startColumn"></param> /// <param name="endColumn"></param> public void FillRowData_Mine(object[] values, string startColumn, string endColumn) { _excelRange = _excelSheet.get_Range(startColumn, endColumn); _excelRange.set_Value(_value, values); this.AddRow(startColumn, endColumn); //_excelRange.EntireColumn.AutoFit(); }
private void AutoFitColumns(string startRange, int rowCount, int colCount) { _range = _sheet.get_Range(startRange, _optionalValue); _range = _range.get_Resize(rowCount, colCount); _range.ColumnWidth = 12; _range.Columns.AutoFit(); }
private void ExportExcelFile(List <string[]> data, ref Excel._Worksheet objSheet) { int num = 0; object missingType = Type.Missing; int colCnt = detailEquipment.GetColumnCount; int rowCnt = data.Count; string[] headers = detailEquipment.GetHeaderText(); string[] columns = new string[colCnt]; for (int c = 0; c < colCnt; c++) { num = c + 65; columns[c] = Convert.ToString((char)num); } try { Excel.Range range; range = objSheet.get_Range(columns[0] + "1:" + columns[colCnt - 1] + "1", Missing.Value); range.Value2 = headers; for (int i = 0; i < rowCnt; i++) { string strRange = columns[0] + (i + 2) + ":" + columns[colCnt - 1] + (i + 2); range = objSheet.get_Range(strRange, Missing.Value); range.Value2 = data[i]; } } catch (Exception ex) { Console.WriteLine(ex.Message); } }
private void CargarEmpleados(Excel._Worksheet oSheet) { CLS_EmpleadosHis sel = new CLS_EmpleadosHis(); sel.MtdSeleccionarEmpleadosHisCuadrilla(); if (sel.Exito && sel.Datos.Rows.Count > 0) { int Fila = 6; for (int i = 0; i < sel.Datos.Rows.Count; i++) { oRng = oSheet.get_Range("A" + Fila.ToString(), "A" + Fila.ToString()); oRng.Merge(); oRng.Value2 = sel.Datos.Rows[i]["Nombre_Empleado"].ToString(); oRng.Font.FontStyle = "Calibri"; oRng.Font.Size = 11; oRng.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; oRng.Font.Bold = false; oRng = oSheet.get_Range("B" + Fila.ToString(), "B" + Fila.ToString()); oRng.Merge(); oRng.Value2 = sel.Datos.Rows[i]["Dias_Trabajados"].ToString(); oRng.Font.FontStyle = "Calibri"; oRng.Font.Size = 11; oRng.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; oRng.Font.Bold = false; oRng.NumberFormat = "#,##0"; Fila++; } } }
public static void UWriteListViewToExcel(ListView LView, string way, string strTitle) { try { Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); object m_objOpt = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Workbooks ExcelBooks = (Microsoft.Office.Interop.Excel.Workbooks)ExcelApp.Workbooks; Microsoft.Office.Interop.Excel._Workbook ExcelBook = (Microsoft.Office.Interop.Excel._Workbook)(ExcelBooks.Add(m_objOpt)); Microsoft.Office.Interop.Excel._Worksheet ExcelSheet = (Microsoft.Office.Interop.Excel._Worksheet)ExcelBook.ActiveSheet; ExcelSheet.Columns.EntireColumn.AutoFit();//列宽自适应 //设置标题 ExcelApp.Caption = strTitle; ExcelSheet.Cells[1, 1] = strTitle; Range rg3 = ExcelSheet.get_Range("A1", "E1"); rg3.MergeCells = true; rg3.set_Value(Type.Missing, strTitle); rg3.Font.Bold = true; rg3.Font.Size = 18; rg3.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //写入列名 for (int i = 1; i <= LView.Columns.Count; i++) { ExcelSheet.Cells[2, i] = LView.Columns[i - 1].Text; } //写入内容 for (int i = 3; i < LView.Items.Count + 3; i++) { ExcelSheet.Cells[i, 1] = LView.Items[i - 3].Text; for (int j = 2; j <= LView.Columns.Count; j++) { ExcelSheet.Cells[i, j] = LView.Items[i - 3].SubItems[j - 1].Text; } } for (int i = 1; i < LView.Items.Count + 3; i++) { string startcell = string.Format("A{0}", i.ToString()); string endcell = string.Format("E{0}", i.ToString()); Range r = ExcelSheet.get_Range(startcell, endcell); r.Font.Bold = true; r.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; } Range range = ExcelSheet.Columns; range.Columns.AutoFit(); ExcelBook.SaveAs(way, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //显示Excel //ExcelApp.Visible = true; ExcelApp.Quit(); GC.Collect(); MessageBox.Show("导出数据成功!", "系统信息"); } catch (SystemException e) { MessageBox.Show(e.ToString()); } }
private void izvozpodatkovbutton_Click(object sender, EventArgs e) { oXL = new Excel.Application(); oWB = (Excel.Workbook)oXL.Workbooks.Add(); oSheet = (Excel._Worksheet)oWB.ActiveSheet; oSheet.Cells[1, 1] = "Ime Lekarne"; oSheet.Cells[1, 2] = "Telefon"; oSheet.Cells[1, 3] = "Delovni Čas"; oSheet.Cells[1, 4] = "Naslov"; oSheet.Cells[1, 5] = "Kraj"; oSheet.Cells[1, 6] = "Število Delavcev"; oSheet.get_Range("A1", "F1").Font.Bold = true; oSheet.get_Range("A1", "F1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; polnjenjeexcel(); try { oWB.Application.ActiveWorkbook.SaveAs(@"C:\Users\Jon\Desktop\Excel\" + imeexceltext.Text + ".xlsx"); MessageBox.Show("Uspešno ustvarjena excel datoteka"); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
/// <summary> /// HTML转换为Excel /// </summary> /// <param name="IsMergeCells"></param> /// <param name="tableStr"></param> public void HtmlToExcel(bool IsMergeCells, string tableStr) { string a1 = "<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">\n<html xmlns=\"http://www.w3.org/1999/xhtml\">\n<head>\n<title></title>\n<meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\" />\n<style>\n</style>\n</head>\n<body>\n"; string a2 = "\n</body>\n</html>"; tableStr = a1 + tableStr + a2; if (IsMergeCells) { //合并单元格 //m_objRange = m_objSheet.get_Range("A1", "K4"); //m_objRange.Merge(System.Reflection.Missing.Value); m_objRange = m_objSheet.get_Range("A1", "A1"); //m_objRange. } m_objRange.Select(); m_objRange.Cells[1, 1] = tableStr; m_objRange.Activate(); m_objRange.Copy(Missing.Value); //m_objSheet.get_Range("B2", "B2") //.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationSubtract //, false, false); m_objRange.EntireColumn.AutoFit(); }
} //End Extract Spectrums public ITargetBlock <string> Run() { if (SynchronizationContext.Current == null) { SynchronizationContext.SetSynchronizationContext(new WindowsFormsSynchronizationContext()); } TransformBlock <string, DataTable> loadWafers = new TransformBlock <string, DataTable>( w => { return(GetWaferSpectrum(w, TEST_TYPE.INITIAL)); }); TransformBlock <DataTable, IEnumerable <Spectrum> > convert = new TransformBlock <DataTable, IEnumerable <Spectrum> >( dt => { return(ExtractSpectrum(dt)); }); ActionBlock <IEnumerable <Spectrum> > display = new ActionBlock <IEnumerable <Spectrum> >(list => { List <Spectrum> tmp = list.ToList <Spectrum>(); Globals.ThisAddIn.Application.Calculation = Excel.XlCalculation.xlCalculationManual; Excel.Application ExcelApp = ((Excel.Application)Globals.ThisAddIn.Application); var wb = ExcelApp.Workbooks.Add(); ExcelApp.Visible = true; Excel._Worksheet ws = wb.Sheets.Add(); ws.Name = "TestBlock"; for (int i = 0; i < tmp.Count; i += 2) { int count = tmp[i].Wl.Length; object[] wl_obj = new object[count]; object[] int_obj = new object[count]; for (int x = 0; x < count; x++) { wl_obj[x] = tmp[i].Wl[x]; int_obj[x] = tmp[i].Intensity[x]; } Excel.Range wl = ws.get_Range((Excel.Range)(ws.Cells[1, i]), (Excel.Range)(ws.Cells[count, i])); Excel.Range intensity = ws.get_Range((Excel.Range)(ws.Cells[1, i]), (Excel.Range)(ws.Cells[count, i])); wl.Value = tmp[i].Wl; intensity.Value = tmp[i].Intensity; } Globals.ThisAddIn.Application.Calculation = Excel.XlCalculation.xlCalculationSemiautomatic; }, // Specify a task scheduler from the current synchronization context // so that the action runs on the UI thread. new ExecutionDataflowBlockOptions { TaskScheduler = TaskScheduler.FromCurrentSynchronizationContext() }); loadWafers.LinkTo(convert); convert.LinkTo(display); //loadWafers.Post(wafer); return(loadWafers); }
private void FormatConsolidatedSheet(Excel._Workbook workbook, Excel._Worksheet consolidatedPivot) { Excel.Range oRng = consolidatedPivot.get_Range("A1", "F2"); oRng.Interior.Color = ColorTranslator.ToOle(Color.LightBlue); oRng.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone; oRng = consolidatedPivot.get_Range("A3", "F" + getLastUsedRow(consolidatedPivot)); oRng.Interior.Color = ColorTranslator.ToOle(Color.White); }
public void wrtie_data(double[] M, int stroka, int stolbec, int kolvo_strok, int kolvo_stolbcov) { string str = cifra_to_bukva(stolbec) + stroka.ToString(); m_objRange = m_objSheet.get_Range(str, m_objOpt); m_objRange = m_objRange.get_Resize(kolvo_strok, kolvo_stolbcov); m_objRange.set_Value(m_objOpt, M); }
void frm_FormClosed(object sender, System.Windows.Forms.FormClosedEventArgs e) { if (frm.DialogResult == System.Windows.Forms.DialogResult.OK) { Excel._Worksheet a = (Excel._Worksheet)Application.ActiveWorkbook.ActiveSheet; if (frm.Status == "C") { try { a.get_Range(frm.Pos, type).ClearComments(); } catch { } a.get_Range(frm.Pos, type).AddComment(frm.TTFormular); } else if (frm.Status == "L") { try { //DataTable dt = frm.DataReturn; System.Data.DataTable dt = frm.DataReturn; //Microsoft.Office.Interop.Excel.DataTable dtEx; //Excel.Workbook _wbook = (Excel.Workbook)Application.ActiveWorkbook; //_wbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); Excel.Worksheet _wsheet = (Excel.Worksheet)Application.ActiveWorkbook.ActiveSheet; Microsoft.Office.Interop.Excel.Range currentRange = _wsheet.get_Range(_address, Type.Missing); for (int i = 0; i < dt.Columns.Count; i++) { Excel.Range _range = (Excel.Range)_wsheet.Cells[currentRange.Row, i + currentRange.Column]; _range.Font.Bold = true; _range.set_Value(Type.Missing, dt.Columns[i].ColumnName); } for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { Excel.Range _range = (Excel.Range)_wsheet.Cells[i + currentRange.Row + 1, j + currentRange.Column]; _range.set_Value(Type.Missing, dt.Rows[i][j]); } } //string add = _wsheet.Name + "!R1C1:R" + (dt.Rows.Count + 1) + "C" + dt.Columns.Count; //_wbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); //Excel.Worksheet _wpivotsheet = (Excel.Worksheet)Application.ActiveWorkbook.ActiveSheet; //string des = _wpivotsheet.Name + "!R3C1"; //_wbook.PivotCaches().Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, add).CreatePivotTable(des, "PivotTable1", Type.Missing, Microsoft.Office.Interop.Excel.XlPivotTableVersionList.xlPivotTableVersion10); } catch (Exception ex) { BUS.CommonControl.AddLog("ErroLog", __documentDirectory + "\\Log", "[Addin] [" + DateTime.Now.ToString() + "] : " + ex.Message + "\n\t" + ex.Source + "\n\t" + ex.StackTrace); } //a.get_Range(frm.Pos, type).AddComment(frm.TTFormular); } else { a.get_Range(frm.Pos, type).set_Value(type, frm.TTFormular); } } }
/// <summary> /// fill the header columns for the range specified and make it bold if specified /// </summary> /// <param name="headers"></param> /// <param name="startColumn"></param> /// <param name="endColumn"></param> protected void FillHeaderColumn(object[] headers, string startColumn, string endColumn) { _excelRange = _excelSheet.get_Range(startColumn, endColumn); _excelRange.set_Value(_value, headers); if (BoldHeaders == true) { this.BoldRow(startColumn, endColumn); } _excelRange.EntireColumn.AutoFit(); }
public void AutoFit() { range = objSheet.get_Range("A1", "Z2000"); objSheet.Columns.AutoFit(); range.Select(); range = objSheet.get_Range("A1", "A1"); range.Select(); }
public void PutHeading(string Heading, string SheetName) { //AddExcelRows("A1", 1, 3, "sajdhsajkdn"); _sheet.Cells[1, 1] = Heading; _sheet.Name = SheetName; var font = _sheet.get_Range("a1").Font; font.Bold = true; font.Size = 16; font.ColorIndex = 1; font.FontStyle = "Times New Roman"; }
private void izvozButton_Click(object sender, EventArgs e) { oXL = new Excel.Application(); oWB = (Excel.Workbook)oXL.Workbooks.Add(); oSheet = (Excel._Worksheet)oWB.ActiveSheet; oSheet.Cells[1, 1] = "Naslov"; oSheet.Cells[1, 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray); oSheet.Cells[1, 2] = "Avtor"; oSheet.Cells[1, 2].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray); oSheet.Cells[1, 3] = "Leto"; oSheet.Cells[1, 3].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray); oSheet.Cells[1, 4] = "Oddelek"; oSheet.Cells[1, 4].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray); oSheet.Cells[1, 5] = "Založnik"; oSheet.Cells[1, 5].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray); oSheet.Cells[1, 6] = "Status"; oSheet.Cells[1, 6].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray); oSheet.get_Range("A1", "F1").Font.Bold = true; oSheet.get_Range("A1", "F1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; oSheet.Columns[1].ColumnWidth = 60; oSheet.Columns[2].ColumnWidth = 40; oSheet.Columns[3].ColumnWidth = 7; oSheet.Columns[4].ColumnWidth = 30; oSheet.Columns[5].ColumnWidth = 42; List <knjige> knigger = baza.izpisvsehknjig(); foreach (knjige x in knigger) { //worksheet.Cells[i + 2, 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green); oSheet.Cells[naprej, 1] = x.Naslov; oSheet.Cells[naprej, 2] = x.Avtor; oSheet.Cells[naprej, 3] = x.Leto; oSheet.Cells[naprej, 4] = x.Section; oSheet.Cells[naprej, 5] = x.Zalozba; if (x.Status == 0) { oSheet.Cells[naprej, 6].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); } else { oSheet.Cells[naprej, 6].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green); } naprej++; } oWB.Application.ActiveWorkbook.SaveAs(@"C:\Users\Ziga\Desktop\Excel\" + izvozIme.Text + ".xlsx"); }
/// <summary> /// Geçerli çalışma sayfasındaki bir hücrenin değerini ayarlayın. /// </summary> /// <param name="cellIndex"></param> /// <param name="value"></param> public void SetCellValue(string cellIndex, object value) { if (m_Worksheet != null) { object cell1 = cellIndex; Excel.Range range = m_Worksheet.get_Range(cell1, missing); if (range != null) { range.Value2 = value; } } }
private void AddRow(Excel._Worksheet sheet, Orders.itemsItem orderItem, OrderProducts.itemsItem orderProduct, int birim) { var searchRange = sheet.get_Range("L1", "L" + sheet.Rows.Count); Excel.Range findRng = searchRange.Find(orderProduct.order_product_id, Missing.Value, Excel.XlFindLookIn.xlFormulas, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, false, Missing.Value); if (findRng != null) { logTxt.AppendText("\r\n"); logTxt.AppendText("Zaten kayıtlı."); return; } Excel.Range line = (Excel.Range)sheet.Rows[2]; line.Insert(); Excel.Range last = (Excel.Range)sheet.Rows[2]; Excel.Range range = sheet.get_Range("A1", last); Excel.Range R1 = sheet.get_Range("A3", "M3"); R1.Copy(Type.Missing); Excel.Range xlRange = sheet.UsedRange; var lastRow = last.Row; Excel.Range R2 = sheet.get_Range("A" + lastRow.ToString(), "M" + lastRow.ToString()); R2.PasteSpecial(Excel.XlPasteType.xlPasteFormats, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationMultiply, false, false); sheet.Cells[last.Row, 1] = orderItem.date_added; sheet.Cells[last.Row, 2] = orderItem.order_id; sheet.Cells[last.Row, 3] = orderItem.firstname; sheet.Cells[last.Row, 4] = orderItem.lastname; sheet.Cells[last.Row, 5] = orderProduct.option_name; sheet.Cells[last.Row, 6] = orderProduct.name; sheet.Cells[last.Row, 7] = orderProduct.quantity; sheet.Cells[last.Row, 12] = orderProduct.order_product_id; sheet.Cells[last.Row, 13] = orderItem.order_status_id; sheet.Cells[last.Row, 14] = orderProduct.option_code; sheet.Cells[last.Row, 8] = birim; sheet.Cells[last.Row, 9] = birim * orderProduct.quantity; sheet.Cells[last.Row, 18] = orderProduct.product_id.ToString(); }
private void button1_Click(object sender, System.EventArgs e) { Excel.Application xlexcel = null; Excel._Workbook xlWorkbook = null; Excel._Worksheet xlWorkSheet = null; Excel.Range oRng; try { //start excel and get application object oXL = new Microsoft.Office.Interop.Excel.Application(); //open existing workbook oWB = oXL.Workbooks.Open("C:\\vehicledet.xlsx"); oSheet = (Excel._Worksheet)oWB.ActiveSheet; //Add table headers going cell by cell. xlWorkSheet.Cells[1, 1] = "Plate Number"; xlWorkSheet.Cells[1, 2] = "Car Model"; xlWorkSheet.Cells[1, 3] = "Car Brand"; xlWorkSheet.Cells[1, 4] = "Mileage"; //Format A1:D1 as bold, vertical alignment = center. xlWorkSheet.get_Range("A1", "D1").Font.Bold = true; xlWorkSheet.get_Range("A1", "D1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; // insert text at every last row int _lastRow = xlWorkSheet.Range["A" + xlWorkSheet.Rows.Count].End[Excel.XlDirection.xlUp].Row + 1; xlWorkSheet.Cells[_lastRow, 1] = textBox1.Text; xlWorkSheet.Cells[_lastRow, 2] = textBox2.Text; xlWorkSheet.Cells[_lastRow, 3] = textBox3.Text; xlWorkSheet.Cells[_lastRow, 4] = textBox4.Text; //AutoFit columns A:D. oRng = xlWorkSheet.get_Range("A1", "D1"); oRng.EntireColumn.AutoFit(); //Make sure Excel is not visible and give the user control of Microsoft Excel's lifetime. xlexcel.Visible = false; xlexcel.UserControl = true; //can save update to same file but wont close unless user press x button xlWorkbook.Save(); MessageBox.Show("Data saved successfully"); xlWorkbook.Close(); //allow excel to auto close by itself oXL.Quit(); Marshal.ReleaseComObject(oXL); } 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 void basliklar(int count) { for (int j = 1; j <= count; j++) { ws.get_Range("A1", "N1").Font.Bold = true; ws.Cells[1, j] = "x" + j; if (j == count) { ws.Cells[1, (j + 1)] = "Toplam fonk"; ws.Cells[1, (j + 2)] = "Akt. fonk"; } } }
private void DisplayChart(Excel._Worksheet oSheet, int iStationIndex) { Excel._Workbook oWB; Excel.Range oResizeRange; Excel._Chart oChart; string[,] sArray = new string[4, 2]; sArray[0, 0] = "A"; sArray[0, 1] = "C"; sArray[1, 0] = "E"; sArray[1, 1] = "G"; sArray[2, 0] = "I"; sArray[2, 1] = "K"; sArray[3, 0] = "M"; sArray[3, 1] = "O"; int[] iColor = { 36, 35, 34, 8 }; //Excel.Series oSeries; int iUsedRow = oSheet.get_Range(sArray[iStationIndex, 0] + "1048576").get_End(Excel.XlDirection.xlUp).Row; string sRange = string.Format("{0}1:{1}", sArray[iStationIndex, 0], sArray[iStationIndex, 0] + iUsedRow.ToString()); oResizeRange = oSheet.get_Range(sRange, Missing.Value).get_Resize(Missing.Value, 3); oResizeRange.Borders.Weight = Excel.XlBorderWeight.xlThin; oResizeRange.Interior.ColorIndex = iColor[iStationIndex]; string sRange1 = string.Format("{0}2:{1}", sArray[iStationIndex, 1], sArray[iStationIndex, 1] + iUsedRow.ToString()); oResizeRange = oSheet.get_Range(sRange1, Missing.Value); oResizeRange.NumberFormat = "0.00%"; sRange = string.Format("{0}2:{1},{2}2:{3}", sArray[iStationIndex, 0], sArray[iStationIndex, 0] + iUsedRow.ToString(), sArray[iStationIndex, 1], sArray[iStationIndex, 1] + iUsedRow.ToString()); oWB = (Excel._Workbook)oSheet.Parent; oChart = (Excel._Chart)oWB.Charts.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value); oResizeRange = oSheet.get_Range(sRange, Missing.Value); //Use the ChartWizard to create a new chart from the selected data. oChart.ChartWizard(oResizeRange, Excel.XlChartType.xlPieExploded, Missing.Value, Excel.XlRowCol.xlColumns, sArray[iStationIndex, 0], sArray[iStationIndex, 1], true, oSheet.Cells[1, sArray[iStationIndex, 0]], Missing.Value, Missing.Value, Missing.Value); oChart.ApplyDataLabels(); oChart.Location(Excel.XlChartLocation.xlLocationAsObject, oSheet.Name); //Move the chart so as not to cover your data. string sChartName = string.Format("Chart {0}", iChartNum.ToString()); oResizeRange = (Excel.Range)oSheet.Rows.get_Item(5, Missing.Value); oSheet.Shapes.Item(sChartName).Top = (float)(double)oResizeRange.Top; oResizeRange = (Excel.Range)oSheet.Columns.get_Item(iStationIndex * 4 + 1, Missing.Value); oSheet.Shapes.Item(sChartName).Left = (float)(double)oResizeRange.Left; oSheet.Shapes.Item(sChartName).Width = 250; iChartNum++; }
void BuildFramerateAnalysis(Excel._Workbook oWB, Word.Document oDoc, GetBucketValue grv, string uniqueName, string bookmarkName, float k_ignoreTime, int maxFPS) { const float k_fpsSize = 400.0f; const float aspect = 3.0f / 4.0f; Excel._Worksheet oSheet = AddWorksheet(oWB, uniqueName); FillBuckets(oSheet, grv, 0, maxFPS, k_ignoreTime); Excel._Chart oChart = MakeChart(oWB, oSheet.get_Range("C1", "D61"), Excel.XlChartType.xlXYScatterSmoothNoMarkers, Excel.XlRowCol.xlColumns, 1, 0, false); SetupAxes(oChart, "Frame Rate", 0.0f, maxFPS, "Percentage of Time Below", 0.0f, -1.0f); Word.InlineShape oShape = InsertChart(oChart, oDoc, uniqueName + "acc", bookmarkName, k_fpsSize, k_fpsSize * aspect, 0.5f); oChart = MakeChart(oWB, oSheet.get_Range("A1", "B61"), Excel.XlChartType.xlXYScatterSmoothNoMarkers, Excel.XlRowCol.xlColumns, 1, 0, false); SetupAxes(oChart, "Frame Rate", 0.0f, maxFPS, "Percentage of Time", 0.0f, -1.0f); oShape = InsertChart(oChart, oDoc, uniqueName, bookmarkName, k_fpsSize, k_fpsSize * aspect, 0.5f); }
void insertTable(Excel._Worksheet oSheet, int startCol = 1, int startRow = 1) { Excel.Range oRng; string[,] data = new string[resultTable.Rows.Count, resultTable.Columns.Count]; CultureInfo curCI = new CultureInfo(CultureInfo.CurrentCulture.Name); curCI.NumberFormat.NumberDecimalSeparator = Properties.Settings.Default.NumberDecimalSeparator; System.Threading.Thread.CurrentThread.CurrentCulture = curCI; Excel.Range rngToCopy = oSheet.get_Range($"{Convert.ToString(startRow + 1 )}:{Convert.ToString(startRow + resultTable.Rows.Count - 1 )}"); // rngToCopy.Copy(); rngToCopy.Insert(); for (int r = 0; r < resultTable.Rows.Count; r++) { for (int c = 0; c < resultTable.Columns.Count; c++) { if (resultTable.Rows[r][c].GetType().Name == "DateTime") { data[r, c] = ((DateTime)resultTable.Rows[r][c]).ToString(Properties.Settings.Default.ShortDatePattern); } else { data[r, c] = resultTable.Rows[r][c].ToString(); } } } oRng = oSheet.get_Range(xlsAdress(startCol, startRow), xlsAdress(startCol - 1 + data.GetLength(1), startRow - 1 + data.GetLength(0))); oRng.Cells.Value2 = data; for (int c = 0; c < data.GetLength(1); c++) { dynamic cell = oSheet.get_Range(xlsAdress(startCol + c, startRow), xlsAdress(startCol + c, startRow)); if (cell.DisplayFormat.NumberFormatLocal != "General") { oRng = oSheet.get_Range(xlsAdress(startCol + c, startRow), xlsAdress(startCol + c, startRow - 1 + data.GetLength(0))); oRng.Cells.TextToColumns(); } } // oRng.EntireColumn.AutoFit(); System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo(CultureInfo.CurrentCulture.Name); }
void CreateDBFile() { try { //Start Excel and get Application object. excelapp = new Excel.Application(); excelapp.Visible = true; //Get a new workbook. excelworkbook = excelapp.Workbooks.Add(); excelsheet = excelworkbook.Worksheets.get_Item(1) as Excel.Worksheet; //Add table headers going cell by cell. excelsheet.Cells[1, 1] = "물고기 번호"; excelsheet.Cells[1, 2] = "물고기 과/류"; excelsheet.Cells[1, 3] = "물고기 이름"; excelsheet.Cells[1, 4] = "물고기 크기"; excelsheet.Cells[1, 5] = "물고기 먹성"; excelsheet.Cells[1, 6] = "물고기 성격"; excelsheet.Cells[1, 7] = "물고기 서식층"; excelsheet.Cells[1, 8] = "적정수질"; excelsheet.Cells[1, 9] = "적정온도"; excelsheet.Cells[1, 10] = "사육 난이도"; excelsheet.Cells[1, 11] = "번식 난이도"; excelsheet.Cells[1, 12] = "합사 난이도"; excelsheet.Cells[1, 13] = "물고기 부과설명"; //Format A1:M1 as bold, vertical alignment = center. excelsheet.get_Range("A1", "M1").Font.Bold = true; excelsheet.get_Range("A1", "M1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; } 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"); } finally { ReleaseObject(excelsheet); ReleaseObject(excelworkbook); ReleaseObject(excelapp); } }
public void Function_Click(IRibbonControl control_) { Xl.Application xlApp = (Xl.Application)ExcelDna.Integration.ExcelDnaUtil.Application; String fname = control_.Id; fname = "=" + fname + "()"; Xl.Range rg = xlApp.ActiveCell; String cellName = ExcelUtil.ExcelColumnIndexToName(rg.Column) + rg.Row; Xl._Worksheet sheet = (Xl.Worksheet)xlApp.ActiveSheet; Xl.Range range = sheet.get_Range(cellName, System.Type.Missing); string previousFormula = range.FormulaR1C1.ToString(); range.Value2 = fname; range.Select(); syncContext_ = SynchronizationContext.Current; if (syncContext_ == null) { syncContext_ = new System.Windows.Forms.WindowsFormsSynchronizationContext(); } FunctionWizardThread othread = new FunctionWizardThread(range, syncContext_); Thread thread = new Thread(new ThreadStart(othread.functionargumentsPopup)); thread.Start(); }
public int RowMinMax() { // New method written so the end user doesn't go over the amount of cells already used. // However, this may be scratched as it could be that the end user wishes to fill new boxes. int rowCounter = 0; Excel.Application oXL = null; Excel._Workbook oWB = null; Excel._Worksheet oSheet = null; try { // Again, unlike 0.0.12, this should always be in a try/catch. oXL = new Excel.Application(); oWB = oXL.Workbooks.Open(excelDocument); oSheet = String.IsNullOrEmpty(dropDown.Text) ? (Excel._Worksheet)oWB.ActiveSheet : (Excel._Worksheet)oWB.Worksheets[dropDown.Text]; Excel.Range last = oSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing); // Finds the last used cell. Excel.Range range = oSheet.get_Range("A1", last); rowCounter = last.Row; } catch (Exception ex) { Debug.Write(ex); MessageBox.Show("Please open a valid .xlsx document!"); } finally { if (oWB != null) { oWB.Close(); // Close down document, to avoid deadlock or R/W issues. } } return(rowCounter); // Return this max value. }
//Exportar para Excel private void exportar(DataGridView tabela) { btnExportar.Text = "Exportando..."; btnExportar.Enabled = false; var excelApp = new Excel.Application(); //excelApp.Workbooks.Add(); Excel._Worksheet workSheet = excelApp.ActiveSheet; int row = 1; foreach (DataGridViewRow linha in tabela.Rows) { for (int column = 1; column < tabela.ColumnCount; column++) { if (linha.Cells[column].Value != null) { workSheet.Cells[row, column] = linha.Cells[column].Value.ToString(); } } row++; } // Define a largura das colunas automaticamente Excel.Range aRange = workSheet.get_Range("A1", "H" + tabela.RowCount); Console.WriteLine("A1 " + " H" + tabela.RowCount); aRange.Columns.AutoFit(); btnExportar.Text = "Exportar"; btnExportar.Enabled = true; excelApp.Visible = true; }
public void readExcel() { string valueString = string.Empty; objExcelApp = new Microsoft.Office.Interop.Excel.Application(); objBooks = (Excel.Workbooks)objExcelApp.Workbooks; //Open the workbook containing the address data. objBook = objBooks.Open(@"C:\Temp\data\Test.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //Get a reference to the first sheet of the workbook. objSheets = objBook.Worksheets; objSheet = (Excel._Worksheet)objSheets.get_Item(1); //Select the range of data containing the addresses and get the outer boundaries. rngLast = objSheet.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell); long lLastRow = rngLast.Row; long lLastCol = rngLast.Column; // Iterate through the data and concatenate the values into a comma-delimited string. for (long rowCounter = 1; rowCounter <= lLastRow; rowCounter++) { for (long colCounter = 1; colCounter <= lLastCol; colCounter++) { //Write the next value into the string. Excel.Range cell = (Excel.Range)objSheet.Cells[rowCounter, colCounter]; string cellvalue = cell.Value.ToString(); //TODO: add your business logic for retrieve cell value } } }
public void createExcelSumCategories(Excel._Worksheet oSheet, int rowInHands, int rowInflow, int rowOutflow) { oSheet.Cells[4, 5] = "SUMMARY"; oSheet.Cells[5, 5] = "Inflow = "; oSheet.Cells[6, 5] = "Outflow = "; oSheet.Cells[7, 5] = "In hands = "; if (rowInHands == 4) /* Gdy nie znajdzie zadnych maili w IN-HANDS */ { oSheet.Cells[7, 6].Value = 0; } else { oSheet.Cells[7, 6].Formula = "=ROWS(C5:C" + rowInHands + ")"; } if (rowInflow == 4) /* Gdy nie znajdzie zadnych maili w INFLOW */ { oSheet.Cells[5, 6].Value = 0; } else { oSheet.Cells[5, 6].Formula = "=ROWS(A5:A" + rowInflow + ")"; } if (rowOutflow == 4) /* Gdy nie znajdzie zadnych maili w OUTFLOW */ { oSheet.Cells[6, 6].Value = 0; } else { oSheet.Cells[6, 6].Formula = "=ROWS(B5:B" + rowOutflow + ")"; } oSheet.get_Range("E5", "E7").Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; }
private void build_comp_graphs(Excel.Worksheet worksheet, Excel._Worksheet worksheet2, int irows, int orows) { for (int i = 1; i <= irows; i++) { string col1 = String.Format("A{0}", i); string col2 = String.Format("B{0}", i); string col3 = String.Format("C{0}", i); worksheet.Range[col1].Formula = String.Format("='yearly_steps'!{0}", col1); worksheet.Range[col2].Formula = String.Format("='yearly_steps'!{0}", col2); if (i == 4) { worksheet.Range[col3].Formula = String.Format("={0}", col2); } else if (i > 4) { worksheet.Range[col3].Formula = String.Format("=(({0} - {1}) * {2})+{3}", col1, String.Format("A{0}", i - 1), col2, String.Format("C{0}", i - 1)); } } Excel.ChartObjects charts = worksheet.ChartObjects(); Excel.ChartObject chartObject = charts.Add(200, 100, 750, 400); Excel.Chart chart = chartObject.Chart; chart.ChartType = Excel.XlChartType.xlXYScatterLines; Excel.Series series = chartObject.Chart.SeriesCollection().Add(worksheet2.get_Range("B4", String.Format("B{0}", orows))); series.XValues = worksheet2.get_Range("A4", String.Format("A{0}", orows)); series.Name = "Original Data"; series = chartObject.Chart.SeriesCollection().Add(worksheet.Range[String.Format("B4:B{0}", irows)]); series.XValues = worksheet.Range[String.Format("A4:A{0}", irows)]; series.Name = "Interpolated Data"; series.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleDash; chartObject = charts.Add(200, 525, 750, 400); chart = chartObject.Chart; chart.ChartType = Excel.XlChartType.xlXYScatterLines; series = chartObject.Chart.SeriesCollection().Add(worksheet.get_Range("B4", String.Format("B{0}", orows))); series.XValues = worksheet.get_Range("A4", String.Format("A{0}", orows)); series.Name = "Original Data"; series = chartObject.Chart.SeriesCollection().Add(worksheet.Range[String.Format("C4:C{0}", irows)]); series.XValues = worksheet.Range[String.Format("A4:A{0}", irows)]; series.Name = "Interpolated Data"; series.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleDash; }
// A9: Number of classroom teachers by teaching level of education, employment status, type of institution and sex void sheetA9(Excel.Application excelApp, SqlConnection sqlConn, string year, string country) { //Constant references for columns and rows const int FEMALE_OFFSET = 1; //row offset const int PUBLIC = 14; const int PRIVATE = 17; Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.Worksheets["A9"]; workSheet.Activate(); Excel.Range usedRange = workSheet.UsedRange; Func <string, int> getCol = null; getCol = n => usedRange.Find(n).Column; getCol.Memoize(); SqlCommand cmd = new SqlCommand( @"select ISCED, SCHOOLTYPE, GENDER, sum(COUNT) as COUNT from #TeacherBaseTable group by ISCED, SCHOOLTYPE, GENDER", sqlConn); using (SqlDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { string isced = rdr.GetString(0); string schoolType = rdr.GetString(1); string gender = rdr.GetString(2); int count = rdr.GetInt32(3); Console.WriteLine(String.Format("{0}, {1}, {2}, {3}", isced, gender, schoolType, count.ToString())); int rowOffset = gender == "M" ? 0 : FEMALE_OFFSET; int row = schoolType == "PUBLIC" ? PUBLIC : PRIVATE + rowOffset; List <string> columns = new List <string>(); if (isced == "ISCED 24" || isced == "ISCED 34") { columns.Add("ISCED 24+34"); columns.Add(isced.Substring(0, 7)); } else if (isced == "ISCED 25" || isced == "ISCED 35") { columns.Add("ISCED 25+35"); columns.Add(isced.Substring(0, 7)); } else { columns.Add(isced); } foreach (string column in columns) { workSheet.Cells[row, getCol(column)] = workSheet.get_Range(helpers.GetCellAddress(getCol(column), row)).Value2 + count; } } } }
//virtual method /// <summary> /// Create an wookSheet of the Excel /// </summary> /// <param name="dataTable"></param> /// <param name="sheetName"></param> public virtual void CreatSheet(SQL.DataTable dataTable, string sheetName) { /*-------------------------*/ //create work sheet name #region create a woork sheet by name WorkSheet = (Excel._Worksheet)_Excel.Worksheets.Add(); WorkSheet.Name = sheetName; Console.WriteLine("\n----------------------------------------"); Console.WriteLine("Create a new work sheet[{0}]", sheetName); #endregion /*-------------------------*/ //Add column names to excel sheet #region add sheet header and set sheet width and font string[] colNames = new string[dataTable.Columns.Count]; int col = 0; //fetch column names from dtData foreach (SQL.DataColumn dc in dataTable.Columns) { colNames[col++] = dc.ColumnName; } //last column for english alphabet //char lastColumn = (char)(65 + dataTable.Columns.Count - 1); string lastColumnName = GetExcelColumnName(dataTable.Columns.Count); Console.WriteLine("lastColumn: " + lastColumnName); //assign data to column headers WorkSheet.get_Range("A1", lastColumnName + "1").Value2 = colNames; //set width WorkSheet.Columns.AutoFit(); //set columns format to text format WorkSheet.Columns.NumberFormat = "@"; //set column headers' font to bold //WorkSheet.get_Range("A1", lastColumnName + "1").Font.Bold = true; #endregion /*-------------------------*/ // Add DataRows data to Excel #region New method for add DataRows data to Excel --- Slower Console.WriteLine("Generating data for Sheet[{0}]...", sheetName); string data = null; for (int i = 0; i <= dataTable.Rows.Count - 1; i++) { for (int j = 0; j <= dataTable.Columns.Count - 1; j++) { data = dataTable.Rows[i].ItemArray[j].ToString(); WorkSheet.Cells[i + 2, j + 1] = data; } } #endregion Console.WriteLine("Sheet [{0}] created!", sheetName); Console.WriteLine("----------------------------------------\n"); }
public ReportBuilder() { app = new Excel.Application(); appBooks = app.Workbooks; currentBook = appBooks.Add(Missing.Value); sheets = currentBook.Worksheets; currentSheet = (Excel._Worksheet)sheets.get_Item(1); range = currentSheet.get_Range("A1", Missing.Value); charts = currentSheet.ChartObjects(Type.Missing); chartObject = charts.Add(400, LastIndex, chartWidth, chartHeight); }
private void automateExcelSpreadsheet_Click(object sender, EventArgs e) { Excel.Range oRng; try { oXL = new Excel.Application(); oXL.Visible = true; oWB = oXL.Workbooks.Add(Missing.Value); oWB.Title = "Test"; oSheet = (Excel._Worksheet)oWB.ActiveSheet; oSheet.Name = "Test"; oSheet.Cells[1, 1] = "First Name"; oSheet.Cells[1, 2] = "Last Name"; oSheet.Cells[1, 3] = "E-mail"; oSheet.Cells[1, 4] = "Salary"; oSheet.get_Range("A1", "D1").Font.Bold = true; oSheet.get_Range("A1", "D1").RowHeight = 37; oSheet.get_Range("A1", "D1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; string[,] saNames = new string[5, 2]; saNames[0, 0] = "John"; saNames[0, 1] = "Smith"; saNames[1, 0] = "Tom"; saNames[1, 1] = "Brown"; saNames[2, 0] = "Sue"; saNames[2, 1] = "Thomas"; saNames[3, 0] = "Jane"; saNames[3, 1] = "Jones"; saNames[4, 0] = "Adams"; saNames[4, 1] = "Johnson"; oSheet.get_Range("A2", "B6").Value2 = saNames; oRng = oSheet.get_Range("C2", "C6"); oRng.Formula = "=A2 & \".\" & B2 & \"@\" & \"gmail.com\""; oRng = oSheet.get_Range("D2", "D6"); oRng.Formula = "=RAND()*10000"; oRng.NumberFormat = "$0.00"; oRng = oSheet.get_Range("A1", "D1"); oRng.EntireColumn.AutoFit(); DisplayQuarterlySales(oSheet); oXL.Visible = true; oXL.UserControl = true; } catch (Exception exception) { string errorMessage = "Error :"; errorMessage = string.Concat(errorMessage, exception.Message); errorMessage = string.Concat(errorMessage, " Line: "); errorMessage = string.Concat(errorMessage, exception.Source); MessageBox.Show(errorMessage, "Error"); } }
public List<AccountExcel> readExcel(string sExcelPath) { var lReturn = new List<AccountExcel>(); string valueString = string.Empty; objExcelApp = new Microsoft.Office.Interop.Excel.Application(); objBooks = (Excel.Workbooks)objExcelApp.Workbooks; //Open the workbook containing the address data. objBook = objBooks.Open(sExcelPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //Get a reference to the first sheet of the workbook. objSheets = objBook.Worksheets; objSheet = (Excel._Worksheet)objSheets.get_Item(1); rngLast = objSheet.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell); long lLastRow = rngLast.Row; long lLastCol = rngLast.Column; for (long rowCounter = 2; rowCounter <= lLastRow; rowCounter++) //FirstRow Has Headers - start at row 2 { if (ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 1]).Value) != "") { var adAccount = new AccountExcel(); adAccount.sCustomerNumber = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 1]).Value); adAccount.sAccountName = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 40]).Value); adAccount.sAddressLine1 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 2]).Value); adAccount.sAddressLine2 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 5]).Value); adAccount.sAddressLine3 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 9]).Value); adAccount.sPostCode = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 15]).Value); adAccount.sTelephone = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 17]).Value); adAccount.sVatNumber = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 18]).Value); adAccount.sCountryCode = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 21]).Value); adAccount.sEmail = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 37]).Value); adAccount.sWeb = "";// ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 38]).Value); adAccount.sKAM = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 31]).Value); adAccount.sRegion = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 24]).Value); lReturn.Add(adAccount); } } //Close the Excel Object objBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value); objBooks.Close(); objExcelApp.Quit(); Marshal.ReleaseComObject(objSheet); Marshal.ReleaseComObject(objSheets); Marshal.ReleaseComObject(objBooks); Marshal.ReleaseComObject(objBook); Marshal.ReleaseComObject(objExcelApp); objSheet = null; objSheets = null; objBooks = null; objBook = null; objExcelApp = null; GC.GetTotalMemory(false); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.GetTotalMemory(true); return (lReturn); }