public void ExpotToExcel(DataGridView dataGridView1,string SaveFilePath) { xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); int i = 0; int j = 0; for (i = 0; i <= dataGridView1.RowCount - 1; i++) { for (j = 0; j <= dataGridView1.ColumnCount - 1; j++) { DataGridViewCell cell = dataGridView1[j, i]; xlWorkSheet.Cells[i + 1, j + 1] = cell.Value; } } xlWorkBook.SaveAs(SaveFilePath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); MessageBox.Show("Your file is saved" + SaveFilePath); }
/// <exception cref="FileNotFoundException"><c>FileNotFoundException</c>.</exception> /// <exception cref="Exception"><c>Exception</c>.</exception> public IList<IDataRecord> GetDataFromExcel(string filePath) { try { Log.Info("Начало импорта"); if (!File.Exists(filePath)) throw new FileNotFoundException(string.Format("Файл не найден. [{0}]", filePath)); Excelapp = new Application { Visible = false }; Workbook = Excelapp.Workbooks.Open(filePath, 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); ReadDoc(); } catch( Exception e) { Log.Error("Ошибка импорта", e); throw; } finally { Workbook.Close(); Excelapp.Quit(); Excelapp = null; } return DataRecords; }
private void button1_Click(object sender, EventArgs e) { MyApp = new Excel.Application(); MyApp.Visible = false; MyBook = MyApp.Workbooks.Open(path); MySheet = (Excel.Worksheet)MyBook.Sheets[1]; lastrow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row; BindingList<Dompet> DompetList = new BindingList<Dompet>(); for (int index = 2; index <= lastrow; index++) { System.Array MyValues = (System.Array)MySheet.get_Range ("A" + index.ToString(),"F" + index.ToString()).Cells.Value; DompetList.Add(new Dompet { JmlPemesanan = MyValues.GetValue(1,1).ToString(), JmlPekerja = MyValues.GetValue(1,2).ToString(), Peralatan = MyValues.GetValue(1,3).ToString(), JenisKulit = MyValues.GetValue(1,4).ToString(), ModelDompet = MyValues.GetValue(1,5).ToString(), Prediksi = MyValues.GetValue(1,6).ToString() }); } dataGridView1.DataSource = (BindingList<Dompet>)DompetList; dataGridView1.AutoResizeColumns(); }
public LoadandMatchMetricsNames(String dashboardFile) { try { app = new Excel.Application(); app.Visible = true; dashboard = app.Workbooks.Open(dashboardFile, 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); /* for (int i = 0; i < metricsFiles.Length; i++) { metrics = app.Workbooks.Open(metricsFiles[i], 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); //this is where we read all the data into some sort of array //ExcelScanInternal(metrics); metrics.Close(false, metricsFiles[i], null); } */ //cleanup //dashboard.Close(false, dashboardFile, null); } catch (Exception ex) { Console.WriteLine(ex.Message); } }
public Workbook(Excel.Workbook wb, Excel.Application app, Action dispose_callback) { _app = app; _wb = wb; _wb_name = wb.Name; _dispose_callback = dispose_callback; }
public ExcelHelper(string _filePath) { filePath = _filePath; excelApp = new Excel.Application(); object objOpt = System.Reflection.Missing.Value; wbclass = (Excel.Workbook)excelApp.Workbooks.Open(_filePath, objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt); }
private static void RemovePageRecords(Workbook wb) { var ws = wb.Sheets[1] as Excel.Worksheet; if (ws == null) return; var va = ws.UsedRange.Value2 as object[,]; if (va == null) return; var rows = va.GetUpperBound(0); var cols = va.GetUpperBound(1); var pageCol = -1; Range delRange = null; for (var row = 1; row <= rows; row++) for (var col = (pageCol < 0 ? 1 : pageCol); col <= (pageCol < 0 ? cols : pageCol); col++) if (IsPageTotal(va[row, col])) { pageCol = col; delRange = delRange == null ? ws.Rows[row] : _xl.Union(delRange, ws.Rows[row]) as Range; } if (delRange != null && delRange.Rows.Count > 0) delRange.EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp); }
/// <summary> /// Class constructor does all the work /// </summary> /// <param name="excelApp"></param> /// <param name="fileName"></param> public StudentWorkbook( Excel.Application excelApp, string fileName ) { try // to open the student's spreadsheet { excelWorkbook = excelApp.Workbooks.Open( fileName, 0, true, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, // read only false, 0, true, false, false ); } catch ( Exception e ) { Console.WriteLine( "error: " + e.Message ); Console.WriteLine( "Could not open spreadsheet " + fileName ); } Excel.Sheets excelSheets = excelWorkbook.Worksheets; // get the Worksheets collection Excel.Worksheet excelWorksheet = excelSheets[ 1 ]; // get the first one // get the Team Number cell Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range( "B4", "B4" ); // try to convert this cell to an integer if ( ( teamNumber = TryForInt( excelCell.Value ) ) == 0 ) { Console.WriteLine( "\nTeam number invalid in " + fileName + "\n" ); } // get the scores cells scores = excelWorksheet.get_Range( "B7", "B15" ); // get the Additional Comments cell comments = excelWorksheet.get_Range( "B18", "B18" ); } // end of StudentWorkbook()
private void Init() { xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Open(name, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlSh = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); }
public getData readData() { getData.dateT.Clear(); getData.prj.Clear(); getData.descr.Clear(); getData.taskTime.Clear(); notes.Clear(); ExcelApp = new Excel.Application(); ExcelApp.Visible = false; WorkBookExcel = ExcelApp.Workbooks.Open(_filePath, false); //открываем книгу WorkSheetExcel = (Excel.Worksheet)WorkBookExcel.Sheets[1]; //Получаем ссылку на лист 1 //excelcells = excelworksheet.get_Range("D215", Type.Missing); //Выбираем ячейку для вывода A1 // WorkSheetExcel.Cells[i, 1].Text.ToString() != "" for (int i = 2; WorkSheetExcel.Cells[i, 1].Text.ToString() != ""; i++) { getData.dateT.Add(WorkSheetExcel.Cells[i, 1].Text.ToString()); getData.prj.Add(WorkSheetExcel.Cells[i, 2].Text.ToString()); getData.descr.Add(WorkSheetExcel.Cells[i, 3].Text.ToString()); getData.taskTime.Add(WorkSheetExcel.Cells[i, 4].Text.ToString()); } WorkBookExcel.Close(false, Type.Missing, Type.Missing); //закрыл не сохраняя ExcelApp.Quit(); GC.Collect(); getData.dateT.Reverse(); getData.prj.Reverse(); getData.descr.Reverse(); getData.taskTime.Reverse(); return getData; }
//************************************************************************* // Constructor: GroupByVertexAttributeDialog() // /// <overloads> /// Initializes a new instance of the <see /// cref="GroupByVertexAttributeDialog" /> class. /// </overloads> /// /// <param name="workbook"> /// Workbook containing the graph contents. /// </param> //************************************************************************* public GroupByVertexAttributeDialog( Microsoft.Office.Interop.Excel.Workbook workbook ) { InitializeComponent(); m_oWorkbook = workbook; // Instantiate an object that saves and retrieves the user settings for // this dialog. Note that the object automatically saves the settings // when the form closes. m_oGroupByVertexAttributeDialogUserSettings = new GroupByVertexAttributeDialogUserSettings(this); if ( ExcelUtil.TryGetTable(m_oWorkbook, WorksheetNames.Vertices, TableNames.Vertices, out m_oVertexTable) ) { cbxVertexColumnName.PopulateWithTableColumnNames(m_oVertexTable); } cbxVertexColumnFormat.PopulateWithObjectsAndText( ExcelColumnFormat.Other, "Categories", ExcelColumnFormat.Number, "Numbers", ExcelColumnFormat.Date, "Dates", ExcelColumnFormat.Time, "Times", ExcelColumnFormat.DateAndTime, "Dates with times" ); DoDataExchange(false); AssertValid(); }
public ClusteringManager() { //prepare Excel objects: m_ObjExcel = new Microsoft.Office.Interop.Excel.Application(); m_ObjWorkBook = m_ObjExcel.Workbooks.Open( MethodInputResponse.INPUT_FILE_PATH, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); m_ObjWorkSheet1 = (myExcel.Worksheet)m_ObjWorkBook.Sheets[1]; myExcel.Sheets xlSheets = m_ObjWorkBook.Sheets as myExcel.Sheets; for (int i = 3; i < MAX_CLUSTERS_NUMBER; i++) { xlSheets.Add(Type.Missing, m_ObjWorkSheet1, Type.Missing, Type.Missing); } }
/// <summary> /// Метод закрытия приложения Excel /// </summary> public void CloseExcelDoc() { excelBook.Close(falseObj, missingObj, missingObj); excelApp.Quit(); excelBook = null; excelApp = null; }
/// <summary> /// 构造函数,将一个已有Excel工作簿作为模板,并指定输出路径 /// </summary> /// <param name="templetFilePath">Excel模板文件路径</param> /// <param name="outputFilePath">输出Excel文件路径</param> public ExcelBase(string templetFilePath, string outputFilePath) { if (templetFilePath == null) throw new Exception("Excel模板文件路径不能为空!"); if (outputFilePath == null) throw new Exception("输出Excel文件路径不能为空!"); if (!File.Exists(templetFilePath)) throw new Exception("指定路径的Excel模板文件不存在!"); this.templetFile = templetFilePath; this.outputFile = outputFilePath; //创建一个Application对象并使其可见 beforeTime = DateTime.Now; app = new Excel.ApplicationClass(); app.Visible = true; afterTime = DateTime.Now; //打开模板文件,得到WorkBook对象 workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); //得到WorkSheet对象 workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1); }
public TaskPriority() { InitializeComponent(); missing = System.Reflection.Missing.Value; config_data.ConfigFile = Environment.GetEnvironmentVariable("USERPROFILE")+"\\IntCallBack.xls"; xlApp = new msexcel.Application(); time_wasting = false; if (File.Exists(config_data.ConfigFile)) { xlWorkBook = xlApp.Workbooks.Open(config_data.ConfigFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); xlWorkSheet = (msexcel.Worksheet) xlWorkBook.Worksheets.get_Item(1); double dummy = (double) (xlWorkSheet.Cells[1, 2] as msexcel.Range ).Value ; config_data.PopUp = ((int)dummy == 1) ? true : false; config_data.RFrequency = (int)(xlWorkSheet.Cells[2, 2] as msexcel.Range).Value; config_data.Urgent_Hrs = (int)(xlWorkSheet.Cells[3, 2] as msexcel.Range).Value; config_data.Urgent_Mins = (int)(xlWorkSheet.Cells[4, 2] as msexcel.Range).Value; config_data.task1 = (string) (xlWorkSheet.Cells[5, 2] as msexcel.Range).Value; config_data.task2 = (string)(xlWorkSheet.Cells[6, 2] as msexcel.Range).Value; config_data.task3 = (string)(xlWorkSheet.Cells[7, 2] as msexcel.Range).Value; config_data.task4 = (string)(xlWorkSheet.Cells[8, 2] as msexcel.Range).Value; re_load_flag = true; } else { xlWorkBook = xlApp.Workbooks.Add(missing); xlWorkSheet = xlWorkBook.Worksheets.get_Item(1); config_data.PopUp = true; config_data.RFrequency = 3; config_data.Urgent_Hrs = 8; config_data.Urgent_Mins = 0; config_data.task1 = config_data.task2 = config_data.task3 = config_data.task4 = ""; xlWorkSheet.Cells[1, 1] = "PopUP"; xlWorkSheet.Cells[2, 1] = "Frequency"; xlWorkSheet.Cells[3, 1] = "Urgent Hrs"; xlWorkSheet.Cells[4, 1] = "Urgent Mins"; xlWorkSheet.Cells[5,1] = "Task 1"; xlWorkSheet.Cells[6,1] = "Task 2"; xlWorkSheet.Cells[7,1] = "Task 3"; xlWorkSheet.Cells[8,1] = "Task 4"; xlWorkSheet.Cells[1, 2] = (config_data.PopUp == true) ? "1" : "2"; xlWorkSheet.Cells[2, 2] = config_data.RFrequency.ToString(); xlWorkSheet.Cells[3, 2] = config_data.Urgent_Hrs.ToString(); xlWorkSheet.Cells[4, 2] = config_data.Urgent_Mins.ToString(); xlWorkSheet.Cells[5, 2] = config_data.task1; xlWorkSheet.Cells[6, 2] = config_data.task1; xlWorkSheet.Cells[7, 2] = config_data.task1; xlWorkSheet.Cells[8, 2] = config_data.task1; xlWorkBook.SaveAs(config_data.ConfigFile, msexcel.XlFileFormat.xlWorkbookNormal, missing, missing, missing, missing, msexcel.XlSaveAsAccessMode.xlShared, missing, missing, missing, missing, missing); //xlWorkBook.Close(); re_load_flag = false; } }
public void reporttoexcel_clients(List<string> station_name_list, List<int> station_turnover_list, List<int> station_avg_list) { Eapp.Visible = true; string path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location); book = Eapp.Workbooks.Open(path + @"\отчет_клиента_шаблон.xlsx"); excel.Worksheet sheet = (excel.Worksheet)book.Worksheets.get_Item(1); excel.Range range_sheet = sheet.UsedRange; for (int i = 2; i < station_name_list.Count + 2; i++) { excel.Range range_cur = range_sheet.Cells[i, 1]; range_cur.Value2 = station_name_list[i - 2]; range_cur = range_sheet.Cells[i, 2]; range_cur.Value2 = station_turnover_list[i - 2]; range_cur = range_sheet.Cells[i, 3]; range_cur.Value2 = station_avg_list[i - 2]; } book.SaveAs(path + @"\reports\отчет_клиента.xlsx"); Eapp.Quit(); }
private void Form1_Load(object sender, EventArgs e) { app = new Excel.Application(); app.Visible = false; workbook = app.Workbooks.Open(Application.StartupPath + "\\Дни рождения сотрудников.xls"); worksheet = workbook.ActiveSheet; int i = 0; string today = DateTime.Now.ToString("dd.MM"); string tomorrow = DateTime.Now.AddDays(1).ToString("dd.MM"); for (i = 1; i <= worksheet.UsedRange.Rows.Count; i++) { if (worksheet.Cells[2][i].Value == "Іб та ПД" || worksheet.Cells[3][i].Value == "Іб та ПД" || worksheet.Cells[2][i].Value == "Інформаційної безпеки та передачі даних" || worksheet.Cells[3][i].Value == "Інформаційної безпеки та передачі даних") { string birthday = worksheet.Cells[4][i].Value.ToString("dd.MM"); if (birthday == today) { label1.Text = ""; label1.Text += "Сегодня свой день рождения отмечает " + worksheet.Cells[1][i].Value; } if (birthday == tomorrow) { label1.Text = ""; label1.Text += "Завтра свой день рождения отмечает " + worksheet.Cells[1][i].Value; } } } app.Quit(); }
public void export() { try { //excelApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application"); //wb = excelApp.Workbooks.Add(); wb = (Excel.Workbook)Globals.ThisAddIn.Application.ActiveWorkbook; ws = wb.Worksheets.get_Item(1) as Excel.Worksheet; // 데이타 넣기 int r = 1; foreach (var d in this.exceptionList_) { ws.Cells[r, 1] = d.Message; r++; } //this.wb.Worksheets.Add(ws); } finally { // Clean up ReleaseExcelObject(ws); ReleaseExcelObject(wb); ReleaseExcelObject(excelApp); } }
public static void InitializeExcel() { _myApp = new Excel.Application {Visible = false}; _myBook = _myApp.Workbooks.Open(ExcelPath); _mySheet = (Excel.Worksheet)_myBook.Sheets[1]; // Explict cast is not required here _lastRow = _mySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row; }
/// <summary> /// Function for initializing access to and validating the resources found in the given directory. /// </summary> public void InitializePlanGenerator() { //A message of where the resource files are located Console.WriteLine("EventFiles: " + resourcepath); try { //Initialize excel accesser class excel = new Excel.Application(); //Path to the excel sheet is created var excelSheetPath = Path.GetFullPath(resourcepath); //The excel arc found at the resource path is opened and a gateway is initialized //TODO: create a system for choosing the correct file CurrentWorkBook = excel.Workbooks.Open(excelSheetPath); //The plangenerationhandler function is called PlanGenerationHandler(); } catch (Exception) { } finally { EvalMessage(); //COM objects countermeasure - this is needed for the excel processes to be closed Process[] excelProcs = Process.GetProcessesByName("EXCEL"); foreach (Process proc in excelProcs) { proc.Kill(); } } }
/// <summary> /// Creates a spreadsheet in the give xls filename. /// </summary> /// <param name="filename">The complete filename with the absolute path.</param> /// <param name="sheetname">The name of the sheet e.g. Hidden</param> /// <returns>True if succeeded, false if failed.</returns> public static bool createWorksheet(String filename, String sheetname, bool needsToBeHidden = false) { successStatus = false; try { successStatus = openXlApp(); CurrentSpreadSheet css = CurrentSpreadSheet.Instance; //checking if the call is being made for the currently open worbook. this is less expensive. if ((css.CurrentWorkBook != null) && (css.CurrentWorkBook.FullName == filename)) { xlSheets = css.CurrentWorkBook.Sheets as Excel.Sheets; } else { xlWorkbook = openXlWorkBook(filename); xlSheets = xlWorkbook.Sheets as Excel.Sheets; } xlSheet = (Excel.Worksheet)xlSheets.Add(xlSheets[xlSheets.Count + 1]); xlSheet.Name = sheetname; if (needsToBeHidden) xlSheet.Visible = Excel.XlSheetVisibility.xlSheetHidden; xlWorkbook.Save(); successStatus = quitXlApp(); } finally { garbageCollect(); } return successStatus; }
static void Main(string[] args) { //GetSPList("http://sharepoint/sites/fim/ISV9-3", "v-jianzh", "isnes21)$MAM", "fareast.corp.microsoft.com"); //foreach (ListItem listItem in listItems) //{ // Console.WriteLine("ID:{0} Application Name:{1}", listItem.Id, listItem["Application_x0020_Name"].ToString()); //} xApp = new Excel.Application(); xApp.Visible = true; xbook = xApp.Workbooks.Open("C:\\share\\temp.xlsx"); GetVisibleFormExcel(); //for (int i = 0; i < nameArr.Count;i++ ) //{ // Console.WriteLine("Name:{0} IE:{1} FireFox:{2} Chrome:{3}", nameArr[i],ieArr[i],firefoxArr[i],chromeArr[i]); //} UpdateToMasterList(); xbook = null; xApp.Quit(); xApp = null; Console.ReadLine(); }
public ComExportExcel(string templatePath) { xlApp = new ComExcel.Application(); xlWorkBook = xlApp.Workbooks.Open(templatePath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", true, false, 0, true, 1, 0); //xlWorkBook = xlApp.Workbooks.Open(templatePath, ReadOnly:false, Editable:true ); xlWorkSheet = (ComExcel.Worksheet)xlWorkBook.Worksheets.get_Item(1); }
public static void openXL(string xlp) { xla = new Excel.Application(); xlw = xla.Workbooks.Open(xlp); xls = xlw.Worksheets.get_Item(1); xlr = xls.UsedRange; }
public void ReadProcesses() { count = 0; ExcelApp = new Excel.Application(); ExcelApp.Visible = false; WorkBookExcel = ExcelApp.Workbooks.Open(_filePath, false); //открываем книгу //Читаем данные по проектам WorkSheetExcel = (Excel.Worksheet)WorkBookExcel.Sheets["Processes"]; //Получаем ссылку на лист Processes List<string> row = new List<string>(); int n = 6; for (int i = 2; WorkSheetExcel.Cells[i, 1].Text.ToString() != ""; i++) { row = new List<string>(); for (int j = 1; j < n; j++) row.Add(WorkSheetExcel.Cells[i, j].Text.ToString()); //строка массива заполняется просто суммой i и j Mas.Add(row); //строка добавляется в массив count++; } //test = WorkSheetExcel.Cells[2, 1]; WorkBookExcel.Close(false, Type.Missing, Type.Missing); ExcelApp.Quit(); GC.Collect(); }
//加载 private void Form1_Load(object sender, EventArgs e) { panel1.Visible = false; app = new MSExcel.Application(); app.Visible = false; book = app.Workbooks.Open(@"D:\template.xls"); sheet = (MSExcel.Worksheet)book.ActiveSheet; //串口设置默认选择项 cbSerial.SelectedIndex = 1; //note:获得COM9口,但别忘修改 cbBaudRate.SelectedIndex = 5; cbDataBits.SelectedIndex = 3; cbStop.SelectedIndex = 0; cbParity.SelectedIndex = 0; //sp1.BaudRate = 9600; Control.CheckForIllegalCrossThreadCalls = false; //这个类中我们不检查跨线程的调用是否合法(因为.net 2.0以后加强了安全机制,,不允许在winform中直接跨线程访问控件的属性) sp1.DataReceived += new SerialDataReceivedEventHandler(sp1_DataReceived); //sp1.ReceivedBytesThreshold = 1; radio1.Checked = true; //单选按钮默认是选中的 rbRcvStr.Checked = true; //准备就绪 sp1.DtrEnable = true; sp1.RtsEnable = true; //设置数据读取超时为1秒 sp1.ReadTimeout = 1000; sp1.Close(); }
private void drawGraph() { Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); try { xlWorkBook = xlApp.Workbooks.Open("C:\\Users\\DELL\\Desktop\\ReadFromSerial_DrawGraphic\\csharp-Excel.xls", 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //Get all the sheets in the workbook while (thread1.IsAlive) { //son satır bulunuyor excel dosyasındaki Excel.Range last = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing); Excel.Range range = xlWorkSheet.get_Range("A1", last); int lastUsedRow = last.Row; int lastUsedColumn = last.Column; string ReceiveData = myport.ReadLine(); // comdan degeri okuyuruz // alınan degerdeki stringleri temizleyerek sadece double değeri yakalıyor string[] HeatingData = ReceiveData.Split(':'); string[] HeatingData2 = HeatingData[1].Split('D'); var result = HeatingData2[0]; double heating = Convert.ToDouble(result); theTime = DateTime.Now; // anlik olarak zamani ogreniyoruz! string zaman = theTime.ToString("yyyy/MM/dd HH:mm:ss"); Thread.Sleep(1000); // ilk threadi anlik olarak durduruyor ve Invoke ile GUI threadini ulasip cizdiriyor! this.Invoke((MethodInvoker)delegate { chart1.Series["Series1"].Points.AddY(result); // excel dosyasındaki son yazılan satırdan bir sonraki satıra sıcaklığı yazdırıyor xlWorkSheet.Cells[lastUsedRow+1, 2] = (heating / 100); xlWorkSheet.Cells[lastUsedRow + 1, 1] = zaman; }); } } catch { // MessageBox.Show("Dosya bulunamadı"); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Cells[1, 1] = "Zaman"; xlWorkSheet.Cells[1, 2] = "Sıcaklık Celcius"; xlWorkBook.SaveAs("C:\\Users\\DELL\\Desktop\\ReadFromSerial_DrawGraphic\\csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); MessageBox.Show("Dosya oluşturuldu , proje klasörünüzde bulunmaktadır"); } }
public void BackUpCreate(string pach) { IDbCommandBuilder bilder = database.GetDbCommandBuilder(); DataTable dt = new DataTable(); DbConnection connect = (DbConnection)bilder.Connection; var cmd = connect.CreateCommand(); string FilePath = SearchFilePath(pach); excelApp = new Microsoft.Office.Interop.Excel.Application(); workbook = excelApp.Workbooks.Open(FilePath, 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); DataTable oriTable = new DataTable(); connect.Open(); try { foreach (Microsoft.Office.Interop.Excel.Worksheet ws in workbook.Sheets) { originalTableName = ws.Name; oriTable = originalData.Tables.Add(originalTableName); var SearchSQL = new StringBuilder(); SearchSQL.Append(" SELECT * \n"); SearchSQL.Append(" FROM \n"); SearchSQL.Append(originalTableName); cmd.CommandText = SearchSQL.ToString(); var reader = cmd.ExecuteReader(); originalData.Tables[originalTableName].Load(reader); CreateXlsDs createXls = CreateXlsDs.GetInstance(); createXls.SetSchema(originalData.Clone()); reader.Dispose(); } } catch (Exception ex) { ErrorMessages.Append(ex.Message); } finally { if (workbook != null) { #region Clean Up Close the workbook and release all the memory. workbook.Close(false, pach, Missing.Value); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); #endregion } workbook = null; if (excelApp != null) { excelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); } excelApp = null; connect.Close(); connect.Dispose(); } }
public static void InitializeExcel() { MyApp = new Excel.Application(); MyApp.Visible = false; MyBook = MyApp.Workbooks.Open(DB_PATH); MySheet = (Excel.Worksheet)MyBook.Sheets[1]; // Explict cast is not required here lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row; }
private void btnGenerate_Click(object sender, EventArgs e) { if (cbModel.SelectedIndex == 0) { MessageBox.Show("Select Model ..."); return; } int dayofyr = dateTimePicker1.Value.DayOfYear; int model = cbModel.SelectedIndex; //System.Data.OleDb.OleDbDataAdapter MyCommand,MyCommand1; System.Data.DataSet DtSet; DtSet = new System.Data.DataSet(); DataSet dtt = new DataSet(); string resultpath = ""; switch (model) { case 1: //LMS dtt.Clear(); resultpath = Properties.Settings.Default.lsrresult; //MyCommand = GetData(dayofyr, resultpath); Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(resultpath, ReadOnly: false); xlApp.Visible = false; Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; System.Data.OleDb.OleDbConnection MyConnection; System.Data.OleDb.OleDbDataAdapter MyCommand; MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + resultpath + "';Extended Properties=Excel 8.0;"); MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Results$] where Day_Number=" + dayofyr, MyConnection); MyCommand.Fill(dtt).ToString(); MyCommand.Dispose(); MyConnection.Close(); xlWorkbook.Close(); xlApp.Quit(); break; case 2: //SVM dtt.Clear(); resultpath = Properties.Settings.Default.svmresult; Excel.Application xlApp1 = new Excel.Application(); Excel.Workbook xlWorkbook1 = xlApp1.Workbooks.Open(resultpath, ReadOnly: false); xlApp1.Visible = false; Excel._Worksheet xlWorksheet1 = xlWorkbook1.Sheets[1]; Excel.Range xlRange1 = xlWorksheet1.UsedRange; System.Data.OleDb.OleDbConnection MyConnection1; System.Data.OleDb.OleDbDataAdapter MyCommand1; MyConnection1 = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + resultpath + "';Extended Properties=Excel 8.0;"); MyCommand1 = new System.Data.OleDb.OleDbDataAdapter("select * from [Results$] where Day_Number=" + dayofyr, MyConnection1); MyCommand1.Fill(dtt).ToString(); MyCommand1.Dispose(); MyConnection1.Close(); xlWorkbook1.Close(); xlApp1.Quit(); //MyCommand = GetData(dayofyr, resultpath); //MyCommand.Fill(dtt); break; case 3: //Both dtt.Clear(); resultpath = Properties.Settings.Default.lsrresult; Excel.Application xlApp2 = new Excel.Application(); Excel.Workbook xlWorkbook2 = xlApp2.Workbooks.Open(resultpath, ReadOnly: false); xlApp2.Visible = false; Excel._Worksheet xlWorksheet2 = xlWorkbook2.Sheets[1]; Excel.Range xlRange2 = xlWorksheet2.UsedRange; System.Data.OleDb.OleDbConnection MyConnection2; System.Data.OleDb.OleDbDataAdapter MyCommand2; MyConnection2 = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + resultpath + "';Extended Properties=Excel 8.0;"); MyCommand2 = new System.Data.OleDb.OleDbDataAdapter("select * from [Results$] where Day_Number=" + dayofyr, MyConnection2); MyCommand2.Fill(dtt).ToString(); MyCommand2.Dispose(); MyConnection2.Close(); xlWorkbook2.Close(); xlApp2.Quit(); //MyCommand = GetData(dayofyr, resultpath); // MyCommand.Fill(dtt); resultpath = Properties.Settings.Default.svmresult; Excel.Application xlApp3 = new Excel.Application(); Excel.Workbook xlWorkbook3 = xlApp3.Workbooks.Open(resultpath, ReadOnly: false); xlApp3.Visible = false; Excel._Worksheet xlWorksheet3 = xlWorkbook3.Sheets[1]; Excel.Range xlRange3 = xlWorksheet3.UsedRange; System.Data.OleDb.OleDbConnection MyConnection3; System.Data.OleDb.OleDbDataAdapter MyCommand3; MyConnection3 = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + resultpath + "';Extended Properties=Excel 8.0;"); MyCommand3 = new System.Data.OleDb.OleDbDataAdapter("select * from [Results$] where Day_Number=" + dayofyr, MyConnection3); MyCommand3.Fill(dtt).ToString(); MyCommand3.Dispose(); MyConnection3.Close(); xlWorkbook3.Close(); xlApp3.Quit(); //MyCommand = GetData(dayofyr, resultpath); //MyCommand.Fill(dtt); break; default: //0 or else dtt.Clear(); //MessageBox.Show("Select Model ..."); break; } //MyCommand.Fill(dtt).ToString(); dataGridView1.DataSource = dtt.Tables[0]; dataGridView1.DataBindings.ToString(); /*string pathname = Path.GetDirectoryName(path); * filePath1.ReadOnly = true; * filePath1.Text = path; * Excel.Application xlApp = new Excel.Application(); * Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@path, ReadOnly: false); * xlApp.Visible = false; * Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; * Excel.Range xlRange = xlWorksheet.UsedRange; * opLstCst2.Items.Clear(); * System.Data.OleDb.OleDbConnection MyConnection; * System.Data.DataSet DtSet; * System.Data.OleDb.OleDbDataAdapter MyCommand, MyCommand1; * MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + path + "';Extended Properties=Excel 8.0;"); * MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection); * DtSet = new System.Data.DataSet(); * MyCommand.Fill(dtt).ToString(); * dataGridViewDisplayPanel.DataSource = dtt.Tables[0]; * dataGridViewDisplayPanel.DataBindings.ToString(); * for (int m = 0; m < dataGridViewDisplayPanel.Columns.Count; m++) * { * opLstCst2.Items.Add(dataGridViewDisplayPanel.Columns[m].Name); * } * xlWorkbook.Close(); * xlApp.Quit(); * lblTotal.Text = "Total: " + dataGridViewDisplayPanel.Rows.Count;*/ }
public void ExportFileFromDataGridViewEmployee(System.Windows.Forms.DataGridView dgv, string fileName) { //set properties for SaveFileDilog SaveFileDialog sfdSave = new SaveFileDialog(); sfdSave.Filter = "Excel file(*.xls)|*.xls"; sfdSave.Title = "Save to Excel file"; sfdSave.FileName = fileName; //proccess save file excel if (sfdSave.ShowDialog() == DialogResult.OK) { //create file infomation FileInfo f = new FileInfo(sfdSave.FileName); //delete if file exists if (f.Exists == true) { f.Delete(); } //Create excel file ExcelCOM.Application exApp = new ExcelCOM.Application(); ExcelCOM.Workbook exBook = exApp.Workbooks.Add(ExcelCOM.XlWBATemplate.xlWBATWorksheet); ExcelCOM.Worksheet exSheet = (ExcelCOM.Worksheet)exBook.Worksheets[1]; //ExcelCOM.Range chartRange; exSheet.Name = fileName; //list field will be exported List <string> field = new List <string> { "EmployeeCode", "EmployeeName", "UserName", "Gender", "BirthDate", "PhoneNumber", "Email", "Address", "EmployeeTypeName", "CountryName", "EmployeeTypeName2", "CountryName2", "EmployeeTypeName3", "CountryName3", "HourlyWages", "CreatedDate", "UpdatedDate", "Notes" }; //import header int col = 1; for (int i = 0; i < field.Count; i++) { exSheet.Cells[1, col] = dgv.Columns[field[i]].HeaderText; col++; } //import data for (int i = 0; i < dgv.Rows.Count; i++) { col = 1; for (int j = 0; j < field.Count; j++) { exSheet.Cells[i + 2, col] = dgv.Rows[i].Cells[field[j]].Value.ToString(); col++; } } //(ExcelCOM.XlLineStyle.xlContinuous, ExcelCOM.XlBorderWeight.xlMedium, ExcelCOM.XlColorIndex.xlColorIndexAutomatic, ExcelCOM.XlColorIndex.xlColorIndexAutomatic); //chartRange.BorderAround(ExcelCOM.XlLineStyle.xlContinuous, ExcelCOM.XlBorderWeight.xlMedium, ExcelCOM.XlColorIndex.xlColorIndexAutomatic, ExcelCOM.XlColorIndex.xlColorIndexAutomatic); exApp.Visible = false; //Save file excel into dictionary that you choose exBook.SaveAs(sfdSave.FileName, ExcelCOM.XlFileFormat.xlWorkbookNormal, null, null, false, false, ExcelCOM.XlSaveAsAccessMode.xlExclusive, false, false, false, false, false); //close and release object exBook.Close(false, false, false); exApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp); CustomMessageBox.MessageBox.ShowCustomMessageBox(Common.clsLanguages.GetResource("CRM385"), Common.clsLanguages.GetResource("CRM11"), Common.Config.CUSTOM_MESSAGEBOX_ICON.Information, Common.Config.CUSTOM_MESSAGEBOX_BUTTON.OK); } }
public string GetCellValue( string FileName, string SheetName, string CellAddress) { string CellValue = ""; if (System.IO.File.Exists(FileName)) { bool Proceed = false; Excel.Application xlApp = null; Excel.Workbooks xlWorkBooks = null; Excel.Workbook xlWorkBook = null; Excel.Worksheet xlWorkSheet = null; Excel.Sheets xlWorkSheets = null; Excel.Range xlCells = null; xlApp = new Excel.Application(); xlApp.DisplayAlerts = false; xlWorkBooks = xlApp.Workbooks; xlWorkBook = xlWorkBooks.Open(FileName); xlApp.Visible = false; xlWorkSheets = xlWorkBook.Sheets; for (int x = 1; x <= xlWorkSheets.Count; x++) { xlWorkSheet = (Excel.Worksheet)xlWorkSheets[x]; if (xlWorkSheet.Name == SheetName) { Proceed = true; break; } System.Runtime.InteropServices .Marshal.FinalReleaseComObject(xlWorkSheet); xlWorkSheet = null; } if (Proceed) { xlCells = xlWorkSheet.Range[CellAddress]; try { Console.WriteLine("Bold: {0}", xlCells.Font.Bold); // bool Console.WriteLine("Italic: {0}", xlCells.Font.Italic); // bool ForeColor = System.Drawing.ColorTranslator.FromOle((int)xlCells.Font.Color); CellValue = Convert.ToString(xlCells.Value); } catch (Exception) { // Reduntant CellValue = ""; } } else { MessageBox.Show(SheetName + " not found."); } xlWorkBook.Close(); xlApp.UserControl = true; xlApp.Quit(); ReleaseComObject(xlCells); ReleaseComObject(xlWorkSheets); ReleaseComObject(xlWorkSheet); ReleaseComObject(xlWorkBook); ReleaseComObject(xlWorkBooks); ReleaseComObject(xlApp); } else { MessageBox.Show("'" + FileName + "' not located. Try one of the write examples first."); } return(CellValue); }
private void Generate() { var WordApp = new Word.Application(); WordApp.Visible = false; var WordDocument = WordApp.Documents.Open(@"" + pathDoc); Excel.Application ObjWorkExcel = new Excel.Application(); Excel.Workbook ObjWorkBook = ObjWorkExcel.Workbooks.Open(@"" + pathXls); Excel.Worksheet ObjWorkSheet = (Excel.Worksheet)ObjWorkBook.Sheets[1]; try { int nInLastRow = ObjWorkSheet.Cells.Find("*", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row; int nInLastCol = ObjWorkSheet.Cells.Find("*", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSearchOrder.xlByColumns, Excel.XlSearchDirection.xlPrevious, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Column; string[,] list = new string[nInLastRow, nInLastCol]; for (int i = 0; i < nInLastRow; i++) { for (int j = 0; j < nInLastCol; j++) { list[i, j] = ObjWorkSheet.Cells[i + 1, j + 1].Text.ToString(); } } for (int i = 0; i < nInLastRow; i++) { for (int j = 0; j < nInLastCol; j++) { string tmp = list[i, j]; ReplaceWordStub("{" + j + "}", tmp, WordDocument); if (j == nInLastCol - 1) { string fName = Regex.Replace(list[i, j], "[\"«»]", ""); WordDocument.SaveAs(@"" + pathSave + "\\" + fName + ".docx"); WordDocument.Close(false, Type.Missing, Type.Missing); WordDocument = WordApp.Documents.Open(@"" + pathDoc); } } } } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error); } finally { WordDocument.Close(false, Type.Missing, Type.Missing); WordApp.Quit(); ObjWorkBook.Close(false, Type.Missing, Type.Missing); ObjWorkExcel.Quit(); GC.Collect(); } }
public static void WriteTabSoFar(Tournament tournament, DataContext context, int roundNumber) { SqlHelper helper = new SqlHelper(tournament.Database); List <Round> rounds = helper.GetRounds().Result.Where(r => r.RoundNumber <= roundNumber).ToList(); List <Tuple <int, List <Debate> > > debates = new List <Tuple <int, List <Debate> > >(); foreach (Round round in rounds) { debates.Add(Tuple.Create(round.RoundNumber, helper.GetDebates(round.RoundId).Result.ToList())); } List <SpeakerDraw> draws = new List <SpeakerDraw>(); foreach (Tuple <int, List <Debate> > debate in debates) { foreach (Debate d in debate.Item2) { draws.AddRange(helper.GetSpeakerDrawsByDebate(d.DebateId).Result); } } List <Speaker> speakers = new List <Speaker>(); speakers.AddRange(context.Speakers); speakers.AsParallel().ForAll(s => s.Draws = draws.Where(d => d.SpeakerId.Equals(s.SpeakerId)).ToList()); speakers = speakers.OrderByDescending(s => s.Draws.Sum(sd => sd.Result.Points())).ThenByDescending(s => s.Draws.Sum(sd => sd.SpeakerPoints)).ToList(); Excel.Application excelApplication = new Excel.Application() { Visible = false }; Excel.Workbook excelWorkbook = excelApplication.Workbooks.Add(); Excel.Sheets excelSheets = excelWorkbook.Worksheets; Excel.Worksheet sheet = (Excel.Worksheet)excelSheets.get_Item("Sheet1"); Excel.Range cell = sheet.get_Range("A2"); cell.Value2 = "Speaker"; cell = sheet.get_Range("B2"); cell.Value2 = "Total Points"; cell = sheet.get_Range("C2"); cell.Value2 = "Total Speaker Points"; int columns = 1; int columnLetter = 'D'; for (int i = 1; i <= roundNumber; i++) { cell = sheet.get_Range(string.Format("{0}1", (char)columnLetter)); cell.Value2 = string.Concat("Round ", i); cell = sheet.get_Range(string.Format("{0}2", (char)columnLetter++)); cell.Value2 = "Result"; cell = sheet.get_Range(string.Format("{0}2", (char)columnLetter++)); cell.Value2 = "Speaker Points"; columns += 3; } int letter = 'A'; int row = 3; foreach (Speaker speaker in speakers) { letter = 'A'; cell = sheet.get_Range(string.Format("{0}{1}", (char)letter++, row)); cell.Value2 = speaker.Name; cell = sheet.get_Range(string.Format("{0}{1}", (char)letter++, row)); cell.Value2 = speaker.Draws.Sum(x => x.Result.Points()); cell = sheet.get_Range(string.Format("{0}{1}", (char)letter++, row)); cell.Value2 = speaker.Draws.Sum(x => x.SpeakerPoints); foreach (Round round in rounds) { List <Debate> theseDebates = debates[round.RoundNumber - 1].Item2; SpeakerDraw speakerDraw = speaker.Draws.First(sd => theseDebates.Select(d => d.DebateId).Contains(sd.DebateId)); cell = sheet.get_Range(string.Format("{0}{1}", (char)letter++, row)); cell.Value2 = speakerDraw.Result.Points(); cell = sheet.get_Range(string.Format("{0}{1}", (char)letter++, row)); cell.Value2 = speakerDraw.SpeakerPoints; } row++; } string directory = Path.Combine(Path.GetDirectoryName(tournament.Location), string.Format("Round {0}", roundNumber)); if (!Directory.Exists(directory)) { Directory.CreateDirectory(directory); } string filename = Path.Combine(directory, string.Format("Round {0} - tab.xlsx", roundNumber)); string endCell = string.Format("{0}{1}", (char)(letter - 1), row - 1); cell = sheet.get_Range("A1", endCell); cell.Columns.AutoFit(); excelApplication.DisplayAlerts = false; excelWorkbook.SaveAs(filename); excelWorkbook.Close(); excelApplication.DisplayAlerts = true; excelApplication.Quit(); }
private void XlsToResx(string xlsFile) { if (!File.Exists(xlsFile)) { return; } string path = new FileInfo(xlsFile).DirectoryName; Excel.Application app = new Excel.Application(); Excel.Workbook wb = app.Workbooks.Open(xlsFile, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); Excel.Sheets sheets = wb.Worksheets; Excel.Worksheet sheet = (Excel.Worksheet)sheets.get_Item(1); bool hasLanguage = true; int col = 5; while (hasLanguage) { object val = (sheet.Cells[2, col] as Excel.Range).Text; if (val is string) { if (!String.IsNullOrEmpty((string)val)) { string cult = (string)val; string pathCulture = path + @"\" + cult; if (!System.IO.Directory.Exists(pathCulture)) { System.IO.Directory.CreateDirectory(pathCulture); } ResXResourceWriter rw = null; int row = 3; string fileSrc; string fileDest; bool readrow = true; while (readrow) { fileSrc = (sheet.Cells[row, 1] as Excel.Range).Text.ToString(); fileDest = (sheet.Cells[row, 2] as Excel.Range).Text.ToString(); if (String.IsNullOrEmpty(fileDest)) { break; } string f = pathCulture + @"\" + JustStem(fileDest) + "." + cult + ".resx"; rw = new ResXResourceWriter(f); while (readrow) { string key = (sheet.Cells[row, 3] as Excel.Range).Text.ToString(); object data = (sheet.Cells[row, col] as Excel.Range).Text.ToString(); if ((key is String) & !String.IsNullOrEmpty(key)) { if (data is string) { string text = data as string; text = text.Replace("\\r", "\r"); text = text.Replace("\\n", "\n"); rw.AddResource(new ResXDataNode(key, text)); } row++; string file = (sheet.Cells[row, 2] as Excel.Range).Text.ToString(); if (file != fileDest) { break; } } else { readrow = false; } } rw.Close(); } } else { hasLanguage = false; } } else { hasLanguage = false; } col++; } }
private void DataSetToXls(ResxData rd, string fileName) { Excel.Application app = new Excel.Application(); Excel.Workbook wb = app.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Sheets sheets = wb.Worksheets; Excel.Worksheet sheet = (Excel.Worksheet)sheets.get_Item(1); sheet.Name = "Localize"; sheet.Cells[1, 1] = "Resx source"; sheet.Cells[1, 2] = "Resx Name"; sheet.Cells[1, 3] = "Key"; sheet.Cells[1, 4] = "Value"; string[] cultures = GetCulturesFromDataSet(rd); int index = 5; foreach (string cult in cultures) { CultureInfo ci = new CultureInfo(cult); sheet.Cells[1, index] = ci.DisplayName; sheet.Cells[2, index] = ci.Name; index++; } DataView dw = rd.Resx.DefaultView; dw.Sort = "FileSource, Key"; int row = 3; foreach (DataRowView drw in dw) { ResxData.ResxRow r = (ResxData.ResxRow)drw.Row; sheet.Cells[row, 1] = r.FileSource; sheet.Cells[row, 2] = r.FileDestination; sheet.Cells[row, 3] = r.Key; sheet.Cells[row, 4] = r.Value; ResxData.ResxLocalizedRow[] rows = r.GetResxLocalizedRows(); foreach (ResxData.ResxLocalizedRow lr in rows) { string culture = lr.Culture; int col = Array.IndexOf(cultures, culture); if (col >= 0) { sheet.Cells[row, col + 5] = lr.Value; } } row++; } sheet.Cells.get_Range("A1", "Z1").EntireColumn.AutoFit(); // Save the Workbook and quit Excel. wb.SaveAs(fileName, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); wb.Close(false, m_objOpt, m_objOpt); app.Quit(); }
private void btnCreateBM_Click(object sender, EventArgs e) { grvVT.FocusedRowHandle = -1; if (grvVT.RowCount == 0) { return; } //string materialCode = TextUtils.ToString(grvModuleM.GetFocusedRowCellValue(colCodeM)); //string materialName = TextUtils.ToString(grvModuleM.GetFocusedRowCellValue(colNameM)); //if (materialName == "") //{ // MessageBox.Show("Tên vật tư không được để trống!", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop); // return; //} string path = ""; FolderBrowserDialog fbd = new FolderBrowserDialog(); if (fbd.ShowDialog() == DialogResult.OK) { path = fbd.SelectedPath; } else { return; } string filePath = Application.StartupPath + "\\Templates\\YCVT.xls"; string currentPath = path + "\\YCVT." + DateTime.Now.ToString("ddMMyyy") + ".xls"; try { File.Copy(filePath, currentPath, true); } catch (Exception ex) { MessageBox.Show("Có lỗi khi tạo file yêu cầu cấp vật tư!" + Environment.NewLine + ex.Message, TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop); return; } using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo Yêu cầu cấp vật tư...")) { System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Excel.Application app = default(Excel.Application); Excel.Workbook workBoook = default(Excel.Workbook); Excel.Worksheet workSheet = default(Excel.Worksheet); try { app = new Excel.Application(); app.Workbooks.Open(currentPath); workBoook = app.Workbooks[1]; workSheet = (Excel.Worksheet)workBoook.Worksheets[1]; workSheet.Cells[6, 3] = txtCustomer.Text.Trim(); workSheet.Cells[6, 5] = txtCustomerCode.Text.Trim(); workSheet.Cells[6, 9] = txtPhuTrachHD.Text.Trim(); workSheet.Cells[7, 3] = txtKhachHangCuoi.Text.Trim(); workSheet.Cells[7, 5] = TextUtils.ToString(grvContract.GetFocusedRowCellValue(colContractCode)); workSheet.Cells[13, 7] = "Tân Phát, ngày " + DateTime.Now.Day + " tháng " + DateTime.Now.Month + " năm " + DateTime.Now.Year; for (int i = grvVT.RowCount - 1; i >= 0; i--) { workSheet.Cells[12, 1] = i + 1; workSheet.Cells[12, 2] = grvVT.GetRowCellDisplayText(i, colMaName); workSheet.Cells[12, 3] = grvVT.GetRowCellDisplayText(i, colMaCode); workSheet.Cells[12, 4] = grvVT.GetRowCellDisplayText(i, colMaHang); workSheet.Cells[12, 5] = grvVT.GetRowCellDisplayText(i, colUnit); workSheet.Cells[12, 6] = grvVT.GetRowCellDisplayText(i, colMaQty); workSheet.Cells[12, 7] = grvVT.GetRowCellDisplayText(i, colMaModule); workSheet.Cells[12, 8] = grvVT.GetRowCellDisplayText(i, colDate); workSheet.Cells[12, 9] = grvVT.GetRowCellDisplayText(i, colNote); //workSheet.Cells[12, 10] = grvVT.GetRowCellDisplayText(i, colMaPrice); //workSheet.Cells[12, 11] = grvVT.GetRowCellDisplayText(i, colMaTotal); //workSheet.Cells[12, 12] = grvVT.GetRowCellDisplayText(i, colMaDes); ((Excel.Range)workSheet.Rows[12]).Insert(); } ((Excel.Range)workSheet.Rows[11]).Delete(); ((Excel.Range)workSheet.Rows[11]).Delete(); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { if (app != null) { app.ActiveWorkbook.Save(); app.Workbooks.Close(); app.Quit(); } } Process.Start(currentPath); } }
public void OutputAsExcelFile(DataGridView dataGridView) { if (dataGridView.Rows.Count <= 0) { MessageBox.Show("无数据!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } string filePath = ""; SaveFileDialog s = new SaveFileDialog(); s.Title = "保存Excel文件"; s.Filter = "Excel文件(*.xls)|*.xls"; s.FilterIndex = 1; if (s.ShowDialog() == DialogResult.OK) { filePath = s.FileName; } else { return; } //第一步:将dataGridView转化为dataTable,这样可以过滤掉dataGridView中的隐藏列 DataTable tmpDataTable = new DataTable("tmpDataTable"); DataTable modelTable = new DataTable("ModelTable"); for (int column = 0; column < dataGridView.Columns.Count; column++) { if (dataGridView.Columns[column].Visible == true) { DataColumn tempColumn = new DataColumn(dataGridView.Columns[column].HeaderText, typeof(string)); tmpDataTable.Columns.Add(tempColumn); DataColumn modelColumn = new DataColumn(dataGridView.Columns[column].Name, typeof(string)); modelTable.Columns.Add(modelColumn); } } for (int row = 0; row < dataGridView.Rows.Count; row++) { if (dataGridView.Rows[row].Visible == false) { continue; } DataRow tempRow = tmpDataTable.NewRow(); for (int i = 0; i < tmpDataTable.Columns.Count; i++) { tempRow[i] = dataGridView.Rows[row].Cells[modelTable.Columns[i].ColumnName].Value; } tmpDataTable.Rows.Add(tempRow); } if (tmpDataTable == null) { return; } //第二步:导出dataTable到Excel long rowNum = tmpDataTable.Rows.Count; //行数 int columnNum = tmpDataTable.Columns.Count; //列数 Excel.Application m_xlApp = new Excel.Application(); m_xlApp.DisplayAlerts = false;//不显示更改提示 m_xlApp.Visible = false; Excel.Workbooks workbooks = m_xlApp.Workbooks; Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 try { string[,] datas = new string[rowNum + 1, columnNum]; for (int i = 0; i < columnNum; i++) //写入字段 { datas[0, i] = tmpDataTable.Columns[i].Caption; } //Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]); Excel.Range range = m_xlApp.Range[worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]]; range.Interior.ColorIndex = 15;//15代表灰色 range.Font.Bold = true; range.Font.Size = 10; int r = 0; for (r = 0; r < rowNum; r++) { for (int i = 0; i < columnNum; i++) { object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()]; datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式 } System.Windows.Forms.Application.DoEvents(); //添加进度条 } //Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]); Excel.Range fchR = m_xlApp.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]]; fchR.Value2 = datas; worksheet.Columns.EntireColumn.AutoFit(); //列宽自适应。 //worksheet.Name = "dd"; //m_xlApp.WindowState = Excel.XlWindowState.xlMaximized; m_xlApp.Visible = false; // = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]); range = m_xlApp.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]]; //range.Interior.ColorIndex = 15;//15代表灰色 range.Font.Size = 9; range.RowHeight = 14.25; range.Borders.LineStyle = 1; range.HorizontalAlignment = 1; workbook.Saved = true; workbook.SaveCopyAs(filePath); } catch (Exception ex) { MessageBox.Show("导出异常:" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning); } finally { EndReport(); } m_xlApp.Workbooks.Close(); m_xlApp.Workbooks.Application.Quit(); m_xlApp.Application.Quit(); m_xlApp.Quit(); MessageBox.Show("导出成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); }
private void saveFileToExcel(string filename) { try { Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Add(true); Excel.Worksheet xlWorksheet = (Excel.Worksheet)xlApp.ActiveSheet; Excel.Range xlRange = xlWorksheet.UsedRange; for (int i = 0; i < DataGridView1.ColumnCount; i++) { xlRange = (Excel.Range)xlWorksheet.Cells[1, i + 1]; xlRange.Value2 = DataGridView1.Columns[i].HeaderText; xlRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; } for (int j = 0; j < DataGridView1.RowCount; j++) { for (int i = 0; i < DataGridView1.ColumnCount; i++) { xlRange = (Excel.Range)xlWorksheet.Cells[j + 2, i + 1]; if (DataGridView1[i, j].Value == null) { xlRange.Value2 = "0.0"; } else { xlRange.Value2 = DataGridView1[i, j].Value.ToString(); xlRange.Font.Color = System.Drawing.ColorTranslator.ToOle(DataGridView1[i, j].Style.ForeColor); DataGridView1[i, j].Value = ""; } xlRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; } } xlApp.DisplayAlerts = false; xlWorkbook.SaveAs(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //if (printer) //xlWorkbook.PrintOut(1, 1, 1, false, true, false, true, filename); if (xlApp != null) { xlWorkbook.Close(Type.Missing, filename, Type.Missing); xlApp.Quit(); Marshal.ReleaseComObject(xlWorksheet); Marshal.ReleaseComObject(xlWorkbook); Marshal.ReleaseComObject(xlApp); xlWorksheet = null; xlWorkbook = null; xlApp = null; } GC.Collect(); } catch (Exception ex) { MessageBox.Show(ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void btnExcelExport_Click(object sender, EventArgs e) { MsExl.Application app = null; MsExl.Workbook wb = null; MsExl.Worksheet ws = null; if (MessageBox.Show("This process will take few minutes to generate file. \n Click OK to Continue.", this.Text, MessageBoxButtons.OKCancel, MessageBoxIcon.Information) != DialogResult.OK) { return; } try { panel1.Enabled = false; panel2.Enabled = false; panel3.Enabled = false; this.Cursor = Cursors.WaitCursor; app = new MsExl.Application(); app.Visible = false; wb = app.Workbooks.Add(MsExl.XlWBATemplate.xlWBATWorksheet); ws = (MsExl.Worksheet)wb.ActiveSheet; // Headers. for (int i = 0; i < grdBusRouteList.Columns.Count; i++) { ws.Cells[1, i + 1] = grdBusRouteList.Columns[i].Name; } // Content. for (int i = 0; i < grdBusRouteList.Rows.Count; i++) { for (int j = 0; j < grdBusRouteList.Columns.Count; j++) { if (grdBusRouteList.Rows[i].Cells[j].Value != null) { ws.Cells[i + 2, j + 1] = grdBusRouteList.Rows[i].Cells[j].Value.ToString(); } } } // Autofit Cells,Column and Rows Width. ws.Columns.AutoFit(); ws.Rows.WrapText = true; ws.Range[ws.Cells[1, 1], ws.Cells[grdBusRouteList.Rows.Count + 1, grdBusRouteList.Columns.Count]].Borders.LineStyle = true; ws.Range[ws.Cells[1, 1], ws.Cells[1, grdBusRouteList.Columns.Count]].Font.Bold = true; wb.SaveAs(GetExportFileName(".xls"), MsExl.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); wb.Close(Missing.Value, Missing.Value, Missing.Value); app.Quit(); app = null; wb = null; ws = null; MessageBox.Show("Export Excel file successfully created at below mentioned path. \n " + GetExportFileName(".xls"), this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information); this.Cursor = Cursors.Default; panel1.Enabled = true; panel2.Enabled = true; panel3.Enabled = true; } catch (Exception ex) { this.Cursor = Cursors.Default; panel1.Enabled = true; panel2.Enabled = true; panel3.Enabled = true; if (app != null) { wb = null; ws = null; app.Quit(); app = null; } MessageBox.Show(ex.Message, this.Text + " : btnExcelExport_Click : " + ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error); } }
/// <summary> /// Метод создания и сохранения документов /// в форматах Microsoft Word (doc, PDF), /// Excel (exls) /// </summary> /// <param name="type">Тип создаваемого документа /// отчёт или статистика</param> /// <param name="format">Формат сохранения /// документ или таблица</param> /// <param name="name">Название документа</param> /// <param name="table">Входная таблица с данными</param> public void Document_Create(Document_Type type, Document_Format format, string name, DataTable table) { //Получение данных о конфигурации документа Configuration_class configuration_Class = new Configuration_class(); configuration_Class.Document_Configuration_Get(); //Проверка на пустоту названия switch (name != "" || name != null) { case true: //Выбор формата либо Word либо Excel switch (format) { case Document_Format.Word: //Запуск процесса в дистпечере задач word.Application application = new word.Application(); //создание документа в процессе word.Document document //Присвоение документа процессу, Visible: true //возможность редактирования документа = application.Documents.Add(Visible: true); try { //Объявление дипапазона для формирования текста word.Range range = document.Range(0, 0); //89Настройка отступов в документе document.Sections.PageSetup.LeftMargin = application.CentimetersToPoints( (float)Configuration_class. doc_Left_Merge); document.Sections.PageSetup.TopMargin = application.CentimetersToPoints( (float)Configuration_class. doc_Top_Merge); document.Sections.PageSetup.RightMargin = application. CentimetersToPoints((float) Configuration_class.doc_Right_Merg); document.Sections.PageSetup.BottomMargin = application.CentimetersToPoints( (float)Configuration_class. doc_Bottom_Merge); //Присвоение текстового знеачения в дипазон range.Text = "Ресторан"; //Настройка выравнивания текста range.ParagraphFormat.Alignment = word.WdParagraphAlignment. wdAlignParagraphCenter; //Настройка интервала после абзаца range.ParagraphFormat.SpaceAfter = 1; //Настройка интервала перед абзаца range.ParagraphFormat.SpaceBefore = 1; //Настройка межстрочного интервала range.ParagraphFormat.LineSpacingRule = word.WdLineSpacing.wdLineSpaceSingle; //Настройка названия шрифта range.Font.Name = "Times New Roman"; //Настройка размера шрифта range.Font.Size = 12; //Добавление параграфов document.Paragraphs.Add(); //В конце текста document.Paragraphs.Add(); //Свободный document.Paragraphs.Add(); //Для будущего текста //Параграф для названия документа word.Paragraph Document_Name = document.Paragraphs.Add(); //Настройка параграфа через свойство диапазона Document_Name.Format.Alignment = word.WdParagraphAlignment.wdAlignParagraphCenter; Document_Name.Range.Font.Name = "Times New Roman"; Document_Name.Range.Font.Size = 16; //Проверка на тип документа, отчёт или статистика switch (type) { case Document_Type.Report: Document_Name.Range.Text = "ОТЧЁТ"; break; case Document_Type.Statistic: Document_Name.Range.Text = "СТАТИСТИЧЕСКИЙ ОТЧЁТ"; break; } document.Paragraphs.Add(); document.Paragraphs.Add(); document.Paragraphs.Add(); word.Paragraph statparg = document.Paragraphs.Add(); //Создание области таблицы в документе word.Table stat_table //Добавление таблицы в область документа //Указывается параграф в котором документ создан //Количество строк и столбцов = document.Tables.Add(statparg.Range, table.Rows.Count, table.Columns.Count); //Настройка границ таблицы внутренние stat_table.Borders.InsideLineStyle = word.WdLineStyle.wdLineStyleSingle; //Настройка границ таблицы внешние stat_table.Borders.OutsideLineStyle = word.WdLineStyle.wdLineStyleSingle; //Выравнивание текста внутри ячеек по ширине stat_table.Rows.Alignment = word.WdRowAlignment.wdAlignRowCenter; //Выравнивание текста внутри ячеек по высоте stat_table.Range.Cells.VerticalAlignment = word.WdCellVerticalAlignment.wdCellAlignVerticalCenter; stat_table.Range.Font.Size = 11; stat_table.Range.Font.Name = "Times New Roman"; //Индексация столбцов и строк в Word начинается с 1,1 for (int row = 1; row <= table.Rows.Count; row++) { for (int col = 1; col <= table.Columns.Count; col++) { stat_table.Cell(row, col).Range.Text = table.Rows[row - 1][col - 1].ToString(); } } document.Paragraphs.Add(); document.Paragraphs.Add(); //Парадграф с фиксациейц даты создания документа word.Paragraph Footparg = document.Paragraphs.Add(); Footparg.Range.Text = string.Format("Дата создания \t\t\t{0}", DateTime.Now.ToString("dd.MM.yyyy")); } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); } finally { switch (format) { case Document_Format.Word: //Сохранение документа с названием из метода, //и в формате doc document.SaveAs2(string.Format("{0}\\{1}", Environment.CurrentDirectory, name), word.WdSaveFormat.wdFormatDocument); break; case Document_Format.PDF: //Сохранение документа в формате PDF document.SaveAs2(string.Format("{0}\\{1}", Environment.CurrentDirectory, name), word.WdSaveFormat.wdFormatPDF); break; } //Закрываем документ document.Close(); //Выходим из процесса с его закрытием application.Quit(); } break; case Document_Format.Excel: //Создание процесса Excel excel.Application application_ex = new excel.Application(); //Создание книги excel.Workbook workbook = application_ex.Workbooks.Add(); //Создание страницы excel.Worksheet worksheet = (excel.Worksheet)workbook.ActiveSheet; try { switch (type) { case Document_Type.Report: //Название страницы worksheet.Name = "Отчёт"; for (int row = 0; row < table.Rows.Count; row++) { for (int col = 0; col < table.Columns.Count; col++) { //ЗАнесение данных в ячейку worksheet.Cells[row + 1][col + 1] = table.Rows[row][col].ToString(); } } //Указание диапазона работы с ячеёками листа excel.Range border //Начало диапазона = worksheet.Range[worksheet.Cells[1, 1], //Динамический конец диапазона в зависимости от //выдодимых данных worksheet.Cells[table.Rows.Count + 1] [table.Columns.Count + 1]]; //Стиль линий границ ячеек border.Borders.LineStyle = excel.XlLineStyle.xlContinuous; //Выравнивание во высоте border.VerticalAlignment = excel.XlHAlign.xlHAlignCenter; //Выравнивание по ширине border.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter; //Внесение даты создания документа worksheet.Cells[table.Rows.Count + 3][2] = string.Format("Дата создания {0}", DateTime.Now.ToString()); //Объединение ячеек worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2], worksheet.Cells[table.Rows.Count + 2, table.Columns.Count + 2]].Merge(); break; case Document_Type.Statistic: worksheet.Name = "Статистический отчёт"; for (int row = 0; row < table.Rows.Count; row++) { for (int col = 0; col < table.Columns.Count; col++) { worksheet.Cells[row + 1][col + 1] = table.Rows[row][col].ToString(); } } excel.Range border1 = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[table.Rows.Count + 1] [table.Columns.Count + 1]]; border1.Borders.LineStyle = excel.XlLineStyle.xlContinuous; border1.VerticalAlignment = excel.XlHAlign.xlHAlignCenter; border1.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter; worksheet.Cells[table.Rows.Count + 3][2] = string.Format("Дата создания {0}", DateTime.Now.ToString()); worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2], worksheet.Cells[table.Rows.Count + 2, table.Columns.Count + 2]].Merge(); //Класс области графиков excel.ChartObjects chartObjects = (excel.ChartObjects)worksheet.ChartObjects( Type.Missing); //Область размещения графиков: отступы слева сверху, //размер ширина и высота excel.ChartObject chartObject = chartObjects.Add(300, 50, 250, 250); //Объявление области графика excel.Chart chart = chartObject.Chart; //Объявление колекции построений графиков excel.SeriesCollection seriesCollection = (excel.SeriesCollection)chart.SeriesCollection( Type.Missing); //Объявление посторения графика excel.Series series = seriesCollection.NewSeries(); //Тип графика chart.ChartType = excel.XlChartType.xl3DColumn; //Диапазон значений по оси X series.XValues = worksheet.get_Range("B2", "B" + table.Rows.Count + 1); //Диапазон значений по оси Y series.Values = worksheet.get_Range("C2", "C" + table.Rows.Count + 1); break; } } catch { } finally { //Сохранение книги workbook.SaveAs(string.Format("{0}\\{1}", Environment.CurrentDirectory, name), application_ex.DefaultSaveFormat); //Закрытие книги workbook.Close(); //Завершение процесса application_ex.Quit(); } break; } break; case false: System.Windows.Forms.MessageBox.Show ("Введите название документп"); break; } }
private void runModel(object sender, EventArgs e) { TextBox[] array = { data1, data2, data3, data4, data5, data6, data7, data8, data9 }; TextBox[] fireHole = { hole50, hole5 }; notificationPanel.Show(); Excel.Application excelApp = null; Excel.Workbooks books = null; Excel.Workbook inputFile = null; Excel.Workbook templateFile = null; Excel.Worksheet inputSheets = null; Excel.Worksheet fireBallSheet = null; Excel.Worksheet templateSheet = null; Excel.Worksheet wellLinesTransect = null; Excel.Sheets templateSheets = null; Excel.Range dataRange = null; Excel.Range holeRange = null; Excel.Range fireBallRange = null; try { excelApp = new Excel.Application(); excelApp.Visible = false; excelApp.DisplayAlerts = false; books = excelApp.Workbooks; inputFile = books.Open(Path.Combine(Environment.CurrentDirectory, @"Workbooks\well_lines.xlsx")); inputSheets = inputFile.Sheets["Well lines input sheet"]; fireBallSheet = inputFile.Sheets["Fire Ball"]; dataRange = inputSheets.Range["B1", "B9"]; int i = 0; foreach (Excel.Range cell in dataRange) { cell.Value = array[i].Text; i++; Marshal.FinalReleaseComObject(cell); } holeRange = inputSheets.Range["B17", "B18"]; int iter = 0; foreach (Excel.Range cell in holeRange) { cell.Value = fireHole[iter].Text; iter++; Marshal.FinalReleaseComObject(cell); } int index = 0; fireBallRange = fireBallSheet.Range["E4", "E104"]; foreach (Excel.Range cell in fireBallRange) { double step5 = double.Parse(step5factor.Text); double value = double.Parse(InitPage.GasLineValues.plantGasLine[index]) * double.Parse(step5factor.Text); cell.Value = value; index++; Marshal.FinalReleaseComObject(cell); } templateFile = books.Open(Path.Combine(Environment.CurrentDirectory, @"Templates\Well Results.xlsx")); templateSheet = templateFile.Sheets["Well_Lines_Data"]; templateSheets = templateFile.Sheets; inputSheets.Range["B1", "B11"].Copy(templateSheet.Range["B1", "B11"]); inputSheets.Range["B17", "B18"].Copy(templateSheet.Range["B16", "B17"]); wellLinesTransect = templateSheets.Add(); wellLinesTransect.Name = "Well_Lines_Transect"; inputSheets.ChartObjects("Chart 3").Chart.CopyPicture(); wellLinesTransect.Paste(); templateFile.SaveAs(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), data1.Text)); } finally { // Clean up sheets GC.Collect(); GC.WaitForPendingFinalizers(); Marshal.FinalReleaseComObject(wellLinesTransect); Marshal.FinalReleaseComObject(templateSheet); Marshal.FinalReleaseComObject(fireBallSheet); Marshal.FinalReleaseComObject(inputSheets); Marshal.FinalReleaseComObject(books); Marshal.FinalReleaseComObject(templateSheets); Marshal.FinalReleaseComObject(dataRange); Marshal.FinalReleaseComObject(holeRange); Marshal.FinalReleaseComObject(fireBallRange); wellLinesTransect = null; templateSheet = null; fireBallSheet = null; inputSheets = null; books = null; templateSheets = null; dataRange = null; holeRange = null; fireBallRange = null; inputFile.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value); templateFile.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value); Marshal.FinalReleaseComObject(inputFile); Marshal.FinalReleaseComObject(templateFile); inputFile = null; templateFile = null; excelApp.Application.Quit(); Marshal.FinalReleaseComObject(excelApp); excelApp = null; notificationPanel.Controls.Find("lblGenerating", true)[0].Hide(); notificationPanel.Controls.Find("lblSuccess", true)[0].Show(); notificationPanel.Controls.Find("btnClose", true)[0].Show(); }; }
public void ExportFileFromDataTable_Dung(DataTable dt, string fileName, string title, string[] prm) { //set properties for SaveFileDilog SaveFileDialog sfdSave = new SaveFileDialog(); sfdSave.Filter = "Excel file(*.xls)|*.xls"; sfdSave.Title = "Save to Excel file"; sfdSave.FileName = fileName; //proccess save file excel if (sfdSave.ShowDialog() == DialogResult.OK) { //create file infomation FileInfo f = new FileInfo(sfdSave.FileName); //delete if file exists if (f.Exists == true) { f.Delete(); } //Create excel file ExcelCOM.Application exApp = new ExcelCOM.Application(); ExcelCOM.Workbook exBook = exApp.Workbooks.Add(ExcelCOM.XlWBATemplate.xlWBATWorksheet); ExcelCOM.Worksheet exSheet = (ExcelCOM.Worksheet)exBook.Worksheets[1]; //Microsoft.Office.Interop.Excel.Range excelCellrange; ExcelCOM.Range excelCellrange; exSheet.Name = fileName; //import Caption excelCellrange = exSheet.Range[exSheet.Cells[1, 1], exSheet.Cells[1, dt.Columns.Count]]; exSheet.get_Range((object)exSheet.Cells[1, 1], (object)exSheet.Cells[1, dt.Columns.Count]).Merge(false); excelCellrange = exSheet.get_Range((object)exSheet.Cells[1, 1], (object)exSheet.Cells[1, dt.Columns.Count]); excelCellrange.FormulaR1C1 = title; excelCellrange.HorizontalAlignment = 3; excelCellrange.VerticalAlignment = 3; excelCellrange = exSheet.Range[exSheet.Cells[1, 1], exSheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]]; excelCellrange.NumberFormat = "@"; //import header int col = 1; for (int i = 1; i <= dt.Columns.Count; i++) { exSheet.Cells[2, col] = dt.Columns[i - 1].Caption; col++; } //import data //int row = 1; for (int i = 0; i < dt.Rows.Count; i++) { //row++; col = 1; for (int j = 0; j < dt.Columns.Count; j++) { if (prm != null) { //exSheet.Cells[i + 3, col].NumberFormat = "@"; exSheet.Cells[i + 3, col] = dt.Rows[i][j].ToString(); } col++; } } // now we resize the columns excelCellrange = exSheet.Range[exSheet.Cells[1, 1], exSheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]]; excelCellrange.EntireColumn.AutoFit(); Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders; border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border.Weight = 2d; //excelCellrange = exSheet.Range[exSheet.Cells[1, 1], exSheet.Cells[1, dt.Columns.Count]]; //FormattingExcelCells(excelCellrange, "#f5f5f5", System.Drawing.Color.DarkBlue, true); //excelCellrange = exSheet.Range[exSheet.Cells[2, 1], exSheet.Cells[2, dt.Columns.Count]]; //FormattingExcelCells(excelCellrange, "#0488a1", System.Drawing.Color.White, true); exApp.Visible = false; // //exBook.CheckCompatibility = false; //Save file excel into dictionary that you choose exBook.SaveAs(sfdSave.FileName, ExcelCOM.XlFileFormat.xlWorkbookNormal, null, null, false, false, ExcelCOM.XlSaveAsAccessMode.xlExclusive, false, false, false, false, false); //close and release object exBook.Close(false, false, false); exApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp); CustomMessageBox.MessageBox.ShowCustomMessageBox(Common.clsLanguages.GetResource("ExcelExport"), Common.clsLanguages.GetResource("Information"), Common.Config.CUSTOM_MESSAGEBOX_ICON.Information, Common.Config.CUSTOM_MESSAGEBOX_BUTTON.OK); } }
private ResxData XlsToDataSet(string xlsFile) { Excel.Application app = new Excel.Application(); Excel.Workbook wb = app.Workbooks.Open(xlsFile, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); Excel.Sheets sheets = wb.Worksheets; Excel.Worksheet sheet = (Excel.Worksheet)sheets.get_Item(1); ResxData rd = new ResxData(); int row = 3; bool continueLoop = true; while (continueLoop) { string fileSrc = (sheet.Cells[row, 1] as Excel.Range).Text.ToString(); if (String.IsNullOrEmpty(fileSrc)) { break; } ResxData.ResxRow r = rd.Resx.NewResxRow(); r.FileSource = (sheet.Cells[row, 1] as Excel.Range).Text.ToString(); r.FileDestination = (sheet.Cells[row, 2] as Excel.Range).Text.ToString(); r.Key = (sheet.Cells[row, 3] as Excel.Range).Text.ToString(); r.Value = (sheet.Cells[row, 4] as Excel.Range).Text.ToString(); rd.Resx.AddResxRow(r); bool hasCulture = true; int col = 5; while (hasCulture) { string cult = (sheet.Cells[2, col] as Excel.Range).Text.ToString(); if (String.IsNullOrEmpty(cult)) { break; } ResxData.ResxLocalizedRow lr = rd.ResxLocalized.NewResxLocalizedRow(); lr.Culture = cult; lr.Key = (sheet.Cells[row, 3] as Excel.Range).Text.ToString(); lr.Value = (sheet.Cells[row, col] as Excel.Range).Text.ToString(); lr.ParentId = r.Id; lr.SetParentRow(r); rd.ResxLocalized.AddResxLocalizedRow(lr); col++; } row++; } rd.AcceptChanges(); wb.Close(false, m_objOpt, m_objOpt); app.Quit(); return(rd); }
/// <summary> /// Export file excel from datagridview /// </summary> /// <param name="dgv"></param> /// <param name="fileName"></param> public void ExportFileFromDataGridView(System.Windows.Forms.DataGridView dgv, string fileName) { //set properties for SaveFileDilog SaveFileDialog sfdSave = new SaveFileDialog(); sfdSave.Filter = "Excel file(*.xls)|*.xls"; sfdSave.Title = "Save to Excel file"; sfdSave.FileName = fileName; //proccess save file excel if (sfdSave.ShowDialog() == DialogResult.OK) { //create file infomation FileInfo f = new FileInfo(sfdSave.FileName); //delete if file exists if (f.Exists == true) { f.Delete(); } //Create excel file ExcelCOM.Application exApp = new ExcelCOM.Application(); ExcelCOM.Workbook exBook = exApp.Workbooks.Add(ExcelCOM.XlWBATemplate.xlWBATWorksheet); ExcelCOM.Worksheet exSheet = (ExcelCOM.Worksheet)exBook.Worksheets[1]; //ExcelCOM.Range chartRange; exSheet.Name = fileName; //get data from datagridview import into excel file //exSheet.Cells.FormatConditions //exSheet.get_Range("b2", "e3").Merge(false); //chartRange = exSheet.get_Range("b2", "e9"); //chartRange.BorderAround(ExcelCOM.XlLineStyle.xlContinuous, ExcelCOM.XlBorderWeight.xlMedium, ExcelCOM.XlColorIndex.xlColorIndexAutomatic, ExcelCOM.XlColorIndex.xlColorIndexAutomatic); //xlWorkBook.SaveAs("csharp.net-informations.xls", ExcelCOM.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, ExcelCOM.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); //import header int col = 1; for (int i = 1; i <= dgv.ColumnCount; i++) { exSheet.Cells[1, col] = dgv.Columns[i - 1].HeaderText; col++; //chartRange = exSheet.Cells[1, col]; //Font.Bold = true; } //import data for (int i = 0; i < dgv.Rows.Count; i++) { col = 1; for (int j = 1; j < dgv.ColumnCount; j++) { exSheet.Cells[i + 2, col + 1] = dgv.Rows[i].Cells[j].Value.ToString(); col++; //chartRange.Cells.Formula } } //(ExcelCOM.XlLineStyle.xlContinuous, ExcelCOM.XlBorderWeight.xlMedium, ExcelCOM.XlColorIndex.xlColorIndexAutomatic, ExcelCOM.XlColorIndex.xlColorIndexAutomatic); //chartRange.BorderAround(ExcelCOM.XlLineStyle.xlContinuous, ExcelCOM.XlBorderWeight.xlMedium, ExcelCOM.XlColorIndex.xlColorIndexAutomatic, ExcelCOM.XlColorIndex.xlColorIndexAutomatic); exApp.Visible = false; //Save file excel into dictionary that you choose exBook.SaveAs(sfdSave.FileName, ExcelCOM.XlFileFormat.xlWorkbookNormal, null, null, false, false, ExcelCOM.XlSaveAsAccessMode.xlExclusive, false, false, false, false, false); //close and release object exBook.Close(false, false, false); exApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp); CustomMessageBox.MessageBox.ShowCustomMessageBox(Common.clsLanguages.GetResource("CRM385"), Common.clsLanguages.GetResource("CRM11"), Common.Config.CUSTOM_MESSAGEBOX_ICON.Information, Common.Config.CUSTOM_MESSAGEBOX_BUTTON.OK); } }
private void button6_Click(object sender, EventArgs e) { int l = 3; int cont = co.Matricula().Rows.Count; int pos = 0; int[] num; num = new int[100000]; progressBar1.Visible = true; progressBar1.Maximum = cont; for (int i = 0; i < cont; i++) { va.Matricula = i; int aux = co.ExportarBrancos(va).Rows.Count; if (aux == 0 && i != 0) { num[pos] = i; pos = pos + 1; } progressBar1.Value++; } progressBar1.Value = 0; salvarArquivo.FileName = "RMs em Branco"; salvarArquivo.DefaultExt = "*.xls"; salvarArquivo.Filter = "Todos os Aquivos do Excel (*.xls)|*.xls| Todos os arquivos (*.*)|*.*"; try { xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 4]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 4]].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; xlWorkSheet.Cells[1, 1] = "Lista de RMs em branco"; xlWorkSheet.Cells[1, 1].ColumnWidth = 10; xlWorkSheet.Cells[1, 1].Font.Size = 16; xlWorkSheet.Cells[2, 1] = "RM"; xlWorkSheet.Cells[2, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; //progressBar1.Visible = true; progressBar1.Maximum = pos; for (int i = 0; i < pos; i++) { xlWorkSheet.Cells[l, 1] = num[i]; xlWorkSheet.Cells[l, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; l = l + 1; progressBar1.Value++; } progressBar1.Value = 0; progressBar1.Visible = false; new System.Threading.Thread(delegate() { Export(); }).Start(); } catch (Exception ex) { string msg = "Erro : " + ex.Message; frmMensagem mg = new frmMensagem(msg); mg.ShowDialog(); } }
void Excel_WorkbookEvent(Xl.Workbook Wb) { InvalidateRibbonUi(); }
protected void Excel_Click(object sender, ImageClickEventArgs e) { List <Data> data = GetData(); System.Reflection.Missing missingValue = System.Reflection.Missing.Value; //создаем и инициализируем объекты Excel Excel.Application App = new Microsoft.Office.Interop.Excel.Application(); //добавляем в файл Excel книгу. Параметр в данной функции - используемый для создания книги шаблон. //если нас устраивает вид по умолчанию, то можно спокойно передавать пустой параметр. Excel.Workbooks xlsWBs = App.Workbooks; Excel.Workbook xlsWB = xlsWBs.Add(missingValue); //и использует из нее Excel.Sheets xlsSheets = xlsWB.Worksheets; Excel.Worksheet xlsSheet = (Excel.Worksheet)xlsSheets.get_Item(1); string[] cols = { "№ события", "Источник", "Диспетчер", "дата/время", "Вид услуги", "Зона", "Событие", "Описание", "Принял", "дата/время", "Исполнил", "Комментарий", "дата/время", "Тайминг" }; for (int i = 0; i < cols.Length; i++) { xlsSheet.Cells[1, i + 1] = cols[i]; } for (int i = 0; i < data.Count; i++) { xlsSheet.Cells[i + 2, 1] = data[i].Id; xlsSheet.Cells[i + 2, 2] = data[i].Sourse; xlsSheet.Cells[i + 2, 3] = data[i].IO; xlsSheet.Cells[i + 2, 4] = data[i].Date1; xlsSheet.Cells[i + 2, 5] = data[i].RegistrId; xlsSheet.Cells[i + 2, 6] = data[i].LiftId; xlsSheet.Cells[i + 2, 7] = data[i].TypeId; xlsSheet.Cells[i + 2, 8] = data[i].EventId; xlsSheet.Cells[i + 2, 9] = data[i].ToApp; xlsSheet.Cells[i + 2, 10] = data[i].DateToApp; xlsSheet.Cells[i + 2, 11] = data[i].Who; xlsSheet.Cells[i + 2, 12] = data[i].Comment; xlsSheet.Cells[i + 2, 13] = data[i].Date2; xlsSheet.Cells[i + 2, 14] = data[i].Timing; } string name = DateTime.Now.ToString("ddMMyyyy-hhmm") + User.Identity.Name + ".xls"; xlsWB.SaveAs(Request.PhysicalApplicationPath + KOS.App_Code.ClearTemp._folder + "\\" + name, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, missingValue, missingValue, missingValue, missingValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missingValue, missingValue, missingValue, missingValue, missingValue); //закрываем книгу xlsWB.Close(false, missingValue, missingValue); xlsWB = null; xlsWBs = null; xlsSheet = null; xlsSheets = null; //закрываем приложение App.Quit(); //уменьшаем счетчики ссылок на COM объекты, что, по идее должно их освободить. // System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsSheet); // System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsSheets); // System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWB); // System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWBs); System.Runtime.InteropServices.Marshal.ReleaseComObject(App); // Download.NavigateUrl = "~/" + KOS.App_Code.ClearTemp._folder + "\\" + name; // Download.Text = "Скачать документ"; // Download.Visible = true; }
private void keyisdown(object sender, KeyEventArgs e) { #region GameKeys if (e.KeyCode == Keys.Left) { goleft = true; } if (e.KeyCode == Keys.Right) { goright = true; } if (e.KeyCode == Keys.Up) { goup = true; } if (e.KeyCode == Keys.Down) { godown = true; } if (e.KeyCode == Keys.Enter) { tbMsg.Enabled = true; tbMsg.Focus(); } #endregion #region DebugKeys if (e.KeyCode == Keys.P) //DEBUG { String x = Microsoft.VisualBasic.Interaction.InputBox("What's the desired port?", "DEBUG PORT", debugPort.ToString()); try { debugPort = Int32.Parse(x); client_port = x; Clients.Remove(debugPort); // Removes ourselves from the list } catch (Exception ex) { MessageBox.Show(ex.Message); } } if (e.KeyCode == Keys.N) { String x = Microsoft.VisualBasic.Interaction.InputBox("What's your client name?", "NAME", "client" + debugPort.ToString()); try { PlayersID.Add(x, 1); } catch (Exception ex) { MessageBox.Show(ex.Message); } } if (e.KeyCode == Keys.S) { InitChannel(client_port); initClientServer(); try { if (TEST_OFFLINE) { throw new Exception(); } string myIp = new WebClient().DownloadString(@"http://icanhazip.com").Trim(); reqObj.Register(PlayersID.FirstOrDefault(x => x.Value == 1).Key, "tcp://" + myIp + ":" + debugPort + "/ClientService"); } catch (Exception excpt) //Pokemon Exception Handling { excpt.GetType(); //ESTA LINHA NAO FAZ NADA reqObj.Register(PlayersID.FirstOrDefault(x => x.Value == 1).Key, "tcp://LOCALHOST:" + debugPort + "/ClientService"); } reqObj.JoinGame(PlayersID.FirstOrDefault(x => x.Value == 1).Key); } if (e.KeyCode == Keys.C) { String x = Microsoft.VisualBasic.Interaction.InputBox("What's the server address?", "NAME", server_host); try { server_host = x; initClient(); } catch (Exception ex) { MessageBox.Show(ex.Message); } } if (e.KeyCode == Keys.C) { //String x = Microsoft.VisualBasic.Interaction.InputBox("What's the server address?", "NAME", server_host MessageBox.Show(pmc.delay.ToString()); } if (e.KeyCode == Keys.D) { String text = ""; foreach (Tuple <string, CliChat> c in cc) { text += c.Item1.ToString() + "\n"; } foreach (KeyValuePair <String, int> c in chatClientsVectorClock) { text += "Chat:" + c.ToString() + "\n"; } text += "-----CLOCK-------\n"; foreach (Tuple <string, Dictionary <string, int> > c in messagesVectorClock) { foreach (KeyValuePair <string, int> d in c.Item2) { text += "VecClock[" + d.Key + "]:" + d.Value.ToString(); } text += "\n"; } MessageBox.Show(text); } if (e.KeyCode == Keys.E) { String text = ""; foreach (Tuple <string, CliChat> c in cc) { text += c.Item1.ToString() + "\n"; } foreach (Control c in GetControlsByPartialName("Ghost")) { text += c.ToString() + "\n"; } MessageBox.Show(text); Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "..\\..\\..\\moves.xlsx")); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; //excel is not zero based!! ArrayList a = new ArrayList(); for (int i = 1; i <= 56; i++) { a.Clear(); for (int j = 1; j <= 2; j++) { //new line if (j == 1) { Console.Write("\r\n"); } //write the value to the console if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null) { a.Add(xlRange.Cells[i, j].Value2); } } traceMoves.Add((int)Math.Floor((double)a[0]), (string)a[1]); } GC.Collect(); GC.WaitForPendingFinalizers(); //release com objects to fully kill excel process from running in the background Marshal.ReleaseComObject(xlRange); Marshal.ReleaseComObject(xlWorksheet); //close and release xlWorkbook.Close(); Marshal.ReleaseComObject(xlWorkbook); //quit and release xlApp.Quit(); Marshal.ReleaseComObject(xlApp); foreach (KeyValuePair <int, string> vp in traceMoves) { Console.Write(vp.ToString()); } } #endregion DEBUG }
public static void CreateOverview(Tournament tournament, DataContext context, Guid roundId) { SqlHelper helper = new SqlHelper(tournament.Database); Round round = helper.GetRound(roundId).Result; List <Debate> debates = helper.GetDebates(roundId).Result; Excel.Application excelApplication = new Excel.Application() { Visible = false }; Excel.Workbook excelWorkbook = excelApplication.Workbooks.Add(); Excel.Sheets excelSheets = excelWorkbook.Worksheets; Excel.Worksheet sheet = (Excel.Worksheet)excelSheets.get_Item("Sheet1"); debates = debates.OrderBy(d => context.Venues.First(v => d.VenueId.Equals(v.VenueId)).Name).ToList(); int column = 1; int letter = 'A'; Excel.Range range = sheet.get_Range("A1"); range.Value2 = "Venue"; foreach (Position p in Enum.GetValues(typeof(Position))) { if (p.Equals(Position.Invalid)) { continue; } range = sheet.get_Range(string.Format("{0}{1}", (char)++letter, column)); range.Value2 = p.ToString(); } range = sheet.get_Range(string.Format("{0}{1}", (char)++letter, column)); range.Value2 = "Adjudicators"; column++; foreach (Debate debate in debates) { letter = 'A'; Venue venue = context.Venues.First(v => v.VenueId.Equals(debate.VenueId)); range.Value2 = string.Format("{0}{1}", venue.SpecialNeedsVenue ? "[SN]" : string.Empty, venue.Name); range = sheet.get_Range(string.Format("{0}{1}", (char)++letter, column)); List <SpeakerDraw> speakerDraws = helper.GetSpeakerDrawsByDebate(debate.DebateId).Result; foreach (SpeakerDraw speakerDraw in speakerDraws.OrderBy(sd => sd.Position)) { range = sheet.get_Range(string.Format("{0}{1}", (char)++letter, column)); range.Value2 = context.Speakers.First(s => speakerDraw.SpeakerId.Equals(s.SpeakerId)).Name; } List <JudgeDraw> judgeDraws = helper.GetJudgeDrawsByDebate(debate.DebateId).Result.OrderBy(j => j.Number).ToList(); List <Guid> judgeIds = judgeDraws.Select(j => j.JudgeId).ToList(); List <Judge> judges = context.Judges.Where(j => judgeIds.Contains(j.JudgeId)).ToList(); List <string> names = judges.Select(j => j.Name).ToList(); range = sheet.get_Range(string.Format("{0}{1}", (char)++letter, column)); range.Value2 = string.Join(", ", names); column++; } string directory = Path.Combine(Path.GetDirectoryName(tournament.Location), string.Format("Round {0}", round.RoundNumber)); if (!Directory.Exists(directory)) { Directory.CreateDirectory(directory); } string filename = Path.Combine(directory, string.Format("Round {0} - overview.xlsx", round.RoundNumber)); string endCell = string.Format("{0}{1}", (char)(letter), column - 1); range = sheet.get_Range("A1", endCell); range.Columns.AutoFit(); excelApplication.DisplayAlerts = false; excelWorkbook.SaveAs(filename); excelWorkbook.Close(); excelApplication.DisplayAlerts = true; excelApplication.Quit(); }
/// <summary>Función para Insertar el Indice. /// <para>Inserta el Indice en el archivo de Excel. Referencia: <see cref="InsertIndice(Excel.Worksheet, int, Excel.Range, bool, int)"/> se agrega la referencia ExcelAddIn.Generales para invocarla.</para> /// <seealso cref="InsertIndice(Excel.Worksheet, int, Excel.Range, bool, int)"/> /// </summary> public static void InsertIndice(Excel.Worksheet xlSht, int CantReg, Excel.Range currentCell, bool ConFormula, int NroPrincipal) { Worksheet sheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet); Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook; string _NameFile = wb.Name; string _AnexoFile = sheet.Name; FileInfo _Excel = new FileInfo(Globals.ThisAddIn.Application.ActiveWorkbook.FullName); Excel.Range currentFind = null; Excel.Range currentFindExpl = null; Excel.Range RangeLocked = null; int NroRow = currentCell.Row; int NroColumn = currentCell.Column; string IndicePrevio = ""; long IndiceInicial = 0; int iTotalColumns = 0; int k = 1; int i = 1; long indiceNvo = 0; int CantExpl = 0; currentCell = (Excel.Range)xlSht.Cells[NroRow, 1]; IndicePrevio = currentCell.get_Value(Type.Missing).ToString(); currentFindExpl = (Excel.Range)xlSht.Cells[NroRow + 1, 1]; if (currentFindExpl.get_Value(Type.Missing) != null) { if (currentFindExpl.get_Value(Type.Missing).ToString().ToUpper().Trim() == "EXPLICACION") { NroRow++; } } IndiceInicial = Convert.ToInt64(IndicePrevio) + 100; int rowexpl = 0; List <int> FilasExplicacion = new List <int>(); int CantRango = 0; long IndiceInicialx = IndiceInicial; foreach (Excel.Name cname in Globals.ThisAddIn.Application.Names) { if (cname.Name == "IA_0" + Convert.ToString(IndiceInicialx)) { CantRango++; IndiceInicialx = IndiceInicialx + 100; rowexpl = cname.RefersToRange.Cells.Row + 1; currentFindExpl = (Excel.Range)xlSht.Cells[rowexpl, 1]; if (currentFindExpl.get_Value(Type.Missing) != null) { if (currentFindExpl.get_Value(Type.Missing).ToString().ToUpper().Trim() == "EXPLICACION") { CantExpl++; if (!FilasExplicacion.Contains(rowexpl + CantReg))//los indices que tienen explicacion la fila actual + los registros que ingresó nvos { FilasExplicacion.Add(rowexpl + CantReg); } } } } } currentFind = currentCell.Find(IndiceInicial, Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing); int NroRowx = 0; CantRango = CantRango + CantExpl; int NroPrincipalAux = DameRangoPrincipal(NroPrincipal, xlSht); while (i <= CantReg) { indiceNvo = Convert.ToInt64(IndicePrevio) + 100; Excel.Range rangej = xlSht.get_Range(string.Format("{0}:{0}", NroRow + i, Type.Missing)); rangej.Select(); rangej.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove); var rangeall = xlSht.get_Range(string.Format("{0}:{0}", NroPrincipalAux - 1, Type.Missing)); var rangeaCopy = xlSht.get_Range(string.Format("{0}:{0}", NroRow + i, Type.Missing)); iTotalColumns = xlSht.UsedRange.Columns.Count; rangeall.Copy(); rangeaCopy.PasteSpecial(Excel.XlPasteType.xlPasteFormulas, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false); rangeaCopy.Locked = false; k = 1; while (k <= iTotalColumns) { if (!(rangeaCopy.Cells[k].HasFormula)) { rangeaCopy.Cells[k].Value = ""; } k = k + 1; } xlSht.Cells[NroRow + i, 1] = "0" + Convert.ToString(indiceNvo); sheet.Controls.Remove("IA_0" + indiceNvo); AddNamedRange(NroRow + i, 1, "IA_0" + Convert.ToString(indiceNvo)); currentCell = (Excel.Range)xlSht.Cells[NroRow + i, 1]; IndicePrevio = currentCell.get_Value(Type.Missing).ToString(); //currentCell = xlSht.Range[xlSht.Cells[NroRow + i, 1], xlSht.Cells[NroRow + i, 3]]; //currentCell.Font.Color = System.Drawing.Color.FromArgb(0, 0, 255); ((Excel.Range)xlSht.Cells[NroRow + 1, 2]).NumberFormat = "General"; // le doy formato text al concepto ((Excel.Range)xlSht.Cells[NroRow + 1, 2]).WrapText = true; RangeLocked = (Excel.Range)xlSht.Cells[NroRow + i, 1]; RangeLocked.Locked = true; // con esto bloqueo solo la primera columna i++; } if (currentFind != null) { NroRowx = NroRow + CantReg; currentCell = (Excel.Range)xlSht.Cells[NroRowx, 1]; IndicePrevio = currentCell.get_Value(Type.Missing).ToString(); int j = 1; while (j <= CantRango) { if (!FilasExplicacion.Contains(NroRowx + j)) { indiceNvo = Convert.ToInt64(IndicePrevio) + 100; xlSht.Cells[NroRowx + j, 1] = "0" + Convert.ToString(indiceNvo); sheet.Controls.Remove("IA_0" + indiceNvo); AddNamedRange(NroRowx + j, 1, "IA_0" + Convert.ToString(indiceNvo)); currentCell = (Excel.Range)xlSht.Cells[NroRowx + j, 1]; IndicePrevio = currentCell.get_Value(Type.Missing).ToString(); } j++; } } string NombreHoja = xlSht.Name.ToUpper().Replace(" ", ""); List <oSubtotal> ColumnasST = DameColumnasST((NombreHoja)); Excel.Range Sum_Range = null; int NroFinal = NroRow + CantReg + CantRango; int _Rango = 0; string _Renglon; string _Columna; string _rCelda = ""; foreach (oSubtotal ST in ColumnasST) { Sum_Range = xlSht.get_Range(ST.Columna + (NroPrincipalAux).ToString(), ST.Columna + (NroPrincipalAux).ToString()); Sum_Range.Formula = "=sum(" + ST.Columna + (NroPrincipalAux + 1).ToString() + ":" + ST.Columna + (NroFinal).ToString() + ")"; _Rango = NroFinal - NroPrincipalAux; // nroprincipalaux siempre tiene el numero de la fila padre al restar te da la cantidad de rows insertadas _Renglon = (Sum_Range.Row).ToString(); _Columna = Generales.ColumnAdress(Sum_Range.Column); _rCelda = _Columna + "" + _Renglon; InsertarReferencia(_NameFile, _AnexoFile, _rCelda, _Rango, _Columna, _Renglon, CantReg); } Sum_Range = xlSht.get_Range("B" + (NroPrincipal).ToString(), "B" + (NroPrincipal).ToString()); Sum_Range.Select(); }
/// <summary> /// This is the method that actually does the work. /// </summary> /// <param name="DA">The DA object can be used to retrieve data from input parameters and /// to store data in output parameters.</param> protected override void SolveInstance(IGH_DataAccess DA) { string defaultDir = "C:\\\\ant\\\\init\\\\ExcelDataset\\\\"; process2.StartInfo.FileName = "python.exe"; process2.StartInfo.Arguments = "doAllWork.py"; process2.StartInfo.WorkingDirectory = @workingDir; process2.EnableRaisingEvents = true; process2.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden; string workingDir2 = workingDir.Replace("\\", "\\\\"); bool openfile = false; if (!DA.GetData(0, ref openfile)) { return; } if (!openfile) { return; } openFileDialog1.InitialDirectory = "c:\\"; openFileDialog1.Filter = "Excel File (*.xlsx)|*.xlsx"; openFileDialog1.FilterIndex = 2; openFileDialog1.RestoreDirectory = true; if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK) { string data = "["; string target = "["; string features = "["; int rCnt; int cCnt; int rw = 0; int cl = 0; string thisfile = openFileDialog1.FileName; xlWorkBook = xlApp.Workbooks.Open(@thisfile, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); range = xlWorkSheet.UsedRange; rw = range.Rows.Count; cl = range.Columns.Count; string strText = "N. of Features : " + (cl - 1) + "\t N. of Samples: " + (rw - 1) + "\t \n ------------------------------ \n--- FEATURES : \n "; //MessageBox.Show(rw.ToString() + " rows , " + cl.ToString() + " Cols"); string newstr = "["; for (cCnt = 1; cCnt <= cl - 1; cCnt++) { if (cCnt > 1) { newstr += ", "; features += ", "; } newstr += "\""; newstr += range.Cells[1, cCnt].Value2.ToString(); newstr += "\""; features += "\""; features += range.Cells[1, cCnt].Value2.ToString(); features += "\""; } features += "]"; newstr += "]\n ------------------------------ \n--- DATA : \n "; for (rCnt = 2; rCnt <= rw; rCnt++) { if (rCnt > 2) { data += ","; } data += "["; newstr += "\n " + (rCnt - 1).ToString() + "- "; newstr += "["; for (cCnt = 1; cCnt <= cl - 1; cCnt++) { if (cCnt > 1) { data += ","; newstr += ", "; } newstr += range.Cells[rCnt, cCnt].Value2.ToString(); data += range.Cells[rCnt, cCnt].Value2.ToString(); } newstr += "]"; data += "]"; } data += "]"; newstr += "]\n------------------------------ \n--- TARGETS : \n "; for (rCnt = 2; rCnt <= rw; rCnt++) { if (rCnt > 2) { target += ","; } newstr += "["; newstr += range.Cells[rCnt, cl].Value2.ToString(); target += range.Cells[rCnt, cl].Value2.ToString(); newstr += "] "; } target += "]"; PythonFile(@data, @target, @features, workingDir); xlWorkBook.Close(true, null, null); xlApp.Quit(); Marshal.ReleaseComObject(xlWorkSheet); Marshal.ReleaseComObject(xlWorkBook); Marshal.ReleaseComObject(xlApp); process2.Start(); DA.SetData(0, strText + newstr); DA.SetData(1, @workingDir2 + "data.txt"); DA.SetData(2, @workingDir2 + "targets.txt"); } }
public static void ActualizarReferencia(string _Archivo, string _Anexo, string _Celda, int _Cantidad, string _Column, string _Row, int CantEliminar, string Accion) { string _Path = ExcelAddIn.Access.Configuration.Path; if (File.Exists(_Path + "\\references\\" + _Archivo + ".json")) { string _jCadena = ""; StreamReader _fJason = new StreamReader(_Path + "\\references\\" + _Archivo + ".json"); while (_fJason.Peek() >= 0) { var _json = _fJason.ReadLine(); oIndices _Indices = JsonConvert.DeserializeObject <oIndices>(_json); if (_Indices.Anexo + _Indices.Column + _Indices.Row == _Anexo + _Column + _Row) { if (Accion == "E") {//Eliminar _Indices.Cantidad -= CantEliminar; } else if (Accion == "A") {//Agregar _Indices.Cantidad += 1; } _json = JsonConvert.SerializeObject(_Indices); } if (_Indices.Column != _Column) { if (_Indices.Row == _Row) { if (Accion == "E") {//Eliminar _Indices.Cantidad -= CantEliminar; } else if (Accion == "A") {//Agregar _Indices.Cantidad += 1; } _json = JsonConvert.SerializeObject(_Indices); } } if (_Indices.Anexo + _Indices.Row != _Anexo + _Row) { if (Convert.ToInt32(_Row) < Convert.ToInt32(_Indices.Row)) { if (Accion == "E") {//Eliminar _Indices.Row = (Convert.ToInt32(_Indices.Row) - CantEliminar).ToString(); } else if (Accion == "A") {//Agregar _Indices.Row = (Convert.ToInt32(_Indices.Row) + 1).ToString(); } _json = JsonConvert.SerializeObject(_Indices); } } _jCadena += _json; _jCadena += string.Format(Environment.NewLine); } _fJason.Close(); File.WriteAllText(_Path + "\\references\\" + _Archivo + ".json", _jCadena); Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook; wb.Save(); } }
public static void InsertarReferencia(string _Archivo, string _Anexo, string _Celda, int _Cantidad, string _Column, string _Row, int _Posicion) { string _Path = ExcelAddIn.Access.Configuration.Path; var Indices = new oIndices { Archivo = _Archivo, Anexo = _Anexo, Celda = _Celda, Cantidad = _Cantidad, Column = _Column, Row = _Row }; string jReference = JsonConvert.SerializeObject(Indices); if (Directory.Exists(_Path + "\\references")) { if (!File.Exists(_Path + "\\references\\" + _Archivo + ".json")) { File.WriteAllText(_Path + "\\references\\" + _Archivo + ".json", jReference); } else { string _jCadena = ""; StreamReader _fJason = new StreamReader(_Path + "\\references\\" + _Archivo + ".json"); while (_fJason.Peek() >= 0) { var _json = _fJason.ReadLine(); oIndices _Indices = JsonConvert.DeserializeObject <oIndices>(_json); //if(Convert.ToInt32(_Row) < Convert.ToInt32(_Indices.Row)) //{ // Match m = Regex.Match(_Indices.Celda, "(\\d+)"); // string _num = "0"; // if (m.Success){_num = m.Value;} // _Indices.Row = (Convert.ToInt32(_Indices.Row) + _Posicion).ToString(); // _json = JsonConvert.SerializeObject(_Indices); //} if (_Indices.Column + _Indices.Row != _Column + _Row) { _jCadena += _json; _jCadena += string.Format(Environment.NewLine); } } _fJason.Close(); //_jCadena += string.Format(Environment.NewLine); _jCadena += jReference; File.WriteAllText(_Path + "\\references\\" + _Archivo + ".json", _jCadena); } } else { Directory.CreateDirectory(_Path + "\\references"); if (!File.Exists(_Path + "\\references\\" + _Archivo + ".json")) { File.WriteAllText(_Path + "\\references\\" + _Archivo + ".json", jReference); } else { string _jCadena = ""; StreamReader _fJason = new StreamReader(_Path + "\\references\\" + _Archivo + ".json"); while (_fJason.Peek() >= 0) { _jCadena = _jCadena + _fJason.ReadLine(); } _fJason.Close(); _jCadena = _jCadena + jReference; File.WriteAllText(_Path + "\\references\\" + _Archivo + ".json", _jCadena); } } Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook; wb.Save(); }
/// <summary> /// 从table导出数据到Excel /// </summary> /// <param name="dt">要导出数据的table</param> /// <param name="name">表格名称</param> /// <param name="title">标题</param> /// <param name="staDate">统计起始日期</param> /// <param name="endDate">统计终止日期</param> /// <param name="staColumns">需要统计数据的列</param> /// <param name="pointTxt">指定为文本的列</param> /// <param name="countCoumn">合计行数列</param> /// <param name="formula">自定义公式("F,实载率,C{0}/D{1}|G,总班次,C{0}+E{1}")</param> /// <param name="remark">表格底部 要添加的备注</param> public static void Export(DataTable dt, string bookName, string title, string staDate, string endDate, string staColumns, string pointTxt, int countCoumn, string formula, string remark) { try { if (dt.Rows.Count < 1) { return; } Excel.Application books = new Excel.Application(); Excel.Workbook mybook = (Excel.Workbook)books.Workbooks.Add(1); ((Excel.Worksheet)mybook.Worksheets[1]).Name = bookName; Excel.Worksheet mysheet = (Excel.Worksheet)mybook.Worksheets[1]; //第一行 标题 mysheet.Cells[1, 1] = title; if (staDate.Length > 0 || endDate.Length > 0) { //第二行 统计日期 mysheet.Cells[2, 1] = "统计日期:";//+staDate+" - "+endDate; mysheet.Cells[2, 2] = staDate; mysheet.Cells[2, 3] = endDate; //mysheet.Cells[2, 10] = "检验员:"; } //填标题 int columns = 0; //列数 for (int j = 0, jj = 0; j < dt.Columns.Count; j++) //j 与jj不一定相等 只有 所有列的Visible属性全部都为true时才相等 { mysheet.Cells[3, jj + 1] = dt.Columns[j].ColumnName.ToString(); jj++; columns = jj; } if (pointTxt.Length > 0) { string[] points = pointTxt.Split(','); foreach (string str in points) { int i = int.Parse(str); int ch = 'A' + i - 1; char c = (Char)ch; mysheet.get_Range(c.ToString() + 4.ToString(), c.ToString() + (4 + dt.Rows.Count).ToString()).NumberFormatLocal = "@"; } } int rows = dt.Rows.Count;//行数 //填 数据 for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0, jj = 0; j < dt.Columns.Count; j++) { string tmp = dt.Rows[i][j].ToString(); mysheet.Cells[i + 4, jj + 1] = tmp; jj++; } } //添加 合计行 if (rows > 0) { mysheet.Cells[rows + 4, 1] = "合计:"; if (countCoumn == 1) { mysheet.Cells[rows + 4, 1] = "合计:" + rows.ToString(); } if (countCoumn > 1) { mysheet.Cells[rows + 4, countCoumn] = rows.ToString(); } string[] cloumns = staColumns.Split(','); try { foreach (string str in cloumns) { int i = int.Parse(str); int ch = 'A' + i - 1; char c = (Char)ch; mysheet.Cells[rows + 4, i] = "=SUM(" + c.ToString() + "4:" + c.ToString() + (rows + 3).ToString() + ")"; } } catch { } } //添加备注 if (remark != "") { mysheet.Cells[rows + 5, 1] = remark; mysheet.get_Range(mysheet.Cells[rows + 5, 1], mysheet.Cells[rows + 5, columns]).Select(); mysheet.get_Range(mysheet.Cells[rows + 5, 1], mysheet.Cells[rows + 5, columns]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; } //自定义公式 格式"F,实载率,C{0}/D{1}|G,总班次,C{0}+E{1}" if (formula.Length > 0) { string[] formulas = formula.Split('|'); try { foreach (string str in formulas) { string[] param = str.Split(','); int i = char.Parse(param[0]); if (param[1].Length > 0) { mysheet.Cells[3, i - 64] = param[1]; } mysheet.Cells[rows + 4, i - 64] = "=" + string.Format(param[2], rows + 4, rows + 4); } } catch { } } //设置格式 try { //设置 所有文字大小为9 mysheet.get_Range(mysheet.Cells[1, 1], mysheet.Cells[rows + 5, columns]).Font.Size = 12; } catch { } try { //设置 表格为最适应宽度 mysheet.get_Range(mysheet.Cells[2, 1], mysheet.Cells[rows + 4, columns]).Select(); mysheet.get_Range(mysheet.Cells[2, 1], mysheet.Cells[rows + 4, columns]).Columns.AutoFit(); } catch { } try { //设置标题跨列居中 mysheet.get_Range(mysheet.Cells[1, 1], mysheet.Cells[1, columns]).Select(); mysheet.get_Range(mysheet.Cells[1, 1], mysheet.Cells[1, columns]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; } catch { } try { //加边框 //内部为虚线 mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders.LineStyle = Excel.XlLineStyle.xlDash; //设置虚线 mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders.Weight = Excel.XlBorderWeight.xlHairline; //设为极细 //设置左边线 mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlMedium; //设置上边线 mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlMedium; //设置右边线 mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlMedium; //设置下边线 mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlMedium; } catch { } try { //设置 纸张为A4 纵向 mysheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; mysheet.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait; } catch { } books.Visible = true; //mybook.SaveCopyAs("行包受理员营收汇总.xls");//.Save(); mybook = null; System.Runtime.InteropServices.Marshal.ReleaseComObject(books); books = null; } catch (Exception ex) { MessageBox.Show("导出Excel表出错,可能您的机器未安装Excel!系统报错:" + ex.Message, "出错提示", MessageBoxButtons.OK, MessageBoxIcon.Question); if (DialogResult.Yes == MessageBox.Show("您是否想保存Excel?", "保存提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question)) { //ExcelSaver.SaveExcel(dt, bookName, title, staDate, endDate, staColumns, pointTxt, countCoumn, remark); } } finally { GC.Collect(); } }
private void btnExcelReport_Click(object sender, EventArgs e) { if (!reportIsReady) { Control.Exclamation("Запрос не выполнен. Чтобы сгенерировать отчет, необходимо выполнить запрос.", "Ошибка при создании отчета"); return; } Excel.Application excel = new Excel.Application(); if (excel == null) { Control.Exclamation("На вашем компьютере отсутствует программа Excel.", "Ошибка при открытии Excel"); return; } excel.SheetsInNewWorkbook = 1; excel.Workbooks.Add(Type.Missing); Excel.Workbook excelWorkBook = excel.Workbooks[1]; Excel.Worksheet excelWorkSheet = excelWorkBook.Worksheets[1]; for (int i = 0; i < 5; i++) { excel.Columns[i + 1].ColumnWidth = 20; } Excel.Range excelCells = excelWorkSheet.get_Range("A1", "E1"); excelCells.Merge(); excelCells.HorizontalAlignment = Excel.Constants.xlCenter; excel.Rows[1].Cells[1].Value = "Запрос"; excel.Rows[2].Cells[1].Value = "Сущность"; excel.Rows[2].Cells[2].Value = "Атрибут"; excel.Rows[2].Cells[3].Value = "Условие"; excel.Rows[2].Cells[4].Value = "Значение"; excel.Rows[2].Cells[5].Value = "Связка"; // Записываем запросы for (int i = 0; i < dgvQueries.Rows.Count; i++) { for (int j = 0; j < dgvQueries.Columns.Count; j++) { excel.Rows[i + 3].Cells[j + 1].Value = dgvQueries.Rows[i].Cells[j].Value.ToString(); } } if (cbEntities.Text == "Пользователи") { excelCells = excelWorkSheet.get_Range((char)65 + (5 + dgvQueries.Rows.Count).ToString(), (char)(65 + 1) + (5 + dgvQueries.Rows.Count).ToString()); } else { excelCells = excelWorkSheet.get_Range((char)65 + (5 + dgvQueries.Rows.Count).ToString(), (char)(65 + 4) + (5 + dgvQueries.Rows.Count).ToString()); } excelCells.HorizontalAlignment = Excel.Constants.xlCenter; excelCells.Merge(); int shift = dgvQueries.Rows.Count; excel.Rows[5 + shift].Cells[1].Value = "Результаты поиска"; if (cbEntities.Text == "Пользователи") { excel.Rows[6 + shift].Cells[1].Value = "ID"; excel.Rows[6 + shift].Cells[2].Value = "Имя пользователя"; } else { excel.Rows[6 + shift].Cells[1].Value = "ID"; excel.Rows[6 + shift].Cells[2].Value = "Название"; excel.Rows[6 + shift].Cells[3].Value = "Оисание"; excel.Rows[6 + shift].Cells[4].Value = "Дата создания"; excel.Rows[6 + shift].Cells[5].Value = "Средняя оценка"; } // Записываем результаты запроса for (int i = 0; i < dgvResult.Rows.Count; i++) { for (int j = 0; j < dgvResult.Columns.Count; j++) { object result = dgvResult.Rows[i].Cells[j].Value; if (result is DateTime) { excel.Rows[i + 7 + shift].Cells[j + 1].Value = DateTime.Parse(dgvResult.Rows[i].Cells[j].Value.ToString()).Date; } else { excel.Rows[i + 7 + shift].Cells[j + 1].Value = dgvResult.Rows[i].Cells[j].Value == null ? "" : dgvResult.Rows[i].Cells[j].Value.ToString(); } } } excel.Visible = true; }
/// <summary> /// 新控件gridview导出数据到Excel /// </summary> /// <param name="grid">要导出数据的datagridview</param> /// <param name="name">表格名称</param> /// <param name="title">标题</param> /// <param name="staDate">统计起始日期</param> /// <param name="endDate">统计终止日期</param> /// <param name="staColumns">需要统计数据的列</param> /// <param name="pointTxt">指定为文本的列</param> /// <param name="countCoumn">合计行数列</param> /// <param name="formula">自定义公式("F,实载率,C{0}/D{1}|G,总班次,C{0}+E{1}")</param> /// <param name="remark">表格底部 要添加的备注</param> public static void Export(DevExpress.XtraGrid.GridControl grid, string name, string title, string staDate, string endDate, string staColumns, string pointTxt, int countCoumn, string formula, string remark) { try { DevExpress.XtraGrid.Views.Grid.GridView dgv = (DevExpress.XtraGrid.Views.Grid.GridView)grid.MainView; if (dgv.RowCount < 1) { return; } Excel.Application books = new Microsoft.Office.Interop.Excel.Application(); Excel.Workbook mybook = (Excel.Workbook)books.Workbooks.Add(1); ((Excel.Worksheet)mybook.Worksheets[1]).Name = name; Excel.Worksheet mysheet = (Excel.Worksheet)mybook.Worksheets[1]; Excel.Range range; //第一行 标题 mysheet.Cells[1, 1] = title; if (staDate.Length > 0 || endDate.Length > 0) { //第二行 统计日期 mysheet.Cells[2, 1] = "统计日期:";//+staDate+" - "+endDate; mysheet.Cells[2, 2] = staDate; mysheet.Cells[2, 3] = endDate; //mysheet.Cells[2, 10] = "检验员:"; } //填标题 int columns = 0; //列数 for (int j = 0, jj = 0; j < dgv.Columns.Count; j++) //j 与jj不一定相等 只有 所有列的Visible属性全部都为true时才相等 { if (dgv.Columns[j].Visible == true) { mysheet.Cells[3, jj + 1] = dgv.Columns[j].Caption.ToString(); jj++; columns = jj; } } if (pointTxt.Length > 0) { string[] points = pointTxt.Split(','); foreach (string str in points) { int i = int.Parse(str); int ch = 'A' + i - 1; char c = (Char)ch; mysheet.get_Range(c.ToString() + 4.ToString(), c.ToString() + (4 + dgv.RowCount).ToString()).NumberFormatLocal = "@"; } } int rows = dgv.RowCount;//行数 //填 数据 for (int i = 0; i < dgv.RowCount; i++) { for (int j = 0, jj = 0; j < dgv.Columns.Count; j++) { if (dgv.Columns[j].Visible == true) { string columnName = dgv.Columns[j].FieldName; DataRow row = dgv.GetDataRow(i); if (row[columnName] != null) { switch (row[columnName].GetType().ToString()) { case "System.String": //字符串类型 break; case "System.DateTime": //日期类型 System.DateTime dateV; break; case "System.Boolean": //布尔型 bool boolV = false; break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; break; case "System.Decimal": //浮点型 case "System.Double": range = (Excel.Range)mysheet.Cells[i + 4, jj + 1]; range.NumberFormatLocal = "0"; break; case "System.DBNull": //空值处理 break; default: break; } string tmp = row[columnName].ToString(); tmp = tmp.ToLower() == "true" ? "√" : tmp; tmp = tmp.ToLower() == "false" ? "" : tmp; mysheet.Cells[i + 4, jj + 1] = tmp; } else { mysheet.Cells[i + 4, jj + 1] = ""; } jj++; } } } //添加 合计行 if (rows > 0) { mysheet.Cells[rows + 4, 1] = "合计:"; if (countCoumn == 1) { mysheet.Cells[rows + 4, 1] = "合计:" + rows.ToString(); } if (countCoumn > 1) { mysheet.Cells[rows + 4, countCoumn] = rows.ToString(); } string[] cloumns = staColumns.Split(','); try { foreach (string str in cloumns) { int i = int.Parse(str); int ch = 'A' + i - 1; char c = (Char)ch; mysheet.Cells[rows + 4, i] = "=SUM(" + c.ToString() + "4:" + c.ToString() + (rows + 3).ToString() + ")"; } } catch { } } //添加备注 if (remark != "") { mysheet.Cells[rows + 5, 1] = remark; mysheet.get_Range(mysheet.Cells[rows + 5, 1], mysheet.Cells[rows + 5, columns]).Select(); mysheet.get_Range(mysheet.Cells[rows + 5, 1], mysheet.Cells[rows + 5, columns]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; } //自定义公式 格式"F,实载率,C{0}/D{1}|G,总班次,C{0}+E{1}" if (formula.Length > 0) { string[] formulas = formula.Split('|'); try { foreach (string str in formulas) { string[] param = str.Split(','); int i = char.Parse(param[0]); mysheet.Cells[3, i - 64] = param[1]; mysheet.Cells[rows + 4, i - 64] = "=" + string.Format(param[2], rows + 4, rows + 4); } } catch { } } //设置格式 //设置 所有文字大小为9 try { mysheet.get_Range(mysheet.Cells[1, 1], mysheet.Cells[rows + 5, columns]).Font.Size = 9; } catch { } try { //设置 表格为最适应宽度 mysheet.get_Range(mysheet.Cells[2, 1], mysheet.Cells[rows + 4, columns]).Select(); mysheet.get_Range(mysheet.Cells[2, 1], mysheet.Cells[rows + 4, columns]).Columns.AutoFit(); } catch { } try { //设置标题跨列居中 mysheet.get_Range(mysheet.Cells[1, 1], mysheet.Cells[1, columns]).Select(); mysheet.get_Range(mysheet.Cells[1, 1], mysheet.Cells[1, columns]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; } catch { } try { //加边框 //内部为虚线 mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders.LineStyle = Excel.XlLineStyle.xlDash; //设置虚线 mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders.Weight = Excel.XlBorderWeight.xlHairline; //设为极细 //设置左边线 mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlMedium; //设置上边线 mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlMedium; //设置右边线 mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlMedium; //设置下边线 mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlMedium; } catch { } try { //设置 纸张为A4 纵向 mysheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; mysheet.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait; } catch { } books.Visible = true; //mybook.SaveCopyAs("行包受理员营收汇总.xls");//.Save(); mybook = null; System.Runtime.InteropServices.Marshal.ReleaseComObject(books); books = null; } catch (Exception ex) { MessageBox.Show("导出Excel表出错,可能您的机器未安装Excel!系统报错:" + ex.Message, "出错提示", MessageBoxButtons.OK, MessageBoxIcon.Question); if (DialogResult.Yes == MessageBox.Show("您是否想保存Excel?", "保存提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question)) { ExcelSaver.SaveExcel(grid, name, title, staDate, endDate, staColumns, pointTxt, countCoumn, remark); } } finally { GC.Collect(); } }
/// <summary> /// gridview导出到模板 /// </summary> /// <param name="grid">数据源</param> /// <param name="templateURL">模板路径</param> /// <param name="staDate">起始日期</param> /// <param name="endDate">结束日期</param> /// <param name="insertPoint">第几行开始插入</param> /// <param name="remark">表格底部 要添加的备注</param> public static void Export(DataGridView grid, string templateURL, string staDate, string endDate, int insertPoint, string remark) { try { if (grid.Rows.Count < 1) { return; } Object missing = System.Reflection.Missing.Value; Excel.Application books = new Microsoft.Office.Interop.Excel.Application(); //加载模板 Excel.Workbook mybook = books.Workbooks.Open(templateURL, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);//.Add(missing); //获取第一个工作薄 Excel.Worksheet mysheet = (Excel.Worksheet)mybook.Worksheets[1]; //第一行 标题 //mysheet.Cells[1, 1] = title; if (staDate.Length > 0 || endDate.Length > 0) { //第二行 统计日期 mysheet.Cells[2, 1] = "统计日期:";//+staDate+" - "+endDate; mysheet.Cells[2, 2] = staDate; mysheet.Cells[2, 3] = endDate; //mysheet.Cells[2, 10] = "检验员:"; } int columns = grid.Columns.Count; //列数 int rows = grid.Rows.Count; //行数 //填 数据 for (int i = 0; i < grid.Rows.Count; i++) { mysheet.get_Range(mysheet.Cells[i + insertPoint, 1], mysheet.Cells[i + insertPoint, 1]).EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, missing); for (int j = 0, jj = 0; j < grid.Columns.Count; j++) { if (grid.Columns[j].Visible == true) { if (grid.Rows[i].Cells[j].Value != null) { string tmp = grid.Rows[i].Cells[j].Value.ToString(); tmp = tmp.ToLower() == "true" ? "√" : tmp; tmp = tmp.ToLower() == "false" ? "" : tmp; mysheet.Cells[i + insertPoint, jj + 1] = tmp; } else { mysheet.Cells[i + insertPoint, jj + 1] = ""; } jj++; } } } //删除模板空余行 mysheet.get_Range(mysheet.Cells[rows + insertPoint, 1], mysheet.Cells[rows + insertPoint, 1]).EntireRow.Delete(); //添加 合计行 mysheet.Cells[rows + insertPoint, 1] = "合计:" + rows.ToString(); //添加备注 if (remark != "") { mysheet.Cells[rows + insertPoint + 1, 1] = remark; mysheet.get_Range(mysheet.Cells[rows + insertPoint + 1, 1], mysheet.Cells[rows + insertPoint + 1, columns]).Select(); mysheet.get_Range(mysheet.Cells[rows + insertPoint + 1, 1], mysheet.Cells[rows + insertPoint + 1, columns]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; } //保存 SaveFileDialog saveFileDialog1 = new SaveFileDialog(); saveFileDialog1.Filter = "excel2007|*.xlsx"; saveFileDialog1.FileName = templateURL.Substring(templateURL.LastIndexOf('\\') + 1); saveFileDialog1.DefaultExt = ".xlsx"; string fileName = ""; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { fileName = saveFileDialog1.FileName; mybook.SaveAs(fileName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing); } //按顺序释放资源。 NAR(mysheet); NAR(mybook); books.Quit(); NAR(books); } catch (Exception ex) { MessageBox.Show("导出Excel表出错,可能您的机器未安装Excel!系统报错:" + ex.Message, "出错提示", MessageBoxButtons.OK, MessageBoxIcon.Question); //if (DialogResult.Yes == MessageBox.Show("您是否想保存Excel?", "保存提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question)) //{ // ExcelSaver.SaveExcel(dt, bookName, title, staDate, endDate, staColumns, pointTxt, countCoumn, remark); //} } finally { GC.Collect(); } }
/// <summary> /// EnterNameフォームのコンストラクタです。 /// 引数として渡された、構成員名簿データと操作中のアプリケーションを自身のメンバに複製します。 /// </summary> /// <param name="namelist">構成員名簿データ</param> /// <param name="app">操作中のExcelアプリケーション</param> public EnterName(string[,] namelist, Excel.Workbook book) { InitializeComponent(); this.namelist = namelist; this.book = book; }
private void Form1_Load(object sender, EventArgs e) { app = new Excel.Application(); app.Visible = false; workbook = app.Workbooks.Open(Application.StartupPath + "\\Дни рождения сотрудников.xls"); worksheet = (Excel.Worksheet) workbook.ActiveSheet; int i = 0; names = worksheet.get_Range("A1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count)); departaments1 = worksheet.get_Range("B1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count)); departaments2 = worksheet.get_Range("C1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count)); birthdays = worksheet.get_Range("D1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count)); string current_date = DateTime.Now.ToString("dd.MM"); for (i = 1; i <= worksheet.UsedRange.Rows.Count; i++) { string departament1_value = Convert.ToString(departaments1.Cells[i]); string departament2_value = Convert.ToString(departaments1.Cells[i]); string birthday_value = Convert.ToString(departaments1.Cells[i]); string name_value = Convert.ToString(departaments1.Cells[i]); if (departament1_value == "Іб та ПД" || departament2_value == "Іб та ПД") { if (birthday_value == current_date) label1.Text = "Сьогодні свій день народження відзначає " + name_value; } } app.Quit(); }