public static void openXL(string xlp) { xla = new Excel.Application(); xlw = xla.Workbooks.Open(xlp); xls = xlw.Worksheets.get_Item(1); xlr = xls.UsedRange; }
public void readExcel() { string valueString = string.Empty; objExcelApp = new Microsoft.Office.Interop.Excel.Application(); objBooks = (Excel.Workbooks)objExcelApp.Workbooks; //Open the workbook containing the address data. objBook = objBooks.Open(@"C:\Temp\data\Test.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //Get a reference to the first sheet of the workbook. objSheets = objBook.Worksheets; objSheet = (Excel._Worksheet)objSheets.get_Item(1); //Select the range of data containing the addresses and get the outer boundaries. rngLast = objSheet.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell); long lLastRow = rngLast.Row; long lLastCol = rngLast.Column; // Iterate through the data and concatenate the values into a comma-delimited string. for (long rowCounter = 1; rowCounter <= lLastRow; rowCounter++) { for (long colCounter = 1; colCounter <= lLastCol; colCounter++) { //Write the next value into the string. Excel.Range cell = (Excel.Range)objSheet.Cells[rowCounter, colCounter]; string cellvalue = cell.Value.ToString(); //TODO: add your business logic for retrieve cell value } } }
/// <summary> /// Class constructor does all the work /// </summary> /// <param name="excelApp"></param> /// <param name="fileName"></param> public StudentWorkbook( Excel.Application excelApp, string fileName ) { try // to open the student's spreadsheet { excelWorkbook = excelApp.Workbooks.Open( fileName, 0, true, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, // read only false, 0, true, false, false ); } catch ( Exception e ) { Console.WriteLine( "error: " + e.Message ); Console.WriteLine( "Could not open spreadsheet " + fileName ); } Excel.Sheets excelSheets = excelWorkbook.Worksheets; // get the Worksheets collection Excel.Worksheet excelWorksheet = excelSheets[ 1 ]; // get the first one // get the Team Number cell Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range( "B4", "B4" ); // try to convert this cell to an integer if ( ( teamNumber = TryForInt( excelCell.Value ) ) == 0 ) { Console.WriteLine( "\nTeam number invalid in " + fileName + "\n" ); } // get the scores cells scores = excelWorksheet.get_Range( "B7", "B15" ); // get the Additional Comments cell comments = excelWorksheet.get_Range( "B18", "B18" ); } // end of StudentWorkbook()
public void addData(int row, int col, string data, string cell1, string cell2, string format) { worksheet.Cells[row, col] = data; workSheet_range = worksheet.get_Range(cell1, cell2); workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb(); workSheet_range.NumberFormat = format; }
public Insert(Excel.Application application, CultureInfo culture) { try { _excelapp = application; _culture = culture; _range = application.ActiveWindow.RangeSelection; } catch (Exception ex) { new FrmException(_excelapp, ex).ShowDialog(); } }
public Date(Excel.Application application, CultureInfo culture) { try { _excelapp = application; _culture = culture; _range = application.ActiveWindow.RangeSelection; _minDateTime = new DateTime(1900, 1, 1); } catch (Exception ex) { new FrmException(_excelapp, ex).ShowDialog(); } }
public Gen(Excel.Application application, CultureInfo culture) { try { _excelapp = application; _culture = culture; _range = application.ActiveWindow.RangeSelection; _singlesel = _range.Columns.Count == 1 && _range.Rows.Count == 1; _fnb = _singlesel ? new FieldNumber("Number of rows", "", 100, 1, true) : null; } catch (Exception ex) { new FrmException(_excelapp, ex).ShowDialog(); } }
//Constructor that takes an Excel range as an argument //The data is stored in the cells of the range public NormalDistribution(Excel.Range r) { _cells = r; _size = r.Count; _mean = __mean(); _variance = __variance(); _standard_deviation = __standard_deviation(); _error = __error(); _ranked_errors = __rank_errors(); }
public void generatExcelNoSubmit(string f) { string file_Name = f; m_oExcelApp = new Excel.Application(); m_oExcelApp.Visible = false; m_oExcelApp.UserControl = false; m_oSheet = null; excelRange = null; try { m_oBook = m_oExcelApp.Workbooks.Add(missing); Excel.Sheets sheets = m_oBook.Worksheets; //Add new 4 Sheet //sheets.Add(System.Type.Missing, m_oBook.Sheets[3], 1, Excel.XlSheetType.xlWorksheet); //Product Sheet [Sheet1] m_oSheet = (Excel._Worksheet)m_oBook.Sheets[1]; m_oSheet.Activate(); m_oSheet.Name = "รายการเสนอซื้อ"; SetData_to_SheetNoSubmit(); string template = Application.StartupPath; string strRunReport = file_Name; //string strPass = "******"; password m_oBook.SaveAs(strRunReport, Excel.XlFileFormat.xlWorkbookNormal, null, null, null, null, Excel.XlSaveAsAccessMode.xlShared, null, null, null, null, null); m_oExcelApp.Visible = true; } catch (interop.COMException ex) { MessageBox.Show("Error accessing Excel: " + ex.ToString()); } catch (Exception ex) { MessageBox.Show("Error: " + ex.ToString()); } finally { if (m_oExcelApp == null) { m_oExcelApp.Quit(); m_oExcelApp = null; GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } } }
private void Sheet1_Startup(object sender, System.EventArgs e) { rngUp = this.Range["B2", missing]; rngDown = this.Range["B3", missing]; rngInterest = this.Range["B4", missing]; rngInitial = this.Range["B5", missing]; rngPeriods = this.Range["B6", missing]; rngExercise = this.Range["B7", missing]; rngRuns = this.Range["B8", missing]; rngRemote = this.Range["B9", missing]; }
public ReportBuilder() { app = new Excel.Application(); appBooks = app.Workbooks; currentBook = appBooks.Add(Missing.Value); sheets = currentBook.Worksheets; currentSheet = (Excel._Worksheet)sheets.get_Item(1); range = currentSheet.get_Range("A1", Missing.Value); charts = currentSheet.ChartObjects(Type.Missing); chartObject = charts.Add(400, LastIndex, chartWidth, chartHeight); }
public static void openXL(string xlp) { try { xla = new Excel.Application(); xlw = xla.Workbooks.Open(xlp); xls = xlw.Sheets[1]; xlr = xls.UsedRange; } catch (Exception e) { w(" FAIL: " + e.ToString()); } }
internal Worksheet( ExcelApplication excel, Workbook workbook, Excel.Worksheet _worksheet, ObjectDisposedCallback disposeCallback, bool displayGridLines) { this.ExcelApplication = excel; this.Workbook = workbook; this._worksheet = _worksheet; this._cells = _worksheet.Cells; this.disposeCallback = disposeCallback; this.Name = _worksheet.Name; DisplayGridlines = displayGridLines; }
protected override void subWrite() { fileColumn = worksheet.Columns[2]; directoryColumn = worksheet.Columns[1]; writeHeader(); writeFileNamesToColumnB(linkSourceDirectory); DirectoryInfo[] subDirectories = linkSourceDirectory.GetDirectories(); foreach (var sub in subDirectories) { writeDirectoryPathToColumnA(sub); writeFileNamesToColumnB(sub); } }
public void addData(int row, int col, string content, bool bold, bool italic, bool underline, int? size, string fontName) { //Data worksheet.Cells[row, col] = content; //Cell format workSheet_range = worksheet.get_Range(string.Empty + new string((char)(col + 64), 1) + row.ToString()); workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb(); workSheet_range.Font.Bold = bold; workSheet_range.Font.Italic = italic; workSheet_range.Font.Underline = underline; if (size != null) workSheet_range.Font.Size = size; if(fontName != null) workSheet_range.Font.Name = fontName; }
public static void ReplaceExcelRange(Range com, InputSample input) { bool done = false; while (!done) { try { com.Value2 = input.GetInputArray(); done = true; } catch (Exception) { } } }
public static Excel1.Range ReadExcel(string filePath, int workSheetNumber) { try { string startupPath = System.IO.Directory.GetCurrentDirectory(); string outPutDirectory = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().CodeBase); string xmlfilepath = Path.Combine(outPutDirectory + filePath); string xmlfile_path = new Uri(xmlfilepath).LocalPath; xlApp = new Excel1.Application(); xlWorkBook = xlApp.Workbooks.Open(xmlfile_path, 0, false, 5, "", "", true, Excel1.XlPlatform.xlWindows, "\t", true, false, 0, true, true, false); xlWorkSheet = (Excel1.Worksheet)xlWorkBook.Worksheets.get_Item(workSheetNumber); return range = xlWorkSheet.UsedRange; } catch (Exception) { return null; } }
public void createHeaders(int row, int col, string htext, string cell1, string cell2, int mergeColumns, string b, bool font, int size, string fcolor) { worksheet.Cells[row, col] = htext; workSheet_range = worksheet.get_Range(cell1, cell2); //workSheet_range.Merge(mergeColumns); switch (b) { case "YELLOW": workSheet_range.Interior.Color = System.Drawing.Color.Gray.ToArgb(); break; case "GRAY": workSheet_range.Interior.Color = System.Drawing.Color.Gray.ToArgb(); break; case "GAINSBORO": workSheet_range.Interior.Color = System.Drawing.Color.Gainsboro.ToArgb(); break; case "Turquoise": workSheet_range.Interior.Color = System.Drawing.Color.Turquoise.ToArgb(); break; case "PeachPuff": workSheet_range.Interior.Color = System.Drawing.Color.PeachPuff.ToArgb(); break; default: // workSheet_range.Interior.Color = System.Drawing.Color..ToArgb(); break; } workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb(); workSheet_range.Font.Bold = font; workSheet_range.ColumnWidth = size; if (fcolor.Equals("")) { workSheet_range.Font.Color = System.Drawing.Color.White.ToArgb(); } else { workSheet_range.Font.Color = System.Drawing.Color.Red; } }
public SpreadsheetSplitter(string filename, string destination_folder) { excel = new Excel.Application(); excel.Visible = false; excel.DisplayAlerts = false; workbook = excel.Workbooks.Open(filename, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); first_sheet = (Excel.Worksheet)workbook.Sheets.get_Item(1); string main_sheet_name = first_sheet.Name; output_folder = destination_folder; sheet_range = first_sheet.UsedRange; heading_column_start = 1; heading_column_end = sheet_range.Columns.Count - 1; heading_row_start = 1; heading_row_end = 2; start_row = 3; start_col = GetExcelColumnName(heading_column_start); end_col = GetExcelColumnName(heading_column_end); }
//Constructor that takes an array of TreeNodes as an argument public NormalDistribution(AST.Range[] range_nodes, Excel.Application app) { //turn the dictionary into an Excel.Range Excel.Range r1 = range_nodes[0].GetCOMObject(app); foreach (AST.Range range_node in range_nodes) { try //in a try-catch because Union malfunctioned in one observed case { r1 = app.Union(r1, range_node.GetCOMObject(app)); } catch { } } _cells = r1; _size = r1.Count; _mean = __mean(); _variance = __variance(); _standard_deviation = __standard_deviation(); _error = __error(); _ranked_errors = __rank_errors(); }
//constructor public CXLRTemplateDef(Excel.Range dsRange){ for(int i = 1; i <= dsRange.Rows.Count; i++) { Excel.Range vCurRow = (Excel.Range)dsRange.Rows[i, Type.Missing]; String vCurDefValue = ExcelSrv.ExtractCellValue(vCurRow.Cells[1, 1]); if (!String.IsNullOrEmpty(vCurDefValue) && vCurDefValue.StartsWith(csGrpHeaderDef)) { String vFldName = ExcelSrv.ExtractFieldName(vCurDefValue); this._headerFormats.Add(vFldName, vCurRow); } else if(!String.IsNullOrEmpty(vCurDefValue) && vCurDefValue.StartsWith(csGrpFooterDef)) { String vFldName = ExcelSrv.ExtractFieldName(vCurDefValue); this._footerFormats.Add(vFldName, vCurRow); } else if(!String.IsNullOrEmpty(vCurDefValue) && (vCurDefValue.Equals(csDetailsDef))) { this._detailsRng = vCurRow; } else if(!String.IsNullOrEmpty(vCurDefValue) && (vCurDefValue.Equals(csTotalsDef))) { this._totalsRng = vCurRow; } } }
/// <summary> /// Конструктор для инициалицазии переменных класса /// </summary> /// <param name="fileExcel">Путь к файлу для открытия</param> public ExcelApplication(string fileExcel) : base(fileExcel) { try { excelApp = new Excel.Application(); excelBook = excelApp.Workbooks.Open(fileToExcel); excelSheet = (Excel.Worksheet)excelBook.Worksheets.get_Item(1); excelRange = excelSheet.UsedRange; } catch { MessageBox.Show("Заданный файл Excel не доступен!"); //excelBook.Close(falseObj,missingObj,missingObj); excelApp.Quit(); excelBook = null; excelApp = null; Application.Exit(); } }
public void OpenExcelDoc(String ofd) { Thread.Sleep(1000); application = new MExcel.Application(); try { workBook = application.Workbooks.Open(ofd); } catch (System.Runtime.InteropServices.COMException) { MessageBox.Show("Не удалось открыть файл Excel","Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Information); application.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(application); System.GC.Collect(); application = new MExcel.Application(); workBook = application.Workbooks.Open(ofd); } workSheet = (MExcel.Worksheet)workBook.Worksheets.get_Item(4); range = workSheet.UsedRange; }
/// <summary> /// Открыть файл - адрес берется из характеристики /// </summary> public void OpenExcelDoc(/*String ofd*/) { Thread.Sleep(1000); application = new MExcel.Application(); try { workBook = application.Workbooks.Open(characteristic.FileAdres); } catch (System.Runtime.InteropServices.COMException error) { //MessageBox.Show(error.Message, "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Information); application.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(application); System.GC.Collect(); application = new MExcel.Application(); workBook = application.Workbooks.Open(characteristic.FileAdres); } workSheet = (MExcel.Worksheet)workBook.Worksheets.get_Item(characteristic.NumberList); range = workSheet.UsedRange; }
private void btnFetch_Click(object sender, RibbonControlEventArgs e) { activeWorksheet = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet); firstRow = activeWorksheet.get_Range("A1"); //firstRow.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown); newFirstRow = activeWorksheet.get_Range("A1"); try { firstRow.Value2 = "Fetching Data Please Wait..."; string url = "http://localhost:5000/sample/getdata"; WebClient wc = new WebClient(); wc.DownloadStringCompleted += HttpsCompleted; wc.DownloadStringAsync(new Uri(url)); } catch(Exception ex) { firstRow.Value2 = "An Error occured while accessing the service"; } }
private void HttpsCompleted(object sender, DownloadStringCompletedEventArgs e) { if (e.Error == null) { string response = e.Result; DataTable xmlData = Newtonsoft.Json.JsonConvert.DeserializeObject<DataTable>(response); //DataTable dtObj = new DataTable("MyExcel"); try { activeWorksheet.UsedRange.ClearContents(); foreach (DataColumn cols in xmlData.Columns) { newFirstRow.Value2 = cols.ColumnName; newFirstRow = newFirstRow.Next; } newFirstRow = activeWorksheet.Cells[firstRow.Row + 1, firstRow.Column]; object[] data = new object[xmlData.Columns.Count]; foreach (DataRow dr in xmlData.Rows) { data = new object[xmlData.Columns.Count]; data = dr.ItemArray; //= (Excel.Range)activeWorksheet.Cells[newFirstRow.Row, firstRow.Column]; ; Excel.Range newRangeData = newFirstRow.get_Resize(1, xmlData.Columns.Count); newRangeData.set_Value(Type.Missing, data); newFirstRow = activeWorksheet.Cells[newFirstRow.Row + 1, firstRow.Column]; } firstRow.get_Offset(1, 1).get_Resize(xmlData.Rows.Count, xmlData.Columns.Count).Style = "Currency"; } catch(Exception ex) { firstRow.Value2 = "An Error occured while performing operation on the excel"; } } else { firstRow.Value2 = "An Error occured while accessing the service"; } }
public void AddInfo(FunctionTableItem[] fun, ValueIs value = ValueIs.Magnitude, bool addChart = false) { Excel.Series series; Excel.Range temp; range = range.get_Resize(2, fun.Length); range.set_Value(Missing.Value, GetArray(fun, value)); if (addChart) { LastIndex += chartHeight + 10; chartObject = charts.Add(400, LastIndex, chartWidth, chartHeight); } currentChart = chartObject.Chart; series = ((Excel.SeriesCollection) currentChart.SeriesCollection(Type.Missing)).NewSeries(); temp = range.get_Resize(1, fun.Length); series.Values = temp; series.XValues = temp.get_Offset(1, 0); currentChart.ChartType = Excel.XlChartType.xlXYScatterSmoothNoMarkers; range = range.get_Offset(3, 0); }
public void excelWrite() { //if (File.Exists(_filePath)) //{ // File.Delete(_filePath); // FileStream fs = File.Create(_filePath); // fs.Close(); //} int i=0; ExcelApp = new Excel.Application(); ExcelApp.Visible = true; WorkBookExcel = ExcelApp.Workbooks.Open(_filePath, false); //открываем книгу WorkSheetExcel = (Excel.Worksheet)WorkBookExcel.Sheets[1]; //Получаем ссылку на лист 1 foreach (string n in _tNames) { RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 1]; RangeExcel.Value = _tNames[i]; RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 2]; RangeExcel.Value = _tTels[i]; RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 3]; RangeExcel.Value = _tTels2[i]; RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 4]; RangeExcel.Value = _tTels3[i]; RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 5]; RangeExcel.Value = _tEmail[i]; RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 6]; RangeExcel.Value = _tDiv[i]; RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 7]; RangeExcel.Value = _tPos[i]; i++; } WorkBookExcel.Close(true, Type.Missing, Type.Missing); ExcelApp.Quit(); GC.Collect(); }
static void Main(string[] args) { var ExcelPath = @"C:\Users\SUJITH KALARI\Documents\Visual Studio 2015\Projects\ExcelJson\ExcelJson\bin\Debug\Bookks.xlsx"; var sheetName = ""; var destinationPath = @"C:\Users\SUJITH KALARI\Documents\Visual Studio 2015\Projects\ExcelJson\ExcelJson\bin\Debug\E2J.json"; var json = ""; Dictionary <string, int> sheet = new Dictionary <string, int>(); sheet.Add("Text", 1); sheet.Add("Image", 2); sheet.Add("Audio", 3); sheet.Add("Video", 4); Excel.Application xlApp = null; Excel.Workbook xlWorkBook = null; Excel.Worksheet xlWorkSheet = null; Excel.Range range = null; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Open(ExcelPath); var connectionString = String.Format(@" Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Extended Properties=""Excel 12.0 Xml;HDR=YES""", ExcelPath); File.AppendAllText(destinationPath, "{ AssetList :["); foreach (System.Collections.Generic.KeyValuePair <string, int> sheetInfo in sheet) { sheetName = sheetInfo.Key; int sheetNo = sheetInfo.Value; File.AppendAllText(destinationPath, sheetName); //Creating and opening a data connection to the Excel sheet using (var conn = new OleDbConnection(connectionString)) { conn.Open(); var cmd = conn.CreateCommand(); cmd.CommandText = String.Format( @"SELECT * FROM [{0}$]", sheetName ); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(sheetNo); range = xlWorkSheet.UsedRange; int rowRange = range.Rows.Count; int columnRange = range.Columns.Count; int columnCount = 0; Console.WriteLine(columnRange); Console.WriteLine(rowRange); using (var rdr = cmd.ExecuteReader()) { var query = (from DbDataRecord row in rdr select row).Select(x => { //dynamic item = new ExpandoObject(); Dictionary <string, object> item = new Dictionary <string, object>(); for (columnCount = 0; columnCount < columnRange; columnCount++) { item.Add(rdr.GetName(columnCount), x[columnCount]); } return(item); }); //Generates JSON from the LINQ query json = JsonConvert.SerializeObject(query); //Write the file to the destination path File.AppendAllText(destinationPath, json); } } File.AppendAllText(destinationPath, "]"); } File.AppendAllText(destinationPath, "}"); }
void outDataFimlsExcel() { //TH có dữ liệu để ghi if (dtgvRevenue.Rows.Count > 0) { //Khai báo và khởi tạo các đối tượng Excel.Application exApp = new Excel.Application(); Excel.Workbook exBook = exApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet exSheet = (Excel.Worksheet)exBook.Worksheets[1]; //Định dạng chung Excel.Range tenCuaHang = (Excel.Range)exSheet.Cells[1, 1]; tenCuaHang.Font.Size = 14; tenCuaHang.Font.Bold = true; tenCuaHang.Font.Color = Color.Blue; tenCuaHang.Value = "BẢNG DANH SÁCH DOANH THU PHIM"; Excel.Range dcCuaHang = (Excel.Range)exSheet.Cells[2, 1]; dcCuaHang.Font.Size = 13; dcCuaHang.Font.Bold = true; dcCuaHang.Font.Color = Color.Blue; dcCuaHang.Value = "Copyright: Nguyễn Văn Cung"; Excel.Range dtCuaHang = (Excel.Range)exSheet.Cells[3, 1]; dtCuaHang.Font.Size = 13; dtCuaHang.Font.Bold = true; dtCuaHang.Font.Color = Color.Blue; dtCuaHang.Value = "Điện thoại: 0399544543"; Excel.Range header = (Excel.Range)exSheet.Cells[5, 2]; exSheet.get_Range("B5:G5").Merge(true); header.Font.Size = 13; header.Font.Bold = true; header.Font.Color = Color.Red; header.Value = "DANH SÁCH DOANH THU PHIM"; //Định dạng tiêu đề bảng exSheet.get_Range("A7:E7").Font.Bold = true; exSheet.get_Range("A7:E7").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; exSheet.get_Range("A7").Value = "STT"; exSheet.get_Range("B7").Value = "Tên Phim"; exSheet.get_Range("B7").ColumnWidth = 20; exSheet.get_Range("C7").Value = "Thể loại"; exSheet.get_Range("C7").ColumnWidth = 30; exSheet.get_Range("D7").Value = "Ngày khởi chiếu"; exSheet.get_Range("D7").ColumnWidth = 20; exSheet.get_Range("E7").Value = "Ngày kết thúc"; exSheet.get_Range("E7").ColumnWidth = 20; exSheet.get_Range("F7").Value = "Tổng chi phí"; exSheet.get_Range("F7").ColumnWidth = 20; exSheet.get_Range("G7").Value = "Đạo diễn"; exSheet.get_Range("G7").ColumnWidth = 20; exSheet.get_Range("H7").Value = "Tổng chi phí"; exSheet.get_Range("I7").Value = "Doanh thu"; //In dữ liệu for (int i = 0; i < dtgvRevenue.Rows.Count - 1; i++) { exSheet.get_Range("A" + (i + 8).ToString() + ":G" + (i + 8).ToString()).Font.Bold = false; exSheet.get_Range("A" + (i + 8).ToString()).Value = (i + 1).ToString(); exSheet.get_Range("B" + (i + 8).ToString()).Value = dtgvRevenue.Rows[i].Cells[0].Value; exSheet.get_Range("C" + (i + 8).ToString()).Value = dtgvRevenue.Rows[i].Cells[1].Value; exSheet.get_Range("D" + (i + 8).ToString()).Value = dtgvRevenue.Rows[i].Cells[2].Value; exSheet.get_Range("E" + (i + 8).ToString()).Value = dtgvRevenue.Rows[i].Cells[3].Value; exSheet.get_Range("F" + (i + 8).ToString()).Value = dtgvRevenue.Rows[i].Cells[4].Value; exSheet.get_Range("G" + (i + 8).ToString()).Value = dtgvRevenue.Rows[i].Cells[0].Value; exSheet.get_Range("H" + (i + 8).ToString()).Value = dtgvRevenue.Rows[i].Cells[5].Value; exSheet.get_Range("I" + (i + 8).ToString()).Value = dtgvRevenue.Rows[i].Cells[6].Value; } exSheet.Name = "Danh sách doanh thu phim"; exBook.Activate(); dlgSave.Filter = "Excel Document(*.xls)|*.xls |Word Document(*.doc) |*.doc|All files(*.*)|*.*"; dlgSave.FilterIndex = 1; dlgSave.AddExtension = true; dlgSave.DefaultExt = ".xls"; if (dlgSave.ShowDialog() == System.Windows.Forms.DialogResult.OK) { exBook.SaveAs(dlgSave.FileName.ToString()); } exApp.Quit(); } else { MessageBox.Show("Không có danh sách hàng để in"); } }
/// <summary> /// Method to add an excel rows /// </summary> /// <param name="startRange"></param> /// <param name="rowCount"></param> /// <param name="colCount"></param> /// <param name="values"></param> private void AddExcelRows(string startRange, int rowCount, int colCount, object values) { _range = _sheet.get_Range(startRange, _optionalValue); _range = _range.get_Resize(rowCount, colCount); _range.set_Value(_optionalValue, values); }
static void Main(string[] args) { //Open Chrome Driver IWebDriver driver = new ChromeDriver(@"C:\Users\Anneline\source\repos\"); //Open Excel Spreadsheet to read data var excelApp = new Excel.Application(); excelApp.Visible = true; Excel.Workbook xlWorkbook = excelApp.Workbooks.Open(@"C:\DATA\UserLoginData.xlsx"); Excel._Worksheet xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; for (int i = 2; i <= rowCount; i++) //Rowcount { for (int j = 1; j <= colCount; j++) //Columncount { //Open CaMS Dev Environment var UrlLink = (Excel.Range)xlWorksheet.Cells[i, j]; string WebLink = UrlLink.Value2.ToString(); driver.Navigate().GoToUrl(WebLink); driver.Manage().Window.Maximize(); driver.Manage().Timeouts().ImplicitWait = TimeSpan.FromSeconds(10); j = j + 1; //Enter the Username var UserN = (Excel.Range)xlWorksheet.Cells[i, j]; string Username = UserN.Value2.ToString(); IWebElement UserName = driver.FindElement(By.Id("Input_Email")); UserName.SendKeys(Username); j = j + 1; //Enter the Password var PasSWord = (Excel.Range)xlWorksheet.Cells[i, j]; string PassW = PasSWord.Value2.ToString(); IWebElement PassWOrd = driver.FindElement(By.Id("Input_Password")); PassWOrd.SendKeys(PassW); j = j + 1; //Click on the Login Button IWebElement LoginButton1 = driver.FindElement(By.CssSelector("#account > div:nth-child(4) > div > button")); LoginButton1.Click(); //Click on the Logout Button Actions action1 = new Actions(driver); action1.MoveToElement(driver.FindElement(By.XPath("//*[@id='navbarDropdownProfile']/img"))).Build().Perform(); IWebElement manageProfile = driver.FindElement(By.XPath("//*[@id='navbarDropdownProfile']/img")); manageProfile.Click(); IWebElement LogInoutButton2 = driver.FindElement(By.XPath("/html/body/div/main/nav/div/div[2]/ul/li[4]/div/a[3]")); LogInoutButton2.Click(); //Click on the Login Button IWebElement LoginButton3 = driver.FindElement(By.XPath("/html/body/div/div/div/div[1]/div/div[2]/div[2]/div[1]/div[2]/a")); LoginButton3.Click(); } } //Cleanup and Quit Excel Spreadsheet GC.Collect(); GC.WaitForPendingFinalizers(); //release com objects to fully kill excel process from running in the background Marshal.ReleaseComObject(xlRange); Marshal.ReleaseComObject(xlWorksheet); //close and release xlWorkbook.Close(); Marshal.ReleaseComObject(xlWorkbook); //quit and release excelApp.Quit(); Marshal.ReleaseComObject(excelApp); //Quit Browser driver.Quit(); }
void Application_SheetSelectionChange(object Sh, ExcelRaw.Range Target) { bbTransformations.clearTransformationsRibbon(this); }
public string convertFiles(IList <string> filePaths) { m_filePaths = filePaths; m_fileCount = m_filePaths.Count; int fileIndex = 0; ArrayList sheet1ValueList = new ArrayList(); ArrayList sheet2ValueList = new ArrayList(); Cross_TopTag topTag; Cross_Data[] data; Cross_Paragraphs[][] paragraphs; Cross_Qas[][][] qas; Cross_Answers[][][][] answers; ETRI_TopTag EtopTag; object[,] sheet1ValueArray; object[,] sheet2ValueArray; int totalCountQas = 0; int totalCountParagraphs = 0; int sheet1TotalRowCount = 0; int sheet2TotalRowCount = 0; int exceptionDatacount = 0; int exceptionDow = 0; ArrayList splitedFileName = new ArrayList(); foreach (var item in m_filePaths) { string[] temp; m_path = item; var missing = Type.Missing; temp = m_path.Split('_'); splitedFileName.Add(temp); objApp = new Excel.Application(); objWorkbooks = objApp.Workbooks; int countParagraphs = 0; int countQas = 0; int currentRow = 0; bool excelOpen = false; try { if (m_currentConvertingMode == convertingMode.CJSONToCExcel) { #region JSON -> Excel 변환 // ** name1 영역 파싱 topTag = JsonConvert.DeserializeObject <Cross_TopTag>(File.ReadAllText(m_path)); // name2 영역 파싱 data = new Cross_Data[topTag.data.Count]; for (int i = 0; i < data.Length; i++) { data[i] = JsonConvert.DeserializeObject <Cross_Data>(topTag.data[i].ToString()); } // ** name3 영역 파싱 paragraphs = new Cross_Paragraphs[data.Length][]; for (int i = 0; i < data.Length; i++) { paragraphs[i] = new Cross_Paragraphs[data[i].paragraphs.Count]; for (int j = 0; j < data[i].paragraphs.Count; j++) { paragraphs[i][j] = JsonConvert.DeserializeObject <Cross_Paragraphs>(data[i].paragraphs[j].ToString()); countParagraphs++; totalCountParagraphs++; } } // ** name4 영역 파싱 qas = new Cross_Qas[data.Length][][]; for (int i = 0; i < data.Length; i++) { qas[i] = new Cross_Qas[paragraphs[i].Length][]; for (int j = 0; j < paragraphs[i].Length; j++) { qas[i][j] = new Cross_Qas[paragraphs[i][j].qas.Count]; for (int k = 0; k < paragraphs[i][j].qas.Count; k++) { qas[i][j][k] = JsonConvert.DeserializeObject <Cross_Qas>(paragraphs[i][j].qas[k].ToString()); countQas++; totalCountQas++; } } } // ** name5 영역 파싱 answers = new Cross_Answers[data.Length][][][]; for (int i = 0; i < data.Length; i++) { answers[i] = new Cross_Answers[paragraphs[i].Length][][]; for (int j = 0; j < paragraphs[i].Length; j++) { answers[i][j] = new Cross_Answers[qas[i][j].Length][]; for (int k = 0; k < qas[i][j].Length; k++) { answers[i][j][k] = new Cross_Answers[qas[i][j][k].answers.Count]; for (int m = 0; m < qas[i][j][k].answers.Count; m++) { answers[i][j][k][m] = JsonConvert.DeserializeObject <Cross_Answers>(qas[i][j][k].answers[m].ToString()); } } } } // ** sheet1ValueArray & sheet2ValueArray 영역 크기 지정 sheet1RowCount = countQas; sheet2RowCount = countParagraphs; sheet1ValueArray = new object[sheet1RowCount, sheet1ColCount]; sheet2ValueArray = new object[sheet2RowCount, sheet2ColCount]; // ** sheet1ValueArray & sheet2ValueArray에 데이터 입력 // * paragraph 순번 & name1 영역 for (int row = 0; row < sheet2RowCount; row++) { sheet2ValueArray[row, 0] = sheet2TotalRowCount + 1; sheet2ValueArray[row, 1] = topTag.version; sheet2ValueArray[row, 2] = topTag.creator; sheet2ValueArray[row, 3] = topTag.progress; sheet2ValueArray[row, 4] = topTag.formatt; sheet2ValueArray[row, 5] = topTag.time; sheet2ValueArray[row, 6] = topTag.check; sheet2ValueArray[row, 7] = topTag.firstfile; sheet2ValueArray[row, 8] = topTag.secondfile; sheet2TotalRowCount++; } // * name2 & name3 영역 currentRow = 0; for (int d = 0; d < data.Length; d++) { for (int p = 0; p < paragraphs[d].Length; p++) { sheet2ValueArray[currentRow, 9] = data[d].title; sheet2ValueArray[currentRow, 10] = paragraphs[d][p].context; sheet2ValueArray[currentRow, 11] = paragraphs[d][p].context_en; sheet2ValueArray[currentRow, 12] = paragraphs[d][p].context_tagged; currentRow++; } } // * name4 영역 currentRow = 0; int currentParaNum = 1; for (int d = 0; d < data.Length; d++) { for (int p = 0; p < paragraphs[d].Length; p++) { for (int q = 0; q < qas[d][p].Length; q++) { sheet1ValueArray[currentRow, 0] = sheet1TotalRowCount + 1; sheet1ValueArray[currentRow, 1] = qas[d][p][q].id; sheet1ValueArray[currentRow, 2] = qas[d][p][q].confuseQt1; sheet1ValueArray[currentRow, 3] = qas[d][p][q].confuseQf1; sheet1ValueArray[currentRow, 4] = qas[d][p][q].confuseSat1; sheet1ValueArray[currentRow, 5] = qas[d][p][q].confuseLat1; sheet1ValueArray[currentRow, 6] = qas[d][p][q].question; sheet1ValueArray[currentRow, 7] = qas[d][p][q].question_en; sheet1ValueArray[currentRow, 8] = qas[d][p][q].question_tagged1; sheet1ValueArray[currentRow, 9] = qas[d][p][q].questionType1; sheet1ValueArray[currentRow, 10] = qas[d][p][q].questionFocus1; sheet1ValueArray[currentRow, 11] = qas[d][p][q].questionSAT1; sheet1ValueArray[currentRow, 12] = qas[d][p][q].questionLAT1; sheet1ValueArray[currentRow, 13] = qas[d][p][q].confuseQt2; sheet1ValueArray[currentRow, 14] = qas[d][p][q].confuseQf2; sheet1ValueArray[currentRow, 15] = qas[d][p][q].confuseSat2; sheet1ValueArray[currentRow, 16] = qas[d][p][q].confuseLat2; sheet1ValueArray[currentRow, 17] = qas[d][p][q].question_tagged2; // sheet1ValueArray[currentRow, 18] = qas[d][p][q].questionType2; // sheet1ValueArray[currentRow, 19] = qas[d][p][q].questionFocus2; // sheet1ValueArray[currentRow, 20] = qas[d][p][q].questionSAT2; // sheet1ValueArray[currentRow, 21] = qas[d][p][q].questionLAT2; sheet1ValueArray[currentRow, 22] = qas[d][p][q].confuseQt3; sheet1ValueArray[currentRow, 23] = qas[d][p][q].confuseQf3; sheet1ValueArray[currentRow, 24] = qas[d][p][q].confuseSat3; sheet1ValueArray[currentRow, 25] = qas[d][p][q].confuseLat3; sheet1ValueArray[currentRow, 26] = qas[d][p][q].question_tagged3; sheet1ValueArray[currentRow, 27] = qas[d][p][q].questionType3; sheet1ValueArray[currentRow, 28] = qas[d][p][q].questionFocus3; sheet1ValueArray[currentRow, 29] = qas[d][p][q].questionSAT3; sheet1ValueArray[currentRow, 30] = qas[d][p][q].questionLAT3; sheet1ValueArray[currentRow, 37] = currentParaNum; currentRow++; sheet1TotalRowCount++; } currentParaNum++; } } // * name5 영역 currentRow = 0; for (int d = 0; d < data.Length; d++) { for (int p = 0; p < paragraphs[d].Length; p++) { for (int q = 0; q < qas[d][p].Length; q++) { if (qas[d][p][q].answers.Count > 3) { return("정답의 개수가 3개 초과인 문제가 있습니다.\r\n파일: " + m_path); } int answerStartColNum = 31; for (int a = 0; a < answers[d][p][q].Length; a++) { sheet1ValueArray[currentRow, answerStartColNum] = answers[d][p][q][a].text; sheet1ValueArray[currentRow, answerStartColNum + 1] = answers[d][p][q][a].text_en; sheet1ValueArray[currentRow, answerStartColNum + 2] = answers[d][p][q][a].text_tagged; sheet1ValueArray[currentRow, answerStartColNum + 3] = answers[d][p][q][a].text_syn; sheet1ValueArray[currentRow, answerStartColNum + 4] = answers[d][p][q][a].answer_start; sheet1ValueArray[currentRow, answerStartColNum + 5] = answers[d][p][q][a].answer_end; answerStartColNum += 6; } currentRow++; } } } if ((++fileIndex) < m_fileCount) { sheet1ValueList.Add(sheet1ValueArray); sheet2ValueList.Add(sheet2ValueArray); continue; } //마지막 파일 ADD sheet1ValueList.Add(sheet1ValueArray); sheet2ValueList.Add(sheet2ValueArray); // 여러 sheetValueArray들을 각 작업량의 따라 나눠 하나로 통합 string[] separator = { "(", ")", "-", " " }; //제외할 요소들 int totalRowCount_sheet1 = 0; int totalRowCount_sheet2 = 0; for (int i = 0; i < fileIndex; i++) { string[] _temp = (string[])splitedFileName[i]; string[] splited = _temp[2].Split(separator, StringSplitOptions.RemoveEmptyEntries); //sheet1 작업 int startIndex = Convert.ToInt32(splited[0]); int endIndex = Convert.ToInt32(splited[1]); int length = endIndex - startIndex + 1; totalRowCount_sheet1 += length; int rowIndex_sheet1 = 0; int rowIndex_sheet2 = 0; object[,] temp_arrList = (object[, ])sheet1ValueList[i]; object[,] tempSheet1Value = new object[length, sheet1ColCount]; for (int j = startIndex - 1; j < endIndex; j++) { for (int k = 0; k < sheet1ColCount; k++) { tempSheet1Value[rowIndex_sheet1, k] = temp_arrList[j, k]; } rowIndex_sheet1++; } //sheet2 작업 startIndex = (int)tempSheet1Value[0, 37]; endIndex = (int)tempSheet1Value[rowIndex_sheet1 - 1, 37]; length = endIndex - startIndex + 1; totalRowCount_sheet2 += length; temp_arrList = (object[, ])sheet2ValueList[i]; object[,] tempSheet2Value = new object[length, sheet2ColCount]; for (int j = startIndex - 1; j < endIndex; j++) { for (int k = 0; k < sheet2ColCount; k++) { tempSheet2Value[rowIndex_sheet2, k] = temp_arrList[j, k]; } rowIndex_sheet2++; } sheet1ValueList.RemoveAt(i); sheet2ValueList.RemoveAt(i); sheet1ValueList.Insert(i, tempSheet1Value); sheet2ValueList.Insert(i, tempSheet2Value); } sheet1RowCount = totalRowCount_sheet1; sheet2RowCount = totalRowCount_sheet2; sheet1ValueArray = new object[sheet1RowCount, sheet1ColCount]; sheet2ValueArray = new object[sheet2RowCount, sheet2ColCount]; int sheet1RowIndex = 0; int sheet2RowIndex = 0; int _sheet1RowCount; int _sheet2RowCount; for (int i = 0; i < sheet1ValueList.Count; i++) { object[,] tempSheet1Value = (object[, ])sheet1ValueList[i]; object[,] tempSheet2Value = (object[, ])sheet2ValueList[i]; _sheet1RowCount = (int)(tempSheet1Value.Length / sheet1ColCount); for (int j = 0; j < _sheet1RowCount; j++) { for (int k = 0; k < sheet1ColCount; k++) { sheet1ValueArray[sheet1RowIndex, k] = tempSheet1Value[j, k]; } sheet1RowIndex++; } _sheet2RowCount = (int)(tempSheet2Value.Length / sheet2ColCount); for (int j = 0; j < _sheet2RowCount; j++) { for (int k = 0; k < sheet2ColCount; k++) { sheet2ValueArray[sheet2RowIndex, k] = tempSheet2Value[j, k]; } sheet2RowIndex++; } } //엑셀파일에 writting excelOpen = true; objWorkbook = objWorkbooks.Add(missing); objWorksheets = objWorkbook.Worksheets; // * sheet2 부분 적용 objWorksheet = (Excel.Worksheet)objWorksheets.get_Item(1); objWorksheet.Name = "Paragraphs"; range = objWorksheet.get_Range("A1", "M1"); range.HorizontalAlignment = HCENTER; range.Interior.Color = Color.FromArgb(142, 169, 219); range.Value2 = sheet2ColHeader; Marshal.ReleaseComObject(range); Excel.Range c1 = objWorksheet.Cells[2, 1]; Excel.Range c2 = objWorksheet.Cells[sheet2RowCount + 1, sheet2ColCount]; range = objWorksheet.get_Range(c1, c2); range.Value = sheet2ValueArray; Marshal.FinalReleaseComObject(c1); Marshal.FinalReleaseComObject(c2); Marshal.FinalReleaseComObject(range); Marshal.ReleaseComObject(objWorksheet); // * sheet1 부분 적용 objWorksheet = (Excel.Worksheet)objWorksheets.Add(missing, missing, missing, missing); objWorksheet.Name = "Qas"; range = objWorksheet.get_Range("A1", "AL1"); range.HorizontalAlignment = HCENTER; range.Interior.Color = Color.FromArgb(142, 169, 219); range.Value2 = sheet1ColHeader; Marshal.ReleaseComObject(range); c1 = objWorksheet.Cells[2, 1]; c2 = objWorksheet.Cells[sheet1RowCount + 1, sheet1ColCount]; range = objWorksheet.get_Range(c1, c2); range.Value = sheet1ValueArray; Marshal.FinalReleaseComObject(c1); Marshal.FinalReleaseComObject(c2); Marshal.FinalReleaseComObject(range); Marshal.FinalReleaseComObject(objWorksheet); Marshal.FinalReleaseComObject(objWorksheets); m_savePath = Path.ChangeExtension(m_path, "xlsx"); FileInfo fi = new FileInfo(m_savePath); if (fi.Exists) { fi.Delete(); } objWorkbook.SaveAs(m_savePath, Excel.XlFileFormat.xlOpenXMLWorkbook, missing, missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlUserResolution, true, missing, missing, missing); objWorkbook.Close(false, missing, missing); objWorkbooks.Close(); objApp.Quit(); Marshal.FinalReleaseComObject(objWorkbook); Marshal.FinalReleaseComObject(objWorkbooks); Marshal.FinalReleaseComObject(objApp); objApp = null; excelOpen = false; #endregion } else { #region Excel -> JSON 변환 // ** Excel 파일 불러와서 object 이중배열에 데이터 입력 excelOpen = true; objWorkbook = objWorkbooks.Open(m_path); objWorksheets = objWorkbook.Worksheets; objWorksheet = (Excel.Worksheet)objWorksheets[1]; range = objWorksheet.UsedRange; sheet1ValueArray = (object[, ])range.get_Value(missing); Marshal.ReleaseComObject(range); Marshal.ReleaseComObject(objWorksheet); objWorksheet = (Excel.Worksheet)objWorksheets[2]; range = objWorksheet.UsedRange; sheet2ValueArray = (object[, ])range.get_Value(missing); Marshal.FinalReleaseComObject(range); Marshal.FinalReleaseComObject(objWorksheet); Marshal.FinalReleaseComObject(objWorksheets); objWorkbook.Close(false, missing, missing); objWorkbooks.Close(); objApp.Quit(); Marshal.FinalReleaseComObject(objWorkbook); Marshal.FinalReleaseComObject(objWorkbooks); Marshal.FinalReleaseComObject(objApp); objApp = null; excelOpen = false; // ** sheet1, sheet2 object 이중배열의 데이터를 JSON 태그 클래스의 객체에 입력 // * topTag 객체 데이터 입력 EtopTag = new ETRI_TopTag(); EtopTag.version = sheet2ValueArray[2, 2] == null ? "" : sheet2ValueArray[2, 2].ToString(); EtopTag.creator = sheet2ValueArray[2, 3] == null ? "" : sheet2ValueArray[2, 3].ToString(); EtopTag.data = new List <object>(); // * topTag 객체 내의 Data 객체 리스트 입력 IList <object> titleList = new List <object>(); for (int r = 2; r <= sheet2ValueArray.GetLength(0); r++) { object tempTitle = sheet2ValueArray[r, 10]; if (!titleList.Any()) // 리스트에 아무것도 없을때 (=맨처음) { titleList.Add(tempTitle); } else if (tempTitle == null) // null 이거나 "" 일 때 tempTitle == "" { titleList.Add(tempTitle); } else if (titleList.Contains(tempTitle)) // 타이틀 이미 입력됨(통과) { continue; } if (!titleList.Contains(tempTitle)) { titleList.Clear(); titleList.Add(tempTitle); } ETRI_Data tempData = new ETRI_Data(); tempData.title = tempTitle == null ? "" : tempTitle.ToString(); tempData.paragraphs = new List <object>(); EtopTag.data.Add(tempData); } // * topTag->Data 객체 리스트 내의 Paragraphs 객체 리스트 입력 int dataCount = 0; object currentTitle = sheet2ValueArray[2, 10]; List <ETRI_Data> tempDataList = EtopTag.data.Cast <ETRI_Data>().ToList(); for (int r = 2; r <= sheet2ValueArray.GetLength(0); r++) { ETRI_Paragraphs tempParagraphs = new ETRI_Paragraphs(); tempParagraphs.context = sheet2ValueArray[r, 11] == null ? "" : sheet2ValueArray[r, 11].ToString(); tempParagraphs.context_en = sheet2ValueArray[r, 12] == null ? "" : sheet2ValueArray[r, 12].ToString(); tempParagraphs.context_tagged = sheet2ValueArray[r, 13] == null ? "" : sheet2ValueArray[r, 13].ToString(); tempParagraphs.qas = new List <object>(); if (sheet2ValueArray[r, 10] == null || sheet2ValueArray[r, 7].ToString() == "") { if (r != 2) { dataCount++; } tempDataList[dataCount].paragraphs.Add(tempParagraphs); currentTitle = sheet2ValueArray[r, 10] == null ? "" : sheet2ValueArray[r, 7].ToString(); } else if (sheet2ValueArray[r, 10].ToString().Equals(currentTitle)) { tempDataList[dataCount].paragraphs.Add(tempParagraphs); } else { dataCount++; exceptionDatacount++; tempDataList[dataCount].paragraphs.Add(tempParagraphs); currentTitle = sheet2ValueArray[r, 10].ToString(); } exceptionDow++; } EtopTag.data = tempDataList.Cast <object>().ToList(); // * topTag->Data->Paragraphs 객체 리스트 내의 Qas 객체 리스트 입력 dataCount = 0; int paragraphCount = 0; int currentParagraph = 1; tempDataList = EtopTag.data.Cast <ETRI_Data>().ToList(); List <ETRI_Qas> tempQasList = new List <ETRI_Qas>(); for (int r = 2; r <= sheet1ValueArray.GetLength(0); r++) { ETRI_Qas tempQas = new ETRI_Qas(); tempQas.id = sheet1ValueArray[r, 2] == null ? "" : sheet1ValueArray[r, 2].ToString(); tempQas.question = sheet1ValueArray[r, 7] == null ? "" : sheet1ValueArray[r, 7].ToString(); tempQas.question_en = sheet1ValueArray[r, 8] == null ? "" : sheet1ValueArray[r, 8].ToString(); tempQas.question_tagged = sheet1ValueArray[r, 27] == null ? "" : sheet1ValueArray[r, 27].ToString(); tempQas.questionType = sheet1ValueArray[r, 28] == null ? "" : sheet1ValueArray[r, 28].ToString(); tempQas.questionFocus = sheet1ValueArray[r, 29] == null ? "" : sheet1ValueArray[r, 29].ToString(); tempQas.questionSAT = sheet1ValueArray[r, 30] == null ? "" : sheet1ValueArray[r, 30].ToString(); tempQas.questionLAT = sheet1ValueArray[r, 31] == null ? "" : sheet1ValueArray[r, 31].ToString(); int ansStartColNum = 32; ETRI_Answers tempAnswers = new ETRI_Answers(); tempAnswers.text = sheet1ValueArray[r, ansStartColNum] == null ? "" : sheet1ValueArray[r, ansStartColNum].ToString(); tempAnswers.text_en = sheet1ValueArray[r, ansStartColNum + 1] == null ? "" : sheet1ValueArray[r, ansStartColNum + 1].ToString(); tempAnswers.text_tagged = sheet1ValueArray[r, ansStartColNum + 2] == null ? "" : sheet1ValueArray[r, ansStartColNum + 2].ToString(); tempAnswers.text_syn = sheet1ValueArray[r, ansStartColNum + 3] == null ? "" : sheet1ValueArray[r, ansStartColNum + 3].ToString(); tempAnswers.answer_start = Convert.ToInt32(sheet1ValueArray[r, ansStartColNum + 4]); tempAnswers.answer_end = Convert.ToInt32(sheet1ValueArray[r, ansStartColNum + 5]); List <ETRI_Answers> tempAnswersList = new List <ETRI_Answers>(); tempAnswersList.Add(tempAnswers); tempQas.answers = tempAnswersList.Cast <object>().ToList(); tempQasList.Add(tempQas); currentParagraph = Convert.ToInt32(sheet1ValueArray[r, 38]); //36 if (r + 1 <= sheet1ValueArray.GetLength(0)) // 다음 목표 row가 sheet1ValueArray의 1차 배열 길이를 넘지 않을때 { if (currentParagraph != Convert.ToInt32(sheet1ValueArray[r + 1, 38])) // 현재 row의 소속 paragraph 값과 다음 row의 소속 paragraph값을 비교하여 같지 않다면 { EtopTag.data.Cast <ETRI_Data>().ToList()[dataCount].paragraphs.Cast <ETRI_Paragraphs>().ToList()[paragraphCount].qas = tempQasList.Cast <object>().ToList(); // Qas 리스트 삽입 tempQasList = new List <ETRI_Qas>(); if (paragraphCount < EtopTag.data.Cast <ETRI_Data>().ToList()[dataCount].paragraphs.Count - 1) // paragraphCount 값이 현재 Data에서의 끝에 도달하기 전에는 이렇게 처리 { paragraphCount++; } else // 도달하고 난 후에는 이렇게 처리 { dataCount++; paragraphCount = 0; } } } if (r == sheet1ValueArray.GetLength(0)) // 현재 row가 마지막일때 { EtopTag.data.Cast <ETRI_Data>().ToList()[dataCount].paragraphs.Cast <ETRI_Paragraphs>().ToList()[paragraphCount].qas = tempQasList.Cast <object>().ToList(); } } // ** JSON 파일로 저장 m_savePath = Path.ChangeExtension(m_path, "json"); FileInfo fi = new FileInfo(m_savePath); if (fi.Exists) // 파일이 이미 존재하면 삭제 { fi.Delete(); } string saveJSONText; bool m_EtoJNullRemoveCheck = false; if (m_EtoJNullRemoveCheck) { saveJSONText = JsonConvert.SerializeObject(EtopTag, Formatting.Indented, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore // Null값 객체 제거 } ); } else { saveJSONText = JsonConvert.SerializeObject(EtopTag, Formatting.Indented, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Include // Null값 객체 포함 } ); } using (StreamWriter sw = new StreamWriter(m_savePath)) { sw.Write(saveJSONText); } #endregion } } catch (Exception e) { if (excelOpen) { Marshal.FinalReleaseComObject(range); Marshal.FinalReleaseComObject(objWorksheet); Marshal.FinalReleaseComObject(objWorksheets); objWorkbook.Close(false, missing, missing); objWorkbooks.Close(); objApp.Quit(); Marshal.FinalReleaseComObject(objWorkbook); Marshal.FinalReleaseComObject(objWorkbooks); Marshal.FinalReleaseComObject(objApp); objApp = null; } return("-------------------------------------------------------\n예외처리 된 오류 발생.\r\n파일: " + m_path + "-------------------------------------------------------\n데이터카운트: " + exceptionDatacount + "\n열 번호: " + exceptionDow + "-------------------------------------------------------\n오류 이유:" + e.ToString()); } } return("모든 파일 변환 성공"); }
public void CreateReport(DataTable data, string saveToPath) { if (data == null) { messager.ErrorMessage("Ошибка получения отчетной таблицы! Повторите попытку или обратитесь к разработчику."); return; } if (string.IsNullOrEmpty(saveToPath) || string.IsNullOrWhiteSpace(saveToPath)) { messager.ErrorMessage(string.Format("Ошибка в указанном пути файла:{1}\"{0}\",{1}укажите правильный путь и имя файла!", saveToPath, Environment.NewLine )); return; } if (!Directory.Exists(Path.GetDirectoryName(saveToPath))) { Directory.CreateDirectory(Path.GetDirectoryName(saveToPath)); } try { Excel.Application eApp = new Excel.Application(); Excel.Workbook wBook = eApp.Workbooks.Add(); Excel.Worksheet wSheet = wBook.Worksheets.Add(); for (int i = 0; i < data.Columns.Count; i++) { wSheet.Cells[1, i + 1] = data.Columns[i].ColumnName; } for (int i = 0; i < data.Rows.Count; i++) { for (int j = 0; j < data.Columns.Count; j++) { wSheet.Cells[i + 2, j + 1] = data.Rows[i][j]; } } //finding a last cell Excel.Range r1 = wSheet.Cells.get_End(Excel.XlDirection.xlToRight); Excel.Range r2 = wSheet.Cells.get_End(Excel.XlDirection.xlDown); string rightEnd = r1.get_Address().Split('$')[1]; //letter string bottomEnd = r2.get_Address().Split('$')[2]; //number //last cell address string lastCell = rightEnd + bottomEnd; //painting grid List <Excel.XlBordersIndex> borders = new List <Excel.XlBordersIndex>() { Excel.XlBordersIndex.xlEdgeRight, Excel.XlBordersIndex.xlEdgeLeft, Excel.XlBordersIndex.xlEdgeBottom, Excel.XlBordersIndex.xlEdgeTop, Excel.XlBordersIndex.xlInsideHorizontal, Excel.XlBordersIndex.xlInsideVertical }; foreach (Excel.XlBordersIndex brd in borders) { Excel.Range r = wSheet.get_Range("A1", lastCell); r.Borders[brd].Weight = Excel.XlBorderWeight.xlThin; r.Borders[brd].LineStyle = Excel.XlLineStyle.xlContinuous; r.Borders[brd].ColorIndex = 0; } wSheet.get_Range("A1", lastCell).Font.Size = 14; wSheet.Columns.AutoFit(); wBook.SaveAs(saveToPath); eApp.Visible = true; } catch { messager.ErrorMessage(string.Format("Ошибка создания документа Microsoft Office Excel - \"{0}\"{1}{1}Обратитесь к разработчику!", Path.GetFileName(saveToPath), Environment.NewLine )); } }
// Вставка формул #region Формулы private static void sumTableExcel(Excel.Worksheet ws, int numberFile, int firstRow, int lastRow) { switch (numberFile) { case 1: // Диапазоны для вставки формул Excel.Range rngCol1Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 8], ws.Cells[lastRow, 8]]; // Формула для подсчета суммы столбца "Покупки включая НДС" Excel.Range rngCol2Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 9], ws.Cells[lastRow, 9]]; // Формула для подсчета суммы стобца "Покупки без НДС" Excel.Range rngCol3Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 10], ws.Cells[lastRow, 10]]; // Формула для подсчета суммы столбца "Сумма НДС" // Указываем диапазон для формул rngCol1Sum.Formula = $"=SUM(r{firstRow}c{8}:r{lastRow - 1}c{8})"; rngCol2Sum.Formula = $"=SUM(r{firstRow}c{9}:r{lastRow - 1}c{9})"; rngCol3Sum.Formula = $"=SUM(r{firstRow}c{10}:r{lastRow - 1}c{10})"; break; case 2: // Диапазоны для вставки формул Excel.Range rngCol4Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 7], ws.Cells[lastRow, 7]]; // Формула для подсчета суммы столбца "Покупки включая НДС" Excel.Range rngCol5Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 8], ws.Cells[lastRow, 8]]; // Формула для подсчета суммы стобца "Покупки без НДС" Excel.Range rngCol6Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 9], ws.Cells[lastRow, 9]]; // Формула для подсчета суммы столбца "Сумма НДС" // Указываем диапазон для формул rngCol4Sum.Formula = $"=SUM(r{firstRow}c{7}:r{lastRow - 1}c{7})"; rngCol5Sum.Formula = $"=SUM(r{firstRow}c{8}:r{lastRow - 1}c{8})"; rngCol6Sum.Formula = $"=SUM(r{firstRow}c{9}:r{lastRow - 1}c{9})"; break; case 3: // Диапазоны для вставки формул Excel.Range rngCol7Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 3], ws.Cells[lastRow, 3]]; // Формула для подсчета суммы столбца "Выручка, руб." Excel.Range rngCol8Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 4], ws.Cells[lastRow, 4]]; // Формула для подсчета суммы стобца "Валовая прибыль, руб." // Указываем диапазон для формул rngCol7Sum.Formula = $"=SUM(r{firstRow}c{3}:r{lastRow - 1}c{3})"; rngCol8Sum.Formula = $"=SUM(r{firstRow}c{4}:r{lastRow - 1}c{4})"; break; case 4: // Диапазоны для вставки формул Excel.Range rngCol9Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 3], ws.Cells[lastRow, 3]]; // Формула для подсчета суммы столбца "Выручка, руб." Excel.Range rngCol10Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 4], ws.Cells[lastRow, 4]]; // Формула для подсчета суммы стобца "Премиальная часть, руб." // Указываем диапазон для формул rngCol9Sum.Formula = $"=SUM(r{firstRow}c{4}:r{lastRow - 1}c{4})"; rngCol10Sum.Formula = $"=SUM(r{firstRow}c{5}:r{lastRow - 1}c{5})"; break; case 5: // Диапазоны для вставки формул Excel.Range rngCol11Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 6], ws.Cells[lastRow, 6]]; // Формула для подсчета суммы столбца "Сумма без НДС, руб." Excel.Range rngCol12Sum = (Excel.Range)ws.Range[ws.Cells[lastRow + 1, 6], ws.Cells[lastRow + 1, 6]]; // Формула для подсчета произведения стобца "Сумма без НДС, руб." строки "НДС 18%" Excel.Range rngCol13Sum = (Excel.Range)ws.Range[ws.Cells[lastRow + 2, 6], ws.Cells[lastRow + 2, 6]]; // Формула для подсчета суммы столца "Сумма без НДС, руб." строки "НДС 18%" + "ИТОГО" // Указываем диапазон для формул rngCol11Sum.Formula = $"=SUM(r{firstRow}c{6}:r{lastRow - 1}c{6})"; rngCol12Sum.Formula = $"=r{lastRow}c{6}*0.20"; rngCol13Sum.Formula = $"=r{lastRow + 1}c{6}+r{lastRow}c{6}"; break; case 6: // Диапазоны для вставки формул Excel.Range rngCol14Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 6], ws.Cells[lastRow, 6]]; // Формула для подсчета суммы столбца "Сумма, руб." // Указываем диапазон для формул rngCol14Sum.Formula = $"=SUM(r{firstRow}c{6}:r{lastRow - 1}c{6})"; break; case 7: // Диапазоны для вставки формул Excel.Range rngCol15Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 33], ws.Cells[lastRow, 33]]; // Формула для подсчета суммы столбца "Мест, штук" Excel.Range rngCol16Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 38], ws.Cells[lastRow, 38]]; // Формула для подсчета суммы стобца "Количество" Excel.Range rngCol17Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 44], ws.Cells[lastRow, 44]]; // Формула для подсчета суммы столца "Сумма без учета НДС, руб. коп." Excel.Range rngCol18Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 50], ws.Cells[lastRow, 50]]; // Формула для подсчета суммы столбца "НДС - сумма, руб. коп." Excel.Range rngCol19Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 54], ws.Cells[lastRow, 54]]; // Формула для подсчета суммы столбца "Сумма с учетом НДС, руб. коп." // Указываем диапазон для формул rngCol15Sum.Formula = $"=SUM(r{firstRow}c{33}:r{lastRow - 1}c{33})"; rngCol16Sum.Formula = $"=SUM(r{firstRow}c{38}:r{lastRow - 1}c{38})"; rngCol17Sum.Formula = $"=SUM(r{firstRow}c{44}:r{lastRow - 1}c{44})"; rngCol18Sum.Formula = $"=SUM(r{firstRow}c{50}:r{lastRow - 1}c{50})"; rngCol19Sum.Formula = $"=SUM(r{firstRow}c{54}:r{lastRow - 1}c{54})"; break; case 8: // Диапазоны для вставки формул Excel.Range rngCol20Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 6], ws.Cells[lastRow, 6]]; // Формула для подсчета суммы столбца "Сумма, руб. коп." Excel.Range rngCol20_1Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 5], ws.Cells[lastRow, 5]]; // Формула для подсчета суммы столбца "Штук" Excel.Range rngCol20_2Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 4], ws.Cells[lastRow, 4]]; // Формула для подсчета суммы столбца "Цена" // Указываем диапазон для формул rngCol20Sum.Formula = $"=SUM(r{firstRow}c{6}:r{lastRow - 2}c{6})"; rngCol20_1Sum.Formula = $"=SUM(r{firstRow}c{5}:r{lastRow - 2}c{5})"; rngCol20_2Sum.Formula = $"=SUM(r{firstRow}c{4}:r{lastRow - 1}c{4})"; break; case 9: // Диапазоны для вставки формул Excel.Range rngCol21Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 7], ws.Cells[lastRow, 7]]; // Формула для подсчета суммы столбца "Мест, штук" Excel.Range rngCol22Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 8], ws.Cells[lastRow, 8]]; // Формула для подсчета суммы стобца "Количество" // Указываем диапазон для формул rngCol21Sum.Formula = $"=SUM(r{firstRow}c{7}:r{lastRow - 1}c{7})"; rngCol22Sum.Formula = $"=SUM(r{firstRow}c{8}:r{lastRow - 1}c{8})"; break; } }
// Печатает таблицу без вставки текста public static void ExportToExcelNew(Form form, DataGridView dgv, int fRow, int fColumn, string nameTable) { string baseDirectory = System.AppDomain.CurrentDomain.BaseDirectory; string path = baseDirectory + "doc//Прайс-лист.xlsx"; int firstColumn = fColumn; // Первый столбец int firstRow = fRow; // Первая строка int lastRow = firstRow + dgv.RowCount; // Последняя строка int lastColumn = firstColumn + dgv.ColumnCount; // Последний столбец Excel.Application xl = new Excel.Application(); // Создаем экземпляр Excel'а try { Excel.Workbook wb = (Excel.Workbook)xl.Workbooks.Open(path, false, false); // Открываем файл Excel Excel.Worksheet ws = (Excel.Worksheet)wb.ActiveSheet; // Указываем ссылку на активный лист файла Excel form.Cursor = Cursors.WaitCursor; // Очистка старого форматирования Excel.Range rngDEL = (Excel.Range)ws.Range[ws.Cells[1, 1], ws.Cells[7, dgv.ColumnCount + 4]]; rngDEL.Clear(); // Заполняем массив заголовками object[] headers = new object[dgv.ColumnCount]; for (int i = 0; i < dgv.ColumnCount; i++) { headers[i] = dgv.Columns[i].HeaderText; } // Заполняем массив данными object[,] rc = new object[dgv.RowCount, dgv.ColumnCount]; for (int i = 0; i < dgv.RowCount; i++) { for (int j = 0; j < dgv.ColumnCount; j++) { rc[i, j] = dgv.Rows[i].Cells[j].Value; } } // Вставка текста в ячейки шаблона ws.Cells[1, 1] = DateTime.Now.ToShortDateString(); ws.Cells[3, 1] = nameTable; // Форматирование Excel.Range rngName = ws.Range[ws.Cells[3, 1], ws.Cells[3, 1]]; rngName.Font.Size = 16; rngName.Font.Bold = true; // Добавляем пустые строки со сдвигом вниз Excel.Range insertColumn = ws.Range[ws.Cells[firstRow, firstColumn], ws.Cells[lastRow - 2, lastColumn - 1]]; insertColumn.Insert(Excel.XlDirection.xlDown); // Вставка рамок Excel.Range pasteBorders = ws.Range[ws.Cells[firstRow - 1, firstColumn], ws.Cells[lastRow - 1, lastColumn]]; pasteBorders.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous; pasteBorders.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous; pasteBorders.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlContinuous; pasteBorders.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlContinuous; pasteBorders.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous; // Вставляем заголовки из массива в Excel Excel.Range pasteCaptions = ws.Range[ws.Cells[firstRow - 1, firstColumn], ws.Cells[firstRow - 1, lastColumn]]; pasteCaptions.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, headers); pasteCaptions.Font.Bold = true; // Вставляем текст из массива в Excel Excel.Range pasteText = ws.Range[ws.Cells[firstRow, firstColumn], ws.Cells[lastRow - 1, lastColumn - 1]]; pasteText.Font.Bold = false; pasteText.Font.Name = "Arial"; pasteText.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, rc); // Удаляем последнюю пустую строку со сдвигом вверх Excel.Range deleteRow = ws.Range[ws.Cells[lastRow - 1, firstColumn], ws.Cells[lastRow - 1, lastColumn]]; deleteRow.Delete(Excel.XlDirection.xlUp); // Удаляем последний столбец Excel.Range deleteColumn = ws.Range[ws.Cells[firstRow - 1, lastColumn], ws.Cells[lastRow, lastColumn]]; deleteColumn.Clear(); // Автоподбор ширины столбцов Excel.Range rngAF = ws.Range[ws.Cells[firstRow - 1, 1], ws.Cells[lastRow, lastColumn]]; rngAF.Columns.AutoFit(); rngAF.Rows.AutoFit(); form.Cursor = Cursors.Default; xl.Visible = true; // Делаем книгу Excel видимой xl.UserControl = true; // Делаем книгу Excel активной } catch (Exception ex) { MessageBox.Show(ex.Message + "\n" + ex, "Ошибка"); } }
/// <summary> /// 有条件导出 /// </summary> /// <param name="details"></param> /// <param name="type"></param> private void ExportExcel(IList <Model.ProduceInDepotDetail> details, string type) { try { Type objClassType = null; objClassType = Type.GetTypeFromProgID("Excel.Application"); if (objClassType == null) { MessageBox.Show("本機沒有安裝Excel", "提示!", MessageBoxButtons.OK); return; } Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Application.Workbooks.Add(true); Microsoft.Office.Interop.Excel.Range r = excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 17]); r.MergeCells = true;//合并单元格 //Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter = -4108; //Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium= -4138; //Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic= -4105; switch (type) { case "防霧": excel.Cells[1, 1] = "強化/防霧 工作日報表"; excel.Cells[3 + details.Count, 17] = "QR8-06-05-1"; break; case "品檢": excel.Cells[1, 1] = "品檢日報表"; excel.Cells[3 + details.Count, 17] = "QR8-03-01-1"; break; case "組A(半)": excel.Cells[1, 1] = "組裝半成品日報表"; excel.Cells[3 + details.Count, 17] = "QR8-03-02-1"; break; case "組A": excel.Cells[1, 1] = "組裝成品日報表"; break; } #region Set Header excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).RowHeight = 25; excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Font.Size = 20; excel.get_Range(excel.Cells[1, 1], excel.Cells[2, 17]).HorizontalAlignment = -4108; excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 17]).ColumnWidth = 12; excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 2]).ColumnWidth = 20; excel.get_Range(excel.Cells[2, 3], excel.Cells[2, 3]).ColumnWidth = 40; excel.get_Range(excel.Cells[2, 11], excel.Cells[2, 12]).ColumnWidth = 20; excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 17]).Interior.Color = 12566463; excel.get_Range(excel.Cells[2, 1], excel.Cells[details.Count + 2, 17]).RowHeight = 20; excel.get_Range(excel.Cells[2, 1], excel.Cells[details.Count + 2, 17]).Font.Size = 13; excel.get_Range(excel.Cells[3, 1], excel.Cells[details.Count + 2, 17]).WrapText = true; excel.get_Range(excel.Cells[3, 1], excel.Cells[details.Count + 2, 17]).EntireRow.AutoFit(); excel.Cells[2, 1] = "入庫日期"; excel.Cells[2, 2] = "入庫單號"; excel.Cells[2, 3] = "產品名稱"; excel.Cells[2, 4] = "型號"; excel.Cells[2, 5] = "公司部門"; excel.Cells[2, 6] = "單位"; excel.Cells[2, 7] = "生產數量"; excel.Cells[2, 8] = "合計生產"; excel.Cells[2, 9] = "合計合格"; excel.Cells[2, 10] = "合計入庫"; excel.Cells[2, 11] = "合計轉生產"; excel.Cells[2, 12] = "加工單"; excel.Cells[2, 13] = "客戶訂單號"; excel.Cells[2, 14] = "生產數量"; excel.Cells[2, 15] = "合格數量"; excel.Cells[2, 16] = "轉生產數量"; excel.Cells[2, 17] = "入庫數量"; #endregion #region 取商品對應的母件型號 foreach (var item in details) { if (string.IsNullOrEmpty(item.CustomerProductName)) { //item.CustomerProductName = (this.manager as BL.ProduceInDepotDetailManager).SelectCustomerProductNameByPronoteHeaderId(item.PronoteHeaderId); 如果一个订单里面多个商品同时用到某个子件,在物料需求里面该子件会合并计算为一笔,其实它对应有多个主件 item.CustomerProductName = new Help().GetCustomerProductNameByPronoteHeaderId(item.PronoteHeaderId, item.ProductId); } } #endregion for (int i = 0; i < details.Count; i++) { excel.Cells[i + 3, 1] = details[i].mProduceInDepotDate.HasValue ? details[i].mProduceInDepotDate.Value.ToString("yyyy-MM-dd") : ""; excel.Cells[i + 3, 2] = details[i].ProduceInDepotId; excel.Cells[i + 3, 3] = details[i].ProductName; excel.Cells[i + 3, 4] = details[i].CustomerProductName; excel.Cells[i + 3, 5] = details[i].WorkHousename; excel.Cells[i + 3, 6] = details[i].ProductUnit; excel.Cells[i + 3, 7] = details[i].PronoteHeaderSum; excel.Cells[i + 3, 8] = details[i].HeJiProceduresSum; excel.Cells[i + 3, 9] = details[i].HeJiCheckOutSum; excel.Cells[i + 3, 10] = details[i].HeJiProduceQuantity; excel.Cells[i + 3, 11] = details[i].HeJiProduceTransferQuantity; excel.Cells[i + 3, 12] = details[i].PronoteHeaderId; excel.Cells[i + 3, 13] = details[i].CusXOId; excel.Cells[i + 3, 14] = details[i].ProceduresSum; excel.Cells[i + 3, 15] = details[i].CheckOutSum; excel.Cells[i + 3, 16] = details[i].ProduceTransferQuantity; excel.Cells[i + 3, 17] = details[i].ProduceQuantity; } excel.Visible = true;//是否打开该Excel文件 excel.WindowState = XlWindowState.xlMaximized; } catch { MessageBox.Show("Excel未生成完畢,請勿操作,并重新點擊按鈕生成數據!", "提示!", MessageBoxButtons.OK); return; } }
public void OpenExcel(string strFileName) { object missing = System.Reflection.Missing.Value; Application excel = new Application();//lauch excel application if (excel == null) { Console.Out.Write("Can't access excel"); } else { excel.Visible = false; excel.UserControl = true; // 以只讀的形式打開EXCEL文檔 Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing); //取得第一個工作薄 Worksheet ws = (Worksheet)wb.Worksheets.get_Item(1); Excel.Range xlRange = ws.UsedRange; //Console.Out.WriteLine("Row = " + ws.UsedRange.Rows.Count); //Console.Out.WriteLine("Col = " + ws.UsedRange.Columns.Count); teachernum = Convert.ToInt16(xlRange.Cells[1, 1].Value2); data = new string[teachernum][]; errordata = new string[teachernum][]; subclass = new string[teachernum][]; checkdata = new string[teachernum][]; teachername = new string[teachernum]; for (int i = 0; i < teachernum; i++) { data[i] = new string[classnum]; errordata[i] = new string[classnum]; subclass[i] = new string[classnum]; checkdata[i] = new string[classnum]; } string[][] sa = new string[ws.UsedRange.Rows.Count][]; for (int i = 0; i < ws.UsedRange.Rows.Count; i++) { sa[i] = new string[ws.UsedRange.Columns.Count]; } for (int i = 1; i <= ws.UsedRange.Rows.Count; i++) { for (int j = 1; j <= ws.UsedRange.Columns.Count; j++) { //new line //if (j == 1) // Console.Write("\r\n"); //write the value to the console // if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null) // Console.Write(xlRange.Cells[i, j].Value2.ToString() + "\t"); //add useful things here! if (i > 1) { if (xlRange.Cells[i, j].Value2 == null) { sa[i - 2][j - 1] = ""; } else { sa[i - 2][j - 1] = xlRange.Cells[i, j].Value2.ToString(); } } } } for (int i = 0; i < teachernum; i++) { teachername[i] = sa[i][0]; for (int j = 1; j <= classnum; j++) { string class_string = sa[i][j]; data[i][j - 1] = class_string; } } for (int i = 0; i < 3; i++) { for (int j = 1; j <= classnum; j++) { subclass[i][j - 1] = sa[teachernum + i][j]; } } } excel.Quit(); excel = null; GC.Collect(); }
private void LoadExcel(string path) { Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(path); Excel.Worksheet xlWorkSheet = xlWorkBook.Worksheets[1]; Excel.Range xlRange = xlWorkSheet.UsedRange; int n = 1; for (int i = 2; i <= xlRange.Rows.Count; i++) { if (xlRange.Cells[i, 1].Value2 != null) { string ChucNangKhac = ""; string NVBang = "5NV"; string Moc = "Moc0"; int server = 0; int qq = 4; var username = xlRange.Cells[i, 1].Value2.ToString(); var pwd = xlRange.Cells[i, 2].Value2.ToString(); if (xlRange.Cells[i, 3].Value2 != null) { server = int.Parse(xlRange.Cells[i, 3].Value2.ToString()); } if (xlRange.Cells[i, 4].Value2 != null) { NVBang = xlRange.Cells[i, 4].Value2.ToString(); } if (xlRange.Cells[i, 5].Value2 != null) { Moc = xlRange.Cells[i, 5].Value2.ToString(); } if (xlRange.Cells[i, 6].Value2 != null) { ChucNangKhac = xlRange.Cells[i, 6].Value2.ToString(); } if (xlRange.Cells[i, 7].Value2 != null) { qq = int.Parse(xlRange.Cells[i, 7].Value2.ToString()); } listAccounts.Add(new User() { Index = n, Username = username, Password = pwd, Server = server, NVBang = NVBang, Moc = Moc, ChucNangKhac = ChucNangKhac, QQ = qq, }); n++; } } DataGridUsers.ItemsSource = listAccounts; ketThuc = listAccounts.Count - 1; GC.Collect(); GC.WaitForPendingFinalizers(); Marshal.ReleaseComObject(xlRange); Marshal.ReleaseComObject(xlWorkSheet); xlWorkBook.Close(); Marshal.ReleaseComObject(xlWorkBook); xlApp.Quit(); Marshal.ReleaseComObject(xlApp); currentUser = 0; }
private static void ToExcel(DataGridView ExportGrid, string p_ReportName) { //如果网格尚未数据绑定 if (ExportGrid == null) { return; } // 列索引,行索引 int colIndex = 0; int rowIndex = 0; int j; //总可见列数,总可见行数 //int colCount = ExportGrid.Columns.GetColumnCount(DataGridViewElementStates.Visible); //int rowCount = ExportGrid.Rows.GetRowCount(DataGridViewElementStates.Visible); int rowCount = 0; int colCount = SelectedColumns.Count; if (PrintAllRows) //打印全部 { rowCount = ExportGrid.Rows.GetRowCount(DataGridViewElementStates.Visible); } else //打印选择 { rowCount = ExportGrid.Rows.GetRowCount(DataGridViewElementStates.Selected); } //如果DataGridView中没有行,返回 if (rowCount == 0) { return; } //保存对话框 if (!SaveFileDialog()) { return; } // 创建Excel对象 Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); if (xlApp == null) { MessageBox.Show("Excel无法启动", "系统信息"); return; } // 创建Excel工作薄 Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true); Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1]; // 设置标题,实测中发现执行设置字体大小和将字体设置为粗体的语句耗时较长,故注释掉了 string[] sTitle = p_ReportName.Split(new char[1] { ';' }); Microsoft.Office.Interop.Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]); range.MergeCells = true; xlApp.ActiveCell.FormulaR1C1 = sTitle[0]; //xlApp.ActiveCell.Font.Size = 20; //xlApp.ActiveCell.Font.Bold = true; xlApp.ActiveCell.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; // 单据汇总 // 创建缓存数据 object[,] objData = new object[rowCount + 2 + sTitle.Length, colCount]; for (j = 1; j < sTitle.Length; j++) { //Microsoft.Office.Interop.Excel.Range range1 = xlSheet.get_Range(xlApp.Cells[rowIndex, 1], xlApp.Cells[rowIndex, colCount]); //range1.MergeCells = true; objData[rowIndex, 0] = sTitle[j]; rowIndex++; } objData[rowIndex, 0] = ""; rowIndex++; // 获取列标题,隐藏的列不处理 for (int i = 0; i < ExportGrid.ColumnCount; i++) { if (!PrintDGV.SelectedColumns.Contains(ExportGrid.Columns[i].HeaderText)) { continue; } if (ExportGrid.Columns[i].Visible) { objData[rowIndex, colIndex++] = ExportGrid.Columns[i].HeaderText; } } // 获取数据,隐藏的列的数据忽略 for (int i = 1; i <= ExportGrid.Rows.GetRowCount(DataGridViewElementStates.Visible); i++) { if (!PrintAllRows && !ExportGrid.Rows[i - 1].Selected) { continue; } rowIndex++; colIndex = 0; for (j = 0; j < ExportGrid.ColumnCount; j++) { if (!PrintDGV.SelectedColumns.Contains(ExportGrid.Columns[j].HeaderText)) { continue; } if (ExportGrid.Columns[j].Visible) { if (ExportGrid[j, i - 1].Value != null) { objData[rowIndex, colIndex++] = ExportGrid[j, i - 1].Value.ToString(); } else { objData[rowIndex, colIndex++] = ""; } } } System.Windows.Forms.Application.DoEvents(); } // 写入Excel //xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colIndex]).Font.Bold = true; range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[rowCount + +2 + sTitle.Length, colCount]); range.Value2 = objData; // 保存 try { xlApp.Cells.EntireColumn.AutoFit(); xlApp.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; xlApp.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; //xlApp.Visible = true; xlBook.Saved = true; xlBook.SaveCopyAs(FullFileName); MessageBox.Show("导出成功!", "系统信息"); } catch { MessageBox.Show("保存出错,请检查文件是否被正使用!", "系统信息"); //return false; } finally { xlApp.Quit(); GC.Collect(); //KillProcess("excel"); } //return true; }
/// <summary> /// Method to make columns auto fit according to data /// </summary> /// <param name="startRange"></param> /// <param name="rowCount"></param> /// <param name="colCount"></param> private void AutoFitColumns(string startRange, int rowCount, int colCount) { _range = _sheet.get_Range(startRange, _optionalValue); _range = _range.get_Resize(rowCount, colCount); _range.Columns.AutoFit(); }
private void WorksheetProjects_Change(Excel.Range Target) { Debug.WriteLine("Projects Worksheet changed"); }
/// <summary> /// Given an IP address and a network specification, returns a boolean indicating whether the single /// IP address exists within the network. /// </summary> /// <param name="Address1">First IP address to test</param> /// <param name="Address2">Second IP address to test (can be a network block in VLSM slash-style notation)</param> /// <param name="Netmask">Netmask (required if the network block is not in VLSM format)</param> /// <returns></returns> public object IsInSameNetwork4(string Address1, string Address2, [Optional] object Netmask) { IPAddress _address1 = null; if (!IPAddress.TryParse(Address1, out _address1)) { return(new ErrorWrapper((int)CVErrNum.ErrValue)); } IPAddress _address2 = null; IPAddress _netmask = null; if (Address2.IndexOf('/') > 0) { string[] _networkArg = Address2.Split('/'); if (!IPAddress.TryParse(_networkArg[0], out _address2)) { return(new ErrorWrapper((int)CVErrNum.ErrValue)); } try { _netmask = _ConvertBitlenToMask4(Convert.ToInt32(_networkArg[1])); } catch (ArgumentException) { return(new ErrorWrapper((int)CVErrNum.ErrValue)); } } else { if (!IPAddress.TryParse(Address2, out _address2)) { return(new ErrorWrapper((int)CVErrNum.ErrValue)); } } if (!(Netmask is System.Reflection.Missing)) { string _netmaskStr = ""; if (Netmask is string) { _netmaskStr = (string)Netmask; } if (Netmask is Excel.Range) { Excel.Range _netmaskRng = (Excel.Range)Netmask; _netmaskStr = (string)_netmaskRng.Value; } if (!IPAddress.TryParse((string)_netmaskStr, out _netmask)) { return(new ErrorWrapper((int)CVErrNum.ErrValue)); } } if (_netmask == null) { return(new ErrorWrapper((int)CVErrNum.ErrNull)); } IPAddress _addr1Masked = _PerformBitmask(_address1, _netmask); IPAddress _addr2Masked = _PerformBitmask(_address2, _netmask); if (_addr1Masked.Equals(_addr2Masked)) { return(true); } return(false); }
private void button1_Click(object sender, EventArgs e) { button1.Enabled = false; numericUpDown1.Enabled = false; numericUpDown2.Enabled = false; checkBox2.Enabled = false; checkBox1.Enabled = false; if (openFileDialog1.ShowDialog() == DialogResult.OK) { /// /// Копирую таблицы со структурой /// Правила - двумерная структура /// Сетевые объекты - трёхмерная (двумерная таблица с объектом-списком) /// Сервисы - двумерная таблица /// Rulesd_Delegate d = null; d = new Rulesd_Delegate(Rulesd); IAsyncResult R = null; R = d.BeginInvoke(openFileDialog1.FileName, null, null); Rules = d.EndInvoke(R); /* * Это N правил + 1 отрезаем кончающие правила * Далее начальные */ if (checkBox2.Checked && numericUpDown1.Value < numericUpDown2.Value && numericUpDown2.Value < Rules.Count) { Rules.RemoveRange(Convert.ToInt32(numericUpDown2.Value) + 1, Rules.Count - Convert.ToInt32(numericUpDown2.Value) - 1); Rules.RemoveRange(0, Convert.ToInt32(numericUpDown1.Value)); } else if (checkBox2.Checked) { MessageBox.Show("Проверьте правильно ли Вы выставили значения диапазона интересующих правил"); return; } AddTextToStatus("Скопировал Таблицу правил"); NetworkObjd_Delegate ND = null; ND = new NetworkObjd_Delegate(NetworkObjd); IAsyncResult NR = null; NR = ND.BeginInvoke(openFileDialog1.FileName, null, null); NetworkObj = ND.EndInvoke(NR); AddTextToStatus("Скопировал Таблицу Сетевых имён"); for (int k = 0; k < NetworkObj.Count; k++) { for (int l = 0; l < NetworkObj[l].Count; l++) { NetworkObj[k][l] = NetworkObj[k][l].Distinct(); } } Servicesd_Delegate SD = null; SD = new Servicesd_Delegate(Servicesd); IAsyncResult SR = null; SR = SD.BeginInvoke(openFileDialog1.FileName, null, null); Services = SD.EndInvoke(SR); AddTextToStatus("Скопировал Таблицу сервисов"); /// /// Открываем книгу Excel. /// Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application(); Workbook WB; Worksheet WS; WB = ObjExcel.Workbooks.Add(System.Reflection.Missing.Value); WS = (Worksheet)WB.Sheets[1]; int i = 1; AddTextToStatus("Открыл ExCel"); progressBar1.Maximum = Rules.Count; progressBar1.Value = 0; try { foreach (List <string> row in Rules) { if (i == 1) { WS.Cells[i, 1].Value = "Сервис"; WS.Cells[i, 2].Value = "Сегмент источника"; WS.Cells[i, 3].Value = "IP Источника"; WS.Cells[i, 4].Value = "Сегмент назначения"; WS.Cells[i, 5].Value = "IP назначения"; WS.Cells[i, 6].Value = "Порты"; WS.Cells[i, 7].Value = "Решение"; WS.Cells[i, 8].Value = "Установлено"; WS.Cells[i, 9].Value = "Время"; WS.Cells[i, 10].Value = "Комментарий"; i++; continue; } string[] split = { "\r\n", "\r", "\n" }; /// /// Это для Source /// Есть структура данных ResolvedNetObj, которая содержит /// отрезолвленные члены группового объекта /// Создадим список из структуры ResolvedNetObj который будет содержать /// все объекты ячейки источник из политики /// List <string> CurrentSourceNetObj = row[2].Split(split, StringSplitOptions.RemoveEmptyEntries).ToList(); List <ResolvedNetObj> CurrentSrc = GetResolvedNetObj(CurrentSourceNetObj); AddTextToStatus("Отрезолвил Объекты Источники для " + i.ToString() + " правила"); foreach (ResolvedNetObj oneNetObj in CurrentSrc) { if (oneNetObj.ResolvedObj.Count > 1) { foreach (List <string> OneResNetObj in oneNetObj.ResolvedObj) { if (ToNodeList.Where(n => n.NetObjName == OneResNetObj[0]).Count() == 0) { List <ResolvedNetObj> tmpone = GetResolvedNetObj(new List <string> { OneResNetObj[0] }); ToNodeList.AddRange(tmpone); if (ToNodeList.Where(n => n.NetObjName == oneNetObj.NetObjName).Count() == 0) { ToNodeList.Add(oneNetObj); } } else { continue; } } } else if (ToNodeList.Where(n => n.NetObjName == oneNetObj.NetObjName).Count() == 0) { ToNodeList.Add(oneNetObj); } } /// /// Это для Destination /// List <string> CurrentDestNetObj = row[3].Split(split, StringSplitOptions.RemoveEmptyEntries).ToList(); List <ResolvedNetObj> CurrentDst = GetResolvedNetObj(CurrentDestNetObj); AddTextToStatus("Отрезолвил Объекты Назначения для " + i.ToString() + " правила"); foreach (ResolvedNetObj oneNetObj in CurrentDst) { if (oneNetObj.ResolvedObj.Count > 1) { foreach (List <string> OneResNetObj in oneNetObj.ResolvedObj) { if (ToNodeList.Where(n => n.NetObjName == OneResNetObj[0]).Count() == 0) { List <string> one = new List <string> { OneResNetObj[0] }; List <ResolvedNetObj> tmpone = GetResolvedNetObj(one); ToNodeList.AddRange(tmpone); if (ToNodeList.Where(n => n.NetObjName == oneNetObj.NetObjName).Count() == 0) { ToNodeList.Add(oneNetObj); } } else { continue; } } } else if (ToNodeList.Where(n => n.NetObjName == oneNetObj.NetObjName).Count() == 0) { ToNodeList.Add(oneNetObj); } } /// /// Это для портов /// List <string> CurrenPortObj = row[5].Split(split, StringSplitOptions.RemoveEmptyEntries).ToList(); List <List <string> > CurrentPortObjResolved = new List <List <string> >(); progressBar2.Maximum = CurrenPortObj.Count; progressBar2.Value = 0; foreach (string OnePortObj in CurrenPortObj) { string s = OnePortObj.Replace("Not ", "").Trim(); List <List <string> > tmp = ResolvePorts(s); if (OnePortObj.Contains("Not ")) { for (int b = 0; b < tmp.Count; b++) { tmp[b][0] = "Not " + tmp[b][0]; } } CurrentPortObjResolved.AddRange(tmp); if (progressBar2.InvokeRequired) { progressBar2.BeginInvoke(new MethodInvoker(() => progressBar2.Value++)); } else { progressBar2.Value++; } System.Windows.Forms.Application.DoEvents(); } AddTextToStatus("Отрезолвил Объекты порты для " + i.ToString() + " правила"); for (int a = 0; a < row.Count; a++) { if (a == 1) { WS.Cells[i, 1].Value = row[a]; } if (a == 2) { AddNetworkObj(CurrentSrc, WB, WS, i, 2); } if (a == 3) { AddNetworkObj(CurrentDst, WB, WS, i, 4); } if (a == 5) { string ThisCell = ""; foreach (List <string> PortTableRow in CurrentPortObjResolved) { ThisCell += PortTableRow[0] + " (" + PortTableRow[1] + "/" + PortTableRow[2] + ")" + Environment.NewLine; } WS.Cells[i, 6].Value = ThisCell.Replace("Icmp (Icmp/)", "ICMP") .Replace("(ALL_DCE_RPC/DCE-RPC)", "(Все DCE-RPC)") .Replace("Any (Any/)", "Any") .Replace("l_ALL_DCE_RPC(l_ALL_DCE_RPC / DCE - RPC)", "l_ALL_DCE_RPC (Все DCE-RPC)") .Trim(); } if (a == 6) { WS.Cells[i, 7].Value = row[a]; } if (a == 8) { WS.Cells[i, 8].Value = row[a].Replace("\r\n", "\n").Replace("\n\r", "\n").Replace("\r\r", "\n").Replace("\n\n", "\n").Trim(); } if (a == 9) { WS.Cells[i, 9].Value = row[a]; } if (a == 10) { WS.Cells[i, 10].Value = row[a].Replace(" ", "").Trim(); } if (row[0].Contains("Disabled")) { Microsoft.Office.Interop.Excel.Range c1 = WS.Cells[i, 1]; Microsoft.Office.Interop.Excel.Range c2 = WS.Cells[i, 10]; Range oRange = (Microsoft.Office.Interop.Excel.Range)WS.get_Range(c1, c2); oRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); oRange.Font.Bold = true; } if (a == 4 || a == 7) { continue; } } AddTextToStatus("Записал " + i.ToString() + " правило"); i++; if (progressBar1.InvokeRequired) { progressBar1.BeginInvoke(new MethodInvoker(() => progressBar1.Value++)); } else { progressBar1.Value++; } System.Windows.Forms.Application.DoEvents(); } AddTextToStatus("Добавляю лист Node"); AddNodeList(WB); if (checkBox3.Checked) { AddAllNodeList(WB); } if (CurrentErrorObjResolved.Count != 0) { PolycyConverter.Form3 form3 = new PolycyConverter.Form3(); form3.ShowDialog(this); } else { PolycyConverter.Form2 form2 = new PolycyConverter.Form2(); form2.ShowDialog(this); } } catch (Exception theException) { String errorMessage; errorMessage = "Error: "; errorMessage = String.Concat(errorMessage, theException.Message); errorMessage = String.Concat(errorMessage, " Line: "); errorMessage = String.Concat(errorMessage, theException.Source); MessageBox.Show(errorMessage, "Error"); ObjExcel.Columns.AutoFit(); ObjExcel.Rows.AutoFit(); PolycyConverter.Form3 form3 = new PolycyConverter.Form3(); form3.ShowDialog(this); return; } finally { WS.Activate(); Microsoft.Office.Interop.Excel.Range c1 = WS.Cells[1, 2]; Microsoft.Office.Interop.Excel.Range c2 = WS.Cells[i + 1, 10]; Range oRange = (Microsoft.Office.Interop.Excel.Range)WS.get_Range(c1, c2); oRange.Columns.AutoFit(); oRange.Rows.AutoFit(); WS.Application.ActiveWindow.SplitRow = 1; WS.Application.ActiveWindow.FreezePanes = true; ObjExcel.Visible = true; ObjExcel.UserControl = true; Rules.Clear(); NetworkObj.Clear(); Services.Clear(); button1.Enabled = true; numericUpDown1.Enabled = true; numericUpDown2.Enabled = true; checkBox2.Enabled = true; checkBox1.Enabled = true; Marshal.ReleaseComObject(WS); WS = null; Marshal.ReleaseComObject(WB); WB = null; Marshal.ReleaseComObject(ObjExcel); ObjExcel = null; GC.Collect(); } } }
private void Excel_SheetSelectionChange(object worksheet, Excel.Range range) { //#if DEBUG // Log.Logger.Debug("SheetSelectionChange"); //#endif }
/// <summary> /// Given a range of dotted-quad IP addresses, finds a single subnet that encapsulates all /// of the addresses listed. /// </summary> /// <param name="AddrRange">Range of IP addresses in dotted-quad format</param> /// <returns>Network block, in VLSM slash-style format</returns> public object Summarize4(Excel.Range AddrRange) { IPAddress _min = IPAddress.Any; IPAddress _max = IPAddress.Any; AddressFamily AF = AddressFamily.InterNetwork; IPAddressComparer AC = new IPAddressComparer(); object Values = AddrRange.Value; if (AddrRange.Rows.Count == 1 && AddrRange.Columns.Count == 1) { IPAddress _addr; if (!(IPAddress.TryParse(Values.ToString(), out _addr))) { return(new ErrorWrapper((int)CVErrNum.ErrValue)); } _min = _addr; _max = _addr; } else { object[,] _array = (object[, ])Values; for (int r = 1; r <= _array.GetLength(0); r++) { for (int c = 1; c <= _array.GetLength(1); c++) { object cellObj = _array.GetValue(r, c); string cellValue = cellObj.ToString(); IPAddress _addr = null; if (!(IPAddress.TryParse(cellValue, out _addr))) { return(new ErrorWrapper((int)CVErrNum.ErrValue)); } if (AF == AddressFamily.Unknown) { AF = _addr.AddressFamily; } else { if (AF != _addr.AddressFamily) { return(new ErrorWrapper((int)CVErrNum.ErrValue)); } } if (_min == IPAddress.Any || AC.Compare(_min, _addr) < 0) { _min = _addr; } if (_max == IPAddress.Any || AC.Compare(_max, _addr) > 0) { _max = _addr; } } } } for (int i = 32; i >= 0; i--) { IPAddress mask = _ConvertBitlenToMask4(i); IPAddress _network = _PerformBitmask(_min, mask); if (_network.Equals(_PerformBitmask(_max, mask))) { return(String.Format("{0}/{1}", _network, i)); } } // Flow of the program should never get here. return("0.0.0.0/0"); }
private void WorksheetFloorGridSize_Change(Excel.Range Target) { Debug.WriteLine("Floor Grid Size Worksheet changed"); }
/// <summary> /// Attempts a TCP connection to the given IP address and port. /// </summary> /// <param name="Address">IP address of the target</param> /// <param name="Port">Target TCP port</param> /// <param name="Timeout">Timeout in milliseconds. (Defaults to 3000ms.)</param> /// <returns></returns> public object TCPConnect(string Address, decimal Port, [Optional] object Timeout) { IPAddress _addr; if (!IPAddress.TryParse(Address, out _addr)) { return(new ErrorWrapper((int)CVErrNum.ErrValue)); } int _port = (int)Port; if (_port < 0 || _port > 65535) { return(new ErrorWrapper((int)CVErrNum.ErrValue)); } int _timeout = 3000; if (!(Timeout is System.Reflection.Missing)) { if (Timeout is decimal || Timeout is double) { _timeout = Convert.ToInt32(Timeout); } if (Timeout is Excel.Range) { Excel.Range _timeoutRng = (Excel.Range)Timeout; _timeout = Convert.ToInt32(_timeoutRng.Value); } } if (_timeout < 1) { return(new ErrorWrapper((int)CVErrNum.ErrValue)); } IPEndPoint _ep = new IPEndPoint(_addr, _port); Socket _sock = new Socket(_addr.AddressFamily, SocketType.Stream, ProtocolType.Tcp); _sock.SetSocketOption(SocketOptionLevel.Socket, SocketOptionName.Linger, new LingerOption(true, 0)); //_sock.SetSocketOption(SocketOptionLevel.Socket, SocketOptionName.DontLinger, true); _sock.Blocking = false; bool _success = false; DateTime _tZero = DateTime.Now; try { IAsyncResult _ar = _sock.BeginConnect(_ep, null, null); _ar.AsyncWaitHandle.WaitOne(_timeout, true); _success = _sock.Connected; if (_success) { _sock.EndConnect(_ar); } } catch (SocketException e) { if (e.ErrorCode == 10013) { return("Failed"); } return(String.Format("Cannot connect: {0}: {1}", e.ErrorCode, e.Message)); } TimeSpan _timeTaken = DateTime.Now - _tZero; //_sock.Close(0); if (_success) { return(String.Format("Success: {0}ms", _timeTaken.TotalMilliseconds)); } else { return("Failed"); } }
private void sendexcel(DataGridView drg) { int cellfin; cellfin = dtgDetalle.ColumnCount; //LLAMA copyall(); Microsoft.Office.Interop.Excel.Application excell; Microsoft.Office.Interop.Excel.Workbook workbook; Microsoft.Office.Interop.Excel.Worksheet Sheet; object miobj = System.Reflection.Missing.Value; excell = new Excel.Application(); excell.Visible = true; col = drg.Columns.Count / 26; string Letracol = "ABCDEFEHIJKLMNOPQRSTUVWXYZ"; // string Complementocol; //Determinando la letra que se usara despues de la columna 26 //if (col > 0) //{ // Columnas = drg.Columns.Count - (26 * col); // Complementocol = Letracol.ToString().Substring(col - 1, 1); //} //else //{ // Columnas = drg.Columns.Count; // Complementocol = ""; //} //string ColumnaFinal; //incre = Encoding.ASCII.GetBytes("A")[0]; string activecell = "A1"; string activecell2 = Regex.Replace(activecell, @"[^\d]", ""); int cant_lt = (activecell.Replace("$", "").Length) - activecell2.Length; string letra = activecell.Substring(0, cant_lt); int indx = Letracol.IndexOf(letra.Substring(0, 1)); if (col > 0) { Columnas = drg.Columns.Count - (26 * col); Complementocol = Letracol.ToString().Substring(col - 1, 1); } if (cant_lt > 1) { indx2 = Letracol.IndexOf(letra.Substring(1, 1)); if (cant_lt == 2) { if (drg.Columns.Count + indx > 26) { Columnas = drg.Columns.Count - 1; Complementocol = Letracol.ToString().Substring(indx2 + 1, 1); incre = Encoding.ASCII.GetBytes(letra.Substring(0, 1))[0]; incre = incre - drg.Columns.Count; } else { Columnas = drg.Columns.Count; Complementocol = Letracol.ToString().Substring(indx2, 1); incre = Encoding.ASCII.GetBytes(letra.Substring(1, 1))[0]; } } } else if (drg.Columns.Count + indx > 26) { Columnas = drg.Columns.Count - 1; Complementocol = Letracol.ToString().Substring(0, 1); incre = Encoding.ASCII.GetBytes(Complementocol)[0]; } else { Columnas = drg.Columns.Count; Complementocol = ""; incre = Encoding.ASCII.GetBytes(letra)[0]; } string ColumnaFinal; ColumnaFinal = Complementocol.ToString() + Convert.ToChar(incre + Columnas - 1).ToString(); workbook = excell.Workbooks.Add(miobj); Sheet = workbook.Worksheets.get_Item(1); Excel.Range rg = Sheet.Cells[5, 1]; Excel.Range Enc; Excel.Range det; Excel.Range RN; Excel.Range Report; Excel.Range Reportxt; rg.Select(); // obtener colummnas de encabezado for (int c = 0; c < drg.Columns.Count; c++) { //datos = nombre de la datatable que se ocupa en el grid Sheet.Cells[4, c + 1] = String.Format("{0}", dtpF.Columns[c].Caption); } Sheet.PasteSpecial(rg, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); try { // nombre de la empresa RN = Sheet.get_Range("A1", ColumnaFinal + "1"); RN.Font.Name = "Arial Rounded MT Bold"; //rango.Font.Color = Color.Blue; RN.Font.Size = 14; Sheet.Cells[1, 1] = "LESA S.A. DE C.V."; RN.Merge(); RN.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //Nombre del Reporte Report = Sheet.get_Range("A2", ColumnaFinal + "2"); Report.Font.Name = "Arial Rounded MT Bold"; Report.Font.Size = 12; Sheet.Cells[2, 1] = "REGISTRO DE ORDENES" + " FECHA " + desde + " a " + hasta; Report.Select(); Report.Merge(); Report.Font.Bold = true; Report.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; Reportxt = Sheet.get_Range("A3", ColumnaFinal + "3"); Reportxt.Font.Name = "Arial Rounded MT Bold"; Reportxt.Font.Size = 12; //Sheet.Cells[3, 1] = "BODEGAS " + Bodegaini + " a " + Bodegafin + " "; //Reportxt.Select(); //Reportxt.Merge(); //Reportxt.Font.Bold = true; //Reportxt.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //ENCABEZDO DE COLUMNAS Enc = Sheet.get_Range("A4", ColumnaFinal + 4); Enc.Font.Name = "Arial Rounded MT Bold"; Enc.Font.Size = 9; Enc.Borders.LineStyle = Excel.XlLineStyle.xlDouble; Enc.Font.Bold = true; //DETALLE //ENCABEZDO DE COLUMNAS } catch (SystemException exec) { MessageBoxButtons bt1 = MessageBoxButtons.OK; DialogResult result = MessageBox.Show(exec.ToString(), "!!!!ERROR!!!!", bt1, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1); } }
public void Spread(Excel.Range cell) { cell.Value2 = "{object}"; }
private static void copyAndInsertTotalFooter(Worksheet _worksheet, int _totalRows) { Microsoft.Office.Interop.Excel.Range _copyRange = _worksheet.Range["B165:E168"]; Microsoft.Office.Interop.Excel.Range _insertRange = _worksheet.Range["B" + (_totalRows + 3) + ":E" + (_totalRows + 6) + ""]; _insertRange.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftToRight, _copyRange.Cut()); }
public bool OnRightClick(Excel.Range target) { return(false); }
public void Export(DataTable dt, string sheetName, string title) { //Tạo các đối tượng Excel Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks oBooks; Microsoft.Office.Interop.Excel.Sheets oSheets; Microsoft.Office.Interop.Excel.Workbook oBook; Microsoft.Office.Interop.Excel.Worksheet oSheet; //Tạo mới một Excel WorkBook oExcel.Visible = true; oExcel.DisplayAlerts = false; oExcel.Application.SheetsInNewWorkbook = 1; oBooks = oExcel.Workbooks; oBook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing)); oSheets = oBook.Worksheets; oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheets.get_Item(1); oSheet.Name = sheetName; // Tạo phần đầu nếu muốn Microsoft.Office.Interop.Excel.Range head = oSheet.get_Range("A1", "D1"); head.MergeCells = true; head.Value2 = title; head.Font.Bold = true; head.Font.Name = "2020"; head.Font.Size = "18"; head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; // Tạo tiêu đề cột Microsoft.Office.Interop.Excel.Range cl1 = oSheet.get_Range("A3", "A3"); cl1.Value2 = "Năm"; cl1.ColumnWidth = 13.5; Microsoft.Office.Interop.Excel.Range cl2 = oSheet.get_Range("B3", "B3"); cl2.Value2 = "Tổng tiền"; cl2.ColumnWidth = 25.0; Microsoft.Office.Interop.Excel.Range rowHead = oSheet.get_Range("A3", "B3"); rowHead.Font.Bold = true; // Kẻ viền rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid; // Thiết lập màu nền rowHead.Interior.ColorIndex = 15; rowHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; // Tạo mẳng đối tượng để lưu dữ toàn bồ dữ liệu trong DataTable, // vì dữ liệu được được gán vào các Cell trong Excel phải thông qua object thuần. object[,] arr = new object[dt.Rows.Count, dt.Columns.Count]; //Chuyển dữ liệu từ DataTable vào mảng đối tượng for (int r = 0; r < dt.Rows.Count; r++) { DataRow dr = dt.Rows[r]; for (int c = 0; c < dt.Columns.Count; c++) { arr[r, c] = dr[c]; } } //Thiết lập vùng điền dữ liệu int rowStart = 4; int columnStart = 1; int rowEnd = rowStart + dt.Rows.Count - 1; int columnEnd = dt.Columns.Count; // Ô bắt đầu điền dữ liệu Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowStart, columnStart]; // Ô kết thúc điền dữ liệu Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnEnd]; // Lấy về vùng điền dữ liệu Microsoft.Office.Interop.Excel.Range range = oSheet.get_Range(c1, c2); //Điền dữ liệu vào vùng đã thiết lập range.Value2 = arr; // Kẻ viền range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid; // Căn giữa cột STT Microsoft.Office.Interop.Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnStart]; Microsoft.Office.Interop.Excel.Range c4 = oSheet.get_Range(c1, c3); oSheet.get_Range(c3, c4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; }
//------------------------------------------------------------------------- /* Get data from Range to Parent.DT, field names to Fields */ void SheetToTable(Excel.Worksheet ws, Excel.Range rg = null, bool fieldsOnly = false) { Fields = new List <string>(); if (!fieldsOnly) { Parent.DT = new DataTable(); } if (rg == null) { rg = ws.UsedRange; } if (rg.Columns.Count == 0 || rg.Rows.Count == 0) { return; } DataTable dt = new DataTable(); int r0 = (FirstLineNames ? 1 : 0); // fields for (int c = 1; c <= rg.Columns.Count; c++) { Type type = typeof(String); if (FirstLineNames && rg.Cells[1, c] != null && rg.Cells[1, c].Value != null && !string.IsNullOrEmpty(rg.Cells[1, c].Value.ToString().Trim())) { string colName = rg.Cells[1, c].Value.ToString().Trim(); string tmpName = colName; int suff = 0; while (dt.Columns.OfType <DataColumn>().Count(x => x.ColumnName == colName) > 0) { colName = tmpName + "_" + (++suff).ToString(); } dt.Columns.Add(colName, type); } else { dt.Columns.Add(GetRCName((rg.Cells[1, c] as Excel.Range).get_Address(), false), type); } } Fields.AddRange(dt.Columns.OfType <DataColumn>().Select(x => x.ColumnName)); // data if (!fieldsOnly) { object[,] dtArr; // for data from Range if (rg.Columns.Count == 1 && rg.Rows.Count == 1) // because if it's only one cell get_Value() not return an array { dtArr = (object[, ])Array.CreateInstance(typeof(Object), new[] { 1, 1 }, new[] { 1, 1 }); dtArr[1, 1] = rg.get_Value(); } else { dtArr = (object[, ])rg.get_Value(); } Parent.DT = dt.Clone(); object[] dtRow = new object[dtArr.GetLength(1)]; // array for table row for (int r = r0; r < dtArr.GetLength(0); r++) { for (int c = 0; c < dtArr.GetLength(1); c++) { dtRow[c] = dtArr[r + 1, c + 1]; } Parent.DT.LoadDataRow(dtRow, true); } } }
public void ecxelPoster() { DateTime pReadDate = DateTime.Now; string pSheetName = "Poster"; _Excel.Application oXL = null;; _Excel._Workbook oWB; _Excel._Worksheet oSheetPoster; _Excel._Worksheet oSheetRegnskab; _Excel.Window oWindow; _Excel.Range oRng; var rec_regnskab = Program.qryAktivRegnskab(); string SaveAs = rec_regnskab.Eksportmappe + pSheetName + pReadDate.ToString("_yyyyMMdd_hhmmss") + ".xlsx"; var JournalPoster = from h in Program.karPosteringer join d1 in Program.karKontoplan on h.Konto equals d1.Kontonr into details1 from x in details1.DefaultIfEmpty() orderby h.Nr select new clsJournalposter { ds = (x.Type == "Drift") ? "D" : "S", k = IUAP(x.Type, x.DK), Konto = h.Konto.ToString() + "-" + x.Kontonavn, Dato = h.Dato, Bilag = h.Bilag, Nr = h.Nr, Id = h.Id, Tekst = h.Tekst, Beløb = h.Nettobeløb, }; using (new ExcelUILanguageHelper()) { try { //Start Excel and get Application object. oXL = new _Excel.Application(); //oXL.Visible = false; oXL.Visible = true; //For debug //Get a new workbook. oWB = oXL.Workbooks.Add((Missing.Value)); oSheetPoster = (_Excel._Worksheet)oWB.ActiveSheet; oWindow = oXL.ActiveWindow; if (pSheetName.Length > 0) { oSheetPoster.Name = pSheetName.Substring(0, pSheetName.Length > 34 ? 34 : pSheetName.Length); } int row = 1; Program.frmMain.MainformProgressBar.Value = 0; Program.frmMain.MainformProgressBar.Minimum = 0; Program.frmMain.MainformProgressBar.Maximum = (from h in Program.karPosteringer select h).Count(); Program.frmMain.MainformProgressBar.Step = 1; Program.frmMain.MainformProgressBar.Visible = true; foreach (clsJournalposter m in JournalPoster) { Program.frmMain.MainformProgressBar.PerformStep(); row++; Type objectType = m.GetType(); PropertyInfo[] properties = objectType.GetProperties(); int col = 0; foreach (PropertyInfo property in properties) { col++; string Name = property.Name; //string NamePropertyType = property.GetValue(m, null).GetType().ToString(); oSheetPoster.Cells[row, col] = property.GetValue(m, null); if (row == 2) { object[] CustomAttributes = property.GetCustomAttributes(false); foreach (var att in CustomAttributes) { Type tp = att.GetType(); if (tp.ToString() == "Trans2Summa3060.Fieldattr") { Fieldattr attr = (Fieldattr)att; string heading = attr.Heading; oSheetPoster.Cells[1, col] = heading; } } } } } oRng = (_Excel.Range)oSheetPoster.Rows[1, Missing.Value]; oRng.Font.Name = "Arial"; oRng.Font.Size = 12; oRng.Font.Strikethrough = false; oRng.Font.Superscript = false; oRng.Font.Subscript = false; oRng.Font.OutlineFont = false; oRng.Font.Shadow = false; oRng.Font.Bold = true; oRng.HorizontalAlignment = _Excel.Constants.xlCenter; oRng.VerticalAlignment = _Excel.Constants.xlBottom; oRng.WrapText = false; oRng.Orientation = 0; oRng.AddIndent = false; oRng.IndentLevel = 0; oRng.ShrinkToFit = false; oRng.MergeCells = false; string BottomRight = "D" + row.ToString(); oRng = oSheetPoster.get_Range("D2", BottomRight); oRng.NumberFormat = "dd-mm-yyyy"; oSheetPoster.Cells.EntireColumn.AutoFit(); oWindow.SplitRow = 1; oWindow.FreezePanes = true; oSheetPoster.get_Range("A1", Missing.Value).Select(); oSheetRegnskab = (_Excel._Worksheet)oWB.Worksheets.Add(System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing); //oXL.Visible = true; //For debug _Excel.Range x1 = oSheetPoster.Cells[1, 1]; _Excel.Range x2 = oSheetPoster.Cells[row, 9]; _Excel.Range xx = oSheetPoster.get_Range(x1, x2); _Excel.PivotField _pvtField = null; _Excel.PivotTable _pivot = oSheetPoster.PivotTableWizard( _Excel.XlPivotTableSourceType.xlDatabase, //SourceType xx, //SourceData oSheetRegnskab.get_Range("A3", Missing.Value), //TableDestination "PivotTable1", //TableName System.Type.Missing, //RowGrand System.Type.Missing, //CollumnGrand System.Type.Missing, //SaveData System.Type.Missing, //HasAutoformat System.Type.Missing, //AutoPage System.Type.Missing, //Reserved System.Type.Missing, //BackgroundQuery System.Type.Missing, //OptimizeCache System.Type.Missing, //PageFieldOrder System.Type.Missing, //PageFieldWrapCount System.Type.Missing, //ReadData System.Type.Missing); //Connection _pvtField = (_Excel.PivotField)_pivot.PivotFields("ds"); _pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlRowField; _pvtField = (_Excel.PivotField)_pivot.PivotFields("k"); _pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlRowField; _pvtField = (_Excel.PivotField)_pivot.PivotFields("Konto"); _pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlRowField; _pvtField = (_Excel.PivotField)_pivot.PivotFields("Dato"); _pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlColumnField; _pvtField = (_Excel.PivotField)_pivot.PivotFields("Beløb"); _pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlDataField; _pvtField.Function = _Excel.XlConsolidationFunction.xlSum; _pvtField.NumberFormat = "#,##0"; oSheetRegnskab.Name = "Regnskab"; oRng = oSheetRegnskab.get_Range("D3", Missing.Value); oRng.Select(); bool[] Periods = { false, false, false, false, true, false, true }; oRng.Group(true, true, Missing.Value, Periods); oRng = oSheetRegnskab.get_Range("D4", "P4"); oRng.HorizontalAlignment = _Excel.XlHAlign.xlHAlignRight; oSheetRegnskab.PageSetup.LeftHeader = "&14" + rec_regnskab.Navn; oSheetRegnskab.PageSetup.CenterHeader = ""; oSheetRegnskab.PageSetup.RightHeader = "&P af &N"; oSheetRegnskab.PageSetup.LeftFooter = "&Z&F"; oSheetRegnskab.PageSetup.CenterFooter = ""; oSheetRegnskab.PageSetup.RightFooter = "&D&T"; oSheetRegnskab.PageSetup.LeftMargin = oXL.InchesToPoints(0.75); oSheetRegnskab.PageSetup.RightMargin = oXL.InchesToPoints(0.75); oSheetRegnskab.PageSetup.TopMargin = oXL.InchesToPoints(1); oSheetRegnskab.PageSetup.BottomMargin = oXL.InchesToPoints(1); oSheetRegnskab.PageSetup.HeaderMargin = oXL.InchesToPoints(0.5); oSheetRegnskab.PageSetup.FooterMargin = oXL.InchesToPoints(0.5); oSheetRegnskab.PageSetup.PrintHeadings = false; oSheetRegnskab.PageSetup.PrintGridlines = true; oSheetRegnskab.PageSetup.CenterHorizontally = false; oSheetRegnskab.PageSetup.CenterVertically = false; oSheetRegnskab.PageSetup.Orientation = _Excel.XlPageOrientation.xlLandscape; oSheetRegnskab.PageSetup.Draft = false; oSheetRegnskab.PageSetup.PaperSize = _Excel.XlPaperSize.xlPaperA4; oSheetRegnskab.PageSetup.FirstPageNumber = 1; oSheetRegnskab.PageSetup.Order = _Excel.XlOrder.xlDownThenOver; oSheetRegnskab.PageSetup.BlackAndWhite = false; oSheetRegnskab.PageSetup.Zoom = 100; oSheetRegnskab.PageSetup.PrintErrors = _Excel.XlPrintErrors.xlPrintErrorsDisplayed; oWB.ShowPivotTableFieldList = false; for (var i = oWB.Worksheets.Count; i > 0; i--) { _Excel._Worksheet oSheetWrk = (_Excel._Worksheet)oWB.Worksheets.get_Item(i); if ((oSheetWrk.Name != "Regnskab") && (oSheetWrk.Name != "Poster")) { oSheetWrk.Delete(); } } oSheetRegnskab.get_Range("A1", Missing.Value).Select(); oWB.SaveAs(SaveAs, _Excel.XlFileFormat.xlWorkbookDefault, "", "", false, false, _Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); oWB.Saved = true; oXL.Visible = true; Program.frmMain.MainformProgressBar.Visible = false; //oXL.Quit(); //oXL = null; } catch (Exception theException) { String errorMessage; errorMessage = "Error: "; errorMessage = String.Concat(errorMessage, theException.Message); errorMessage = String.Concat(errorMessage, " Line: "); errorMessage = String.Concat(errorMessage, theException.Source); MessageBox.Show(errorMessage, "Error"); } } }
/// <summary> /// 射出日报表 /// </summary> /// <param name="details"></param> private void ExportExcelForSheChu(IList <Model.ProduceInDepotDetail> details) { try { Type objClassType = null; objClassType = Type.GetTypeFromProgID("Excel.Application"); if (objClassType == null) { MessageBox.Show("本機沒有安裝Excel", "提示!", MessageBoxButtons.OK); return; } Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Application.Workbooks.Add(true); Microsoft.Office.Interop.Excel.Range r = excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 18]); r.MergeCells = true;//合并单元格 //Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter = -4108; //Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium= -4138; //Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic= -4105; excel.Cells[1, 1] = "射出日報表"; #region Set Header excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).RowHeight = 25; excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Font.Size = 20; excel.get_Range(excel.Cells[1, 1], excel.Cells[2, 18]).HorizontalAlignment = -4108; excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 18]).ColumnWidth = 12; excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 2]).ColumnWidth = 20; excel.get_Range(excel.Cells[2, 3], excel.Cells[2, 3]).ColumnWidth = 30; excel.get_Range(excel.Cells[2, 11], excel.Cells[2, 12]).ColumnWidth = 20; excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 18]).Interior.Color = 12566463; excel.get_Range(excel.Cells[2, 1], excel.Cells[details.Count + 2, 18]).RowHeight = 20; excel.get_Range(excel.Cells[2, 1], excel.Cells[details.Count + 2, 18]).Font.Size = 13; excel.get_Range(excel.Cells[3, 1], excel.Cells[details.Count + 2, 18]).WrapText = true; excel.get_Range(excel.Cells[3, 1], excel.Cells[details.Count + 2, 18]).EntireRow.AutoFit(); excel.Cells[2, 1] = "入庫日期"; excel.Cells[2, 2] = "入庫單號"; excel.Cells[2, 3] = "產品名稱"; excel.Cells[2, 4] = "公司部門"; excel.Cells[2, 5] = "單位"; excel.Cells[2, 6] = "生產數量"; excel.Cells[2, 7] = "合計生產"; excel.Cells[2, 8] = "合計合格"; excel.Cells[2, 9] = "合計入庫"; excel.Cells[2, 10] = "合計轉生產"; excel.Cells[2, 11] = "加工單"; excel.Cells[2, 12] = "客戶訂單號"; excel.Cells[2, 13] = "生產數量"; excel.Cells[2, 14] = "合格數量"; excel.Cells[2, 15] = "轉生產數量"; excel.Cells[2, 16] = "入庫數量"; excel.Cells[2, 17] = "班別"; excel.Cells[2, 18] = "機台"; #endregion for (int i = 0; i < details.Count; i++) { excel.Cells[i + 3, 1] = details[i].mProduceInDepotDate.HasValue ? details[i].mProduceInDepotDate.Value.ToString("yyyy-MM-dd") : ""; excel.Cells[i + 3, 2] = details[i].ProduceInDepotId; excel.Cells[i + 3, 3] = details[i].ProductName; excel.Cells[i + 3, 4] = details[i].WorkHousename; excel.Cells[i + 3, 5] = details[i].ProductUnit; excel.Cells[i + 3, 6] = details[i].PronoteHeaderSum; excel.Cells[i + 3, 7] = details[i].HeJiProceduresSum; excel.Cells[i + 3, 8] = details[i].HeJiCheckOutSum; excel.Cells[i + 3, 9] = details[i].HeJiProduceQuantity; excel.Cells[i + 3, 10] = details[i].HeJiProduceTransferQuantity; excel.Cells[i + 3, 11] = details[i].PronoteHeaderId; excel.Cells[i + 3, 12] = details[i].CusXOId; excel.Cells[i + 3, 13] = details[i].ProceduresSum; excel.Cells[i + 3, 14] = details[i].CheckOutSum; excel.Cells[i + 3, 15] = details[i].ProduceTransferQuantity; excel.Cells[i + 3, 16] = details[i].ProduceQuantity; excel.Cells[i + 3, 17] = details[i].BusinessHoursType; excel.Cells[i + 3, 18] = details[i].Machine; } excel.Visible = true;//是否打开该Excel文件 excel.WindowState = XlWindowState.xlMaximized; } catch { MessageBox.Show("Excel未生成完畢,請勿操作,并重新點擊按鈕生成數據!", "提示!", MessageBoxButtons.OK); return; } }
/* * inputs: category * date range * Dates must be format: yyyymmdd * output: sum of purchases */ public double calculateSum(string category, string fromDate, string toDate) { int maxIndex = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row; double sum = 0; int fromDateInt = Int32.Parse(fromDate); int toDateInt = Int32.Parse(toDate); category = category.ToLower(); foreach (Excel.Range row in xlWorkSheet.UsedRange.Rows) { dateCell = xlWorkSheet.Cells[row.Row, 1]; catCell = xlWorkSheet.Cells[row.Row, 2]; valCell = xlWorkSheet.Cells[row.Row, 3]; if (row.Row == 1) continue; if (category == "all") { if (dateCell.Value >= fromDateInt && dateCell.Value <= toDateInt) { sum += valCell.Value; } } else if (category == "other") { if (dateCell.Value >= fromDateInt && dateCell.Value <= toDateInt && !catCell.Value.ToLower().Contains("housing") && !catCell.Value.ToLower().Contains("food") && !catCell.Value.ToLower().Contains("savings") && !catCell.Value.ToLower().Contains("transportation") && !catCell.Value.ToLower().Contains("past expenses")) { sum += valCell.Value; } } else { if (dateCell.Value >= fromDateInt && dateCell.Value <= toDateInt && catCell.Value.ToLower().Contains(category)) { sum += valCell.Value; } } releaseObject(catCell); releaseObject(dateCell); releaseObject(valCell); } return sum; }
// Форматирование + формулы #region Форматирование private static void GetSum(Excel.Worksheet ws, int firstRow, int lastRow, bool getSum, string nameFile) { switch (nameFile) { case "Книга покупок.xlsx": // Форматирование для книги "Книга покупок" if (getSum == true) { sumTableExcel(ws, 1, firstRow, lastRow); } // Указание диапазонов для редактирования Excel.Range rngCol1 = (Excel.Range)ws.Range[ws.Cells[firstRow, 1], ws.Cells[lastRow, 1]]; // Столбец 1 Excel.Range rngCol2 = (Excel.Range)ws.Range[ws.Cells[firstRow, 2], ws.Cells[lastRow, 5]]; // Столбцы 2-7 Excel.Range rngCol3 = (Excel.Range)ws.Range[ws.Cells[firstRow, 6], ws.Cells[lastRow, 6]]; // Столбец 8 Excel.Range rngCol4 = (Excel.Range)ws.Range[ws.Cells[firstRow, 8], ws.Cells[lastRow, 10]]; // Столбцы 9-11 // Форматирование выравнивания rngCol1.HorizontalAlignment = Excel.Constants.xlRight; rngCol2.HorizontalAlignment = Excel.Constants.xlLeft; rngCol3.HorizontalAlignment = Excel.Constants.xlCenter; rngCol4.HorizontalAlignment = Excel.Constants.xlRight; // Установка формата rngCol4.NumberFormat = "#,##0.00"; break; case "Книга продаж.xlsx": // Форматирование для книги "Книга продаж" if (getSum == true) { sumTableExcel(ws, 2, firstRow, lastRow); } // Указание диапазонов для редактирования Excel.Range rngCol5 = (Excel.Range)ws.Range[ws.Cells[firstRow, 2], ws.Cells[lastRow, 2]]; // Столбцы 1-2 Excel.Range rngCol5_1 = (Excel.Range)ws.Range[ws.Cells[firstRow, 1], ws.Cells[lastRow, 1]]; // Столбец 1 Excel.Range rngCol6 = (Excel.Range)ws.Range[ws.Cells[firstRow, 3], ws.Cells[lastRow, 5]]; // Столбцы 3-5 Excel.Range rngCol8 = (Excel.Range)ws.Range[ws.Cells[firstRow, 7], ws.Cells[lastRow, 9]]; // Столбцы 6-8 // Форматирование выравнивания rngCol5.HorizontalAlignment = Excel.Constants.xlLeft; rngCol5_1.HorizontalAlignment = Excel.Constants.xlCenter; rngCol6.HorizontalAlignment = Excel.Constants.xlLeft; rngCol8.HorizontalAlignment = Excel.Constants.xlRight; // Установка формата rngCol8.NumberFormat = "#,##0.00"; break; case "Отчет по продажам.xlsx": // Форматирование для книги "Отчет по продажам" if (getSum == true) { sumTableExcel(ws, 3, firstRow, lastRow); } // Указание диапазонов для редактирования Excel.Range rngCol9 = (Excel.Range)ws.Range[ws.Cells[firstRow, 2], ws.Cells[lastRow, 3]]; // Столбцы 2-3 Excel.Range rngCol10 = (Excel.Range)ws.Range[ws.Cells[firstRow, 6], ws.Cells[lastRow, 6]]; // Столбец 6 Excel.Range rngCol10_1 = (Excel.Range)ws.Range[ws.Cells[firstRow, 7], ws.Cells[lastRow, 9]]; // Столбцы 7-9 // Форматирование выравнивания rngCol9.HorizontalAlignment = Excel.Constants.xlLeft; rngCol10.HorizontalAlignment = Excel.Constants.xlLeft; rngCol10_1.HorizontalAlignment = Excel.Constants.xlRight; // Установка формата rngCol10_1.NumberFormat = "#,##0.00"; break; case "Отчет по менеджерам.xlsx": // Форматирование для книги "Отчет по менеджерам" if (getSum == true) { sumTableExcel(ws, 4, firstRow, lastRow); } // Указание диапазонов для редактирования Excel.Range rngCol11 = (Excel.Range)ws.Range[ws.Cells[firstRow, 1], ws.Cells[lastRow, 2]]; // Столбцы 1-2 Excel.Range rngCol12 = (Excel.Range)ws.Range[ws.Cells[firstRow, 3], ws.Cells[lastRow, 5]]; // Столбцы 3-5 // Форматирование выравнивания rngCol11.HorizontalAlignment = Excel.Constants.xlLeft; rngCol12.HorizontalAlignment = Excel.Constants.xlRight; // Установка формата rngCol12.NumberFormat = "#,##0.00"; break; case "Счет - Накладная.xlsx": // Форматирование для книги "Счет - накладная" if (getSum == true) { sumTableExcel(ws, 5, firstRow, lastRow); } // Указание диапазонов для редактирования Excel.Range rngCol13 = (Excel.Range)ws.Range[ws.Cells[firstRow, 1], ws.Cells[lastRow, 1]]; // Столбец 1 Excel.Range rngCol14 = (Excel.Range)ws.Range[ws.Cells[firstRow, 2], ws.Cells[lastRow, 2]]; // Столбец 2 Excel.Range rngCol15 = (Excel.Range)ws.Range[ws.Cells[firstRow, 3], ws.Cells[lastRow, 4]]; // Столбцы 10-11 Excel.Range rngCol16 = (Excel.Range)ws.Range[ws.Cells[firstRow, 5], ws.Cells[lastRow, 6]]; // Столбцы 12-13 // Форматирование выравнивания rngCol13.HorizontalAlignment = Excel.Constants.xlRight; rngCol14.HorizontalAlignment = Excel.Constants.xlLeft; rngCol15.HorizontalAlignment = Excel.Constants.xlCenter; rngCol16.HorizontalAlignment = Excel.Constants.xlRight; // Установка формата rngCol16.NumberFormat = "#,##0.00"; break; case "Товарный чек.xlsx": // Форматирование для книги "Товарный чек" if (getSum == true) { sumTableExcel(ws, 6, firstRow, lastRow); } // Указание диапазонов для редактирования Excel.Range rngCol17 = (Excel.Range)ws.Range[ws.Cells[firstRow, 1], ws.Cells[lastRow, 1]]; // Столбец 1 Excel.Range rngCol18 = (Excel.Range)ws.Range[ws.Cells[firstRow, 2], ws.Cells[lastRow, 2]]; // Столбец 2 Excel.Range rngCol19 = (Excel.Range)ws.Range[ws.Cells[firstRow, 3], ws.Cells[lastRow, 4]]; // Столбцы 3-4 Excel.Range rngCol20 = (Excel.Range)ws.Range[ws.Cells[firstRow, 5], ws.Cells[lastRow, 6]]; // Столбцы 12-13 // Форматирование выравнивания rngCol17.HorizontalAlignment = Excel.Constants.xlLeft; rngCol18.HorizontalAlignment = Excel.Constants.xlRight; rngCol19.HorizontalAlignment = Excel.Constants.xlCenter; rngCol20.HorizontalAlignment = Excel.Constants.xlRight; // Установка формата rngCol20.NumberFormat = "#,##0.00"; break; case "ТОРГ-12 (Товарная накладная).xlsx": // Форматирование для книги "ТОРГ-12" if (getSum == true) { sumTableExcel(ws, 7, firstRow, lastRow); } // Указание диапазонов для редактирования Excel.Range rngCol21 = (Excel.Range)ws.Range[ws.Cells[firstRow, 1], ws.Cells[lastRow, 1]]; // Столбец 1 Excel.Range rngCol22 = (Excel.Range)ws.Range[ws.Cells[firstRow, 4], ws.Cells[lastRow, 4]]; // Столбец 4 Excel.Range rngCol23 = (Excel.Range)ws.Range[ws.Cells[firstRow, 18], ws.Cells[lastRow, 24]]; // Столбцы 18-24 Excel.Range rngCol24 = (Excel.Range)ws.Range[ws.Cells[firstRow, 27], ws.Cells[lastRow, 54]]; // Столбцы 27-54 // Форматирование выравнивания rngCol21.HorizontalAlignment = Excel.Constants.xlCenter; rngCol22.HorizontalAlignment = Excel.Constants.xlLeft; rngCol23.HorizontalAlignment = Excel.Constants.xlCenter; rngCol24.HorizontalAlignment = Excel.Constants.xlRight; // Установка формата rngCol24.NumberFormat = "#,##0.00"; break; case "ТОРГ-13 (Перемещение).xlsx": // Форматирование для книги "ТОРГ-13" if (getSum == true) { sumTableExcel(ws, 8, firstRow, lastRow); } // Указание диапазонов для редактирования Excel.Range rngCol25 = (Excel.Range)ws.Range[ws.Cells[firstRow, 1], ws.Cells[lastRow, 1]]; // Столбец 1 Excel.Range rngCol26 = (Excel.Range)ws.Range[ws.Cells[firstRow, 2], ws.Cells[lastRow, 3]]; // Столбцы 2-3 Excel.Range rngCol29 = (Excel.Range)ws.Range[ws.Cells[firstRow, 4], ws.Cells[lastRow, 6]]; // Столбцы 4-6 // Форматирование выравнивания rngCol25.HorizontalAlignment = Excel.Constants.xlLeft; rngCol25.VerticalAlignment = Excel.Constants.xlTop; rngCol26.HorizontalAlignment = Excel.Constants.xlCenter; rngCol29.HorizontalAlignment = Excel.Constants.xlRight; // Высота ячеек rngCol25.RowHeight = 12; // Установка формата rngCol29.NumberFormat = "#,##0.00"; break; case "Прайс-лист.xlsx": // Форматирование для книги "Прайс-лист" // Указание диапазонов для редактирования Excel.Range rngCol30 = (Excel.Range)ws.Range[ws.Cells[firstRow, 1], ws.Cells[lastRow, 1]]; // Столбец 1 Excel.Range rngCol31 = (Excel.Range)ws.Range[ws.Cells[firstRow, 2], ws.Cells[lastRow, 2]]; // Столбец 2 Excel.Range rngCol32 = (Excel.Range)ws.Range[ws.Cells[firstRow, 3], ws.Cells[lastRow, 3]]; // Столбец 3 Excel.Range rngCol33 = (Excel.Range)ws.Range[ws.Cells[firstRow, 4], ws.Cells[lastRow, 4]]; // Столбец 4 // Форматирование выравнивания rngCol30.HorizontalAlignment = Excel.Constants.xlRight; rngCol30.Font.Size = 10; rngCol31.HorizontalAlignment = Excel.Constants.xlLeft; rngCol31.Font.Size = 10; rngCol32.HorizontalAlignment = Excel.Constants.xlCenter; rngCol32.Font.Size = 10; rngCol33.HorizontalAlignment = Excel.Constants.xlRight; rngCol33.Font.Size = 10; // Установка формата rngCol33.NumberFormat = "#,##0.00"; break; case "Остаток товара на складе.xlsx": // Форматирование для книги "Остаток товара на складе" if (getSum == true) { sumTableExcel(ws, 9, firstRow, lastRow); } // Указание диапазонов для редактирования Excel.Range rngCol34 = (Excel.Range)ws.Range[ws.Cells[firstRow, 1], ws.Cells[lastRow, 1]]; // Столбец 1 Excel.Range rngCol35 = (Excel.Range)ws.Range[ws.Cells[firstRow, 2], ws.Cells[lastRow, 3]]; // Столбцы 2-3 Excel.Range rngCol36 = (Excel.Range)ws.Range[ws.Cells[firstRow, 4], ws.Cells[lastRow, 8]]; // Столбцы 4-8 // Форматирование выравнивания rngCol34.HorizontalAlignment = Excel.Constants.xlRight; rngCol34.Font.Size = 11; rngCol34.Font.Name = "Calibri"; rngCol35.HorizontalAlignment = Excel.Constants.xlLeft; rngCol35.Font.Size = 11; rngCol35.Font.Name = "Calibri"; rngCol36.HorizontalAlignment = Excel.Constants.xlRight; rngCol36.Font.Size = 11; rngCol36.Font.Name = "Calibri"; // Установка формата rngCol36.NumberFormat = "#,##0.00"; break; } }
private void Form1_Load(object sender, EventArgs e) { app = new Excel.Application(); app.Visible = false; workbook = app.Workbooks.Open(Application.StartupPath + "\\Дни рождения сотрудников.xls"); worksheet = (Excel.Worksheet) workbook.ActiveSheet; int i = 0; names = worksheet.get_Range("A1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count)); departaments1 = worksheet.get_Range("B1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count)); departaments2 = worksheet.get_Range("C1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count)); birthdays = worksheet.get_Range("D1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count)); string current_date = DateTime.Now.ToString("dd.MM"); for (i = 1; i <= worksheet.UsedRange.Rows.Count; i++) { string departament1_value = Convert.ToString(departaments1.Cells[i]); string departament2_value = Convert.ToString(departaments1.Cells[i]); string birthday_value = Convert.ToString(departaments1.Cells[i]); string name_value = Convert.ToString(departaments1.Cells[i]); if (departament1_value == "Іб та ПД" || departament2_value == "Іб та ПД") { if (birthday_value == current_date) label1.Text = "Сьогодні свій день народження відзначає " + name_value; } } app.Quit(); }