private void ProceedProductMonthCharts(XlWorksheet productSheet, SalesReportProduct itemProduct) { double chartTop = productSheet.Rows[15].Top; double chartWidth = productSheet.Columns[14].Left; double chartHeight = productSheet.Rows[30].Top - productSheet.Rows[15].Top; XlChartObject chartMonths = productSheet.ChartObjects.Add(1, chartTop, chartWidth, chartHeight); chartMonths.Chart.SetSourceData(productSheet.Range("$A9:$M12")); chartTop = productSheet.Rows[31].Top; chartWidth = productSheet.Columns[14].Left; chartHeight = productSheet.Rows[40].Top - productSheet.Rows[33].Top; XlChartObject chartCountMonths = productSheet.ChartObjects.Add(1, chartTop, chartWidth, chartHeight); chartCountMonths.Chart.ChartType = XlChartType.xlLine; chartCountMonths.Chart.SetSourceData(productSheet.Range("$A13:$M13")); double chartLeft = productSheet.Columns[15].Left; chartTop = productSheet.Rows[15].Top; chartWidth = productSheet.Columns[19].Left - productSheet.Columns[15].Left; chartHeight = productSheet.Rows[30].Top - productSheet.Rows[15].Top; XlChartObject chartCountYears = productSheet.ChartObjects.Add(chartLeft, chartTop, chartWidth, chartHeight); chartCountYears.Chart.ChartType = XlChartType.xlCylinderColClustered; chartCountYears.Chart.SetSourceData(productSheet.Range("$O9:$P12")); }
private void button1_Click(object sender, EventArgs e) { // start excel and turn off msg boxes excelApplication = new XlApplication(); excelApplication.DisplayAlerts = false; // add a new workbook XlWorkbook workBook = excelApplication.Workbooks.Add(); XlWorksheet workSheet = workBook.Worksheets[1]; // we need some data to display XlRange dataRange = PutSampleData(workSheet); // create a nice diagram XlChartObject chart = workSheet.ChartObjects.Add(70, 100, 375, 225); chart.Chart.SetSourceData(dataRange); // save the book string fileExtension = XlConverter.GetDefaultExtension(excelApplication); string workbookFile = string.Format("{0}\\Example5{1}", Environment.CurrentDirectory, fileExtension); workBook.SaveAs(workbookFile); // close excel and dispose reference excelApplication.Quit(); excelApplication.Dispose(); FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile); fDialog.ShowDialog(this); }
private XlRange PutSampleData(XlWorksheet 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 = "Column1"; 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")); }
private static void WriteWorkbook(string fName) { Console.WriteLine("Creating workbook named [{0}]", fName); XlWorkbook book = new XlWorkbook(); XlWorksheets sheets = book.Worksheets; XlSchema schema = GetDeploymentSchema(); XlWorksheet sheet = sheets.AddWorksheet("DET_Deployments", XlColor.White, schema); GetDeploymentRows(sheet); schema = GetMeasurementSchema(); sheet = sheets.AddWorksheet("DET_Measurements", XlColor.White, schema); GetMeasurementRows(sheet); schema = GetSiteSchema(); sheet = sheets.AddWorksheet("LIST_Sites", XlColor.Orange, schema); GetSiteRows(sheet); schema = GetSensorSchema(); sheet = sheets.AddWorksheet("LIST_Sensors", XlColor.Orange, schema); GetSensorRows(sheet); XlCustomProperties properties = book.Properties; properties.AddCustomProperty("oncorId", Guid.NewGuid().ToString()); properties.AddCustomProperty("oncorUser", "Dr. Frank N. Furter, ESQ"); book.Save(fName); Console.WriteLine("Closing workbook named [{0}]", fName); }
private static XlRows GetSensorRows(XlWorksheet sheet) { XlRows rows = sheet.Rows; AddDummyRows(rows, sheet.Schema); return(rows); }
private void ProceedSummaryPrintSettings(XlWorksheet summarySheet) { summarySheet.PageSetup.Orientation = XlPageOrientation.xlLandscape; summarySheet.PageSetup.Zoom = false; summarySheet.PageSetup.FitToPagesTall = 1; summarySheet.PageSetup.FitToPagesWide = 1; }
private static void GenerateMergeCells(XlWorksheet ws) { // объединение ячеек для заголовка ws.AppendMergeCell( new String[] { "A1:C1", "A2:C2", "A3:C3" } ); }
private static XlRows GetMeasurementRows(XlWorksheet sheet) { XlRows rows = sheet.Rows; AddDummyRows(rows, sheet.Schema); return(rows); }
private void CheckHeaders(XlWorksheet worksheet) { if (worksheet != null) { Schema s; List <string> h; if (worksheet.Name == "DET_Deployments") { s = DeploymentDTO.GetSchema(); } else if (worksheet.Name == "DET_Measurements") { s = MeasurementDTO.GetSchema(); } else if (worksheet.Name == "LIST_Sites") { s = SiteDTO.GetSchema(); } else if (worksheet.Name == "LIST_Sensors") { s = SensorDTO.GetSchema(); } else { return; } h = new List <string>(); List <string> hdrs = ExcelBaseDet.Headers(worksheet); bool bad = false; for (int i = 0; i < hdrs.Count - 1; i++) { string t = hdrs[i]; for (int j = i + 1; j < hdrs.Count; j++) { if (t == hdrs[j]) { bad = true; break; //inner } } if (bad) { break; //outer } } if (bad) { generalDet.ValidationIssues.Add(ValidationIssue.Code.DuplicateHeader, "Duplicate column header in " + worksheet.Name); } foreach (SchemaEntry c in s) { h.Add(c.LowerColumnName); } if (!ExcelBaseDet.HasHeaders(hdrs, h)) { generalDet.ValidationIssues.Add(ValidationIssue.Code.MissingFieldHeader, "Missing column header in " + worksheet.Name); } } }
private static void AppendValueToSpreadsheetCell(XlWorksheet ws, UInt32 rowIndex, UInt32 columnIndex, Object value, Type type) { String cellValueText = null; if (value != null && value != DBNull.Value) { switch (type.ToString()) { case "System.Decimal": ws.UpsertCell(rowIndex, columnIndex, 3, value); break; case "System.Double": ws.UpsertCell(rowIndex, columnIndex, 3, value); break; case "System.Int32": ws.UpsertCell(rowIndex, columnIndex, 4, value); break; default: if (value != null) { cellValueText = value.ToString(); } ws.UpsertCell(rowIndex, columnIndex, 2, cellValueText); break; } } else { ws.UpsertCell(rowIndex, columnIndex, 2, cellValueText); } }
private void ProceedProductPrintSettings(XlWorksheet productSheet) { productSheet.PageSetup.Orientation = XlPageOrientation.xlLandscape; productSheet.PageSetup.Zoom = false; productSheet.PageSetup.FitToPagesTall = 1; productSheet.PageSetup.FitToPagesWide = 1; productSheet.PageSetup.PrintArea = "$A$1:$R$39"; }
private void ProceedSummaryMatrix(SalesReport report, XlWorksheet summarySheet, XlStyle matrixStyle) { // table columns summarySheet.Range("B2").Value = "Count"; summarySheet.Range("C2").Value = "Revenue"; summarySheet.Range("D2").Value = "%"; summarySheet.Range("E2").Value = "Storage"; string leftBottomCellAdress = XlConverter.ToCellAdress(1, 3 + report.Products.Length); string rightBottomCellAdress = XlConverter.ToCellAdress(5, 3 + report.Products.Length); summarySheet.Range("$A2:$" + rightBottomCellAdress).Style = matrixStyle; int rowIndex = 3; int columnIndex = 1; int i = 0; foreach (SalesReportProduct itemProduct in report.Products) { string prodName = itemProduct.ProductName; int prodId = itemProduct.ProductId; summarySheet.Cells(rowIndex, columnIndex).Value = prodName; string formula = string.Format("='{0}-{1}'!{2}", itemProduct.ProductName, itemProduct.ProductId, XlConverter.ToCellAdress(_monthToReport + 1, 13)); summarySheet.Cells(rowIndex, columnIndex + 1).Value = formula; formula = string.Format("='{0}-{1}'!{2}", itemProduct.ProductName, itemProduct.ProductId, XlConverter.ToCellAdress(_monthToReport + 1, 12)); summarySheet.Cells(rowIndex, columnIndex + 2).Value = formula; formula = string.Format("={0}*100/{1}", XlConverter.ToCellAdress(3, rowIndex), XlConverter.ToCellAdress(3, 3 + report.Products.Length)); summarySheet.Cells(rowIndex, columnIndex + 3).Formula = formula; formula = string.Format("='{0}-{1}'!{2}", itemProduct.ProductName, itemProduct.ProductId, "B6"); summarySheet.Cells(rowIndex, columnIndex + 4).Value = formula; int storeCount = Convert.ToInt16(summarySheet.Cells(rowIndex, columnIndex + 4).Value); if ((i % 2) == 0) { summarySheet.Range("$A" + (i + 3).ToString() + ":$E" + (i + 3).ToString()).Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro); } rowIndex++; i++; } string sumFormula = string.Format("=Sum({0}:{1})", "C3", "C" + (report.Products.Length + 3 - 1).ToString()); summarySheet.Cells(rowIndex, columnIndex + 2).Value = sumFormula; summarySheet.Range("$C3:$C" + (report.Products.Length + 3).ToString()).NumberFormat = "#,##0.00 €"; summarySheet.Range("$D3:$D" + (report.Products.Length + 3).ToString()).NumberFormat = "0\"%\""; summarySheet.Cells(3 + report.Products.Length, 1).Value = "Total:"; summarySheet.Range("D2").HorizontalAlignment = XlHAlign.xlHAlignCenter; summarySheet.Range("$B2:$E2").Font.Bold = true; summarySheet.Range(leftBottomCellAdress + ":" + rightBottomCellAdress).Font.Bold = true; summarySheet.Range(leftBottomCellAdress + ":" + rightBottomCellAdress).BorderAround(XlLineStyle.xlDouble, XlBorderWeight.xlMedium); }
private void button1_Click(object sender, EventArgs e) { // start excel and turn off msg boxes excelApplication = new XlApplication(); excelApplication.DisplayAlerts = false; // add a new workbook XlWorkbook workBook = excelApplication.Workbooks.Add(); XlWorksheet workSheet = workBook.Worksheets[1]; /*do background color for cells*/ string listSeperator = System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator; // draw the face string rangeAdressFace = string.Format("$C10:$M10{0}$C30:$M30{0}$C11:$C30{0}$M11:$M30", listSeperator); workSheet.Range(rangeAdressFace).Interior.Color = XlConverter.ToDouble(Color.DarkGreen); string rangeAdressEyes = string.Format("$F14{0}$J14", listSeperator); workSheet.Range(rangeAdressEyes).Interior.Color = XlConverter.ToDouble(Color.Black); string rangeAdressNoise = string.Format("$G18:$I19", listSeperator); workSheet.Range(rangeAdressNoise).Interior.Color = XlConverter.ToDouble(Color.DarkGreen); string rangeAdressMouth = string.Format("$F26{0}$J26{0}$G27:$I27", listSeperator); workSheet.Range(rangeAdressMouth).Interior.Color = XlConverter.ToDouble(Color.DarkGreen); /*do borderlines for cells*/ // border the face with the border arround method workSheet.Range(rangeAdressFace).BorderAround(LateBindingApi.Excel.Enums.XlLineStyle.xlDashDot, LateBindingApi.Excel.Enums.XlBorderWeight.xlThin, Color.BlueViolet.ToArgb()); workSheet.Range(rangeAdressEyes).BorderAround(LateBindingApi.Excel.Enums.XlLineStyle.xlDashDot, LateBindingApi.Excel.Enums.XlBorderWeight.xlThin, Color.BlueViolet.ToArgb()); workSheet.Range(rangeAdressNoise).BorderAround(LateBindingApi.Excel.Enums.XlLineStyle.xlDouble, LateBindingApi.Excel.Enums.XlBorderWeight.xlThin, Color.BlueViolet.ToArgb()); // border explicitly workSheet.Range(rangeAdressMouth).Borders[LateBindingApi.Excel.Enums.XlBordersIndex.xlEdgeBottom].LineStyle = LateBindingApi.Excel.Enums.XlLineStyle.xlDouble; workSheet.Range(rangeAdressMouth).Borders[LateBindingApi.Excel.Enums.XlBordersIndex.xlEdgeBottom].Weight = 4; workSheet.Range(rangeAdressMouth).Borders[LateBindingApi.Excel.Enums.XlBordersIndex.xlEdgeBottom].Color = 400; // save the book string fileExtension = XlConverter.GetDefaultExtension(excelApplication); string workbookFile = string.Format("{0}\\Example1{1}", Environment.CurrentDirectory, fileExtension); workBook.SaveAs(workbookFile); // close excel and dispose reference excelApplication.Quit(); excelApplication.Dispose(); FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile); fDialog.ShowDialog(this); }
static void Main(string[] args) { Console.WriteLine("XlLateBinding Performance Test - 5000 Cells."); /* * start excel and disable messageboxes and screen updating */ XlApplication excelApplication = new XlApplication(); excelApplication.DisplayAlerts = false; excelApplication.ScreenUpdating = false; /* * create new empty worksheet */ excelApplication.Workbooks.Add(); XlWorksheet sheet = excelApplication.Workbooks[1].Worksheets[1]; /* * do the test */ DateTime timeStart = DateTime.Now; for (int i = 1; i <= 5000; i++) { // cells property for a sheet in OfficeFor.Net are not supported // the reason for all examples use range string rangeAdress = "$A" + i.ToString(); XlRange cellRange = sheet.Range(rangeAdress); cellRange.Value = "value"; cellRange.Font.Name = "Verdana"; cellRange.NumberFormat = "@"; cellRange.BorderAround(XlLineStyle.xlDouble, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black); } DateTime timeEnd = DateTime.Now; TimeSpan timeElapsed = timeEnd - timeStart; /* * display for user */ string outputConsole = string.Format("Time Elapsed: {0}{1}Press any Key.", timeElapsed, Environment.NewLine); Console.WriteLine(outputConsole); Console.Read(); /* * write result in logfile */ string logFile = Path.Combine(Environment.CurrentDirectory, "LateBinding.log"); string logFileAppend = timeElapsed.ToString() + Environment.NewLine; File.AppendAllText(logFile, logFileAppend, Encoding.UTF8); excelApplication.Quit(); excelApplication.Dispose(); }
static void Main(string[] args) { Console.WriteLine("XlLateBinding Performance Test - 10.000 Cells."); /* * start excel and disable messageboxes and screen updating */ XlApplication excelApplication = new XlApplication(); excelApplication.DisplayAlerts = false; excelApplication.ScreenUpdating = false; /* * create new empty worksheet */ excelApplication.Workbooks.Add(); XlWorksheet sheet = excelApplication.Workbooks[1].Worksheets[1]; /* * do the test */ DateTime timeStart = DateTime.Now; for (int i = 1; i <= 10000; i++) { string rangeAdress = "$A" + i.ToString(); XlRange cellRange = sheet.Range(rangeAdress); cellRange.Value = "value"; cellRange.Font.Name = "Verdana"; cellRange.NumberFormat = "@"; cellRange.WrapText = false; XlComment sampleComment = cellRange.AddComment("Sample Comment"); } DateTime timeEnd = DateTime.Now; TimeSpan timeElapsed = timeEnd - timeStart; /* * display for user */ string outputConsole = string.Format("Time Elapsed: {0}{1}Press any Key.", timeElapsed, Environment.NewLine); Console.WriteLine(outputConsole); Console.Read(); /* * write result in logfile */ string logFile = Path.Combine(Environment.CurrentDirectory, "LateBinding.log"); string logFileAppend = timeElapsed.ToString() + Environment.NewLine; File.AppendAllText(logFile, logFileAppend, Encoding.UTF8); excelApplication.Quit(); excelApplication.Dispose(); }
private void button1_Click(object sender, EventArgs e) { // start excel and turn off msg boxes _excelApplication = new XlApplication(); _excelApplication.DisplayAlerts = false; _excelApplication.ScreenUpdating = false; // add a new workbook XlWorkbook workBook = _excelApplication.Workbooks.Add(); // we use the first sheet as summary sheet and remove the 2 last sheets XlWorksheet summarySheet = workBook.Worksheets[1]; workBook.Worksheets[3].Delete(); workBook.Worksheets[2].Delete(); // we get the data & perform the report _report = new SalesReport(_yearToReport, _monthToReport); _report.Proceed(); // we create named styles for the range.Style property CreateStorageAndRankingStyle(workBook, "StorageAndRanking"); CreateMonthStyle(workBook, "MonthInfos"); CreateMonthStyle(workBook, "YearTotalInfos"); // write product sheets XlWorksheet productSheet = null; foreach (SalesReportProduct itemProduct in _report.Products) { productSheet = workBook.Worksheets.Add(); ProceedProductWorksheet(productSheet, itemProduct); productSheet.Move(null, workBook.Worksheets[workBook.Worksheets.Count]); } // write summary sheet ProceedSummaryWorksheet(_report, workBook, summarySheet, productSheet); summarySheet.Range("$A2").Select(); // save the book string fileExtension = XlConverter.GetDefaultExtension(_excelApplication); string workbookFile = string.Format("{0}\\Example10{1}", Environment.CurrentDirectory, fileExtension); workBook.SaveAs(workbookFile); // close excel and dispose reference _excelApplication.Quit(); _excelApplication.Dispose(); FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile); fDialog.ShowDialog(this); }
internal static List <string> Headers(XlWorksheet worksheet) { List <string> headers = new List <string>(); IXlRowData row = worksheet.Rows[0]; foreach (IXlCell o in row) { headers.Add(o.CellValue); } return(headers); }
private void ProceedProductMonthInfo(XlWorksheet productSheet, SalesReportProduct itemProduct) { int rowIndex = 9; int iMonthCellIndex = 1; productSheet.Range("$A9:$M13").StyleAsString = "MonthInfos"; productSheet.Cells(rowIndex + 1, iMonthCellIndex).Value = "ManufacturerPriceSummary"; productSheet.Cells(rowIndex + 2, iMonthCellIndex).Value = "SalesPricesSummary"; productSheet.Cells(rowIndex + 3, iMonthCellIndex).Value = "TotalRevenue"; productSheet.Cells(rowIndex + 4, iMonthCellIndex).Value = "CountOfSales"; iMonthCellIndex = 2;; foreach (SalesReportReportEntity itemMonth in itemProduct.PrevMonths) { productSheet.Cells(rowIndex, iMonthCellIndex).Value = GetMonthName(iMonthCellIndex - 2); productSheet.Cells(rowIndex + 1, iMonthCellIndex).Value = itemMonth.ManufactorPriceSummary; productSheet.Cells(rowIndex + 2, iMonthCellIndex).Value = itemMonth.SalesPricesSummary; productSheet.Cells(rowIndex + 3, iMonthCellIndex).Value = itemMonth.OutcomeSummary; productSheet.Cells(rowIndex + 4, iMonthCellIndex).Value = itemMonth.CountOfSales; iMonthCellIndex++; } string cellAdress1 = XlConverter.ToCellAdress(itemProduct.PrevMonths.Count + 2, 10); string cellAdress2 = XlConverter.ToCellAdress(itemProduct.PrevMonths.Count + 2, 12); productSheet.Range("$B10:$" + cellAdress1).Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro); productSheet.Range("$B12:$" + cellAdress2).Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro); productSheet.Cells(rowIndex, iMonthCellIndex).Value = GetMonthName(_monthToReport - 1); productSheet.Cells(rowIndex + 1, iMonthCellIndex).Value = itemProduct.Month.ManufactorPriceSummary; productSheet.Cells(rowIndex + 2, iMonthCellIndex).Value = itemProduct.Month.SalesPricesSummary; productSheet.Cells(rowIndex + 3, iMonthCellIndex).Value = itemProduct.Month.OutcomeSummary; productSheet.Cells(rowIndex + 4, iMonthCellIndex).Value = itemProduct.Month.CountOfSales; for (int i = itemProduct.PrevMonths.Count + 2; i <= 12; i++) { iMonthCellIndex++; productSheet.Cells(rowIndex, iMonthCellIndex).Value = GetMonthName(i - 1); } productSheet.Range("$B9:$M9").NumberFormat = ""; productSheet.Range("$B9:$M9").Font.Bold = true; productSheet.Range("$B13:$M13").NumberFormat = ""; productSheet.Range("$B13:$M13").HorizontalAlignment = XlHAlign.xlHAlignCenter; if (itemProduct.PrevMonths.Count < 11) { string topLeftMergeCellAdress = "$" + XlConverter.ToCellAdress(itemProduct.PrevMonths.Count + 3, 10); productSheet.Range(topLeftMergeCellAdress + ":$M13").MergeCells = true; } }
private void AddRows(XlWorkbook book, DataTab data) { XlWorksheets sheets = book.Worksheets; List <BaseDTO> list = new List <BaseDTO>(data); XlWorksheet sheet = sheets.AddWorksheet(data.Name, data.Color, data.Schema); int numberRows = list.Count; for (int rowIndex = 0; rowIndex < numberRows; rowIndex++) { Dictionary <string, string> values = list[rowIndex].Values(); sheet.AddRow(rowIndex, values); } }
private void ProceedProductWorksheetHeader(XlWorksheet productSheet, SalesReportProduct itemProduct) { int rowIndex = 1; int columnIndex = 1; productSheet.PageSetup.LeftHeader = "&D created"; productSheet.PageSetup.CenterHeader = "Vintage Digital Inc."; productSheet.PageSetup.RightHeader = string.Format("Monthly Sales Report {1:00}/{0}", _yearToReport, _monthToReport); productSheet.Cells(rowIndex, columnIndex).Value = itemProduct.ProductName; productSheet.Cells(rowIndex, columnIndex).Font.Bold = true; productSheet.Cells(rowIndex, columnIndex).Font.Underline = true; productSheet.Cells(rowIndex, columnIndex).Font.Size = 14; productSheet.Cells(rowIndex, columnIndex).Font.Name = "Verdana"; }
private static void GenerateBackgroundColor(XlWorksheet ws, DataTable t) { for (int ri = 0; ri < t.Rows.Count; ri++) { DataRow dr = t.Rows[ri]; String bgColor = dr["bg_color"] as String; if (!String.IsNullOrWhiteSpace(bgColor)) { int ci = 0; foreach (Md.TableColumn col in Md.Table1Columns) { ws.SetCellBackgroundColor((ri + 1), ci, bgColor); // !!! 1 - количество строк в шапке ci++; } } } }
private void LoadRows(XlWorksheet worksheet, LoadRow loadRow) { for (int index = 1; index < worksheet.Rows.Count; index++) { var values = worksheet.ValueRow(index); if (values.Count > 0) { // Skip empty rows foreach (String cur in values.Values) { if (!string.IsNullOrEmpty(cur)) { loadRow(worksheet.Name, values); //we got at least one non-empty cell break; } } } } }
private void ProceedProductWorksheet(XlWorksheet productSheet, SalesReportProduct itemProduct) { string sheetName = string.Format("{0}-{1}", itemProduct.ProductName, itemProduct.ProductId.ToString()); productSheet.Name = sheetName; // its not a random chain, write data first and create charts second ProceedProductStorageInfo(productSheet, itemProduct); ProceedProductMonthInfo(productSheet, itemProduct); ProceedProductYearTotalInfo(productSheet, itemProduct); ProceedProductMonthCharts(productSheet, itemProduct); ProceedProductPrintSettings(productSheet); productSheet.Columns.AutoFit(); // proceed AutoFit before header & ranking ProceedProductWorksheetHeader(productSheet, itemProduct); ProceedProductRankingInfo(productSheet, itemProduct); productSheet.Columns[14].ColumnWidth = 2.14; productSheet.Columns[17].ColumnWidth = 5.14; }
private static void GenerateColumns(XlWorksheet ws, Md.TableColumn[] cols) { uint cn = 1; for (int ci = 0; ci < cols.Length; ci++) { Md.TableColumn col = cols[ci]; if (col.Width == null) { ws.AppendColumn(cn, cn, true); } else { Double.TryParse(col.Width, out Double width); ws.AppendColumn(cn, cn, false, true, width); } cn++; } }
private void ProceedProductRankingInfo(XlWorksheet productSheet, SalesReportProduct itemProduct) { int rowIndex = 3; int columnIndex = 4; productSheet.Range("$D3:$F6").StyleAsString = "StorageAndRanking"; productSheet.Cells(rowIndex, columnIndex).Value = "Count Ranking"; productSheet.Cells(rowIndex, columnIndex).Font.Bold = true; productSheet.Cells(rowIndex + 1, columnIndex).Value = "Month"; productSheet.Cells(rowIndex + 2, columnIndex).Value = "Year"; productSheet.Cells(rowIndex + 3, columnIndex).Value = "Total"; productSheet.Cells(rowIndex + 1, columnIndex + 2).Value = itemProduct.SalesRankMonth; productSheet.Cells(rowIndex + 2, columnIndex + 2).Value = itemProduct.SalesRankYear; productSheet.Cells(rowIndex + 3, columnIndex + 2).Value = itemProduct.SalesRankTotal; productSheet.Range("$D4:$E6").Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlLineStyleNone; }
private void ProceedProductYearTotalInfo(XlWorksheet productSheet, SalesReportProduct itemProduct) { int ColumnIndex = 15; int RowIndex = 9; productSheet.Range("$O9:$R13").StyleAsString = "YearTotalInfos"; productSheet.Cells(RowIndex, ColumnIndex).Value = "Year " + _yearToReport.ToString(); productSheet.Cells(RowIndex + 1, ColumnIndex).Value = itemProduct.Year.ManufactorPriceSummary; productSheet.Cells(RowIndex + 2, ColumnIndex).Value = itemProduct.Year.SalesPricesSummary; productSheet.Cells(RowIndex + 3, ColumnIndex).Value = itemProduct.Year.OutcomeSummary; productSheet.Cells(RowIndex + 4, ColumnIndex).Value = itemProduct.Year.CountOfSales; productSheet.Cells(RowIndex, ColumnIndex + 1).Value = "Year " + (_yearToReport - 1).ToString(); productSheet.Cells(RowIndex + 1, ColumnIndex + 1).Value = itemProduct.PrevYear.ManufactorPriceSummary; productSheet.Cells(RowIndex + 2, ColumnIndex + 1).Value = itemProduct.PrevYear.SalesPricesSummary; productSheet.Cells(RowIndex + 3, ColumnIndex + 1).Value = itemProduct.PrevYear.OutcomeSummary; productSheet.Cells(RowIndex + 4, ColumnIndex + 1).Value = itemProduct.PrevYear.CountOfSales; productSheet.Range("$O10:$P10").Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro); productSheet.Range("$O12:$P12").Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro); ColumnIndex = 18; RowIndex = 9; productSheet.Cells(RowIndex, ColumnIndex).Value = "Total"; productSheet.Cells(RowIndex + 1, ColumnIndex).Value = itemProduct.Total.ManufactorPriceSummary; productSheet.Cells(RowIndex + 2, ColumnIndex).Value = itemProduct.Total.SalesPricesSummary; productSheet.Cells(RowIndex + 3, ColumnIndex).Value = itemProduct.Total.OutcomeSummary; productSheet.Cells(RowIndex + 4, ColumnIndex).Value = itemProduct.Total.CountOfSales; productSheet.Range("$R10").Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro); productSheet.Range("$R12").Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro); productSheet.Range("$O9:$R9").NumberFormat = ""; productSheet.Range("$O9:$R9").Font.Bold = true; productSheet.Range("$O13:$R13").NumberFormat = ""; productSheet.Range("$O13:$R13").HorizontalAlignment = XlHAlign.xlHAlignCenter; productSheet.Range("$Q9:$Q13").MergeCells = true; }
private void ProceedProductStorageInfo(XlWorksheet productSheet, SalesReportProduct itemProduct) { int rowIndex = 3; int columnIndex = 1; productSheet.Range("$A3:$B6").StyleAsString = "StorageAndRanking"; productSheet.Cells(rowIndex, columnIndex).Value = "Storage Info"; productSheet.Cells(rowIndex, columnIndex).Font.Bold = true; productSheet.Cells(rowIndex + 1, columnIndex).Value = "Storage Count"; productSheet.Cells(rowIndex + 2, columnIndex).Value = "Sales in Progress"; productSheet.Cells(rowIndex + 3, columnIndex).Value = "Recalc Storage Count "; productSheet.Cells(rowIndex + 1, columnIndex + 1).Value = itemProduct.StorageCount; productSheet.Cells(rowIndex + 2, columnIndex + 1).Value = itemProduct.OpenOrders.CountOfSales; productSheet.Cells(rowIndex + 3, columnIndex + 1).Value = itemProduct.StorageCount - itemProduct.OpenOrders.CountOfSales; SetProductStorageCountColor(itemProduct.StorageCount, productSheet.Cells(rowIndex + 1, columnIndex + 1)); SetProductStorageCountColor(itemProduct.StorageCount - itemProduct.OpenOrders.CountOfSales, productSheet.Cells(rowIndex + 3, columnIndex + 1)); }
private void button1_Click(object sender, EventArgs e) { // start excel and turn off msg boxes excelApplication = new XlApplication(); excelApplication.DisplayAlerts = false; // add a new workbook XlWorkbook workBook = excelApplication.Workbooks.Add(); XlWorksheet workSheet = workBook.Worksheets[1]; workSheet.Cells(1, 1).Value = "these sample shapes was dynamicly created by code."; // create a star XlShape starShape = workSheet.Shapes.AddShape(LateBindingApi.Excel.Enums.MsoAutoShapeType.msoShape32pointStar, 10, 50, 200, 20); // create a simple textbox XlShape textBox = workSheet.Shapes.AddTextbox(LateBindingApi.Excel.Enums.MsoTextOrientation.msoTextOrientationHorizontal, 10, 150, 200, 50); textBox.TextFrame.Characters().Text = "text"; textBox.TextFrame.Characters().Font.Size = 14; // create a wordart XlShape textEffect = workSheet.Shapes.AddTextEffect(LateBindingApi.Excel.Enums.MsoPresetTextEffect.msoTextEffect14, "WordArt", "Arial", 12, LateBindingApi.Excel.Enums.MsoTriState.msoTrue, LateBindingApi.Excel.Enums.MsoTriState.msoFalse, 10, 250); // create text effect XlShape textDiagram = workSheet.Shapes.AddTextEffect(LateBindingApi.Excel.Enums.MsoPresetTextEffect.msoTextEffect11, "Effect", "Arial", 14, LateBindingApi.Excel.Enums.MsoTriState.msoFalse, LateBindingApi.Excel.Enums.MsoTriState.msoFalse, 10, 350); // save the book string fileExtension = XlConverter.GetDefaultExtension(excelApplication); string workbookFile = string.Format("{0}\\Example4{1}", Environment.CurrentDirectory, fileExtension); workBook.SaveAs(workbookFile); // close excel and dispose reference excelApplication.Quit(); excelApplication.Dispose(); FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile); fDialog.ShowDialog(this); }
private static void PrintWorksheet(XlWorksheet worksheet) { int rowIndex = 1; Color color = Color.FromArgb(worksheet.TabColor.ArgbColor); Console.WriteLine("Worksheet Name: {0}, Color: {1}, Position: {2}", worksheet.Name, color, worksheet.Position); foreach (XlColumn cur in worksheet.Columns) { Console.Write(cur.Name + ", "); } Console.WriteLine(); foreach (IXlRowData row in worksheet.Rows) { int columnIndex = 1; PrintRow(worksheet.ValueRow(rowIndex - 1)); //foreach (IXlCell cell in row) //{ // Console.WriteLine("Row: {3}, Col: {4}, Cell Value: {0}, Type: {1}, Style: {2}", cell.CellValue, cell.CellType, cell.CellStyle, rowIndex, columnIndex++); //} rowIndex++; } }
private void CheckHeaders(XlWorksheet worksheet) { if (worksheet != null) { Schema s = null; List <string> h; //if (worksheet.Name == "DET_Deployments") //{ // s = DeploymentDTO.GetSchema(); //} //else // return; //h = new List<string>(); //foreach (SchemaEntry c in s) //{ // h.Add(c.ColumnName); //} //if (!ExcelBaseDet.HasHeaders(ExcelBaseDet.Headers(worksheet), h)) //{ // generalDet.ValidationIssues.Add(ValidationIssue.Code.MissingFieldHeader, "Missing column header in " + worksheet.Name); //} } }