internal void GetHorizontalIDs(_Worksheet worksheet) { string currentColumn = idRowStart; string cellName = currentColumn + idRow.ToString(); Range range = null; byte id; for (int i = 0; i < pointsCount; i++) { range = worksheet.get_Range(cellName, Missing.Value); if (range == null) Console.WriteLine("ERROR: range == null, Cell = {0}", cellName); id = Convert.ToByte(range.Value2); TeleportPoint point = leftPoints[id].Clone(); point.TableColumnPos = currentColumn; point.TableRowPos = idRow; if (!topPoints.ContainsKey(id)) { topPoints.Add(id, point); } else { topPoints[id] = point; } currentColumn = GetNextColumnName(currentColumn); cellName = currentColumn + idRow.ToString(); } }
private void LoadToExcel(List<Week> weeks, _Worksheet worksheet) { int currentRow = 2; foreach (var week in weeks) { foreach (var day in week.Days) { var currentColumn = 1; worksheet.Cells[currentRow, currentColumn] = day.Date; foreach (var lesson in day.ListOfLessons) { currentColumn = 1; PutLessonData(lesson.Name, worksheet,currentRow, ref currentColumn); PutLessonData(lesson.StartTime, worksheet, currentRow, ref currentColumn); PutLessonData(lesson.Classroom, worksheet, currentRow, ref currentColumn); PutLessonData(lesson.Teacher, worksheet, currentRow, ref currentColumn); currentRow++; } } } }
public void InformeGeneralObras() { oExcel = new Application(); oBooks = oExcel.Workbooks; oBook = oBooks.Add(1); oSheets = (Sheets)oBook.Worksheets; oSheet = oSheets.get_Item(1); this.oSheet.Cells[1,1] = "Consecutivo"; this.oSheet.Cells[1,2] = "Título"; this.oSheet.Cells[1,3] = "Núm. de Material"; this.oSheet.Cells[1,4] = "Año"; this.oSheet.Cells[1,5] = "Tiraje"; int ind = 2; for (int j = 0; j < obrasImprimir.Count; j++) { oSheet.Cells[1][ind] = obrasImprimir[j].Consecutivo; oSheet.Cells[2][ind] = obrasImprimir[j].Titulo; oSheet.Cells[3][ind] = obrasImprimir[j].NumMaterial; oSheet.Cells[4][ind] = obrasImprimir[j].AnioPublicacion; oSheet.Cells[5][ind] = obrasImprimir[j].Tiraje; ind++; } this.oExcel.ActiveWorkbook.Save(); this.oExcel.Quit(); }
/// <summary> /// Activates a workbook via its name. /// </summary> /// <param name="name">The name of the workbook</param> /// <returns>true if the workbook was found, false otherways</returns> public bool activateWorkbook(string name) { foreach (_Workbook workbook in excel.Workbooks) { if (workbook.Name == name) { workbook.Activate(); this.workbook = workbook; this.worksheet = (_Worksheet) workbook.ActiveSheet; return true; } } return false; }
//Método para cargar un archivo de Excel static bool excel_init(String ruta) { appExcel = new ApplicationClass(); if (System.IO.File.Exists(ruta)){ workBookExl = appExcel.Workbooks.Open(ruta,0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",true, false, 0, true, false, false); wSheet = (_Worksheet)appExcel.ActiveWorkbook.ActiveSheet; return true; }else{ Console.WriteLine("El documento " + ruta + " No puede abrirse"); System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel); appExcel = null; return false; } }
private static void CloseExcel(_Worksheet worksheet, Application application, Workbook workbook, string path) { var range = worksheet.Range["A1", "F1"]; range.EntireColumn.AutoFit(); range.Font.Bold = true; application.Visible = false; application.UserControl = false; workbook.SaveAs(path, XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); workbook.Close(); }
//Método para cerrar una conexión en excel static void excel_close() { if (appExcel != null){ try{ workBookExl.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel); appExcel = null; wSheet = null; }catch (Exception ex){ appExcel = null; Console.WriteLine("Ocurrieron problemas al intentar liberar los recursos: " + ex.ToString()); }finally{ GC.Collect(); } } }
private string InitExcel(out Application application, out Workbook workbook, out _Worksheet worksheet) { String path = GetFilePath(); File.Delete(path); application = new Application { Visible = true }; workbook = application.Workbooks.Add(); worksheet = (_Worksheet)workbook.ActiveSheet; worksheet.Cells[1, 1] = "День Недели"; worksheet.Cells[1, 2] = "Предмет"; worksheet.Cells[1, 3] = "Время"; worksheet.Cells[1, 4] = "Аудитория"; worksheet.Cells[1, 5] = "Преподователь"; return path; }
private void OpenExcel (out Application application, out Workbook workbook, out _Worksheet worksheet) { var path = GetFilePath(); File.Delete(path); application = new Application { Visible = true }; workbook = application.Workbooks.Add(); worksheet = (_Worksheet)workbook.ActiveSheet; worksheet.Cells[1, 1] = "День Недели"; worksheet.Cells[1, 2] = "Предмет"; worksheet.Cells[1, 3] = "Время"; worksheet.Cells[1, 4] = "Аудитория"; worksheet.Cells[1, 5] = "Преподователь"; var range = worksheet.Range["A1", "F1"]; range.EntireColumn.AutoFit(); range.Font.Bold = true; }
//Method to close excel connection public void excel_close() { if (appExcel != null) { try { newWorkbook.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel); appExcel = null; objsheet = null; } catch (Exception ex) { appExcel = null; } finally { GC.Collect(); } } }
//Method to close excel connection static void excel_close() { if (appExcel != null) { try { newWorkbook.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel); appExcel = null; objsheet = null; } catch (Exception ex) { appExcel = null; MessageBox.Show("Unable to release the Object " + ex.ToString()); } finally { GC.Collect(); } } }
private void WriteData( _Worksheet excelWorksheet, IEnumerable<Player> players, IDictionary<Player, double> playerAveragesDictionary, string teamName, double teamAverage, double teamHandicap, ref int rowIndex) { excelWorksheet.Cells[rowIndex, 1] = teamName; rowIndex += 2; excelWorksheet.Cells[rowIndex, 1] = "Name"; excelWorksheet.Cells[rowIndex, 2] = "Average"; excelWorksheet.Cells[rowIndex, 3] = "Game 1"; excelWorksheet.Cells[rowIndex, 4] = "Game 2"; excelWorksheet.Cells[rowIndex, 5] = "Game 3"; excelWorksheet.Cells[rowIndex, 6] = "Total"; rowIndex += 2; foreach (Player player in players) { excelWorksheet.Cells[rowIndex, 1] = player.Name; excelWorksheet.Cells[rowIndex, 2] = playerAveragesDictionary[player]; rowIndex++; } excelWorksheet.Cells[rowIndex, 1] = "Team"; excelWorksheet.Cells[rowIndex, 2] = teamAverage; rowIndex += 2; excelWorksheet.Cells[rowIndex, 1] = "Handicap"; excelWorksheet.Cells[rowIndex, 2] = teamHandicap; rowIndex += 2; excelWorksheet.Cells[rowIndex, 1] = "Total"; excelWorksheet.Cells[rowIndex, 2] = teamHandicap + teamAverage; rowIndex += 2; }
private void button1_Click(object sender, EventArgs e) { if (myArray == null) { MessageBox.Show("请先读取数据"); return; } //開啟一個新的應用程式 myExcel = new Excel.Application(); //加入新的活頁簿 myExcel.Workbooks.Add(true); //停用警告訊息 myExcel.DisplayAlerts = true; //讓Excel文件可見 myExcel.Visible = true; //引用第一個活頁簿 myBook = myExcel.Workbooks[1]; //設定活頁簿焦點 myBook.Activate(); //加入新的工作表在第1張工作表之後 myBook.Sheets.Add(Type.Missing, myBook.Worksheets[1], 1, Type.Missing); //引用第一個工作表 mySheet = (Worksheet)myBook.Worksheets[1]; //命名工作表的名稱為 "Array" mySheet.Name = "Array"; //設工作表焦點 mySheet.Activate(); int UpBound1 = myArray.GetUpperBound(0);//二維陣列數上限 int UpBound2 = myArray.GetUpperBound(1);//二維陣列數上限 //寫入報表名稱 myExcel.Cells[1, 4] = "全自动生成報表"; //設定範圍 myRange = (Range)mySheet.Range[mySheet.Cells[2, 1], mySheet.Cells[UpBound1 + 2, UpBound2 + 1]]; myRange.Select(); //用陣列一次寫入資料 myRange.Value2 = myArray; //設定儲存路徑 string PathFile = Directory.GetCurrentDirectory() + @"\我的报表.xlsx"; //另存活頁簿 myBook.SaveAs(PathFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing , XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //關閉活頁簿 //myBook.Close(false, Type.Missing, Type.Missing); ////關閉Excel //myExcel.Quit(); ////釋放Excel資源 //System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel); myBook = null; mySheet = null; myRange = null; myExcel = null; GC.Collect(); }
//Method to initialize opening Excel static void excel_init(String path) { //string slash = @"\\"; //slash = Regex.Escape(slash); //string singleslash = @"/"; //path = Regex.Replace(slash,path,singleslash); // path = "@" + path; // path = path.Replace("\\","\\"); appExcel = new Microsoft.Office.Interop.Excel.ApplicationClass(); if (System.IO.File.Exists(path)) { // then go and load this into excel newWorkbook = appExcel.Workbooks.Open(path, true, true); objsheet = (_Worksheet)appExcel.ActiveWorkbook.ActiveSheet; } else { MessageBox.Show("Unable to open file!"); System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel); appExcel = null; System.Windows.Forms.Application.Exit(); } }
protected void btnExport_Click(object sender, EventArgs e) { try { //创建一个新的excel文档 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Workbooks wbks = excel.Workbooks; //若打开已有excel,把“true”替换成该excel的文件路径; //注:若新建一个excel文档,“xxx”替换成true即可;不过这里新建的excel文档默认只有一个sheet。 _Workbook _wbk = wbks.Add(true); //取得sheet Sheets shs = _wbk.Sheets; //i是要取得的sheet的index _Worksheet _wsh = (_Worksheet)shs.get_Item(1); //_wsh.Cells[1, 1] = " //添加行 //((Range)_wsh.Rows[11, Missing.Value]).Insert(Missing.Value, XlInsertFormatOrigin.xlFormatFromLeftOrAbove); //添加列 //_wsh.get_Range(_wsh.Cells[1, 1], Missing.Value).Insert(Missing.Value, XlInsertShiftDirection.xlShiftToRight); //_wsh.get_Range(_wsh.Cells[1, 1], _wsh.Cells[_wsh.Rows.Count, 1]).Insert(Missing.Value, XlInsertShiftDirection.xlShiftToRight); //设置单元格颜色 //((Range)_wsh.Rows[1, Missing.Value]).Interior.ColorIndex = 3; _wsh.Cells[1, 1] = "流水号"; _wsh.Cells[1, 2] = "零件号"; _wsh.Cells[1, 3] = "零件名称"; _wsh.Cells[1, 4] = "数量"; _wsh.Cells[1, 5] = "工序"; _wsh.Cells[1, 6] = "工位"; _wsh.Cells[1, 7] = "计划号"; _wsh.Cells[1, 8] = "SO"; _wsh.Cells[1, 9] = "地点"; _wsh.Cells[1, 10] = "供应商"; _wsh.Cells[1, 11] = "类型"; _wsh.Cells[1, 12] = "保管员"; for (int i = 0; i < ASPxGridView1.VisibleRowCount; i++) { //添加行 //object a = ASPxGridView1.GetRow(i); _wsh.Cells[i + 2, 1] = ASPxGridView1.GetRowValues(i, "GHTM"); _wsh.Cells[i + 2, 2] = ASPxGridView1.GetRowValues(i, "ABOM_COMP"); _wsh.Cells[i + 2, 3] = ASPxGridView1.GetRowValues(i, "ABOM_DESC"); _wsh.Cells[i + 2, 4] = ASPxGridView1.GetRowValues(i, "ABOM_QTY"); _wsh.Cells[i + 2, 5] = ASPxGridView1.GetRowValues(i, "ABOM_OP"); _wsh.Cells[i + 2, 6] = ASPxGridView1.GetRowValues(i, "ABOM_WKCTR"); _wsh.Cells[i + 2, 7] = ASPxGridView1.GetRowValues(i, "ABOM_JHDM"); _wsh.Cells[i + 2, 8] = ASPxGridView1.GetRowValues(i, "ABOM_SO"); _wsh.Cells[i + 2, 9] = ASPxGridView1.GetRowValues(i, "GZDD"); _wsh.Cells[i + 2, 10] = ASPxGridView1.GetRowValues(i, "ABOM_GYS"); _wsh.Cells[i + 2, 11] = ASPxGridView1.GetRowValues(i, "COMP_FLAG"); _wsh.Cells[i + 2, 12] = ASPxGridView1.GetRowValues(i, "BGY"); string compFlag = ASPxGridView1.GetRowValues(i, "COMP_FLAG").ToString(); switch (compFlag) { case "0": //2代表白色 ((Range)_wsh.Rows[i + 2, Missing.Value]).Interior.ColorIndex = 2; break; case "1": //3代表红色 ((Range)_wsh.Rows[i + 2, Missing.Value]).Interior.ColorIndex = 3; break; case "2": //10代表不刺眼的绿色 ((Range)_wsh.Rows[i + 2, Missing.Value]).Interior.ColorIndex = 10; break; default: return; } } //如果目录不存在则创建目录 if (!Directory.Exists("C:/excel")) { Directory.CreateDirectory("C:/excel"); } //屏蔽掉系统跳出的覆盖文件Alert excel.DisplayAlerts = false; _wbk.Saved = true; //保存到指定目录 //替换点否会报错 _wbk.SaveAs("C:/excel/改制差异清单明细信息导出.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //用@的方法只是不会报错,但\显示不出来 //showAlert(this, @"文件已保存至C:\excel\改制差异清单明细信息导出.xls"); //加一个中文的双引号就不会有什么问题 showAlert(this, "文件已成功保存!"); //设置回默认值 excel.DisplayAlerts = true; excel.Quit(); //释放掉多余的excel进程 //??释放有问题,关闭电脑的时候会提示--将这些资源依次释放再设置为空,最后回收内存就不会再有问题 System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); System.Runtime.InteropServices.Marshal.ReleaseComObject((object)wbks); System.Runtime.InteropServices.Marshal.ReleaseComObject((object)_wbk); System.Runtime.InteropServices.Marshal.ReleaseComObject((object)shs); System.Runtime.InteropServices.Marshal.ReleaseComObject((object)_wsh); _wsh = null; shs = null; _wbk = null; wbks = null; excel = null; GC.Collect(0); } catch { showAlert(this, "导出失败!"); return; } }
private void PutLessonData(String data, _Worksheet worksheet,int currentRow, ref int currentColumn) { currentColumn++; worksheet.Cells[currentRow, currentColumn] = data; }
/// <summary> /// ConsolidatedXMLExportToExcel /// </summary> /// <param name="dt"></param> /// <param name="ws"></param> /// <param name="nextRow"></param> /// <param name="isCompare"></param> /// <param name="isPolicyWiseSummary"></param> public string ConsolidatedXmlExportToExcel(DataTable dt, _Worksheet ws, bool nextRow, bool isCompare, bool isPolicyWiseSummary) { // Copy the DataTable to an object array var rawData = new object[dt.Rows.Count + 1, dt.Columns.Count]; int intRowNum; if (nextRow) { if (ws.UsedRange.Rows.Count == 1) { intRowNum = ws.UsedRange.Rows.Count; if (isCompare) { intRowNum = intRowNum + 2; } } else { ////intRowNum = ws.UsedRange.Rows.Count + 1; if (isPolicyWiseSummary) { intRowNum = ws.UsedRange.Rows.Count; } else { intRowNum = ws.UsedRange.Rows.Count + 1; } } } else { intRowNum = ws.UsedRange.Rows.Count; } if (!(intRowNum > 1)) { // Copy the column names to the first row of the object array for (var col = 0; col < dt.Columns.Count; col++) { rawData[0, col] = dt.Columns[col].ColumnName; } // Copy the values to the object array for (var col = 0; col < dt.Columns.Count; col++) { for (var row = 0; row < dt.Rows.Count; row++) { rawData[row + 1, col] = dt.Rows[row].ItemArray[col]; } } //if (dt.Rows.Count > 0) //{ // ((Range)ws.Rows[intRowNum + dt.Rows.Count, Type.Missing]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow); //} } else { if (isCompare || isPolicyWiseSummary) { for (var col = 0; col < dt.Columns.Count; col++) { rawData[0, col] = dt.Columns[col].ColumnName; } for (var col = 0; col < dt.Columns.Count; col++) { for (var row = 0; row < dt.Rows.Count; row++) { rawData[row + 1, col] = dt.Rows[row].ItemArray[col]; } } } else { // Copy the values to the object array for (var col = 0; col < dt.Columns.Count; col++) { for (var row = 0; row < dt.Rows.Count; row++) { rawData[row, col] = dt.Rows[row].ItemArray[col]; } } } if (dt.Rows.Count > 0) { ((Range)ws.Rows[intRowNum + dt.Rows.Count, Type.Missing]).Interior.Color = ColorTranslator.ToOle(Color.Yellow); } } // Calculate the final column letter var finalColLetter = string.Empty; const string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; var colCharsetLen = colCharset.Length; if (dt.Columns.Count > colCharsetLen) { finalColLetter = colCharset.Substring( (dt.Columns.Count - 1) / colCharsetLen - 1, 1); } finalColLetter += colCharset.Substring( (dt.Columns.Count - 1) % colCharsetLen, 1); string excelRange; if (intRowNum > 1) { if (nextRow) { excelRange = string.Format(CultureInfo.InvariantCulture, "A{1}:{0}{2}", finalColLetter, intRowNum, (intRowNum + dt.Rows.Count)); } else { excelRange = string.Format(CultureInfo.InvariantCulture, "A{1}:{0}{2}", finalColLetter, intRowNum + 1, (intRowNum + dt.Rows.Count)); } } else { excelRange = string.Format(CultureInfo.InvariantCulture, "A{1}:{0}{2}", finalColLetter, intRowNum, ws.UsedRange.Rows.Count + dt.Rows.Count); } intRowNum = intRowNum + dt.Rows.Count + dt.Rows.Count; ws.Range[excelRange, Type.Missing].NumberFormat = "@"; ws.Range[excelRange, Type.Missing].Value2 = rawData; ws.Range[excelRange, Type.Missing].EntireColumn.AutoFit(); //ws.get_Range(excelRange, Type.Missing).AutoFit(); // Mark the first row as BOLD var j = 2; for (var i = 0; i < dt.Rows.Count; i++) { ((Range)ws.Rows[1, Type.Missing]).Font.Bold = true; ((Range)ws.Rows[1, Type.Missing]).Font.Color = ColorTranslator.ToOle(Color.LightGray); ((Range)ws.Rows[1, Type.Missing]).Interior.Color = Color.Blue; ((Range)ws.Rows[j, Type.Missing]).Borders.Color = ColorTranslator.ToOle(Color.Black); ((Range)ws.Rows[j, Type.Missing]).Interior.Color = ColorTranslator.ToOle(Color.LightGray); var value = dt.Rows[i]["PASS or FAIL"].ToString(); if (value == "PASS") { ((Range)ws.Columns[1, Type.Missing].Rows[j, Type.Missing]).Interior.Color = ColorTranslator.ToOle(Color.LightGreen); ((Range)ws.Columns[2, Type.Missing].Rows[j, Type.Missing]).Interior.Color = ColorTranslator.ToOle(Color.LightGreen); ((Range)ws.Columns[3, Type.Missing].Rows[j, Type.Missing]).Interior.Color = ColorTranslator.ToOle(Color.LightGreen); ((Range)ws.Columns[4, Type.Missing].Rows[j, Type.Missing]).Interior.Color = ColorTranslator.ToOle(Color.LightGreen); } if (value == "FAIL") { ((Range)ws.Columns[1, Type.Missing].Rows[j, Type.Missing]).Interior.Color = ColorTranslator.ToOle(Color.Red); ((Range)ws.Columns[2, Type.Missing].Rows[j, Type.Missing]).Interior.Color = ColorTranslator.ToOle(Color.Red); ((Range)ws.Columns[3, Type.Missing].Rows[j, Type.Missing]).Interior.Color = ColorTranslator.ToOle(Color.Red); ((Range)ws.Columns[4, Type.Missing].Rows[j, Type.Missing]).Interior.Color = ColorTranslator.ToOle(Color.Red); } j++; // if (text.Contains("Passed")) // { // ((Range)ws.Columns[5, Type.Missing]).Rows[2, Type.Missing].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green); // } // if (text.Contains("Failed")) // { // ((Range)ws.Columns[5, Type.Missing]).Rows[3, Type.Missing].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); // } } return finalColLetter + "," + intRowNum; }
public void ExportDataToExcel_New(string saveFileFullPath, string fileName, string[] tableName, string[] sheetName, DataSet ds) { PreExitExcel(); string filename = fileName.Substring(0, fileName.LastIndexOf(".")); try { _ExcelApp = new Microsoft.Office.Interop.Excel.Application(); _ExcelWBook = (_Workbook)(_ExcelApp.Workbooks.Open(excelTemplatePath + filename, 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)); for (int i = 0; i < tableName.Length; i++) { _IniRow = 3; _sheetName = sheetName[i].ToString(); _ExcelWSheet = (_Worksheet)_ExcelWBook.Sheets[sheetName[i].ToString()]; int rowcount = ds.Tables[i].Rows.Count; for (int j = 0; j < ds.Tables[i].Rows.Count; j++) { for (int k = 0; k < ds.Tables[i].Columns.Count; k++) { _ExcelWSheet.Cells[_IniRow, k + 1] = ds.Tables[i].Rows[j][k].ToString(); } _IniRow++; } } ExcelHelper excelHelper = new ExcelHelper(); _ExcelApp.Rows.RowHeight = "15"; //ExApp.DisplayAlerts = false; _ExcelWBook.CheckCompatibility = false;//Add for Diable Compatibility for saving excel _ExcelWBook.SaveAs(saveFileFullPath, XlFileFormat.xlWorkbookNormal, null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, null, null, null); _ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value); _ExcelApp.Workbooks.Close(); _ExcelApp.Quit(); Marshal.ReleaseComObject(_ExcelWSheet); Marshal.ReleaseComObject(_ExcelWBook); Marshal.ReleaseComObject(_ExcelApp); } catch (Exception e) { _ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value); _ExcelApp.Workbooks.Close(); _ExcelApp.Quit(); throw e; } }
/// <summary> /// Validates the affected mapped range to check if the data is going to be imported /// in any of the available range /// </summary> /// <param name="currentSheet">Current active sheet</param> /// <param name="currentRange">Target range</param> /// <returns>True if the data is in affected range</returns> private bool ValidateAffectedMappedRange(_Worksheet currentSheet, Range currentRange) { bool isContinue = true; if (currentSheet != null && currentRange != null && this.currentWorkbookMap != null && this.currentWorkbookMap.SelectedLayerMap != null) { Dictionary<string, string> allNamedRange = new Dictionary<string, string>(); // Get all Layer details into dictionary. this.currentWorkbookMap.LocalLayerMaps.ForEach(item => { allNamedRange.Add(item.RangeDisplayName, item.RangeAddress); }); Dictionary<string, string> affectedMappedNamedRanges = currentSheet.GetAffectedNamedRanges(currentRange, allNamedRange); foreach (LayerMap layerMap in this.currentWorkbookMap.LocalLayerMaps) { if (this.currentWorkbookMap.SelectedLayerMap.MapType == LayerMapType.WWT) { // Checks if the affected range is in any of the existing ranges for WWT layer if (affectedMappedNamedRanges.ContainsKey(layerMap.RangeDisplayName) && layerMap.RangeName.IsValid()) { Ribbon.ShowError(Properties.Resources.MappedWWTRangeError); isContinue = false; break; } } else { // Checks if the affected range is in any of the existing ranges for local in WWT layer and // the range is not the current range for selected layer if (affectedMappedNamedRanges.ContainsKey(layerMap.RangeDisplayName) && layerMap.RangeName.IsValid() && !layerMap.RangeDisplayName.Equals(this.currentWorkbookMap.SelectedLayerMap.RangeDisplayName)) { isContinue = Ribbon.ShowWarningWithResult(Properties.Resources.MappedLocalInWWTRangeError); break; } } } } else { isContinue = false; } return isContinue; }
private static void WriteAttendanceStats(_Worksheet newWorksheet, int semester, int class_id) { var students = StudentsRepository.GetAllStudents(class_id); int nothing = 0; int neizvineni = 0; List <Student> studentsNeizv = new List <Student>(); if (semester % 20 == 1) { nothing = students.Where(s => s.Attendances.Where(a => a.Date1.Month >= 2 && a.Date1.Month <= 6).Count() == 0).Count(); studentsNeizv = students.Where(s => s.Attendances .Where(a => (a.Att_type == 0 || a.Att_type == 1) && (a.Date1.Month >= 2 && a.Date1.Month <= 6)) .Count() > 0).ToList(); neizvineni = studentsNeizv.Count; } else { nothing = students.Where(s => s.Attendances.Where(a => a.Date1.Month >= 9 || a.Date1.Month == 1).Count() == 0).Count(); studentsNeizv = students.Where(s => s.Attendances .Where(a => (a.Att_type == 0 || a.Att_type == 1) && (a.Date1.Month >= 9 || a.Date1.Month == 1)) .Count() > 0).ToList(); neizvineni = studentsNeizv.Count; } x += 2; int header = x; newWorksheet.Range[newWorksheet.Cells[x, 1], newWorksheet.Cells[x, 2]].Merge(); newWorksheet.Cells[x, 1] = "Отсъствия"; newWorksheet.Range[newWorksheet.Cells[x, 1], newWorksheet.Cells[x, 2]].Font.Bold = true; x++; newWorksheet.Cells[x, 1] = "Без отсъствия"; newWorksheet.Cells[x, 2] = nothing.ToString(); x++; newWorksheet.Cells[x, 1] = "С неизвинени отсъствия"; newWorksheet.Cells[x, 2] = neizvineni.ToString(); x++; newWorksheet.Cells[x, 1] = "До 3 часа"; newWorksheet.Cells[x, 2] = studentsNeizv .Where(s => s.Attendances.Where(a => a.Att_type == 0).Count() + (s.Attendances.Where(a => a.Att_type == 1).Count() / 3) <= 3).Count(); x++; newWorksheet.Cells[x, 1] = "До 10 часа"; newWorksheet.Cells[x, 2] = studentsNeizv .Where(s => s.Attendances.Where(a => a.Att_type == 0).Count() + (s.Attendances.Where(a => a.Att_type == 1).Count() / 3) > 3 && s.Attendances.Where(a => a.Att_type == 0).Count() + (s.Attendances.Where(a => a.Att_type == 1).Count() / 3) <= 10).Count(); x++; newWorksheet.Cells[x, 1] = "До 15 часа"; newWorksheet.Cells[x, 2] = studentsNeizv .Where(s => s.Attendances.Where(a => a.Att_type == 0).Count() + (s.Attendances.Where(a => a.Att_type == 1).Count() / 3) > 10 && s.Attendances.Where(a => a.Att_type == 0).Count() + (s.Attendances.Where(a => a.Att_type == 1).Count() / 3) <= 15).Count(); x++; newWorksheet.Cells[x, 1] = "До 25 часа"; newWorksheet.Cells[x, 2] = studentsNeizv .Where(s => s.Attendances.Where(a => a.Att_type == 0).Count() + (s.Attendances.Where(a => a.Att_type == 1).Count() / 3) > 15 && s.Attendances.Where(a => a.Att_type == 0).Count() + (s.Attendances.Where(a => a.Att_type == 1).Count() / 3) <= 25).Count(); x++; newWorksheet.Cells[x, 1] = "До 10 часа"; newWorksheet.Cells[x, 2] = studentsNeizv .Where(s => s.Attendances.Where(a => a.Att_type == 0).Count() + (s.Attendances.Where(a => a.Att_type == 1).Count() / 3) > 25).Count(); newWorksheet.Range[newWorksheet.Cells[header, 1], newWorksheet.Cells[x, 2]].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //var att = AttendanceRepository.GetAttendanceBetweenDates(DateTime.Now.AddYears(-2), DateTime.Now.AddYears(2), class_id); //if (semester == 2) att = att.Where(a => a.Date1.Month >= 2 && a.Date1.Month <= 6).ToList(); //else att = att.Where(a => a.Date1.Month >= 9 || a.Date1.Month == 1).ToList(); }
private void Generate_Click(object sender, RoutedEventArgs e) { List <string> NCProg_Tools = new List <string>(); Microsoft.Office.Interop.Excel.Application oXL; _Workbook oWB; _Worksheet oSheetProjectSummary = null; _Worksheet oSheetProjectSummaryFull = null; _Worksheet oSheetNCProgSummary = null; _Worksheet oSheetNewNCProgSummary = null; _Worksheet oSheetToolpathDetails = null; _Worksheet oSheetToolList = null; _Worksheet oSheetNewToolList = null; Dictionary <string, string> VarsListProjectSummary = new Dictionary <string, string>(); Dictionary <string, string> VarsListProjectSummaryFull = new Dictionary <string, string>(); Dictionary <string, string> VarsListNCProgSummary = new Dictionary <string, string>(); Dictionary <string, string> VarsListToolpathDetails = new Dictionary <string, string>(); Dictionary <string, string> VarsListToolList = new Dictionary <string, string>(); bool Has_ProjectSummary = false; bool Has_ProjectSummaryFull = false; bool Has_NCProgSummary = false; bool Has_ToolList = false; bool Has_ToolpathDetails = false; string Project_Path = PowerMILLAutomation.ExecuteEx("print $project_pathname(0)"); string NCProgDetails = ""; string MergedModelList = ""; int FileQty = 0; if (listNCProgsSelected.Items.Count == 0) { //No NC Program selected, do nothing... MessageBox.Show("Please select at least one NC Program"); } else { List <ToolInfo> ToolData = new List <ToolInfo>(); List <ToolpathInfo> ToolpathData = new List <ToolpathInfo>(); List <ToolInfo> ProjectToolData = new List <ToolInfo>(); List <ToolpathInfo> ProjectToolpathData = new List <ToolpathInfo>(); ProjectInfo Project = new ProjectInfo(); //Start Excel and get Application object. oXL = new Microsoft.Office.Interop.Excel.Application(); oXL.Visible = true; oXL.DisplayAlerts = false; //Get the template from the option page string Path = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\ExcellSetupSheet\\Template.ini"; string TemplateFile = ""; if (File.Exists(Path)) { const Int32 BufferSize = 128; using (var fileStream = File.OpenRead(Path)) using (var streamReader = new StreamReader(fileStream, Encoding.UTF8, true, BufferSize)) { String line; while ((line = streamReader.ReadLine()) != null) { TemplateFile = line; } } } else { Path = System.Reflection.Assembly.GetExecutingAssembly().CodeBase.Substring(8, System.Reflection.Assembly.GetExecutingAssembly().CodeBase.Length - 8); string directory = System.IO.Path.GetDirectoryName(Path); TemplateFile = directory + "\\Tool_List.xlsx"; } if (!Directory.Exists(Project_Path + "\\Excel_Setupsheet\\")) { Directory.CreateDirectory(Project_Path + "\\Excel_Setupsheet\\"); } string[] fileArrayXLSX = Directory.GetFiles(Project_Path + "\\Excel_Setupsheet\\", "*.xlsx"); string[] fileArrayXLS = Directory.GetFiles(Project_Path + "\\Excel_Setupsheet\\", "*.xls"); foreach (string File in fileArrayXLSX) { if (File.IndexOf("~$") < 0) { FileQty = FileQty + 1; } } foreach (string File in fileArrayXLS) { if (File.IndexOf("~$") < 0) { FileQty = FileQty + 1; } } if (FileQty > 0) { File.Copy(TemplateFile, Project_Path + "\\Excel_Setupsheet\\SetupSheet_" + FileQty + ".xlsx"); TemplateFile = Project_Path + "\\Excel_Setupsheet\\SetupSheet_" + FileQty + ".xlsx"; } else { File.Copy(TemplateFile, Project_Path + "\\Excel_Setupsheet\\SetupSheet.xlsx"); TemplateFile = Project_Path + "\\Excel_Setupsheet\\SetupSheet.xlsx"; } //Get a new workbook. oWB = (_Workbook)(oXL.Workbooks.Open(TemplateFile)); List <string> ModelList = PowerMILLAutomation.GetListOf(PowerMILLAutomation.enumEntity.MachinableModels); PowerMILLAutomation.GetModelsLimits(ModelList, out double ModelsMinX, out double ModelsMinY, out double ModelsMinZ, out double ModelsMaxX, out double ModelsMaxY, out double ModelsMaxZ); Project = new ProjectInfo(); Project.MachModelsMaxX = ModelsMaxX.ToString(); Project.MachModelsMaxY = ModelsMaxY.ToString(); Project.MachModelsMaxZ = ModelsMaxZ.ToString(); Project.MachModelsMinX = ModelsMinX.ToString(); Project.MachModelsMinY = ModelsMinY.ToString(); Project.MachModelsMinZ = ModelsMinZ.ToString(); Project.Name = PowerMILLAutomation.ExecuteEx("print $project_pathname(1)"); Project.Path = PowerMILLAutomation.ExecuteEx("print $project_pathname(0)"); Project.OrderNumber = PowerMILLAutomation.ExecuteEx("print $project.orderNumber"); Project.Programmer = PowerMILLAutomation.ExecuteEx("print $project.programmer"); Project.PartName = PowerMILLAutomation.ExecuteEx("print $project.partname"); Project.Customer = PowerMILLAutomation.ExecuteEx("print $project.customer"); Project.Date = DateTime.Now.ToString(); Project.Notes = PowerMILLAutomation.ExecuteEx("print $project.notes"); Project.ExcelTemplate = TemplateFile; foreach (string Model in ModelList) { MergedModelList = MergedModelList + Environment.NewLine + Model; } Project.ModelsList = MergedModelList; Project.CombinedNCTPList = ""; foreach (Worksheet worksheet in oWB.Worksheets) { if (worksheet.Name == "Project_Summary") { oSheetProjectSummary = worksheet; //Extract template keywords and cell adresses VarsListProjectSummary = WriteFiles.ExtractTemplateData(oSheetProjectSummary); Has_ProjectSummary = true; } else if (worksheet.Name == "Project_Summary_Full") { oSheetProjectSummaryFull = worksheet; //Extract template keywords and cell adresses VarsListProjectSummaryFull = WriteFiles.ExtractTemplateData(oSheetProjectSummaryFull); Has_ProjectSummaryFull = true; } else if (worksheet.Name == "NCProg_Summary") { oSheetNCProgSummary = worksheet; //Extract template keywords and cell adresses VarsListNCProgSummary = WriteFiles.ExtractTemplateData(oSheetNCProgSummary); Has_NCProgSummary = true; } else if (worksheet.Name == "Toolpath_Details") { oSheetToolpathDetails = worksheet; //Extract template keywords and cell adresses VarsListToolpathDetails = WriteFiles.ExtractTemplateData(oSheetToolpathDetails); Has_ToolpathDetails = true; } else if (worksheet.Name == "ToolList") { oSheetToolList = worksheet; //Extract template keywords and cell adresses VarsListToolList = WriteFiles.ExtractTemplateData(oSheetToolList); Has_ToolList = true; } } List <string> NCProg_Toolpaths = new List <string>(); foreach (String NCProg in listNCProgsSelected.Items) { if (NCProgDetails == "") { NCProgDetails = NCProg; } else { NCProgDetails = NCProgDetails + Environment.NewLine + NCProg; } NCProg_Toolpaths = PowerMILLAutomation.GetNCProgToolpathes(NCProg); foreach (string Toolpath in NCProg_Toolpaths) { NCProgDetails = NCProgDetails + Environment.NewLine + " " + Toolpath; } Project.TotalTime = Project.TotalTime + double.Parse(PowerMILLAutomation.ExecuteEx("print $entity('ncprogram';'" + NCProg + "').Statistics.TotalTime")); //Extract the PowerMILL parameters found in the template from the current NCProgram toolapths WriteFiles.ExtractData(NCProg, out ToolData, out ToolpathData); if (Has_ProjectSummaryFull) { foreach (ToolpathInfo Toolpath in ToolpathData) { ProjectToolpathData.Add(new ToolpathInfo { Name = Toolpath.Name, Description = Toolpath.Description, Notes = Toolpath.Notes, ToolName = Toolpath.ToolName, ToolNumber = Toolpath.ToolNumber, ToolDiameter = Toolpath.ToolDiameter, ToolType = Toolpath.ToolType, ToolCutterLength = Toolpath.ToolCutterLength, ToolHolderName = Toolpath.ToolHolderName, ToolOverhang = Toolpath.ToolOverhang, ToolNumberOfFlutes = Toolpath.ToolNumberOfFlutes, ToolLengthOffset = Toolpath.ToolLengthOffset, ToolRadOffset = Toolpath.ToolRadOffset, ToolpathType = Toolpath.ToolpathType, ToolTipRadius = Toolpath.ToolTipRadius, ToolDescription = Toolpath.ToolDescription, Thickness = Toolpath.Thickness, AxialThickness = Toolpath.AxialThickness, CutterComp = Toolpath.CutterComp, Feed = Toolpath.Feed, Speed = Toolpath.Speed, IPT = Toolpath.IPT, SFM = Toolpath.SFM, PlungeFeed = Toolpath.PlungeFeed, SkimFeed = Toolpath.SkimFeed, Coolant = Toolpath.Coolant, Stepover = Toolpath.Stepover, DOC = Toolpath.DOC, GeneralAxisType = Toolpath.GeneralAxisType, Statistic_Time = Toolpath.Statistic_Time, TPWorkplane = Toolpath.TPWorkplane, Tolerance = Toolpath.Tolerance, RapidHeight = Toolpath.RapidHeight, SkimHeight = Toolpath.SkimFeed, ToolpathMinX = Toolpath.ToolpathMinX, ToolpathMinY = Toolpath.ToolpathMinY, ToolpathMinZ = Toolpath.ToolpathMinZ, ToolpathMaxX = Toolpath.ToolpathMaxX, ToolpathMaxY = Toolpath.ToolpathMaxY, ToolpathMaxZ = Toolpath.ToolpathMaxZ, FirstLeadInType = Toolpath.FirstLeadInType, SecondLeadInType = Toolpath.SecondLeadInType, FirstLeadOutType = Toolpath.FirstLeadOutType, SecondLeadOutType = Toolpath.SecondLeadOutType, CuttingDistance = Toolpath.CuttingDistance, NCProgName = NCProg }); } } if (Has_NCProgSummary) { //Write the Excel document int Index = oSheetNCProgSummary.Index; oSheetNCProgSummary.Copy(oSheetNCProgSummary, Type.Missing); if (NCProg.Length > 31) { oWB.Sheets[Index].Name = NCProg.Replace("*", "").Substring(0, 30); } else { oWB.Sheets[Index].Name = NCProg; } oSheetNewNCProgSummary = oWB.Sheets[Index]; if (Has_ToolpathDetails) { WriteFiles.CreateExcelFile(NCProg, ToolpathData, ToolData, Project, oSheetNewNCProgSummary, VarsListNCProgSummary, oWB, true, Project_Path, oSheetNewNCProgSummary, NCProgDetails); } else { WriteFiles.CreateExcelFile(NCProg, ToolpathData, ToolData, Project, oSheetNewNCProgSummary, VarsListNCProgSummary, oWB, false, Project_Path, oSheetNewNCProgSummary, NCProgDetails); } } if (Has_ToolpathDetails) { //Write the Excel document WriteFiles.CreateExcelFile(NCProg, ToolpathData, ToolData, Project, oSheetToolpathDetails, VarsListToolpathDetails, oWB, false, Project_Path, oSheetNewNCProgSummary, NCProgDetails); } if (Has_ToolList) { //Write the Excel document int Index = oSheetToolList.Index; oSheetToolList.Copy(oSheetToolList, Type.Missing); if (NCProg.Length > 25) { oWB.Sheets[Index].Name = NCProg.Replace("*", "").Substring(0, 24) + "-Tools"; } else { oWB.Sheets[Index].Name = NCProg + "-Tools"; } oWB.Sheets[Index].Name = NCProg + "-Tools"; oSheetNewToolList = oWB.Sheets[Index]; WriteFiles.CreateExcelFile(NCProg, ToolpathData, ToolData, Project, oSheetNewToolList, VarsListToolList, oWB, false, Project_Path, oSheetNewNCProgSummary, NCProgDetails); } } Project.CombinedNCTPList = NCProgDetails; if (Has_ProjectSummary) { WriteFiles.CreateExcelFile("None", ToolpathData, ToolData, Project, oSheetProjectSummary, VarsListProjectSummary, oWB, true, Project_Path, oSheetProjectSummary, NCProgDetails); } if (Has_ProjectSummaryFull) { WriteFiles.CreateExcelFile("None", ProjectToolpathData, ToolData, Project, oSheetProjectSummaryFull, VarsListProjectSummaryFull, oWB, true, Project_Path, oSheetProjectSummaryFull, NCProgDetails); } if (Has_NCProgSummary) { oSheetNCProgSummary.Delete(); } if (Has_ToolpathDetails) { oSheetToolpathDetails.Delete(); } if (Has_ToolList) { oSheetToolList.Delete(); } oXL.DisplayAlerts = true; oWB.Sheets[1].Activate(); oWB.Save(); MessageBox.Show("SetupSheet exported successfully"); } }
static void Main(string[] args) { Application xlApp = new Application(); Workbook xlWorkBook = xlApp.Workbooks .Open(@"C:\FF\211439_1_19_2020_copy.xlsx"); // Read Defensive Data AllDefensiveRecords.ReadExcel(xlWorkBook); // Read Kicker Data KickerRecord.ReadExcel(xlWorkBook); // Read Offensive Data AllOffensiveRecords.ReadExcel(xlWorkBook); // Read Schedule ScheduleRecord.ReadExcel(xlWorkBook); // Read Team Players TeamPlayersRecord.ReadExcel(xlWorkBook); // Read Teams Team.ReadExcel(xlWorkBook); xlWorkBook.Close(); // For each team summarize offensive record // For each tema summarize defensive record foreach (Team t in Team.Teams) { t.SummarizeRecord(true); t.SummarizeRecord(false); } // Find average def values foreach (Team t in Team.Teams) { t.DefenseSummary(); } // Find average offensive coef foreach (Team t in Team.Teams) { t.OffenseSummary(); } // Calculate team counts foreach (Team t in Team.Teams) { t.PosCounts(); } // For each team find pos / metric coeficients foreach (Team t in Team.Teams) { t.CalculatePosCoef(); } // For each player summarize coeficients Player.Init(); foreach (Player p in Player.AllPlayers) { p.FindPrevCoef(); } // For each team find team players expected coeficients foreach (Team t in Team.Teams) { t.UpdatePlayerCoef(); } // For each team update schedule foreach (Team t in Team.Teams) { t.UpdateSchedule(); } // For each player find expected points foreach (Player p in Player.AllPlayers) { p.FindExpectedPoints(); } xlApp = new Application(); xlApp.Workbooks.Add(); _Worksheet workSheet = xlApp.ActiveSheet; workSheet.Name = "Coeficients"; int col = 1; workSheet.Cells[1, col++] = "Name"; workSheet.Cells[1, col++] = "Position"; foreach (string m in Enum.GetNames(typeof(Metric))) { workSheet.Cells[1, col++] = m; } col = 1; int row = 2; foreach (Player p in Player.AllPlayers) { workSheet.Cells[row, col++] = p.Name; workSheet.Cells[row, col++] = p.Position.ToString(); foreach (Metric m in Enum.GetValues(typeof(Metric))) { workSheet.Cells[row, col++] = p.realCoef[m]; } row++; col = 1; } xlApp.Worksheets.Add(); workSheet = xlApp.ActiveSheet; workSheet.Name = "Weeks"; col = 1; workSheet.Cells[1, col++] = "Name"; workSheet.Cells[1, col++] = "Position"; workSheet.Cells[1, col++] = "Team"; workSheet.Cells[1, col++] = "Drafted"; workSheet.Cells[1, col++] = "ADP"; workSheet.Cells[1, col++] = "Tier"; for (int i = 1; i < 17; i++) { workSheet.Cells[1, col++] = "Week " + i; } col = 1; row = 2; foreach (Player p in Player.AllPlayers) { workSheet.Cells[row, col++] = p.Name; workSheet.Cells[row, col++] = p.Position.ToString(); workSheet.Cells[row, col++] = p.Team.ShortName; col += 3; for (int i = 1; i < 17; i++) { if (p.expectedPoints.ContainsKey(i)) { workSheet.Cells[row, col++] = p.expectedPoints[i]; } else { workSheet.Cells[row, col++] = 0; } } row++; col = 1; } workSheet.SaveAs(@"C:\FF\testNew.xlsx"); xlApp.Quit(); }
private void LoadXL_Button_Click(object sender, EventArgs e) { OpenFileDialog log = new OpenFileDialog(); string filePath = ""; if (log.ShowDialog() == System.Windows.Forms.DialogResult.OK) { filePath = log.FileName; //Order: Name, price, location, quantity, max quantity //StreamReader reader = new StreamReader(filePath); DataTypes.ShoppingList list = new DataTypes.ShoppingList(); //Excel stuff Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Workbook xlWorkbook = xlApp.Workbooks.Open(filePath); _Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Range xlRange = xlWorksheet.UsedRange; list.SetListName("Loaded Excel List"); int rowCount = xlRange.Rows.Count; for (int i = 1; i <= rowCount; i++) { DataTypes.ListItem item; string name = ""; string location = ""; double price = 0; int quantity = 0; int maxQuantity = 0; if (xlRange.Cells[i, 1] != null && xlRange.Cells[i, 1].Value2 != null) { name = Convert.ToString(xlRange.Cells[i, 1].Value2); } if (xlRange.Cells[i, 2] != null && xlRange.Cells[i, 2].Value2 != null) { price = Convert.ToDouble(xlRange.Cells[i, 2].Value2); } if (xlRange.Cells[i, 3] != null && xlRange.Cells[i, 3].Value2 != null) { location = Convert.ToString(xlRange.Cells[i, 3].Value2); } if (xlRange.Cells[i, 4] != null && xlRange.Cells[i, 4].Value2 != null) { quantity = Convert.ToInt32(xlRange.Cells[i, 4].Value2); } if (xlRange.Cells[i, 5] != null && xlRange.Cells[i, 5].Value2 != null) { maxQuantity = Convert.ToInt32(xlRange.Cells[i, 5].Value2); } item = new DataTypes.ListItem(name, location, quantity, maxQuantity, (float)price); list.AddItem(item); } //reader.Close(); parent.currList = list; parent.FullListBox.Items.Clear(); parent.FullListBox.Items.AddRange(parent.currList.GetNameList().ToArray()); } }
/// <summary> /// 读取并保存Excel中的信息到数据库中 /// </summary> private void ReadAndSaveExcelInfo() { string strTmpTable, strTmpField, strTmpValue; string val1 = ""; string val2 = ""; string val3 = ""; string val4 = ""; string val5 = ""; string val6 = ""; string val7 = ""; string val8 = ""; string val9 = ""; string val10 = ""; string val11 = ""; string val12 = ""; string val13 = ""; string val14 = ""; string val15 = ""; string val16 = ""; int num = 0; Sheets sheets = workbook.Worksheets; _Worksheet worksheet = (_Worksheet)sheets.get_Item(1); Excel.Range range = worksheet.get_Range("A2", "P65535");//A2、O65535表示到Excel从第A列的第2行到第O列的第65535-1行 A2、P65535表示到Excel从第A列的第2行到第P列的第65535-1行 System.Array values = (System.Array)range.Formula; num = values.GetLength(0); //(OWC11.XlBorderWeight.xlThin); //边框细线 //((Excel.Range)worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[3, 3])).Borders.Weight = 25; //单元格高度 //((Excel.Range)worksheet.Cells[2, 2]).ColumnWidth = 20; //单元格列宽度 //((Range)worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[3, 3])).MergeCells(true); //合并单元格 //((Range)worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[3, 3])).Font.Bold(true); //字体粗体 //if (values.GetValue(1, 1).ToString().Trim() != "车证号" && values.GetValue(1, 2).ToString().Trim() != "车号" && values.GetValue(1, 3).ToString().Trim() != "合同号") //{ // MessageBox.Show("预报选择错误,请重新选择Excel文件: '汽车衡预报模板'!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); // return; //} for (int i = 1; i <= num; i++) { //if (values.GetValue(i, 1).ToString().Trim() == "" && values.GetValue(i, 2).ToString().Trim() == "" && values.GetValue(i, 5).ToString().Trim() == "") //{ // num = i; //} if (values.GetValue(i, 1).ToString().Trim() == "" || values.GetValue(i, 2).ToString().Trim() == "") { break; } else { val1 = values.GetValue(i, 1).ToString().Trim(); //记帐日期 val2 = values.GetValue(i, 2).ToString().Trim(); //订单号 val3 = values.GetValue(i, 3).ToString().Trim(); //订单行项目号 // val4 = values.GetValue(i, 4).ToString().Trim(); //物料编码 // val5 = values.GetValue(i, 5).ToString().Trim(); //物料名称 val6 = values.GetValue(i, 4).ToString().Trim(); //批号 val7 = values.GetValue(i, 5).ToString().Trim(); //收货数量 val8 = values.GetValue(i, 6).ToString().Trim(); //计量单位 val9 = values.GetValue(i, 7).ToString().Trim(); //工厂 val10 = values.GetValue(i, 8).ToString().Trim(); //库存地点 // val11 = values.GetValue(i, 11).ToString().Trim(); //移动类型 val12 = values.GetValue(i, 9).ToString().Trim(); //上工序批次 // val13 = values.GetValue(i, 13).ToString().Trim(); //特殊库存标志 //val14 = values.GetValue(i, 14).ToString().Trim(); //销售订单号 //val15 = values.GetValue(i, 15).ToString().Trim(); //销售订单行项目号 val16 = values.GetValue(i, 10).ToString().Trim(); //抬头文本 string strLRR = CoreFS.SA06.CoreUserInfo.UserInfo.GetUserName(); string strLRD = CoreFS.SA06.CoreUserInfo.UserInfo.GetDepartment(); strTmpTable = "dt_carweight_weight"; strTmpField = "fs_weightno,FS_ACCOUNTDATE,FS_PRODUCTNO,FS_ITEMNO,FS_STOVENO,FN_NETWEIGHT,FS_PLANT,FS_SAPSTORE,fd_grossdatetime"; strTmpValue = "'" + Guid.NewGuid().ToString() + "','" + val1 + "','" + val2 + "','" + val3 + "','" + val6 + "','" + val7 + "','" + val9 + "','" + val10 + "',sysdate"; CoreClientParam ccp = new CoreClientParam(); ccp.ServerName = "ygjzjl.base.QueryData"; ccp.MethodName = "insertDataInfo"; ccp.ServerParams = new object[] { strTmpTable, strTmpField, strTmpValue }; this.ExecuteNonQuery(ccp, CoreInvokeType.Internal); } } }
public void createOneWayAnova(List <Variable> variables) { _Worksheet sheet = WorksheetHelper.NewWorksheet("One-Way ANOVA"); sheet.Cells[1, 1] = "ANOVA Summary"; sheet.Cells[2, 1] = "Total Sample Size"; sheet.Cells[3, 1] = "Grand Mean"; sheet.Cells[4, 1] = "Pooled Std Dev"; sheet.Cells[5, 1] = "Pooled Variance"; sheet.Cells[6, 1] = "Number of Samples"; sheet.Cells[7, 1] = "Confidence Level"; sheet.Cells[9, 1] = "ANOVA Sample Stats"; sheet.Cells[10, 1] = "Sample Size"; sheet.Cells[11, 1] = "Sample Mean"; sheet.Cells[12, 1] = "Sample Std Dev"; sheet.Cells[13, 1] = "Sample Variance"; sheet.Cells[14, 1] = "Pooling Weight"; sheet.Cells[17, 1] = "One-Way ANOVA Table"; sheet.Cells[18, 1] = "Between Variation"; sheet.Cells[19, 1] = "Within Variation"; sheet.Cells[20, 1] = "Total Variation"; sheet.Cells[23, 1] = "Confidence Interval Tests"; sheet.Cells[16, 2] = "Sum of"; sheet.Cells[17, 2] = "Squares"; sheet.Cells[16, 3] = "Degrees of"; sheet.Cells[17, 3] = "Freedom"; sheet.Cells[16, 4] = "Mean"; sheet.Cells[17, 4] = "Squares"; sheet.Cells[17, 5] = "F-Ratio"; sheet.Cells[17, 6] = "p-Value"; sheet.Cells[22, 2] = "Difference"; sheet.Cells[23, 2] = " of Means"; int col = 1; foreach (Variable variable in variables) { col++; var range = variable.getRange().Address(true, true, true); sheet.Cells[9, col] = variable.name; sheet.WriteFunction(10, col, "COUNT(" + range + ")"); sheet.WriteFunction(11, col, "AVERAGE(" + range + ")"); sheet.WriteFunction(12, col, "STDEV.S(" + range + ")"); sheet.WriteFunction(13, col, "VAR.S(" + range + ")"); } sheet.WriteFunction(2, 2, "SUM(" + AddressConverter.CellAddress(10, 2, false, false) + ":" + AddressConverter.CellAddress(10, col, false, false) + ")"); sheet.WriteFunction(3, 2, "SUMPRODUCT(" + AddressConverter.CellAddress(10, 2, false, false) + ":" + AddressConverter.CellAddress(10, col, false, false) + "," + AddressConverter.CellAddress(11, 2, false, false) + ":" + AddressConverter.CellAddress(11, col, false, false) + ")/" + AddressConverter.CellAddress(2, 2, false, false)); sheet.Cells[6, 2] = variables.Count; sheet.Cells[7, 2] = model.confidenceLevel; ((Range)sheet.Cells[7, 2]).NumberFormat = "0.00%"; col = 1; foreach (Variable variable in variables) { col++; sheet.WriteFunction(14, col, "(" + AddressConverter.CellAddress(10, col, false, false) + "-1)/(B2-B6)"); } sheet.WriteFunction(5, 2, "SUMPRODUCT(" + AddressConverter.CellAddress(13, 2, false, false) + ":" + AddressConverter.CellAddress(13, col, false, false) + "," + AddressConverter.CellAddress(14, 2, false, false) + ":" + AddressConverter.CellAddress(14, col, false, false) + ")"); sheet.WriteFunction(4, 2, "SQRT(" + AddressConverter.CellAddress(5, 2, false, false) + ")"); sheet.WriteFunction(18, 2, "SUMPRODUCT(" + AddressConverter.CellAddress(10, 2, false, false) + ":" + AddressConverter.CellAddress(10, col, false, false) + ",(" + AddressConverter.CellAddress(11, 2, false, false) + ":" + AddressConverter.CellAddress(11, col, false, false) + "-B3)^2)"); sheet.WriteFunction(19, 2, "(B2-" + variables.Count + ")*B5"); sheet.WriteFunction(20, 2, "B18+B19"); sheet.Cells[18, 3] = variables.Count - 1; sheet.WriteFunction(19, 3, "B2-" + variables.Count); sheet.WriteFunction(20, 3, "C18+C19"); sheet.WriteFunction(18, 4, "B18/C18"); sheet.WriteFunction(19, 4, "B19/C19"); sheet.WriteFunction(18, 5, "D18/D19"); sheet.WriteFunction(18, 6, "F.DIST.RT(E18,C18,C19)"); int row = 24; int c = 0; for (int i = 0; i < variables.Count; i++) { for (int j = i + 1; j < variables.Count; j++) { c++; Variable var1 = variables[i]; Variable var2 = variables[j]; sheet.Cells[row, 1] = var1.name + " - " + var2.name; sheet.WriteFunction(row, 2, AddressConverter.CellAddress(11, i + 2, false, false) + "-" + AddressConverter.CellAddress(11, j + 2, false, false)); col = 3; if (model.noCorrection) { sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "-(ABS(T.INV((1-B7)/2,C19)))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))"); sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "+(ABS(T.INV((1-B7)/2,C19)))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))"); } if (model.bonferroni) { sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "-(ABS(T.INV(((1-B7)/(B6*(B6-1)/2))/2,C19)))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))"); sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "+(ABS(T.INV(((1-B7)/(B6*(B6-1)/2))/2,C19)))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))"); } if (model.scheffe) { sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "-SQRT((B6-1)*F.INV.RT(1-B7,B6-1,C19))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))"); sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "+SQRT((B6-1)*F.INV.RT(1-B7,B6-1,C19))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))"); } row++; } } col = 3; if (model.noCorrection) { sheet.Cells[22, col] = "No Correction"; sheet.get_Range(AddressConverter.CellAddress(22, col, false, false), AddressConverter.CellAddress(22, col + 1, false, false)).Merge(); sheet.Cells[23, col] = "Lower"; sheet.Cells[23, col + 1] = "Upper"; sheet.get_Range(AddressConverter.CellAddress(23, col, false, false), AddressConverter.CellAddress(23, col + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; sheet.get_Range(AddressConverter.CellAddress(24, col, false, false), AddressConverter.CellAddress(23 + c, col, false, false)).Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlDot; col += 2; } if (model.bonferroni) { sheet.Cells[22, col] = "Bonferroni"; sheet.get_Range(AddressConverter.CellAddress(22, col, false, false), AddressConverter.CellAddress(22, col + 1, false, false)).Merge(); sheet.Cells[23, col] = "Lower"; sheet.Cells[23, col + 1] = "Upper"; sheet.get_Range(AddressConverter.CellAddress(23, col, false, false), AddressConverter.CellAddress(23, col + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; sheet.get_Range(AddressConverter.CellAddress(24, col, false, false), AddressConverter.CellAddress(23 + c, col, false, false)).Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlDot; col += 2; } if (model.scheffe) { sheet.Cells[22, col] = "Scheffe"; sheet.get_Range(AddressConverter.CellAddress(22, col, false, false), AddressConverter.CellAddress(22, col + 1, false, false)).Merge(); sheet.Cells[23, col] = "Lower"; sheet.Cells[23, col + 1] = "Upper"; sheet.get_Range(AddressConverter.CellAddress(23, col, false, false), AddressConverter.CellAddress(23, col + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; sheet.get_Range(AddressConverter.CellAddress(24, col, false, false), AddressConverter.CellAddress(23 + c, col, false, false)).Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlDot; } ((Range)sheet.Cells[1, 1]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 2]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 3]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 4]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 5]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 6]).EntireColumn.AutoFit(); sheet.get_Range("B1", "J200").Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter; sheet.get_Range("A1", "B1").Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; sheet.get_Range("A9", AddressConverter.CellAddress(9, variables.Count + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; sheet.get_Range("A17", "F17").Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; sheet.get_Range("A23", "B17").Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; sheet.get_Range("B3", "B5").NumberFormat = "0.0000"; sheet.get_Range("B11", AddressConverter.CellAddress(14, variables.Count + 1, false, false)).NumberFormat = "0.000"; sheet.get_Range("B18", "B20").NumberFormat = "0.0000"; sheet.get_Range("D18", "E19").NumberFormat = "0.0000"; sheet.get_Range("B24", AddressConverter.CellAddress(23 + c, 2, false, false)).NumberFormat = "0.0000"; sheet.get_Range("C24", AddressConverter.CellAddress(23 + c, 8, false, false)).NumberFormat = "0.000000"; sheet.get_Range("C24", AddressConverter.CellAddress(23 + c, 8, false, false)).Cells.HorizontalAlignment = XlHAlign.xlHAlignRight; Globals.ExcelAddIn.Application.ActiveWindow.DisplayGridlines = false; }
public static MailMessage Read1MailFromExcel(_Worksheet excelSheet, int rowIndex, Action<String> ErrorOutput) { if (excelSheet.Cells[rowIndex, 1].Value2 != null) { MailMessage newMail = new MailMessage(); //加入寄件者 newMail.From = new MailAddress(((String)excelSheet.Cells[rowIndex, 1].Value2).Trim()); //加入收件者 if (excelSheet.Cells[rowIndex, 2].Value2 != null) { String[] Tos = ((String)excelSheet.Cells[rowIndex, 2].Value2).Split(',', ';', '\n'); foreach (var to in Tos) { MailAddress adress = null; try { adress = new MailAddress(to.Trim()); newMail.To.Add(adress); } catch { if (ErrorOutput != null) ErrorOutput(rowIndex + " Something wrong with Mail Adress : " + to); } } } //加入副本 if (excelSheet.Cells[rowIndex, 3].Value2 != null) { String[] CCs = ((String)excelSheet.Cells[rowIndex, 3].Value2).Split(',', ';', '\n'); foreach (var to in CCs) { MailAddress adress = null; try { adress = new MailAddress(to.Trim()); newMail.CC.Add(adress); } catch { if (ErrorOutput != null) ErrorOutput(rowIndex + " Something wrong with Mail Adress : " + to); } } } //加入密件副本 if (excelSheet.Cells[rowIndex, 4].Value2 != null) { String[] BCCs = ((String)excelSheet.Cells[rowIndex, 4].Value2).Split(',', ';', '\n'); foreach (var to in BCCs) { MailAddress adress = null; try { adress = new MailAddress(to.Trim()); newMail.Bcc.Add(adress); } catch { if (ErrorOutput != null) ErrorOutput(rowIndex + " Something wrong with Mail Adress : " + to); } } } //信件標題 if (excelSheet.Cells[rowIndex, 5].Value2 != null) { String Subject = excelSheet.Cells[rowIndex, 5].Value2; newMail.Subject = Subject; } else if (ErrorOutput != null) ErrorOutput(rowIndex + " Subject is null!"); //信件內容 if (excelSheet.Cells[rowIndex, 6].Value2 != null) { String Body = excelSheet.Cells[rowIndex, 6].Value2; newMail.Body = Body; } else if (ErrorOutput != null) ErrorOutput(rowIndex + " Body is null!"); //附件 String[] Attachments = ((String)excelSheet.Cells[rowIndex, 7].Value2)?.Split(',', ';', '\n'); if (excelSheet.Cells[rowIndex, 7].Value2 != null) { //[] Attachments = ((String)excelSheet.Cells[rowIndex, 7].Value2).Split(',', ';', '\n'); Attachments = (from filepath in Attachments select filepath.Trim()).ToArray(); foreach (var fileName in Attachments) if (File.Exists(@fileName)) { newMail.Attachments.Add(new Attachment(File.Open(@fileName, FileMode.Open, FileAccess.Read, FileShare.Read), Path.GetFileName(@fileName))); } else if (File.Exists(fileName)) { newMail.Attachments.Add(new Attachment(File.Open(fileName, FileMode.Open, FileAccess.Read, FileShare.Read), Path.GetFileName(fileName))); } else { if (ErrorOutput != null) ErrorOutput(rowIndex + " File not found : " + fileName + "!"); } } return newMail; } else { return null; } }
/// <summary> /// Gets layer data for WWT layer /// </summary> /// <param name="workSheet">Active worksheet</param> private void GetLayerDataForWWT(_Worksheet workSheet) { if (workSheet != null) { object[,] layerData = WWTManager.GetLayerData(this.currentWorkbookMap.SelectedLayerMap.LayerDetails.ID, false); if (layerData != null && layerData.Length > 0) { // Gets the range from the excel for data row and columns Range currentRange = workSheet.GetRange(ThisAddIn.ExcelApplication.ActiveCell, layerData.GetLength(0), layerData.GetLength(1)); if (currentRange != null) { if (ValidateAffectedMappedRange(workSheet, currentRange)) { string address = currentRange.Address; if (currentRange != null) { currentRange.Select(); InsertRows(currentRange); // Gets the new range for with the active cell address Range newRange = workSheet.Application.Range[address]; // Creates named range for the new range CreateRangeForLayer(newRange); newRange.SetValue(layerData); SetFormatForDateColumns(workSheet); } } } } } }
private void PrintCategories(_Worksheet _sheet, int rowIn, String input, DataSet dataSet) { double n = (_sheet.Cells[2, 2] as Range).Value; double p = (_sheet.Cells[3, 2] as Range).Value; double alpha = (double)model.alpha; int tail = 1; if (input.Equals("equal")) { alpha = alpha / 200.0; tail = 2; } else { alpha = alpha / 100.0; tail = 1; } double p0 = Convert.ToDouble(model.Null); double error = Math.Sqrt((p0 * (1 - p0)) / n); var row = rowIn; _sheet.Cells[row, 1] = "Hypothesized mean"; _sheet.Cells[row++, 2] = model.Null; _sheet.Cells[row, 1] = "Alternative Hypothesis"; if (input.Equals("equal")) { _sheet.Cells[row++, 2] = "'=/= " + model.Null; } else { _sheet.Cells[row++, 2] = "> " + model.Null; } _sheet.Cells[row, 1] = "Alpha"; _sheet.Cells[row++, 2] = (double)model.alpha / 100.0; _sheet.Cells[row, 1] = "Standard Error"; _sheet.Cells[row++, 2] = error; _sheet.Cells[row, 1] = "Z-Test Statistic"; double z = (p - p0) / error; _sheet.Cells[row++, 2] = z; _sheet.Cells[row, 1] = "p-Value"; double pValue = _sheet.Application.WorksheetFunction.NormSDist(z); _sheet.Cells[row++, 2] = pValue; _sheet.Cells[row, 1] = "Null Hypothesis"; if (pValue <= alpha) { _sheet.Cells[row, 2] = "Reject"; } else { _sheet.Cells[row, 2] = "Accept"; } }
private void axWebBrowser2_NavigateComplete2(object sender, AxSHDocVw.DWebBrowserEvents2_NavigateComplete2Event e) { /// return; object o = e.pDisp; oWebBrowser = e.pDisp; try { Object oDocument = o.GetType().InvokeMember("Document", BindingFlags.GetProperty, null, o, null); Object oApplication = o.GetType().InvokeMember("Application", BindingFlags.GetProperty, null, oDocument, null); Excel.Application eApp = (Excel.Application)oApplication; eApp.UserControl = true; //Inputexcel(eApp); //textexcel(); #region 方法2 //Object refmissing = System.Reflection.Missing.Value; //object[] args = new object[4]; //args[0] = SHDocVw.OLECMDID.OLECMDID_HIDETOOLBARS; //args[1] = SHDocVw.OLECMDEXECOPT.OLECMDEXECOPT_DONTPROMPTUSER; //args[2] = refmissing; //args[3] = refmissing; //object axWebBrowser = this.webBrowser1.ActiveXInstance; //axWebBrowser.GetType().InvokeMember("ExecWB", // BindingFlags.InvokeMethod, null, axWebBrowser, args); //object Application = axWebBrowser.GetType().InvokeMember("Document", // BindingFlags.GetProperty, null, axWebBrowser, null); //Excel.Workbook wbb = (Excel.Workbook)oApplication; //Excel.ApplicationClass excel = wbb.Application as Excel.ApplicationClass; //Excel.Workbook wb = excel.Workbooks[1]; //Excel.Worksheet ws = wb.Worksheets[1] as Excel.Worksheet; //ws.Cells.Font.Name = "Verdana"; //ws.Cells.Font.Size = 14; //ws.Cells.Font.Bold = true; //Excel.Range range = ws.Cells; //Excel.Range oCell = range[10, 10] as Excel.Range; //oCell.Value2 = "你好"; #endregion #region inster tx //object objBooks = eApp.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, eApp, null); ////添加一个新的Workbook //object objBook = objBooks.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, objBooks, null); ////获取Sheet集 //object objSheets = objBook.GetType().InvokeMember("Worksheets", BindingFlags.GetProperty, null, objBook, null); ////获取第一个Sheet对象 //object[] Parameters = new Object[1] { 1 }; //object objSheet = objSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, objSheets, Parameters); //Parameters = new Object[2] { 1, 1 + 1 }; //object objCells = objSheet.GetType().InvokeMember("Cells", BindingFlags.GetProperty, null, objSheet, Parameters); ////向指定单元格填写内容值 //Parameters = new Object[1] { "name" }; //objCells.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, objCells, Parameters); #endregion #region 一、首先简要回顾一下如何操作Excel表 Workbooks workbooks = eApp.Workbooks; Excel.ApplicationClass excel = workbooks.Application as Excel.ApplicationClass; Microsoft.Office.Interop.Excel.Worksheet WS = (Microsoft.Office.Interop.Excel.Worksheet)workbooks.get_Item(1); Excel.Workbook wb = excel.Workbooks[1]; //_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet); int c = workbooks.Count; _Workbook workbook = workbooks.Add(ZFCEPath); Sheets sheets = workbook.Worksheets; _Worksheet worksheet = (_Worksheet)sheets.get_Item(1); Range range1 = worksheet.get_Range("A1", Missing.Value); const int nCells = 2345; range1.Value2 = nCells; #endregion ExcelExit(); } catch (Exception ex) { ExcelExit(); throw; } }
private void PrintCategories2(_Worksheet _sheet, int rowIn, String input, DataSet dataSet) { double n = (_sheet.Cells[2, 2] as Range).Value; double n1 = (_sheet.Cells[2, 3] as Range).Value; double p = (_sheet.Cells[3, 2] as Range).Value; double p1 = (_sheet.Cells[3, 3] as Range).Value; double alpha = (double)model.alpha; int tail = 1; if (input.Equals("equal")) { alpha = alpha / 200.0; tail = 2; } else { alpha = alpha / 100.0; tail = 1; } var row = rowIn; _sheet.Cells[row, 1] = "Pooled Proportion"; double pooled = ((int)(n * p) + (int)(n1 * p1)) / (n + n1); double error = Math.Sqrt((pooled * (1 - pooled)) * (1.0 / n + 1.0 / n1)); _sheet.Cells[row++, 2] = pooled; _sheet.Cells[row, 1] = "Difference Between Proportions"; _sheet.Cells[row++, 2] = p - p1; _sheet.Cells[row, 1] = "Hypothesized mean"; _sheet.Cells[row++, 2] = 0; _sheet.Cells[row, 1] = "Alternative Hypothesis"; if (input.Equals("equal")) { _sheet.Cells[row++, 2] = "'=/= " + 0; } else { _sheet.Cells[row++, 2] = "> " + 0; } _sheet.Cells[row, 1] = "Alpha"; _sheet.Cells[row++, 2] = (double)model.alpha / 100.0; _sheet.Cells[row, 1] = "Standard Error"; _sheet.Cells[row++, 2] = error; _sheet.Cells[row, 1] = "Test Statistic"; double z = (p - p1) / error; _sheet.Cells[row++, 2] = z; _sheet.Cells[row, 1] = "p-Value"; double pValue = _sheet.Application.WorksheetFunction.TDist(z, n - 1, tail); //double pValue = _sheet.Application.WorksheetFunction.NormSDist(z); _sheet.Cells[row++, 2] = pValue; _sheet.Cells[row, 1] = "Null Hypothesis"; if (pValue <= alpha) { _sheet.Cells[row, 2] = "Reject"; } else { _sheet.Cells[row, 2] = "Accept"; } }
public void AEExportDataToExcel(string fileName, string[] tableNames, string[] sheetNames, DataSet ds, int[] irow, DateTime generateDate) { PreExitExcel(); string filename1 = fileName.Substring(0, fileName.LastIndexOf(".")); try { _ExcelApp = new Microsoft.Office.Interop.Excel.Application(); _ExcelWBook = (_Workbook)(_ExcelApp.Workbooks.Open(excelTemplatePath + filename1, 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)); for (int i = 0; i < tableNames.Length; i++) { _IniRow = irow[i]; switch (tableNames[i].ToString()) { case "AnnuityOpenPeriod": case "AnnuityUrgent": case "AnnuityStatusUrgent": _SName = "AnnuityFinal"; break; case "AnnuityRpt": _SName = "AnnuityRpt"; break; case "AnnuityStatus": _SName = "AnnuityStatus"; break; case "EBusinessRpt": _SName = "EBusinessRpt Grouped"; break; default: ; break; } _ExcelWSheet = (_Worksheet)_ExcelWBook.Sheets[_SName]; for (int j = 0; j < ds.Tables[tableNames[i]].Rows.Count; j++) { for (int k = 0; k < ds.Tables[tableNames[i]].Columns.Count; k++) { _ExcelWSheet.Cells[_IniRow, k + 1] = ds.Tables[tableNames[i]].Rows[j][k].ToString(); } _IniRow++; } } string strDate = generateDate.AddDays(-1).ToString("MMdd"); ExcelHelper excelHelper = new ExcelHelper(); _fullFilePath = string.Format(excelHelper.getFilename(generateDate) + "\\{0}-{1}.xls", filename1, strDate); _ExcelApp.Rows.RowHeight = "15"; _ExcelWBook.SaveAs(_fullFilePath, XlFileFormat.xlWorkbookNormal, null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, null, null, null); _ExcelApp.DisplayAlerts = false; _ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value); _ExcelApp.Workbooks.Close(); _ExcelApp.Quit(); Marshal.ReleaseComObject(_ExcelWSheet); Marshal.ReleaseComObject(_ExcelWBook); Marshal.ReleaseComObject(_ExcelApp); } catch (Exception e) { _ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value); _ExcelApp.Workbooks.Close(); _ExcelApp.Quit(); throw e; } }
public void createHypothese(List <Variable> variables, DataSet dataSet) { _Worksheet worksheet = WorksheetHelper.NewWorksheet("Hypothesis test"); if (variables.Count == 1) { foreach (Variable variable in variables) { worksheet.Cells[1, 1] = "Hypothesis test"; worksheet.Cells[1, 2] = variable.name; worksheet.Cells[2, 1] = "Sample size"; worksheet.Cells[2, 2] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variable.Range + ")"; worksheet.Cells[3, 1] = "Sample proportion"; string ran = variable.Range.ToString(); Array dist = dataSet.getWorksheet().Range[ran].Value; double count = 0; foreach (var item in dist) { if (Convert.ToInt32(item) == 0) { System.Diagnostics.Debug.WriteLine(item); count = count + 1; } } double n = (worksheet.Cells[2, 2] as Range).Value; double prop = count / n; worksheet.Cells[3, 2] = prop; if (model.equal) { PrintCategories(worksheet, 5, "equal", dataSet); } else if (model.greater) { PrintCategories(worksheet, 5, "greater", dataSet); } } } else if (variables.Count == 2) { worksheet.Cells[1, 1] = "Hypothesis test"; worksheet.Cells[1, 2] = variables[0].name; worksheet.Cells[1, 3] = variables[1].name; worksheet.Cells[2, 1] = "Sample size"; worksheet.Cells[2, 2] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + ")"; worksheet.Cells[2, 3] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variables[1].Range + ")"; worksheet.Cells[3, 1] = "Sample proportion"; string ran = variables[0].Range.ToString(); string ran1 = variables[1].Range.ToString(); Array dist = dataSet.getWorksheet().Range[ran].Value; Array dist1 = dataSet.getWorksheet().Range[ran1].Value; double count = 0; double count1 = 0; foreach (var item in dist) { if (Convert.ToInt32(item) == 0) { System.Diagnostics.Debug.WriteLine(item); count = count + 1; } } foreach (var item in dist1) { if (Convert.ToInt32(item) == 0) { System.Diagnostics.Debug.WriteLine(item); count1 = count1 + 1; } } double n = (worksheet.Cells[2, 2] as Range).Value; double n1 = (worksheet.Cells[2, 3] as Range).Value; double prop = count / n; double prop1 = count1 / n1; worksheet.Cells[3, 2] = prop; worksheet.Cells[3, 3] = prop1; if (model.equal) { PrintCategories2(worksheet, 5, "equal", dataSet); } else if (model.greater) { PrintCategories2(worksheet, 5, "greater", dataSet); } } }
public static bool ExportForDataGridview(System.Data.DataTable dt, string fileName, bool isShowExcle) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); try { if (app == null) { return(false); } app.Visible = isShowExcle; Workbooks workbooks = app.Workbooks; _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet); Sheets sheets = workbook.Worksheets; _Worksheet worksheet = (_Worksheet)sheets.get_Item(1); if (worksheet == null) { return(false); } string sLen = ""; //取得最后一列列名 char H = (char)(64 + dt.Columns.Count / 26); char L = (char)(64 + dt.Columns.Count % 26); if (dt.Columns.Count < 26) { sLen = L.ToString(); } else { sLen = H.ToString() + L.ToString(); } //标题 string sTmp = sLen + "1"; Range ranCaption = worksheet.get_Range(sTmp, "A1"); string[] asCaption = new string[dt.Columns.Count]; for (int i = 0; i < dt.Columns.Count; i++) { asCaption[i] = dt.Columns[i].ColumnName; } ranCaption.Value2 = asCaption; //数据 object[] obj = new object[dt.Columns.Count]; for (int r = 0; r < dt.Rows.Count - 1; r++) { for (int l = 0; l < dt.Columns.Count; l++) { if (dt.Rows[r][l].GetType() == typeof(DateTime)) { obj[l] = dt.Rows[r][l].ToString(); } else { obj[l] = dt.Rows[r][l].ToString(); } } string cell1 = sLen + ((int)(r + 2)).ToString(); string cell2 = "A" + ((int)(r + 2)).ToString(); Range ran = worksheet.get_Range(cell1, cell2); ran.Value2 = obj; } //保存 workbook.SaveCopyAs(fileName); workbook.Saved = true; } finally { //关闭 app.UserControl = false; app.Quit(); } return(true); }
public PercentPoorEntireStateDeckArea(string networkId, string simulationId, _Worksheet oSheet, BridgeAnalysis analysis) : base(networkId, simulationId, oSheet, analysis) { }
public static void OutputExcelChk_Microsoft(DataGridView dgvCarPlay, string Title, string savePath) { Microsoft.Office.Interop.Excel.Application o = null; try { o = new ApplicationClass(); } catch (Exception exception) { MessageBox.Show("Excel无法启动,请确保已安装Excel 2003 及以上版本!"); Record.execFileRecord("导出报表", exception.ToString()); return; } try { int num = 4; int num2 = 1; int num3 = 0; int num4 = 2; _Workbook workbook = o.Workbooks.Add(true); _Worksheet activeSheet = (_Worksheet)workbook.ActiveSheet; activeSheet.Name = Title; num3 = num2; foreach (DataGridViewColumn column in dgvCarPlay.Columns) { if (column.Visible && (column.CellType != typeof(DataGridViewCheckBoxCell))) { num3++; activeSheet.Cells[num, num3] = column.HeaderText; activeSheet.get_Range(activeSheet.Cells[num, num3], activeSheet.Cells[num, num3]).HorizontalAlignment = XlVAlign.xlVAlignCenter; if (column.CellType == typeof(DataGridViewTextBoxCell)) { activeSheet.get_Range(activeSheet.Cells[num, num3], activeSheet.Cells[num + dgvCarPlay.Rows.Count, num3]).HorizontalAlignment = XlVAlign.xlVAlignCenter; } } } foreach (DataGridViewRow row in (IEnumerable)dgvCarPlay.Rows) { if ((row.Cells["colSelect"].Value != null) && "true".Equals(row.Cells["colSelect"].Value.ToString().ToLower())) { num++; num3 = num2; foreach (DataGridViewColumn column2 in dgvCarPlay.Columns) { if (column2.Visible && (column2.CellType != typeof(DataGridViewCheckBoxCell))) { num3++; activeSheet.Cells[num, num3] = "'" + row.Cells[column2.Index].Value; } } continue; } } int num5 = num + 1; num3 = num2; foreach (DataGridViewColumn column3 in dgvCarPlay.Columns) { if (column3.Visible) { num3++; } } num5 = num + 2; activeSheet.Cells[num5, 2] = "合计:"; activeSheet.get_Range(activeSheet.Cells[num5, 2], activeSheet.Cells[num5, 2]).HorizontalAlignment = XlHAlign.xlHAlignCenter; activeSheet.Cells[num5, 3] = ((num - 4)).ToString() + "条记录"; activeSheet.get_Range(activeSheet.Cells[num5, 3], activeSheet.Cells[num5, 3]).HorizontalAlignment = XlHAlign.xlHAlignLeft; activeSheet.Cells[2, num4] = Title; num4 = 1; activeSheet.get_Range(activeSheet.Cells[2, num4], activeSheet.Cells[2, num4]).Font.Bold = true; activeSheet.get_Range(activeSheet.Cells[2, num4], activeSheet.Cells[2, num4]).Font.Size = 0x16; activeSheet.get_Range(activeSheet.Cells[2, num4], activeSheet.Cells[2, num4]).Font.ColorIndex = 14; activeSheet.get_Range(activeSheet.Cells[4, num4], activeSheet.Cells[num5, num3]).Select(); activeSheet.get_Range(activeSheet.Cells[4, num4], activeSheet.Cells[num5, num3]).Columns.AutoFit(); activeSheet.get_Range(activeSheet.Cells[2, num4], activeSheet.Cells[2, num3]).Select(); activeSheet.get_Range(activeSheet.Cells[2, num4], activeSheet.Cells[2, num3]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection; if (string.IsNullOrEmpty(savePath)) { savePath = @"C:\Documents and Settings\Administrator\My Documents\Report.xls"; } workbook.SaveCopyAs(savePath); workbook.Close(false, null, null); o.Quit(); Marshal.ReleaseComObject(workbook); Marshal.ReleaseComObject(o); Marshal.ReleaseComObject(activeSheet); workbook = null; o = null; activeSheet = null; GC.Collect(); MessageBox.Show("数据已经成功导出到:" + savePath, "导出完成", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); } catch (Exception exception2) { MessageBox.Show(exception2.Message); } }
public static string GetMkbDataFromFile(string mkbFilePath) { CultureInfo oldCi = Thread.CurrentThread.CurrentCulture; try { Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US"); _excelProcessesBeforeStartWork = Process.GetProcessesByName("EXCEL"); // Стартуем Excel-приложение _oxl = new Application(); _oxb = _oxl.Workbooks.Open(mkbFilePath, 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); object[,] ksgData = GetKsgData(); try { _ows = (_Worksheet)_oxb.Sheets["МКБ 10"]; } catch { throw new Exception("Лист с именем 'МКБ 10' не найден. Вероятно, указанный файл имеет неверный формат."); } int rowCnt = 2; int shag = 10000; while (shag > 0) { while (((Range)_ows.Cells[rowCnt, 1]).Value2 != null) { rowCnt += shag; } rowCnt -= shag; shag /= 10; } var data = (object[, ])_ows.get_Range(_ows.Cells[2, 1], _ows.Cells[rowCnt, 3]).Value2; var sb = new StringBuilder(); for (int i = 1; i < rowCnt; i++) { string mkbCode = data[i, 1]?.ToString(); if (string.IsNullOrEmpty(mkbCode)) { continue; } sb.AppendFormat("Code={0}{4}Name={1}{4}KsgCode={2}{4}KsgName={3}{5}", mkbCode, data[i, 2], data[i, 3], GetKsgNameByCode(ksgData, data[i, 3]?.ToString()), DbEngine.DataSplitStr, DbEngine.ObjSplitStr); } return(sb.ToString()); } catch (Exception ex) { throw new Exception("В процессе загрузки кодов МКБ произошла ошибка:\r\n" + ex); } finally { QuitExcel(); Thread.CurrentThread.CurrentCulture = oldCi; } }
/// <summary> /// Activates a worksheet on the current active workbook via its name. /// </summary> /// <param name="name">The name of the workbook</param> /// <returns>true if the worksheet was found, false otherways</returns> public bool activateWorksheet(string name) { foreach (_Worksheet worksheet in excel.ActiveWorkbook.Sheets) { if (worksheet.Name == name) { worksheet.Activate(); this.worksheet = worksheet; return true; } } return false; }
private static void QuitExcel() { if (_oxl != null) { if (_oxb != null) { _oxb.Close(false, Missing.Value, Missing.Value); if (_ows != null) { Marshal.ReleaseComObject(_ows); GC.GetTotalMemory(true); _ows = null; } if (_owr != null) { Marshal.ReleaseComObject(_owr); GC.GetTotalMemory(true); _owr = null; } Marshal.ReleaseComObject(_oxb); GC.GetTotalMemory(true); _oxb = null; } _oxl.Quit(); Marshal.ReleaseComObject(_oxl); GC.GetTotalMemory(true); _oxl = null; GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); Thread.Sleep(100); } _excelProcessesAfterStartWork = Process.GetProcessesByName("EXCEL"); // // Find created process and kill it, if standart close is not successful // for (int i = 0; i < _excelProcessesAfterStartWork.Length; i++) { bool flag = true; for (int j = 0; j < _excelProcessesBeforeStartWork.Length; j++) { if (_excelProcessesBeforeStartWork[j].Id == _excelProcessesAfterStartWork[i].Id) { flag = false; break; } } if (flag) { WinExec("taskkill /F /PID " + _excelProcessesAfterStartWork[i].Id, 0); } } }
//读取数据 private void btRead_Click(object sender, EventArgs e) { int MAXLINE = 5000; int i = 0, j = 0, k = 0, m = 0;//m为总行数 int fileCount = lvFile.Items.Count; string DataTag; int eCount = 0;//有效工作簿数 int sCount = 0;//当前表中工作簿数 Point point; Object missing = Type.Missing; int iCount = lbContent.Items.Count; //重点区域,范围型读取单元格区域 RangeSelector mainRange = new RangeSelector(tbMainRange.Text); //预判断块读取还是固定位置读取,初始化总数组大小 if (mainRange.getWidth() > 0) myArray = new String[MAXLINE, mainRange.getWidth() + iCount + 1];//最多千行 else myArray = new String[MAXLINE, iCount + 1];//最多千行 //開啟一個新的應用程式 myExcel = new Excel.Application(); for (i = 0; i < fileCount; i++) { //停用警告訊息 myExcel.DisplayAlerts = false; //讓Excel文件可見 myExcel.Visible = true; //引用第一個活頁簿 myBook = myExcel.Workbooks.Open(lvFile.Items[i].SubItems[2].Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); //設定活頁簿焦點 myBook.Activate(); //判断所有工作簿 sCount = myBook.Worksheets.Count; for (k = 1; k <= sCount; k++) { //大表判断条件 if (cbSheetSelect.Text != "全部" && Int16.Parse(cbSheetSelect.Text) != k) continue; //选择当前表 mySheet = (Worksheet)myBook.Worksheets[k]; //設工作表焦點 mySheet.Activate(); //特征值判断 if (tbSheetPos.Text != "") { point = pointPos(tbSheetPos.Text); if (mySheet.Cells[point.Y, point.X].Value != tbSheetCont.Text) continue; } eCount++; //备注列判断 if (tbDataTag.Text == "") DataTag = lvFile.Items[i].SubItems[0].Text; //未设置备注默认使用文件名 else { Point tagpos = pointPos(tbDataTag.Text); DataTag = Convert.ToString(mySheet.Cells[tagpos.Y, tagpos.X].Value); } string mainStart = tbMainStart.Text; string mainEnd = tbMainEnd.Text; //判断选择哪种模式 if (mainRange.Count() > 1) { mainRange = new RangeSelector(tbMainRange.Text);//重新恢复原区域值 //重点区域起始位置判断 Point nowPos = mainRange.getCurPos(); for (j = 0; j < mainRange.Count(); j++) { string myCell = Convert.ToString(mySheet.Cells[nowPos.Y, nowPos.X].Value); if (mainStart == "") break; if (myCell == mainStart) break; mainRange.acc(); } //mainRange.lineacc(); //移到关键字下一行 mainRange.SetStartVal(mainRange.getCurPos()); //读取内容 while (m < MAXLINE) //最大读取行数上限估计 { nowPos = mainRange.getCurPos(); string lineFirstCell = Convert.ToString(mySheet.Cells[nowPos.Y, nowPos.X].Value); if (lineFirstCell == null|| lineFirstCell=="") break; //首字为空 if (lineFirstCell == tbMainEnd.Text) break; //符合结束字符串 if (mainRange.pos > mainRange.Count()) break;//读取完了就退出 for (j = 0; j < mainRange.getWidth(); j++)//读取一行 { point = mainRange.getCurPos(); myArray[m, j] = Convert.ToString(mySheet.Cells[point.Y, point.X].Value); //不管什么类型都转为字符串 mainRange.acc(); } myArray[m, j] = DataTag; m++; } } else { //准备读取单元格相关信息,固定位置读取单元格 if (iCount >= 1) { List<Array> ListOfLine = new List<Array>(); //所有的读取行集合 String[] myLine = new String[iCount]; //单行对象 RangeSelector[] rsContentA = new RangeSelector[iCount]; for (j = 0; j < iCount; j++) { rsContentA[j] = new RangeSelector(lbContent.Items[j].ToString()); } j = 0; foreach (RangeSelector cont in rsContentA) { cont.acc(); point = cont.getCurPos(); myArray[m, j] = Convert.ToString(mySheet.Cells[point.Y, point.X].Value); //不管什么类型都转为字符串 j++; if (j > iCount) break;//xxxxxxx } myArray[m, j - 1] = DataTag; m++; } } } //关闭当前活页簿 myBook.Close(); System.Windows.Forms.Application.DoEvents(); } myExcel.Quit(); }
public static string GetServiceDataFromFile(string servicesFilePath) { CultureInfo oldCi = Thread.CurrentThread.CurrentCulture; try { Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US"); _excelProcessesBeforeStartWork = Process.GetProcessesByName("EXCEL"); // Стартуем Excel-приложение _oxl = new Application(); _oxb = _oxl.Workbooks.Open(servicesFilePath, 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); try { _ows = (_Worksheet)_oxb.Sheets["Группировщик детальный"]; } catch { throw new Exception("Лист с именем 'Группировщик детальный' не найден. Вероятно, указанный файл имеет неверный формат."); } int rowCnt = 2; int shag = 10000; while (shag > 0) { while (((Range)_ows.Cells[rowCnt, 1]).Value2 != null) { rowCnt += shag; } rowCnt -= shag; shag /= 10; } var data = (object[, ])_ows.get_Range(_ows.Cells[2, 6], _ows.Cells[rowCnt, 14]).Value2; var sb = new StringBuilder(); for (int i = 1; i < rowCnt; i++) { string service = data[i, 2]?.ToString(); if (string.IsNullOrEmpty(service)) { continue; } sb.AppendFormat("{0};{1};{2};{3}^", service, data[i, 1], data[i, 8], data[i, 9]); } return(sb.ToString()); } catch (Exception ex) { throw new Exception("В процессе загрузки данных с услугами произошла ошибка:\r\n" + ex); } finally { QuitExcel(); Thread.CurrentThread.CurrentCulture = oldCi; } }
private void CreateExcel(DateTime date) { DateTime dtNow = DateTime.Now; bool createdNew = false; using (Mutex mutex = new Mutex(false, "Global\\CreateExcel", out createdNew)) { var allowEveryoneRule = new MutexAccessRule(new SecurityIdentifier(WellKnownSidType.WorldSid, null), MutexRights.FullControl, AccessControlType.Allow); var securitySettings = new MutexSecurity(); securitySettings.AddAccessRule(allowEveryoneRule); mutex.SetAccessControl(securitySettings); bool hasHandle = false; try { try { hasHandle = mutex.WaitOne(); if (hasHandle == false) throw new TimeoutException("Timeout waiting for exclusive access"); } catch (AbandonedMutexException) { hasHandle = true; } objExcelSummary = new Application(); objWorkbookSummary = objExcelSummary.Workbooks.Add(System.Reflection.Missing.Value); string spacer = Convert.ToChar(32).ToString() + Convert.ToChar(32).ToString() + Convert.ToChar(32).ToString() + Convert.ToChar(32).ToString() + Convert.ToChar(32).ToString() + Convert.ToChar(32).ToString() + Convert.ToChar(32).ToString(); // DELETE EXTRA SHEETS //((Worksheet)objWorkbookSummary.Sheets[3]).Delete(); //((Worksheet)objWorkbookSummary.Sheets[2]).Delete(); objSummary = ((Worksheet)objWorkbookSummary.Sheets[1]); objSummary.Name = "CALLS"; objSummary.PageSetup.PrintGridlines = false; objSummary.PageSetup.CenterHorizontally = true; objSummary.PageSetup.CenterVertically = false; objSummary.PageSetup.FitToPagesWide = 1; objSummary.PageSetup.FitToPagesTall = false; objSummary.PageSetup.Zoom = false; //objSummary.PageSetup.Zoom = 100; objSummary.PageSetup.TopMargin = objExcelSummary.Application.InchesToPoints(0.50); objSummary.PageSetup.BottomMargin = objExcelSummary.Application.InchesToPoints(0.25); objSummary.PageSetup.LeftMargin = objExcelSummary.Application.InchesToPoints(0.25); objSummary.PageSetup.RightMargin = objExcelSummary.Application.InchesToPoints(0.25); //0.25 objSummary.PageSetup.Orientation = XlPageOrientation.xlLandscape; objSummary.PageSetup.HeaderMargin = objExcelSummary.Application.InchesToPoints(0.00); objSummary.PageSetup.FooterMargin = objExcelSummary.Application.InchesToPoints(0.00); //objSummary.PageSetup.RightFooter = "Page &P of &N"; //objSummary.PageSetup.RightFooter = "Run date for " + date.ToString("MMM/dd"); objSummary.PageSetup.RightFooter = "Run-Date: " + dtNow.ToString("MMM") + " " + dtNow.Day + GetOrdinal(dtNow.Day) + ", " + dtNow.Year + " for " + date.ToString("MMM") + " " + date.Day + GetOrdinal(date.Day) + ", " + date.Year + Convert.ToChar(32) + Convert.ToChar(32) + Convert.ToChar(32) + Convert.ToChar(13).ToString() + "Page &P of &N" + Convert.ToChar(32) + Convert.ToChar(32) + Convert.ToChar(32) + Convert.ToChar(13).ToString(); } finally { if (hasHandle) mutex.ReleaseMutex(); } } }
public static void Export(List <PatientClass> patientList, DbEngine dbEngine) { CultureInfo oldCi = Thread.CurrentThread.CurrentCulture; try { Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US"); // Стартуем Excel-приложение _oxl = new Application(); // Создаем новую книгу _oxb = _oxl.Workbooks.Add(Missing.Value); int len = _oxb.Sheets.Count; for (int i = len; i > 1; i--) { ((_Worksheet)_oxb.Sheets[i]).Delete(); } _ows = (_Worksheet)_oxb.Sheets[1]; _ows.Cells.WrapText = true; _ows.Cells.VerticalAlignment = 2; _ows.Cells.HorizontalAlignment = 2; _owr = _ows.get_Range("A1", "Q1"); _owr.MergeCells = true; _owr.Font.Bold = true; _owr.Font.Size = 14; _owr.RowHeight = 30; _owr.HorizontalAlignment = 3; _ows.Cells[1, 1] = "Список пациентов на " + ConvertEngine.GetRightDateString(DateTime.Now); _owr = _ows.get_Range("R1", "AB1"); _owr.MergeCells = true; _owr.Font.Bold = true; _owr.Font.Size = 14; _owr.RowHeight = 30; _owr.HorizontalAlignment = 3; _ows.Cells[1, 18] = "Данные по операциям"; _owr = _ows.get_Range("A2", "A2"); _owr.ColumnWidth = 15; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "ФИО пациента"; _owr = _ows.get_Range("B2", "B2"); _owr.ColumnWidth = 4; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Возраст"; _owr = _ows.get_Range("C2", "C2"); _owr.ColumnWidth = 10; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Дата рождения"; _owr = _ows.get_Range("D2", "D2"); _owr.ColumnWidth = 14; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Адрес"; _owr = _ows.get_Range("E2", "E2"); _owr.ColumnWidth = 14; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Место работы"; _owr = _ows.get_Range("F2", "F2"); _owr.ColumnWidth = 9; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Телефон"; _owr = _ows.get_Range("G2", "G2"); _owr.ColumnWidth = 9; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Тип стационара"; _owr = _ows.get_Range("H2", "H2"); _owr.ColumnWidth = 9; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Код МКБ"; _owr = _ows.get_Range("I2", "I2"); _owr.ColumnWidth = 9; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Код услуги"; _owr = _ows.get_Range("J2", "J2"); _owr.ColumnWidth = 20; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Название услуги"; _owr = _ows.get_Range("K2", "K2"); _owr.ColumnWidth = 13; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Нозология"; _owr = _ows.get_Range("L2", "L2"); _owr.ColumnWidth = 10; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Лечащий врач"; _owr = _ows.get_Range("M2", "M2"); _owr.ColumnWidth = 9; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Дата поступления"; _owr = _ows.get_Range("N2", "N2"); _owr.ColumnWidth = 10; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Дата выписки"; _owr = _ows.get_Range("O2", "O2"); _owr.ColumnWidth = 7; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "№ истории болезни"; _owr = _ows.get_Range("P2", "P2"); _owr.ColumnWidth = 5; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Количество операций"; _owr = _ows.get_Range("Q2", "Q2"); _owr.ColumnWidth = 25; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Диагноз"; _owr = _ows.get_Range("R2", "R2"); _owr.ColumnWidth = 22; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Название операции"; _owr = _ows.get_Range("S2", "S2"); _owr.ColumnWidth = 10; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Дата операции"; _owr = _ows.get_Range("T2", "T2"); _owr.ColumnWidth = 6; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Время начала операции"; _owr = _ows.get_Range("U2", "U2"); _owr.ColumnWidth = 6; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Время окончания операции"; _owr = _ows.get_Range("V2", "V2"); _owr.ColumnWidth = 10; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Список хирургов"; _owr = _ows.get_Range("W2", "W2"); _owr.ColumnWidth = 12; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Список ассистентов"; _owr = _ows.get_Range("X2", "X2"); _owr.ColumnWidth = 8; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Анестезист"; _owr = _ows.get_Range("Y2", "Y2"); _owr.ColumnWidth = 8; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Анестезистка"; _owr = _ows.get_Range("Z2", "Z2"); _owr.ColumnWidth = 8; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Операц. мед. сестра"; _owr = _ows.get_Range("AA2", "AA2"); _owr.ColumnWidth = 8; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Санитар"; _owr = _ows.get_Range("AB2", "AB2"); _owr.ColumnWidth = 85; _owr.Font.Bold = true; _owr.HorizontalAlignment = 3; _owr.Value2 = "Ход операции"; int rowCnt = 3; for (int i = 0; i < patientList.Count; i++) { _ows.Cells[rowCnt, 1] = patientList[i].GetFullName(); _ows.Cells[rowCnt, 2] = patientList[i].Age; _ows.Cells[rowCnt, 3] = ConvertEngine.GetRightDateString(patientList[i].Birthday, false); _ows.Cells[rowCnt, 4] = patientList[i].GetAddress(); _ows.Cells[rowCnt, 5] = patientList[i].WorkPlace; _ows.Cells[rowCnt, 6] = patientList[i].Phone; _ows.Cells[rowCnt, 7] = patientList[i].TypeOfKSG; _ows.Cells[rowCnt, 8] = patientList[i].MKB; _ows.Cells[rowCnt, 9] = patientList[i].ServiceCode; _ows.Cells[rowCnt, 10] = patientList[i].ServiceName; _ows.Cells[rowCnt, 11] = patientList[i].Nosology; _ows.Cells[rowCnt, 12] = patientList[i].DoctorInChargeOfTheCase; _ows.Cells[rowCnt, 13] = ConvertEngine.GetRightDateString(patientList[i].DeliveryDate, true); if (patientList[i].ReleaseDate.HasValue) { _ows.Cells[rowCnt, 14] = ConvertEngine.GetRightDateString(patientList[i].ReleaseDate.Value, true); } _ows.Cells[rowCnt, 15] = patientList[i].NumberOfCaseHistory; _ows.Cells[rowCnt, 16] = patientList[i].Operations.Count.ToString(); _ows.Cells[rowCnt, 17] = patientList[i].Diagnose; if (patientList[i].Operations.Count == 0) { rowCnt++; } else { foreach (OperationClass operationInfo in patientList[i].Operations) { _ows.Cells[rowCnt, 18] = operationInfo.Name; _ows.Cells[rowCnt, 19] = ConvertEngine.GetRightDateString(operationInfo.DataOfOperation); _ows.Cells[rowCnt, 20] = ConvertEngine.GetRightTimeString(operationInfo.StartTimeOfOperation); _ows.Cells[rowCnt, 21] = ConvertEngine.GetRightTimeString(operationInfo.EndTimeOfOperation); _ows.Cells[rowCnt, 22] = ListToString(operationInfo.Surgeons); _ows.Cells[rowCnt, 23] = ListToString(operationInfo.Assistents); _ows.Cells[rowCnt, 24] = operationInfo.HeAnaesthetist; _ows.Cells[rowCnt, 25] = operationInfo.SheAnaesthetist; _ows.Cells[rowCnt, 26] = operationInfo.ScrubNurse; _ows.Cells[rowCnt, 27] = operationInfo.Orderly; _ows.Cells[rowCnt, 28] = operationInfo.OperationCourse; rowCnt++; } } } } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { if (_oxl != null) { _oxl.Visible = true; _oxl.UserControl = true; if (_oxb != null) { Marshal.ReleaseComObject(_oxb); _oxb = null; } if (_ows != null) { Marshal.ReleaseComObject(_ows); _ows = null; } if (_owr != null) { Marshal.ReleaseComObject(_owr); _owr = null; } Marshal.ReleaseComObject(_oxl); _oxl = null; GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); } Thread.CurrentThread.CurrentCulture = oldCi; } }
private void GetLayerDataForLocalInWWT(_Worksheet workSheet) { if (workSheet != null && this.currentWorkbookMap.SelectedLayerMap != null) { Range currentLayerRange = workSheet.Application.Range[this.currentWorkbookMap.SelectedLayerMap.RangeAddress]; Range validationRange = currentLayerRange; if (currentLayerRange != null) { object[,] layerData = WWTManager.GetLayerData(this.currentWorkbookMap.SelectedLayerMap.LayerDetails.ID, false); if (layerData != null && layerData.Length > 0) { int difference = 0; Range rowDifferenceRange = null, columnDifferenceRange = null; string rowaddress = string.Empty, colAddress = string.Empty; // 1. Gets the difference from the current layer range // 2. Gets the range with the first row cell // 3. Inserts rows/columns to range with difference if (currentLayerRange.Columns.Count < layerData.GetLength(1)) { difference = GetRangeDifference(currentLayerRange.Columns.Count, layerData.GetLength(1)); // Take the column next to last column of the current range as first column getting inserted. Range firstColumnCell = (Range)currentLayerRange.Cells[1, currentLayerRange.Columns.Count + 1]; columnDifferenceRange = workSheet.GetRange(firstColumnCell, currentLayerRange.GetRowsCount(), difference); } // The range provides a combination of the current range and column difference range, // which is used for affected range and formula validation. if (difference > 0) { validationRange = validationRange.Resize[Type.Missing, validationRange.Columns.Count + difference]; } int rowsCount = currentLayerRange.GetRowsCount(); if (rowsCount < layerData.GetLength(0)) { difference = GetRangeDifference(rowsCount, layerData.GetLength(0)); // Take the row next to last row of the current range as first row getting inserted. Range firstRowCell = (Range)currentLayerRange.Cells[rowsCount + 1, 1]; rowDifferenceRange = workSheet.GetRange(firstRowCell, difference, currentLayerRange.Columns.Count); } // The range provides a combination of the current range and row difference range // which is used for affected range and formula validation. if (difference > 0) { validationRange = validationRange.Resize[validationRange.GetRowsCount() + difference, Type.Missing]; } if (ValidateLocalInWWTLayerData(workSheet, validationRange)) { Range rowRange = null, colRange = null; if (columnDifferenceRange != null) { // Insert the columns which are needed to get the latest data. colAddress = columnDifferenceRange.Address; InsertColumns(columnDifferenceRange); colRange = workSheet.Application.Range[colAddress]; } if (rowDifferenceRange != null) { // Insert the rows which are needed to get the latest data. rowaddress = rowDifferenceRange.Address; InsertRows(rowDifferenceRange); rowRange = workSheet.Application.Range[rowaddress]; } bool createLayerForRange = false; if (rowRange != null) { // Any new rows added, add them to the layer range. createLayerForRange = true; currentLayerRange = workSheet.Application.get_Range(rowRange, currentLayerRange); } if (colRange != null) { // Any new columns added, add them to the layer range. createLayerForRange = true; currentLayerRange = workSheet.Application.get_Range(colRange, currentLayerRange); } ThisAddIn.ExcelApplication.SheetChange -= new AppEvents_SheetChangeEventHandler(OnSheetChange); currentLayerRange.Cells.Clear(); ThisAddIn.ExcelApplication.SheetChange += new AppEvents_SheetChangeEventHandler(OnSheetChange); currentLayerRange.Select(); // Only in case if the range size increased, update the layer properties. if (createLayerForRange) { CreateRangeForLayer(currentLayerRange); } else { // Since Range is not getting changed in case of no more rows/columns added to them, call this method to update the layer properties alone. SetLayerRangeProperties(this.currentWorkbookMap.SelectedLayerMap.RangeName); } bool isResizeRangeRequired = false; // In case if the rows are less, resize the range, instead paste only the rows having data and rest of rows will be left as empty. // Additional empty rows cannot be deleted, since there could be data in the columns which are not part of range which will be lost. if (currentLayerRange.GetRowsCount() > layerData.GetLength(0)) { difference = GetRangeDifference(currentLayerRange.GetRowsCount(), layerData.GetLength(0)); currentLayerRange = currentLayerRange.Resize[currentLayerRange.GetRowsCount() - difference, Type.Missing]; isResizeRangeRequired = true; } // In case if the columns are less, resize the range, instead paste only the columns having data and rest of columns will be left as empty. // Additional empty columns cannot be deleted, since there could be data in the rows which are not part of range which will be lost. if (currentLayerRange.Columns.Count > layerData.GetLength(1)) { difference = GetRangeDifference(currentLayerRange.Columns.Count, layerData.GetLength(1)); currentLayerRange = currentLayerRange.Resize[Type.Missing, currentLayerRange.Columns.Count - difference]; isResizeRangeRequired = true; } if (isResizeRangeRequired) { CreateRangeForLayer(currentLayerRange); currentLayerRange.Select(); } currentLayerRange.SetValue(layerData); SetFormatForDateColumns(workSheet); } } } } }
public static bool FillForms2() { try { //暂时支持40个点 11.14 string destinationFile = System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F2.xlsx"; if (!File.Exists(destinationFile)) { string sourceFile = System.IO.Directory.GetCurrentDirectory() + "\\F2.xlsx"; // 此处是默认的表格模板 //string = @"D:\\ProjectFormTemplet\\testCopy2.xlsx"; bool isrewrite = true; // true=覆盖已存在的同名文件,false则反之 System.IO.File.Copy(sourceFile, destinationFile, isrewrite); } else { } xls = new Microsoft.Office.Interop.Excel.Application(); book = xls.Workbooks.Open(destinationFile, 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); string Data = System.IO.File.ReadAllText(System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\all.txt", Encoding.Default); List <Forms> _projectData = JsonConvert.DeserializeObject <List <Forms> >(Data); sheet = (_Worksheet)book.Worksheets.get_Item(1); sheet.Activate(); int l1, l2, l3, l4, l5, l6; l1 = _projectData[0].F2.LandPointCodeList.Length; l2 = _projectData[0].F2.LandPointTypeList.Length; //l3 = _projectData[0].F2.LandBoundaryExplain.Length; l4 = _projectData[0].F2.LandBoundaryLocation.Length; l5 = _projectData[0].F2.LandBoundaryType.Length; l6 = _projectData[0].F2.LandPointDistance.Length; if (l1 - 19 <= 0) { if (l1 == l2 && l1 == l4 + 1 && l1 == l5 + 1 && l1 == l6 + 1) { sheet.Cells[4, 1] = _projectData[0].F2.LandPointCodeList[0]; sheet.Cells[4, (_projectData[0].F2.LandPointTypeList[0] + 2)] = "√"; for (int n = 0; n < l4; n++) { sheet.Cells[(2 * n + 5), 1] = _projectData[0].F2.LandPointCodeList[n + 1]; sheet.Cells[(2 * n + 5), (_projectData[0].F2.LandPointTypeList[n + 1] + 2)] = "√"; sheet.Cells[(2 * n + 4), 7] = _projectData[0].F2.LandPointDistance[n]; sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryType[n] + 8)] = "√"; sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryLocation[n] + 16)] = "√"; //sheet.Cells[(2 * n + 4), 19] = _projectData[0].F2.LandBoundaryExplain[n]; 说明不填写 } sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F2-1.pdf"); } else { return(false); } } else if (l1 - 19 <= 19) { if (l1 == l2 && l1 == l4 + 1 && l1 == l5 + 1 && l1 == l6 + 1) { sheet.Cells[4, 1] = _projectData[0].F2.LandPointCodeList[0]; sheet.Cells[4, (_projectData[0].F2.LandPointTypeList[0] + 2)] = "√"; for (int n = 0; n < 19; n++) { sheet.Cells[(2 * n + 5), 1] = _projectData[0].F2.LandPointCodeList[n + 1]; sheet.Cells[(2 * n + 5), (_projectData[0].F2.LandPointTypeList[n + 1] + 2)] = "√"; sheet.Cells[(2 * n + 4), 7] = _projectData[0].F2.LandPointDistance[n]; sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryType[n] + 8)] = "√"; sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryLocation[n] + 16)] = "√"; //sheet.Cells[(2 * n + 4), 19] = _projectData[0].F2.LandBoundaryExplain[n]; 说明不填写 } sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F2-1.pdf"); sheet = (_Worksheet)book.Worksheets.get_Item(2); sheet.Activate(); sheet.Cells[4, 1] = _projectData[0].F2.LandPointCodeList[19]; sheet.Cells[4, (_projectData[0].F2.LandPointTypeList[19] + 2)] = "√"; for (int n = 0; n < l1 - 19; n++) { sheet.Cells[(2 * n + 5), 1] = _projectData[0].F2.LandPointCodeList[19 + n + 1]; sheet.Cells[(2 * n + 5), (_projectData[0].F2.LandPointTypeList[19 + n + 1] + 2)] = "√"; sheet.Cells[(2 * n + 4), 7] = _projectData[0].F2.LandPointDistance[19 + n]; sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryType[19 + n] + 8)] = "√"; sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryLocation[19 + n] + 16)] = "√"; //sheet.Cells[(2 * n + 4), 19] = _projectData[0].F2.LandBoundaryExplain[n]; 说明不填写 } sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F2-2.pdf"); } else { return(false); } } else { return(false); } // 超过38个点暂时不支持 11.14 book.Save(); book.Close(false, Missing.Value, Missing.Value); //关闭打开的表 xls.Quit(); //Excel sheet = null; book = null; xls = null; GC.Collect(); return(true); } catch (Exception) { return(false); } }
/// <summary> /// Sets the Date format for the columns which are mapped to StartDate and EndDate. /// </summary> /// <param name="workSheet">Worksheet object</param> private void SetFormatForDateColumns(_Worksheet workSheet) { Range currentLayerRange = workSheet.Application.Range[this.currentWorkbookMap.SelectedLayerMap.RangeAddress]; Range firstCell = null; if (this.currentWorkbookMap.SelectedLayerMap.LayerDetails.StartDateColumn != Common.Constants.DefaultColumnIndex) { foreach (Range area in currentLayerRange.Areas) { firstCell = area.Cells[this.currentWorkbookMap.SelectedLayerMap.LayerDetails.StartDateColumn + 1] as Range; Range startDateColumn = workSheet.GetRange(firstCell, currentLayerRange.GetRowsCount(), 1); startDateColumn.NumberFormat = "m/d/yyyy h:mm"; } } if (this.currentWorkbookMap.SelectedLayerMap.LayerDetails.EndDateColumn != Common.Constants.DefaultColumnIndex) { foreach (Range area in currentLayerRange.Areas) { firstCell = area.Cells[this.currentWorkbookMap.SelectedLayerMap.LayerDetails.EndDateColumn + 1] as Range; Range endDateColumn = workSheet.GetRange(firstCell, currentLayerRange.GetRowsCount(), 1); endDateColumn.NumberFormat = "m/d/yyyy h:mm"; } } }
public static bool FillForms3() { try { //暂时支持40条界址线 11.14 string destinationFile = System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F3.xlsx"; if (!File.Exists(destinationFile)) { string sourceFile = System.IO.Directory.GetCurrentDirectory() + "\\F3.xlsx"; // 此处是默认的表格模板 //string = @"D:\\ProjectFormTemplet\\testCopy2.xlsx"; bool isrewrite = true; // true=覆盖已存在的同名文件,false则反之 System.IO.File.Copy(sourceFile, destinationFile, isrewrite); } else { } xls = new Microsoft.Office.Interop.Excel.Application(); book = xls.Workbooks.Open(destinationFile, 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); string Data = System.IO.File.ReadAllText(System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\all.txt", Encoding.Default); List <Forms> _projectData = JsonConvert.DeserializeObject <List <Forms> >(Data); sheet = (_Worksheet)book.Worksheets.get_Item(1); sheet.Activate(); int l1, l2, l3; l1 = _projectData[0].F3.StartPointCodeList.Length; l2 = _projectData[0].F3.InnerPointCodeList.Length; l3 = _projectData[0].F3.EndPointCodeList.Length; if (l1 == l2 && l2 == l3) { if (l1 - 21 <= 0) { for (int n = 0; n < l1; n++) { sheet.Cells[n + 5, 1] = _projectData[0].F3.StartPointCodeList[n]; sheet.Cells[n + 5, 2] = _projectData[0].F3.InnerPointCodeList[n]; sheet.Cells[n + 5, 3] = _projectData[0].F3.EndPointCodeList[n]; } sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F3-1.pdf"); } else if (l1 - 21 <= 21) { for (int n = 0; n < 21; n++) { sheet.Cells[n + 5, 1] = _projectData[0].F3.StartPointCodeList[n]; sheet.Cells[n + 5, 2] = _projectData[0].F3.InnerPointCodeList[n]; sheet.Cells[n + 5, 3] = _projectData[0].F3.EndPointCodeList[n]; } sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F3-1.pdf"); sheet = (_Worksheet)book.Worksheets.get_Item(2); sheet.Activate(); for (int n = 0; n < l1 - 21; n++) { sheet.Cells[n + 5, 1] = _projectData[0].F3.StartPointCodeList[21 + n]; sheet.Cells[n + 5, 2] = _projectData[0].F3.InnerPointCodeList[21 + n]; sheet.Cells[n + 5, 3] = _projectData[0].F3.EndPointCodeList[21 + n]; } sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F3-2.pdf"); } else { return(false); } // 超过42条线暂时不支持 11.14 } else { return(false); } } catch (Exception) { return(false); } book.Save(); book.Close(false, Missing.Value, Missing.Value); //关闭打开的表 xls.Quit(); //Excel sheet = null; book = null; xls = null; GC.Collect(); return(true); }
/// <summary> /// Validates if the range is affecting any of the existing range of layers /// and checks if the range has formula /// </summary> /// <param name="workSheet">Current active worksheet</param> /// <param name="range">Current range</param> /// <returns>Returns true if the layer is valid</returns> private bool ValidateLocalInWWTLayerData(_Worksheet workSheet, Range range) { bool isValid = false; if (ValidateAffectedMappedRange(workSheet, range)) { if (range.ValidateFormula()) { isValid = Ribbon.ShowWarningWithResult(Properties.Resources.RangeHasFormulaWarning); } else { isValid = true; } } return isValid; }
/// <summary> /// 效率慢 /// </summary> /// <returns></returns> public static bool FillForms() { try { string destinationFile = System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\Form.xlsx"; if (!File.Exists(destinationFile)) { string sourceFile = System.IO.Directory.GetCurrentDirectory() + "\\Form.xlsx"; // 此处是默认的表格模板 //string = @"D:\\ProjectFormTemplet\\testCopy2.xlsx"; bool isrewrite = true; // true=覆盖已存在的同名文件,false则反之 System.IO.File.Copy(sourceFile, destinationFile, isrewrite); } else { } xls = new Microsoft.Office.Interop.Excel.Application(); book = xls.Workbooks.Open(destinationFile, 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); string Data = System.IO.File.ReadAllText(System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\all.txt", Encoding.Default); List <Forms> _projectData = JsonConvert.DeserializeObject <List <Forms> >(Data); for (int formNum = 1; formNum < 3; formNum++) { sheet = (_Worksheet)book.Worksheets.get_Item(formNum); sheet.Activate(); if (formNum == 1) { sheet.Cells[2, 9] = _projectData[0].F1.TableID; sheet.Cells[26, 6] = _projectData[0].F1.ParcelCode; sheet.Cells[31, 6] = _projectData[0].F1.InvestigateOrganization; sheet.Cells[43, 6] = _projectData[0].F1.InvestigateDate; //特殊 日期起止 sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\Cover.pdf"); } else if (formNum == 2) { sheet.Cells[2, 3] = _projectData[0].F1.OwnPowerSide;// C2=2,3 D5=5,4 先列后行 sheet.Cells[3, 3] = _projectData[0].F1.UsePowerSide; sheet.Cells[16, 9] = _projectData[0].F1.ParcelCode; sheet.Cells[3, 9] = _projectData[0].F1.PowerSideType; sheet.Cells[4, 9] = _projectData[0].F1.PowerSideCertificateType; sheet.Cells[5, 9] = _projectData[0].F1.PowerSideCertificateCode; sheet.Cells[6, 9] = _projectData[0].F1.PowerSideAddress; sheet.Cells[7, 3] = _projectData[0].F1.PowerType; sheet.Cells[7, 8] = _projectData[0].F1.PowerCharacter; sheet.Cells[7, 10] = _projectData[0].F1.LandPowerCertificatePaper; sheet.Cells[8, 3] = _projectData[0].F1.Location; sheet.Cells[9, 3] = _projectData[0].F1.PrincipalCertificateCode; sheet.Cells[9, 6] = _projectData[0].F1.PrincipalCertificateType; sheet.Cells[10, 6] = _projectData[0].F1.ProcuratorCertificateCode; sheet.Cells[9, 10] = _projectData[0].F1.PrincipalCertificateTelephone; sheet.Cells[11, 3] = _projectData[0].F1.ProcuratorName; sheet.Cells[11, 6] = _projectData[0].F1.ProcuratorCertificateType; sheet.Cells[12, 6] = _projectData[0].F1.ProcuratorCertificateCode; sheet.Cells[11, 10] = _projectData[0].F1.ProcuratorCertificateTelephone; sheet.Cells[13, 3] = _projectData[0].F1.PowerSetPattern; sheet.Cells[14, 3] = _projectData[0].F1.NationalEconomyIndustryClassificationCode; sheet.Cells[16, 3] = _projectData[0].F1.PreParcelCode; sheet.Cells[16, 9] = _projectData[0].F1.ParcelCode; sheet.Cells[17, 3] = _projectData[0].F1.UnitNumber; sheet.Cells[18, 5] = _projectData[0].F1.MapScale; sheet.Cells[19, 5] = _projectData[0].F1.MapCode; sheet.Cells[20, 3] = _projectData[0].F1.ParcelRangeNorth; sheet.Cells[21, 3] = _projectData[0].F1.ParcelRangeEast; sheet.Cells[22, 3] = _projectData[0].F1.ParcelRangeSouth; sheet.Cells[23, 3] = _projectData[0].F1.ParcelRangeWest; sheet.Cells[24, 3] = _projectData[0].F1.Rank; sheet.Cells[24, 9] = _projectData[0].F1.Price; sheet.Cells[25, 3] = _projectData[0].F1.PermittedUsefor; sheet.Cells[26, 5] = _projectData[0].F1.PermittedTypeCode; sheet.Cells[25, 8] = _projectData[0].F1.PracticalUsefor; sheet.Cells[26, 10] = _projectData[0].F1.PracticalTypeCode; sheet.Cells[27, 3] = _projectData[0].F1.PermittedArea; sheet.Cells[27, 6] = _projectData[0].F1.ParcelArea; sheet.Cells[27, 10] = _projectData[0].F1.BuildLandArea; sheet.Cells[29, 10] = _projectData[0].F1.BuildTotalArea; string _landUseTime = _projectData[0].F1.LandUseStartTime + "--" + _projectData[0].F1.LandUseEndTime; sheet.Cells[30, 3] = _landUseTime; //特殊 日期起止 sheet.Cells[31, 3] = _projectData[0].F1.CommonUse; //sheet.Cells[33, 3] = _projectData[0].F1.Explain; 说明不填 sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F1.pdf"); } } book.Save(); book.Close(false, Missing.Value, Missing.Value); //关闭打开的表 xls.Quit(); //Excel sheet = null; book = null; xls = null; GC.Collect(); return(true); } catch (Exception) { return(false); } }
public void ExportDataToExcel(string fileName, string[] tableName, string[] sheetName, DataSet ds, DateTime generateDate) { PreExitExcel(); string filename = fileName.Substring(0, fileName.LastIndexOf(".")); try { _ExcelApp = new Microsoft.Office.Interop.Excel.Application(); _ExcelWBook = (_Workbook)(_ExcelApp.Workbooks.Open(excelTemplatePath + filename, 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)); for (int i = 0; i < tableName.Length; i++) { if (tableName[i] == "TriBenchMarkRpt_MatchedBacklog") { _IniRow = 2; _sheetName = sheetName[i].ToString(); _ExcelWSheet = (_Worksheet)_ExcelWBook.Sheets[sheetName[i].ToString()]; for (int j = 0; j < ds.Tables[tableName[i].ToString()].Rows.Count; j++) { for (int k = 0; k < ds.Tables[tableName[i].ToString()].Columns.Count; k++) { _ExcelWSheet.Cells[_IniRow, k + 2] = ds.Tables[tableName[i].ToString()].Rows[j][k].ToString(); } _IniRow++; } } else { _IniRow = 3; _sheetName = sheetName[i].ToString(); _ExcelWSheet = (_Worksheet)_ExcelWBook.Sheets[sheetName[i].ToString()]; int rowcount = ds.Tables[i].Rows.Count; for (int j = 0; j < ds.Tables[tableName[i].ToString()].Rows.Count; j++) { for (int k = 0; k < ds.Tables[tableName[i].ToString()].Columns.Count; k++) { _ExcelWSheet.Cells[_IniRow, k + 1] = ds.Tables[tableName[i].ToString()].Rows[j][k].ToString(); } _IniRow++; } } } string strDate = generateDate.AddDays(-1).ToString("MMdd"); ExcelHelper excelHelper = new ExcelHelper(); _fullFilePath = string.Format(excelHelper.getFilename(generateDate) + "\\{0}-{1}.xls", filename, strDate); _ExcelApp.Rows.RowHeight = "15"; //ExApp.DisplayAlerts = false; _ExcelWBook.CheckCompatibility = false;//Add for Diable Compatibility for saving excel _ExcelWBook.SaveAs(_fullFilePath, XlFileFormat.xlWorkbookNormal, null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, null, null, null); _ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value); _ExcelApp.Workbooks.Close(); _ExcelApp.Quit(); Marshal.ReleaseComObject(_ExcelWSheet); Marshal.ReleaseComObject(_ExcelWBook); Marshal.ReleaseComObject(_ExcelApp); } catch (Exception e) { _ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value); _ExcelApp.Workbooks.Close(); _ExcelApp.Quit(); throw e; } }
private void PutLessonData(String data, _Worksheet worksheet, int currentRow, ref int currentColumn) { currentColumn++; worksheet.Cells[currentRow, currentColumn] = data; }
//获取Sheet Name public string GetFirstSheetName(string filePath) { string sheetName = string.Empty; try { _ExcelApp = new Microsoft.Office.Interop.Excel.Application(); _ExcelWBook = (_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)); _ExcelWSheet = (Worksheet)_ExcelWBook.Sheets[1]; sheetName = _ExcelWSheet.Name; _ExcelApp.DisplayAlerts = false; _ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value); _ExcelApp.Workbooks.Close(); _ExcelApp.Quit(); return sheetName; } catch (Exception) { _ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value); _ExcelApp.Workbooks.Close(); _ExcelApp.Quit(); return sheetName; } }
public void createSummaryStatistics(List <Variable> variables) { if (variables.Count == 0) { return; } _Worksheet worksheet = WorksheetHelper.NewWorksheet("One-Variable Summary"); int column = 1; int row = 2; if (model.mean) { worksheet.Cells[row++, column] = "Mean"; } if (model.variance) { worksheet.Cells[row++, column] = "Variance"; } if (model.standardDeviation) { worksheet.Cells[row++, column] = "Standard Deviation"; } if (model.skewness) { worksheet.Cells[row++, column] = "Skewness"; } if (model.kurtosis) { worksheet.Cells[row++, column] = "Kurtosis"; } if (model.median) { worksheet.Cells[row++, column] = "Median"; } if (model.meanAbsDeviation) { worksheet.Cells[row++, column] = "Mean Abs. Deviation"; } if (model.mode) { worksheet.Cells[row++, column] = "Mode"; } if (model.minimum) { worksheet.Cells[row++, column] = "Minimum"; } if (model.maximum) { worksheet.Cells[row++, column] = "Maximum"; } if (model.range) { worksheet.Cells[row++, column] = "Range"; } if (model.count) { worksheet.Cells[row++, column] = "Count"; } if (model.sum) { worksheet.Cells[row++, column] = "Sum"; } if (model.firstQuartile) { worksheet.Cells[row++, column] = "First Quartile"; } if (model.thirdQuartile) { worksheet.Cells[row++, column] = "Third Quartile"; } if (model.interquartileRange) { worksheet.Cells[row++, column] = "Interquartile Range"; } ((Range)worksheet.Cells[row, column]).EntireColumn.AutoFit(); row = 1; column = 2; foreach (Variable variable in variables) { worksheet.Cells[row++, column] = variable.name; var range = variable.getRange().Address(true, true, true); if (model.mean) { worksheet.Cells[row++, column] = "=AVERAGE(" + range + ")"; } if (model.variance) { worksheet.Cells[row++, column] = "=VAR.S(" + range + ")"; } if (model.standardDeviation) { worksheet.Cells[row++, column] = "=STDEV.S(" + range + ")"; } if (model.skewness) { worksheet.Cells[row++, column] = "=SKEW(" + range + ")"; } if (model.kurtosis) { worksheet.Cells[row++, column] = "=KURT(" + range + ")"; } if (model.median) { worksheet.Cells[row++, column] = "=MEDIAN(" + range + ")"; } if (model.meanAbsDeviation) { worksheet.Cells[row++, column] = "=AVEDEV(" + range + ")"; } if (model.mode) { worksheet.Cells[row++, column] = "=MODE.SNGL(" + range + ")"; } if (model.minimum) { worksheet.Cells[row++, column] = "=MIN(" + range + ")"; } if (model.maximum) { worksheet.Cells[row++, column] = "=MAX(" + range + ")"; } if (model.range) { worksheet.Cells[row++, column] = "=MAX(" + range + ") - MIN(" + range + ")"; } if (model.count) { worksheet.Cells[row++, column] = "=COUNT(" + range + ")"; } if (model.sum) { worksheet.Cells[row++, column] = "=SUM(" + range + ")"; } if (model.firstQuartile) { worksheet.Cells[row++, column] = "=QUARTILE.INC(" + range + ",1)"; } if (model.thirdQuartile) { worksheet.Cells[row++, column] = "=QUARTILE.INC(" + range + ",3)"; } if (model.interquartileRange) { worksheet.Cells[row++, column] = "=QUARTILE.INC(" + range + ",3) - QUARTILE.INC(" + range + ",1)"; } ((Range)worksheet.Cells[row, column]).EntireColumn.AutoFit(); row = 1; column++; } }
private void LoadExcelData(Dictionary <string, Region> regions, Dictionary <string, Division> divisions) { string currentCell = String.Empty; string filePath = "C:\\Users\\nsinotte\\Desktop\\Personal Projects\\SkyLine\\skyline-challenges\\SkylineChallenges-CSharp\\RestAPIProcessing\\US Census Bureau Regions and Divisions.xlsx"; Application file = new Application(); Workbook book = file.Workbooks.Open(filePath); _Worksheet sheet = book.Sheets[1]; Range range = sheet.UsedRange; for (int i = 3; i < 9; ++i) { currentCell = range[i, 3].Value2.ToString().Trim(); divisions.Add(currentCell, Division.NewEngland); regions.Add(currentCell, Region.Northeast); } for (int i = 10; i < 13; ++i) { currentCell = range[i, 3].Value2.ToString().Trim(); divisions.Add(currentCell, Division.MidAtlantic); regions.Add(currentCell, Region.Northeast); } for (int i = 15; i < 20; ++i) { currentCell = range[i, 3].Value2.ToString().Trim(); divisions.Add(currentCell, Division.EastNorthCentral); regions.Add(currentCell, Region.Midwest); } for (int i = 21; i < 28; ++i) { currentCell = range[i, 3].Value2.ToString().Trim(); divisions.Add(currentCell, Division.WestNorthCentral); regions.Add(currentCell, Region.Midwest); } for (int i = 3; i < 12; ++i) { currentCell = range[i, 7].Value2.ToString().Trim(); divisions.Add(currentCell, Division.SouthAtlantic); regions.Add(currentCell, Region.South); } for (int i = 13; i < 17; ++i) { currentCell = range[i, 7].Value2.ToString().Trim(); divisions.Add(currentCell, Division.EastSouthCentral); regions.Add(currentCell, Region.South); } for (int i = 18; i < 22; ++i) { currentCell = range[i, 7].Value2.ToString().Trim(); divisions.Add(currentCell, Division.WestSouthCentral); regions.Add(currentCell, Region.South); } for (int i = 24; i < 32; ++i) { currentCell = range[i, 7].Value2.ToString().Trim(); divisions.Add(currentCell, Division.Mountain); regions.Add(currentCell, Region.West); } for (int i = 33; i < 37; ++i) { currentCell = range[i, 7].Value2.ToString().Trim(); divisions.Add(currentCell, Division.Pacific); regions.Add(currentCell, Region.West); } }
private void WriteToExcel(string filePath) { if (File.Exists(filePath)) { File.Delete(filePath); } Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); xlApp.Visible = false; xlApp.DisplayAlerts = false; _Workbook xlWorkbook = xlApp.Workbooks.Add(Type.Missing); //report _Worksheet xlReportSheet = xlWorkbook.Sheets[1]; xlReportSheet.Name = "Report"; int row = 1; xlReportSheet.Cells[row++, 1] = $"Error Count: {_errorModels.Count}"; xlReportSheet.Cells[row, 1] = "File Name"; xlReportSheet.Cells[row, 2] = "Error Message"; row++; foreach (ErrorModel error in _errorModels) { xlReportSheet.Cells[row, 1] = error.FileName; xlReportSheet.Cells[row, 2] = error.ErrorMessage; row++; } //data var xlSheets = xlWorkbook.Sheets as Sheets; _Worksheet xlWorksheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); xlWorksheet.Name = "Data"; xlWorksheet.Cells[_lastRow, 1] = "Company"; xlWorksheet.Cells[_lastRow, 3] = "File Name"; xlWorksheet.Cells[_lastRow, 2] = "Amendment Files"; xlWorksheet.Cells[_lastRow, 4] = "Paragraph"; _lastRow++; foreach (KeyValuePair <string, CompanyModel> entry in _companyModels) { xlWorksheet.Cells[_lastRow, 1] = entry.Key; //append files int paraRow = _lastRow; foreach (FileModel file in entry.Value.FileModels) { xlWorksheet.Cells[paraRow, 3] = file.FileName; foreach (string para in file.Paragraphs) { xlWorksheet.Cells[paraRow++, 4] = para; } } //append amendments int amendRow = _lastRow; foreach (string amendment in entry.Value.AmendmentFileNames) { xlWorksheet.Cells[amendRow++, 2] = amendment; } _lastRow = Math.Max(paraRow, amendRow); _lastRow++; } xlWorkbook.SaveAs(filePath, XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); xlWorkbook.Close(); xlApp.Quit(); }
public void Save(List<Week> weeks, _Worksheet worksheet) { // тут можно че-то добавить _sealedExcelWriter.Save(weeks,worksheet); }
public static void printLable(int id) { String sql = "select * from 标签 where ID={0}"; System.Data.DataTable dt = new System.Data.DataTable(); if (!mySystem.Parameter.isSqlOk) { OleDbDataAdapter da = new OleDbDataAdapter(String.Format(sql, id), mySystem.Parameter.connOle); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); da.Fill(dt); } else { SqlDataAdapter da = new SqlDataAdapter(String.Format(sql, id), mySystem.Parameter.conn); SqlCommandBuilder cb = new SqlCommandBuilder(da); da.Fill(dt); } if (dt.Rows.Count == 0) { MessageBox.Show("无法找到标签信息"); return; } DataRow dr = dt.Rows[0]; string path = Directory.GetCurrentDirectory(); Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook wb = null; wb = oXL.Workbooks.Open(path + @"/../../xls/cleancut/7 标签-清洁分切.xlsx"); _Worksheet my = wb.Worksheets[wb.Worksheets.Count]; //dr["生产指令"] = mySystem.Parameter.cleancutInstruction; //dr["生产指令ID"] = mySystem.Parameter.cleancutInstruID; //dr["膜代码"] = cb膜代码.Text; //dr["批号-卷号 "] = tb批号.Text; //dr["合格数量米"] = tb米.Text; //dr["合格数量千克"] = tbKg.Text; //dr["原膜代码"] = cb原膜代码.Text; //dr["分切日期"] = dtp分切日期.Value.ToString("yyyy/MM/dd"); //dr["分切班次"] = cb白班.Checked ? "白班" : "夜班"; my.Select(); my.Cells[2, 2].Value = dr["膜代码"]; my.Cells[3, 2].Value = dr["批号_卷号"]; my.Cells[4, 2].Value = dr["合格数量米"] + "米; " + dr["合格数量千克"] + "Kg"; my.Cells[5, 2].Value = dr["原膜代码"]; my.Cells[6, 2].Value = String.Format("{0} {1}", Convert.ToDateTime(dr["分切日期"]).ToString("yyyy/MM/dd"), dr["分切班次"].ToString() == "白班" ? "白班☑ 夜班□" : "白班□ 夜班☑"); my = wb.Worksheets[1]; my.Select(); oXL.Visible = false; my.PrintOut(); // 关闭文件,false表示不保存 wb.Close(false); // 关闭Excel进程 oXL.Quit(); // 释放COM资源 Marshal.ReleaseComObject(wb); Marshal.ReleaseComObject(oXL); wb = null; oXL = null; }
public static MsExcel._Workbook Shashade(MsExcel.Application appExcel) { _Workbook ExcelBooks = null; appExcel.Visible = false; ExcelBooks = appExcel.Workbooks.Add(); string Sheet_Name = "站点数据"; _Worksheet ExcelSheets = TU.Add_Sheets(ExcelBooks, Sheet_Name);//添加一个sheet ExcelSheets.Name = Sheet_Name; System.Data.DataTable dalaopo = new System.Data.DataTable("dalaopo"); dalaopo.Columns.Add("站点", typeof(double)); // dalaopo.Columns.Add("降水数据", typeof(double)); System.Data.DataTable jieguoTB = new System.Data.DataTable(); double[] n = new double[] { 129, 135, 128, 132, 99, 113, 157, 81, 70, 111, 78, 138, 103, 88, 110, 91 }; double[] T = new double[] { 5, 10, 20, 50 }; for (int i = 0; i < n.Length; i++) { List <double> gailvL = new List <double>(); for (int j = 0; j < T.Length; j++) { double gailv = 1 - 50 / (n[i] * T[j]); gailvL.Add(gailv); } double[] gailvd = gailvL.ToArray(); string[] gailvS = MYTUW.TUW.doublearrTOstring(gailvd); System.Data.DataTable jieguo = MYTUW.TUW.ArToDT1(gailvS); jieguoTB.Merge(jieguo); } #region /* for (int i = 1; i < RawDataStr_A.Length; i++) * { * char[] seperators = { ',' }; * string[] R_str1 = TU.ParseStringTo_Array<string>(RawDataStr_A[i], seperators); * * double zhandian = Convert.ToDouble(R_str1[0]); * string jiangshuistr = Convert.ToString(R_str1[R_str1.Length -1]); * double jiangshui= Convert.ToDouble(R_str1[R_str1.Length - 1]); * if (Convert .ToDouble ( jiangshuistr) > 30000) * { * string housanwei = Convert.ToString(R_str1[R_str1.Length - 1]).Substring(Convert.ToString(R_str1[R_str1.Length - 1]).Length - 3); * // Console.WriteLine(housanwei); * if (Convert .ToDouble (housanwei)>500&& Convert.ToDouble(housanwei)!=700) * { * jiangshui = Convert.ToDouble(housanwei); * } * else * { * jiangshui = 1; * } * * } * dalaopo.Rows.Add(zhandian, jiangshui * 0.1); * * * } * var groupNew = from row in dalaopo.AsEnumerable() * group row by new * { * zhandian = (row.Field<double>("站点")) * } * into groupRes * orderby groupRes.Key.zhandian * select groupRes; * //把分完组的每个表格放入DS中 * DataSet RTB_Grp = new DataSet(); * foreach (var eachGroup in groupNew) * { * System.Data.DataTable TBMid1 = eachGroup.CopyToDataTable(); * * RTB_Grp.Tables.Add(TBMid1); * } * //对每天的数据进行处理并放入 Traffic_TB中RTB_Grp.Tables .Count * for (int i=0;i< RTB_Grp.Tables.Count; i++) * { * System.Data.DataTable meigezhandian = RTB_Grp.Tables[i]; * List<double> baoyurishu = new List<double>(); * List<double> quannianyuliang = new List<double>(); * for (int j=0;j < meigezhandian.Rows.Count;j++) * { * double yuliang = Convert.ToDouble(meigezhandian.Rows[j][1]); * quannianyuliang.Add(yuliang); * if (yuliang>50) * { * baoyurishu.Add(yuliang); * } * } * double baoyutianshu = baoyurishu.Count; * double baoyuliang = baoyurishu.Sum(); * double baoyuqiangdu = baoyuliang / baoyutianshu; * double zongyuliang = quannianyuliang.Sum(); * double baoyugongxianlv = baoyuliang / zongyuliang; * jieguoTB.Rows.Add(Convert.ToDouble(meigezhandian.Rows[0][0]), baoyutianshu, * baoyuliang, baoyuqiangdu, baoyugongxianlv); * } */ #endregion MYTUW.TUW.DTToExcel(jieguoTB, ExcelSheets, 2, 1); MYTUW.TUW.Delete_sheet(appExcel, ExcelBooks); return(ExcelBooks); }
protected void WriteCell(_Worksheet excelSheet, int curentCellRowIndex, int currentCellColIndex, object value) { excelSheet.Cells[curentCellRowIndex, currentCellColIndex] = value; }
public static bool FillForm() { try { projname = _importProjectName; string destinationFile = System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\Forms.xlsx"; if (!File.Exists(destinationFile)) { string sourceFile = System.IO.Directory.GetCurrentDirectory() + "\\Forms.xlsx"; // 此处是默认的表格模板 //string = @"D:\\ProjectFormTemplet\\testCopy2.xlsx"; bool isrewrite = true; // true=覆盖已存在的同名文件,false则反之 System.IO.File.Copy(sourceFile, destinationFile, isrewrite); } else { } xls = new Microsoft.Office.Interop.Excel.Application(); book = xls.Workbooks.Open(destinationFile, 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); string Data = System.IO.File.ReadAllText(System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\all.txt", Encoding.Default); List <Forms> _projectData = JsonConvert.DeserializeObject <List <Forms> >(Data); //填写完Form1 for (int formNum = 1; formNum < 3; formNum++) { sheet = (_Worksheet)book.Worksheets.get_Item(formNum); sheet.Activate(); if (formNum == 1) { sheet.Cells[2, 9] = _projectData[0].F1.TableID; sheet.Cells[26, 6] = _projectData[0].F1.ParcelCode; sheet.Cells[31, 6] = _projectData[0].F1.InvestigateOrganization; sheet.Cells[43, 6] = _projectData[0].F1.InvestigateDate; //特殊 日期起止 sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\Cover.pdf"); } else if (formNum == 2) { sheet.Cells[2, 3] = _projectData[0].F1.OwnPowerSide;// C2=2,3 D5=5,4 先列后行 sheet.Cells[3, 3] = _projectData[0].F1.UsePowerSide; sheet.Cells[16, 9] = _projectData[0].F1.ParcelCode; sheet.Cells[3, 9] = _projectData[0].F1.PowerSideType; sheet.Cells[9, 3] = _projectData[0].F1.PrincipalName; sheet.Cells[4, 9] = _projectData[0].F1.PowerSideCertificateType; sheet.Cells[5, 9] = _projectData[0].F1.PowerSideCertificateCode; sheet.Cells[6, 9] = _projectData[0].F1.PowerSideAddress; sheet.Cells[7, 3] = _projectData[0].F1.PowerType; sheet.Cells[7, 8] = _projectData[0].F1.PowerCharacter; sheet.Cells[7, 10] = _projectData[0].F1.LandPowerCertificatePaper; sheet.Cells[8, 3] = _projectData[0].F1.Location; sheet.Cells[10, 6] = _projectData[0].F1.PrincipalCertificateCode; sheet.Cells[9, 6] = _projectData[0].F1.PrincipalCertificateType; sheet.Cells[9, 10] = _projectData[0].F1.PrincipalCertificateTelephone; sheet.Cells[11, 3] = _projectData[0].F1.ProcuratorName; sheet.Cells[11, 6] = _projectData[0].F1.ProcuratorCertificateType; sheet.Cells[12, 6] = _projectData[0].F1.ProcuratorCertificateCode; sheet.Cells[11, 10] = _projectData[0].F1.ProcuratorCertificateTelephone; sheet.Cells[13, 3] = _projectData[0].F1.PowerSetPattern; sheet.Cells[14, 3] = _projectData[0].F1.NationalEconomyIndustryClassificationCode; sheet.Cells[16, 3] = _projectData[0].F1.PreParcelCode; sheet.Cells[16, 9] = _projectData[0].F1.ParcelCode; sheet.Cells[17, 3] = _projectData[0].F1.UnitNumber; sheet.Cells[18, 5] = _projectData[0].F1.MapScale; sheet.Cells[19, 5] = _projectData[0].F1.MapCode; sheet.Cells[20, 3] = _projectData[0].F1.ParcelRangeNorth; sheet.Cells[21, 3] = _projectData[0].F1.ParcelRangeEast; sheet.Cells[22, 3] = _projectData[0].F1.ParcelRangeSouth; sheet.Cells[23, 3] = _projectData[0].F1.ParcelRangeWest; sheet.Cells[24, 3] = _projectData[0].F1.Rank; sheet.Cells[24, 9] = _projectData[0].F1.Price; sheet.Cells[25, 3] = _projectData[0].F1.PermittedUsefor; sheet.Cells[26, 5] = _projectData[0].F1.PermittedTypeCode; sheet.Cells[25, 8] = _projectData[0].F1.PracticalUsefor; sheet.Cells[26, 10] = _projectData[0].F1.PracticalTypeCode; sheet.Cells[27, 3] = _projectData[0].F1.PermittedArea; sheet.Cells[27, 6] = _projectData[0].F1.ParcelArea; sheet.Cells[27, 10] = _projectData[0].F1.BuildLandArea; sheet.Cells[29, 10] = _projectData[0].F1.BuildTotalArea; string _landUseTime = _projectData[0].F1.LandUseStartTime + "--" + _projectData[0].F1.LandUseEndTime; sheet.Cells[30, 3] = _landUseTime; //特殊 日期起止 sheet.Cells[31, 3] = _projectData[0].F1.CommonUse; //sheet.Cells[33, 3] = _projectData[0].F1.Explain; 说明不填 sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F1.pdf"); } } //填写Form2 sheet = (_Worksheet)book.Worksheets.get_Item(3); sheet.Activate(); int l1, l2, l3, l4, l5, l6; l1 = _projectData[0].F2.LandPointCodeList.Length; l2 = _projectData[0].F2.LandPointTypeList.Length; //l3 = _projectData[0].F2.LandBoundaryExplain.Length; l4 = _projectData[0].F2.LandBoundaryLocation.Length; l5 = _projectData[0].F2.LandBoundaryType.Length; l6 = _projectData[0].F2.LandPointDistance.Length; _Form2Number = l1 / 19; if (_Form2Number == 0 || l1 == 19) { _Form2Number = 1; if (l1 == l2 && l1 == l4 + 1 && l1 == l5 + 1 && l1 == l6 + 1) { sheet.Cells[4, 1] = _projectData[0].F2.LandPointCodeList[0]; if (_projectData[0].F2.LandPointTypeList[0] == -1) { } else { sheet.Cells[4, (_projectData[0].F2.LandPointTypeList[0] + 2)] = "√"; } for (int n = 0; n < l4; n++) { sheet.Cells[(2 * n + 5), 1] = _projectData[0].F2.LandPointCodeList[n + 1]; if (_projectData[0].F2.LandPointTypeList[n + 1] == -1) { } else { sheet.Cells[(2 * n + 5), (_projectData[0].F2.LandPointTypeList[n + 1] + 2)] = "√"; } sheet.Cells[(2 * n + 4), 7] = _projectData[0].F2.LandPointDistance[n]; if (_projectData[0].F2.LandBoundaryType[n] == -1) { } else { sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryType[n] + 8)] = "√"; } if (_projectData[0].F2.LandBoundaryLocation[n] == -1) { } else { sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryLocation[n] + 16)] = "√"; } //sheet.Cells[(2 * n + 4), 19] = _projectData[0].F2.LandBoundaryExplain[n]; 说明不填写 } sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F2-1.pdf"); } else { return(false); } } else if (_Form2Number == 1 && l1 > 19) { _Form2Number = 2; if (l1 == l2 && l1 == l4 + 1 && l1 == l5 + 1 && l1 == l6 + 1) { sheet.Cells[4, 1] = _projectData[0].F2.LandPointCodeList[0]; sheet.Cells[4, (_projectData[0].F2.LandPointTypeList[0] + 2)] = "√"; for (int n = 0; n < 19; n++) { sheet.Cells[(2 * n + 5), 1] = _projectData[0].F2.LandPointCodeList[n + 1]; sheet.Cells[(2 * n + 5), (_projectData[0].F2.LandPointTypeList[n + 1] + 2)] = "√"; sheet.Cells[(2 * n + 4), 7] = _projectData[0].F2.LandPointDistance[n]; sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryType[n] + 8)] = "√"; sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryLocation[n] + 16)] = "√"; //sheet.Cells[(2 * n + 4), 19] = _projectData[0].F2.LandBoundaryExplain[n]; 说明不填写 } sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F2-1.pdf"); sheet = (_Worksheet)book.Worksheets.get_Item(4); sheet.Activate(); sheet.Cells[4, 1] = _projectData[0].F2.LandPointCodeList[19]; sheet.Cells[4, (_projectData[0].F2.LandPointTypeList[19] + 2)] = "√"; for (int n = 0; n < l1 - 19; n++) { sheet.Cells[(2 * n + 5), 1] = _projectData[0].F2.LandPointCodeList[19 + n + 1]; sheet.Cells[(2 * n + 5), (_projectData[0].F2.LandPointTypeList[19 + n + 1] + 2)] = "√"; sheet.Cells[(2 * n + 4), 7] = _projectData[0].F2.LandPointDistance[19 + n]; sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryType[19 + n] + 8)] = "√"; sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryLocation[19 + n] + 16)] = "√"; //sheet.Cells[(2 * n + 4), 19] = _projectData[0].F2.LandBoundaryExplain[n]; 说明不填写 } sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F2-2.pdf"); } else { return(false); } } else { return(false); } // 超过38个点暂时不支持 11.14 //填写Form3 sheet = (_Worksheet)book.Worksheets.get_Item(5); sheet.Activate(); l1 = _projectData[0].F3.StartPointCodeList.Length; l2 = _projectData[0].F3.InnerPointCodeList.Length; l3 = _projectData[0].F3.EndPointCodeList.Length; _Form3Number = l1 / 21; if (_Form3Number == 0 || l1 == 21) { _Form3Number = 1; for (int n = 0; n < l1; n++) { sheet.Cells[n + 5, 1] = _projectData[0].F3.StartPointCodeList[n]; sheet.Cells[n + 5, 2] = _projectData[0].F3.InnerPointCodeList[n]; sheet.Cells[n + 5, 3] = _projectData[0].F3.EndPointCodeList[n]; } sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F3-1.pdf"); } else if (_Form3Number == 1 && l1 > 21) { _Form3Number = 2; for (int n = 0; n < 21; n++) { sheet.Cells[n + 5, 1] = _projectData[0].F3.StartPointCodeList[n]; sheet.Cells[n + 5, 2] = _projectData[0].F3.InnerPointCodeList[n]; sheet.Cells[n + 5, 3] = _projectData[0].F3.EndPointCodeList[n]; } sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F3-1.pdf"); sheet = (_Worksheet)book.Worksheets.get_Item(6); sheet.Activate(); for (int n = 0; n < l1 - 21; n++) { sheet.Cells[n + 5, 1] = _projectData[0].F3.StartPointCodeList[21 + n]; sheet.Cells[n + 5, 2] = _projectData[0].F3.InnerPointCodeList[21 + n]; sheet.Cells[n + 5, 3] = _projectData[0].F3.EndPointCodeList[21 + n]; } sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F3-2.pdf"); } else { return(false); } // 超过42条线暂时不支持 11.14 book.Save(); book.Close(false, Missing.Value, Missing.Value); //关闭打开的表 xls.Quit(); //Excel sheet = null; book = null; xls = null; GC.Collect(); return(true); } catch (Exception e) { var s = e.ToString(); return(false); } }
private void Proceso( ) { string country = ""; string city = ""; int i = 2; Process.GetCurrentProcess().PriorityBoostEnabled = true; // Aftësimi i përforcimit të prioritetit të procesit aktual Process.GetCurrentProcess().PriorityClass = ProcessPriorityClass.RealTime; // Caltimi i prioritetit të procesit aktual në RealTime // Of course this only affects the main thread rather than child threads. Thread.CurrentThread.Priority = ThreadPriority.Highest; if (File.Exists(excelFile)) // kontrollim nëse skedari ekziston, masë parandaluese // në rast se rastësisht skedari fshihet ose disku ose usb memoria dëmtohet ose largohet. { xlApp = new Microsoft.Office.Interop.Excel.Application(); //Krimi i objekteve COM. Krijon një objekt COM për çdo gjë që referencohet xlWorkbook = xlApp.Workbooks.Open(excelFile); xlWorksheet = xlWorkbook.Sheets[1]; xlRange = xlWorksheet.UsedRange; rowCount = xlRange.Rows.Count; setTotalCount(); city = ""; //qarkullimi nëpër rreshta dhe qeliza për lexim dhe shkrim të vlerës sonë të fituar //excel nuk është i bazuar nga zero!! i fillon nga 2 që të anashkalohet titulli for (i = 2; i <= rowCount - 2; i++) { var cell1 = xlRange.Cells[i, 1]; // nxjerrja e të dhënës në formë objekti COM në rreshtin i dhe shtyllën 1 city = cell1.Value2.ToString(); // konvertimi i formës COM në string if (city.Length > 0) // Nëse Emërtim i qytetit mungon atëherë anashkalohet { try { country = LookForCountry(city); // Kërkon për emrin e shtetit } catch (Exception ex) { MessageBox.Show(ex.Message); break; } if (country.Length > 0) { processed++; unprocessed = rowCount - processed; percentage = (processed / rowCount); } setProcessed(); setUnprocessed(); setPercentage(); xlRange.Cells[i, 2] = country; // Nëse gjendet e vëmë emrin e shtetit në qelizën e dytë të rreshtit i. } } } Process.GetCurrentProcess().PriorityClass = ProcessPriorityClass.Normal; // Pas kryerjes së punës e kthejmë //vlerën e prioritet në prioritet normal // Of course this only affects the main thread rather than child threads. Thread.CurrentThread.Priority = ThreadPriority.Normal; string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); // marrja e shtegut të Desktop. xlWorkbook.SaveAs(Path.Combine(path, "Exported_" + naked + ".xlsx")); // ruajtja e materialit të përpunuar në emër të ri Marshal.ReleaseComObject(xlRange); Marshal.ReleaseComObject(xlWorksheet); //mbyllja dhe lirimi i objetkit xlWorkbook.Close(); Marshal.ReleaseComObject(xlWorkbook); //mbyllja dhe lirimi i objektit xlApp.Quit(); Marshal.ReleaseComObject(xlApp); }