private void UseColorUtils() { // Colors in excel use a double representation // NetOffice color utils help to deal with them if (Application.Workbooks.Count > 0 && Application.Workbooks[1].Worksheets.Count > 0) { Excel.Worksheet sheet = Application.Workbooks[1].Worksheets[1] as Excel.Worksheet; double setColor = Utils.Color.ToDouble(Color.Red); sheet.Range("A1:B4").Interior.Color = setColor; Color getColor = Utils.Color.ToColor(sheet.Range("A1:B4").Interior.Color); } }
private static Excel.Range PutSampleData(Excel.Worksheet workSheet) { workSheet.Cells[2, 2].Value = "Datum"; workSheet.Cells[3, 2].Value = DateTime.Now.ToShortDateString(); workSheet.Cells[4, 2].Value = DateTime.Now.ToShortDateString(); workSheet.Cells[5, 2].Value = DateTime.Now.ToShortDateString(); workSheet.Cells[6, 2].Value = DateTime.Now.ToShortDateString(); workSheet.Cells[2, 3].Value = "Columns1"; workSheet.Cells[3, 3].Value = 25; workSheet.Cells[4, 3].Value = 33; workSheet.Cells[5, 3].Value = 30; workSheet.Cells[6, 3].Value = 22; workSheet.Cells[2, 4].Value = "Column2"; workSheet.Cells[3, 4].Value = 25; workSheet.Cells[4, 4].Value = 33; workSheet.Cells[5, 4].Value = 30; workSheet.Cells[6, 4].Value = 22; workSheet.Cells[2, 5].Value = "Column3"; workSheet.Cells[3, 5].Value = 25; workSheet.Cells[4, 5].Value = 33; workSheet.Cells[5, 5].Value = 30; workSheet.Cells[6, 5].Value = 22; return(workSheet.Range("$B2:$E6")); }
public TestResult DoTest() { Excel.Application application = null; DateTime startTime = DateTime.Now; try { application = new NetOffice.ExcelApi.Application(); application.DisplayAlerts = false; application.Workbooks.Add(); Excel.Worksheet sheet = application.Workbooks[1].Sheets[1] as Excel.Worksheet; for (int i = 1; i <= 200; i++) { sheet.Cells[i, 1].Value = string.Format("Test {0}", i); sheet.Range(string.Format("$B{0}", i)).Value = 42.3; } return(new TestResult(true, DateTime.Now.Subtract(startTime), "", null, "")); } catch (Exception exception) { return(new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, "")); } finally { if (null != application) { application.Quit(); application.Dispose(); } } }
private void listViewSearchResults_DoubleClick(object sender, EventArgs e) { try { if (listViewSearchResults.SelectedItems.Count > 0) { Excel.Worksheet activeSheet = Addin.Application.ActiveSheet as Excel.Worksheet; Excel.Range activeCell = Addin.Application.ActiveCell; if (null != activeCell) { int rowIndex = activeCell.Row; int columnIndex = activeCell.Column; string targetRangeAddress = CalculateRangeArea(rowIndex, columnIndex, 7); Customer selectedCustomer = listViewSearchResults.SelectedItems[0].Tag as Customer; Excel.Range targetRange = activeSheet.Range(targetRangeAddress); targetRange.Value2 = ToStringArray(selectedCustomer); targetRange.HorizontalAlignment = XlHAlign.xlHAlignLeft; activeSheet.Columns[targetRange.Column].AutoFit(); activeCell.Dispose(); activeSheet.Dispose(); } } } catch (Exception exception) { MessageBox.Show(this, exception.Message, "An error occured", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public TestResult DoTest() { Excel.Application application = null; DateTime startTime = DateTime.Now; try { application = COMObject.Create <Excel.Application>(COMObjectCreateOptions.CreateNewCore); application.DisplayAlerts = false; application.Workbooks.Add(); Excel.Worksheet sheet = application.Workbooks[1].Sheets[1] as Excel.Worksheet; foreach (Excel.Range item in sheet.Range("$A1:$B100")) { item.Value = DateTime.Now; } return(new TestResult(true, DateTime.Now.Subtract(startTime), "", null, "")); } catch (Exception exception) { return(new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, "")); } finally { if (null != application) { application.Quit(); application.Dispose(); } } }
private static void AddDataRows(Excel.Worksheet sheet, DataSet dataset, object[,] tempArray) { var range = sheet.Range(sheet.Cells[1, 1], sheet.Cells[(dataset.Tables["dataTable"].Rows.Count), (dataset.Tables["dataTable"].Columns.Count)]); sheet.Name = "Relatório"; range.Value = tempArray; }
static void Main(string[] args) { try { // Console.WriteLine("NetOffice Utils Concept Test"); Console.WriteLine("0 Milliseconds trace values is not a bug - its just to fast\r\n"); NetOffice.Settings.Default.PerformanceTrace.Enabled = true; NetOffice.Settings.Default.PerformanceTrace.Alert += new NetOffice.PerformanceTrace.PerformanceAlertEventHandler(PerformanceTrace_Alert); // Criteria 1 // Enable performance trace in excel generaly. set interval limit to 100 to see all actions there need >= 100 milliseconds NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi"].Enabled = true; NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi"].IntervalMS = 100; // Criteria 2 // Enable additional performance trace for all members of Range in excel. set interval limit to 20 to see all actions there need >=20 milliseconds NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Range"].Enabled = true; NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Range"].IntervalMS = 20; // Criteria 3 // Enable additional performance trace for WorkSheet Range property in excel. set interval limit to 0 to see all calls anywhere NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Worksheet", "Range"].Enabled = true; NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Worksheet", "Range"].IntervalMS = 0; // Criteria 4 // Enable additional performance trace for Range this[] indexer in excel. set interval limit to 0 to see all calls anywhere NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Range", "_Default"].Enabled = true; NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Range", "_Default"].IntervalMS = 0; Excel.Application application = new Excel.ApplicationClass(); application.DisplayAlerts = false; Excel.Workbook book = application.Workbooks.Add(); Excel.Worksheet sheet = book.Sheets.Add() as Excel.Worksheet; for (int i = 1; i <= 5; i++) { Excel.Range range = sheet.Range("A" + i.ToString()); range.Value = "Test123"; range[1, 1].Value = "Test234"; } application.Quit(); application.Dispose(); Console.WriteLine("\r\nTest passed"); Console.ReadKey(); } catch (Exception exception) { Console.WriteLine(exception.Message); Console.ReadKey(); } }
public void RunExample() { // start excel and turn off msg boxes Excel.Application excelApplication = new Excel.Application(); excelApplication.DisplayAlerts = false; // add a new workbook Excel.Workbook workBook = excelApplication.Workbooks.Add(); Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1]; // draw back color and perform the BorderAround method workSheet.Range("$B2:$B5").Interior.Color = ToDouble(Color.DarkGreen); workSheet.Range("$B2:$B5").BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic); // draw back color and border the range explicitly workSheet.Range("$D2:$D5").Interior.Color = ToDouble(Color.DarkGreen); workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDouble; workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Weight = 4; workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Color = ToDouble(Color.Black); workSheet.Cells[1, 1].Value = "We have 2 simple shapes created."; // save the book string fileExtension = GetDefaultExtension(excelApplication); string workbookFile = string.Format("{0}\\Example01{1}", _hostApplication.RootDirectory, fileExtension); workBook.SaveAs(workbookFile); // close excel and dispose reference excelApplication.Quit(); excelApplication.Dispose(); // show dialog for the user(you!) _hostApplication.ShowFinishDialog(null, workbookFile); }
private static void Thread2Method(object mre) { Excel.Worksheet sheet = _application.ActiveSheet as Excel.Worksheet; foreach (Excel.Range range in sheet.Range("A1:B200")) { Excel.Workbook book = range.Application.ActiveWorkbook; foreach (object item in book.Sheets) { Excel.Worksheet otherSheet = item as Excel.Worksheet; Excel.Range rng = otherSheet.Cells[1, 1]; } } (mre as ManualResetEvent).Set(); }
//private void RemoveUserInterface() //{ // // _excelApplication.CommandBars("Cell").Reset(); //} #region UI Trigger /// <summary> /// Click event trigger from created buttons. incoming call comes from excel application thread. /// </summary> /// <param name="Ctrl"></param> /// <param name="CancelDefault"></param> private void commandBarBtn_ClickEvent(NetOffice.OfficeApi.CommandBarButton Ctrl, ref bool CancelDefault) { try { Excel.Worksheet workSheet = (Excel.Worksheet)_excelApplication.ActiveWorkbook.ActiveSheet; workSheet.Cells[1, 1].Value = "ExcelVersion"; workSheet.Cells[1, 2].Value = _excelApplication.Version; // create a utils instance, not need for but helpful to keep the lines of code low NetOffice.ExcelApi.Tools.CommonUtils utils = new NetOffice.ExcelApi.Tools.CommonUtils(_excelApplication); // draw back color and perform the BorderAround method workSheet.Range("$B2:$B5").Interior.Color = utils.Color.ToDouble(Color.DarkGreen); workSheet.Range("$B2:$B5").BorderAround(XlLineStyle.xlContinuous, NetOffice.ExcelApi.Enums.XlBorderWeight.xlMedium, NetOffice.ExcelApi.Enums.XlColorIndex.xlColorIndexAutomatic); MessageBox.Show(_excelApplication.Version, "ExcelVersion", MessageBoxButtons.OK, MessageBoxIcon.Information); Ctrl.Dispose(); } catch (Exception exception) { string message = string.Format("An error occured.{0}{0}{1}", Environment.NewLine, exception.Message); MessageBox.Show(message, _progId, MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private static Excel.Range PutSampleData(Excel.Worksheet workSheet) { for (int j = 0; j < dt.Columns.Count; j++) { workSheet.Cells[1, j + 1].Value = dt.Columns[j].ColumnName; } //Populate rest of the data int iRow = 2; for (int rowNo = 0; rowNo < dt.Rows.Count; rowNo++) { for (int colNo = 0; colNo < dt.Columns.Count; colNo++) { workSheet.Cells[iRow, colNo + 1].Value = dt.Rows[rowNo][colNo].ToString(); } iRow++; } return(workSheet.Range("$B2:$E6")); }
public Xl.Worksheet WriteInterchanges(Xl.Worksheet worksheet, IEnumerable <Interchange> interchanges) { var tableColOffset = 1; var tableRowOffset = 1; var titleCell = worksheet.Cells[1, 1]; titleCell.Value = "Interchange"; //titleCell.Style = _Style.GetMasterTableHeaderStyle(worksheet); titleCell.EntireColumn.ColumnWidth = 1.0; var header = new List <object> { "PrefectureCode", "TempInterchangeId", "IC_Kana", "IC_Kanji", "Highway", "Latitude", "Longitude", "Data_Date" }; var data = new List <List <object> > { header }.Concat(interchanges.Select(r => new List <object> { r.PrefectureCode, r.TempInterchangeId, r.IC_Kana, r.IC_Kanji, r.HighwayDisplay, r.Latitude, r.Longitude , DateTime.SpecifyKind(r.DataDate, DateTimeKind.Utc).ToLocalTime() })).ToArray().CreateRectangularArray(); var tableTopLeft = worksheet.Cells[tableRowOffset + 1, tableColOffset + 1]; var tableBottomRight = worksheet.Cells[interchanges.Count() + tableRowOffset + 1, header.Count() + tableColOffset]; Xl.Range range = worksheet.Range(tableTopLeft, tableBottomRight); range.set_Value(Type.Missing, data); var opList = worksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange, range, null, XlYesNoGuess.xlYes); opList.Name = $"Zenrin.Interchange"; opList.TableStyle = "TableStyleLight9"; opList.ListColumns[8].DataBodyRange.NumberFormatLocal = Format_DateTime; var colorRange = AddinContext.ExcelApp.Union(opList.ListColumns[1].DataBodyRange, opList.ListColumns[8].DataBodyRange); colorRange.Interior.ThemeColor = XlThemeColor.xlThemeColorAccent1; colorRange.Interior.TintAndShade = 0.5; opList.Range.Columns.AutoFit(); opList.Range.Rows.AutoFit(); return(worksheet); }
private static void AddDataRows(Excel.Worksheet sheet) { sheet.Name = "Financial report"; var reportResult = GetData(); var value = new string[reportResult.Length, 5]; for (var row = 0; row < reportResult.Length; row++) { Console.WriteLine("Importing:\n---------------"); for (int col = 0; col < reportResult[row].Length; col++) { value[row, col] = reportResult[row][col]; Console.WriteLine(reportResult[row][col]); } } var range = sheet.Range(sheet.Cells[4, 1], sheet.Cells[reportResult.Length + 3, reportResult[1].Length]); range.Value = value; Console.WriteLine("Ready!!!"); }
public void Run() { // Enable and trigger trace alert NetOffice.Settings.Default.PerformanceTrace.Enabled = true; NetOffice.Settings.Default.PerformanceTrace.Alert += delegate(NetOffice.PerformanceTrace sender, NetOffice.PerformanceTrace.PerformanceAlertEventArgs args) { Console.WriteLine("{0} {1}:{2} in {3} Milliseconds ({4} Ticks)", args.CallType, args.EntityName, args.MethodName, args.TimeElapsedMS, args.Ticks); }; // Criteria 1 // Enable performance trace in excel generaly. set interval limit to 100ms to see all actions there need >= 100 milliseconds NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi"].Enabled = true; NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi"].IntervalMS = 100; // Criteria 2 // Enable additional performance trace for all members of WorkSheet in excel. set interval limit to 20ms to see all actions there need >=20 milliseconds NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Worksheet"].Enabled = true; NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Worksheet"].IntervalMS = 20; // Criteria 3 // Enable additional performance trace for WorkSheet Range property in excel. set interval limit to 0ms to see all calls anywhere NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Worksheet", "Range"].Enabled = true; NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Worksheet", "Range"].IntervalMS = 0; // do some stuff Excel.Application application = new NetOffice.ExcelApi.Application(); application.DisplayAlerts = false; Excel.Workbook book = application.Workbooks.Add(); Excel.Worksheet sheet = book.Sheets.Add() as Excel.Worksheet; for (int i = 1; i <= 5; i++) { Excel.Range range = sheet.Range("A" + i.ToString()); range.Value = "Test123"; range[1, 1].Value = "Test234"; } application.Quit(); application.Dispose(); HostApplication.ShowFinishDialog(); }
public void RunExample() { // start excel and turn off msg boxes Excel.Application excelApplication = new Excel.Application(); excelApplication.DisplayAlerts = false; // create a utils instance, not need for but helpful to keep the lines of code low CommonUtils utils = new CommonUtils(excelApplication); // add a new workbook Excel.Workbook workBook = excelApplication.Workbooks.Add(); Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1]; // draw back color and perform the BorderAround method workSheet.Range("$B2:$B5").Interior.Color = utils.Color.ToDouble(Color.DarkGreen); workSheet.Range("$B2:$B5").BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic); // draw back color and border the range explicitly workSheet.Range("$D2:$D5").Interior.Color = utils.Color.ToDouble(Color.DarkGreen); workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDouble; workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Weight = 4; workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Color = utils.Color.ToDouble(Color.Black); workSheet.Cells[1, 1].Value = "We have 2 simple shapes created."; string workbookFile = null; if (workSheet.EntityIsAvailable("ExportAsFixedFormat")) { // save the sheet as PDF workbookFile = System.IO.Path.Combine(HostApplication.RootDirectory, "Example10.pdf"); workSheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, workbookFile, XlFixedFormatQuality.xlQualityStandard); } else { // we are sorry - pdf export is not supported in Excel 2003 or below workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example10", Excel.Tools.DocumentFormat.Normal); workBook.SaveAs(workbookFile); } // close excel and dispose reference excelApplication.Quit(); excelApplication.Dispose(); // show dialog for the user(you!) HostApplication.ShowFinishDialog(null, workbookFile); }
public Xl.Worksheet WriteHighwayInterchanges(Xl.Worksheet worksheet, IEnumerable <HighwayInterchange> highwayInterchanges) { var tableColOffset = 1; var tableRowOffset = 1; var titleCell = worksheet.Cells[1, 1]; titleCell.Value = "HighwayInterchange"; //titleCell.Style = _Style.GetMasterTableHeaderStyle(worksheet); titleCell.EntireColumn.ColumnWidth = 1.0; var header = new List <object> { "HighwayId", "Highway", "Interchange", "SortOrder", "Latitude", "Longitude" }; var data = new List <List <object> > { header }.Concat(highwayInterchanges.Select(r => new List <object> { r.TempHighwayId, r.HighwayKanji, r.Interchange.IC_Kanji, r.SortOrder, r.Interchange.Latitude, r.Interchange.Longitude })).ToArray().CreateRectangularArray(); var tableTopLeft = worksheet.Cells[tableRowOffset + 1, tableColOffset + 1]; var tableBottomRight = worksheet.Cells[highwayInterchanges.Count() + tableRowOffset + 1, header.Count() + tableColOffset]; Xl.Range range = worksheet.Range(tableTopLeft, tableBottomRight); range.set_Value(Type.Missing, data); var opList = worksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange, range, null, XlYesNoGuess.xlYes); opList.Name = $"Zenrin.HighwayInterchange"; opList.TableStyle = "TableStyleLight9"; var colorRange = opList.ListColumns[1].DataBodyRange; colorRange.Interior.ThemeColor = XlThemeColor.xlThemeColorAccent1; colorRange.Interior.TintAndShade = 0.5; opList.Range.Columns.AutoFit(); opList.Range.Rows.AutoFit(); return(worksheet); }
public static void Main() { // start excel and turn off msg boxes NetOffice.ExcelApi.Application excelApplication = new NetOffice.ExcelApi.Application(); excelApplication.DisplayAlerts = false; // add a new workbook NetOffice.ExcelApi.Workbook workBook = excelApplication.Workbooks.Add(); NetOffice.ExcelApi.Worksheet workSheet = (NetOffice.ExcelApi.Worksheet)workBook.Worksheets[1]; // draw back color and perform the BorderAround method workSheet.Range("$B2:$B5").Interior.Color = ToDouble(Color.DarkGreen); workSheet.Range("$B2:$B5").BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic); // draw back color and border the range explicitly workSheet.Range("$D2:$D5").Interior.Color = ToDouble(Color.DarkGreen); workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDouble; workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Weight = 4; workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Color = ToDouble(Color.Black); workSheet.Cells[1, 1].Value = "We have 2 simple shapes created."; // we need some data to display NetOffice.ExcelApi.Range dataRange = PutSampleData(workSheet); // create a nice diagram NetOffice.ExcelApi.ChartObject chart = ((NetOffice.ExcelApi.ChartObjects)workSheet.ChartObjects()).Add(70, 100, 375, 225); chart.Chart.SetSourceData(dataRange); // save the book string fileExtension = GetDefaultExtension(excelApplication); string workbookFile = Path.Combine(Directory.GetCurrentDirectory(), string.Format("Example01{0}", fileExtension)); workBook.SaveAs(workbookFile); // close excel and dispose reference excelApplication.Quit(); excelApplication.Dispose(); }
public void RunExample() { // start excel and turn off msg boxes Excel.Application excelApplication = COMObject.Create <Excel.Application>(); excelApplication.DisplayAlerts = false; // create a utils instance, no need for but helpful to keep the lines of code low CommonUtils utils = new CommonUtils(excelApplication); // add a new workbook Excel.Workbook workBook = excelApplication.Workbooks.Add(); Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1]; // draw back color and call the BorderAround method workSheet.Range("$B2:$B5").Interior.Color = utils.Color.ToDouble(Color.DarkGreen); workSheet.Range("$B2:$B5").BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic); // draw back color and border the range explicitly workSheet.Range("$D2:$D5").Interior.Color = utils.Color.ToDouble(Color.DarkGreen); workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDouble; workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Weight = 4; workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Color = utils.Color.ToDouble(Color.Black); workSheet.Cells[1, 1].Value = "We have 2 simple shapes created."; // save the book - utils want build the filename for us string workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example01", DocumentFormat.Normal); workBook.SaveAs(workbookFile); // close excel and dispose reference excelApplication.Quit(); excelApplication.Dispose(); // show end dialog HostApplication.ShowFinishDialog(null, workbookFile); }
public TestResult DoTest() { Excel.Application application = null; DateTime startTime = DateTime.Now; try { // start excel and turn off Application msg boxes application = new Excel.Application(); application.DisplayAlerts = false; // add a new workbook Excel.Workbook workBook = application.Workbooks.Add(); Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1]; /* some kind of numerics */ // the given thread culture in all latebinding calls are stored in NetOffice.Settings. // you can change the culture. default is en-us. CultureInfo cultureInfo = NetOffice.Settings.Default.ThreadCulture; string Pattern1 = string.Format("0{0}00", cultureInfo.NumberFormat.CurrencyDecimalSeparator); string Pattern2 = string.Format("#{1}##0{0}00", cultureInfo.NumberFormat.CurrencyDecimalSeparator, cultureInfo.NumberFormat.CurrencyGroupSeparator); workSheet.Range("A1").Value = "Type"; workSheet.Range("B1").Value = "Value"; workSheet.Range("C1").Value = "Formatted " + Pattern1; workSheet.Range("D1").Value = "Formatted " + Pattern2; int integerValue = 532234; workSheet.Range("A3").Value = "Integer"; workSheet.Range("B3").Value = integerValue; workSheet.Range("C3").Value = integerValue; workSheet.Range("C3").NumberFormat = Pattern1; workSheet.Range("D3").Value = integerValue; workSheet.Range("D3").NumberFormat = Pattern2; double doubleValue = 23172.64; workSheet.Range("A4").Value = "double"; workSheet.Range("B4").Value = doubleValue; workSheet.Range("C4").Value = doubleValue; workSheet.Range("C4").NumberFormat = Pattern1; workSheet.Range("D4").Value = doubleValue; workSheet.get_Range("D4").NumberFormat = Pattern2; float floatValue = 84345.9132f; workSheet.Range("A5").Value = "float"; workSheet.Range("B5").Value = floatValue; workSheet.Range("C5").Value = floatValue; workSheet.Range("C5").NumberFormat = Pattern1; workSheet.Range("D5").Value = floatValue; workSheet.Range("D5").NumberFormat = Pattern2; Decimal decimalValue = 7251231.313367m; workSheet.Range("A6").Value = "Decimal"; workSheet.Range("B6").Value = decimalValue; workSheet.Range("C6").Value = decimalValue; workSheet.Range("C6").NumberFormat = Pattern1; workSheet.Range("D6").Value = decimalValue; workSheet.Range("D6").NumberFormat = Pattern2; workSheet.Range("A9").Value = "DateTime"; workSheet.Range("B10").Value = cultureInfo.DateTimeFormat.FullDateTimePattern; workSheet.Range("C10").Value = cultureInfo.DateTimeFormat.LongDatePattern; workSheet.Range("D10").Value = cultureInfo.DateTimeFormat.ShortDatePattern; workSheet.Range("E10").Value = cultureInfo.DateTimeFormat.LongTimePattern; workSheet.Range("F10").Value = cultureInfo.DateTimeFormat.ShortTimePattern; // DateTime DateTime dateTimeValue = DateTime.Now; workSheet.Range("B11").Value = dateTimeValue; workSheet.Range("B11").NumberFormat = cultureInfo.DateTimeFormat.FullDateTimePattern; workSheet.Range("C11").Value = dateTimeValue; workSheet.Range("C11").NumberFormat = cultureInfo.DateTimeFormat.LongDatePattern; workSheet.Range("D11").Value = dateTimeValue; workSheet.Range("D11").NumberFormat = cultureInfo.DateTimeFormat.ShortDatePattern; workSheet.Range("E11").Value = dateTimeValue; workSheet.Range("E11").NumberFormat = cultureInfo.DateTimeFormat.LongTimePattern; workSheet.Range("F11").Value = dateTimeValue; workSheet.Range("F11").NumberFormat = cultureInfo.DateTimeFormat.ShortTimePattern; // string workSheet.Range("A14").Value = "String"; workSheet.Range("B14").Value = "This is a sample String"; workSheet.Range("B14").NumberFormat = "@"; // number as string workSheet.Range("B15").Value = "513"; workSheet.Range("B15").NumberFormat = "@"; // set colums workSheet.Columns[1].AutoFit(); workSheet.Columns[2].AutoFit(); workSheet.Columns[3].AutoFit(); workSheet.Columns[4].AutoFit(); return(new TestResult(true, DateTime.Now.Subtract(startTime), "", null, "")); } catch (Exception exception) { return(new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, "")); } finally { if (null != application) { application.Quit(); application.Dispose(); } } }
public void RunExample() { // start excel and turn off msg boxes Excel.Application excelApplication = new Excel.Application(); excelApplication.DisplayAlerts = false; // add a new workbook Excel.Workbook workBook = excelApplication.Workbooks.Add(); Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1]; // font action workSheet.Range("A1").Value = "Arial Size:8 Bold Italic Underline"; workSheet.Range("A1").Font.Name = "Arial"; workSheet.Range("A1").Font.Size = 8; workSheet.Range("A1").Font.Bold = true; workSheet.Range("A1").Font.Italic = true; workSheet.Range("A1").Font.Underline = true; workSheet.Range("A1").Font.Color = Color.Violet.ToArgb(); workSheet.Range("A3").Value = "Times New Roman Size:10"; workSheet.Range("A3").Font.Name = "Times New Roman"; workSheet.Range("A3").Font.Size = 10; workSheet.Range("A3").Font.Color = Color.Orange.ToArgb(); workSheet.Range("A5").Value = "Comic Sans MS Size:12 WrapText"; workSheet.Range("A5").Font.Name = "Comic Sans MS"; workSheet.Range("A5").Font.Size = 12; workSheet.Range("A5").WrapText = true; workSheet.Range("A5").Font.Color = Color.Navy.ToArgb(); // HorizontalAlignment workSheet.Range("A7").Value = "xlHAlignLeft"; workSheet.Range("A7").HorizontalAlignment = XlHAlign.xlHAlignLeft; workSheet.Range("B7").Value = "xlHAlignCenter"; workSheet.Range("B7").HorizontalAlignment = XlHAlign.xlHAlignCenter; workSheet.Range("C7").Value = "xlHAlignRight"; workSheet.Range("C7").HorizontalAlignment = XlHAlign.xlHAlignRight; workSheet.Range("D7").Value = "xlHAlignJustify"; workSheet.Range("D7").HorizontalAlignment = XlHAlign.xlHAlignJustify; workSheet.Range("E7").Value = "xlHAlignDistributed"; workSheet.Range("E7").HorizontalAlignment = XlHAlign.xlHAlignDistributed; // VerticalAlignment workSheet.Range("A9").Value = "xlVAlignTop"; workSheet.Range("A9").VerticalAlignment = XlVAlign.xlVAlignTop; workSheet.Range("B9").Value = "xlVAlignCenter"; workSheet.Range("B9").VerticalAlignment = XlVAlign.xlVAlignCenter; workSheet.Range("C9").Value = "xlVAlignBottom"; workSheet.Range("C9").VerticalAlignment = XlVAlign.xlVAlignBottom; workSheet.Range("D9").Value = "xlVAlignDistributed"; workSheet.Range("D9").VerticalAlignment = XlVAlign.xlVAlignDistributed; workSheet.Range("E9").Value = "xlVAlignJustify"; workSheet.Range("E9").VerticalAlignment = XlVAlign.xlVAlignJustify; // setup rows and columns workSheet.Columns[1].AutoFit(); workSheet.Columns[2].ColumnWidth = 25; workSheet.Columns[3].ColumnWidth = 25; workSheet.Columns[4].ColumnWidth = 25; workSheet.Columns[5].ColumnWidth = 25; workSheet.Rows[9].RowHeight = 25; // save the book string fileExtension = GetDefaultExtension(excelApplication); string workbookFile = string.Format("{0}\\Example02{1}", _hostApplication.RootDirectory, fileExtension); workBook.SaveAs(workbookFile); // close excel and dispose reference excelApplication.Quit(); excelApplication.Dispose(); // show dialog for the user(you!) _hostApplication.ShowFinishDialog(null, workbookFile); }
public void RunExample() { // start excel and turn Application msg boxes Excel.Application excelApplication = new Excel.Application(); excelApplication.DisplayAlerts = false; // create a utils instance, not need for but helpful to keep the lines of code low CommonUtils utils = new CommonUtils(excelApplication); // add a new workbook Excel.Workbook workBook = excelApplication.Workbooks.Add(); Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1]; // the given thread culture in all NetOffice calls are stored in NetOffice.Settings. // you can change the culture of course. Default is en-us. CultureInfo cultureInfo = NetOffice.Settings.Default.ThreadCulture; string Pattern1 = string.Format("0{0}00", cultureInfo.NumberFormat.CurrencyDecimalSeparator); string Pattern2 = string.Format("#{1}##0{0}00", cultureInfo.NumberFormat.CurrencyDecimalSeparator, cultureInfo.NumberFormat.CurrencyGroupSeparator); workSheet.Range("A1").Value = "Type"; workSheet.Range("B1").Value = "Value"; workSheet.Range("C1").Value = "Formatted " + Pattern1; workSheet.Range("D1").Value = "Formatted " + Pattern2; int integerValue = 532234; workSheet.Range("A3").Value = "Integer"; workSheet.Range("B3").Value = integerValue; workSheet.Range("C3").Value = integerValue; workSheet.Range("C3").NumberFormat = Pattern1; workSheet.Range("D3").Value = integerValue; workSheet.Range("D3").NumberFormat = Pattern2; double doubleValue = 23172.64; workSheet.Range("A4").Value = "double"; workSheet.Range("B4").Value = doubleValue; workSheet.Range("C4").Value = doubleValue; workSheet.Range("C4").NumberFormat = Pattern1; workSheet.Range("D4").Value = doubleValue; workSheet.Range("D4").NumberFormat = Pattern2; float floatValue = 84345.9132f; workSheet.Range("A5").Value = "float"; workSheet.Range("B5").Value = floatValue; workSheet.Range("C5").Value = floatValue; workSheet.Range("C5").NumberFormat = Pattern1; workSheet.Range("D5").Value = floatValue; workSheet.Range("D5").NumberFormat = Pattern2; Decimal decimalValue = 7251231.313367m; workSheet.Range("A6").Value = "Decimal"; workSheet.Range("B6").Value = decimalValue; workSheet.Range("C6").Value = decimalValue; workSheet.Range("C6").NumberFormat = Pattern1; workSheet.Range("D6").Value = decimalValue; workSheet.Range("D6").NumberFormat = Pattern2; workSheet.Range("A9").Value = "DateTime"; workSheet.Range("B10").Value = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.FullDateTimePattern; workSheet.Range("C10").Value = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.LongDatePattern; workSheet.Range("D10").Value = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.ShortDatePattern; workSheet.Range("E10").Value = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.LongTimePattern; workSheet.Range("F10").Value = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.ShortTimePattern; // DateTime DateTime dateTimeValue = DateTime.Now; workSheet.Range("B11").Value = dateTimeValue; workSheet.Range("B11").NumberFormat = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.FullDateTimePattern; workSheet.Range("C11").Value = dateTimeValue; workSheet.Range("C11").NumberFormat = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.LongDatePattern; workSheet.Range("D11").Value = dateTimeValue; workSheet.Range("D11").NumberFormat = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.ShortDatePattern; workSheet.Range("E11").Value = dateTimeValue; workSheet.Range("E11").NumberFormat = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.LongTimePattern; workSheet.Range("F11").Value = dateTimeValue; workSheet.Range("F11").NumberFormat = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.ShortTimePattern; // string workSheet.Range("A14").Value = "String"; workSheet.Range("B14").Value = "This is a sample String"; workSheet.Range("B14").NumberFormat = "@"; // number as string workSheet.Range("B15").Value = "513"; workSheet.Range("B15").NumberFormat = "@"; // set colums workSheet.Columns[1].AutoFit(); workSheet.Columns[2].AutoFit(); workSheet.Columns[3].AutoFit(); workSheet.Columns[4].AutoFit(); // save the book string workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example03", Excel.Tools.DocumentFormat.Normal); workBook.SaveAs(workbookFile); // close excel and dispose reference excelApplication.Quit(); excelApplication.Dispose(); // show dialog for the user(you!) HostApplication.ShowFinishDialog(null, workbookFile); }
public static void prjTb(System.Data.DataTable fnlCns, System.Data.DataTable nwPrm, string prjNme) { string str; int num; DataRow row2; double num2; int num4; double num5; if (prjNme.Length > 0x17) { str = prjNme.Substring(0, 0x17); } else { str = prjNme; } List<string> list = new List<string>(); foreach (DataColumn column in fnlCns.Columns) { if (((column.ColumnName == "Prior Premium") || (column.ColumnName == "Prior Subsidy")) || (column.ColumnName == "Prior EE Contribution")) { list.Add(column.ColumnName); } if (column.ColumnName.Contains("Prior") && column.ColumnName.Contains("Surcharge")) { list.Add(column.ColumnName); } } System.Data.DataTable table = new System.Data.DataTable(); for (num = 0; num < list.Count; num++) { table.Columns.Add(list[num]); } foreach (DataRow row in fnlCns.Rows) { row2 = table.NewRow(); foreach (DataColumn column in table.Columns) { row2[column.ColumnName] = row[column.ColumnName]; } table.Rows.Add(row2); } List<double> list2 = new List<double>(); foreach (DataColumn column in table.Columns) { num2 = 0.0; foreach (DataRow row in table.Rows) { num2 += double.Parse(row[column.ColumnName].ToString()); } list2.Add(num2); } List<string> list3 = new List<string>(); foreach (DataColumn column in fnlCns.Columns) { if (((column.ColumnName == "New Premium") || (column.ColumnName == "New Subsidy")) || (column.ColumnName == "New EE Contribution")) { list3.Add(column.ColumnName); } if (column.ColumnName.Contains("New") && column.ColumnName.Contains("Surcharge")) { list3.Add(column.ColumnName); } } System.Data.DataTable table2 = new System.Data.DataTable(); for (num = 0; num < list3.Count; num++) { table2.Columns.Add(list3[num]); } foreach (DataRow row in fnlCns.Rows) { row2 = table2.NewRow(); foreach (DataColumn column in table2.Columns) { row2[column.ColumnName] = row[column.ColumnName]; } table2.Rows.Add(row2); } List<double> list4 = new List<double>(); foreach (DataColumn column in table2.Columns) { num2 = 0.0; foreach (DataRow row in table2.Rows) { num2 += double.Parse(row[column.ColumnName].ToString()); } list4.Add(num2); } bool flag = false; foreach (Worksheet worksheet in oWB.Worksheets) { if (worksheet.Name.Equals(str + " Summary")) { flag = true; } } if (flag) { throw new Exception("Projection with same name already exists"); } oSheet = (Worksheet) shts.Add(); oSheet.Name = str + " Summary"; oSheet.Activate(); int count = 0; count = oWB.Sheets.Count; oSheet.Move(null, shts[count]); NetOffice.ExcelApi.Range entireColumn = null; NetOffice.ExcelApi.Range range2 = null; for (num = 0; num < table.Columns.Count; num++) { oSheet.Cells[5, num + 1].Value = table.Columns[num].ColumnName; oSheet.Cells[5, num + 1].Interior.Color = Color.FromArgb(0xc5d9f1); oSheet.Cells[6, num + 1].Value = list2[num]; oSheet.Cells[6, num + 1].HorizontalAlignment = HorizontalAlignment.Center; oSheet.Cells[6, num + 1].NumberFormat = "$ #,###,###.00"; range2 = oSheet.Cells[6, num + 1]; entireColumn = range2.EntireColumn; entireColumn.AutoFit(); } for (num = 0; num < table2.Columns.Count; num++) { oSheet.Cells[8, num + 1].Value = table2.Columns[num].ColumnName; oSheet.Cells[8, num + 1].Interior.Color = Color.FromArgb(0xc5d9f1); oSheet.Cells[9, num + 1].Value = list4[num]; oSheet.Cells[9, num + 1].HorizontalAlignment = HorizontalAlignment.Center; oSheet.Cells[9, num + 1].NumberFormat = "$ #,###,###.00"; range2 = oSheet.Cells[6, num + 1]; entireColumn = range2.EntireColumn; entireColumn.AutoFit(); } oSheet.Cells[1, 1].Value = prjNme + " Summary"; oSheet.Cells[1, 1].Font.Bold = true; oSheet.Cells[1, 1].Font.Size = 20; oSheet = (Worksheet) shts.Add(); oSheet.Activate(); count = oWB.Sheets.Count; oSheet.Move(null, shts[count]); oSheet.Name = str + " Rates"; List<int> list5 = new List<int>(); for (num = 0; num < nwPrm.Columns.Count; num++) { oSheet.Cells[4, num + 1].Value = nwPrm.Columns[num].ColumnName; oSheet.Cells[4, num + 1].Interior.Color = Color.FromArgb(0xc5d9f1); if ((nwPrm.Columns[num].ColumnName == "AV") || (nwPrm.Columns[num].ColumnName == "Factor")) { list5.Add(num); } } object[,] objArray = new object[nwPrm.Rows.Count, nwPrm.Columns.Count]; for (num = 0; num < nwPrm.Rows.Count; num++) { num4 = 0; while (num4 < nwPrm.Columns.Count) { try { objArray[num, num4] = double.Parse(nwPrm.Rows[num][num4].ToString()); } catch { objArray[num, num4] = nwPrm.Rows[num][num4]; } num4++; } } NetOffice.ExcelApi.Range range3 = oSheet.Cells[5, 1]; NetOffice.ExcelApi.Range range4 = oSheet.Cells[nwPrm.Rows.Count + 4, nwPrm.Columns.Count]; oSheet.Range(range3, range4).Value = objArray; for (num = 0; num < nwPrm.Columns.Count; num++) { range2 = oSheet.Cells[4, num + 1]; try { num5 = double.Parse(oSheet.Cells[5, num + 1].Value.ToString()); if ((oSheet.Cells[4, num + 1].Value.ToString() != "AV") && (oSheet.Cells[4, num + 1].Value.ToString() != "Factor")) { entireColumn = range2.EntireColumn; entireColumn.NumberFormat = "$ #,###.00"; } entireColumn.HorizontalAlignment = HorizontalAlignment.Center; entireColumn.AutoFit(); } catch { entireColumn = range2.EntireColumn; entireColumn.HorizontalAlignment = HorizontalAlignment.Center; entireColumn.AutoFit(); } } oSheet.Cells[1, 1].Value = prjNme + " New Rates and Contributions"; oSheet.Cells[1, 1].Font.Bold = true; oSheet.Cells[1, 1].Font.Size = 20; oSheet = (Worksheet) shts.Add(); oSheet.Activate(); count = oWB.Sheets.Count; oSheet.Move(null, shts[count]); oSheet.Name = str + " Detail"; for (num = 0; num < fnlCns.Columns.Count; num++) { oSheet.Cells[4, num + 1].Value = fnlCns.Columns[num].ColumnName; oSheet.Cells[4, num + 1].Interior.Color = Color.FromArgb(0xc5d9f1); } objArray = new object[fnlCns.Rows.Count, fnlCns.Columns.Count]; for (num = 0; num < fnlCns.Rows.Count; num++) { for (num4 = 0; num4 < fnlCns.Columns.Count; num4++) { try { objArray[num, num4] = double.Parse(fnlCns.Rows[num][num4].ToString()); } catch { objArray[num, num4] = fnlCns.Rows[num][num4]; } } } range3 = oSheet.Cells[5, 1]; range4 = oSheet.Cells[fnlCns.Rows.Count + 4, fnlCns.Columns.Count]; oSheet.Range(range3, range4).Value = objArray; for (num = 0; num < fnlCns.Columns.Count; num++) { range2 = oSheet.Cells[4, num + 1]; try { num5 = double.Parse(oSheet.Cells[5, num + 1].Value.ToString()); entireColumn = range2.EntireColumn; if (num > 1) { entireColumn.NumberFormat = "$ #,###.00"; } entireColumn.HorizontalAlignment = HorizontalAlignment.Center; entireColumn.AutoFit(); } catch { entireColumn = range2.EntireColumn; entireColumn.HorizontalAlignment = HorizontalAlignment.Center; entireColumn.AutoFit(); } } oSheet.Cells[1, 1].Value = prjNme + " Detailed Census and Financial Information"; oSheet.Cells[1, 1].Font.Bold = true; oSheet.Cells[1, 1].Font.Size = 20; oSheet = (Worksheet) shts[str + " Summary"]; oSheet.Select(); }
public static void runScenarioSummary() { runCalcAction(delegate { AxCalcEngineAPI.AxCalcEngineAPI.Initialize(); shts = oWB.Worksheets; oSheet = (Worksheet) shts["Projection Input Options"]; string ancPln = (string) oSheet.Range("anchorPlan").Value; string option = (string) oSheet.Range("subType").Value; string mgOpt = (string) oSheet.Range("migType").Value; string str4 = (string) oSheet.Range("projNme").Value; string inMode = (string) oSheet.Range("modal").Value; string outMode = (string) oSheet.Range("modalOut").Value; string str7 = (string) oSheet.Range("clientNme").Value; string str8 = oSheet.Range("clientID").Value.ToString(); string str9 = ""; double modalAdj = Utility.modalConvert(inMode, outMode); if ((string.IsNullOrEmpty(str7) || string.IsNullOrEmpty(str8)) || (str8.Length != 8)) { throw new Exception("Enter valid client name and ID"); } if (string.IsNullOrEmpty(str4)) { throw new Exception("You must enter a projection name"); } string displayName = ""; try { displayName = AxCalcEngineAPI.AxCalcEngineAPI._user.DisplayName; } catch { throw new Exception("Establishing a Connection"); } if (displayName.Equals("Offline User")) { throw new Exception("You must be connected to the Aon Network to run Ax Model"); } RatingGroups groups = new RatingGroups("Rating Groups", true); PriorSurcharge surcharge = new PriorSurcharge("Rating Groups", modalAdj); NewSurcharge surcharge2 = new NewSurcharge("Rating Groups", modalAdj); PriorPremiums premiums = new PriorPremiums("Prior Premiums"); NewPremiums premiums2 = new NewPremiums("New Premiums"); Census census = new Census("Census"); System.Data.DataTable first = groups.RFactors; System.Data.DataTable prm = Utility.convertListToDataTable(premiums2.NewPrmLst); System.Data.DataTable table3 = Utility.convertListToDataTable(premiums.OldPrmLst); prm = Utility.mdlPrm(prm, modalAdj); table3 = Utility.mdlPrm(table3, modalAdj); System.Data.DataTable cns = Utility.convertListToDataTable(census.CnLst); List<System.Data.DataTable> prSurChrg = surcharge.PSCharge; List<System.Data.DataTable> nwSurChrg = surcharge2.NSCharge; System.Data.DataTable second = Utility.findPremiumCarrier(prm, str9); System.Data.DataTable dt = Utility.findPremiumCarrier(prm, ancPln); System.Data.DataTable table7 = Utility.Join(first, second); System.Data.DataTable anc = Utility.getAnchorPlans(dt, ancPln, groups.bsCvTr); System.Data.DataTable nwPrm = Utility.calcBaseSubsidy(table7, anc, option, modalAdj); System.Data.DataTable fnlCns = Utility.SurChrgCombine(Utility.mrgCnsPrm(cns, table3, nwPrm, mgOpt), prSurChrg, nwSurChrg); groups = null; surcharge = null; surcharge2 = null; premiums = null; premiums2 = null; census = null; prjTb(fnlCns, nwPrm, str4); AxCalcEngineAPI.AxCalcEngineAPI.sendCalcLog(str7, str8, "http://axmodellogger.azurewebsites.net/Default.aspx", "AxModel_0.9"); }); }
public static void writeColumn(Worksheet xlWs, object[] Column, int ColumnNo, int startRow) { try { object[,] objArray = new object[Column.Length, 1]; for (int i = 0; i < Column.Length; i++) { objArray[i, 0] = Column[i]; } Range range = xlWs.Cells[startRow, ColumnNo]; Range range2 = xlWs.Cells[startRow + objArray.GetUpperBound(0), ColumnNo]; xlWs.Range(range, range2).Value2 = objArray; xlWs.DisposeChildInstances(); } catch { } }
public TestResult DoTest() { Excel.Application application = null; DateTime startTime = DateTime.Now; try { // start excel and turn off msg boxes application = COMObject.Create <Excel.Application>(COMObjectCreateOptions.CreateNewCore); application.DisplayAlerts = false; // add a new workbook Excel.Workbook workBook = application.Workbooks.Add(); Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1]; // font action workSheet.Range("A1").Value = "Arial Size:8 Bold Italic Underline"; workSheet.Range("A1").Font.Name = "Arial"; workSheet.Range("A1").Font.Size = 8; workSheet.Range("A1").Font.Bold = true; workSheet.Range("A1").Font.Italic = true; workSheet.Range("A1").Font.Underline = true; workSheet.Range("A1").Font.Color = Color.Violet.ToArgb(); workSheet.Range("A3").Value = "Times New Roman Size:10"; workSheet.Range("A3").Font.Name = "Times New Roman"; workSheet.Range("A3").Font.Size = 10; workSheet.Range("A3").Font.Color = Color.Orange.ToArgb(); workSheet.Range("A5").Value = "Comic Sans MS Size:12 WrapText"; workSheet.Range("A5").Font.Name = "Comic Sans MS"; workSheet.Range("A5").Font.Size = 12; workSheet.Range("A5").WrapText = true; workSheet.Range("A5").Font.Color = Color.Navy.ToArgb(); // HorizontalAlignment workSheet.Range("A7").Value = "xlHAlignLeft"; workSheet.Range("A7").HorizontalAlignment = XlHAlign.xlHAlignLeft; workSheet.Range("B7").Value = "xlHAlignCenter"; workSheet.Range("B7").HorizontalAlignment = XlHAlign.xlHAlignCenter; workSheet.Range("C7").Value = "xlHAlignRight"; workSheet.Range("C7").HorizontalAlignment = XlHAlign.xlHAlignRight; workSheet.Range("D7").Value = "xlHAlignJustify"; workSheet.Range("D7").HorizontalAlignment = XlHAlign.xlHAlignJustify; workSheet.Range("E7").Value = "xlHAlignDistributed"; workSheet.Range("E7").HorizontalAlignment = XlHAlign.xlHAlignDistributed; // VerticalAlignment workSheet.Range("A9").Value = "xlVAlignTop"; workSheet.Range("A9").VerticalAlignment = XlVAlign.xlVAlignTop; workSheet.Range("B9").Value = "xlVAlignCenter"; workSheet.Range("B9").VerticalAlignment = XlVAlign.xlVAlignCenter; workSheet.Range("C9").Value = "xlVAlignBottom"; workSheet.Range("C9").VerticalAlignment = XlVAlign.xlVAlignBottom; workSheet.Range("D9").Value = "xlVAlignDistributed"; workSheet.Range("D9").VerticalAlignment = XlVAlign.xlVAlignDistributed; workSheet.Range("E9").Value = "xlVAlignJustify"; workSheet.Range("E9").VerticalAlignment = XlVAlign.xlVAlignJustify; // setup rows and columns workSheet.Columns[1].AutoFit(); workSheet.Columns[2].ColumnWidth = 25; workSheet.Columns[3].ColumnWidth = 25; workSheet.Columns[4].ColumnWidth = 25; workSheet.Columns[5].ColumnWidth = 25; workSheet.Rows[9].RowHeight = 25; return(new TestResult(true, DateTime.Now.Subtract(startTime), "", null, "")); } catch (Exception exception) { return(new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, "")); } finally { if (null != application) { application.Quit(); application.Dispose(); } } }
public void RunExample() { // start excel and turn off msg boxes Excel.Application excelApplication = COMObject.Create <Excel.Application>(); excelApplication.DisplayAlerts = false; // create a utils instance, no need for but helpful to keep the lines of code low CommonUtils utils = new CommonUtils(excelApplication); // add a new workbook Excel.Workbook workBook = excelApplication.Workbooks.Add(); Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1]; // font action workSheet.Range("A1").Value = "Arial Size:8 Bold Italic Underline"; workSheet.Range("A1").Font.Name = "Arial"; workSheet.Range("A1").Font.Size = 8; workSheet.Range("A1").Font.Bold = true; workSheet.Range("A1").Font.Italic = true; workSheet.Range("A1").Font.Underline = true; workSheet.Range("A1").Font.Color = Color.Violet.ToArgb(); workSheet.Range("A3").Value = "Times New Roman Size:10"; workSheet.Range("A3").Font.Name = "Times New Roman"; workSheet.Range("A3").Font.Size = 10; workSheet.Range("A3").Font.Color = Color.Orange.ToArgb(); workSheet.Range("A5").Value = "Comic Sans MS Size:12 WrapText"; workSheet.Range("A5").Font.Name = "Comic Sans MS"; workSheet.Range("A5").Font.Size = 12; workSheet.Range("A5").WrapText = true; workSheet.Range("A5").Font.Color = Color.Navy.ToArgb(); // HorizontalAlignment workSheet.Range("A7").Value = "xlHAlignLeft"; workSheet.Range("A7").HorizontalAlignment = XlHAlign.xlHAlignLeft; workSheet.Range("B7").Value = "xlHAlignCenter"; workSheet.Range("B7").HorizontalAlignment = XlHAlign.xlHAlignCenter; workSheet.Range("C7").Value = "xlHAlignRight"; workSheet.Range("C7").HorizontalAlignment = XlHAlign.xlHAlignRight; workSheet.Range("D7").Value = "xlHAlignJustify"; workSheet.Range("D7").HorizontalAlignment = XlHAlign.xlHAlignJustify; workSheet.Range("E7").Value = "xlHAlignDistributed"; workSheet.Range("E7").HorizontalAlignment = XlHAlign.xlHAlignDistributed; // VerticalAlignment workSheet.Range("A9").Value = "xlVAlignTop"; workSheet.Range("A9").VerticalAlignment = XlVAlign.xlVAlignTop; workSheet.Range("B9").Value = "xlVAlignCenter"; workSheet.Range("B9").VerticalAlignment = XlVAlign.xlVAlignCenter; workSheet.Range("C9").Value = "xlVAlignBottom"; workSheet.Range("C9").VerticalAlignment = XlVAlign.xlVAlignBottom; workSheet.Range("D9").Value = "xlVAlignDistributed"; workSheet.Range("D9").VerticalAlignment = XlVAlign.xlVAlignDistributed; workSheet.Range("E9").Value = "xlVAlignJustify"; workSheet.Range("E9").VerticalAlignment = XlVAlign.xlVAlignJustify; // setup rows and columns workSheet.Columns[1].AutoFit(); workSheet.Columns[2].ColumnWidth = 25; workSheet.Columns[3].ColumnWidth = 25; workSheet.Columns[4].ColumnWidth = 25; workSheet.Columns[5].ColumnWidth = 25; workSheet.Rows[9].RowHeight = 25; // save the book - utils want build the filename for us string workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example02", DocumentFormat.Normal); workBook.SaveAs(workbookFile); // close excel and dispose reference excelApplication.Quit(); excelApplication.Dispose(); // show end dialog HostApplication.ShowFinishDialog(null, workbookFile); }
private void ExecuteEvents(Timeline timeline, TimelineHandler handler) { try { foreach (TimelineEvent timelineEvent in handler.TimeLineEvents) { try { _log.Trace($"Excel event - {timelineEvent}"); WorkingHours.Is(handler); if (timelineEvent.DelayBefore > 0) { Thread.Sleep(timelineEvent.DelayBefore); } if (timeline != null) { var pids = ProcessManager.GetPids(ProcessManager.ProcessNames.Excel).ToList(); if (pids.Count > timeline.TimeLineHandlers.Count(o => o.HandlerType == HandlerType.Excel)) { return; } } // start excel and turn off msg boxes Excel.Application excelApplication = new Excel.Application { DisplayAlerts = false, Visible = true }; try { excelApplication.WindowState = XlWindowState.xlMinimized; foreach (Excel.Workbook item in excelApplication.Workbooks) { item.Windows[1].WindowState = XlWindowState.xlMinimized; } } catch (Exception e) { _log.Trace($"Could not minimize: {e}"); } // create a utils instance, not need for but helpful to keep the lines of code low CommonUtils utils = new CommonUtils(excelApplication); _log.Trace("Excel adding workbook"); // add a new workbook Excel.Workbook workBook = excelApplication.Workbooks.Add(); _log.Trace("Excel adding worksheet"); Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1]; // draw back color and perform the BorderAround method workSheet.Range("$B2:$B5").Interior.Color = utils.Color.ToDouble(Color.DarkGreen); workSheet.Range("$B2:$B5").BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic); // draw back color and border the range explicitly workSheet.Range("$D2:$D5").Interior.Color = utils.Color.ToDouble(Color.DarkGreen); workSheet.Range("$D2:$D5") .Borders[(Excel.Enums.XlBordersIndex)XlBordersIndex.xlInsideHorizontal] .LineStyle = XlLineStyle.xlDouble; workSheet.Range("$D2:$D5") .Borders[(Excel.Enums.XlBordersIndex)XlBordersIndex.xlInsideHorizontal] .Weight = 4; workSheet.Range("$D2:$D5") .Borders[(Excel.Enums.XlBordersIndex)XlBordersIndex.xlInsideHorizontal] .Color = utils.Color.ToDouble(Color.Black); var writeSleep = ProcessManager.Jitter(100); Thread.Sleep(writeSleep); workSheet.Cells[1, 1].Value = "We have 2 simple shapes created."; string rand = RandomFilename.Generate(); string dir = timelineEvent.CommandArgs[0].ToString(); if (dir.Contains("%")) { dir = Environment.ExpandEnvironmentVariables(dir); } if (Directory.Exists(dir)) { Directory.CreateDirectory(dir); } string path = $"{dir}\\{rand}.xlsx"; //if directory does not exist, create! _log.Trace($"Checking directory at {path}"); DirectoryInfo f = new FileInfo(path).Directory; if (f == null) { _log.Trace($"Directory does not exist, creating directory at {f.FullName}"); Directory.CreateDirectory(f.FullName); } try { if (File.Exists(path)) { File.Delete(path); } } catch (Exception e) { _log.Error($"Excel file delete exception: {e}"); } _log.Trace($"Excel saving to path - {path}"); workBook.SaveAs(path); FileListing.Add(path); Report(handler.HandlerType.ToString(), timelineEvent.Command, timelineEvent.CommandArgs[0].ToString()); if (timelineEvent.DelayAfter > 0) { //sleep and leave the app open _log.Trace($"Sleep after for {timelineEvent.DelayAfter}"); Thread.Sleep(timelineEvent.DelayAfter - writeSleep); } // close excel and dispose reference excelApplication.Quit(); excelApplication.Dispose(); excelApplication = null; workBook = null; workSheet = null; try { Marshal.ReleaseComObject(excelApplication); } catch { } try { Marshal.FinalReleaseComObject(excelApplication); } catch { } GC.Collect(); } catch (Exception e) { _log.Error($"Excel handler exception: {e}"); } finally { Thread.Sleep(5000); } } } catch (Exception e) { _log.Error(e); } finally { KillApp(); _log.Trace($"Excel closing..."); } }
/// <summary> /// ExcelファイルのA1セル選択処理 /// 引数1:Excelファイルパス /// </summary> /// <param name="args"></param> /// <returns></returns> public static int Main(string[] args) { //引数チェック if (!CheckArgs(args)) { return(NG); } //Excel起動 WriteLog("Excel起動待ち"); using (Excel.Application excelApplication = new Excel.Application()) { WriteLog("Excel起動"); try { //Excel描画停止(速度改善のため) ExcelBeginUpdate(excelApplication); //対象ファイルを開く WriteLog("ファイル開く"); Excel.Workbook workBook = excelApplication.Workbooks.Open(filePath); //A1セルを選択状態にする WriteLog($"全シート数:{workBook.Sheets.Count}"); for (int i = 1; i <= workBook.Sheets.Count; i++) { WriteLog($"{i}シート目処理中"); //シート取得 Excel.Worksheet sheet = (Excel.Worksheet)workBook.Sheets[i]; WriteLog($"シート名:{sheet.Name}"); //非表示のシートは操作に失敗するので無視する if (sheet.Visible != Excel.Enums.XlSheetVisibility.xlSheetVisible) { continue; } //シートを選択状態にする(こうしないとセル選択に失敗する) sheet.Select(); //倍率を100%に変更 excelApplication.ActiveWindow.Zoom = 100; //スクロールを一番先頭に変更(選択セルを変更しても、スクロールバーの位置が元のままなので、先頭に移動) excelApplication.ActiveWindow.ScrollColumn = 1; excelApplication.ActiveWindow.ScrollRow = 1; //一番左上のA1セルを選択状態に変更 sheet.Range("A1").Select(); } //一番左に存在するシートを選択状態にする WriteLog("一番左のシートを選択"); Excel.Worksheet firstSheet = (Excel.Worksheet)workBook.Sheets[1]; firstSheet.Select(); //保存 WriteLog("保存"); workBook.Save(); workBook.Close(); //Excel終了 excelApplication.Quit(); } catch (Exception ex) { //何らかのエラー発生 WriteLog("例外エラー発生", ex); return(NG); } finally { //Excelを閉じる try { excelApplication.Quit(); excelApplication.Dispose(); } catch { } //例外発生時は無視 } } return(OK); }
public void AddInventorySheetContent(string sheetHeader, string workSheetName, DataTable Tbl) { Excel.Worksheet workSheet = (Excel.Worksheet)excelApp.Worksheets[workSheetName]; AddInventoryFileHeader(workSheet); // column headings for (int i = 0; i < 4; i++) { workSheet.Cells[7, (i + 1)].Value = Tbl.Columns[i].Caption; workSheet.Cells[7, (i + 1)].Font.Bold = true; workSheet.Cells[7, (i + 1)].Font.Size = 8; workSheet.Cells[7, (i + 1)].HorizontalAlignment = XlHAlign.xlHAlignCenter; workSheet.Cells[7, (i + 1)].VerticalAlignment = XlHAlign.xlHAlignCenter; workSheet.Range(workSheet.Cells[7, i + 1], workSheet.Cells[8, i + 1]).Merge(); } workSheet.Cells[7, 13].Value = Tbl.Columns["ParcelCode"].Caption; workSheet.Cells[7, 13].Font.Bold = true; workSheet.Cells[7, 13].Font.Size = 8; workSheet.Cells[7, 13].HorizontalAlignment = XlHAlign.xlHAlignCenter; workSheet.Cells[7, 13].VerticalAlignment = XlHAlign.xlHAlignCenter; workSheet.Range(workSheet.Cells[7, 13], workSheet.Cells[8, 13]).Merge(); for (int i = 4; i < 12; i += 2) { workSheet.Cells[7, (i + 1)].Value = Tbl.Columns[i].Caption; workSheet.Range(workSheet.Cells[7, i + 1], workSheet.Cells[7, i + 2]).Merge(); workSheet.Cells[8, (i + 1)].Value = "Số lượng"; workSheet.Cells[8, (i + 2)].Value = "Thành tiền"; } //Excel.Range range = workSheet.Range(workSheet.Cells[4, (i + 1)], workSheet.Cells[4, (i + 1)]); //BorderAround(range, System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black)); var sumRemain = 0.0; var sumImport = 0.0; var sumExport = 0.0; // rows for (int i = 0; i < Tbl.Rows.Count; i++) { // workSheet.Cells[(i + 9), 0].Value = i + 1; for (int j = 0; j < Tbl.Columns.Count; j++) { workSheet.Cells[(i + 9), (j + 1)].Value = Tbl.Rows[i][j]; if (j == 3 || j == 5 || j == 7 || j == 9) { workSheet.Cells[(i + 9), (j + 2)].NumberFormat = "#,###.0"; } else if (j == 4 || j == 6 || j == 8 || j == 10) { workSheet.Cells[(i + 9), (j + 2)].NumberFormat = "#,###.0"; } } sumRemain += double.Parse(Tbl.Rows[i][4].ToString().Trim()); sumImport += double.Parse(Tbl.Rows[i][6].ToString().Trim()); sumExport += double.Parse(Tbl.Rows[i][8].ToString().Trim()); } var sumRow = Tbl.Rows.Count + 10; workSheet.Cells[sumRow, 1].Value = "TỔNG CỘNG"; workSheet.Cells[sumRow, 1].Font.Bold = true; workSheet.Cells[sumRow, 1].Font.Size = 8; workSheet.Cells[sumRow, 1].HorizontalAlignment = XlHAlign.xlHAlignCenter; workSheet.Cells[sumRow, 1].VerticalAlignment = XlHAlign.xlHAlignCenter; workSheet.Range(workSheet.Cells[sumRow, 1], workSheet.Cells[sumRow, 4]).Merge(); workSheet.Range(workSheet.Cells[sumRow, 1], workSheet.Cells[sumRow, 13]).Font.Bold = true; workSheet.Cells[sumRow, 5].Value = sumRemain; workSheet.Cells[sumRow, 7].Value = sumImport; workSheet.Cells[sumRow, 9].Value = sumExport; workSheet.Cells[sumRow, 11].Value = sumRemain + sumImport - sumExport; BorderAround(workSheet.Range(workSheet.Cells[9, 1], workSheet.Cells[sumRow, 13]), System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black)); workSheet.Range(workSheet.Cells[9, 1], workSheet.Cells[sumRow, 13]).Font.Size = 8; workSheet.Range(workSheet.Cells[9, 1], workSheet.Cells[sumRow, 13]).Borders[XlBordersIndex.xlInsideVertical].Weight = XlBorderWeight.xlThin; workSheet.Range(workSheet.Cells[9, 1], workSheet.Cells[sumRow, 13]).Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlContinuous; workSheet.Range(workSheet.Cells[9, 1], workSheet.Cells[sumRow, 13]).Borders[XlBordersIndex.xlInsideHorizontal].Weight = XlBorderWeight.xlHairline; workSheet.Range(workSheet.Cells[9, 1], workSheet.Cells[sumRow, 13]).Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlContinuous; BorderAround(workSheet.Range(workSheet.Cells[sumRow, 1], workSheet.Cells[sumRow, 13]), System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black)); //format numbers //auto fit columns workSheet.Columns.AutoFit(); }
public void AddProductionResultSheetContent(string sheetName, List <MeasurementRecord> listRecords) { Excel.Worksheet workSheet = (Excel.Worksheet)excelApp.Worksheets[sheetName]; var exportTime = this.creatingTime.Split('/'); //Title workSheet.Cells[2, 1].Value = "DivisionID"; workSheet.Cells[2, 2].Value = "DIGINET"; workSheet.Cells[3, 1].Value = "TranMonth"; workSheet.Cells[3, 2].Value = exportTime[0]; workSheet.Cells[4, 1].Value = "TranYear"; workSheet.Cells[4, 2].Value = exportTime[1]; workSheet.Range(workSheet.Cells[2, 1], workSheet.Cells[4, 1]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue); workSheet.Cells[2, 7].Value = "PRODUCTION RESULT"; workSheet.Range(workSheet.Cells[2, 7], workSheet.Cells[2, 8]).Merge(); workSheet.Cells[3, 7].Value = "KẾT QUẢ SẢN XUẤT"; workSheet.Range(workSheet.Cells[3, 7], workSheet.Cells[3, 8]).Merge(); workSheet.Range(workSheet.Cells[2, 7], workSheet.Cells[3, 8]).Font.Bold = true; workSheet.Range(workSheet.Cells[2, 7], workSheet.Cells[3, 8]).Font.Size = 14; #region header workSheet.Cells[5, 8].Value = "MASTER"; workSheet.Range(workSheet.Cells[5, 8], workSheet.Cells[5, 10]).Merge(); workSheet.Range(workSheet.Cells[5, 3], workSheet.Cells[5, 14]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); workSheet.Cells[5, 23].Value = "DETAIL"; workSheet.Range(workSheet.Cells[5, 23], workSheet.Cells[5, 25]).Merge(); workSheet.Range(workSheet.Cells[5, 15], workSheet.Cells[5, 30]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightCyan); workSheet.Cells[5, 34].Value = "SUB INFORMATION"; workSheet.Range(workSheet.Cells[5, 34], workSheet.Cells[5, 36]).Merge(); workSheet.Range(workSheet.Cells[5, 31], workSheet.Cells[5, 40]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen); workSheet.Cells[5, 45].Value = "ANALYSIS CODE"; workSheet.Range(workSheet.Cells[5, 45], workSheet.Cells[5, 47]).Merge(); workSheet.Range(workSheet.Cells[5, 41], workSheet.Cells[5, 50]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue); workSheet.Cells[5, 57].Value = "RECEIPT/DELIVERY INDEX"; workSheet.Range(workSheet.Cells[5, 57], workSheet.Cells[5, 59]).Merge(); workSheet.Range(workSheet.Cells[5, 51], workSheet.Cells[5, 61]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightPink); workSheet.Cells[5, 65].Value = "SPECIFICATION"; workSheet.Range(workSheet.Cells[5, 65], workSheet.Cells[5, 67]).Merge(); workSheet.Range(workSheet.Cells[5, 62], workSheet.Cells[5, 71]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue); int col = 3; workSheet.Cells[7, col].Value = "Loại phiếu"; workSheet.Cells[6, col++].Value = "Voucher Type"; workSheet.Cells[7, col].Value = "Loại nghiệp vụ"; workSheet.Cells[6, col++].Value = "TransType"; workSheet.Cells[7, col].Value = "Số phiếu"; workSheet.Cells[6, col++].Value = "Voucher No"; workSheet.Cells[7, col].Value = "Ngày phiếu"; workSheet.Cells[6, col++].Value = "Voucher Date"; workSheet.Cells[7, col].Value = "Diễn giải phiếu"; workSheet.Cells[6, col++].Value = "Voucher Description"; workSheet.Cells[7, col].Value = "Người lập"; workSheet.Cells[6, col++].Value = "Employee ID"; workSheet.Cells[7, col].Value = "Loại đối tượng"; workSheet.Cells[6, col++].Value = "Object Type ID"; workSheet.Cells[7, col].Value = "Mã đối tượng"; workSheet.Cells[6, col++].Value = "Object ID"; workSheet.Cells[7, col].Value = "Nhân viên KCS"; workSheet.Cells[6, col++].Value = "KCSEmployeeID"; workSheet.Cells[7, col].Value = "Ngày KCS"; workSheet.Cells[6, col++].Value = "FindDate"; workSheet.Cells[7, col].Value = "Kỳ sản xuất"; workSheet.Cells[6, col++].Value = "Prod Period"; workSheet.Cells[7, col].Value = "Loại KQSX"; workSheet.Cells[6, col++].Value = "Result Type ID"; workSheet.Cells[7, col].Value = "Mã hàng"; workSheet.Cells[6, col++].Value = "Inventory ID"; workSheet.Cells[7, col].Value = "ĐVT"; workSheet.Cells[6, col++].Value = "Unit ID"; workSheet.Cells[7, col].Value = "Số lượng"; workSheet.Cells[6, col++].Value = "Original Quantity"; workSheet.Cells[7, col].Value = "Số lượng quy đổi"; workSheet.Cells[6, col++].Value = "Converted Quantity"; workSheet.Cells[7, col].Value = "Đơn giá"; workSheet.Cells[6, col++].Value = "Unit Price"; workSheet.Cells[7, col].Value = "Thành tiền"; workSheet.Cells[6, col++].Value = "Amount"; workSheet.Cells[7, col].Value = "Ghi chú"; workSheet.Cells[6, col++].Value = "Note"; workSheet.Cells[7, col].Value = "Số lô"; workSheet.Cells[6, col++].Value = "Location No"; workSheet.Cells[7, col].Value = "Ngày sản xuất"; workSheet.Cells[6, col++].Value = "Production date"; workSheet.Cells[7, col].Value = "Ngày hết hạn"; workSheet.Cells[6, col++].Value = "Limit date"; workSheet.Cells[7, col].Value = "Mã chất lượng"; workSheet.Cells[6, col++].Value = "QualityID"; workSheet.Cells[7, col].Value = "Kế hoạch sản xuất"; workSheet.Cells[6, col++].Value = "MPSVoucherNo"; workSheet.Cells[7, col].Value = "Lệnh sản xuất"; workSheet.Cells[6, col++].Value = "ProOrderNo"; workSheet.Cells[7, col].Value = "% hoàn thành"; workSheet.Cells[6, col++].Value = "Finished Percent"; workSheet.Cells[7, col].Value = "Mã sản phẩm"; workSheet.Cells[6, col++].Value = "Product Code"; workSheet.Cells[7, col].Value = "Yếu tố chi phí"; workSheet.Cells[6, col++].Value = "Material Type"; workSheet.Cells[7, col].Value = "Số thứ 1"; workSheet.Cells[6, col++].Value = "Num 1"; workSheet.Cells[7, col].Value = "Số thứ 2"; workSheet.Cells[6, col++].Value = "Num 2"; workSheet.Cells[7, col].Value = "Số thứ 3"; workSheet.Cells[6, col++].Value = "Num 3"; workSheet.Cells[7, col].Value = "Số thứ 4"; workSheet.Cells[6, col++].Value = "Num 4"; workSheet.Cells[7, col].Value = "Số thứ 5"; workSheet.Cells[6, col++].Value = "Num 5"; workSheet.Cells[7, col].Value = "Chuỗi 1"; workSheet.Cells[6, col++].Value = "String 1"; workSheet.Cells[7, col].Value = "Chuỗi 2"; workSheet.Cells[6, col++].Value = "String 2"; workSheet.Cells[7, col].Value = "Chuỗi 3"; workSheet.Cells[6, col++].Value = "String 3"; workSheet.Cells[7, col].Value = "Chuỗi 4"; workSheet.Cells[6, col++].Value = "String 4"; workSheet.Cells[7, col].Value = "Chuỗi 5"; workSheet.Cells[6, col++].Value = "String 5"; workSheet.Cells[7, col].Value = "Khoản mục 1"; workSheet.Cells[6, col++].Value = "K-Code 01"; workSheet.Cells[7, col].Value = "Khoản mục 2"; workSheet.Cells[6, col++].Value = "K-Code 02"; workSheet.Cells[7, col].Value = "Khoản mục 3"; workSheet.Cells[6, col++].Value = "K-Code 03"; workSheet.Cells[7, col].Value = "Khoản mục 4"; workSheet.Cells[6, col++].Value = "K-Code 04"; workSheet.Cells[7, col].Value = "Khoản mục 5"; workSheet.Cells[6, col++].Value = "K-Code 05"; workSheet.Cells[7, col].Value = "Khoản mục 6"; workSheet.Cells[6, col++].Value = "K-Code 06"; workSheet.Cells[7, col].Value = "Khoản mục 7"; workSheet.Cells[6, col++].Value = "K-Code 07"; workSheet.Cells[7, col].Value = "Khoản mục 8"; workSheet.Cells[6, col++].Value = "K-Code 08"; workSheet.Cells[7, col].Value = "Khoản mục 9"; workSheet.Cells[6, col++].Value = "K-Code 09"; workSheet.Cells[7, col].Value = "Khoản mục 10"; workSheet.Cells[6, col++].Value = "K-Code 10"; workSheet.Cells[7, col].Value = "Công thức"; workSheet.Cells[6, col++].Value = "Formula"; workSheet.Cells[7, col].Value = "Chỉ số 01"; workSheet.Cells[6, col++].Value = "Num01"; workSheet.Cells[7, col].Value = "Chỉ số 02"; workSheet.Cells[6, col++].Value = "Num02"; workSheet.Cells[7, col].Value = "Chỉ số 03"; workSheet.Cells[6, col++].Value = "Num03"; workSheet.Cells[7, col].Value = "Chỉ số 04"; workSheet.Cells[6, col++].Value = "Num04"; workSheet.Cells[7, col].Value = "Chỉ số 05"; workSheet.Cells[6, col++].Value = "Num05"; workSheet.Cells[7, col].Value = "Chỉ số 06"; workSheet.Cells[6, col++].Value = "Num06"; workSheet.Cells[7, col].Value = "Chỉ số 07"; workSheet.Cells[6, col++].Value = "Num07"; workSheet.Cells[7, col].Value = "Chỉ số 08"; workSheet.Cells[6, col++].Value = "Num08"; workSheet.Cells[7, col].Value = "Chỉ số 09"; workSheet.Cells[6, col++].Value = "Num09"; workSheet.Cells[7, col].Value = "Chỉ số 10"; workSheet.Cells[6, col++].Value = "Num10"; workSheet.Cells[7, col].Value = "Quy cách 1"; workSheet.Cells[6, col++].Value = "Spec 01"; workSheet.Cells[7, col].Value = "Quy cách 2"; workSheet.Cells[6, col++].Value = "Spec 02"; workSheet.Cells[7, col].Value = "Quy cách 3"; workSheet.Cells[6, col++].Value = "Spec 03"; workSheet.Cells[7, col].Value = "Quy cách 4"; workSheet.Cells[6, col++].Value = "Spec 04"; workSheet.Cells[7, col].Value = "Quy cách 5"; workSheet.Cells[6, col++].Value = "Spec 05"; workSheet.Cells[7, col].Value = "Quy cách 6"; workSheet.Cells[6, col++].Value = "Spec 06"; workSheet.Cells[7, col].Value = "Quy cách 7"; workSheet.Cells[6, col++].Value = "Spec 07"; workSheet.Cells[7, col].Value = "Quy cách 8"; workSheet.Cells[6, col++].Value = "Spec 08"; workSheet.Cells[7, col].Value = "Quy cách 9"; workSheet.Cells[6, col++].Value = "Spec 09"; workSheet.Cells[7, col].Value = "Quy cách 10"; workSheet.Cells[6, col++].Value = "Spec 10"; workSheet.Range(workSheet.Cells[5, 3], workSheet.Cells[7, 71]).Font.Bold = true; workSheet.Range(workSheet.Cells[5, 3], workSheet.Cells[7, 71]).Font.Size = 10; workSheet.Range(workSheet.Cells[5, 3], workSheet.Cells[7, 71]).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic); workSheet.Range(workSheet.Cells[5, 3], workSheet.Cells[5, 71]).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic); workSheet.Range(workSheet.Cells[6, 3], workSheet.Cells[7, 71]).Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlContinuous; workSheet.Range(workSheet.Cells[6, 3], workSheet.Cells[7, 71]).Borders[XlBordersIndex.xlInsideVertical].Weight = XlBorderWeight.xlThin; #endregion for (int i = 0; i < listRecords.Count; i++) { var row = i + 8; //VoucherType workSheet.Cells[row, 3].Value = "KQT"; //TransType workSheet.Cells[row, 4].Value = "KQSX"; //VoucherNo var day = listRecords[i].RecordDate.Day.ToString().PadLeft(3, '0'); var month = listRecords[i].RecordDate.Month.ToString().PadLeft(2, '0'); var year = listRecords[i].RecordDate.Year.ToString().Substring(2, 2); workSheet.Cells[row, 5].Value = day + "/KQT/" + month + "/" + year; //Voucher Description string desc = (listRecords[i].Material.TypeID == (int)MaterialTypeEnum.Pat ? "Nhập Pat " : "Nhập Keo ") + listRecords[i].Material.Code + " Rev " + listRecords[i].Material.Revision; if (!listRecords[i].Description.Equals("Cân thực")) { desc = desc + " (Thí nghiệm)"; } workSheet.Cells[row, 7].Value = desc; ////Prod Period //workSheet.Cells[row, 13].Value = "KY" + this.creatingTime.ToString().PadLeft(7, '0'); //Inventory ID workSheet.Cells[row, 15].Value = listRecords[i].Material.Code; //Unit workSheet.Cells[row, 16].Value = listRecords[i].WeightUnit; //Original Quantity workSheet.Cells[row, 17].Value = Math.Round(listRecords[i].Weight, 1); //Converted Quantity workSheet.Cells[row, 18].Value = Math.Round(listRecords[i].Weight, 1); //Note workSheet.Cells[row, 21].Value = listRecords[i].Material.TypeID == (int)MaterialTypeEnum.Pat ? "NK PAT" : "NK KEO"; //Location No workSheet.Cells[row, 22].Value = listRecords[i].ParcelCode; //Production date workSheet.Cells[row, 23].Value = listRecords[i].RecordDate.ToShortDateString(); } var row2 = 8 + listRecords.Count; workSheet.Range(workSheet.Cells[5, 3], workSheet.Cells[row2, 30]).BorderAround(XlLineStyle.xlDouble, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic); workSheet.Range(workSheet.Cells[8, 3], workSheet.Cells[row2, 30]).Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlContinuous; workSheet.Range(workSheet.Cells[8, 3], workSheet.Cells[row2, 30]).Borders[XlBordersIndex.xlInsideVertical].Weight = XlBorderWeight.xlThin; workSheet.Range(workSheet.Cells[8, 3], workSheet.Cells[row2, 30]).Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlContinuous; workSheet.Range(workSheet.Cells[8, 3], workSheet.Cells[row2, 30]).Borders[XlBordersIndex.xlInsideHorizontal].Weight = XlBorderWeight.xlHairline; //auto fit columns workSheet.Columns.AutoFit(); }
public InputOptionsPage(Worksheet oSheet) { this.ancPln = (string)oSheet.Range("anchorPlan").Value; this.subsidyOption = (string)oSheet.Range("subType").Value; this.mgOpt = (string)oSheet.Range("migType").Value; this.projName = (string)oSheet.Range("projNme").Value; this.inMode = (string)oSheet.Range("modal").Value; this.outMode = (string)oSheet.Range("modalOut").Value; this.clientName = (string)oSheet.Range("clientNme").Value; this.clientID = oSheet.Range("clientID").Value.ToString(); this.subsidyValue = double.Parse(oSheet.Range("Sub").Value.ToString()); this.subsidyMin = double.Parse(oSheet.Range("Min").Value == null ? 0.ToString() : oSheet.Range("Min").Value.ToString()); this.subsidyMax = double.Parse(oSheet.Range("Max").Value == null ? 9999999999.ToString() : oSheet.Range("Max").Value.ToString()); defaultAnchorPlan = ""; this.modalAdj = Utility.modalConvert(inMode, outMode); this.displayName = ""; this.reviewerName = (string)oSheet.Range("peerReviewer").Value; this.finalModelIndicator = (((string)oSheet.Range("finalRun").Value).Equals("Yes", StringComparison.OrdinalIgnoreCase)); ClientNameIDCheck(); CheckProjName(); CheckValidSubsidyPercent(); SetAndCheckDisplayNameAndConnection(); }
public static Object[,] PullInExcelData(int startRow, int startColumn, Worksheet oSheet) { int row = oSheet.UsedRange.Rows[oSheet.UsedRange.Rows.Count].Row; int column = oSheet.UsedRange.Columns[oSheet.UsedRange.Columns.Count].Column; object[,] objArray = (object[,]) oSheet.Range(oSheet.Cells[startRow, startColumn], oSheet.Cells[row, column]) .get_Value(XlRangeValueDataType.xlRangeValueDefault); return objArray; }
private void doWork2() { PowerPoint.Presentation presentation = null; PowerPoint.Application applicationPowerPoint = null; Excel.Workbook book = null; Excel.Application applicationExcel = null; try { Dictionary <String, Excel.Shape> excelShapesDictionary = new Dictionary <string, Excel.Shape>(); Log.Information("Opening Excel Applicaiton"); applicationExcel = new Excel.Application(); applicationExcel.DisplayAlerts = false; String xlsx = "D:\\Input\\Input.xlsx"; String output_folder = "D:\\Output"; String pptxSingle = "D:\\Input\\input.pptx"; Log.Information("Opening Excel File {0}", xlsx); book = applicationExcel.Workbooks.Open(xlsx, true, true); Excel.Worksheet worksheet = (Excel.Worksheet)book.Worksheets[1]; int index = 3; List <PresentationItem> presentationItems = new List <PresentationItem>(); do { String s = worksheet.Range("A" + index).Value2.ToString(); Log.Information(worksheet.Name + " " + s); PresentationItem presentationItem = new PresentationItem(); presentationItem.OutputFileName = s; int index2 = 1; do { String attr_type = worksheet.Range(((char)((byte)'A' + index2)).ToString() + 1).Value2.ToString(); String attr_name = worksheet.Range(((char)((byte)'A' + index2)).ToString() + 2).Value2.ToString(); String attr_value = worksheet.Range(((char)((byte)'A' + index2)).ToString() + index).Value2.ToString(); Log.Information("{0} {1} {2} {3}", ((char)((byte)'A' + index2)).ToString() + 1, attr_type, attr_name, attr_value); presentationItem.Attributes.Add(new Attribute(attr_type, attr_name, attr_value)); index2++; if (index2 == 20) { break; } } while (worksheet.Range(((char)((byte)'A' + index2)).ToString() + 1).Value2 != null && worksheet.Range(((char)((byte)'A' + index2)).ToString() + 1).Value2.ToString() != ""); index++; presentationItems.Add(presentationItem); if (index == 100) { break; } } while (worksheet.Range("A" + index).Value2 != null && worksheet.Range("A" + index).Value2.ToString() != ""); Log.Information("Closing Excel File"); book.Close(); Log.Information("Opening PowerPoint Applicaiton"); applicationPowerPoint = new PowerPoint.Application(); applicationPowerPoint.DisplayAlerts = PowerPoint.Enums.PpAlertLevel.ppAlertsNone; foreach (var presentationItem in presentationItems) { Dictionary <int, List <PowerPoint.Shape> > powerpointShapesSheetDictionary = new Dictionary <int, List <PowerPoint.Shape> >(); Log.Information("Opening Presentation {0}", pptxSingle); presentation = applicationPowerPoint.Presentations.Open(pptxSingle, true, true, true); foreach (PowerPoint.Shape shape in presentation.SlideMaster.Shapes) { Log.Information("\tSlide Master: Shape Name : {0} Size(w x h) : {1} x {2} Position(left x top) : {3} , {4}", shape.Name, shape.Width, shape.Height, shape.Left, shape.Top); if (shape.HasTextFrame == Office.Enums.MsoTriState.msoTrue && shape.TextFrame.HasText == Office.Enums.MsoTriState.msoTrue) { foreach (var attribute in presentationItem.Attributes) { if (attribute.type == "Text") { string before = shape.TextFrame.TextRange.Text; shape.TextFrame.TextRange.Replace("|*" + attribute.name + "*|", attribute.value); string after = shape.TextFrame.TextRange.Text; if (before != after) { Log.Information("\t\t{0} >> {1}", before, after); } } } } } Log.Information("Iterating all shapes in all slides and filtering shapes with name staring with \"#\""); foreach (PowerPoint.Slide slide in presentation.Slides) { foreach (PowerPoint.Shape shape in slide.Shapes) { Log.Information("\tSlide No : {0} Shape Name : {1} Size(w x h) : {2} x {3} Position(left x top) : {4} , {5}", slide.SlideNumber, shape.Name, shape.Width, shape.Height, shape.Left, shape.Top); if (shape.HasTextFrame == Office.Enums.MsoTriState.msoTrue && shape.TextFrame.HasText == Office.Enums.MsoTriState.msoTrue) { foreach (var attribute in presentationItem.Attributes) { if (attribute.type == "Text") { string before = shape.TextFrame.TextRange.Text; shape.TextFrame.TextRange.Replace("|*" + attribute.name + "*|", attribute.value); string after = shape.TextFrame.TextRange.Text; if (before != after) { Log.Information("\t\t{0} >> {1}", before, after); } } } } foreach (var attribute in presentationItem.Attributes) { if (attribute.type == "Chart" && shape.Name == "|*" + attribute.name + "*|") { string before = shape.Name; shape.Name = attribute.value; string after = shape.Name; if (before != after) { Log.Information("\t\t{0} >> {1}", before, after); } } } } Log.Information(""); } String outputfile = Path.Combine(output_folder, new FileInfo(pptxSingle).Name); Log.Information("Saving a copy of updated Presentation to {0}", outputfile); presentation.SaveCopyAs(outputfile); Log.Information("Closing Presentation"); } presentation.Close(); Log.Information("Closing PowerPoint Applicaiton"); applicationPowerPoint.Quit(); applicationPowerPoint.Dispose(); Log.Information("Closing Excel Applicaiton"); applicationExcel.Quit(); applicationExcel.Dispose(); Log.Information("Done"); } catch (Exception e) { Log.Error("Fatal Error - " + e.ToString()); thisForm.BeginInvoke((MethodInvoker)(() => { MessageBox.Show(thisForm, e.ToString(), "Fatal Error", MessageBoxButtons.OK, MessageBoxIcon.Error); })); try { presentation.Close(); } catch (Exception ee) { } try { applicationPowerPoint.Quit(); applicationPowerPoint.Dispose(); } catch (Exception ee) { } try { book.Close(); } catch (Exception ee) { } try { applicationExcel.Quit(); applicationExcel.Dispose(); } catch (Exception ee) { } } }
internal void CreateWorkSheet(Worksheet worksheet) { try { worksheet.DisplayRightToLeft = false; worksheet.Name = "Loop" + channekNumber; worksheet.Range("B1:D1").Font.Bold = true; worksheet.Range("B1").Value = "loop" + channekNumber; worksheet.Range("C1").Value = "x"; worksheet.Range("D1").Value = "y"; worksheet.Range("A1").EntireColumn.ColumnWidth = 8.38; worksheet.Range("B1").EntireColumn.ColumnWidth = 11.25; worksheet.Range("C1").EntireColumn.ColumnWidth = 11.25; worksheet.Range("D1").EntireColumn.ColumnWidth = 11.25; worksheet.Range("A1:D50").HorizontalAlignment = XlHAlign.xlHAlignCenter; List<System.Windows.Point> OrderPoints = Points.OrderBy(x => x.X).ToList(); List<System.Windows.Point> OrderDetectorPoints = DetectorPoints.OrderBy(x => x.X).ToList(); worksheet.Range("C1:D" + (Freqs.Length + 1)).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic); worksheet.Range("C1:D" + (Freqs.Length + 1)).Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlDouble; worksheet.Range("C1:D" + (Freqs.Length + 1)).Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDouble; worksheet.Range("C1:D" + (Freqs.Length + 1)).Borders[XlBordersIndex.xlInsideVertical].Weight = 4; worksheet.Range("C1:D" + (Freqs.Length + 1)).Borders[XlBordersIndex.xlInsideHorizontal].Weight = 4; worksheet.Range("C1:D" + (Freqs.Length + 1)).Borders[XlBordersIndex.xlInsideVertical].Color = ToDouble(Color.Black); worksheet.Range("C1:D" + (Freqs.Length + 1)).Borders[XlBordersIndex.xlInsideHorizontal].Color = ToDouble(Color.Black); int startrow = 2; int i; for (i = 0; i < Freqs.Length; i++) { worksheet.Range("A" + (i + startrow)).Value = "f" + (i + 1); worksheet.Range("B" + (i + startrow)).Value = Freqs[i]; worksheet.Range("C" + (i + startrow)).Value = Math.Round(OrderPoints[i].X, 2); worksheet.Range("D" + (i + startrow)).Value = Math.Round(OrderPoints[i].Y, 2); worksheet.Range("F" + (i + startrow)).Value = Math.Round(OrderDetectorPoints[i].X, 2); worksheet.Range("G" + (i + startrow)).Value = Math.Round(OrderDetectorPoints[i].Y, 2); } startrow = i + 3; worksheet.Range("A" + (startrow) + ":C" + (Freqs.Length + startrow)).Borders.LineStyle = XlLineStyle.xlContinuous; worksheet.Range("A" + (startrow) + ":C" + (Freqs.Length + startrow)).Borders.Weight = 2d; worksheet.Range("B" + (startrow)).Value = "Ltot"; worksheet.Range("C" + (startrow)).Value = "L"; startrow++; for (i = 0; i < Points.Length; i++) { worksheet.Range("A" + (i + startrow)).Value = (i + 1); worksheet.Range("B" + (i + startrow)).Value = Math.Round(Points[i].X, 2); worksheet.Range("C" + (i + startrow)).Value = Math.Round(Points[i].Y, 2); } startrow += i + 2; worksheet.Range("A2:A" + startrow).HorizontalAlignment = XlHAlign.xlHAlignLeft; worksheet.Range("A" + (startrow) + ":C" + (2 + startrow)).Borders.LineStyle = XlLineStyle.xlContinuous; worksheet.Range("A" + (startrow) + ":C" + (2 + startrow)).Borders.Weight = 2d; worksheet.Range("A" + startrow + ":C" + startrow).Merge(); worksheet.Range("A" + startrow + ":C" + startrow).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow); worksheet.Range("A" + startrow + ":C" + startrow).Value = PolinomFunction1; startrow++; worksheet.Range("A" + startrow).Value = "a1"; worksheet.Range("B" + startrow).Value = "b1"; worksheet.Range("C" + startrow).Value = "c1"; startrow++; worksheet.Range("A" + startrow).Value = A1; worksheet.Range("B" + startrow).Value = B1; worksheet.Range("C" + startrow).Value = C1; startrow++; startrow++; worksheet.Range("A" + (startrow) + ":C" + (2 + startrow)).Borders.LineStyle = XlLineStyle.xlContinuous; worksheet.Range("A" + (startrow) + ":C" + (2 + startrow)).Borders.Weight = 2d; worksheet.Range("A" + startrow + ":C" + startrow).Merge(); worksheet.Range("A" + startrow + ":C" + startrow).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow); worksheet.Range("A" + startrow + ":C" + startrow).Value = PolinomFunction2; startrow++; worksheet.Range("A" + startrow).Value = "a2"; worksheet.Range("B" + startrow).Value = "b2"; worksheet.Range("C" + startrow).Value = "c2"; startrow++; worksheet.Range("A" + startrow).Value = A2; worksheet.Range("B" + startrow).Value = B2; worksheet.Range("C" + startrow).Value = C2; startrow++; startrow++; worksheet.Range("A" + (startrow) + ":B" + (2 + startrow)).Borders.LineStyle = XlLineStyle.xlContinuous; worksheet.Range("A" + (startrow) + ":B" + (2 + startrow)).Borders.Weight = 2d; worksheet.Range("A" + startrow + ":B" + startrow).Merge(); worksheet.Range("A" + startrow + ":B" + startrow).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow); worksheet.Range("A" + startrow + ":B" + startrow).Value = LineFunction; startrow++; worksheet.Range("A" + startrow).Value = "k"; worksheet.Range("B" + startrow).Value = "l"; startrow++; worksheet.Range("A" + startrow).Value = K; worksheet.Range("B" + startrow).Value = L; ChartObject chart = ((ChartObjects)worksheet.ChartObjects()).Add(300, 0, 770, 500); chart.Chart.ChartType = XlChartType.xlXYScatterSmoothNoMarkers; chart.Chart.HasLegend = true; chart.Chart.Legend.Position = XlLegendPosition.xlLegendPositionRight; chart.Chart.Legend.Left = 680; chart.Chart.Legend.Top = 10; chart.Chart.Legend.Format.Fill.ForeColor.RGB = ColorTranslator.ToOle(Color.LightGray); chart.Chart.Legend.Border.Color = ColorTranslator.ToOle(Color.DarkGray); chart.Chart.HasTitle = true; chart.Chart.ChartTitle.Text = "Loop " + channekNumber; Axis axisy = (Axis)chart.Chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary); axisy.HasTitle = true; axisy.AxisTitle.Text = "L Loop"; Axis axisx = (Axis)chart.Chart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary); axisx.HasTitle = true; axisx.AxisTitle.Text = "L Total"; SeriesCollection seriesCollection = (SeriesCollection)chart.Chart.SeriesCollection(); #region Calibration Point using (Series oSeries = seriesCollection.NewSeries()) { oSeries.XValues = worksheet.get_Range("C2", "C9"); oSeries.Values = worksheet.get_Range("D2", "D9"); oSeries.ChartType = XlChartType.xlXYScatter; oSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowLabel); oSeries.HasDataLabels = true; DataLabels labels = (DataLabels)oSeries.DataLabels(); labels.Separator = " , "; labels.ShowCategoryName = true; labels.ShowValue = true; oSeries.MarkerForegroundColor = ColorTranslator.ToOle(GetColor(CalibrateModel.Table1Color)); oSeries.MarkerBackgroundColor = ColorTranslator.ToOle(GetColor(CalibrateModel.Table1Color)); oSeries.MarkerStyle = XlMarkerStyle.xlMarkerStyleCircle; oSeries.MarkerSize = 10; oSeries.Name = "Calibration"; } #endregion #region Measurements Point using (Series oSeries = seriesCollection.NewSeries()) { oSeries.XValues = worksheet.get_Range("F2", "F9"); oSeries.Values = worksheet.get_Range("G2", "G9"); oSeries.ChartType = XlChartType.xlXYScatter; oSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowNone); oSeries.MarkerForegroundColor = ColorTranslator.ToOle(GetColor(CalibrateModel.Table2Color)); oSeries.MarkerBackgroundColor = ColorTranslator.ToOle(GetColor(CalibrateModel.Table2Color)); oSeries.MarkerStyle = XlMarkerStyle.xlMarkerStyleDiamond; oSeries.MarkerSize = 10; oSeries.Name = "Measurements"; } #endregion #region Line using (Series oSeries = seriesCollection.NewSeries()) { oSeries.XValues = worksheet.get_Range("C2", "C3"); oSeries.Values = worksheet.get_Range("D2", "D3"); oSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowNone); oSeries.MarkerStyle = XlMarkerStyle.xlMarkerStyleNone; oSeries.ChartType = XlChartType.xlXYScatterLinesNoMarkers; oSeries.Format.Line.ForeColor.RGB = ColorTranslator.ToOle(GetColor(CalibrateModel.F3Color)); oSeries.Format.Line.BackColor.RGB = ColorTranslator.ToOle(GetColor(CalibrateModel.F3Color)); oSeries.Name = LineFunctionNumber.ToString(); } #endregion #region F1 startrow = 100; foreach (var item in Calc1000Ponts(A1, B1, C1, Gragh1)) { worksheet.Range("A" + (startrow)).Value = Math.Round(item.X, 2); worksheet.Range("B" + (startrow)).Value = Math.Round(item.Y, 2); startrow++; } using (Series oSeries = seriesCollection.NewSeries()) { oSeries.XValues = worksheet.get_Range("A100", "A" + startrow); oSeries.Values = worksheet.get_Range("B100", "B" + startrow); oSeries.ChartType = XlChartType.xlXYScatterSmoothNoMarkers; oSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowNone); oSeries.MarkerStyle = XlMarkerStyle.xlMarkerStyleNone; oSeries.Format.Line.ForeColor.RGB = ColorTranslator.ToOle(GetColor(CalibrateModel.F1Color)); oSeries.Format.Line.BackColor.RGB = ColorTranslator.ToOle(GetColor(CalibrateModel.F1Color)); oSeries.Name = PolinomFunction1Number.ToString(); } #endregion #region F1 startrow = 100; foreach (var item in Calc1000Ponts(A2, B2, C2, Gragh2)) { worksheet.Range("C" + (startrow)).Value = Math.Round(item.X, 2); worksheet.Range("D" + (startrow)).Value = Math.Round(item.Y, 2); startrow++; } using (Series oSeries = seriesCollection.NewSeries()) { oSeries.XValues = worksheet.get_Range("C100", "C" + startrow); oSeries.Values = worksheet.get_Range("D100", "D" + startrow); oSeries.ChartType = XlChartType.xlXYScatterSmoothNoMarkers; oSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowNone); oSeries.MarkerStyle = XlMarkerStyle.xlMarkerStyleNone; oSeries.Format.Line.ForeColor.RGB = ColorTranslator.ToOle(GetColor(CalibrateModel.F2Color)); oSeries.Format.Line.BackColor.RGB = ColorTranslator.ToOle(GetColor(CalibrateModel.F2Color)); oSeries.Name = PolinomFunction2Number.ToString(); } #endregion if (!System.IO.Directory.Exists(CalibrateModel.filespath)) System.IO.Directory.CreateDirectory(CalibrateModel.filespath); try { chart.Chart.Export(CalibrateModel.filespath + @"ChartLoop" + channekNumber + ".jpeg", "JPEG"); } catch (Exception xe) { Console.WriteLine(xe.Message); } } catch { releaseObject(worksheet); } }
public void AddExportSheetContent(string sheetName, List <MeasurementDetailRecord> listRecordDetails) { Excel.Worksheet workSheet = (Excel.Worksheet)excelApp.Worksheets[sheetName]; //Title workSheet.Cells[4, 1].Value = "BÁO CÁO XUẤT NGUYÊN VẬT LIỆU"; workSheet.Range(workSheet.Cells[4, 1], workSheet.Cells[4, 13]).Merge(); workSheet.Cells[5, 1].Value = "Kỳ: Tháng " + this.creatingTime; workSheet.Cells[5, 1].Font.Size = 10; workSheet.Range(workSheet.Cells[5, 1], workSheet.Cells[5, 13]).Merge(); workSheet.Cells[7, 3].Value = "Nghiệp vụ(TransType)"; workSheet.Cells[7, 3].Font.Size = 10; workSheet.Cells[7, 3].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); workSheet.Cells[7, 4].Value = "PXK"; workSheet.Cells[7, 4].Font.Size = 10; workSheet.Cells[8, 3].Value = "Loại nghiệp vụ(TransTypeID)"; workSheet.Cells[8, 3].Font.Size = 10; workSheet.Cells[8, 3].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); workSheet.Cells[8, 4].Value = "2"; workSheet.Cells[8, 4].Font.Size = 10; workSheet.Range(workSheet.Cells[4, 1], workSheet.Cells[8, 13]).Font.Bold = true; workSheet.Range(workSheet.Cells[4, 1], workSheet.Cells[5, 13]).HorizontalAlignment = XlHAlign.xlHAlignCenter; workSheet.Range(workSheet.Cells[4, 1], workSheet.Cells[5, 13]).VerticalAlignment = XlHAlign.xlHAlignCenter; #region header workSheet.Cells[10, 2].Value = "Loại phiếu"; workSheet.Cells[11, 2].Value = "VoucherTypeID"; workSheet.Cells[12, 2].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 3].Value = "Số phiếu"; workSheet.Cells[11, 3].Value = "VoucherNo"; workSheet.Cells[12, 3].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 4].Value = "Ngày phiếu"; workSheet.Cells[11, 4].Value = "VoucherDate"; workSheet.Cells[12, 4].Value = "Kiểu ngày"; workSheet.Cells[10, 5].Value = "Diễn giải"; workSheet.Cells[11, 5].Value = "VoucherDesc "; workSheet.Cells[12, 5].Value = "Kiểu chuỗi: Max 250"; workSheet.Cells[10, 6].Value = "Số seri"; workSheet.Cells[11, 6].Value = "SerialNo "; workSheet.Cells[12, 6].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 7].Value = "Số hóa đơn"; workSheet.Cells[11, 7].Value = "RefNo "; workSheet.Cells[12, 7].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 8].Value = "Loại tiền"; workSheet.Cells[11, 8].Value = "CurrencyID"; workSheet.Cells[12, 8].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 9].Value = "Tỷ giá"; workSheet.Cells[11, 9].Value = "ExchangeRate"; workSheet.Cells[12, 9].Value = "Kiểu số"; workSheet.Cells[10, 10].Value = "Loại đối tượng"; workSheet.Cells[11, 10].Value = "ObjectTypeID"; workSheet.Cells[12, 10].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 11].Value = "Đối tượng"; workSheet.Cells[11, 11].Value = "ObjectID"; workSheet.Cells[12, 11].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 12].Value = "Người lập"; workSheet.Cells[11, 12].Value = "EmployeeID"; workSheet.Cells[12, 12].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 13].Value = "Kho hàng"; workSheet.Cells[11, 13].Value = "RWareHouseID"; workSheet.Cells[12, 13].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 14].Value = "Kho xuất(VCNB)"; workSheet.Cells[11, 14].Value = "DWareHouseID"; workSheet.Cells[12, 14].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 15].Value = "Mã hàng"; workSheet.Cells[11, 15].Value = "InventoryID"; workSheet.Cells[12, 15].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 16].Value = "ĐVT"; workSheet.Cells[11, 16].Value = "UnitID"; workSheet.Cells[12, 16].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 17].Value = "TK Nợ"; workSheet.Cells[11, 17].Value = "DebitAccountID"; workSheet.Cells[12, 17].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 18].Value = "TK Có"; workSheet.Cells[11, 18].Value = "CreditAccountID"; workSheet.Cells[12, 18].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 19].Value = "Số lượng"; workSheet.Cells[11, 19].Value = "OQuantity"; workSheet.Cells[12, 19].Value = "Kiểu số"; workSheet.Cells[10, 20].Value = "Số lượng quy đổi"; workSheet.Cells[11, 20].Value = "CQuantity"; workSheet.Cells[12, 20].Value = "Kiểu số"; workSheet.Cells[10, 21].Value = "Đơn giá"; workSheet.Cells[11, 21].Value = "UnitPrice"; workSheet.Cells[12, 21].Value = "Kiểu số"; workSheet.Cells[10, 22].Value = "Thành tiền"; workSheet.Cells[11, 22].Value = "OAmount"; workSheet.Cells[12, 22].Value = "Kiểu số"; workSheet.Cells[10, 23].Value = "Thành tiền quy đổi"; workSheet.Cells[11, 23].Value = "CAmount"; workSheet.Cells[12, 23].Value = "Kiểu số"; workSheet.Cells[10, 24].Value = "Diễn giải chi tiết"; workSheet.Cells[11, 24].Value = "DetailDescription"; workSheet.Cells[12, 24].Value = "Kiểu chuỗi: Max 250"; workSheet.Cells[10, 25].Value = "Lô"; workSheet.Cells[11, 25].Value = "LocationNo"; workSheet.Cells[12, 25].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 26].Value = "Ngày hết hạn"; workSheet.Cells[11, 26].Value = "LimitDate"; workSheet.Cells[12, 26].Value = "Kiểu ngày"; workSheet.Cells[10, 27].Value = "Ngày sản xuất"; workSheet.Cells[11, 27].Value = "ProDate"; workSheet.Cells[12, 27].Value = "Kiểu ngày"; workSheet.Cells[10, 28].Value = "Tập chí"; workSheet.Cells[11, 28].Value = "PeriodID"; workSheet.Cells[12, 28].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 29].Value = "Mã sản phẩm"; workSheet.Cells[11, 29].Value = "ProductID"; workSheet.Cells[12, 29].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 30].Value = "Lệnh sản xuất"; workSheet.Cells[11, 30].Value = "ProOrderNo"; workSheet.Cells[12, 30].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 31].Value = "Khoản mục 1"; workSheet.Cells[11, 31].Value = "Ana01ID"; workSheet.Cells[12, 31].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 32].Value = "Khoản mục 2"; workSheet.Cells[11, 32].Value = "Ana02ID"; workSheet.Cells[12, 32].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 33].Value = "Khoản mục 3"; workSheet.Cells[11, 33].Value = "Ana03ID"; workSheet.Cells[12, 33].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 34].Value = "Khoản mục 4"; workSheet.Cells[11, 34].Value = "Ana04ID"; workSheet.Cells[12, 34].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 35].Value = "Khoản mục 5"; workSheet.Cells[11, 35].Value = "Ana05ID"; workSheet.Cells[12, 35].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 36].Value = "Khoản mục 6"; workSheet.Cells[11, 36].Value = "Ana06ID"; workSheet.Cells[12, 36].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 37].Value = "Khoản mục 7"; workSheet.Cells[11, 37].Value = "Ana07ID"; workSheet.Cells[12, 37].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 38].Value = "Khoản mục 8"; workSheet.Cells[11, 38].Value = "Ana08ID"; workSheet.Cells[12, 38].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 39].Value = "Khoản mục 9"; workSheet.Cells[11, 39].Value = "Ana09ID"; workSheet.Cells[12, 39].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 40].Value = "Khoản mục 10"; workSheet.Cells[11, 40].Value = "Ana10ID"; workSheet.Cells[12, 40].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 41].Value = "Quy cách 1"; workSheet.Cells[11, 41].Value = "Spec01ID"; workSheet.Cells[12, 41].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 42].Value = "Quy cách 2"; workSheet.Cells[11, 42].Value = "Spec02ID"; workSheet.Cells[12, 42].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 43].Value = "Quy cách 3"; workSheet.Cells[11, 43].Value = "Spec03ID"; workSheet.Cells[12, 43].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 44].Value = "Quy cách 4"; workSheet.Cells[11, 44].Value = "Spec04ID"; workSheet.Cells[12, 44].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 45].Value = "Quy cách 5"; workSheet.Cells[11, 45].Value = "Spec05ID"; workSheet.Cells[12, 45].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 46].Value = "Quy cách 6"; workSheet.Cells[11, 46].Value = "Spec06ID"; workSheet.Cells[12, 46].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 47].Value = "Quy cách 7"; workSheet.Cells[11, 47].Value = "Spec07ID"; workSheet.Cells[12, 47].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 48].Value = "Quy cách 8"; workSheet.Cells[11, 48].Value = "Spec08ID"; workSheet.Cells[12, 48].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 49].Value = "Quy cách 9"; workSheet.Cells[11, 49].Value = "Spec09ID"; workSheet.Cells[12, 49].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 50].Value = "Quy cách 10"; workSheet.Cells[11, 50].Value = "Spec10ID"; workSheet.Cells[12, 50].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 51].Value = "Thông tin phụ chuỗi 1"; workSheet.Cells[11, 51].Value = "VRef1"; workSheet.Cells[12, 51].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 52].Value = "Thông tin phụ chuỗi 2"; workSheet.Cells[11, 52].Value = "VRef2"; workSheet.Cells[12, 52].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 53].Value = "Thông tin phụ chuỗi 3"; workSheet.Cells[11, 53].Value = "VRef3"; workSheet.Cells[12, 53].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 54].Value = "Thông tin phụ chuỗi 4"; workSheet.Cells[11, 54].Value = "VRef4"; workSheet.Cells[12, 54].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 55].Value = "Thông tin phụ chuỗi 5"; workSheet.Cells[11, 55].Value = "VRef5"; workSheet.Cells[12, 55].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 56].Value = "Thông tin phụ số 1"; workSheet.Cells[11, 56].Value = "NRef1"; workSheet.Cells[12, 56].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 57].Value = "Thông tin phụ số 2"; workSheet.Cells[11, 57].Value = "NRef2"; workSheet.Cells[12, 57].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 58].Value = "Thông tin phụ số 3"; workSheet.Cells[11, 58].Value = "NRef3"; workSheet.Cells[12, 58].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 59].Value = "Thông tin phụ số 4"; workSheet.Cells[11, 59].Value = "NRef4"; workSheet.Cells[12, 59].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 60].Value = "Thông tin phụ số 5"; workSheet.Cells[11, 60].Value = "NRef5"; workSheet.Cells[12, 60].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 61].Value = "Thông tin phụ ngày 1"; workSheet.Cells[11, 61].Value = "DRef1"; workSheet.Cells[12, 61].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 62].Value = "Thông tin phụ ngày 2"; workSheet.Cells[11, 62].Value = "DRef2"; workSheet.Cells[12, 62].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 63].Value = "Thông tin phụ ngày 3"; workSheet.Cells[11, 63].Value = "DRef3"; workSheet.Cells[12, 63].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 64].Value = "Thông tin phụ ngày 4"; workSheet.Cells[11, 64].Value = "DRef4"; workSheet.Cells[12, 64].Value = "Kiểu chuỗi: Max 20"; workSheet.Cells[10, 65].Value = "Thông tin phụ ngày 5"; workSheet.Cells[11, 65].Value = "DRef5"; workSheet.Cells[12, 65].Value = "Kiểu chuỗi: Max 20"; workSheet.Range(workSheet.Cells[10, 2], workSheet.Cells[11, 65]).Font.Bold = true; workSheet.Range(workSheet.Cells[10, 2], workSheet.Cells[11, 65]).Font.Size = 10; workSheet.Range(workSheet.Cells[12, 2], workSheet.Cells[12, 65]).Font.Italic = true; workSheet.Range(workSheet.Cells[12, 2], workSheet.Cells[12, 65]).Font.Size = 8; workSheet.Range(workSheet.Cells[10, 2], workSheet.Cells[12, 65]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightCyan); workSheet.Range(workSheet.Cells[10, 2], workSheet.Cells[12, 65]).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic); workSheet.Range(workSheet.Cells[10, 2], workSheet.Cells[12, 65]).Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlContinuous; workSheet.Range(workSheet.Cells[10, 2], workSheet.Cells[12, 65]).Borders[XlBordersIndex.xlInsideVertical].Weight = XlBorderWeight.xlThin; workSheet.Range(workSheet.Cells[10, 2], workSheet.Cells[12, 65]).HorizontalAlignment = XlHAlign.xlHAlignCenter; workSheet.Range(workSheet.Cells[10, 2], workSheet.Cells[12, 65]).VerticalAlignment = XlHAlign.xlHAlignCenter; #endregion for (int i = 0; i < listRecordDetails.Count; i++) { var row = i + 13; //VoucherTypeID workSheet.Cells[row, 2].Value = "PXK"; //VoucherNo var day = listRecordDetails[i].MeasurementRecord.RecordDate.Day.ToString().PadLeft(3, '0'); var month = listRecordDetails[i].MeasurementRecord.RecordDate.Month.ToString().PadLeft(2, '0'); var year = listRecordDetails[i].MeasurementRecord.RecordDate.Year.ToString().Substring(2, 2); workSheet.Cells[row, 3].Value = day + "/XKT/" + month + "/" + year; //VourcherDesc workSheet.Cells[row, 5].Value = listRecordDetails[i].MeasurementRecord.Material.TypeID == (int)MaterialTypeEnum.Keo ? "Xuất cán Keo" : "Xuất cán Pat"; //RWareHouse workSheet.Cells[row, 13].Value = "PL"; //InventoryID workSheet.Cells[row, 15].Value = listRecordDetails[i].Material.Code; //Unit workSheet.Cells[row, 16].Value = listRecordDetails[i].WeightUnit; //CQuantity workSheet.Cells[row, 20].Value = listRecordDetails[i].Weight; //LocationNO workSheet.Cells[row, 25].Value = listRecordDetails[i].ParcelCode; //ProductID workSheet.Cells[row, 29].Value = listRecordDetails[i].MeasurementRecord.Material.Code; } var row2 = 13 + listRecordDetails.Count; workSheet.Range(workSheet.Cells[10, 2], workSheet.Cells[row2, 65]).BorderAround(XlLineStyle.xlDouble, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic); workSheet.Range(workSheet.Cells[13, 2], workSheet.Cells[row2, 65]).Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlContinuous; workSheet.Range(workSheet.Cells[13, 2], workSheet.Cells[row2, 65]).Borders[XlBordersIndex.xlInsideVertical].Weight = XlBorderWeight.xlThin; workSheet.Range(workSheet.Cells[13, 2], workSheet.Cells[row2, 65]).Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlContinuous; workSheet.Range(workSheet.Cells[13, 2], workSheet.Cells[row2, 65]).Borders[XlBordersIndex.xlInsideHorizontal].Weight = XlBorderWeight.xlHairline; //auto fit columns workSheet.Columns.AutoFit(); }
public static void Export <T>( this ICollectionView collectionView, Dictionary <string, Model.DataCellFormats> fieldsAndFormats, string reportTitle, string reportDescription, Func <T, string, string, object> getValueDelegate, Action <string> callBack = null) { if (getValueDelegate == null || fieldsAndFormats == null || reportTitle == null) { return; } const string commentFieldName = nameof(Model.Meter.Коментарий); bool hasCommentColumn = fieldsAndFormats.ContainsKey(commentFieldName); IEnumerable <T> collection = null; Application.Current.Dispatcher.Invoke(() => { collection = collectionView.Cast <T>(); }); int numberOfRows = collection.Count(); // +1 т.к. первый столбец номер по порядку int numberOfColumns = fieldsAndFormats.Count + 1; callBack?.Invoke("чтение данных"); object[,] output = BuildDataArray(); object[,] outputWithTwoRowPerRecord = null; if (hasCommentColumn) { outputWithTwoRowPerRecord = BuildDataArrayWithTwoRowPerRecord(); } callBack?.Invoke("поиск MS Excel"); string fileName = System.IO.Path.GetTempFileName(); fileName = System.IO.Path.ChangeExtension(fileName, "xlsx"); System.Globalization.CultureInfo defaultCulture = System.Threading.Thread.CurrentThread.CurrentCulture; // HACK: Workaround for Excel bug on machines which are set up in the English language, but not an English region. System.Globalization.CultureInfo enusCultureInfo = System.Globalization.CultureInfo.GetCultureInfo("en-US"); System.Threading.Thread.CurrentThread.CurrentCulture = enusCultureInfo; Excel.Application excelApplication = null; Excel.Workbook xlWorkbook = null; Excel.Worksheet xlWorksheet = null; Excel.Worksheet xlWorksheet2 = null; NetOffice.OfficeApi.Tools.Contribution.CommonUtils utils = null; Exception exception = null; Process(output, outputWithTwoRowPerRecord); OpenCreatedWorkBook(); // возвращает значение указанного поля записи object GetValueOfField(T item, string fieldName) { object value = string.Empty; if (string.IsNullOrWhiteSpace(fieldsAndFormats[fieldName].ExcelFormat) == false) { value = getValueDelegate(item, string.Empty, fieldName); } else { value = getValueDelegate(item, fieldsAndFormats[fieldName].ContentDisplayFormat, fieldName); } if (value is DateOnly dateOnlyValue) { value = dateOnlyValue.ToDateTime(TimeOnly.MinValue); } return(value); } // создание массива данных object[,] BuildDataArray() { // +1 т.к. первый столбец номер по порядку int countOfColumns = fieldsAndFormats.Count + 1; // +1 т.к. первая строка шапка object[,] output = new object[numberOfRows + 1, countOfColumns]; // for (int i = 0; i < numberOfRows + 1; i++) // output[i] = new object[numberOfColumns]; output[0, 0] = "№ п/п"; int ind = 1; foreach (KeyValuePair <string, Model.DataCellFormats> field in fieldsAndFormats) { output[0, ind++] = Utils.ConvertFromTitleCase(field.Key); } callBack?.Invoke("заполнение таблицы"); int rowIndex = 1; foreach (T item in collection) { output[rowIndex, 0] = rowIndex; ind = 1; // т.к. первый столбец номер по порядку foreach (string field in fieldsAndFormats.Keys) { output[rowIndex, ind++] = GetValueOfField(item, field); } rowIndex++; } return(output); } // создание массива данных со второй строкой в каждой записи где указан комментарий object[,] BuildDataArrayWithTwoRowPerRecord() { // -1 т.к. исключен столбец Комментарий // +1 т.к. первый столбец номер по порядку int countOfColumns = fieldsAndFormats.Count - 1 + 1; int numberOfRecords = collection.Count(); // +1 т.к. первая строка шапка object[,] output = new object[(2 * numberOfRecords) + 1, countOfColumns]; // for (int i = 0; i < numberOfRows + 1; i++) // output[i] = new object[numberOfColumns]; output[0, 0] = "№ п/п"; int ind = 1; foreach (KeyValuePair <string, Model.DataCellFormats> field in fieldsAndFormats) { if (field.Key == commentFieldName) { continue; } else { output[0, ind++] = Utils.ConvertFromTitleCase(field.Key); } } callBack?.Invoke("заполнение таблицы"); int rowIndex = 1; int recordIndex = 1; foreach (T item in collection) { output[rowIndex, 0] = recordIndex; ind = 1; // т.к. первый столбец номер по порядку foreach (string field in fieldsAndFormats.Keys) { if (field == commentFieldName) { continue; } output[rowIndex, ind++] = GetValueOfField(item, field); } string commentValue = GetValueOfField(item, commentFieldName).ToString().Trim().Replace('\n', '\t'); output[rowIndex + 1, 1] = commentValue; rowIndex += 2; recordIndex++; } return(output); } void ApplyDataFormatForSheet(Excel.Worksheet xlWorksheet, Excel.Range rangeToSetData, bool thisWorkSheetIsForPrint) { // -1 т.к. исключен столбец Комментарий // +1 т.к. первый столбец номер по порядку int countOfColumns = hasCommentColumn ? fieldsAndFormats.Count - 1 + 1 : fieldsAndFormats.Count + 1; callBack?.Invoke("установка формата данных"); int rowIndex = 2; // 1 - шапка таблицы int ind = 1; int recordIndex = 1; foreach (T item in collection) { rangeToSetData[rowIndex, 0 + 1].NumberFormat = "0"; ind = 1; // т.к. первый столбец номер по порядку foreach (string field in fieldsAndFormats.Keys) { if (thisWorkSheetIsForPrint && field == commentFieldName) { continue; } try { object value = string.Empty; if (string.IsNullOrWhiteSpace(fieldsAndFormats[field].ExcelFormat) == false) { string format = string.IsNullOrWhiteSpace(fieldsAndFormats[field].ExcelFormat) ? "General" : fieldsAndFormats[field].ExcelFormat; rangeToSetData[rowIndex, 0 + 1 + ind].NumberFormat = format; } else if (string.IsNullOrWhiteSpace(fieldsAndFormats[field].ContentDisplayFormat) == false) { rangeToSetData[rowIndex, 0 + 1 + ind].NumberFormat = "General"; } } catch (Exception e) { #if DEBUG App.ToDebug(e); #endif } finally { ind++; } } if (thisWorkSheetIsForPrint && hasCommentColumn) { // ячейка с номером по порядку Excel.Range rng = rangeToSetData[rowIndex, 1]; rng = rng.Resize(2, 1); rng.Merge(); rng.HorizontalAlignment = XlHAlign.xlHAlignLeft; rng.VerticalAlignment = XlVAlign.xlVAlignCenter; // ячейка с комментарием rng = rangeToSetData[rowIndex + 1, 2].Resize(1, countOfColumns - 1); rng.Merge(); rng.WrapText = true; rng.NumberFormat = "@"; rng = rangeToSetData[rowIndex, 1].Resize(2, countOfColumns); rng.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic); rng.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDot; rng.Borders[XlBordersIndex.xlInsideHorizontal].Weight = XlBorderWeight.xlThin; rng.Borders[XlBordersIndex.xlInsideHorizontal].Color = utils.Color.ToDouble(System.Drawing.Color.Gray); rng.Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlDot; rng.Borders[XlBordersIndex.xlInsideVertical].Weight = XlBorderWeight.xlThin; rng.Borders[XlBordersIndex.xlInsideVertical].Color = utils.Color.ToDouble(System.Drawing.Color.Gray); if (recordIndex % 2 == 0) { rng = rangeToSetData[rowIndex, 1].Resize(2, countOfColumns); rng.Interior.Color = utils.Color.ToDouble(System.Drawing.Color.WhiteSmoke); } rowIndex += 2; } else { rowIndex++; } recordIndex++; } } void Process(object[,] outputData1, object[,] outputData2) { try { callBack?.Invoke("создание книги MS Excel"); excelApplication = new Excel.Application { DisplayAlerts = false, ScreenUpdating = false, }; utils = new NetOffice.OfficeApi.Tools.Contribution.CommonUtils(excelApplication); xlWorkbook = excelApplication.Workbooks.Add(); xlWorksheet = (Excel.Worksheet)xlWorkbook.Sheets[1]; Excel.Range all = xlWorksheet.Range("A1"); Excel.Range header = xlWorksheet.Range("A1"); header.WrapText = true; header.Resize(1, numberOfColumns).Merge(); using (Excel.Font font = header.Font) { font.Size = 14; font.Bold = true; } header.HorizontalAlignment = XlHAlign.xlHAlignCenter; header.VerticalAlignment = XlVAlign.xlVAlignCenter; double oneRowHeight = (double)header.RowHeight; int rowsCount = reportTitle.Split(new[] { Environment.NewLine }, StringSplitOptions.None).Length; header.RowHeight = oneRowHeight * rowsCount * 1.1; header.Value2 = reportTitle; Excel.Range description = xlWorksheet.Range("A2"); description.Resize(1, numberOfColumns).Merge(); description.WrapText = true; using (Excel.Font font = description.Font) { font.Size = 12; font.Italic = true; } description.HorizontalAlignment = XlHAlign.xlHAlignLeft; description.VerticalAlignment = XlVAlign.xlVAlignCenter; oneRowHeight = (double)description.RowHeight; rowsCount = reportDescription.Split(new[] { Environment.NewLine }, StringSplitOptions.None).Length; description.RowHeight = oneRowHeight * rowsCount * 1.1; description.Value2 = reportDescription; callBack?.Invoke("настройка книги MS Excel"); if (hasCommentColumn) { // создание копии листа xlWorksheet.Copy(xlWorksheet); xlWorksheet = (Excel.Worksheet)xlWorkbook.Sheets[1]; xlWorksheet2 = (Excel.Worksheet)xlWorkbook.Sheets[2]; xlWorksheet.Name = "Данные"; xlWorksheet2.Name = "Для печати"; Excel.Range data2 = xlWorksheet2.Range("A3").Resize((2 * numberOfRows) + 1, numberOfColumns - 1); data2.VerticalAlignment = VerticalAlignment.Center; data2.NumberFormat = "@"; data2.Value = outputData2; ApplyDataFormatForSheet(xlWorksheet2, data2, true); Excel.Range tableHeader = xlWorksheet2.Range("A3").Resize(1, numberOfColumns - 1); tableHeader.WrapText = true; using (Excel.Font font = tableHeader.Font) { font.Size = 12; font.Bold = true; } header.HorizontalAlignment = XlHAlign.xlHAlignCenter; header.VerticalAlignment = XlVAlign.xlVAlignCenter; tableHeader.Interior.Color = utils.Color.ToDouble(System.Drawing.Color.LightGray); tableHeader.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic); tableHeader.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDot; tableHeader.Borders[XlBordersIndex.xlInsideHorizontal].Weight = XlBorderWeight.xlThin; tableHeader.Borders[XlBordersIndex.xlInsideHorizontal].Color = utils.Color.ToDouble(System.Drawing.Color.Black); } Excel.Range data = xlWorksheet.Range("A3").Resize(numberOfRows + 1, numberOfColumns); data.NumberFormat = "@"; data.Value = outputData1; ApplyDataFormatForSheet(xlWorksheet, data, false); xlWorksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange, data, Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "DataTable"; xlWorksheet.ListObjects["DataTable"].TableStyle = "TableStyleMedium6"; SetupWorkSheet(xlWorksheet, numberOfColumns); if (hasCommentColumn) { SetupWorkSheet(xlWorksheet2, numberOfColumns - 1); } callBack?.Invoke("сохранение книги MS Excel"); xlWorkbook.SaveAs(fileName); xlWorkbook.Close(false); callBack?.Invoke("завершение"); logger?.Info($"Export >> файл сформирован и сохранен: '{fileName}'"); } catch (Exception e) { exception = e; #if DEBUG App.ToDebug(e); #endif App.ShowError("Произошла ошибка:\n" + App.GetExceptionDetails(e)); return; } finally { System.Threading.Thread.CurrentThread.CurrentCulture = defaultCulture; excelApplication.Quit(); excelApplication.ScreenUpdating = true; excelApplication.DisplayAlerts = true; if (exception != null) { if (excelApplication.Workbooks.Any()) { foreach (Excel.Workbook workbook in excelApplication.Workbooks.Where(x => !x.IsDisposed)) { workbook.Close(false, System.Reflection.Missing.Value, Missing.Value); workbook.Dispose(); } } if (excelApplication.IsDisposed == false) { excelApplication.Quit(); excelApplication.Dispose(); } } } } void SetupWorkSheet(Excel.Worksheet xlWorksheet, int numberOfColumns) { foreach (int i in Enumerable.Range(1, numberOfColumns)) { xlWorksheet.Columns[i].AutoFit(); } Excel.PageSetup ps = xlWorksheet.PageSetup; ps.PaperSize = XlPaperSize.xlPaperA4; ps.Orientation = XlPageOrientation.xlLandscape; ps.Zoom = false; ps.FitToPagesWide = 1; ps.FitToPagesTall = false; ps.PrintTitleRows = "$3:$3"; ps.LeftMargin = excelApplication.CentimetersToPoints(1.0); ps.RightMargin = excelApplication.CentimetersToPoints(1.0); ps.TopMargin = excelApplication.CentimetersToPoints(2.0); ps.BottomMargin = excelApplication.CentimetersToPoints(1.0); ps.HeaderMargin = excelApplication.CentimetersToPoints(0.6); ps.FooterMargin = excelApplication.CentimetersToPoints(0.6); ps.CenterHorizontally = true; ps.RightHeader = DateTime.Now.ToString("D", defaultCulture); ps.CenterFooter = "Страница &P / &N"; ps.PrintArea = xlWorksheet.Range("A1").Resize(numberOfRows + 3, numberOfColumns).Address; } void OpenCreatedWorkBook() { try { callBack?.Invoke("открытие созданной книги MS Excel"); using System.Diagnostics.Process p = new System.Diagnostics.Process { StartInfo = new System.Diagnostics.ProcessStartInfo(fileName) { UseShellExecute = true, }, }; p.Start(); // System.Diagnostics.Process.Start(fileName); } catch (Exception e) { #if DEBUG App.ToDebug(e); #endif App.ShowError("Произошла ошибка при открытии файла:\n" + App.GetExceptionDetails(e)); } } }